Account Payable Expense Distribution Report

Epicor ERP 10
1

I would like to access the raw data set that makes up this report, it uses a TempGLDist table, that I assume is built on the fly as I cannot find it in the DB. My assumption is that there is a base program that builds this when you execute the report.
Is there a way to find out what tables it uses to populate the TempGLDist table so I can build a BAQ ?

2

Pretty sure this comes from the Posting Engine. Since E9, Epicor has had the ability to post to multiple books so the kind of account mapping that used to exist is not as easy anymore.

For posted items, the GLCTran table will show you the mapping you’re looking for, I believe. It contains ALL the mapping so you’ll have to select out the tables of interest

Mark W.

3

Like Mark said, the GLCTran table holds the info for posted transactions. Since you’re interested in the Temp table, I’ll assume you’re looking for the info prior to posting.

The TranGLC holds the details for transactions that will use GLC’s For example, if you specify an expense account on a PORelease - or if one is automatically specified by a GLC (like on the PartClass) - a TranGLC record is created. The record would have:

  • RelatedTable = PORel
  • Key1 = PONum
  • Key2 = POLine
  • Key3 = PORelNum

So when a PO Receipt is processed, the GL transactions that are created are pulled from the TranGLC table.

I’d guess that the records with RelatedTable = APInvDtl woukd have what youd need.

1 Like
4

And if you’re interested in unposted account numbers, use the Edit List under the Actions menu. You can send it to CSV if you want to manipulate it. It’s ugly but it works.

5

I took a look at TranGLC and it looks like you’d want

  • RelatedTable = APinvExp
  • Key1 = VendorNum
  • Key2 = APInvNum
  • Key3 = Invc Line (0 for Header Misc Charge)
  • Key4 = Not sure
  • Key5 = APInv Group ID

Note: there appears to be a pair of entries, wit:

  • One having Key5 as blank. This has JrnlCode, Num and line info, as well as a Debit Amt
  • The other having the APInv Group ID as Key5. This one has theGL Acct, but no Jrnl info, or DB(or CR) amount
6

Thanks for all the replies. I believe I figured out what I needed.

select
[APInvHed].[VendorNum] as [APInvHed_VendorNum],
[Vendor].[Name] as [Vendor_Name],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
[APInvHed].[InvoiceNum] as [APInvHed_InvoiceNum],
[APInvHed].[DueDate] as [APInvHed_DueDate],
[APInvDtl].[PartNum] as [APInvDtl_PartNum],
[APInvDtl].[PONum] as [APInvDtl_PONum],
[APInvDtl].[POLine] as [APInvDtl_POLine],
[APInvDtl].[Description] as [APInvDtl_Description],
[APInvDtl].[OurQty] as [APInvDtl_OurQty],
[APInvDtl].[ExtCost] as [APInvDtl_ExtCost],
[TranGLC].[Key1] as [TranGLC_Key1],
[TranGLC].[Key2] as [TranGLC_Key2],
[TranGLC].[Key3] as [TranGLC_Key3],
[TranGLC].[Key4] as [TranGLC_Key4],
[TranGLC].[Key5] as [TranGLC_Key5],
[TranGLC].[GLAccount] as [TranGLC_GLAccount],
[TranGLC].[SegValue1] as [TranGLC_SegValue1],
[TranGLC].[SegValue2] as [TranGLC_SegValue2],
[TranGLC].[SegValue3] as [TranGLC_SegValue3],
[TranGLC].[SegValue4] as [TranGLC_SegValue4],
[TranGLC].[SysGLControlType] as [TranGLC_SysGLControlType],
[TranGLC].[SysGLControlCode] as [TranGLC_SysGLControlCode],
[TranGLC].[InvoiceLine] as [TranGLC_InvoiceLine],
[TranGLC].[VendorNum] as [TranGLC_VendorNum],
[GLAccount].[AccountDesc] as [GLAccount_AccountDesc],
[GLBudgetDtl].[BudgetAmt] as [GLBudgetDtl_BudgetAmt]
from Erp.APInvHed as APInvHed
inner join Erp.APInvDtl as APInvDtl on
APInvHed.Company = APInvDtl.Company
and APInvHed.VendorNum = APInvDtl.VendorNum
and APInvHed.InvoiceNum = APInvDtl.InvoiceNum
inner join Erp.APInvExp as APInvExp on
APInvDtl.Company = APInvExp.Company
and APInvDtl.VendorNum = APInvExp.VendorNum
and APInvDtl.InvoiceNum = APInvExp.InvoiceNum
and APInvDtl.InvoiceLine = APInvExp.InvoiceLine
inner join Erp.TranGLC as TranGLC on
APInvExp.Company = TranGLC.Company
and APInvExp.VendorNum = TranGLC.VendorNum
and APInvExp.InvoiceLine = TranGLC.InvoiceLine
and APInvExp.InvoiceNum = TranGLC.Key2
and ( not TranGLC.SegValue1 in (‘20115’) )

inner join Erp.GLAccount as GLAccount on
TranGLC.Company = GLAccount.Company
and TranGLC.GLAccount = GLAccount.GLAccount
left outer join Erp.GLBudgetDtl as GLBudgetDtl on
GLAccount.GLAccount = GLBudgetDtl.BalanceAcct
and ( GLBudgetDtl.FiscalYear = @Year and GLBudgetDtl.FiscalPeriod = @Month )

inner join Erp.Vendor as Vendor on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
where (APInvHed.FiscalYear = @Year and APInvHed.FiscalPeriod = @Month)