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.
Below is the SQL Script that I created.
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.
/*
7/24/2014
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 @RowID
INT
DECLARE @ApplyFrom
VARCHAR(21)
DECLARE @ApplyTo
VARCHAR(21)
DECLARE @ApplyAmount
NUMERIC(19, 5)
DECLARE @ApplyFromType
INT
DECLARE @ApplyToType
INT
DECLARE @ApplyDate
DATETIME
DECLARE @ErrorState
INT
DECLARE @ErrorString
VARCHAR(255)
SELECT @ApplyDate
= '2014-07-24'
--Get data from
staging table
DECLARE ApplyCursor
CURSOR FOR
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
WHILE @@FETCH_STATUS
= 0
BEGIN
--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)
BEGIN
--If apply was successful
UPDATE staging SET imported = 1, importstatus = '' WHERE RowID = @RowID
END
ELSE
BEGIN
--If apply failed
UPDATE staging SET imported = 0, importstatus = @ErrorString WHERE RowID = @RowID
END
FETCH NEXT FROM ApplyCursor INTO @RowID, @ApplyFrom, @ApplyTo, @ApplyAmount, @ApplyFromType, @ApplyToType
END
CLOSE ApplyCursor
DEALLOCATE 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.
No comments:
Post a Comment