SQL Scripts–Vendor Invoice Pricing Discrepancy
The following SQL Script will show you all Payables invoices that have been entered into your system where the price your vendor is charging does not match the price of your purchase order.
We use this, in conjunction with Task Centre, to alert us when a vendor has charged us more than what our purchase order was submitted for. Very valuable when you have a payables clerk processing 100’s of invoices a day (it is easy for them to make a mistake).
This script has options for total amount of discrepancy. This allows you to “tweak” the script to not show line items that are off a couple of pennies (or whatever tolerance you wish).
We actually use $5, so that is set as default in the script. To change, replace the value in line 2 (SET @dollar_amount=5), to whatever dollar value you want. DO NOT INCLUDE THE DECIMALS.
The script using the following views:
- Purchase Order (tspvPurchaseOrder)
DECLARE @dollar_amount INT
SET @dollar_amount = 5
select [Invoice_Date] = c.receiptdate,
[PO_Number] = a.po_number,
[POP_Type] = case when c.poptype = ’1′ then ‘Shipment’
when c.poptype = ’2′ then ‘ Invoice’
when c.poptype = ’3′ then ‘Shipment/Invoice’
[PO_Date] = po_date,
[Vendor_ID] = vendor_id,
[Vendor_Name] = vendor_name,
[Item_Number] = d.itemnmbr,
[Item_Desc] = d.itemdesc,
[PO_Qty] = d.qtyorder,
[Inv Unit Cost] = b.unitcost,
[PO Unit Cost] = d.unitcost,
[Created_By] = c.user2ent
from tspvpurchaseorder a
join pop30310 b on a.po_number = b.ponumber
join pop30300 c on b.poprctnm = c.poprctnm
join pop10110 d on a.po_number = d.ponumber
AND b.itemnmbr = d.itemnmbr
AND b.itemdesc = d.itemdesc
where ( ( b.unitcost – d.unitcost ) > @dollar_amount )
order by po_number desc