Query to Get Oracle Payments Details

SELECT  /*+ ORDERED */

  ECODE (ap.vendor_type_lookup_code, 'EMPLOYEE', ap.vendor_name, hz.party_name) party_name,

  DECODE (ai.invoice_type_lookup_code,'PAYMENT REQUEST', hzps.party_site_name,ass.vendor_site_code) party_site_name,

  ai.invoice_num,

  ai.description,

  (SELECT MASKED_BANK_ACCOUNT_NUM

     FROM iby_ext_bank_accounts

    WHERE ext_bank_account_id = ps.external_bank_account_id)

     bank_account_num,

  TO_CHAR (asi.due_date, 'YYYY-MM-DD"T"HH24:MI:SS') due_date,

  ps.amount_remaining,

  NVL (asi.withholding_amount, 0) withheld_amount,

  NVL (asi.discount_amount, 0) discount_amount,

  -- asi.amount_remaining payment_amount

  asi.payment_amount payment_amount,

  NVL (

     (SELECT amount_remaining

        FROM ap_selected_invoices_all asi2

       WHERE asi2.original_invoice_id = TO_CHAR (asi.invoice_id)

             AND asi2.payment_num = asi.payment_num),

     0)

     AS interest_amount,

  asi.payment_currency_code selected_ps_pmt_currency,

  hr.name selected_ps_org_name

FROM ap_selected_invoices_all asi,

  ap_payment_schedules_all ps,

  ap_invoices_all ai,

  hz_parties hz,

  hz_party_sites hzps,

  ap_supplier_sites_all ass,

  hr_operating_units hr,

  ap_suppliers ap

WHERE     1 = 1

  AND asi.checkrun_id = :p_checkrun_id

  AND asi.org_id = hr.organization_id

  AND asi.original_invoice_id IS NULL

  AND asi.invoice_id = ps.invoice_id

  AND asi.payment_num = ps.payment_num

  AND ps.invoice_id = ai.invoice_id

  AND ai.party_id = hz.party_id

  AND ap.party_id(+) = hz.party_id

  AND ai.vendor_id = DECODE (SIGN (ai.vendor_id), -1, ai.vendor_id, NVL (ap.vendor_id, ai.vendor_id))

  AND ai.party_site_id = hzps.party_site_id(+)

  AND ai.vendor_site_id = ass.vendor_site_id(+)

ORDER BY party_name;