We have an issue with one of our vendor reports. The amount spent is not correct. For example, for this quarter, it says we have spent much more than we actually have. For March it is much less. Here is the Query:
select
[Vendor].[VendorNum] as [Vendor_VendorNum],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[Vendor].[Address1] as [Vendor_Address1],
[Vendor].[Address2] as [Vendor_Address2],
[Vendor].[Address3] as [Vendor_Address3],
[Vendor].[City] as [Vendor_City],
[Vendor].[State] as [Vendor_State],
[Vendor].[ZIP] as [Vendor_ZIP],
[Vendor].[PhoneNum] as [Vendor_PhoneNum],
[Vendor].[TermsCode] as [Vendor_TermsCode],
[POHeader].[OrderDate] as [POHeader_OrderDate],
[POHeader].[TotalCharges] as [POHeader_TotalCharges],
[POHeader].[TotalMiscCharges] as [POHeader_TotalMiscCharges],
[POHeader].[TotalOrder] as [POHeader_TotalOrder],
[VendCnt].[ConNum] as [VendCnt_ConNum],
[VendCnt].[Name] as [VendCnt_Name],
[VendCnt].[PhoneNum] as [VendCnt_PhoneNum],
[VendCnt].[EmailAddress] as [VendCnt_EmailAddress],
[Vendor].[PrimPCon] as [Vendor_PrimPCon]
from Erp.Vendor as Vendor
inner join Erp.POHeader as POHeader on
Vendor.Company = POHeader.Company
and Vendor.VendorNum = POHeader.VendorNum
left outer join Erp.VendCnt as VendCnt on
VendCnt.Company = Vendor.Company
and VendCnt.VendorNum = Vendor.VendorNum
and VendCnt.ConNum = Vendor.PrimPCon
The current BAQ is not pulling the correct numbers. When we pull the data from the supplier tracker (links) it shows different summations; by a huge margin on both sides.
That was added by you or someone to filter the returned records. Are you looking to only see the last year’s values? If so, I would put that filter in the sub query. Also, you don’t need two sub queries for this, just a CASE statement for one year vs another.
This was not created by me. I was tasked with fixing it, however. I believe they want the current year, not last year. I am not sure how to edit the SubQuery, as nothing comes up under that tab:
Period to Period, our Received not invoiced is not an issue. The goal is to capture invoiced dollars either associated with matched purchase orders or misc invoice dollars. The exercise is how much do we owe a supplier/vendor according to their invoicing… Sum by supplier invoice date is fine.
a quick query would show the duplicates for you to investigate if this is the issue.
select POHeader.PONum, count(*)
from Erp.Vendor as Vendor
inner join Erp.POHeader as POHeader on
Vendor.Company = POHeader.Company
and Vendor.VendorNum = POHeader.VendorNum
left join Erp.VendCnt as VendCnt on
VendCnt.Company = Vendor.Company
and VendCnt.VendorNum = Vendor.VendorNum
and VendCnt.ConNum = Vendor.PrimPCon
group by POHeader.PONum
having count(*)>1
You cannot paste script into the BAQ designer - you have to design from scratch - attached is an AP Spend by Supplier BAQ that hopefully will help. This BAQ looks at AP invoices rather than PO’s as the PO could be raised for a different value than the final supplier invoice
