eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IBY_XML_FNDCPT_BANKACCOUNT_V

Source

View Text - Preformatted


SELECT XMLConcat( XMLElement("BankAccountInternalID", ext_ba.ext_bank_account_id), XMLElement("BankName", ce_bank.party_name), XMLElement("BankNumber", bankProfile.bank_or_branch_number), XMLElement("BranchInternalID", ext_ba.branch_id), XMLElement("BranchName", ce_branch.party_name), XMLElement("BranchNumber", branchProfile.bank_or_branch_number), XMLElement("BranchType", XMLElement("Code", branchCA.class_code), XMLElement("Meaning", bank_branch_type_lookup.meaning) ), XMLElement("BankAccountName", ext_ba.bank_account_name), XMLElement ("AlternateBankAccountName", ext_ba.bank_account_name_alt), XMLElement("BankAccountNumber", iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num_electronic, ext_ba.ba_num_elec_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'), baek.subkey_cipher_text, baes.segment_cipher_text, baes.encoding_scheme, ext_ba.ba_mask_setting, ext_ba.ba_unmask_length) ), XMLElement("UserEnteredBankAccountNumber", iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num, ext_ba.ba_num_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'), bak.subkey_cipher_text, bas.segment_cipher_text, bas.encoding_scheme, ext_ba.ba_mask_setting, ext_ba.ba_unmask_length) ), XMLElement("MaskedBankAccountNumber",ext_ba.masked_bank_account_num), XMLElement("SwiftCode", branchCP.eft_swift_code), XMLElement("IBANNumber", iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.iban, ext_ba.iban_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'), ibk.subkey_cipher_text, ibs.segment_cipher_text, ibs.encoding_scheme, ext_ba.ba_mask_setting, ext_ba.ba_unmask_length) ), XMLElement("MaskedIBANNumber", ext_ba.masked_IBAN), XMLElement("CheckDigits", ext_ba.check_digits), XMLElement("BankAccountType", XMLElement("Code", ext_ba.bank_account_type), XMLElement("Meaning", bank_account_type_lookup.meaning) ), XMLElement("BankAccountCurrency", XMLElement("Code", ext_ba.currency_code) ), XMLElement("BankAddress", IBY_FD_EXTRACT_GEN_PVT.Get_account_Address(branch_hps.location_id, ext_ba.country_code)), XMLElement("AccountHolderName",ce_owner.party_name), CASE WHEN ext_ba.attribute_category IS NULL THEN NULL ELSE XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_EXT_BANK_ACCOUNTS', ext_ba.ext_bank_account_id, NULL)) END ), ext_ba.ext_bank_account_id FROM iby_ext_bank_accounts ext_ba, iby_account_owners acct_owner, fnd_lookup_values bank_branch_type_lookup, fnd_lookups bank_account_type_lookup, hz_parties ce_bank, hz_parties ce_branch, hz_parties ce_owner, hz_organization_profiles branchProfile, hz_organization_profiles bankProfile, hz_code_assignments bankCA, hz_code_assignments branchCA, HZ_CONTACT_POINTS BranchCP, fnd_territories_vl tl, iby_sys_security_subkeys baek, iby_sys_security_subkeys bak, iby_sys_security_subkeys ibk, iby_security_segments baes, iby_security_segments bas, iby_security_segments ibs, hz_party_sites branch_hps WHERE ext_ba.bank_id=ce_bank.party_id(+) and ext_ba.branch_id=ce_branch.party_id(+) and ce_branch.party_id=branchProfile.party_id(+) AND ext_ba.ext_bank_account_id = acct_owner.ext_bank_account_id(+) AND acct_owner.primary_flag(+) = 'Y' AND nvl(acct_owner.end_date, sysdate + 10) > sysdate AND acct_owner.account_owner_party_id = ce_owner.party_id(+) And SYSDATE between nvl(TRUNC(branchProfile.effective_start_date), sysdate-1) and NVL(TRUNC(branchProfile.effective_end_date), SYSDATE+1) And SYSDATE between nvl(TRUNC(bankProfile.effective_start_date) ,sysdate-1) and NVL(TRUNC(bankProfile.effective_end_date), SYSDATE+1) and ce_bank.party_id=bankProfile.party_id(+) AND branchCA.class_code= bank_branch_type_lookup.lookup_code(+) and BankCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE' And BankCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' And BankCA.OWNER_TABLE_ID(+) = ce_bank.PARTY_ID And BranchCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE' And BranchCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' And BranchCA.OWNER_TABLE_ID(+) = ce_branch.PARTY_ID AND bank_branch_type_lookup.lookup_type(+) = 'BANK_BRANCH_TYPE' AND ext_ba.bank_account_type = bank_account_type_lookup.lookup_code(+) AND bank_account_type_lookup.lookup_type(+) = 'BANK_ACCOUNT_TYPE' And BranchCP.owner_table_name(+) = 'HZ_PARTIES' And BranchCP.owner_table_id(+) = ce_branch.party_id And BranchCP.contact_point_type(+) = 'EFT' And NVL(BranchCP.status(+), 'A') = 'A' and tl.territory_code(+)=ce_bank.country and branch_hps.party_id(+)=ce_branch.party_id and branch_hps.identifying_address_flag(+)='Y' and bank_branch_type_lookup.language(+)=USERENV('LANG') and bank_branch_type_lookup.view_application_id (+)=222 AND (ext_ba.ba_num_sec_segment_id = bas.sec_segment_id(+)) AND (bas.sec_subkey_id = bak.sec_subkey_id(+)) AND (ext_ba.iban_sec_segment_id = ibs.sec_segment_id(+)) AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+)) AND (ext_ba.ba_num_elec_sec_segment_id = baes.sec_segment_id(+)) AND (baes.sec_subkey_id = baek.sec_subkey_id(+))

