ALTER PROCEDURE "dba"."pr_koondbil"()
begin
  declare li_bilnr integer;
  declare li_bilnr1 integer;
  declare li_bilnr2 integer;
  declare ldc_summa decimal(20,6);
  declare ldc_adeeb decimal(20,6);
  declare ldc_akree decimal(20,6);
  declare ldc_ldeeb decimal(20,6);
  declare ldc_lkree decimal(20,6);
  declare ls_eriline varchar(50);
  declare ls_bil1 char(2);
  declare ls_bil2 char(2);
  declare ls_bil3 char(2);
  declare ls_bil4 char(2);
  declare li_level integer;
  declare err_notfound exception for sqlstate value '02000';
  declare c_gl_pr_koond dynamic scroll cursor for select kontlist.bilnr,kontlist.bilnr2,gl_pr_koond.adeeb,gl_pr_koond.akree,gl_pr_koond.ldeeb,gl_pr_koond.lkree from
      gl_pr_koond left outer join kontlist on gl_pr_koond.konto = kontlist.konto;
  declare c_pr_bil dynamic scroll cursor for select pr_bil.bilnr,pr_bil.eriline from pr_bil;
  declare c_pr_koondbil dynamic scroll cursor for select gl_pr_koondbil.bilnr,gl_pr_koondbil.bil1,gl_pr_koondbil.bil2,gl_pr_koondbil.bil3,gl_pr_koondbil.bil4 from gl_pr_koondbil order by gl_pr_koondbil.bil1 asc,gl_pr_koondbil.bil2 asc,gl_pr_koondbil.bil3 asc,gl_pr_koondbil.bil4 asc;
  delete from gl_pr_koondbil;
  insert into gl_pr_koondbil( bilnr,aktpas,bil1,bil2,bil3,bil4,nimi,eriline,summa1,summa2) select pr_bil.bilnr,pr_bil.aktpas,pr_bil.bil1,pr_bil.bil2,pr_bil.bil3,pr_bil.bil4,pr_bil.nimi,pr_bil.eriline,0,0 from pr_bil;
  open c_gl_pr_koond;
  pr_koond_loop: loop
    --  loe pearaamatu koondi rida
    fetch next c_gl_pr_koond into li_bilnr1,li_bilnr2,ldc_adeeb,ldc_akree,ldc_ldeeb,ldc_lkree;
    if sqlstate = err_notfound then -- kui eof
      leave pr_koond_loop
    end if;
    if li_bilnr1 is null then
      set li_bilnr1=0
    end if;
    if li_bilnr2 is null then
      set li_bilnr2=0
    end if;
    if li_bilnr1 > 0 or li_bilnr2 > 0 then
      if ldc_adeeb > 0 or ldc_akree > 0 then
        if ldc_adeeb > ldc_akree then
          set li_bilnr=li_bilnr1;
          if li_bilnr = 0 then
            set li_bilnr=li_bilnr2
          end if
        else
          set li_bilnr=li_bilnr2;
          if li_bilnr = 0 then
            set li_bilnr=li_bilnr1
          end if
        end if;
        update gl_pr_koondbil set summa1 = summa1+ldc_akree-ldc_adeeb where gl_pr_koondbil.bilnr = li_bilnr and gl_pr_koondbil.aktpas = 'P';
        update gl_pr_koondbil set summa1 = summa1+ldc_adeeb-ldc_akree where gl_pr_koondbil.bilnr = li_bilnr and gl_pr_koondbil.aktpas <> 'P'
      end if;
      if ldc_ldeeb > 0 or ldc_lkree > 0 then
        if ldc_ldeeb > ldc_lkree then
          set li_bilnr=li_bilnr1;
          if li_bilnr = 0 then
            set li_bilnr=li_bilnr2
          end if
        else
          set li_bilnr=li_bilnr2;
          if li_bilnr = 0 then
            set li_bilnr=li_bilnr1
          end if
        end if;
        update gl_pr_koondbil set summa2 = summa2+ldc_lkree-ldc_ldeeb where gl_pr_koondbil.bilnr = li_bilnr and gl_pr_koondbil.aktpas = 'P';
        update gl_pr_koondbil set summa2 = summa2+ldc_ldeeb-ldc_lkree where gl_pr_koondbil.bilnr = li_bilnr and gl_pr_koondbil.aktpas <> 'P'
      end if
    end if
  end loop pr_koond_loop;
  close c_gl_pr_koond;
  -- erilised vahekokkuvotted
  open c_pr_bil;
  pr_bil_loop: loop
    --  loe bilansi rida
    fetch next c_pr_bil into li_bilnr,ls_eriline;
    if sqlstate = err_notfound then -- kui eof
      leave pr_bil_loop
    end if;
    if length(trim(ls_eriline)) > 0 then
      set ls_eriline=','+trim(ls_eriline)+',';
      select sum(summa1),sum(summa2) into ldc_adeeb,ldc_ldeeb from gl_pr_koondbil where locate(ls_eriline,','+trim(gl_pr_koondbil.bil1)+',') > 0;
      update gl_pr_koondbil set summa1kok = ldc_adeeb-summa1,summa2kok = ldc_ldeeb-summa2 where gl_pr_koondbil.bilnr = li_bilnr
    end if
  end loop pr_bil_loop;
  close c_pr_bil;
  --  vahekokkuvotted
  open c_pr_koondbil;
  pr_koondbil_loop: loop
    --  loe koond bilansi rida
    fetch next c_pr_koondbil into li_bilnr,ls_bil1,ls_bil2,ls_bil3,ls_bil4;
    if sqlstate = err_notfound then -- kui eof
      leave pr_koondbil_loop
    end if;
    set li_level = 0;
    if length(trim(ls_bil1)) > 0 then
      set li_level=1;
      if length(trim(ls_bil2)) > 0 then
        set li_level=2;
        if length(trim(ls_bil3)) > 0 then
          set li_level=3;
          if length(trim(ls_bil4)) > 0 then
            set li_level=4
          end if
        end if
      end if
    end if;
    if li_level > 0 then
      case li_level
      when 1 then
        select sum(summa1),sum(summa2) into ldc_adeeb,ldc_ldeeb from gl_pr_koondbil where gl_pr_koondbil.bil1 = ls_bil1
      when 2 then
        select sum(summa1),sum(summa2) into ldc_adeeb,ldc_ldeeb from gl_pr_koondbil where gl_pr_koondbil.bil1 = ls_bil1 and gl_pr_koondbil.bil2 = ls_bil2
      when 3 then
        select sum(summa1),sum(summa2) into ldc_adeeb,ldc_ldeeb from gl_pr_koondbil where gl_pr_koondbil.bil1 = ls_bil1 and gl_pr_koondbil.bil2 = ls_bil2 and gl_pr_koondbil.bil3 = ls_bil3
      when 4 then
        select sum(summa1),sum(summa2) into ldc_adeeb,ldc_ldeeb from gl_pr_koondbil where gl_pr_koondbil.bil1 = ls_bil1 and gl_pr_koondbil.bil2 = ls_bil2 and gl_pr_koondbil.bil3 = ls_bil3 and gl_pr_koondbil.bil4 = ls_bil4
      end case
      ;
      update gl_pr_koondbil set summa1kok = summa1kok+ldc_adeeb-summa1,summa2kok = summa2kok+ldc_ldeeb-summa2 where gl_pr_koondbil.bilnr = li_bilnr
    end if
  end loop pr_koondbil_loop;
  close c_pr_koondbil;
  delete from gl_pr_koondbil where gl_pr_koondbil.summa1 = 0 and gl_pr_koondbil.summa2 = 0 and gl_pr_koondbil.summa1kok = 0 and gl_pr_koondbil.summa2kok = 0;
  update gl_pr_koondbil set bil2 = '' where bil2 is null;
  update gl_pr_koondbil set bil3 = '' where bil3 is null;
  update gl_pr_koondbil set bil4 = '' where bil4 is null;
  update gl_pr_koondbil set eriline = '' where eriline is null
end