Automatically Running the Receivables Aging Routine via SQL - Dynamics GP

The manual aging routine can be a controversial topic when dealing with Dynamics GP.  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.

Check back soon for information on more simple solutions that we'll be discussing!