/* Formatted on 8/22/2021 12:02:13 PM (QP5 v5.136.908.31019) */
  SELECT DISTINCT
         pha.segment1 "PO NUM",
         msi.segment1 "ITEM CODE",
         pla.item_description "DESCRIPTION",
         lines.tax_rate_code,
         pv.vendor_name "SUPPLIER",
         pv.VENDOR_TYPE_LOOKUP_CODE "SUPPLIER TYPE",
         papf.full_name "BUYER",
         TRUNC (pha.creation_date) "CREATION DATE",
         pla.line_num "LINE NUM",
         (SELECT pla1.quantity * pla1.unit_price
            FROM po_lines_all pla1
           WHERE 1 = 1 AND pla1.po_line_id = pla.po_line_id)
            "PO LINE AMT",
         pha.currency_code "CURRENCY",
         plla.need_by_date,
         plla.closed_code "SHIPMENT STATUS", --Its display status of each line which we called as shipment status
         (SELECT DECODE (PHA.APPROVED_FLAG,
                         'Y', 'Approved',
                         'R', 'Requires Reapproval',
                         'F', 'Rejected',
                         'N', 'In Process',
                         NULL, 'In Complete')
            FROM PO.PO_HEADERS_ALL PH1
           WHERE PH1.PO_HEADER_ID = pha.PO_HEADER_ID)
            "PO APPROVAL STATUS"
    FROM apps.po_headers_all pha,
         apps.ap_suppliers pv,
         apps.ap_supplier_sites_all pvs,
         hr_locations hl1,
         hr_locations hl2,
         apps.per_all_people_f papf,
         apps.po_lines_all pla,
         hr_operating_units hou,
         apps.ap_terms_tl atl,
         apps.po_line_locations_all plla,
         ZX_LINES Lines,
         inv.mtl_system_items_b msi
   WHERE     pha.vendor_id = pv.vendor_id
         AND pha.vendor_site_id = pvs.vendor_site_id
         AND pha.ship_to_location_id = hl1.location_id
         AND pha.bill_to_location_id = hl2.location_id
         AND pha.agent_id = papf.person_id
         AND pha.po_header_id = pla.po_header_id
         AND pha.org_id = hou.organization_id
         AND pha.terms_id = atl.term_id
         AND plla.po_header_id = pha.po_header_id
         AND pla.po_line_id = plla.po_line_id
         AND pha.org_id = plla.org_id
         AND pha.org_id = &ORG_ID
         AND pha.creation_date > '&FROM_CREATION_DATE'
         AND lines.trx_id(+) = pha.po_header_id
         AND msi.inventory_item_id = pla.item_id
ORDER BY 1, 2, 8