AP Invoices, Payments, holds Query in Oracle Applications

AP Invoices, Payments, holds Query in Oracle Applications

SELECT apps.ap_invoices_pkg.get_approval_status
                                (aia.invoice_id
                                ,aia.invoice_amount
                                ,aia.payment_status_flag
                                ,aia.invoice_type_lookup_code
                                ) approval_status
      ,aps.vendor_name supplier_name, aps.vendor_id supplier_id
      ,apss.vendor_site_id supplier_site_id, apss.vendor_site_code
      ,aia.invoice_num
      ,DECODE (aia.payment_status_flag
              ,'Y', 'Paid'
              ,'N', 'Unpaid'
              ,'P', 'Partially Paid'
              ) payment_status
      ,ah.hold_lookup_code, hl.description hold_description
      ,aip.amount payment_amount, ac.check_date, ac.check_number
      ,apsc.due_date scheduled_payment_date
  FROM apps.ap_invoice_payments_all aip
      ,apps.ap_invoices_all aia
      ,apps.ap_suppliers aps
      ,apps.ap_supplier_sites_all apss
      ,apps.ap_holds_all ah
      , (SELECT lookup_code, meaning, description
           FROM apps.fnd_lookups
          WHERE lookup_type = 'HOLD CODE') hl
      ,apps.ap_checks_all ac
      ,apps.ap_payment_schedules_all apsc
 WHERE aip.invoice_id(+) = aia.invoice_id
   AND aip.accounting_date > SYSDATE - 100
   AND aps.vendor_id = aia.vendor_id
   --AND aia.invoice_id = 1244359
   AND aia.vendor_site_id = apss.vendor_site_id
   AND aps.vendor_id = apss.vendor_id
   AND ah.invoice_id(+) = aia.invoice_id
   AND ah.hold_lookup_code = hl.lookup_code(+)
   AND aip.check_id = ac.check_id(+)
   AND apsc.invoice_id(+) = aia.invoice_id