数据清洗二
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;