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.
I think I figured this out. I stared at this for half the day today.
In short, it looks like “period zero” is the only one that stores the opening balance. Then all other math is pulled from there.
Here is a dump of GLPeriodBal (all columns in the order they come in) for a specific account:
Another way to look at this: ignore period zero and you can do a running sum.
What started all this for me is we were baffled as to why some (hardly used) accounts had huge opening balances in them for our new fiscal year (which started July 1).
It turns out that back in 2021 we had to make fiscal years out to 2026 because a 5-year deferred-revenue service contract. Well, when you make the new year, it gives this warning:
Fiscal Calendar Main is referenced by GL Book(s): Main. Updating this calendar requires running Transfer Balances to apply the change to Periodic Balances. Are you sure?
And I guess someone did…So we are still living with 2021 data today.
Incidentally, I did read that you can “transfer opening balances” more than once without any harm.
