Open POs with no invoices in purchase order

When carrying out UAT testing, there is often a need to include changing a requisition raised in an old, closed purchasing period, so you can test the update process.
In order to do that, you need to be able to identify open POs that have not been fully matched and closed.
This SQL will do that - it finds open POs and counts receipts and invoices raised against the PO.
A simple test is to use open POs with no invoices against them, but it's useful to also check against some that have been receipted and invoiced.

SELECT DISTINCT pha.segment1 po_num
              , pav.agent_name buyer
              , haout2.NAME buyer_hr_org
              , fu2.description
              , fu2.user_name
              , '--------------------'
              , prha.segment1 req_num
              , prha.creation_date
              , '--------------------'
              , (SELECT COUNT(DISTINCT receipt_num)
                   FROM po.rcv_shipment_lines rsl
                      , po.rcv_shipment_headers rsh
                  WHERE rsl.shipment_header_id = rsh.shipment_header_id
                    AND pha.po_header_id = rsl.po_header_id) rx_ct
              -- count of receipts
,               (SELECT   COUNT(DISTINCT aia.invoice_num)
                     FROM ap.ap_invoices_all aia
                        , ap.ap_invoice_distributions_all aida
                        , po.po_distributions_all pda
                    WHERE aia.invoice_id = aida.invoice_id
                      AND aida.po_distribution_id = pda.po_distribution_id
                      AND pda.po_header_id = pha.po_header_id
                 GROUP BY pha.segment1) inv_ct                -- invoice count
              , (SELECT   SUM(pla.unit_price * pla.quantity)
                     FROM po.po_lines_all pla
                    WHERE pla.po_header_id = pha.po_header_id
                 GROUP BY pha.po_header_id) amount
              , (SELECT   SUM(aida.amount)
                     FROM ap.ap_invoices_all aia
                        , ap.ap_invoice_distributions_all aida
                        , po.po_distributions_all pda
                    WHERE aia.invoice_id = aida.invoice_id
                      AND aida.po_distribution_id = pda.po_distribution_id
                      AND pda.po_header_id = pha.po_header_id
                 GROUP BY pha.segment1) matched_amt
              , '--------------------'
              , papf.full_name req_prepaper
              , SUBSTR(haout.NAME, 0, 2) service
              , haout.NAME hr_org
              , fu.description
              , fu.user_name
              , '--------------------'
              , prla.suggested_vendor_name supplier
              , prla.suggested_vendor_location site
           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_people_f papf2
              , hr.per_all_assignments_f paaf
              , hr.per_all_assignments_f paaf2
              , hr.hr_all_organization_units_tl haout
              , hr.hr_all_organization_units_tl haout2
              , apps.po_agents_v pav
              , applsys.fnd_user fu
              , applsys.fnd_user fu2
          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 pha.agent_id = pav.agent_id
            AND haout.organization_id = paaf.organization_id
            AND haout2.organization_id = paaf2.organization_id
            AND paaf.person_id = papf.person_id
            AND paaf2.person_id = papf2.person_id
            AND pav.agent_id = papf2.person_id
            AND papf.person_id = fu.employee_id
            AND pha.created_by = fu2.user_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 SYSDATE BETWEEN papf2.effective_start_date AND NVL(papf2.effective_end_date, SYSDATE + 1)
            AND SYSDATE BETWEEN paaf2.effective_start_date AND NVL(paaf2.effective_end_date, SYSDATE + 1)
            AND paaf.primary_flag = 'Y'
            AND paaf.assignment_type = 'E'
            AND paaf2.primary_flag = 'Y'
            AND paaf2.assignment_type = 'E'
            AND prha.authorization_status = 'APPROVED'
            AND pha.authorization_status = 'APPROVED'
            AND LOWER(pha.closed_code) = 'open'
            AND prha.creation_date BETWEEN '20-MAY-2008' AND '16-JUN-2008'
       ORDER BY prha.segment1 DESC;