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.
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
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13