VIEW: APPS.IBY_EXTN_INSTR_DETAILS_V

Source

View Text - Preformatted

SELECT x.rowid, x.trxn_extension_id, p.payment_channel_code, pt.payment_channel_name, x.instr_assignment_id, sub_q.instrument_type, sub_q.instrument_id, sub_q.currency_code, sub_q.masked_cc_number, sub_q.cc_number_hash1, sub_q.cc_number_hash2, decode(sub_q.encrypted, 'A',null,sub_q.expirydate), decode(sub_q.encrypted, 'A', 'XX/XX', to_char(sub_q.expirydate, 'mm/yy')), DECODE(sub_q.expirydate, null, sub_q.expired_flag, decode(sign(sub_q.expirydate-sysdate),-1,'Y','N')), sub_q.card_issuer_code, sub_q.card_issuer_name, sub_q.purchasecard_subtype, sub_q.CARD_HOLDER_NAME, nvl(sub_q.encrypted, 'N'), (select lkp.meaning from fnd_lookups lkp where lkp.lookup_type = 'IBY_CARD_EXPIRATION_STATUS' and (decode(DECODE(sub_q.expirydate, null, sub_q.expired_flag, decode(sign(sub_q.expirydate-sysdate),-1,'Y','N')),'Y', 'EXPIRED','N','UNEXPIRED')) = lkp.lookup_code), sub_q.masked_bank_account_num, sub_q.bank_or_branch_number, DECODE( DECODE(x.instrument_security_code, NULL, NULL, x.instr_sec_code_length), NULL, NULL, 1, 'X', 2, 'XX', 3, 'XXX', 4, 'XXXX', 'XXXXX') INSTRUMENT_SECURITY_CODE, x.voice_authorization_flag, x.voice_authorization_date, x.voice_authorization_code, x.origin_application_id, x.order_id, x.po_number, x.po_line_number, x.trxn_ref_number1, x.trxn_ref_number2, NVL(x.payment_system_order_number, SUBSTR(iby_fndcpt_trxn_pub.get_tangible_id(a.application_short_name, x.order_id, x.trxn_ref_number1, x.trxn_ref_number2 ), 1, 80)), x.additional_info, sub_q.BANK_NAME, sub_q.BANK_BRANCH_NAME FROM iby_fndcpt_tx_extensions x, fnd_application a, iby_fndcpt_pmt_chnnls_b p , iby_fndcpt_pmt_chnnls_tl pt , (SELECT u.instrument_type, u.instrument_id, null currency_code, c.masked_cc_number, c.cc_number_hash1, c.cc_number_hash2, c.card_issuer_code, c.encrypted, c.expirydate, c.expired_flag, it.card_issuer_name, c.purchasecard_subtype, NVL(c.chname, hzp.party_name) CARD_HOLDER_NAME, null masked_bank_account_num, null bank_or_branch_number, null BANK_NAME, null BANK_BRANCH_NAME, u.instrument_payment_use_id FROM iby_creditcard c , iby_creditcard_issuers_b i , iby_creditcard_issuers_tl it , iby_pmt_instr_uses_all u , hz_parties hzp WHERE u.instrument_type = 'CREDITCARD' AND u.instrument_id = c.instrid AND c.card_issuer_code = i.card_issuer_code(+) AND c.card_owner_id = hzp.party_id(+) AND I.card_issuer_code=IT.card_issuer_code(+) AND IT.language(+)=USERENV('LANG') UNION ALL SELECT u.instrument_type, u.instrument_id, b.currency_code, null masked_cc_number, null cc_number_hash1, null cc_number_hash2, null card_issuer_code, b.encrypted, null expirydate, null expired_flag, null card_issuer_name, null purchasecard_subtype, null CARD_HOLDER_NAME, b.masked_bank_account_num, bankprofile.bank_or_branch_number, bhzp.party_name BANK_NAME, brhzp.party_name BANK_BRANCH_NAME, u.instrument_payment_use_id FROM iby_ext_bank_accounts b , iby_pmt_instr_uses_all u , hz_parties bhzp , hz_parties brhzp , hz_organization_profiles bankprofile WHERE u.instrument_type = 'BANKACCOUNT' AND u.instrument_id = b.ext_bank_account_id AND b.bank_id=bhzp.party_id(+) AND b.branch_id=brhzp.party_id(+) AND bhzp.party_id=bankProfile.party_id(+) AND sysdate BETWEEN TRUNC(bankprofile.effective_start_date(+)) AND NVL(TRUNC(bankprofile.effective_end_date(+)), sysdate + 1) ) sub_q WHERE x.origin_application_id = a.application_id AND x.instr_assignment_id = sub_q.instrument_payment_use_id(+) AND x.payment_channel_code = p.payment_channel_code AND P.payment_channel_code=PT.payment_channel_code AND PT.language=USERENV('LANG')

