VIEW: APPS.IBY_EXTN_SETTLEMENTS_V

Source

View Text - Preformatted

SELECT settletxn.trxn_extension_id, decode(settletxn.status, NULL, 'N', 'Y') , decode(settletxn.status, 0, 'SETTLEMENT_SUCCESS', 100, 'SETTLEMENT_PENDING', 111, 'SETTLEMENT_PENDING', 11, 'SETTLEMENT_PENDING', NULL) FROM (SELECT op.trxn_extension_id, summ.status, Rank() over (PARTITION BY Op.Trxn_Extension_Id ORDER BY op.Transactionid ) rank FROM iby_trxn_summaries_all summ, iby_fndcpt_tx_operations op WHERE (summ.transactionid = op.transactionid) AND((instrtype IN ('CREDITCARD', 'PURCHASECARD') AND reqtype = 'ORAPMTCAPTURE') OR(instrtype = 'BANKACCOUNT' AND trxntypeid IS NULL) OR(instrtype = 'PINLESSDEBITCARD' AND reqtype = 'ORAPMTREQ')) AND(status IN(0, 100, 111, 11)))settletxn WHERE settletxn.rank = 1

View Text - HTML Formatted

SELECT SETTLETXN.TRXN_EXTENSION_ID
, DECODE(SETTLETXN.STATUS
, NULL
, 'N'
, 'Y')
, DECODE(SETTLETXN.STATUS
, 0
, 'SETTLEMENT_SUCCESS'
, 100
, 'SETTLEMENT_PENDING'
, 111
, 'SETTLEMENT_PENDING'
, 11
, 'SETTLEMENT_PENDING'
, NULL)
FROM (SELECT OP.TRXN_EXTENSION_ID
, SUMM.STATUS
, RANK() OVER (PARTITION BY OP.TRXN_EXTENSION_ID ORDER BY OP.TRANSACTIONID ) RANK
FROM IBY_TRXN_SUMMARIES_ALL SUMM
, IBY_FNDCPT_TX_OPERATIONS OP
WHERE (SUMM.TRANSACTIONID = OP.TRANSACTIONID) AND((INSTRTYPE IN ('CREDITCARD'
, 'PURCHASECARD')
AND REQTYPE = 'ORAPMTCAPTURE') OR(INSTRTYPE = 'BANKACCOUNT'
AND TRXNTYPEID IS NULL) OR(INSTRTYPE = 'PINLESSDEBITCARD'
AND REQTYPE = 'ORAPMTREQ')) AND(STATUS IN(0
, 100
, 111
, 11)))SETTLETXN
WHERE SETTLETXN.RANK = 1