Written by Steve Endow
This morning, I assisted a customer who had imported and posted 2,800 AR cash receipts, only to discover afterward that the corresponding payment applications hadn’t been imported.
Integration Manager doesn’t have the capability to import only payment applications, so we needed a workaround to import the 2,800 missing applications.
I had previously designed an eConnect integration to import AR cash receipts and apply them to open invoices. However, because the customer already possessed the payment application data, that solution was unnecessary. We simply needed a method to import the applications directly.
Instead of using a .NET application, I decided to leverage the eConnect RM Apply stored procedure (taRMApply). By importing the AR payment application data into a SQL staging table, a SQL script could process each record and call the taRMApply procedure. It seemed straightforward, but I anticipated potential complications.
My initial SQL script read the apply data from the staging table into a cursor, looped through it, and invoked taRMApply for each record. The process proved surprisingly simple and efficient. I was able to easily track the success of each application by updating the staging table with a status flag and any error messages returned by eConnect.
The SQL Script I developed is provided below.
This script assumes you have a staging table containing a unique row ID, the necessary “apply from” and “apply to” values, an imported flag field, and an import status field. Feel free to modify the table and field names as needed.
Note: The ErrorString output from the taRMApply procedure will be an error number. This number can be cross-referenced with the DYNAMICS..taErrorCode table to retrieve the corresponding error message text.
Thorough testing on a test database is recommended. In my initial test, applying 2,800 payments took approximately one to two minutes, indicating good performance.
The process went smoothly overall. Apart from a minor script error that we quickly resolved, everything worked as expected.
If you encounter any issues with the sample script or have improvement suggestions, please don’t hesitate to reach out.
/*
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 = ‘2022-06-11T14:28:20Z’
--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.
You can also find him onGoogle+andTwitter
http://www.precipioservices.com
