ALTER PROCEDURE "DBA"."dk_koond1"(inout id_skoop integer,in id_obj integer,in id_paring integer,in peralg date,in perlop date, inout a_valver smallint default 1, in gl_id_user integer default 32767)
begin
  declare li_i integer;
  declare li_sisse integer;
  declare ld_kuup date;
  declare ldc_summa decimal(15,2);
  declare ldc_summa2 decimal(15,2);
  declare li_asutnr integer;
  declare li_kulnr integer;
  declare li_talnr integer;
  declare li_artnr integer;
  declare li_kbartnr integer;
  declare li_tulnr integer;
  declare li_objectnr integer;
  declare li_osaknr integer;
  declare li_subjectnr integer;
  declare li_suundnr integer;
  declare li_paring integer;
  declare li_saldod integer;
  declare err_notfound exception for sqlstate value '02000';
  declare c_dk_kanne dynamic scroll cursor for select dk_kanne.sisse,dk_kanne.kuup,dk_kanne.summa,dk_kanne.summa2,dk_kanne.asutnr,if dk_kanne.kulnr is null then 0 else dk_kanne.kulnr endif,if dk_kanne.talnr is null then 0 else dk_kanne.talnr endif,if dk_kanne.artnr is null then 0 else dk_kanne.artnr endif as ab_artnr,if kblist.artnr is null then ab_artnr else kblist.artnr endif,if dk_kanne.tulnr is null then 0 else dk_kanne.tulnr endif,if dk_kanne.objectnr is null then 0 else dk_kanne.objectnr endif,if dk_kanne.osaknr is null then 0 else dk_kanne.osaknr endif,if dk_kanne.subjectnr is null then 0 else dk_kanne.subjectnr endif,if dk_kanne.suundnr is null then 0 else dk_kanne.suundnr endif from
      dk_kanne left outer join gl_1 on(dk_kanne.id = gl_1.id) and(dk_kanne.kuup = gl_1.kuup) key left outer join dk_obj,dk_kanne key left outer join kblist where
      ((dk_kanne.id = gl_1.id) and(dk_kanne.kuup = gl_1.kuup) or id_paring = 0) and(dk_kanne.objnr = id_obj or id_obj = 0) and(dk_obj.skoop = id_skoop or id_skoop = 0) and dk_kanne.asutnr > 0 and dk_kanne.kuup <= perlop;
  delete from gl_dk_koond;
  delete from gl_pr_koond;
  select tr_dk_koond1_saldod into li_saldod from t_user where id_user=gl_id_user;
  if id_obj > 0 then
    select dk_obj.skoop into id_skoop from dk_obj where dk_obj.objnr = id_obj
  end if;
  set li_paring=set_gl_1(id_paring);
  open c_dk_kanne;
  dk_kanne_loop: loop
    --  loe arvelduste kanne
    fetch next c_dk_kanne into li_sisse,ld_kuup,ldc_summa,ldc_summa2,li_asutnr,li_kulnr,li_talnr,li_artnr,li_kbartnr,li_tulnr,li_objectnr,li_osaknr,li_subjectnr,li_suundnr;
    if sqlstate = err_notfound then -- kui eof
      leave dk_kanne_loop
    end if;
    set li_i=0;
    while li_i < 2 loop
      if li_i = 1 then
        set ldc_summa=ldc_summa2;
        set li_artnr=li_kbartnr
      end if;
      if not(ldc_summa is null or ldc_summa = 0) then
        -- kas asutus ei eksisteeri koondis
        if not exists(select 1 from gl_dk_koond where gl_dk_koond.asutnr = li_asutnr and gl_dk_koond.kulnr = li_kulnr and gl_dk_koond.talnr = li_talnr and gl_dk_koond.artnr = li_artnr and gl_dk_koond.tulnr = li_tulnr and gl_dk_koond.objectnr = li_objectnr and gl_dk_koond.osaknr = li_osaknr and gl_dk_koond.subjectnr = li_subjectnr and gl_dk_koond.suundnr = li_suundnr) then
          insert into gl_dk_koond( asutnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,adeeb,akree,deeb,kree,ldeeb,lkree,x1,x2,x3,x4,sisse) values( li_asutnr,li_kulnr,li_talnr,li_artnr,li_tulnr,li_objectnr,li_osaknr,li_subjectnr,li_suundnr,0,0,0,0,0,0,0,0,0,0,0) 
        end if;
        -- summeerimine
        if ld_kuup < peralg then
          if li_sisse > 0 then
            update gl_dk_koond set adeeb = adeeb+ldc_summa,sisse = sisse+1 where gl_dk_koond.asutnr = li_asutnr and gl_dk_koond.kulnr = li_kulnr and gl_dk_koond.talnr = li_talnr and gl_dk_koond.artnr = li_artnr and gl_dk_koond.tulnr = li_tulnr and gl_dk_koond.objectnr = li_objectnr and gl_dk_koond.osaknr = li_osaknr and gl_dk_koond.subjectnr = li_subjectnr and gl_dk_koond.suundnr = li_suundnr
          else
            update gl_dk_koond set akree = akree+ldc_summa,sisse = sisse-1 where gl_dk_koond.asutnr = li_asutnr and gl_dk_koond.kulnr = li_kulnr and gl_dk_koond.talnr = li_talnr and gl_dk_koond.artnr = li_artnr and gl_dk_koond.tulnr = li_tulnr and gl_dk_koond.objectnr = li_objectnr and gl_dk_koond.osaknr = li_osaknr and gl_dk_koond.subjectnr = li_subjectnr and gl_dk_koond.suundnr = li_suundnr
          end if
        else
          if li_sisse > 0 then
            update gl_dk_koond set deeb = deeb+ldc_summa,sisse = sisse+1 where gl_dk_koond.asutnr = li_asutnr and gl_dk_koond.kulnr = li_kulnr and gl_dk_koond.talnr = li_talnr and gl_dk_koond.artnr = li_artnr and gl_dk_koond.tulnr = li_tulnr and gl_dk_koond.objectnr = li_objectnr and gl_dk_koond.osaknr = li_osaknr and gl_dk_koond.subjectnr = li_subjectnr and gl_dk_koond.suundnr = li_suundnr
          else
            update gl_dk_koond set kree = kree+ldc_summa,sisse = sisse-1 where gl_dk_koond.asutnr = li_asutnr and gl_dk_koond.kulnr = li_kulnr and gl_dk_koond.talnr = li_talnr and gl_dk_koond.artnr = li_artnr and gl_dk_koond.tulnr = li_tulnr and gl_dk_koond.objectnr = li_objectnr and gl_dk_koond.osaknr = li_osaknr and gl_dk_koond.subjectnr = li_subjectnr and gl_dk_koond.suundnr = li_suundnr
          end if
        end if
      end if;
      set li_i=li_i+1
    end loop
  end loop dk_kanne_loop;
  close c_dk_kanne;
  call dk_koond1_kp(id_skoop,id_obj,id_paring,peralg,perlop,a_valver);
  -- loppjaagid
  update gl_dk_koond set adeeb = adeeb-akree,akree = akree-adeeb;
  update gl_dk_koond set adeeb = 0 where gl_dk_koond.adeeb < 0;
  update gl_dk_koond set akree = 0 where gl_dk_koond.akree < 0;
  update gl_dk_koond set ldeeb = adeeb-akree+deeb-kree,lkree = akree-adeeb+kree-deeb;
  update gl_dk_koond set ldeeb = 0 where gl_dk_koond.ldeeb < 0;
  update gl_dk_koond set lkree = 0 where gl_dk_koond.lkree < 0;
  delete from gl_dk_koond where adeeb = 0 and akree = 0 and deeb = 0 and kree = 0;
  -- kustuta jäägid, kus asutus/isik on li_saldod
  if li_saldod=1 then
    insert into gl_pr_koond( asutnr,deeb)
        select gl_dk_koond.asutnr,sum(gl_dk_koond.ldeeb-gl_dk_koond.lkree)
         from gl_dk_koond
         group by gl_dk_koond.asutnr;
    delete gl_dk_koond from gl_dk_koond left outer join gl_pr_koond on gl_dk_koond.asutnr=gl_pr_koond.asutnr where gl_pr_koond.deeb=0;
  end if;
end