Categories
Inventory Project Manufacturing

Item Shortage Checks & Project Manufacturing

We were wondering why we only got shortage warnings some of the time when we were doing PO receipts. A Service Request and many trace files later, Support let us know that were were experiencing intended functionality. The shortage checking functionality considers total on-hand and does not consider projects at all. So, if you have none on hand in your project’s inventory and have a shortage on a job for that project, you will not receive a shortage message during PO receipt if inventory exists in another project.

We logged an enhancement request, but we know how they go…

Categories
Purchasing Technical

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.