Journal Listing Query

Epicor ERP 9
1

Epicor version 9.05.701

Epicor / Financial Management / General Ledger / Reports / Journal Listing

Does anybody know the table(s) that make up this report?

Essentially, this report gives me a great double entry audit log of every type of transaction…but i need the table(s) so that i can put it into epicor via SQL and modify / filter the data etc.

Note: The table GLPeriodBal only gives the balance of the GL, i need the individual transactions.

2

GLJrnDtl?

2 Likes
3

I really hope you meant “… put it into Excel via SQL …”, and that you’r not looking to change the table in your live DB via SQL.

And GlJrnDtl will give you the entries that match the Report you referenced.

Not sure about E9, but in E10, to find transactions that make up the “Periodic Posting Process” entries, use the table TranGLC

1 Like
4

sorry my bad wording…I did mean put it into excel!
Apologies for the confusion

5

Here’s the SQL from an ODBC connection I just made to test it.

SELECT GLJrnDtl.Company, GLJrnDtl.BookID, GLJrnDtl.JournalCode, GLJrnDtl.JournalNum, GLJrnDtl.JournalLine, GLJrnDtl.GLAccount, GLJrnDtl.DebitAmount, GLJrnDtl.CreditAmount, GLJrnDtl.Description
FROM LIVE.dbo.GLJrnDtl GLJrnDtl
WHERE (GLJrnDtl.FiscalYear=2018) AND (GLJrnDtl.FiscalPeriod=4)
GROUP BY GLJrnDtl.Company, GLJrnDtl.BookID, GLJrnDtl.JournalCode, GLJrnDtl.JournalNum, GLJrnDtl.JournalLine, GLJrnDtl.GLAccount, GLJrnDtl.DebitAmount, GLJrnDtl.CreditAmount, GLJrnDtl.Description
ORDER BY GLJrnDtl.JournalCode, GLJrnDtl.JournalNum, GLJrnDtl.JournalLine

Taking the results and making a pivot table to summarize the Debits and Credits (done separately), matches the summary table of that report you mentioned.

EDIT: The GROUP BY is unnecessary as I didn’t SUM() the Debits and Credits in the query.

1 Like