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

数据整合_SP

阅读更多

CREATE OR REPLACE PACKAGE BODY NETS_TCIMS_BACK_CLEAN_DATA
IS

  SPLIT_CHR   VARCHAR2(1) := ',';

  /***********************************************************
  --功能说明:   整合需手工清洗数据前准备1
  --参数说明:
  --调用函数:
  --修改记录:  EX-LIUJIALI001
  --注意事项:  顺序在所有清洗功能完成之后
  --*********************************************************/
  PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_1
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_1_STG_TMP');
   
    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_1_STG_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'A'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'B'||SPLIT_CHR,'')||
           NVL2(D.SYS_ID,'C'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                A,
           PC_CLEAN_CITY_STG_TMP           B,
           (SELECT SYS_ID, VEHICLE_NO, CITY,
                   VEHICLE_NO_INTEGRITY ,CLEAN_STATUS
             FROM PC_CLEAN_VEHICLE_NO_STG_TMP 
           WHERE CLEAN_STATUS = '0')       C,
           (SELECT DISTINCT SYS_ID           --, TEL_NO, CITY,CLEAN_STATUS
              FROM PC_CLEAN_TEL_NO_STG_TMP   --PC_CLEAN_TELEPHONE_STG_TMP  
           WHERE CLEAN_STATUS = '0')       D,    -- 返回手工清洗时使用
           --PC_CLEAN_TEL_NO_STG_TMP         D
           IDL_EX_BATCH                     F
     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 = 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,41,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,41,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
  END SP_UNITE_PC_PRE_BACK_DATA_1;
 
  /***********************************************************
  --功能说明:   整合需手工清洗数据前准备2
  --参数说明:
  --调用函数:
  --修改记录:  EX-LIUJIALI001
  --注意事项:  顺序在所有清洗功能完成之后
  --*********************************************************/
  PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_2
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_2_STG_TMP');
   
    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_2_STG_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'D'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'E'||SPLIT_CHR,'')||
           NVL2(D.SYS_ID,'F'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                A,
          ( SELECT SYS_ID ,BRAND_TYPE_CODE, CLEAN_STATUS
             FROM PC_CLEAN_BRAND_TYPE_CD_STG_TMP
           WHERE CLEAN_STATUS = '0') B,
          ( SELECT SYS_ID, USAGE_CODE, USAGE_ATTRIBUTE, CLEAN_STATUS
             FROM PC_CLEAN_USAGE_CODE_STG_TMP 
           WHERE CLEAN_STATUS = '0')   C,
          ( SELECT SYS_ID, VEHICLE_TYPE_CODE, VEHICLE_TYPE, CLEAN_STATUS
             FROM  PC_CLEAN_VEHIC_TYPE_CD_STG_TMP
            WHERE CLEAN_STATUS = '0')  D,
            IDL_EX_BATCH                    F
     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 = 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,42,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        P_ERRMSG := SUBSTR(SQLERRM,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,42,NULL,'03',P_ERRMSG,NULL,NULL,NULL);
        RAISE;
  END SP_UNITE_PC_PRE_BACK_DATA_2; 
 
  /***********************************************************
  --功能说明:   整合需手工清洗数据前准备3
  --参数说明:
  --调用函数:
  --修改记录:  EX-LIUJIALI001
  --注意事项:  顺序在所有清洗功能完成之后
  --*********************************************************/
  PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_3
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_3_STG_TMP');
   
    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_3_STG_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'G'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'H'||SPLIT_CHR,'')||
           NVL2(D.SYS_ID,'I'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                A,
           (SELECT SYS_ID ,FIRST_REGISTER_DATE ,CLEAN_STATUS, POLICY_END_DATE
               FROM PC_CLEAN_FST_REG_DATE_STG_TMP
           WHERE CLEAN_STATUS = '0')  B,
           (SELECT SYS_ID ,POLICY_END_DATE ,CLEAN_STATUS
               FROM PC_CLEAN_POLI_END_DATE_STG_TMP
           WHERE CLEAN_STATUS = '0') C,
           (SELECT SYS_ID ,POLICY_EFFECTIVE_DATE ,CLEAN_STATUS
               FROM PC_CLEAN_POLI_EFC_DATE_STG_TMP
             WHERE CLEAN_STATUS = '0') D,
             IDL_EX_BATCH                    F
     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 = 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,43,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   整合需手工清洗数据前准备4
  --参数说明:
  --调用函数:
  --修改记录:  EX-LIUJIALI001
  --注意事项:  顺序在所有清洗功能完成之后
  --*********************************************************/
  PROCEDURE SP_UNITE_PC_PRE_BACK_DATA_4
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEP_BACK_DATA_4_STG_TMP');
   
    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_4_STG_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(F 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'J'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'K'||SPLIT_CHR,'')||
           NVL2(D.SYS_ID,'L'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                           A,
           (SELECT SYS_ID, CUST_DOB, CLEAN_STATUS
              FROM PC_CLEAN_CUST_DOB_STG_TMP  K
             WHERE NOT EXISTS(
                SELECT 1 FROM PC_CLEAN_ID_NUMBER_STG_TMP E
                 WHERE K.SYS_ID = E.SYS_ID
                   AND E.CLEAN_STATUS = '1')
               AND CLEAN_STATUS = '0')                B,
           (SELECT SYS_ID, MAIN_DRIVER_DOB, CLEAN_STATUS
             FROM PC_CLEAN_MAIN_DRIV_DOB_STG_TMP
           WHERE CLEAN_STATUS = '0')                  C,
           (SELECT SYS_ID, DRIVER_LICENSE_FST_ISSUE_DATE, CLEAN_STATUS
             FROM PC_CLEAN_LIC_ISUE_DATE_STG_TMP
           WHERE CLEAN_STATUS = '0')                  D,
           IDL_EX_BATCH                               F
     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 = 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,44,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   整合需手工清洗数据(产险)
  --参数说明:
  --调用函数:
  --修改记录:  EX-LIUJIALI001
  --注意事项:  顺序在<整合需手工清洗数据前准备1,2,3,4>完成之后
  --*********************************************************/
  PROCEDURE SP_UNITE_PC_BACK_DATA
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表  无须清空数据
    --NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','');

    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEP_BACK_DATA_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2) PARALLEL(E 2) PARALLEL(F 2)*/
           A.SYS_ID,
           B.CLEAN_FLAG||C.CLEAN_FLAG||D.CLEAN_FLAG||E.CLEAN_FLAG CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA                A,
           IDL_SEP_BACK_DATA_1_STG_TMP     B,
           IDL_SEP_BACK_DATA_2_STG_TMP     C,
           IDL_SEP_BACK_DATA_3_STG_TMP     D,
           IDL_SEP_BACK_DATA_4_STG_TMP     E,
           IDL_EX_BATCH                    F
     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;
 
    --合并数据给sqlserver手工清洗, 提取需要的数据
    INSERT /*+APPEND*/
      INTO  IDL_SQL_SEP_CUST_RETURN  --IDL_SEP_BACK_DATA
            (
            SQL_SYS_ID,
            SYS_ID,                           
            TCIMS_BATCH_ID,                   
            TCIMS_CUST_ID,                    
            TCIMS_VT_ID,                  
            CITY,                             
            THIRD_ORG,                        
            SECONDARY_ORG,                    
            PROVINCE,                         
            AREA_INFO,                        
            SRC_TYPE,                         
            LIST_TYPE,                        
            CUST_CLASS,                       
            CUST_TYPE,                        
            APPLICANT_PARTY_NO,               
            CIF2_ID,                          
            CIF2_ID_TYPE,                     
            CUST_NAME,                        
            SEX,                              
            SALUTATION,                       
            MARITAL_STATUS,                   
            CUST_DOB,                         
            POSITION,                         
            ID_TYPE,                          
            ID_NUMBER,                        
            CONTACT_NAME,                     
            TELEPHONE_NUMBER,                 
            FAX_AREA_CODE,                    
            FAX_NUM,                          
            APPLICANT_EMAIL,                  
            ZIP_CODE,                         
            ADDRESS,                          
            REMARK,                           
            DRIVER_LICENSE_NO,                
            DRIVER_LICENSE_FST_ISSUE_DATE,    
            DRIVE_VEHICLE_TYPE_CODE,          
            VEHICLE_NO,                       
            BRAND_TYPE,                       
            BRAND_TYPE_CODE,                  
            USAGE_ATTRIBUTE,                  
            USAGE_CODE,                       
            ATTRIBUTE_CODE,                   
            VEHICLE_TYPE,                     
            VEHICLE_TYPE_CODE,                
            VEHICLE_CLASS_CODE,               
            VEHICLE_BODY_COLOR,               
            AUTOMODEL_NAME,                   
            VEHICLE_MODEL_NAME_CN,            
            VEHICLE_MODEL_NO,                 
            FACTORY_LOGO,                     
            VEHICLE_SERIES,                   
            PURCHASE_PRICE,                   
            BRAND_TYPE_FIRST_SALE_DATE,       
            SEAT_NUMBER,                      
            TON_NUMBER,                       
            FULL_CAPACITY,                    
            EXHAUST,                          
            FOREIGN_VEHICLE_NO,               
            ENGINE_NUMBER,                    
            VEHICLE_FRAME,                    
            DEV_CODE,                         
            REFIX_DESC,                       
            VEHICLE_VALUE,                    
            VEHICLE_REMARK,                   
            VEHICLE_GENERAL_TYPE_CODE,        
            VEHICLE_SPECIFIC_TYPE_CODE,       
            MANUFACTURE_COUNTRY,              
            VT_FACTORY,                       
            VEHICLE_STATUS,                   
            USED_YEARS,                       
            FIRST_REGISTER_DATE,              
            LEAVE_FACTORY_DATE,               
            BUY_VEHICLE_DATE,                 
            VEHICLE_RANK_CODE,                
            VEHICLE_OWNER,                    
            LICENSE_ISSUE_DATE,               
            MAIN_DRIVER_NO,                   
            MAIN_DRIVER_DOB,                  
            MAIN_DRIVER_SEX,                  
            MAIN_DRIVER_NAME,                 
            DRIVE_AREA_CODE,                  
            INSURED_PERSON_NAME,              
            INSURED_PERSON_ADDRESS,           
            C01_POLICY_NO,                    
            C01_DEPARTMENT_CODE,              
            C01_DEPARTMENT_CHINESE_NAME,      
            C01_LAST_POLICY_NO,               
            C01_LAST_YEAR_APPLY_COMPANY,      
            C01_VALUE_CHINESE_NAME,           
            C01_SALE_AGENT_CODE,              
            C01_EMPLOYEE_NAME,                
            C01_BUSINESS_SRC_CODE,            
            C01_BUSINESS_SRC_NAME,            
            C01_BUSINESS_SRC_DETAIL_CODE,     
            C01_BUSINESS_SRC_DETAIL_NAME,     
            C01_CHANNEL_SRC_CODE,             
            C01_CHANNEL_SRC_NAME,             
            C01_CHANNEL_SRC_DETAIL_CODE,      
            C01_CHANNEL_SRC_DETAIL_NAME,      
            C01_INSURANCE_BEGIN_TIME,         
            C01_INSURANCE_END_TIME,           
            C01_TOTAL_ACTUAL_PREMIUM,         
            C01_PREMIUM_INFO,                 
            C01_APPLY_TIME,                   
            C01_UNDERWRITE_TIME,              
            C01_INPUT_BY_ID,                  
            C01_INPUT_BY,                     
            C51_POLICY_NO,                    
            C51_DEPARTMENT_CODE,              
            C51_DEPARTMENT_CHINESE_NAME,      
            C51_LAST_POLICY_NO,               
            C51_LAST_YEAR_APPLY_COMPANY,      
            C51_VALUE_CHINESE_NAME,           
            C51_SALE_AGENT_CODE,              
            C51_EMPLOYEE_NAME,                
            C51_BUSINESS_SRC_CODE,            
            C51_BUSINESS_SRC_NAME,            
            C51_BUSINESS_SRC_DETAIL_CODE,     
            C51_BUSINESS_SRC_DETAIL_NAME,     
            C51_CHANNEL_SRC_CODE,             
            C51_CHANNEL_SRC_NAME,             
            C51_CHANNEL_SRC_DETAIL_CODE,      
            C51_CHANNEL_SRC_DETAIL_NAME,      
            C51_INSURANCE_BEGIN_TIME,         
            C51_INSURANCE_END_TIME,           
            C51_TOTAL_ACTUAL_PREMIUM,         
            C51_PREMIUM_INFO,                 
            C51_APPLY_TIME,                   
            C51_UNDERWRITE_TIME,              
            C51_INPUT_BY_ID,                  
            C51_INPUT_BY,                     
            VIOLATION_RATIO,                  
            CLAIM_RATIO,                      
            VIOLATION_PREMIUM_CHANGE,         
            INSURANCE_TYPE_FLAG,              
            POLICY_EFFECTIVE_DATE,            
            POLICY_END_DATE,                  
            PA_APPLY_HISTORY,                 
            PA_LIFE_CLIENT,                   
            BIZ_MODEL,                        
            SUB_BIZMODEL,                     
            EXPIRED_DATE,                     
            CAMPAIGN_ID,                      
            CAMPAIGN_NAME,                    
            GROUP_ID,                         
            CAMPAIGN_SPLIT_NAME,              
            BATCH_NAME,                       
            TASK_GROUP_ID,                    
            TASK_ID,                          
            IS_CONTACTED,                     
            C51_PHONE_RESULT,                 
            C51_SALE_STAGE,                   
            C51_SALE_DECISION,                
            C51_ADDED_EXPLAIN,                
            C01_PHONE_RESULT,                 
            C01_SALE_STAGE,                   
            C01_SALE_DECISION,                
            C01_ADDED_EXPLAIN,                
            C01_SALE_RESULT_CLASS,            
            C51_SALE_RESULT_CLASS,            
            SALE_RESULT_CLASS,                
            IS_AGENCY_PHONE,                  
            IS_ADDRESS_VALID,                 
            SHIELD_FLAG,                      
            RISK_TIMES,                       
            LIST_PRIORITY,                    
            CREATED_DATE,                     
            CREATED_BY,                       
            UPDATED_DATE,                     
            UPDATED_BY,                       
            INVALID_CLEAN_FLAG
            )
    SELECT  /*+PARALLEL(A 2) PARALLEL(B 2)  PARALLEL(F 2)*/
            A.SQL_SYS_ID,
            A.SYS_ID,
            A.TCIMS_BATCH_ID,
            A.TCIMS_CUST_ID,
            A.TCIMS_VT_ID,
            A.CITY,
            A.THIRD_ORG,
            A.SECONDARY_ORG,
            A.PROVINCE,
            A.AREA_INFO,
            A.SRC_TYPE,
            A.LIST_TYPE,
            A.CUST_CLASS,
            A.CUST_TYPE,
            A.APPLICANT_PARTY_NO,
            A.CIF2_ID,
            A.CIF2_ID_TYPE,
            A.CUST_NAME,
            A.SEX,
            A.SALUTATION,
            A.MARITAL_STATUS,
            A.CUST_DOB,
            A.POSITION,
            A.ID_TYPE,
            A.ID_NUMBER,
            A.CONTACT_NAME,
            A.TELEPHONE_NUMBER,
            A.FAX_AREA_CODE,
            A.FAX_NUM,
            A.APPLICANT_EMAIL,
            A.ZIP_CODE,
            A.ADDRESS,
            A.REMARK,
            A.DRIVER_LICENSE_NO,
            A.DRIVER_LICENSE_FST_ISSUE_DATE,
            A.DRIVE_VEHICLE_TYPE_CODE,
            A.VEHICLE_NO,
            A.BRAND_TYPE,
            A.BRAND_TYPE_CODE,
            A.USAGE_ATTRIBUTE,
            A.USAGE_CODE,
            A.ATTRIBUTE_CODE,
            A.VEHICLE_TYPE,
            A.VEHICLE_TYPE_CODE,
            A.VEHICLE_CLASS_CODE,
            A.VEHICLE_BODY_COLOR,
            A.AUTOMODEL_NAME,
            A.VEHICLE_MODEL_NAME_CN,
            A.VEHICLE_MODEL_NO,
            A.FACTORY_LOGO,
            A.VEHICLE_SERIES,
            A.PURCHASE_PRICE,
            A.BRAND_TYPE_FIRST_SALE_DATE,
            A.SEAT_NUMBER,
            A.TON_NUMBER,
            A.FULL_CAPACITY,
            A.EXHAUST,
            A.FOREIGN_VEHICLE_NO,
            A.ENGINE_NUMBER,
            A.VEHICLE_FRAME,
            A.DEV_CODE,
            A.REFIX_DESC,
            A.VEHICLE_VALUE,
            A.VEHICLE_REMARK,
            A.VEHICLE_GENERAL_TYPE_CODE,
            A.VEHICLE_SPECIFIC_TYPE_CODE,
            A.MANUFACTURE_COUNTRY,
            A.VT_FACTORY,
            A.VEHICLE_STATUS,
            A.USED_YEARS,
            A.FIRST_REGISTER_DATE,
            A.LEAVE_FACTORY_DATE,
            A.BUY_VEHICLE_DATE,
            A.VEHICLE_RANK_CODE,
            A.VEHICLE_OWNER,
            A.LICENSE_ISSUE_DATE,
            A.MAIN_DRIVER_NO,
            A.MAIN_DRIVER_DOB,
            A.MAIN_DRIVER_SEX,
            A.MAIN_DRIVER_NAME,
            A.DRIVE_AREA_CODE,
            A.INSURED_PERSON_NAME,
            A.INSURED_PERSON_ADDRESS,
            A.C01_POLICY_NO,
            A.C01_DEPARTMENT_CODE,
            A.C01_DEPARTMENT_CHINESE_NAME,
            A.C01_LAST_POLICY_NO,
            A.C01_LAST_YEAR_APPLY_COMPANY,
            A.C01_VALUE_CHINESE_NAME,
            A.C01_SALE_AGENT_CODE,
            A.C01_EMPLOYEE_NAME,
            A.C01_BUSINESS_SRC_CODE,
            A.C01_BUSINESS_SRC_NAME,
            A.C01_BUSINESS_SRC_DETAIL_CODE,
            A.C01_BUSINESS_SRC_DETAIL_NAME,
            A.C01_CHANNEL_SRC_CODE,
            A.C01_CHANNEL_SRC_NAME,
            A.C01_CHANNEL_SRC_DETAIL_CODE,
            A.C01_CHANNEL_SRC_DETAIL_NAME,
            A.C01_INSURANCE_BEGIN_TIME,
            A.C01_INSURANCE_END_TIME,
            A.C01_TOTAL_ACTUAL_PREMIUM,
            A.C01_PREMIUM_INFO,
            A.C01_APPLY_TIME,
            A.C01_UNDERWRITE_TIME,
            A.C01_INPUT_BY_ID,
            A.C01_INPUT_BY,
            A.C51_POLICY_NO,
            A.C51_DEPARTMENT_CODE,
            A.C51_DEPARTMENT_CHINESE_NAME,
            A.C51_LAST_POLICY_NO,
            A.C51_LAST_YEAR_APPLY_COMPANY,
            A.C51_VALUE_CHINESE_NAME,
            A.C51_SALE_AGENT_CODE,
            A.C51_EMPLOYEE_NAME,
            A.C51_BUSINESS_SRC_CODE,
            A.C51_BUSINESS_SRC_NAME,
            A.C51_BUSINESS_SRC_DETAIL_CODE,
            A.C51_BUSINESS_SRC_DETAIL_NAME,
            A.C51_CHANNEL_SRC_CODE,
            A.C51_CHANNEL_SRC_NAME,
            A.C51_CHANNEL_SRC_DETAIL_CODE,
            A.C51_CHANNEL_SRC_DETAIL_NAME,
            A.C51_INSURANCE_BEGIN_TIME,
            A.C51_INSURANCE_END_TIME,
            A.C51_TOTAL_ACTUAL_PREMIUM,
            A.C51_PREMIUM_INFO,
            A.C51_APPLY_TIME,
            A.C51_UNDERWRITE_TIME,
            A.C51_INPUT_BY_ID,
            A.C51_INPUT_BY,
            A.VIOLATION_RATIO,
            A.CLAIM_RATIO,
            A.VIOLATION_PREMIUM_CHANGE,
            A.INSURANCE_TYPE_FLAG,
            A.POLICY_EFFECTIVE_DATE,
            A.POLICY_END_DATE,
            A.PA_APPLY_HISTORY,
            A.PA_LIFE_CLIENT,
            A.BIZ_MODEL,
            A.SUB_BIZMODEL,
            A.EXPIRED_DATE,
            A.CAMPAIGN_ID,
            A.CAMPAIGN_NAME,
            A.GROUP_ID,
            A.CAMPAIGN_SPLIT_NAME,
            A.BATCH_NAME,
            A.TASK_GROUP_ID,
            A.TASK_ID,
            A.IS_CONTACTED,
            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,
            A.C01_SALE_RESULT_CLASS,
            A.C51_SALE_RESULT_CLASS,
            A.SALE_RESULT_CLASS,
            A.IS_AGENCY_PHONE,
            A.IS_ADDRESS_VALID,
            A.SHIELD_FLAG,
            A.RISK_TIMES,
            A.LIST_PRIORITY,
            A.CREATED_DATE,
            A.CREATED_BY,
            A.UPDATED_DATE,
            A.UPDATED_BY,
            B.CLEAN_FLAG
      FROM IDL_SEP_SRC_DATA       A,
           IDL_SEP_BACK_DATA_TMP  B,
           IDL_EX_BATCH           F
     WHERE A.SYS_ID = B.SYS_ID
       AND B.CLEAN_FLAG IS NOT NULL
       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,45,NULL,'02',NULL,NULL,NULL,NULL);

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

  /***********************************************************
  --功能说明:   整合需手工清洗数据(寿险)
  --参数说明:
  --调用函数:
  --修改记录:  EX-LIUJIALI001
  --注意事项:  顺序在寿险数据清洗完成之后
  --*********************************************************/
  PROCEDURE SP_UNITE_LA_BACK_DATA
  IS
    P_ID        NUMBER;         -- 日志记录ID
    P_ERRMSG    VARCHAR2(500);  -- 错误记录

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

    --清空结果临时表  无须清空数据
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','IDL_SEL_BACK_DATA_1_TMP');
   
    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SEL_BACK_DATA_1_TMP
           (SYS_ID,CLEAN_FLAG)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(C 2) PARALLEL(D 2)*/
           A.SYS_ID,
           NVL2(B.SYS_ID,'C'||SPLIT_CHR,'')||
           NVL2(C.SYS_ID,'A'||SPLIT_CHR,'') CLEAN_FLAG
      FROM IDL_SEL_SRC_DATA                A,
           (SELECT DISTINCT SYS_ID 
             FROM LA_CLEAN_TEL_NO_STG_TMP
             WHERE CLEAN_STATUS = '0')     B,
           LA_CLEAN_CITY_STG_TMP           C,
           IDL_EX_BATCH                    D
     WHERE A.SYS_ID = B.SYS_ID(+)
       AND A.SYS_ID = C.SYS_ID(+)
       AND A.TCIMS_BATCH_ID = D.TCIMS_BATCH_ID
       AND D.SERIES_TYPE = '02'
       AND D.BATCH_STATUS = '11'
       AND D.PREPARE_FLAG = '1';
    COMMIT;

    --合并数据给sqlserver手工清洗
    INSERT /*+APPEND*/
      INTO IDL_SQL_SEL_CUST_RETURN   --IDL_SEL_BACK_DATA
           (
            SQL_SYS_ID,
            SYS_ID,
            TCIMS_BATCH_ID,
            TCIMS_CUST_ID,
            CLIENT_NO,
            APPLICANT_PARTY_NO,
            SRC_TYPE,
            LIST_TYPE,
            LIST_PRIORITY,
            CUST_NAME,
            SEX,
            ID_TYPE,
            ID_NUMBER,
            CUST_DOB,
            WORK_UNIT,
            DEPARTMENT_CHINESE_NAME,
            LIST_CREATED_DATE,
            POSITION,
            POSITION_CODE,
            EDUCATIONAL_BACKGROUND,
            MARITAL_STATUS,
            PROF_GRADE,
            ANNUAL_INCOME,
            CONTACT_ADDRESS,
            CONTACT_ZIPCODE,
            ADDRESS,
            ZIP_CODE,
            EMAIL,
            SHIELD_FLAG,
            CAMPAIGN_NAME,
            CAMPAIGN_SPLIT_NAME,
            BATCH_NAME,
            SUPERVISOR_ID,
            TEAM_LEADER_ID,
            TMR_UM_ID,
            PRODUCT_NAME,
            CONTACT_DATE,
            CALL_RESULT,
            IS_INSURE_FLAG,
            IS_SUBMIT_FLAG,
            IS_FREE_FLAG,
            CALL_BACK_REMARK,
            SUCCESS_DATE,
            TRANSFER_SUCCESS_DAY,
            LIST_STATUS,
            REGION_CODE,
            CREDIT_CARD_CALL_DATE,
            SUBMIT_DATE,
            CREATED_DATE,
            CREATED_BY,
            UPDATED_DATE,
            UPDATED_BY,
            INVALID_CLEAN_FLAG
           )
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2) PARALLEL(F 2)*/
            A.SQL_SYS_ID,
            A.SYS_ID,
            A.TCIMS_BATCH_ID,
            A.TCIMS_CUST_ID,
            A.CLIENT_NO,
            A.APPLICANT_PARTY_NO,
            A.SRC_TYPE,
            A.LIST_TYPE,
            A.LIST_PRIORITY,
            A.CUST_NAME,
            A.SEX,
            A.ID_TYPE,
            A.ID_NUMBER,
            A.CUST_DOB,
            A.WORK_UNIT,
            A.DEPARTMENT_CHINESE_NAME,
            A.LIST_CREATED_DATE,
            A.POSITION,
            A.POSITION_CODE,
            A.EDUCATIONAL_BACKGROUND,
            A.MARITAL_STATUS,
            A.PROF_GRADE,
            A.ANNUAL_INCOME,
            A.CONTACT_ADDRESS,
            A.CONTACT_ZIPCODE,
            A.ADDRESS,
            A.ZIP_CODE,
            A.EMAIL,
            A.SHIELD_FLAG,
            A.CAMPAIGN_NAME,
            A.CAMPAIGN_SPLIT_NAME,
            A.BATCH_NAME,
            A.SUPERVISOR_ID,
            A.TEAM_LEADER_ID,
            A.TMR_UM_ID,
            A.PRODUCT_NAME,
            A.CONTACT_DATE,
            A.CALL_RESULT,
            A.IS_INSURE_FLAG,
            A.IS_SUBMIT_FLAG,
            A.IS_FREE_FLAG,
            A.CALL_BACK_REMARK,
            A.SUCCESS_DATE,
            A.TRANSFER_SUCCESS_DAY,
            A.LIST_STATUS,
            A.REGION_CODE,
            A.CREDIT_CARD_CALL_DATE,
            A.SUBMIT_DATE,
            A.CREATED_DATE,
            A.CREATED_BY,
            A.UPDATED_DATE,
            A.UPDATED_BY,
            B.CLEAN_FLAG        
      FROM IDL_SEL_SRC_DATA              A,
           IDL_SEL_BACK_DATA_1_TMP       B, 
           IDL_EX_BATCH                  F
     WHERE B.CLEAN_FLAG IS NOT NULL
       AND A.SYS_ID = B.SYS_ID
       AND A.TCIMS_BATCH_ID = F.TCIMS_BATCH_ID
       AND F.SERIES_TYPE = '02'
       AND F.BATCH_STATUS = '11'
       AND F.PREPARE_FLAG = '1';     
   COMMIT;
  
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(P_ID,53,NULL,'02',NULL,NULL,NULL,NULL);

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

