IBY_EXT_BANK_ACCOUNTS

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
ROW_ID EXT_BANK_ACCOUNT_ID BANK_PARTY_ID COUNTRY_CODE BANK_NAME BANK_NUMBER BRANCH_PARTY_ID BANK_BRANCH_NAME BRANCH_NUMBER BRANCH_TYPE BANK_BRANCH_ADDRESS_ID EFT_SWIFT_CODE BANK_ACCOUNT_ID BANK_ACCOUNT_NAME BANK_ACCOUNT_NUMBER BANK_ACCT_NUM_HASH1 BANK_ACCT_NUM_HASH2 CURRENCY_CODE DESCRIPTION CHECK_DIGITS MULTI_CURRENCY_ALLOWED_FLAG ALTERNATE_ACCOUNT_NAME SHORT_ACCT_NAME ACCOUNT_SUFFIX IBAN_NUMBER IBAN_NUMBER_HASH1 IBAN_NUMBER_HASH2 PRIMARY_ACCT_OWNER_PARTY_ID PRIMARY_ACCT_OWNER_NAME ACCOUNT_CLASSIFICATION BANK_ACCOUNT_TYPE AGENCY_LOCATION_CODE START_DATE END_DATE PAYMENT_FACTOR_FLAG FOREIGN_PAYMENT_USE_FLAG EXCHANGE_RATE_AGREEMENT_NUM EXCHANGE_RATE_AGREEMENT_TYPE EXCHANGE_RATE HEDGING_CONTRACT_REFERENCE SECONDARY_ACCOUNT_REFERENCE ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 OBJECT_VERSION_NUMBER BANK_ACCOUNT_NUM_ELECTRONIC BANK_CODE CLEAR_BANK_ACCOUNT_NUMBER CLEAR_IBAN VAT_BANK_ACCOUNT_ID

Name

ROW_ID

EXT_BANK_ACCOUNT_ID

BANK_PARTY_ID

COUNTRY_CODE

BANK_NAME

BANK_NUMBER

BRANCH_PARTY_ID

BANK_BRANCH_NAME

BRANCH_NUMBER

BRANCH_TYPE

BANK_BRANCH_ADDRESS_ID

EFT_SWIFT_CODE

BANK_ACCOUNT_ID

BANK_ACCOUNT_NAME

BANK_ACCOUNT_NUMBER

BANK_ACCT_NUM_HASH1

BANK_ACCT_NUM_HASH2

CURRENCY_CODE

DESCRIPTION

CHECK_DIGITS

MULTI_CURRENCY_ALLOWED_FLAG

ALTERNATE_ACCOUNT_NAME

SHORT_ACCT_NAME

ACCOUNT_SUFFIX

IBAN_NUMBER

IBAN_NUMBER_HASH1

IBAN_NUMBER_HASH2

PRIMARY_ACCT_OWNER_PARTY_ID

PRIMARY_ACCT_OWNER_NAME

ACCOUNT_CLASSIFICATION

BANK_ACCOUNT_TYPE

AGENCY_LOCATION_CODE

START_DATE

END_DATE

PAYMENT_FACTOR_FLAG

FOREIGN_PAYMENT_USE_FLAG

EXCHANGE_RATE_AGREEMENT_NUM

EXCHANGE_RATE_AGREEMENT_TYPE

EXCHANGE_RATE

HEDGING_CONTRACT_REFERENCE

SECONDARY_ACCOUNT_REFERENCE

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

OBJECT_VERSION_NUMBER

BANK_ACCOUNT_NUM_ELECTRONIC

BANK_CODE

CLEAR_BANK_ACCOUNT_NUMBER

CLEAR_IBAN

VAT_BANK_ACCOUNT_ID

Query

