The Dynamics GP receivables aging routine can be a controversial topic. For some businesses, seeing aged values at a certain period in time (say end of week, end of month, end of year), is all that’s needed for receivables management. For other businesses, there may be a demand to have more up to date receivable balances thus requiring the aging routine to be run on a more frequent basis.
With a system like Microsoft Dynamics GP where the customer base is so diverse, having this sort of flexibility is key and makes sense from a development perspective. However… it does create a rather tedious process of having to run the aging process each time the receivables team deems they’d like to see up to date summary balances by period.
Until Microsoft releases the ability to auto-age on a pre-determined schedule (fingers crossed for GP 2016!), the following script can be used to run the aging process directly through SQL. The beauty of having a script is that this routine can be scheduled to run on a nightly basis thus never having to manually run again!
Please note that this stored procedure is designed to run in
GP2015R2
. Table/Stored procedures can change between versions thus requiring the script to be updated. If you’re looking for another version of the script, please contact us or let us know in the comments below.
/* Automatic Running of Aging Routine --Includes Aging of Finance Charges --Deployed Date yyMMdd --Runs every x hours, days, months, etc */ DECLARE @O_iErrorState int, @I_dAgingDate datetime select @I_dAgingDate = convert (varchar(10),GetDate(),102) EXEC dbo.rmAgeCustomer 0,'','þþþþþþþþþþþþþþþ',@I_dAgingDate,127,1,0,'',@O_iErrorState OUT /* Automatic Running of Aging Routine --Excludes Aging of Finance Charges --Deployed Date yyMMdd --Runs every x hours, days, months, etc */ DECLARE @O_iErrorState int,@I_dAgingDate datetime select @I_dAgingDate = convert(varchar(10),GetDate(),102) EXEC dbo.rmAgeCustomer 0,'','þþþþþþþþþþþþþþþ',@I_dAgingDate,127,0,0,'', @O_iErrorState OUT
After running the script, you can open the aging window in GP to view your last run and also view the customer summary window to see the “Aged as of date”.
Quick, efficient, and manageable solutions that make everyone’s live easier are our favorite. Running the auto-aging routine via SQL when wanting to see up to date balances is a no-brainer. Just be sure that this routine is documented so that it doesn’t get forgotten in future upgrades or server changes.
2 thoughts on “Automatically Running the Receivables Aging Routine via SQL – Dynamics GP”
Just saw this article. It’s great info. We are on GP2015R2 and have a need to automate the RM Paid Transaction Removal.
Do you have any scripts or suggestions that would help?
Hey @GERRYW, I do not!
However, there is a stored procedure (similar to this A/R aging SP). It’s called rmPaidTransactionRemoval.
I’m sure a good enough search for that SP will turn up something good. OR, with some SQL expertise, could likely get it tested & then scheduled to run in production.
If an absolute necessity, maybe take a look @ PowerShell w/ Dynamics GP as well.
Good luck!