CREATE OR REPLACE VIEW HIGHWAY_EXCHANGE.V_MOBILEPAYCHECK
    (MOBILE_PAYCHECK_ID,MOBILEPAY_DATE,MOBILEPAY_TYPE,TICKET_CODE,TICKET_PRICE,
    TRADE_NO,TRADE_NUM,RESULT_CODE,RESULT_DESC,JSON_STRING,TRADE_FSPARE,
    TRADE_SSPARE,TRADE_TSPARE,MOBILE_PAYCHECK_STATE,MOBILE_PAYCHECK_DESC)
AS
SELECT
	MOBILE_PAYCHECK_ID,MOBILEPAY_DATE,MOBILEPAY_TYPE,TICKET_CODE,TICKET_PRICE,
	TRADE_NO,TRADE_NUM,RESULT_CODE,RESULT_DESC,JSON_STRING,TRADE_FSPARE,
	TRADE_SSPARE,TRADE_TSPARE,MOBILE_PAYCHECK_STATE,MOBILE_PAYCHECK_DESC
FROM
	HIGHWAY_EXCHANGE.T_MOBILE_PAYCHECK A
WHERE
	NOT EXISTS(SELECT 1 FROM HIGHWAY_EXCHANGE.T_SELLDATA_EXTRA B
		WHERE UPPER(SELLDATA_EXTRA_DESC) LIKE 'SUCCESS%' AND 
			B.SELLDATA_TYPE IN ('ALIPAY','WECHAT') AND 
			A.TICKET_CODE = B.TICKETCODE) AND 
	A.RESULT_CODE NOT IN ('100') AND MOBILEPAY_DATE < SYSDATE - 1/144 AND 
	NVL(A.MOBILE_PAYCHECK_DESC,'1') NOT LIKE '%CHECKED%'
UNION ALL
SELECT
	MOBILE_PAYCHECK_ID,MOBILEPAY_DATE,MOBILEPAY_TYPE,TICKET_CODE,TICKET_PRICE,
	TRADE_NO,TRADE_NUM,RESULT_CODE,RESULT_DESC,JSON_STRING,TRADE_FSPARE,
	TRADE_SSPARE,TRADE_TSPARE,MOBILE_PAYCHECK_STATE,MOBILE_PAYCHECK_DESC
FROM
	HIGHWAY_EXCHANGE.T_MOBILE_PAYCHECK A
WHERE
	NOT EXISTS(SELECT 1 FROM HIGHWAY_EXCHANGE.T_SELLDATA_EXTRA B
		WHERE UPPER(SELLDATA_EXTRA_DESC) LIKE 'SUCCESS%' AND 
			B.SELLDATA_TYPE IN ('ALIPAY','WECHAT') AND 
			A.TICKET_CODE = B.TICKETCODE) AND 
	A.RESULT_CODE IN ('100') AND MOBILEPAY_DATE < SYSDATE - 1/144