31 lines
1.4 KiB
Plaintext
31 lines
1.4 KiB
Plaintext
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 |