R12 Internal Bank Details query

SELECT hou.NAME                        "OPERATING UNIT",
       cbbv.bank_name,
       cbbv.bank_branch_name,
       cba.bank_account_name,
       hp.party_name                   "LEGAL ENTITY",
       cbau.ar_use_enable_flag         "RECEIVABLES_ACCOUNT_USE",
       cbau.ap_use_enable_flag         "PAYABLES_ACCOUNT_USE",
       cba.bank_account_num            "ACCOUNT_NUMBER",
       cba.bank_account_type           "ACCOUNT TYPE",
       cba.iban_number,
       cba.currency_code,
       cba.multi_currency_allowed_flag,
       cba.description,
       gcck1.concatenated_segments     "CASH ACCOUNT",
       gcck2.concatenated_segments     "BANK_CHARGES_ACCOUNT",
       gcck3.concatenated_segments     "FOREIGN_EXCHANGE_CHARGES",
       gcck4.concatenated_segments     "CASH_CLEARING_ACCOUNT",
       gcck5.concatenated_segments     "BANK_ERRORS_ACCOUNT",
       gcck6.concatenated_segments     "FUTURE_DATED_PAYMENT_ACCOUNT",
       cba.ap_amount_tolerance         "PAYMENT_TOLERANCE_AMOUNT",
       cba.ap_percent_tolerance        "PAYMENT_TOLERANCE_PERCENTAGE",
       cba.ar_amount_tolerance         "RECEIPT_TOLERANCE_AMOUNT",
       cba.ar_percent_tolerance        "RECEIPT_TOLERANCE_PERCENTAGE",
       cba.ce_amount_tolerance         "CASHFLOW_TOLERANCE_AMOUNT",
       cba.ce_percent_tolerance        "CASHFLOW_TOLERANCE_PERCENTAGE",
       cba.recon_oi_amount_tolerance   "OPEN_INT_TOLERANCE_AMOUNT",
       cba.recon_oi_percent_tolerance  "OPEN_INT_TOLERANCE_PERCENTAGE",
       gcck7.concatenated_segments     "ON_ACCOUNT_ACCOUNT",
       gcck8.concatenated_segments     "UNAPPLIED_ACCOUNT",
       gcck9.concatenated_segments     "UNIDENTIFIED_ACCOUNT",
       gcck10.concatenated_segments    "ASSET_ACCOUNT",
       gcck11.concatenated_segments    "REMITTANCE_ACCOUNT",
       gcck12.concatenated_segments    "RECEIPT_CLEARING_ACCOUNT"
  FROM ce_bank_accounts         cba,
       ce_bank_acct_uses_all    cbau,
       ce_gl_accounts_ccid      cgac,
       ce_bank_branches_v       cbbv,
       hr_operating_units       hou,
       hz_parties               hp,
       gl_code_combinations_kfv gcck1,
       gl_code_combinations_kfv gcck2,
       gl_code_combinations_kfv gcck3,
       gl_code_combinations_kfv gcck4,
       gl_code_combinations_kfv gcck5,
       gl_code_combinations_kfv gcck6,
       gl_code_combinations_kfv gcck7,
       gl_code_combinations_kfv gcck8,
       gl_code_combinations_kfv gcck9,
       gl_code_combinations_kfv gcck10,
       gl_code_combinations_kfv gcck11,
       gl_code_combinations_kfv gcck12
 WHERE cbbv.bank_party_id = cba.bank_id
   AND cbbv.branch_party_id = cba.bank_branch_id
   AND cba.bank_account_id = cbau.bank_account_id
   AND cgac.bank_acct_use_id = cbau.bank_acct_use_id
   AND cbau.org_id = hou.organization_id
   AND hp.party_id = cba.account_owner_party_id
   AND gcck1.code_combination_id = cgac.ap_asset_ccid
   AND gcck2.code_combination_id = cgac.bank_charges_ccid
   AND gcck3.code_combination_id = cba.fx_charge_ccid
   AND gcck4.code_combination_id = cgac.cash_clearing_ccid
   AND gcck5.code_combination_id(+) = cgac.bank_errors_ccid
   AND gcck6.code_combination_id(+) = cgac.future_dated_payment_ccid
   AND gcck7.code_combination_id = cgac.on_account_ccid
   AND gcck8.code_combination_id = cgac.unapplied_ccid
   AND gcck9.code_combination_id = cgac.unidentified_ccid
   AND gcck10.code_combination_id(+) = cgac.asset_code_combination_id
   AND gcck11.code_combination_id = cgac.remittance_ccid
   AND gcck12.code_combination_id = cgac.receipt_clearing_ccid

R12 Vendor/Supplier Bank Details Query

------------------------------------------------
Employee as a supplier Bank details
------------------------------------------------

SELECT aps.vendor_id,
       apss.vendor_site_id,
       aps.vendor_name,
       apss.vendor_site_code,
       ieb.bank_name,
       ieb.country,
       iebb.bank_branch_name,
       iebb.eft_swift_code,
       iebb.branch_number,
       ieba.bank_account_num,
       ieba.bank_account_name,
       iban
  FROM ap.ap_suppliers              aps,
       per_all_people_f             papf,
       ap.ap_supplier_sites_all     apss,
       apps.iby_ext_bank_accounts   ieba,
       apps.iby_account_owners      iao,
       apps.iby_ext_banks_v         ieb,
       apps.iby_ext_bank_branches_v iebb
 WHERE aps.vendor_id = apss.vendor_id
   AND iao.account_owner_party_id = aps.party_id
   AND ieba.ext_bank_account_id = iao.ext_bank_account_id
   AND ieb.bank_party_id = iebb.bank_party_id
   AND ieba.branch_id = iebb.branch_party_id
   AND ieba.bank_id = ieb.bank_party_id
   AND aps.employee_id = papf.person_id
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND
       papf.effective_end_date;
     
-----------------------------------------------------
Supplier Bank details (Not an Employee)
-----------------------------------------------------

SELECT aps.vendor_id,
       apss.vendor_site_id,
       aps.vendor_name,
       apss.vendor_site_code,
       ieb.bank_name,
       ieb.country,
       iebb.bank_branch_name,
       iebb.eft_swift_code,
       iebb.branch_number,
       ieba.bank_account_num,
       ieba.bank_account_name,
       iban
  FROM ap.ap_suppliers              aps,
       ap.ap_supplier_sites_all     apss,
       apps.iby_ext_bank_accounts   ieba,
       apps.iby_account_owners      iao,
       apps.iby_ext_banks_v         ieb,
       apps.iby_ext_bank_branches_v iebb
 WHERE aps.vendor_id = apss.vendor_id
   AND iao.account_owner_party_id = aps.party_id
   AND ieba.ext_bank_account_id = iao.ext_bank_account_id
   AND ieb.bank_party_id = iebb.bank_party_id
   AND ieba.branch_id = iebb.branch_party_id
   AND ieba.bank_id = ieb.bank_party_id

---Check series information exists in CE_PAYMENT_DOCUMENTS table.