Query to get PO With Approval , Invoice and Payment Details in Oracle APPS

SELECT inv.org_id

      ,supp.segment1 supplier_number

      ,supp.vendor_name supplier_name

      ,UPPER (supp.vendor_type_lookup_code) supplier_type

      ,supp_site.vendor_site_code supplier_site_code

      ,supp_site.address_line1

  ,supp_site.address_line2

  ,supp_site.address_line3

  ,supp_site.address_line4

      ,supp_site.city 

      ,supp_site.country

      ,TO_CHAR (po_hdrs.creation_date, 'DD-MON-RRRR') po_creation_date

      ,po_hdrs.segment1 po_number

      ,po_hdrs.type_lookup_code po_type

      ,po_dist.quantity_ordered

      ,po_dist.quantity_cancelled

      ,po_lines.item_id 

      ,po_lines.item_description item_description

      ,po_lines.unit_price

      ,(NVL (po_dist.quantity_ordered, 0) - NVL (po_dist.quantity_cancelled, 0))

       * NVL (po_lines.unit_price, 0)

          po_line_amount

      ,(SELECT DECODE (ph.approved_flag, 'Y', 'Approved'

                                   , 'R', 'Requires Reapproval'

   , 'F', 'Failed Approval'

   , 'N', 'Never Approved'

   )

          FROM po.po_headers_all ph

         WHERE ph.po_header_id = po_hdrs.po_header_id)

          po_approved_status

      ,inv.invoice_type_lookup_code invoice_type

      ,inv.invoice_amount invoice_amount

      ,TO_CHAR(inv.invoice_date, 'DD-MON-RRRR') invoice_date

      ,inv.invoice_num 

      ,(SELECT DECODE (ap_inv_dist.match_status_flag, 'A', 'Approved'

                                                , 'T', 'Tested but not Validate'

,'Never Validated')

          FROM ap.ap_invoice_distributions_all ap_inv_dist

         WHERE ap_inv_dist.invoice_distribution_id = inv_dist.invoice_distribution_id) invoice_approved_status

      ,inv.amount_paid

      ,inv_pmt.amount

      ,inv_pmt.check_id

      ,inv_pmt.invoice_payment_id payment_id

      ,chk.check_number cheque_number

      ,TO_CHAR(chk.check_date, 'DD-MON-RRRR') check_date

  FROM ap_invoices_all inv

      ,ap_invoice_distributions_all inv_dist

  ,po_distributions_all po_dist

  ,po_headers_all po_hdrs

  ,ap_suppliers supp

  ,ap_supplier_sites_all supp_site

  ,po_lines_all po_lines

  ,ap_invoice_payments_all inv_pmt

      ,ap_checks_all chk

 WHERE     inv.invoice_id = inv_dist.invoice_id

       AND inv_dist.po_distribution_id = po_dist.po_distribution_id(+)

       AND po_dist.po_header_id = po_hdrs.po_header_id(+)

       AND supp.vendor_id(+) = po_hdrs.VENDOR_ID

       AND supp_site.vendor_site_id(+) = po_hdrs.vendor_site_id

       AND po_hdrs.po_header_id = po_lines.po_header_id

       AND po_dist.po_line_id = po_lines.po_line_id

       AND inv.invoice_id = inv_pmt.invoice_id

       AND inv_pmt.check_id = chk.check_id

       AND supp_site.vendor_site_id = chk.vendor_site_id

       AND po_dist.po_header_id IS NOT NULL

       AND inv.payment_status_flag = 'Y'

       AND po_hdrs.type_lookup_code != 'BLANKET'