ALTER PROCEDURE "DBA"."pr_koondbil_pikk"()
BEGIN
  declare li_id integer;
  declare ls_konto char(7);
  declare li_pikkus integer;
  declare ls_nimi char(100);
  declare ldc_summa numeric(15,2);
  declare ldc_sum1 numeric(15,2);
  declare ldc_tulem numeric(15,2);
  declare ldc_103500 numeric(15,2);
  declare ldc_203500 numeric(15,2);
  declare err_notfound exception for sqlstate value '02000';
  declare c_pr_pikkbilkir dynamic scroll cursor for select "kontlist"."konto","kontlist"."nimi" from kontlist where left(konto,1) in ('1','2') order by konto asc;
  delete from gl_tr_tabel;
  select sum(deebet-kreedit) into ldc_103500 from gl_pr_saldoandmik where left(gl_pr_saldoandmik.konto,6)='103500';
  if ldc_103500 is null then
    set ldc_103500=0
  end if;
  select sum(kreedit-deebet) into ldc_203500 from gl_pr_saldoandmik where left(gl_pr_saldoandmik.konto,6)='203500';
  if ldc_203500 is null then
    set ldc_203500=0
  end if;
  select sum(kreedit-deebet) into ldc_tulem from gl_pr_saldoandmik where left(gl_pr_saldoandmik.konto,1)=3 or left(gl_pr_saldoandmik.konto,1)=4 or left(gl_pr_saldoandmik.konto,1)=5 or left(gl_pr_saldoandmik.konto,1)=6 or left(gl_pr_saldoandmik.konto,1)=7;
  if ldc_tulem is null then
    set ldc_tulem=0
  end if;
  open c_pr_pikkbilkir;
  pr_pikkbilkir_loop: loop
    --  loe rida
    fetch next c_pr_pikkbilkir into ls_konto,ls_nimi;
    if sqlstate = err_notfound then -- kui eof
      leave pr_pikkbilkir_loop
    end if;
    set li_pikkus=Length(ls_konto);
    if li_pikkus>0 then
      set ldc_summa=0;
      set ldc_sum1=0;
      set li_id=1;
      if left(ls_konto,1)='1' then
        select sum(deebet-kreedit) into ldc_summa from gl_pr_saldoandmik where left(gl_pr_saldoandmik.konto,li_pikkus)=ls_konto;
        if ldc_summa is null then
          set ldc_summa=0
        end if;
        if left(ls_konto,li_pikkus)=left('103500',li_pikkus) and li_pikkus<6 then
          set ldc_summa=ldc_summa-ldc_103500
        end if;
        if ls_konto='103500' then
          set li_id=4650
        end if;
      else
        if ls_konto='2' then
          select sum(kreedit) into ldc_sum1 from gl_pr_saldoandmik where left(gl_pr_saldoandmik.konto,1)='2';
          if ldc_sum1 is null then
            set ldc_sum1=0
          end if;
          set ldc_summa=ldc_sum1-ldc_103500;
          select sum(deebet) into ldc_sum1 from gl_pr_saldoandmik where left(gl_pr_saldoandmik.konto,1)='7';
          if ldc_sum1 is null then
            set ldc_sum1=0
          end if;
          set ldc_summa=ldc_summa-ldc_sum1;
        elseif ls_konto='299000' then
          set ldc_summa=ldc_tulem
        else
          select sum(kreedit-deebet) into ldc_summa from gl_pr_saldoandmik where left(gl_pr_saldoandmik.konto,li_pikkus)=ls_konto;
          if ldc_summa is null then
            set ldc_summa=0
          end if;
          if left(ls_konto,li_pikkus)=left('203500',li_pikkus) and li_pikkus<6 then
            set ldc_summa=ldc_summa-ldc_203500
          end if;
          if ls_konto='29' and li_pikkus=2 then
            set ldc_summa=ldc_summa+ldc_tulem;
          end if;
        end if;
        if ls_konto='203500' then
          set li_id=4660
        elseif left(ls_konto,2)='29' then
          set li_id=4670
        end if;
      end if;
      if ldc_summa is not null and ldc_summa<>0 then
        insert into gl_tr_tabel("id",kood,nimi,ldeeb) values(li_id,ls_konto,ls_nimi,ldc_summa)
      end if
    end if;
  end loop pr_pikkbilkir_loop;
  close c_pr_pikkbilkir;
  if ldc_103500<>ldc_203500 then
    insert into gl_tr_tabel("id",kood,nimi,ldeeb) values(4640,'','Eelarvesse kuuluv netovara',ldc_203500-ldc_103500)
  end if

 END