ALTER PROCEDURE "dba"."mt_stl_koond"()
begin
  declare ll_id integer;
  declare ld_kuup date;
  declare ll_objnr integer;
  declare ld_arvekuup date;
  declare ls_arvenr char(20);
  declare ll_asutnr integer;
  declare ldc_summa decimal(15,2);
  declare ldc_summa2 decimal(15,2);
  declare ldc_kogus decimal(15,3);
  declare ldc_hind decimal(15,3);
  declare ldc_kb_protsent decimal(10,3);
  declare ls_sisu char(250);
  declare ls_kood char(20);
  declare ls_taiendus char(200);
  declare ls_koktaiendus char(600);
  declare ls_saaja char(101);
  declare ls_koksaaja char(300);
  declare ls_yhik char(12);
  declare ll_skoop integer;
  declare err_notfound exception for sqlstate value '02000';
  declare c_gl_2 dynamic scroll cursor for select gl_2.id,gl_2.kuup from gl_2;
-- sisse ja valja plussiga
--           if mt_kanne.sisse=0 then mt_kanne.summa else (-1)*mt_kanne.summa endif,
--           if mt_kanne.sisse=0 then mt_kanne.summa2 else (-1)*mt_kanne.summa2 endif,
--           if mt_kanne.kogus is null then 0 else if mt_kanne.sisse=0 then mt_kanne.kogus else (-1)*mt_kanne.kogus endif endif,
  declare c_mt_kanne dynamic scroll cursor for
    select mt_kanne."id",
           mt_kanne.summa,
           mt_kanne.summa2,
           if mt_kanne.kogus is null then 0 else mt_kanne.kogus endif,
           if mt_kanne.hind is null then 0 else mt_kanne.hind endif,
           tootlist.toot_nr,
           trim(tootlist.nimi + ' ' + tootlist."info"),
           mt_kanne."info",
           if tootlist.yhiknr is null then '' else yhiklist.nimi endif,
           if mt_kanne.kbnr is null then -1 else kblist.protsent endif
      from mt_kanne left outer join mt_yksus on mt_kanne.yksusnr=mt_yksus.yksusnr left outer join mt_alus on mt_kanne.alusnr = mt_alus.alusnr left outer join tootlist on mt_kanne.tootnr=tootlist.tootnr left outer join yhiklist on tootlist.yhiknr = yhiklist.yhiknr left outer join kblist on mt_kanne.kbnr = kblist.kbnr
      where mt_yksus.skoop=ll_skoop and mt_kanne.objnr = ll_objnr and mt_kanne.arvekuup = ld_arvekuup and mt_kanne.arvenr = ls_arvenr and mt_kanne.asutnr=ll_asutnr;
  delete from gl_tr_arve;
  open c_gl_2;
  gl_2_loop: loop
    fetch next c_gl_2 into ll_id,ld_kuup;
    if sqlstate = err_notfound then -- kui eof
      leave gl_2_loop
    end if;
    select mt_kanne.objnr,mt_kanne.arvekuup,mt_kanne.arvenr,mt_kanne.asutnr,mt_yksus.skoop into ll_objnr,ld_arvekuup,ls_arvenr,ll_asutnr,ll_skoop from mt_kanne left outer join mt_yksus on mt_kanne.yksusnr=mt_yksus.yksusnr left outer join mt_obj on mt_kanne.objnr=mt_obj.objnr where mt_kanne."id" = ll_id and mt_kanne.kuup = ld_kuup;
    if not exists(select 1 from gl_tr_arve where gl_tr_arve.objnr=ll_objnr and gl_tr_arve.arvekuup=ld_arvekuup and gl_tr_arve.arvenr=ls_arvenr and gl_tr_arve.asutnr=ll_asutnr) then
      set ls_koktaiendus = '';
      open c_mt_kanne;
      mt_kanne_loop: loop
        --  loe arvelduste kanne
        fetch next c_mt_kanne into ll_id,ldc_summa,ldc_summa2,ldc_kogus,ldc_hind,ls_kood,ls_sisu,ls_taiendus,ls_yhik,ldc_kb_protsent;
         if sqlstate = err_notfound then -- kui eof
          leave mt_kanne_loop
        end if;
        if exists(select 1 from gl_tr_arve where gl_tr_arve.objnr=ll_objnr and gl_tr_arve.arvekuup=ld_arvekuup and gl_tr_arve.arvenr=ls_arvenr and gl_tr_arve.asutnr=ll_asutnr and gl_tr_arve.kood=ls_kood and gl_tr_arve.sisu=ls_sisu and gl_tr_arve.yhik=ls_yhik and gl_tr_arve.hind=ldc_hind and gl_tr_arve.kb_protsent=ldc_kb_protsent and gl_tr_arve.ls_laps_info=ls_sisu) then
          update gl_tr_arve set gl_tr_arve.summa = gl_tr_arve.summa + ldc_summa, gl_tr_arve.summa2 = gl_tr_arve.summa2 + ldc_summa2, gl_tr_arve.kogus = gl_tr_arve.kogus + ldc_kogus where gl_tr_arve.objnr=ll_objnr and gl_tr_arve.arvekuup=ld_arvekuup and gl_tr_arve.arvenr=ls_arvenr and gl_tr_arve.asutnr=ll_asutnr and gl_tr_arve.sisu=ls_sisu and gl_tr_arve.yhik=ls_yhik and gl_tr_arve.hind=ldc_hind and gl_tr_arve.kb_protsent=ldc_kb_protsent and gl_tr_arve.ls_laps_info=ls_sisu
        else
          insert into gl_tr_arve (objnr,arvekuup,arvenr,asutnr,skoop,taiendus,id,summa,summa2,kogus,hind,kb_protsent,kood,sisu,yhik,ls_laps_info)
            values (ll_objnr,ld_arvekuup,ls_arvenr,ll_asutnr,ll_skoop,'',ll_id,ldc_summa,ldc_summa2,ldc_kogus,ldc_hind,ldc_kb_protsent,ls_kood,ls_sisu,ls_yhik,ls_sisu)
        end if;
        if Length(ls_taiendus) > 0 then
          if locate(ls_koktaiendus,ls_taiendus,1) = 0 then
            set ls_koktaiendus=Left(Trim(ls_koktaiendus+' '+ls_taiendus),250)
          end if
        end if;
      end loop mt_kanne_loop;
      close c_mt_kanne;
      update gl_tr_arve set gl_tr_arve.taiendus = ls_koktaiendus where gl_tr_arve.objnr=ll_objnr and gl_tr_arve.arvekuup=ld_arvekuup and gl_tr_arve.arvenr=ls_arvenr and gl_tr_arve.asutnr=ll_asutnr
    end if
  end loop gl_2_loop;
  close c_gl_2;
end