最近存储过程

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;