屏蔽数据_SP
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;
