Thursday, July 24, 2014

Importing Dynamics GP AR Apply records using SQL

By Steve Endow

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.

Steve Endow, Precipio Services
Read RM payment application data from a staging table and apply payments to open invoices

sp_help RM20201
sp_help taRMApply

DECLARE @ApplyAmount NUMERIC(19, 5)
DECLARE @ApplyFromType INT
DECLARE @ErrorString VARCHAR(255)

SELECT @ApplyDate = '2014-07-24'

--Get data from staging table
SELECT RowID, ApplyFrom, ApplyTo, ApplyAmount, ApplyFromType, ApplyToType FROM staging WHERE imported = 0

OPEN ApplyCursor

--Retrieve first record from cursor
FETCH NEXT FROM ApplyCursor INTO @RowID, @ApplyFrom, @ApplyTo, @ApplyAmount, @ApplyFromType, @ApplyToType


       --Perform apply
       EXEC dbo.taRMApply
              @I_vAPTODCNM = @ApplyTo, -- char(21)
              @I_vAPFRDCNM = @ApplyFrom, -- char(21)
              @I_vAPPTOAMT = @ApplyAmount, -- numeric
              @I_vAPFRDCTY = @ApplyFromType, -- int
              @I_vAPTODCTY = @ApplyToType, -- int
              @I_vDISTKNAM = 0, -- numeric
              @I_vWROFAMNT = 0, -- numeric
              @I_vAPPLYDATE = @ApplyDate, -- datetime
              @I_vGLPOSTDT = @ApplyDate, -- datetime
              @I_vUSRDEFND1 = '', -- char(50)
              @I_vUSRDEFND2 = '', -- char(50)
              @I_vUSRDEFND3 = '', -- char(50)
              @I_vUSRDEFND4 = '', -- varchar(8000)
              @I_vUSRDEFND5 = '', -- varchar(8000)
              @O_iErrorState = @ErrorState OUTPUT, -- int
              @oErrString = @ErrorString OUTPUT -- varchar(255)

       --Check for success
       IF (@ErrorState = 0)
                     --If apply was successful
                     UPDATE staging SET imported = 1, importstatus = '' WHERE RowID = @RowID
                     --If apply failed
                     UPDATE staging SET imported = 0, importstatus = @ErrorString WHERE RowID = @RowID

       FETCH NEXT FROM ApplyCursor INTO @RowID, @ApplyFrom, @ApplyTo, @ApplyAmount, @ApplyFromType, @ApplyToType


CLOSE ApplyCursor


Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

No comments: