BAQ link APInvDtl to TranGLC

[rirish (Ryan Irish)]

Hello,

I’ve been tasked with creating an AP report that displays the GL code associated with AP invoices.

The way Epicor handles the transglc table is something of a nightmare, but I think I have the correct relationships in terms of key1, 2, and 3.

I know I’m supposed to filter on ‘RelatedToFile’, (does anyone know the filter value for APInvDtl?) but I don’t think that matters yet as I’m getting an error when I test.

There are plenty of questions and complaints here related to TranGLC, but I haven’t found anything that helps yet.

[MLamkin (Marjorie Lamkin)]

and

Those linked up automatically when I put TranGLC first, then added APInvDtl, then I just moved it up in the Table List.

[Chad_Smith]

Here’s a at least a good starting place on TranGLC.

KB0028094: Adding GL Account to a BAQ

[Hally (Simon Hall)]

This might be use to stick in your toolbox as well

Efficient Joining Between the TranGLC and PartTran Tables

KB0039633

[rirish (Ryan Irish)]

Thanks for the help.

I figured out that I needed to add the APInvExp table and link TranGLC to that, and then also change the variable type in my table join.

I’m closer now, but I’m still getting two rows returned for each invoice line…one record type ‘A’ and one record type ‘R’. Once I figure out which one to get rid of I might have this solved.

[Hally (Simon Hall)]

A = Account Type
R = Reference Type.

You can normally find this sort of information in either the field help or the data dictionary.

[imak (Ian Mak)]

Was given a task to do the same. Came across 4x different posts on EpiUsers but no one really posted any concrete results.

Just wanted to just leave this BAQ here for anyone needing this in the future. You can add/remove display fields as necessary.

Ian_JournalTrackerDtl.baq (11.9 KB)

select
[TranGLC].[Company] as [TranGLC_Company],
[TranGLC].[RelatedToFile] as [TranGLC_RelatedToFile],
[Vendor].[Name] as [Vendor_Name],
[GLJrnDtl].[PostedDate] as [GLJrnDtl_PostedDate],
[APInvHed].[ApplyDate] as [APInvHed_ApplyDate],
[GLJrnDtl].[APInvoiceNum] as [GLJrnDtl_APInvoiceNum],
[TranGLC].[Key3] as [TranGLC_Key3],
[APInvDtl].[Description] as [APInvDtl_Description],
[TranGLC].[JournalCode] as [TranGLC_JournalCode],
[TranGLC].[JournalNum] as [TranGLC_JournalNum],
[TranGLC].[JournalLine] as [TranGLC_JournalLine],
[TranGLC].[BookCreditAmount] as [TranGLC_BookCreditAmount],
[TranGLC].[BookDebitAmount] as [TranGLC_BookDebitAmount],
[GLAccount].[SegValue1] as [GLAccount_SegValue1],
[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]

from Erp.TranGLC as [TranGLC]
inner join Erp.GLJrnDtl as [GLJrnDtl] on
TranGLC.Company = GLJrnDtl.Company
and TranGLC.FiscalCalendarID = GLJrnDtl.FiscalCalendarID
and TranGLC.FiscalYear = GLJrnDtl.FiscalYear
and TranGLC.JournalCode = GLJrnDtl.JournalCode
and TranGLC.JournalNum = GLJrnDtl.JournalNum
and TranGLC.JournalLine = GLJrnDtl.JournalLine
inner join Erp.Vendor as [Vendor] on
GLJrnDtl.Company = Vendor.Company
and GLJrnDtl.VendorNum = Vendor.VendorNum
left outer join Erp.APInvDtl as [APInvDtl] on
TranGLC.Company = APInvDtl.Company
and TranGLC.Key1 = CONVERT( nvarchar(50), APInvDtl.VendorNum)
and TranGLC.Key2 = CONVERT( nvarchar(50), APInvDtl.InvoiceNum)
and TranGLC.Key3 = CONVERT( nvarchar(50), APInvDtl.InvoiceLine)
left outer join Erp.APInvHed as [APInvHed] on
APInvDtl.Company = APInvHed.Company
and APInvDtl.VendorNum = APInvHed.VendorNum
and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
inner join Erp.GLAccount as [GLAccount] on
GLAccount.Company = TranGLC.Company
and GLAccount.SegValue1 = TranGLC.SegValue1
order by TranGLC.JournalNum Desc

Combined the knowledge from these threads to achieve the result:

BAQ for GL Account Summary - Kinetic ERP - Epicor User Help Forum

Invoice data from a Journal for a report - Epicor ERP 10 - Epicor User Help Forum

BAQ (simple?) Linking GLJrnDtl to InvoiceDtl - Epicor ERP 10 - Epicor User Help Forum

(and this thread)
BAQ link APInvDtl to TranGLC - Epicor ERP 10 - Epicor User Help Forum