ALTER PROCEDURE "dba"."pr_saldovordlus_1_1"(in a_skoopkood char(16),in a_skoop integer,in id_paring integer,in a_partnr integer,in perlop date,in a_id_user integer default 32767, in a_valver smallint default 1 )
BEGIN
  declare li_paring integer;
  declare li_00_ravonr integer;
  declare ls_saldod_konto char(7);
  declare li_saldod smallint;
  declare li_saldod_9 smallint;
  delete from gl_dk_koond;
  delete from gl_id_konto;
  set li_paring=set_gl_1(id_paring);
  set li_00_ravonr=get_00_ravonr('');
  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;
  update partlist set doknr = null;
  insert into gl_dk_koond(konto,partnr,ravonr,adeeb)
    select pr_kanne.deeb,if kontlist.partnr is null then pr_kanne.partnr else kontlist.partnr endif as pr_partnr,if pr_kanne.ravonr is null then 0 else pr_kanne.ravonr endif,sum(if a_valver=4 then pr_kanne.summaeek else pr_kanne.summa 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,length(a_skoopkood))=a_skoopkood and(pr_kanne.skoop = a_skoop or a_skoop=0) and pr_partnr=a_partnr and pr_kanne.deeb is not null and years(pr_kanne.kuup) < years(perlop) and 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_kanne.ravonr;
  insert into gl_dk_koond(konto,partnr,ravonr,akree)
    select pr_kanne.kree,if kontlist.partnr is null then pr_kanne.partnr2 else kontlist.partnr endif as pr_partnr,if pr_kanne.ravonr is null then 0 else pr_kanne.ravonr endif,sum(if a_valver=4 then pr_kanne.summaeek else pr_kanne.summa 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,length(a_skoopkood))=a_skoopkood and(pr_kanne.skoop = a_skoop or a_skoop=0) and pr_partnr=a_partnr and pr_kanne.kree is not null and years(pr_kanne.kuup) < years(perlop) and 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_kanne.ravonr;
  update gl_dk_koond left outer join kontlist on gl_dk_koond.konto=kontlist.konto set gl_dk_koond.ravonr=if kontlist.nulliravo is not null or gl_dk_koond.ravonr=0 then li_00_ravonr else gl_dk_koond.ravonr endif;
  insert into gl_dk_koond(konto,partnr,ravonr,deeb)
    select pr_kanne.deeb,if kontlist.partnr is null then pr_kanne.partnr else kontlist.partnr endif as pr_partnr,if pr_kanne.ravonr is null then 0 else pr_kanne.ravonr endif,sum(if a_valver=4 then pr_kanne.summaeek else pr_kanne.summa 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,length(a_skoopkood))=a_skoopkood and(pr_kanne.skoop = a_skoop or a_skoop=0) and pr_partnr=a_partnr and pr_kanne.deeb is not null and years(pr_kanne.kuup)=years(perlop) and pr_kanne.kuup<=perlop
          group by pr_kanne.deeb,pr_partnr,pr_kanne.ravonr;
  insert into gl_dk_koond(konto,partnr,ravonr,kree)
    select pr_kanne.kree,if kontlist.partnr is null then pr_kanne.partnr2 else kontlist.partnr endif as pr_partnr,if pr_kanne.ravonr is null then 0 else pr_kanne.ravonr endif,sum(if a_valver=4 then pr_kanne.summaeek else pr_kanne.summa 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,length(a_skoopkood))=a_skoopkood and(pr_kanne.skoop = a_skoop or a_skoop=0) and pr_partnr=a_partnr and pr_kanne.kree is not null and years(pr_kanne.kuup)=years(perlop) and pr_kanne.kuup<=perlop
          group by pr_kanne.kree,pr_partnr,pr_kanne.ravonr;
  -- deebet kontojuhised
  update gl_dk_koond left outer join kontlist on gl_dk_koond.konto=kontlist.konto set gl_dk_koond.partnr=if kontlist.djuhpart is null or kontlist.djuhpart=0 then 0 else gl_dk_koond.partnr endif where gl_dk_koond.akree=0 and gl_dk_koond.kree=0;
  -- kreedit kontojuhised
  update gl_dk_koond left outer join kontlist on gl_dk_koond.konto=kontlist.konto set gl_dk_koond.partnr=if kontlist.kjuhpart is null or kontlist.kjuhpart=0 then 0 else gl_dk_koond.partnr endif where gl_dk_koond.adeeb=0 and gl_dk_koond.deeb=0;
  -- nulli tehingupartner
  update gl_dk_koond set partnr=if_part_ravo(konto,partnr,ravonr);
  insert into gl_id_konto( id,konto,summa)
    select gl_dk_koond.partnr,Left(gl_dk_koond.konto,6) as ab_konto, sum(gl_dk_koond.adeeb-gl_dk_koond.akree+gl_dk_koond.deeb-gl_dk_koond.kree)
      from gl_dk_koond left outer join kontlist on gl_dk_koond.konto = kontlist.konto left outer join partlist on gl_dk_koond.partnr = partlist.partnr
      where gl_dk_koond.partnr = a_partnr and kontlist.savordlus = 1 and partlist.konsol = 1
      group by gl_dk_koond.partnr,ab_konto order by gl_dk_koond.partnr asc,ab_konto asc;
  delete from gl_id_konto where summa = 0;
  update gl_id_konto set gl_id_konto.summa = -1*gl_id_konto.summa where("Left"(gl_id_konto.konto,1) = '2' or "Left"(gl_id_konto.konto,1) = '3');
END