ALTER PROCEDURE "DBA"."pa_arvuta_erisood"(in a_skoopkood char(16), in a_skoop integer, in a_t_akuu numeric(2),in a_t_aasta numeric(4))
begin
  -- kuu maksustatav summa
  declare ldc_tulu_summa3 numeric(15,2);
  declare ldc_sots_summa3 numeric(15,2);
  -- kuu maks
  declare ldc_tulu_maks3 numeric(15,2);
  declare ldc_sots_maks3 numeric(15,2);
  -- jooksva rea maks
  declare ldc_tulu_maks4 numeric(15,2);
  declare ldc_sots_maks4 numeric(15,2);
  -- maksu proportsioon
  declare ldc_tulu_prop numeric(15,6);
  declare ldc_sots_prop numeric(15,6);
  -- viimane arvutatav rida
  declare li_last_tulu_id integer;
  declare li_last_tulu_a_aasta integer;
  -- maarad
  declare ldc_tulu_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 li_tatk_skoop smallint;
  declare err_notfound exception for sqlstate value '02000';
  declare c_tasu dynamic scroll cursor for select pa_erisood.id,pa_erisood.a_aasta,pa_erisood.summa from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr left outer join lisalist on pa_erisood.lisanr=lisalist.lisanr left outer join skoobid on pa_yksus.skoop=skoobid.skoop where lisalist.objnr=40 and pa_erisood.t_aasta = a_t_aasta and pa_erisood.t_akuu = a_t_akuu and (li_tatk_skoop=0 or pa_yksus.skoop=a_skoop) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  declare c_tasu60 dynamic scroll cursor for select pa_erisood.id,pa_erisood.a_aasta,pa_erisood.summa from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr left outer join lisalist on pa_erisood.lisanr=lisalist.lisanr left outer join skoobid on pa_yksus.skoop=skoobid.skoop where lisalist.objnr=60 and pa_erisood.t_aasta = a_t_aasta and pa_erisood.t_akuu = a_t_akuu and (li_tatk_skoop=0 or pa_yksus.skoop=a_skoop) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  set ldc_tulu_protsent=get_maar(2,ymd(a_t_aasta,a_t_akuu,1));
  set ldc_sots_protsent=get_maar(7,ymd(a_t_aasta,a_t_akuu,1));
  select pa_konfm.yld_tatk_skoop into li_tatk_skoop from pa_konfm where pa_konfm.id = 1;
  -- Lisa 4
  -- kuu tulumaksuga maksustatav summa
  select sum(pa_erisood.summa) into ldc_tulu_summa3 from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr left outer join lisalist on pa_erisood.lisanr=lisalist.lisanr left outer join skoobid on pa_yksus.skoop=skoobid.skoop where lisalist.objnr=40 and pa_erisood.t_aasta = a_t_aasta and pa_erisood.t_akuu = a_t_akuu and (li_tatk_skoop=0 or pa_yksus.skoop=a_skoop) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_tulu_summa3 is null then
    set ldc_tulu_summa3=0
  else 
    set ldc_tulu_summa3=Round(ldc_tulu_summa3,2)
  end if;
  -- tulumaks
  set ldc_tulu_maks3=Round(ldc_tulu_summa3*(ldc_tulu_protsent/(100-ldc_tulu_protsent)),2);
  -- prop
  if ldc_tulu_summa3 = 0 then
    set ldc_tulu_prop=0
  else
    set ldc_tulu_prop=ldc_tulu_maks3/ldc_tulu_summa3
  end if;
  -- kuu sotsiaalmaksuga maksustatav summa
  set ldc_sots_summa3=ldc_tulu_summa3+ldc_tulu_maks3;
  -- 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_tasu;
  tasu_loop: loop
    fetch next c_tasu into li_id,li_a_aasta,ldc_summa3;
    if sqlstate = err_notfound then
      leave tasu_loop
    end if;
    set ldc_tulu_maks4=Round(ldc_summa3*ldc_tulu_prop,2);
    set ldc_tulu_maks3=ldc_tulu_maks3-ldc_tulu_maks4;
    set ldc_sots_maks4=Round((ldc_summa3+ldc_tulu_maks4)*ldc_sots_prop,2);
    set ldc_sots_maks3=ldc_sots_maks3-ldc_sots_maks4;
    update pa_erisood set pa_erisood.tulumaks = ldc_tulu_maks4,pa_erisood.sotsmaks = ldc_sots_maks4 where pa_erisood.id = li_id and pa_erisood.a_aasta = li_a_aasta;
    set li_last_tulu_id=li_id;
    set li_last_tulu_a_aasta=li_a_aasta
  end loop tasu_loop;
  close c_tasu;
  if ldc_tulu_maks3 <> 0 and li_last_tulu_id > 0 then
    update pa_erisood set pa_erisood.tulumaks = pa_erisood.tulumaks+ldc_tulu_maks3 where pa_erisood.id = li_last_tulu_id and pa_erisood.a_aasta = li_last_tulu_a_aasta
  end if;
  if ldc_sots_maks3 <> 0 and li_last_tulu_id > 0 then
    update pa_erisood set pa_erisood.sotsmaks = pa_erisood.sotsmaks+ldc_sots_maks3 where pa_erisood.id = li_last_tulu_id and pa_erisood.a_aasta = li_last_tulu_a_aasta
  end if;
  -- Lisa 6
  -- kuu tulumaksuga maksustatav summa
  select sum(pa_erisood.summa) into ldc_tulu_summa3 from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr left outer join lisalist on pa_erisood.lisanr=lisalist.lisanr left outer join skoobid on pa_yksus.skoop=skoobid.skoop where lisalist.objnr=60 and pa_erisood.t_aasta = a_t_aasta and pa_erisood.t_akuu = a_t_akuu and (li_tatk_skoop=0 or pa_yksus.skoop=a_skoop) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_tulu_summa3 is null then
    set ldc_tulu_summa3=0
  else 
    set ldc_tulu_summa3=Round(ldc_tulu_summa3,2)
  end if;
  -- tulumaks
  set ldc_tulu_maks3=Round(ldc_tulu_summa3*(ldc_tulu_protsent/(100-ldc_tulu_protsent)),2);
  -- prop
  if ldc_tulu_summa3 = 0 then
    set ldc_tulu_prop=0
  else
    set ldc_tulu_prop=ldc_tulu_maks3/ldc_tulu_summa3
  end if;
  -- maksu jagamine tasude ridadele
  set li_id=0;
  open c_tasu60;
  tasu60_loop: loop
    fetch next c_tasu60 into li_id,li_a_aasta,ldc_summa3;
    if sqlstate = err_notfound then
      leave tasu60_loop
    end if;
    set ldc_tulu_maks4=Round(ldc_summa3*ldc_tulu_prop,2);
    set ldc_tulu_maks3=ldc_tulu_maks3-ldc_tulu_maks4;
    update pa_erisood set pa_erisood.tulumaks = ldc_tulu_maks4 where pa_erisood.id = li_id and pa_erisood.a_aasta = li_a_aasta;
    set li_last_tulu_id=li_id;
    set li_last_tulu_a_aasta=li_a_aasta
  end loop tasu60_loop;
  close c_tasu60;
  if ldc_tulu_maks3 <> 0 and li_last_tulu_id > 0 then
    update pa_erisood set pa_erisood.tulumaks = pa_erisood.tulumaks+ldc_tulu_maks3 where pa_erisood.id = li_last_tulu_id and pa_erisood.a_aasta = li_last_tulu_a_aasta
  end if;
end