ALTER PROCEDURE "dba"."pr_koond1_part_asut"(in a_skoopkood char(16),in a_skoop integer,in id_paring integer,in peralg date,in perlop date)
begin
  declare li_paring integer;
  declare ls_saldod_konto char(7);
  declare li_saldod smallint;
  declare li_saldod_9 smallint;
  declare li_pikkus smallint;
  delete from gl_pr_koond;
  delete from gl_dk_koond;
  set li_pikkus=Length(a_skoopkood);
  if li_pikkus is null then
    set li_pikkus=0
  end if;
  set li_paring=set_gl_1(id_paring);
  set ls_saldod_konto=get_pr_saldod_konto(a_skoop);
  select pr_konfm.yld_saldod,pr_konfm.yld_saldod_9 into li_saldod,li_saldod_9 from pr_konfm where pr_konfm.id = 1;
    insert into gl_dk_koond(konto,partnr,asutnr,adeeb)
        select if li_saldod = 1 and Length(ls_saldod_konto)>0 and locate('345678',"left"(pr_kanne.deeb,1)) > 0 then ls_saldod_konto else pr_kanne.deeb endif,
               if kontlist.djuhpart is not null and kontlist.djuhpart=1 then if kontlist.partnr is null then if pr_kanne.partnr is null then 0 else pr_kanne.partnr endif else kontlist.partnr endif else 0 endif as pr_partnr,
               if kontlist.djuhpart is not null and kontlist.djuhpart=1 then if kontlist.partnr is null then if pr_kanne.asutnr is null then 0 else pr_kanne.asutnr endif else 0 endif else 0 endif as pr_asutnr,
               sum(pr_kanne.summa)
        from pr_kanne left outer join gl_1 on(pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) left outer join skoobid on pr_kanne.skoop=skoobid.skoop left outer join kontlist on pr_kanne.deeb=kontlist.konto
        where ((pr_kanne.id = gl_1.id) and(pr_kanne.kuup=gl_1.kuup) or id_paring=0) and Left(skoobid.kood,li_pikkus)=a_skoopkood and(pr_kanne.skoop=a_skoop or a_skoop=0) and pr_kanne.deeb is not null and years(pr_kanne.kuup) < years(peralg) and (Length(ls_saldod_konto)>0 or not(li_saldod = 1 and locate('345678',"left"(pr_kanne.deeb,1)) > 0)) and not(li_saldod_9 = 1 and "left"(pr_kanne.deeb,1) = '9')
        group by pr_kanne.deeb,pr_partnr,pr_asutnr;
    insert into gl_dk_koond(konto,partnr,asutnr,adeeb,deeb)
        select pr_kanne.deeb,
               if kontlist.djuhpart is not null and kontlist.djuhpart=1 then if kontlist.partnr is null then if pr_kanne.partnr is null then 0 else pr_kanne.partnr endif else kontlist.partnr endif else 0 endif as pr_partnr,
               if kontlist.djuhpart is not null and kontlist.djuhpart=1 then if kontlist.partnr is null then if pr_kanne.asutnr is null then 0 else pr_kanne.asutnr endif else 0 endif else 0 endif as pr_asutnr,
               sum(if pr_kanne.kuup<peralg then pr_kanne.summa else 0 endif),
               sum(if pr_kanne.kuup>=peralg then pr_kanne.summa else 0 endif)
        from pr_kanne left outer join gl_1 on(pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) left outer join skoobid on pr_kanne.skoop=skoobid.skoop left outer join kontlist on pr_kanne.deeb=kontlist.konto
        where ((pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) or id_paring = 0) and Left(skoobid.kood,li_pikkus)=a_skoopkood and(pr_kanne.skoop=a_skoop or a_skoop=0) and pr_kanne.deeb is not null and pr_kanne.kuup <= perlop and years(pr_kanne.kuup) >= years(peralg)
        group by pr_kanne.deeb,pr_partnr,pr_asutnr;
    insert into gl_dk_koond(konto,partnr,asutnr,akree)
        select if li_saldod = 1 and Length(ls_saldod_konto)>0 and locate('345678',"left"(pr_kanne.kree,1)) > 0 then ls_saldod_konto else pr_kanne.kree endif,
               if kontlist.kjuhpart is not null and kontlist.kjuhpart=1 then if kontlist.partnr is null then if pr_kanne.partnr2 is null then 0 else pr_kanne.partnr2 endif else kontlist.partnr endif else 0 endif as pr_partnr,
               if kontlist.kjuhpart is not null and kontlist.kjuhpart=1 then if kontlist.partnr is null then if pr_kanne.asutnr is null then 0 else pr_kanne.asutnr endif else 0 endif else 0 endif as pr_asutnr,
               sum(pr_kanne.summa)
        from pr_kanne left outer join gl_1 on(pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) left outer join skoobid on pr_kanne.skoop=skoobid.skoop left outer join kontlist on pr_kanne.kree=kontlist.konto
        where((pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) or id_paring = 0) and Left(skoobid.kood,li_pikkus)=a_skoopkood and(pr_kanne.skoop=a_skoop or a_skoop=0) and pr_kanne.kree is not null and years(pr_kanne.kuup) < years(peralg) and (Length(ls_saldod_konto)>0 or not(li_saldod = 1 and locate('345678',"left"(pr_kanne.kree,1)) > 0)) and not(li_saldod_9 = 1 and "left"(pr_kanne.kree,1) = '9')
        group by pr_kanne.kree,pr_partnr,pr_asutnr;
    insert into gl_dk_koond(konto,partnr,asutnr,akree,kree)
        select pr_kanne.kree,
               if kontlist.kjuhpart is not null and kontlist.kjuhpart=1 then if kontlist.partnr is null then if pr_kanne.partnr2 is null then 0 else pr_kanne.partnr2 endif else kontlist.partnr endif else 0 endif as pr_partnr,
               if kontlist.kjuhpart is not null and kontlist.kjuhpart=1 then if kontlist.partnr is null then if pr_kanne.asutnr is null then 0 else pr_kanne.asutnr endif else 0 endif else 0 endif as pr_asutnr,
               sum(if pr_kanne.kuup<peralg then pr_kanne.summa else 0 endif),
               sum(if pr_kanne.kuup>=peralg then pr_kanne.summa else 0 endif)
        from pr_kanne left outer join gl_1 on(pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) left outer join skoobid on pr_kanne.skoop=skoobid.skoop left outer join kontlist on pr_kanne.kree=kontlist.konto
        where((pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) or id_paring = 0) and Left(skoobid.kood,li_pikkus)=a_skoopkood and(pr_kanne.skoop=a_skoop or a_skoop=0) and pr_kanne.kree is not null and pr_kanne.kuup <= perlop and years(pr_kanne.kuup) >= years(peralg)
        group by pr_kanne.kree,pr_partnr,pr_asutnr;
  -- kustuta TP
  update gl_dk_koond set partnr=0,asutnr=0 where (li_saldod=1 and konto=ls_saldod_konto);
  -- kustuta isikud
  update gl_dk_koond left outer join partlist on gl_dk_koond.partnr=partlist.partnr set asutnr=0 where Left(partlist.part,6)='800699' or Left(partlist.part,4)='8004';
  -- koond
  insert into gl_pr_koond( konto,partnr,asutnr,adeeb,akree,deeb,kree) 
    select gl_dk_koond.konto,gl_dk_koond.partnr,gl_dk_koond.asutnr,sum(gl_dk_koond.adeeb),sum(gl_dk_koond.akree),sum(gl_dk_koond.deeb),sum(gl_dk_koond.kree) from
      gl_dk_koond group by gl_dk_koond.konto,gl_dk_koond.partnr,gl_dk_koond.asutnr;
  -- loppjaagid
  update gl_pr_koond set ldeeb=adeeb-akree+deeb-kree,lkree=akree-adeeb+kree-deeb,adeeb=adeeb-akree,akree=akree-adeeb,sdeeb=deeb-kree,skree=kree-deeb;
  update gl_pr_koond set adeeb = 0 where gl_pr_koond.adeeb < 0;
  update gl_pr_koond set akree = 0 where gl_pr_koond.akree < 0;
  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;
  update gl_pr_koond set sdeeb = 0 where gl_pr_koond.sdeeb < 0;
  update gl_pr_koond set skree = 0 where gl_pr_koond.skree < 0;
  delete from gl_pr_koond where adeeb = 0 and akree = 0 and deeb = 0 and kree = 0
end