VIEW: APPS.IBY_TRXN_EXT_AUTHS_V

Source

View Text - Preformatted

SELECT x.trxn_extension_id, p.payment_channel_code, p.payment_channel_name, x.instr_assignment_id, o.transactionid, decode(a.status, 0, 'AUTH_SUCCESS', 1, 'COMMUNICATION_ERROR', 2, 'DUPLICATE_AUTH', 4, 'GENERAL_INVALID_PARAM', 5, 'PAYMENT_SYS_REJECT', 11, 'AUTH_PENDING', 12, 'AUTH_PENDING', 13, 'AUTH_PENDING', 16, 'PAYMENT_SYS_REJECT', 17, 'PAYMENT_SYS_REJECT', 18, 'AUTH_PENDING', 19, 'GENERAL_INVALID_PARAM', 20, 'PAYMENT_SYS_REJECT', 100, 'AUTH_PENDING', 101, 'COMMUNICATION_ERROR', 111, 'AUTH_PENDING', 'GENERAL_SYS_ERROR'), a.status, a.updatedate, a.amount, decode(SIGN((a.updatedate + nvl(i.authorization_validity_period, 0)) -decode(i.authorization_validity_period, NULL, a.updatedate -10, sysdate)), 0, 0, -1, 0, a.amount), a.payeeid, a.process_profile_code, a.instrtype, a.tangibleid, a.bepcode, a.bepmessage, c.authcode, c.avscode, c.cvv2result, nvl(seft.settle_require_vrfy_flag, 'N'), a.initiator_extension_id, x.origin_application_id FROM IBY_FNDCPT_PMT_CHNNLS_VL P,IBY_FNDCPT_TX_EXTENSIONS X,IBY_FNDCPT_TX_OPERATIONS O,IBY_TRXN_SUMMARIES_ALL A,IBY_TRXN_CORE C,IBY_CREDITCARD_ISSUERS_B I,IBY_FNDCPT_SYS_EFT_PF_B SEFT,IBY_FNDCPT_USER_EFT_PF_B UEFT WHERE (x.payment_channel_code = p.payment_channel_code) AND (x.trxn_extension_id = o.trxn_extension_id) AND (o.transactionid = a.transactionid) AND (a.instrsubtype = i.card_issuer_code(+)) AND (DECODE(instrtype, 'BANKACCOUNT',a.process_profile_code, NULL) = ueft.USER_EFT_PROFILE_CODE(+)) AND (ueft.SYS_EFT_PROFILE_CODE = seft.SYS_EFT_PROFILE_CODE(+)) AND (a.trxnmid = c.trxnmid(+)) AND (a.reqtype = 'ORAPMTREQ') AND (a.trxntypeid IN (2,3,20))

View Text - HTML Formatted

SELECT X.TRXN_EXTENSION_ID
, P.PAYMENT_CHANNEL_CODE
, P.PAYMENT_CHANNEL_NAME
, X.INSTR_ASSIGNMENT_ID
, O.TRANSACTIONID
, DECODE(A.STATUS
, 0
, 'AUTH_SUCCESS'
, 1
, 'COMMUNICATION_ERROR'
, 2
, 'DUPLICATE_AUTH'
, 4
, 'GENERAL_INVALID_PARAM'
, 5
, 'PAYMENT_SYS_REJECT'
, 11
, 'AUTH_PENDING'
, 12
, 'AUTH_PENDING'
, 13
, 'AUTH_PENDING'
, 16
, 'PAYMENT_SYS_REJECT'
, 17
, 'PAYMENT_SYS_REJECT'
, 18
, 'AUTH_PENDING'
, 19
, 'GENERAL_INVALID_PARAM'
, 20
, 'PAYMENT_SYS_REJECT'
, 100
, 'AUTH_PENDING'
, 101
, 'COMMUNICATION_ERROR'
, 111
, 'AUTH_PENDING'
, 'GENERAL_SYS_ERROR')
, A.STATUS
, A.UPDATEDATE
, A.AMOUNT
, DECODE(SIGN((A.UPDATEDATE + NVL(I.AUTHORIZATION_VALIDITY_PERIOD
, 0)) -DECODE(I.AUTHORIZATION_VALIDITY_PERIOD
, NULL
, A.UPDATEDATE -10
, SYSDATE))
, 0
, 0
, -1
, 0
, A.AMOUNT)
, A.PAYEEID
, A.PROCESS_PROFILE_CODE
, A.INSTRTYPE
, A.TANGIBLEID
, A.BEPCODE
, A.BEPMESSAGE
, C.AUTHCODE
, C.AVSCODE
, C.CVV2RESULT
, NVL(SEFT.SETTLE_REQUIRE_VRFY_FLAG
, 'N')
, A.INITIATOR_EXTENSION_ID
, X.ORIGIN_APPLICATION_ID
FROM IBY_FNDCPT_PMT_CHNNLS_VL P
, IBY_FNDCPT_TX_EXTENSIONS X
, IBY_FNDCPT_TX_OPERATIONS O
, IBY_TRXN_SUMMARIES_ALL A
, IBY_TRXN_CORE C
, IBY_CREDITCARD_ISSUERS_B I
, IBY_FNDCPT_SYS_EFT_PF_B SEFT
, IBY_FNDCPT_USER_EFT_PF_B UEFT
WHERE (X.PAYMENT_CHANNEL_CODE = P.PAYMENT_CHANNEL_CODE)
AND (X.TRXN_EXTENSION_ID = O.TRXN_EXTENSION_ID)
AND (O.TRANSACTIONID = A.TRANSACTIONID)
AND (A.INSTRSUBTYPE = I.CARD_ISSUER_CODE(+))
AND (DECODE(INSTRTYPE
, 'BANKACCOUNT'
, A.PROCESS_PROFILE_CODE
, NULL) = UEFT.USER_EFT_PROFILE_CODE(+))
AND (UEFT.SYS_EFT_PROFILE_CODE = SEFT.SYS_EFT_PROFILE_CODE(+))
AND (A.TRXNMID = C.TRXNMID(+))
AND (A.REQTYPE = 'ORAPMTREQ')
AND (A.TRXNTYPEID IN (2
, 3
, 20))