数据清洗2

create or replace package body NETS_TCIMS_PC_TRANSFORM
IS

  list_TAB_SCR_TYPE_PATTERN_D TAB_SCR_TYPE_PATTERN_D;
  list_TAB_TMP_TYPE_PATTERN_D TAB_TMP_SCR_TYPE_PATTERN_D;
 
  /***********************************************************
  --功能说明:   转换、标识 上海异地车
  --参数说明:
  --调用函数:
  --修改记录：  ex-liujiali001
  --注意事项：  此项操作 必须在 电话号码清洗 操作后执行  (其中有一个号码匹配也行)
  --*********************************************************/
  PROCEDURE SP_TRANSFORM_SH_YDC_FLAG
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,29,NULL,'01',NULL,NULL,NULL,NULL);

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

    --抽取记录编号、机构、电话字段  数据
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SH_YDC_FLAG_1_TMP
           (
            SYS_ID,
            SECONDARY_ORG,
            TEL_NO
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
             A.SYS_ID SYS_ID,
             TO_SINGLE_BYTE(TRIM(A.SECONDARY_ORG)) SECONDARY_ORG,
             B.TEL_NO TEL_NO
        FROM IDL_SEP_SRC_DATA A,
             (SELECT SPLIT_SYSID, SYS_ID, CODE, TEL_NO, CITY, CLEAN_STATUS
               FROM PC_CLEAN_TEL_NO_STG_TMP
              WHERE CODE IS NULL) B,   --使用的分开的电话号码
            IDL_EX_BATCH     C
       WHERE A.SYS_ID = B.SYS_ID(+)
         AND A.TCIMS_BATCH_ID = C.TCIMS_BATCH_ID
         AND C.SERIES_TYPE = '01'
         AND C.BATCH_STATUS = '11'
         AND C.PREPARE_FLAG = '1';
    COMMIT;

    --筛选指定的二级机构
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SH_YDC_FLAG_2_TMP
           (
            SYS_ID,
            TEL_NO
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             A.TEL_NO
        FROM PC_CLEAN_SH_YDC_FLAG_1_TMP A,
             BDL_RULE_YDC_ORG B
       WHERE A.SECONDARY_ORG = B.SECONDARY_ORG_code;
    COMMIT;

    --筛选电话号码 PC_CLEAN_TEL_NO_STG_TMP  电话号码表  code 为空 即为手机
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SH_YDC_FLAG_3_TMP
           (
            SYS_ID,
            CITY,
            SECONDARY_ORG,
            THIRD_ORG,
            LIST_TYPE
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID SYS_ID,
             '202001' CITY,             --上海
             '202'    SECONDARY_ORG,    --上海
             '202001' THIRD_ORG,         --上海
             'YDC'    LIST_TYPE        --修改名单类型  非新加标识 '上海YDC' SH_YDC_FLAG
        FROM PC_CLEAN_SH_YDC_FLAG_2_TMP A,
             BDL_RULE_CITY_REF_TEL B
       WHERE SUBSTR(A.TEL_NO,1,B.BEGINNO_LEN) >= B.BEGINNO
         AND SUBSTR(A.TEL_NO,1,B.ENDNO_LEN) <= B.ENDNO   
         AND B.CITY = '202001';             
    COMMIT;

    -- 更新 stg 表
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SH_YDC_FLAG_STG_TMP
           (
            SYS_ID,
            CITY,
            SECONDARY_ORG,
            THIRD_ORG,
            LIST_TYPE
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID                             SYS_ID,            
             NVL(B.CITY,A.CITY)                   CITY,
             NVL(B.SECONDARY_ORG,A.SECONDARY_ORG) SECONDARY_ORG,
             NVL(B.THIRD_ORG,A.THIRD_ORG)         THIRD_ORG,
             NVL(B.LIST_TYPE,A.LIST_TYPE)         LIST_TYPE
        FROM IDL_SEP_SRC_DATA A,
             (SELECT DISTINCT SYS_ID,LIST_TYPE,CITY,SECONDARY_ORG,THIRD_ORG
                FROM PC_CLEAN_SH_YDC_FLAG_3_TMP) B
       WHERE A.SYS_ID = B.SYS_ID(+);
    COMMIT;

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

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

  /***********************************************************
  --功能说明:   清洗、转换 电销结果
  --参数说明:
  --调用函数:
  --修改记录：  ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_TRANSFORM_SALE_RESULT
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

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

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

    --抽取数据,并清除特殊字符 去回车、Tab，全半角空格
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SALE_RESULT_1_TMP
           (
            SYS_ID,
            C51_PHONE_RESULT,
            C51_SALE_STAGE,
            C51_SALE_DECISION,
            C51_ADDED_EXPLAIN,
            C01_PHONE_RESULT,
            C01_SALE_STAGE,
            C01_SALE_DECISION,
            C01_ADDED_EXPLAIN
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID SYS_ID,
             REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C51_PHONE_RESULT), CHR(13), ''), CHR(10), ''),' ','') C51_PHONE_RESULT,
             REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C51_SALE_STAGE), CHR(13), ''), CHR(10), ''),' ','') C51_SALE_STAGE,
             REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C51_SALE_DECISION), CHR(13), ''), CHR(10), ''),' ','') C51_SALE_DECISION,
             REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C51_ADDED_EXPLAIN), CHR(13), ''), CHR(10), ''),' ','') C51_ADDED_EXPLAIN,
             REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C01_PHONE_RESULT), CHR(13), ''), CHR(10), ''),' ','') C01_PHONE_RESULT,
             REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C01_SALE_STAGE), CHR(13), ''), CHR(10), ''),' ','') C01_SALE_STAGE,
             REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C01_SALE_DECISION), CHR(13), ''), CHR(10), ''),' ','') C01_SALE_DECISION,
             REPLACE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.C01_ADDED_EXPLAIN), CHR(13), ''), CHR(10), ''),' ','') C01_ADDED_EXPLAIN
        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_SALE_RESULT_2_TMP
           (
            SYS_ID,
            C51_SALE_RESULT_CLASS,
            C01_SALE_RESULT_CLASS
            )
      SELECT /*+PARALLEL(A 4)*/
             A.SYS_ID SYS_ID,
             ( SELECT DISTINCT B.SALE_RESULT_CLASS_CODE
                 FROM BDL_RULE_SALE_RESULT_RELATION B
                 WHERE B.PHONE_RESULT_CODE||B.SALE_STAGE_CODE||B.SALE_DECISION_CODE =
                       NVL2(B.PHONE_RESULT_CODE,A.C51_PHONE_RESULT,'')||
                       NVL2(B.SALE_STAGE_CODE,A.C51_SALE_STAGE,'')||
                       NVL2(B.SALE_DECISION_CODE,A.C51_SALE_DECISION,'')
             ) C51_SALE_RESULT_CLASS,
             ( SELECT DISTINCT B.SALE_RESULT_CLASS_CODE
                 FROM BDL_RULE_SALE_RESULT_RELATION B
                 WHERE B.PHONE_RESULT_CODE||B.SALE_STAGE_CODE||B.SALE_DECISION_CODE =
                       NVL2(B.PHONE_RESULT_CODE,A.C01_PHONE_RESULT,'')||
                       NVL2(B.SALE_STAGE_CODE,A.C01_SALE_STAGE,'')||
                       NVL2(B.SALE_DECISION_CODE,A.C01_SALE_DECISION,'')
             ) C01_SALE_RESULT_CLASS
        FROM PC_CLEAN_SALE_RESULT_1_TMP A;
    COMMIT;

    -- 匹配 电销结果分类
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SALE_RESULT_3_TMP
           (
            SYS_ID,
            C51_SALE_RESULT_CLASS,
            C01_SALE_RESULT_CLASS,
            SALE_RESULT_CLASS
           )
      SELECT /*+PARALLEL(A 4)*/
             A.SYS_ID SYS_ID,
             A.C51_SALE_RESULT_CLASS,
             A.C01_SALE_RESULT_CLASS,
             (SELECT B.SALE_RESULT_CLASS
               FROM BDL_RULE_SALE_RESULT_CLASS_REL B
               WHERE B.C01_SALE_RESULT_CLASS||B.C51_SALE_RESULT_CLASS =
                     A.C01_SALE_RESULT_CLASS||A.C51_SALE_RESULT_CLASS) SALE_RESULT_CLASS
        FROM PC_CLEAN_SALE_RESULT_2_TMP A;
    COMMIT;

    -- 标识 记录中 电销结果分类 及无效电销结果
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SALE_RESULT_STG_TMP
           (
              SYS_ID,
              C51_PHONE_RESULT,
              C51_SALE_STAGE,
              C51_SALE_DECISION,
              C51_ADDED_EXPLAIN,
              C01_PHONE_RESULT,
              C01_SALE_STAGE,
              C01_SALE_DECISION,
              C01_ADDED_EXPLAIN,
              C51_SALE_RESULT_CLASS,
              C01_SALE_RESULT_CLASS,
              SALE_RESULT_CLASS,
              CLEAN_STATUS
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID SYS_ID,
             A.C51_PHONE_RESULT,
             A.C51_SALE_STAGE,
             A.C51_SALE_DECISION,
             A.C51_ADDED_EXPLAIN,
             A.C01_PHONE_RESULT,
             A.C01_SALE_STAGE,
             A.C01_SALE_DECISION,
             A.C01_ADDED_EXPLAIN,
             B.C51_SALE_RESULT_CLASS,
             B.C01_SALE_RESULT_CLASS,
             B.SALE_RESULT_CLASS,
             CASE
               WHEN B.SYS_ID IS NULL THEN
                    '0'
               ELSE
                    '1'
             END CLEAN_STATUS
        FROM PC_CLEAN_SALE_RESULT_1_TMP A,
             PC_CLEAN_SALE_RESULT_3_TMP B
       WHERE A.SYS_ID = B.SYS_ID(+);
    COMMIT;

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

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

  /***********************************************************
  --功能说明:   清洗、转换 客户分类
  --参数说明:
  --调用函数:
  --修改记录：  ex-liujiali001
  --注意事项：  本操作  须在 《清洗、转换 电销结果》 <转换、标识 上海异地车> 后执行
  --*********************************************************/
  PROCEDURE SP_CLEAN_TRANSFORM_CUST_CLASS
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

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

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

    -- 获取 电销结果分类 、名单类型 数据
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_CLASS_1_TMP
           (
            SYS_ID,
            LIST_TYPE,
            VEHICLE_NO,
            IS_CONTACTED,
            BIZ_MODEL,
            SUB_BIZMODEL,
            SALE_RESULT_CLASS,
            VEHICLE_NO_INTEGRITY,
            CUST_CLASS
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2)*/
           A.SYS_ID,
           NVL(D.LIST_TYPE,replace(A.LIST_TYPE,'/','')) LIST_TYPE,
           NVL(C.VEHICLE_NO,A.VEHICLE_NO) VEHICLE_NO,
           A.IS_CONTACTED,
           A.BIZ_MODEL,
           replace(A.SUB_BIZMODEL,'/','') SUB_BIZMODEL,
           NVL(B.SALE_RESULT_CLASS,replace(A.SALE_RESULT_CLASS,'/','')) SALE_RESULT_CLASS,
           NVL(C.VEHICLE_NO_INTEGRITY,0) VEHICLE_NO_INTEGRITY,
           '' CUST_CLASS
      FROM IDL_SEP_SRC_DATA A,
           (SELECT SYS_ID, C51_PHONE_RESULT, C51_SALE_STAGE, C51_SALE_DECISION, C51_ADDED_EXPLAIN,
                   C01_PHONE_RESULT, C01_SALE_STAGE, C01_SALE_DECISION, C01_ADDED_EXPLAIN,
                   C51_SALE_RESULT_CLASS, C01_SALE_RESULT_CLASS, SALE_RESULT_CLASS, CLEAN_STATUS
              FROM PC_CLEAN_SALE_RESULT_STG_TMP
            WHERE CLEAN_STATUS = '1' ) B,
           (SELECT SYS_ID, VEHICLE_NO, CITY, CLEAN_STATUS, VEHICLE_NO_INTEGRITY
              FROM PC_CLEAN_VEHICLE_NO_STG_TMP
           WHERE CLEAN_STATUS = '1' ) C,
           PC_CLEAN_SH_YDC_FLAG_STG_TMP D,
            IDL_EX_BATCH     E
      WHERE A.SYS_ID = B.SYS_ID(+)
        AND A.SYS_ID = C.SYS_ID(+)
        AND A.SYS_ID = D.SYS_ID(+)
        AND A.TCIMS_BATCH_ID = E.TCIMS_BATCH_ID
         AND E.SERIES_TYPE = '01'
         AND E.BATCH_STATUS = '11'
         AND E.PREPARE_FLAG = '1';
    COMMIT;

   -- 与规则匹配 标识 客户分类   优先级 1       原有客户分类的是否要处理
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_CLASS_2_TMP
           (
            SYS_ID,
            CUST_CLASS
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           B.CUST_CLASS
      FROM PC_CLEAN_CUST_CLASS_1_TMP A,
           BDL_RULE_CUST_CLASS B
     WHERE (B.SALE_RESULT_CLASS IS NULL OR INSTR(B.SALE_RESULT_CLASS,A.SALE_RESULT_CLASS) > 0)
       AND (B.IS_CONTACTED  IS NULL OR INSTR(B.IS_CONTACTED,A.IS_CONTACTED) > 0)
       AND (B.BIZ_MODEL  IS NULL OR INSTR(B.BIZ_MODEL,A.BIZ_MODEL) > 0)
       AND (B.SUB_BIZMODEL  IS NULL OR INSTR(B.SUB_BIZMODEL,A.SUB_BIZMODEL) > 0)
       AND INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
       AND B.PRIORITY = '1';
    COMMIT;

   -- 与规则匹配 标识 客户分类   优先级 2
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_CLASS_2_TMP
           (
            SYS_ID,
            CUST_CLASS
           )     
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
           A.SYS_ID,
           C.CUST_CLASS
      FROM BDL_RULE_CRM_A_CLASS_CUST B,
           PC_CLEAN_CUST_CLASS_1_TMP A,
           BDL_RULE_CUST_CLASS C
     WHERE A.VEHICLE_NO = B.VEHICLE_NO
       AND INSTR(C.LIST_TYPE,A.LIST_TYPE) > 0
       AND A.VEHICLE_NO_INTEGRITY = '1'
       AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
                       WHERE D.SYS_ID = A.SYS_ID)
       AND C.COMPARE_RULE = '1'
       AND C.PRIORITY = '2';
    COMMIT;

   -- 与规则匹配 标识 客户分类  优先级 3
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_CLASS_2_TMP
           (
            SYS_ID,
            CUST_CLASS
           )     
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           B.CUST_CLASS
      FROM PC_CLEAN_CUST_CLASS_1_TMP A,
           BDL_RULE_CUST_CLASS B
     WHERE (B.SALE_RESULT_CLASS IS NULL OR INSTR(B.SALE_RESULT_CLASS,A.SALE_RESULT_CLASS) > 0)
       AND (B.IS_CONTACTED  IS NULL OR INSTR(B.IS_CONTACTED,A.IS_CONTACTED) > 0)
       AND (B.BIZ_MODEL  IS NULL OR INSTR(B.BIZ_MODEL,A.BIZ_MODEL) > 0)
       AND (B.SUB_BIZMODEL  IS NULL OR INSTR(B.SUB_BIZMODEL,A.SUB_BIZMODEL) > 0)
       AND INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
       AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
                       WHERE D.SYS_ID = A.SYS_ID)
       AND B.PRIORITY = '3';
    COMMIT;

   -- 与规则匹配 标识 客户分类  优先级 4
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_CLASS_2_TMP
           (
            SYS_ID,
            CUST_CLASS
           )     
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           B.CUST_CLASS
      FROM PC_CLEAN_CUST_CLASS_1_TMP A,
           BDL_RULE_CUST_CLASS B
     WHERE (B.SALE_RESULT_CLASS IS NULL OR INSTR(B.SALE_RESULT_CLASS,A.SALE_RESULT_CLASS) > 0)
       AND (B.IS_CONTACTED  IS NULL OR INSTR(B.IS_CONTACTED,A.IS_CONTACTED) > 0)
       AND (B.BIZ_MODEL  IS NULL OR INSTR(B.BIZ_MODEL,A.BIZ_MODEL) > 0)
       AND (B.SUB_BIZMODEL  IS NULL OR INSTR(B.SUB_BIZMODEL,A.SUB_BIZMODEL) > 0)
       AND INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
       AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
                       WHERE D.SYS_ID = A.SYS_ID)
       AND B.PRIORITY = '4';
    COMMIT;

   -- 与规则匹配 标识 客户分类  优先级 5
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_CLASS_2_TMP
           (
            SYS_ID,
            CUST_CLASS
           )     
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           B.CUST_CLASS
      FROM PC_CLEAN_CUST_CLASS_1_TMP A,
           BDL_RULE_CUST_CLASS B
     WHERE (B.SALE_RESULT_CLASS IS NULL OR INSTR(B.SALE_RESULT_CLASS,A.SALE_RESULT_CLASS) > 0)
       AND (B.IS_CONTACTED  IS NULL OR INSTR(B.IS_CONTACTED,A.IS_CONTACTED) > 0)
       AND (B.BIZ_MODEL  IS NULL OR INSTR(B.BIZ_MODEL,A.BIZ_MODEL) > 0)
       AND (B.SUB_BIZMODEL  IS NULL OR INSTR(B.SUB_BIZMODEL,A.SUB_BIZMODEL) > 0)
       AND INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
       AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
                       WHERE D.SYS_ID = A.SYS_ID)
       AND B.PRIORITY = '5';
    COMMIT;

   -- 与规则匹配 标识 客户分类  优先级 8
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_CLASS_2_TMP
           (
            SYS_ID,
            CUST_CLASS
           )     
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           B.CUST_CLASS
      FROM PC_CLEAN_CUST_CLASS_1_TMP A,
           BDL_RULE_CUST_CLASS B
     WHERE INSTR(B.LIST_TYPE,A.LIST_TYPE) > 0
       AND NOT EXISTS(SELECT 1 FROM PC_CLEAN_CUST_CLASS_2_TMP D
                       WHERE D.SYS_ID = A.SYS_ID)
       AND B.PRIORITY = '8';
    COMMIT;

    ---- 整理 标识 客户分类
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_CLASS_STG_TMP
           (
            SYS_ID,
            CUST_CLASS
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           B.CUST_CLASS
      FROM PC_CLEAN_CUST_CLASS_1_TMP A,
           PC_CLEAN_CUST_CLASS_2_TMP B
     WHERE A.SYS_ID = B.SYS_ID(+);
    COMMIT;

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

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

  /***********************************************************
  --功能说明:   合并清洗后的 CITY/客户身份证件号码/车牌号码
                             /联系人地址/电话号码 供地区标识使用
  --参数说明:
  --调用函数:
  --修改记录：  EX-LIUJIALI001
  --注意事项：  顺序在清洗功能完成之后   , 地区标识 之前
  --*********************************************************/
  PROCEDURE SP_UNITE_FIELD_FOR_AREA_INFO
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,25,NULL,'01',NULL,NULL,NULL,NULL);

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

    -- 合并清洗结果，并进行记录有效性标识（出现需要手工清洗的字段即为无效，目前只有车牌号码、电话号码）
    INSERT /*+APPEND*/
      INTO PC_UNITE_MANY_FIELD_1_STG_TMP
      ( SYS_ID,
        CITY,
        ID_NUMBER,
        VEHICLE_NO,
        ADDRESS,
        TEL_NO,
        STATUS)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2)*/
           A.SYS_ID,
           A.CITY,
           B.ID_NUMBER,
           C.VEHICLE_NO,
           D.ADDRESS,
           --E.CODE,
           E.TEL_NO,
           CASE
             WHEN C.SYS_ID IS NULL OR E.SYS_ID IS NULL THEN
                  '0'
             ELSE
                  '1'
           END STATUS
      FROM IDL_SEP_SRC_DATA            A,
           (SELECT SYS_ID, ID_NUMBER, CLEAN_STATUS, SEX, CUST_DOB
              FROM PC_CLEAN_ID_NUMBER_STG_TMP
           WHERE CLEAN_STATUS = '1') B,   --身份证号码
           (SELECT SYS_ID, VEHICLE_NO, CITY, CLEAN_STATUS, VEHICLE_NO_INTEGRITY
              FROM PC_CLEAN_VEHICLE_NO_STG_TMP
           WHERE CLEAN_STATUS = '1') C,   --车牌号码
           (SELECT SYS_ID, ADDRESS, IS_ADDRESS_VALID
              FROM PC_CLEAN_ADDRESS_STG_TMP 
           WHERE IS_ADDRESS_VALID = '1')  D,   --联系人地址
           (SELECT SYS_ID, TEL_NO, CITY, CLEAN_STATUS
              FROM PC_CLEAN_TELEPHONE_STG_TMP
           WHERE CLEAN_STATUS = '1') E,    --电话号码（使用合并的电话号码）
           IDL_EX_BATCH              F     
           --PC_CLEAN_TEL_NO_STG_TMP     E    --电话号码
     WHERE A.SYS_ID = B.SYS_ID(+)
       AND A.SYS_ID = C.SYS_ID(+)
       AND A.SYS_ID = D.SYS_ID(+)
       AND A.SYS_ID = E.SYS_ID(+)
       AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
         AND F.SERIES_TYPE = '01'
         AND F.BATCH_STATUS = '11'
         AND F.PREPARE_FLAG = '1';
    COMMIT;                

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,25,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,25,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
  END SP_UNITE_FIELD_FOR_AREA_INFO;

  /***********************************************************
  --功能说明:   地区标识
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须在 NETS_TCIMS_PC_TRANSFORM.SP_UNITE_FIELD_FOR_AREA_INFO 执行完后，才能执行
  --*********************************************************/
  PROCEDURE SP_CLEAN_TRANSFORM_AREA_INFO
  IS

    list_CITY_NAME Tab_CITY_NAME;

    CURSOR CUR_SCR_TYPE_PATTERN_D(v_CITY_NAME VARCHAR2) IS
      SELECT CITY ,
             ID_NUMBER ,
             VEHICLE_NO ,
             VEHICLE_NO_NOTLIKE,
             ADDRESS ,
             ADDRESS_NOTLIKE ,
             TELEPHONE_NUMBER ,
             AREA_FLAG
        FROM BDL_RULE_REF_AREA_FLAG_2
       WHERE CITY = v_CITY_NAME;

    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

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

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

    --地区标识（不可直接使用LIKE，需要经过解析），其中CITY_NAME与NDC_REF_AREA_FLAG_1互斥
    SELECT CITY BULK COLLECT
      INTO list_CITY_NAME
      FROM (SELECT DISTINCT CITY FROM BDL_RULE_REF_AREA_FLAG_2
            INTERSECT
            SELECT DISTINCT CITY FROM PC_UNITE_MANY_FIELD_1_STG_TMP); --单行电话记录

    FOR i IN 1 .. list_CITY_NAME.COUNT LOOP
       OPEN CUR_SCR_TYPE_PATTERN_D(list_CITY_NAME(i));
      FETCH CUR_SCR_TYPE_PATTERN_D BULK COLLECT
        INTO list_TAB_SCR_TYPE_PATTERN_D;
      CLOSE CUR_SCR_TYPE_PATTERN_D;

      IF list_TAB_SCR_TYPE_PATTERN_D.COUNT > 0 THEN
        DELETE FROM PC_CLEAN_CRM_AREA_FLAG_1_TMP;
        COMMIT;

        DELETE FROM PC_SCR_TYPE_PATTERN_D_TMP;
        COMMIT;

        --解析规则
        TRY_FORMAT_SCR_RULES;
        COMMIT;

         SELECT CITY,
                ID_NUMBER,
                ID_NUMBER_LIST,
                VEHICLE_NO,
                VEHICLE_NO_LIST,
                VEHICLE_NO_NOTLIKE,
                VEHICLE_NO_NOTLIKE_LIST,
                ADDRESS,
                ADDRESS_LIST,
                ADDRESS_NOTLIKE,
                ADDRESS_NOTLIKE_LIST,
                TELEPHONE_NUMBER,
                EXEC_TEL_NO_LIST,
                CRM_AREA  BULK COLLECT
          INTO list_TAB_TMP_TYPE_PATTERN_D
          FROM PC_SCR_TYPE_PATTERN_D_TMP;

        INSERT /*+APPEND*/
          INTO PC_CLEAN_CRM_AREA_FLAG_1_TMP
               (
                 SYS_ID,
                 CRM_AREA_FLAG
               )
          SELECT /*+PARALLEL(A,4)*/
                 A.SYS_ID,
                 TRY_MATCH_AREA_FLAG(A.ID_NUMBER ,A.VEHICLE_NO ,A.VEHICLE_NO ,
                                     A.ADDRESS ,A.ADDRESS ,A.TEL_NO) CRM_AREA_FLAG
            FROM PC_UNITE_MANY_FIELD_1_STG_TMP A
           WHERE A.CITY = list_CITY_NAME(i);
        COMMIT;
       
      --地区标识（不可直接使用LIKE）
      INSERT /*+APPEND*/
        INTO PC_CLEAN_CRM_AREA_FLAG_STG_TMP
               (
                 SYS_ID,
                 CRM_AREA_FLAG
               )       
          SELECT /*+PARALLEL(A,4)*/
                 A.SYS_ID,
                 A.CRM_AREA_FLAG CRM_AREA_FLAG
            FROM PC_CLEAN_CRM_AREA_FLAG_1_TMP A
           WHERE A.CRM_AREA_FLAG IS NOT NULL;
        COMMIT;
       
      END IF;

    END LOOP;

    --地区标识（可直接使用LIKE），其中CITY_NAME与NDC_REF_AREA_FLAG_2互斥
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CRM_AREA_FLAG_STG_TMP
               (
                 SYS_ID,
                 CRM_AREA_FLAG
               )           
      SELECT SYS_ID,
             CRM_AREA_FLAG
        FROM (SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
                     A.SYS_ID,
                     B.AREA_FLAG CRM_AREA_FLAG ,
                     ROW_NUMBER()OVER(PARTITION BY A.SYS_ID ORDER BY A.SYS_ID DESC NULLS LAST) RN
                FROM PC_UNITE_MANY_FIELD_1_STG_TMP A,
                     BDL_RULE_REF_AREA_FLAG_1 B
               WHERE A.CITY = B.CITY
                 AND ((B.ID_NUMBER IS NOT NULL AND A.ID_NUMBER LIKE B.ID_NUMBER) OR B.ID_NUMBER IS NULL)
                 AND ((B.VEHICLE_NO IS NOT NULL AND A.VEHICLE_NO LIKE B.VEHICLE_NO) OR B.VEHICLE_NO IS NULL)
                 AND ((B.VEHICLE_NO_NOTLIKE IS NOT NULL AND A.VEHICLE_NO NOT LIKE B.VEHICLE_NO_NOTLIKE) OR
                     B.VEHICLE_NO_NOTLIKE IS NULL)
                 AND ((B.ADDRESS IS NOT NULL AND A.ADDRESS LIKE B.ADDRESS) OR B.ADDRESS IS NULL)
                 AND ((B.ADDRESS_NOTLIKE IS NOT NULL AND A.ADDRESS NOT LIKE B.ADDRESS_NOTLIKE) OR
                     B.ADDRESS_NOTLIKE IS NULL)
                 AND ((B.TELEPHONE_NUMBER IS NOT NULL AND
                     (((A.CODE || '-' || A.TEL_NO) LIKE B.TELEPHONE_NUMBER AND A.CODE IS NOT NULL)
                     OR (A.CODE IS NULL AND A.TEL_NO LIKE B.TELEPHONE_NUMBER))                     
                     OR B.TELEPHONE_NUMBER IS NULL)))
       WHERE RN = 1;
    COMMIT;
   
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,28,NULL,'02',NULL,NULL,NULL,NULL);

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

  /*
    部分规则解析
  */
  PROCEDURE TRY_FORMAT_SCR_RULES IS

    var_idno                 VARCHAR2(100) := NULL;
    var_idno_LIST            RangeLikeObjList := NULL;
    var_car_no               VARCHAR2(100) := NULL;
    var_car_no_LIST          RangeLikeObjList := NULL;
    var_car_no_notlike       VARCHAR2(100) := NULL;
    var_car_no_notlike_LIST  RangeLikeObjList := NULL;
    var_address              VARCHAR2(100) := NULL;
    var_address_LIST         RangeLikeObjList := NULL;
    var_address_notlike      VARCHAR2(100) := NULL;
    var_address_notlike_LIST RangeLikeObjList := NULL;
    var_tel_no               VARCHAR2(100) := NULL;
    var_tel_no_LIST          RangeLikeObjList := NULL;
  begin

    FOR i IN list_TAB_SCR_TYPE_PATTERN_D.FIRST .. list_TAB_SCR_TYPE_PATTERN_D.LAST LOOP
      --nets_客户身份证件号码
      TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
                                   .id_number ,
                                   var_idno,
                                   var_idno_LIST);
      --nets_车牌号码
      TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
                                   .vehicle_no ,
                                   var_car_no,
                                   var_car_no_LIST);
      --notlike_nets_车牌号码
      TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
                                   .vehicle_no_notlike ,
                                   var_car_no_notlike,
                                   var_car_no_notlike_LIST);
      --nets_联系人地址
      TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
                                   .address ,
                                   var_address,
                                   var_address_LIST);
      --notlike_nets_联系人地址
      TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
                                   .address_notlike ,
                                   var_address_notlike,
                                   var_address_notlike_LIST);
      --电话号码
      TRY_GET_SCR_RangeLikeObjList(list_TAB_SCR_TYPE_PATTERN_D(i)
                                   .telephone_number ,
                                   var_tel_no,
                                   var_tel_no_LIST);

      INSERT INTO PC_SCR_TYPE_PATTERN_D_TMP
        ( CITY,
          ID_NUMBER,
          ID_NUMBER_LIST,
          VEHICLE_NO,
          VEHICLE_NO_LIST,
          VEHICLE_NO_NOTLIKE,
          VEHICLE_NO_NOTLIKE_LIST,
          ADDRESS,
          ADDRESS_LIST,
          ADDRESS_NOTLIKE,
          ADDRESS_NOTLIKE_LIST,
          TELEPHONE_NUMBER,
          EXEC_TEL_NO_LIST ,
          CRM_AREA)
      VALUES
        (list_TAB_SCR_TYPE_PATTERN_D(i).city ,
         var_idno,
         var_idno_LIST,
         var_car_no,
         var_car_no_LIST,
         var_car_no_notlike,
         var_car_no_notlike_LIST,
         var_address,
         var_address_LIST,
         var_address_notlike,
         var_address_notlike_LIST,
         var_tel_no,
         var_tel_no_LIST,
         list_TAB_SCR_TYPE_PATTERN_D(i).AREA_FLAG);

    END LOOP;

  end TRY_FORMAT_SCR_RULES;

  /*
     分解规则
  */
  PROCEDURE TRY_GET_SCR_RangeLikeObjList(v_INSTR   IN VARCHAR2,
                                         v_OUTSTR  OUT VARCHAR2,
                                         v_OUTLIST OUT RangeLikeObjList) IS
    aArray         RangeLikeObjList := RangeLikeObjList();
    i_start        integer;
    i_end          integer;
    inx            integer;
    var_rangestr   VARCHAR2(600) := '';
    var_torangestr VARCHAR2(1000) := '';
    var_tmpstr     VARCHAR2(1000) := '';
  begin
    var_tmpstr := v_INSTR;
    inx        := 1;
    i_start    := 0;
    i_end      := 0;

    IF var_tmpstr IS NULL THEN
      v_OUTSTR  := NULL;
      v_OUTLIST := NULL;
      RETURN;
    ELSE
      --存在类似粤J-[012][0129][0129][0012][0012]
      LOOP
        i_start := INSTR(var_tmpstr, '[');
        EXIT WHEN i_start = 0;
        i_end := INSTR(var_tmpstr, ']');

        var_rangestr   := SUBSTR(var_tmpstr,
                                 i_start + 1,
                                 i_end - i_start - 1);
        var_torangestr := '';
        FOR j IN 1 .. LENGTH(var_rangestr) LOOP
          var_torangestr := var_torangestr || '''' ||
                            SUBSTR(var_rangestr, j, 1) || ''',';
        END LOOP;

        var_torangestr := SUBSTR(var_torangestr,
                                 1,
                                 LENGTH(var_torangestr) - 1);
        var_tmpstr     := SUBSTR(var_tmpstr, 1, i_start - 1) || '_' ||
                          SUBSTR(var_tmpstr, i_end + 1);
        aArray.Extend;
        aArray(inx) := RangeLikeObj(i_start, var_torangestr);
        inx := inx + 1;
      END LOOP;

      v_OUTSTR  := var_tmpstr;
      v_OUTLIST := aArray;
    END IF;

  END;

  /*
     标识地区
  */
  --六个字段如果不是NULL，要AND匹配
  function TRY_MATCH_AREA_FLAG(v_IDNO            VARCHAR2,
                               v_CAR_NO          VARCHAR2,
                               v_CAR_NO_NOTLIKE  VARCHAR2,
                               v_ADDRESS         VARCHAR2,
                               v_ADDRESS_NOTLIKE VARCHAR2,
                               v_TEL_NO          VARCHAR2) return varchar2 IS

    var_tmpstr              VARCHAR2(1000) := '';
    str_array               NETS_TCIMS_COM_CLEANOUT.type_array;
    var_idno                VARCHAR2(1) := '0';
    var_car_no              VARCHAR2(1) := '0';
    var_car_no_notlike      VARCHAR2(1) := '0';
    var_car_no_notlike_STR  VARCHAR2(100) := '';
    var_address             VARCHAR2(1) := '0';
    var_address_notlike     VARCHAR2(1) := '0';
    var_address_notlike_STR VARCHAR2(100) := '';
    var_tel_no              VARCHAR2(1) := '0';
    var_tmp                 VARCHAR2(100) := '';
    aArray                  RangeLikeObjList := RangeLikeObjList();
  begin

    FOR i IN list_TAB_TMP_TYPE_PATTERN_D.FIRST .. list_TAB_TMP_TYPE_PATTERN_D.LAST LOOP
      var_idno                := '0';
      var_car_no              := '0';
      var_car_no_notlike      := '0';
      var_address             := '0';
      var_address_notlike     := '0';
      var_tel_no              := '0';
      var_car_no_notlike_STR  := '';
      var_address_notlike_STR := '';

      --nets_客户身份证件号码
      var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).ID_NUMBER;

      IF var_tmpstr IS NULL THEN
        var_idno := '1';
      ELSE
        IF v_IDNO LIKE var_tmpstr THEN
          var_idno := '1';
          aArray   := list_TAB_TMP_TYPE_PATTERN_D(i).ID_NUMBER_LIST;
          FOR k IN 1 .. aArray.COUNT LOOP
            IF SUBSTR(v_IDNO, aArray(k).id, 1) NOT IN (aArray(k).rangestr) THEN
              var_idno := '0';
              EXIT;
            END IF;
          END LOOP;
        END IF;
      END IF;

      --nets_车牌号码
      var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).VEHICLE_NO;
      aArray.DELETE();

      IF var_tmpstr IS NULL THEN
        var_car_no := '1';
      ELSE

        IF v_CAR_NO LIKE var_tmpstr THEN
          var_car_no := '1';
          aArray     := list_TAB_TMP_TYPE_PATTERN_D(i).VEHICLE_NO_LIST;
          FOR k IN 1 .. aArray.COUNT LOOP
            IF SUBSTR(v_CAR_NO, aArray(k).id, 1) NOT IN
               (aArray(k).rangestr) THEN
              var_car_no := '0';
              EXIT;
            END IF;
          END LOOP;
        END IF;

      END IF;

      --notlike_nets_车牌号码
      var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).VEHICLE_NO_NOTLIKE;
      aArray.DELETE();

      IF var_tmpstr IS NULL THEN
        var_car_no_notlike := '1';
      ELSE
        IF v_CAR_NO_NOTLIKE LIKE var_tmpstr THEN
          var_car_no_notlike := '0';
          aArray             := list_TAB_TMP_TYPE_PATTERN_D(i)
                               .VEHICLE_NO_NOTLIKE_LIST;
          FOR k IN 1 .. aArray.COUNT LOOP
            IF SUBSTR(v_CAR_NO_NOTLIKE, aArray(k).id, 1) IN
               (aArray(k).rangestr) THEN
              var_car_no_notlike_STR := var_car_no_notlike_STR || '0';
            ELSE
              var_car_no_notlike_STR := var_car_no_notlike_STR || '1';
            END IF;
          END LOOP;
        ELSE
          var_car_no_notlike := '1';
        END IF;

      END IF;

      --nets_联系人地址
      var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).ADDRESS;
      aArray.DELETE();

      IF var_tmpstr IS NULL THEN
        var_address := '1';
      ELSE
        IF v_ADDRESS LIKE var_tmpstr THEN
          var_address := '1';
          aArray      := list_TAB_TMP_TYPE_PATTERN_D(i).ADDRESS_LIST;
          FOR k IN 1 .. aArray.COUNT LOOP
            IF SUBSTR(v_ADDRESS, aArray(k).id, 1) NOT IN
               (aArray(k).rangestr) THEN
              var_address := '0';
              EXIT;
            END IF;
          END LOOP;
        END IF;

      END IF;

      --notlike_nets_联系人地址
      var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).ADDRESS_NOTLIKE;
      aArray.DELETE();

      IF var_tmpstr IS NULL THEN
        var_address_notlike := '1';
      ELSE
        IF v_ADDRESS_NOTLIKE LIKE var_tmpstr THEN
          var_address_notlike := '0';
          aArray              := list_TAB_TMP_TYPE_PATTERN_D(i)
                                .ADDRESS_NOTLIKE_LIST;
          FOR k IN 1 .. aArray.COUNT LOOP
            IF SUBSTR(v_ADDRESS_NOTLIKE, aArray(k).id, 1) IN
               (aArray(k).rangestr) THEN
              var_address_notlike_STR := var_address_notlike_STR || '0';
            ELSE
              var_address_notlike_STR := var_address_notlike_STR || '1';
            END IF;
          END LOOP;
        ELSE
          var_address_notlike := '1';
        END IF;

      END IF;

      --电话号码
      var_tmpstr := list_TAB_TMP_TYPE_PATTERN_D(i).TELEPHONE_NUMBER;
      aArray.DELETE();

      IF var_tmpstr IS NULL THEN
        var_tel_no := '1';
      ELSE
        --v_TEL_NO 是由多个电话号码用“,”连接起来的，要拆分匹配
        str_array := NETS_TCIMS_COM_CLEANOUT.SPLIT_STR(v_TEL_NO, '/');
        IF str_array.count > 0 THEN
          aArray := list_TAB_TMP_TYPE_PATTERN_D(i).EXEC_TEL_NO_LIST;
          FOR ii in str_array.first .. str_array.last LOOP
            IF str_array(ii) IS NOT NULL AND str_array(ii) LIKE var_tmpstr THEN
              var_tel_no := '1';
              FOR k IN 1 .. aArray.COUNT LOOP
                IF SUBSTR(str_array(ii), aArray(k).id, 1) NOT IN
                   (aArray(k).rangestr) THEN
                  var_tel_no := '0';
                  EXIT;
                END IF;
              END LOOP;
            END IF;
            IF var_tel_no = '1' THEN
              EXIT;
            END IF;
          END LOOP;
        END IF;

      END IF;

      IF var_idno = '1' AND var_car_no = '1' AND
         (var_car_no_notlike = '1' OR
         (var_car_no_notlike = '0' AND var_car_no_notlike_STR IS NOT NULL AND
         INSTR(var_car_no_notlike_STR, '1') > 0)) AND var_address = '1' AND
         (var_address_notlike = '1' OR
         (var_address_notlike = '0' AND
         var_address_notlike_STR IS NOT NULL AND
         INSTR(var_address_notlike_STR, '1') > 0)) AND var_tel_no = '1' THEN
        var_tmp := list_TAB_TMP_TYPE_PATTERN_D(i).CRM_AREA;
        EXIT;
      END IF;
    END LOOP;

    return(var_tmp);

  end TRY_MATCH_AREA_FLAG;

  /***********************************************************
  --功能说明:   清洗客户姓名_1(外部程序清洗前的操作)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_CUST_NAME_1
  IS
 
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
    v_spec_chr         VARCHAR2(200);        
    v_spec_chr_length  NUMBER(10);
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,24,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CUST_NAME_3_TMP');
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CUST_NAME_4_TMP');
   
    v_spec_chr := NETS_TCIMS_COM_CLEANOUT.V_SPECIAL_CHR_EN||
                  NETS_TCIMS_COM_CLEANOUT.V_SPECIAL_CHR_ZN||
                  NETS_TCIMS_COM_CLEANOUT.v_small_letter||
                  NETS_TCIMS_COM_CLEANOUT.v_capital_letter||
                  NETS_TCIMS_COM_CLEANOUT.v_figure;
   
     v_spec_chr_length := length(v_spec_chr);
   
    -- 获取姓名列的源数据
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_NAME_1_TMP
           (
             SYS_ID,
             CUST_NAME
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           REPLACE(TRANSLATE(A.CUST_NAME,
                             CHR(43683) || CHR(63918) || CHR(42235) || CHR(43682) ||
                             CHR(42107) || CHR(44979) || chr(44789),
                             '       '),
                   ' ',
                   '') CUST_NAME
        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_CUST_NAME_6_TMP
           (
             SYS_ID,
             CUST_NAME
           )
      SELECT /*+PARALLEL(A,4)*/
             SYS_ID,
             REPLACE(TRANSLATE(REPLACE(REPLACE(TO_SINGLE_BYTE(CUST_NAME),
                                                  CHR(13),''),
                                       CHR(10),''),
                               v_spec_chr,
                               rpad(' ',v_spec_chr_length,' ')),
                     ' ',
                     '') CUST_NAME
        FROM PC_CLEAN_CUST_NAME_1_TMP A;
    COMMIT;
 
    -- 根据"客户姓名前后缀"索引表去掉姓名前后缀
     INSERT /*+APPEND*/
       INTO PC_CLEAN_CUST_NAME_8_TMP
           (
             SYS_ID,
             CUST_NAME
           )      
       SELECT SYS_ID, CUST_NAME
         FROM (SELECT SYS_ID,
                      CUST_NAME,
                      ROW_NUMBER() OVER(PARTITION BY SYS_ID ORDER BY SYS_ID DESC) RN
                 FROM (SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
                              A.SYS_ID, SUBSTR(A.CUST_NAME, B.LEN + 1) CUST_NAME
                         FROM PC_CLEAN_CUST_NAME_6_TMP A,
                              BDL_RULE_REF_NAME_PPFIX B
                        WHERE B.PPFIX = SUBSTR(A.CUST_NAME, 1, B.LEN)))
        WHERE RN = 1;
     COMMIT;
    
     INSERT /*+APPEND*/
       INTO PC_CLEAN_CUST_NAME_9_TMP
           (
             SYS_ID,
             CUST_NAME
           )      
     SELECT /*+PARALLEL(C 2) PARALLEL(D 2)*/
            C.SYS_ID,
            NVL(D.CUST_NAME, C.CUST_NAME) CUST_NAME
         FROM PC_CLEAN_CUST_NAME_6_TMP C,
              PC_CLEAN_CUST_NAME_8_TMP D
        WHERE C.SYS_ID = D.SYS_ID(+);
     COMMIT;
 
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_NAME_2_TMP
           (
             SYS_ID,
             CUST_NAME
           )     
      SELECT SYS_ID,
             CUST_NAME
        FROM (SELECT SYS_ID,
                     CUST_NAME,
                     ROW_NUMBER() OVER(PARTITION BY SYS_ID ORDER BY SYS_ID DESC) RN
                FROM (SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
                       A.SYS_ID,
                       SUBSTR(A.CUST_NAME, 1, LENGTH(A.CUST_NAME) - B.LEN) CUST_NAME
                        FROM PC_CLEAN_CUST_NAME_9_TMP A,
                             BDL_RULE_REF_NAME_PPFIX B
                       WHERE B.PPFIX = SUBSTR(A.CUST_NAME, -B.LEN)))
       WHERE RN = 1;
    COMMIT;
 
    --取去掉姓名前后缀的名称 或者没有匹配上的合并
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_NAME_3_TMP
           (
             SYS_ID,
             CUST_NAME
           )     
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID, NVL(B.CUST_NAME, A.CUST_NAME) CUST_NAME
      FROM PC_CLEAN_CUST_NAME_6_TMP A,
           PC_CLEAN_CUST_NAME_2_TMP B
     WHERE A.SYS_ID = B.SYS_ID(+);
    COMMIT;
   
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,24,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   清洗客户姓名_2(外部程序清洗后的操作)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_CUST_NAME_2
  IS
 
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CUST_NAME_STG_TMP');
   
    --将"张王李赵"姓氏概率较大的字符开头的,且姓名长度为4的判断为个人
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_NAME_7_TMP
           (
            SYS_ID,
            CUST_NAME,
            ORG_FLAG
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             A.CUST_NAME,
             CASE
               WHEN B.ORG_FLAG IS NULL AND
                    SUBSTR(A.CUST_NAME, 1, 1) IN ('张', '王', '李', '赵') AND
                    LENGTH(A.CUST_NAME) = 4 THEN
                    'P'
               ELSE
                    B.ORG_FLAG
             END ORG_FLAG
        FROM PC_CLEAN_CUST_NAME_3_TMP A,
             PC_CLEAN_CUST_NAME_4_TMP B
       WHERE A.SYS_ID = B.SYS_ID(+);
    commit;
    -- 长度等于中文字符长度，中文字符长度小于等于4标识为"个人"，
    -- 同时将长度为1的补上"*"，长度大于4标识为"团体"
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_NAME_5_TMP
           (
            SYS_ID,
            CUST_NAME,
            ORG_FLAG
           )     
      SELECT /*+PARALLEL(A 4)*/
             A.SYS_ID,
             CASE
               WHEN A.ORG_FLAG IS NULL THEN
                CASE
               WHEN LENGTH(A.CUST_NAME) * 2 = LENGTHB(A.CUST_NAME) AND
                    LENGTH(A.CUST_NAME) = 1 THEN
                A.CUST_NAME || '*'
               ELSE
                A.CUST_NAME
             END ELSE A.CUST_NAME END CUST_NAME,
             CASE
               WHEN A.ORG_FLAG IS NULL THEN
                 CASE
                   WHEN A.CUST_NAME IS NOT NULL AND LENGTH(A.CUST_NAME) * 2 = LENGTHB(A.CUST_NAME) THEN
                      CASE
                         WHEN LENGTH(A.CUST_NAME) <= 4 THEN
                              'P'
                         ELSE
                              'O'
                         END
                   ELSE
                         NULL
                   END
               ELSE
                   A.ORG_FLAG
               END ORG_FLAG
        FROM PC_CLEAN_CUST_NAME_7_TMP A;
    COMMIT;
 
    -- 将清洗完的数据插入的STG表，并标识清洗状态
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CUST_NAME_STG_TMP
           (
              SYS_ID,
              CUST_NAME,
              ORG_FLAG,
              CLEAN_STATUS
           )
      SELECT /*+PARALLE(A 4)*/
             SYS_ID,
             CUST_NAME,
             ORG_FLAG,
             CASE
               WHEN ORG_FLAG IS NULL THEN
                '0'
               ELSE
                '1'
             END CLEAN_STATUS
        FROM PC_CLEAN_CUST_NAME_5_TMP A;
    COMMIT;
   
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,27,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,27,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_CUST_NAME_2;
 
  /***********************************************************
  --功能说明:   清洗完成后，更新批次表
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须在 产险清洗 返回手工清洗 完毕 后 进行
  --*********************************************************/
  PROCEDURE SP_UPDATE_BATCH_INFO
  IS
 
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,108,NULL,'01',NULL,NULL,NULL,NULL);

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

    -- 更新批次状态，及数据信息
    UPDATE /*+PARALLE(A 4)*/
           IDL_EX_BATCH A
       SET A.INVALID_CUST_COUNT = (SELECT count(*)
                                     FROM IDL_SQL_SEP_CUST_RETURN B
                                    WHERE B.TCIMS_BATCH_ID = A.TCIMS_BATCH_ID),
           A.VALID_CUST_COUNT = (SELECT A.BATCH_TOTAL_NUM - count(*)
                                   FROM IDL_SQL_SEP_CUST_RETURN B
                                  WHERE B.TCIMS_BATCH_ID = A.TCIMS_BATCH_ID),
           A.BATCH_STATUS = '12',
           A.UPDATED_DATE = SYSDATE,
           A.UPDATED_BY = 'SYSTEM'                   
     WHERE A.BATCH_STATUS = '11'
       AND A.SERIES_TYPE = '01'
       AND A.PREPARE_FLAG = '1';
    COMMIT;
   
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,108,NULL,'02',NULL,NULL,NULL,NULL);

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