数据清洗3

CREATE OR REPLACE PACKAGE BODY NETS_TCIMS_PC_CLEANOUT
IS

? /***********************************************************
? --功能说明:? 清洗城市
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --注意事项：? 这个是清洗规则中首当其冲的过程
? --*********************************************************/
? PROCEDURE SP_CLEAN_CITY
? IS
???? p_id???? NUMBER;???????? -- 日志记录id
???? p_errmsg varchar2(500);? -- 错误记录
? BEGIN

??? -- 操作日志记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,2,NULL,'01',NULL,NULL,NULL,NULL);

??? -- truncate 清空?? 表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CITY_STG_TMP');
???
??? --标识无效数据? 无城市编码即为无效
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CITY_STG_TMP
?????????? (SYS_ID)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID
?????? FROM IDL_SEP_SRC_DATA A,
??????????? IDL_EX_BATCH???? B
?????? WHERE (A.CITY IS NULL OR A.SECONDARY_ORG IS NULL OR THIRD_ORG IS NULL)
???????? AND A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
???????? AND B.SERIES_TYPE = '01'
???????? AND B.BATCH_STATUS = '11'
???????? AND B.PREPARE_FLAG = '1';
??? COMMIT;
???
?? -- 更新本次操作日志
?? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,2,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,2,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_CITY;

? /***********************************************************
? --功能说明:? 清洗车牌号码
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_VEHICLE_NO
? IS
???? p_id???? NUMBER;???????? -- 日志记录id
???? p_errmsg varchar2(500);? -- 错误记录
? BEGIN

??? -- 操作日志记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,22,NULL,'01',NULL,NULL,NULL,NULL);

??? -- truncate 清空? PC_CLEAN_VEHICLE_NO_STG_TMP 表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_VEHICLE_NO_STG_TMP');

??? --去掉特殊符号。回车，头尾的全半角空格、Tab、全角横杠转换为半角横杠，去空格 ，车牌后面的‘.’
??? --全角转换为半角
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_VEHICLE_NO_1_TMP
?????????? (SYS_ID,VEHICLE_NO,CITY)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? REPLACE(TRANSLATE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_NO),
??????????????????????????????????????????? CHR(13),''),
?????????????????????????????????????? CHR(10),''),
???????????? '－.*'||NETS_TCIMS_COM_CLEANOUT.V_SPECIAL_CHR_EN||NETS_TCIMS_COM_CLEANOUT.V_SPECIAL_CHR_ZN,
???????????? '-????????????????????????????????????? '),
???????????????????? ' ',
???????????????????? '') VEHICLE_NO,
???????????? A.CITY
?????? 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_VEHICLE_NO_2_TMP
?????????? (SYS_ID,VEHICLE_NO,CITY)
????? SELECT /*+PARALLEL(A 4)*/
???????????? SYS_ID,
???????????? CASE
?????????????? WHEN SUBSTR(A.VEHICLE_NO, 1, 2) <>
??????????????????? (SELECT MAX(B.SHORTEN)
?????????????????????? FROM BDL_RULE_PROVINCE_CITY B
????????????????????? WHERE B.CITY = A.CITY) THEN
??????????????? (SELECT MAX(B.SHORTEN)
?????????????????? FROM BDL_RULE_PROVINCE_CITY B
????????????????? WHERE B.CITY = A.CITY) || SUBSTR(A.VEHICLE_NO, 3)
?????????????? ELSE
??????????????? VEHICLE_NO
???????????? END VEHICLE_NO,
???????????? A.CITY
??????? FROM PC_CLEAN_VEHICLE_NO_1_TMP A;
??? COMMIT;

??? -- 补充车牌号码中缺少的 '-' 分隔符
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_VEHICLE_NO_3_TMP
???????? (SYS_ID,VEHICLE_NO,CITY)
????? SELECT /*+PARALLEL(A 4)*/
???????????? SYS_ID,
???????????? CASE
?????????????? WHEN INSTR(VEHICLE_NO, '-') = 0 THEN
??????????????? SUBSTR(VEHICLE_NO, 1, 2) || '-' || SUBSTR(VEHICLE_NO, 3)
?????????????? ELSE
??????????????? VEHICLE_NO
???????????? END VEHICLE_NO,
???????????? CITY
?????? FROM PC_CLEAN_VEHICLE_NO_2_TMP A;
??? COMMIT;

??? -- 将车牌不完整或车牌无内容数据补充“*”，
??? -- 注：车牌不完整定义：长度小于8（补充上“-”时）
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_VEHICLE_NO_4_TMP
?????????? (SYS_ID,VEHICLE_NO,CITY)
????? SELECT /*+PARALLEL(A)*/
???????????? SYS_ID,
???????????? CASE
?????????????? WHEN LENGTH(VEHICLE_NO) < 8 OR VEHICLE_NO IS NULL THEN
??????????????? VEHICLE_NO || '*'
?????????????? ELSE
??????????????? VEHICLE_NO
???????????? END VEHICLE_NO,
???????????? CITY
??????? FROM PC_CLEAN_VEHICLE_NO_3_TMP A;
??? COMMIT;

??? -- 6.车牌以[京,津,冀,晋,蒙,辽,吉,黑,沪,苏,浙,皖,闽,赣,
??? --????????? 云,鲁,豫,鄂,湘,粤,桂,琼,渝,川,黔,滇,藏,陕,甘,青,宁,新][A-Z]-开头,
??? --????????? 长度大于等于8,或含有"*"号车牌为正确车牌,其他需要手工清洗
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_VEHICLE_NO_5_TMP
?????????? (SYS_ID,VEHICLE_NO,CITY,CLEAN_STATUS)
??????? SELECT /*+PARALLEL(A 4)*/
?????????????? SYS_ID,
?????????????? VEHICLE_NO,
?????????????? CITY,
?????????????? CASE
???????????????? WHEN (SUBSTR(A.VEHICLE_NO, 1, 1) IN
????????????????????? ('京', '津', '冀', '晋', '蒙', '辽', '吉', '黑', '沪', '苏', '浙', '皖',
?????????????????????? '闽', '赣', '云', '鲁', '豫', '鄂', '湘', '粤', '桂', '琼', '渝', '川',
?????????????????????? '黔', '滇', '藏', '陕', '甘', '青', '宁', '新')
????????????????????? AND UPPER(SUBSTR(A.VEHICLE_NO, 2, 1)) IN
????????????????????? ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
????????????????????? 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')
????????????????????? AND SUBSTR(A.VEHICLE_NO, 3, 1) = '-' AND LENGTH(A.VEHICLE_NO) >= 8) OR
????????????????????? INSTR(VEHICLE_NO, '*') > 0 THEN
????????????????????? '1'
???????????????? ELSE
????????????????????? '0'
?????????????? END CLEAN_STATUS
????????? FROM PC_CLEAN_VEHICLE_NO_4_TMP A;
??? COMMIT;

