pkg_val_kpd.pck

数据迁移使用的ETL程序包

        t_CSE4.delete;
        t_CSE5.delete;
      end if;
    
      l_Count1 := l_Count1 + 1;
      if l_Count1 mod 10000 = 0 then
        update vld_log
           set success_num = l_Count1, end_time = sysdate
         where procedure_name = l_ProName
           and batch_seq = p_Seq
           and vld_mode = p_Mode;
        commit;
      end if;
    end loop;
    close c_Pr;
  
    /*************************************************************************/
    if t_CSE1.count > 0 then
    
      forall i in t_CSE1.first .. t_CSE1.last
        insert into vld_kpd_detail
        values
          (l_TblName,
           p_Seq,
           p_Mode,
           t_CSE1(i),
           t_CSE2(i),
           t_CSE3(i),
           t_CSE4(i),
           t_CSE5(i),
           null);
    
    end if;
  
    /*************************************************************************/
    -- update check_log
    select count(distinct row_id)
      into l_ErrorNum
      from vld_kpd_detail
     where table_name = l_TblName
       and batch_seq = p_Seq
       and vld_mode = p_Mode
       and row_id > l_StartRec
       and row_id <= l_EndRec;
  
    l_SuccessNum := l_TotalNum - l_ErrorNum;
  
    update vld_log
       set success_num = l_SuccessNum,
           error_num   = l_ErrorNum,
           end_time    = sysdate,
           exec_msg    = 'Completed'
     where procedure_name = l_ProName
       and batch_seq = p_Seq
       and vld_mode = p_Mode;
    /*********************************End*************************************/
    commit;
  exception
    when others then
      l_ErrMsg := substr(sqlerrm, 1, 100);
      rollback;
    
      update vld_log
         set exec_msg = l_ErrMsg
       where procedure_name = l_ProName
         and batch_seq = p_Seq
         and vld_mode = p_Mode;
    
      commit;
  end prc_val_pr;

  /***************************************************************************/
  procedure prc_posval_pr(p_Seq number, p_Mode number) is
    /***************************************************************************/
    -- Author  : Wan, Li
    -- Created : 6/27/2006 12:56:03 PM
    -- Purpose : post validation pro for Prod
    /***************************************************************************/
    l_ErrMsg  varchar2(100);
    l_ProName varchar2(20) := 'PRC_POSVAL_PR';
    l_TblName varchar2(20) := 'SRC_PROD_KENAN';
  begin
    /*initialize check_log*/
    insert into vld_log
    values
      (l_ProName, p_Seq, p_Mode, sysdate, null, null, null, null, null);
  
    commit;
    /*end initialize check_log*/
  
    /* update src_prod_kenan set is_processed = 'N'
    where is_processed = 'E';
    
    for rec_vkd in (
                   select distinct src_id, primary_id from vld_kpd_detail
                   where table_name = l_TblName
                     and batch_seq = p_Seq
                     and vld_mode = p_Mode
                   )
    loop
      update src_prod_kenan set is_processed = 'E' 
      where substr(src_id, 1, 2) = substr(rec_vkd.src_id, 1, 2)
        and serv_id = rec_vkd.primary_id;
    end loop;*/
  
    update vld_log
       set end_time = sysdate, exec_msg = 'Completed'
     where procedure_name = l_ProName
       and batch_seq = p_Seq
       and vld_mode = p_Mode;
  
    commit;
  exception
    when others then
      l_ErrMsg := substr(sqlerrm, 1, 100);
      rollback;
      update vld_log
         set exec_msg = l_ErrMsg
       where procedure_name = l_ProName
         and batch_seq = p_Seq
         and vld_mode = p_Mode;
      commit;
  end prc_posval_pr;

  /***************************************************************************/
  procedure prc_preval_dc(p_Seq number, p_Mode number) is
    /***************************************************************************/
    -- Author  : Wan, Li
    -- Created : 6/27/2006 12:56:03 PM
    -- Purpose : update row_id for src_discount
    /***************************************************************************/
    l_ProName   varchar2(20) := 'PRC_PREVAL_DC';
    l_ErrMsg    varchar2(100);
    l_NullRowID number(10);
    l_MaxRowID  number(10);
  begin
    select count(1)
      into l_NullRowID
      from src_discount_kenan
     where row_id is null;
    if l_NullRowID > 0 then
      insert into vld_log
      values
        (l_ProName, p_Seq, p_Mode, sysdate, null, null, null, null, null);
      commit;
      /*end initialize check_log*/
      select nvl(max(row_id), 0) into l_MaxRowID from src_discount_kenan;
      update src_discount_kenan
         set row_id = l_MaxRowID + rownum, is_processed = 'E'
       where row_id is null;
      update vld_log
         set end_time = sysdate, exec_msg = 'Completed'
       where procedure_name = l_ProName
         and batch_seq = p_Seq
         and vld_mode = p_Mode;
    
      commit;
    end if;
  exception
    when others then
      rollback;
      update vld_log
         set exec_msg = l_ErrMsg
       where procedure_name = l_ProName
         and batch_seq = p_Seq
         and vld_mode = p_Mode;
      commit;
  end prc_preval_dc;

  /***************************************************************************/
  procedure prc_val_dc(p_Seq         number,
                       p_Mode        number,
                       p_TotalStream number,
                       p_StreamNo    number) is
    /***************************************************************************/
    -- Author  : Wan, Li
    -- Created : 6/27/2006 12:56:03 PM
    -- Purpose : Validation for src_discount_kenan
    /***************************************************************************/
    type LOOPREC is record(
      serv_id           src_discount_kenan.serv_id%type,
      acct_nbr_97       src_discount_kenan.acct_nbr_97%type,
      src_id            src_discount_kenan.src_id%type,
      group_no          src_discount_kenan.group_no%type,
      discount_type     src_discount_kenan.discount_type%type,
      disct_method      src_discount_kenan.disct_method%type,
      kenan_discount_id src_discount_kenan.kenan_discount_id%type,
      start_dt          src_discount_kenan.start_dt%type,
      end_dt            src_discount_kenan.end_dt%type,
      is_sbu            src_discount_kenan.is_sbu%type,
      sbu_acct_nbr_97   src_discount_kenan.sbu_acct_nbr_97%type,
      row_id            src_discount_kenan.row_id%type,
      status            src_serv.status%type,
      service_start_dt  date,
      service_end_dt    date,
      suspend_default   src_serv.suspend_default%type,
      x                 src_discount_kenan.x%type,
      y                 src_discount_kenan.y%type);
  
    c_DC            REFCURSOR;
    rec_DC          LOOPREC;
    t_CSE1          CSETABLE1;
    t_CSE2          CSETABLE2;
    t_CSE3          CSETABLE3;
    t_CSE4          CSETABLE4;
    t_CSE5          CSETABLE5;
    l_TblName       varchar2(20) := 'SRC_DISCOUNT_KENAN';
    l_ProName       varchar2(20) := 'PRC_VAL_DC_' || to_char(p_TotalStream) || '_' ||
                                    to_char(p_StreamNo);
    l_StartRec      number(10);
    l_EndRec        number(10);
    l_ErrMsg        varchar2(100);
    l_TotalNum      number(10) := 0;
    l_SuccessNum    number(10) := 0;
    l_ErrorNum      number(10) := 0;
    l_SubQuery      varchar2(2000);
    l_Query         varchar2(3000);
    l_Count         number(5) := 1;
    l_Count1        number(10) := 0;
    l_CountCorridor number(10) := 0;
    --l_flag           number(10) := 0;
  begin
    /*************************************************************************/
    /*initialize check_log*/
    select max(row_id) into l_EndRec from src_discount_kenan;
    l_StartRec := floor(l_EndRec * (p_StreamNo - 1) / p_TotalStream);
    l_EndRec   := floor(l_EndRec * p_StreamNo / p_TotalStream);
  
    select count(1)
      into l_TotalNum
      from src_discount_kenan
     where row_id > l_StartRec
       and row_id <= l_EndRec;
  
    insert into vld_log
    values
      (l_ProName,
       p_Seq,
       p_Mode,
       sysdate,
       null,
       l_TotalNum,
       null,
       null,
       null);
    commit;
  
    t_CSE1.delete;
    t_CSE2.delete;
    t_CSE3.delete;
    t_CSE4.delete;
    t_CSE5.delete;
  
    /*end initialize check_log*/
    l_SubQuery := 'select ss.serv_id, sd.acct_nbr_97, sd.src_id, sd.group_no, sd.discount_type, sd.disct_method, sd.kenan_discount_id,' ||
                  'sd.start_dt, sd.end_dt, sd.is_sbu, sd.sbu_acct_nbr_97, sd.row_id,' ||
                  'ss.status, ss.start_dt, ss.end_dt, ss.suspend_default, sd.x, sd.y ';
  
    if p_Mode = 1 then
      -- TABLE ACCESS FULL 
      l_Query := l_SubQuery ||
                 'from src_discount_kenan sd
                 left outer join src_serv ss
                 on sd.serv_id = ss.serv_id
                 and substr(sd.src_id, 1, 2) = substr(ss.src_id, 1, 2)
                 and ss.is_processed =''N''                 
                 where (sd.row_id > :1 and sd.row_id <= :2)';
    else
      -- CHECK ERROR RECORDS
      l_Query := l_SubQuery || 'from src_discount_kenan sd
                 left outer join src_serv ss
                 on sd.serv_id = ss.serv_id
                 and substr(sd.src_id, 1, 2) = substr(ss.src_id, 1, 2)
                 and sd.discount_type = ''3''
                 and ss.is_processed =''N''
                 where (sd.row_id > :1 and sd.row_id <= :2)
                 and sd.is_processed = ''E''';
    end if;
  
    open c_DC for l_Query
      using l_StartRec, l_EndRec;
    <<NEXTLOOP01556>>
    loop
      fetch c_DC
        into rec_DC;
      exit when c_DC%notfound;
      /*OPERATION LOGIC*/
    
      -- 02:
      if rec_DC.discount_type = '3' and rec_DC.serv_id is null then
        t_CSE1(l_Count) := rec_DC.row_id;
        t_CSE2(l_Count) := rec_DC.src_id;
        t_CSE3(l_Count) := rec_DC.serv_id;
        t_CSE4(l_Count) := 'DC02';
        t_CSE5(l_Count) := C_ErrMsg_DC02;
      
        l_Count := l_Count + 1;
        GOTO NEXTLOOP01556;
      end if;
    
      -- 03:
      if rec_DC.discount_type in ('2', '4', '5') then
        declare
          l_CountDc03 number(10);
        begin
          select count(1)
            into l_CountDc03
            from src_acct
           where acct_nbr_97 = rec_DC.acct_nbr_97
             and substr(src_id, 1, 2) = substr(rec_Dc.src_id, 1, 2);
          if l_CountDc03 = 0 then
            t_CSE1(l_Count) := rec_DC.row_id;
            t_CSE2(l_Count) := rec_DC.src_id;
            t_CSE3(l_Count) := rec_DC.acct_nbr_97;
            t_CSE4(l_Count) := 'DC03';