kenan_get_data_pd.pck

数据迁移使用的ETL程序包

      commit;
    else
      select count(*) into ls_count from src_PROD_KENAN 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_PROD_KENAN
              (ACCT_NBR_97,
               ACCT_NBR_BILL,
               SERV_ID,
               SERV_NUM,
               PROD_REF_NO_BILL,
               PROD_NAME_BILL,
               RC_RATE,
               UNITS,
               UNITS_TYPE,
               OPEN_ITEM_ID,
               START_DT,
               END_DT,
               SRC_ID,
               SRC_TAB,
               SRC_KEY1,
               SRC_KEY2,
               SRC_LAST_UPDATED,
               SRC_FILESET,
               SRC_UPDATE_COUNT,
               SERVER_ID,
               SERVER_SID,
               SERVER_USER,
               IS_PROCESSED,
               SIB_PROCESS_STATUS,
               KENAN_PRODUCT_ID,
               KENAN_PRODUCT_NAME,
               SIEBEL_PRODUCT_ID,
               SIEBEL_PRODUCT_NAME,
               BILLED_THRU_DATE,
               NO_BILL,
               ROW_ID,
               IS_OVERRIDEN,
               ORDER_ITEM_CODE,
               SEQUENCE_ID,
               PROD_TRACKING_ID,
               ROW_ID_2,
               SRC_KEY3,
               IS_MAIN_ACCT,
               PROD_SERV_ID,
               SERV_PRODUCT_ID,
               PAR_SEQUENCE_ID,
               LAYER_ID)
            values
              (trim(rec.ACCT_NBR_97),
               trim(rec.ACCT_NBR_BILL),
               trim(rec.SERV_ID),
               trim(rec.SERV_NUM),
               trim(rec.PROD_REF_NO_BILL),
               trim(rec.PROD_NAME_BILL),
               trim(rec.RC_RATE),
               trim(rec.UNITS),
               trim(rec.UNITS_TYPE),
               trim(rec.OPEN_ITEM_ID),
               trunc(rec.START_DT),
               trunc(rec.END_DT),
               trim(rec.SRC_ID),
               trim(rec.SRC_TAB),
               trim(rec.SRC_KEY1),
               trim(rec.SRC_KEY2),
               trim(rec.SRC_LAST_UPDATED),
               trim(rec.SRC_FILESET),
               trim(rec.SRC_UPDATE_COUNT),
               trim(rec.SERVER_ID),
               trim(rec.SERVER_SID),
               trim(rec.SERVER_USER),
               'N' --IS_PROCESSED             
              ,
               'N' --SIB_PROCESS_STATUS        
              ,
               trim(rec.KENAN_PRODUCT_ID),
               trim(rec.KENAN_PRODUCT_NAME),
               trim(rec.SIEBEL_PRODUCT_ID),
               trim(rec.SIEBEL_PRODUCT_NAME),
               trunc(rec.BILLED_THRU_DATE),
               trim(rec.NO_BILL),
               ls_row_id --ROW_ID                       
              ,
               trim(rec.IS_OVERRIDEN),
               trim(rec.ORDER_ITEM_CODE),
               trim(rec.SEQUENCE_ID),
               trim(rec.PROD_TRACKING_ID),
               trim(rec.ROW_ID_2),
               trim(rec.SRC_KEY3),
               trim(rec.IS_MAIN_ACCT),
               trim(rec.PROD_SERV_ID),
               trim(rec.SERV_PRODUCT_ID),
               trim(rec.PAR_SEQUENCE_ID),
               trim(rec.LAYER_ID));
          
            if mod(ls_cnt, 10000) = 0 then
              commit;
            end if;
          
          exception
            when others then
              ls_wrong := ls_wrong + 1;
              insert into src_PROD_KENAN_err
              values
                (trim(rec.ACCT_NBR_97),
                 trim(rec.ACCT_NBR_BILL),
                 trim(rec.SERV_ID),
                 trim(rec.SERV_NUM),
                 trim(rec.PROD_REF_NO_BILL),
                 trim(rec.PROD_NAME_BILL),
                 trim(rec.RC_RATE),
                 trim(rec.UNITS),
                 trim(rec.UNITS_TYPE),
                 trim(rec.OPEN_ITEM_ID),
                 trunc(rec.START_DT),
                 trunc(rec.END_DT),
                 trim(rec.SRC_ID),
                 trim(rec.SRC_TAB),
                 trim(rec.SRC_KEY1),
                 trim(rec.SRC_KEY2),
                 trim(rec.SRC_LAST_UPDATED),
                 trim(rec.SRC_FILESET),
                 trim(rec.SRC_UPDATE_COUNT),
                 trim(rec.SERVER_ID),
                 trim(rec.SERVER_SID),
                 trim(rec.SERVER_USER),
                 'N' --IS_PROCESSED             
                ,
                 'N' --SIB_PROCESS_STATUS        
                ,
                 trim(rec.KENAN_PRODUCT_ID),
                 trim(rec.KENAN_PRODUCT_NAME),
                 trim(rec.SIEBEL_PRODUCT_ID),
                 trim(rec.SIEBEL_PRODUCT_NAME),
                 TRUNC(rec.BILLED_THRU_DATE),
                 trim(rec.NO_BILL),
                 ls_row_id --ROW_ID                       
                ,
                 trim(rec.IS_OVERRIDEN),
                 trim(rec.ORDER_ITEM_CODE),
                 trim(rec.SEQUENCE_ID),
                 trim(rec.PROD_TRACKING_ID),
                 trim(rec.ROW_ID_2),
                 trim(rec.SRC_KEY3),
                 trim(rec.IS_MAIN_ACCT),
                 trim(rec.PROD_SERV_ID),
                 trim(rec.SERV_PRODUCT_ID),
                 trim(rec.PAR_SEQUENCE_ID),
                 trim(rec.LAYER_ID));
          end;
          if mod(ls_cnt, 10000) = 0 then
            commit;
          end if;
        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;
  
  exception
    when others then
      v_ErrCode := sqlcode;
      v_ErrMsg  := sqlerrm;
      insert into get_data_log
        (log_key, log_type, log_string, log_date, log_proc, memo)
      values
        (seq_get_data_log.nextval,
         v_ErrCode,
         v_ErrMsg,
         sysdate,
         'get_' || in_string,
         '');
    
      commit;
    
  end;

  procedure GET_SRC_CONTRACT_ASSIGNMENT_HQ(in_string varchar2) is
    ls_row_id number;
    ls_cnt    number;
    ls_count  number;
    ls_wrong  number;
    cursor cur is
      select * from SRC_CONTRACT_ASSIGN_HQ_up;
  begin
    execute immediate 'alter session set nls_date_format =''yyyymmddhh24miss''';
    IF UPPER(IN_STRING) <> UPPER('SRC_CONTRACT_ASSIGNMENT_HQ') 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_CONTRACT_ASSIGNMENT_HQ
       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_CONTRACT_ASSIGNMENT_HQ
              (GROUP_ID,
               ACCT_NBR_97,
               KENAN_DISCOUNT_ID,
               START_DT,
               END_DT,
               EXCLUDE,
               src_id)
            values
              (TRIM(REC.GROUP_ID),
               TRIM(REC.ACCT_NBR_97),
               TRIM(REC.KENAN_DISCOUNT_ID),
               TRUNC(REC.START_DT),
               TRUNC(REC.END_DT),
               TRIM(REC.EXCLUDE),
               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_CONTRACT_ASSIGN_HQ_err
                (GROUP_ID,
                 ACCT_NBR_97,
                 KENAN_DISCOUNT_ID,
                 START_DT,
                 END_DT,
                 EXCLUDE,
                 src_id)
              values
                (TRIM(REC.GROUP_ID),
                 TRIM(REC.ACCT_NBR_97),
                 TRIM(REC.KENAN_DISCOUNT_ID),
                 TRUNC(REC.START_DT),
                 TRUNC(REC.END_DT),
                 TRIM(REC.EXCLUDE),
                 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_CAH_EXT_DATA(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_upload;
  begin
    execute immediate 'alter session set nls_date_format =''yyyymmddhh24miss''';
    IF UPPER(IN_STRING) <> UPPER('SRC_CAH_EXT_DATA') 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 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