ERP Software Logo

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 [email protected].

    by Crestwood Associates

     

    Ask This Expert a Question / Leave a Comment

     

     
     
    Show Buttons
    Hide Buttons