IBY_XML_FD_VAT_BA_1_0

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
EXT_BANK_ACCOUNT BANK_ACCOUNT_ID

Name

EXT_BANK_ACCOUNT

BANK_ACCOUNT_ID

Query

SQL_Statement
SELECT XMLConcat( XMLElement("BankAccountInternalID", ext_ba.ext_bank_account_id), XMLElement("BankName", ext_ba_v.BANK_NAME), XMLElement("AlternateBankName", (select BANK_NAME_ALT from ce_index_banks bnk, iby_ext_bank_accounts bnkacct where bnk.BANK_PARTY_ID = bnkacct.BANK_ID AND bnkacct.EXT_BANK_ACCOUNT_ID = ext_ba.ext_bank_account_id)), XMLElement("BankNumber", ext_ba_v.BANK_NUMBER), XMLElement("BranchInternalID", ext_ba_v.BRANCH_PARTY_ID), XMLElement("BranchName", ext_ba_v.BANK_BRANCH_NAME), XMLElement("AlternateBranchName", (select BANK_BRANCH_NAME_ALT from ce_index_bank_branches bnkbrch, iby_ext_bank_accounts bnkacct where bnkbrch.BRANCH_PARTY_ID = bnkacct.BRANCH_ID AND bnkacct.EXT_BANK_ACCOUNT_ID = ext_ba.ext_bank_account_id)), XMLElement("BranchNumber", ext_ba_v.BRANCH_NUMBER), XMLElement("BankCode",ext_ba_v.bank_code), XMLElement("BankAccountName", ext_ba_v.BANK_ACCOUNT_NAME), XMLElement("AlternateBankAccountName", ext_ba_v.ALTERNATE_ACCOUNT_NAME), 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("MaskedBankAccountNumber", iby_ext_bankacct_pub.mask_bank_number( 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), syssec.ext_ba_mask_setting, syssec.ext_ba_unmask_len) ), 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("BankAccountSuffix",ext_ba.account_suffix), XMLElement("SwiftCode", ext_ba_v.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", iby_ext_bankacct_pub.mask_bank_number(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),syssec.ext_ba_mask_setting, syssec.ext_ba_unmask_len)), XMLElement("CheckDigits", ext_ba.check_digits), XMLELEMENT("SecondaryBankAccountReference",ext_ba.SECONDARY_ACCOUNT_REFERENCE), XMLElement("BankAccountType", XMLElement("Code", ext_ba_v.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((ext_ba_v.bank_branch_address_id), ext_ba.country_code)), XMLElement("PrimaryOwner", XMLElement("Name", ext_ba_v.PRIMARY_ACCT_OWNER_NAME) ), XMLElement("FactorAccount", XMLElement("Code", ext_ba.FOREIGN_PAYMENT_USE_FLAG), XMLElement("Meaning", factor_lookup.meaning) ), IBY_FD_EXTRACT_GEN_PVT.Get_Intermediary_Bank_Accts(ext_ba.ext_bank_account_id) , XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_EXT_BANK_ACCOUNTS', ext_ba.ext_bank_account_id, NULL)), XMLElement("BranchDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTIES', ext_ba.BRANCH_ID, NULL)) ), ext_ba.ext_bank_account_id BANK_ACCOUNT_ID from iby_ext_bank_accounts ext_ba, fnd_lookups bank_account_type_lookup, fnd_lookups factor_lookup, IBY_SYS_SECURITY_SUBKEYS bak, IBY_SECURITY_SEGMENTS bas, IBY_SYS_SECURITY_SUBKEYS ibk, IBY_SECURITY_SEGMENTS ibs, IBY_SYS_SECURITY_SUBKEYS baek, IBY_SECURITY_SEGMENTS baes, iby_sys_security_options syssec, iby_ext_bank_accounts_v ext_ba_v WHERE ext_ba.ext_bank_account_id = ext_ba_v.ext_bank_account_id AND ext_ba.bank_account_type = bank_account_type_lookup.lookup_code(+) AND bank_account_type_lookup.lookup_type(+) = 'IBY_BANKACCT_TYPES' And factor_lookup.lookup_type(+) = 'IBY_YES_NO' And ext_ba.payment_factor_flag = factor_lookup.lookup_code(+) 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(+))

SQL_Statement

SELECT XMLConcat( XMLElement("BankAccountInternalID", ext_ba.ext_bank_account_id), XMLElement("BankName", ext_ba_v.BANK_NAME), XMLElement("AlternateBankName", (select BANK_NAME_ALT from ce_index_banks bnk, iby_ext_bank_accounts bnkacct where bnk.BANK_PARTY_ID = bnkacct.BANK_ID AND bnkacct.EXT_BANK_ACCOUNT_ID = ext_ba.ext_bank_account_id)), XMLElement("BankNumber", ext_ba_v.BANK_NUMBER), XMLElement("BranchInternalID", ext_ba_v.BRANCH_PARTY_ID), XMLElement("BranchName", ext_ba_v.BANK_BRANCH_NAME), XMLElement("AlternateBranchName", (select BANK_BRANCH_NAME_ALT from ce_index_bank_branches bnkbrch, iby_ext_bank_accounts bnkacct where bnkbrch.BRANCH_PARTY_ID = bnkacct.BRANCH_ID AND bnkacct.EXT_BANK_ACCOUNT_ID = ext_ba.ext_bank_account_id)), XMLElement("BranchNumber", ext_ba_v.BRANCH_NUMBER), XMLElement("BankCode",ext_ba_v.bank_code), XMLElement("BankAccountName", ext_ba_v.BANK_ACCOUNT_NAME), XMLElement("AlternateBankAccountName", ext_ba_v.ALTERNATE_ACCOUNT_NAME), 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("MaskedBankAccountNumber", iby_ext_bankacct_pub.mask_bank_number( 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), syssec.ext_ba_mask_setting, syssec.ext_ba_unmask_len) ), 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("BankAccountSuffix",ext_ba.account_suffix), XMLElement("SwiftCode", ext_ba_v.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", iby_ext_bankacct_pub.mask_bank_number(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),syssec.ext_ba_mask_setting, syssec.ext_ba_unmask_len)), XMLElement("CheckDigits", ext_ba.check_digits), XMLELEMENT("SecondaryBankAccountReference",ext_ba.SECONDARY_ACCOUNT_REFERENCE), XMLElement("BankAccountType", XMLElement("Code", ext_ba_v.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((ext_ba_v.bank_branch_address_id), ext_ba.country_code)), XMLElement("PrimaryOwner", XMLElement("Name", ext_ba_v.PRIMARY_ACCT_OWNER_NAME) ), XMLElement("FactorAccount", XMLElement("Code", ext_ba.FOREIGN_PAYMENT_USE_FLAG), XMLElement("Meaning", factor_lookup.meaning) ), IBY_FD_EXTRACT_GEN_PVT.Get_Intermediary_Bank_Accts(ext_ba.ext_bank_account_id) , XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_EXT_BANK_ACCOUNTS', ext_ba.ext_bank_account_id, NULL)), XMLElement("BranchDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTIES', ext_ba.BRANCH_ID, NULL)) ), ext_ba.ext_bank_account_id BANK_ACCOUNT_ID from iby_ext_bank_accounts ext_ba, fnd_lookups bank_account_type_lookup, fnd_lookups factor_lookup, IBY_SYS_SECURITY_SUBKEYS bak, IBY_SECURITY_SEGMENTS bas, IBY_SYS_SECURITY_SUBKEYS ibk, IBY_SECURITY_SEGMENTS ibs, IBY_SYS_SECURITY_SUBKEYS baek, IBY_SECURITY_SEGMENTS baes, iby_sys_security_options syssec, iby_ext_bank_accounts_v ext_ba_v WHERE ext_ba.ext_bank_account_id = ext_ba_v.ext_bank_account_id AND ext_ba.bank_account_type = bank_account_type_lookup.lookup_code(+) AND bank_account_type_lookup.lookup_type(+) = 'IBY_BANKACCT_TYPES' And factor_lookup.lookup_type(+) = 'IBY_YES_NO' And ext_ba.payment_factor_flag = factor_lookup.lookup_code(+) 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(+))