BAQ (simple?) Linking GLJrnDtl to InvoiceDtl

Epicor ERP 10
1

Problem: I need all the detail information for everything that has been invoiced. Example: I need all of the Part Numbers & Descriptions that have been invoiced for chart #1101 in our chart of accounts.

Solution: The GLJrnDtl table has the value for every line item in the InvoiceDtl table. Link the GLJrnDtl table to InvoiceDtl table and voila!

Nope try again: The GLJrnDtl table has the ARInvoiceNum field that gets me halfway. I cannot find the Invoice Line number referenced in that table anywhere. So…

Does someone know how to link the GLJrnDtl table to the InvoiceDtl table? Is there an intermediary table in there? Is there a cryptically named field in the GLJrnDtl table that translates to “InvoiceLine”?

Thanks for your help in advance!

2

I think you’ll need the TranGLC table. RelatedToFile would be InvcDtl
(invoice line) and Key1 is the Invoice Number. IIRC Key2 is the Invoice
Line

4 Likes
3

Excellent. Thank you for the response. This is exactly what I was looking for. Now I have a full breakdown of all of our sales that ties to our financials nicely. Much appreciated.

4

Glad I could help Bruce

5

For anyone else who comes across this, including these columns in the join makes the query optimizer use an index for about a 100x performance boost:

select *
from erp.GLJrnDtl gl
join erp.TranGLC tr
	on gl.Company = tr.Company
	and gl.FiscalCalendarID = tr.FiscalCalendarID
	and gl.FiscalYear = tr.FiscalYear
	and gl.JournalCode = tr.JournalCode
	and gl.JournalNum = tr.JournalNum
	and gl.JournalLine = tr.JournalLine
3 Likes