The below is the query which will provide the supplier bank details:
SELECT
vendor_name supplier_name,
segment1 supplier_number,
ass.vendor_site_code site_code,
ieba.bank_account_name,
ieba.bank_account_num,
ieba.masked_bank_account_num,
(select user_name from apps.fnd_user where user_id=ieba.last_updated_by) updated_by,
to_char(ieba.last_update_date,’dd-Mon-yy hh24:mi:ss’) updated_date
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 iepa.payee_party_id = aps.party_id
AND iepa.supplier_site_id = ass.vendor_site_id
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.segment1 =:p_vendor_number;
