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