Query to get the Purchase Requisition and Purchase Order Details
SELECT DISTINCT prha.creation_date req_creation_date
, prha.segment1 req_no
, prha.authorization_status
, prha.apps_source_code /* POR means req came from iProcurment */
, papf2.full_name req_prepaper
, prla.line_num req_line_number
, prla.item_description
, prla.need_by_date
, pha.segment1 po_number
FROM po.po_requisition_headers_all prha
, po.po_requisition_lines_all prla
, po.po_line_locations_all plla
, hr.per_all_people_f papf2
, po.po_headers_all pha
, po.po_lines_all pla
WHERE prha.preparer_id = papf2.person_id
AND prha.requisition_header_id = prla.requisition_header_id
AND prla.line_location_id = plla.line_location_id(+)
AND plla.po_line_id = pla.po_line_id(+)
AND pla.po_header_id = pha.po_header_id(+)
AND pha.segment1 = 'xxxxxx' /* Change this value to PO# per your needs*/
AND prha.segment1 = 'xxxxxx' /* Change this value to Requisition# per your needs*/
AND prha.authorization_status = 'APPROVED'
AND prla.closed_code IS NULL
AND ( prla.cancel_flag = 'N'
OR prla.cancel_flag IS NULL)
ORDER BY 1 DESC;
POR in apps_source_code of Purchase Requisition Header denotes it is a iProc Requisition.
***Please leave a comment**
