pkg_val_kpd.pck

数据迁移使用的ETL程序包

            t_CSE5(l_Count) := C_ErrMsg_DC03;
          
            l_Count := l_Count + 1;
            GOTO NEXTLOOP01556;
          end if;
        end;
      
      end if;
    
      --Add New Validation 16
      if rec_DC.disct_method = '6' then
      
        select count(1)
          into l_CountCorridor
          from src_discount_kenan
         where serv_id = rec_DC.serv_id
           and kenan_discount_id = rec_DC.kenan_discount_id
           and substr(src_id, 1, 2) = substr(rec_Dc.src_id, 1, 2);
        if l_CountCorridor > 1 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) := 'DC16';
          t_CSE5(l_Count) := C_ErrMsg_DC16;
        
          l_Count := l_Count + 1;
        end if;
      end if;
    
      --PR31
      if rec_DC.disct_method = '6' then
        declare
        begin
          for rec_31 in (select count(*) num,
                                sdk.serv_id,
                                sdk.src_id,
                                cpg.point_category,
                                cpg.point_target
                           from src_discount_kenan   sdk,
                                CORRIDOR_PLAN_GROUPS cpg
                          where sdk.serv_id = rec_DC.serv_id
                            and substr(sdk.src_id, 1, 2) =
                                substr(rec_DC.src_id, 1, 2)
                            and sdk.kenan_discount_id = cpg.corridor_plan_id
                          group by sdk.serv_id,
                                   sdk.src_id,
                                   cpg.point_category,
                                   cpg.point_target
                         having count(1) > 1) loop
          
            if rec_31.num > 1 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) := 'DC31';
              t_CSE5(l_Count) := C_ErrMsg_DC31;
            
              l_Count := l_Count + 1;
            end if;
          end loop;
        end;
      end if;
    
      --欠费双停
      if rec_DC.suspend_default = '双向' and rec_DC.status = 'A' then
        declare
          l_CountDC32 number(10) := 0;
        begin
          select count(1)
            into l_CountDC32
            from src_discount_kenan
           where serv_id = rec_DC.serv_id
             and kenan_discount_id = '81802'
             and substr(src_id, 1, 2) = substr(rec_Dc.src_id, 1, 2);
        
          if l_CountDC32 = 0 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) := 'DC32';
            t_CSE5(l_Count) := C_ErrMsg_DC32;
          
            l_Count := l_Count + 1;
          
          end if;
        end;
      end if;
    
      --欠费单停
      if rec_DC.suspend_default = '单向' and rec_DC.status = 'A' then
        declare
          l_CountDC33 number(10) := 0;
        begin
          select count(1)
            into l_CountDC33
            from src_discount_kenan
           where serv_id = rec_DC.serv_id
             and kenan_discount_id = '81802'
             and substr(src_id, 1, 2) = substr(rec_Dc.src_id, 1, 2);
        
          if l_CountDC33 > 0 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) := 'DC33';
            t_CSE5(l_Count) := C_ErrMsg_DC33;
          
            l_Count := l_Count + 1;
          
          end if;
        end;
      end if;
    
      --Add New Validation 17
      if rec_DC.disct_method = 3 and rec_DC.x > 100 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) := 'DC17';
        t_CSE5(l_Count) := C_ErrMsg_DC17;
      
        l_Count := l_Count + 1;
      
      end if;
    
      -- 01:
      if rec_DC.disct_method <> '6' and
         rec_DC.discount_type not in ('2', '3', '4', '5') 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) := 'DC01';
        t_CSE5(l_Count) := C_ErrMsg_DC01;
      
        l_Count := l_Count + 1;
      end if;
    
      -- 04:
      if rec_DC.discount_type in ('4', '5') and rec_DC.group_no 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.acct_nbr_97;
        t_CSE4(l_Count) := 'DC04';
        t_CSE5(l_Count) := C_ErrMsg_DC04;
      
        l_Count := l_Count + 1;
      end if;
      -- 05:
      if rec_DC.disct_method is null or
         rec_DC.disct_method not in ('1', '2', '3', '4', '5', '6') then
        t_CSE1(l_Count) := rec_DC.row_id;
        t_CSE2(l_Count) := rec_DC.src_id;
        t_CSE3(l_Count) := nvl(rec_DC.serv_id, rec_DC.acct_nbr_97);
        t_CSE4(l_Count) := 'DC05';
        t_CSE5(l_Count) := C_ErrMsg_DC05;
      
        l_Count := l_Count + 1;
      end if;
      -- 06:
      -- 07:
      -- 14:
      begin
        if rec_DC.disct_method <> '6' then
          if t_CKC(to_char(rec_DC.kenan_discount_id) || '2') = '65' then
            null;
          end if;
        elsif rec_DC.disct_method = '6' then
          if t_CKC(to_char(rec_DC.kenan_discount_id) || '3') = '65' then
            null;
          end if;
        end if;
      exception
        when no_data_found then
          t_CSE1(l_Count) := rec_DC.row_id;
          t_CSE2(l_Count) := rec_DC.src_id;
          t_CSE3(l_Count) := nvl(rec_DC.serv_id, rec_DC.acct_nbr_97);
          t_CSE4(l_Count) := 'DC14';
          t_CSE5(l_Count) := C_ErrMsg_DC14;
        
          l_Count := l_Count + 1;
      end;
      -- 15:
      declare
        l_Acctnbr97 src_discount.sbu_acct_nbr_97%type;
      begin
        if rec_DC.is_sbu = 1 then
          if rec_DC.sbu_acct_nbr_97 is null then
            t_CSE1(l_Count) := rec_DC.row_id;
            t_CSE2(l_Count) := rec_DC.src_id;
            t_CSE3(l_Count) := nvl(rec_DC.serv_id, rec_DC.acct_nbr_97);
            t_CSE4(l_Count) := 'DC15';
            t_CSE5(l_Count) := C_ErrMsg_DC15;
          
            l_Count := l_Count + 1;
          else
            select acct_nbr_97
              into l_Acctnbr97
              from src_acct
             where acct_nbr_97 = rec_DC.sbu_acct_nbr_97;
          end if;
        end if;
      exception
        when no_data_found then
          t_CSE1(l_Count) := rec_DC.row_id;
          t_CSE2(l_Count) := rec_DC.src_id;
          t_CSE3(l_Count) := nvl(rec_DC.serv_id, rec_DC.acct_nbr_97);
          t_CSE4(l_Count) := 'DC15';
          t_CSE5(l_Count) := C_ErrMsg_DC15;
        
          l_Count := l_Count + 1;
      end;
    
      /*TIME HIERARCHY*/
      -- 08:
      if rec_DC.start_dt is null then
        t_CSE1(l_Count) := rec_DC.row_id;
        t_CSE2(l_Count) := rec_DC.src_id;
        t_CSE3(l_Count) := nvl(rec_DC.serv_id, rec_DC.acct_nbr_97);
        t_CSE4(l_Count) := 'DC08';
        t_CSE5(l_Count) := C_ErrMsg_DC08;
      
        l_Count := l_Count + 1;
      end if;
      -- 09:
      -- 10:
      if rec_DC.discount_type = '3' and
         rec_DC.start_dt < rec_DC.service_start_dt then
        t_CSE1(l_Count) := rec_DC.row_id;
        t_CSE2(l_Count) := rec_DC.src_id;
        t_CSE3(l_Count) := nvl(rec_DC.serv_id, rec_DC.acct_nbr_97);
        t_CSE4(l_Count) := 'DC10';
        t_CSE5(l_Count) := C_ErrMsg_DC10;
      
        l_Count := l_Count + 1;
      end if;
      -- 11:
      if rec_DC.discount_type in ('2', '4', '5') then
        declare
          l_CountDc11 number(10);
        begin
          select count(1)
            into l_CountDc11
            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)
             and acct_created_date > rec_Dc.start_dt;
          if l_CountDc11 > 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) := 'DC11';
            t_CSE5(l_Count) := C_ErrMsg_DC11;
          
            l_Count := l_Count + 1;
          end if;
        end;
      end if;
      -- 12:
      if rec_DC.end_dt < rec_DC.start_dt then
        t_CSE1(l_Count) := rec_DC.row_id;
        t_CSE2(l_Count) := rec_DC.src_id;
        t_CSE3(l_Count) := nvl(rec_DC.serv_id, rec_DC.acct_nbr_97);
        t_CSE4(l_Count) := 'DC12';
        t_CSE5(l_Count) := C_ErrMsg_DC12;
      
        l_Count := l_Count + 1;
      end if;
      -- 13:
      if rec_DC.status = 'D' and
         (rec_DC.end_dt is null or (rec_DC.end_dt > rec_DC.service_end_dt and
         rec_DC.start_dt <> rec_DC.end_dt)) then
        t_CSE1(l_Count) := rec_DC.row_id;
        t_CSE2(l_Count) := rec_DC.src_id;
        t_CSE3(l_Count) := nvl(rec_DC.serv_id, rec_DC.acct_nbr_97);
        t_CSE4(l_Count) := 'DC13';
        t_CSE5(l_Count) := C_ErrMsg_DC13;
      
        l_Count := l_Count + 1;
      end if;
    
      /*INSERT INTO VLD_KPD_DETAIL*/
      if l_Count > 1000 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);
      
        commit;
      
        l_Count := 1;
        t_CSE1.delete;
        t_CSE2.delete;
        t_CSE3.delete;
        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