I am building out a script that de-normalizes GL ledger entries for an external reporting interface and need to esure I am querying the GLJrnlDtl table correctly.
This de-normalization proccess happens in 2 passes: the first pass scans the GLPeriodBal table and re-constructs the opening and closing balance for each account on any and all years/periods within the report range. I got that and it works great.
The second pass is going to take ALL journal entries (GLJrnlDtl) for each of the given FiscalYear and FiscalPeriod “chunks” and read the entries in the order they SHOULD BE INTERPRETED.
So let’s say I know for FiscalYear=2023 and FiscalPeriod=10… I want to read ALL entries (for ALL accounts) that happened in the correct order, this is what I have thus far:
SELECT * FROM Erp.GLJrnDtl WHERE FiscalYear=2023 and FiscalPeriod = 10 AND Posted = 1 ORDER BY JEDate ASC, JournalNum ASC, JournalLine ASC;
My question: is the ORDER BY correct?
The idea here is: I will iterate over the records returned in-order and apply the debit and credit ammounts to the “Starting Balance” of each running total. On my end, I am storing EACH individual GLJrnlDtl record - but WITH THE STARTING and ENDING balance. This way, the data is flattened out and when pulling a single journal entry, I can instantly see what the running total for the given account was before anbd after that entry took place (without the need to scan and calculate things).
A side question I have is this: where on earth does Epicor come up with this JournalNum’s? They seem out-of-order, and to have no particular rhyme or reason.