Notes

SELECT *
FROM
(SELECT rownum rn,
ZZ.*
FROM
(SELECT MC.MERCHANTCUSTOMERNO,
MC.MERCHANTNAME,
T.MID,
ML.DESCRIPTION AS LOCATIONNAME,
ML.MERCHANTTYPE,
MT.DESCRIPTION AS MERCHANT_TYPE_DES,
T.TID,
T.TXNAMOUNT,
TO_CHAR(T.ONLINECREATEDTIME,'yyyy') AS TXNYEAR,
TO_CHAR(T.ONLINECREATEDTIME,'yyyy-mm-dd hh24:mi:ss') AS TXNDATETIME,
T.TXNDATE,
T.TXNTIME,
T.TXNCURRENCY,
T.TXNTYPECODE,
T.RRN,
T.AUTHCODE,
T.TRACENO,
T.MCC,
T.STATUS,
T.EODSTATUS,
TO_CHAR(T.SETTLEMENTDATE,'yyyy-mm-dd') AS SETTLEMENTDATE,
T.RESPONSECODE,
NVL(T.ONOFFSTSTUS,'0') AS ONOFFSTSTUS,
T.BATCHNO,
T.POSENTRYMODE,
T.CARDNO,
T.PROCESSINGCODE,
T.MTI,
T.CAIC,
MCC.DESCRIPTION AS MCCDES,
C.DESCRIPTION AS CURRENCYDES,
C.EXPONENT,
T.LISTENERTYPE,
T.CHANNELTYPE,
TO_CHAR(T.CREATETIME,'yyyy-mm-dd hh24:mi:ss') AS CREATEDTIME,
CT.DESCRIPTION AS CHANNELDES,
TO_CHAR(T.LASTUPDATETIME,'yyyy-mm-dd hh24:mi:ss') AS LASTUPDATEDTIME,
LT.DESCRIPTION AS LISTNERDES,
TT.DESCRIPTION AS TXNTYPEDES,
S.DESCRIPTION AS STATUSDES,
ES.DESCRIPTION AS EODSTATUSDES
FROM TRANSACTION T
INNER JOIN MERCHANTLOCATION ML
ON T.MID = ML.MERCHANTID
LEFT JOIN MERCHANTTYPE MT
ON MT.CODE = ML.MERCHANTTYPE
INNER JOIN MERCHANTCUSTOMER MC
ON MC.MERCHANTCUSTOMERNO = ML.MERCHANTCUSTOMERNO
LEFT JOIN CURRENCY C
ON C.CURRENCYNUMCODE = T.TXNCURRENCY
LEFT JOIN STATUS S
ON S.ONLINECODE = T.STATUS
LEFT JOIN STATUS ES
ON ES.STATUSCODE = T.EODSTATUS
LEFT JOIN LISTNERTYPE LT
ON LT.LISTNERTYPE = T.LISTENERTYPE
LEFT JOIN CHANNELTYPE CT
ON CT.CHANNELTYPE = T.CHANNELTYPE
LEFT JOIN MCC
ON MCC.MCCCODE = T.MCC
LEFT JOIN TRANSACTIONTYPE TT
ON TT.ONLINETXNTYPE = T.TXNTYPECODE
WHERE T.TXNID IS NOT NULL
AND ( ( T.REQUESTFROM = '2'
AND T.TXNTYPECODE != '49' )
OR ( T.STATUS = '20'
AND T.REQUESTFROM = '2'
AND T.TXNTYPECODE = '48' )
OR ( T.STATUS = '20'
AND T.REQUESTFROM = '1'
AND T.TXNTYPECODE = '43' ) )
ORDER BY T.LOCALDATE DESC,
T.LOCALTIME DESC
)ZZ
)
WHERE RN BETWEEN 0 AND 1000