I was working with a customer this morning who had recently imported and posted 2,800 AR cash receipts. Only after posting the cash receipts did they realize that the payment applications for those cash receipts did not import.
Since Integration Manager doesn't allow you to import just payment applications, we had to come up with a way to import the 2,800 AR payment applications.
A few years ago, I developed an eConnect integration that imported AR cash receipts and automatically applied them to open invoices. But since the client already had the payment application data, that was overkill--we just needed a way to import the applications without any additional fancy logic.
Rather than deal with a .NET application, I figured we could just use the eConnect RM Apply stored procedure--taRMApply. If we imported the AR payment application data into a SQL staging table, a SQL script could loop through the apply records and call the taRMApply procedure for each. Sounded easy, but I was afraid there would be a catch somewhere.
So I created an initial SQL script that would read the apply data from the staging table into a cursor, loop through that cursor, and call taRMApply. It turned out to be surprisingly simple and clean. And I was able to easily update each record of the staging table to indicate whether the apply was successful, and if not, record the error from eConnect.
Here is the SQL Script that I created, which I have also shared up on my OneDrive in case you want to download it instead.
The script assumes you have a staging table with a unique row ID, the necessary apply from and apply to values, and an imported flag field and an importstatus field. You can adjust the table names, fields, and field names to suit your needs.
One last note--the ErrorString output from the taRMApply procedure is going to be an error number. You can lookup that number in the DYNAMICS..taErrorCode table to get the related text error message.
You will want to test this yourself, and make sure to perform an initial test on a Test database first, but it appears to have worked well. It took maybe a minute or or two so to apply the 2,800 payments based on an initial test, so it appears to be fairly fast.
To my pleasant surprise, the process went smoothly, and other than a small error in the script that we quickly fixed, it worked well.
If you find any errors in the sample script below, or have any suggestions for improving it, please let me know.