??? --标示车牌号码完整? （为后续比对做准备）
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_VEHICLE_NO_STG_TMP
?????????? (SYS_ID,VEHICLE_NO,CITY,CLEAN_STATUS,VEHICLE_NO_INTEGRITY)
????? SELECT /*+PARALLEL(A 4)*/
???????????? SYS_ID,
???????????? UPPER(VEHICLE_NO),
???????????? CITY,
???????????? CLEAN_STATUS,
???????????? CASE
?????????????? WHEN LENGTH(A.VEHICLE_NO) >= 8 AND SUBSTR(A.VEHICLE_NO, -1) <> '*' THEN
??????????????? '1'
?????????????? ELSE
??????????????? '0'
???????????? END VEHICLE_NO_INTEGRITY
??????? FROM PC_CLEAN_VEHICLE_NO_5_TMP A;
??? COMMIT;

?? -- 更新本次操作日志
?? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,22,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,22,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_VEHICLE_NO;

? /***********************************************************
? --功能说明:? 清洗联系电话,
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --注意事项： 必须在车牌清洗之后运行
? --*********************************************************/
? PROCEDURE SP_CLEAN_TELEPHONE_NO
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
??? v_sql?????? VARCHAR2(4000);
??? v_sql_split VARCHAR2(4000);

??? CURSOR cur_TEL_SPLIT IS
????? SELECT SP.SPLITFIX FROM BDL_RULE_TEL_SPLIT SP;

? BEGIN
??? -- 操作日志记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,26,NULL,'01',NULL,NULL,NULL,NULL);
???
??? -- truncate 清空? PC_CLEAN_TEL_NO_STG_TMP 表???
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_TEL_NO_STG_TMP');
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_TELEPHONE_STG_TMP');
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_STG_TEL_NO_TMP');????

??? --回车，头尾的全半角空格、Tab、全角横杠转换为半角横杠，
??? --全角转换为半角
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_TEL_NO_1_TMP
???????? (
????????? SYS_ID,
????????? TEL_NO,
????????? TEL_NO_VALID,
????????? CITY,
????????? VEHICLE_NO,
????????? ADDRESS
???????? )
????? SELECT /*+PARALLEL(C 2) PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? CASE
?????????????? WHEN LENGTH(TRIM(A.TELEPHONE_NUMBER)) > 5 THEN
??????????????????? TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.TELEPHONE_NUMBER),
?????????????????????????????????? CHR(13), ''),CHR(10),''))
???????????? END TEL_NO,
???????????? CASE
?????????????? WHEN LENGTH(TRIM(A.TELEPHONE_NUMBER)) > 5 THEN
??????????????????? '1'
?????????????? ELSE
??????????????????? '0'
???????????? END TEL_NO_VALID,
???????????? --B.CITY,
???????????? (select city_name from BDL_COM_DISTRICT_ORG WHERE city_code = C.CITY) CITY,
???????????? A.VEHICLE_NO,
???????????? A.ADDRESS
??????? FROM PC_CLEAN_VEHICLE_NO_STG_TMP C,
???????????? IDL_SEP_SRC_DATA??????????? A,
???????????? IDL_EX_BATCH??????????????? B
???????????? /*(SELECT SYS_ID, VEHICLE_NO, CITY, CLEAN_STATUS, VEHICLE_NO_INTEGRITY
??????????????? FROM PC_CLEAN_VEHICLE_NO_STG_TMP
?????????????? WHERE CLEAN_STATUS = '1') B*/
??????? WHERE A.SYS_ID = C.SYS_ID(+)
????????? AND A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
????????? AND B.SERIES_TYPE = '01'
????????? AND B.BATCH_STATUS = '11'
????????? AND B.PREPARE_FLAG = '1';
??? COMMIT;

??? --取分割区号-座机-分机的符号串，'''－'',''x'',''*'',''＊'',''呼'',''转'''
??? FOR arow IN cur_TEL_SPLIT LOOP
????? v_sql?????? := v_sql || trim(arow.splitfix);
????? v_sql_split := v_sql_split || '-';
??? END LOOP;

??? --用户定义的区号和分机分隔符转换为"-"
??? IF v_sql IS NOT NULL THEN
????? INSERT /*+APPEND*/
??????? INTO PC_CLEAN_TEL_NO_2_TMP
???????????? (
????????????? SYS_ID,
????????????? TEL_NO,
????????????? TEL_NO_VALID,
????????????? CITY,
????????????? VEHICLE_NO,
????????????? ADDRESS
???????????? )???????
??????? SELECT /*+PARALLEL(A 4)*/
?????????????? SYS_ID,
?????????????? CASE
???????????????? WHEN TEL_NO_VALID = '1' THEN
????????????????? TRANSLATE(TEL_NO, v_sql, v_sql_split)
???????????????? ELSE
????????????????? TEL_NO
?????????????? END TEL_NO,
?????????????? TEL_NO_VALID,
?????????????? CITY,
?????????????? A.VEHICLE_NO,
?????????????? A.ADDRESS
????????? FROM PC_CLEAN_TEL_NO_1_TMP A;
????? COMMIT;
??? ELSE
????? INSERT /*+APPEND*/
??????? INTO PC_CLEAN_TEL_NO_2_TMP
???????????? (
????????????? SYS_ID,
????????????? TEL_NO,
????????????? TEL_NO_VALID,
????????????? CITY,
????????????? VEHICLE_NO,
????????????? ADDRESS
???????????? )
??????? SELECT /*+PARALLEL(A 4)*/
?????????????? A.SYS_ID, A.TEL_NO, A.TEL_NO_VALID, A.CITY,A.VEHICLE_NO,A.ADDRESS
????????? FROM PC_CLEAN_TEL_NO_1_TMP A;
????? COMMIT;
??? END IF;

