eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IBY_TRXN_EXTENSIONS_V

Source

View Text - Preformatted


SELECT extn.rowid, extn.trxn_extension_id, p.payment_channel_code, pt.payment_channel_name, extn.instr_assignment_id, nvl(u.instrument_type, p.instrument_type), u.instrument_id, b.currency_code, c.masked_cc_number, c.cc_number_hash1, c.cc_number_hash2, decode(c.encrypted, 'A',to_date(null),c.expirydate), decode(c.encrypted,'A','XX/XX',to_char(c.expirydate, 'mm/yy')), DECODE(c.expirydate, null, c.expired_flag, decode(sign(c.expirydate-sysdate),-1,'Y','N')), c.card_issuer_code, it.card_issuer_name, c.purchasecard_subtype, nvl(c.chname, hzp.party_name), b.masked_bank_account_num, bankprofile.bank_or_branch_number, decode(decode(extn.instrument_security_code, NULL, NULL, extn.instr_sec_code_length), NULL, NULL, 1, 'X', 2, 'XX', 3, 'XXX', 4, 'XXXX', 'XXXXX'), extn.voice_authorization_flag, extn.voice_authorization_date, extn.voice_authorization_code, extn.origin_application_id, extn.order_id, extn.po_number, extn.po_line_number, extn.trxn_ref_number1, extn.trxn_ref_number2, nvl(extn.payment_system_order_number, SUBSTR(iby_fndcpt_trxn_pub.get_tangible_id(a.application_short_name, extn.order_id, extn.trxn_ref_number1, extn.trxn_ref_number2), 1, 80)), extn.additional_info, decode(authtxn.status, NULL, 'N', 'Y'), decode(authtxn.status, 0, 'AUTH_SUCCESS', 100, 'AUTH_PENDING', 111, 'AUTH_PENDING', NULL), decode(settletxn.status, NULL, 'N', 'Y'), decode(settletxn.status, 0, 'SETTLEMENT_SUCCESS', 100, 'SETTLEMENT_PENDING', 111, 'SETTLEMENT_PENDING', 11, 'SETTLEMENT_PENDING', NULL), decode(returntxn.status, NULL, 'N', 'Y'), decode(returntxn.status, 0, 'RETURN_SUCCESS', 100, 'RETURN_PENDING', 111, 'RETURN_PENDING', NULL), bhzp.party_name, brhzp.party_name, DECODE(u.instrument_type,'CREDITCARD', NVL(c.encrypted, 'N'), NVL(b.encrypted, 'N')), (select lkp.meaning from fnd_lookups lkp where lkp.lookup_type = 'IBY_CARD_EXPIRATION_STATUS' and (decode(DECODE(c.expirydate, null, c.expired_flag, decode(sign(c.expirydate-sysdate),-1,'Y','N')),'Y', 'EXPIRED','N','UNEXPIRED')) = lkp.lookup_code), extn.SEQ_TYPE_LAST FROM iby_creditcard c, iby_creditcard_issuers_b i, iby_creditcard_issuers_tl it, iby_ext_bank_accounts b, iby_fndcpt_pmt_chnnls_b p, iby_fndcpt_pmt_chnnls_tl pt, iby_fndcpt_tx_extensions extn, iby_pmt_instr_uses_all u, hz_parties hzp, fnd_application a, hz_parties bhzp, hz_parties brhzp, hz_organization_profiles bankprofile, (SELECT op.trxn_extension_id, summ.status FROM iby_trxn_summaries_all summ, iby_fndcpt_tx_operations op WHERE(summ.transactionid = op.transactionid) AND(reqtype = 'ORAPMTREQ') AND(status IN(0, 100, 111)) AND(op.transactionid = (SELECT MIN(transactionid) FROM iby_fndcpt_tx_operations WHERE trxn_extension_id = op.trxn_extension_id )) AND((trxntypeid IN(2, 3)) OR((trxntypeid = 20) AND(summ.trxnmid = (SELECT MAX(trxnmid) FROM iby_trxn_summaries_all WHERE transactionid = summ.transactionid AND(reqtype = 'ORAPMTREQ') AND(status IN(0, 100, 111)) AND(trxntypeid = 20) )))) ) authtxn, ( Select Op.Trxn_Extension_Id, Summ.Status, Rank() over (PARTITION BY Op.Trxn_Extension_Id ORDER BY op.Transactionid ) rank From Iby_Trxn_Summaries_All Summ, Iby_Fndcpt_Tx_Operations Op Where(Summ.Transactionid = Op.Transactionid) And((Instrtype In ('CREDITCARD', 'PURCHASECARD') And Reqtype = 'ORAPMTCAPTURE') Or(Instrtype = 'BANKACCOUNT' And Trxntypeid Is Null) Or(Instrtype = 'PINLESSDEBITCARD' And Reqtype = 'ORAPMTREQ')) And(Status In(0, 100, 111, 11)) ) settletxn, (SELECT op.trxn_extension_id, summ.status, Rank() over (PARTITION BY Op.Trxn_Extension_Id ORDER BY op.Transactionid ) rank FROM iby_trxn_summaries_all summ, iby_fndcpt_tx_operations op WHERE(summ.transactionid = op.transactionid) AND(reqtype = 'ORAPMTRETURN') AND(status IN(0, 100, 111))) returntxn WHERE (extn.instr_assignment_id = u.instrument_payment_use_id(+)) AND (DECODE(u.instrument_type, 'CREDITCARD',u.instrument_id, NULL) = c.instrid(+)) AND (DECODE(u.instrument_type, 'BANKACCOUNT',u.instrument_id, NULL) = b.ext_bank_account_id(+)) AND (extn.payment_channel_code = p.payment_channel_code) AND (c.card_issuer_code = i.card_issuer_code(+)) AND (c.card_owner_id = hzp.party_id(+)) AND (extn.origin_application_id = a.application_id) AND (b.bank_id=bhzp.party_id(+)) AND (b.branch_id=brhzp.party_id(+)) and (bhzp.party_id=bankProfile.party_id(+)) and (I.card_issuer_code=IT.card_issuer_code(+)) and (IT.language(+)=USERENV('LANG')) and (P.payment_channel_code=PT.payment_channel_code) and (PT.language=USERENV('LANG')) AND (extn.trxn_extension_id = authtxn.trxn_extension_id(+)) AND (extn.trxn_extension_id = settletxn.trxn_extension_id(+)) AND (extn.trxn_extension_id = returntxn.trxn_extension_id(+)) AND sysdate BETWEEN TRUNC(bankprofile.effective_start_date(+)) AND nvl(TRUNC(bankprofile.effective_end_date(+)), sysdate + 1) and Settletxn.rank(+) = 1 AND returntxn.Rank(+) = 1

