Oracle EBS – Query to get Open Purchase Order Details
SELECT org_code, org, operating_unit, buyer, supplier, po_number,
line_num line_number, item_code item,
item_description description, quantity, unit_price price,
need_by_date, shipment_line_num, shipment_qty,
shipment_needby_date, shipment_received, open_qty,
shipment_cancelled, shipment_billed, period_name, po_date,
SYSDATE extract_run_date, po_status
FROM (
SELECT ph.po_header_id, hou.NAME operating_unit,
ood.organization_code org_code, ood.organization_name org,
ph.closed_code, pl.closed_code ln_closedode,
ph.type_lookup_code source_type, ppx1.full_name buyer,
ph.segment1 po_number, ph.creation_date po_date,
aps.vendor_name supplier, ph.authorization_status po_status,
pl.line_num, pl.line_num shipment_line_num, msi.segment1 item_code,
pl.item_description item_description, pl.quantity quantity,
pl.unit_meas_lookup_code uom, poll.need_by_date,
poll.need_by_date shipment_needby_date, pl.unit_price unit_price,
(poll.quantity * poll.price_override) amount,
NVL (poll.quantity, 0) – NVL (poll.quantity_shipped, 0) open_qty,
poll.quantity shipment_qty, poll.quantity_received shipment_received,
poll.quantity_billed shipment_billed,
poll.quantity_cancelled shipment_cancelled,
TO_CHAR (ph.creation_date, ‘MON-YY’) period_name
FROM po_headers_all ph,
po_lines_all pl,
po_line_locations_all poll,
ap_suppliers aps,
po_distributions_all pd,
hr_operating_units hou,
mtl_system_items_b msi,
org_organization_definitions ood,
per_people_x ppx1
WHERE 1 = 1
AND ph.po_header_id = pl.po_header_id
AND ph.vendor_id = aps.vendor_id
AND pl.po_line_id = poll.po_line_id
AND pl.po_line_id = pd.po_line_id
AND ph.po_header_id = pd.po_header_id
AND poll.line_location_id = pd.line_location_id
AND ph.org_id = hou.organization_id
AND msi.inventory_item_id(+) = pl.item_id
AND msi.organization_id(+) = pd.destination_organization_id
AND pd.destination_organization_id = ood.organization_id
AND ppx1.person_id(+) = ph.agent_id
AND TO_CHAR (ph.creation_date, ‘MON-YY’) = TO_CHAR (SYSDATE, ‘MON-YY’)
AND ph.authorization_status = ‘APPROVED’
AND NVL (ph.closed_code, ‘OPEN’) = ‘OPEN’
AND NVL (pl.closed_code, ‘OPEN’) = ‘OPEN’
AND ph.cancel_flag ‘Y’
AND pl.cancel_flag ‘Y’
AND (pd.quantity_ordered – NVL (pd.quantity_cancelled, 0))
– NVL (poll.quantity_received, 0) 0
)
