Oracle EBS - SQL Query - Supplier Site associated Bank Details

Subject: Business need to extract the supplier site level associated bank details.

--SQL:

SELECT

    'Suppplier Site'    "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    iban,

    ieba.bank_account_name,

    cbv.bank_name,

    cbv.address_line1   bank_address_1,

    cbv.country         bank_country,

    cbv.city            bank_city,

    cbbv.bank_branch_name,

    cbbv.address_line1  branch_address_1,

    cbbv.city           branch_city,

    cbbv.country        branch_country,

    cbbv.branch_number,

    cbbv.eft_swift_code bic,

    foreign_payment_use_flag

FROM

    apps.ap_suppliers            aps,

    apps.iby_external_payees_all iepa,

    apps.iby_pmt_instr_uses_all  ipiua,

    apps.iby_ext_bank_accounts   ieba,

    apps.ce_banks_v              cbv,

    apps.ce_bank_branches_v      cbbv,

    apps.ap_supplier_sites_all   ass

WHERE

        1 = 1

    AND aps.vendor_id = ass.vendor_id

    AND ass.org_id = :p_org_id

    AND iepa.payee_party_id = aps.party_id

    AND iepa.party_site_id IS NOT NULL

    AND iepa.supplier_site_id IS NOT NULL

    AND ipiua.ext_pmt_party_id (+) = iepa.ext_payee_id

    AND ieba.ext_bank_account_id (+) = ipiua.instrument_id

    AND ieba.bank_id = cbv.bank_party_id (+)

    AND ieba.branch_id = cbbv.branch_party_id (+)

--    AND aps.vendor_id in (32323,34962)

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num