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.
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
select batch_no, tran_no, dr_cr, amount from bch_tran
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.
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
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
Check the imbalance is corrected by running the Balance Sheet report and confirming the "Total Assets" and "Total Liabilities" numbers match.