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
2788
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
