In how many ways we can link the requisition to purchase order in oracle ebs

 In Oracle E-Business Suite (EBS) R12 / R12.2, a Purchase Requisition can be joined to a Purchase Order across 4 primary levels, depending on how the requisition was converted or referenced during the procurement lifecycle

1. Line Location Level (Most Common - AutoCreate)

When a requisition line is processed through the AutoCreate Workbench or Buyer Work Center, Oracle directly links the requisition line to the PO shipment line location.

SQL

SELECT prha.segment1 req_number,
       pha.segment1  po_number
FROM   po_requisition_headers_all prha,
       po_requisition_lines_all   prla,
       po_line_locations_all      pll,
       po_lines_all               pla,
       po_headers_all             pha
WHERE  prha.requisition_header_id = prla.requisition_header_id
  AND  prla.line_location_id      = pll.line_location_id
  AND  pll.po_line_id             = pla.po_line_id
  AND  pla.po_header_id           = pha.po_header_id;

2. Line Level Direct Join
In some EBS versions or custom interfaces, the foreign key po_line_id is populated directly on the requisition line record.
SQL
SELECT prha.segment1 req_number,
       pha.segment1  po_number
FROM   po_requisition_headers_all prha,
       po_requisition_lines_all   prla,
       po_lines_all               pla,
       po_headers_all             pha
WHERE  prha.requisition_header_id = prla.requisition_header_id
  AND  prla.po_line_id            = pla.po_line_id
  AND  pla.po_header_id           = pha.po_header_id;
3. Distribution Level (Split Accounting Lines)
When a requisition distribution is split across multiple charge accounts or projects, the association is recorded at the distribution level (req_distribution_id).
SQL
SELECT prha.segment1 req_number,
       pha.segment1  po_number
FROM   po_requisition_headers_all prha,
       po_requisition_lines_all   prla,
       po_req_distributions_all   prda,
       po_distributions_all       pda,
       po_lines_all               pla,
       po_headers_all             pha
WHERE  prha.requisition_header_id = prla.requisition_header_id
  AND  prla.requisition_line_id   = prda.requisition_line_id
  AND  prda.distribution_id       = pda.req_distribution_id
  AND  pda.po_line_id             = pla.po_line_id
  AND  pla.po_header_id           = pha.po_header_id;
4. Header Reference Level (Imported / Manual PO Mapping)
For POs created via interface APIs (e.g., Purchasing Documents Open Interface) or where manual cross-referencing occurs, the PO distribution table stores the Requisition Header number as text in req_header_reference_num or req_line_reference_num.
SQL
SELECT prha.segment1 req_number,
       pha.segment1  po_number
FROM   po_requisition_headers_all prha,
       po_distributions_all       pda,
       po_lines_all               pla,
       po_headers_all             pha
WHERE  prha.segment1              = pda.req_header_reference_num
  AND  pda.po_line_id             = pla.po_line_id
  AND  pla.po_header_id           = pha.po_header_id;