Want to find out if your buyers are overriding Program/Blanket Agreement Pricing?

My current client has negotiated pricing for the vast majority of their purchases. On entering an Oracle PO, the negotiated pricing defaults as soon as the item is entered on the PO. The buyer does, however, have the option of overriding this price.

We were asked to develop a query that the internal auditors could use to review these price changes. We found a column in the po_lines_all called base_unit price, which is populated with the first price that is entered/defaulted for a PO line. The unit_price is the price once the PO is approved. By comparing base_unit_price and unit_price, we were able to provide what the auditors wanted.

Here’s the query:

SELECT poh.segment1 “PO #”,
pol.line_num “Line #”,
pov.vendor_name “Vendor”,
papf.full_name “Buyer”,
msi.segment1 “Item”,
msi.description “Description”,
pol.unit_price “Unit Price”,
pol.base_unit_price “Base Unit Price”,
(pol.unit_price-pol.base_unit_price) “Delta”,
poh_a.segment1 “Blanket Agreement”,
pol.creation_date “Creation Date”,
fu_b.user_name “Created By”,
pol.last_update_date “Last Update Date”,
fu_a.user_name “Last Updated By”
FROM inv.mtl_system_items_b msi,
po.po_headers_all poh,
po.po_lines_all pol,
apps.po_vendors pov,
apps.per_all_people_f papf,
apps.fnd_user fu_a,
apps.fnd_user fu_b,
po.po_headers_all poh_a
WHERE msi.organization_id   = 83
AND msi.inventory_item_id   = pol.item_id
AND pol.base_unit_price    != pol.unit_price
AND pol.base_unit_price    != 0
AND poh.po_header_id        = pol.po_header_id
AND poh.vendor_id           = pov.vendor_id
AND poh.agent_id            = papf.person_id
AND fu_a.user_id            = pol.last_updated_by
AND fu_b.user_id            = pol.created_by
AND pol.from_header_id      = poh_a.po_header_id(+)
AND papf.effective_end_date > sysdate
ORDER BY 1,
2

In our case, 83 is the organization_id of the Operating Unit. You would have to substitute your own value.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>