View Text - HTML Formatted

SELECT X.ROWID
, X.TRXN_EXTENSION_ID
, P.PAYMENT_CHANNEL_CODE
, PT.PAYMENT_CHANNEL_NAME
, X.INSTR_ASSIGNMENT_ID
, SUB_Q.INSTRUMENT_TYPE
, SUB_Q.INSTRUMENT_ID
, SUB_Q.CURRENCY_CODE
, SUB_Q.MASKED_CC_NUMBER
, SUB_Q.CC_NUMBER_HASH1
, SUB_Q.CC_NUMBER_HASH2
, DECODE(SUB_Q.ENCRYPTED
, 'A'
, NULL
, SUB_Q.EXPIRYDATE)
, DECODE(SUB_Q.ENCRYPTED
, 'A'
, 'XX/XX'
, TO_CHAR(SUB_Q.EXPIRYDATE
, 'MM/YY'))
, DECODE(SUB_Q.EXPIRYDATE
, NULL
, SUB_Q.EXPIRED_FLAG
, DECODE(SIGN(SUB_Q.EXPIRYDATE-SYSDATE)
, -1
, 'Y'
, 'N'))
, SUB_Q.CARD_ISSUER_CODE
, SUB_Q.CARD_ISSUER_NAME
, SUB_Q.PURCHASECARD_SUBTYPE
, SUB_Q.CARD_HOLDER_NAME
, NVL(SUB_Q.ENCRYPTED
, 'N')
, (SELECT LKP.MEANING
FROM FND_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = 'IBY_CARD_EXPIRATION_STATUS'
AND (DECODE(DECODE(SUB_Q.EXPIRYDATE
, NULL
, SUB_Q.EXPIRED_FLAG
, DECODE(SIGN(SUB_Q.EXPIRYDATE-SYSDATE)
, -1
, 'Y'
, 'N'))
, 'Y'
, 'EXPIRED'
, 'N'
, 'UNEXPIRED')) = LKP.LOOKUP_CODE)
, SUB_Q.MASKED_BANK_ACCOUNT_NUM
, SUB_Q.BANK_OR_BRANCH_NUMBER
, DECODE( DECODE(X.INSTRUMENT_SECURITY_CODE
, NULL
, NULL
, X.INSTR_SEC_CODE_LENGTH)
, NULL
, NULL
, 1
, 'X'
, 2
, 'XX'
, 3
, 'XXX'
, 4
, 'XXXX'
, 'XXXXX') INSTRUMENT_SECURITY_CODE
, X.VOICE_AUTHORIZATION_FLAG
, X.VOICE_AUTHORIZATION_DATE
, X.VOICE_AUTHORIZATION_CODE
, X.ORIGIN_APPLICATION_ID
, X.ORDER_ID
, X.PO_NUMBER
, X.PO_LINE_NUMBER
, X.TRXN_REF_NUMBER1
, X.TRXN_REF_NUMBER2
, NVL(X.PAYMENT_SYSTEM_ORDER_NUMBER
, SUBSTR(IBY_FNDCPT_TRXN_PUB.GET_TANGIBLE_ID(A.APPLICATION_SHORT_NAME
, X.ORDER_ID
, X.TRXN_REF_NUMBER1
, X.TRXN_REF_NUMBER2 )
, 1
, 80))
, X.ADDITIONAL_INFO
, SUB_Q.BANK_NAME
, SUB_Q.BANK_BRANCH_NAME
FROM IBY_FNDCPT_TX_EXTENSIONS X
, FND_APPLICATION A
, IBY_FNDCPT_PMT_CHNNLS_B P
, IBY_FNDCPT_PMT_CHNNLS_TL PT
, (SELECT U.INSTRUMENT_TYPE
, U.INSTRUMENT_ID
, NULL CURRENCY_CODE
, C.MASKED_CC_NUMBER
, C.CC_NUMBER_HASH1
, C.CC_NUMBER_HASH2
, C.CARD_ISSUER_CODE
, C.ENCRYPTED
, C.EXPIRYDATE
, C.EXPIRED_FLAG
, IT.CARD_ISSUER_NAME
, C.PURCHASECARD_SUBTYPE
, NVL(C.CHNAME
, HZP.PARTY_NAME) CARD_HOLDER_NAME
, NULL MASKED_BANK_ACCOUNT_NUM
, NULL BANK_OR_BRANCH_NUMBER
, NULL BANK_NAME
, NULL BANK_BRANCH_NAME
, U.INSTRUMENT_PAYMENT_USE_ID
FROM IBY_CREDITCARD C
, IBY_CREDITCARD_ISSUERS_B I
, IBY_CREDITCARD_ISSUERS_TL IT
, IBY_PMT_INSTR_USES_ALL U
, HZ_PARTIES HZP
WHERE U.INSTRUMENT_TYPE = 'CREDITCARD'
AND U.INSTRUMENT_ID = C.INSTRID
AND C.CARD_ISSUER_CODE = I.CARD_ISSUER_CODE(+)
AND C.CARD_OWNER_ID = HZP.PARTY_ID(+)
AND I.CARD_ISSUER_CODE=IT.CARD_ISSUER_CODE(+)
AND IT.LANGUAGE(+)=USERENV('LANG') UNION ALL SELECT U.INSTRUMENT_TYPE
, U.INSTRUMENT_ID
, B.CURRENCY_CODE
, NULL MASKED_CC_NUMBER
, NULL CC_NUMBER_HASH1
, NULL CC_NUMBER_HASH2
, NULL CARD_ISSUER_CODE
, B.ENCRYPTED
, NULL EXPIRYDATE
, NULL EXPIRED_FLAG
, NULL CARD_ISSUER_NAME
, NULL PURCHASECARD_SUBTYPE
, NULL CARD_HOLDER_NAME
, B.MASKED_BANK_ACCOUNT_NUM
, BANKPROFILE.BANK_OR_BRANCH_NUMBER
, BHZP.PARTY_NAME BANK_NAME
, BRHZP.PARTY_NAME BANK_BRANCH_NAME
, U.INSTRUMENT_PAYMENT_USE_ID
FROM IBY_EXT_BANK_ACCOUNTS B
, IBY_PMT_INSTR_USES_ALL U
, HZ_PARTIES BHZP
, HZ_PARTIES BRHZP
, HZ_ORGANIZATION_PROFILES BANKPROFILE
WHERE U.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND U.INSTRUMENT_ID = B.EXT_BANK_ACCOUNT_ID
AND B.BANK_ID=BHZP.PARTY_ID(+)
AND B.BRANCH_ID=BRHZP.PARTY_ID(+)
AND BHZP.PARTY_ID=BANKPROFILE.PARTY_ID(+)
AND SYSDATE BETWEEN TRUNC(BANKPROFILE.EFFECTIVE_START_DATE(+))
AND NVL(TRUNC(BANKPROFILE.EFFECTIVE_END_DATE(+))
, SYSDATE + 1) ) SUB_Q
WHERE X.ORIGIN_APPLICATION_ID = A.APPLICATION_ID
AND X.INSTR_ASSIGNMENT_ID = SUB_Q.INSTRUMENT_PAYMENT_USE_ID(+)
AND X.PAYMENT_CHANNEL_CODE = P.PAYMENT_CHANNEL_CODE
AND P.PAYMENT_CHANNEL_CODE=PT.PAYMENT_CHANNEL_CODE
AND PT.LANGUAGE=USERENV('LANG')