ALTER PROCEDURE "DBA"."ea_tul_kokku"( in a_skoop integer, in a_komb_id integer, in a_year integer, in a_peralg date default ymd(2014,1,1), in a_perlop date default ymd(2014,1,1), in a_skoopkood char(16) default '')
BEGIN
  declare li_tyyp smallint;
  declare li_colcount smallint;
  declare ld_peralg date;
  declare ld_perlop date;
  declare li_objnr integer;
  declare li_eaobj smallint;
  declare ls_insert char(1500);
  declare ls_colnr char(5);
  declare ls_ea_summa char(180);
  declare ls_kp_summa char(180);
  declare ls_pr_summa char(180);
  declare li_id integer;
  declare li_id_lkd integer;
  declare li_varaasta smallint;
  declare li_varper smallint;
  declare li_sum_meet integer;
  declare li_sum_kul integer;
  declare li_sum_tal integer;
  declare li_sum_tul integer;
  declare li_sum_object integer;
  declare li_sum_osak integer;
  declare li_sum_subject integer;
  declare li_sum_suund integer;
  declare li_sum_taot integer;
  declare li_tekont smallint;
  declare li_kaasa integer;
  declare err_notfound exception for sqlstate value '02000';
  declare c_ea_col dynamic scroll cursor for select ea_col_def."id",ea_col_def.tyyp,ea_col_def.objnr,ea_col_def.eaobj,ea_col_def.peralg,ea_col_def.perlop,ea_col_def.varaasta,ea_col_def.varper from ea_col_def left outer join ea_lkd_col on ea_col_def."id"=ea_lkd_col.id_col where
      ea_lkd_col.id_lkd = li_id_lkd order by ea_col_def.jrknr asc, ea_col_def."id";
  select ea_komb.id_lkd,ea_komb.tr_sum_meet,ea_komb.tr_sum_kul,ea_komb.tr_sum_tal,ea_komb.tr_sum_tul,ea_komb.tr_sum_object,ea_komb.tr_sum_osak,ea_komb.tr_sum_subject,ea_komb.tr_sum_suund,ea_komb.tr_sum_taot into li_id_lkd,li_sum_meet,li_sum_kul,li_sum_tal,li_sum_tul,li_sum_object,li_sum_osak,li_sum_subject,li_sum_suund,li_sum_taot from ea_komb where "id" = a_komb_id;
  select tm_rmpt.k_ea_tekont into li_tekont from tm_rmpt where tm_rmpt.id=1;
  set li_colcount=0;
  delete from gl_ea_tulkond;
  delete from gl_ea_kulkond;
  delete from gl_selectall;
  set ls_ea_summa='ea_kanne.summa';
  set ls_kp_summa='kp_kanne.summa';
  set ls_pr_summa='pr_kanne.summa';
  open c_ea_col;
  ea_col_loop: loop
    fetch next c_ea_col into li_id,li_tyyp,li_objnr,li_eaobj,ld_peralg,ld_perlop,li_varaasta,li_varper;
    if sqlstate = err_notfound then
      leave ea_col_loop
    end if;
    if li_varper=1 then
      set ld_peralg=a_peralg;
      set ld_perlop=a_perlop;
    else
      if li_varaasta=1 then
        set ld_peralg=ymd(a_year,month(ld_peralg),day(ld_peralg));
        if month(ld_perlop)=2 then
          if day(ld_perlop)>27 then
            set ld_perlop=get_perlop(ymd(a_year,month(ld_perlop),28))
          else
            set ld_perlop=ymd(a_year,month(ld_perlop),day(ld_perlop));
          end if;
        else
          set ld_perlop=ymd(a_year,month(ld_perlop),day(ld_perlop))
        end if
      end if
    end if;
    set li_colcount=li_colcount+1;
    set ls_colnr = 'col'+Right('00'+convert(char(2),li_colcount),2);
    insert into gl_selectall(id1,s1) values (li_id,ls_colnr);
    if li_tyyp=1 then
      set ls_insert = 'insert into gl_ea_kulkond( meetnr,kulnr,talnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,'+ls_colnr+')
                         select ea_kanne.meetnr,ea_kanne.kulnr,ea_kanne.talnr,ea_kanne.tulnr,ea_kanne.objectnr,ea_kanne.osaknr,ea_kanne.subjectnr,ea_kanne.suundnr,ea_kanne.taotnr,sum('+ls_ea_summa+')
                           from ea_kanne left outer join ea_obj on ea_kanne.objnr=ea_obj.objnr left outer join skoobid on ea_obj.skoop=skoobid.skoop
                           where (ea_obj.skoop=a_skoop or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and (ea_kanne.objnr=li_objnr or (li_objnr is null and ((li_eaobj=0 and ea_obj.eaobj=1) or (li_eaobj=1 and ea_obj.eaobj1=1) or (li_eaobj=2 and ea_obj.eaobj2=1) or (li_eaobj=3 and ea_obj.eaobj3=1)))) and (ea_kanne.kuup>=ld_peralg) and ea_kanne.kuup<=ld_perlop and ea_kanne.artnr is null and ea_kanne.tulnr is not null
                           group by ea_kanne.meetnr,ea_kanne.kulnr,ea_kanne.talnr,ea_kanne.tulnr,ea_kanne.objectnr,ea_kanne.osaknr,ea_kanne.subjectnr,ea_kanne.suundnr,ea_kanne.taotnr';
      execute immediate ls_insert
    elseif li_tyyp=2 then
      set ls_insert = 'insert into gl_ea_kulkond( meetnr,kulnr,talnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,'+ls_colnr+')
                         select kp_kanne.meetnr,kp_kanne.kulnr,kp_kanne.talnr,kp_kanne.tulnr,kp_kanne.objectnr,kp_kanne.osaknr,kp_kanne.subjectnr,kp_kanne.suundnr,kp_kanne.taotnr,sum(if kp_kanne.sisse=1 then '+ls_kp_summa+' else -1*'+ls_kp_summa+' endif)
                           from kp_kanne left outer join kp_obj on kp_kanne.objnr=kp_obj.objnr left outer join skoobid on kp_obj.skoop=skoobid.skoop
                           where (kp_obj.skoop = a_skoop or a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and (kp_obj.eaobj = 1) and(kp_obj.eelarve=0) and(kp_kanne.kuup >= ld_peralg) and(kp_kanne.kuup <= ld_perlop) and(kp_kanne.artnr is null) and kp_kanne.tulnr is not null
                           group by kp_kanne.meetnr,kp_kanne.kulnr,kp_kanne.talnr,kp_kanne.tulnr,kp_kanne.objectnr,kp_kanne.osaknr,kp_kanne.subjectnr,kp_kanne.suundnr,kp_kanne.taotnr';
      execute immediate ls_insert
    elseif li_tyyp=3 then
      if li_tekont=1 then
        set ls_insert = 'insert into gl_ea_kulkond( meetnr,kulnr,talnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,'+ls_colnr+')
                           select pr_kanne.meetnr,pr_kanne.kulnr,pr_kanne.talnr,kontlist.tulnr,pr_kanne.objectnr,pr_kanne.osaknr,pr_kanne.subjectnr,pr_kanne.suundnr,pr_kanne.taotnr,(-1)*sum('+ls_pr_summa+')
                             from pr_kanne left outer join kontlist on pr_kanne.deeb = kontlist.konto left outer join skoobid on pr_kanne.skoop=skoobid.skoop
                             where (pr_kanne.skoop = a_skoop or a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and kontlist.kulukonto=1 and kontlist.tulnr is not null and pr_kanne.deeb is not null and pr_kanne.kuup >= ld_peralg and pr_kanne.kuup <= ld_perlop
                             group by pr_kanne.meetnr,pr_kanne.kulnr,pr_kanne.talnr,kontlist.tulnr,pr_kanne.objectnr,pr_kanne.osaknr,pr_kanne.subjectnr,pr_kanne.suundnr,pr_kanne.taotnr';
        execute immediate ls_insert;
        set ls_insert = 'insert into gl_ea_kulkond( meetnr,kulnr,talnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,'+ls_colnr+')
                           select pr_kanne.meetnr,pr_kanne.kulnr,pr_kanne.talnr,kontlist.tulnr,pr_kanne.objectnr,pr_kanne.osaknr,pr_kanne.subjectnr,pr_kanne.suundnr,pr_kanne.taotnr,sum('+ls_pr_summa+')
                             from pr_kanne left outer join kontlist on pr_kanne.kree = kontlist.konto left outer join skoobid on pr_kanne.skoop=skoobid.skoop
                             where (pr_kanne.skoop = a_skoop or a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and kontlist.kulukonto=1 and kontlist.tulnr is not null and pr_kanne.kree is not null and pr_kanne.kuup >= ld_peralg and pr_kanne.kuup <= ld_perlop
                             group by pr_kanne.meetnr,pr_kanne.kulnr,pr_kanne.talnr,kontlist.tulnr,pr_kanne.objectnr,pr_kanne.osaknr,pr_kanne.subjectnr,pr_kanne.suundnr,pr_kanne.taotnr';
        execute immediate ls_insert
      else
        set ls_insert = 'insert into gl_ea_kulkond( meetnr,kulnr,talnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,'+ls_colnr+')
                           select pr_kanne.meetnr,pr_kanne.kulnr,pr_kanne.talnr,pr_kanne.tulnr,pr_kanne.objectnr,pr_kanne.osaknr,pr_kanne.subjectnr,pr_kanne.suundnr,pr_kanne.taotnr,(-1)*sum('+ls_pr_summa+')
                             from pr_kanne left outer join kontlist on pr_kanne.deeb = kontlist.konto left outer join skoobid on pr_kanne.skoop=skoobid.skoop
                             where (pr_kanne.skoop = a_skoop or a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and kontlist.kulukonto=1 and pr_kanne.artnr is null and pr_kanne.tulnr is not null and pr_kanne.deeb is not null and pr_kanne.kuup >= ld_peralg and pr_kanne.kuup <= ld_perlop
                             group by pr_kanne.meetnr,pr_kanne.kulnr,pr_kanne.talnr,pr_kanne.tulnr,pr_kanne.objectnr,pr_kanne.osaknr,pr_kanne.subjectnr,pr_kanne.suundnr,pr_kanne.taotnr';
        execute immediate ls_insert;
        set ls_insert = 'insert into gl_ea_kulkond( meetnr,kulnr,talnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,'+ls_colnr+')
                           select pr_kanne.meetnr,pr_kanne.kulnr,pr_kanne.talnr,pr_kanne.tulnr,pr_kanne.objectnr,pr_kanne.osaknr,pr_kanne.subjectnr,pr_kanne.suundnr,pr_kanne.taotnr,sum('+ls_pr_summa+')
                             from pr_kanne left outer join kontlist on pr_kanne.kree = kontlist.konto left outer join skoobid on pr_kanne.skoop=skoobid.skoop
                             where (pr_kanne.skoop = a_skoop or a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and kontlist.kulukonto=1 and pr_kanne.artnr is null and pr_kanne.tulnr is not null and pr_kanne.kree is not null and pr_kanne.kuup >= ld_peralg and pr_kanne.kuup <= ld_perlop
                             group by pr_kanne.meetnr,pr_kanne.kulnr,pr_kanne.talnr,pr_kanne.tulnr,pr_kanne.objectnr,pr_kanne.osaknr,pr_kanne.subjectnr,pr_kanne.suundnr,pr_kanne.taotnr';
        execute immediate ls_insert
      end if
    end if
  end loop ea_col_loop;
  close c_ea_col;
  set ls_insert = 'insert into gl_ea_tulkond( meetnr,kulnr,talnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20)
                     select gl_ea_kulkond.meetnr,gl_ea_kulkond.kulnr,gl_ea_kulkond.talnr,gl_ea_kulkond.tulnr,gl_ea_kulkond.objectnr,gl_ea_kulkond.osaknr,gl_ea_kulkond.subjectnr,gl_ea_kulkond.suundnr,gl_ea_kulkond.taotnr,sum(col01),sum(col02),sum(col03),sum(col04),sum(col05),sum(col06),sum(col07),sum(col08),sum(col09),sum(col10),sum(col11),sum(col12),sum(col13),sum(col14),sum(col15),sum(col16),sum(col17),sum(col18),sum(col19),sum(col20)
                       from gl_ea_kulkond
                       group by gl_ea_kulkond.meetnr,gl_ea_kulkond.kulnr,gl_ea_kulkond.talnr,gl_ea_kulkond.tulnr,gl_ea_kulkond.objectnr,gl_ea_kulkond.osaknr,gl_ea_kulkond.subjectnr,gl_ea_kulkond.suundnr,gl_ea_kulkond.taotnr';
  execute immediate ls_insert;
  if li_sum_meet > 0 then
    select if kaasa=1 then 1 else 0 endif into li_kaasa from kp_sksum where sksumnr=li_sum_meet;
    if li_kaasa=1 then
      delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_meet and gl_ea_tulkond.meetnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is null
    else
      select count() into li_kaasa from kp_sksum_mark where sksumnr=li_sum_meet;
      if li_kaasa is null then
        set li_kaasa=0
      end if;
      if li_kaasa=0 then
        delete from gl_ea_tulkond where gl_ea_tulkond.meetnr>0
      else
        delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_meet and gl_ea_tulkond.meetnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is not null
      end if;
    end if;
  end if;
  if li_sum_kul > 0 then
    select if kaasa=1 then 1 else 0 endif into li_kaasa from kp_sksum where sksumnr=li_sum_kul;
    if li_kaasa=1 then
      delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_kul and gl_ea_tulkond.kulnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is null
    else
      select count() into li_kaasa from kp_sksum_mark where sksumnr=li_sum_kul;
      if li_kaasa is null then
        set li_kaasa=0
      end if;
      if li_kaasa=0 then
        delete from gl_ea_tulkond where gl_ea_tulkond.kulnr>0
      else
        delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_kul and gl_ea_tulkond.kulnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is not null
      end if;
    end if;
  end if;
  if li_sum_tal > 0 then
    select if kaasa=1 then 1 else 0 endif into li_kaasa from kp_sksum where sksumnr=li_sum_tal;
    if li_kaasa=1 then
      delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_tal and gl_ea_tulkond.talnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is null
    else
      select count() into li_kaasa from kp_sksum_mark where sksumnr=li_sum_tal;
      if li_kaasa is null then
        set li_kaasa=0
      end if;
      if li_kaasa=0 then
        delete from gl_ea_tulkond where gl_ea_tulkond.talnr>0
      else
        delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_tal and gl_ea_tulkond.talnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is not null
      end if;
    end if;
  end if;
  if li_sum_tul > 0 then
    select if kaasa=1 then 1 else 0 endif into li_kaasa from kp_sksum where sksumnr=li_sum_tul;
    if li_kaasa=1 then
      delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_tul and gl_ea_tulkond.tulnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is null
    else
      select count() into li_kaasa from kp_sksum_mark where sksumnr=li_sum_tul;
      if li_kaasa is null then
        set li_kaasa=0
      end if;
      if li_kaasa=0 then
        delete from gl_ea_tulkond where gl_ea_tulkond.tulnr>0
      else
        delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_tul and gl_ea_tulkond.tulnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is not null
      end if;
    end if;
  end if;
  if li_sum_object > 0 then
    select if kaasa=1 then 1 else 0 endif into li_kaasa from kp_sksum where sksumnr=li_sum_object;
    if li_kaasa=1 then
      delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_object and gl_ea_tulkond.objectnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is null
    else
      select count() into li_kaasa from kp_sksum_mark where sksumnr=li_sum_object;
      if li_kaasa is null then
        set li_kaasa=0
      end if;
      if li_kaasa=0 then
        delete from gl_ea_tulkond where gl_ea_tulkond.objectnr>0
      else
        delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_object and gl_ea_tulkond.objectnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is not null
      end if;
    end if;
  end if;
  if li_sum_osak > 0 then
    select if kaasa=1 then 1 else 0 endif into li_kaasa from kp_sksum where sksumnr=li_sum_osak;
    if li_kaasa=1 then
      delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_osak and gl_ea_tulkond.osaknr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is null
    else
      select count() into li_kaasa from kp_sksum_mark where sksumnr=li_sum_osak;
      if li_kaasa is null then
        set li_kaasa=0
      end if;
      if li_kaasa=0 then
        delete from gl_ea_tulkond where gl_ea_tulkond.osaknr>0
      else
        delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_osak and gl_ea_tulkond.osaknr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is not null
      end if;
    end if;
  end if;
  if li_sum_subject > 0 then
    select if kaasa=1 then 1 else 0 endif into li_kaasa from kp_sksum where sksumnr=li_sum_subject;
    if li_kaasa=1 then
      delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_subject and gl_ea_tulkond.subjectnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is null
    else
      select count() into li_kaasa from kp_sksum_mark where sksumnr=li_sum_subject;
      if li_kaasa is null then
        set li_kaasa=0
      end if;
      if li_kaasa=0 then
        delete from gl_ea_tulkond where gl_ea_tulkond.subjectnr>0
      else
        delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_subject and gl_ea_tulkond.subjectnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is not null
      end if;
    end if;
  end if;
  if li_sum_suund > 0 then
    select if kaasa=1 then 1 else 0 endif into li_kaasa from kp_sksum where sksumnr=li_sum_suund;
    if li_kaasa=1 then
      delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_suund and gl_ea_tulkond.suundnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is null
    else
      select count() into li_kaasa from kp_sksum_mark where sksumnr=li_sum_suund;
      if li_kaasa is null then
        set li_kaasa=0
      end if;
      if li_kaasa=0 then
        delete from gl_ea_tulkond where gl_ea_tulkond.suundnr>0
      else
        delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_suund and gl_ea_tulkond.suundnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is not null
      end if;
    end if;
  end if;
  if li_sum_taot > 0 then
    select if kaasa=1 then 1 else 0 endif into li_kaasa from kp_sksum where sksumnr=li_sum_taot;
    if li_kaasa=1 then
      delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_taot and gl_ea_tulkond.taotnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is null
    else
      select count() into li_kaasa from kp_sksum_mark where sksumnr=li_sum_taot;
      if li_kaasa is null then
        set li_kaasa=0
      end if;
      if li_kaasa=0 then
        delete from gl_ea_tulkond where gl_ea_tulkond.taotnr>0
      else
        delete from gl_ea_tulkond from gl_ea_tulkond left outer join kp_sksum_mark on kp_sksum_mark.sksumnr=li_sum_taot and gl_ea_tulkond.taotnr=kp_sksum_mark.xxxnr where kp_sksum_mark.xxxnr is not null
      end if;
    end if;
  end if;
  call ea_compute(li_id_lkd,'gl_ea_tulkond',4)
END