数据整合_SP

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;