View Text - HTML Formatted

SELECT XMLCONCAT( XMLELEMENT("BANKACCOUNTINTERNALID"
, EXT_BA.EXT_BANK_ACCOUNT_ID)
, XMLELEMENT("BANKNAME"
, CE_BANK.PARTY_NAME)
, XMLELEMENT("BANKNUMBER"
, BANKPROFILE.BANK_OR_BRANCH_NUMBER)
, XMLELEMENT("BRANCHINTERNALID"
, EXT_BA.BRANCH_ID)
, XMLELEMENT("BRANCHNAME"
, CE_BRANCH.PARTY_NAME)
, XMLELEMENT("BRANCHNUMBER"
, BRANCHPROFILE.BANK_OR_BRANCH_NUMBER)
, XMLELEMENT("BRANCHTYPE"
, XMLELEMENT("CODE"
, BRANCHCA.CLASS_CODE)
, XMLELEMENT("MEANING"
, BANK_BRANCH_TYPE_LOOKUP.MEANING) )
, XMLELEMENT("BANKACCOUNTNAME"
, EXT_BA.BANK_ACCOUNT_NAME)
, XMLELEMENT ("ALTERNATEBANKACCOUNTNAME"
, EXT_BA.BANK_ACCOUNT_NAME_ALT)
, XMLELEMENT("BANKACCOUNTNUMBER"
, IBY_EXT_BANKACCT_PUB.UNCIPHER_BANK_NUMBER (EXT_BA.BANK_ACCOUNT_NUM_ELECTRONIC
, EXT_BA.BA_NUM_ELEC_SEC_SEGMENT_ID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY')
, BAEK.SUBKEY_CIPHER_TEXT
, BAES.SEGMENT_CIPHER_TEXT
, BAES.ENCODING_SCHEME
, EXT_BA.BA_MASK_SETTING
, EXT_BA.BA_UNMASK_LENGTH) )
, XMLELEMENT("USERENTEREDBANKACCOUNTNUMBER"
, IBY_EXT_BANKACCT_PUB.UNCIPHER_BANK_NUMBER (EXT_BA.BANK_ACCOUNT_NUM
, EXT_BA.BA_NUM_SEC_SEGMENT_ID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY')
, BAK.SUBKEY_CIPHER_TEXT
, BAS.SEGMENT_CIPHER_TEXT
, BAS.ENCODING_SCHEME
, EXT_BA.BA_MASK_SETTING
, EXT_BA.BA_UNMASK_LENGTH) )
, XMLELEMENT("MASKEDBANKACCOUNTNUMBER"
, EXT_BA.MASKED_BANK_ACCOUNT_NUM)
, XMLELEMENT("SWIFTCODE"
, BRANCHCP.EFT_SWIFT_CODE)
, XMLELEMENT("IBANNUMBER"
, IBY_EXT_BANKACCT_PUB.UNCIPHER_BANK_NUMBER (EXT_BA.IBAN
, EXT_BA.IBAN_SEC_SEGMENT_ID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY')
, IBK.SUBKEY_CIPHER_TEXT
, IBS.SEGMENT_CIPHER_TEXT
, IBS.ENCODING_SCHEME
, EXT_BA.BA_MASK_SETTING
, EXT_BA.BA_UNMASK_LENGTH) )
, XMLELEMENT("MASKEDIBANNUMBER"
, EXT_BA.MASKED_IBAN)
, XMLELEMENT("CHECKDIGITS"
, EXT_BA.CHECK_DIGITS)
, XMLELEMENT("BANKACCOUNTTYPE"
, XMLELEMENT("CODE"
, EXT_BA.BANK_ACCOUNT_TYPE)
, XMLELEMENT("MEANING"
, BANK_ACCOUNT_TYPE_LOOKUP.MEANING) )
, XMLELEMENT("BANKACCOUNTCURRENCY"
, XMLELEMENT("CODE"
, EXT_BA.CURRENCY_CODE) )
, XMLELEMENT("BANKADDRESS"
, IBY_FD_EXTRACT_GEN_PVT.GET_ACCOUNT_ADDRESS(BRANCH_HPS.LOCATION_ID
, EXT_BA.COUNTRY_CODE))
, XMLELEMENT("ACCOUNTHOLDERNAME"
, CE_OWNER.PARTY_NAME)
, CASE WHEN EXT_BA.ATTRIBUTE_CATEGORY IS NULL THEN NULL ELSE XMLELEMENT("DESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'IBY_EXT_BANK_ACCOUNTS'
, EXT_BA.EXT_BANK_ACCOUNT_ID
, NULL)) END )
, EXT_BA.EXT_BANK_ACCOUNT_ID
FROM IBY_EXT_BANK_ACCOUNTS EXT_BA
, IBY_ACCOUNT_OWNERS ACCT_OWNER
, FND_LOOKUP_VALUES BANK_BRANCH_TYPE_LOOKUP
, FND_LOOKUPS BANK_ACCOUNT_TYPE_LOOKUP
, HZ_PARTIES CE_BANK
, HZ_PARTIES CE_BRANCH
, HZ_PARTIES CE_OWNER
, HZ_ORGANIZATION_PROFILES BRANCHPROFILE
, HZ_ORGANIZATION_PROFILES BANKPROFILE
, HZ_CODE_ASSIGNMENTS BANKCA
, HZ_CODE_ASSIGNMENTS BRANCHCA
, HZ_CONTACT_POINTS BRANCHCP
, FND_TERRITORIES_VL TL
, IBY_SYS_SECURITY_SUBKEYS BAEK
, IBY_SYS_SECURITY_SUBKEYS BAK
, IBY_SYS_SECURITY_SUBKEYS IBK
, IBY_SECURITY_SEGMENTS BAES
, IBY_SECURITY_SEGMENTS BAS
, IBY_SECURITY_SEGMENTS IBS
, HZ_PARTY_SITES BRANCH_HPS
WHERE EXT_BA.BANK_ID=CE_BANK.PARTY_ID(+)
AND EXT_BA.BRANCH_ID=CE_BRANCH.PARTY_ID(+)
AND CE_BRANCH.PARTY_ID=BRANCHPROFILE.PARTY_ID(+)
AND EXT_BA.EXT_BANK_ACCOUNT_ID = ACCT_OWNER.EXT_BANK_ACCOUNT_ID(+)
AND ACCT_OWNER.PRIMARY_FLAG(+) = 'Y'
AND NVL(ACCT_OWNER.END_DATE
, SYSDATE + 10) > SYSDATE
AND ACCT_OWNER.ACCOUNT_OWNER_PARTY_ID = CE_OWNER.PARTY_ID(+)
AND SYSDATE BETWEEN NVL(TRUNC(BRANCHPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(BRANCHPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(TRUNC(BANKPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(BANKPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND CE_BANK.PARTY_ID=BANKPROFILE.PARTY_ID(+)
AND BRANCHCA.CLASS_CODE= BANK_BRANCH_TYPE_LOOKUP.LOOKUP_CODE(+)
AND BANKCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE'
AND BANKCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND BANKCA.OWNER_TABLE_ID(+) = CE_BANK.PARTY_ID
AND BRANCHCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE'
AND BRANCHCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND BRANCHCA.OWNER_TABLE_ID(+) = CE_BRANCH.PARTY_ID
AND BANK_BRANCH_TYPE_LOOKUP.LOOKUP_TYPE(+) = 'BANK_BRANCH_TYPE'
AND EXT_BA.BANK_ACCOUNT_TYPE = BANK_ACCOUNT_TYPE_LOOKUP.LOOKUP_CODE(+)
AND BANK_ACCOUNT_TYPE_LOOKUP.LOOKUP_TYPE(+) = 'BANK_ACCOUNT_TYPE'
AND BRANCHCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND BRANCHCP.OWNER_TABLE_ID(+) = CE_BRANCH.PARTY_ID
AND BRANCHCP.CONTACT_POINT_TYPE(+) = 'EFT'
AND NVL(BRANCHCP.STATUS(+)
, 'A') = 'A'
AND TL.TERRITORY_CODE(+)=CE_BANK.COUNTRY
AND BRANCH_HPS.PARTY_ID(+)=CE_BRANCH.PARTY_ID
AND BRANCH_HPS.IDENTIFYING_ADDRESS_FLAG(+)='Y'
AND BANK_BRANCH_TYPE_LOOKUP.LANGUAGE(+)=USERENV('LANG')
AND BANK_BRANCH_TYPE_LOOKUP.VIEW_APPLICATION_ID (+)=222
AND (EXT_BA.BA_NUM_SEC_SEGMENT_ID = BAS.SEC_SEGMENT_ID(+))
AND (BAS.SEC_SUBKEY_ID = BAK.SEC_SUBKEY_ID(+))
AND (EXT_BA.IBAN_SEC_SEGMENT_ID = IBS.SEC_SEGMENT_ID(+))
AND (IBS.SEC_SUBKEY_ID = IBK.SEC_SUBKEY_ID(+))
AND (EXT_BA.BA_NUM_ELEC_SEC_SEGMENT_ID = BAES.SEC_SEGMENT_ID(+))
AND (BAES.SEC_SUBKEY_ID = BAEK.SEC_SUBKEY_ID(+))