ALTER PROCEDURE "dba"."pa_arvuta_erisood_lisa5"(in a_t_akuu numeric(2),in a_t_aasta numeric(4), in a_skoop integer, in a_skoopkood char(16) default '')
begin
  -- kuu maksustatav summa
  declare ldc_tulu_summa1 numeric(15,2);
  -- aasta maksustatav summa
  declare ldc_tulu_summa3 numeric(15,2);
  declare ldc_sots_summa3 numeric(15,2);
  -- kuu maks
  declare ldc_tulu_maks1 numeric(15,2);
  -- varasem maks
  declare ldc_tulu_maks2 numeric(15,2);
  -- aasta maks
  declare ldc_tulu_maks3 numeric(15,2);
  -- jooksva rea maks
  declare ldc_tulu_maks4 numeric(15,2);
  -- maksu proportsioon
  declare ldc_tulu_prop numeric(15,6);
  -- viimane arvutatav rida
  declare li_last_tulu_id integer;
  declare li_last_tulu_a_aasta integer;
  -- ridade summad
  declare ldc_rida5 numeric(15,2);
  declare ldc_rida7 numeric(15,2);
  -- maarad
  declare ldc_tulu_protsent numeric(10,3);
  declare ldc_vaba_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_erisood1 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=50 and lisalist.ridanr='1' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  declare c_erisood11 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=50 and lisalist.ridanr='3' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  declare c_erisood12 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=50 and lisalist.ridanr='13' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  declare c_erisood2 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=50 and lisalist.ridanr='23' 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 or a_skoop=0) 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_vaba_protsent=get_maar(16,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;
--  message 'osa 1.';
--  message 'kuu tulumaksuga maksustatav summa';
  set ldc_tulu_summa1=0;
  select sum(pa_erisood.summa) into ldc_tulu_summa1 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=50 and lisalist.ridanr='1' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_tulu_summa1 is null then
    set ldc_tulu_summa1=0
  else 
    set ldc_tulu_summa1=Round(ldc_tulu_summa1,2)
  end if;
--  message ldc_tulu_summa1;
--  message 'kuu maks';
  set ldc_tulu_maks1=Round(ldc_tulu_summa1*(ldc_tulu_protsent/(100-ldc_tulu_protsent)),2);
--  message ldc_tulu_maks1;
  -- prop
  if ldc_tulu_summa1 = 0 then
    set ldc_tulu_prop=0
  else
    set ldc_tulu_prop=ldc_tulu_maks1/ldc_tulu_summa1
  end if;
--message ldc_tulu_prop;
  -- maksu jagamine tasude ridadele
  set li_id=0;
  set li_last_tulu_id=0;
  open c_erisood1;
  erisood1_loop: loop
    fetch next c_erisood1 into li_id,li_a_aasta,ldc_summa3;
    if sqlstate = err_notfound then
      leave erisood1_loop
    end if;
--message ldc_summa3;
    set ldc_tulu_maks4=Round(ldc_summa3*ldc_tulu_prop,2);
    set ldc_tulu_maks1=ldc_tulu_maks1-ldc_tulu_maks4;
--message ldc_tulu_maks1;
    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 erisood1_loop;
  close c_erisood1;
  if ldc_tulu_maks1 <> 0 and li_last_tulu_id > 0 then
    update pa_erisood set pa_erisood.tulumaks = pa_erisood.tulumaks+ldc_tulu_maks1 where pa_erisood.id = li_last_tulu_id and pa_erisood.a_aasta = li_last_tulu_a_aasta
  end if;
  -- message 'osa 1.1';
  -- message 'kuu tulumaksuga maksustatav summa';
  set ldc_tulu_summa1=0;
  select sum(pa_erisood.summa) into ldc_tulu_summa1 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=50 and lisalist.ridanr='3' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_tulu_summa1 is null then
    set ldc_tulu_summa1=0
  else 
    set ldc_tulu_summa1=Round(ldc_tulu_summa1,2)
  end if;
  -- message ldc_tulu_summa3;
  -- message 'aasta tulumaksuga maksustatav summa';
  set ldc_tulu_summa3=0;
  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=50 and lisalist.ridanr='3' 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 or a_skoop=0) 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;
  -- message ldc_tulu_summa3;
  -- message 'aasta sots.maksuga maksustatavad valjamaksed ja 3 protsenti';
  set ldc_sots_summa3=0;
  select sum(pa_tasu.summa) into ldc_sots_summa3 from pa_tasu left outer join pa_yksus on pa_tasu.yksusnr = pa_yksus.yksusnr left outer join pa_taslist on pa_tasu.tas_s = pa_taslist.tas_s left outer join skoobid on pa_yksus.skoop=skoobid.skoop where pa_taslist.smrk = 1 and pa_taslist.sisse = 0 and pa_tasu.palgaleht is not null and(pa_tasu.t_aasta = a_t_aasta and pa_tasu.t_akuu <= a_t_akuu) and (li_tatk_skoop=0 or pa_yksus.skoop=a_skoop or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_sots_summa3 is null then
    set ldc_sots_summa3=0;
    set ldc_rida5=0
  else 
    set ldc_sots_summa3=Round(ldc_sots_summa3,2);
    set ldc_rida5=Round(ldc_sots_summa3*ldc_vaba_protsent/100,2)
  end if;
  -- message ldc_sots_summa3;
  -- message ldc_rida5;
  -- message 'kasum';
  set ldc_rida7=0;
  select sum(pa_erisood.summa) into ldc_rida7 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=50 and lisalist.ridanr='6' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_rida7 is null then
    set ldc_rida7=0
  else 
    set ldc_rida7=Round(ldc_rida7*0.1,2)
  end if;
  -- message ldc_rida7;
  -- message 'rida 8';
  -- message ldc_tulu_summa3;
  if ldc_tulu_summa3-ldc_rida5 < ldc_tulu_summa3-ldc_rida7 then
    set ldc_tulu_summa3=ldc_tulu_summa3-ldc_rida5
  else
    set ldc_tulu_summa3=ldc_tulu_summa3-ldc_rida7
  end if;
  if ldc_tulu_summa3 < 0 then
    set ldc_tulu_summa3=0
  end if;
  -- message ldc_tulu_summa3;
  -- message 'aasta tulumaks';
  set ldc_tulu_maks3=Round(ldc_tulu_summa3*(ldc_tulu_protsent/(100-ldc_tulu_protsent)),2);
  -- message ldc_tulu_maks3;
  -- message 'varasem maks';
  set ldc_tulu_maks2=0;
  select sum(pa_erisood.tulumaks) into ldc_tulu_maks2 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=50 and lisalist.ridanr='3' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_tulu_maks2 is null then
    set ldc_tulu_maks2=0
  else 
    set ldc_tulu_maks2=Round(ldc_tulu_maks2,2)
  end if;
  -- message ldc_tulu_maks2;
  -- message 'kuu maks';
  set ldc_tulu_maks1=ldc_tulu_maks3-ldc_tulu_maks2;
  -- message ldc_tulu_maks1;
  -- prop
  if ldc_tulu_summa1 = 0 then
    set ldc_tulu_prop=0
  else
    set ldc_tulu_prop=ldc_tulu_maks1/ldc_tulu_summa1
  end if;
  -- maksu jagamine tasude ridadele
  set li_id=0;
  set li_last_tulu_id=0;
  open c_erisood11;
  erisood11_loop: loop
    fetch next c_erisood11 into li_id,li_a_aasta,ldc_summa3;
    if sqlstate = err_notfound then
      leave erisood11_loop
    end if;
    set ldc_tulu_maks4=Round(ldc_summa3*ldc_tulu_prop,2);
    set ldc_tulu_maks1=ldc_tulu_maks1-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 erisood11_loop;
  close c_erisood11;
  if ldc_tulu_maks1 <> 0 and li_last_tulu_id > 0 then
    update pa_erisood set pa_erisood.tulumaks = pa_erisood.tulumaks+ldc_tulu_maks1 where pa_erisood.id = li_last_tulu_id and pa_erisood.a_aasta = li_last_tulu_a_aasta
  end if;
  -- message 'osa 1.2';
  -- message 'kuu tulumaksuga maksustatav summa';
  set ldc_tulu_summa1=0;
  select sum(pa_erisood.summa) into ldc_tulu_summa1 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=50 and lisalist.ridanr='13' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_tulu_summa1 is null then
    set ldc_tulu_summa1=0
  else 
    set ldc_tulu_summa1=Round(ldc_tulu_summa1,2)
  end if;
  -- message ldc_tulu_summa3;
  -- message 'aasta tulumaksuga maksustatav summa';
  set ldc_tulu_summa3=0;
  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=50 and lisalist.ridanr='13' 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 or a_skoop=0) 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;
  -- message ldc_tulu_summa3;
  -- message 'aasta sots.maksuga x protsenti';
  set ldc_rida5=Round(ldc_sots_summa3*ldc_vaba_protsent/100,2);
  -- message ldc_rida5;
  -- message 'maar';
  set ldc_rida7=a_t_akuu*50+ldc_rida5;
  -- message ldc_rida7;
  -- message 'rida 17';
  set ldc_tulu_summa3=ldc_tulu_summa3-ldc_rida7;
  if ldc_tulu_summa3 < 0 then
    set ldc_tulu_summa3=0
  end if;
  -- message ldc_tulu_summa3;
  -- message 'aasta tulumaks';
  set ldc_tulu_maks3=Round(ldc_tulu_summa3*(ldc_tulu_protsent/(100-ldc_tulu_protsent)),2);
  -- message ldc_tulu_maks3;
  -- message 'varasem maks';
  set ldc_tulu_maks2=0;
  select sum(pa_erisood.tulumaks) into ldc_tulu_maks2 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=50 and lisalist.ridanr='13' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_tulu_maks2 is null then
    set ldc_tulu_maks2=0
  else 
    set ldc_tulu_maks2=Round(ldc_tulu_maks2,2)
  end if;
  -- message ldc_tulu_maks2;
  -- message 'kuu maks';
  set ldc_tulu_maks1=ldc_tulu_maks3-ldc_tulu_maks2;
  -- message ldc_tulu_maks1;
  // prop
  if ldc_tulu_summa1 = 0 then
    set ldc_tulu_prop=0
  else
    set ldc_tulu_prop=ldc_tulu_maks1/ldc_tulu_summa1
  end if;
  -- maksu jagamine tasude ridadele
  set li_id=0;
  set li_last_tulu_id=0;
  open c_erisood12;
  erisood12_loop: loop
    fetch next c_erisood12 into li_id,li_a_aasta,ldc_summa3;
    if sqlstate = err_notfound then
      leave erisood12_loop
    end if;
    set ldc_tulu_maks4=Round(ldc_summa3*ldc_tulu_prop,2);
    set ldc_tulu_maks1=ldc_tulu_maks1-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 erisood12_loop;
  close c_erisood12;
  if ldc_tulu_maks1 <> 0 and li_last_tulu_id > 0 then
    update pa_erisood set pa_erisood.tulumaks = pa_erisood.tulumaks+ldc_tulu_maks1 where pa_erisood.id = li_last_tulu_id and pa_erisood.a_aasta = li_last_tulu_a_aasta
  end if;
  -- message 'osa 2.';
  -- message 'kuu tulumaksuga maksustatav summa';
  set ldc_tulu_summa1=0;
  select sum(pa_erisood.summa) into ldc_tulu_summa1 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=50 and lisalist.ridanr='23' 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 or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood;
  if ldc_tulu_summa1 is null then
    set ldc_tulu_summa1=0
  else 
    set ldc_tulu_summa1=Round(ldc_tulu_summa1,2)
  end if;
  -- message ldc_tulu_summa1;
  -- message 'kuu maks';
  set ldc_tulu_maks1=Round(ldc_tulu_summa1*(ldc_tulu_protsent/(100-ldc_tulu_protsent)),2);
  -- message ldc_tulu_maks1;
  -- prop
  if ldc_tulu_summa1 = 0 then
    set ldc_tulu_prop=0
  else
    set ldc_tulu_prop=ldc_tulu_maks1/ldc_tulu_summa1
  end if;
  -- maksu jagamine tasude ridadele
  set li_id=0;
  set li_last_tulu_id=0;
  open c_erisood2;
  erisood2_loop: loop
    fetch next c_erisood2 into li_id,li_a_aasta,ldc_summa3;
    if sqlstate = err_notfound then
      leave erisood2_loop
    end if;
    set ldc_tulu_maks4=Round(ldc_summa3*ldc_tulu_prop,2);
    set ldc_tulu_maks1=ldc_tulu_maks1-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 erisood2_loop;
  close c_erisood2;
  if ldc_tulu_maks1 <> 0 and li_last_tulu_id > 0 then
    update pa_erisood set pa_erisood.tulumaks = pa_erisood.tulumaks+ldc_tulu_maks1 where pa_erisood.id = li_last_tulu_id and pa_erisood.a_aasta = li_last_tulu_a_aasta
  end if;
end