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
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
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
Check out the Amazon Deals of the day based on your interests..