??? --将空格,非数字替换为斜杠,
??? --4.只保留半角数字和“-”，“/”，其他字符全部删除
??? --将空格,非数字替换为斜杠,同时对电话分列,分隔符为'/' NDC_TMP_TEL_NO_4
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_TEL_NO_NO_USE_TMP
?????????? (clean_tel_result)
????? SELECT /*+PARALLEL(A 4)*/
???????????? NETS_TCIMS_COM_CLEANOUT.CLEAN_TEL(A.SYS_ID, A.TEL_NO, A.CITY,A.VEHICLE_NO,A.ADDRESS)
??????? FROM PC_CLEAN_TEL_NO_2_TMP A
?????? WHERE A.TEL_NO_VALID = '1';
??? COMMIT;

??? -- 识别手机号码
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_TEL_NO_5_TMP
?????????? (SPLIT_SYSID, SYS_ID, TEL_NO)
????? SELECT /*+PARALLEL(A 4)*/
???????????? SPLIT_SYSID,
???????????? SYS_ID,
???????????? LTRIM(REPLACE(TEL_NO, '-', ''),'0') TEL_NO
??????? FROM PC_CLEAN_TEL_NO_4_TMP A
?????? WHERE SUBSTR(A.TEL_NO, 1, 3) IN
???????????? (SELECT PREFIX FROM BDL_RULE_MOBILE_PREFIX);
??? COMMIT;

??? -- 识别座机号码并取区号
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_TEL_NO_6_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? CITY,
??????????? VEHICLE_NO,
??????????? ADDRESS
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SPLIT_SYSID,
???????????? A.SYS_ID,
???????????? CASE
?????????????? WHEN (INSTR(A.TEL_NO, '-') BETWEEN 3 AND 4) THEN
??????????????? '0'||SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1)
?????????????? ELSE
??????????????? B.CODE
???????????? END CODE,
???????????? CASE
?????????????? WHEN (INSTR(A.TEL_NO, '-') BETWEEN 3 AND 4) THEN
??????????????? SUBSTR(A.TEL_NO, INSTR(A.TEL_NO, '-') + 1)
?????????????? ELSE
??????????????? A.TEL_NO
???????????? END TEL_NO,
???????????? A.CITY,
???????????? A.VEHICLE_NO,
???????????? A.ADDRESS
??????? FROM PC_CLEAN_TEL_NO_4_TMP A,
???????????? BDL_RULE_TEL_CITY_CODE B
?????? WHERE NOT EXISTS (SELECT 1
??????????????? FROM PC_CLEAN_TEL_NO_5_TMP TT
?????????????? WHERE A.SPLIT_SYSID = TT.SPLIT_SYSID)
???????? AND A.CITY = B.CITY_NAME;
??? COMMIT;

??? --转换特殊的 升位区域
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_TEL_NO_3_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? RISE_AREA
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SPLIT_SYSID,
???????????? A.SYS_ID,
???????????? A.CODE,
???????????? A.TEL_NO,
???????????? NVL(B.RISE_AREA,A.CITY) RISE_AREA
??????? FROM PC_CLEAN_TEL_NO_6_TMP? A,
???????????? (SELECT C.CODE, D.RISE_AREA
??????????????? FROM PC_CLEAN_TEL_NO_6_TMP C,
???????????????????? BDL_RULE_TEL_RISE_AREA D
?????????????? WHERE ((C.VEHICLE_NO LIKE D.VEHICLE_NO AND D.ADDRESS IS NULL)
????????????????? OR (C.ADDRESS LIKE D.ADDRESS AND D.VEHICLE_NO IS NULL)
????????????????? OR (C.ADDRESS LIKE D.ADDRESS AND C.VEHICLE_NO LIKE D.VEHICLE_NO))) B
??????? WHERE A.CODE = B.CODE(+);
??? COMMIT;????????

??? -- 座机号码升位
??? -- 请确保号码升位规则不重叠
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_TEL_NO_7_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? RISE_AREA
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SPLIT_SYSID,
???????????? A.SYS_ID,
???????????? A.CODE,
???????????? CASE
?????????????? WHEN B.action = 'PRE_INSERT' THEN
??????????????? B.to_pattern || A.TEL_NO
?????????????? WHEN B.action = 'REPLACE' THEN
??????????????? SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || B.to_pattern ||
??????????????? SUBSTR(A.TEL_NO, B.RIGHT_START)
???????????? END TEL_NO,
???????????? A.RISE_AREA
??????? FROM PC_CLEAN_TEL_NO_3_TMP A,
???????????? BDL_RULE_TEL_CITY_PATTERN B
?????? WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
???????????? SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1) LIKE
???????????? B.OLD_PATTERN) OR
???????????? (INSTR(A.TEL_NO, '-') < 1 AND A.TEL_NO LIKE B.OLD_PATTERN))
???????? AND B.LIKE_MODEL = '1'
???????? AND B.DATA_TYPE = 'PC'
???????? AND A.RISE_AREA = B.CITY_NAME;
??? COMMIT;

