CREATE PROCEDURE "DBA"."pr_koond_pmen"( in a_skoop integer, in perlop date )
BEGIN
  declare ls_sql char(3000);
  declare ls_insert char(2000);
  declare ls_select char(3000);
  declare ls_from char(2000);
  declare ls_where char(2000);
  declare ls_group char(2000);
  declare ls_insert_level char(2000);
  declare ls_select_level char(2000);
  declare ls_group_level char(2000);
  declare ls_select_level_d char(2000);
  declare ls_select_level_k char(2000);
  declare ls_deebkree char(4);
  declare li_i smallint;
  declare li_meetlist smallint;
  declare li_kullist smallint;
  declare li_tallist smallint;
  declare li_tullist smallint;
  declare li_artlist smallint;
  declare li_objlist smallint;
  declare li_osaklist smallint;
  declare li_sublist smallint;
  declare li_suundlist smallint;
  declare li_taotlist smallint;
  declare li_alliklist smallint;
  declare li_ravolist smallint;
  declare li_asulist smallint;
  declare ls_saldod_konto char(7);
  declare li_saldod smallint;
  declare li_saldod_9 smallint;
  delete from gl_dk_koond;
  delete from gl_pr_koond;
  select pr_konfm.yld_saldod,pr_konfm.yld_saldod_9,pr_konfm.yld_saldod_konto,yld_imp_asutnr into li_saldod,li_saldod_9,ls_saldod_konto,li_asulist from pr_konfm where pr_konfm.id = 1;
  select dsn_meetlist,dsn_kullist,dsn_tallist,dsn_tullist,dsn_artlist,dsn_objlist,dsn_osaklist,dsn_sublist,dsn_suundlist,dsn_taotlist,dsn_alliklist,dsn_ravolist into li_meetlist,li_kullist,li_tallist,li_tullist,li_artlist,li_objlist,li_osaklist,li_sublist,li_suundlist,li_taotlist,li_alliklist,li_ravolist from tm_rmpt where id=1;
  set ls_group=' group by kanne_konto';
  set ls_insert_level = '';
  set ls_select_level_d = '';
  set ls_select_level_k = '';
  if li_meetlist=1 then
        set ls_insert_level=ls_insert_level+',meetnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.meetnr is null then 0 else pr_kanne.meetnr endif as kanne_meetnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.meetnr is null then 0 else pr_kanne.meetnr endif as kanne_meetnr';
        set ls_group=ls_group+',kanne_meetnr'
  end if;
  if li_kullist=1 then
        set ls_insert_level=ls_insert_level+',kulnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhkul is null or kontlist.djuhkul = 0 then 0 else pr_kanne.kulnr endif as kanne_kulnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhkul is null or kontlist.kjuhkul = 0 then 0 else pr_kanne.kulnr endif as kanne_kulnr';
        set ls_group=ls_group+',kanne_kulnr'
  end if;
  if li_tallist=1 then
        set ls_insert_level=ls_insert_level+',talnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.talnr is null or kontlist.djuhtal is null or kontlist.djuhtal = 0 then 0 else pr_kanne.talnr endif as kanne_talnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.talnr is null or kontlist.kjuhtal is null or kontlist.kjuhtal = 0 then 0 else pr_kanne.talnr endif as kanne_talnr';
        set ls_group=ls_group+',kanne_talnr'
  end if;
  if li_artlist=1 then
        set ls_insert_level=ls_insert_level+',artnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.artnr is null or kontlist.djuhart is null or kontlist.djuhart = 0 then 0 else pr_kanne.artnr endif as kanne_artnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.artnr is null or kontlist.kjuhart is null or kontlist.kjuhart = 0 then 0 else pr_kanne.artnr endif as kanne_artnr';
        set ls_group=ls_group+',kanne_artnr'
  end if;
  if li_tullist=1 then
        set ls_insert_level=ls_insert_level+',tulnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.tulnr is null or kontlist.djuhtul is null or kontlist.djuhtul = 0 then 0 else pr_kanne.tulnr endif as kanne_tulnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.tulnr is null or kontlist.kjuhtul is null or kontlist.kjuhtul = 0 then 0 else pr_kanne.tulnr endif as kanne_tulnr';
        set ls_group=ls_group+',kanne_tulnr'
  end if;
  if li_objlist=1 then
        set ls_insert_level=ls_insert_level+',objectnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.objectnr is null or kontlist.djuhobject is null or kontlist.djuhobject = 0 then 0 else pr_kanne.objectnr endif as kanne_objectnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.objectnr is null or kontlist.kjuhobject is null or kontlist.kjuhobject = 0 then 0 else pr_kanne.objectnr endif as kanne_objectnr';
        set ls_group=ls_group+',kanne_objectnr'
  end if;
  if li_osaklist=1 then
        set ls_insert_level=ls_insert_level+',osaknr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.osaknr is null or kontlist.djuhosak is null or kontlist.djuhosak = 0 then 0 else pr_kanne.osaknr endif as kanne_osaknr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.osaknr is null or kontlist.kjuhosak is null or kontlist.kjuhosak = 0 then 0 else pr_kanne.osaknr endif as kanne_osaknr';
        set ls_group=ls_group+',kanne_osaknr'
  end if;
  if li_sublist=1 then
        set ls_insert_level=ls_insert_level+',subjectnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.subjectnr is null or kontlist.djuhsubject is null or kontlist.djuhsubject = 0 then 0 else pr_kanne.subjectnr endif as kanne_subjectnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.subjectnr is null or kontlist.kjuhsubject is null or kontlist.kjuhsubject = 0 then 0 else pr_kanne.subjectnr endif as kanne_subjectnr';
        set ls_group=ls_group+',kanne_subjectnr'
  end if;
  if li_suundlist=1 then
        set ls_insert_level=ls_insert_level+',suundnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.suundnr is null or kontlist.djuhsuund is null or kontlist.djuhsuund = 0 then 0 else pr_kanne.suundnr endif as kanne_suundnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.suundnr is null or kontlist.kjuhsuund is null or kontlist.kjuhsuund = 0 then 0 else pr_kanne.suundnr endif as kanne_suundnr';
        set ls_group=ls_group+',kanne_suundnr'
  end if;
  if li_taotlist=1 then
        set ls_insert_level=ls_insert_level+',taotnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.taotnr is null then 0 else pr_kanne.taotnr endif as kanne_taotnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.taotnr is null then 0 else pr_kanne.taotnr endif as kanne_taotnr';
        set ls_group=ls_group+',kanne_taotnr'
  end if;
  if li_alliklist=1 then
        set ls_insert_level=ls_insert_level+',alliknr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.alliknr is null or kontlist.djuhallik is null or kontlist.djuhallik = 0 then 0 else pr_kanne.alliknr endif as kanne_alliknr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.alliknr is null or kontlist.kjuhallik is null or kontlist.kjuhallik = 0 then 0 else pr_kanne.alliknr endif as kanne_alliknr';
        set ls_group=ls_group+',kanne_alliknr'
  end if;
  if li_ravolist=1 then
        set ls_insert_level=ls_insert_level+',ravonr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.ravonr is null or kontlist.djuhravo is null or kontlist.djuhravo = 0 then 0 else pr_kanne.ravonr endif as kanne_ravonr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.ravonr is null or kontlist.kjuhravo is null or kontlist.kjuhravo = 0 then 0 else pr_kanne.ravonr endif as kanne_ravonr';
        set ls_group=ls_group+',kanne_ravonr'
  end if;
  if li_asulist=1 then
    set ls_insert_level=ls_insert_level+',asutnr';
    set ls_select_level_d=ls_select_level_d+',if pr_kanne.asutnr is null or kontlist.djuhpart is null or kontlist.djuhpart = 0 then 0 else pr_kanne.asutnr endif as kanne_asutnr';
    set ls_select_level_k=ls_select_level_k+',if pr_kanne.asutnr is null or kontlist.kjuhpart is null or kontlist.kjuhpart = 0 then 0 else pr_kanne.asutnr endif as kanne_asutnr';
    set ls_group=ls_group+',kanne_asutnr'
  end if;
  set ls_insert_level=ls_insert_level+',partnr';
  set ls_select_level_d=ls_select_level_d+',if kontlist.djuhpart is null or kontlist.djuhpart = 0 then 0 else (if kontlist.partnr is null then(if pr_kanne.partnr is null then 0 else pr_kanne.partnr endif) else kontlist.partnr endif) endif as kanne_partnr';
  set ls_select_level_k=ls_select_level_k+',if kontlist.kjuhpart is null or kontlist.kjuhpart = 0 then 0 else (if kontlist.partnr is null then(if pr_kanne.partnr2 is null then 0 else pr_kanne.partnr2 endif) else kontlist.partnr endif) endif as kanne_partnr';
  set ls_group=ls_group+',kanne_partnr';
  set ls_insert_level=ls_insert_level+')';
  set li_i=1;
  deebkree_loop: while li_i < 3 loop
    case li_i
    when 1 then
      set ls_deebkree='deeb';
      set ls_select_level=ls_select_level_d
    when 2 then
      set ls_deebkree='kree';
      set ls_select_level=ls_select_level_k
    end case;
    set ls_insert='insert into gl_dk_koond( konto,' + ls_deebkree + ls_insert_level;
    set ls_select=' select if li_saldod = 1 and Length(ls_saldod_konto)>0 and locate(''345678'',"left"(pr_kanne.'+ls_deebkree+',1)) > 0 then ls_saldod_konto else trim(pr_kanne.'+ls_deebkree+') endif as kanne_konto,sum(pr_kanne.summa)' + ls_select_level;
    set ls_from=' from pr_kanne left outer join kontlist on pr_kanne.'+ls_deebkree+' = kontlist.konto';
    set ls_where=' where (pr_kanne.skoop=a_skoop) and pr_kanne.'+ls_deebkree+' is not null and years(pr_kanne.kuup) <= years(perlop) and not(li_saldod_9 = 1 and "left"(pr_kanne.'+ls_deebkree+',1) = ''9'')';
    set ls_sql=ls_insert + ls_select + ls_from + ls_where + ls_group;
    execute immediate ls_sql;
    set li_i=li_i+1
  end loop;
  if length(ls_saldod_konto) > 0 then
    update gl_dk_koond set kulnr=0,talnr=0,artnr=0,tulnr=0,objectnr=0,osaknr=0,subjectnr=0,suundnr=0,taotnr=0,meetnr=0,alliknr=0,ravonr=0,partnr=0 where konto=ls_saldod_konto
  end if;
  -- koond
  insert into gl_pr_koond( konto,deeb,kree,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,meetnr,taotnr,asutnr,partnr) 
    select gl_dk_koond.konto,sum(gl_dk_koond.deeb),sum(gl_dk_koond.kree),kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,meetnr,taotnr,asutnr,partnr
      from gl_dk_koond
      group by gl_dk_koond.konto,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,meetnr,taotnr,asutnr,partnr;
  -- loppjaagid
  update gl_pr_koond set ldeeb=deeb-kree,lkree=kree-deeb;
  update gl_pr_koond set ldeeb=0 where gl_pr_koond.ldeeb<0;
  update gl_pr_koond set lkree=0 where gl_pr_koond.lkree<0;
  delete from gl_pr_koond where deeb=0 and kree=0
END