Hi Everyone!
I would like to create a BAQ to replicate the GL account summary report with the addition of Invoice#|Supplier#|description instead of just description.
I am relatively new to BAQ’s however I have tried all different scenarios with different tables and joins but either i receive BAQ errors or the incorrect data is pulling back.
I believe i have the tables needed but wanted to ask if anyone has done this before and could point me in the right direct as to what the joins should look like?
Also, have you come across duplicate data from the TRANGLC and GLJRN tables and if so, how do you deal with that please?
Here is what I have so far.
[TranGLC].[Company] as [TranGLC_Company],
[APInvHed].[FiscalYear] as [APInvHed_FiscalYear],
[APInvHed].[FiscalPeriod] as [APInvHed_FiscalPeriod],
[APInvHed].[InvoiceNum] as [APInvHed_InvoiceNum],
[APInvDtl].[VendorNum] as [APInvDtl_VendorNum],
[GLJrnHed].[Description] as [GLJrnHed_Description],
[GLJrnDtl].[GLAccount] as [GLJrnDtl_GLAccount],
[GLJrnDtl].[SegValue1] as [GLJrnDtl_SegValue1],
[GLJrnDtl].[SegValue2] as [GLJrnDtl_SegValue2],
[GLJrnDtl].[SegValue3] as [GLJrnDtl_SegValue3],
[GLJrnDtl].[SegValue4] as [GLJrnDtl_SegValue4],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[VendorNum] as [Vendor_VendorNum],
[Vendor].[Name] as [Vendor_Name],
[GLJrnDtl].[JournalCode] as [GLJrnDtl_JournalCode],
[GLJrnDtl].[JournalNum] as [GLJrnDtl_JournalNum],
[GLJrnDtl].[JournalLine] as [GLJrnDtl_JournalLine],
[GLJrnDtl].[DebitAmount] as [GLJrnDtl_DebitAmount],
[GLJrnDtl].[CreditAmount] as [GLJrnDtl_CreditAmount],
[GLJrnDtl].[BalanceAcct] as [GLJrnDtl_BalanceAcct]
from Erp.TranGLC as TranGLC
left outer join Erp.APInvExp as APInvExp on
TranGLC.Company = APInvExp.Company
and TranGLC.VendorNum = APInvExp.VendorNum
and TranGLC.APInvoiceNum = APInvExp.InvoiceNum
and TranGLC.InvoiceLine = APInvExp.InvoiceLine
and TranGLC.RelatedToFile = 'APInvExp'
left outer join Erp.APInvDtl as APInvDtl on
APInvExp.Company = APInvDtl.Company
and APInvExp.VendorNum = APInvDtl.VendorNum
and APInvExp.InvoiceNum = APInvDtl.InvoiceNum
and APInvExp.InvoiceLine = APInvDtl.InvoiceLine
left outer join Erp.APInvHed as APInvHed on
APInvDtl.Company = APInvHed.Company
and APInvDtl.VendorNum = APInvHed.VendorNum
and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
left outer join Erp.Vendor as Vendor on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
full outer join Erp.GLJrnHed as GLJrnHed on
GLJrnHed.Company = TranGLC.Company
and GLJrnHed.BookID = TranGLC.BookID
and GLJrnHed.FiscalYear = TranGLC.FiscalYear
and GLJrnHed.JournalCode = TranGLC.JournalCode
and GLJrnHed.JournalNum = TranGLC.JournalNum
and 'GLJrnHed' = TranGLC.RelatedToFile
and ( GLJrnHed.Company = 'GBTE' and GLJrnHed.FiscalYear = 2024 and GLJrnHed.FiscalPeriod = 5 )
left outer join Erp.GLJrnDtl as GLJrnDtl on
GLJrnHed.Company = GLJrnDtl.Company
and GLJrnHed.FiscalYear = GLJrnDtl.FiscalYear
and GLJrnHed.FiscalPeriod = GLJrnDtl.FiscalPeriod
and GLJrnHed.JournalNum = GLJrnDtl.JournalNum>
TIA - Lisa