??? /*
??? 8******->81******
??? *******->8*******
??? 除去LIKE_MODEL='1'的升位
??? */
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_TEL_NO_7_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? RISE_AREA
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SPLIT_SYSID,
???????????? A.SYS_ID,
???????????? A.CODE,
???????????? CASE
?????????????? WHEN B.action = 'PRE_INSERT' THEN
??????????????? B.to_pattern || A.TEL_NO
?????????????? WHEN B.action = 'REPLACE' THEN
??????????????? SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || B.to_pattern ||
??????????????? SUBSTR(A.TEL_NO, B.RIGHT_START)
???????????? END TEL_NO,
???????????? A.RISE_AREA
??????? FROM PC_CLEAN_TEL_NO_3_TMP???????? A,
???????????? BDL_RULE_TEL_CITY_PATTERN???? B
?????? WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
???????????? SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1) LIKE
???????????? B.OLD_PATTERN) OR
???????????? (INSTR(A.TEL_NO, '-') < 1 AND A.TEL_NO LIKE B.OLD_PATTERN))
???????? AND NOT EXISTS(SELECT 1 FROM LA_CLEAN_TEL_NO_7_TMP C
???????????????????????? WHERE C.SPLIT_SYSID = A.SPLIT_SYSID)
???????? AND B.LIKE_MODEL = '2'
???????? AND B.DATA_TYPE = 'PC'
???????? AND A.RISE_AREA = B.CITY_NAME;
??? COMMIT;

??? /*******************************************
??? 处理 第三种模式 升位：
????? 例如： 广东省 “湛江 ”
??? 前提： 除去 LIKE_MODEL = '1' 和 '2' 的升位
??? ********************************************/
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_TEL_NO_7_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? RISE_AREA
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SPLIT_SYSID,
???????????? A.SYS_ID,
???????????? A.CODE,
???????????? CASE
?????????????? WHEN B.action = 'PRE_INSERT' THEN
????????????????? SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || A.TEL_NO
?????????????? WHEN B.action = 'REPLACE' THEN
??????????????? B.to_pattern || SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || A.TEL_NO
?????????????? END TEL_NO,
???????????? A.RISE_AREA
??????? FROM PC_CLEAN_TEL_NO_3_TMP???????? A,
???????????? BDL_RULE_TEL_CITY_PATTERN???? B
?????? WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
???????????? SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1) LIKE
???????????? B.OLD_PATTERN) OR
???????????? (INSTR(A.TEL_NO, '-') < 1 AND A.TEL_NO LIKE B.OLD_PATTERN))
???????? AND NOT EXISTS(SELECT 1 FROM LA_CLEAN_TEL_NO_7_TMP C
???????????????????????? WHERE C.SPLIT_SYSID = A.SPLIT_SYSID)
???????? AND B.LIKE_MODEL = '3'
???????? AND B.DATA_TYPE = 'PC'
???????? AND A.RISE_AREA = B.CITY_NAME;
??? COMMIT;

??? /*******************************************
??? 处理 第四种模式 升位：
????? 例如： 特例模式 “泉州 ”
??? 前提： 除去 LIKE_MODEL = '1' 、 '2' 和 ‘3’的升位
??? ********************************************/
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_TEL_NO_7_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? RISE_AREA
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SPLIT_SYSID,
???????????? A.SYS_ID,
???????????? A.CODE,
???????????? CASE
?????????????? WHEN B.action = 'PRE_INSERT' THEN
??????????????? B.to_pattern || A.TEL_NO
?????????????? WHEN B.action = 'REPLACE' THEN
??????????????? B.to_pattern || SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || B.MIDDLE_INSERT || SUBSTR(A.TEL_NO, B.RIGHT_START)
???????????? END TEL_NO,
???????????? A.RISE_AREA
??????? FROM PC_CLEAN_TEL_NO_3_TMP???????? A,
???????????? BDL_RULE_TEL_CITY_PATTERN???? B
?????? WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
???????????? SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1) LIKE
???????????? B.OLD_PATTERN) OR
???????????? (INSTR(A.TEL_NO, '-') < 1 AND A.TEL_NO LIKE B.OLD_PATTERN))
???????? AND NOT EXISTS(SELECT 1 FROM LA_CLEAN_TEL_NO_7_TMP C
???????????????????????? WHERE C.SPLIT_SYSID = A.SPLIT_SYSID)
???????? AND B.LIKE_MODEL = '4'
???????? AND B.DATA_TYPE = 'PC'
???????? AND A.RISE_AREA = B.CITY_NAME;
??? COMMIT;

??? --同时把正确的号码放入
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_TEL_NO_7_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? RISE_AREA
?????????? )
????? SELECT /*+PARALLEL(A 2) PARALLEL(C 2)*/
???????????? A.SPLIT_SYSID, A.SYS_ID, A.CODE, A.TEL_NO, A.RISE_AREA
??????? FROM PC_CLEAN_TEL_NO_3_TMP A,
???????????? BDL_RULE_TEL_CITY_CODE C
?????? WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
???????????? LENGTH(SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1)) =
???????????? C.VALID_LEGTH) OR (INSTR(A.TEL_NO, '-') < 1 AND
???????????? LENGTH(A.TEL_NO) = C.VALID_LEGTH))
???????? AND NOT EXISTS(SELECT 1 FROM LA_CLEAN_TEL_NO_7_TMP B
???????????????????????? WHERE B.SPLIT_SYSID = A.SPLIT_SYSID)
???????? AND A.RISE_AREA = C.CITY_NAME
???????? AND C.DATA_TYPE = 'PC';
??? COMMIT;

