Correcting Balance Sheet Discrepancies

Overview

Customers may encounter an issue where there is a decimal imbalance or discrepancy on the balance sheet resulting in an unbalanced balance sheet where total assets do not match total liabilities and capital as expected.

This scenario can happen for different reasons such as incorrect use of the software, decimal rounding, and/or direct updates made to the database through 3rd party integrations for instance the Everest-Avatax integration which populates records in the Everest database.

This article addresses the scenario where the unbalanced balance sheet issue occurs especially on rare occasions with Avatax integration when decimal rounding is done.

Solution

Administrator access to Everest Company Database is required to implement the resolution steps outlined below to ensure that the total assets match total liabilities and capital:

1. Run this query to find the unbalanced journal in the unposted transactions:

select batch_no, tran_no, sum(case dr_cr when 1 then tran_amt else -tran_amt end) amt from (
select batch_no, tran_no, dr_cr, tran_amt from bch_head 
union all 
select batch_no, tran_no, dr_cr, amount from bch_tran
) a
group by batch_no, tran_no
having sum(case dr_cr when 1 then tran_amt else -tran_amt end) <> 0

2. Backup records before updating

Note: Update the below queries accordingly to match the batch_no, tran_no, and order_no identified in step 1 above.

select * into zd2405410_bch_head  
from bch_head where batch_no = 789 and tran_no = 3037

select *  into zd2405410_BCH_TRAN 
from BCH_TRAN where batch_no = 789 and tran_no = 3037

select invoices_id, order_no, status, tax_amount, inv_amount, tax_amt_fex into zd2405410_invoices
from invoices where order_no = '475385' and status = 9

3. Fix the discrepancy in the data by running this query:

Note: Update the bch_tran_id, bch_head_id, and invoices_id in the below queries accordingly.

begin transaction 
update BCH_TRAN set amount =0, amount_fex = 0  where bch_tran_id = 6767208
update BCH_HEAD set TRAN_AMT =0, amount_fex = 0  where bch_head_id = 1399092
update invoices set tax_amount = 0, inv_amount=0, tax_amt_fex = 0  where invoices_id = 1094289
commit

4. Finally, recalculate transactions using the existing stored procedure:

exec UPDATE_COAHIST_ACTIVITY_FROM_TRANSACT '' 

5. You may run the following query to retrieve logs

select top 10 * from [Avatax.Log] where entity_code = '475385' and entity_type = 9 order by response_received_on

Testing

Check the imbalance is corrected by running the Balance Sheet report and confirming the "Total Assets" and "Total Liabilities" numbers match.

Back to top