A/P Report to Check PO Pricing Against Past Pricing to Catch MFG Pricing Errors

A/P Report to Check PO Pricing Against Past Pricing to Catch MFG Pricing Errors

A few things about this query.

– it is used to catch pricing errors on the vendor side by comparing past pricing against current pricing on incoming po’s

Variables used in this query:

– XXX, replace the XXX with a beginning date range
– YYY, replace the YYY with the ending date range

select distinct (b.itemnmbr) [Item Number],a.ponumber [PO Number],
case
when a.postatus = ‘1’ then ‘New’
when a.postatus = ‘2’ then ‘Released’
when a.postatus = ‘3’ then ‘Change Order’
when a.postatus = ‘4’ then ‘Received’
when a.postatus = ‘5’ then ‘Closed’
when a.postatus = ‘6’ then ‘Cancelled’
end [Status],
case
when a.potype = ‘1’ then ‘Standard’
when a.potype = ‘2’ then ‘Drop Ship’
when a.potype = ‘3’ then ‘Blanket’
end [PO Type],
isnull((select x.currcost from iv00101 x where x.ITEMNMBR = b.itemnmbr), 0) [Current Cost],
convert(decimal(10,2),b.unitcost) [PO Cost],
(select max(x.unitcost) from pop10310 x where x.ITEMnmbr = b.ITEMNMBR) [S/I Cost],
isnull((select x.LISTPRCE from iv00105 x where x.itemnmbr = b.itemnmbr),0) [List Price],
a.SUBTOTAL [PO Total], b.QTYORDER [QTY Invoiced], b.VNDITDSC [Item Description], b.EXTDCOST [Extended Cost], a.VENDORID [Vendor ID], a.VENDNAME [Vendor Name],
d.receiptdate [Receipt Date],
d.vnddocnm [Vendor Doc Number],
c.poprctnm [POP Receipt Number],
d.bachnumb [Batch ID]
from pop10100 a
left outer join pop10110 b
on b.ponumber = a.PONUMBER
left outer join pop10310 c
on c.ponumber = a.PONUMBER
left outer join pop10300 d
on d.POPRCTNM = c.POPRCTNM
left outer join pop10500 e
on e.ponumber = a.ponumber
where d.receiptdate between XXX and YYY