Understanding the GL Account Table

Epicor ERP 10
1

Good afternoon…

Let me preface this question by pointing out: I am a software engineer, NOT an accountant.

Boss comes to me and says: I want a HTML view into the GL. Backstory here: I have built out a very comprehensive web interface for various BI aspects of our Epicor system. The idea behind his request is so he can quickly view where certain things “tie together” in the GL.

Fast forward to now…

I noticed 2 main tables: GLJrnHed and GLJrnDtl.

I assumed this was something similar to how invoices, PO’s and sales orders were structured: each HED record has many DTL records.

But I am not sure I understand the relationship of HED <-> DTL entries as they relate to the GL.

Why you ask? It is 2023… and yet - the newest record in our GLJrnHed table is from END OF LAST YEAR. This is the first strange thing to me. The next strange thing is this: the entries in the DTL table seem to have no relation to the entries in the HED table. Yes, the JournalNum joins fine - but here is an example:

I have 10 entries in the GL DTL table that reference a FEW AP invoices. All 10 were entered just a couple days ago (2023-02-11). Of coarse, there are the matching debit and credit entries, but… the GLJrnHed has 3 entries (not just one) with this same JournalNum.

What is even more puzzling is: Of the 3 entries, they are dated from 2012, 2013 and 2014. Why would these 10 recently-created DTL entries be related to 3 entries in the HED table that seem to have absolutely no relation to each-other??

It seems like - for what my boss wants - I need only look at the DTL table (the HED table really does not seem to provide anything useful for what I am doing)… no?

Sorry… my understanding of double-entry bookkeeping is limited to what I recal from grade school.

-Dean

2

I believe GLJrnHed only exists for manual journal entries created and posted by a user. By and large, the “automatic” journal entries (AP Invoices, Payments, AR Invoices, Cash Receipts, Bank Adjustments, Inventory/WIP transactions, etc…) will only have GLJrnDtl records for those movements. Those GLJrnHed records probably do not actually match the details. Did you join on Fiscal Year, Journal Num, and Journal Code?

Additionally, you will want to use the TranGLC table to link these GLJrnDtl records to the source records. The RelatedToFile column in TranGLC will give you the source of that transaction.

3

Soooo…,

I need to join - not only on JournalNum - but also FiscalYear AND JournalCode? Talking about joining DTL and HED tables here…

Also… I see the TranGLC table. What would the join criteria be to link this with GLJrnDtl?

I am assuming: JournalNum, JournalLine and FiscalYear?

4

This thread and others referencing transglc go over the joins.

1 Like