AP Supplier Banks Payments Query

SELECT   pvs.org_id org_id, iep.default_payment_method_code, alc.lookup_code,
         hou.NAME org_name, pv.vendor_id supplier_id,
         pv.segment1 supplier_number, plc.description supplier_type,
         pv.vendor_name supplier_name,
         (CASE
             WHEN TRUNC (NVL (pv.end_date_active, SYSDATE + 1)) >=
                                                               TRUNC (SYSDATE)
                THEN ‘Active’
             ELSE ‘Inactive’
          END
         ) status,
         pvs.vendor_site_code site_name,
         (SELECT TO_CHAR (MAX (check_date), ‘DD-MON-RRRR’)
            FROM apps.ap_checks
           WHERE vendor_id = pv.vendor_id
             AND vendor_site_id = pvs.vendor_site_id) last_payment_date,
         pv.vat_registration_num tax_registration_number,
         fu.user_name updated_by,
         TO_CHAR (pvs.last_update_date, ‘DD-MON-RRRR’) update_date,
         pvs.address_line1 site_address1, pvs.address_line2 site_address2,
         pvs.address_line3 site_address3,
         pvs.address_lines_alt alternate_address, pvs.city city,
         pvs.state state, pvs.zip postal_code,
         pvs.area_code || ‘ ‘ || pvs.phone telephone,
         pvs.fax_area_code || ‘ ‘ || pvs.fax fax,
         pvs.email_address site_email, pvs.remittance_email remittance_email,
         pvs.supplier_notif_method notify_method, pvs.attribute13 remit_type,
         apt.NAME payment_terms, pvs.payment_currency_code currency,
         ieb.bank_account_name, ieb.bank_account_number bank_account_num,
         ieb.branch_number bank_branch_name,
         TO_CHAR (ipi.start_date, ‘DD-MON-RRRR’) effective_date,
         pvs.hold_unmatched_invoices_flag hold_unmatched_invoices,
         pvs.hold_future_payments_flag hold_unvalidated_invoices,
         pvs.hold_all_payments_flag hold_all_payments
    FROM apps.ap_suppliers pv,
         apps.ap_supplier_sites_all pvs,
         apps.hz_parties party_supp,
         apps.hz_party_sites site_supp,
         applsys.fnd_user fu,
         apps.hr_operating_units hou,
         apps.po_lookup_codes plc,
         apps.ap_lookup_codes alc,
         apps.ap_terms apt,
         iby.iby_external_payees_all iep,
         iby.iby_pmt_instr_uses_all ipi,
         apps.iby_ext_bank_accounts_v ieb
   WHERE pv.vendor_id = pvs.vendor_id
     AND party_supp.party_id = pv.party_id
     AND party_supp.party_id = site_supp.party_id
     AND site_supp.party_site_id = pvs.party_site_id
     AND pvs.last_updated_by = fu.user_id(+)
     AND pvs.org_id = hou.organization_id
     AND pv.vendor_type_lookup_code = plc.lookup_code(+)
     AND plc.lookup_type(+) = ‘VENDOR TYPE’
     AND pvs.terms_id = apt.term_id
     AND iep.supplier_site_id = pvs.vendor_site_id
     AND iep.payee_party_id = pv.party_id
     AND iep.default_payment_method_code = alc.lookup_code
     AND alc.lookup_type = ‘PAYMENT METHOD’
     AND iep.payee_party_id = party_supp.party_id
     AND iep.party_site_id = site_supp.party_site_id
     AND iep.ext_payee_id = ipi.ext_pmt_party_id(+)
     AND ipi.instrument_id = ieb.ext_bank_account_id(+)
ORDER BY pv.vendor_name, pvs.vendor_site_code;