Causes of trial balance imbalances
The following are some areas to check if your Trial Balance is not balancing
- Invalid or non-existent account groups against GL accounts
- Invalid accno or subaccno values in the GL_TRANS table
- Invalid period_seqno values in the GL_TRANS table
- Unbalanced GL Batches
- Retained Earnings entries not in the first period of a financial year
- Incorrect End of Year Retained Earnings entry
- Missing Retained Earnings entries for batches posted to a prior financial year
- Mixed periods within a GL Batch
Possible causes of Trial Balance imbalances
Invalid or non-existent account groups against GL accounts
select * from glaccs
where accgroup not in (select accgroup from gl_accgroups)
Invalid accno or subaccno values in the GL_TRANS table
select t.*, s.accno, s.subaccno from gltrans t
left join glsubaccs s on t.accno=s.accno and t.subaccno=s.subaccno
where s.accno is NULL or s.subaccno is NULL
Invalid period_seqno values in the GL_TRANS table
select * from gltrans
where period_seqno not in
(select seqno from period_status where ledger = 'G')
Unbalanced GL Batches
select batchno, sum(amount) as batchtotal
from gltrans
where batchno <> -1
group by batchno
having abs(sum(amount)) > 0.01
order by batchno
Retained earnings entries not in the first period of a financial year
select * from gltrans
where batchno = -1 and period_seqno not in
(select seqno from period_status where ledger = 'g' and period_seqno = 1)
Incorrect End of Year Retained Earnings entry
Find the EOY profit/loss posted to the database for the financial year that you're reconciling.
select * from gltrans
where details like 'GL End Of Year Profit/Loss' and period_seqno = XX
(The period_seqno in this example would be that of Apr 2008)
Calculate what the EOY profit/loss should be based on the transactions in the GL_TRANS table.
select sum(t.amount) from gltrans t
join glaccs a on t.accno = a.accno
where a.section = 0
and t.periodno >= XX and t.periodno <= XX
and seqno < XXXX
(The seqno used is the one from the previous script. The PERIODNO used are the from value and to value for the previous year - in this example, that of Apr 2007 and Mar 2008)
Discrepancies between the 2 scripts are usually due to P&L account being changed to a BS account (or vice versa) after the EOY roll was done.
Missing Retained Earnings entries for batches posted to a prior financial year
Find any profit adjustment entries posted to the GL
select details, amount from gltrans
where periodno = -1
and details <> 'GL End Of Year Profit/Loss' and seqno > XXXX
(The seqno used is the one from the previous script.)
Calculate profit adjustments that should have been posted to the Retained Earnings account.
select t.batchno, sum(t.amount) as amount from gltrans t
join glaccs a on t.accno = a.accno
where a.section = 0
and periodno >= XX and periodno <= XX
and seqno > XXXX
group by batchno
order by batchno
(The seqno used is the one from the previous script. The PERIODNO used are the from value and to value for the previous year - in this example, that of Apr 2007 and Mar 2008)
You need to match the transactions from the above two scripts to determine any variations or any that are missing. Variations would need correction and the ones missing will need to be added to GL_TRANS.
Mixed periods in a GL Batch
All transactions within the same GL Batch should post into the same period in the GL.
The following will identify GL Batches that have mixed periods:
select batchno, periodno, sum(amount)
from gltrans g
where batchno <> -1
group by batchno, periodno
having abs(sum(amount)) > 0.01
order by batchno, periodno