kenan_get_data_pd.pck

数据迁移使用的ETL程序包

        ls_wrong  := 0;
        ls_row_id := 0;
        ls_cnt    := 0;
        for rec in cur loop
          begin
            ls_row_id := ls_row_id + 1;
            ls_cnt    := ls_cnt + 1;
            insert into SRC_CAH_EXT_DATA
              (GROUP_ID,
               ACCT_NBR_97,
               SERV_ID,
               IS_SERVICE_EXCLUDE,
               SERVICE_START_DT,
               SERVICE_END_DT,
               KENAN_DISCOUNT_ID,
               src_id)
            values
              (TRIM(REC.GROUP_ID),
               TRIM(REC.ACCT_NBR_97),
               TRIM(REC.SERV_ID),
               TRIM(REC.IS_SERVICE_EXCLUDE),
               TRUNC(REC.SERVICE_START_DT),
               TRUNC(REC.SERVICE_END_DT),
               TRIM(REC.KENAN_DISCOUNT_ID),
               trim(rec.src_id));
          
            if mod(ls_cnt, 10000) = 0 then
              commit;
            end if;
          
          exception
            when others then
              ls_wrong := ls_wrong + 1;
              insert into SRC_CAH_EXT_DATA_err
                (GROUP_ID,
                 ACCT_NBR_97,
                 SERV_ID,
                 IS_SERVICE_EXCLUDE,
                 SERVICE_START_DT,
                 SERVICE_END_DT,
                 KENAN_DISCOUNT_ID,
                 src_id)
              values
                (TRIM(REC.GROUP_ID),
                 TRIM(REC.ACCT_NBR_97),
                 TRIM(REC.SERV_ID),
                 TRIM(REC.IS_SERVICE_EXCLUDE),
                 TRIM(REC.SERVICE_START_DT),
                 TRIM(REC.SERVICE_END_DT),
                 TRIM(REC.KENAN_DISCOUNT_ID),
                 trim(rec.src_id));
          end;
        
        end loop;
        commit;
        insert into get_data_log
          (log_key, log_type, log_string, log_date, log_proc, memo)
        values
          (seq_get_data_log.nextval,
           '完成',
           '总共完成数量 ' || ls_cnt,
           sysdate,
           'get_' || in_string,
           '错误数量 ' || ls_wrong);
        commit;
      
      end if;
    end if;
    commit;
  
  end;

  procedure GET_SRC_INR_BATCH_NRC(in_string varchar2) is
    ls_row_id number;
    ls_cnt    number;
    ls_count  number;
    ls_wrong  number;
    cursor cur is
      select * from SRC_INR_BATCH_NRC_upload;
  begin
    execute immediate 'alter session set nls_date_format =''yyyymmddhh24miss''';
    IF UPPER(IN_STRING) <> UPPER('SRC_INR_BATCH_NRC') THEN
      insert into get_data_log
        (log_key, log_type, log_string, log_date, log_proc, memo)
      values
        (seq_get_data_log.nextval,
         'wrong',
         '参数传入错误',
         sysdate,
         in_string,
         '');
      commit;
    else
      select count(*)
        into ls_count
        from SRC_INR_BATCH_NRC
       where rownum = 1;
      if ls_count = 1 then
        insert into get_data_log
          (log_key, log_type, log_string, log_date, log_proc, memo)
        values
          (seq_get_data_log.nextval,
           'wrong',
           in_string || '非空！无法执行insert！',
           sysdate,
           'get_' || in_string,
           '');
        commit;
      else
        ls_wrong  := 0;
        ls_row_id := 0;
        ls_cnt    := 0;
        for rec in cur loop
          begin
            ls_row_id := ls_row_id + 1;
            ls_cnt    := ls_cnt + 1;
            insert into SRC_INR_BATCH_NRC
            values
              (trim(rec.SERV_NUM),
               trim(rec.ACCT_NBR),
               trim(rec.SERV_TYPE_ID),
               trim(rec.NRC_LEVEL),
               trim(rec.NRC_AMOUNT),
               trim(rec.LEGACY_ANNOTATION),
               TRUNC(rec.NRC_EFFECTIVE_DATE),
               trim(rec.IS_PROCESSED),
               trim(rec.FILE_SEQ_NUM),
               trim(rec.STAFF_NAME),
               trim(rec.OPEN_ITEM_ID),
               trim(rec.SRC_ID),
               ls_row_id,
               trim(rec.SERV_ID),
               trim(rec.TYPE_NRC_ID),
               trim(rec.SRC_TAB));
          
            if mod(ls_cnt, 10000) = 0 then
              commit;
            end if;
          
          exception
            when others then
              ls_wrong := ls_wrong + 1;
              insert into SRC_INR_BATCH_NRC_err
              values
                (trim(rec.SERV_NUM),
                 trim(rec.ACCT_NBR),
                 trim(rec.SERV_TYPE_ID),
                 trim(rec.NRC_LEVEL),
                 trim(rec.NRC_AMOUNT),
                 trim(rec.LEGACY_ANNOTATION),
                 TRUNC(rec.NRC_EFFECTIVE_DATE),
                 trim(rec.IS_PROCESSED),
                 trim(rec.FILE_SEQ_NUM),
                 trim(rec.STAFF_NAME),
                 trim(rec.OPEN_ITEM_ID),
                 trim(rec.SRC_ID),
                 ls_row_id,
                 trim(rec.SERV_ID),
                 trim(rec.TYPE_NRC_ID),
                 trim(rec.SRC_TAB));
          end;
        
        end loop;
        commit;
        insert into get_data_log
          (log_key, log_type, log_string, log_date, log_proc, memo)
        values
          (seq_get_data_log.nextval,
           '完成',
           '总共完成数量 ' || ls_cnt,
           sysdate,
           'get_' || in_string,
           '错误数量 ' || ls_wrong);
        commit;
      
      end if;
    end if;
    commit;
  
  end;

  procedure GET_SRC_CAH_EXT_DATA_PATCH(in_string varchar2) is
    ls_row_id number;
    ls_cnt    number;
    ls_count  number;
    ls_wrong  number;
    cursor cur is
      select * from SRC_CAH_EXT_DATA_PATCH_UP;
  begin
    execute immediate 'alter session set nls_date_format =''yyyymmddhh24miss''';
    IF UPPER(IN_STRING) <> UPPER('SRC_CAH_EXT_DATA_PATCH') THEN
      insert into get_data_log
        (log_key, log_type, log_string, log_date, log_proc, memo)
      values
        (seq_get_data_log.nextval,
         'wrong',
         '参数传入错误',
         sysdate,
         in_string,
         '');
      commit;
    else
      select count(*)
        into ls_count
        from SRC_CAH_EXT_DATA_PATCH
       where rownum = 1;
      if ls_count = 1 then
        insert into get_data_log
          (log_key, log_type, log_string, log_date, log_proc, memo)
        values
          (seq_get_data_log.nextval,
           'wrong',
           in_string || '非空！无法执行insert！',
           sysdate,
           'get_' || in_string,
           '');
        commit;
      else
        ls_wrong  := 0;
        ls_row_id := 0;
        ls_cnt    := 0;
        for rec in cur loop
          begin
            ls_row_id := ls_row_id + 1;
            ls_cnt    := ls_cnt + 1;
            insert into SRC_CAH_EXT_DATA_PATCH
              (GROUP_ID,
               ACCT_NBR_97,
               SERV_ID,
               IS_SERVICE_EXCLUDE,
               SERVICE_START_DT,
               SERVICE_END_DT,
               KENAN_DISCOUNT_ID,
               src_id)
            values
              (TRIM(REC.GROUP_ID),
               TRIM(REC.ACCT_NBR_97),
               TRIM(REC.SERV_ID),
               TRIM(REC.IS_SERVICE_EXCLUDE),
               TRUNC(REC.SERVICE_START_DT),
               TRUNC(REC.SERVICE_END_DT),
               TRIM(REC.KENAN_DISCOUNT_ID),
               trim(rec.src_id));
          
            if mod(ls_cnt, 10000) = 0 then
              commit;
            end if;
          
          exception
            when others then
              ls_wrong := ls_wrong + 1;
              insert into SRC_CAH_EXT_DATA_PATCH_err
                (GROUP_ID,
                 ACCT_NBR_97,
                 SERV_ID,
                 IS_SERVICE_EXCLUDE,
                 SERVICE_START_DT,
                 SERVICE_END_DT,
                 KENAN_DISCOUNT_ID,
                 src_id)
              values
                (TRIM(REC.GROUP_ID),
                 TRIM(REC.ACCT_NBR_97),
                 TRIM(REC.SERV_ID),
                 TRIM(REC.IS_SERVICE_EXCLUDE),
                 TRUNC(REC.SERVICE_START_DT),
                 TRUNC(REC.SERVICE_END_DT),
                 TRIM(REC.KENAN_DISCOUNT_ID),
                 trim(rec.src_id));
          end;
        
        end loop;
        commit;
        insert into get_data_log
          (log_key, log_type, log_string, log_date, log_proc, memo)
        values
          (seq_get_data_log.nextval,
           '完成',
           '总共完成数量 ' || ls_cnt,
           sysdate,
           'get_' || in_string,
           '错误数量 ' || ls_wrong);
        commit;
      
      end if;
    end if;
    commit;
  
  end;
  /*procedure get_data(in_string varchar2) is
  begin
  
    if upper(in_string) = upper('src_discount_kenan') then
      get_src_discount_kenan(in_string);
    
    elsif upper(in_string) = upper('src_prod_kenan') then
      get_src_prod_kenan(in_string);
    
    elsif upper(in_string) = upper('SRC_CONTRACT_ASSIGNMENT_HQ') then
      get_SRC_CONTRACT_ASSIGNMENT_HQ(in_string);
    
    elsif upper(in_string) = upper('src_cah_ext_data') then
      get_src_cah_ext_data(in_string);
    
    elsif upper(in_string) = upper('src_inr_batch_nrc') then
      get_src_inr_batch_nrc(in_string);
    
    elsif upper(in_string) = upper('SRC_CAH_EXT_DATA_PATCH') then
      get_SRC_CAH_EXT_DATA_PATCH(in_string);
    
    else
    
      insert into get_data_log
        (log_key, log_type, log_string, log_date, log_proc, memo)
      values
        (seq_get_data_log.nextval,
         '错误',
         '没有这张表' || in_string,
         sysdate,
         'get_' || in_string,
         '');
      commit;
    
    end if;
  
    insert into get_data_log
      (log_key, log_type, log_string, log_date, log_proc, memo)
    values
      (seq_get_data_log.nextval,
       '完成',
       '' || in_string,
       sysdate,
       'get_' || in_string,
       '');
    commit;
  
  end;*/

end;
/