Introduction:
This Post illustrates the steps required to fetch the Open Purchase Order Data in Oracle EBS.
Script:
SELECT pv.segment1 vendor_number, pv.vendor_name AS vendor_name,
(SELECT location_code
FROM hr_locations_all
WHERE location_id = pha.ship_to_location_id) po_ship_to_location,
(SELECT location_code
FROM hr_locations_all
WHERE location_id = pha.bill_to_location_id) po_bill_to_location,
DECODE (plla.match_option, ‘P’, ‘PO’, ‘R’, ‘Receipt’, ”) line_status,
UPPER (pha.authorization_status) authorization_status,
TRUNC (pha.creation_date) AS purchase_order_creation_date,
NVL (plla.promised_date,
plla.need_by_date) AS promised_or_need_by_date,
pha.segment1 AS purchase_order_number,
pla.line_num AS purchase_order_line, mcb.segment1 AS product_line,
(SELECT concatenated_segments
FROM mtl_item_categories a,
mtl_category_sets_tl b,
mtl_categories_b_kfv c
WHERE a.category_set_id = b.category_set_id
AND b.category_set_name = ‘Royalty’
AND a.category_id = c.category_id
AND a.inventory_item_id = msi.inventory_item_id
AND a.organization_id = msi.organization_id
AND ROWNUM = 1) royalty_product,
msi.segment1 AS item_number, msi.description AS item_description,
pla.quantity AS purchase_order_quantity,
pla.unit_meas_lookup_code AS purchase_order_uom,
(SELECT uom_code
FROM mtl_units_of_measure mum
WHERE unit_of_measure = pla.unit_meas_lookup_code) uom_code,
plla.quantity_received AS quantity_received,
(pla.quantity – plla.quantity_received) AS difference,
muc.conversion_rate AS uom_conversion_rate,
pla.unit_price AS purchase_order_unit_price,
((pla.quantity – plla.quantity_received) * pla.unit_price
) AS extended_purchase_order_price,
(SELECT location_code
FROM hr_locations_all
WHERE location_id =
pvs.ship_to_location_id)
supplier_ship_to_location,
(SELECT location_code
FROM hr_locations_all
WHERE location_id =
pvs.bill_to_location_id)
supplier_bill_to_location,
pha.closed_code po_closed_code, pla.closed_code line_closed_code,
plla.closed_code loc_closed_code, pha.closed_code po_header_status,
pla.closed_code po_line_status, plla.closed_code po_shipments_status,
pvs.city, pvs.state, pvs.province, msi.inventory_item_id
FROM po_headers_all pha,
po_vendors pv,
po_lines_all pla,
po_line_locations_all plla,
mtl_system_items_b msi,
mtl_uom_conversions muc,
mtl_item_categories_v mic,
mtl_categories_b mcb,
po_vendor_sites_all pvs
WHERE pha.vendor_id = pv.vendor_id
AND pha.po_header_id = pla.po_header_id
AND pha.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pla.item_id = msi.inventory_item_id
AND msi.inventory_item_id = muc.inventory_item_id
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = mic.inventory_item_id
AND mic.category_id = mcb.category_id
AND msi.organization_id = plla.ship_to_organization_id
AND pvs.vendor_site_id = pha.vendor_site_id
AND mic.category_set_name = ‘Inventory’
AND pha.org_id = :p_org_id
AND pla.quantity <> plla.quantity_received
AND (pha.closed_code = ‘OPEN’ OR NVL (pha.closed_code, ‘X’) = ‘X’)
AND (pla.closed_code NOT LIKE ‘%CLOSED’ OR NVL (pla.closed_code, ‘X’) = ‘X’
)
AND (plla.closed_code NOT LIKE ‘%CLOSED’
OR NVL (plla.closed_code, ‘X’) = ‘X’
)
AND pha.creation_date >= ’01-JAN-2018′
Queries
Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions
