CREATE PROCEDURE "DBA"."pr_koond_tulkul_tasakaal"(in a_skoopkood char(16),in a_skoop integer,in id_paring integer,in peralg date,in perlop date)
begin
  declare li_paring integer;
  declare err_notfound exception for sqlstate value '02000';
  delete from gl_pr_kanne;
  delete from gl_dk_koond;
  delete from gl_pr_koond;
  set li_paring=set_gl_1(id_paring);
  insert into gl_pr_kanne(deeb,kree,artnr,summa)
    select pr_kanne.deeb,
           pr_kanne.kree,
           if pr_kanne.artnr is null and pr_kanne.tulnr is null then 0 else 1 endif as pr_artnr,
           sum(pr_kanne.summa)
      from pr_kanne left outer join gl_1 on(pr_kanne."id" = gl_1."id") and(pr_kanne.kuup = gl_1.kuup)
                    left outer join skoobid on pr_kanne.skoop=skoobid.skoop
      where (((pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup)) or id_paring = 0) and
            (Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and(pr_kanne.skoop=a_skoop or a_skoop=0)) and
            (pr_kanne.kuup>=peralg and pr_kanne.kuup<=perlop)
      group by pr_kanne.deeb,pr_kanne.kree,pr_artnr;
  -- bilansi kontod
  insert into gl_dk_koond(asutnr,konto,deeb)
    select 1,Left(gl_pr_kanne.deeb,2) as pr_konto,sum(gl_pr_kanne.summa)
      from gl_pr_kanne left outer join kontlist on gl_pr_kanne.deeb=kontlist.konto
      where gl_pr_kanne.deeb is not null and
            kontlist.bilvalkon=0 and kontlist.kulukonto=0
      group by pr_konto;
  insert into gl_dk_koond(asutnr,konto,kree)
    select 1,Left(gl_pr_kanne.kree,2) as pr_konto,sum(gl_pr_kanne.summa)
      from gl_pr_kanne left outer join kontlist on gl_pr_kanne.kree=kontlist.konto
      where gl_pr_kanne.kree is not null and
            kontlist.bilvalkon=0 and kontlist.kulukonto=0
      group by pr_konto;
  -- tulu/kulu kontod liigiga
  insert into gl_dk_koond(asutnr,konto,deeb)
    select 2,Left(gl_pr_kanne.deeb,2) as pr_konto,sum(gl_pr_kanne.summa)
      from gl_pr_kanne left outer join kontlist on gl_pr_kanne.deeb=kontlist.konto
      where gl_pr_kanne.deeb is not null and
            kontlist.bilvalkon=0 and kontlist.kulukonto=1 and gl_pr_kanne.artnr=1
      group by pr_konto;
  insert into gl_dk_koond(asutnr,konto,kree)
    select 2,Left(gl_pr_kanne.kree,2) as pr_konto,sum(gl_pr_kanne.summa)
      from gl_pr_kanne left outer join kontlist on gl_pr_kanne.kree=kontlist.konto
      where gl_pr_kanne.kree is not null and
            kontlist.bilvalkon=0 and kontlist.kulukonto=1 and gl_pr_kanne.artnr=1
      group by pr_konto;
  -- tulu/kulu kontod liigita
  insert into gl_dk_koond(asutnr,konto,deeb)
    select 3,Left(gl_pr_kanne.deeb,2) as pr_konto,sum(gl_pr_kanne.summa)
      from gl_pr_kanne left outer join kontlist on gl_pr_kanne.deeb=kontlist.konto
      where gl_pr_kanne.deeb is not null and
            kontlist.bilvalkon=0 and kontlist.kulukonto=1 and gl_pr_kanne.artnr=0
      group by pr_konto;
  insert into gl_dk_koond(asutnr,konto,kree)
    select 3,Left(gl_pr_kanne.kree,2) as pr_konto,sum(gl_pr_kanne.summa)
      from gl_pr_kanne left outer join kontlist on gl_pr_kanne.kree=kontlist.konto
      where gl_pr_kanne.kree is not null and
            kontlist.bilvalkon=0 and kontlist.kulukonto=1 and gl_pr_kanne.artnr=0
      group by pr_konto;
  -- koond
  insert into gl_pr_koond( asutnr,konto,deeb,kree)
    select gl_dk_koond.asutnr,gl_dk_koond.konto,sum(gl_dk_koond.deeb),sum(gl_dk_koond.kree)
      from gl_dk_koond
      group by gl_dk_koond.asutnr,gl_dk_koond.konto;
  -- loppjaagid
  update gl_pr_koond set sdeeb=deeb-kree,skree=kree-deeb;
  update gl_pr_koond set sdeeb=0 where gl_pr_koond.sdeeb<0;
  update gl_pr_koond set skree=0 where gl_pr_koond.skree<0;
end