RKL eSolutions Blog Trends and Insights

Automate the Sage 500 ERP Daily Aging Recalculation

Sage 500 ERP Aging Recalculation

Within Sage 500 ERP, there are tasks that allow you to age all of your Open Receivables and Open Payables to ensure that they are current when viewing this information on reports and explorer views throughout Sage 500 ERP.  These tasks are called Age Customer Accounts within the Accounts Receivable Module (Figure 1) and Age Vendor Accounts within the Accounts Payable Module (Figure 2).

Since this is often run to recalculate the aging for all accounts, a common request is to have this task automated such that a user does not have to remember to run these tasks daily.  Below we will walk you through the steps to automate this utilizing a SQL Server Agent Job on your Microsoft SQL Server.

 

Sage 500 Age Open Receivable in the AR module
Figure 1:
Age Open Receivable in the AR module

 

Sage 500 Age Open Payables in the AP module
Figure 2: Age Open Payables in the AP module

 

Automate the Aging

Each of the Aging processes has a procedure available that can be executed to Age all Vendor and Customer Accounts.  These procedures can be setup to execute on a scheduled basis to ensure that your aging information is up to date within Sage 500 ERP.

Some notes about the below:

  • This example was completed with Sage 500 ERP version 2016 (aka 7.7) and may differ slightly from other versions of Sage 500.
  • This example was created using SQL Server Management Studio 2012
  • The below steps require that you have at least a basic knowledge of Microsoft SQL Server Management Studio and appropriate access to configure a Job on your Microsoft SQL Server.

To automate the aging, follow these steps:

  • Within Microsoft SQL Server Management Studio, create a new Job (Figure 3)
  • Assign your job a name, such as “Age Vendor and Customer Accounts” (Figure 4)
  • Create and Name the first Step on your Job to “Age Customer Accounts” (Figure 5)
    1. Ensure that you select your production Sage 500 database
    2. Insert the code from sql into the command window
    3. Update the CompanyID parameter in the Command section to match your Sage 500 Company ID
    4. Save your Step
  • Create and Name the second Step on your Job to “Age Vendor Accounts” (Figure 6)
    1. Ensure that you select your production Sage 500 database
    2. Insert the code from sql into the command window
    3. Update the CompanyID parameter in the Command section to match your Sage 500 Company ID
    4. Save your Step
  • Setup a schedule to execute this job daily (Figure 7)
    1. We usually choose a time prior to the start of a typical work day
  • Save your Job.
Sage 500 Create a new Job Figure 3: Sage 500 Create a new Job

Sage 500 New Job Figure 4: Assign a name to your new Job, such as “Age Vendor and Customer Accounts”

Sage 500 Aging Customer Accounts
Figure 5: Create step for Aging Customer Accounts

Sage 500 Aging Vendor Accounts Figure 6: Create step for Aging Vendor Accounts

Sage 500 New Job Schedule Figure 7: Setup a daily schedule to execute this Job.

 

Once your job has been configured, confirm that the job is executing the next few days by reviewing the Job History within Microsoft SQL Server Management Studio.  (Figure 8)

Sage 500 Review Job History Figure 8: Review Job History to confirm that the job is completing successfully.

 

Want the SQL code for Age Customer Accounts and Age Vendor Accounts?

Contact us and we will send you the code.

Russ Griffith

Written by Russ Griffith

Working as the Director for our Sage 500 Practice, Russ assists companies who need to improve their business processes and evaluate how to make their business systems work for them. By working with the key resources at each company he is able to understand the needs and challenges that are present in each unique environment.