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
