CREATE PROCEDURE "DBA"."pr_koond1_saldoandmik_tp"( )
BEGIN
  delete from gl_pr_saldoandmik_det;
  -- eeldus pr_koond1_saldoandmik
  insert into gl_pr_saldoandmik_det( konto,part,reg_nr,nimi,osa,allik,ravo,deebet,kreedit)
    select Left(gl_dk_koond.konto,6) as ab_konto,
          if gl_dk_koond.partnr = 0 then '' else Left(partlist.part,6) endif as ab_part,
          if partlist.reg_nr is null or Length(trim(partlist.reg_nr))=0 then if gl_dk_koond.saajanr=0 then '' else if asulist.reg_nr is null or Length(trim(asulist.reg_nr))=0 then 'Puudub' else asulist.reg_nr endif endif else partlist.reg_nr endif as ab_reg,
          if partlist.reg_nr is null or Length(trim(partlist.reg_nr))=0 then if gl_dk_koond.saajanr=0 then '' else get_asulist_nimi(asulist.asutnr) endif else partlist.nimi endif as ab_nimi,
          if gl_dk_koond.talnr = 0 then '' else if tallist.askood is null or Trim(tallist.askood)='' then "Left"(tallist.osa,5) else "Left"(tallist.askood,5) endif endif as ab_osa,
          if gl_dk_koond.alliknr = 0 then '' else "Left"(alliklist.allik,2) endif as ab_allik,
          if gl_dk_koond.ravonr = 0 then '' else "Left"(ravolist.ravo,2) endif as ab_ravo,
          sum(gl_dk_koond.adeeb-gl_dk_koond.akree+gl_dk_koond.deeb-gl_dk_koond.kree),
          sum(gl_dk_koond.akree-gl_dk_koond.adeeb+gl_dk_koond.kree-gl_dk_koond.deeb)
    from gl_dk_koond left outer join partlist on gl_dk_koond.partnr = partlist.partnr
                     left outer join asulist on gl_dk_koond.saajanr = asulist.asutnr
                     left outer join tallist on gl_dk_koond.talnr = tallist.talnr
                     left outer join alliklist on gl_dk_koond.alliknr = alliklist.alliknr
                     left outer join ravolist on gl_dk_koond.ravonr = ravolist.ravonr
    group by ab_konto,ab_part,ab_reg,ab_nimi,ab_osa,ab_allik,ab_ravo;
  update gl_pr_saldoandmik_det set deebet = 0 where gl_pr_saldoandmik_det.deebet < 0;
  update gl_pr_saldoandmik_det set kreedit = 0 where gl_pr_saldoandmik_det.kreedit < 0;
  delete from gl_pr_saldoandmik_det where deebet = 0 and kreedit = 0;
  update gl_pr_saldoandmik_det left outer join kontlist on "Left"(gl_pr_saldoandmik_det.konto+'       ',7) = "Left"(kontlist.konto+'       ',7) left outer join pr_bil on kontlist.bilnr = pr_bil.bilnr set gl_pr_saldoandmik_det.deebet = -1*gl_pr_saldoandmik_det.kreedit,gl_pr_saldoandmik_det.kreedit = 0 where gl_pr_saldoandmik_det.kreedit > 0 and pr_bil.aktpas = 'A';
  update gl_pr_saldoandmik_det left outer join kontlist on "Left"(gl_pr_saldoandmik_det.konto+'       ',7) = "Left"(kontlist.konto+'       ',7) left outer join pr_bil on kontlist.bilnr2 = pr_bil.bilnr set gl_pr_saldoandmik_det.kreedit = -1*gl_pr_saldoandmik_det.deebet,gl_pr_saldoandmik_det.deebet = 0 where gl_pr_saldoandmik_det.deebet > 0 and pr_bil.aktpas = 'P'
END