ERP Software Logo1

Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

Crestwood Associates

Auto Age Receivables in Dynamics GP


Email | Print

Automatically Age Receivables - SQL Script

Here's a great time-saving tip.  Set up your receivables to automatically age.

Yes, I said RECEIVABLES.  In Dynamics GP, payables age automatically but receivables do not.  Usually we have to manually run the receivables aging process.  But did you know you can schedule receivables to age automatically too with a simple SQL script?  This trick will age all customers, statement cycles, and balance types as of the current date.

Here's How:

  1. Log into SQL Management Studio
  2. Go into the Object Explorer / Management / Maintenance Plans
  3. Right click on Maintenance Plans and select ‘New Maintenance Plan’
  4. Name the plan – we used Daily AR Aging
  5. Go into the toolbox on the left side
  6. Choose Execute T-SQL Statement Task and drag into the big empty area on the right
  7. Right click in the box and choose editAuto age receivables 1
  8. Copy and paste in the following SQL statement (there are Unicode characters in this script – so it’s important you copy and paste)
    USE two
    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
    SELECT @O_iErrorState

    Note:  make sure you change the name of the database in the first line of code to your own.  In this example, the database is named "two".

  9. Next, go up and under the schedule area, click on the little schedule button to tell the system when you would like this script to run.
  10. Best Practice from a running standpoint would be to have it run after midnight, so the date is current.
  11. If you have multiple companies / databases, simply copy the five lines again and change the company name.

 

If you're not familiar with SQL Management Studio, you might want to watch this short video clip on setting up the SQL script.

 

For all your Dynamics GP needs, contact Crestwood Associates at Marketing@Crestwood.com.

by Crestwood Associates

 

Comments are closed.