Hello Everyone,
I’m trying to create a BAQ to retrieve PO number + Vendor Name from GLJrnDtl for AP Invoices but got stuck with Bad SQL statement and all sort of redundant results.
Appreciate if someone can show me my mistakes.
select
[GLJrnDtl].[Description] as [GLJrnDtl_Description],
[Vendor].[Name] as [Vendor_Name],
[GLJrnDtl].[PostedDate] as [GLJrnDtl_PostedDate],
[GLJrnDtl].[FiscalYear] as [GLJrnDtl_FiscalYear],
[GLJrnDtl].[FiscalPeriod] as [GLJrnDtl_FiscalPeriod],
[GLJrnDtl].[SourceModule] as [GLJrnDtl_SourceModule],
[GLJrnDtl].[APInvoiceNum] as [GLJrnDtl_APInvoiceNum],
[APInvDtl].[PONum] as [APInvDtl_PONum],
[APInvDtl].[InvoiceNum] as [APInvDtl_InvoiceNum],
[GLJrnDtl].[SegValue1] as [GLJrnDtl_SegValue1],
[GLJrnDtl].[GLAccount] as [GLJrnDtl_GLAccount],
[GLJrnDtl].[BookDebitAmount] as [GLJrnDtl_BookDebitAmount],
[GLJrnDtl].[BookCreditAmount] as [GLJrnDtl_BookCreditAmount],
[GLJrnDtl].[DebitAmount] as [GLJrnDtl_DebitAmount],
[GLJrnDtl].[CreditAmount] as [GLJrnDtl_CreditAmount]
from Erp.GLJrnDtl as GLJrnDtl
inner join Erp.TranGLC as TranGLC on
GLJrnDtl.JournalCode = TranGLC.JournalCode
and GLJrnDtl.JournalNum = TranGLC.JournalNum
and GLJrnDtl.JournalLine = TranGLC.JournalLine
and GLJrnDtl.FiscalYear = TranGLC.FiscalYear
and GLJrnDtl.CorrAccUID = TranGLC.CorrAccUID
inner join Erp.APInvDtl as APInvDtl on
TranGLC.Company = APInvDtl.Company
and TranGLC.Key2 = APInvDtl.InvoiceNum
and TranGLC.Key3 = APInvDtl.InvoiceLine
and TranGLC.Key1 = APInvDtl.VendorNum
inner join Erp.Vendor as Vendor on
APInvDtl.Company = Vendor.Company
and APInvDtl.VendorNum = Vendor.VendorNum
where (GLJrnDtl.PostedDate >= ‘01/01/2022’ and GLJrnDtl.FiscalYear = 2022 and GLJrnDtl.SegValue1 = ‘51000’)
Thanks!