IBY_EXT_BA_VAL_RESULTS

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
EXT_BANK_ACCOUNT_ID TEMP_EXT_BANK_ACCT_ID BANK_ACCOUNT_ENTITY_TYPE BA_CREATED_FROM EXT_BANK_ACCT_CHANGE_HIST_ID BANK_ACCT_VERIF_STATUS_CODE BANK_ACCT_VERIF_STATUS_MSG BA_PROVIDER_VERIF_RESP_CODE BA_PROVIDER_VERIF_RESP_MSG BA_INTERNAL_VERIF_RESP_MSG ACCOUNT_OWNER_ID ACCT_OWNER_ENTITY_TYPE ACCOUNT_OWNER_PARTY_ID OWNER_VERIF_STATUS_CODE OWNER_VERIF_STATUS_MSG OWNER_CREAED_FROM OWNER_PROVIDER_VERIF_RESP_CODE OWNER_PROVIDER_VERIF_RESP_MSG OWNER_INTERNAL_VERIF_RESP_MSG FINAL_VERIF_STATUS_CODE

Name

EXT_BANK_ACCOUNT_ID

TEMP_EXT_BANK_ACCT_ID

BANK_ACCOUNT_ENTITY_TYPE

BA_CREATED_FROM

EXT_BANK_ACCT_CHANGE_HIST_ID

BANK_ACCT_VERIF_STATUS_CODE

BANK_ACCT_VERIF_STATUS_MSG

BA_PROVIDER_VERIF_RESP_CODE

BA_PROVIDER_VERIF_RESP_MSG

BA_INTERNAL_VERIF_RESP_MSG

ACCOUNT_OWNER_ID

ACCT_OWNER_ENTITY_TYPE

ACCOUNT_OWNER_PARTY_ID

OWNER_VERIF_STATUS_CODE

OWNER_VERIF_STATUS_MSG

OWNER_CREAED_FROM

OWNER_PROVIDER_VERIF_RESP_CODE

OWNER_PROVIDER_VERIF_RESP_MSG

OWNER_INTERNAL_VERIF_RESP_MSG

FINAL_VERIF_STATUS_CODE

Query

