IBY_EXTN_SUMM_RETURNS_V

Details

• Schema: FUSION

• Object owner: IBY

• Object type: VIEW

Columns

Name
TRXN_EXTENSION_ID RETURNED_FLAG RETURNED_STATUS RETURN_VAL_STATUS

Query

SQL_Statement

SELECT x.trxn_extension_id TRXN_EXTENSION_ID, DECODE(summ.status, NULL, 'N', 'Y') RETURNED_FLAG, DECODE(summ.status, 0, 'RETURN_SUCCESS', 100, 'RETURN_PENDING', 111, 'RETURN_PENDING', 11, 'RETURN_PENDING', 5, 'PAYMENT_SYS_REJECT', 16, 'PAYMENT_SYS_REJECT', 17, 'PAYMENT_SYS_REJECT', 20, 'PAYMENT_SYS_REJECT', NULL) RETURNED_STATUS, summ.return_val_status RETURN_VAL_STATUS FROM iby_fndcpt_tx_extensions x, (SELECT ts.status, ts.transactionid,op.trxn_extension_id, DECODE(ts.status, 11, 'PENDING', 100,'PENDING', 5, 'REJECTED', 16, 'REJECTED', 17, 'REJECTED', 20, 'REJECTED', 111, DECODE(b.batchstatus, 11, 'TRANSMITTED', 0, 'TRANSMITTED', 'PENDING'), 'TRANSMITTED') return_val_status FROM iby_trxn_summaries_all ts, iby_fndcpt_tx_operations op, iby_batches_all b WHERE ts.transactionid = op.transactionid AND ts.mbatchid = b.mbatchid(+) AND ts.trxnmid = (SELECT max(tt.trxnmid) FROM iby_trxn_summaries_all tt WHERE tt.reqtype IN ('ORAPMTRETURN','ORAPMTCREDIT') AND tt.status IN (0, 11, 100, 111, 5, 16, 17, 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(+)