分享到:
评论

相关推荐

    dsplib.zip_DSPF_sp_mat_trans_MIMO通信系统_mimo_ofdm

    总的来说,dsplib.zip是一个面向MIMO-OFDM通信系统的强大工具,它整合了SP矩阵变换的关键算法,为理解和实践现代无线通信的核心技术提供了宝贵的资源。通过深入学习和利用这个库,我们可以更好地理解和驾驭这个复杂...

    SQL_SP3升级包

    SQL Server的Service Pack是一种累积性更新,它将自上一个Service Pack发布以来的所有关键更新、补丁和改进整合到一个方便安装的包中。SQL_SP3包含以下主要知识点: 1. **性能优化**:SQL Server SP3可能包含针对...

    Telerik_Reporting_R2_2018_SP1_12_1_18_620_DEV_Downloadly.ir.msi.rar

    Telerik Reporting与Delphi的整合意味着开发者可以方便地在Delphi项目中嵌入报告功能,提供强大的报表设计和呈现能力。 Telerik Reporting的核心特性包括: 1. **报告设计器**:提供了直观的拖放界面,允许开发者...

    s_GPS_INS_position_sp_demo_惯导_GPS/INS_DEMO_GPS_GPS+惯导_

    标题中的“s_GPS_INS_position_sp_demo”暗示了一个关于GPS(全球定位系统)和惯性导航系统(INS)结合使用的示例程序。这个程序可能用于演示如何整合这两种定位技术以提高定位精度和鲁棒性。 惯性导航系统是通过...

    免费快易网站推广软件2012_SP4

    4. **社交媒体整合**:软件集成了各大社交媒体平台,用户可以一键分享网站内容到Facebook、Twitter等社交网络,扩大影响力,吸引更多的潜在访问者。 5. **网站分析**:内置了基本的网站统计工具,能实时监测网站的...

    S7_HIGRAPH_V5_1_SP1.zip.zip

    6. **网络通信**:除了与PLC的直接通讯,S7_HIGRAPH还支持与其他设备或系统的网络通信,如SCADA系统、数据库服务器等,实现信息的整合与共享。 7. **安全性与权限管理**:为了保护系统安全,软件具备权限管理功能,...

    Telerik_Reporting_R1_2018_SP2_12_0_18_227_DEV_Downloadly.ir.rar

    Telerik Reporting与Delphi的整合使得开发者能够轻松地在Delphi项目中嵌入复杂的报告功能,从而提高应用的业务分析和数据可视化能力。 Telerik Reporting提供了丰富的报表元素,包括表格、图表、图片、文本框、线条...

    Telerik_Reporting_R3_2018_SP1_12_2_18_1017_DEV_Downloadly.ir.msi.rar

    在Delphi开发环境中,Telerik Reporting提供了专门的组件库,使得开发者可以快速将报表功能整合到Delphi应用程序中。通过Delphi IDE中的Telerik Reporting控件,开发者可以方便地创建、编辑和预览报表,并在运行时...

    PowerEasy_CMS2006_SP3

    - **新功能引入**:可能添加了一些新的功能或服务,如社交媒体整合、移动设备适配等,以应对不断变化的互联网环境。 **3. 使用PowerEasy CMS2006 SP3建站流程** - **安装部署**:下载并解压PowerEasy_CMS2006_SP3_...

    SAP_SP-ECC6基本业务全套配置过程_SD配置

    通过上述详尽的配置步骤,企业可以在SAP_SP-ECC6系统中建立起一套完整的SD模块框架,不仅能够支持日常的销售与分销活动,还能够为企业提供深度的数据分析能力和决策支持,从而在竞争激烈的市场环境中保持优势。...

    SAP_BPC_70_SP03_NW_Ops.pdf

    它允许企业进行财务计划、预测、报告和财务合并,包括业务流程整合以及支持多维数据建模。SAP BPC广泛应用于各种规模的企业,尤其适用于复杂的财务需求。 首先,需要了解SAP BPC 7.0 SP03 NW版本是针对SAP ...

    Spring-Boot-ShpReader-master.zip_Geotools_apartment6xh_python_sp

    标题中的"Spring-Boot-ShpReader-master.zip_Geotools_apartment6xh_python_sp"揭示了我们的项目核心——Spring Boot与GeoTools的整合,用于读取Shapefile(Shp)数据,并可能涉及到Python脚本以及GIS服务的实现。...

    西门子PCS7V6.0_SP2 全系列授权

    PCS7的核心是基于SIMATIC IT平台,它整合了自动化、信息管理和过程控制技术,实现了工厂全生命周期管理。SP2作为服务包,通常包含了自上一版本发布以来的所有补丁和修正,以解决已知问题并提升性能。对于PCS7 V6.0来...

    SQL Server2005 SP4

    SP4是SQL Server 2005的最后一个服务包,它整合了自SQL Server 2005 RTM(Release to Manufacturing)以来的所有累积更新,确保用户能够获取到最全面的修复和改进。 SQL Server 2005 SP4分为两个版本,分别针对不同...

    开发Spring MVC应用程序补充—程序源码下载.rar_spring_spring mvc_spring mvc 源码_sp

    7. **服务层与数据访问**:学习如何整合Spring MVC与Service层,以及DAO层进行数据库操作,例如使用@Autowired进行依赖注入。 8. **异常处理**:配置和实现全局和局部异常处理器,确保优雅地处理错误和异常。 9. *...

    Office2003SP3_5in1_20130721.rar

    "Office2003SP3_5in1_20130721.rar"这个压缩包文件,则是将这些应用程序整合在一个五合一的安装包中,便于用户一次性下载和安装。 SP3,即Service Pack 3,是Office 2003的第三次重大更新,它包含了一系列的性能...

    SSSP整合及分页开发

    SSSP(Spring、Spring MVC、Spring Data和Spring JPA)整合是Java开发中常见的技术栈,主要用于构建高效、模块化的Web应用程序。这个整合方案利用了Spring框架的强大功能,结合Spring MVC处理HTTP请求,Spring Data...

    动易4.01+动网7.0SP2整合插件

    操作说明,请仔细阅读操作说明后进行操作: 自建一个升级包目录,将文件clwang_... 5、运行clwang.asp更新用户数据。 6、欢迎访问创力网站(www.clwang.com)和创力论坛(bbs.clwang.com) 升级程序由一枝梅制作。

    风月同学录整合动网论坛dv sp2

    风月同学录整合动网论坛dv... 2、同时打开数据表Dv_user,同学录数据表Dv_user字段后面带了个“1”的,复制到动网数据库表。 3、把setup.asp的数据库路径改成动网的数据库路径! 至此整合已经好了,但不能一站登录。

Global site tag (gtag.js) - Google Analytics