Open Purchase Requisition in PO

This can be used to identify requisitions which need to be converted to Purchase Orders

SELECT   prha.segment1 req_num

       , TRIM(TO_CHAR((prla.quantity * prla.unit_price), '999,999,999.99')) line_value

       , prla.line_num

       , prha.creation_date

       , prla.creation_date line_creation_date

       , prla.vendor_id

       , prla.vendor_site_id

       , NVL2(

            (SELECT DISTINCT pha2.vendor_id

                        FROM po.po_headers_all pha2

                       WHERE pha2.type_lookup_code = 'CONTRACT'

                         AND pha2.authorization_status = 'APPROVED'

                         AND pha2.creation_date >= '01-APR-2009'

                         AND pha2.vendor_id = prla.vendor_id

                         AND pha2.vendor_site_id = prla.vendor_site_id

                         AND pha.org_id = prha.org_id)

          , 'YES'

          , ''

         ) cpa_exists

       , (SELECT MIN(pah.creation_date)

            FROM po.po_action_history pah

           WHERE object_id = prha.requisition_header_id

             AND pah.object_type_code = 'REQUISITION'

             AND pah.action_code = 'SUBMIT') submitted_for_approval

       , (SELECT MAX(pah.creation_date)

            FROM po.po_action_history pah

           WHERE object_id = prha.requisition_header_id

             AND pah.object_type_code = 'REQUISITION'

             AND pah.action_code = 'APPROVE') last_approved

       , mcb.segment1 || '.' || mcb.segment2 purchase_category

       , pct.NAME commodity

       , CASE

            WHEN pct.NAME IS NOT NULL

               THEN (SELECT papf2.full_name

                       FROM po.po_commodities_tl pct

                          , po.po_commodity_categories pcc

                          , po.po_commodity_grants pcg

                          , hr.per_all_people_f papf2

                      WHERE pct.commodity_id = pcg.commodity_id

                        AND pcc.category_id(+) = mcb.category_id

                        AND pct.commodity_id(+) = pcc.commodity_id

                        AND papf2.person_id = pcg.person_id

                        AND SYSDATE BETWEEN papf2.effective_start_date AND papf2.effective_end_date)

         END commodity_buyer

       , CASE

            -- ----------------------------------------------- PUNCHOUT

         WHEN prla.catalog_type = 'EXTERNAL'

         AND prla.catalog_source = 'EXTERNAL'

         AND prla.source_type_code = 'VENDOR'

               THEN 'PUNCHOUT'

            -- -----------------------------------------------INTERNAL CATALOGUE

         WHEN prla.catalog_type = 'CATALOG'

         AND prla.catalog_source = 'INTERNAL'

         AND prla.source_type_code = 'VENDOR'

         AND prla.item_id IS NULL

               THEN 'LOCAL_CATALOGUE'

            -- ----------------------------------------------- NON CATALOGUE:

         WHEN prla.catalog_type = 'NONCATALOG'

         AND prla.catalog_source = 'INTERNAL'

         AND prla.source_type_code = 'VENDOR'

               THEN 'NONCAT'

            ELSE 'Other'

         END order_type

       , prla.suggested_vendor_product_code catalogue_code

       , prha.emergency_po_num

       , TO_CHAR(prla.need_by_date, 'DD-MON-RRRR') need_by_date

       , prla.item_description

       , hlat.description deliver_to

       , prla.quantity

       , prla.unit_price

--       , TRIM(TO_CHAR((prla.quantity * prla.unit_price), '999,999,999.99')) line_value

       , prla.unit_meas_lookup_code uom

       , prla.note_to_agent note_to_buyer

       , prla.note_to_vendor note_to_supplier

       , papf.full_name req_prepaper

       , papf.email_address req_email

       , SUBSTR(haout.NAME, 0, 2) service

       , haout.NAME hr_org

       , prla.suggested_vendor_name supplier

       , prla.suggested_vendor_location site

       , prha.description req_description

    FROM po.po_requisition_headers_all prha

       , po.po_requisition_lines_all prla

       , po.po_line_locations_all plla

       , po.po_lines_all pla

       , po.po_headers_all pha

       , hr.per_all_people_f papf

       , hr.per_all_assignments_f paaf

       , hr.hr_all_organization_units_tl haout

       , hr.hr_locations_all_tl hlat

       , inv.mtl_categories_b mcb

       , po.po_commodities_tl pct

       , po.po_commodity_categories pcc

   WHERE prha.requisition_header_id = prla.requisition_header_id

     AND plla.line_location_id(+) = prla.line_location_id

     AND pla.po_line_id(+) = plla.po_line_id

     AND prha.preparer_id = papf.person_id

     AND pha.po_header_id(+) = plla.po_header_id

     AND prla.deliver_to_location_id = hlat.location_id

     AND haout.organization_id = paaf.organization_id

     AND paaf.person_id = papf.person_id

     AND mcb.category_id = prla.category_id

     AND pcc.category_id(+) = mcb.category_id

     AND pct.commodity_id(+) = pcc.commodity_id

     AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

     AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date

     AND (prla.cancel_flag IS NULL OR prla.cancel_flag = 'N')

     AND prha.authorization_status = 'APPROVED'

     AND prha.creation_date >= '01-JAN-2012'

     AND paaf.assignment_type = 'E'

     AND paaf.primary_flag = 'Y'

     AND prla.closed_date IS NULL

     AND pha.segment1 IS NULL

ORDER BY prha.segment1 DESC

       , prla.line_num;