VIEW: APPS.IBY_EXTERNAL_BANK_ACCOUNTS_V

Source

View Text - Preformatted

SELECT eb.rowid, eb.ext_bank_account_id, eb.bank_id, eb.country_code, bp.party_name, bapr.bank_or_branch_number, eb.branch_id, br.party_name, brpr.bank_or_branch_number, branchCA.class_code, s.location_id, 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 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 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 (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 SYSDATE between nvl(TRUNC(Bapr.effective_start_date), sysdate-1) and NVL(TRUNC(Bapr.effective_end_date), SYSDATE+1) And SYSDATE between nvl(TRUNC(Brpr.effective_start_date) ,sysdate-1) and NVL(TRUNC(Brpr.effective_end_date), SYSDATE+1)

View Text - HTML Formatted

SELECT EB.ROWID
, EB.EXT_BANK_ACCOUNT_ID
, EB.BANK_ID
, EB.COUNTRY_CODE
, BP.PARTY_NAME
, BAPR.BANK_OR_BRANCH_NUMBER
, EB.BRANCH_ID
, BR.PARTY_NAME
, BRPR.BANK_OR_BRANCH_NUMBER
, BRANCHCA.CLASS_CODE
, S.LOCATION_ID
, 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
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
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 (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 SYSDATE BETWEEN NVL(TRUNC(BAPR.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(BAPR.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(TRUNC(BRPR.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(BRPR.EFFECTIVE_END_DATE)
, SYSDATE+1)