SQL_Statement
SELECT eb.rowid, eb.ext_bank_account_id, eb.bank_id, eb.country_code, decode(eb.bank_branch_party_flag, 'N', eb.bank_name, bp.party_name), decode(eb.bank_branch_party_flag, 'N', eb.bank_number, bapr.bank_or_branch_number), eb.branch_id, decode(eb.bank_branch_party_flag, 'N', eb.bank_branch_name, br.party_name), decode(eb.bank_branch_party_flag, 'N', eb.branch_number, brpr.bank_or_branch_number), branchCA.class_code, s.location_id, decode(eb.bank_branch_party_flag, 'N', eb.eft_swift_code, BranchCP.eft_swift_code), eb.ext_bank_account_id, eb.bank_account_name, eb.masked_bank_account_num, eb.bank_account_num_hash1, eb.bank_account_num_hash2, eb.currency_code, eb.description, eb.check_digits, DECODE(eb.currency_code, NULL, 'Y', 'N'), eb.bank_account_name_alt, eb.short_acct_name, eb.account_suffix, eb.masked_iban, eb.iban_hash1, eb.iban_hash2, ow.account_owner_party_id, op.party_name, eb.account_classification, eb.bank_account_type, eb.agency_location_code, eb.start_date, eb.end_date, eb.payment_factor_flag, eb.foreign_payment_use_flag, eb.exchange_rate_agreement_num, eb.exchange_rate_agreement_type, eb.exchange_rate, eb.hedging_contract_reference, eb.secondary_account_reference, eb.attribute_category, eb.attribute1, eb.attribute2, eb.attribute3, eb.attribute4, eb.attribute5, eb.attribute6, eb.attribute7, eb.attribute8, eb.attribute9, eb.attribute10, eb.attribute11, eb.attribute12, eb.attribute13, eb.attribute14, eb.attribute15, eb.object_version_number, eb.bank_account_num_electronic, brpr.bank_code, iby_ext_bankacct_pub.Uncipher_Bank_Number (eb.bank_account_num, eb.ba_num_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'), bak.subkey_cipher_text, bas.segment_cipher_text, bas.encoding_scheme, eb.ba_mask_setting, eb.ba_unmask_length) CLEAR_BANK_ACCOUNT_NUMBER, iby_ext_bankacct_pub.Uncipher_Bank_Number (eb.iban, eb.iban_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'), ibk.subkey_cipher_text, ibs.segment_cipher_text, ibs.encoding_scheme, eb.ba_mask_setting, eb.ba_unmask_length) CLEAR_IBAN, eb.VAT_BANK_ACCOUNT_ID FROM HZ_ORGANIZATION_PROFILES BAPR, HZ_ORGANIZATION_PROFILES BrPR, HZ_PARTIES BP, HZ_PARTY_SITES S, IBY_ACCOUNT_OWNERS OW, HZ_PARTIES BR, HZ_PARTIES OP, IBY_EXT_BANK_ACCOUNTS EB, hz_code_assignments branchCA, HZ_CONTACT_POINTS BranchCP, IBY_SYS_SECURITY_SUBKEYS bak, IBY_SECURITY_SEGMENTS bas, IBY_SYS_SECURITY_SUBKEYS ibk, IBY_SECURITY_SEGMENTS ibs WHERE eb.bank_id = bp.party_id(+) AND eb.bank_id = bapr.party_id(+) AND eb.branch_id = br.party_id(+) and eb.branch_id = brpr.party_id(+) AND eb.ext_bank_account_id = ow.ext_bank_account_id(+) AND ow.primary_flag(+) = 'Y' AND NVL(ow.end_date,SYSDATE+10)>SYSDATE AND ow.account_owner_party_id = op.party_id(+) AND (br.party_id = s.party_id(+)) AND (s.identifying_address_flag(+) = 'Y') AND (s.status(+) = 'A') And (BranchCP.owner_table_name(+) = 'HZ_PARTIES') And (BranchCP.owner_table_id(+) = eb.branch_id) And (BranchCP.contact_point_type(+) = 'EFT' ) And (NVL(BranchCP.status(+), 'A') = 'A') And (BranchCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE' ) And (BranchCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES') And (BranchCA.OWNER_TABLE_ID(+) = eb.branch_ID) And TRUNC(SYSDATE) between nvl(TRUNC(Bapr.effective_start_date), sysdate-1) and NVL(TRUNC(Bapr.effective_end_date), SYSDATE+1) And TRUNC(SYSDATE) between nvl(TRUNC(Brpr.effective_start_date) ,sysdate-1) and NVL(TRUNC(Brpr.effective_end_date), SYSDATE+1) AND eb.ba_num_sec_segment_id = bas.sec_segment_id(+) AND (bas.sec_subkey_id = bak.sec_subkey_id(+)) AND (eb.iban_sec_segment_id = ibs.sec_segment_id(+)) AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+))

SQL_Statement

