CREATE PROCEDURE "dba"."mt_kustuta_kanne_null"( in a_skoop integer,in a_yksusnr integer, in a_yksuskood char(16),in perlop date )
BEGIN
  declare li_yksusnr integer;
  declare err_notfound exception for sqlstate value '02000';
  declare c_mt_yksus dynamic scroll cursor for
    select mt_yksus.yksusnr
    from mt_yksus
    where (mt_yksus.yksusnr=a_yksusnr or a_yksusnr=0) and
          Left(mt_yksus.kood,Length(a_yksuskood))=a_yksuskood and
          (mt_yksus.skoop=a_skoop);
  declare c_mt_toode dynamic scroll cursor for
    select tootnr
    from mt_toode
    where mt_toode.yksusnr=li_yksusnr;
  delete from gl_mt_koond;
  open c_mt_yksus;
  mt_yksus_loop: loop
    fetch next c_mt_yksus into li_yksusnr;
    if sqlstate = err_notfound then -- kui eof
      leave mt_yksus_loop
    end if;

    insert into gl_mt_koond(tootnr,lkogus,lsumma)
      select mt_kanne.tootnr,
             sum(if mt_kanne.sisse=1 then mt_kanne.kogus else -1*mt_kanne.kogus endif),
             sum(if mt_kanne.sisse=1 then mt_kanne.summa+mt_kanne.summa2 else -1*(mt_kanne.summa+mt_kanne.summa2) endif)
        from mt_kanne left outer join mt_yksus on mt_kanne.yksusnr=mt_yksus.yksusnr
        where mt_kanne.yksusnr=li_yksusnr and
              mt_kanne.tootnr>0 and
              mt_kanne.kuup<=perlop
        group by mt_kanne.tootnr;

    delete from gl_mt_koond where lkogus=0 and lsumma=0;

    delete mt_kanne
      from mt_kanne left outer join gl_mt_koond on mt_kanne.tootnr=gl_mt_koond.tootnr
      where gl_mt_kanne.yksusnr=li_yksusnr and
            mt_kanne.kuup<=perlop and
            gl_mt_koond.tootnr is null;

    open c_mt_toode;
    mt_toode_loop: loop
      fetch next c_mt_toode into li_tootnr;
      if sqlstate = err_notfound then -- kui eof
        leave mt_toode_loop
      end if;
      if not exists(select 1 from mt_kanne where mt_kanne.yksusnr=li_yksusnr and mt_kanne.tootnr=li_tootnr) then
        delete from mt_toode where mt_toode.yksusnr=li_yksusnr and mt_toode.tootnr=li_tootnr
      end if;
    end loop mt_toode_loop;
    close c_mt_toode;


  end loop mt_yksus_loop;
  close c_mt_yksus;
END