IBY_EXTN_SETTLE_STATUS_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 TRXN_EXTENSION_ID, TRXNMID, DECODE(status, NULL, 'N', 'Y') SETTLED_FLAG, DECODE(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, DECODE(status, 0, 'TRANSMITTED', 11, 'PENDING', 100,'PENDING', 5, 'REJECTED', 16, 'REJECTED', 17, 'REJECTED', 20, 'REJECTED', 111, DECODE(batchstatus, 11, 'TRANSMITTED', 0, 'TRANSMITTED', 'PENDING'), NULL) SETTLEMENT_VAL_STATUS FROM (select x.trxn_extension_id, ts.trxnmid, ts.creation_date, ts.status, b.batchstatus, ROW_NUMBER() OVER(PARTITION BY x.trxn_extension_id ORDER BY ts.creation_date DESC NULLS LAST) AS ROWNUMBER FROM iby_fndcpt_tx_extensions x left join iby_fndcpt_tx_operations op on x.trxn_extension_id = op.trxn_extension_id left join iby_trxn_summaries_all ts on op.transactionid = ts.transactionid and ts.status IN (0, 100, 111, 11, 5, 16, 17, 20) AND (ts.reqtype = case when ts.instrtype IN ('CREDITCARD', 'PURCHASECARD') then 'ORAPMTCAPTURE' when ts.instrtype = 'BANKACCOUNT' then 'ORAPMTBATCHREQ' when ts.instrtype = 'PINLESSDEBITCARD' then 'ORAPMTREQ' end) left join iby_batches_all b on ts.mbatchid = b.mbatchid ) WHERE ROWNUMBER = 1