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.
Brendan Philbin
Epicor Administrator - EthosEnergy