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