接口记录查询

--合同接口日志查询
SELECT DISTINCT CCI.CRM_CONTRACT_NUMBER , C.*
  FROM CUX.CUX_CMP_CONTRACT_INFO_HEADER CCI, CUX.CUX_ESB_DATA_TRACE C
 WHERE C.ROW_ID = CCI.ROWID
  AND CCI.CRM_CONTRACT_NUMBER = 'NBHT-20200537020'
   /*AND C.PROCESS_STATUS = 'E' --成功 S 失败 E
   AND C.LAST_UPDATE_DATE >
       TO_DATE('2020/6/19 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
   AND C.ESB_TYPE = 'ESB_FSSC_AR_CONTRACT'
   AND EXISTS
 (SELECT 1
          FROM CUX.CUX_OE_ORDER_PROJECT_B B, OE_ORDER_HEADERS_ALL H
         WHERE B.ORDER_HEADER_ID = H.HEADER_ID
           AND B.INTERNAL_CONTRACT_ID = CCI.CRM_CONTRACT_ID
           AND H.CREATION_DATE >= TO_DATE('2020/03/01', 'YYYY-MM-DD'))
   AND NOT EXISTS
 (SELECT 1
          FROM CUX.CUX_ESB_DATA_TRACE T
         WHERE 1 = 1
           AND T.ROW_ID = CCI.ROWID
           AND T.PROCESS_STATUS = 'S'
           AND T.LAST_UPDATE_DATE >=
               TO_DATE('2020/6/19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))*/
 ORDER BY cci.crm_contract_number,C.LAST_UPDATE_DATE DESC;

--订单收款计划日志查询
SELECT DISTINCT CAPP.ERP_ORDER_NUMBER, C.*
  FROM CUX.CUX_AR_PAY_PLAN_B CAPP, CUX.CUX_ESB_DATA_TRACE C
 WHERE C.ROW_ID = CAPP.ROWID
  AND CAPP.ERP_ORDER_NUMBER = 100069691
/*  AND C.PROCESS_STATUS = 'E' --成功 S 失败 E
   AND C.LAST_UPDATE_DATE >
       TO_DATE('2020/6/19 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
   AND C.ESB_TYPE = 'ESB_FSSC_AR_PLAN'
   AND NOT EXISTS
 (SELECT 1
          FROM CUX.CUX_ESB_DATA_TRACE T
         WHERE 1 = 1
           AND T.ROW_ID = CAPP.ROWID
           AND T.PROCESS_STATUS = 'S'
           AND T.LAST_UPDATE_DATE >=
               TO_DATE('2020/6/19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))*/
 ORDER BY C.LAST_UPDATE_DATE DESC;

--发货明细接口日志查询
SELECT  OOH.ORDER_NUMBER, OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER ||
             DECODE(OOL.OPTION_NUMBER, NULL, NULL, '.' || OOL.OPTION_NUMBER) LINE_NUM, C.*
  FROM OE_ORDER_HEADERS_ALL OOH,
       OE_ORDER_LINES_ALL   OOL,
       CUX.CUX_ESB_DATA_TRACE   C
 WHERE C.ROW_ID = OOL.ROWID
   AND OOL.HEADER_ID = OOH.HEADER_ID
   AND OOH.ORDER_NUMBER = 100069691
 /*   AND C.PROCESS_STATUS = 'E' --成功 S 失败 E
  AND C.LAST_UPDATE_DATE > TO_DATE('2020/6/19 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
      AND NOT EXISTS
 (SELECT 1
          FROM CUX.CUX_ESB_DATA_TRACE T
         WHERE 1 = 1
           AND T.ROW_ID = OOL.ROWID
           AND T.PROCESS_STATUS = 'S'
           AND T.LAST_UPDATE_DATE >=
               TO_DATE('2020/6/19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))*/
   AND C.ESB_TYPE = 'ESB_FSSC_SHIPMENT'
   ORDER BY C.LAST_UPDATE_DATE DESC;

--初始化订单收款计划日志查询
SELECT CAPP.ERP_ORDER_NUMBER, C.*
  FROM CUX.CUX_AR_PAY_PLAN_B CAPP, CUX.CUX_ESB_DATA_TRACE C
 WHERE C.ROW_ID = CAPP.ROWID
   AND C.PROCESS_STATUS = 'E' --成功 S 失败 E
   AND C.LAST_UPDATE_DATE >
       TO_DATE('2020/6/10 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
   AND C.ESB_TYPE = 'ESB_FSSC_AR_PLAN_OLD'
   AND NOT EXISTS
 (SELECT 1
          FROM CUX.CUX_ESB_DATA_TRACE T
         WHERE 1 = 1
           AND T.ROW_ID = CAPP.ROWID
           AND T.PROCESS_STATUS = 'S'
           AND T.LAST_UPDATE_DATE >=
               TO_DATE('2020/6/10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
   
--合同执行情况日期
SELECT OOH.ORDER_NUMBER, T.*
  FROM ONT.OE_ORDER_HEADERS_ALL OOH, CUX.CUX_ESB_DATA_TRACE T
 WHERE 1 = 1
   AND OOH.ROWID = T.ROW_ID
   AND T.LAST_UPDATE_DATE >
       TO_DATE('2020/6/29 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
   AND T.ESB_TYPE = 'ESB_FSSC_AR_CONTRACT_EXC';

--客户接口日志
SELECT HC.ACCOUNT_NAME, C.*
  FROM CUX_ESB_DATA_TRACE C, HZ_CUST_ACCOUNTS HC
 WHERE 1 = 1
   AND HC.ROWID = C.ROW_ID
   AND C.PROCESS_STATUS = 'E'
   AND C.LAST_UPDATE_DATE > SYSDATE - 0.1
   AND C.ESB_TYPE = 'ESB_FSSC_CUSTOMER';
--项目接口日志
SELECT PA.SEGMENT1, C.*
  FROM PA_PROJECTS_ALL PA, CUX.CUX_ESB_DATA_TRACE C
 WHERE 1 = 1
   AND PA.ROWID = C.ROW_ID
   AND PA.SEGMENT1 = 'PD20D003-0'
 ORDER BY C.LAST_UPDATE_DATE DESC;