ALTER PROCEDURE "DBA"."ls_majakogus"( in a_alusmaksnr integer, in a_maksnr integer, in a_yksusnr integer, in a_kuup date )
BEGIN
  declare ll_id integer;
  declare ll_lapsnr integer;
  declare ldc_sum1 decimal(15,3);
  declare ls_info char(100);
  declare err_notfound exception for sqlstate value '02000';
  declare c_ls_maksud dynamic scroll cursor for
    select ls_maksud.id,
           ls_maksud.lapsnr
      from ls_maksud 
      where "ls_maksud"."maksnr" = a_maksnr and
            "ls_maksud"."yksusnr" = a_yksusnr and
            "ls_maksud"."akuup"<=a_kuup and
           ("ls_maksud"."lkuup" is null or "ls_maksud"."lkuup">=a_kuup);
  declare c_ls_alusmaksud dynamic scroll cursor for
    select ls_maksud.lapsnr,
           if ls_maksud.par1=0 then 1 else ls_maksud.par1 endif
      from ls_maksud
      where "ls_maksud"."maksnr" = a_alusmaksnr and
            "ls_maksud"."yksusnr" = a_yksusnr and
            "ls_maksud"."akuup"<=a_kuup and
           ("ls_maksud"."lkuup" is null or "ls_maksud"."lkuup">=a_kuup);
  declare c_ls_kogused dynamic scroll cursor for
    select ls_kogused.kogus,
           ls_kogused.maksuid
      from ls_kogused left outer join ls_maksud on ls_kogused.maksuid=ls_maksud.id
      where ls_maksud.maksnr=a_maksnr and
            "ls_maksud"."yksusnr" = a_yksusnr and
            Month(ls_kogused.kuup)=Month(a_kuup) and
            Years(ls_kogused.kuup)=Years(a_kuup) and
            ls_kogused.majakogus is null;
  select yhiklist.nimi into ls_info from ls_makslist left outer join yhiklist on ls_makslist.yhiknr=yhiklist.yhiknr where "ls_makslist"."maksnr" = a_alusmaksnr;
  delete gl_tr_universal;
  open c_ls_maksud;
  ls_maksud_loop: loop
    fetch next c_ls_maksud into ll_id,ll_lapsnr;
    if sqlstate = err_notfound then -- kui eof
      leave ls_maksud_loop
    end if;
    insert into gl_tr_universal (id,id2,sum1,"info") values (ll_id,ll_lapsnr,0,'')
  end loop ls_maksud_loop;
  close c_ls_maksud;
  open c_ls_alusmaksud;
  ls_alusmaksud_loop: loop
    fetch next c_ls_alusmaksud into ll_lapsnr, ldc_sum1;
    if sqlstate = err_notfound then -- kui eof
      leave ls_alusmaksud_loop
    end if;
    update "gl_tr_universal" set "gl_tr_universal"."sum1"="gl_tr_universal"."sum1"+ldc_sum1, "gl_tr_universal"."info"=ls_info where "gl_tr_universal"."id2"=ll_lapsnr
  end loop ls_alusmaksud_loop;
  close c_ls_alusmaksud;
  open c_ls_kogused;
  ls_kogused_loop: loop
    fetch next c_ls_kogused into ldc_sum1,ll_id;
    if sqlstate = err_notfound then -- kui eof
      leave ls_kogused_loop
    end if;
    update "gl_tr_universal" set "gl_tr_universal"."sum2"=if "gl_tr_universal"."sum2" is null then ldc_sum1 else "gl_tr_universal"."sum2"+ldc_sum1 endif where "gl_tr_universal"."id"=ll_id
  end loop ls_kogused_loop;
  close c_ls_kogused
END