SQL_Statement
SELECT ba.EXT_BANK_ACCOUNT_ID EXT_BANK_ACCOUNT_ID, null TEMP_EXT_BANK_ACCT_ID, 'MAIN' BANK_ACCOUNT_ENTITY_TYPE, ba_h.CREATED_FROM BA_CREATED_FROM, ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID EXT_BANK_ACCT_CHANGE_HIST_ID, ba_h.BANK_ACCT_VERIF_STATUS_CODE BANK_ACCT_VERIF_STATUS_CODE, ba_val_status.MEANING BANK_ACCT_VERIF_STATUS_MSG, ba_h.PROVIDER_VERIF_RESPONSE_CODE BA_PROVIDER_VERIF_RESP_CODE, ba_h.PROVIDER_VERIF_RESPONSE_MSG BA_PROVIDER_VERIF_RESP_MSG, ba_val_msg.MEANING BA_INTERNAL_VERIF_RESP_MSG, owner.ACCOUNT_OWNER_ID ACCOUNT_OWNER_ID, 'MAIN' ACCT_OWNER_ENTITY_TYPE, owner.ACCOUNT_OWNER_PARTY_ID ACCOUNT_OWNER_PARTY_ID, owner_h.OWNER_VERIF_STATUS_CODE OWNER_VERIF_STATUS_CODE, owner_val_status.MEANING OWNER_VERIF_STATUS_MSG, owner_h.CREATED_FROM OWNER_CREAED_FROM, owner_h.PROVIDER_VERIF_RESPONSE_CODE OWNER_PROVIDER_VERIF_RESP_CODE, owner_h.PROVIDER_VERIF_RESPONSE_MSG OWNER_PROVIDER_VERIF_RESP_MSG, owner_val_msg.MEANING OWNER_INTERNAL_VERIF_RESP_MSG, (CASE owner_h.OWNER_VERIF_STATUS_CODE WHEN 'SUCCESS' THEN 'SUCCESS' WHEN 'FAILURE' THEN 'FAILURE' WHEN 'NEUTRAL' THEN DECODE(ba_h.BANK_ACCT_VERIF_STATUS_CODE,'FAILURE','FAILURE','NEUTRAL') ELSE NULL END) FINAL_VERIF_STATUS_CODE FROM IBY_EXT_BANK_ACCOUNTS ba, IBY_ACCOUNT_OWNERS owner, IBY_EXT_BANK_ACCT_CHANGE_HIST ba_h, IBY_BANK_ACCT_OWNER_CHNG_HIST owner_h, FND_LOOKUPS ba_val_status, FND_LOOKUPS owner_val_status, FND_LOOKUPS ba_val_msg, FND_LOOKUPS owner_val_msg WHERE ba.EXT_BANK_ACCOUNT_ID = owner.EXT_BANK_ACCOUNT_ID AND ba.LAST_CHANGE_HIST_ID = ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID(+) AND ba.EXT_BANK_ACCOUNT_ID = ba_h.EXT_BANK_ACCOUNT_ID(+) AND owner.LAST_CHANGE_HIST_ID = owner_h.ACCT_OWNER_CHANGE_HISTORY_ID(+) AND ba_val_status.LOOKUP_TYPE(+) = 'ORA_IBY_BANKACCT_VERIF_STATUS' AND ba_val_status.LOOKUP_CODE(+) = ba.BANK_ACCT_VERIF_STATUS_CODE AND owner_val_status.LOOKUP_CODE(+) = owner.OWNER_VERIF_STATUS_CODE AND owner_val_status.LOOKUP_TYPE(+) = 'ORA_IBY_BA_OWNER_VERIF_STATUS' AND ba_val_msg.LOOKUP_TYPE(+) = 'ORA_IBY_INTRNL_BA_VERIF_RESP' AND owner_val_msg.LOOKUP_TYPE(+) = 'ORA_IBY_INTRNL_OWNER_VERIF_RES' AND ba_val_msg.LOOKUP_CODE(+) = ba_h.INTERNAL_ACCT_VERIF_RESP_CODE AND owner_val_msg.LOOKUP_CODE(+) = owner_h.INTERNAL_OWNER_VERIF_RESP_CODE UNION ALL SELECT tmp_ba.EXT_BANK_ACCOUNT_ID EXT_BANK_ACCOUNT_ID, tmp_ba.TEMP_EXT_BANK_ACCT_ID TEMP_EXT_BANK_ACCT_ID, 'TEMP' BANK_ACCOUNT_ENTITY_TYPE, ba_h.CREATED_FROM BA_CREATED_FROM, ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID EXT_BANK_ACCT_CHANGE_HIST_ID, ba_h.BANK_ACCT_VERIF_STATUS_CODE BANK_ACCT_VERIF_STATUS_CODE, ba_val_status.MEANING BANK_ACCT_VERIF_STATUS_MSG, ba_h.PROVIDER_VERIF_RESPONSE_CODE BA_PROVIDER_VERIF_RESP_CODE, ba_h.PROVIDER_VERIF_RESPONSE_MSG BA_PROVIDER_VERIF_RESP_MSG, ba_val_msg.MEANING BA_INTERNAL_VERIF_RESP_MSG, owner_h.ACCOUNT_OWNER_ID ACCOUNT_OWNER_ID, ACCT_OWNER_ENTITY_TYPE_CODE ACCT_OWNER_ENTITY_TYPE, owner_h.ACCOUNT_OWNER_PARTY_ID ACCOUNT_OWNER_PARTY_ID, owner_h.OWNER_VERIF_STATUS_CODE OWNER_VERIF_STATUS_CODE, owner_val_status.MEANING OWNER_VERIF_STATUS_MSG, owner_h.CREATED_FROM OWNER_CREAED_FROM, owner_h.PROVIDER_VERIF_RESPONSE_CODE OWNER_PROVIDER_VERIF_RESP_CODE, owner_h.PROVIDER_VERIF_RESPONSE_MSG OWNER_PROVIDER_VERIF_RESP_MSG, owner_val_msg.MEANING OWNER_INTERNAL_VERIF_RESP_MSG, (CASE owner_h.OWNER_VERIF_STATUS_CODE WHEN 'SUCCESS' THEN 'SUCCESS' WHEN 'FAILURE' THEN 'FAILURE' WHEN 'NEUTRAL' THEN DECODE(ba_h.BANK_ACCT_VERIF_STATUS_CODE,'FAILURE','FAILURE','NEUTRAL') ELSE NULL END) FINAL_VERIF_STATUS_CODE FROM IBY_TEMP_EXT_BANK_ACCTS tmp_ba, IBY_EXT_BANK_ACCT_CHANGE_HIST ba_h, IBY_BANK_ACCT_OWNER_CHNG_HIST owner_h, FND_LOOKUPS ba_val_status, FND_LOOKUPS owner_val_status, FND_LOOKUPS ba_val_msg, FND_LOOKUPS owner_val_msg WHERE tmp_ba.TEMP_EXT_BANK_ACCT_ID = ba_h.EXT_BANK_ACCOUNT_ID(+) AND ba_h.BANK_ACCOUNT_ENTITY_TYPE_CODE = 'TEMP' AND owner_h.EXT_BANK_ACCT_CHANGE_HIST_ID = ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID AND ba_val_status.LOOKUP_TYPE(+) = 'ORA_IBY_BANKACCT_VERIF_STATUS' AND ba_val_status.LOOKUP_CODE(+) = ba_h.BANK_ACCT_VERIF_STATUS_CODE AND owner_val_status.LOOKUP_CODE(+) = owner_h.OWNER_VERIF_STATUS_CODE AND owner_val_status.LOOKUP_TYPE(+) = 'ORA_IBY_BA_OWNER_VERIF_STATUS' AND ba_val_msg.LOOKUP_TYPE(+) = 'ORA_IBY_INTRNL_BA_VERIF_RESP' AND owner_val_msg.LOOKUP_TYPE(+) = 'ORA_IBY_INTRNL_OWNER_VERIF_RES' AND ba_val_msg.LOOKUP_CODE(+) = ba_h.INTERNAL_ACCT_VERIF_RESP_CODE AND owner_val_msg.LOOKUP_CODE(+) = owner_h.INTERNAL_OWNER_VERIF_RESP_CODE

