Query to find Purchase Order from a Requisition Using SQL
Introduction / Issue
In Oracle E-Business Suite (EBS), the Procure-to-Pay (P2P) process begins with creating a Purchase Requisition and continues until a Purchase Order (PO) is generated. During production support, technical consultants often receive requests from users asking whether a Purchase Order has been created for a particular requisition.
Checking this information manually through the Oracle EBS application can be time-consuming, especially when dealing with many transactions. This blog explains how to trace a Purchase Order from a Requisition using SQL queries.
Why We Need to Do It / Cause of the Issue
During the procurement process, users may encounter situations such as:
- A Purchase Requisition is approved, but the corresponding Purchase Order cannot be located.
- Users want to verify whether a Purchase Order has been created for a specific requisition.
- Reports display only the requisition number, while the purchasing team needs the related Purchase Order number.
- Technical consultants need to validate P2P data during production support or custom report development.
If the relationship between the Requisition and Purchase Order is not identified quickly, it can delay procurement activities and increase support effort.
How Do We Solve It:
Step 1: Verify the Requisition Details
Run the following query to check whether the requisition exists and to verify its approval status.
SELECT segment1 Requisition_Number,
authorization_status,
creation_date
FROM po_requisition_headers_all
WHERE segment1 = ‘&Requisition_Number’;
If the requisition status is APPROVED, it is eligible for Purchase Order creation.
Step 2: Verify the Purchase Order Created from the Requisition
Run the following query to identify the Purchase Order linked to the requisition.
SELECT prh.segment1 Requisition_Number, pha.segment1 Purchase_Order
FROM
po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
po_distributions_all pod,
po_headers_all pha
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id AND
prd.distribution_id = pod.req_distribution_id AND
pod.po_header_id = pha.po_header_id AND
prh.segment1 = ‘&Requisition_Number’;
This query returns the Purchase Order number associated with the specified requisition.
Step 3: Retrieve Purchase Order Details
Once the Purchase Order number is identified, retrieve additional information such as supplier name and approval status.
SELECT pha.segment1 Purchase_Order,
aps.vendor_name,
pha.authorization_status
FROM po_headers_all pha,
ap_suppliers aps
WHERE pha.vendor_id = aps.vendor_id
AND pha.segment1 = ‘&PO_Number’;
Conclusion
Finding a Purchase Order from a Purchase Requisition is a common requirement in Oracle EBS production support. By using a few SQL queries, technical consultants can quickly determine whether a Purchase Order has been created, verify its approval status, and retrieve supplier details.
This approach reduces troubleshooting time, improves support efficiency, and helps procurement teams track transactions accurately. Understanding the relationship between the key purchasing tables—PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL, PO_REQ_DISTRIBUTIONS_ALL, PO_DISTRIBUTIONS_ALL, and PO_HEADERS_ALL—is an essential skill for Oracle EBS technical consultants working on reports, customizations, and production support.
