Importing customer payment for existing invoice

We have an existing payment engine that processes credit card and checking. I have created the ability to lookup invoices with a balance, "grab" all the details and offer customer payment through other means. Now I need to bring the payment information back into Sage 500, so the invoice will be closed. Has anyone out there done this before, or know of stored procedures I should focus on?

PS - I am fairly fluent with SQL. I would like to automate this process, so I am trying the SQL way before involving Data Import Manager.

  • If you are on a current version of Sage 500 there is a stored procedure in the database that allow you to process Cash Receipts. Once it is complete and if all the data you pass is validated it will create a pending batch that would be available in Sage 500 for your users. This procedure is called spARapiPendingCashReceiptIns. Unfortunately there is no detailed documentation on the procedure so you will need to review it and figure out how to load the staging tables, call the procedure, and then check the log table for warnings, errors, and/or success.
  • Hi Russ, thank you for the quick reply. The really fun part about this is, I am actually working on a Mas500 system. But I see the stored proc you referenced, and I can go through it to figure out how I need to arrange the staging table data to then run the procedure. It looks like a massive procedure, so I better make a coffee run.

    In your experience, assuming I populate the necessary tables references in the proc, is that all I need to do to have the payment "attached" to the existing invoice/close the invoice/posted property in the system? I respect all the ins-and-outs built in to the payments and batching functions, so I don't want to cut corners.
  • I am not really sure what you are asking above, but yes if you load the staging tables correctly and call the procedure correctly you will end up with a pending cash receipts batch. You will still need to go into Sage 500 and post the batch through the user interface. However, if you have never done this before it will take you a bit of time to understand this and how to properly call it. I would give yourself a week or so worth of coffee runs...
  • That is perfect! The office manager can then go into the pending batch and process it.

    Thank you!
  • I have gone over the entire procedure. It has answered a ton of questions. But I have a more questions...

    1. Is it safe to assume, if you provide the invoice key in StgCustPmtAppl.ApplyToInvcNo then the payment will be associated? I see where that field is being migrated over to the live table but I don't see where the invoice balance is being set to $0.00 and status is set to closed.

    2. This procedure seems to specifically deal with credit card and misc cash transactions. Is there a different procedure for checking (ACH) transactions?

    Thank you for your responses!!