IBY_EXTN_SUMM_SETTLES_V

Details

• Schema: FUSION

• Object owner: IBY

• Object type: VIEW

Columns

Name
TRXN_EXTENSION_ID TRXNMID SETTLED_FLAG SETTLEMENT_STATUS SETTLEMENT_VAL_STATUS

Query

SQL_Statement

SELECT x.trxn_extension_id TRXN_EXTENSION_ID, summ.trxnmid TRXNMID, DECODE(summ.status, NULL, 'N', 'Y') SETTLED_FLAG, DECODE(summ.status, 0, 'SETTLEMENT_SUCCESS', 100, 'SETTLEMENT_PENDING', 111, 'SETTLEMENT_PENDING', 11, 'SETTLEMENT_PENDING', 5, 'PAYMENT_SYS_REJECT', 16, 'PAYMENT_SYS_REJECT', 17, 'PAYMENT_SYS_REJECT', 20, 'PAYMENT_SYS_REJECT', NULL) SETTLEMENT_STATUS, summ.settlement_val_status SETTLEMENT_VAL_STATUS FROM iby_fndcpt_tx_extensions x, (SELECT ts.status, ts.trxnmid, 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') settlement_val_status FROM iby_trxn_summaries_all ts, iby_fndcpt_tx_operations op, iby_batches_all b WHERE ts.transactionid = op.transactionid AND (b.mbatchid(+) = ts.mbatchid) AND ts.trxnmid = ( SELECT max(tt.trxnmid) FROM iby_trxn_summaries_all tt WHERE ( (tt.instrtype IN ('CREDITCARD', 'PURCHASECARD') AND tt.reqtype = 'ORAPMTCAPTURE') OR (tt.instrtype = 'BANKACCOUNT' AND tt.reqtype = 'ORAPMTBATCHREQ') OR (tt.instrtype = 'PINLESSDEBITCARD' AND tt.reqtype = 'ORAPMTREQ') ) AND (tt.status IN (0, 100, 111, 11, 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(+)