Query for Supplier details with banks

SELECT         

                aps.vendor_name|| ' ~ '|| ass.vendor_site_code "Payee_Identifier"

                ,accts.ext_bank_account_id "Payee_Bank_Account_Identifier"

                ,bank.party_name "bank_name"

                ,branch.bank_branch_name "Branch_Name"

                ,accts.country_code "account_country_code"

                ,accts.bank_account_name "Account_Name"

                ,accts.bank_account_num "Account_Number"

                ,accts.currency_code "Account_Currency_Code"

                ,NULL "Allow_International_Payments"

                ,uses.start_date "Account_start_date"

                ,uses.end_date "Account_end_date"

                ,accts.iban "IBAN"

                ,accts.check_digits "check_digits"

                ,accts.bank_account_name_alt "Account_Alternate_Name"

                ,accts.bank_account_type "Bank_account_type"

                ,accts.account_suffix "Account_Suffix"

                ,accts.description "DESCRIPTION"

                ,accts.agency_location_code "Agency_Location_Code"

                ,accts.exchange_rate_agreement_num   "Exchange_Rate_Agreement_Number"

                ,accts.exchange_rate_agreement_type "Exchange_Rate_Agreement_Type"

                ,accts.exchange_rate "EXCHANGE_RATE"

                ,accts.secondary_account_reference "SECONDARY_ACCOUNT_REFERENCE"

           FROM  ap_suppliers aps

                ,ap_supplier_sites_all ass

                ,iby_pmt_instr_uses_all uses

                ,iby_external_payees_all payee

                ,iby_ext_bank_accounts accts

                ,hz_parties bank

                ,ce_bank_branches_v branch

          WHERE uses.instrument_type   = 'BANKACCOUNT'

            AND aps.vendor_id          = ass.vendor_id

            AND aps.party_id           = payee.payee_party_id

            AND payee.ext_payee_id     = uses.ext_pmt_party_id

            AND payee.payment_function = 'PAYABLES_DISB'

            AND uses.instrument_id     = accts.ext_bank_account_id

            AND accts.bank_id          = bank.party_id

            --AND accts.bank_id          = bankprofile.party_id

            AND accts.branch_id         = branch.branch_party_id

            AND aps.end_date_active  IS NULL

            AND ass.inactive_date    IS NULL

Query to find Suppliers/ Sites with 'Receipt' match option.

SELECT

    aps.vendor_id,

    aps.vendor_name,

    DECODE(aps.match_option,'P','Purchase Order','R','Receipt') match_option_suppiers,

    apssa.vendor_site_id,

    DECODE(apssa.match_option,'P','Purchase Order','R','Receipt') match_option_suppier_sites

FROM

    apps.ap_suppliers aps,

    apps.ap_supplier_sites_all apssa

WHERE    aps.vendor_id = apssa.vendor_id

     AND    aps.match_option = 'R'

     AND    apssa.match_option = 'R'

     AND    purchasing_site_flag IS NOT NULL;

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;