Query to find the Purchase Requisition and Purchase Order Details

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**