CREATE OR REPLACE PACKAGE BODY NETS_TCIMS_LA_SHIELD
IS
v_split_chr varchar2(1) := ',';
/*******************************************************************************
需要在寿险准备使用批次表中增加业务系列“01”寿险“02”交叉销售,用于区分寿险出库屏蔽
处理时不分,完全处理,特殊的单独处理
********************************************************************************/
/***********************************************************
--功能说明: 寿险屏蔽数据准备(入库屏蔽准备)
--参数说明:
--调用函数:
--修改记录: create by ex-qiuweisheng001/ex-liujiali001
--注意事项: 清洗后
--*********************************************************/
PROCEDURE SP_SHIELD_ENTER_PREPARE_DATA
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,60,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_SHIELD_PRE_DATA_STG_TMP');
--准备寿险屏蔽数据
INSERT /*+APPEND*/
INTO LA_SHIELD_PRE_DATA_STG_TMP
(
TCIMS_CUST_ID,
ORG,
ADDRESS,
TELEPHONE_NUMBER,
SERIES_TYPE
)
SELECT /*+PARALLEL(A 2) PARALLEL(C 2)*/
A.SYS_ID ,
A.DEPARTMENT_CHINESE_NAME ORG,
--A.ADDRESS,
A.CONTACT_ADDRESS, --使用邮寄地址
C.TEL_NO TELEPHONE_NUMBER,
'01' SERIES_TYPE
FROM LA_ENTER_RESULT_STG_0_TMP A, --去重复 ,非法 机构
--LA_CLEAN_ADDRESS_STG_TMP B, --非清洗后的地址
(SELECT SYS_ID, TEL_NO, CITY
FROM LA_CLEAN_TELEPHONE_STG_TMP
WHERE CLEAN_STATUS = '1')C
--LA_COMPARE_REPEAT_STG_TMP D -- 重复记录
WHERE A.SYS_ID = C.SYS_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,60,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := SUBSTR(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,60,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_SHIELD_ENTER_PREPARE_DATA;
/***********************************************************
--功能说明: 寿险屏蔽数据准备(出库屏蔽准备)
--参数说明:
--调用函数:
--修改记录: create by ex-qiuweisheng001/ex-liujiali001
--注意事项: 名单准备后
--*********************************************************/
PROCEDURE SP_SHIELD_OUT_PREPARE_DATA
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,89,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_SHIELD_PRE_DATA_STG_TMP');
--准备寿险屏蔽数据
INSERT /*+APPEND*/
INTO LA_SHIELD_PRE_DATA_STG_TMP
(
TCIMS_CUST_ID,
ORG,
ADDRESS,
TELEPHONE_NUMBER,
SERIES_TYPE
)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
A.USE_SYS_ID TCIMS_CUST_ID,
A.DEPARTMENT_CHINESE_NAME ORG,
A.CONTACT_ADDRESS , --A.ADDRESS, 使用 CONTACT_ADDRESS 客户邮寄地址
A.OFF_TEL||V_SPLIT_CHR||A.CONTACT_TEL||V_SPLIT_CHR||
A.HOME_TEL||V_SPLIT_CHR||A.MOBILE_TEL TELEPHONE_NUMBER,
B.SERIES_TYPE
FROM DML_SEL_USE_CUST_INFO A,
DML_SEL_USE_CUST_BATCH B
WHERE B.BATCH_STATUS = '26'
AND A.BATCH_ID = B.BATCH_ID ;
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,89,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := SUBSTR(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,89,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_SHIELD_OUT_PREPARE_DATA;
/***********************************************************
--功能说明: 屏蔽无法送单地区(出入屏蔽\交叉销售可重用)
--参数说明:
--调用函数:
--修改记录: create by ex-qiuweisheng001/ex-liujiali001
--注意事项: 入屏蔽 SP_SHIELD_ENTER_PREPARE_DATA 完成后
出屏蔽 SP_SHIELD_PREPARE_DATA 完成后
--*********************************************************/
PROCEDURE SP_SHIELD_UNABLE_AREA
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,65,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_SHIELD_UNABLE_AREA_STG_TMP');
--标识屏蔽结果
INSERT /*+APPEND*/
INTO LA_SHIELD_UNABLE_AREA_STG_TMP
(TCIMS_CUST_ID,SHIELD_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
A.TCIMS_CUST_ID,
'D' SHIELD_FLAG
FROM LA_SHIELD_PRE_DATA_STG_TMP A,
BDL_RULE_REF_SHIELD_LA_UN_AREA B
WHERE ((A.ADDRESS LIKE B.ADDRESS) OR B.ADDRESS IS NULL)
AND ((A.ORG LIKE B.ORG) OR B.ORG IS NULL);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,65,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := SUBSTR(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,65,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_SHIELD_UNABLE_AREA;
/***********************************************************
--功能说明: 屏蔽投诉领导人电话(出入屏蔽\交叉销售可重用)
--参数说明:
--调用函数:
--修改记录: create by ex-qiuweisheng001/ex-liujiali001
--注意事项: 入屏蔽 SP_SHIELD_ENTER_PREPARE_DATA 完成后
出屏蔽 SP_SHIELD_PREPARE_DATA 完成后
--*********************************************************/
PROCEDURE SP_SHIELD_CHARGE_PHONE
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,66,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_SHIELD_CHARGE_TEL_STG_TMP');
--标识屏蔽结果
INSERT /*+APPEND*/
INTO LA_SHIELD_CHARGE_TEL_STG_TMP
(TCIMS_CUST_ID,SHIELD_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
A.TCIMS_CUST_ID,
'G' SHIELD_FLAG
FROM LA_SHIELD_PRE_DATA_STG_TMP A,
BDL_RULE_SHIELD_CHARGE_TELE B
WHERE INSTR(A.TELEPHONE_NUMBER,B.TEL)>0;
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,66,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := SUBSTR(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,66,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_SHIELD_CHARGE_PHONE;
/***********************************************************
--功能说明: 屏蔽业务员电话(出入屏蔽\交叉销售可重用)
--参数说明:
--调用函数:
--修改记录: create by ex-qiuweisheng001/ex-liujiali001
--注意事项: 入屏蔽 SP_SHIELD_ENTER_PREPARE_DATA 完成后
出屏蔽 SP_SHIELD_PREPARE_DATA 完成后
--*********************************************************/
PROCEDURE SP_SHIELD_OPERATOR_PHONE
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,67,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_SHIELD_OPERAT_TELE_STG_TMP');
--标识屏蔽结果
INSERT /*+APPEND*/
INTO LA_SHIELD_OPERAT_TELE_STG_TMP
(TCIMS_CUST_ID,SHIELD_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
A.TCIMS_CUST_ID,
'K' SHIELD_FLAG
FROM LA_SHIELD_PRE_DATA_STG_TMP A,
BDL_RULE_SHIELD_OPERATOR_TEL B
WHERE INSTR(A.TELEPHONE_NUMBER,B.TEL)>0;
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,67,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := SUBSTR(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,67,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_SHIELD_OPERATOR_PHONE;
/***********************************************************
--功能说明: 屏蔽 产险_投诉电话 (出屏蔽\交叉销售可重用)
--参数说明:
--调用函数:
--修改记录: create by ex-qiuweisheng001/ex-liujiali001
--注意事项: 出屏蔽 SP_SHIELD_PREPARE_DATA 完成后
--*********************************************************/
PROCEDURE SP_SHIELD_ACROSS_PHONE
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,99,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_SHIELD_ACROSS_TELE_STG_TMP');
--标识屏蔽结果
INSERT /*+APPEND*/
INTO LA_SHIELD_ACROSS_TELE_STG_TMP
(TCIMS_CUST_ID,SHIELD_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
A.TCIMS_CUST_ID,
'G' SHIELD_FLAG
FROM LA_SHIELD_PRE_DATA_STG_TMP A,
BDL_RULE_REF_SHIELD_PHONE B
WHERE INSTR(A.TELEPHONE_NUMBER,B.PHONE)>0
AND A.SERIES_TYPE = '02'
AND B.PHONE_TYPE = '3';
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,99,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := SUBSTR(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,99,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_SHIELD_ACROSS_PHONE;
/***********************************************************
--功能说明: 组合屏蔽结果(入库屏蔽) 更新准备入库名单表
--参数说明:
--调用函数:
--修改记录: create by ex-qiuweisheng001/ex-liujiali001
--注意事项: 须在<SP_SHIELD_UNABLE_AREA, SP_SHIELD_CHARGE_PHONE, SP_SHIELD_OPERATOR_PHONE >
入库屏蔽完成后进行
--*********************************************************/
PROCEDURE SP_UNITE_ALL_IN_SHIELD_RESULT
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,69,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_IN_SHIELD_UNITE_STG_TMP');
-- 整合所有屏蔽标识
INSERT /*+APPEND*/
INTO LA_IN_SHIELD_UNITE_STG_TMP
(SYS_ID,SHIELD_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
DISTINCT
A.SYS_ID,
NVL2(B.SHIELD_FLAG,B.SHIELD_FLAG||v_split_chr,'')||
NVL2(C.SHIELD_FLAG,C.SHIELD_FLAG||v_split_chr,'')||
NVL2(D.SHIELD_FLAG,D.SHIELD_FLAG||v_split_chr,'') SHIELD_FLAG
FROM LA_ENTER_RESULT_STG_0_TMP A,
(SELECT TCIMS_CUST_ID,
MAX(SHIELD_FLAG) SHIELD_FLAG
FROM LA_SHIELD_UNABLE_AREA_STG_TMP
GROUP BY TCIMS_CUST_ID) B,
(SELECT TCIMS_CUST_ID,
MAX(SHIELD_FLAG) SHIELD_FLAG
FROM LA_SHIELD_CHARGE_TEL_STG_TMP
GROUP BY TCIMS_CUST_ID) C,
(SELECT TCIMS_CUST_ID,
MAX(SHIELD_FLAG) SHIELD_FLAG
FROM LA_SHIELD_OPERAT_TELE_STG_TMP
GROUP BY TCIMS_CUST_ID) D
WHERE A.SYS_ID = B.TCIMS_CUST_ID(+)
AND A.SYS_ID = C.TCIMS_CUST_ID(+)
AND A.SYS_ID = D.TCIMS_CUST_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,69,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := SUBSTR(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,69,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_UNITE_ALL_IN_SHIELD_RESULT;
/***********************************************************
--功能说明: 组合屏蔽结果(出库屏蔽) 更新准备入库名单表
--参数说明:
--调用函数:
--修改记录: create by ex-qiuweisheng001/ex-liujiali001
--注意事项: 须在<SP_SHIELD_UNABLE_AREA, SP_SHIELD_CHARGE_PHONE,
SP_SHIELD_OPERATOR_PHONE SP_SHIELD_ACROSS_PHONE>
出库屏蔽完成后进行
--*********************************************************/
PROCEDURE SP_UNITE_ALL_OUT_SHIELD_RESULT
IS
p_id NUMBER; -- 日志记录id
p_errmsg VARCHAR2(500); -- 错误记录
BEGIN
-- 操作记录
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,105,NULL,'01',NULL,NULL,NULL,NULL);
--清空结果临时表
NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','LA_OUT_SHIELD_UNITE_STG_TMP');
-- 整合所有屏蔽标识
INSERT /*+APPEND*/
INTO LA_OUT_SHIELD_UNITE_STG_TMP
(TCIMS_CUST_ID,SHIELD_FLAG)
SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2)*/
DISTINCT
A.USE_SYS_ID TCIMS_CUST_ID,
NVL2(B.SHIELD_FLAG,B.SHIELD_FLAG||v_split_chr,'')||
NVL2(C.SHIELD_FLAG,C.SHIELD_FLAG||v_split_chr,'')||
NVL2(D.SHIELD_FLAG,D.SHIELD_FLAG||v_split_chr,'')||
NVL2(E.SHIELD_FLAG,E.SHIELD_FLAG||v_split_chr,'') SHIELD_FLAG
FROM DML_SEL_USE_CUST_INFO A,
(SELECT TCIMS_CUST_ID,
MAX(SHIELD_FLAG) SHIELD_FLAG
FROM LA_SHIELD_UNABLE_AREA_STG_TMP
GROUP BY TCIMS_CUST_ID) B,
(SELECT TCIMS_CUST_ID,
MAX(SHIELD_FLAG) SHIELD_FLAG
FROM LA_SHIELD_CHARGE_TEL_STG_TMP
GROUP BY TCIMS_CUST_ID) C,
(SELECT TCIMS_CUST_ID,
MAX(SHIELD_FLAG) SHIELD_FLAG
FROM LA_SHIELD_OPERAT_TELE_STG_TMP
GROUP BY TCIMS_CUST_ID) D,
(SELECT TCIMS_CUST_ID,
MAX(SHIELD_FLAG) SHIELD_FLAG
FROM LA_SHIELD_ACROSS_TELE_STG_TMP
GROUP BY TCIMS_CUST_ID) E,
DML_SEL_USE_CUST_BATCH F
WHERE F.BATCH_STATUS = '26'
AND A.BATCH_ID = F.BATCH_ID
AND A.USE_SYS_ID = B.TCIMS_CUST_ID(+)
AND A.USE_SYS_ID = C.TCIMS_CUST_ID(+)
AND A.USE_SYS_ID = D.TCIMS_CUST_ID(+)
AND A.USE_SYS_ID = E.TCIMS_CUST_ID(+);
COMMIT;
-- 更新本次操作日志
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,105,NULL,'02',NULL,NULL,NULL,NULL);
EXCEPTION
WHEN OTHERS THEN
p_errmsg := SUBSTR(sqlerrm,1,500);
NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,105,NULL,'03',p_errmsg,NULL,NULL,NULL);
RAISE;
END SP_UNITE_ALL_OUT_SHIELD_RESULT;
END NETS_TCIMS_LA_SHIELD;