I am trying to pull some data for accounting, and need to be able to have Invoice information for all of last year. I have all the fields that are needed, except that I need the GL account each line of every invoice was charge to added. I have read several other issues that have been posted around this type of issue and nothing I have seen there has helped. I think I am close to having it, just not sure what I am doing wrong. Here is what I have:
select
[APInvDtl].[InvoiceNum] as [APInvDtl_InvoiceNum],
[APInvDtl].[InvoiceLine] as [APInvDtl_InvoiceLine],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
[APTran].[TranDate] as [APTran_TranDate],
[CheckHed].[CheckNum] as [CheckHed_CheckNum],
[Vendor].[Name] as [Vendor_Name],
[Vendor].[VendorID] as [Vendor_VendorID],
[APInvDtl].[PONum] as [APInvDtl_PONum],
[APInvDtl].[OurQty] as [APInvDtl_OurQty],
[APInvDtl].[PUM] as [APInvDtl_PUM],
[APInvDtl].[UnitCost] as [APInvDtl_UnitCost],
[APInvDtl].[ExtCost] as [APInvDtl_ExtCost],
[PurTerms].[Description] as [PurTerms_Description],
[APInvDtl].[Description] as [APInvDtl_Description],
[TranGLC].[GLAccount] as [TranGLC_GLAccount],
[APInvDtl].[PartNum] as [APInvDtl_PartNum],
[Vendor].[TaxPayerID] as [Vendor_TaxPayerID],
[APInvDtl].[POLine] as [APInvDtl_POLine],
[APInvHed].[FiscalYear] as [APInvHed_FiscalYear],
[APInvDtl].[PackSlip] as [APInvDtl_PackSlip],
[APInvDtl].[PackLine] as [APInvDtl_PackLine],
[RcvHead].[EntryPerson] as [RcvHead_EntryPerson],
[RcvHead].[ReceiptDate] as [RcvHead_ReceiptDate],
[APTran].[CheckNum] as [APTran_CheckNum]
from Erp.APInvDtl as APInvDtl
inner join Erp.APInvHed as APInvHed on
APInvDtl.Company = APInvHed.Company
and APInvDtl.VendorNum = APInvHed.VendorNum
and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
and ( APInvHed.FiscalYear = 2020 )
left outer join Erp.APTran as APTran on
APInvHed.Company = APTran.Company
and APInvHed.VendorNum = APTran.VendorNum
and APInvHed.InvoiceNum = APTran.InvoiceNum
inner join Erp.Vendor as Vendor on
APTran.Company = Vendor.Company
and APTran.VendorNum = Vendor.VendorNum
inner join Erp.CheckHed as CheckHed on
APTran.Company = CheckHed.Company
and APTran.HeadNum = CheckHed.HeadNum
and APTran.CheckNum = CheckHed.CheckNum
inner join Erp.PurTerms as PurTerms on
APInvHed.Company = PurTerms.Company
and APInvHed.TermsCode = PurTerms.TermsCode
left outer join Erp.RcvHead as RcvHead on
APInvDtl.Company = RcvHead.Company
and APInvDtl.VendorNum = RcvHead.VendorNum
and APInvDtl.PurPoint = RcvHead.PurPoint
and APInvDtl.PackSlip = RcvHead.PackSlip
left outer join Erp.TranGLC as TranGLC on
APInvDtl.Company = TranGLC.Company
and APInvDtl.VendorNum = TranGLC.Key1
and APInvDtl.InvoiceNum = TranGLC.Key2
and APInvDtl.InvoiceLine = TranGLC.Key3
and ( TranGLC.RelatedToFile = 'APInvDtl' )
order by Vendor.
Hopefully someone can see something and can tell me where I am going wrong on this.