ALTER PROCEDURE "dba"."pr_pa_kan_reserv"(in a_skoop integer,in a_peralg date,in a_perlop date)
begin
  declare li_ii integer;
  declare li_v_aasta numeric(4);
  declare li_v_eelm_aasta numeric(4);
  declare li_v_akuu numeric(2);
  declare li_tasu_v_aasta numeric(4);
  declare li_tasu_v_akuu numeric(2);
  declare li_tasu_a_aasta numeric(4);
  declare li_tasu_a_akuu numeric(2);
  declare li_tasu_t_aasta numeric(4);
  declare li_tasu_t_akuu numeric(2);
  declare ldc_summa decimal(15,2);
  declare ldc_sotsmaks decimal(15,2);
  declare ldc_tatkmaks decimal(15,2);
  declare ldc_par1 decimal(15,2);
  declare ls_order char(3);
  declare ls_deeb char(7);
  declare ls_kree char(7);
  declare ls_res_deeb char(7);
  declare ls_res_kree char(7);
  declare ls_reskree char(7);
  declare ls_konto char(7);
  declare ls_sotsdeeb char(7);
  declare ls_sotskree char(7);
  declare ls_sotskonto char(7);
  declare ls_sotsart char(16);
  declare li_sotsartnr integer;
  declare ls_tatkdeeb char(7);
  declare ls_tatkkree char(7);
  declare ls_tatkkonto char(7);
  declare ls_tatkart char(16);
  declare li_tatkartnr integer;
  declare li_tasupartnr integer;
  declare li_asutpartnr integer;
  declare li_partnr integer;
  declare li_partnr2 integer;
  declare li_meetnr integer;
  declare li_kulnr integer;
  declare li_talnr integer;
  declare li_artnr integer;
  declare li_tulnr integer;
  declare li_objectnr integer;
  declare li_osaknr integer;
  declare li_subjectnr integer;
  declare li_suundnr integer;
  declare li_taotnr integer;
  declare li_alliknr integer;
  declare li_ravonr integer;
  declare li_skoop integer;
  declare ls_algol char(2);
  declare ls_info char(50);
  declare li_sisse smallint;
  declare err_notfound exception for sqlstate value '02000';
  declare c_pa_reserv dynamic scroll cursor for
    select if pa_reserv.v_aasta = li_v_aasta then pa_reserv.summa else -1*pa_reserv.summa endif,
           if pa_reserv.v_aasta = li_v_aasta then pa_reserv.tatkmaks else -1*pa_reserv.tatkmaks endif,
           if pa_reserv.v_aasta = li_v_aasta then pa_reserv.sotsmaks else -1*pa_reserv.sotsmaks endif,
           if pa_reserv.deeb is null then (if pa_taslist_b.deeb is null then 'xxxxxyz' else pa_taslist_b.deeb endif) else pa_reserv.deeb endif,
           if pa_reserv.partnr is null then (if asulist.partnr is null then 0 else asulist.partnr endif) else pa_reserv.partnr endif,
           if pa_reserv.kree is null then if ls_reskree='xxxxxyz' then (if pa_taslist_b.kree is null then 'xxxxxyz' else pa_taslist_b.kree endif) else ls_reskree endif else pa_reserv.kree endif,
           if pa_reserv.partnr2 is null then (if asulist.partnr is null then 0 else asulist.partnr endif) else pa_reserv.partnr2 endif,
          (if pa_reserv.meetnr is null then (if pa_paryld_b.meetnr is null then (if pa_taslist_b.meetnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.meetnr is null then 0 else pa_yksus.meetnr endif) else pa_taslist_b.meetnr endif) else pa_paryld_b.meetnr endif) else pa_reserv.meetnr endif),
          (if pa_reserv.kulnr is null then (if pa_paryld_b.kulnr is null then (if pa_taslist_b.kulnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.kulnr is null then 0 else pa_yksus.kulnr endif) else pa_taslist_b.kulnr endif) else pa_paryld_b.kulnr endif) else pa_reserv.kulnr endif),
          (if pa_reserv.talnr is null then (if pa_paryld_b.talnr is null then (if pa_taslist_b.talnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.talnr is null then 0 else pa_yksus.talnr endif) else pa_taslist_b.talnr endif) else pa_paryld_b.talnr endif) else pa_reserv.talnr endif),
          (if pa_reserv.artnr is null then (if pa_taslist_b.artnr is null then (if pa_yksus.artnr is null then 0 else pa_yksus.artnr endif) else pa_taslist_b.artnr endif) else pa_reserv.artnr endif),
          (if pa_reserv.tulnr is null then (if pa_taslist_b.tulnr is null then (if pa_yksus.tulnr is null then 0 else pa_yksus.tulnr endif) else pa_taslist_b.tulnr endif) else pa_reserv.tulnr endif),
          (if pa_reserv.objectnr is null then (if pa_paryld_b.objectnr is null then (if pa_taslist_b.objectnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.objectnr is null then 0 else pa_yksus.objectnr endif) else pa_taslist_b.objectnr endif) else pa_paryld_b.objectnr endif) else pa_reserv.objectnr endif),
          (if pa_reserv.osaknr is null then (if pa_paryld_b.osaknr is null then (if pa_taslist_b.osaknr is null or pa_taslist_b.samkul=0 then (if pa_yksus.osaknr is null then 0 else pa_yksus.osaknr endif) else pa_taslist_b.osaknr endif) else pa_paryld_b.osaknr endif) else pa_reserv.osaknr endif),
          (if pa_reserv.subjectnr is null then (if pa_paryld_b.subjectnr is null then (if pa_taslist_b.subjectnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.subjectnr is null then 0 else pa_yksus.subjectnr endif) else pa_taslist_b.subjectnr endif) else pa_paryld_b.subjectnr endif) else pa_reserv.subjectnr endif),
          (if pa_reserv.suundnr is null then (if pa_paryld_b.suundnr is null then (if pa_taslist_b.suundnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.suundnr is null then 0 else pa_yksus.suundnr endif) else pa_taslist_b.suundnr endif) else pa_paryld_b.suundnr endif) else pa_reserv.suundnr endif),
          (if pa_reserv.taotnr is null then (if pa_paryld_b.taotnr is null then (if pa_taslist_b.taotnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.taotnr is null then 0 else pa_yksus.taotnr endif) else pa_taslist_b.taotnr endif) else pa_paryld_b.taotnr endif) else pa_reserv.taotnr endif),
           if pa_reserv.alliknr is null then 0 else pa_reserv.alliknr endif,
           if pa_reserv.ravonr is null then 0 else pa_reserv.ravonr endif,
           pa_yksus.skoop
     from pa_reserv left outer join pa_taslist as pa_taslist_b on pa_reserv.vtas_s = pa_taslist_b.tas_s
                    left outer join pa_paryld as pa_paryld_b on pa_reserv.vparyldnr = pa_paryld_b.paryldnr
                    left outer join pa_yksus on pa_reserv.yksusnr = pa_yksus.yksusnr
                    left outer join pa_leping on pa_reserv.lepnr = pa_leping.lepnr
                    left outer join asulist on pa_leping.asutnr = asulist.asutnr
     where (pa_yksus.skoop = a_skoop or a_skoop = 0) and
          ((pa_reserv.v_aasta=li_v_aasta or pa_reserv.v_aasta=li_v_eelm_aasta) and pa_reserv.v_akuu=li_v_akuu);
  delete from gl_pr_kanne;
  select if k_sotskree is null or Trim(k_sotskree) = '' then 'xxxxxyz' else k_sotskree endif,
         if k_tatkkree is null or Trim(k_tatkkree) = '' then 'xxxxxyz' else k_tatkkree endif,
         if k_sotsdeeb is null or Trim(k_sotsdeeb) = '' then 'xxxxxyz' else k_sotsdeeb endif,
         if k_tatkdeeb is null or Trim(k_tatkdeeb) = '' then 'xxxxxyz' else k_tatkdeeb endif,
         if k_sotsart is null or Trim(k_sotsart) = '' then 'xxxxxyz' else k_sotsart endif,
         if k_tatkart is null or Trim(k_tatkart) = '' then 'xxxxxyz' else k_tatkart endif,
         if k_reskree is null or Trim(k_reskree) = '' then 'xxxxxyz' else k_reskree endif
    into ls_sotskree,ls_tatkkree,ls_sotsdeeb,ls_tatkdeeb,ls_sotsart,ls_tatkart,ls_reskree
    from pa_konfm where pa_konfm.id = 1;
  select first artlist.artnr into li_sotsartnr from artlist where Trim(artlist.art) = ls_sotsart;
  if li_sotsartnr is null then
    set li_sotsartnr=0
  end if;
  select first artlist.artnr into li_tatkartnr from artlist where Trim(artlist.art) = ls_tatkart;
  if li_tatkartnr is null then
    set li_tatkartnr=0
  end if;
  set li_v_aasta=Years(a_perlop);
  set li_v_akuu=Month(a_perlop);
  select pa_konfm.yld_reserv_mem, if pa_konfm.yld_reserv_import=1 then li_v_aasta-1 else 1956 endif into ls_order,li_v_eelm_aasta from pa_konfm where pa_konfm.id = 1;
  open c_pa_reserv;
  pa_reserv_loop: loop
    fetch next c_pa_reserv into ldc_summa,ldc_tatkmaks,ldc_sotsmaks,ls_res_deeb,li_partnr,ls_res_kree,li_partnr2,li_meetnr,li_kulnr,li_talnr,li_artnr,li_tulnr,li_objectnr,li_osaknr,li_subjectnr,li_suundnr,li_taotnr,li_alliknr,li_ravonr,li_skoop;
    if sqlstate = err_notfound then -- kui eof
      leave pa_reserv_loop
    end if;
    set li_ii=1;
    while li_ii <= 3 loop
      case li_ii
      when 1 then
        set ls_info='Puhkusereserv';
        set ls_deeb=ls_res_deeb;
        set ls_kree=ls_res_kree
      when 2 then
        set ldc_summa=ldc_tatkmaks;
        if left(ls_res_deeb,3)='103' then
          set ls_deeb=ls_res_deeb;
          set ls_kree=ls_tatkdeeb;
        else
          set ls_deeb=ls_tatkdeeb;
          set ls_kree=ls_res_kree;
        end if;
        set li_artnr=li_tatkartnr;
        set ls_info='Puhkusereservi tööandja töötuskindlustus'
      when 3 then
        set ldc_summa=ldc_sotsmaks;
        if left(ls_res_deeb,3)='103' then
          set ls_deeb=ls_res_deeb;
          set ls_kree=ls_sotsdeeb;
        else
          set ls_deeb=ls_sotsdeeb;
          set ls_kree=ls_res_kree;
        end if;
        set li_artnr=li_sotsartnr;
        set ls_info='Puhkusereservi sotsiaalmaks'
      end case
      ;
      if ldc_summa <> 0 and ls_deeb <> 'xxxxxyz' and ls_kree <> 'xxxxxyz' then
        -- kas kanne ei eksisteeri koondis
        if exists(select 1 from gl_pr_kanne where gl_pr_kanne.skoop = li_skoop and gl_pr_kanne.info = ls_info and gl_pr_kanne.deeb = ls_deeb and gl_pr_kanne.kree = ls_kree and gl_pr_kanne.meetnr = li_meetnr and gl_pr_kanne.kulnr = li_kulnr and gl_pr_kanne.talnr = li_talnr and gl_pr_kanne.artnr = li_artnr and gl_pr_kanne.tulnr = li_tulnr and gl_pr_kanne.objectnr = li_objectnr and gl_pr_kanne.osaknr = li_osaknr and gl_pr_kanne.subjectnr = li_subjectnr and gl_pr_kanne.suundnr = li_suundnr and gl_pr_kanne.taotnr = li_taotnr and gl_pr_kanne.alliknr = li_alliknr and gl_pr_kanne.ravonr = li_ravonr and gl_pr_kanne.partnr = li_partnr and gl_pr_kanne.partnr2 = li_partnr2) then
          update gl_pr_kanne set summa = summa+ldc_summa where gl_pr_kanne.skoop = li_skoop and gl_pr_kanne.info = ls_info and gl_pr_kanne.deeb = ls_deeb and gl_pr_kanne.kree = ls_kree and gl_pr_kanne.meetnr = li_meetnr and gl_pr_kanne.kulnr = li_kulnr and gl_pr_kanne.talnr = li_talnr and gl_pr_kanne.artnr = li_artnr and gl_pr_kanne.tulnr = li_tulnr and gl_pr_kanne.objectnr = li_objectnr and gl_pr_kanne.osaknr = li_osaknr and gl_pr_kanne.subjectnr = li_subjectnr and gl_pr_kanne.suundnr = li_suundnr and gl_pr_kanne.taotnr = li_taotnr and gl_pr_kanne.alliknr = li_alliknr and gl_pr_kanne.ravonr = li_ravonr and gl_pr_kanne.partnr = li_partnr and gl_pr_kanne.partnr2 = li_partnr2
        else
          insert into gl_pr_kanne( tulnud,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,artnr,tulnr,objectnr,skoop,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,partnr,partnr2,info) values( 'Puhkusereserv',a_perlop,ldc_summa,ls_order,ls_deeb,ls_kree,li_meetnr,li_kulnr,li_talnr,li_artnr,li_tulnr,li_objectnr,li_skoop,li_osaknr,li_subjectnr,li_suundnr,li_taotnr,li_alliknr,li_ravonr,li_partnr,li_partnr2,ls_info) 
        end if;
      end if;
      set li_ii=li_ii+1
    end loop
  end loop pa_reserv_loop;
  close c_pa_reserv;
  delete from gl_pr_kanne where summa = 0;
  update gl_pr_kanne set deeb = null where(deeb = 'xxxxxyz' or Length(trim(deeb)) = 0);
  update gl_pr_kanne set kree = null where(kree = 'xxxxxyz' or Length(trim(kree)) = 0);
  update gl_pr_kanne set meetnr = null where meetnr = 0;
  update gl_pr_kanne set kulnr = null where kulnr = 0;
  update gl_pr_kanne set talnr = null where talnr = 0;
  update gl_pr_kanne set artnr = null where artnr = 0;
  update gl_pr_kanne set tulnr = null where tulnr = 0;
  update gl_pr_kanne set objectnr = null where objectnr = 0;
  update gl_pr_kanne set osaknr = null where osaknr = 0;
  update gl_pr_kanne set subjectnr = null where subjectnr = 0;
  update gl_pr_kanne set suundnr = null where suundnr = 0;
  update gl_pr_kanne set taotnr = null where taotnr = 0;
  update gl_pr_kanne set alliknr = null where alliknr = 0;
  update gl_pr_kanne set ravonr = null where ravonr = 0;
  update gl_pr_kanne set partnr = null where partnr = 0;
  update gl_pr_kanne set partnr2 = null where partnr2 = 0;
end