SQL_Statement

SELECT ba.EXT_BANK_ACCOUNT_ID EXT_BANK_ACCOUNT_ID,

null TEMP_EXT_BANK_ACCT_ID,

'MAIN' BANK_ACCOUNT_ENTITY_TYPE,

ba_h.CREATED_FROM BA_CREATED_FROM,

ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID EXT_BANK_ACCT_CHANGE_HIST_ID,

ba_h.BANK_ACCT_VERIF_STATUS_CODE BANK_ACCT_VERIF_STATUS_CODE,

ba_val_status.MEANING BANK_ACCT_VERIF_STATUS_MSG,

ba_h.PROVIDER_VERIF_RESPONSE_CODE BA_PROVIDER_VERIF_RESP_CODE,

ba_h.PROVIDER_VERIF_RESPONSE_MSG BA_PROVIDER_VERIF_RESP_MSG,

ba_val_msg.MEANING BA_INTERNAL_VERIF_RESP_MSG,

owner.ACCOUNT_OWNER_ID ACCOUNT_OWNER_ID,

'MAIN' ACCT_OWNER_ENTITY_TYPE,

owner.ACCOUNT_OWNER_PARTY_ID ACCOUNT_OWNER_PARTY_ID,

owner_h.OWNER_VERIF_STATUS_CODE OWNER_VERIF_STATUS_CODE,

owner_val_status.MEANING OWNER_VERIF_STATUS_MSG,

owner_h.CREATED_FROM OWNER_CREAED_FROM,

owner_h.PROVIDER_VERIF_RESPONSE_CODE OWNER_PROVIDER_VERIF_RESP_CODE,

owner_h.PROVIDER_VERIF_RESPONSE_MSG OWNER_PROVIDER_VERIF_RESP_MSG,

owner_val_msg.MEANING OWNER_INTERNAL_VERIF_RESP_MSG,

(CASE owner_h.OWNER_VERIF_STATUS_CODE

WHEN 'SUCCESS' THEN 'SUCCESS'

WHEN 'FAILURE' THEN 'FAILURE'

WHEN 'NEUTRAL' THEN DECODE(ba_h.BANK_ACCT_VERIF_STATUS_CODE,'FAILURE','FAILURE','NEUTRAL')

ELSE NULL

END) FINAL_VERIF_STATUS_CODE

FROM IBY_EXT_BANK_ACCOUNTS ba,

IBY_ACCOUNT_OWNERS owner,

IBY_EXT_BANK_ACCT_CHANGE_HIST ba_h,

IBY_BANK_ACCT_OWNER_CHNG_HIST owner_h,

FND_LOOKUPS ba_val_status,

FND_LOOKUPS owner_val_status,

FND_LOOKUPS ba_val_msg,

FND_LOOKUPS owner_val_msg

WHERE ba.EXT_BANK_ACCOUNT_ID = owner.EXT_BANK_ACCOUNT_ID

AND ba.LAST_CHANGE_HIST_ID = ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID(+)

AND ba.EXT_BANK_ACCOUNT_ID = ba_h.EXT_BANK_ACCOUNT_ID(+)

AND owner.LAST_CHANGE_HIST_ID = owner_h.ACCT_OWNER_CHANGE_HISTORY_ID(+)

AND ba_val_status.LOOKUP_TYPE(+) = 'ORA_IBY_BANKACCT_VERIF_STATUS'

AND ba_val_status.LOOKUP_CODE(+) = ba.BANK_ACCT_VERIF_STATUS_CODE

AND owner_val_status.LOOKUP_CODE(+) = owner.OWNER_VERIF_STATUS_CODE

AND owner_val_status.LOOKUP_TYPE(+) = 'ORA_IBY_BA_OWNER_VERIF_STATUS'

AND ba_val_msg.LOOKUP_TYPE(+) = 'ORA_IBY_INTRNL_BA_VERIF_RESP'

