purchase order report query

SELECT   poh.segment1 po_no, '( ' || poh.revision_num || ' )' revision_num,
         pvsa.vendor_site_id, pvsa.vendor_site_code,
         poh.creation_date po_date, poh.type_lookup_code po_type,
         poh.authorization_status, pv.vendor_name, pvsa.address_line1,
         pvsa.address_line2, pvsa.address_line3, pvsa.city, pvsa.state,
         pol.vendor_product_num vendor_num, poh.vendor_contact_id,
         DECODE (pol.item_revision,
                 NULL, NULL,
                 '[' || pol.item_revision || ']'
                ) rev,
         msi.segment1 item_code, pol.item_description item_desc,
         DECODE (poh.type_lookup_code,
                 'STANDARD', poll.need_by_date,
                 NULL
                ) need_by_date,
         muom.uom_code primary_uom_code,
                                        ROUND (pol.quantity, 3) quantity,
         ROUND(pol.unit_price,3)unit_price,
         TO_NUMBER (RPAD ((pol.quantity * pol.unit_price), 15)) total_amt,
         poh.start_date, poh.end_date, ap.description payment_term,
         poh.pay_on_code, msi.organization_id, msi.inventory_item_id,
         poh.po_header_id, poh.po_header_id po_hid_for_line, pol.po_line_id,
         poh.fob_lookup_code, '(' || poh.attribute1 || ')' po_type,
         poh.attribute2 insuranceby, pv.segment1 vendor_code,
         (SELECT displayed_field
            FROM po_lookup_codes
           WHERE lookup_code IN (poh.fob_lookup_code)
             AND lookup_type = 'FOB') delivery_terms,
         poh.freight_terms_lookup_code mode_of_shipment,
         poh.ship_via_lookup_code carrier, NULL po_release_id,
         NULL AS release_num, poh.revision_num po_rnum_for_line,
         DECODE (poh.type_lookup_code,
                 'STANDARD', poll.line_location_id,
                 0
                ) line_location_id,poh.ship_to_location_id,poh.bill_to_location_id
    -- for blanket order - line location will be zero..
FROM     po_headers_all poh,
         po_lines_all pol,
         po_line_locations_all poll,
         po_vendors pv,
         po_vendor_sites_all pvsa,
         mtl_system_items_b msi,
         mtl_units_of_measure_tl muom,
         ap_terms ap
   WHERE poh.po_header_id = pol.po_header_id
     AND pol.po_header_id = poll.po_header_id(+)
     AND pol.po_line_id = poll.po_line_id(+)
     AND pol.item_id = msi.inventory_item_id
     AND poh.segment1 = :po_num
     AND poh.org_id = :p_org_id
     AND msi.organization_id = :p_organization_id
     AND poh.vendor_id = pv.vendor_id
     AND pv.vendor_id = pvsa.vendor_id
     AND pvsa.vendor_site_id = poh.vendor_site_id
     AND ap.term_id(+) = poh.terms_id
     AND NVL (pol.closed_flag, 'N') = 'N'
     AND NVL (pol.cancel_flag, 'N') = 'N'
     --AND NVL (:p_release_num, 0) = 0
     AND pol.unit_meas_lookup_code = muom.unit_of_measure
UNION                            /*For  Purchase Orders without Item Details*/
SELECT   poh.segment1 po_no, '( ' || poh.revision_num || ' )' revision_num,
         pvsa.vendor_site_id, pvsa.vendor_site_code,
         poh.creation_date po_date, poh.type_lookup_code po_type,
         poh.authorization_status, pv.vendor_name, pvsa.address_line1,
         pvsa.address_line2, pvsa.address_line3, pvsa.city, pvsa.state,
         pol.vendor_product_num vendor_num, poh.vendor_contact_id,
         DECODE (pol.item_revision,
                 NULL, NULL,
                 '[' || pol.item_revision || ']'
                ) rev,
         NULL item_code, pol.item_description item_desc,
         DECODE (poh.type_lookup_code,
                 'STANDARD', poll.need_by_date,
                 NULL
                ) need_by_date,
         muom.uom_code primary_uom_code, ROUND (pol.quantity, 3) quantity,
         ROUND(pol.unit_price,3)unit_price,
         TO_NUMBER (RPAD ((pol.quantity * pol.unit_price), 15)) total_amt,
         poh.start_date, poh.end_date, ap.description payment_term,
         poh.pay_on_code, NULL organization_id, NULL inventory_item_id,
         poh.po_header_id, poh.po_header_id po_hid_for_line, pol.po_line_id,
         poh.fob_lookup_code, '(' || poh.attribute1 || ')' po_type,
         poh.attribute2 insuranceby, pv.segment1 vendor_code,
         (SELECT displayed_field
            FROM po_lookup_codes
           WHERE lookup_code IN (poh.fob_lookup_code)
             AND lookup_type = 'FOB') delivery_terms,
         poh.freight_terms_lookup_code mode_of_shipment,
         poh.ship_via_lookup_code carrier, NULL po_release_id,
         NULL AS release_num, poh.revision_num po_rnum_for_line,
         DECODE (poh.type_lookup_code,
                 'STANDARD', poll.line_location_id,
                 0
                ) line_location_id,poh.ship_to_location_id,poh.bill_to_location_id
    -- for blanket order - line location will be zero..