SELECT eb.rowid, eb.ext_bank_account_id, eb.bank_id, eb.country_code, decode(eb.bank_branch_party_flag, 'N', eb.bank_name, bp.party_name), decode(eb.bank_branch_party_flag, 'N', eb.bank_number, bapr.bank_or_branch_number), eb.branch_id, decode(eb.bank_branch_party_flag, 'N', eb.bank_branch_name, br.party_name), decode(eb.bank_branch_party_flag, 'N', eb.branch_number, brpr.bank_or_branch_number), branchCA.class_code, s.location_id, decode(eb.bank_branch_party_flag, 'N', eb.eft_swift_code, BranchCP.eft_swift_code), eb.ext_bank_account_id, eb.bank_account_name, eb.masked_bank_account_num, eb.bank_account_num_hash1, eb.bank_account_num_hash2, eb.currency_code, eb.description, eb.check_digits, DECODE(eb.currency_code, NULL, 'Y', 'N'), eb.bank_account_name_alt, eb.short_acct_name, eb.account_suffix, eb.masked_iban, eb.iban_hash1, eb.iban_hash2, ow.account_owner_party_id, op.party_name, eb.account_classification, eb.bank_account_type, eb.agency_location_code, eb.start_date, eb.end_date, eb.payment_factor_flag, eb.foreign_payment_use_flag, eb.exchange_rate_agreement_num, eb.exchange_rate_agreement_type, eb.exchange_rate, eb.hedging_contract_reference, eb.secondary_account_reference, eb.attribute_category, eb.attribute1, eb.attribute2, eb.attribute3, eb.attribute4, eb.attribute5, eb.attribute6, eb.attribute7, eb.attribute8, eb.attribute9, eb.attribute10, eb.attribute11, eb.attribute12, eb.attribute13, eb.attribute14, eb.attribute15, eb.object_version_number, eb.bank_account_num_electronic, brpr.bank_code, iby_ext_bankacct_pub.Uncipher_Bank_Number (eb.bank_account_num, eb.ba_num_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'), bak.subkey_cipher_text, bas.segment_cipher_text, bas.encoding_scheme, eb.ba_mask_setting, eb.ba_unmask_length) CLEAR_BANK_ACCOUNT_NUMBER, iby_ext_bankacct_pub.Uncipher_Bank_Number (eb.iban, eb.iban_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'), ibk.subkey_cipher_text, ibs.segment_cipher_text, ibs.encoding_scheme, eb.ba_mask_setting, eb.ba_unmask_length) CLEAR_IBAN, eb.VAT_BANK_ACCOUNT_ID FROM HZ_ORGANIZATION_PROFILES BAPR, HZ_ORGANIZATION_PROFILES BrPR, HZ_PARTIES BP, HZ_PARTY_SITES S, IBY_ACCOUNT_OWNERS OW, HZ_PARTIES BR, HZ_PARTIES OP, IBY_EXT_BANK_ACCOUNTS EB, hz_code_assignments branchCA, HZ_CONTACT_POINTS BranchCP, IBY_SYS_SECURITY_SUBKEYS bak, IBY_SECURITY_SEGMENTS bas, IBY_SYS_SECURITY_SUBKEYS ibk, IBY_SECURITY_SEGMENTS ibs

WHERE eb.bank_id = bp.party_id(+) AND eb.bank_id = bapr.party_id(+) AND eb.branch_id = br.party_id(+) and eb.branch_id = brpr.party_id(+) AND eb.ext_bank_account_id = ow.ext_bank_account_id(+) AND ow.primary_flag(+) = 'Y' AND NVL(ow.end_date,SYSDATE+10)>SYSDATE AND ow.account_owner_party_id = op.party_id(+) AND (br.party_id = s.party_id(+)) AND (s.identifying_address_flag(+) = 'Y') AND (s.status(+) = 'A') And (BranchCP.owner_table_name(+) = 'HZ_PARTIES') And (BranchCP.owner_table_id(+) = eb.branch_id) And (BranchCP.contact_point_type(+) = 'EFT' ) And (NVL(BranchCP.status(+), 'A') = 'A') And (BranchCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE' ) And (BranchCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES') And (BranchCA.OWNER_TABLE_ID(+) = eb.branch_ID) And TRUNC(SYSDATE) between nvl(TRUNC(Bapr.effective_start_date), sysdate-1) and NVL(TRUNC(Bapr.effective_end_date), SYSDATE+1) And TRUNC(SYSDATE) between nvl(TRUNC(Brpr.effective_start_date) ,sysdate-1) and NVL(TRUNC(Brpr.effective_end_date), SYSDATE+1) AND eb.ba_num_sec_segment_id = bas.sec_segment_id(+) AND (bas.sec_subkey_id = bak.sec_subkey_id(+)) AND (eb.iban_sec_segment_id = ibs.sec_segment_id(+)) AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+))