Generating Customers list and their expected balances based on accounting journals

Overview

Customers are displaying an incorrect Net Balance even when they have never purchased anything before.

This can occur when the data for the balance of the customer table is incorrectly updated by the system.

Solution

The solution when this issue occurs is to update the database records directly by executing a custom data fix script for which customers should submit a support ticket for further analysis and resolution.

To start off the troubleshooting process, execute the below SQL statement which will return the list of all the customers and the expected balance values based on the journal entries in the system. The script can be executed and the results compared with the current state of the customers as seen from the UI and any obvious discrepancies will indicate the need to engage Everest support for assistance.

SQL Script:

select c.cust_code, 
isnull(u_dr,0) udr, isnull(u_cr,0) ucr, unpost_dr, UNPOST_CR,
isnull(pdr,0) pdr, isnull(pcr,0) pcr, cur_dr, cur_cr,
adv_avail
from cust c with (nolock) left join
( 
select sub_code, sum(u_dr) u_dr, sum(u_cr) u_cr
from 
(
select sub_code, case dr_cr when 1 then TRAN_AMT else 0 end u_dr,
case dr_cr when 2 then TRAN_AMT else 0 end u_cr
from bch_head h with (nolock) 
where doc_type in (8,9,11,12) and BANK_CODE in (
select ACNT_CODE from coa with (nolock) where CTRL_ACNT_TYPE in (2,5)
)
and sub_type = 2
union all 
select sub_code, case dr_cr when 1 then AMOUNT else 0 end u_dr,
case dr_cr when 2 then AMOUNT else 0 end u_cr
from BCH_TRAN t with (nolock) 
where doc_type in (8,9,11,12) and acnt_code in (
select ACNT_CODE from coa with (nolock) where CTRL_ACNT_TYPE in (2,5)
)
and sub_type = 2
) s group by sub_code 
)a on a.SUB_CODE = c.CUST_CODE
left join (
select sub_code, sum(pdr) pdr, sum(pcr) pcr from (
select sub_code, doc_no, doc_type, sum(case dr_cr when 1 then AMOUNT else 0 end) pdr,
sum(case dr_cr when 2 then AMOUNT else 0 end) pcr
from transact t with (nolock) 
where doc_type in (8,9,11,12) and acnt_code in (
select ACNT_CODE from coa with (nolock) where CTRL_ACNT_TYPE in (2,5)
) 
and sub_type = 2
group by sub_code, doc_no, doc_type
having sum(case dr_cr when 1 then AMOUNT else -amount end) <> 0 
) a
group by sub_code
) b on b.sub_code = c.cust_code;

Sample output from the ACCEL SAMPLE COMPANY database:

Back to top