select *
from ((select t1.GOPAY_TXN_TM GOPAY_TXN_TM,
t1.ORDER_STLM_TIME order_stlm_time,
t1.MER_ORDER_ID mer_order_id,
t1.GOPAY_ORDER_ID gopay_order_id,
t5.cust_name trans_adr,
t1.MER_ID mer_id,
t1.MER_TXN_AMT order_deal_amt,
t1.ORDER_REAL_FEE_AMT order_real_fee_amt,
t1.GOPAY_INT_TXN_CD GOPAY_INT_TXN_CD,
t1.GATEWAY_SOURCE order_ori,
t1.TXN_STA_CD trans_status,
'' hander_status,
'' options,
t1.CREATED_TS CREATED_TS,
wf.STEP_NUM step_num,
wf.DECISION decision,
wf.DONE done,
t1.PAY_CHANNEL pay_channel,
t1.REVERSAL_FLAG reversal_flag
from CPS_GEN_MAIN_ORDER t1,
GOPAY_ALL_CUST_INFO t5,
WF_APPR_VIEW wf
where t1.MER_ID = t5.CUST_ID
and t1.gopay_order_id = wf.TARGET_ID
and wf.TARGET_TYPE = '1'
and wf.APPROVAL_ID <> 1
and not exists
(select *
from WF_APPR_VIEW v0
where (select count(v.TARGET_ID) cn over(partition by v.TARGET_ID)
from WF_APPR_TASK_DTL v
where v0.TARGET_ID = v.TARGET_ID and cn>1
)
and v0.TARGET_ID = wf.TARGET_ID
and wf.APPROVAL_ID = 2
and wf.TARGET_TYPE = 1)
and t1.GOPAY_INT_TXN_CD not in ('00207')
)) tt
where 1 = 1
SELECT *
FROM ((SELECT T1.GOPAY_TXN_TM GOPAY_TXN_TM,
T1.ORDER_STLM_TIME ORDER_STLM_TIME,
T1.MER_ORDER_ID MER_ORDER_ID,
T1.GOPAY_ORDER_ID GOPAY_ORDER_ID,
T5.CUST_NAME TRANS_ADR,
T1.MER_ID MER_ID,
T1.MER_TXN_AMT ORDER_DEAL_AMT,
T1.ORDER_REAL_FEE_AMT ORDER_REAL_FEE_AMT,
T1.GOPAY_INT_TXN_CD GOPAY_INT_TXN_CD,
T1.GATEWAY_SOURCE ORDER_ORI,
T1.TXN_STA_CD TRANS_STATUS,
'' HANDER_STATUS,
'' OPTIONS,
T1.CREATED_TS CREATED_TS,
WF.STEP_NUM STEP_NUM,
WF.DECISION DECISION,
WF.DONE DONE,
T1.PAY_CHANNEL PAY_CHANNEL,
T1.REVERSAL_FLAG REVERSAL_FLAG
FROM CPS_GEN_MAIN_ORDER T1
JOIN GOPAY_ALL_CUST_INFO T5
ON T1.MER_ID = T5.CUST_ID
JOIN WF_APPR_VIEW WF
ON T1.GOPAY_ORDER_ID = WF.TARGET_ID
LEFT JOIN (SELECT TARGET_ID,
COUNT(V.TARGET_ID) OVER(PARTITION BY TARGET_ID) CNT
FROM WF_APPR_TASK_DTL V) B
ON (B.CNT > 1 AND WF.APPROVAL_ID = 2 AND
B.TARGET_ID = WF.TARGET_ID)
WHERE AND WF.TARGET_TYPE = '1' AND WF.APPROVAL_ID <> 1 AND
B.TARGET_ID IS NULL AND T1.GOPAY_INT_TXN_CD NOT IN ('00207'))) TT
WHERE 1 = 1