CREATE OR REPLACE PACKAGE BODY NETS_TCIMS_BACK_CLEAN_DATA
IS
SPLIT_CHR VARCHAR2(1) := ',';
/***********************************************************
--功能说明: 整合需手工清洗数据前准备1
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在所有清洗功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_1
IS
P_ID NUMBER; -- 日志记录ID
P_ERRMSG VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,41,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_1_STG_TMP');
--合并数据给sqlserver手工清洗
INSERT /*+APPEND*/
INTO IDL_SEP_BACK_DATA_1_STG_TMP
(SYS_ID,CLEAN_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
A.SYS_ID,
NVL2(B.SYS_ID,'A'||SPLIT_CHR,'')||
NVL2(C.SYS_ID,'B'||SPLIT_CHR,'')||
NVL2(D.SYS_ID,'C'||SPLIT_CHR,'') CLEAN_FLAG
FROM IDL_SEP_SRC_DATA A,
PC_CLEAN_CITY_STG_TMP B,
(SELECT SYS_ID, VEHICLE_NO, CITY,
VEHICLE_NO_INTEGRITY ,CLEAN_STATUS
FROM PC_CLEAN_VEHICLE_NO_STG_TMP
WHERE CLEAN_STATUS = '0') C,
(SELECT DISTINCT SYS_ID --, TEL_NO, CITY,CLEAN_STATUS
FROM PC_CLEAN_TEL_NO_STG_TMP --PC_CLEAN_TELEPHONE_STG_TMP
WHERE CLEAN_STATUS = '0') D, -- 返回手工清洗时使用
--PC_CLEAN_TEL_NO_STG_TMP D
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.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
AND F.SERIES_TYPE = '01'
AND F.BATCH_STATUS = '11'
AND F.PREPARE_FLAG = '1';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,41,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,41,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_PRE_BACK_DATA_1;
/***********************************************************
--功能说明: 整合需手工清洗数据前准备2
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在所有清洗功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_2
IS
P_ID NUMBER; -- 日志记录ID
P_ERRMSG VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,42,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_2_STG_TMP');
--合并数据给sqlserver手工清洗
INSERT /*+APPEND*/
INTO IDL_SEP_BACK_DATA_2_STG_TMP
(SYS_ID,CLEAN_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
A.SYS_ID,
NVL2(B.SYS_ID,'D'||SPLIT_CHR,'')||
NVL2(C.SYS_ID,'E'||SPLIT_CHR,'')||
NVL2(D.SYS_ID,'F'||SPLIT_CHR,'') CLEAN_FLAG
FROM IDL_SEP_SRC_DATA A,
( SELECT SYS_ID ,BRAND_TYPE_CODE, CLEAN_STATUS
FROM PC_CLEAN_BRAND_TYPE_CD_STG_TMP
WHERE CLEAN_STATUS = '0') B,
( SELECT SYS_ID, USAGE_CODE, USAGE_ATTRIBUTE, CLEAN_STATUS
FROM PC_CLEAN_USAGE_CODE_STG_TMP
WHERE CLEAN_STATUS = '0') C,
( SELECT SYS_ID, VEHICLE_TYPE_CODE, VEHICLE_TYPE, CLEAN_STATUS
FROM PC_CLEAN_VEHIC_TYPE_CD_STG_TMP
WHERE CLEAN_STATUS = '0') D,
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.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
AND F.SERIES_TYPE = '01'
AND F.BATCH_STATUS = '11'
AND F.PREPARE_FLAG = '1';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,42,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,42,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_PRE_BACK_DATA_2;
/***********************************************************
--功能说明: 整合需手工清洗数据前准备3
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在所有清洗功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_3
IS
P_ID NUMBER; -- 日志记录ID
P_ERRMSG VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,43,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_3_STG_TMP');
--合并数据给sqlserver手工清洗
INSERT /*+APPEND*/
INTO IDL_SEP_BACK_DATA_3_STG_TMP
(SYS_ID,CLEAN_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
A.SYS_ID,
NVL2(B.SYS_ID,'G'||SPLIT_CHR,'')||
NVL2(C.SYS_ID,'H'||SPLIT_CHR,'')||
NVL2(D.SYS_ID,'I'||SPLIT_CHR,'') CLEAN_FLAG
FROM IDL_SEP_SRC_DATA A,
(SELECT SYS_ID ,FIRST_REGISTER_DATE ,CLEAN_STATUS, POLICY_END_DATE
FROM PC_CLEAN_FST_REG_DATE_STG_TMP
WHERE CLEAN_STATUS = '0') B,
(SELECT SYS_ID ,POLICY_END_DATE ,CLEAN_STATUS
FROM PC_CLEAN_POLI_END_DATE_STG_TMP
WHERE CLEAN_STATUS = '0') C,
(SELECT SYS_ID ,POLICY_EFFECTIVE_DATE ,CLEAN_STATUS
FROM PC_CLEAN_POLI_EFC_DATE_STG_TMP
WHERE CLEAN_STATUS = '0') D,
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.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
AND F.SERIES_TYPE = '01'
AND F.BATCH_STATUS = '11'
AND F.PREPARE_FLAG = '1';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,43,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,43,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_PRE_BACK_DATA_3;
/***********************************************************
--功能说明: 整合需手工清洗数据前准备4
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在所有清洗功能完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_4
IS
P_ID NUMBER; -- 日志记录ID
P_ERRMSG VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,44,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_4_STG_TMP');
--合并数据给sqlserver手工清洗
INSERT /*+APPEND*/
INTO IDL_SEP_BACK_DATA_4_STG_TMP
(SYS_ID,CLEAN_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
A.SYS_ID,
NVL2(B.SYS_ID,'J'||SPLIT_CHR,'')||
NVL2(C.SYS_ID,'K'||SPLIT_CHR,'')||
NVL2(D.SYS_ID,'L'||SPLIT_CHR,'') CLEAN_FLAG
FROM IDL_SEP_SRC_DATA A,
(SELECT SYS_ID, CUST_DOB, CLEAN_STATUS
FROM PC_CLEAN_CUST_DOB_STG_TMP K
WHERE NOT EXISTS(
SELECT 1 FROM PC_CLEAN_ID_NUMBER_STG_TMP E
WHERE K.SYS_ID = E.SYS_ID
AND E.CLEAN_STATUS = '1')
AND CLEAN_STATUS = '0') B,
(SELECT SYS_ID, MAIN_DRIVER_DOB, CLEAN_STATUS
FROM PC_CLEAN_MAIN_DRIV_DOB_STG_TMP
WHERE CLEAN_STATUS = '0') C,
(SELECT SYS_ID, DRIVER_LICENSE_FST_ISSUE_DATE, CLEAN_STATUS
FROM PC_CLEAN_LIC_ISUE_DATE_STG_TMP
WHERE CLEAN_STATUS = '0') D,
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.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
AND F.SERIES_TYPE = '01'
AND F.BATCH_STATUS = '11'
AND F.PREPARE_FLAG = '1';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,44,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,44,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_PRE_BACK_DATA_4;
/***********************************************************
--功能说明: 整合需手工清洗数据(产险)
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在<整合需手工清洗数据前准备1,2,3,4>完成之后
--*********************************************************/
PROCEDURE SP_UNITE_PC_BACK_DATA
IS
P_ID NUMBER; -- 日志记录ID
P_ERRMSG VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,45,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表 无须清空数据
--NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','');
--合并数据给sqlserver手工清洗
INSERT /*+APPEND*/
INTO IDL_SEP_BACK_DATA_TMP
(SYS_ID,CLEAN_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2)*/
A.SYS_ID,
B.CLEAN_FLAG||C.CLEAN_FLAG||D.CLEAN_FLAG||E.CLEAN_FLAG CLEAN_FLAG
FROM IDL_SEP_SRC_DATA A,
IDL_SEP_BACK_DATA_1_STG_TMP B,
IDL_SEP_BACK_DATA_2_STG_TMP C,
IDL_SEP_BACK_DATA_3_STG_TMP D,
IDL_SEP_BACK_DATA_4_STG_TMP 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.SYS_ID = E.SYS_ID(+)
AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
AND F.SERIES_TYPE = '01'
AND F.BATCH_STATUS = '11'
AND F.PREPARE_FLAG = '1';
COMMIT;
--合并数据给sqlserver手工清洗, 提取需要的数据
INSERT /*+APPEND*/
INTO IDL_SQL_SEP_CUST_RETURN --IDL_SEP_BACK_DATA
(
SQL_SYS_ID,
SYS_ID,
TCIMS_BATCH_ID,
TCIMS_CUST_ID,
TCIMS_VT_ID,
CITY,
THIRD_ORG,
SECONDARY_ORG,
PROVINCE,
AREA_INFO,
SRC_TYPE,
LIST_TYPE,
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,
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY,
INVALID_CLEAN_FLAG
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(F 2)*/
A.SQL_SYS_ID,
A.SYS_ID,
A.TCIMS_BATCH_ID,
A.TCIMS_CUST_ID,
A.TCIMS_VT_ID,
A.CITY,
A.THIRD_ORG,
A.SECONDARY_ORG,
A.PROVINCE,
A.AREA_INFO,
A.SRC_TYPE,
A.LIST_TYPE,
A.CUST_CLASS,
A.CUST_TYPE,
A.APPLICANT_PARTY_NO,
A.CIF2_ID,
A.CIF2_ID_TYPE,
A.CUST_NAME,
A.SEX,
A.SALUTATION,
A.MARITAL_STATUS,
A.CUST_DOB,
A.POSITION,
A.ID_TYPE,
A.ID_NUMBER,
A.CONTACT_NAME,
A.TELEPHONE_NUMBER,
A.FAX_AREA_CODE,
A.FAX_NUM,
A.APPLICANT_EMAIL,
A.ZIP_CODE,
A.ADDRESS,
A.REMARK,
A.DRIVER_LICENSE_NO,
A.DRIVER_LICENSE_FST_ISSUE_DATE,
A.DRIVE_VEHICLE_TYPE_CODE,
A.VEHICLE_NO,
A.BRAND_TYPE,
A.BRAND_TYPE_CODE,
A.USAGE_ATTRIBUTE,
A.USAGE_CODE,
A.ATTRIBUTE_CODE,
A.VEHICLE_TYPE,
A.VEHICLE_TYPE_CODE,
A.VEHICLE_CLASS_CODE,
A.VEHICLE_BODY_COLOR,
A.AUTOMODEL_NAME,
A.VEHICLE_MODEL_NAME_CN,
A.VEHICLE_MODEL_NO,
A.FACTORY_LOGO,
A.VEHICLE_SERIES,
A.PURCHASE_PRICE,
A.BRAND_TYPE_FIRST_SALE_DATE,
A.SEAT_NUMBER,
A.TON_NUMBER,
A.FULL_CAPACITY,
A.EXHAUST,
A.FOREIGN_VEHICLE_NO,
A.ENGINE_NUMBER,
A.VEHICLE_FRAME,
A.DEV_CODE,
A.REFIX_DESC,
A.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,
A.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,
A.MAIN_DRIVER_DOB,
A.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,
A.POLICY_EFFECTIVE_DATE,
A.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,
A.C51_PHONE_RESULT,
A.C51_SALE_STAGE,
A.C51_SALE_DECISION,
A.C51_ADDED_EXPLAIN,
A.C01_PHONE_RESULT,
A.C01_SALE_STAGE,
A.C01_SALE_DECISION,
A.C01_ADDED_EXPLAIN,
A.C01_SALE_RESULT_CLASS,
A.C51_SALE_RESULT_CLASS,
A.SALE_RESULT_CLASS,
A.IS_AGENCY_PHONE,
A.IS_ADDRESS_VALID,
A.SHIELD_FLAG,
A.RISK_TIMES,
A.LIST_PRIORITY,
A.CREATED_DATE,
A.CREATED_BY,
A.UPDATED_DATE,
A.UPDATED_BY,
B.CLEAN_FLAG
FROM IDL_SEP_SRC_DATA A,
IDL_SEP_BACK_DATA_TMP B,
IDL_EX_BATCH F
WHERE A.SYS_ID = B.SYS_ID
AND B.CLEAN_FLAG IS NOT NULL
AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
AND F.SERIES_TYPE = '01'
AND F.BATCH_STATUS = '11'
AND F.PREPARE_FLAG = '1';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,45,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,45,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_PC_BACK_DATA;
/***********************************************************
--功能说明: 整合需手工清洗数据(寿险)
--参数说明:
--调用函数:
--修改记录： EX-LIUJIALI001
--注意事项： 顺序在寿险数据清洗完成之后
--*********************************************************/
PROCEDURE SP_UNITE_LA_BACK_DATA
IS
P_ID NUMBER; -- 日志记录ID
P_ERRMSG VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,53,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表 无须清空数据
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEL_BACK_DATA_1_TMP');
--合并数据给sqlserver手工清洗
INSERT /*+APPEND*/
INTO IDL_SEL_BACK_DATA_1_TMP
(SYS_ID,CLEAN_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
A.SYS_ID,
NVL2(B.SYS_ID,'C'||SPLIT_CHR,'')||
NVL2(C.SYS_ID,'A'||SPLIT_CHR,'') CLEAN_FLAG
FROM IDL_SEL_SRC_DATA A,
(SELECT DISTINCT SYS_ID
FROM LA_CLEAN_TEL_NO_STG_TMP
WHERE CLEAN_STATUS = '0') B,
LA_CLEAN_CITY_STG_TMP C,
IDL_EX_BATCH D
WHERE A.SYS_ID = B.SYS_ID(+)
AND A.SYS_ID = C.SYS_ID(+)
AND A.TCIMS_BATCH_ID = D.TCIMS_BATCH_ID
AND D.SERIES_TYPE = '02'
AND D.BATCH_STATUS = '11'
AND D.PREPARE_FLAG = '1';
COMMIT;
--合并数据给sqlserver手工清洗
INSERT /*+APPEND*/
INTO IDL_SQL_SEL_CUST_RETURN --IDL_SEL_BACK_DATA
(
SQL_SYS_ID,
SYS_ID,
TCIMS_BATCH_ID,
TCIMS_CUST_ID,
CLIENT_NO,
APPLICANT_PARTY_NO,
SRC_TYPE,
LIST_TYPE,
LIST_PRIORITY,
CUST_NAME,
SEX,
ID_TYPE,
ID_NUMBER,
CUST_DOB,
WORK_UNIT,
DEPARTMENT_CHINESE_NAME,
LIST_CREATED_DATE,
POSITION,
POSITION_CODE,
EDUCATIONAL_BACKGROUND,
MARITAL_STATUS,
PROF_GRADE,
ANNUAL_INCOME,
CONTACT_ADDRESS,
CONTACT_ZIPCODE,
ADDRESS,
ZIP_CODE,
EMAIL,
SHIELD_FLAG,
CAMPAIGN_NAME,
CAMPAIGN_SPLIT_NAME,
BATCH_NAME,
SUPERVISOR_ID,
TEAM_LEADER_ID,
TMR_UM_ID,
PRODUCT_NAME,
CONTACT_DATE,
CALL_RESULT,
IS_INSURE_FLAG,
IS_SUBMIT_FLAG,
IS_FREE_FLAG,
CALL_BACK_REMARK,
SUCCESS_DATE,
TRANSFER_SUCCESS_DAY,
LIST_STATUS,
REGION_CODE,
CREDIT_CARD_CALL_DATE,
SUBMIT_DATE,
CREATED_DATE,
CREATED_BY,
UPDATED_DATE,
UPDATED_BY,
INVALID_CLEAN_FLAG
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(F 2)*/
A.SQL_SYS_ID,
A.SYS_ID,
A.TCIMS_BATCH_ID,
A.TCIMS_CUST_ID,
A.CLIENT_NO,
A.APPLICANT_PARTY_NO,
A.SRC_TYPE,
A.LIST_TYPE,
A.LIST_PRIORITY,
A.CUST_NAME,
A.SEX,
A.ID_TYPE,
A.ID_NUMBER,
A.CUST_DOB,
A.WORK_UNIT,
A.DEPARTMENT_CHINESE_NAME,
A.LIST_CREATED_DATE,
A.POSITION,
A.POSITION_CODE,
A.EDUCATIONAL_BACKGROUND,
A.MARITAL_STATUS,
A.PROF_GRADE,
A.ANNUAL_INCOME,
A.CONTACT_ADDRESS,
A.CONTACT_ZIPCODE,
A.ADDRESS,
A.ZIP_CODE,
A.EMAIL,
A.SHIELD_FLAG,
A.CAMPAIGN_NAME,
A.CAMPAIGN_SPLIT_NAME,
A.BATCH_NAME,
A.SUPERVISOR_ID,
A.TEAM_LEADER_ID,
A.TMR_UM_ID,
A.PRODUCT_NAME,
A.CONTACT_DATE,
A.CALL_RESULT,
A.IS_INSURE_FLAG,
A.IS_SUBMIT_FLAG,
A.IS_FREE_FLAG,
A.CALL_BACK_REMARK,
A.SUCCESS_DATE,
A.TRANSFER_SUCCESS_DAY,
A.LIST_STATUS,
A.REGION_CODE,
A.CREDIT_CARD_CALL_DATE,
A.SUBMIT_DATE,
A.CREATED_DATE,
A.CREATED_BY,
A.UPDATED_DATE,
A.UPDATED_BY,
B.CLEAN_FLAG
FROM IDL_SEL_SRC_DATA A,
IDL_SEL_BACK_DATA_1_TMP B,
IDL_EX_BATCH F
WHERE B.CLEAN_FLAG IS NOT NULL
AND A.SYS_ID = B.SYS_ID
AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
AND F.SERIES_TYPE = '02'
AND F.BATCH_STATUS = '11'
AND F.PREPARE_FLAG = '1';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,53,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
P_ERRMSG := SUBSTR(SQLERRM,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,53,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
RAISE;
END SP_UNITE_LA_BACK_DATA;
END NETS_TCIMS_BACK_CLEAN_DATA;
