ALTER PROCEDURE "dba"."pv_koond_kaart"(in a_skoop integer,in a_yksusnr integer,in a_yksuskood char(16),in a_paring integer,in a_id integer,in a_kuup date)
begin
  declare ls_konto char(7);
  declare ls_toot_nr char(20);
  declare ls_a_toot_nr char(20);
  declare ls_vara_nr char(10);
  declare ls_nimi char(100);
  declare ls_yldnimi char(200);
  declare ld_kuup date;
  declare ld_ekspl date;
  declare ldc_summa numeric(15,2);
  declare ldc_mjm numeric(15,2);
  declare li_yksusnr integer;
  declare li_yldnimnr integer;
  declare li_a_yldnimnr integer;
  declare li_id integer;
  declare li_id_min integer;
  declare li_paring integer;
  declare err_notfound exception for sqlstate value '02000';
  declare c_pv_kanne dynamic scroll cursor for
        select pv_yksus.konto,
               pv_kanne.yksusnr,
               "pv_kanne"."id",
               "pv_kanne"."yldnimnr",
               if pv_kanne.toot_nr is null then '' else trim(pv_kanne.toot_nr) endif,
               pv_kanne.vara_nr,
               pv_kanne.nimi,
               if pv_kanne_muuinfo.yldnimi is null or Trim(pv_kanne_muuinfo.yldnimi)='' then pv_kanne.nimi else pv_kanne_muuinfo.yldnimi endif,
               pv_kanne.kuup,
               pv_kanne.ekspl,
               pv_kanne.alg_m,
               if pv_kanne.objnr2 is null then pv_kanne.alg_m else 0 endif
            from pv_kanne left outer join gl_1 on(pv_kanne.id = gl_1.id) and(pv_kanne.kuup = gl_1.kuup)
                          left outer join pv_yksus on pv_kanne.yksusnr = pv_yksus.yksusnr
                          left outer join pv_kanne_muuinfo on pv_kanne.yldnimnr=pv_kanne_muuinfo."id"
            where((pv_kanne.id = gl_1.id) and(pv_kanne.kuup = gl_1.kuup) or a_paring = 0) and
                 (pv_yksus.skoop = a_skoop or a_skoop = 0) and
                 (pv_kanne.yksusnr = a_yksusnr or a_yksusnr = 0) and Left(pv_yksus.kood,Length(a_yksuskood))=a_yksuskood and
                 (a_id=0 or pv_kanne.id=a_id or (li_a_yldnimnr is not null and pv_kanne.yldnimnr=li_a_yldnimnr) or (Length(ls_a_toot_nr)>0 and pv_kanne.toot_nr=ls_a_toot_nr)) and
                 pv_kanne.kuup <= a_kuup
            order by "pv_kanne"."id" asc;
  delete from gl_pv_kanne;
  set li_paring=set_gl_1(a_paring);
  set li_a_yldnimnr=null;
  set ls_a_toot_nr='';
  -- kui yks vara
  if a_id > 0 then
    select "pv_kanne"."yldnimnr" into li_a_yldnimnr from pv_kanne where "pv_kanne"."id" = a_id;
    if li_a_yldnimnr is null then
      select Trim("pv_kanne"."toot_nr") into ls_a_toot_nr from pv_kanne where "pv_kanne"."id" = a_id;
      if ls_a_toot_nr is null then
        set ls_a_toot_nr=''
      end if
    end if
  end if;
  open c_pv_kanne;
  pv_kanne_loop: loop
    fetch next c_pv_kanne into ls_konto,li_yksusnr,li_id,li_yldnimnr,ls_toot_nr,ls_vara_nr,ls_nimi,ls_yldnimi,ld_kuup,ld_ekspl,ldc_summa,ldc_mjm;
    if sqlstate = err_notfound then -- kui eof
      leave pv_kanne_loop
    end if;
    -- korduv kanne
    set li_id_min=null;
    if li_yldnimnr is not null then
      select first id into li_id_min from gl_pv_kanne where yldnimnr=li_yldnimnr;
    else
      if Length(ls_toot_nr)>0 then
        select first id into li_id_min from gl_pv_kanne where toot_nr=ls_toot_nr
      end if;
    end if;
    if li_id_min is null then
      insert into gl_pv_kanne("id",yldnimnr,konto,toot_nr,vara_nr,nimi,yldnimi,kuup,ekspl,alg_m,mjm,yksusnr)
             values(li_id,li_yldnimnr,ls_konto,ls_toot_nr,ls_vara_nr,ls_nimi,ls_yldnimi,ld_kuup,ld_ekspl,ldc_summa,ldc_mjm,li_yksusnr);
    else
      update gl_pv_kanne set mjm=mjm+ldc_mjm,alg_m=alg_m+ldc_summa where gl_pv_kanne.id=li_id_min
    end if;
  end loop pv_kanne_loop;
  close c_pv_kanne
end