CREATE OR REPLACE PACKAGE BODY NETS_TCIMS_UNION_RESULT
IS
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 所有清洗、功能完成之后 屏蔽 操作之前
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_0
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,54,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_0_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_0_TMP
(
SYS_ID,
VT_FACTORY,
VEHICLE_STATUS,
CITY,
SECONDARY_ORG,
THIRD_ORG,
LIST_TYPE
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
A.SYS_ID,
A.VT_FACTORY,
A.VEHICLE_STATUS,
NVL(B.CITY,A.CITY) CITY,
NVL(B.SECONDARY_ORG,A.SECONDARY_ORG) SECONDARY_ORG,
NVL(B.THIRD_ORG,A.THIRD_ORG) THIRD_ORG,
NVL(B.LIST_TYPE,A.LIST_TYPE) LIST_TYPE
FROM IDL_SEP_SRC_DATA A,
PC_CLEAN_SH_YDC_FLAG_STG_TMP B,
IDL_EX_BATCH C
WHERE A.SYS_ID = B.SYS_ID(+)
AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CITY_STG_TMP E
WHERE A.SYS_ID = E.SYS_ID)
AND NOT EXISTS(SELECT 1 FROM PC_COMPARE_REPEAT_STG_TMP D
WHERE A.SYS_ID = D.SYS_ID)
AND A.TCIMS_BATCH_ID = C.TCIMS_BATCH_ID
AND C.SERIES_TYPE = '01'
AND C.BATCH_STATUS = '14'
AND C.PREPARE_FLAG = '1';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,54,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := substr(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,54,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_0;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_0 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_1
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,70,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_1_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_1_TMP
(
SYS_ID,
VEHICLE_NO,
VEHICLE_NO_INTEGRITY,
TEL_NO,
BRAND_TYPE_CODE
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
B.VEHICLE_NO,
B.VEHICLE_NO_INTEGRITY,
C.TEL_NO,
D.BRAND_TYPE_CODE
FROM PC_ENTER_RESULT_STG_0_TMP A,
(SELECT SYS_ID,
VEHICLE_NO,
CITY,
VEHICLE_NO_INTEGRITY
FROM PC_CLEAN_VEHICLE_NO_STG_TMP
WHERE CLEAN_STATUS = '1') B,
(SELECT SYS_ID,
TEL_NO,
CITY
FROM PC_CLEAN_TELEPHONE_STG_TMP
WHERE CLEAN_STATUS = '1') C,
(SELECT SYS_ID,
BRAND_TYPE_CODE
FROM PC_CLEAN_BRAND_TYPE_CD_STG_TMP
WHERE CLEAN_STATUS = '1') D
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,70,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,70,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_1;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_0 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_2
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,71,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_2_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_2_TMP
(
SYS_ID,
USAGE_CODE,
ENGINE_NUMBER,
VEHICLE_FRAME
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
B.USAGE_CODE,
C.ENGINE_NUMBER,
D.VEHICLE_FRAME
FROM PC_ENTER_RESULT_STG_0_TMP A,
(SELECT SYS_ID,
USAGE_CODE,
USAGE_ATTRIBUTE
FROM PC_CLEAN_USAGE_CODE_STG_TMP
WHERE CLEAN_STATUS = '1') B,
PC_CLEAN_ENGINE_NO_STG_TMP C,
PC_CLEAN_VEHICLE_FRAME_STG_TMP D
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,71,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,71,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_2;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_0 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_3
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,72,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_3_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_3_TMP
(
SYS_ID,
VEHICLE_TYPE_CODE,
SALUTATION,
ADDRESS,
IS_ADDRESS_VALID
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
B.VEHICLE_TYPE_CODE,
C.SALUTATION,
D.ADDRESS,
D.IS_ADDRESS_VALID
FROM PC_ENTER_RESULT_STG_0_TMP A,
(SELECT SYS_ID,
VEHICLE_TYPE_CODE,
VEHICLE_TYPE
FROM PC_CLEAN_VEHIC_TYPE_CD_STG_TMP
WHERE CLEAN_STATUS = '1') B,
PC_CLEAN_SALUTATION_STG_TMP C,
PC_CLEAN_ADDRESS_STG_TMP D
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,72,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,72,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_3;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_0 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_4
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,73,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_4_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_4_TMP
(
SYS_ID,
MAIN_DRIVER_SEX,
TON_NUMBER,
SEAT_NUMBER,
VEHICLE_VALUE,
EXHAUST,
VEHICLE_BODY_COLOR
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
B.MAIN_DRIVER_SEX,
C.TON_NUMBER,
C.SEAT_NUMBER,
C.VEHICLE_VALUE,
C.EXHAUST,
D.VEHICLE_BODY_COLOR
FROM PC_ENTER_RESULT_STG_0_TMP A,
PC_CLEAN_DRIVER_SEX_STG_TMP B,
PC_CLEAN_SOME_NUMBER_STG_TMP C,
PC_CLEAN_CAR_COLOR_STG_TMP D
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,73,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,73,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_4;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_0 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_5
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,74,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_5_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_5_TMP
(
SYS_ID,
ID_NUMBER,
SEX,
CUST_DOB
)
SELECT /*+ PUSH_SUBQ PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
B.ID_NUMBER,
NVL(B.SEX,C.SEX) SEX,
NVL(B.CUST_DOB,TO_DATE(D.CUST_DOB,'YYYY-MM-DD')) CUST_DOB
FROM PC_ENTER_RESULT_STG_0_TMP A,
(SELECT SYS_ID,ID_NUMBER,SEX,CUST_DOB
FROM PC_CLEAN_ID_NUMBER_STG_TMP
WHERE CLEAN_STATUS = '1') B, -- 改生日 客户性别
(SELECT SYS_ID,SEX
FROM PC_CLEAN_SEX_STG_TMP ) C,
(SELECT SYS_ID,CUST_DOB
FROM PC_CLEAN_CUST_DOB_STG_TMP
WHERE CLEAN_STATUS = '1' ) D
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,74,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,74,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_5;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_0 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_6
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,75,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_6_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_6_TMP
(
SYS_ID,
FIRST_REGISTER_DATE,
POLICY_END_DATE,
POLICY_EFFECTIVE_DATE,
MAIN_DRIVER_DOB,
DRIVER_LICENSE_FST_ISSUE_DATE
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2)*/
A.SYS_ID,
TO_DATE(B.FIRST_REGISTER_DATE,'YYYY-MM-DD') FIRST_REGISTER_DATE,
TO_DATE(C.POLICY_END_DATE,'YYYY-MM-DD') POLICY_END_DATE,
TO_DATE(D.POLICY_EFFECTIVE_DATE,'YYYY-MM-DD') POLICY_EFFECTIVE_DATE,
TO_DATE(E.MAIN_DRIVER_DOB,'YYYY-MM-DD') MAIN_DRIVER_DOB,
TO_DATE(F.DRIVER_LICENSE_FST_ISSUE_DATE,'YYYY-MM-DD') DRIVER_LICENSE_FST_ISSUE_DATE
FROM PC_ENTER_RESULT_STG_0_TMP A,
(SELECT SYS_ID,FIRST_REGISTER_DATE,POLICY_END_DATE
FROM PC_CLEAN_FST_REG_DATE_STG_TMP
WHERE CLEAN_STATUS = '1') B,
(SELECT SYS_ID,POLICY_END_DATE
FROM PC_CLEAN_POLI_END_DATE_STG_TMP
WHERE CLEAN_STATUS = '1') C,
(SELECT SYS_ID,POLICY_EFFECTIVE_DATE
FROM PC_CLEAN_POLI_EFC_DATE_STG_TMP
WHERE CLEAN_STATUS = '1') D,
(SELECT SYS_ID,MAIN_DRIVER_DOB
FROM PC_CLEAN_MAIN_DRIV_DOB_STG_TMP
WHERE CLEAN_STATUS = '1') E,
(SELECT SYS_ID,DRIVER_LICENSE_FST_ISSUE_DATE
FROM PC_CLEAN_LIC_ISUE_DATE_STG_TMP
WHERE CLEAN_STATUS = '1') F
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+)
AND A.SYS_ID = E.SYS_ID(+)
AND A.SYS_ID = F.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,75,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,75,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_6;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_0 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_7
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,76,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_7_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_7_TMP --- 后续更新需要 判断更新
(
SYS_ID,
CITY,
SECONDARY_ORG,
THIRD_ORG,
LIST_TYPE,
C01_PHONE_RESULT,
C01_SALE_STAGE,
C01_SALE_DECISION,
C01_ADDED_EXPLAIN,
C51_PHONE_RESULT,
C51_SALE_STAGE,
C51_SALE_DECISION,
C51_ADDED_EXPLAIN,
C01_SALE_RESULT_CLASS,
C51_SALE_RESULT_CLASS,
SALE_RESULT_CLASS,
CUST_CLASS
)
SELECT /*+PARALLEL(A 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
A.CITY,
A.SECONDARY_ORG,
A.THIRD_ORG,
A.LIST_TYPE,
C.C01_PHONE_RESULT,
C.C01_SALE_STAGE,
C.C01_SALE_DECISION,
C.C01_ADDED_EXPLAIN,
C.C51_PHONE_RESULT,
C.C51_SALE_STAGE,
C.C51_SALE_DECISION,
C.C51_ADDED_EXPLAIN,
C.C01_SALE_RESULT_CLASS,
C.C51_SALE_RESULT_CLASS,
C.SALE_RESULT_CLASS,
D.CUST_CLASS
FROM PC_ENTER_RESULT_STG_0_TMP A,
--PC_CLEAN_SH_YDC_FLAG_STG_TMP B, (PC_ENTER_RESULT_STG_0_TMP 中已经包含了）
(SELECT SYS_ID,C51_PHONE_RESULT,C51_SALE_STAGE,C51_SALE_DECISION,C51_ADDED_EXPLAIN,
C01_PHONE_RESULT,C01_SALE_STAGE,C01_SALE_DECISION,C01_ADDED_EXPLAIN,
C51_SALE_RESULT_CLASS,C01_SALE_RESULT_CLASS,SALE_RESULT_CLASS
FROM PC_CLEAN_SALE_RESULT_STG_TMP
WHERE CLEAN_STATUS = '1') C,
PC_CLEAN_CUST_CLASS_STG_TMP D
WHERE --A.SYS_ID = B.SYS_ID(+) AND
A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,76,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,76,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_7;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_0 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_8
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,77,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_8_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_8_TMP --- 后续更新需要 判断更新
(
SYS_ID,
AUTOMODEL_NAME,
PURCHASE_PRICE,
FACTORY_LOGO,
VEHICLE_SERIES,
VEHICLE_CLASS_CODE,
CRM_AREA_FLAG,
CUST_NAME,
ORG_FLAG
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
B.AUTOMODEL_NAME,
B.PURCHASE_PRICE,
B.FACTORY_LOGO,
B.VEHICLE_SERIES,
B.VEHICLE_CLASS_CODE,
C.CRM_AREA_FLAG,
D.CUST_NAME,
D.ORG_FLAG
FROM PC_ENTER_RESULT_STG_0_TMP A,
(SELECT SYS_ID,AUTOMODEL_NAME,PURCHASE_PRICE,FACTORY_LOGO,
VEHICLE_SERIES,VEHICLE_CLASS_CODE,IS_PRICE_VALID
FROM PC_CLEAN_CAR_NAME_PRIC_STG_TMP
WHERE CLEAN_STATUS = '1') B,
PC_CLEAN_CRM_AREA_FLAG_STG_TMP C,
(SELECT SYS_ID,CUST_NAME,ORG_FLAG
FROM PC_CLEAN_CUST_NAME_STG_TMP
WHERE CLEAN_STATUS = '1') D
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,77,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,77,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_8;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_1 ,,8 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_9
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,78,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_9_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_9_TMP --- 后续更新需要 判断更新
(
SYS_ID,
VEHICLE_NO,
VEHICLE_NO_INTEGRITY,
TEL_NO,
BRAND_TYPE_CODE,
USAGE_CODE,
ENGINE_NUMBER,
VEHICLE_FRAME,
VEHICLE_TYPE_CODE,
SALUTATION,
ADDRESS,
IS_ADDRESS_VALID,
MAIN_DRIVER_SEX,
TON_NUMBER,
SEAT_NUMBER,
VEHICLE_VALUE,
EXHAUST,
VEHICLE_BODY_COLOR
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2)*/
A.SYS_ID,
B.VEHICLE_NO,
B.VEHICLE_NO_INTEGRITY,
B.TEL_NO,
B.BRAND_TYPE_CODE,
C.USAGE_CODE,
C.ENGINE_NUMBER,
C.VEHICLE_FRAME,
D.VEHICLE_TYPE_CODE,
D.SALUTATION,
D.ADDRESS,
D.IS_ADDRESS_VALID,
E.MAIN_DRIVER_SEX,
E.TON_NUMBER,
E.SEAT_NUMBER,
E.VEHICLE_VALUE,
E.EXHAUST,
E.VEHICLE_BODY_COLOR
FROM PC_ENTER_RESULT_STG_0_TMP A,
PC_ENTER_RESULT_STG_1_TMP B,
PC_ENTER_RESULT_STG_2_TMP C,
PC_ENTER_RESULT_STG_3_TMP D,
PC_ENTER_RESULT_STG_4_TMP E
WHERE A.SYS_ID = B.SYS_ID
AND A.SYS_ID = C.SYS_ID
AND A.SYS_ID = D.SYS_ID
AND A.SYS_ID = E.SYS_ID;
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,78,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,78,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_9;
/***********************************************************
--功能说明:
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 SP_UNITE_PC_ENTER_RESULT_1 ,,8 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT_10
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,79,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_ENTER_RESULT_STG_10_TMP');
INSERT /*+APPEND*/
INTO PC_ENTER_RESULT_STG_10_TMP --- 后续更新需要 判断更新
(
SYS_ID,
ID_NUMBER,
SEX,
CUST_DOB,
FIRST_REGISTER_DATE,
POLICY_END_DATE,
POLICY_EFFECTIVE_DATE,
MAIN_DRIVER_DOB,
DRIVER_LICENSE_FST_ISSUE_DATE,
CITY,
SECONDARY_ORG,
THIRD_ORG,
LIST_TYPE,
C01_PHONE_RESULT,
C01_SALE_STAGE,
C01_SALE_DECISION,
C01_ADDED_EXPLAIN,
C51_PHONE_RESULT,
C51_SALE_STAGE,
C51_SALE_DECISION,
C51_ADDED_EXPLAIN,
C01_SALE_RESULT_CLASS,
C51_SALE_RESULT_CLASS,
SALE_RESULT_CLASS,
CUST_CLASS,
AUTOMODEL_NAME,
PURCHASE_PRICE,
FACTORY_LOGO,
VEHICLE_SERIES,
VEHICLE_CLASS_CODE,
CRM_AREA_FLAG,
CUST_NAME,
ORG_FLAG
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2)*/
A.SYS_ID,
B.ID_NUMBER,
B.SEX,
B.CUST_DOB,
C.FIRST_REGISTER_DATE,
C.POLICY_END_DATE,
C.POLICY_EFFECTIVE_DATE,
C.MAIN_DRIVER_DOB,
C.DRIVER_LICENSE_FST_ISSUE_DATE,
D.CITY,
D.SECONDARY_ORG,
D.THIRD_ORG,
D.LIST_TYPE,
D.C01_PHONE_RESULT,
D.C01_SALE_STAGE,
D.C01_SALE_DECISION,
D.C01_ADDED_EXPLAIN,
D.C51_PHONE_RESULT,
D.C51_SALE_STAGE,
D.C51_SALE_DECISION,
D.C51_ADDED_EXPLAIN,
D.C01_SALE_RESULT_CLASS,
D.C51_SALE_RESULT_CLASS,
D.SALE_RESULT_CLASS,
D.CUST_CLASS,
E.AUTOMODEL_NAME,
E.PURCHASE_PRICE,
E.FACTORY_LOGO,
E.VEHICLE_SERIES,
E.VEHICLE_CLASS_CODE,
E.CRM_AREA_FLAG,
E.CUST_NAME,
E.ORG_FLAG
FROM PC_ENTER_RESULT_STG_0_TMP A,
PC_ENTER_RESULT_STG_5_TMP B,
PC_ENTER_RESULT_STG_6_TMP C,
PC_ENTER_RESULT_STG_7_TMP D,
PC_ENTER_RESULT_STG_8_TMP E
WHERE A.SYS_ID = B.SYS_ID
AND A.SYS_ID = C.SYS_ID
AND A.SYS_ID = D.SYS_ID
AND A.SYS_ID = E.SYS_ID;
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,79,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,79,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT_10;
/***********************************************************
--功能说明: 清洗、屏蔽结束后的整合，供库存比对使用(产险)
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 所有清洗、屏蔽 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_ENTER_RESULT
IS
P_ID NUMBER; -- 日志记录ID
P_ERRMSG VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,83,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_STG_DATA_TMP');
--清洗合并 与比对结果合并 PC_COMPARE_REPEAT_STG_TMP PC_STG_DATA_TMP
INSERT /*+APPEND*/
INTO PC_STG_DATA_TMP
(
SYS_ID,
TCIMS_BATCH_ID,
TCIMS_CUST_ID,
TCIMS_VT_ID,
CITY,
SECONDARY_ORG,
THIRD_ORG,
AREA_INFO,
SRC_TYPE,
LIST_TYPE,
PROVINCE,
CUST_CLASS,
CUST_TYPE,
APPLICANT_PARTY_NO,
CIF2_ID,
CIF2_ID_TYPE,
CUST_NAME,
SEX,
SALUTATION,
MARITAL_STATUS,
CUST_DOB,
POSITION,
ID_TYPE,
ID_NUMBER,
CONTACT_NAME,
TELEPHONE_NUMBER,
FAX_AREA_CODE,
FAX_NUM,
APPLICANT_EMAIL,
ZIP_CODE,
ADDRESS,
REMARK,
DRIVER_LICENSE_NO,
DRIVER_LICENSE_FST_ISSUE_DATE,
DRIVE_VEHICLE_TYPE_CODE,
VEHICLE_NO,
BRAND_TYPE,
BRAND_TYPE_CODE,
USAGE_ATTRIBUTE,
USAGE_CODE,
ATTRIBUTE_CODE,
VEHICLE_TYPE,
VEHICLE_TYPE_CODE,
VEHICLE_CLASS_CODE,
VEHICLE_BODY_COLOR,
AUTOMODEL_NAME,
VEHICLE_MODEL_NAME_CN,
VEHICLE_MODEL_NO,
FACTORY_LOGO,
VEHICLE_SERIES,
PURCHASE_PRICE,
BRAND_TYPE_FIRST_SALE_DATE,
SEAT_NUMBER,
TON_NUMBER,
FULL_CAPACITY,
EXHAUST,
FOREIGN_VEHICLE_NO,
ENGINE_NUMBER,
VEHICLE_FRAME,
DEV_CODE,
REFIX_DESC,
VEHICLE_VALUE,
VEHICLE_REMARK,
VEHICLE_GENERAL_TYPE_CODE,
VEHICLE_SPECIFIC_TYPE_CODE,
MANUFACTURE_COUNTRY,
VT_FACTORY,
VEHICLE_STATUS,
USED_YEARS,
FIRST_REGISTER_DATE,
LEAVE_FACTORY_DATE,
BUY_VEHICLE_DATE,
VEHICLE_RANK_CODE,
VEHICLE_OWNER,
LICENSE_ISSUE_DATE,
MAIN_DRIVER_NO,
MAIN_DRIVER_DOB,
MAIN_DRIVER_SEX,
MAIN_DRIVER_NAME,
DRIVE_AREA_CODE,
INSURED_PERSON_NAME,
INSURED_PERSON_ADDRESS,
C01_POLICY_NO,
C01_DEPARTMENT_CODE,
C01_DEPARTMENT_CHINESE_NAME,
C01_LAST_POLICY_NO,
C01_LAST_YEAR_APPLY_COMPANY,
C01_VALUE_CHINESE_NAME,
C01_SALE_AGENT_CODE,
C01_EMPLOYEE_NAME,
C01_BUSINESS_SRC_CODE,
C01_BUSINESS_SRC_NAME,
C01_BUSINESS_SRC_DETAIL_CODE,
C01_BUSINESS_SRC_DETAIL_NAME,
C01_CHANNEL_SRC_CODE,
C01_CHANNEL_SRC_NAME,
C01_CHANNEL_SRC_DETAIL_CODE,
C01_CHANNEL_SRC_DETAIL_NAME,
C01_INSURANCE_BEGIN_TIME,
C01_INSURANCE_END_TIME,
C01_TOTAL_ACTUAL_PREMIUM,
C01_PREMIUM_INFO,
C01_APPLY_TIME,
C01_UNDERWRITE_TIME,
C01_INPUT_BY_ID,
C01_INPUT_BY,
C51_POLICY_NO,
C51_DEPARTMENT_CODE,
C51_DEPARTMENT_CHINESE_NAME,
C51_LAST_POLICY_NO,
C51_LAST_YEAR_APPLY_COMPANY,
C51_VALUE_CHINESE_NAME,
C51_SALE_AGENT_CODE,
C51_EMPLOYEE_NAME,
C51_BUSINESS_SRC_CODE,
C51_BUSINESS_SRC_NAME,
C51_BUSINESS_SRC_DETAIL_CODE,
C51_BUSINESS_SRC_DETAIL_NAME,
C51_CHANNEL_SRC_CODE,
C51_CHANNEL_SRC_NAME,
C51_CHANNEL_SRC_DETAIL_CODE,
C51_CHANNEL_SRC_DETAIL_NAME,
C51_INSURANCE_BEGIN_TIME,
C51_INSURANCE_END_TIME,
C51_TOTAL_ACTUAL_PREMIUM,
C51_PREMIUM_INFO,
C51_APPLY_TIME,
C51_UNDERWRITE_TIME,
C51_INPUT_BY_ID,
C51_INPUT_BY,
VIOLATION_RATIO,
CLAIM_RATIO,
VIOLATION_PREMIUM_CHANGE,
INSURANCE_TYPE_FLAG,
POLICY_EFFECTIVE_DATE,
POLICY_END_DATE,
PA_APPLY_HISTORY,
PA_LIFE_CLIENT,
BIZ_MODEL,
SUB_BIZMODEL,
EXPIRED_DATE,
CAMPAIGN_ID,
CAMPAIGN_NAME,
GROUP_ID,
CAMPAIGN_SPLIT_NAME,
BATCH_NAME,
TASK_GROUP_ID,
TASK_ID,
IS_CONTACTED,
C51_PHONE_RESULT,
C51_SALE_STAGE,
C51_SALE_DECISION,
C51_ADDED_EXPLAIN,
C01_PHONE_RESULT,
C01_SALE_STAGE,
C01_SALE_DECISION,
C01_ADDED_EXPLAIN,
C01_SALE_RESULT_CLASS,
C51_SALE_RESULT_CLASS,
SALE_RESULT_CLASS,
IS_AGENCY_PHONE,
IS_ADDRESS_VALID,
SHIELD_FLAG,
RISK_TIMES,
LIST_PRIORITY,
VEHICLE_NO_INTEGRITY,
ENGINE_NUMBER_SUBL6,
VEHICLE_FRAME_SUB17,
LIST_TYPE_CODE,
SRC_TYPE_CODE,
CITY_CODE
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2)*/
A.SYS_ID,
A.TCIMS_BATCH_ID,
A.TCIMS_CUST_ID,
A.TCIMS_VT_ID,
C.CITY,
C.SECONDARY_ORG,
C.THIRD_ORG,
C.CRM_AREA_FLAG AREA_INFO,
A.SRC_TYPE,
C.LIST_TYPE,
A.PROVINCE,
C.CUST_CLASS,
C.ORG_FLAG CUST_TYPE,
A.APPLICANT_PARTY_NO,
A.CIF2_ID,
A.CIF2_ID_TYPE,
C.CUST_NAME,
C.SEX,
B.SALUTATION,
A.MARITAL_STATUS,
C.CUST_DOB,
A.POSITION,
A.ID_TYPE,
C.ID_NUMBER,
A.CONTACT_NAME,
B.TEL_NO TELEPHONE_NUMBER,
A.FAX_AREA_CODE,
A.FAX_NUM,
A.APPLICANT_EMAIL,
A.ZIP_CODE,
B.ADDRESS,
A.REMARK,
A.DRIVER_LICENSE_NO,
C.DRIVER_LICENSE_FST_ISSUE_DATE,
A.DRIVE_VEHICLE_TYPE_CODE,
B.VEHICLE_NO,
A.BRAND_TYPE,
B.BRAND_TYPE_CODE,
A.USAGE_ATTRIBUTE,
B.USAGE_CODE,
A.ATTRIBUTE_CODE,
A.VEHICLE_TYPE,
B.VEHICLE_TYPE_CODE,
C.VEHICLE_CLASS_CODE,
B.VEHICLE_BODY_COLOR,
C.AUTOMODEL_NAME,
A.VEHICLE_MODEL_NAME_CN,
A.VEHICLE_MODEL_NO,
C.FACTORY_LOGO,
C.VEHICLE_SERIES,
C.PURCHASE_PRICE,
A.BRAND_TYPE_FIRST_SALE_DATE,
B.SEAT_NUMBER,
B.TON_NUMBER,
A.FULL_CAPACITY,
B.EXHAUST,
FOREIGN_VEHICLE_NO,
B.ENGINE_NUMBER,
B.VEHICLE_FRAME,
A.DEV_CODE,
A.REFIX_DESC,
B.VEHICLE_VALUE,
A.VEHICLE_REMARK,
A.VEHICLE_GENERAL_TYPE_CODE,
A.VEHICLE_SPECIFIC_TYPE_CODE,
A.MANUFACTURE_COUNTRY,
A.VT_FACTORY,
A.VEHICLE_STATUS,
A.USED_YEARS,
C.FIRST_REGISTER_DATE,
A.LEAVE_FACTORY_DATE,
A.BUY_VEHICLE_DATE,
A.VEHICLE_RANK_CODE,
A.VEHICLE_OWNER,
A.LICENSE_ISSUE_DATE,
A.MAIN_DRIVER_NO,
C.MAIN_DRIVER_DOB,
B.MAIN_DRIVER_SEX,
A.MAIN_DRIVER_NAME,
A.DRIVE_AREA_CODE,
A.INSURED_PERSON_NAME,
A.INSURED_PERSON_ADDRESS,
A.C01_POLICY_NO,
A.C01_DEPARTMENT_CODE,
A.C01_DEPARTMENT_CHINESE_NAME,
A.C01_LAST_POLICY_NO,
A.C01_LAST_YEAR_APPLY_COMPANY,
A.C01_VALUE_CHINESE_NAME,
A.C01_SALE_AGENT_CODE,
A.C01_EMPLOYEE_NAME,
A.C01_BUSINESS_SRC_CODE,
A.C01_BUSINESS_SRC_NAME,
A.C01_BUSINESS_SRC_DETAIL_CODE,
A.C01_BUSINESS_SRC_DETAIL_NAME,
A.C01_CHANNEL_SRC_CODE,
A.C01_CHANNEL_SRC_NAME,
A.C01_CHANNEL_SRC_DETAIL_CODE,
A.C01_CHANNEL_SRC_DETAIL_NAME,
A.C01_INSURANCE_BEGIN_TIME,
A.C01_INSURANCE_END_TIME,
A.C01_TOTAL_ACTUAL_PREMIUM,
A.C01_PREMIUM_INFO,
A.C01_APPLY_TIME,
A.C01_UNDERWRITE_TIME,
A.C01_INPUT_BY_ID,
A.C01_INPUT_BY,
A.C51_POLICY_NO,
A.C51_DEPARTMENT_CODE,
A.C51_DEPARTMENT_CHINESE_NAME,
A.C51_LAST_POLICY_NO,
A.C51_LAST_YEAR_APPLY_COMPANY,
A.C51_VALUE_CHINESE_NAME,
A.C51_SALE_AGENT_CODE,
A.C51_EMPLOYEE_NAME,
A.C51_BUSINESS_SRC_CODE,
A.C51_BUSINESS_SRC_NAME,
A.C51_BUSINESS_SRC_DETAIL_CODE,
A.C51_BUSINESS_SRC_DETAIL_NAME,
A.C51_CHANNEL_SRC_CODE,
A.C51_CHANNEL_SRC_NAME,
A.C51_CHANNEL_SRC_DETAIL_CODE,
A.C51_CHANNEL_SRC_DETAIL_NAME,
A.C51_INSURANCE_BEGIN_TIME,
A.C51_INSURANCE_END_TIME,
A.C51_TOTAL_ACTUAL_PREMIUM,
A.C51_PREMIUM_INFO,
A.C51_APPLY_TIME,
A.C51_UNDERWRITE_TIME,
A.C51_INPUT_BY_ID,
A.C51_INPUT_BY,
A.VIOLATION_RATIO,
A.CLAIM_RATIO,
A.VIOLATION_PREMIUM_CHANGE,
A.INSURANCE_TYPE_FLAG,
C.POLICY_EFFECTIVE_DATE,
C.POLICY_END_DATE,
A.PA_APPLY_HISTORY,
A.PA_LIFE_CLIENT,
A.BIZ_MODEL,
A.SUB_BIZMODEL,
A.EXPIRED_DATE,
A.CAMPAIGN_ID,
A.CAMPAIGN_NAME,
A.GROUP_ID,
A.CAMPAIGN_SPLIT_NAME,
A.BATCH_NAME,
A.TASK_GROUP_ID,
A.TASK_ID,
A.IS_CONTACTED,
C.C51_PHONE_RESULT,
C.C51_SALE_STAGE,
C.C51_SALE_DECISION,
C.C51_ADDED_EXPLAIN,
C.C01_PHONE_RESULT,
C.C01_SALE_STAGE,
C.C01_SALE_DECISION,
C.C01_ADDED_EXPLAIN,
C.C01_SALE_RESULT_CLASS,
C.C51_SALE_RESULT_CLASS,
C.SALE_RESULT_CLASS,
A.IS_AGENCY_PHONE,
B.IS_ADDRESS_VALID,
D.SHIELD_FLAG,
A.RISK_TIMES,
E.SRC_CREDIBILITY ,
B.VEHICLE_NO_INTEGRITY,
CASE
WHEN LENGTH(B.ENGINE_NUMBER) >= 6 THEN
SUBSTR(B.ENGINE_NUMBER,-6)
ELSE
NULL
END ENGINE_NUMBER_SUBL6,
CASE
WHEN LENGTH(B.VEHICLE_FRAME) = 17 THEN
B.VEHICLE_FRAME
ELSE
NULL
END VEHICLE_FRAME_SUB17,
C.LIST_TYPE,
A.SRC_TYPE,
C.CITY
FROM IDL_SEP_SRC_DATA A,
PC_ENTER_RESULT_STG_9_TMP B,
PC_ENTER_RESULT_STG_10_TMP C,
PC_IN_SHIELD_UNITE_STG_TMP D, -- 可能存在重复记录
(select t.src_type_code,t.src_credibility from bdl_com_src_type t
where t.data_type='PC') E,
IDL_EX_BATCH F
WHERE A.SYS_ID = B.SYS_ID
AND A.SYS_ID = C.SYS_ID
AND A.SYS_ID = D.SYS_ID(+)
AND A.SRC_TYPE = E.SRC_TYPE_CODE(+)
AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
AND F.SERIES_TYPE = '01'
AND F.BATCH_STATUS = '14'
AND F.PREPARE_FLAG = '1';
COMMIT;
-- 更新批次状态，及数据信息
UPDATE /*+PARALLE(A 4)*/
IDL_EX_BATCH A
SET A.BATCH_STATUS = '13',
A.UPDATED_DATE = SYSDATE,
A.UPDATED_BY = 'SYSTEM'
WHERE A.BATCH_STATUS = '14'
AND A.SERIES_TYPE = '01'
AND A.PREPARE_FLAG = '1';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,83,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,83,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_ENTER_RESULT;
/***********************************************************
--功能说明: 寿险合并
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 所有清洗、功能完成之后 屏蔽 操作之前
--*********************************************************/
PROCEDURE SP_UNITE_LA_ENTER_RESULT_0
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,55,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_ENTER_RESULT_STG_0_TMP');
INSERT /*+APPEND*/
INTO LA_ENTER_RESULT_STG_0_TMP
(
SYS_ID,
DEPARTMENT_CHINESE_NAME,
CONTACT_ADDRESS
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
A.SYS_ID,
A.DEPARTMENT_CHINESE_NAME,
A.CONTACT_ADDRESS
FROM IDL_SEL_SRC_DATA A,
IDL_EX_BATCH B
WHERE NOT EXISTS(SELECT 1 FROM LA_CLEAN_CITY_STG_TMP C
WHERE A.SYS_ID = C.SYS_ID)
AND NOT EXISTS(SELECT 1 FROM LA_COMPARE_REPEAT_STG_TMP D
WHERE A.SYS_ID = D.SYS_ID)
AND A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
AND B.SERIES_TYPE = '02'
AND B.BATCH_STATUS = '14'
AND B.PREPARE_FLAG = '1';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,55,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := substr(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,55,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_UNITE_LA_ENTER_RESULT_0;
/***********************************************************
--功能说明: 寿险合并
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 所有清洗、功能完成之后 屏蔽 操作之前
--*********************************************************/
PROCEDURE SP_UNITE_LA_ENTER_RESULT_1
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,80,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_ENTER_RESULT_STG_1_TMP');
INSERT /*+APPEND*/
INTO LA_ENTER_RESULT_STG_1_TMP
(
SYS_ID,
TEL_NO,
ID_NUMBER,
ID_NUMBER_UPGRADE,
CUST_NAME
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
B.TEL_NO ,
C.ID_NUMBER ,
C.ID_NUMBER_UPGRADE ,
D.CUST_NAME
FROM LA_ENTER_RESULT_STG_0_TMP A,
(SELECT SYS_ID ,TEL_NO, CITY, CLEAN_STATUS
FROM LA_CLEAN_TELEPHONE_STG_TMP
WHERE CLEAN_STATUS = '1') B,
LA_CLEAN_ID_NUMBER_STG_TMP C,
LA_CLEAN_CUST_NAME_STG_TMP D
WHERE A.SYS_ID = B.SYS_ID
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,80,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,80,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_LA_ENTER_RESULT_1;
/***********************************************************
--功能说明: 寿险合并
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 所有清洗、功能完成之后 屏蔽 操作之前
--*********************************************************/
PROCEDURE SP_UNITE_LA_ENTER_RESULT_2
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,81,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_ENTER_RESULT_STG_2_TMP');
INSERT /*+APPEND*/
INTO LA_ENTER_RESULT_STG_2_TMP
(
SYS_ID,
ADDRESS,
IS_ADDRESS_VALID,
CUST_DOB,
SUCCESS_DATE
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
B.ADDRESS,
B.IS_ADDRESS_VALID,
C.CUST_DOB,
D.SUCCESS_DATE
FROM LA_ENTER_RESULT_STG_0_TMP A,
LA_CLEAN_ADDRESS_STG_TMP B,
LA_CLEAN_CUST_DOB_STG_TMP C,
LA_CLEAN_SUCCESS_DATE_STG_TMP D
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,81,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,81,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_LA_ENTER_RESULT_2;
/***********************************************************
--功能说明: 寿险合并
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 所有清洗、功能完成之后 屏蔽 操作之前
--*********************************************************/
PROCEDURE SP_UNITE_LA_ENTER_RESULT_3
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,82,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_ENTER_RESULT_STG_3_TMP');
INSERT /*+APPEND*/
INTO LA_ENTER_RESULT_STG_3_TMP
(
SYS_ID,
TRANSF_SUC_DAY,
SUBMIT_DATE,
LIST_STATUS_CODE
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
B.TRANSF_SUC_DAY ,
C.SUBMIT_DATE ,
D.LIST_STATUS_CODE
FROM LA_ENTER_RESULT_STG_0_TMP A,
LA_CLEAN_TRANS_SUC_DAY_STG_TMP B,
LA_CLEAN_SUBMIT_DATE_STG_TMP C,
LA_CLEAN_LIST_STATUS_STG_TMP D
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.SYS_ID = D.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,82,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,82,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_LA_ENTER_RESULT_3;
/***********************************************************
--功能说明: 清洗、屏蔽结束后的整合，供库存比对使用(寿险)
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在 所有清洗、屏蔽 功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_LA_ENTER_RESULT
IS
P_ID NUMBER; -- 日志记录ID
P_ERRMSG VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,84,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_STG_DATA_TMP');
INSERT /*+APPEND*/
INTO LA_STG_DATA_TMP
(
SYS_ID,
TCIMS_CUST_ID,
CUST_NAME,
SEX,
ID_TYPE,
ID_NUMBER,
CUST_DOB,
CONTACT_ADDRESS,
CONTACT_ZIPCODE,
ADDRESS,
ZIP_CODE,
WORK_UNIT,
TELEPHONE_NUMBER,
DEPARTMENT_CHINESE_NAME,
CLIENT_NO,
APPLICANT_PARTY_NO,
CAMPAIGN_NAME,
BATCH_NAME,
SUPERVISOR_ID,
TEAM_LEADER_ID,
TMR_UM_ID,
PRODUCT_NAME,
CONTACT_DATE,
CALL_RESULT,
SUBMIT_DATE,
CALL_BACK_REMARK,
SUCCESS_DATE,
TRANSFER_SUCCESS_DAY,
LIST_STATUS,
CREDIT_CARD_CALL_DATE,
LIST_CREATED_DATE,
EDUCATIONAL_BACKGROUND,
MARITAL_STATUS,
PROF_GRADE,
ANNUAL_INCOME,
SHIELD_FLAG,
LIST_TYPE,
REGION_CODE,
EMAIL,
TCIMS_BATCH_ID,
LIST_PRIORITY,
SRC_TYPE,
IS_INSURE_FLAG,
IS_SUBMIT_FLAG,
IS_FREE_FLAG,
C
扫一扫
4586
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