AND owner_val_msg.LOOKUP_TYPE(+) = 'ORA_IBY_INTRNL_OWNER_VERIF_RES'

AND ba_val_msg.LOOKUP_CODE(+) = ba_h.INTERNAL_ACCT_VERIF_RESP_CODE

AND owner_val_msg.LOOKUP_CODE(+) = owner_h.INTERNAL_OWNER_VERIF_RESP_CODE

UNION ALL

SELECT tmp_ba.EXT_BANK_ACCOUNT_ID EXT_BANK_ACCOUNT_ID,

tmp_ba.TEMP_EXT_BANK_ACCT_ID TEMP_EXT_BANK_ACCT_ID,

'TEMP' BANK_ACCOUNT_ENTITY_TYPE,

ba_h.CREATED_FROM BA_CREATED_FROM,

ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID EXT_BANK_ACCT_CHANGE_HIST_ID,

ba_h.BANK_ACCT_VERIF_STATUS_CODE BANK_ACCT_VERIF_STATUS_CODE,

ba_val_status.MEANING BANK_ACCT_VERIF_STATUS_MSG,

ba_h.PROVIDER_VERIF_RESPONSE_CODE BA_PROVIDER_VERIF_RESP_CODE,

ba_h.PROVIDER_VERIF_RESPONSE_MSG BA_PROVIDER_VERIF_RESP_MSG,

ba_val_msg.MEANING BA_INTERNAL_VERIF_RESP_MSG,

owner_h.ACCOUNT_OWNER_ID ACCOUNT_OWNER_ID,

ACCT_OWNER_ENTITY_TYPE_CODE ACCT_OWNER_ENTITY_TYPE,

owner_h.ACCOUNT_OWNER_PARTY_ID ACCOUNT_OWNER_PARTY_ID,

owner_h.OWNER_VERIF_STATUS_CODE OWNER_VERIF_STATUS_CODE,

owner_val_status.MEANING OWNER_VERIF_STATUS_MSG,

owner_h.CREATED_FROM OWNER_CREAED_FROM,

owner_h.PROVIDER_VERIF_RESPONSE_CODE OWNER_PROVIDER_VERIF_RESP_CODE,

owner_h.PROVIDER_VERIF_RESPONSE_MSG OWNER_PROVIDER_VERIF_RESP_MSG,

owner_val_msg.MEANING OWNER_INTERNAL_VERIF_RESP_MSG,

(CASE owner_h.OWNER_VERIF_STATUS_CODE

WHEN 'SUCCESS' THEN 'SUCCESS'

WHEN 'FAILURE' THEN 'FAILURE'

WHEN 'NEUTRAL' THEN DECODE(ba_h.BANK_ACCT_VERIF_STATUS_CODE,'FAILURE','FAILURE','NEUTRAL')

ELSE NULL

END) FINAL_VERIF_STATUS_CODE

FROM IBY_TEMP_EXT_BANK_ACCTS tmp_ba,

IBY_EXT_BANK_ACCT_CHANGE_HIST ba_h,

IBY_BANK_ACCT_OWNER_CHNG_HIST owner_h,

FND_LOOKUPS ba_val_status,

FND_LOOKUPS owner_val_status,

FND_LOOKUPS ba_val_msg,

FND_LOOKUPS owner_val_msg

WHERE tmp_ba.TEMP_EXT_BANK_ACCT_ID = ba_h.EXT_BANK_ACCOUNT_ID(+)

AND ba_h.BANK_ACCOUNT_ENTITY_TYPE_CODE = 'TEMP'

AND owner_h.EXT_BANK_ACCT_CHANGE_HIST_ID = ba_h.EXT_BANK_ACCT_CHANGE_HIST_ID

AND ba_val_status.LOOKUP_TYPE(+) = 'ORA_IBY_BANKACCT_VERIF_STATUS'

AND ba_val_status.LOOKUP_CODE(+) = ba_h.BANK_ACCT_VERIF_STATUS_CODE

AND owner_val_status.LOOKUP_CODE(+) = owner_h.OWNER_VERIF_STATUS_CODE

AND owner_val_status.LOOKUP_TYPE(+) = 'ORA_IBY_BA_OWNER_VERIF_STATUS'

AND ba_val_msg.LOOKUP_TYPE(+) = 'ORA_IBY_INTRNL_BA_VERIF_RESP'

AND owner_val_msg.LOOKUP_TYPE(+) = 'ORA_IBY_INTRNL_OWNER_VERIF_RES'

AND ba_val_msg.LOOKUP_CODE(+) = ba_h.INTERNAL_ACCT_VERIF_RESP_CODE

AND owner_val_msg.LOOKUP_CODE(+) = owner_h.INTERNAL_OWNER_VERIF_RESP_CODE