I’m trying to build a BAQ for our CFO that shows all closing balances for each account back to a certain date. He wanted each column to be the closing balance from each month all the way up until now.
The problem I am running into is that the closing balance isn’t correct because there is no opening balance for any record. Is there something I’m doing wrong or perhaps a utility that can be run to recalculate the opening balances based on the historical data? What’s especially strange is that the printed Trial Balance report appears to be getting an opening balance from somewhere… but it’s not the GLPeriodBal table.
Why wouldn’t the values in the GLPeriodBal match up with the values printed on the Trial Balance report? What other logic or black magic might be at work here? If I were to summarize the GL Transactions would they (should they?) add up to the report balances?
Summarizing the TranGLC table did not match up to anything. I suspect there are ways to manually make transactions to move money from one account to another. Do I need to pull in data from another table?
I’ve made progress since my last update. I’ve found that GLJrnDtl appears to have “everything” in it. Now I can’t sit here and tell you what purpose in life TranGLC serves compared to GLJrnDtl… but I have very nearly replicated the Trial Balance report in a BAQ… I have one account that is like $400 off… which is like 0.0025% off… not sure what that’s all about.
TranGLC is filled in by the transactions in Epicor. The GLJrnDtl contains the entries from those transactions processed through the posting engine, as well as manual journal entries.
