SQL Scripts – Purchase Order Line Item Discrepancy
The following script will compare all open purchase order line item pricing with the standard cost of the item on the Item Card in GP . This is useful to monitor if people are purchasing inventory for more than what you are selling it for.
For example: I have a widget setup with a cost of $100.00 and I am selling it based on the cost of $100.00 but my purchasing agent has just purchased 10 more widgets for $110.00, this will show you that you have a pricing discrepancy.
I have this script setup as an alert so that I am notified when this happens instantly.
The script does not use any special views like most of my scripts do. I wrote this one a while ago, which is also the reason for the weird looking joins. The syntax is correct but a different way than I write queries now.
***SQL Script***
select a.ponumber,
c.postatus,
a.qtyorder,
a.itemnmbr,
a.itemdesc,
a.locncode,
a.vendorid,
c.vendname,
a.unitcost,
b.stndcost,
b.currcost,
c.user2ent,
c.buyerid,
c.docdate
from ( select * from pop10110 ) A
left join ( select * from iv00101 ) B on a.itemnmbr = b.itemnmbr
left join ( select * from pop10100 ) C on a.ponumber = c.ponumber
where ( ( a.unitcost > b.stndcost )
or ( a.unitcost = 0 ) )
and ( a.ponumber IN (
select ponumber
from pop10100
where (/*canceled, closed, or new*/ postatus <> ‘5’
and postatus <> ‘6’
and postatus <> ‘1’ ) ) )
and a.itemnmbr not like ‘*%’
order by c.buyerid,
c.user2ent,
c.docdate,
a.ponumber asc