View Text - HTML Formatted

SELECT EXTN.ROWID
, EXTN.TRXN_EXTENSION_ID
, P.PAYMENT_CHANNEL_CODE
, PT.PAYMENT_CHANNEL_NAME
, EXTN.INSTR_ASSIGNMENT_ID
, NVL(U.INSTRUMENT_TYPE
, P.INSTRUMENT_TYPE)
, U.INSTRUMENT_ID
, B.CURRENCY_CODE
, C.MASKED_CC_NUMBER
, C.CC_NUMBER_HASH1
, C.CC_NUMBER_HASH2
, DECODE(C.ENCRYPTED
, 'A'
, TO_DATE(NULL)
, C.EXPIRYDATE)
, DECODE(C.ENCRYPTED
, 'A'
, 'XX/XX'
, TO_CHAR(C.EXPIRYDATE
, 'MM/YY'))
, DECODE(C.EXPIRYDATE
, NULL
, C.EXPIRED_FLAG
, DECODE(SIGN(C.EXPIRYDATE-SYSDATE)
, -1
, 'Y'
, 'N'))
, C.CARD_ISSUER_CODE
, IT.CARD_ISSUER_NAME
, C.PURCHASECARD_SUBTYPE
, NVL(C.CHNAME
, HZP.PARTY_NAME)
, B.MASKED_BANK_ACCOUNT_NUM
, BANKPROFILE.BANK_OR_BRANCH_NUMBER
, DECODE(DECODE(EXTN.INSTRUMENT_SECURITY_CODE
, NULL
, NULL
, EXTN.INSTR_SEC_CODE_LENGTH)
, NULL
, NULL
, 1
, 'X'
, 2
, 'XX'
, 3
, 'XXX'
, 4
, 'XXXX'
, 'XXXXX')
, EXTN.VOICE_AUTHORIZATION_FLAG
, EXTN.VOICE_AUTHORIZATION_DATE
, EXTN.VOICE_AUTHORIZATION_CODE
, EXTN.ORIGIN_APPLICATION_ID
, EXTN.ORDER_ID
, EXTN.PO_NUMBER
, EXTN.PO_LINE_NUMBER
, EXTN.TRXN_REF_NUMBER1
, EXTN.TRXN_REF_NUMBER2
, NVL(EXTN.PAYMENT_SYSTEM_ORDER_NUMBER
, SUBSTR(IBY_FNDCPT_TRXN_PUB.GET_TANGIBLE_ID(A.APPLICATION_SHORT_NAME
, EXTN.ORDER_ID
, EXTN.TRXN_REF_NUMBER1
, EXTN.TRXN_REF_NUMBER2)
, 1
, 80))
, EXTN.ADDITIONAL_INFO
, DECODE(AUTHTXN.STATUS
, NULL
, 'N'
, 'Y')
, DECODE(AUTHTXN.STATUS
, 0
, 'AUTH_SUCCESS'
, 100
, 'AUTH_PENDING'
, 111
, 'AUTH_PENDING'
, NULL)
, DECODE(SETTLETXN.STATUS
, NULL
, 'N'
, 'Y')
, DECODE(SETTLETXN.STATUS
, 0
, 'SETTLEMENT_SUCCESS'
, 100
, 'SETTLEMENT_PENDING'
, 111
, 'SETTLEMENT_PENDING'
, 11
, 'SETTLEMENT_PENDING'
, NULL)
, DECODE(RETURNTXN.STATUS
, NULL
, 'N'
, 'Y')
, DECODE(RETURNTXN.STATUS
, 0
, 'RETURN_SUCCESS'
, 100
, 'RETURN_PENDING'
, 111
, 'RETURN_PENDING'
, NULL)
, BHZP.PARTY_NAME
, BRHZP.PARTY_NAME
, DECODE(U.INSTRUMENT_TYPE
, 'CREDITCARD'
, NVL(C.ENCRYPTED
, 'N')
, NVL(B.ENCRYPTED
, 'N'))
, (SELECT LKP.MEANING
FROM FND_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = 'IBY_CARD_EXPIRATION_STATUS'
AND (DECODE(DECODE(C.EXPIRYDATE
, NULL
, C.EXPIRED_FLAG
, DECODE(SIGN(C.EXPIRYDATE-SYSDATE)
, -1
, 'Y'
, 'N'))
, 'Y'
, 'EXPIRED'
, 'N'
, 'UNEXPIRED')) = LKP.LOOKUP_CODE)
, EXTN.SEQ_TYPE_LAST
FROM IBY_CREDITCARD C
, IBY_CREDITCARD_ISSUERS_B I
, IBY_CREDITCARD_ISSUERS_TL IT
, IBY_EXT_BANK_ACCOUNTS B
, IBY_FNDCPT_PMT_CHNNLS_B P
, IBY_FNDCPT_PMT_CHNNLS_TL PT
, IBY_FNDCPT_TX_EXTENSIONS EXTN
, IBY_PMT_INSTR_USES_ALL U
, HZ_PARTIES HZP
, FND_APPLICATION A
, HZ_PARTIES BHZP
, HZ_PARTIES BRHZP
, HZ_ORGANIZATION_PROFILES BANKPROFILE
, (SELECT OP.TRXN_EXTENSION_ID
, SUMM.STATUS
FROM IBY_TRXN_SUMMARIES_ALL SUMM
, IBY_FNDCPT_TX_OPERATIONS OP WHERE(SUMM.TRANSACTIONID = OP.TRANSACTIONID) AND(REQTYPE = 'ORAPMTREQ') AND(STATUS IN(0
, 100
, 111)) AND(OP.TRANSACTIONID = (SELECT MIN(TRANSACTIONID)
FROM IBY_FNDCPT_TX_OPERATIONS
WHERE TRXN_EXTENSION_ID = OP.TRXN_EXTENSION_ID )) AND((TRXNTYPEID IN(2
, 3)) OR((TRXNTYPEID = 20) AND(SUMM.TRXNMID = (SELECT MAX(TRXNMID)
FROM IBY_TRXN_SUMMARIES_ALL
WHERE TRANSACTIONID = SUMM.TRANSACTIONID AND(REQTYPE = 'ORAPMTREQ') AND(STATUS IN(0
, 100
, 111)) AND(TRXNTYPEID = 20) )))) ) AUTHTXN
, ( SELECT OP.TRXN_EXTENSION_ID
, SUMM.STATUS
, RANK() OVER (PARTITION BY OP.TRXN_EXTENSION_ID ORDER BY OP.TRANSACTIONID ) RANK
FROM IBY_TRXN_SUMMARIES_ALL SUMM
, IBY_FNDCPT_TX_OPERATIONS OP WHERE(SUMM.TRANSACTIONID = OP.TRANSACTIONID) AND((INSTRTYPE IN ('CREDITCARD'
, 'PURCHASECARD')
AND REQTYPE = 'ORAPMTCAPTURE') OR(INSTRTYPE = 'BANKACCOUNT'
AND TRXNTYPEID IS NULL) OR(INSTRTYPE = 'PINLESSDEBITCARD'
AND REQTYPE = 'ORAPMTREQ')) AND(STATUS IN(0
, 100
, 111
, 11)) ) SETTLETXN
, (SELECT OP.TRXN_EXTENSION_ID
, SUMM.STATUS
, RANK() OVER (PARTITION BY OP.TRXN_EXTENSION_ID ORDER BY OP.TRANSACTIONID ) RANK
FROM IBY_TRXN_SUMMARIES_ALL SUMM
, IBY_FNDCPT_TX_OPERATIONS OP WHERE(SUMM.TRANSACTIONID = OP.TRANSACTIONID) AND(REQTYPE = 'ORAPMTRETURN') AND(STATUS IN(0
, 100
, 111))) RETURNTXN
WHERE (EXTN.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID(+))
AND (DECODE(U.INSTRUMENT_TYPE
, 'CREDITCARD'
, U.INSTRUMENT_ID
, NULL) = C.INSTRID(+))
AND (DECODE(U.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, U.INSTRUMENT_ID
, NULL) = B.EXT_BANK_ACCOUNT_ID(+))
AND (EXTN.PAYMENT_CHANNEL_CODE = P.PAYMENT_CHANNEL_CODE)
AND (C.CARD_ISSUER_CODE = I.CARD_ISSUER_CODE(+))
AND (C.CARD_OWNER_ID = HZP.PARTY_ID(+))
AND (EXTN.ORIGIN_APPLICATION_ID = A.APPLICATION_ID)
AND (B.BANK_ID=BHZP.PARTY_ID(+))
AND (B.BRANCH_ID=BRHZP.PARTY_ID(+))
AND (BHZP.PARTY_ID=BANKPROFILE.PARTY_ID(+))
AND (I.CARD_ISSUER_CODE=IT.CARD_ISSUER_CODE(+))
AND (IT.LANGUAGE(+)=USERENV('LANG'))
AND (P.PAYMENT_CHANNEL_CODE=PT.PAYMENT_CHANNEL_CODE)
AND (PT.LANGUAGE=USERENV('LANG'))
AND (EXTN.TRXN_EXTENSION_ID = AUTHTXN.TRXN_EXTENSION_ID(+))
AND (EXTN.TRXN_EXTENSION_ID = SETTLETXN.TRXN_EXTENSION_ID(+))
AND (EXTN.TRXN_EXTENSION_ID = RETURNTXN.TRXN_EXTENSION_ID(+))
AND SYSDATE BETWEEN TRUNC(BANKPROFILE.EFFECTIVE_START_DATE(+))
AND NVL(TRUNC(BANKPROFILE.EFFECTIVE_END_DATE(+))
, SYSDATE + 1)
AND SETTLETXN.RANK(+) = 1
AND RETURNTXN.RANK(+) = 1