FROM     po_headers_all poh,
         po_lines_all pol,
         po_line_locations_all poll,
         po_vendors pv,
         po_vendor_sites_all pvsa,
         mtl_units_of_measure_tl muom,
         ap_terms ap
   WHERE poh.po_header_id = pol.po_header_id
     AND pol.po_header_id = poll.po_header_id(+)
     AND pol.po_line_id = poll.po_line_id(+)
     AND poh.segment1 = :po_num_from
     AND poh.org_id = :p_org_id
     AND poh.vendor_id = pv.vendor_id
     AND pv.vendor_id = pvsa.vendor_id
     AND pvsa.vendor_site_id = poh.vendor_site_id
     AND ap.term_id(+) = poh.terms_id
     AND NVL (pol.closed_flag, 'N') = 'N'
     AND NVL (pol.cancel_flag, 'N') = 'N'
--     AND pol.item_id IS not NULL
     --AND NVL (:p_release_num, 0) = 0
     AND pol.unit_meas_lookup_code = muom.unit_of_measure
 UNION                             -- For Blanket Releases with Item reference.                                        
SELECT   poh.segment1 po_no, '( ' || por.revision_num || ' )' revision_num,
         pvsa.vendor_site_id, pvsa.vendor_site_code,
         poh.creation_date po_date, poh.type_lookup_code po_type,
         por.authorization_status, pv.vendor_name, pvsa.address_line1,
         pvsa.address_line2, pvsa.address_line3, pvsa.city, pvsa.state,
         pol.vendor_product_num vendor_num, poh.vendor_contact_id,
         null AS rev ,
         msi.segment1 item_code, pol.item_description item_desc,
         poll.need_by_date, msi.primary_uom_code,
         ROUND (poll.quantity, 3) quantity,
         ROUND(poll.price_override,3)unit_price,
         TO_NUMBER (RPAD ((poll.quantity * poll.price_override), 15)
                   ) total_amt,
         poh.start_date, poh.end_date, ap.description payment_term,
         poh.pay_on_code, msi.organization_id, msi.inventory_item_id,
         poh.po_header_id, poh.po_header_id po_hid_for_line, poll.po_line_id,
         poh.fob_lookup_code, '(' || poh.attribute1 || ')' po_type,
         poh.attribute2 insuranceby, pv.segment1 vendor_code,
         (SELECT displayed_field
            FROM po_lookup_codes
           WHERE lookup_code IN (poh.fob_lookup_code)
             AND lookup_type = 'FOB') delivery_terms,
         poh.freight_terms_lookup_code mode_of_shipment,
         poh.ship_via_lookup_code carrier, por.po_release_id po_release_id,
         por.release_num, por.revision_num po_rnum_for_line,
         NVL (poll.line_location_id, 0) line_location_id,poh.ship_to_location_id,poh.bill_to_location_id
    FROM po_headers_all poh,
         po_lines_all pol,
         po_line_locations_all poll,
         po_vendors pv,
         po_vendor_sites_all pvsa,
         mtl_system_items_b msi,
         po_distributions_all pod,
         po_releases_all por,
         ap_terms ap
   WHERE poh.po_header_id = pol.po_header_id                            ------
     AND pol.po_line_id = poll.po_line_id(+)
     AND pol.po_header_id = poll.po_header_id(+)
     AND pol.item_id = msi.inventory_item_id
     AND poh.org_id = :p_org_id
     AND msi.organization_id = :p_organization_id
     AND poh.vendor_id = pv.vendor_id
     AND pv.vendor_id = pvsa.vendor_id
     AND pvsa.vendor_site_id = poh.vendor_site_id
     AND ap.term_id(+) = poh.terms_id
     AND NVL (pol.closed_flag, 'N') = 'N'
     AND NVL (pol.cancel_flag, 'N') = 'N'
     AND poh.type_lookup_code = 'BLANKET'
     AND poh.segment1 = :po_num_from
     AND poh.po_header_id = por.po_header_id
     AND poh.po_header_id = por.po_header_id
