合并清洗

CREATE OR REPLACE PACKAGE BODY NETS_TCIMS_PC_SHIELD IS

  SPLIT_CHR   VARCHAR2(1) := ',';

/*
   屏蔽代码要使用  基表查询
   BDL_RULE_REF_SHIELD
*/ 
  /***********************************************************
  --功能说明:   合并清洗 供 核保限制车型屏蔽 使用(入库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  EX-LIUJIALI001
  --注意事项：  顺序在所有清洗功能完成之后   PC_ENTER_RESULT_STG_0_TMP
  --*********************************************************/
  PROCEDURE SP_UNITE_FOR_SHIELD_CAR_MODEL
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_PREPARE_1_STG_TMP');
   
    -- 合并清洗后的数据
    INSERT /*+APPEND*/
      INTO PC_SHIELD_PREPARE_1_STG_TMP
           (
            SYS_ID,
            AUTOMODEL_NAME,
            VEHICLE_TYPE,
            TON_NUMBER,
            SEAT_NUMBER,
            CITY,
            SECONDARY_ORG
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
           A.SYS_ID,
           B.AUTOMODEL_NAME,
           --C.VEHICLE_TYPE  VEHICLE_CLASS,  -- 字段备用，待确认字段
           C.VEHICLE_TYPE_CODE,
           D.TON_NUMBER,
           D.SEAT_NUMBER,
           A.CITY,
           A.SECONDARY_ORG
      FROM PC_ENTER_RESULT_STG_0_TMP       A,  -- 清除了 非法 city 和 重复记录
           --IDL_SEP_SRC_DATA                A,
           (SELECT SYS_ID,AUTOMODEL_NAME,PURCHASE_PRICE,FACTORY_LOGO,
                   VEHICLE_SERIES,VEHICLE_CLASS_CODE,IS_PRICE_VALID
             FROM PC_CLEAN_CAR_NAME_PRIC_STG_TMP
           WHERE CLEAN_STATUS = '1')  B,  --车型名称
           (SELECT SYS_ID,VEHICLE_TYPE_CODE,VEHICLE_TYPE
             FROM PC_CLEAN_VEHIC_TYPE_CD_STG_TMP
           WHERE CLEAN_STATUS = '1')  C,  --车辆种类
           PC_CLEAN_SOME_NUMBER_STG_TMP    D   --数字
           --PC_COMPARE_REPEAT_STG_TMP       E    -- 重复记录
      WHERE A.SYS_ID = B.SYS_ID(+)
        AND A.SYS_ID = C.SYS_ID(+)
        AND A.SYS_ID = D.SYS_ID(+);
     COMMIT;
   
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,56,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   抽取准备数据 供 核保限制车型屏蔽 使用(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  EX-LIUJIALI001
  --注意事项：  顺序 名单准备 完成之后
  --*********************************************************/
  PROCEDURE SP_FETCH_FOR_SHIELD_CAR_MODEL
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_PREPARE_1_STG_TMP');
       
    -- 合并清洗后的数据
    INSERT /*+APPEND*/
      INTO PC_SHIELD_PREPARE_1_STG_TMP
           (
            SYS_ID,
            AUTOMODEL_NAME,
            VEHICLE_TYPE,
            TON_NUMBER,
            SEAT_NUMBER,
            CITY,
            SECONDARY_ORG
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
           A.USE_SYS_ID SYS_ID,
           A.AUTOMODEL_NAME,          
           A.VEHICLE_TYPE_CODE,
           A.TON_NUMBER,
           A.SEAT_NUMBER,
           A.CITY,
           A.SECONDARY_ORG
      FROM DML_SEP_USE_CUST_INFO  A,
           DML_SEP_USE_CUST_BATCH B,
           IDL_SEP_EP_BATCH       C
      WHERE A.BATCH_ID = B.BATCH_ID
        AND (B.BATCH_STATUS = '26'
         OR (B.BATCH_ID = C.BATCH_NO
             AND B.BATCH_STATUS = '27'
             AND C.BATCH_STATUS = '28'));
     COMMIT;
   
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,85,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   屏蔽核保限制车型(出入屏蔽可重用)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  入库时 必须 在  SP_UNITE_FOR_SHIELD_CAR_MODEL 运行完毕后
                出库时          SP_FETCH_FOR_SHIELD_CAR_MODEL
  --*********************************************************/
  PROCEDURE SP_SHIELD_RESTRICT_CAR_MODEL
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

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

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

    --标识屏蔽结果   数值比较要判断，  比较条件要改改
    INSERT /*+APPEND*/
      INTO PC_SHIELD_CAR_MODEL_STG_TMP
           ( SYS_ID, SHIELD_FLAG )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             'B' SHIELD_FLAG
        FROM PC_SHIELD_PREPARE_1_STG_TMP A,
             BDL_RULE_REF_SHIELD_CAR_MODEL B
        WHERE ((A.AUTOMODEL_NAME LIKE B.AUTOMODEL_NAME) OR B.AUTOMODEL_NAME IS NULL)
          AND ((A.VEHICLE_TYPE LIKE B.VEHICLE_TYPE) OR B.VEHICLE_TYPE IS NULL)
          AND (INSTR(TON_NUMBER_SIGN,DECODE(SIGN(A.TON_NUMBER - B.TON_NUMBER),'1','>','0','=','-1','<'))>0
                OR B.TON_NUMBER IS NULL)
          AND (INSTR(SEAT_NUMBER_SIGN ,DECODE(SIGN(A.SEAT_NUMBER - B.SEAT_NUMBER),'1','>','0','=','-1','<'))>0
                OR B.SEAT_NUMBER IS NULL)
          AND ((A.CITY LIKE B.CITY) OR B.CITY IS NULL)
          AND ((A.SECONDARY_ORG LIKE B.SECONDARY_ORG) OR B.SECONDARY_ORG IS NULL);
    COMMIT;

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,61,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_RESTRICT_CAR_MODEL;

  /***********************************************************
  --功能说明:   合并清洗 供 出租车 屏蔽 使用(入库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  EX-LIUJIALI001
  --注意事项：  顺序在所有清洗功能完成之后 PC_ENTER_RESULT_STG_0_TMP
  --*********************************************************/
  PROCEDURE SP_UNITE_FOR_SHIELD_TAXI
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_PREPARE_2_STG_TMP');
   
    -- 合并清洗后的数据
    INSERT /*+APPEND*/
      INTO PC_SHIELD_PREPARE_2_STG_TMP
           (
            SYS_ID,
            VEHICLE_NO,
            CUST_NAME,
            AUTOMODEL_NAME
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
           A.SYS_ID,
           B.VEHICLE_NO,
           C.CUST_NAME ,
           D.AUTOMODEL_NAME
      FROM PC_ENTER_RESULT_STG_0_TMP       A,  -- 清除了非法 city -- 重复记录
           --IDL_SEP_SRC_DATA                A,
           (SELECT SYS_ID, VEHICLE_NO ,CITY ,VEHICLE_NO_INTEGRITY
             FROM PC_CLEAN_VEHICLE_NO_STG_TMP  
           WHERE CLEAN_STATUS = '1')  B, 
           (SELECT SYS_ID,CUST_NAME, ORG_FLAG
             FROM PC_CLEAN_CUST_NAME_STG_TMP 
           WHERE CLEAN_STATUS = '1')    C,
           (SELECT SYS_ID ,AUTOMODEL_NAME ,PURCHASE_PRICE ,FACTORY_LOGO,
                   VEHICLE_SERIES ,VEHICLE_CLASS_CODE ,IS_PRICE_VALID
             FROM PC_CLEAN_CAR_NAME_PRIC_STG_TMP
             WHERE CLEAN_STATUS = '1')  D
           --PC_COMPARE_REPEAT_STG_TMP       E    -- 重复记录
      WHERE A.SYS_ID = B.SYS_ID(+)
        AND A.SYS_ID = C.SYS_ID(+)
        AND A.SYS_ID = D.SYS_ID(+);
     COMMIT; 
    
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,57,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   抽取准备数据 供 出租车 屏蔽 使用(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  EX-LIUJIALI001
  --注意事项：  顺序在 名单准备 完成之后
  --*********************************************************/
  PROCEDURE SP_FETCH_FOR_SHIELD_TAXI
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_PREPARE_2_STG_TMP');
   
    -- 合并清洗后的数据
    INSERT /*+APPEND*/
      INTO PC_SHIELD_PREPARE_2_STG_TMP
           (
            SYS_ID,
            VEHICLE_NO,
            CUST_NAME,
            AUTOMODEL_NAME
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
           A.USE_SYS_ID SYS_ID,
           A.VEHICLE_NO,
           A.CUST_NAME ,
           A.AUTOMODEL_NAME
      FROM DML_SEP_USE_CUST_INFO  A,
           DML_SEP_USE_CUST_BATCH B,
           IDL_SEP_EP_BATCH       C
      WHERE A.BATCH_ID = B.BATCH_ID
        AND (B.BATCH_STATUS = '26'
         OR (B.BATCH_ID = C.BATCH_NO
             AND B.BATCH_STATUS = '27'
             AND C.BATCH_STATUS = '28'));
     COMMIT; 
    
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,86,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,86,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
  END SP_FETCH_FOR_SHIELD_TAXI; 
 
  /***********************************************************
  --功能说明:   屏蔽出租车(出入屏蔽可重用)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  入库时 必须 在  SP_UNITE_FOR_SHIELD_TAXI 运行完毕后
                出库时          SP_FETCH_FOR_SHIELD_TAXI
  --*********************************************************/
  PROCEDURE SP_SHIELD_TAXI
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

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

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

    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_TAXI_STG_TMP
           (SYS_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             'C' SHIELD_FLAG
        FROM PC_SHIELD_PREPARE_2_STG_TMP A,
             BDL_RULE_REF_SHIELD_TAXI    B
        WHERE ((A.VEHICLE_NO LIKE B.VEHICLE_NO) OR B.VEHICLE_NO IS NULL)
          AND ((A.CUST_NAME LIKE B.CUST_NAME) OR B.CUST_NAME IS NULL)
          AND ((A.AUTOMODEL_NAME LIKE B.AUTOMODEL_NAME) OR B.AUTOMODEL_NAME IS NULL);
    COMMIT;

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,62,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_TAXI;

  /***********************************************************
  --功能说明:   合并清洗 供 无法送单地区 屏蔽 使用(入库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  EX-LIUJIALI001
  --注意事项：  顺序在所有清洗功能完成之后 PC_ENTER_RESULT_STG_0_TMP
  --*********************************************************/
  PROCEDURE SP_UNITE_FOR_SHIELD_UNABL_AREA
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_PREPARE_3_STG_TMP');
   
    -- 合并清洗后的数据
    INSERT /*+APPEND*/
      INTO PC_SHIELD_PREPARE_3_STG_TMP
           (SYS_ID,AREA_INFO,CITY,SECONDARY_ORG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           B.CRM_AREA_FLAG AREA_INFO,
           A.CITY,
           A.SECONDARY_ORG
           --A.PROVINCE
      FROM PC_ENTER_RESULT_STG_0_TMP       A,  -- 清除了非法 city -- 重复记录
           --IDL_SEP_SRC_DATA                A,
           PC_CLEAN_CRM_AREA_FLAG_STG_TMP  B  --地区标识
           --PC_COMPARE_REPEAT_STG_TMP       E    -- 重复记录
      WHERE A.SYS_ID = B.SYS_ID(+);
     COMMIT;
        
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,58,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   抽取准备数据 供 无法送单地区 屏蔽 使用(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  EX-LIUJIALI001
  --注意事项：  顺序在 名单准备 完成之后
  --*********************************************************/
  PROCEDURE SP_FETCH_FOR_SHIELD_UNABL_AREA
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_PREPARE_3_STG_TMP');
   
    -- 合并清洗后的数据
    INSERT /*+APPEND*/
      INTO PC_SHIELD_PREPARE_3_STG_TMP
           (SYS_ID,AREA_INFO,CITY,SECONDARY_ORG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
           A.USE_SYS_ID SYS_ID,
           A.AREA_INFO,
           A.CITY,
           A.SECONDARY_ORG
           --A.PROVINCE             匹配时不要使用省份字段
      FROM DML_SEP_USE_CUST_INFO A,
           DML_SEP_USE_CUST_BATCH B,
           IDL_SEP_EP_BATCH       C
      WHERE A.BATCH_ID = B.BATCH_ID
        AND (B.BATCH_STATUS = '26'
         OR (B.BATCH_ID = C.BATCH_NO
             AND B.BATCH_STATUS = '27'
             AND C.BATCH_STATUS = '28'));
     COMMIT;
        
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,87,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   屏蔽无法送单地区(出入屏蔽可重用)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  入库时 必须 在  SP_UNITE_FOR_SHIELD_UNABL_AREA 运行完毕后
                出库时          SP_FETCH_FOR_SHIELD_UNABL_AREA
  --*********************************************************/
  PROCEDURE SP_SHIELD_UNABLE_AREA
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

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

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

    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_UNABLE_AREA_STG_TMP
           (SYS_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             'D' SHIELD_FLAG
        FROM PC_SHIELD_PREPARE_3_STG_TMP       A,
             BDL_RULE_REF_SHIELD_UNABL_AREA    B
        WHERE ((A.AREA_INFO LIKE B.AREA_INFO) OR B.AREA_INFO IS NULL)
          AND ((A.CITY LIKE B.CITY) OR B.CITY IS NULL)
          AND ((A.SECONDARY_ORG LIKE B.SECONDARY_ORG) OR B.SECONDARY_ORG IS NULL);
          --AND ((A.PROVINCE LIKE B.PROVINCE) OR A.PROVINCE IS NULL);
    COMMIT;

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,63,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_UNABLE_AREA;

  /***********************************************************
  --功能说明:   合并清洗 供 无法承保车 屏蔽 使用
  --参数说明:
  --调用函数:
  --修改记录：  EX-LIUJIALI001
  --注意事项：  顺序在所有清洗功能完成之后 PC_ENTER_RESULT_STG_0_TMP
  --*********************************************************/
  PROCEDURE SP_UNITE_FOR_SHIELD_UNINSURABL
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_PREPARE_4_STG_TMP');
   
    -- 合并清洗后的数据
    INSERT /*+APPEND*/
      INTO PC_SHIELD_PREPARE_4_STG_TMP
           (
              SYS_ID,
              CITY,
              VT_FACTORY,
              VEHICLE_STATUS,
              BRAND_TYPE_CODE,
              CUST_NAME,
              VEHICLE_NO,
              AUTOMODEL_NAME,
              VEHICLE_TYPE,
              USAGE_CODE
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2) PARALLEL(G 2)*/
           A.SYS_ID,
           A.CITY,
           A.VT_FACTORY,
           A.VEHICLE_STATUS,
           B.BRAND_TYPE_CODE,
           C.CUST_NAME,
           D.VEHICLE_NO,
           E.AUTOMODEL_NAME,
           F.VEHICLE_TYPE,
           G.USAGE_CODE
      FROM PC_ENTER_RESULT_STG_0_TMP       A,  -- 清除了非法 city -- 重复记录
           --IDL_SEP_SRC_DATA                A,
           (SELECT SYS_ID, BRAND_TYPE_CODE
             FROM PC_CLEAN_BRAND_TYPE_CD_STG_TMP
           WHERE CLEAN_STATUS = '1') B,  --号牌种类代码
           (SELECT SYS_ID, CUST_NAME, ORG_FLAG
             FROM PC_CLEAN_CUST_NAME_STG_TMP  
           WHERE CLEAN_STATUS = '1')   C,  --客户姓名
           (SELECT SYS_ID, VEHICLE_NO, CITY, VEHICLE_NO_INTEGRITY
             FROM PC_CLEAN_VEHICLE_NO_STG_TMP 
           WHERE CLEAN_STATUS = '1')   D,  --车牌号码
           (SELECT SYS_ID, AUTOMODEL_NAME, PURCHASE_PRICE, FACTORY_LOGO,
                   VEHICLE_SERIES, VEHICLE_CLASS_CODE, IS_PRICE_VALID
             FROM PC_CLEAN_CAR_NAME_PRIC_STG_TMP
           WHERE CLEAN_STATUS = '1') E,  --车型名称
           (SELECT SYS_ID, VEHICLE_TYPE_CODE, VEHICLE_TYPE
             FROM PC_CLEAN_VEHIC_TYPE_CD_STG_TMP
           WHERE CLEAN_STATUS = '1') F,  --车辆种类
           (SELECT SYS_ID, USAGE_CODE, USAGE_ATTRIBUTE
             FROM PC_CLEAN_USAGE_CODE_STG_TMP
            WHERE CLEAN_STATUS = '1')    G   --使用性质
           --PC_COMPARE_REPEAT_STG_TMP       H    -- 重复记录
      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.SYS_ID = F.SYS_ID(+)
        AND A.SYS_ID = G.SYS_ID(+);
     COMMIT;
        
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,59,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   抽取准备数据 供 无法承保车 屏蔽 使用
  --参数说明:
  --调用函数:
  --修改记录：  EX-LIUJIALI001
  --注意事项：  顺序在 名单准备 完成之后
  --*********************************************************/
  PROCEDURE SP_FETCH_FOR_SHIELD_UNINSURABL
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_PREPARE_4_STG_TMP');
   
    -- 合并清洗后的数据
    INSERT /*+APPEND*/
      INTO PC_SHIELD_PREPARE_4_STG_TMP
           (
              SYS_ID,
              CITY,
              VT_FACTORY,
              VEHICLE_STATUS,
              BRAND_TYPE_CODE,
              CUST_NAME,
              VEHICLE_NO,
              AUTOMODEL_NAME,
              VEHICLE_TYPE,
              USAGE_CODE
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
           A.USE_SYS_ID SYS_ID,
           A.CITY,
           A.VT_FACTORY,           -- 制造厂
           A.VEHICLE_STATUS,       -- 车辆状态
           A.BRAND_TYPE_CODE,
           A.CUST_NAME,
           A.VEHICLE_NO,
           A.AUTOMODEL_NAME,
           A.VEHICLE_TYPE_CODE,
           A.USAGE_CODE
      FROM DML_SEP_USE_CUST_INFO  A,
           DML_SEP_USE_CUST_BATCH B,
           IDL_SEP_EP_BATCH       C
      WHERE A.BATCH_ID = B.BATCH_ID
        AND (B.BATCH_STATUS = '26'
         OR (B.BATCH_ID = C.BATCH_NO
             AND B.BATCH_STATUS = '27'
             AND C.BATCH_STATUS = '28'));
     COMMIT;
        
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,88,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   屏蔽无法承保车(出入屏蔽可重用)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  入库时 必须 在  SP_UNITE_FOR_SHIELD_UNINSURABL 运行完毕后
                出库时          SP_FETCH_FOR_SHIELD_UNINSURABL
  --*********************************************************/
  PROCEDURE SP_SHIELD_UNINSURABL
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

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

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

    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_UNINSURABLE_STG_TMP
           (SYS_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             'I' SHIELD_FLAG
        FROM PC_SHIELD_PREPARE_4_STG_TMP       A,
             BDL_RULE_REF_SHIELD_UNINSURABL    B
        WHERE ((A.CITY = B.CITY) OR B.CITY IS NULL)
          AND ((A.BRAND_TYPE_CODE LIKE B.BRAND_TYPE_CODE) OR B.BRAND_TYPE_CODE IS NULL)
          AND ((A.VT_FACTORY LIKE B.VT_FACTORY) OR B.VT_FACTORY IS NULL)
          AND ((A.CUST_NAME LIKE B.CUST_NAME) OR B.CUST_NAME IS NULL)
          AND ((A.VEHICLE_NO LIKE B.VEHICLE_NO) OR B.VEHICLE_NO IS NULL)
          AND ((A.AUTOMODEL_NAME LIKE B.AUTOMODEL_NAME) OR B.AUTOMODEL_NAME IS NULL)
          AND ((A.AUTOMODEL_NAME NOT LIKE B.NOTLIKE_AUTOMODEL_NAME) OR B.NOTLIKE_AUTOMODEL_NAME IS NULL)
          AND ((A.AUTOMODEL_NAME NOT LIKE B.NOTLIKE_AUTOMODEL_NAME_1) OR B.NOTLIKE_AUTOMODEL_NAME_1 IS NULL)
          AND ((A.VEHICLE_TYPE LIKE B.VEHICLE_TYPE) OR B.VEHICLE_TYPE IS NULL)
          AND ((A.USAGE_CODE = B.USAGE_CODE) OR B.USAGE_CODE IS NULL)
          AND ((A.VEHICLE_STATUS LIKE B.VEHICLE_STATUS) OR B.VEHICLE_STATUS IS NULL);
    COMMIT;

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,64,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_UNINSURABL;

  /***********************************************************
  --功能说明:   屏蔽无效日期(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_SHIELD_INVALID_DATE
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

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

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

    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_INVALID_DATE_STG_TMP
           (TCIMS_CUST_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
             A.USE_SYS_ID TCIMS_CUST_ID,
             'E' SHIELD_FLAG
        FROM DML_SEP_USE_CUST_INFO  A,
             DML_SEP_USE_CUST_BATCH B,
             IDL_SEP_EP_BATCH       C
      WHERE A.FIRST_REGISTER_DATE IS NULL
        AND A.POLICY_END_DATE IS NULL
        AND A.BATCH_ID = B.BATCH_ID
        AND (B.BATCH_STATUS = '26'
         OR (B.BATCH_ID = C.BATCH_NO
             AND B.BATCH_STATUS = '27'
             AND C.BATCH_STATUS = '28'));
    COMMIT;

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,91,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_INVALID_DATE;

  /***********************************************************
  --功能说明:   屏蔽代理电话/投诉电话、领导电话。。。前数据提取\整合(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须 在  数据准备完 后
  --*********************************************************/
  PROCEDURE SP_UNITE_FOR_SHIELD_PHONE
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
    --v_split_chr varchar2(1) := ',';
    v_phone_split_chr varchar2(1) := '-';
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,92,NULL,'01',NULL,NULL,NULL,NULL);

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

    --抽取数据
    INSERT /*+APPEND*/
      INTO PC_SHIELD_ALL_PHONE_1_TMP
           (
            TCIMS_CUST_ID,
            HOME_TEL,
            OFF_TEL,
            MOBILE_TEL,
            OTHER1_TEL,
            OTHER2_TEL,
            OTHER3_TEL,
            OTHER4_TEL,
            OTHER5_TEL,
            OTHER6_TEL,
            OTHER7_TEL,
            VEHICLE_NO
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2)*/
              A.USE_SYS_ID TCIMS_CUST_ID,             
              A.HOME_AREACODE||v_phone_split_chr||A.HOME_TEL HOME_TEL,
              A.OFF_AREACODE||v_phone_split_chr||A.OFF_TEL   OFF_TEL,
              A.MOBILE_TEL,
              A.OTHER1_AREACODE||v_phone_split_chr||A.OTHER1_TEL  OTHER1_TEL,
              A.OTHER2_AREACODE||v_phone_split_chr||A.OTHER2_TEL  OTHER2_TEL,
              A.OTHER3_AREACODE||v_phone_split_chr||A.OTHER3_TEL  OTHER3_TEL,
              A.OTHER4_AREACODE||v_phone_split_chr||A.OTHER4_TEL  OTHER4_TEL,
              A.OTHER5_AREACODE||v_phone_split_chr||A.OTHER5_TEL  OTHER5_TEL,
              A.OTHER6_AREACODE||v_phone_split_chr||A.OTHER6_TEL  OTHER6_TEL,
              A.OTHER7_AREACODE||v_phone_split_chr||A.OTHER7_TEL  OTHER7_TEL,
              A.VEHICLE_NO
        FROM DML_SEP_USE_CUST_INFO  A,
             DML_SEP_USE_CUST_BATCH B,
             IDL_SEP_EP_BATCH       C
       WHERE  A.BATCH_ID = B.BATCH_ID
        AND (B.BATCH_STATUS = '26'
         OR (B.BATCH_ID = C.BATCH_NO
             AND B.BATCH_STATUS = '27'
             AND C.BATCH_STATUS = '28'));
    COMMIT;
   
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,92,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,92,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_UNITE_FOR_SHIELD_PHONE;

  /***********************************************************
  --功能说明:   屏蔽代理电话(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须 在  SP_UNITE_FOR_SHIELD_PHONE 运行完毕后
  --*********************************************************/
  PROCEDURE SP_SHIELD_AGENCY_PHONE
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,93,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_AGENCY_PHONE_STG_TMP');
   
    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_AGENCY_PHONE_STG_TMP
           (TCIMS_CUST_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.TCIMS_CUST_ID,
             'F' SHIELD_FLAG
        FROM PC_SHIELD_ALL_PHONE_1_TMP   A,
             BDL_RULE_REF_SHIELD_PHONE   B
       WHERE ( A.HOME_TEL = B.PHONE
              OR A.OFF_TEL = B.PHONE
              OR A.MOBILE_TEL = B.PHONE
              OR A.OTHER1_TEL = B.PHONE
              OR A.OTHER2_TEL = B.PHONE
              OR A.OTHER3_TEL = B.PHONE
              OR A.OTHER4_TEL = B.PHONE
              OR A.OTHER5_TEL = B.PHONE
              OR A.OTHER6_TEL = B.PHONE
              OR A.OTHER7_TEL = B.PHONE )
         AND B.PHONE_TYPE = '1';
    COMMIT;  

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,93,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_AGENCY_PHONE;

  /***********************************************************
  --功能说明:   屏蔽投诉电话(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须 在  SP_UNITE_FOR_SHIELD_PHONE 运行完毕后
  --*********************************************************/
  PROCEDURE SP_SHIELD_CHARGE_PHONE
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,94,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_CHARGE_PHONE_STG_TMP');
   
    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_CHARGE_PHONE_STG_TMP
           (TCIMS_CUST_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.TCIMS_CUST_ID,
             'G' SHIELD_FLAG
        FROM PC_SHIELD_ALL_PHONE_1_TMP   A,
             BDL_RULE_REF_SHIELD_PHONE   B
       WHERE ( A.HOME_TEL = B.PHONE
              OR A.OFF_TEL = B.PHONE
              OR A.MOBILE_TEL = B.PHONE
              OR A.OTHER1_TEL = B.PHONE
              OR A.OTHER2_TEL = B.PHONE
              OR A.OTHER3_TEL = B.PHONE
              OR A.OTHER4_TEL = B.PHONE
              OR A.OTHER5_TEL = B.PHONE
              OR A.OTHER6_TEL = B.PHONE
              OR A.OTHER7_TEL = B.PHONE )
         AND B.PHONE_TYPE = '3';
    COMMIT;  

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,94,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_CHARGE_PHONE;

  /***********************************************************
  --功能说明:   屏蔽领导电话(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须 在  SP_UNITE_FOR_SHIELD_PHONE 运行完毕后
  --*********************************************************/
  PROCEDURE SP_SHIELD_LEADER_PHONE
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,95,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_LEADER_PHONE_STG_TMP');
   
    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_LEADER_PHONE_STG_TMP
           (TCIMS_CUST_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.TCIMS_CUST_ID,
             'K' SHIELD_FLAG
        FROM PC_SHIELD_ALL_PHONE_1_TMP   A,
             BDL_RULE_REF_SHIELD_PHONE B
       WHERE ( A.HOME_TEL = B.PHONE
              OR A.OFF_TEL = B.PHONE
              OR A.MOBILE_TEL = B.PHONE
              OR A.OTHER1_TEL = B.PHONE
              OR A.OTHER2_TEL = B.PHONE
              OR A.OTHER3_TEL = B.PHONE
              OR A.OTHER4_TEL = B.PHONE
              OR A.OTHER5_TEL = B.PHONE
              OR A.OTHER6_TEL = B.PHONE
              OR A.OTHER7_TEL = B.PHONE )
         AND B.PHONE_TYPE = '2';
    COMMIT;  

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,95,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_LEADER_PHONE;

  /***********************************************************
  --功能说明:   屏蔽车辆过长信息(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须 在  数据准备完 后 
  --*********************************************************/
  PROCEDURE SP_SHIELD_FIRST_REGISTER_DATE
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,96,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_FST_REG_DATE_STG_TMP');
   
    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_FST_REG_DATE_STG_TMP
           (TCIMS_CUST_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
             A.USE_SYS_ID TCIMS_CUST_ID,
             'H' SHIELD_FLAG
        FROM DML_SEP_USE_CUST_INFO          A,
             BDL_RULE_REF_SHIELD_FST_REG_DT B,
             DML_SEP_USE_CUST_BATCH         C,
             IDL_SEP_EP_BATCH       D
       WHERE A.FIRST_REGISTER_DATE < B.FIRST_REGISTER_DATE
         AND A.CITY = B.ORG
         AND A.BATCH_ID = C.BATCH_ID
         AND (C.BATCH_STATUS = '26'
         OR  (C.BATCH_ID = D.BATCH_NO
             AND C.BATCH_STATUS = '27'
             AND D.BATCH_STATUS = '28'));
    COMMIT;

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,96,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_FIRST_REGISTER_DATE;
 
  /***********************************************************
  --功能说明:   屏蔽领导姓名信息(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须 在  数据准备完 后 
  --*********************************************************/
  PROCEDURE SP_SHIELD_LEADER_NAME
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,97,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_LEADER_NAME_STG_TMP');
   
    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_LEADER_NAME_STG_TMP
           (TCIMS_CUST_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
             A.USE_SYS_ID TCIMS_CUST_ID,
             'M' SHIELD_FLAG
        FROM DML_SEP_USE_CUST_INFO          A,
             BDL_RULE_REF_SHIELD_LEADER     B,
             DML_SEP_USE_CUST_BATCH         C,
             IDL_SEP_EP_BATCH       D
       WHERE A.CUST_NAME = B.LEADER_NAME
         AND (A.SECONDARY_ORG = B.SECONDARY_ORG OR B.SECONDARY_ORG IS NULL)
         AND (A.CITY = B.CITY OR B.CITY IS NULL)        
         AND A.BATCH_ID = C.BATCH_ID
         AND (C.BATCH_STATUS = '26'
         OR  (C.BATCH_ID = D.BATCH_NO
             AND C.BATCH_STATUS = '27'
             AND D.BATCH_STATUS = '28'));
    COMMIT;  

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,97,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_LEADER_NAME; 

  /***********************************************************
  --功能说明:   屏蔽不良客户信息(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须 在  SP_UNITE_FOR_SHIELD_PHONE 运行完毕后 
  --*********************************************************/
  PROCEDURE SP_SHIELD_BADNESS_CUST
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
    --v_split_chr varchar2(1) := ',';
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,98,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_BADNESS_CUST_STG_TMP');
   
    --标识屏蔽结果
    INSERT /*+APPEND*/
      INTO PC_SHIELD_BADNESS_CUST_STG_TMP
           (TCIMS_CUST_ID,SHIELD_FLAG)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.TCIMS_CUST_ID,
             'T' SHIELD_FLAG
        FROM PC_SHIELD_ALL_PHONE_1_TMP        A,
             BDL_RULE_REF_SHIELD_BAD_CUST     B
       WHERE (A.VEHICLE_NO LIKE B.VEHICLE_NO AND B.TELEPHONE_NUMBER IS NULL)
          OR (( A.HOME_TEL LIKE B.TELEPHONE_NUMBER
                OR A.OFF_TEL LIKE B.TELEPHONE_NUMBER
                OR A.MOBILE_TEL LIKE B.TELEPHONE_NUMBER
                OR A.OTHER1_TEL LIKE B.TELEPHONE_NUMBER
                OR A.OTHER2_TEL LIKE B.TELEPHONE_NUMBER
                OR A.OTHER3_TEL LIKE B.TELEPHONE_NUMBER
                OR A.OTHER4_TEL LIKE B.TELEPHONE_NUMBER
                OR A.OTHER5_TEL LIKE B.TELEPHONE_NUMBER
                OR A.OTHER6_TEL LIKE B.TELEPHONE_NUMBER
                OR A.OTHER7_TEL LIKE B.TELEPHONE_NUMBER )
              AND ( B.VEHICLE_NO IS NULL OR A.VEHICLE_NO LIKE B.VEHICLE_NO)
              );
    COMMIT;  

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,98,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_BADNESS_CUST; 

  /***********************************************************
  --功能说明:   EP屏蔽数据准备(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  必须 在  数据准备完 后 
  --*********************************************************/
  PROCEDURE SP_SHIELD_PREPARE_FOR_EP
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
    v_cur_tmp   cur_type_tmp;
   
    CURSOR CUR_EP_BATCH(v_BATCH_STATUS VARCHAR2, v_IS_EP VARCHAR2) IS
     SELECT A.BATCH_ID,
            A.BATCH_NAME,
            A.CMP_NAME,
            A.CMP_DATE,
            A.BATCH_TOTAL_NUM,
            A.BATCH_STATUS,
            A.BATCH_PRIORITY,
            A.LIST_TYPE,
            A.DATA_SOURCE,
            A.CREATED_DATE,
            A.CREATED_BY,
            A.UPDATED_DATE,
            A.UPDATED_BY
       FROM DML_SEP_USE_CUST_BATCH  A,
            BDL_COM_DATA_SOURCE     B
      WHERE A.BATCH_STATUS = v_BATCH_STATUS
        AND A.DATA_SOURCE = B.DATA_SOURCE_CODE
        AND B.IS_EP = v_IS_EP;
    
    v_BATCH_ID_tmp  VARCHAR2(20);     -- 批次id截取临时变量
       
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,90,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    --NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','');
   
    OPEN CUR_EP_BATCH('26','01');
    LOOP
        FETCH CUR_EP_BATCH BULK COLLECT INTO v_cur_tmp LIMIT 100;
          EXIT WHEN v_cur_tmp.COUNT = 0;
          FOR i IN v_cur_tmp.FIRST .. v_cur_tmp.LAST  LOOP
         
          IF LENGTH(v_cur_tmp(i).BATCH_ID) > 20 THEN
             v_BATCH_ID_tmp := SUBSTR(v_cur_tmp(i).BATCH_ID,-20);
          ELSE
             v_BATCH_ID_tmp := v_cur_tmp(i).BATCH_ID;
          END IF;
         
          -- 新增交互批次状态
          INSERT /*+APPEND*/
            INTO IDL_SEP_EP_BATCH
           (BATCH_NO     ,
            BATCH_NAME   ,
            CITY_ID      ,
            CREATED_DATE ,
            CREATED_BY   ,
            UPDATED_DATE ,
            UPDATED_BY   ,
            BATCH_STATUS )
            VALUES
            (v_BATCH_ID_tmp,
             v_cur_tmp(i).BATCH_NAME ,
             NULL,
             SYSDATE,
             'SYSTEM',
             SYSDATE,
             'SYSTEM',
             '28');
         
          --修改准备批次状态
          UPDATE DML_SEP_USE_CUST_BATCH
             SET batch_status = '27'
           WHERE batch_id = v_cur_tmp(i).BATCH_ID;
                
          --抽取数据 更新跟EP交互的名单表
          INSERT /*+APPEND*/
            INTO IDL_SEP_EP_CUST
                 (
                  USE_SYS_ID,
                  TCIMS_CUST_ID,
                  BATCH_NO,
                  CITY_ID,
                  CUST_NAME,
                  VEHICLE_NO,
                  ENGINNO,
                  VEHICLEFRAME,
                  FIRSTREGISTERDATE,
                  VEHICLETYPE_NAME,
                  CUST_BIRD,
                  FIRSTSALEDATE,
                  FIRST_LICENSE_DATE,
                  POLICY_EFFECTIVE_DATE,
                  POLICY_END_DATE,
                  MAIN_DRIVER_DOB,
                  CREATED_DATE,
                  CREATED_BY,
                  UPDATED_DATE,
                  UPDATED_BY
                 )
           SELECT /*+PARALLEL(A 4)*/
                  A.USE_SYS_ID,
                  A.TCIMS_CUST_ID,
                  CASE
                    WHEN LENGTH(A.BATCH_ID)>20 THEN
                         SUBSTR(A.BATCH_ID,-20)
                    ELSE
                         A.BATCH_ID
                  END BATCH_NO,
                  A.CITY,
                  A.CUST_NAME,
                  A.VEHICLE_NO,
                  A.ENGINE_NUMBER,
                  A.VEHICLE_FRAME,
                  A.FIRST_REGISTER_DATE,
                  A.AUTOMODEL_NAME,
                  A.CUST_DOB,
                  A.BRAND_TYPE_FIRST_SALE_DATE,
                  A.DRIVER_LICENSE_FST_ISSUE_DATE,
                  A.POLICY_EFFECTIVE_DATE,
                  A.POLICY_END_DATE,
                  A.MAIN_DRIVER_DOB,
                  SYSDATE,
                  'SYSTEM',
                  SYSDATE,
                  'SYSTEM'
             FROM DML_SEP_USE_CUST_INFO A
            WHERE A.BATCH_ID = v_cur_tmp(i).BATCH_ID; 
           COMMIT;            
        END LOOP;
    END LOOP;   

    CLOSE CUR_EP_BATCH;

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

  EXCEPTION
     WHEN OTHERS THEN
        IF CUR_EP_BATCH%ISOPEN THEN
           CLOSE CUR_EP_BATCH;
        END IF;
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,90,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_SHIELD_PREPARE_FOR_EP;

  /***********************************************************
  --功能说明:   组合屏蔽结果(入库屏蔽) 更新准备入库名单表
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  须在<SP_SHIELD_RESTRICT_CAR_MODEL, SP_SHIELD_TAXI,
                     SP_SHIELD_UNABLE_AREA,        SP_SHIELD_UNINSURABL>完成后进行
  --*********************************************************/
  PROCEDURE SP_UNITE_ALL_IN_SHIELD_RESULT
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,68,NULL,'01',NULL,NULL,NULL,NULL);

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

    -- 整合所有屏蔽标识
    INSERT /*+APPEND*/
      INTO PC_IN_SHIELD_UNITE_STG_TMP
           (SYS_ID,SHIELD_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2)*/
           A.SYS_ID,
           NVL2(B.SHIELD_FLAG,B.SHIELD_FLAG||SPLIT_CHR,'')||
           NVL2(C.SHIELD_FLAG,C.SHIELD_FLAG||SPLIT_CHR,'')||
           NVL2(D.SHIELD_FLAG,D.SHIELD_FLAG||SPLIT_CHR,'')||
           NVL2(E.SHIELD_FLAG,D.SHIELD_FLAG||SPLIT_CHR,'') SHIELD_FLAG
      FROM  PC_ENTER_RESULT_STG_0_TMP                A,
            (SELECT SYS_ID,
                    MAX(SHIELD_FLAG) SHIELD_FLAG
               FROM PC_SHIELD_CAR_MODEL_STG_TMP
                    GROUP BY SYS_ID)                 B,
            (SELECT SYS_ID,
                    MAX(SHIELD_FLAG) SHIELD_FLAG
             FROM PC_SHIELD_TAXI_STG_TMP
                  GROUP BY SYS_ID)                   C,
            (SELECT SYS_ID,
                    MAX(SHIELD_FLAG) SHIELD_FLAG
               FROM PC_SHIELD_UNABLE_AREA_STG_TMP
                  GROUP BY SYS_ID)                   D,
            (SELECT SYS_ID,
                    MAX(SHIELD_FLAG) SHIELD_FLAG
             FROM PC_SHIELD_UNINSURABLE_STG_TMP
                  GROUP BY SYS_ID)                   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(+);
    COMMIT;

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,68,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_UNITE_ALL_IN_SHIELD_RESULT;

/*
  组合屏蔽结果
  IDL_SEP_EP_CUST_SCR
  所有内部屏蔽，
 
  --  ep 的屏蔽记录 第二天去取，根据 批次表中的 数据来源  (IDL_SEP_EP_CUST_SCR)
  EP屏蔽数据提取与内部屏蔽同时进行
*/
  /***********************************************************
  --功能说明:   组合屏蔽结果(出库屏蔽) 更新准备名单表
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  须在<SP_UNITE_SHIELD_1,SP_UNITE_SHIELD_2,
                     SP_UNITE_SHIELD_3,SP_UNITE_SHIELD_4>完成后进行
  --*********************************************************/
  PROCEDURE SP_UNITE_ALL_SHIELD_RESULT
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,104,NULL,'01',NULL,NULL,NULL,NULL);

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

    -- 整合所有屏蔽标识
    INSERT /*+APPEND*/
      INTO PC_SHIELD_UNITE_STG_TMP
           (TCIMS_CUST_ID,SHIELD_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2)*/
           A.USE_SYS_ID TCIMS_CUST_ID,
           B.SHIELD_FLAG||C.SHIELD_FLAG||D.SHIELD_FLAG||E.SHIELD_FLAG  SHIELD_FLAG
      FROM  DML_SEP_USE_CUST_INFO          A,
            PC_SHIELD_UNITE_STG_1_TMP      B,
            PC_SHIELD_UNITE_STG_2_TMP      C,
            PC_SHIELD_UNITE_STG_3_TMP      D,
            PC_SHIELD_UNITE_STG_4_TMP      E,
            DML_SEP_USE_CUST_BATCH         F
     WHERE A.USE_SYS_ID = B.TCIMS_CUST_ID(+)
       AND A.USE_SYS_ID = C.TCIMS_CUST_ID(+)
       AND A.USE_SYS_ID = D.TCIMS_CUST_ID(+)
       AND A.USE_SYS_ID = E.TCIMS_CUST_ID(+)
       AND A.BATCH_ID = F.BATCH_ID
       AND (F.BATCH_STATUS = '26' OR F.BATCH_STATUS = '27');
    COMMIT;

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

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := SUBSTR(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,104,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_UNITE_ALL_SHIELD_RESULT;

  /***********************************************************
  --功能说明:   组合屏蔽结果1(出库屏蔽)
  --参数说明:
  --调用函数:
  --修改记录：  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项：  须在所有出库屏蔽完成后进行
  --*********************************************************/
  PROCEDURE SP_UNITE_SHIELD_1
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
   
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,100,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_SHIELD_UNITE_STG_1_TMP');
   
    -- 先整合几个屏蔽标识
    INSERT /*+APPEND*/
      INTO PC_SHIELD_UNITE_STG_1_TMP
           (TCIMS_CUST_ID ,SHIELD_FLAG )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2)*/
           A.USE_SYS_ID TCIMS_CUST_ID,
           NVL2(B.SHIELD_FLAG,B.SHIELD_FLAG||SPLIT_CHR,'')||
           NVL2(C.SHIELD_FLAG,C.SHIELD_FLAG||SPLIT_CHR,'')||
           NVL2(D.SHIELD_FLAG,D.SHIELD_FLAG||SPLIT_CHR,'') SHIELD_FLAG
      FROM  DML_SEP_USE_CUST_INFO          A,
            (SELECT SYS_ID,
                    MAX(SHIELD_FLAG) SHIELD_FLAG
             FROM PC_SHIELD_CAR_MODEL_STG_TMP