??? -- 电话号码清洗结果??? 将清洗完的数据插入的STG表，并标识清洗状态
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_TEL_NO_8_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? CITY,
??????????? CLEAN_STATUS
?????????? )
???? SELECT /*+PARALLEL(t1 2) PARALLEL(t5 2) PARALLEL(tt 2)*/
??????????? NVL2(tt.CODE,TT.SPLIT_SYSID,T5.SPLIT_SYSID) SPLIT_SYSID,
??????????? t1.SYS_ID SYS_ID,
??????????? tt.CODE?? CODE,
??????????? nvl(nvl(tt.TEL_NO,t5.TEL_NO),t1.TEL_NO) TEL_NO,
??????????? t1.CITY?? CITY,
??????????? nvl(nvl2(tt.code,tt.CLEAN_STATUS,t5.CLEAN_STATUS),0) CLEAN_STATUS
?????? FROM PC_CLEAN_TEL_NO_1_TMP t1,
??????????? (SELECT SPLIT_SYSID,
??????????????????? SYS_ID,
??????????????????? TEL_NO,
??????????????????? DECODE(LENGTH(TEL_NO), 11, '1', '0') CLEAN_STATUS
?????????????? FROM PC_CLEAN_TEL_NO_5_TMP) t5,
??????????? (SELECT TL.SPLIT_SYSID,
??????????????????? TL.SYS_ID,
??????????????????? DECODE(NVL(T7.SPLIT_SYSID, 0),
??????????????????????????????????? 0,
??????????????????????????????????? TL.CODE,
??????????????????????????????????? T7.CODE) CODE,
??????????????????? DECODE(NVL(T7.SPLIT_SYSID, 0),
??????????????????????????????????? 0,
??????????????????????????????????? TL.TEL_NO,
??????????????????????????????????? T7.TEL_NO) TEL_NO,
?????????????????? DECODE(NVL(T7.SPLIT_SYSID, 0), 0, '0', '1') CLEAN_STATUS
?????????????? FROM PC_CLEAN_TEL_NO_3_TMP TL, PC_CLEAN_TEL_NO_7_TMP T7
????????????? WHERE TL.SPLIT_SYSID = T7.SPLIT_SYSID(+)) tt
?????? WHERE t1.sys_id = t5.sys_id(+)
???????? AND t1.sys_id = tt.sys_id(+);
??? COMMIT;

??? -- 电话号码清洗结果??? 将清洗完的数据插入的STG表，并标识清洗状态
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_TEL_NO_STG_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? CITY,
??????????? CLEAN_STATUS
?????????? )
????? SELECT SPLIT_SYSID,
???????????? SYS_ID,
???????????? CODE,
???????????? TEL_NO,
???????????? CITY,
???????????? CLEAN_STATUS
??????? FROM (SELECT /*+PARALLEL(A 4)*/
???????????????????? A.SPLIT_SYSID,
???????????????????? A.SYS_ID,
???????????????????? A.CODE,
???????????????????? A.TEL_NO,
???????????????????? A.CITY,
???????????????????? A.CLEAN_STATUS,
???????????????????? ROW_NUMBER() OVER(PARTITION BY A.SYS_ID,A.CODE||A.TEL_NO ORDER BY A.SYS_ID) RN_NO
??????????????? FROM PC_CLEAN_TEL_NO_8_TMP A)
??????? WHERE RN_NO = 1;
??? COMMIT;
???
??? -- 合并电话号码
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_TELEPHONE_STG_TMP
?????????? (
??????????? SYS_ID,
??????????? TEL_NO,
??????????? CITY,
??????????? CLEAN_STATUS
?????????? )
??? SELECT SYS_ID,
?????????? substr(LTRIM(MAX(SYS_CONNECT_BY_PATH(TEL_NO, ',')), ','),1,1000) TEL_NO,
?????????? CITY,
?????????? '1' CLEAN_STATUS
???? FROM (SELECT /*+PARALLEL(A 4)*/
????????????????? A.SYS_ID,
????????????????? NVL2(A.CODE,A.CODE||'-','') || A.TEL_NO TEL_NO,
????????????????? A.CITY,
????????????????? A.CLEAN_STATUS,
????????????????? ROW_NUMBER() OVER(PARTITION BY A.SYS_ID ORDER BY NVL2(A.CODE,A.CODE||'-','') || A.TEL_NO) RN_BY_ID,
????????????????? ROW_NUMBER() OVER(ORDER BY A.SYS_ID, NVL2(A.CODE,A.CODE||'-','') || A.TEL_NO) + TO_NUMBER(A.SYS_ID) RN
???????????? FROM PC_CLEAN_TEL_NO_STG_TMP A
??????????? WHERE A.CLEAN_STATUS = '1')
???? START WITH RN_BY_ID = 1
??? CONNECT BY RN - 1 = PRIOR RN
???? GROUP BY SYS_ID,CITY ORDER BY SYS_ID;??????
??? COMMIT;
???
??? -- 增加一个 代理电话标识? STG 表
??? INSERT /*+APPEND*/
????? INTO PC_STG_TEL_NO_TMP
?????????? (
??????????? SPLIT_SYSID,
??????????? SYS_ID,
??????????? CODE,
??????????? TEL_NO,
??????????? CITY,
??????????? IS_PROXY_PHONE_FLAG,
??????????? TELEPHONE_TYPE
?????????? )
??? SELECT A.SPLIT_SYSID,
?????????? A.SYS_ID,
?????????? A.CODE,
?????????? A.TEL_NO,
?????????? A.CITY,
?????????? CASE
???????????? WHEN RN >= 4 THEN
????????????????? 'Y'
???????????? ELSE
????????????????? 'N'
?????????? END IS_PROXY_PHONE_FLAG,
?????????? NVL2(A.CODE,'02','01') TELEPHONE_TYPE??????????
????? FROM (SELECT /*+PARALLEL(B 4)*/
?????????????????? B.SPLIT_SYSID,
?????????????????? B.SYS_ID,
?????????????????? B.CODE,
?????????????????? B.TEL_NO,
?????????????????? B.CITY,
?????????????????? ROW_NUMBER()OVER(PARTITION BY B.CODE,B.TEL_NO ORDER BY B.SYS_ID ASC) RN
???????? FROM PC_CLEAN_TEL_NO_STG_TMP B
??????? WHERE B.CLEAN_STATUS = '1') A;
??? COMMIT;
?
?/*??
??? -- 后续需要通过 判断 sys_id 下的所有电话号码重复， 记录标识为“代理电话重复数据”
?????? 需要和 更新比对确认
??? -- 2009年11月23日 确认? 这项功能 在入 BDL 层时更新?
?*/
?
??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,26,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,26,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_TELEPHONE_NO;