--     AND por.release_num = :p_release_num
     AND por.po_release_id = poll.po_release_id
     AND pod.po_line_id = poll.po_line_id
     AND pod.po_header_id = poll.po_header_id
     AND pod.line_location_id = poll.line_location_id
UNION           /*For Blanked Purchase Order , Releases without Item Details*/
SELECT   poh.segment1 po_no, '( ' || por.revision_num || ' )' revision_num,
         pvsa.vendor_site_id, pvsa.vendor_site_code,
         poh.creation_date po_date, poh.type_lookup_code po_type,
         por.authorization_status, pv.vendor_name, pvsa.address_line1,
         pvsa.address_line2, pvsa.address_line3, pvsa.city, pvsa.state,
         pol.vendor_product_num vendor_num, poh.vendor_contact_id,
         DECODE (pol.item_revision,
                 NULL, NULL,
                 '[' || pol.item_revision || ']'
                ) rev,
         NULL item_code, pol.item_description item_desc, poll.need_by_date,
         NULL primary_uom_code, ROUND (poll.quantity, 3) quantity,
         ROUND(poll.price_override,3)unit_price,
         TO_NUMBER (RPAD ((poll.quantity * poll.price_override), 15)
                   ) total_amt,
         poh.start_date, poh.end_date, ap.description payment_term,
         poh.pay_on_code, NULL organization_id, NULL inventory_item_id,
         poh.po_header_id, poh.po_header_id po_hid_for_line, pol.po_line_id,
         poh.fob_lookup_code, '(' || poh.attribute1 || ')' po_type,
         poh.attribute2 insuranceby, pv.segment1 vendor_code,
         (SELECT displayed_field
            FROM po_lookup_codes
           WHERE lookup_code IN (poh.fob_lookup_code)
             AND lookup_type = 'FOB') delivery_terms,
         poh.freight_terms_lookup_code mode_of_shipment,
         poh.ship_via_lookup_code carrier, por.po_release_id po_release_id,
         NULL AS release_num, poh.revision_num po_rnum_for_line,
         NVL (poll.line_location_id, 0) line_location_id,poh.ship_to_location_id,poh.bill_to_location_id
    FROM po_headers_all poh,
         po_lines_all pol,
         po_line_locations_all poll,
         po_vendors pv,
         po_vendor_sites_all pvsa,
         po_distributions_all pod,
         po_releases_all por,
         ap_terms ap
   WHERE poh.po_header_id = pol.po_header_id
     AND pol.po_line_id = poll.po_line_id
     AND pol.po_header_id = poll.po_header_id
     AND poh.org_id = :p_org_id
     AND poh.vendor_id = pv.vendor_id
     AND pv.vendor_id = pvsa.vendor_id
     AND pvsa.vendor_site_id = poh.vendor_site_id
     AND ap.term_id(+) = poh.terms_id
     AND NVL (pol.closed_flag, 'N') = 'N'
     AND NVL (pol.cancel_flag, 'N') = 'N'
--     AND poh.type_lookup_code = 'BLANKET'
     AND poh.po_header_id = por.po_header_id
     AND poh.segment1 = :po_num_from
     AND poh.po_header_id = por.po_header_id
     --AND por.release_num = :p_release_num
     AND por.po_release_id = poll.po_release_id
     AND pod.po_line_id = poll.po_line_id
     AND pod.po_header_id = poll.po_header_id
     AND pod.line_location_id = poll.line_location_id
--     AND pol.item_id IS not NULL
ORDER BY 32