List of POs - Audit Query

/* Formatted on 2009/06/01 13:03 (Formatter Plus v4.8.8) */
/* Formatted on 2009/06/01 14:39 (Formatter Plus v4.8.8) */

begin
mo_global.set_policy_context('S',81);
end;
select distinct segment1 from po_headers_all where  po_headers_all.creation_date BETWEEN TO_DATE ('01-APR-2008')
                                             AND TO_DATE ('30-APR-2009') --order by to_number(segment1)        
minus
                                 
SELECT  distinct  po_headers_all.segment1 AS "PO #",
        po_headers_all.type_lookup_code AS "PO Type",
         TO_CHAR (po_headers_all.creation_date, 'DD-Mon-YYYY') AS "PO Date",
         TO_CHAR (po_headers_all.creation_date, 'Month') AS "Month",
         NVL (po_headers_all.authorization_status, 'Incomplete') status,
         prh.segment1 "PR No", to_char(prh.creation_date,'DD-Mon-YYYY') "PR Date",
         hr_employees_current_v.full_name AS "Buyer Name",
         po_headers_all.agent_id AS "Buyer No", po_lines_all.line_num AS line,
         mcb.segment1 || '.' || mcb.segment2 "PO Item",
         po_lines_all.item_description AS description,
         po_lines_all.quantity AS "Order Quantity",
         po_lines_all.quantity * po_lines_all.unit_price AS "Line Total",
         NVL (pll.shipment_status, 'Open') "Receipt Status",
            gcc1.segment1
         || '-'
         || gcc1.segment2
         || '-'
         || gcc1.segment3
         || '-'
         || gcc1.segment4
         || '-'
         || gcc1.segment5
         || '-'
         || gcc1.segment6 "GL Debit Account",
            gcc2.segment1
         || '-'
         || gcc2.segment2
         || '-'
         || gcc2.segment3
         || '-'
         || gcc2.segment4
         || '-'
         || gcc2.segment5
         || '-'
         || gcc2.segment6 "Supplier Credit Account",
         aia.payment_status_flag,
         (SELECT DECODE (x.match_status_flag,
                         'A', 'Approved'
                        )
            FROM ap.ap_invoice_distributions_all x
           WHERE x.invoice_distribution_id = aid.invoice_distribution_id)
                                                          "Invoice Approved?",
         aia.amount_paid, apt.NAME "Payment Term", rsh.receipt_num "GRN Num",
         to_char(TRUNC (rct.transaction_date),'DD-Mon-YYYY') "GRN Date"  
    FROM po_vendors,
         po_headers_all,
         po_lines_all,
         po_distributions_all,
         hr_employees_current_v,
         mtl_categories_b mcb,
         po_requisition_headers_all prh,
         po_requisition_lines_all prn,
         po_req_distributions_all prd,
         po_line_locations_v pll,
         gl_code_combinations gcc1,
         gl_code_combinations gcc2,
         ap_invoice_distributions_all aid,
         ap_invoices_all aia,
         ap_invoice_payments_all aipa,
         ap_terms apt,
         rcv_transactions rct,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl
   WHERE po_vendors.vendor_id = po_headers_all.vendor_id
     AND po_headers_all.po_header_id = po_lines_all.po_header_id
     AND po_lines_all.po_line_id = po_distributions_all.po_line_id
     AND po_headers_all.agent_id = hr_employees_current_v.employee_id
      --  AND po_headers_all.creation_date BETWEEN TO_DATE ('01-NOV-2008')
       --                                      AND TO_DATE ('30-APR-2009')
     AND mcb.category_id = po_lines_all.category_id
    -- AND po_headers_all.segment1 = 749
     AND prh.requisition_header_id = prn.requisition_header_id
     AND prn.requisition_line_id = prd.requisition_line_id
     AND prd.distribution_id = po_distributions_all.req_distribution_id
     AND pll.po_header_id = po_lines_all.po_header_id
     AND pll.po_line_id = po_lines_all.po_line_id
     AND gcc1.code_combination_id = po_distributions_all.code_combination_id
     AND aid.po_distribution_id(+) = po_distributions_all.po_distribution_id
     AND gcc2.code_combination_id(+) = aia.accts_pay_code_combination_id
     AND aia.invoice_id(+) = aid.invoice_id
     AND aipa.invoice_id(+) = aia.invoice_id
     AND apt.term_id(+) = po_headers_all.terms_id
     AND rct.shipment_line_id = rsl.shipment_line_id(+)
     AND rct.shipment_header_id = rsh.shipment_header_id(+)
     AND rct.po_header_id(+) = po_headers_all.po_header_id
     AND rct.transaction_type(+) = 'DELIVER'
     AND po_lines_all.item_description LIKE '%Sandip%'
GROUP BY po_headers_all.segment1,
         po_lines_all.line_num,
         po_vendors.vendor_name,
         po_headers_all.agent_id,
         hr_employees_current_v.full_name,
         po_headers_all.creation_date,
         po_lines_all.closed_code,
         po_headers_all.type_lookup_code,
         po_lines_all.item_description,
         po_lines_all.unit_meas_lookup_code,
         po_lines_all.quantity,
         po_lines_all.unit_price,
         mcb.segment1,
         mcb.segment2,
         prh.segment1,
         po_headers_all.authorization_status,
         prh.creation_date,
         pll.shipment_status,
            gcc1.segment1
         || '-'
         || gcc1.segment2
         || '-'
         || gcc1.segment3
         || '-'
         || gcc1.segment4
         || '-'
         || gcc1.segment5
         || '-'
         || gcc1.segment6,
            gcc2.segment1
         || '-'
         || gcc2.segment2
         || '-'
         || gcc2.segment3
         || '-'
         || gcc2.segment4
         || '-'
         || gcc2.segment5
         || '-'
         || gcc2.segment6,
        aid.dist_code_combination_id,
         aia.payment_status_flag,
         aid.invoice_distribution_id,
         aia.amount_paid,
         apt.NAME,
         rsh.receipt_num,
         TRUNC (rct.transaction_date)                              
ORDER BY --po_headers_all.creation_date,
         to_number(po_headers_all.segment1),
         po_lines_all.line_num
        
        
select distinct segment1 from po_headers_all