This query is used to get open purchase order PO in Oracle Apps R12, it will display complete information of open purchase order which are linked with Requisitions, open purchase order means invoicing or billing is not yet done against purchase order.
SELECT ph.segment1 po_num,
ph.creation_date,
hou.NAME "Operating Unit",
ppx.full_name "Buyer Name",
ph.type_lookup_code "PO Type",
plc.displayed_field "PO Status",
ph.comments, pl.line_num,
plt.order_type_lookup_code "Line Type",
msi.segment1 "Item Code",
pl.item_description,
pl.unit_meas_lookup_code "UOM",
pl.base_unit_price,
pl.unit_price,
pl.quantity,
ood.organization_code "Shipment Org Code",
ood.organization_name "Shipment Org Name",
pv.vendor_name supplier,
pvs.vendor_site_code,
(pl.unit_price * pl.quantity) "Line Amount",
prh.segment1 req_num,
prh.type_lookup_code req_method,
ppx1.full_name "Requisition requestor"
FROM po_headers_all ph,
po_lines_all pl,
po_distributions_all pda,
po_vendors pv,
po_vendor_sites_all pvs,
po_distributions_all pd,
po_req_distributions_all prd,
po_requisition_lines_all prl,
po_requisition_headers_all prh,
hr_operating_units hou,
per_people_x ppx,
mtl_system_items_b msi,
po_line_types_b plt,
org_organization_definitions ood,
per_people_x ppx1,
po_lookup_codes plc
WHERE 1 = 1
AND ph.vendor_id = pv.vendor_id
AND PH.ORG_ID=:P_ORG_ID
AND ph.po_header_id = pl.po_header_id
AND ph.vendor_site_id = pvs.vendor_site_id
AND ph.po_header_id = pd.po_header_id
AND pl.po_line_id = pd.po_line_id
AND pd.req_distribution_id = prd.distribution_id(+)
AND prd.requisition_line_id = prl.requisition_line_id(+)
AND prl.requisition_header_id = prh.requisition_header_id(+)
AND hou.organization_id = ph.org_id
AND ph.agent_id = ppx.person_id
AND pda.po_header_id = ph.po_header_id
AND pda.po_line_id = pl.po_line_id
AND ph.closed_code = 'OPEN'
AND pl.line_type_id = plt.line_type_id
AND ood.organization_id = pda.destination_organization_id
AND ppx1.person_id(+) = prh.preparer_id
AND pda.destination_organization_id = msi.organization_id(+)
AND msi.inventory_item_id = NVL (pl.item_id, msi.inventory_item_id)
AND plc.lookup_type = 'DOCUMENT STATE'
AND plc.lookup_code = ph.closed_code
Table Descriptions
po_headers_all : This table stores PO header related data
po_lines_all : This table stores PO line related data
po_distributions_all : This table stores PO distribution level data
po_vendors : This table stores PO vendor related data
po_vendor_sites_all : This table stores PO vendor’s site related information
po_req_distributions_all : This table stores requisition distribution level information
po_requisition_lines_all : This table stores requisition line level information
po_requisition_headers_all : This table stores requisition header level information
hr_operating_units : This table stores HR operating information
per_people_x : This table stores active employee information
mtl_system_items_b : This table stores item related information
po_line_types_b : This table stores PO line type lookup values
org_organization_definitions : This table stores multiple organization list
po_lookup_codes : This table stores PO related lookup values