CREATE PROCEDURE "DBA"."dk_earve_korrig"( )
BEGIN
  declare ll_id_dk_earve integer;
  declare ll_id_dk_earve_sum integer;
  declare ll_id_dk_earve_rida integer;
  declare ldc_totalsum numeric(15,2);
  declare ldc_totalvatsum numeric(15,2);
  declare ldc_summa numeric(15,2);
  declare ldc_summa2 numeric(15,2);
  declare ldc_kok_summa numeric(15,2);
  declare ldc_kok_summa2 numeric(15,2);
  declare ldc_vahe numeric(15,2);
  declare ldc_old_vahe numeric(15,2);
  declare ldc_koef numeric(15,2);
  declare err_notfound exception for sqlstate value '02000';
  declare c_gl_2 dynamic scroll cursor for select "id" from gl_2;
  declare c_vatkor dynamic scroll cursor for select "id",summa2 from dk_earve_rida where id_dk_earve=ll_id_dk_earve and "id">0 and summa2<>0;
  declare c_sumkor dynamic scroll cursor for select "id",summa from dk_earve_rida where id_dk_earve=ll_id_dk_earve and "id">0 and summa<>0;
  open c_gl_2;
  gl_2_loop: loop
    fetch next c_gl_2 into ll_id_dk_earve;
    if sqlstate = err_notfound then -- kui eof
      leave gl_2_loop
    end if;
    if ll_id_dk_earve>0 then
--    algvaartuste lugemine
      select first "id",summa,summa2,totalsum into ll_id_dk_earve_sum,ldc_kok_summa,ldc_kok_summa2,ldc_totalsum from "dk_earve_sum" where "dk_earve_sum"."id_dk_earve"=ll_id_dk_earve and "dk_earve_sum"."currency"='EUR';
      if ll_id_dk_earve_sum>0 then
        select get_eak_totalvatsum(ll_id_dk_earve,ll_id_dk_earve_sum) into ldc_totalvatsum;
      end if;
      if ldc_kok_summa is null then
        set ldc_kok_summa=0
      end if;
      if ldc_kok_summa2 is null then
        set ldc_kok_summa2=0
      end if;
      if ldc_totalsum is null then
        set ldc_totalsum=0
      end if;
      if ldc_totalvatsum is null then
        set ldc_totalvatsum=0
      end if;
--    summade kontroll
      if ldc_totalsum=0 and ldc_kok_summa+ldc_kok_summa2<>0 then
        set ldc_totalsum=ldc_kok_summa+ldc_kok_summa2
      end if;
      if ldc_totalvatsum=0 and ldc_kok_summa2<>0 then
        set ldc_totalvatsum=ldc_kok_summa2
      end if;
--    kaibemaksu korrigeerimine
      if ldc_totalvatsum<>ldc_kok_summa2 then
        set ldc_vahe=ldc_totalvatsum-ldc_kok_summa2;
        if ldc_vahe<0 then
          set ldc_koef=-0.01
        else
          set ldc_koef=0.01
        end if;
        vatvahe_loop: while ldc_vahe<>0 loop
          set ldc_old_vahe=ldc_vahe;
          open c_vatkor;
          vatkor_loop: loop
            fetch next c_vatkor into ll_id_dk_earve_rida,ldc_summa2;
            if sqlstate = err_notfound then -- kui eof
              leave vatkor_loop
            end if;
            set ldc_vahe=ldc_vahe-ldc_koef;
            update dk_earve_rida set summa2=summa2+ldc_koef where "dk_earve_rida"."id_dk_earve"=ll_id_dk_earve and "dk_earve_rida"."id"=ll_id_dk_earve_rida;
            if ldc_vahe=0 then
              leave vatkor_loop
            end if;
          end loop vatkor_loop;
          close c_vatkor;
          if ldc_vahe=ldc_old_vahe then
            set ldc_vahe=0
          end if
        end loop vatvahe_loop;
        update dk_earve_sum set summa2=ldc_totalvatsum where "dk_earve_sum"."id_dk_earve"=ll_id_dk_earve and "dk_earve_sum"."id"=ll_id_dk_earve_sum;
      end if;
--    maksustatava summa korrigeerimine
      if ldc_totalsum-ldc_totalvatsum<>ldc_kok_summa then
        set ldc_vahe=ldc_totalsum-ldc_totalvatsum-ldc_kok_summa;
        if ldc_vahe<0 then
          set ldc_koef=-0.01
        else
          set ldc_koef=0.01
        end if;
        sumvahe_loop: while ldc_vahe<>0 loop
          set ldc_old_vahe=ldc_vahe;
          open c_sumkor;
          sumkor_loop: loop
            fetch next c_sumkor into ll_id_dk_earve_rida,ldc_summa;
            if sqlstate = err_notfound then -- kui eof
              leave sumkor_loop
            end if;
            set ldc_vahe=ldc_vahe-ldc_koef;
            update dk_earve_rida set summa=summa+ldc_koef where "dk_earve_rida"."id_dk_earve"=ll_id_dk_earve and "dk_earve_rida"."id"=ll_id_dk_earve_rida;
            if ldc_vahe=0 then
              leave sumkor_loop
            end if;
          end loop sumkor_loop;
          close c_sumkor;
          if ldc_vahe=ldc_old_vahe then
            set ldc_vahe=0
          end if
        end loop sumvahe_loop;
        update dk_earve_sum set summa=ldc_totalsum-ldc_totalvatsum where "dk_earve_sum"."id_dk_earve"=ll_id_dk_earve and "dk_earve_sum"."id"=ll_id_dk_earve_sum;
      end if;
    end if;
  end loop gl_2_loop;
  close c_gl_2;
END