1. Tables and Views for Financials
  2. IBY_XML_TEMP_BA_VERIF_1_0_V

IBY_XML_TEMP_BA_VERIF_1_0_V

Details

  • Schema: FUSION

  • Object owner: IBY

  • Object type: VIEW

Columns

Name

EXT_BANK_ACCT_CHANGE_HIST_ID

ACCT_OWNER_CHANGE_HISTORY_ID

BANK_ACCT_OWNER_VERIF_REQUEST

Query

SQL_Statement

SELECT ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID EXT_BANK_ACCT_CHANGE_HIST_ID, ownr_h.ACCT_OWNER_CHANGE_HISTORY_ID ACCT_OWNER_CHANGE_HISTORY_ID,XMLELEMENT("BankAccountVerificationRequest",(XMLELEMENT("RequestId",ownr_h.VERIFICATION_REQ_REFERENCE_ID)),xmlelement("PaymentSystemAccount", xmlelement("AccountName", 'psa'), iby_fd_extract_gen_pvt.get_ins_accountsettingsagg(ownr_h.payment_system_account_id)),

XMLELEMENT("BankAccount",XMLELEMENT("BankAccountNumber",iby_ext_bankacct_pub.Uncipher_User_Bank_Account_Num_Hist(ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID,iby_utility_pvt.get_view_param('SYS_KEY'))),XMLELEMENT("IBAN",iby_ext_bankacct_pub.Uncipher_IBAN_Hist(ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID,iby_utility_pvt.get_view_param('SYS_KEY'))),XMLELEMENT("BankAccountNumberElectronic",iby_ext_bankacct_pub.Uncipher_Bank_Account_Number_Hist(ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID,iby_utility_pvt.get_view_param('SYS_KEY'))),XMLELEMENT("BankName",branch.BANK_NAME),XMLELEMENT("BankNumber",branch.BANK_NUMBER),XMLELEMENT("BranchName",branch.BANK_BRANCH_NAME),XMLELEMENT("BranchNumber",branch.BRANCH_NUMBER),XMLELEMENT("EftSwiftCode",branch.EFT_SWIFT_CODE),XMLELEMENT("BranchType",branch.BANK_BRANCH_TYPE)),

XMLELEMENT("BankAccountOwner",XMLELEMENT("AccountOwnerName",NVL(iby_utility_pvt.get_view_param('OWNER_NAME'), op.PARTY_NAME)),XMLELEMENT("OwnerFirstName",NVL(iby_utility_pvt.get_view_param('OWNER_FIRSTNAME'), op.PERSON_FIRST_NAME)),XMLELEMENT("OwnerLastName",NVL(iby_utility_pvt.get_view_param('OWNER_LASTNAME'), op.PERSON_LAST_NAME)),XMLELEMENT("OwnerPartyType",NVL(iby_utility_pvt.get_view_param('OWNER_TYPE'), op.PARTY_TYPE)), XMLELEMENT("OwnerTaxId",iby_utility_pvt.get_view_param('OWNER_TAX_ID')), XMLELEMENT("PrimaryFlag",ownr_h.primary_flag))) BANK_ACCT_OWNER_VERIF_REQUEST

FROM iby_ext_bank_acct_change_hist ba_h,

iby_bank_acct_owner_chng_hist ownr_h,

iby_temp_ext_bank_accts temp_ba,

hz_parties op,

ce_all_bank_branches_v branch

WHERE temp_ba.temp_ext_bank_acct_id = ba_h.ext_bank_account_id

AND ba_h.BANK_ACCOUNT_ENTITY_TYPE_CODE = 'TEMP'

AND ownr_h.EXT_BANK_ACCT_CHANGE_HIST_ID = ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID

AND ownr_h.account_owner_party_id = op.party_id(+)

AND ba_h.branch_id = branch.branch_party_id(+)