? /***********************************************************
? --功能说明:? 清洗号牌种类
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_BRAND_TYPE
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录

? BEGIN

??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,3,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_BRAND_TYPE_CD_STG_TMP');
???
??? -- 去回车，头尾的全半角空格、Tab
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_BRAND_TYPE_1_TMP
?????????? (SYS_ID,BRAND_TYPE,BRAND_TYPE_CODE)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? NVL(TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.BRAND_TYPE),CHR(13),''),
???????????????????????? CHR(10),'')),'小型汽车') BRAND_TYPE,
???????????? TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.BRAND_TYPE_CODE),CHR(13),''),
??????????????????? CHR(10),'')) BRAND_TYPE_CODE
?????? FROM IDL_SEP_SRC_DATA A,
??????????? IDL_EX_BATCH???? B
????? WHERE (BRAND_TYPE IS NOT NULL
???????? OR BRAND_TYPE_CODE IS NOT NULL)
???????? AND 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_BRAND_TYPE_2_TMP
?????????? (SYS_ID,BRAND_TYPE,BRAND_TYPE_CODE)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? A.BRAND_TYPE,
???????????? CASE
?????????????? WHEN A.BRAND_TYPE_CODE IN
??????????????????? (SELECT BRAND_TYPE_CODE FROM BDL_RULE_REF_BRAND_TYPE) THEN
??????????????????? A.BRAND_TYPE_CODE
?????????????? ELSE
??????????????????? B.BRAND_TYPE_CODE
???????????? END BRAND_TYPE_CODE
?????? FROM PC_CLEAN_BRAND_TYPE_1_TMP A,
??????????? (SELECT DISTINCT BRAND_TYPE, BRAND_TYPE_CODE
????????????? FROM BDL_RULE_REF_BRAND_TYPE)?? B
????? WHERE A.BRAND_TYPE = B.BRAND_TYPE(+);
??? COMMIT;

??? -- 辨别 是否需要手工清洗
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_BRAND_TYPE_CD_STG_TMP
?????????? (SYS_ID,BRAND_TYPE_CODE,CLEAN_STATUS)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? B.BRAND_TYPE_CODE,
???????????? CASE
???????????????? WHEN B.BRAND_TYPE_CODE IS NULL --AND B.BRAND_TYPE IS NULL
????????????????????? THEN
????????????????????? '0'
???????????????? ELSE
????????????????????? '1'
???????????? END CLEAN_STATUS
?????? FROM PC_CLEAN_BRAND_TYPE_2_TMP A,
??????????? BDL_RULE_REF_BRAND_TYPE?? B
????? WHERE A.BRAND_TYPE_CODE = B.BRAND_TYPE_CODE(+);
??? COMMIT;
???????
??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,3,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,3,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_BRAND_TYPE;

? /***********************************************************
? --功能说明:? 清洗使用性质及代码
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_USAGE_ATTRIBUTE
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN

??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,4,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_USAGE_CODE_STG_TMP');

??? --去回车，头尾的全半角空格、Tab、全角横杠、半角横杠
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_USAGE_ATTRIBUTE_1_TMP
???????? (SYS_ID,USAGE_ATTRIBUTE,USAGE_CODE)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? NVL(trim(REPLACE(REPLACE(TO_SINGLE_BYTE(A.USAGE_ATTRIBUTE), CHR(13), ''),
??????????????????? CHR(10),'')),'非营运') USAGE_ATTRIBUTE,
???????????? trim(REPLACE(REPLACE(TO_SINGLE_BYTE(A.USAGE_CODE), CHR(13), ''),
??????????????????? CHR(10),''))USAGE_CODE
??????? 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_USAGE_ATTRIBUTE_2_TMP
?????????? (SYS_ID,USAGE_ATTRIBUTE,USAGE_CODE)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? B.USAGE_ATTRIBUTE,
???????????? CASE
?????????????? WHEN A.USAGE_CODE IN
?????????????????? (SELECT USAGE_CODE FROM BDL_RULE_USAGE_ATTRIBUTE) THEN
??????????????????? A.USAGE_CODE
?????????????? ELSE
??????????????????? B.USAGE_CODE
????????????? END USAGE_CODE
??????? FROM PC_CLEAN_USAGE_ATTRIBUTE_1_TMP A,
???????????? BDL_RULE_USAGE_ATTRIBUTE?????? B
??????? WHERE A.USAGE_ATTRIBUTE = B.USAGE_ATTRIBUTE(+);
??? COMMIT;

??? -- 不在使用性质代码表中且该字段不为空的数据定义为需手工清洗数据
??? -- 将符合标准的记录标识为清洗完成
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_USAGE_CODE_STG_TMP
?????????? (SYS_ID,USAGE_CODE,USAGE_ATTRIBUTE,CLEAN_STATUS)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? B.USAGE_CODE,
???????????? B.USAGE_ATTRIBUTE,
???????????? CASE
?????????????? WHEN B.USAGE_CODE IS NULL THEN
??????????????????? '0'
?????????????? ELSE
??????????????????? '1'
???????????? END CLEAN_STATUS
??????? FROM PC_CLEAN_USAGE_ATTRIBUTE_2_TMP A,
???????????? BDL_RULE_USAGE_ATTRIBUTE?????? B
??????? WHERE A.USAGE_CODE = B.USAGE_CODE(+);
??? COMMIT;

??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,4,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,4,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_USAGE_ATTRIBUTE;

? /***********************************************************
? --功能说明:? 清洗发动机号
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_ENGINE_NO
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录

??? v_str?????? VARCHAR2(100) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
???
? BEGIN

??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,6,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_ENGINE_NO_STG_TMP');

??? --去掉特殊符号。回车，头尾的全半角空格、Tab、全角横杠转换为半角横杠，去空格
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_ENGINE_NO_STG_TMP
???????? (SYS_ID,ENGINE_NUMBER)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? TRANSLATE(
??????????????? REPLACE(UPPER(REPLACE(REPLACE(TO_SINGLE_BYTE(A.ENGINE_NUMBER), CHR(13), ''),
?????????????????????????????????? CHR(10), '')),'E+',''),
??????????????? v_str||REPLACE(UPPER(REPLACE(REPLACE(TO_SINGLE_BYTE(A.ENGINE_NUMBER), CHR(13), ''),CHR(10), '')),'E+',''),
??????????????? v_str
??????????????? ) ENGINE_NUMBER
??????? 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;

??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,6,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,6,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_ENGINE_NO;

? /***********************************************************
? --功能说明:? 清洗车架号
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_VEHICLE_FRAME_NO
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录

??? v_str?????? VARCHAR2(100) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
???
? BEGIN

??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,7,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_VEHICLE_FRAME_STG_TMP');

??? --去掉特殊符号。回车，头尾的全半角空格、Tab、全角横杠转换为半角横杠，去空格
??? --只包含数据和字母,非数字和字母字符全部删除
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_VEHICLE_FRAME_STG_TMP
?????????? (SYS_ID,VEHICLE_FRAME)
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID,
?????????? TRANSLATE(
???????????? REPLACE(UPPER(REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_FRAME), CHR(13), ''),CHR(10), '')), 'E+', ''),
???????????? v_str || REPLACE(UPPER(REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_FRAME), CHR(13), ''),CHR(10), '')), 'E+', ''),
???????????? v_str) VEHICLE_FRAME
???? 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;

??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,7,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,7,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_VEHICLE_FRAME_NO;

? /***********************************************************
? --功能说明:? 清洗车辆种类
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_VEHICLE_TYPE
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录

? BEGIN

??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,5,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_VEHIC_TYPE_CD_STG_TMP');

??? --去回车，头尾的全半角空格、Tab
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_VEHICLE_TYPE_1_TMP
?????????? (SYS_ID,VEHICLE_TYPE,VEHICLE_TYPE_CODE)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_TYPE), CHR(13), ''),
????????????????? CHR(10), '') VEHICLE_TYPE,
???????????? REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_TYPE_CODE), CHR(13), ''),
????????????????? CHR(10), '') VEHICLE_TYPE_CODE
?????? FROM IDL_SEP_SRC_DATA A,
??????????? IDL_EX_BATCH???? B
?????? WHERE (A.VEHICLE_TYPE IS NOT NULL
???????? OR A.VEHICLE_TYPE_CODE IS NOT NULL)
???????? AND 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_VEHICLE_TYPE_2_TMP
?????????? (SYS_ID,VEHICLE_TYPE,VEHICLE_TYPE_CODE)
??? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
?????????? A.SYS_ID,
?????????? B.VEHICLE_TYPE,
?????????? CASE
???????????? WHEN A.VEHICLE_TYPE_CODE IN
????????????????? (SELECT VEHICLE_TYPE_CODE FROM BDL_RULE_VEHICLE_TYPE) THEN
????????????????? A.VEHICLE_TYPE_CODE
???????????? ELSE
????????????????? B.VEHICLE_TYPE_CODE
???????????? END VEHICLE_TYPE_CODE
?????? FROM PC_CLEAN_VEHICLE_TYPE_1_TMP A,
??????????? BDL_RULE_VEHICLE_TYPE?????? B
?????? WHERE A.VEHICLE_TYPE = B.VEHICLE_TYPE(+) ;
???? COMMIT;

??? -- 不在使用性质代码表中且该字段不为空的数据定义为需手工清洗数据
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_VEHIC_TYPE_CD_STG_TMP
?????????? (SYS_ID,VEHICLE_TYPE_CODE,VEHICLE_TYPE,CLEAN_STATUS )
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? B.VEHICLE_TYPE_CODE,
???????????? B.VEHICLE_TYPE,
???????????? CASE
???????????????? WHEN B.VEHICLE_TYPE_CODE IS NULL AND B.VEHICLE_TYPE IS NULL THEN
????????????????????? '0'
???????????????? ELSE
????????????????????? '1'
???????????? END CLEAN_STATUS
??????? FROM PC_CLEAN_VEHICLE_TYPE_2_TMP A,
???????????? BDL_RULE_VEHICLE_TYPE?????? B
?????? WHERE A.VEHICLE_TYPE_CODE = B.VEHICLE_TYPE_CODE(+) ;
??? COMMIT;

??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,5,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,5,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_VEHICLE_TYPE;

? /***********************************************************
? --功能说明:? 清洗称谓
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_SALUTATION
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN

??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,8,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_SALUTATION_STG_TMP');

??? --去回车，头尾的全半角空格、Tab
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SALUTATION_1_TMP
?????????? (SYS_ID,SALUTATION)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.SALUTATION), CHR(13), '')
???????????????? , CHR(10), '')) SALUTATION
?????? 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_SALUTATION_STG_TMP
?????????? (SYS_ID,SALUTATION)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? NVL(B.NEW_SALUTATION,A.SALUTATION) SALUTATION
?????? FROM PC_CLEAN_SALUTATION_1_TMP A,
??????????? BDL_RULE_SALUTATION B
????? WHERE A.SALUTATION = B.OLD_SALUTATION(+);
??? COMMIT;

??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,8,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,8,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_SALUTATION;

? /***********************************************************
? --功能说明:? 清洗客户性别
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --注意事项：? 整合数据时，用 身份证 转换过来的数据 替换（空值除外）
? --*********************************************************/
? PROCEDURE SP_CLEAN_SEX
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN

??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,9,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_SEX_STG_TMP');

??? --去回车，头尾的全半角空格、Tab
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SEX_1_TMP
?????????? (SYS_ID,SEX)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.SEX), CHR(13), ''),chr(10),'')) SEX
?????? 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_SEX_STG_TMP
?????????? (SYS_ID,SEX)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? NVL(B.NEW_SEX,A.SEX) SEX
?????? FROM PC_CLEAN_SEX_1_TMP A,
??????????? BDL_RULE_SEX?????? B
?????? WHERE A.SEX = B.OLD_SEX(+);
??? COMMIT;

??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,9,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,9,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_SEX;

? /***********************************************************
? --功能说明:? 清洗主驾人性别
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_MAIN_DRIVER_SEX
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN

??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,10,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_DRIVER_SEX_STG_TMP');

??? --去回车，头尾的全半角空格、Tab
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_DRIVER_SEX_1_TMP
?????????? (SYS_ID,MAIN_DRIVER_SEX)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.MAIN_DRIVER_SEX), CHR(13), ''),chr(10),'')) MAIN_DRIVER_SEX
?????? 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_DRIVER_SEX_STG_TMP
?????????? (SYS_ID,MAIN_DRIVER_SEX)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? NVL(B.NEW_SEX,A.MAIN_DRIVER_SEX) MAIN_DRIVER_SEX
?????? FROM PC_CLEAN_DRIVER_SEX_1_TMP A,
??????????? BDL_RULE_SEX????????????? B
?????? WHERE A.MAIN_DRIVER_SEX = B.OLD_SEX(+);
??? COMMIT;

??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,10,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,10,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_MAIN_DRIVER_SEX;

? /***********************************************************
? --功能说明:? 清洗 吨位\核定座位数\车辆价值\排气量
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_TON_SEAT_VALU_EXHAUST
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN

??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,11,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_SOME_NUMBER_STG_TMP');

??? --去回车，头尾的全半角空格、Tab
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_SOME_NUMBER_1_TMP
?????????? (SYS_ID,TON_NUMBER,SEAT_NUMBER,VEHICLE_VALUE,EXHAUST)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? translate(TRIM(REPLACE(TO_SINGLE_BYTE(A.TON_NUMBER), CHR(13), '')),
??????????????? '0123456789.'||TRIM(REPLACE(TO_SINGLE_BYTE(A.TON_NUMBER), CHR(13), '')),
??????????????? '0123456789.'
???????????? ) TON_NUMBER,
???????????? translate(TRIM(REPLACE(TO_SINGLE_BYTE(A.SEAT_NUMBER), CHR(13), '')),
??????????????? '0123456789.'||TRIM(REPLACE(TO_SINGLE_BYTE(A.SEAT_NUMBER), CHR(13), '')),
??????????????? '0123456789.'
???????????? ) SEAT_NUMBER,
???????????? translate(TRIM(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_VALUE), CHR(13), '')),
??????????????? '0123456789.'||TRIM(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_VALUE), CHR(13), '')),
??????????????? '0123456789.'
???????????? ) VEHICLE_VALUE,
???????????? translate(TRIM(REPLACE(TO_SINGLE_BYTE(A.EXHAUST), CHR(13), '')),
??????????????? '0123456789.'||TRIM(REPLACE(TO_SINGLE_BYTE(A.EXHAUST), CHR(13), '')),
??????????????? '0123456789.'
???????????? ) EXHAUST
?????? 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_SOME_NUMBER_STG_TMP
?????????? (SYS_ID,TON_NUMBER,SEAT_NUMBER,VEHICLE_VALUE,EXHAUST)
????? SELECT /*+PARALLEL(A 4)*/
???????????? A.SYS_ID,
???????????? TO_NUMBER(A.TON_NUMBER) TON_NUMBER,
???????????? CASE
?????????????? WHEN TO_NUMBER(SEAT_NUMBER) < 999 THEN
??????????????????? TO_NUMBER(SEAT_NUMBER)
?????????????? ELSE
??????????????????? NULL
???????????? END SEAT_NUMBER,
???????????? TO_NUMBER(A.VEHICLE_VALUE) VEHICLE_VALUE,
???????????? TO_NUMBER(A.EXHAUST)?????? EXHAUST
?????? FROM PC_CLEAN_SOME_NUMBER_1_TMP A;
??? COMMIT;

