ALTER PROCEDURE "dba"."pr_dk_kan"(in a_skoop integer,in peralg date,in perlop date)
begin
  declare li_objnr integer;
  declare ls_order char(3);
  declare ls_djkonto char(7);
  declare ls_kjkonto char(7);
  declare li_skoop integer;
  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_spank smallint;
  declare ld_kjperalg date;
  declare ls_dk_kontod char(5000);
  declare err_notfound exception for sqlstate value '02000';
  declare c_dk_obj dynamic scroll cursor for select dk_obj.djkonto,dk_obj.kjkonto,dk_obj."order",dk_obj.objnr,dk_obj.skoop,dk_obj.spank,get_peralg(dk_obj.kjperalg) from dk_obj where(dk_obj.skoop = a_skoop or a_skoop = 0) and dk_obj."order" is not null;
  delete from gl_pr_kanne;
  delete from gl_pr_kanne_import;
  select yld_autokann,
         yld_imp_dk_det,
         if yld_imp_dk_det=1 or yld_imp_kuup=1 then 1 else 0 endif,
         if yld_imp_dk_det=1 or yld_imp_arvenr=1 then 1 else 0 endif,
         if yld_imp_dk_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;
  open c_dk_obj;
  dk_obj_loop: loop
    fetch next c_dk_obj into ls_djkonto,ls_kjkonto,ls_order,li_objnr,li_skoop,li_spank,ld_kjperalg;
    if sqlstate = err_notfound then
      leave dk_obj_loop
    end if;
    set ls_dk_kontod=get_dk_kontod(li_skoop,0);
    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)
      select 'Arveldused',2,
             if li_imp_kuup=1 then dk_kanne.kuup else perlop endif as dk_kuup,
             sum(dk_kanne.summa),
             if li_detimp=1 then Left(Trim(dk_kanne.arvenr+' '+dk_alus.nimi+' '+dk_kanne."info"),200) else if li_imp_arvenr=1 then Trim(dk_kanne.arvenr) else null endif endif as dk_info,
             ls_order,
             if dk_kanne.sisse=1 then ls_djkonto else if locate(ls_dk_kontod,';'+"Left"(dk_kanne.konto+Space(7),7)+';')>0 then null else dk_kanne.konto endif endif as dk_deeb,
             if dk_kanne.sisse=1 then asulist.partnr else if dk_kanne.partnr is null then asulist.partnr else dk_kanne.partnr endif endif as dk_partnr,
             if dk_kanne.sisse=1 then if locate(ls_dk_kontod,';'+"Left"(dk_kanne.konto+Space(7),7)+';')>0 then null else dk_kanne.konto endif else ls_djkonto endif as dk_kree,
             if dk_kanne.sisse=1 then if dk_kanne.partnr is null then asulist.partnr else dk_kanne.partnr endif else asulist.partnr endif as dk_partnr2,
             if li_detimp=1 then dk_kanne.asutnr else if li_imp_asutnr=0 or left(partlist.part,6)='800699' then null else dk_kanne.asutnr endif endif as dk_asutnr,
             dk_kanne.meetnr,dk_kanne.kulnr,dk_kanne.talnr,dk_kanne.artnr,dk_kanne.tulnr,dk_kanne.objectnr,dk_kanne.osaknr,dk_kanne.subjectnr,dk_kanne.suundnr,dk_kanne.taotnr,dk_kanne.alliknr,dk_kanne.ravonr,
             li_skoop
             from dk_kanne left outer join dk_alus on dk_kanne.alusnr=dk_alus.alusnr left outer join asulist on dk_kanne.asutnr = asulist.asutnr left outer join partlist on asulist.partnr=partlist.partnr
             where dk_kanne.konto is not null and dk_kanne.asutnr is not null and dk_kanne.objnr = li_objnr and(dk_kanne.kuup>=peralg) and(dk_kanne.kuup<=perlop)
             group by dk_kuup,dk_info,dk_deeb,dk_partnr,dk_kree,dk_partnr2,dk_asutnr,dk_kanne.meetnr,dk_kanne.kulnr,dk_kanne.talnr,dk_kanne.artnr,dk_kanne.tulnr,dk_kanne.objectnr,dk_kanne.osaknr,dk_kanne.subjectnr,dk_kanne.suundnr,dk_kanne.taotnr,dk_kanne.alliknr,dk_kanne.ravonr;
    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)
      select 'Arveldused',2,
             if li_imp_kuup=1 then dk_kanne.kuup else perlop endif as dk_kuup,
             sum(if kblist.proportsioon is not null and li_spank=1 then summa2 - Round(summa2*kblist.proportsioon/100,2) else summa2 endif),
             if li_detimp=1 then Left(Trim(dk_kanne.arvenr+' '+dk_alus.nimi+' '+dk_kanne."info"),200) else if li_imp_arvenr=1 then Trim(dk_kanne.arvenr) else null endif endif as dk_info,
             ls_order,
             if dk_kanne.sisse=1 then if kblist.djkonto is null then ls_djkonto else kblist.djkonto endif else if li_spank=1 then if kblist.d_konto is null then dk_kanne.konto else kblist.d_konto endif else if kblist.k_konto is null then dk_kanne.konto else kblist.k_konto endif endif endif as dk_deeb,
             if dk_kanne.sisse=1 then asulist.partnr else if dk_kanne.partnr is null then asulist.partnr else dk_kanne.partnr endif endif as dk_partnr,
             if dk_kanne.sisse=1 then if li_spank=1 then if kblist.d_konto is null then dk_kanne.konto else kblist.d_konto endif else if kblist.k_konto is null then dk_kanne.konto else kblist.k_konto endif endif else if kblist.djkonto is null then ls_djkonto else kblist.djkonto endif endif as dk_kree,
             if dk_kanne.sisse=1 then if dk_kanne.partnr is null then asulist.partnr else dk_kanne.partnr endif else asulist.partnr endif as dk_partnr2,
             if li_detimp=1 then dk_kanne.asutnr else if li_imp_asutnr=0 or left(partlist.part,6)='800699' then null else dk_kanne.asutnr endif endif as dk_asutnr,
             dk_kanne.meetnr,dk_kanne.kulnr,dk_kanne.talnr,if kblist.artnr is null then dk_kanne.artnr else kblist.artnr endif as dk_artnr,dk_kanne.tulnr,dk_kanne.objectnr,dk_kanne.osaknr,dk_kanne.subjectnr,dk_kanne.suundnr,dk_kanne.taotnr,dk_kanne.alliknr,dk_kanne.ravonr,
             li_skoop
             from dk_kanne left outer join dk_alus on dk_kanne.alusnr=dk_alus.alusnr left outer join asulist on dk_kanne.asutnr = asulist.asutnr key left outer join kblist left outer join partlist on asulist.partnr=partlist.partnr
             where dk_kanne.asutnr>0 and dk_kanne.objnr = li_objnr and(dk_kanne.kuup >= peralg) and(dk_kanne.kuup <= perlop)
             group by dk_kuup,dk_info,dk_deeb,dk_partnr,dk_kree,dk_partnr2,dk_asutnr,dk_kanne.meetnr,dk_kanne.kulnr,dk_kanne.talnr,dk_artnr,dk_kanne.tulnr,dk_kanne.objectnr,dk_kanne.osaknr,dk_kanne.subjectnr,dk_kanne.suundnr,dk_kanne.taotnr,dk_kanne.alliknr,dk_kanne.ravonr;
    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)
      select 'Arveldused',2,
             if li_imp_kuup=1 then dk_kanne.kuup else perlop endif as dk_kuup,sum(if kblist.proportsioon is not null then Round(summa2*kblist.proportsioon/100,2) else 0 endif),
             if li_detimp=1 then Left(Trim(dk_kanne.arvenr+' '+dk_alus.nimi+' '+dk_kanne."info"),200) else if li_imp_arvenr=1 then Trim(dk_kanne.arvenr) else null endif endif as dk_info,
             ls_order,
             if dk_kanne.sisse=1 then if kblist.djkonto is null then ls_djkonto else kblist.djkonto endif else if kblist.prop_konto is null then dk_kanne.konto else kblist.prop_konto endif endif as dk_deeb,
             if dk_kanne.sisse=1 then asulist.partnr else if dk_kanne.partnr is null then asulist.partnr else dk_kanne.partnr endif endif as dk_partnr,
             if dk_kanne.sisse=1 then if kblist.prop_konto is null then dk_kanne.konto else kblist.prop_konto endif else if kblist.djkonto is null then ls_djkonto else kblist.djkonto endif endif as dk_kree,
             if dk_kanne.sisse=1 then if dk_kanne.partnr is null then asulist.partnr else dk_kanne.partnr endif else asulist.partnr endif as dk_partnr2,
             if li_detimp=1 then dk_kanne.asutnr else if li_imp_asutnr=0 or left(partlist.part,6)='800699' then null else dk_kanne.asutnr endif endif as dk_asutnr,
             dk_kanne.meetnr,dk_kanne.kulnr,dk_kanne.talnr,if kblist.artnr is null then dk_kanne.artnr else kblist.artnr endif as dk_artnr,dk_kanne.tulnr,dk_kanne.objectnr,dk_kanne.osaknr,dk_kanne.subjectnr,dk_kanne.suundnr,dk_kanne.taotnr,dk_kanne.alliknr,dk_kanne.ravonr,
             li_skoop
             from dk_kanne left outer join dk_alus on dk_kanne.alusnr=dk_alus.alusnr left outer join asulist on dk_kanne.asutnr = asulist.asutnr key left outer join kblist left outer join partlist on asulist.partnr=partlist.partnr
             where dk_kanne.asutnr>0 and dk_kanne.objnr = li_objnr and(dk_kanne.kuup >= peralg) and(dk_kanne.kuup <= perlop) and kblist.proportsioon is not null and li_spank=1
             group by dk_kuup,dk_info,dk_deeb,dk_partnr,dk_kree,dk_partnr2,dk_asutnr,dk_kanne.meetnr,dk_kanne.kulnr,dk_kanne.talnr,dk_artnr,dk_kanne.tulnr,dk_kanne.objectnr,dk_kanne.osaknr,dk_kanne.subjectnr,dk_kanne.suundnr,dk_kanne.taotnr,dk_kanne.alliknr,dk_kanne.ravonr;
    -- Ettemaksu jagamine eraldi kontole
    if ls_kjkonto is not null and peralg>=ld_kjperalg then
      delete from gl_pr_koond;
      delete from gl_dk_koond;
      insert into gl_pr_koond( konto,asutnr,adeeb,akree,deeb,kree)
        select '1',dk_kanne.asutnr,sum(if dk_kanne.sisse = 1 and dk_kanne.kuup < peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif),sum(if dk_kanne.sisse = 0 and dk_kanne.kuup < peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif),sum(if dk_kanne.sisse = 1 and dk_kanne.kuup >= peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif),sum(if dk_kanne.sisse = 0 and dk_kanne.kuup >= peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif)
          from dk_kanne
          where dk_kanne.objnr=li_objnr and dk_kanne.asutnr>0 and dk_kanne.kuup<=perlop
          group by dk_kanne.asutnr order by dk_kanne.asutnr asc;
      insert into gl_pr_koond( konto,asutnr,adeeb,akree,deeb,kree)
        select '2',kp_kanne.asutnr,sum(if kp_kanne.sisse = 0 and kp_kanne.kuup < peralg then kp_kanne.summa else 0 endif),sum(if kp_kanne.sisse = 1 and kp_kanne.kuup < peralg then kp_kanne.summa else 0 endif),sum(if kp_kanne.sisse = 0 and kp_kanne.kuup >= peralg then kp_kanne.summa else 0 endif),sum(if kp_kanne.sisse = 1 and kp_kanne.kuup >= peralg then kp_kanne.summa else 0 endif)
          from kp_kanne left outer join kp_obj on kp_kanne.objnr = kp_obj.objnr
          where kp_kanne.konto=ls_djkonto and kp_obj.skoop=li_skoop and kp_kanne.asutnr > 0 and kp_kanne.kuup <= perlop
        group by kp_kanne.asutnr order by kp_kanne.asutnr asc;
      insert into gl_dk_koond( asutnr,adeeb,akree,deeb,kree) 
        select gl_pr_koond.asutnr,sum(gl_pr_koond.adeeb),sum(gl_pr_koond.akree),sum(gl_pr_koond.deeb),sum(gl_pr_koond.kree)
          from gl_pr_koond group by gl_pr_koond.asutnr order by gl_pr_koond.asutnr asc;
      update gl_dk_koond set adeeb = adeeb-akree,akree = akree-adeeb;
      update gl_dk_koond set adeeb = 0 where gl_dk_koond.adeeb < 0;
      update gl_dk_koond set akree = 0 where gl_dk_koond.akree < 0;
      update gl_dk_koond set ldeeb = adeeb-akree+deeb-kree,lkree = akree-adeeb+kree-deeb;
      update gl_dk_koond set ldeeb = 0 where gl_dk_koond.ldeeb < 0;
      update gl_dk_koond set lkree = 0 where gl_dk_koond.lkree < 0;
      if peralg=ld_kjperalg then
        update gl_dk_koond set adeeb=0,akree=0;
      end if;
      if li_spank=0 then
        delete from gl_dk_koond where akree=0 and lkree=0 or akree=lkree;
        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)
          select 'Arveldused',2,perlop,
                 if gl_dk_koond.lkree>gl_dk_koond.akree then gl_dk_koond.lkree-gl_dk_koond.akree else gl_dk_koond.akree-gl_dk_koond.lkree endif,
                 'Ettemaks',
                 ls_order,
                 if gl_dk_koond.lkree>gl_dk_koond.akree then ls_djkonto else ls_kjkonto endif,
                 asulist.partnr,
                 if gl_dk_koond.lkree>gl_dk_koond.akree then ls_kjkonto else ls_djkonto endif,
                 asulist.partnr,
                 if li_detimp=1 then gl_dk_koond.asutnr else if li_imp_asutnr=0 or left(partlist.part,6)='800699' then null else gl_dk_koond.asutnr endif endif,
                 null,null,null,null,null,null,null,null,null,null,null,null,
                 li_skoop
                 from gl_dk_koond left outer join asulist on gl_dk_koond.asutnr=asulist.asutnr left outer join partlist on asulist.partnr=partlist.partnr;
      else
        delete from gl_dk_koond where adeeb=0 and ldeeb=0 or adeeb=ldeeb;
        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)
          select 'Arveldused',2,perlop,
                 if gl_dk_koond.ldeeb>gl_dk_koond.adeeb then gl_dk_koond.ldeeb-gl_dk_koond.adeeb else gl_dk_koond.adeeb-gl_dk_koond.ldeeb endif,
                 'Ettemaks',
                 ls_order,
                 if gl_dk_koond.ldeeb>gl_dk_koond.adeeb then ls_kjkonto else ls_djkonto endif,
                 asulist.partnr,
                 if gl_dk_koond.ldeeb>gl_dk_koond.adeeb then ls_djkonto else ls_kjkonto endif,
                 asulist.partnr,
                 if li_detimp=1 then gl_dk_koond.asutnr else if li_imp_asutnr=0 or left(partlist.part,6)='800699' then null else gl_dk_koond.asutnr endif endif,
                 null,null,null,null,null,null,null,null,null,null,null,null,
                 li_skoop
                 from gl_dk_koond left outer join asulist on gl_dk_koond.asutnr=asulist.asutnr left outer join partlist on asulist.partnr=partlist.partnr;
      end if;
    end if;
  end loop dk_obj_loop;
  close c_dk_obj;
  if li_autokann=1 then
    call pr_autokann('Arveldused 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 'Arveldused',2,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