ALTER PROCEDURE "DBA"."dk_koond_saldoteatis_reg"( in id_skoop integer,in id_obj integer,in a_asutnr integer,in peralg date,in perlop date)
BEGIN
  declare ls_dk_kontod char(5000);
  declare ls_regnr char(11);
  delete from gl_dk_koond;
  delete from gl_pr_koond;
  if id_obj > 0 then
    select dk_obj.skoop into id_skoop from dk_obj where dk_obj.objnr = id_obj
  end if;
  if a_asutnr > 0 then
    select reg_nr into ls_regnr from asulist where asutnr=a_asutnr;
    if ls_regnr is null or length(trim(ls_regnr))=0 then
      set ls_regnr='xyzkljug'
    end if
  else
    set ls_regnr=''
  end if;
  -- arveldused
  insert into gl_pr_koond( konto,asutnr,adeeb,akree,deeb,kree)
    select '1',min(dk_kanne.asutnr),sum(if dk_kanne.sisse = 1 and dk_kanne.kuup < peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif),sum(if dk_kanne.sisse = 0 and dk_kanne.kuup < peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif),sum(if dk_kanne.sisse = 1 and dk_kanne.kuup >= peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif),sum(if dk_kanne.sisse = 0 and dk_kanne.kuup >= peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif)
      from dk_kanne key left outer join dk_obj left outer join asulist on dk_kanne.asutnr=asulist.asutnr
      where (dk_kanne.objnr = id_obj or id_obj = 0) and(dk_obj.skoop = id_skoop or id_skoop = 0) and (asulist.reg_nr = ls_regnr or ls_regnr = '') and dk_kanne.asutnr > 0 and dk_kanne.kuup <= perlop
      group by asulist.reg_nr order by asulist.reg_nr asc;
  set ls_dk_kontod=get_dk_kontod_koond(id_skoop,id_obj);
  insert into gl_pr_koond( konto,asutnr,adeeb,akree,deeb,kree)
    select '2',min(kp_kanne.asutnr),sum(if kp_kanne.sisse = 0 and kp_kanne.kuup < peralg then kp_kanne.summa else 0 endif),sum(if kp_kanne.sisse = 1 and kp_kanne.kuup < peralg then kp_kanne.summa else 0 endif),sum(if kp_kanne.sisse = 0 and kp_kanne.kuup >= peralg then kp_kanne.summa else 0 endif),sum(if kp_kanne.sisse = 1 and kp_kanne.kuup >= peralg then kp_kanne.summa else 0 endif)
      from kp_kanne left outer join kp_obj on kp_kanne.objnr = kp_obj.objnr left outer join asulist on kp_kanne.asutnr=asulist.asutnr
      where locate(ls_dk_kontod,';'+"Left"(kp_kanne.konto+Space(7),7)+';') > 0 and(kp_obj.skoop = id_skoop or id_skoop = 0) and (asulist.reg_nr = ls_regnr or ls_regnr = '') and kp_kanne.asutnr > 0 and kp_kanne.kuup <= perlop
      group by asulist.reg_nr order by asulist.reg_nr asc;
  -- koond
  insert into gl_dk_koond( asutnr,adeeb,akree,deeb,kree) 
    select gl_pr_koond.asutnr,sum(gl_pr_koond.adeeb),sum(gl_pr_koond.akree),sum(gl_pr_koond.deeb),sum(gl_pr_koond.kree)
      from gl_pr_koond group by gl_pr_koond.asutnr order by gl_pr_koond.asutnr asc;
  -- loppjaagid
  update gl_dk_koond set adeeb = adeeb-akree,akree = akree-adeeb;
  update gl_dk_koond set adeeb = 0 where gl_dk_koond.adeeb < 0;
  update gl_dk_koond set akree = 0 where gl_dk_koond.akree < 0;
  update gl_dk_koond set ldeeb = adeeb-akree+deeb-kree;
  delete from gl_dk_koond where adeeb = 0 and akree = 0 and deeb = 0 and kree = 0
END