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