`
haofeng0305
  • 浏览: 31343 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

数据清洗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;

分享到:
评论

相关推荐

    数据清洗PPT.zip

    2. 记录清洗过程:保留数据清洗的历史记录,便于复核和审计。 3. 自动化清洗:利用脚本或ETL工具实现数据清洗的自动化,提高效率。 4. 数据验证:清洗后需进行数据验证,确保处理后的数据满足预期。 通过学习南工程...

    数据清洗研究综述 数据清洗现状

    2. **事后诊断(数据清洗)**:针对已经存在的脏数据进行检测和清除,以提高数据质量。这包括修复错误、填补缺失值、去除重复记录等操作。 #### 三、数据清洗与数据集成的关系 数据清洗与数据集成是数据预处理过程...

    数据清洗ppt-181119.zip

    清华大学课程ppt----数据清洗并没有统一的定义,其定义依赖于具体的应用领域。从广义上讲,数据清洗是将原始数据进行精简以去除冗余和消除不一致,并使剩余的数据转换成可接收的标准格式的过程;而狭义上的数据清洗...

    数据清洗数据源.zip

    数据清洗是数据分析过程中的关键步骤,它涉及到对原始数据进行检查、处理和转换,以确保数据的质量和准确性,为后续的数据分析和挖掘提供可靠的基础。在这个"数据清洗数据源.zip"压缩包中,包含了用于教育和培训...

    清华大学精品大数据之数据清洗课程PPT课件(38页)含习题 第3章 数据清洗ETL基本技术方法.pptx

    数据清洗是大数据领域中至关重要的一个环节,它涉及到数据的质量保证和有效利用。清华大学的这份精品课程《大数据之数据清洗》深入浅出地讲解了数据清洗的ETL(Extraction-Transformation-Loading)基本技术方法,...

    大数据——数据清洗加工技术架构方案(修改版)

    "大数据——数据清洗加工技术架构方案(修改版)" 在大数据时代,数据清洗加工技术架构方案是一个非常重要的组成部分。数据清洗是指对原始数据进行处理、转换和标准化,以确保数据的质量和一致性。今天,我们将对...

    mapreduce项目 数据清洗

    2. **数据清洗**: 数据清洗是大数据处理中的关键步骤,主要是去除不完整、错误、重复或无关的数据。在MapReduce中,数据清洗通常在Map阶段进行,通过自定义的mapper函数实现。例如,可以检查并处理缺失的家族关系...

    数据清洗研究综述

    数据清洗是信息处理技术中的一个重要环节,旨在提高数据质量,使之能够准确反映现实世界的情况,并有效地支持组织的日常运作和决策。随着各行各业积累了大量数据,数据质量问题日益凸显,包括错误、不合法值、空值、...

    python数据清洗

    ### Python 数据清洗知识点详解 #### 一、数据清洗的重要性及必要性 数据清洗在整个数据分析过程中扮演着极其重要的角色。在大数据时代,数据无处不在,然而,并非所有的数据都能直接拿来使用。通常情况下,原始...

    数据清洗规则模板.xlsx

    数据清洗规则

    yolo数据集的清洗工具.zip

    yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集...

    数据清洗研究.rar

    2. 数据清洗的基本步骤: - 缺失值处理:缺失值分析可以通过删除、填充平均值、中位数、众数或使用插补方法(如回归、KNN等)来解决。 - 错误值检测:检查异常值和离群点,可使用统计方法(如Z-score、IQR)或业务...

    Excel数据清洗工具

    Excel数据清洗工具是一款专为Excel文件设计的数据处理软件。它可以有效地帮助用户完成一系列的数据清洗任务,如删除重复值、处理缺失值、格式化数据等。无论是单个文件还是整个文件夹中的多个文件,这款工具都能够...

    数据治理中数据清洗步骤及最佳实践.pdf

    本文将详细探讨数据清洗的五个关键步骤以及在数据清洗过程中应遵循的最佳实践。 首先,数据清洗是指从数据库或数据表中识别、更正和删除不准确、不完整或不相关的数据记录的过程。数据清洗的目的是确保数据集的一致...

    17-数据清洗-清洗电商评论数据1

    数据清洗在IT行业中是数据预处理的关键步骤,尤其在电商数据分析中,确保数据的质量和准确性至关重要。本节主要探讨如何清洗电商评论数据,涉及到的主要知识点包括数据清洗的定义、Json数据解析以及爬虫获取的数据...

    Web 数据清洗研究

    【Web 数据清洗研究】 随着互联网的快速发展,Web 数据已经成为数据获取的重要来源。然而,Web 数据的质量问题不容忽视,因为它们往往包含大量“脏数据”,如滥用缩写、数据输入错误、重复记录、丢失值等,这直接...

    数据清洗指南.pdf

    文档中介绍了一系列具体的数据清洗步骤,如通过缺失数据热图可视化缺失值、使用Pandas库来分析数据集的结构、使用Pandas提供的数据清洗功能进行数据处理等。这些步骤都是分步指南的一部分,帮助读者逐步掌握数据清洗...

    ETL中的数据清洗设计

    (2)数据清洗的原理:数据清洗的目的是保证数据仓库数据质量。数据质量问题可以分为四类:单数据源模式层问题、单数据源实例层问题、多数据源模式层问题和多数据源实例层问题。 (3)数据清洗的具体实现过程:包括...

    Python pandas 数据清洗 基础教程

    Python pandas 数据清洗基础教程 Python pandas 是一个非常流行的数据处理库,它提供了许多强大的功能来处理和分析数据。在数据处理过程中,数据清洗是一个非常重要的步骤,它可以帮助我们将原始数据转换为可以用于...

Global site tag (gtag.js) - Google Analytics