Customers, Suppliers: Difference/discrepancy/discrepancies between Customers/Suppliers Control Account and Customers/Suppliers Age Analysis balances – MS SQL Diagnostic Scripts

• analyse (diagnose) a difference/discrepancy between the Customers/Suppliers Control Account and
• the Customers/Suppliers Age Analysis balances, in Sage 200 Evolution. 

First, note the following:

This query should preferably be fixed by a professional Evolution support consultant as it requires an understanding of how to compile and run MS SQL diagnostic- and update scripts.

Therefore, contact either your Evolution Business Partner (BP) or Evolution Support to get this issue resolved.

Consider the following advice to accomplish the above:

In the explanation below we’ll be referring to analyzing the Receivables Control account vs the Customers Age Analysis balance.

However, the same SQL scripts below can also be used with minimal adjustments, when analyzing the Payables  Control account vs the Suppliers Age Analysis balance.

A. Before running the SQL Scripts

First, apply the following steps:

1. Back up the company and ensure all users are logged out.

2. Run a General Ledger Relink to ensure the total Customers (AR) Control account balance is correct in terms of current transactions processed

3. Run an AR Balance Relink to ensure the total outstanding balance is correct in terms of current transactions processed

4. If the above-mentioned difference still hasn’t been fixed, run the MS SQL scripts below.

B. Receivables Control Account used

In this scenario, the Receivables Control Account has an Account ID = 76, and will be used in the SQL scripts below.

C.  Check for records in the Customers Sub-Ledger not in the Receivables Control account 

Run the following SQL script on the company database. 

select * from postar where cauditnumber not in

(select  distinct cauditnumber from postgl where accountlink = 76)

Note the following:

• Adjust the above script’s Accountlink value in accordance with your own value.
• If there is more than one Receivables Control Account, you can adjust it in the following syntax format:

 select * from postar where cauditnumber not in

(select  distinct cauditnumber from postgl where accountlink in (76, 50))

D. Check Records in Customers Control not in Customers Sub-Ledger  

Run the following SQL script on the company database

select * from postgl where accountlink = 76 and

cauditnumber not in (select cauditnumber from postar)

Note the following:

• Adjust the above script’s Accountlink value in accordance with your own value.
• If there is more than one Receivables Control Account, you can adjust it in the following syntax format:

select * from postgl where accountlink in (76,45) and

cauditnumber not in (select cauditnumber from postar) 

E. Different Values Posted between the Customers Sub-Ledger & Customers General Ledger Control Account

Run the following SQL script on the company database

Declare @ARControlAccountLink Int

Set @ARControlAccountLink = 76

Select AR.cAuditNumber        as [AR AuditNumber],

  AR.ARValue         as [Value in PostAR],

  Ledger.LedgerValue       as [Value in PostGL]                                            

from

(Select cAuditNumber        as cAuditNumber,

  Sum(IsNull(Debit,0)-IsNull(Credit,0)) as LedgerValue 

from PostGL

group by cAuditNumber,AccountLink

having AccountLink = @ARControlAccountLink)   as Ledger

inner join

(Select cAuditNumber        as cAuditNumber,

  Sum(IsNull(Debit,0)-IsNull(Credit,0)) as ARValue 

from PostAR

group by cAuditNumber)        as AR

on Ledger.cAuditNumber = AR.cAuditNumber

where Round(Ledger.LedgerValue,2) <> Round(AR.ARValue,2)     

Note the following:

Adjust the above script’s Accountlink value in accordance to your unique value.

E. Supplier Control Account vs Suppliers Age Analysis Balance analysis

The above scripts can be easily adjusted for Supplier discrepancies. 

To accomplish it, replace all instances of PostAR above with PostAP and AR with AP, in the above scripts.

F. Other reasons for Customer/Supplier Control Account and Customers/Suppliers Age Analysis balances

Open this link for a detailed article on other reasons and solutions to fix the above issue.