CREATE PROCEDURE "DBA"."convert_leping_tun"(  )
BEGIN
  declare ll_count integer;
  declare ll_lepnr integer;
  declare ld_kuup1 date;
  declare ldc_ntn numeric(6,3);
  declare ldc_koht numeric(6,3);
  declare err_notfound exception for sqlstate value '02000';
  declare c_leping dynamic scroll cursor for select lepnr,kuup1,get_tundi_nadalas(lepnr),if koht is null or koht=0 then 1 else koht endif from pa_leping where "pa_leping"."kuup2" is null or "pa_leping"."kuup2" > ymd(2019,12,31);
  select count() into ll_count from pa_leping;
  if ll_count>0 then
    set ll_count=0;
    select count() into ll_count from pa_leping_tun where ntn is not null;
    if ll_count=0 then
      open c_leping;
      leping_loop: loop
        fetch next c_leping into ll_lepnr,ld_kuup1,ldc_ntn,ldc_koht;
          if sqlstate = err_notfound then -- kui eof
            leave leping_loop
          end if;
          set ll_count=0;
          select count() into ll_count from pa_leping_tun where lepnr=ll_lepnr;
          if ll_count>0 then
            update pa_leping_tun set ntn=if koht is null or koht=0 then Round(ldc_ntn,0) else if Round(ldc_ntn/koht,0)>40 then 40 else Round(ldc_ntn/koht,0) endif endif where lepnr=ll_lepnr;
          else
            insert into pa_leping_tun(lepnr,kuup1,koht,ntn) values (ll_lepnr,ld_kuup1,ldc_koht,Round(ldc_ntn/ldc_koht,0));
          end if;
         end loop leping_loop;
        close c_leping;
    end if;
  end if;
END