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.

Figure 1: Age Open Receivable in the AR 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.

Figure 3: Sage 500 Create a new Job

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

Figure 6: Create step for Aging Vendor Accounts

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)

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.