IBY_XML_FNDCPT_BANKACCOUNT

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
BANK_ACCOUNT_CONTENT INSTRID

Name

BANK_ACCOUNT_CONTENT

INSTRID

Query

SQL_Statement
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("BankAccountSuffix",ext_ba.account_suffix),XMLElement("BankAccountCurrency", XMLElement("Code", ext_ba.currency_code) ), XMLElement("BankAddress", XMLConcat ( XMLElement ( "AddressInternalID" , hlc.location_id ) , XMLElement ("AddressLine1" , hlc.address1 ) , XMLElement ( "AddressLine2" , hlc.address2 ) , XMLElement ("AddressLine3" , hlc.address3 ) , XMLElement ( "City" , hlc.city ) , XMLElement ( "County" ,hlc.county ) , XMLElement ( "State" , hlc.state ) , XMLElement ( "Country" , hlc.country ), XMLElement ( "CountryName" , trt.territory_short_name ) , XMLElement("ISO3DigitCountry", trb.iso_territory_code), XMLElement ( "PostalCode" ,ce_bank.postal_code ), XMLElement("PreFormattedConcatenatedAddress", hz_format_pub.format_address(hlc.location_id)), XMLElement("PreFormattedMailingAddress", hz_format_pub.format_address(hlc.location_id, 'POSTAL_ADDR')), XMLElement("AddressName",hps.party_site_name) ) ), XMLElement("AccountHolderName",ce_owner.party_name), XMLElement("BankAccountCountry",ext_ba.country_code), 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 ) BANK_ACCOUNT_CONTENT, ext_ba.ext_bank_account_id INSTRID 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_b trb, fnd_territories_tl trt, hz_party_sites hps, hz_locations hlc, 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 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 TRUNC(sysdate) BETWEEN branchprofile.effective_start_date AND branchprofile.effective_end_date AND branchprofile.effective_latest_change = 'Y' AND TRUNC(sysdate) BETWEEN bankprofile.effective_start_date AND bankprofile.effective_end_date AND bankprofile.effective_latest_change = 'Y' 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_INSTITUTION_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 trb.territory_code(+)=ce_bank.country AND trb.territory_code = trt.territory_code(+) AND trt.language(+) = USERENV('LANG') and hps.party_id(+)=ce_branch.party_id and hps.identifying_address_flag(+)='Y' and hps.status(+) = 'A' and hps.location_id=hlc.location_id(+) and bank_branch_type_lookup.language(+)=USERENV('LANG') 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", 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("BankAccountSuffix",ext_ba.account_suffix),XMLElement("BankAccountCurrency", XMLElement("Code", ext_ba.currency_code) ), XMLElement("BankAddress", XMLConcat ( XMLElement ( "AddressInternalID" , hlc.location_id ) , XMLElement ("AddressLine1" , hlc.address1 ) , XMLElement ( "AddressLine2" , hlc.address2 ) , XMLElement ("AddressLine3" , hlc.address3 ) , XMLElement ( "City" , hlc.city ) , XMLElement ( "County" ,hlc.county ) , XMLElement ( "State" , hlc.state ) , XMLElement ( "Country" , hlc.country ), XMLElement ( "CountryName" , trt.territory_short_name ) , XMLElement("ISO3DigitCountry", trb.iso_territory_code), XMLElement ( "PostalCode" ,ce_bank.postal_code ), XMLElement("PreFormattedConcatenatedAddress", hz_format_pub.format_address(hlc.location_id)), XMLElement("PreFormattedMailingAddress", hz_format_pub.format_address(hlc.location_id, 'POSTAL_ADDR')), XMLElement("AddressName",hps.party_site_name) ) ), XMLElement("AccountHolderName",ce_owner.party_name), XMLElement("BankAccountCountry",ext_ba.country_code), 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 ) BANK_ACCOUNT_CONTENT, ext_ba.ext_bank_account_id INSTRID

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_b trb, fnd_territories_tl trt, hz_party_sites hps, hz_locations hlc, 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

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 TRUNC(sysdate) BETWEEN branchprofile.effective_start_date AND branchprofile.effective_end_date

AND branchprofile.effective_latest_change = 'Y' AND TRUNC(sysdate) BETWEEN bankprofile.effective_start_date AND bankprofile.effective_end_date AND bankprofile.effective_latest_change = 'Y' 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_INSTITUTION_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 trb.territory_code(+)=ce_bank.country

AND trb.territory_code = trt.territory_code(+)

AND trt.language(+) = USERENV('LANG')

and hps.party_id(+)=ce_branch.party_id and hps.identifying_address_flag(+)='Y' and hps.status(+) = 'A'

and hps.location_id=hlc.location_id(+) and bank_branch_type_lookup.language(+)=USERENV('LANG') 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(+))