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 x.rowid, x.trxn_extension_id, p.payment_channel_code, pt.payment_channel_name, x.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, c.expirydate, '**/**', NVL(c.expired_flag, 'N'), c.card_issuer_code, it.card_issuer_name, c.purchasecard_subtype, c.chname, lpad('*', LENGTH(nvl(c.chname, hzp.party_name)), '*'), b.masked_bank_account_num, bankprofile.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'), 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, 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 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 x, 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((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 FROM iby_trxn_summaries_all summ, iby_fndcpt_tx_operations op WHERE(summ.transactionid = op.transactionid) AND((instrtype = 'CREDITCARD' 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 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 (x.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 (x.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 (x.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 (x.trxn_extension_id = authtxn.trxn_extension_id(+)) AND (x.trxn_extension_id = settletxn.trxn_extension_id(+)) AND (x.trxn_extension_id = returntxn.trxn_extension_id(+)) AND sysdate BETWEEN TRUNC(bankprofile.effective_start_date(+)) AND nvl(TRUNC(bankprofile.effective_end_date(+)), sysdate + 1)

View Text - HTML Formatted

SELECT X.ROWID
, X.TRXN_EXTENSION_ID
, P.PAYMENT_CHANNEL_CODE
, PT.PAYMENT_CHANNEL_NAME
, X.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
, C.EXPIRYDATE
, '**/**'
, NVL(C.EXPIRED_FLAG
, 'N')
, C.CARD_ISSUER_CODE
, IT.CARD_ISSUER_NAME
, C.PURCHASECARD_SUBTYPE
, C.CHNAME
, LPAD('*'
, LENGTH(NVL(C.CHNAME
, HZP.PARTY_NAME))
, '*')
, B.MASKED_BANK_ACCOUNT_NUM
, BANKPROFILE.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')
, 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
, 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
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 X
, 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((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
FROM IBY_TRXN_SUMMARIES_ALL SUMM
, IBY_FNDCPT_TX_OPERATIONS OP WHERE(SUMM.TRANSACTIONID = OP.TRANSACTIONID) AND((INSTRTYPE = 'CREDITCARD'
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
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 (X.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 (X.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 (X.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 (X.TRXN_EXTENSION_ID = AUTHTXN.TRXN_EXTENSION_ID(+))
AND (X.TRXN_EXTENSION_ID = SETTLETXN.TRXN_EXTENSION_ID(+))
AND (X.TRXN_EXTENSION_ID = RETURNTXN.TRXN_EXTENSION_ID(+))
AND SYSDATE BETWEEN TRUNC(BANKPROFILE.EFFECTIVE_START_DATE(+))
AND NVL(TRUNC(BANKPROFILE.EFFECTIVE_END_DATE(+))
, SYSDATE + 1)