IBY_EXT_BANK_ACCOUNTS is the table used by Oracle to store the external bank account numbers (like Bank account details of a supplier to remit the money in).
To find the bank name, connect this table to hz_parties ( party_id) table using bank_id to get the bank details.
Also keep an eye on field MASKED_BANK_ACCOUNT_NUM to get the masked account number as printing the complete account number can violate the legal recommendations.
We will discuss the table structure and indexes on this table in this post
Table Structure
| COLUMN_NAME | DATA_TYPE | NULLABLE |
| EXT_BANK_ACCOUNT_ID | NUMBER | N |
| COUNTRY_CODE | VARCHAR2(8) | N |
| BRANCH_ID | NUMBER | Y |
| BANK_ID | NUMBER | Y |
| BANK_ACCOUNT_NUM | VARCHAR2(400) | Y |
| BANK_ACCOUNT_NUM_HASH1 | VARCHAR2(256) | Y |
| BANK_ACCOUNT_NUM_HASH2 | VARCHAR2(256) | Y |
| MASKED_BANK_ACCOUNT_NUM | VARCHAR2(400) | Y |
| BA_MASK_SETTING | VARCHAR2(120) | Y |
| BA_UNMASK_LENGTH | NUMBER | Y |
| CURRENCY_CODE | VARCHAR2(60) | Y |
| IBAN | VARCHAR2(200) | Y |
| IBAN_HASH1 | VARCHAR2(256) | Y |
| IBAN_HASH2 | VARCHAR2(256) | Y |
| MASKED_IBAN | VARCHAR2(200) | Y |
| CHECK_DIGITS | VARCHAR2(120) | Y |
| BANK_ACCOUNT_TYPE | VARCHAR2(100) | Y |
| ACCOUNT_CLASSIFICATION | VARCHAR2(120) | Y |
| ACCOUNT_SUFFIX | VARCHAR2(120) | Y |
| AGENCY_LOCATION_CODE | VARCHAR2(120) | Y |
| PAYMENT_FACTOR_FLAG | VARCHAR2(4) | Y |
| FOREIGN_PAYMENT_USE_FLAG | VARCHAR2(4) | Y |
| EXCHANGE_RATE_AGREEMENT_NUM | VARCHAR2(320) | Y |
| EXCHANGE_RATE_AGREEMENT_TYPE | VARCHAR2(320) | Y |
| EXCHANGE_RATE | NUMBER | Y |
| HEDGING_CONTRACT_REFERENCE | VARCHAR2(80) | Y |
| SECONDARY_ACCOUNT_REFERENCE | VARCHAR2(120) | Y |
| BA_NUM_SEC_SEGMENT_ID | NUMBER | Y |
| ENCRYPTED | VARCHAR2(4) | N |
| IBAN_SEC_SEGMENT_ID | NUMBER | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2(600) | Y |
| ATTRIBUTE1 | VARCHAR2(600) | Y |
| ATTRIBUTE2 | VARCHAR2(600) | Y |
| ATTRIBUTE3 | VARCHAR2(600) | Y |
| ATTRIBUTE4 | VARCHAR2(600) | Y |
| ATTRIBUTE5 | VARCHAR2(600) | Y |
| ATTRIBUTE6 | VARCHAR2(600) | Y |
| ATTRIBUTE7 | VARCHAR2(600) | Y |
| ATTRIBUTE8 | VARCHAR2(600) | Y |
| ATTRIBUTE9 | VARCHAR2(600) | Y |
| ATTRIBUTE10 | VARCHAR2(600) | Y |
| ATTRIBUTE11 | VARCHAR2(600) | Y |
| ATTRIBUTE12 | VARCHAR2(600) | Y |
| ATTRIBUTE13 | VARCHAR2(600) | Y |
| ATTRIBUTE14 | VARCHAR2(600) | Y |
| ATTRIBUTE15 | VARCHAR2(600) | Y |
| REQUEST_ID | NUMBER | Y |
| PROGRAM_APPLICATION_ID | NUMBER | Y |
| PROGRAM_ID | NUMBER | Y |
| PROGRAM_UPDATE_DATE | DATE | Y |
| START_DATE | DATE | Y |
| END_DATE | DATE | Y |
| CREATED_BY | VARCHAR2(256) | N |
| CREATION_DATE | TIMESTAMP(6) | N |
| LAST_UPDATED_BY | VARCHAR2(256) | N |
| LAST_UPDATE_DATE | TIMESTAMP(6) | N |
| LAST_UPDATE_LOGIN | VARCHAR2(128) | Y |
| OBJECT_VERSION_NUMBER | NUMBER | N |
| BANK_ACCOUNT_NAME | VARCHAR2(320) | Y |
| BANK_ACCOUNT_NAME_ALT | VARCHAR2(1280) | Y |
| SHORT_ACCT_NAME | VARCHAR2(120) | Y |
| DESCRIPTION | VARCHAR2(960) | Y |
| BANK_ACCOUNT_NUM_ELECTRONIC | VARCHAR2(400) | Y |
| BA_NUM_ELEC_SEC_SEGMENT_ID | NUMBER | Y |
| SALT_VERSION | NUMBER | Y |
| ORIG_SYSTEM | VARCHAR2(320) | Y |
| ORIG_SYSTEM_REFERENCE | VARCHAR2(960) | Y |
| ENTERPRISE_ID | NUMBER | Y |
| BANK_BRANCH_PARTY_FLAG | VARCHAR2(4) | Y |
| BANK_NAME | VARCHAR2(1440) | Y |
| BANK_NUMBER | VARCHAR2(120) | Y |
| BANK_BRANCH_NAME | VARCHAR2(1440) | Y |
| BRANCH_NUMBER | VARCHAR2(120) | Y |
| EFT_SWIFT_CODE | VARCHAR2(120) | Y |
Indexes
| INDEX NAME | INDEX TYPE | UNIQUENESS | COLUMN NAME |
| IBY_EXT_BANK_ACCOUNTS_N1 | NORMAL | NONUNIQUE | BANK_ID |
| IBY_EXT_BANK_ACCOUNTS_N2 | NORMAL | NONUNIQUE | BRANCH_ID |
| IBY_EXT_BANK_ACCOUNTS_N3 | NORMAL | NONUNIQUE | BANK_ACCOUNT_NUM_HASH1 |
| IBY_EXT_BANK_ACCOUNTS_N3 | NORMAL | NONUNIQUE | BANK_ACCOUNT_NUM_HASH2 |
| IBY_EXT_BANK_ACCOUNTS_N4 | NORMAL | NONUNIQUE | IBAN_HASH1 |
| IBY_EXT_BANK_ACCOUNTS_N4 | NORMAL | NONUNIQUE | IBAN_HASH2 |
| IBY_EXT_BANK_ACCOUNTS_N5 | NORMAL | NONUNIQUE | BA_MASK_SETTING |
| IBY_EXT_BANK_ACCOUNTS_N5 | NORMAL | NONUNIQUE | BA_UNMASK_LENGTH |
| IBY_EXT_BANK_ACCOUNTS_N6 | NORMAL | NONUNIQUE | BANK_ACCOUNT_NUM |
| IBY_EXT_BANK_ACCOUNTS_N6 | NORMAL | NONUNIQUE | BRANCH_ID |
| IBY_EXT_BANK_ACCOUNTS_N6 | NORMAL | NONUNIQUE | BANK_ID |
| IBY_EXT_BANK_ACCOUNTS_N7 | NORMAL | NONUNIQUE | BANK_ACCOUNT_NAME |
| IBY_EXT_BANK_ACCOUNTS_N8 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00075$ |
| IBY_EXT_BANK_ACCOUNTS_U1 | NORMAL | UNIQUE | EXT_BANK_ACCOUNT_ID |
Related Posts
Payments
How to query the payment details
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
From: Mohit Saxena
Author of “Oracle Fusion: Unconventional Ways to Enhance the Performance of BI Reports“