I could use some help joining the GLJrnDtl to the GLJrnDtlMnl table for automatically reversing journal entries (we are running on-premise 10.2.700.8)
I have been working on some SQL views to capture GL data but have run into an issue joining these two tables. The primary kind of journal entry we are dealing with here are accruals of cost and revenue at month end, which our accountants set up to automatically reverse at the first date of the following fiscal period.
I have three custom fields on the GLJrnDtlMnl_UD table that I need to link to the GLJrnDtl record(s) themselves for this SQL view. The issue that I’m running into here is that, when I join in the following way, the GLJrnDtlMnl record only links to the original GLJrnDtl transaction but not to the reversing transaction.
SELECT * FROM Erp.GLJrnDtl as t1 LEFT OUTER JOIN Erp.GLJrnDtlMnl as t2 ON t1.Company = t2.Company AND t1.FiscalYear = t2.FiscalYear AND t1.JournalNum = t2.JournalNum AND t1.JournalLine = t2.JournalLine AND t1.JournalCode = t2.JournalCode AND t1.BookID = t2.BookID AND t1.FiscalYearSuffix = t2.FiscalYearSuffix AND t1.FiscalCalendarID = t2.FiscalCalendarID
I have been unable to find any documentation for joining these two tables, so any help you can provide would be greatly appreciated. Looking at the records in these tables, I’m having trouble intuiting the correct fields on which to join.
Thank you in advance for your help.
Epicor Administrator - EthosEnergy