CREATE PROCEDURE "DBA"."ls_majakogus_summa_arvuta" (in a_maksnr integer, in a_yksusnr integer, in a_kuup date, in a_yhikukogus decimal(15,6), in a_user_code integer )
BEGIN
  declare ll_id integer;
  declare ldc_sum1 decimal(15,3);
  declare ldc_sum5 decimal(15,3);
  declare ld_kuup date;
  declare err_notfound exception for sqlstate value '02000';
  declare c_universal dynamic scroll cursor for select gl_tr_universal."id",gl_tr_universal.sum1,gl_tr_universal.sum5,gl_tr_universal.kuup from gl_tr_universal where gl_tr_universal.sum2 is null;
  delete ls_kogused from ls_kogused left outer join ls_maksud on ls_kogused.maksuid=ls_maksud.id where ls_maksud.yksusnr=a_yksusnr and Years(ls_kogused.kuup)=Years(a_kuup) and Month(ls_kogused.kuup)=Month(a_kuup) and ls_maksud.maksnr=a_maksnr and ls_kogused.majakogus=1;
  open c_universal;
  universal_loop: loop
    fetch next c_universal into ll_id,ldc_sum1,ldc_sum5,ld_kuup;
    if sqlstate = err_notfound then -- kui eof
      leave universal_loop
    end if;
    if ldc_sum1 is not null and ldc_sum1 <> 0 then
      insert into ls_kogused(kuup,user_code,kogus,maksuid,par2,majakogus) values(ld_kuup,a_user_code,ldc_sum1,ll_id,Round(ldc_sum5*a_yhikukogus,6),1)
    end if
  end loop universal_loop;
  close c_universal
END