Query: Link Between PO and Requisition

This Query is a link between PO Requisition and Purchase Order. It will the list of all those PO's which are created via Requisition.

 SELECT prh.segment1 pr_number,  
  ph.segment1 po_number,  
  ph.comments po_comments,  
  ph.ship_to_location_id,  
  ph.bill_to_location_id,  
  ph.terms_id,  
  ph.currency_code,  
  ph.revision_num,  
  he.full_name buyer,  
  ph.type_lookup_code po_document_type,  
  prh.description pr_description,  
  prh.authorization_status pr_status,  
  prh.type_lookup_code,  
  prl.line_num req_line_num,  
  pl.line_num,  
  pll.shipment_num,  
  msi.segment1 item_name,  
  mc.segment1 || '.' || mc.segment2 category_code,  
  prl.destination_context,  
  prl.suggested_vendor_name,  
  oodt.organization_code receving_org,  
  prl.need_by_date,  
  gcct.segment1  
  || '.'  
  || gcct.segment2  
  || '.'  
  || gcct.segment3  
  || '.'  
  || gcct.segment4  
  || '.'  
  || gcct.segment5  
  || '.'  
  || gcct.segment6  
  || '.'  
  || gcct.segment7  
  || '.'  
  || gcct.segment8 gl_code_combination,  
  prl.quantity req_qty,  
  prl.unit_price,  
  pl.unit_price,  
  pl.list_price_per_unit,  
  pl.unit_meas_lookup_code uom,  
  pd.quantity_ordered po_qty,  
  pd.quantity_delivered,  
  pd.quantity_billed,  
  pd.quantity_cancelled,  
  pd.amount_billed,  
  pd.amount_delivered,  
  pd.amount_cancelled,  
  pll.taxable_flag,  
  ph.closed_code po_status,  
  pl.closed_code line_status,  
  pll.closed_code line_location_status,  
  prl.destination_context  
  FROM po_requisition_headers prh,  
  po_requisition_lines prl,  
  po_req_distributions prd,  
  gl_code_combinations gcct,  
  po_distributions pd,  
  po_line_locations pll,  
  po_headers ph,  
  po_lines pl,  
  mtl_system_items_b msi,  
  org_organization_definitions oodt,  
  mtl_categories mc,  
  hr_employees he  
  WHERE 1 = 1  
  AND prh.requisition_header_id = prl.requisition_header_id  
  AND prd.requisition_line_id = prl.requisition_line_id  
  AND pd.req_distribution_id(+) = prd.distribution_id  
  AND ph.po_header_id = pl.po_header_id  
  AND pl.po_line_id = pll.po_line_id  
  AND pl.po_header_id = pll.po_header_id  
  AND pd.po_header_id = ph.po_header_id  
  AND pd.po_line_id = pl.po_line_id  
  AND pd.line_location_id = pll.line_location_id  
  AND msi.inventory_item_id(+) = prl.item_id  
  AND msi.organization_id(+) = prl.destination_organization_id  
  AND oodt.organization_id = prl.destination_organization_id  
  AND gcct.code_combination_id = prd.code_combination_id  
  AND prl.category_id = mc.category_id  
  AND prh.segment1 = NVL (:requisition_name, prh.segment1)  
  AND he.employee_id = ph.agent_id  
  AND ph.segment1 = NVL (:po_number, ph.segment1)  
  AND prh.type_lookup_code ='PURCHASE'  
  AND ph.authorization_status =  
  DECODE (:po_authorization_status,  
  'A', 'APPROVED',  
  'R', 'REQUIRES REAPPROVAL',  
  'P', 'IN PROCESS',  
  'I', 'INCOMPLETE',  
  'X', 'REJECTED',  
  ph.authorization_status  
  )  
  AND prh.authorization_status =  
  DECODE (:pr_status,  
  'A', 'APPROVED',  
  'P', 'IN PROCESS',  
  'I', 'INCOMPLETE',  
  prh.authorization_status  
  )  
  AND prl.destination_context =  
  DECODE (:req_line_type,  
  'I', 'INVENTORY',  
  'E', 'EXPENSE',  
  prl.destination_context  
  )  
  AND NVL (ph.closed_code, 'OPEN') =  
  DECODE (:po_status,  
  'O', 'OPEN',  
  'C', 'CLOSED',  
  'F', 'FINALLY CLOSED',  
  NVL (ph.closed_code, 'OPEN')  
  )  
  AND NVL (pl.closed_code, 'OPEN') =  
  DECODE (:line_status,  
  'O', 'OPEN',  
  'C', 'CLOSED',  
  'F', 'FINALLY CLOSED',  
  NVL (pl.closed_code, 'OPEN')  
  )  
  AND NVL (pll.closed_code, 'OPEN') =  
  DECODE (:line_location_status,  
  'O', 'OPEN',  
  'C', 'CLOSED',  
  'F', 'FINALLY CLOSED',  
  NVL (pll.closed_code, 'OPEN')  
  )  
  AND prh.creation_date > SYSDATE - NVL (:months, 12) * 30  
 ORDER BY prh.segment1, prl.line_num, pl.line_num, pll.shipment_num