CREATE PROCEDURE "dba"."dk_arvetasarno_ref"( in as_viitnr varchar(35) )
BEGIN

  -- alliknr - kannete arv

  declare ll_asutnr integer;
  declare ls_arvenr char(20);
  declare ld_arvekuup date;
  declare ls_konto char(7);
  declare err_notfound exception for sqlstate value '02000';
  declare c_arvetas insensitive cursor for
    select distinct asutnr,arvekuup,arvenr,konto
      from gl_dk_arvetas;

  delete gl_dk_arvetas;

  if as_viitnr is null then
    set as_viitnr=''
  end if;
  set as_viitnr=Trim(as_viitnr);
  if as_viitnr='' then
    insert into gl_dk_arvetas("info",arvesumma,makssumma,alliknr) values ('No reference',0,0,0);
  else
    insert into gl_dk_arvetas("info",kuup,asutnr,arvenr,arvekuup,konto,arvesumma,makssumma,alliknr)
      select as_viitnr,
             dk_kanne.kuup,
             dk_kanne.asutnr,
             if (dk_kanne.crekuup is null or dk_kanne.crenr is null) then (if dk_kanne.arvenr is null then '' else dk_kanne.arvenr endif) else dk_kanne.crenr endif as dk_kanne_arvenr,
             if (dk_kanne.crekuup is null or dk_kanne.crenr is null) then (if dk_kanne.arvekuup is null then ymd(2000,1,1) else dk_kanne.arvekuup endif) else dk_kanne.crekuup endif as dk_kanne_arvekuup,
             dk_obj.djkonto,
             sum((2*dk_kanne.sisse-1)*(dk_kanne.summa+dk_kanne.summa2)),0,
             count()
       from dk_kanne key left outer join dk_obj
       where dk_kanne.viitnr = as_viitnr and dk_obj.spank=0
       group by dk_kanne.kuup,dk_kanne.asutnr,dk_kanne_arvenr,dk_kanne_arvekuup,dk_obj.djkonto;

    set ll_asutnr=0;
    select count() into ll_asutnr from gl_dk_arvetas;

    if ll_asutnr>0 then 
      open c_arvetas;
      arvetas_loop: loop
        fetch next c_arvetas into ll_asutnr,ld_arvekuup,ls_arvenr,ls_konto;
        if sqlstate = err_notfound then -- kui eof
          leave arvetas_loop
        end if;
        insert into gl_dk_arvetas("info",kuup,asutnr,arvenr,arvekuup,konto,arvesumma,makssumma,alliknr)
          select as_viitnr,
                 kp_kanne.kuup,
                 ll_asutnr,
                 ls_arvenr,
                 ld_arvekuup,
                 ls_konto,
                 0,sum((2*kp_kanne.sisse-1)*kp_kanne.summa),
                 count()
         from kp_kanne key left outer join kp_obj
         where kp_kanne.asutnr=ll_asutnr and kp_kanne.konto=ls_konto and kp_kanne.arvenr=ls_arvenr and kp_kanne.arvekuup=ld_arvekuup
         group by kp_kanne.kuup;
       end loop arvetas_loop;
      close c_arvetas;
    else
      insert into gl_dk_arvetas("info",arvesumma,makssumma,alliknr) values (as_viitnr+' No invoce',0,0,0);
    end if;
  end if;
  select "info" as "reference",kuup,arvekuup,arvenr,arvesumma,makssumma,alliknr as kannete_arv from gl_dk_arvetas;
END