Query to list Supplier Bank, Bank Branch, Bank Account information

Shailender Thallam
9 years ago
/**********************************************************************
 *PURPOSE: Query to list Supplier Bank, Bank Branch, Bank Account information
 *AUTHOR: Shailender Thallam 
 ***********************************************************************/
 SELECT owners.account_owner_party_id,
  asp.segment1 vendor_num,
  asp.vendor_name,
  (SELECT NAME
  FROM apps.hr_operating_units hou
  WHERE 1                 = 1
  AND hou.organization_id = asa.org_id
  ) ou_name,
  asa.vendor_site_code,
  ieb.country_code,
  cbbv.bank_name,
  cbbv.bank_number,
  cbbv.bank_branch_name,
  cbbv.branch_number,
  cbbv.bank_branch_type,
  cbbv.eft_swift_code,
  ieb.bank_account_num,
  ieb.currency_code,
  ieb.iban,
  ieb.foreign_payment_use_flag,
  ieb.bank_account_name_alt
FROM apps.iby_pmt_instr_uses_all instrument,
  apps.iby_account_owners owners,
  apps.iby_external_payees_all payees,
  apps.iby_ext_bank_accounts ieb,
  apps.ap_supplier_sites_all asa,
  apps.ap_suppliers asp,
  apps.ce_bank_branches_v cbbv
WHERE owners.primary_flag      = 'Y'
AND owners.ext_bank_account_id = ieb.ext_bank_account_id
AND owners.ext_bank_account_id = instrument.instrument_id
AND payees.ext_payee_id        = instrument.ext_pmt_party_id
AND payees.payee_party_id      = owners.account_owner_party_id
AND payees.supplier_site_id    = asa.vendor_site_id
AND asa.vendor_id              = asp.vendor_id
AND cbbv.branch_party_id(+)    = ieb.branch_id
  --and asp.vendor_name like '%ABC Emp, Name'
AND asp.vendor_name LIKE 'ABCD LLC'
ORDER BY 4,
  1,
  3 ;
For Further Reading
