最近存储过程

CREATE OR REPLACE PROCEDURE STP_INTERNATIONAL(P_DT DATE DEFAULT SYSDATE) IS
  RECE_DT         DATE; --收件端持久化日期
  TRANSFER_DT     DATE; --中转持久化日期
  DELIVERY_DT     DATE; --派件端持久化日期
  CONVEYAN_DT     DATE; --运力持久化日期
  SEND_DT         DATE; --寄件日期（国际件跨度比较大，所以用该变量把运单基础信息表的数据扩大）
  MAX_RECE_DT     DATE;
  MAX_TRANSFER_DT DATE;
  MAX_DELIVERY_DT DATE;
  MAX_CONVEYAN_DT DATE;
begin
  RECE_DT         := TRUNC(P_DT) - 5; --收件端持久化5天前的数据
  TRANSFER_DT     := TRUNC(P_DT) - 3; --中转持久化3天前的数据
  DELIVERY_DT     := TRUNC(P_DT) - 5; --派件端持久化5天前的数据
  CONVEYAN_DT     := TRUNC(P_DT) - 4; --运力持久化4天前的数据
  MAX_RECE_DT     := RECE_DT + 1;
  MAX_TRANSFER_DT := TRANSFER_DT + 1;
  MAX_DELIVERY_DT := DELIVERY_DT + 1;
  MAX_CONVEYAN_DT := CONVEYAN_DT + 1;
  SEND_DT         := TRUNC(P_DT) - 20; --寄件日期国际件跨度比较大
  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          NULL,
                          'START国际件环节处理量日期' ||
                          TO_CHAR(TRUNC(P_DT), 'yyyy-mm-dd'));
  COMMIT;
  --删除过渡表
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TT_PROCESS_RESULT_N';
  ----------------------------------------------仓库发件晚点-------------------------------------------------
  --收件端监控-实发票数明细
  INSERT INTO TT_PROCESS_RESULT_N
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
    SELECT A.REPORT_DT, A.ZONE_CODE, COUNT(1) WAYBILL_CNT, 1, 1
      FROM (SELECT /*+ USE_HASH(B,P) */
             TRUNC(P.PLAN_BEGIN_TM) REPORT_DT, P.ZONE_CODE
              FROM TT_WAYBILL_BASIS B, TT_PKP_DEPOT_DETAIL P
             WHERE B.WAYBILL_NO = P.BILL_NO
               AND B.IS_NATIONAL = 'Y'
               AND B.SEND_TIME >= SEND_DT
                  --实发标志
               AND P.ACTUAL_SEND_FLAG = 1
               AND P.PLAN_BEGIN_TM >= RECE_DT
               AND P.PLAN_BEGIN_TM < MAX_RECE_DT) A
     GROUP BY A.ZONE_CODE, A.REPORT_DT;

  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          SQL%ROWCOUNT,
                          'INSERT INTO TT_PROCESS_RESULT_N 1');
  COMMIT;

  --------------------------------------------中转发件晚点------------------------------------------------
  --中转班次监控-实发票数明细
  INSERT INTO TT_PROCESS_RESULT_N
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
    SELECT B.REPORT_DT, B.ZONE_CODE, COUNT(1) WAYBILL_CNT, 2, 1
      FROM (SELECT /* +USE_HASH(B,T) */
             TRUNC(T.REPORT_DT) REPORT_DT, T.ZONE_CODE
              FROM TT_WAYBILL_BASIS B, TT_TRANSFER_DEPART_DETAIL T
             WHERE B.WAYBILL_NO = T.WAYBILL_NO
               AND B.IS_NATIONAL = 'Y'
               AND B.SEND_TIME >= SEND_DT
               AND T.REPORT_DT >= TRANSFER_DT
               AND T.REPORT_DT < MAX_TRANSFER_DT) B
     GROUP BY B.ZONE_CODE, B.REPORT_DT;

  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          SQL%ROWCOUNT,
                          'INSERT INTO TT_PROCESS_RESULT_N 2');
  COMMIT;

  ----------------------------------------------仓库出仓晚点-----------------------------------------------
  --派件端监控-应发票数明细
  INSERT INTO TT_PROCESS_RESULT_N
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
    SELECT C.REPORT_DT, C.ZONE_CODE, COUNT(1) WAYBILL_CNT, 3, 1
      FROM (SELECT /* +USE_HASH(B,D) */
             TRUNC(D.BATCH_BEGIN_DATE) REPORT_DT, D.ZONE_CODE
              FROM TT_WAYBILL_BASIS B, TT_DELIVERY_BILL_DETAIL D
             WHERE B.WAYBILL_NO = D.WAYBILLNO
               AND B.IS_NATIONAL = 'Y'
               AND B.SEND_TIME >= SEND_DT
                  --应发标志
               AND D.DIM_PLAN_SEND_FLAG LIKE '1-%-%'
               AND D.BATCH_BEGIN_DATE >= DELIVERY_DT
               AND D.BATCH_BEGIN_DATE < MAX_DELIVERY_DT) C
     GROUP BY C.ZONE_CODE, C.REPORT_DT;

  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          SQL%ROWCOUNT,
                          'INSERT INTO TT_PROCESS_RESULT_N 3');
  COMMIT;

  ----------------------------------------------延误派送---------------------------------------------------
  --派件端监控-出仓量明细
  INSERT INTO TT_PROCESS_RESULT_N
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
    SELECT D.REPORT_DT, D.ZONE_CODE, COUNT(1) WAYBILL_CNT, 4, 1
      FROM (SELECT /*+ USE_HASH(B,DE) */
             TRUNC(DE.QUERY_DATE) REPORT_DT, DE.ZONE_CODE
              FROM TT_WAYBILL_BASIS B, TT_DELIVERY_DETAIL DE
             WHERE B.WAYBILL_NO = DE.WAY_BILL_NO
               AND B.IS_NATIONAL = 'Y'
               AND B.SEND_TIME >= SEND_DT
               AND DE.QUERY_DATE >= DELIVERY_DT
               AND DE.QUERY_DATE < MAX_DELIVERY_DT) D
     GROUP BY D.ZONE_CODE, D.REPORT_DT;

  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          SQL%ROWCOUNT,
                          'INSERT INTO TT_PROCESS_RESULT_N 4');
  COMMIT;

  ---------------------------------------------------临时表包含运力key,运单号，日期-------------------------
  --删除临时表
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_UNLOAD_DETAIL_NATIONAL';
  --先插入卸车数据
  INSERT INTO TEMP_UNLOAD_DETAIL_NATIONAL
    (PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
    SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL_UNLOAD,B) FULL(TT_CONVEYANCE_DETAIL_UNLOAD)*/
     PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
      FROM TT_CONVEYANCE_DETAIL_UNLOAD
     WHERE PLAN_DATE >= CONVEYAN_DT
       AND PLAN_DATE < MAX_CONVEYAN_DT
       AND EXISTS (SELECT 1
              FROM TT_WAYBILL_BASIS B
             WHERE B.SEND_TIME >= SEND_DT
               AND B.IS_NATIONAL = 'Y'
               AND B.WAYBILL_NO = BILL_NO);
  COMMIT;

  --再插入漏卸数据
  INSERT INTO TEMP_UNLOAD_DETAIL_NATIONAL
    (PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
    SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL,B) FULL(TT_CONVEYANCE_DETAIL)*/
     PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
      FROM TT_CONVEYANCE_DETAIL
     WHERE DATA_TYPE = 4
       AND PLAN_DATE >= CONVEYAN_DT
       AND PLAN_DATE < MAX_CONVEYAN_DT
       AND EXISTS (SELECT 1
              FROM TT_WAYBILL_BASIS B
             WHERE B.SEND_TIME >= SEND_DT
               AND B.IS_NATIONAL = 'Y'
               AND B.WAYBILL_NO = BILL_NO);

  COMMIT;

  ---------------------------------------------车辆到达晚点-------------------------------------------------
  --运力监控中运力类型为干支线的实到票数明细数据 (无车辆管理区，则记在始发方网点) 实到（卸车+漏卸）
  INSERT INTO TT_PROCESS_RESULT_N
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
    SELECT H.REPORT_DT, H.ZONE_CODE, COUNT(1) WAYBILL_CNT, 5, 1
      FROM (SELECT /*+ USE_HASH(T,S)*/
             TRUNC(T.PLAN_DATE) REPORT_DT,
             NVL(S.VEHICLE_AREA, S.SRC_ZONE_CODE) ZONE_CODE
              FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
             WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
               AND T.PLAN_DATE = S.PLAN_DATE
                  --干支线
               AND S.CONVEYANCE_TYPE = 4
               AND S.PLAN_DATE >= CONVEYAN_DT
               AND S.PLAN_DATE < MAX_CONVEYAN_DT) H
     GROUP BY H.ZONE_CODE, H.REPORT_DT;

  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          SQL%ROWCOUNT,
                          ' INSERT INTO TT_PROCESS_RESULT_N 5 ');
  COMMIT;

  ---------------------------------------------运力管理处处理量-------------------------------------------
  --航管处理量=提货票数+实发票数+应发票数
  --应发票数(中转应发)
  --删除运力中转应发临时表
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_DEPART_INTERNATIONAL';
  INSERT INTO TEMP_DEPART_INTERNATIONAL
    (PLAN_DATE, ZONE_CODE, WAYBILL_NO, SN)
    SELECT /*+ FULL(TD) FULL(TA) USE_HASH(TD,TA) */
     TA.PLAN_DATE_TYPE_DATE,
     TD.ZONE_CODE,
     TD.WAYBILL_NO,
     ROW_NUMBER() OVER(PARTITION BY TD.WAYBILL_NO ORDER BY TD.REPORT_DT) SN
      FROM TT_TRANSFER_PLAN_DEPART_DETAIL TD, TT_CONVEYANCE_ATTATCH TA
     WHERE TA.PLAN_DATE_TYPE = 2
       AND TA.PLAN_DATE_TYPE_DATE >= CONVEYAN_DT
       AND TA.PLAN_DATE_TYPE_DATE < MAX_CONVEYAN_DT
       AND TD.REPORT_DT >= TRANSFER_DT
       AND TD.REPORT_DT < MAX_TRANSFER_DT
       AND TD.ZONE_CODE = TA.SRC_ZONE_CODE
       AND TD.CONVEYANCE_NAME = TA.CONVEYANCE_NAME
       AND TD.LINE_CODE = TA.LINE_CODE
       AND EXISTS (SELECT 1
              FROM TT_WAYBILL_BASIS B
             WHERE B.SEND_TIME >= SEND_DT
               AND B.IS_NATIONAL = 'Y'
               AND B.WAYBILL_NO = TD.WAYBILL_NO);
  COMMIT;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_LOAD_INTERNATIONAL';
  --实发票数(实发就是装车)
  --先插入运力明细数据
  INSERT INTO TEMP_LOAD_INTERNATIONAL
    (PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
    SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL,B) FULL(TT_CONVEYANCE_DETAIL)*/
     PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
      FROM TT_CONVEYANCE_DETAIL
     WHERE DATA_TYPE = 1
       AND PLAN_DATE >= CONVEYAN_DT
       AND PLAN_DATE < MAX_CONVEYAN_DT
       AND EXISTS (SELECT 1
              FROM TT_WAYBILL_BASIS B
             WHERE B.SEND_TIME >= SEND_DT
               AND B.IS_NATIONAL = 'Y'
               AND B.WAYBILL_NO = BILL_NO);
  COMMIT;

  --再插入装车明细数据
  INSERT INTO TEMP_LOAD_INTERNATIONAL
    (PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
    SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL_LOAD,B) FULL(TT_CONVEYANCE_DETAIL_LOAD)*/
     PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
      FROM TT_CONVEYANCE_DETAIL_LOAD
     WHERE PLAN_DATE >= CONVEYAN_DT
       AND PLAN_DATE < MAX_CONVEYAN_DT
       AND EXISTS (SELECT 1
              FROM TT_WAYBILL_BASIS B
             WHERE B.SEND_TIME >= SEND_DT
               AND B.IS_NATIONAL = 'Y'
               AND B.WAYBILL_NO = BILL_NO);
  COMMIT;

  --先删除临时表的数据再向临时表中插入数据
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_INTERNATIONAL';
  --实发票数(实发就是装车)
  --实发票数与应发票数去重
  INSERT INTO TEMP_CONVEYANCE_INTERNATIONAL
    (PLAN_DATE, WAYBILL_NO, ZONE_CODE)
    SELECT NVL(AAA.PLAN_DATE, BBB.PLAN_DATE) PLAN_DATE,
           NVL(AAA.WAYBILL_NO, BBB.WAYBILL_NO) WAYBILL_NO,
           NVL(AAA.SRC_ZONE_CODE, BBB.ZONE_CODE) ZONE_CODE
      FROM (SELECT /*+ USE_HASH(T,A)*/
             A.PLAN_DATE, A.SRC_ZONE_CODE, T.WAYBILL_NO
              FROM TEMP_LOAD_INTERNATIONAL T, TT_CONVEYANCE_SHOW A
             WHERE T.CONVEYANCE_KEY = A.SRC_CONVEYANCE_KEY
               AND T.PLAN_DATE = A.PLAN_DATE
               AND A.PLAN_DATE >= CONVEYAN_DT
               AND A.PLAN_DATE < MAX_CONVEYAN_DT) AAA
      FULL JOIN (SELECT PLAN_DATE, ZONE_CODE, WAYBILL_NO
                   FROM TEMP_DEPART_INTERNATIONAL
                  WHERE SN = 1) BBB
        ON AAA.WAYBILL_NO = BBB.WAYBILL_NO;
  COMMIT;

  --先删除票数临时表
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_CNT_NATIONAL';
  --提货票数(卸车票数+漏卸票数)
  INSERT INTO TEMP_CONVEYANCE_CNT_NATIONAL
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT)
    SELECT F.REPORT_DT, F.ZONE_CODE, COUNT(1) WAYBILL_CNT
      FROM (SELECT /*+USE_HASH(T,S)*/
             TRUNC(T.PLAN_DATE) REPORT_DT, S.SRC_ZONE_CODE ZONE_CODE
              FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
             WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
               AND T.PLAN_DATE = S.PLAN_DATE
               AND S.PLAN_DATE >= CONVEYAN_DT
               AND S.PLAN_DATE < MAX_CONVEYAN_DT) F
     GROUP BY F.ZONE_CODE, F.REPORT_DT;
  COMMIT;

  --实发+应发
  INSERT INTO TEMP_CONVEYANCE_CNT_NATIONAL
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT)
    SELECT TRUNC(F.PLAN_DATE) PLAN_DATE, F.ZONE_CODE, COUNT(1) WAYBILL_CNT
      FROM TEMP_CONVEYANCE_INTERNATIONAL F, TT_WAYBILL_BASIS B
     WHERE B.WAYBILL_NO = F.WAYBILL_NO
       AND B.IS_NATIONAL = ' Y '
       AND B.SEND_TIME >= SEND_DT
     GROUP BY F.PLAN_DATE, F.ZONE_CODE;
  COMMIT;

  --插入数据
  INSERT INTO TT_PROCESS_RESULT_N
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
    SELECT H.REPORT_DT, H.ZONE_CODE, SUM(H.WAYBILL_CNT), 6, 1
      FROM TEMP_CONVEYANCE_CNT_NATIONAL H
     GROUP BY H.REPORT_DT, H.ZONE_CODE;

  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          SQL%ROWCOUNT,
                          ' INSERT INTO TT_PROCESS_RESULT_N 6 ');

  COMMIT;
  --删除临时表的数据
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_DEPART_INTERNATIONAL';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_INTERNATIONAL';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_CNT_NATIONAL';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_LOAD_INTERNATIONAL';

  ----------------------------------------关务处理量--------------------------------------------------------
  --所有网点运力监控中为跨境运输运力的实到票数
  INSERT INTO TT_PROCESS_RESULT_N
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
    SELECT G.REPORT_DT, G.ZONE_CODE, COUNT(1) WAYBILL_CNT, 7, 1
      FROM (SELECT /*+USE_HASH(T,S)*/
             TRUNC(T.PLAN_DATE) REPORT_DT, S.SRC_ZONE_CODE ZONE_CODE
              FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
             WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
               AND T.PLAN_DATE = S.PLAN_DATE
                  --跨境标志
               AND S.CROSS_BORDER = 1
               AND S.PLAN_DATE >= CONVEYAN_DT
               AND S.PLAN_DATE < MAX_CONVEYAN_DT) G
     GROUP BY G.ZONE_CODE, G.REPORT_DT;

  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          SQL%ROWCOUNT,
                          ' INSERT INTO TT_PROCESS_RESULT_N 7 ');

  COMMIT;

  ---------------------------------------运输处理量--------------------------------------------------------
  --运力监控实到票数（卸车+漏卸）
  INSERT INTO TT_PROCESS_RESULT_N
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
    SELECT E.REPORT_DT, E.ZONE_CODE, COUNT(1) WAYBILL_CNT, 9, 1
      FROM (SELECT /*+USE_HASH(T,S)*/
             TRUNC(T.PLAN_DATE) REPORT_DT, S.SRC_ZONE_CODE ZONE_CODE
              FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
             WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
               AND T.PLAN_DATE = S.PLAN_DATE
               AND S.PLAN_DATE >= CONVEYAN_DT
               AND S.PLAN_DATE < MAX_CONVEYAN_DT) E
     GROUP BY E.ZONE_CODE, E.REPORT_DT;

  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          SQL%ROWCOUNT,
                          ' INSERT INTO TT_PROCESS_RESULT_N 9 ');
  COMMIT;

  --删除存放运力key,运单号，日期的临时表
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_UNLOAD_DETAIL_NATIONAL';
  --把过渡表中的结果插入到结果表
  INSERT INTO TT_PROCESS_RESULT
    (REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
    SELECT REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE
      FROM TT_PROCESS_RESULT_N;
  COMMIT;

  PKG_SYS_LOG.EXECUTE_LOG(NULL,
                          'STP_INTERNATIONAL ',
                          SYSDATE,
                          SYSDATE,
                          NULL,
                          NULL,
                          ' END国际件环节处理量日期 ' ||
                          TO_CHAR(TRUNC(P_DT), ' yyyy - mm - dd '));
EXCEPTION
  WHEN OTHERS THEN
    PKG_SYS_LOG.ERROR_LOG(P_PACKAGE_NAME => '',
                          P_PROC_NAME    => ' STP_INTERNATIONAL ',
                          P_EXCEP_DT     => SYSDATE,
                          P_EXCEP_CODE   => SQLCODE,
                          P_EXCEP_DESC   => SQLERRM,
                          P_EXCEP_REMK   => '',
                          P_LINE_NO      => NULL);

end STP_INTERNATIONAL;