IBY_XML_FD_VAT_BAM_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", 'XXXX'), XMLElement("BankName", 'XXXX'), XMLElement("AlternateBankName", 'XXXX'), XMLElement("BankNumber", 'XXXX'), XMLElement("BranchInternalID", 'XXXX'), XMLElement("BranchName", 'XXXX'), XMLElement("AlternateBranchName", 'XXXX'), XMLElement("BranchNumber", 'XXXX'), XMLElement("BankCode", 'XXXX'), XMLElement("BankAccountName", 'XXXX'), XMLElement("AlternateBankAccountName", 'XXXX'), XMLElement("BankAccountNumber", 'XXXX'), XMLElement("MaskedBankAccountNumber", 'XXXX'), XMLElement("UserEnteredBankAccountNumber", 'XXXX'), XMLElement("BankAccountSuffix",'XXXX'), XMLElement("SwiftCode", 'XXXX'), XMLElement("IBANNumber", 'XXXX'), XMLElement("CheckDigits", 'XXXX'), XMLELEMENT("SecondaryBankAccountReference", 'XXXX'), XMLElement("BankAccountType", XMLElement("Code", 'XXXX'), XMLElement("Meaning", 'XXXX') ), XMLElement("BankAccountCurrency", XMLElement("Code", 'XXXX') ), XMLElement("BankAddress", IBY_FD_EXTRACT_GEN_PVT.Get_account_Address(hzps.location_id, ext_ba.country_code)), XMLElement("PrimaryOwner", XMLElement("Name", 'XXXX') ), XMLElement("FactorAccount", XMLElement("Code", 'XXXX'), XMLElement("Meaning", 'XXXX') ),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, hz_party_sites hzps, fnd_lookups bank_account_type_lookup, fnd_lookups factor_lookup WHERE ext_ba.branch_id = hzps.party_id(+) and hzps.identifying_address_flag(+) = 'Y' and hzps.status(+) = 'A' 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(+)

SQL_Statement

SELECT XMLConcat( XMLElement("BankAccountInternalID", 'XXXX'), XMLElement("BankName", 'XXXX'), XMLElement("AlternateBankName", 'XXXX'), XMLElement("BankNumber", 'XXXX'), XMLElement("BranchInternalID", 'XXXX'), XMLElement("BranchName", 'XXXX'), XMLElement("AlternateBranchName", 'XXXX'), XMLElement("BranchNumber", 'XXXX'), XMLElement("BankCode", 'XXXX'), XMLElement("BankAccountName", 'XXXX'), XMLElement("AlternateBankAccountName", 'XXXX'), XMLElement("BankAccountNumber", 'XXXX'), XMLElement("MaskedBankAccountNumber", 'XXXX'), XMLElement("UserEnteredBankAccountNumber", 'XXXX'), XMLElement("BankAccountSuffix",'XXXX'), XMLElement("SwiftCode", 'XXXX'), XMLElement("IBANNumber", 'XXXX'), XMLElement("CheckDigits", 'XXXX'), XMLELEMENT("SecondaryBankAccountReference", 'XXXX'), XMLElement("BankAccountType", XMLElement("Code", 'XXXX'), XMLElement("Meaning", 'XXXX') ), XMLElement("BankAccountCurrency", XMLElement("Code", 'XXXX') ), XMLElement("BankAddress", IBY_FD_EXTRACT_GEN_PVT.Get_account_Address(hzps.location_id, ext_ba.country_code)), XMLElement("PrimaryOwner", XMLElement("Name", 'XXXX') ), XMLElement("FactorAccount", XMLElement("Code", 'XXXX'), XMLElement("Meaning", 'XXXX') ),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, hz_party_sites hzps, fnd_lookups bank_account_type_lookup, fnd_lookups factor_lookup WHERE ext_ba.branch_id = hzps.party_id(+) and hzps.identifying_address_flag(+) = 'Y' and hzps.status(+) = 'A' 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(+)