ALTER PROCEDURE "dba"."pr_pv_kan"(in a_skoop integer,in peralg date,in perlop date)
begin
  declare li_detimp smallint;
  declare li_imp_kuup smallint;
  declare li_imp_asutnr smallint;
  declare li_imp_arvenr smallint;
  declare li_autokann smallint;
  declare li_yksusnr integer;
  declare ld_kuup date;
  declare ldc_summa decimal(15,2);
  declare ls_nimi char(100);
  declare ls_info char(200);
  declare ldc_epk decimal(15,2);
  declare ldc_jpk decimal(15,2);
  declare ldc_ytk decimal(15,2);
  declare ls_order char(3);
  declare ls_deeb char(7);
  declare ls_kree char(7);
  declare ls_konto char(7);
  declare ls_yksuskonto char(7);
  declare ls_yksusdeeb char(7);
  declare ls_yksuskree char(7);
  declare ls_objdeeb char(7);
  declare ls_objkree char(7);
  declare li_partnr integer;
  declare li_partnr2 integer;
  declare li_asutnr 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_ravonr4 integer;
  declare li_objravonr integer;
  declare li_ravonr_11 integer;
  declare li_skoop integer;
  declare ls_dk_kontod char(5000);
  declare ls_kp_kontod char(5000);
  declare err_notfound exception for sqlstate value '02000';
  declare c_pv_yksus dynamic scroll cursor for select pv_yksus.konto,pv_yksus.deeb,pv_yksus.kree,pv_yksus."order",pv_yksus.yksusnr,if pv_yksus.skoop is null then 0 else pv_yksus.skoop endif from pv_yksus where(pv_yksus.skoop = a_skoop or a_skoop = 0) and length(trim(pv_yksus."order")) > 0;
  declare c_pv_kanne_maha dynamic scroll cursor for
    select pv_kanne.deeb,
           if li_imp_kuup=1 then pv_kanne.kuup2 else perlop endif,
           if li_detimp=1 then pv_kanne.nimi else '' endif,
           pv_kanne.alg_m,
           pv_epk(pv_kanne.id,years(perlop)) as epk,
           pv_jpk(pv_kanne.id,years(perlop),month(perlop),epk) as jpk1,
           if years(pv_kanne.kuup)=years(perlop) and pv_kanne.kuup<ymd(years(perlop),month(perlop)+1,1) then pv_kanne.ytk else 0 endif,
           if asulist_a.partnr is null then (if asulist_b.partnr is null then 0 else asulist_b.partnr endif) else asulist_a.partnr endif,
           if asulist_b.partnr is null then 0 else asulist_b.partnr endif,
           if li_imp_asutnr=1 then if pv_kanne.asutnr is null then (if pv_kanne.saajanr is null then 0 else pv_kanne.saajanr endif) else pv_kanne.asutnr endif else 0 endif,
           if pv_kanne.meetnr is null then(if pv_yksus.meetnr is null then 0 else pv_yksus.meetnr endif) else pv_kanne.meetnr endif,
           if pv_kanne.kulnr is null then(if pv_yksus.kulnr is null then 0 else pv_yksus.kulnr endif) else pv_kanne.kulnr endif,
           if pv_kanne.talnr is null then(if pv_yksus.talnr is null then 0 else pv_yksus.talnr endif) else pv_kanne.talnr endif,
           if pv_kanne.artnr is null then 0 else pv_kanne.artnr endif,
           if pv_kanne.tulnr is null then 0 else pv_kanne.tulnr endif,
           if pv_kanne.objectnr is null then 0 else pv_kanne.objectnr endif,
           if pv_kanne.osaknr is null then(if pv_yksus.osaknr is null then 0 else pv_yksus.osaknr endif) else pv_kanne.osaknr endif,
           if pv_kanne.subjectnr is null then (if pv_yksus.subjectnr is null then 0 else pv_yksus.subjectnr endif) else pv_kanne.subjectnr endif,
           if pv_kanne.suundnr is null then(if pv_yksus.suundnr is null then 0 else pv_yksus.suundnr endif) else pv_kanne.suundnr endif,
           if pv_kanne.taotnr is null then(if pv_yksus.taotnr is null then 0 else pv_yksus.taotnr endif) else pv_kanne.taotnr endif,
           if pv_kanne.alliknr is null then 0 else pv_kanne.alliknr endif,
           if pv_kanne.ravonr2 is null then(if pv_kanne.ravonr is null then 0 else pv_kanne.ravonr endif) else pv_kanne.ravonr2 endif
      from pv_kanne left outer join pv_yksus on pv_kanne.yksusnr = pv_yksus.yksusnr left outer join pv_obj on pv_kanne.objnr = pv_obj.objnr left outer join asulist as asulist_a on pv_kanne.asutnr = asulist_a.asutnr left outer join asulist as asulist_b on pv_kanne.saajanr = asulist_b.asutnr
      where pv_kanne.yksusnr = li_yksusnr and pv_kanne.kuup2 >= peralg and pv_kanne.kuup2 <= perlop and pv_kanne.objnr2 is not null;
  delete from gl_pr_kanne;
  delete from gl_pr_kanne_import;
  select yld_autokann,
         yld_imp_pv_det,
         if yld_imp_pv_det=1 or yld_imp_kuup=1 then 1 else 0 endif,
         if yld_imp_pv_det=1 or yld_imp_arvenr=1 then 1 else 0 endif,
         if yld_imp_pv_det=1 or yld_imp_arvenr=1 or yld_imp_asutnr=1 then 1 else 0 endif
    into li_autokann,li_detimp,li_imp_kuup,li_imp_arvenr,li_imp_asutnr from pr_konfm where pr_konfm.id=1;
  set peralg=get_peralg(peralg);
  set perlop=get_perlop(perlop);
  select existid_ravolist('11') into li_ravonr_11 from DUMMY;
  open c_pv_yksus;
  pv_yksus_loop: loop
    fetch next c_pv_yksus into ls_yksuskonto,ls_yksusdeeb,ls_yksuskree,ls_order,li_yksusnr,li_skoop;
    if sqlstate = err_notfound then -- kui eof
      leave pv_yksus_loop
    end if;
    set ls_dk_kontod=get_dk_kontod(li_skoop,0);
    set ls_kp_kontod=get_kp_kontod(li_skoop,0);
    if ls_yksusdeeb is not null and ls_yksuskree is not null then
      insert into gl_pr_kanne_import(tulnud,id_prog,kuup,summa,"info","order",deeb,partnr,kree,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop)
        select 'Pohivarad',6,
               perlop,
               sum(pv_jpk_kuu(pv_kanne."id",perlop)),
               'Kuu kulum',
               ls_order,
               ls_yksusdeeb,
               asulist.partnr as pv_partnr,
               ls_yksuskree,
               asulist.partnr as pv_partnr2,
               if pv_kanne.meetnr is null then pv_yksus.meetnr else pv_kanne.meetnr endif as pv_meetnr,
               if pv_kanne.kulnr is null then pv_yksus.kulnr else pv_kanne.kulnr endif as pv_kulnr,
               if pv_kanne.talnr is null then pv_yksus.talnr else pv_kanne.talnr endif as pv_talnr,
               if pv_yksus.artnr is null then pv_kanne.artnr else pv_yksus.artnr endif as pv_artnr,
               if pv_kanne.tulnr is null then pv_yksus.tulnr else pv_kanne.tulnr endif as pv_tulnr,
               if pv_kanne.objectnr is null then pv_yksus.objectnr else pv_kanne.objectnr endif as pv_objectnr,
               if pv_kanne.osaknr is null then pv_yksus.osaknr else pv_kanne.osaknr endif as pv_osaknr,
               if pv_kanne.subjectnr is null then pv_yksus.subjectnr else pv_kanne.subjectnr endif as pv_subjectnr,
               if pv_kanne.suundnr is null then pv_yksus.suundnr else pv_kanne.suundnr endif as pv_suundnr,
               if pv_kanne.taotnr is null then pv_yksus.taotnr else pv_kanne.taotnr endif as pv_taotnr,
               pv_kanne.alliknr as pv_alliknr,
               if pv_kanne.ravonr3 is null then li_ravonr_11 else pv_kanne.ravonr3 endif as pv_ravonr,
               li_skoop 
          from pv_kanne left outer join pv_yksus on pv_kanne.yksusnr=pv_yksus.yksusnr left outer join asulist on pv_kanne.asutnr = asulist.asutnr
          where pv_kanne.yksusnr=li_yksusnr and pv_kanne.kuup<=perlop
          group by pv_partnr,pv_partnr2,pv_meetnr,pv_kulnr,pv_talnr,pv_artnr,pv_tulnr,pv_objectnr,pv_osaknr,pv_subjectnr,pv_suundnr,pv_taotnr,pv_alliknr,pv_ravonr;
      insert into gl_pr_kanne_import(tulnud,id_prog,kuup,summa,"info","order",deeb,partnr,kree,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop)
        select 'Pohivarad',6,
               perlop,
               sum(pv_jpk_kuu(pv_kanne."id",perlop)),
               'Kuu kulumi paralleelkanne',
               ls_order,
               pv_obj.deeb as pv_deeb,
               asulist.partnr as pv_partnr,
               pv_obj.kree as pv_kree,
               asulist.partnr as pv_partnr2,
               if pv_kanne.meetnr is null then pv_yksus.meetnr else pv_kanne.meetnr endif as pv_meetnr,
               if pv_kanne.kulnr is null then pv_yksus.kulnr else pv_kanne.kulnr endif as pv_kulnr,
               if pv_kanne.talnr is null then pv_yksus.talnr else pv_kanne.talnr endif as pv_talnr,
               if pv_yksus.artnr is null then pv_kanne.artnr else pv_yksus.artnr endif as pv_artnr,
               if pv_kanne.tulnr is null then pv_yksus.tulnr else pv_kanne.tulnr endif as pv_tulnr,
               if pv_kanne.objectnr is null then pv_yksus.objectnr else pv_kanne.objectnr endif as pv_objectnr,
               if pv_kanne.osaknr is null then pv_yksus.osaknr else pv_kanne.osaknr endif as pv_osaknr,
               if pv_kanne.subjectnr is null then pv_yksus.subjectnr else pv_kanne.subjectnr endif as pv_subjectnr,
               if pv_kanne.suundnr is null then pv_yksus.suundnr else pv_kanne.suundnr endif as pv_suundnr,
               if pv_kanne.taotnr is null then pv_yksus.taotnr else pv_kanne.taotnr endif as pv_taotnr,
               pv_kanne.alliknr as pv_alliknr,
               if pv_obj.ravonr is null then if pv_kanne.ravonr3 is null then li_ravonr_11 else pv_kanne.ravonr3 endif else pv_obj.ravonr endif as pv_ravonr,
               li_skoop 
          from pv_kanne left outer join pv_yksus on pv_kanne.yksusnr=pv_yksus.yksusnr left outer join pv_obj on pv_kanne.objnr = pv_obj.objnr left outer join asulist on pv_kanne.asutnr = asulist.asutnr
          where pv_kanne.yksusnr=li_yksusnr and pv_kanne.kuup<=perlop and pv_obj.paralei=0 and pv_obj.deeb is not null and pv_obj.kree is not null
          group by pv_deeb,pv_partnr,pv_kree,pv_partnr2,pv_meetnr,pv_kulnr,pv_talnr,pv_artnr,pv_tulnr,pv_objectnr,pv_osaknr,pv_subjectnr,pv_suundnr,pv_taotnr,pv_alliknr,pv_ravonr;
    end if;
    if ls_yksuskonto is not null then
      open c_pv_kanne_maha;
      pv_kanne_maha_loop: loop
        --  loe pohivahend
        fetch next c_pv_kanne_maha into ls_deeb,ld_kuup,ls_nimi,ldc_summa,ldc_epk,ldc_jpk,ldc_ytk,li_partnr2,li_partnr,li_asutnr,li_meetnr,li_kulnr,li_talnr,li_artnr,li_tulnr,li_objectnr,li_osaknr,li_subjectnr,li_suundnr,li_taotnr,li_alliknr,li_ravonr;
        if sqlstate = err_notfound then -- kui eof
          leave pv_kanne_maha_loop
        end if;
        set ldc_epk=ldc_epk+ldc_jpk+ldc_ytk;
        set ldc_summa=ldc_summa-ldc_epk;
        if ldc_epk <> 0 and ls_yksuskree is not null then
          -- kas kulumi mahakandmise kanne eksisteerib koondis
          if li_detimp=0 and exists(select 1 from gl_pr_kanne_import where gl_pr_kanne_import.kuup = perlop and gl_pr_kanne_import.skoop = li_skoop and gl_pr_kanne_import."order" = ls_order and gl_pr_kanne_import.deeb = ls_yksuskree and gl_pr_kanne_import.partnr = li_partnr and gl_pr_kanne_import.kree = ls_yksuskonto and gl_pr_kanne_import.partnr2 = li_partnr2 and gl_pr_kanne_import.asutnr = li_asutnr and gl_pr_kanne_import.meetnr = li_meetnr and gl_pr_kanne_import.taotnr = li_taotnr and gl_pr_kanne_import.kulnr = li_kulnr and gl_pr_kanne_import.talnr = li_talnr and gl_pr_kanne_import.artnr = li_artnr and gl_pr_kanne_import.tulnr = li_tulnr and gl_pr_kanne_import.objectnr = li_objectnr and gl_pr_kanne_import.osaknr = li_osaknr and gl_pr_kanne_import.subjectnr = li_subjectnr and gl_pr_kanne_import.suundnr = li_suundnr and gl_pr_kanne_import.alliknr = li_alliknr and gl_pr_kanne_import.ravonr = li_ravonr) then
            update gl_pr_kanne_import set summa = summa+ldc_epk where gl_pr_kanne_import.kuup = perlop and gl_pr_kanne_import.skoop = li_skoop and gl_pr_kanne_import."order" = ls_order and gl_pr_kanne_import.deeb = ls_yksuskree and gl_pr_kanne_import.partnr = li_partnr and gl_pr_kanne_import.kree = ls_yksuskonto and gl_pr_kanne_import.partnr2 = li_partnr2 and gl_pr_kanne_import.asutnr = li_asutnr and gl_pr_kanne_import.meetnr = li_meetnr and gl_pr_kanne_import.taotnr = li_taotnr and gl_pr_kanne_import.kulnr = li_kulnr and gl_pr_kanne_import.talnr = li_talnr and gl_pr_kanne_import.artnr = li_artnr and gl_pr_kanne_import.tulnr = li_tulnr and gl_pr_kanne_import.objectnr = li_objectnr and gl_pr_kanne_import.osaknr = li_osaknr and gl_pr_kanne_import.subjectnr = li_subjectnr and gl_pr_kanne_import.suundnr = li_suundnr and gl_pr_kanne_import.alliknr = li_alliknr and gl_pr_kanne_import.ravonr = li_ravonr
          else
            set ls_info=Trim(ls_nimi+' kulumi mahakandmine');
            insert into gl_pr_kanne_import(tulnud,id_prog,kuup,summa,"info","order",deeb,partnr,kree,partnr2,asutnr,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop)
                                    values('Pohivarad',6,ld_kuup,ldc_epk,ls_info,ls_order,ls_yksuskree,li_partnr,ls_yksuskonto,li_partnr2,li_asutnr,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)
          end if
        end if;
        if ldc_summa <> 0 and ls_deeb is not null then
          -- kas pohivahendi mahakandmise kanne ei eksisteeri koondis
          if li_detimp=0 and exists(select 1 from gl_pr_kanne_import where gl_pr_kanne_import.kuup = perlop and gl_pr_kanne_import.skoop = li_skoop and gl_pr_kanne_import."order" = ls_order and gl_pr_kanne_import.deeb = ls_deeb and gl_pr_kanne_import.partnr = li_partnr and gl_pr_kanne_import.kree = ls_yksuskonto and gl_pr_kanne_import.partnr2 = li_partnr2 and gl_pr_kanne_import.asutnr = li_asutnr and gl_pr_kanne_import.meetnr = li_meetnr and gl_pr_kanne_import.taotnr = li_taotnr and gl_pr_kanne_import.kulnr = li_kulnr and gl_pr_kanne_import.talnr = li_talnr and gl_pr_kanne_import.artnr = li_artnr and gl_pr_kanne_import.tulnr = li_tulnr and gl_pr_kanne_import.objectnr = li_objectnr and gl_pr_kanne_import.osaknr = li_osaknr and gl_pr_kanne_import.subjectnr = li_subjectnr and gl_pr_kanne_import.suundnr = li_suundnr and gl_pr_kanne_import.alliknr = li_alliknr and gl_pr_kanne_import.ravonr = li_ravonr) then
            update gl_pr_kanne_import set summa = summa+ldc_summa where gl_pr_kanne_import.kuup = perlop and gl_pr_kanne_import.skoop = li_skoop and gl_pr_kanne_import."order" = ls_order and gl_pr_kanne_import.deeb = ls_deeb and gl_pr_kanne_import.partnr = li_partnr and gl_pr_kanne_import.kree = ls_yksuskonto and gl_pr_kanne_import.partnr2 = li_partnr2 and gl_pr_kanne_import.asutnr = li_asutnr and gl_pr_kanne_import.meetnr = li_meetnr and gl_pr_kanne_import.taotnr = li_taotnr and gl_pr_kanne_import.kulnr = li_kulnr and gl_pr_kanne_import.talnr = li_talnr and gl_pr_kanne_import.artnr = li_artnr and gl_pr_kanne_import.tulnr = li_tulnr and gl_pr_kanne_import.objectnr = li_objectnr and gl_pr_kanne_import.osaknr = li_osaknr and gl_pr_kanne_import.subjectnr = li_subjectnr and gl_pr_kanne_import.suundnr = li_suundnr and gl_pr_kanne_import.alliknr = li_alliknr and gl_pr_kanne_import.ravonr = li_ravonr
          else
            set ls_info=Trim(ls_nimi+' jääkmaksumuse mahakandmine');
            insert into gl_pr_kanne_import(tulnud,id_prog,kuup,summa,"info","order",deeb,partnr,kree,partnr2,asutnr,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop)
                                    values('Pohivarad',6,ld_kuup,ldc_summa,ls_info,ls_order,ls_deeb,li_partnr,ls_yksuskonto,li_partnr2,li_asutnr,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)
          end if
        end if
      end loop pv_kanne_maha_loop;
      close c_pv_kanne_maha
    end if
  end loop pv_yksus_loop;
  close c_pv_yksus;
  call pr_pv_kan_arvele(a_skoop,peralg,perlop);
  delete from gl_pr_kanne_import where summa=0;
  update gl_pr_kanne_import set deeb = null where(deeb = 'xxxxxyz' or Length(trim(deeb)) = 0);
  update gl_pr_kanne_import set kree = null where(kree = 'xxxxxyz' or Length(trim(kree)) = 0);
  update gl_pr_kanne_import set partnr = null where partnr = 0;
  update gl_pr_kanne_import set partnr2 = null where partnr2 = 0;
  update gl_pr_kanne_import set asutnr = null where asutnr = 0;
  update gl_pr_kanne_import set meetnr = null where meetnr = 0;
  update gl_pr_kanne_import set kulnr = null where kulnr = 0;
  update gl_pr_kanne_import set talnr = null where talnr = 0;
  update gl_pr_kanne_import set artnr = null where artnr = 0;
  update gl_pr_kanne_import set tulnr = null where tulnr = 0;
  update gl_pr_kanne_import set objectnr = null where objectnr = 0;
  update gl_pr_kanne_import set osaknr = null where osaknr = 0;
  update gl_pr_kanne_import set subjectnr = null where subjectnr = 0;
  update gl_pr_kanne_import set suundnr = null where suundnr = 0;
  update gl_pr_kanne_import set taotnr = null where taotnr = 0;
  update gl_pr_kanne_import set alliknr = null where alliknr = 0;
  update gl_pr_kanne_import set ravonr = null where ravonr = 0;
  if li_autokann=1 then
    call pr_autokann('Pohivarad autom.paral.')
  end if;
  insert into gl_pr_kanne( tulnud,id_prog,kuup,summa,"info","order",deeb,partnr,kree,partnr2,asutnr,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop)
    select 'Pohivarad',6,kuup,sum(summa),"info","order",deeb,partnr,kree,partnr2,asutnr,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop
      from gl_pr_kanne_import
      group by kuup,"info","order",deeb,partnr,kree,partnr2,asutnr,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop;
  delete from gl_pr_kanne where summa=0;
end