Oracle EBS - SQL Query - Supplier Header associated Bank Details

Subject: Business required to get the supplier information where Supplier Header associated bank details.

--SQL:

 SELECT

    'Suppplier Header'  "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    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

WHERE

        1 = 1

    AND aps.vendor_id IN (

        SELECT

            vendor_id

        FROM

            apps.ap_supplier_sites_all ass

        WHERE

            org_id = nvl(:p_org_id, org_id)

    )

    AND iepa.payee_party_id = aps.party_id

    AND party_site_id IS NULL

    AND supplier_site_id IS 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)

    AND ieba.bank_account_num IS NOT NULL

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num