IBY_EXTN_SUMM_AUTHS

Details

Name
TRXN_EXTENSION_ID AUTHORIZED_FLAG AUTHORIZATION_STATUS

Name

TRXN_EXTENSION_ID

AUTHORIZED_FLAG

AUTHORIZATION_STATUS

SQL_Statement
SELECT x.trxn_extension_id TRXN_EXTENSION_ID, DECODE(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG, DECODE(summ.status, 0, 'AUTH_SUCCESS', 100, 'AUTH_PENDING', 111, 'AUTH_PENDING', 11,'AUTH_PENDING', NULL) AUTHORIZATION_STATUS FROM iby_fndcpt_tx_extensions x, (SELECT ts.transactionid, ts.status, op.trxn_extension_id FROM iby_trxn_summaries_all ts, iby_fndcpt_tx_operations op WHERE (ts.reqtype = 'ORAPMTREQ') AND (ts.transactionid = op.transactionid) AND (ts.status IN (0,11,100,111)) AND (ts.trxntypeid IN (2,3,20)) AND (ts.trxnmid = (SELECT max(tt.trxnmid) FROM iby_trxn_summaries_all tt WHERE (tt.reqtype = 'ORAPMTREQ') AND (tt.status IN (0,11,100,111)) AND (tt.trxntypeid IN (2,3,20)) AND tt.transactionid IN (SELECT oo.transactionid FROM iby_fndcpt_tx_operations oo WHERE oo.trxn_extension_id = op.trxn_extension_id) ) ) ) summ WHERE x.trxn_extension_id = summ.trxn_extension_id(+)

SQL_Statement

SELECT x.trxn_extension_id TRXN_EXTENSION_ID,

DECODE(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG,

DECODE(summ.status, 0, 'AUTH_SUCCESS', 100, 'AUTH_PENDING', 111, 'AUTH_PENDING', 11,'AUTH_PENDING', NULL) AUTHORIZATION_STATUS

FROM iby_fndcpt_tx_extensions x,

(SELECT ts.transactionid, ts.status, op.trxn_extension_id

FROM iby_trxn_summaries_all ts, iby_fndcpt_tx_operations op

WHERE (ts.reqtype = 'ORAPMTREQ')

AND (ts.transactionid = op.transactionid)

AND (ts.status IN (0,11,100,111))

AND (ts.trxntypeid IN (2,3,20))

AND (ts.trxnmid =

(SELECT max(tt.trxnmid) FROM iby_trxn_summaries_all tt

WHERE (tt.reqtype = 'ORAPMTREQ') AND (tt.status IN (0,11,100,111))

AND (tt.trxntypeid IN (2,3,20)) AND tt.transactionid IN

(SELECT oo.transactionid FROM iby_fndcpt_tx_operations oo

WHERE oo.trxn_extension_id = op.trxn_extension_id)

)

) ) summ

WHERE

x.trxn_extension_id = summ.trxn_extension_id(+)