数据清洗2
create or replace package body NETS_TCIMS_PC_TRANSFORM
IS
? list_TAB_SCR_TYPE_PATTERN_D TAB_SCR_TYPE_PATTERN_D;
? list_TAB_TMP_TYPE_PATTERN_D TAB_TMP_SCR_TYPE_PATTERN_D;
?
? /***********************************************************
? --功能说明:?? 转换、标识 上海异地车
? --参数说明:
? --调用函数:
? --修改记录：? ex-liujiali001
? --注意事项：? 此项操作 必须在 电话号码清洗 操作后执行? (其中有一个号码匹配也行)
? --*********************************************************/
? PROCEDURE SP_TRANSFORM_SH_YDC_FLAG
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,29,NULL,'01',NULL,NULL,NULL,NULL);
??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_SH_YDC_FLAG_STG_TMP');
??? --抽取记录编号、机构、电话字段? 数据
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SH_YDC_FLAG_1_TMP
?????????? (
??????????? SYS_ID,
??????????? SECONDARY_ORG,
??????????? TEL_NO
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
???????????? A.SYS_ID SYS_ID,
???????????? TO_SINGLE_BYTE(TRIM(A.SECONDARY_ORG)) SECONDARY_ORG,
???????????? B.TEL_NO TEL_NO
??????? FROM IDL_SEP_SRC_DATA A,
???????????? (SELECT SPLIT_SYSID, SYS_ID, CODE, TEL_NO, CITY, CLEAN_STATUS
?????????????? FROM PC_CLEAN_TEL_NO_STG_TMP
????????????? WHERE CODE IS NULL) B,?? --使用的分开的电话号码
??????????? IDL_EX_BATCH???? C
?????? WHERE A.SYS_ID = B.SYS_ID(+)
???????? AND A.TCIMS_BATCH_ID = C.TCIMS_BATCH_ID
???????? AND C.SERIES_TYPE = '01'
???????? AND C.BATCH_STATUS = '11'
???????? AND C.PREPARE_FLAG = '1';
??? COMMIT;
??? --筛选指定的二级机构
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SH_YDC_FLAG_2_TMP
?????????? (
??????????? SYS_ID,
??????????? TEL_NO
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? A.TEL_NO
??????? FROM PC_CLEAN_SH_YDC_FLAG_1_TMP A,
???????????? BDL_RULE_YDC_ORG B
?????? WHERE A.SECONDARY_ORG = B.SECONDARY_ORG_code;
??? COMMIT;
??? --筛选电话号码 PC_CLEAN_TEL_NO_STG_TMP? 电话号码表? code 为空 即为手机
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SH_YDC_FLAG_3_TMP
?????????? (
??????????? SYS_ID,
??????????? CITY,
??????????? SECONDARY_ORG,
??????????? THIRD_ORG,
??????????? LIST_TYPE
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID SYS_ID,
???????????? '202001' CITY,???????????? --上海
???????????? '202'??? SECONDARY_ORG,??? --上海
???????????? '202001' THIRD_ORG,???????? --上海
???????????? 'YDC'??? LIST_TYPE??????? --修改名单类型? 非新加标识 '上海YDC' SH_YDC_FLAG
??????? FROM PC_CLEAN_SH_YDC_FLAG_2_TMP A,
???????????? BDL_RULE_CITY_REF_TEL B
?????? WHERE SUBSTR(A.TEL_NO,1,B.BEGINNO_LEN) >= B.BEGINNO
???????? AND SUBSTR(A.TEL_NO,1,B.ENDNO_LEN) <= B.ENDNO???
???????? AND B.CITY = '202001';?????????????
??? COMMIT;
??? -- 更新 stg 表
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SH_YDC_FLAG_STG_TMP
?????????? (
??????????? SYS_ID,
??????????? CITY,
??????????? SECONDARY_ORG,
??????????? THIRD_ORG,
??????????? LIST_TYPE
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID???????????????????????????? SYS_ID,????????????
???????????? 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,
???????????? (SELECT DISTINCT SYS_ID,LIST_TYPE,CITY,SECONDARY_ORG,THIRD_ORG
??????????????? FROM PC_CLEAN_SH_YDC_FLAG_3_TMP) B
?????? WHERE A.SYS_ID = B.SYS_ID(+);
??? COMMIT;
??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,29,NULL,'02',NULL,NULL,NULL,NULL);
? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,29,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_TRANSFORM_SH_YDC_FLAG;
? /***********************************************************
? --功能说明:?? 清洗、转换 电销结果
? --参数说明:
? --调用函数:
? --修改记录：? ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_TRANSFORM_SALE_RESULT
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,23,NULL,'01',NULL,NULL,NULL,NULL);
??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_SALE_RESULT_STG_TMP');
??? --抽取数据,并清除特殊字符 去回车、Tab，全半角空格
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SALE_RESULT_1_TMP
?????????? (
??????????? 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
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID SYS_ID,
???????????? REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C51_PHONE_RESULT), CHR(13), ''), CHR(10), ''),' ','') C51_PHONE_RESULT,
???????????? REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C51_SALE_STAGE), CHR(13), ''), CHR(10), ''),' ','') C51_SALE_STAGE,
???????????? REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C51_SALE_DECISION), CHR(13), ''), CHR(10), ''),' ','') C51_SALE_DECISION,
???????????? REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C51_ADDED_EXPLAIN), CHR(13), ''), CHR(10), ''),' ','') C51_ADDED_EXPLAIN,
???????????? REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C01_PHONE_RESULT), CHR(13), ''), CHR(10), ''),' ','') C01_PHONE_RESULT,
???????????? REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C01_SALE_STAGE), CHR(13), ''), CHR(10), ''),' ','') C01_SALE_STAGE,
???????????? REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C01_SALE_DECISION), CHR(13), ''), CHR(10), ''),' ','') C01_SALE_DECISION,
???????????? REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C01_ADDED_EXPLAIN), CHR(13), ''), CHR(10), ''),' ','') C01_ADDED_EXPLAIN
??????? FROM IDL_SEP_SRC_DATA A,
??????????? IDL_EX_BATCH???? B
?????? WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
???????? AND B.SERIES_TYPE = '01'
???????? AND B.BATCH_STATUS = '11'
???????? AND B.PREPARE_FLAG = '1';
??? COMMIT;
??? -- 匹配电销结果代码? 确定子任务电销结果分类
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SALE_RESULT_2_TMP
?????????? (
??????????? SYS_ID,
??????????? C51_SALE_RESULT_CLASS,
??????????? C01_SALE_RESULT_CLASS
??????????? )
????? SELECT /*+PARALLEL(A 4)*/
???????????? A.SYS_ID SYS_ID,
???????????? ( SELECT DISTINCT B.SALE_RESULT_CLASS_CODE
???????????????? FROM BDL_RULE_SALE_RESULT_RELATION B
???????????????? WHERE B.PHONE_RESULT_CODE||B.SALE_STAGE_CODE||B.SALE_DECISION_CODE =
?????????????????????? NVL2(B.PHONE_RESULT_CODE,A.C51_PHONE_RESULT,'')||
?????????????????????? NVL2(B.SALE_STAGE_CODE,A.C51_SALE_STAGE,'')||
?????????????????????? NVL2(B.SALE_DECISION_CODE,A.C51_SALE_DECISION,'')
???????????? ) C51_SALE_RESULT_CLASS,
???????????? ( SELECT DISTINCT B.SALE_RESULT_CLASS_CODE
???????????????? FROM BDL_RULE_SALE_RESULT_RELATION B
???????????????? WHERE B.PHONE_RESULT_CODE||B.SALE_STAGE_CODE||B.SALE_DECISION_CODE =
?????????????????????? NVL2(B.PHONE_RESULT_CODE,A.C01_PHONE_RESULT,'')||
?????????????????????? NVL2(B.SALE_STAGE_CODE,A.C01_SALE_STAGE,'')||
?????????????????????? NVL2(B.SALE_DECISION_CODE,A.C01_SALE_DECISION,'')
???????????? ) C01_SALE_RESULT_CLASS
??????? FROM PC_CLEAN_SALE_RESULT_1_TMP A;
??? COMMIT;
??? -- 匹配 电销结果分类
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SALE_RESULT_3_TMP
?????????? (
??????????? SYS_ID,
??????????? C51_SALE_RESULT_CLASS,
??????????? C01_SALE_RESULT_CLASS,
??????????? SALE_RESULT_CLASS
?????????? )
????? SELECT /*+PARALLEL(A 4)*/
???????????? A.SYS_ID SYS_ID,
???????????? A.C51_SALE_RESULT_CLASS,
???????????? A.C01_SALE_RESULT_CLASS,
???????????? (SELECT B.SALE_RESULT_CLASS
?????????????? FROM BDL_RULE_SALE_RESULT_CLASS_REL B
?????????????? WHERE B.C01_SALE_RESULT_CLASS||B.C51_SALE_RESULT_CLASS =
???????????????????? A.C01_SALE_RESULT_CLASS||A.C51_SALE_RESULT_CLASS) SALE_RESULT_CLASS
??????? FROM PC_CLEAN_SALE_RESULT_2_TMP A;
??? COMMIT;
??? -- 标识 记录中 电销结果分类 及无效电销结果
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SALE_RESULT_STG_TMP
?????????? (
????????????? 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,
????????????? CLEAN_STATUS
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID SYS_ID,
???????????? 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,
???????????? B.C51_SALE_RESULT_CLASS,
???????????? B.C01_SALE_RESULT_CLASS,
???????????? B.SALE_RESULT_CLASS,
???????????? CASE
?????????????? WHEN B.SYS_ID IS NULL THEN
??????????????????? '0'
?????????????? ELSE
??????????????????? '1'
???????????? END CLEAN_STATUS
??????? FROM PC_CLEAN_SALE_RESULT_1_TMP A,
???????????? PC_CLEAN_SALE_RESULT_3_TMP B
?????? WHERE A.SYS_ID = B.SYS_ID(+);
??? COMMIT;
??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,23,NULL,'02',NULL,NULL,NULL,NULL);
? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,23,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_TRANSFORM_SALE_RESULT;
? /***********************************************************
? --功能说明:?? 清洗、转换 客户分类
? --参数说明:
? --调用函数:
? --修改记录：? ex-liujiali001
? --注意事项：? 本操作? 须在 《清洗、转换 电销结果》 <转换、标识 上海异地车> 后执行
? --*********************************************************/
? PROCEDURE SP_CLEAN_TRANSFORM_CUST_CLASS
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,30,NULL,'01',NULL,NULL,NULL,NULL);
??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CUST_CLASS_STG_TMP');
??? -- 获取 电销结果分类 、名单类型 数据
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_CLASS_1_TMP
?????????? (
??????????? SYS_ID,
??????????? LIST_TYPE,
??????????? VEHICLE_NO,
??????????? IS_CONTACTED,
??????????? BIZ_MODEL,
??????????? SUB_BIZMODEL,
??????????? SALE_RESULT_CLASS,
??????????? VEHICLE_NO_INTEGRITY,
??????????? CUST_CLASS
?????????? )
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2)*/
?????????? A.SYS_ID,
?????????? NVL(D.LIST_TYPE,replace(A.LIST_TYPE,'/','')) LIST_TYPE,
?????????? NVL(C.VEHICLE_NO,A.VEHICLE_NO) VEHICLE_NO,
?????????? A.IS_CONTACTED,
?????????? A.BIZ_MODEL,
?????????? replace(A.SUB_BIZMODEL,'/','') SUB_BIZMODEL,
?????????? NVL(B.SALE_RESULT_CLASS,replace(A.SALE_RESULT_CLASS,'/','')) SALE_RESULT_CLASS,
?????????? NVL(C.VEHICLE_NO_INTEGRITY,0) VEHICLE_NO_INTEGRITY,
?????????? '' CUST_CLASS
????? FROM IDL_SEP_SRC_DATA A,
?????????? (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, CLEAN_STATUS
????????????? FROM PC_CLEAN_SALE_RESULT_STG_TMP
??????????? WHERE CLEAN_STATUS = '1' ) B,
?????????? (SELECT SYS_ID, VEHICLE_NO, CITY, CLEAN_STATUS, VEHICLE_NO_INTEGRITY
????????????? FROM PC_CLEAN_VEHICLE_NO_STG_TMP
?????????? WHERE CLEAN_STATUS = '1' ) C,
?????????? PC_CLEAN_SH_YDC_FLAG_STG_TMP D,
??????????? IDL_EX_BATCH???? E
????? 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 = E.TCIMS_BATCH_ID
???????? AND E.SERIES_TYPE = '01'
???????? AND E.BATCH_STATUS = '11'
???????? AND E.PREPARE_FLAG = '1';
??? COMMIT;
?? -- 与规则匹配 标识 客户分类?? 优先级 1?????? 原有客户分类的是否要处理
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_CLASS_2_TMP
?????????? (
??????????? SYS_ID,
??????????? CUST_CLASS
?????????? )
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID,
?????????? B.CUST_CLASS
????? FROM PC_CLEAN_CUST_CLASS_1_TMP A,
?????????? BDL_RULE_CUST_CLASS B
???? WHERE (B.SALE_RESULT_CLASS IS NULL OR INSTR(B.SALE_RESULT_CLASS,A.SALE_RESULT_CLASS) > 0)
?????? AND (B.IS_CONTACTED? IS NULL OR INSTR(B.IS_CONTACTED,A.IS_CONTACTED) > 0)
?????? AND (B.BIZ_MODEL? IS NULL OR INSTR(B.BIZ_MODEL,A.BIZ_MODEL) > 0)
?????? AND (B.SUB_BIZMODEL? IS NULL OR INSTR(B.SUB_BIZMODEL,A.SUB_BIZMODEL) > 0)
?????? AND INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
?????? AND B.PRIORITY = '1';
??? COMMIT;
?? -- 与规则匹配 标识 客户分类?? 优先级 2
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_CLASS_2_TMP
?????????? (
??????????? SYS_ID,
??????????? CUST_CLASS
?????????? )?????
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
?????????? A.SYS_ID,
?????????? C.CUST_CLASS
????? FROM BDL_RULE_CRM_A_CLASS_CUST B,
?????????? PC_CLEAN_CUST_CLASS_1_TMP A,
?????????? BDL_RULE_CUST_CLASS C
???? WHERE A.VEHICLE_NO = B.VEHICLE_NO
?????? AND INSTR(C.LIST_TYPE,A.LIST_TYPE) > 0
?????? AND A.VEHICLE_NO_INTEGRITY = '1'
?????? AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
?????????????????????? WHERE D.SYS_ID = A.SYS_ID)
?????? AND C.COMPARE_RULE = '1'
?????? AND C.PRIORITY = '2';
??? COMMIT;
?? -- 与规则匹配 标识 客户分类? 优先级 3
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_CLASS_2_TMP
?????????? (
??????????? SYS_ID,
??????????? CUST_CLASS
?????????? )?????
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID,
?????????? B.CUST_CLASS
????? FROM PC_CLEAN_CUST_CLASS_1_TMP A,
?????????? BDL_RULE_CUST_CLASS B
???? WHERE (B.SALE_RESULT_CLASS IS NULL OR INSTR(B.SALE_RESULT_CLASS,A.SALE_RESULT_CLASS) > 0)
?????? AND (B.IS_CONTACTED? IS NULL OR INSTR(B.IS_CONTACTED,A.IS_CONTACTED) > 0)
?????? AND (B.BIZ_MODEL? IS NULL OR INSTR(B.BIZ_MODEL,A.BIZ_MODEL) > 0)
?????? AND (B.SUB_BIZMODEL? IS NULL OR INSTR(B.SUB_BIZMODEL,A.SUB_BIZMODEL) > 0)
?????? AND INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
?????? AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
?????????????????????? WHERE D.SYS_ID = A.SYS_ID)
?????? AND B.PRIORITY = '3';
??? COMMIT;
?? -- 与规则匹配 标识 客户分类? 优先级 4
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_CLASS_2_TMP
?????????? (
??????????? SYS_ID,
??????????? CUST_CLASS
?????????? )?????
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID,
?????????? B.CUST_CLASS
????? FROM PC_CLEAN_CUST_CLASS_1_TMP A,
?????????? BDL_RULE_CUST_CLASS B
???? WHERE (B.SALE_RESULT_CLASS IS NULL OR INSTR(B.SALE_RESULT_CLASS,A.SALE_RESULT_CLASS) > 0)
?????? AND (B.IS_CONTACTED? IS NULL OR INSTR(B.IS_CONTACTED,A.IS_CONTACTED) > 0)
?????? AND (B.BIZ_MODEL? IS NULL OR INSTR(B.BIZ_MODEL,A.BIZ_MODEL) > 0)
?????? AND (B.SUB_BIZMODEL? IS NULL OR INSTR(B.SUB_BIZMODEL,A.SUB_BIZMODEL) > 0)
?????? AND INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
?????? AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
?????????????????????? WHERE D.SYS_ID = A.SYS_ID)
?????? AND B.PRIORITY = '4';
??? COMMIT;
?? -- 与规则匹配 标识 客户分类? 优先级 5
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_CLASS_2_TMP
?????????? (
??????????? SYS_ID,
??????????? CUST_CLASS
?????????? )?????
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID,
?????????? B.CUST_CLASS
????? FROM PC_CLEAN_CUST_CLASS_1_TMP A,
?????????? BDL_RULE_CUST_CLASS B
???? WHERE (B.SALE_RESULT_CLASS IS NULL OR INSTR(B.SALE_RESULT_CLASS,A.SALE_RESULT_CLASS) > 0)
?????? AND (B.IS_CONTACTED? IS NULL OR INSTR(B.IS_CONTACTED,A.IS_CONTACTED) > 0)
?????? AND (B.BIZ_MODEL? IS NULL OR INSTR(B.BIZ_MODEL,A.BIZ_MODEL) > 0)
?????? AND (B.SUB_BIZMODEL? IS NULL OR INSTR(B.SUB_BIZMODEL,A.SUB_BIZMODEL) > 0)
?????? AND INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
?????? AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
?????????????????????? WHERE D.SYS_ID = A.SYS_ID)
?????? AND B.PRIORITY = '5';
??? COMMIT;
?? -- 与规则匹配 标识 客户分类? 优先级 8
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_CLASS_2_TMP
?????????? (
??????????? SYS_ID,
??????????? CUST_CLASS
?????????? )?????
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID,
?????????? B.CUST_CLASS
????? FROM PC_CLEAN_CUST_CLASS_1_TMP A,
?????????? BDL_RULE_CUST_CLASS B
???? WHERE INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
?????? AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
?????????????????????? WHERE D.SYS_ID = A.SYS_ID)
?????? AND B.PRIORITY = '8';
??? COMMIT;
??? ---- 整理 标识 客户分类
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_CLASS_STG_TMP
?????????? (
??????????? SYS_ID,
??????????? CUST_CLASS
?????????? )
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID,
?????????? B.CUST_CLASS
????? FROM PC_CLEAN_CUST_CLASS_1_TMP A,
?????????? PC_CLEAN_CUST_CLASS_2_TMP B
???? WHERE A.SYS_ID = B.SYS_ID(+);
??? COMMIT;
??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,30,NULL,'02',NULL,NULL,NULL,NULL);
? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,30,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_TRANSFORM_CUST_CLASS;
? /***********************************************************
? --功能说明:?? 合并清洗后的 CITY/客户身份证件号码/车牌号码
???????????????????????????? /联系人地址/电话号码 供地区标识使用
? --参数说明:
? --调用函数:
? --修改记录：? EX-LIUJIALI001
? --注意事项：? 顺序在清洗功能完成之后?? , 地区标识 之前
? --*********************************************************/
? PROCEDURE SP_UNITE_FIELD_FOR_AREA_INFO
? IS
??? P_ID??????? NUMBER;???????? -- 日志记录ID
??? P_ERRMSG??? VARCHAR2(500);? -- 错误记录
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,25,NULL,'01',NULL,NULL,NULL,NULL);
??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_UNITE_MANY_FIELD_1_STG_TMP');
??? -- 合并清洗结果，并进行记录有效性标识（出现需要手工清洗的字段即为无效，目前只有车牌号码、电话号码）
??? INSERT /*+APPEND*/
????? INTO PC_UNITE_MANY_FIELD_1_STG_TMP
????? ( SYS_ID,
??????? CITY,
??????? ID_NUMBER,
??????? VEHICLE_NO,
??????? ADDRESS,
??????? TEL_NO,
??????? STATUS)
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2)*/
?????????? A.SYS_ID,
?????????? A.CITY,
?????????? B.ID_NUMBER,
?????????? C.VEHICLE_NO,
?????????? D.ADDRESS,
?????????? --E.CODE,
?????????? E.TEL_NO,
?????????? CASE
???????????? WHEN C.SYS_ID IS NULL OR E.SYS_ID IS NULL THEN
????????????????? '0'
???????????? ELSE
????????????????? '1'
?????????? END STATUS
????? FROM IDL_SEP_SRC_DATA??????????? A,
?????????? (SELECT SYS_ID, ID_NUMBER, CLEAN_STATUS, SEX, CUST_DOB
????????????? FROM PC_CLEAN_ID_NUMBER_STG_TMP
?????????? WHERE CLEAN_STATUS = '1') B,?? --身份证号码
?????????? (SELECT SYS_ID, VEHICLE_NO, CITY, CLEAN_STATUS, VEHICLE_NO_INTEGRITY
????????????? FROM PC_CLEAN_VEHICLE_NO_STG_TMP
?????????? WHERE CLEAN_STATUS = '1') C,?? --车牌号码
?????????? (SELECT SYS_ID, ADDRESS, IS_ADDRESS_VALID
????????????? FROM PC_CLEAN_ADDRESS_STG_TMP?
?????????? WHERE IS_ADDRESS_VALID = '1')? D,?? --联系人地址
?????????? (SELECT SYS_ID, TEL_NO, CITY, CLEAN_STATUS
????????????? FROM PC_CLEAN_TELEPHONE_STG_TMP
?????????? WHERE CLEAN_STATUS = '1') E,??? --电话号码（使用合并的电话号码）
?????????? IDL_EX_BATCH????????????? F?????
?????????? --PC_CLEAN_TEL_NO_STG_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(+)
?????? 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,25,NULL,'02',NULL,NULL,NULL,NULL);
? EXCEPTION
???? WHEN OTHERS THEN
??????? P_ERRMSG := SUBSTR(SQLERRM,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,25,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
??????? RAISE;
? END SP_UNITE_FIELD_FOR_AREA_INFO;
? /***********************************************************
? --功能说明:?? 地区标识
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --注意事项：? 必须在 NETS_TCIMS_PC_TRANSFORM.SP_UNITE_FIELD_FOR_AREA_INFO 执行完后，才能执行
? --*********************************************************/
? PROCEDURE SP_CLEAN_TRANSFORM_AREA_INFO
? IS
??? list_CITY_NAME Tab_CITY_NAME;
??? CURSOR CUR_SCR_TYPE_PATTERN_D(v_CITY_NAME VARCHAR2) IS
????? SELECT CITY ,
???????????? ID_NUMBER ,
???????????? VEHICLE_NO ,
???????????? VEHICLE_NO_NOTLIKE,
???????????? ADDRESS ,
???????????? ADDRESS_NOTLIKE ,
???????????? TELEPHONE_NUMBER ,
???????????? AREA_FLAG
??????? FROM BDL_RULE_REF_AREA_FLAG_2
?????? WHERE CITY = v_CITY_NAME;
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,28,NULL,'01',NULL,NULL,NULL,NULL);
??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CRM_AREA_FLAG_STG_TMP');
??? --地区标识（不可直接使用LIKE，需要经过解析），其中CITY_NAME与NDC_REF_AREA_FLAG_1互斥
??? SELECT CITY BULK COLLECT
????? INTO list_CITY_NAME
????? FROM (SELECT DISTINCT CITY FROM BDL_RULE_REF_AREA_FLAG_2
??????????? INTERSECT
??????????? SELECT DISTINCT CITY FROM PC_UNITE_MANY_FIELD_1_STG_TMP); --单行电话记录
??? FOR i IN 1 .. list_CITY_NAME.COUNT LOOP
?????? OPEN CUR_SCR_TYPE_PATTERN_D(list_CITY_NAME(i));
????? FETCH CUR_SCR_TYPE_PATTERN_D BULK COLLECT
??????? INTO list_TAB_SCR_TYPE_PATTERN_D;
????? CLOSE CUR_SCR_TYPE_PATTERN_D;
????? IF list_TAB_SCR_TYPE_PATTERN_D.COUNT > 0 THEN
??????? DELETE FROM PC_CLEAN_CRM_AREA_FLAG_1_TMP;
??????? COMMIT;
??????? DELETE FROM PC_SCR_TYPE_PATTERN_D_TMP;
??????? COMMIT;
??????? --解析规则
??????? TRY_FORMAT_SCR_RULES;
??????? COMMIT;
???????? SELECT CITY,
??????????????? ID_NUMBER,
??????????????? ID_NUMBER_LIST,
??????????????? VEHICLE_NO,
??????????????? VEHICLE_NO_LIST,
??????????????? VEHICLE_NO_NOTLIKE,
??????????????? VEHICLE_NO_NOTLIKE_LIST,
??????????????? ADDRESS,
??????????????? ADDRESS_LIST,
??????????????? ADDRESS_NOTLIKE,
??????????????? ADDRESS_NOTLIKE_LIST,
??????????????? TELEPHONE_NUMBER,
??????????????? EXEC_TEL_NO_LIST,
??????????????? CRM_AREA? BULK COLLECT
????????? INTO list_TAB_TMP_TYPE_PATTERN_D
????????? FROM PC_SCR_TYPE_PATTERN_D_TMP;
??????? INSERT /*+APPEND*/
????????? INTO PC_CLEAN_CRM_AREA_FLAG_1_TMP
?????????????? (
???????????????? SYS_ID,
???????????????? CRM_AREA_FLAG
?????????????? )
????????? SELECT /*+PARALLEL(A,4)*/
???????????????? A.SYS_ID,
???????????????? TRY_MATCH_AREA_FLAG(A.ID_NUMBER ,A.VEHICLE_NO ,A.VEHICLE_NO ,
???????????????????????????????????? A.ADDRESS ,A.ADDRESS ,A.TEL_NO) CRM_AREA_FLAG
??????????? FROM PC_UNITE_MANY_FIELD_1_STG_TMP A
?????????? WHERE A.CITY = list_CITY_NAME(i);
??????? COMMIT;
???????
????? --地区标识（不可直接使用LIKE）
????? INSERT /*+APPEND*/
??????? INTO PC_CLEAN_CRM_AREA_FLAG_STG_TMP
?????????????? (
???????????????? SYS_ID,
???????????????? CRM_AREA_FLAG
?????????????? )???????
????????? SELECT /*+PARALLEL(A,4)*/
???????????????? A.SYS_ID,
???????????????? A.CRM_AREA_FLAG CRM_AREA_FLAG
??????????? FROM PC_CLEAN_CRM_AREA_FLAG_1_TMP A
?????????? WHERE A.CRM_AREA_FLAG IS NOT NULL;
??????? COMMIT;
???????
????? END IF;
??? END LOOP;
??? --地区标识（可直接使用LIKE），其中CITY_NAME与NDC_REF_AREA_FLAG_2互斥
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CRM_AREA_FLAG_STG_TMP
?????????????? (
???????????????? SYS_ID,
???????????????? CRM_AREA_FLAG
?????????????? )???????????
????? SELECT SYS_ID,
???????????? CRM_AREA_FLAG
??????? FROM (SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????????????? A.SYS_ID,
???????????????????? B.AREA_FLAG CRM_AREA_FLAG ,
???????????????????? ROW_NUMBER()OVER(PARTITION BY A.SYS_ID ORDER BY A.SYS_ID DESC NULLS LAST) RN
??????????????? FROM PC_UNITE_MANY_FIELD_1_STG_TMP A,
???????????????????? BDL_RULE_REF_AREA_FLAG_1 B
?????????????? WHERE A.CITY = B.CITY
???????????????? AND ((B.ID_NUMBER IS NOT NULL AND A.ID_NUMBER LIKE B.ID_NUMBER) OR B.ID_NUMBER IS NULL)
???????????????? AND ((B.VEHICLE_NO IS NOT NULL AND A.VEHICLE_NO LIKE B.VEHICLE_NO) OR B.VEHICLE_NO IS NULL)
???????????????? AND ((B.VEHICLE_NO_NOTLIKE IS NOT NULL AND A.VEHICLE_NO NOT LIKE B.VEHICLE_NO_NOTLIKE) OR
???????????????????? B.VEHICLE_NO_NOTLIKE IS NULL)
???????????????? AND ((B.ADDRESS IS NOT NULL AND A.ADDRESS LIKE B.ADDRESS) OR B.ADDRESS IS NULL)
???????????????? AND ((B.ADDRESS_NOTLIKE IS NOT NULL AND A.ADDRESS NOT LIKE B.ADDRESS_NOTLIKE) OR
???????????????????? B.ADDRESS_NOTLIKE IS NULL)
???????????????? AND ((B.TELEPHONE_NUMBER IS NOT NULL AND
???????????????????? (((A.CODE || '-' || A.TEL_NO) LIKE B.TELEPHONE_NUMBER AND A.CODE IS NOT NULL)
???????????????????? OR (A.CODE IS NULL AND A.TEL_NO LIKE B.TELEPHONE_NUMBER))?????????????????????
???????????????????? OR B.TELEPHONE_NUMBER IS NULL)))
?????? WHERE RN = 1;
??? COMMIT;
???
??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,28,NULL,'02',NULL,NULL,NULL,NULL);
? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,28,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_TRANSFORM_AREA_INFO;
? /*
??? 部分规则解析
? */
? PROCEDURE TRY_FORMAT_SCR_RULES IS
??? var_idno???????????????? VARCHAR2(100) := NULL;
??? var_idno_LIST??????????? RangeLikeObjList := NULL;
??? var_car_no?????????????? VARCHAR2(100) := NULL;
??? var_car_no_LIST????????? RangeLikeObjList := NULL;
??? var_car_no_notlike?????? VARCHAR2(100) := NULL;
??? var_car_no_notlike_LIST? RangeLikeObjList := NULL;
??? var_address????????????? VARCHAR2(100) := NULL;
??? var_address_LIST???????? RangeLikeObjList := NULL;
??? var_address_notlike????? VARCHAR2(100) := NULL;
??? var_address_notlike_LIST RangeLikeObjList := NULL;
??? var_tel_no?????????????? VARCHAR2(100) := NULL;
??? var_tel_no_LIST????????? RangeLikeObjList := NULL;
? begin
??? FOR i IN list_TAB_SCR_TYPE_PATTERN_D.FIRST .. list_TAB_SCR_TYPE_PATTERN_D.LAST LOOP
????? --nets_客户身份证件号码
????? TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
?????????????????????????????????? .id_number ,
?????????????????????????????????? var_idno,
?????????????????????????????????? var_idno_LIST);
????? --nets_车牌号码
????? TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
?????????????????????????????????? .vehicle_no ,
?????????????????????????????????? var_car_no,
?????????????????????????????????? var_car_no_LIST);
????? --notlike_nets_车牌号码
????? TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
?????????????????????????????????? .vehicle_no_notlike ,
?????????????????????????????????? var_car_no_notlike,
?????????????????????????????????? var_car_no_notlike_LIST);
????? --nets_联系人地址
????? TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
?????????????????????????????????? .address ,
?????????????????????????????????? var_address,
?????????????????????????????????? var_address_LIST);
????? --notlike_nets_联系人地址
????? TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
?????????????????????????????????? .address_notlike ,
?????????????????????????????????? var_address_notlike,
?????????????????????????????????? var_address_notlike_LIST);
????? --电话号码
????? TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
?????????????????????????????????? .telephone_number ,
?????????????????????????????????? var_tel_no,
?????????????????????????????????? var_tel_no_LIST);
????? INSERT INTO PC_SCR_TYPE_PATTERN_D_TMP
??????? ( CITY,
????????? ID_NUMBER,
????????? ID_NUMBER_LIST,
????????? VEHICLE_NO,
????????? VEHICLE_NO_LIST,
????????? VEHICLE_NO_NOTLIKE,
????????? VEHICLE_NO_NOTLIKE_LIST,
????????? ADDRESS,
????????? ADDRESS_LIST,
????????? ADDRESS_NOTLIKE,
????????? ADDRESS_NOTLIKE_LIST,
????????? TELEPHONE_NUMBER,
????????? EXEC_TEL_NO_LIST ,
????????? CRM_AREA)
????? VALUES
??????? (list_TAB_SCR_TYPE_PATTERN_D(i).city ,
???????? var_idno,
???????? var_idno_LIST,
???????? var_car_no,
???????? var_car_no_LIST,
???????? var_car_no_notlike,
???????? var_car_no_notlike_LIST,
???????? var_address,
???????? var_address_LIST,
???????? var_address_notlike,
???????? var_address_notlike_LIST,
???????? var_tel_no,
???????? var_tel_no_LIST,
???????? list_TAB_SCR_TYPE_PATTERN_D(i).AREA_FLAG);
??? END LOOP;
? end TRY_FORMAT_SCR_RULES;
? /*
???? 分解规则
? */
? PROCEDURE TRY_GET_SCR_RangeLikeObjList(v_INSTR?? IN VARCHAR2,
???????????????????????????????????????? v_OUTSTR? OUT VARCHAR2,
???????????????????????????????????????? v_OUTLIST OUT RangeLikeObjList) IS
??? aArray???????? RangeLikeObjList := RangeLikeObjList();
??? i_start??????? integer;
??? i_end????????? integer;
??? inx??????????? integer;
??? var_rangestr?? VARCHAR2(600) := '';
??? var_torangestr VARCHAR2(1000) := '';
??? var_tmpstr???? VARCHAR2(1000) := '';
? begin
??? var_tmpstr := v_INSTR;
??? inx??????? := 1;
??? i_start??? := 0;
??? i_end????? := 0;
??? IF var_tmpstr IS NULL THEN
????? v_OUTSTR? := NULL;
????? v_OUTLIST := NULL;
????? RETURN;
??? ELSE
????? --存在类似粤J-[012][0129][0129][0012][0012]
????? LOOP
??????? i_start := INSTR(var_tmpstr, '[');
??????? EXIT WHEN i_start = 0;
??????? i_end := INSTR(var_tmpstr, ']');
??????? var_rangestr?? := SUBSTR(var_tmpstr,
???????????????????????????????? i_start + 1,
???????????????????????????????? i_end - i_start - 1);
??????? var_torangestr := '';
??????? FOR j IN 1 .. LENGTH(var_rangestr) LOOP
????????? var_torangestr := var_torangestr || '''' ||
??????????????????????????? SUBSTR(var_rangestr, j, 1) || ''',';
??????? END LOOP;
??????? var_torangestr := SUBSTR(var_torangestr,
???????????????????????????????? 1,
???????????????????????????????? LENGTH(var_torangestr) - 1);
??????? var_tmpstr???? := SUBSTR(var_tmpstr, 1, i_start - 1) || '_' ||
????????????????????????? SUBSTR(var_tmpstr, i_end + 1);
??????? aArray.Extend;
??????? aArray(inx) := RangeLikeObj(i_start, var_torangestr);
??????? inx := inx + 1;
????? END LOOP;
????? v_OUTSTR? := var_tmpstr;
????? v_OUTLIST := aArray;
??? END IF;
? END;
? /*
???? 标识地区
? */
? --六个字段如果不是NULL，要AND匹配
? function TRY_MATCH_AREA_FLAG(v_IDNO??????????? VARCHAR2,
?????????????????????????????? v_CAR_NO????????? VARCHAR2,
?????????????????????????????? v_CAR_NO_NOTLIKE? VARCHAR2,
?????????????????????????????? v_ADDRESS???????? VARCHAR2,
?????????????????????????????? v_ADDRESS_NOTLIKE VARCHAR2,
?????????????????????????????? v_TEL_NO????????? VARCHAR2) return varchar2 IS
??? var_tmpstr????????????? VARCHAR2(1000) := '';
??? str_array?????????????? NETS_TCIMS_COM_CLEANOUT.type_array;
??? var_idno??????????????? VARCHAR2(1) := '0';
??? var_car_no????????????? VARCHAR2(1) := '0';
??? var_car_no_notlike????? VARCHAR2(1) := '0';
??? var_car_no_notlike_STR? VARCHAR2(100) := '';
??? var_address???????????? VARCHAR2(1) := '0';
??? var_address_notlike???? VARCHAR2(1) := '0';
??? var_address_notlike_STR VARCHAR2(100) := '';
??? var_tel_no????????????? VARCHAR2(1) := '0';
??? var_tmp???????????????? VARCHAR2(100) := '';
??? aArray????????????????? RangeLikeObjList := RangeLikeObjList();
? begin
??? FOR i IN list_TAB_TMP_TYPE_PATTERN_D.FIRST .. list_TAB_TMP_TYPE_PATTERN_D.LAST LOOP
????? var_idno??????????????? := '0';
????? var_car_no????????????? := '0';
????? var_car_no_notlike????? := '0';
????? var_address???????????? := '0';
????? var_address_notlike???? := '0';
????? var_tel_no????????????? := '0';
????? var_car_no_notlike_STR? := '';
????? var_address_notlike_STR := '';
????? --nets_客户身份证件号码
????? var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).ID_NUMBER;
????? IF var_tmpstr IS NULL THEN
??????? var_idno := '1';
????? ELSE
??????? IF v_IDNO LIKE var_tmpstr THEN
????????? var_idno := '1';
????????? aArray?? := list_TAB_TMP_TYPE_PATTERN_D(i).ID_NUMBER_LIST;
????????? FOR k IN 1 .. aArray.COUNT LOOP
??????????? IF SUBSTR(v_IDNO, aArray(k).id, 1) NOT IN (aArray(k).rangestr) THEN
????????????? var_idno := '0';
????????????? EXIT;
??????????? END IF;
????????? END LOOP;
??????? END IF;
????? END IF;
????? --nets_车牌号码
????? var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).VEHICLE_NO;
????? aArray.DELETE();
????? IF var_tmpstr IS NULL THEN
??????? var_car_no := '1';
????? ELSE
??????? IF v_CAR_NO LIKE var_tmpstr THEN
????????? var_car_no := '1';
????????? aArray???? := list_TAB_TMP_TYPE_PATTERN_D(i).VEHICLE_NO_LIST;
????????? FOR k IN 1 .. aArray.COUNT LOOP
??????????? IF SUBSTR(v_CAR_NO, aArray(k).id, 1) NOT IN
?????????????? (aArray(k).rangestr) THEN
????????????? var_car_no := '0';
????????????? EXIT;
??????????? END IF;
????????? END LOOP;
??????? END IF;
????? END IF;
????? --notlike_nets_车牌号码
????? var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).VEHICLE_NO_NOTLIKE;
????? aArray.DELETE();
????? IF var_tmpstr IS NULL THEN
??????? var_car_no_notlike := '1';
????? ELSE
??????? IF v_CAR_NO_NOTLIKE LIKE var_tmpstr THEN
????????? var_car_no_notlike := '0';
????????? aArray???????????? := list_TAB_TMP_TYPE_PATTERN_D(i)
?????????????????????????????? .VEHICLE_NO_NOTLIKE_LIST;
????????? FOR k IN 1 .. aArray.COUNT LOOP
??????????? IF SUBSTR(v_CAR_NO_NOTLIKE, aArray(k).id, 1) IN
?????????????? (aArray(k).rangestr) THEN
????????????? var_car_no_notlike_STR := var_car_no_notlike_STR || '0';
??????????? ELSE
????????????? var_car_no_notlike_STR := var_car_no_notlike_STR || '1';
??????????? END IF;
????????? END LOOP;
??????? ELSE
????????? var_car_no_notlike := '1';
??????? END IF;
????? END IF;
????? --nets_联系人地址
????? var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).ADDRESS;
????? aArray.DELETE();
????? IF var_tmpstr IS NULL THEN
??????? var_address := '1';
????? ELSE
??????? IF v_ADDRESS LIKE var_tmpstr THEN
????????? var_address := '1';
????????? aArray????? := list_TAB_TMP_TYPE_PATTERN_D(i).ADDRESS_LIST;
????????? FOR k IN 1 .. aArray.COUNT LOOP
??????????? IF SUBSTR(v_ADDRESS, aArray(k).id, 1) NOT IN
?????????????? (aArray(k).rangestr) THEN
????????????? var_address := '0';
????????????? EXIT;
??????????? END IF;
????????? END LOOP;
??????? END IF;
????? END IF;
????? --notlike_nets_联系人地址
????? var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).ADDRESS_NOTLIKE;
????? aArray.DELETE();
????? IF var_tmpstr IS NULL THEN
??????? var_address_notlike := '1';
????? ELSE
??????? IF v_ADDRESS_NOTLIKE LIKE var_tmpstr THEN
????????? var_address_notlike := '0';
????????? aArray????????????? := list_TAB_TMP_TYPE_PATTERN_D(i)
??????????????????????????????? .ADDRESS_NOTLIKE_LIST;
????????? FOR k IN 1 .. aArray.COUNT LOOP
??????????? IF SUBSTR(v_ADDRESS_NOTLIKE, aArray(k).id, 1) IN
?????????????? (aArray(k).rangestr) THEN
????????????? var_address_notlike_STR := var_address_notlike_STR || '0';
??????????? ELSE
????????????? var_address_notlike_STR := var_address_notlike_STR || '1';
??????????? END IF;
????????? END LOOP;
??????? ELSE
????????? var_address_notlike := '1';
??????? END IF;
????? END IF;
????? --电话号码
????? var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).TELEPHONE_NUMBER;
????? aArray.DELETE();
????? IF var_tmpstr IS NULL THEN
??????? var_tel_no := '1';
????? ELSE
??????? --v_TEL_NO 是由多个电话号码用“,”连接起来的，要拆分匹配
??????? str_array := NETS_TCIMS_COM_CLEANOUT.SPLIT_STR(v_TEL_NO, '/');
??????? IF str_array.count > 0 THEN
????????? aArray := list_TAB_TMP_TYPE_PATTERN_D(i).EXEC_TEL_NO_LIST;
????????? FOR ii in str_array.first .. str_array.last LOOP
??????????? IF str_array(ii) IS NOT NULL AND str_array(ii) LIKE var_tmpstr THEN
????????????? var_tel_no := '1';
????????????? FOR k IN 1 .. aArray.COUNT LOOP
??????????????? IF SUBSTR(str_array(ii), aArray(k).id, 1) NOT IN
?????????????????? (aArray(k).rangestr) THEN
????????????????? var_tel_no := '0';
????????????????? EXIT;
??????????????? END IF;
????????????? END LOOP;
??????????? END IF;
??????????? IF var_tel_no = '1' THEN
????????????? EXIT;
??????????? END IF;
????????? END LOOP;
??????? END IF;
????? END IF;
????? IF var_idno = '1' AND var_car_no = '1' AND
???????? (var_car_no_notlike = '1' OR
???????? (var_car_no_notlike = '0' AND var_car_no_notlike_STR IS NOT NULL AND
???????? INSTR(var_car_no_notlike_STR, '1') > 0)) AND var_address = '1' AND
???????? (var_address_notlike = '1' OR
???????? (var_address_notlike = '0' AND
???????? var_address_notlike_STR IS NOT NULL AND
???????? INSTR(var_address_notlike_STR, '1') > 0)) AND var_tel_no = '1' THEN
??????? var_tmp := list_TAB_TMP_TYPE_PATTERN_D(i).CRM_AREA;
??????? EXIT;
????? END IF;
??? END LOOP;
??? return(var_tmp);
? end TRY_MATCH_AREA_FLAG;
? /***********************************************************
? --功能说明:?? 清洗客户姓名_1(外部程序清洗前的操作)
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_CUST_NAME_1
? IS
?
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
??? v_spec_chr???????? VARCHAR2(200);????????
??? v_spec_chr_length? NUMBER(10);
???
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,24,NULL,'01',NULL,NULL,NULL,NULL);
??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CUST_NAME_3_TMP');
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CUST_NAME_4_TMP');
???
??? v_spec_chr := NETS_TCIMS_COM_CLEANOUT.V_SPECIAL_CHR_EN||
????????????????? NETS_TCIMS_COM_CLEANOUT.V_SPECIAL_CHR_ZN||
????????????????? NETS_TCIMS_COM_CLEANOUT.v_small_letter||
????????????????? NETS_TCIMS_COM_CLEANOUT.v_capital_letter||
????????????????? NETS_TCIMS_COM_CLEANOUT.v_figure;
???
???? v_spec_chr_length := length(v_spec_chr);
???
??? -- 获取姓名列的源数据
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_NAME_1_TMP
?????????? (
???????????? SYS_ID,
???????????? CUST_NAME
?????????? )
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID,
?????????? REPLACE(TRANSLATE(A.CUST_NAME,
???????????????????????????? CHR(43683) || CHR(63918) || CHR(42235) || CHR(43682) ||
???????????????????????????? CHR(42107) || CHR(44979) || chr(44789),
???????????????????????????? '?????? '),
?????????????????? ' ',
?????????????????? '') CUST_NAME
??????? FROM IDL_SEP_SRC_DATA A,
??????????? IDL_EX_BATCH???? B
?????? WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
???????? AND B.SERIES_TYPE = '01'
???????? AND B.BATCH_STATUS = '11'
???????? AND B.PREPARE_FLAG = '1';
??? COMMIT;
?
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_NAME_6_TMP
?????????? (
???????????? SYS_ID,
???????????? CUST_NAME
?????????? )
????? SELECT /*+PARALLEL(A,4)*/
???????????? SYS_ID,
???????????? REPLACE(TRANSLATE(REPLACE(REPLACE(TO_SINGLE_BYTE(CUST_NAME),
????????????????????????????????????????????????? CHR(13),''),
?????????????????????????????????????? CHR(10),''),
?????????????????????????????? v_spec_chr,
?????????????????????????????? rpad(' ',v_spec_chr_length,' ')),
???????????????????? ' ',
???????????????????? '') CUST_NAME
??????? FROM PC_CLEAN_CUST_NAME_1_TMP A;
??? COMMIT;
?
??? -- 根据"客户姓名前后缀"索引表去掉姓名前后缀
???? INSERT /*+APPEND*/
?????? INTO PC_CLEAN_CUST_NAME_8_TMP
?????????? (
???????????? SYS_ID,
???????????? CUST_NAME
?????????? )??????
?????? SELECT SYS_ID, CUST_NAME
???????? FROM (SELECT SYS_ID,
????????????????????? CUST_NAME,
????????????????????? ROW_NUMBER() OVER(PARTITION BY SYS_ID ORDER BY SYS_ID DESC) RN
???????????????? FROM (SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
????????????????????????????? A.SYS_ID, SUBSTR(A.CUST_NAME, B.LEN + 1) CUST_NAME
???????????????????????? FROM PC_CLEAN_CUST_NAME_6_TMP A,
????????????????????????????? BDL_RULE_REF_NAME_PPFIX B
??????????????????????? WHERE B.PPFIX = SUBSTR(A.CUST_NAME, 1, B.LEN)))
??????? WHERE RN = 1;
???? COMMIT;
????
???? INSERT /*+APPEND*/
?????? INTO PC_CLEAN_CUST_NAME_9_TMP
?????????? (
???????????? SYS_ID,
???????????? CUST_NAME
?????????? )??????
???? SELECT /*+PARALLEL(C 2) PARALLEL(D 2)*/
??????????? C.SYS_ID,
??????????? NVL(D.CUST_NAME, C.CUST_NAME) CUST_NAME
???????? FROM PC_CLEAN_CUST_NAME_6_TMP C,
????????????? PC_CLEAN_CUST_NAME_8_TMP D
??????? WHERE C.SYS_ID = D.SYS_ID(+);
???? COMMIT;
?
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_NAME_2_TMP
?????????? (
???????????? SYS_ID,
???????????? CUST_NAME
?????????? )?????
????? SELECT SYS_ID,
???????????? CUST_NAME
??????? FROM (SELECT SYS_ID,
???????????????????? CUST_NAME,
???????????????????? ROW_NUMBER() OVER(PARTITION BY SYS_ID ORDER BY SYS_ID DESC) RN
??????????????? FROM (SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????????????????? A.SYS_ID,
?????????????????????? SUBSTR(A.CUST_NAME, 1, LENGTH(A.CUST_NAME) - B.LEN) CUST_NAME
??????????????????????? FROM PC_CLEAN_CUST_NAME_9_TMP A,
???????????????????????????? BDL_RULE_REF_NAME_PPFIX B
?????????????????????? WHERE B.PPFIX = SUBSTR(A.CUST_NAME, -B.LEN)))
?????? WHERE RN = 1;
??? COMMIT;
?
??? --取去掉姓名前后缀的名称 或者没有匹配上的合并
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_NAME_3_TMP
?????????? (
???????????? SYS_ID,
???????????? CUST_NAME
?????????? )?????
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID, NVL(B.CUST_NAME, A.CUST_NAME) CUST_NAME
????? FROM PC_CLEAN_CUST_NAME_6_TMP A,
?????????? PC_CLEAN_CUST_NAME_2_TMP B
???? WHERE A.SYS_ID = B.SYS_ID(+);
??? COMMIT;
???
??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,24,NULL,'02',NULL,NULL,NULL,NULL);
? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,24,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_CUST_NAME_1;
? /***********************************************************
? --功能说明:?? 清洗客户姓名_2(外部程序清洗后的操作)
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_CUST_NAME_2
? IS
?
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,27,NULL,'01',NULL,NULL,NULL,NULL);
??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CUST_NAME_STG_TMP');
???
??? --将"张王李赵"姓氏概率较大的字符开头的,且姓名长度为4的判断为个人
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_NAME_7_TMP
?????????? (
??????????? SYS_ID,
??????????? CUST_NAME,
??????????? ORG_FLAG
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? A.CUST_NAME,
???????????? CASE
?????????????? WHEN B.ORG_FLAG IS NULL AND
??????????????????? SUBSTR(A.CUST_NAME, 1, 1) IN ('张', '王', '李', '赵') AND
??????????????????? LENGTH(A.CUST_NAME) = 4 THEN
??????????????????? 'P'
?????????????? ELSE
??????????????????? B.ORG_FLAG
???????????? END ORG_FLAG
??????? FROM PC_CLEAN_CUST_NAME_3_TMP A,
???????????? PC_CLEAN_CUST_NAME_4_TMP B
?????? WHERE A.SYS_ID = B.SYS_ID(+);
??? commit;
??? -- 长度等于中文字符长度，中文字符长度小于等于4标识为"个人"，
??? -- 同时将长度为1的补上"*"，长度大于4标识为"团体"
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_NAME_5_TMP
?????????? (
??????????? SYS_ID,
??????????? CUST_NAME,
??????????? ORG_FLAG
?????????? )?????
????? SELECT /*+PARALLEL(A 4)*/
???????????? A.SYS_ID,
???????????? CASE
?????????????? WHEN A.ORG_FLAG IS NULL THEN
??????????????? CASE
?????????????? WHEN LENGTH(A.CUST_NAME) * 2 = LENGTHB(A.CUST_NAME) AND
??????????????????? LENGTH(A.CUST_NAME) = 1 THEN
??????????????? A.CUST_NAME || '*'
?????????????? ELSE
??????????????? A.CUST_NAME
???????????? END ELSE A.CUST_NAME END CUST_NAME,
???????????? CASE
?????????????? WHEN A.ORG_FLAG IS NULL THEN
???????????????? CASE
?????????????????? WHEN A.CUST_NAME IS NOT NULL AND LENGTH(A.CUST_NAME) * 2 = LENGTHB(A.CUST_NAME) THEN
????????????????????? CASE
???????????????????????? WHEN LENGTH(A.CUST_NAME) <= 4 THEN
????????????????????????????? 'P'
???????????????????????? ELSE
????????????????????????????? 'O'
???????????????????????? END
?????????????????? ELSE
???????????????????????? NULL
?????????????????? END
?????????????? ELSE
?????????????????? A.ORG_FLAG
?????????????? END ORG_FLAG
??????? FROM PC_CLEAN_CUST_NAME_7_TMP A;
??? COMMIT;
?
??? -- 将清洗完的数据插入的STG表，并标识清洗状态
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CUST_NAME_STG_TMP
?????????? (
????????????? SYS_ID,
????????????? CUST_NAME,
????????????? ORG_FLAG,
????????????? CLEAN_STATUS
?????????? )
????? SELECT /*+PARALLE(A 4)*/
???????????? SYS_ID,
???????????? CUST_NAME,
???????????? ORG_FLAG,
???????????? CASE
?????????????? WHEN ORG_FLAG IS NULL THEN
??????????????? '0'
?????????????? ELSE
??????????????? '1'
???????????? END CLEAN_STATUS
??????? FROM PC_CLEAN_CUST_NAME_5_TMP A;
??? COMMIT;
???
??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,27,NULL,'02',NULL,NULL,NULL,NULL);
? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,27,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_CUST_NAME_2;
?
? /***********************************************************
? --功能说明:?? 清洗完成后，更新批次表
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --注意事项：? 必须在 产险清洗 返回手工清洗 完毕 后 进行
? --*********************************************************/
? PROCEDURE SP_UPDATE_BATCH_INFO
? IS
?
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
???
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,108,NULL,'01',NULL,NULL,NULL,NULL);
??? --清空结果临时表
??? --NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CUST_NAME_STG_TMP');
??? -- 更新批次状态，及数据信息
??? UPDATE /*+PARALLE(A 4)*/
?????????? IDL_EX_BATCH A
?????? SET A.INVALID_CUST_COUNT = (SELECT count(*)
???????????????????????????????????? FROM IDL_SQL_SEP_CUST_RETURN B
??????????????????????????????????? WHERE B.TCIMS_BATCH_ID = A.TCIMS_BATCH_ID),
?????????? A.VALID_CUST_COUNT = (SELECT A.BATCH_TOTAL_NUM - count(*)
?????????????????????????????????? FROM IDL_SQL_SEP_CUST_RETURN B
????????????????????????????????? WHERE B.TCIMS_BATCH_ID = A.TCIMS_BATCH_ID),
?????????? A.BATCH_STATUS = '12',
?????????? A.UPDATED_DATE = SYSDATE,
?????????? A.UPDATED_BY = 'SYSTEM'???????????????????
???? WHERE A.BATCH_STATUS = '11'
?????? AND A.SERIES_TYPE = '01'
?????? AND A.PREPARE_FLAG = '1';
??? COMMIT;
???
??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,108,NULL,'02',NULL,NULL,NULL,NULL);
? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,108,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_UPDATE_BATCH_INFO;
?
END NETS_TCIMS_PC_TRANSFORM;
