ALTER PROCEDURE "dba"."pa_arvuta_reserv"(in a_v_akuu numeric(2),in a_v_aasta numeric(4))
begin
  -- kuu maksustatav summa
  declare ldc_tatk_summa3 numeric(15,2);
  declare ldc_sots_summa3 numeric(15,2);
  -- kuu maks
  declare ldc_tatk_maks3 numeric(15,2);
  declare ldc_sots_maks3 numeric(15,2);
  -- jooksva rea maks
  declare ldc_tatk_maks4 numeric(15,2);
  declare ldc_sots_maks4 numeric(15,2);
  -- maksu proportsioon
  declare ldc_tatk_prop numeric(15,6);
  declare ldc_sots_prop numeric(15,6);
  -- viimane arvutatav rida
  declare li_last_tatk_id integer;
  declare li_last_tatk_a_aasta integer;
  -- maarad
  declare ldc_tatk_protsent numeric(10,3);
  declare ldc_sots_protsent numeric(10,3);
  -- arvutatava rea fetch
  declare li_id integer;
  declare li_a_aasta integer;
  declare ldc_summa3 numeric(15,2);
  declare err_notfound exception for sqlstate value '02000';
  declare c_reserv dynamic scroll cursor for select pa_reserv.reservnr,pa_reserv.a_aasta,pa_reserv.summa from pa_reserv where pa_reserv.v_aasta = a_v_aasta and pa_reserv.v_akuu = a_v_akuu;
  set ldc_tatk_protsent=get_maar(5,ymd(a_v_aasta,a_v_akuu,1));
  set ldc_sots_protsent=get_maar(7,ymd(a_v_aasta,a_v_akuu,1));
  -- kuu maksuga maksustatav summa
  select sum(pa_reserv.summa) into ldc_tatk_summa3 from pa_reserv where pa_reserv.v_aasta = a_v_aasta and pa_reserv.v_akuu = a_v_akuu;
  if ldc_tatk_summa3 is null then
    set ldc_tatk_summa3=0
  end if;
  select sum(pa_reserv.summa) into ldc_sots_summa3 from pa_reserv where pa_reserv.v_aasta = a_v_aasta and pa_reserv.v_akuu = a_v_akuu;
  if ldc_sots_summa3 is null then
    set ldc_sots_summa3=0
  end if;
  -- tooandja tootuskindlustus
  set ldc_tatk_maks3=Round(ldc_tatk_summa3*ldc_tatk_protsent/100,2);
  -- prop
  if ldc_tatk_summa3 = 0 then
    set ldc_tatk_prop=0
  else
    set ldc_tatk_prop=ldc_tatk_maks3/ldc_tatk_summa3
  end if;
  -- sotsiaalmaks
  set ldc_sots_maks3=Round(ldc_sots_summa3*ldc_sots_protsent/100,2);
  -- prop
  if ldc_sots_summa3 = 0 then
    set ldc_sots_prop=0
  else
    set ldc_sots_prop=ldc_sots_maks3/ldc_sots_summa3
  end if;
  -- maksu jagamine tasude ridadele
  set li_id=0;
  open c_reserv;
  reserv_loop: loop
    fetch next c_reserv into li_id,li_a_aasta,ldc_summa3;
    if sqlstate = err_notfound then
      leave reserv_loop
    end if;
    set ldc_tatk_maks4=Round(ldc_summa3*ldc_tatk_prop,2);
    set ldc_tatk_maks3=ldc_tatk_maks3-ldc_tatk_maks4;
    set ldc_sots_maks4=Round(ldc_summa3*ldc_sots_prop,2);
    set ldc_sots_maks3=ldc_sots_maks3-ldc_sots_maks4;
    update pa_reserv set pa_reserv.tatkmaks = ldc_tatk_maks4,pa_reserv.sotsmaks = ldc_sots_maks4 where pa_reserv.reservnr = li_id and pa_reserv.a_aasta = li_a_aasta;
    set li_last_tatk_id=li_id;
    set li_last_tatk_a_aasta=li_a_aasta
  end loop reserv_loop;
  close c_reserv;
  if ldc_tatk_maks3 <> 0 and li_last_tatk_id > 0 then
    update pa_reserv set pa_reserv.tatkmaks = pa_reserv.tatkmaks+ldc_tatk_maks3 where pa_reserv.reservnr = li_last_tatk_id and pa_reserv.a_aasta = li_last_tatk_a_aasta
  end if;
  if ldc_sots_maks3 <> 0 and li_last_tatk_id > 0 then
    update pa_reserv set pa_reserv.sotsmaks = pa_reserv.sotsmaks+ldc_sots_maks3 where pa_reserv.reservnr = li_last_tatk_id and pa_reserv.a_aasta = li_last_tatk_a_aasta
  end if
end