VIEW: APPS.IBY_EXT_BANK_ACCOUNTS_INT_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.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.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 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.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.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
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)