IBY_TRXN_EXT_AUTHS

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
TRXN_EXTENSION_ID PAYMENT_CHANNEL_CODE PAYMENT_CHANNEL_NAME INSTR_ASSIGNMENT_ID AUTHORIZATION_ID AUTHORIZATION_RESULT_CODE AUTHORIZATION_STATUS AUTHORIZATION_DATE AUTHORIZATION_AMOUNT EFFECTIVE_AUTH_AMOUNT PAYEE_ID PROCESS_PROFILE_CODE INSTRUMENT_TYPE PAYMENT_SYSTEM_ORDER_NUMBER PMT_SYS_ERR_CODE PMT_SYS_ERR_MSG AUTHORIZATION_CODE AVS_CODE INSTR_SEC_CODE_CHECK SETTLE_REQ_AUTH_FLAG INITIATOR_EXTENSION_ID ORIGIN_APPLICATION_ID

Name

TRXN_EXTENSION_ID

PAYMENT_CHANNEL_CODE

PAYMENT_CHANNEL_NAME

INSTR_ASSIGNMENT_ID

AUTHORIZATION_ID

AUTHORIZATION_RESULT_CODE

AUTHORIZATION_STATUS

AUTHORIZATION_DATE

AUTHORIZATION_AMOUNT

EFFECTIVE_AUTH_AMOUNT

PAYEE_ID

PROCESS_PROFILE_CODE

INSTRUMENT_TYPE

PAYMENT_SYSTEM_ORDER_NUMBER

PMT_SYS_ERR_CODE

PMT_SYS_ERR_MSG

AUTHORIZATION_CODE

AVS_CODE

INSTR_SEC_CODE_CHECK

SETTLE_REQ_AUTH_FLAG

INITIATOR_EXTENSION_ID

ORIGIN_APPLICATION_ID

Query

SQL_Statement
SELECT x.trxn_extension_id TRXN_EXTENSION_ID, p.payment_channel_code PAYMENT_CHANNEL_CODE, p.payment_channel_name PAYMENT_CHANNEL_NAME, x.instr_assignment_id INSTR_ASSIGNMENT_ID, o.transactionid AUTHORIZATION_ID, 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' ) AUTHORIZATION_RESULT_CODE, a.status AUTHORIZATION_STATUS, a.updatedate AUTHORIZATION_DATE, a.amount AUTHORIZATION_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 ) EFFECTIVE_AUTH_AMOUNT, a.payeeid PAYEE_ID, a.process_profile_code PROCESS_PROFILE_CODE, a.instrtype INSTRUMENT_TYPE, a.tangibleid PAYMENT_SYSTEM_ORDER_NUMBER, a.bepcode PMT_SYS_ERR_CODE, a.bepmessage PMT_SYS_ERR_MSG, c.authcode AUTHORIZATION_CODE, c.avscode AVS_CODE, c.cvv2result INSTR_SEC_CODE_CHECK, NVL(seft.SETTLE_REQUIRE_VRFY_FLAG,'N') SETTLE_REQ_AUTH_FLAG, a.initiator_extension_id INITIATOR_EXTENSION_ID, x.origin_application_id 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))

SQL_Statement

SELECT

x.trxn_extension_id TRXN_EXTENSION_ID, p.payment_channel_code PAYMENT_CHANNEL_CODE,

p.payment_channel_name PAYMENT_CHANNEL_NAME, x.instr_assignment_id INSTR_ASSIGNMENT_ID,

o.transactionid AUTHORIZATION_ID,

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' ) AUTHORIZATION_RESULT_CODE,

a.status AUTHORIZATION_STATUS, a.updatedate AUTHORIZATION_DATE,

a.amount AUTHORIZATION_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 ) EFFECTIVE_AUTH_AMOUNT,

a.payeeid PAYEE_ID, a.process_profile_code PROCESS_PROFILE_CODE, a.instrtype INSTRUMENT_TYPE,

a.tangibleid PAYMENT_SYSTEM_ORDER_NUMBER, a.bepcode PMT_SYS_ERR_CODE, a.bepmessage PMT_SYS_ERR_MSG, c.authcode AUTHORIZATION_CODE, c.avscode AVS_CODE,

c.cvv2result INSTR_SEC_CODE_CHECK, NVL(seft.SETTLE_REQUIRE_VRFY_FLAG,'N') SETTLE_REQ_AUTH_FLAG,

a.initiator_extension_id INITIATOR_EXTENSION_ID, x.origin_application_id 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))