??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,11,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,11,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_TON_SEAT_VALU_EXHAUST;

? /***********************************************************
? --功能说明:? 清洗 车身颜色
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_VEHICLE_BODY_COLOR
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,12,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CAR_COLOR_STG_TMP');

??? --去回车，头尾的全半角空格、Tab
??? INSERT /*+APPEND*/
????? INTO PC_CLEAN_CAR_COLOR_STG_TMP
?????????? (SYS_ID,VEHICLE_BODY_COLOR)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_BODY_COLOR), CHR(13), '')
????????????????? , CHR(10), '')) VEHICLE_BODY_COLOR
??????? 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;

??? -- 更新本次操作日志
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,12,NULL,'02',NULL,NULL,NULL,NULL);

? EXCEPTION
???? WHEN OTHERS THEN
??????? p_errmsg := substr(sqlerrm,1,500);
??????? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,12,NULL,'03',p_errmsg,NULL,NULL,NULL);
??????? RAISE;
? END SP_CLEAN_VEHICLE_BODY_COLOR;

? /***********************************************************
? --功能说明:? 清洗 身份证件号码
? --参数说明:
? --调用函数:
? --修改记录：? create by ex-qiuweisheng001/ex-liujiali001
? --*********************************************************/
? PROCEDURE SP_CLEAN_ID_NUMBER
? IS
??? p_id??????? NUMBER;???????? -- 日志记录id
??? p_errmsg??? VARCHAR2(500);? -- 错误记录
? BEGIN
??? -- 操作记录
??? NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,21,NULL,'01',NULL,NULL,NULL,NULL);

??? --清空结果临时表
??? NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_ID_NUMBER_STG_TMP');

??? --去掉特殊符号。? 全角转换为半角
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_ID_NUMBER_1_TMP
???????? (SYS_ID,ID_NUMBER)
????? SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
???????????? A.SYS_ID,
???????????? CASE
?????????????? WHEN SUBSTR(TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)), -1) IN ('X', 'x') THEN
??????????????? TRANSLATE(TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)),
????????????????????????? '0123456789' || TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)), '0123456789') || 'X'
?????????????? ELSE
??????????????? TRANSLATE(TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)),
????????????????????????? '0123456789' || TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)), '0123456789')
???????????? END ID_NUMBER
??????? FROM IDL_SEP_SRC_DATA A,
??????????? IDL_EX_BATCH???? B
?????? WHERE A.ID_TYPE = '01'
???????? AND A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
???????? AND B.SERIES_TYPE = '01'
???????? AND B.BATCH_STATUS = '11'
???????? AND B.PREPARE_FLAG = '1';
??? COMMIT;

??? --15位升18
??? INSERT /*+APPEND*/
??? INTO PC_CLEAN_ID_NUMBER_2_TMP
???????? (SYS_ID,ID_NUMBER)
????? SELECT /*+PARALLEL(A 4)*/
?????? A.SYS_ID,
?????? CASE
??<