CREATE PROCEDURE "dba"."dk_reg_koond_kmd"( in a_skoopkood char(16), in a_skoop integer,in a_perlop date)
BEGIN
  declare ld_peralg date;
  declare ld_perlop date;
  delete from gl_dk_kanne;
  set ld_peralg=get_peralg(a_perlop);
  set ld_perlop=get_perlop(a_perlop);
  insert into gl_dk_kanne (objnr,asutnr,arvenr,arvekuup,kbnr,summa,summa2)
    select dk_obj.spank,
           dk_kanne.asutnr,dk_kanne.arvenr,dk_kanne.arvekuup,dk_kanne.kbnr,
           sum(if dk_obj.spank-dk_kanne.sisse=0 then -1*(dk_kanne.summa) else dk_kanne.summa endif),
           sum(if dk_obj.spank-dk_kanne.sisse=0 then -1*(dk_kanne.summa2) else dk_kanne.summa2 endif)
      from dk_kanne left outer join dk_obj on dk_kanne.objnr=dk_obj.objnr
                    left outer join kblist on dk_kanne.kbnr=kblist.kbnr
                    left outer join skoobid on dk_obj.skoop=skoobid.skoop
      where (dk_obj.skoop=a_skoop or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and
            dk_kanne.kuup>=ld_peralg and dk_kanne.kuup<=ld_perlop and
            (dk_kanne.kbnr is not null) and kblist.deklar=1
   group by dk_obj.spank,dk_kanne.asutnr,dk_kanne.arvenr,dk_kanne.arvekuup,dk_kanne.kbnr;
END