Hey all, I’m stumped.
The end goal here is to pull data similar to the Chart Tracker, but for the payments to include the Part Number and Description from the APInvcDtl table. I don’t see these on the Chart Tracker.
I’ve set up a BAQ the links the APInvDtl, TranGLC, and GLJrnDtl to get the majority of the data based on GL Code, and FiscalYear and FiscalPeriod.
My current problem that I am pulling Payment Journals (PJ) correctly, (I believe because they exist in TranGLC) but not the General Journal Entries (GJ).
Attached is the query along with a screenshot of the BAQ.
Am I overthinking this entire thing?
/*
- Disclaimer!!!
- This is not a real query being executed, but a simplified version for general vision.
- Executing it with any other tool may produce a different result.
*/select
[TranGLC].[Key2] as [TranGLC_Key2],
[APInvDtl].[InvoiceLine] as [APInvDtl_InvoiceLine],
[APInvDtl].[PartNum] as [APInvDtl_PartNum],
[APInvDtl].[Description] as [APInvDtl_Description],
[TranGLC].[CreditAmount] as [TranGLC_CreditAmount],
[TranGLC].[DebitAmount] as [TranGLC_DebitAmount],
[TranGLC].[GLAccount] as [TranGLC_GLAccount],
[TranGLC].[GLAcctContext] as [TranGLC_GLAcctContext],
[GLJrnDtl].[FiscalPeriod] as [GLJrnDtl_FiscalPeriod],
[GLJrnDtl].[PostedDate] as [GLJrnDtl_PostedDate],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
[TranGLC].[JournalCode] as [TranGLC_JournalCode]
from Erp.TranGLC as TranGLC
left outer join Erp.APInvDtl as APInvDtl on
TranGLC.Company = APInvDtl.Company
and TranGLC.Key1 = APInvDtl.VendorNum
and TranGLC.Key2 = APInvDtl.InvoiceNum
and TranGLC.Key3 = APInvDtl.InvoiceLine
left outer join Erp.APInvHed as APInvHed on
APInvDtl.Company = APInvHed.Company
and APInvDtl.VendorNum = APInvHed.VendorNum
and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
right outer join Erp.GLJrnDtl as GLJrnDtl on
TranGLC.Company = GLJrnDtl.Company
and TranGLC.FiscalYear = GLJrnDtl.FiscalYear
and TranGLC.JournalCode = GLJrnDtl.JournalCode
and TranGLC.JournalNum = GLJrnDtl.JournalNum
and TranGLC.JournalLine = GLJrnDtl.JournalLine
and TranGLC.CorrAccUID = GLJrnDtl.CorrAccUID
and ( GLJrnDtl.Company = ‘ACYT’ and GLJrnDtl.FiscalPeriod >= @BeginPeriod and GLJrnDtl.FiscalPeriod <= @EndPeriod )full outer join Erp.GLJrnHed as GLJrnHed on
GLJrnHed.Company = TranGLC.Company
and GLJrnHed.BookID = TranGLC.BookID
and GLJrnHed.FiscalYear = TranGLC.FiscalYear
and GLJrnHed.JournalNum = TranGLC.JournalNum
where (TranGLC.Company = ‘ACYT’ and (TranGLC.RelatedToFile = ‘APInvExp’ or TranGLC.RelatedToFile = ‘APInvHed’ ) and (TranGLC.GLAcctContext = ‘Expense’ or TranGLC.GLAcctContext = ‘Payables’ ) and TranGLC.FiscalYear = @FiscalYear and TranGLC.GLAccount = ‘03180|03|80197’)
order by TranGLC.Key2