create or replace PROCEDURE p_upd_kost_aufw_erl_akt
AS
BEGIN
   /*******************************************************************************************************/
   /*******************************   AKT:  fill T_PPMS_KOST_VERD Kosten      *****************************/
   /*******************************************************************************************************/
   MERGE INTO T_PPMS_KOST_VERD pct
        USING (SELECT di001001 AS pr,
                        di023062 AS jahr,
                        di001003 AS ueb_pr,
                        dt470.di029478 AS koagrp,
                        ROUND (SUM (di001413)) AS soll,
                        ROUND (SUM (di001419)) AS ist,
                        ROUND (SUM (di001426)) AS rest,
                        ROUND (SUM (di001432)) AS gesamt,
                        ROUND (SUM (CASE WHEN DT467.DI003414 = '9' THEN 0 ELSE di001509 END)) AS soll_aufw,
                        ROUND (SUM (CASE WHEN DT467.DI003414 = '9' THEN 0 ELSE di001510 END)) AS ist_aufw,
                        ROUND (SUM (CASE WHEN DT467.DI003414 = '9' THEN 0 ELSE di001511 END)) AS rest_aufw
                   FROM dt472
                        INNER JOIN dt461 ON (di001395 = di001001)
                        INNER JOIN DT470 ON (di001366 = di001518)
                        INNER join DT467 on (DT472.DI001517 = DT467.DI001218)
                        INNER JOIN
                        dt861
                           ON (    di001395 = di023119
                               AND di023062 = EXTRACT (YEAR FROM di001519))
                  -- Positive buchungen
                  WHERE (dt472.di001432 <> 0 or dt472.di001509+dt472.di001510+dt472.di001511 <> 0)
                        AND dt461.di001052 IN
                               (SELECT PROJEKT FROM T_PPMS_RISIKO_VERD)
               GROUP BY di001001,
                        di023062,
                        di001003,
                        di029478) dt
           ON (    pct.projekt = dt.pr
               AND pct.jahr = dt.jahr
               AND pct.koagrp = dt.koagrp)
   WHEN MATCHED
   THEN
      UPDATE SET pct.akt_soll_kosten = dt.soll,
                 pct.akt_ist_kosten = dt.ist,
                 pct.akt_rest_kosten = dt.rest,
                 pct.akt_gesamt_kosten = dt.gesamt,
                 pct.akt_obligo_kosten = NULL,
                 pct.akt_soll_aufw = dt.soll_aufw,
                 pct.akt_ist_aufw = dt.ist_aufw,
                 pct.akt_rest_aufw = dt.rest_aufw
   WHEN NOT MATCHED
   THEN
      INSERT     (projekt,
                  jahr,
                  ueb_projekt,
                  koagrp,
                  akt_soll_kosten,
                  akt_ist_kosten,
                  akt_rest_kosten,
                  akt_gesamt_kosten,
                  akt_obligo_kosten,
                  akt_soll_aufw,
                  akt_ist_aufw,
                  akt_rest_aufw,
                  erloes,
                  lvl)
          VALUES (dt.pr,
                  dt.jahr,
                  dt.ueb_pr,
                  dt.koagrp,
                  dt.soll,
                  dt.ist,
                  dt.rest,
                  dt.gesamt,
                  0,
                  dt.soll_aufw,
                  dt.ist_aufw,
                  dt.rest_aufw,
                  CASE WHEN dt.koagrp = 0 THEN 'J' ELSE 'N' END,
                  1);

   /**************     ueberfluessige Jahre in DT861 loeschen      *************/
   DELETE FROM dt861
         WHERE     DI023119 || DI023062 NOT IN
                      (SELECT DISTINCT
                              projekt || jahr
                         FROM T_PPMS_KOST_VERD
                        WHERE projekt IN
                                 (SELECT PROJEKT FROM T_PPMS_RISIKO_VERD))
               AND DI023119 IN (SELECT PROJEKT FROM T_PPMS_RISIKO_VERD)
               AND   NVL (di023165, 0)
                   + NVL (di023178, 0)
                   + NVL (di023166, 0)
                   + NVL (di023179, 0)
                   + NVL (DI028558, 0)
                   + NVL (DI040198, 0)
                   + NVL (DI028563, 0)
                   + NVL (DI040199, 0)
                   + NVL (DI028568, 0)
                   + NVL (DI040200, 0)
                   + NVL (DI028573, 0)
                   + NVL (DI040201, 0)
                   + NVL (di028578, 0)
                   + NVL (di040202, 0)
                   + NVL (di041590, 0)
                   + NVL (di041591, 0)
                   + NVL (DI041579, 0)
                   + NVL (DI041580, 0)
                   + NVL (DI041581, 0)
                   + NVL (DI041582, 0)
                   + NVL (di041583, 0)
                   + NVL (di028831, 0)
                   + NVL (di023173, 0) = 0;

   -- KOAGRP 1
   UPDATE dt861 t
      SET (di041635,                                  -- AKT Personal int Soll
           di041636,
           di041637,
           di041639,
           di041627,                                     -- AKT Aufw. int Soll
           di041628,
           di041629,
           di041630) =
             (SELECT DISTINCT
                     akt_soll_kosten,
                     akt_ist_kosten,
                     akt_rest_kosten,
                       NVL (akt_gesamt_kosten, 0)
                     + (SELECT NVL (di041638, 0)
                          FROM dt861
                         WHERE     di023119 = t.di023119
                               AND di023062 = t.di023062)
                        ges_kosten,
                     akt_soll_aufw,
                     akt_ist_aufw,
                     akt_rest_aufw,
                     (akt_ist_aufw + akt_rest_aufw)
                FROM T_PPMS_KOST_VERD
               WHERE     projekt = t.di023119
                     AND jahr = t.di023062
                     AND lvl = 1
                     AND koagrp = 1
                     AND erloes = 'N')
    WHERE di023119 IN
             (SELECT di001001
                FROM dt461
               WHERE di001052 IN (SELECT PROJEKT FROM T_PPMS_RISIKO_VERD));

   -- KOAGRP 2
   UPDATE dt861 t
      SET (di041640,                                  -- AKT Personal ext Soll
           di041641,
           di041642,
           di041644,
           di041631,                                     -- AKT Aufw. ext Soll
           di041632,
           di041633,
           di041634) =
             (SELECT DISTINCT
                     akt_soll_kosten,
                     akt_ist_kosten,
                     akt_rest_kosten,
                       NVL (akt_gesamt_kosten, 0)
                     + (SELECT NVL (di041643, 0)
                          FROM dt861
                         WHERE     di023119 = t.di023119
                               AND di023062 = t.di023062)
                        ges_kosten,
                     akt_soll_aufw,
                     akt_ist_aufw,
                     akt_rest_aufw,
                     (akt_ist_aufw + akt_rest_aufw)
                FROM T_PPMS_KOST_VERD
               WHERE     projekt = t.di023119
                     AND jahr = t.di023062
                     AND lvl = 1
                     AND koagrp = 2
                     AND erloes = 'N')
    WHERE di023119 IN
             (SELECT di001001
                FROM dt461
               WHERE di001052 IN (SELECT PROJEKT FROM T_PPMS_RISIKO_VERD));

   -- KOAGRP 3
   UPDATE dt861 t
      SET (di041645,                                    -- AKT Sachkosten-Soll
           di041646,
           di041647,
           di041649) =
             (SELECT DISTINCT
                     akt_soll_kosten,
                     akt_ist_kosten,
                     akt_rest_kosten,
                       NVL (akt_gesamt_kosten, 0)
                     + (SELECT NVL (di041648, 0)
                          FROM dt861
                         WHERE     di023119 = t.di023119
                               AND di023062 = t.di023062)
                        ges_kosten
                FROM T_PPMS_KOST_VERD
               WHERE     projekt = t.di023119
                     AND jahr = t.di023062
                     AND lvl = 1
                     AND koagrp = 3
                     AND erloes = 'N')
    WHERE di023119 IN
             (SELECT di001001
                FROM dt461
               WHERE di001052 IN (SELECT PROJEKT FROM T_PPMS_RISIKO_VERD));

   -- KOAGRP 4
   UPDATE dt861 t
      SET (di041650,                                 -- AKT Investitionen-Soll
           di041651,
           di041652,
           di041654) =
             (SELECT DISTINCT
                     akt_soll_kosten,
                     akt_ist_kosten,
                     akt_rest_kosten,
                       NVL (akt_gesamt_kosten, 0)
                     + (SELECT NVL (di041653, 0)
                          FROM dt861
                         WHERE     di023119 = t.di023119
                               AND di023062 = t.di023062)
                        ges_kosten
                FROM T_PPMS_KOST_VERD
               WHERE     projekt = t.di023119
                     AND jahr = t.di023062
                     AND lvl = 1
                     AND koagrp = 4
                     AND erloes = 'N')
    WHERE di023119 IN
             (SELECT di001001
                FROM dt461
               WHERE di001052 IN (SELECT PROJEKT FROM T_PPMS_RISIKO_VERD));

   -- KOAGRP 5
   UPDATE dt861 t
      SET (di041655,                                     -- AKT Sonstiges-Soll
           di041656,
           di041657,
           di041659) =
             (SELECT DISTINCT
                     akt_soll_kosten,
                     akt_ist_kosten,
                     akt_rest_kosten,
                       NVL (akt_gesamt_kosten, 0)
                     + (SELECT NVL (di041658, 0)
                          FROM dt861
                         WHERE     di023119 = t.di023119
                               AND di023062 = t.di023062)
                        ges_kosten
                FROM T_PPMS_KOST_VERD
               WHERE     projekt = t.di023119
                     AND jahr = t.di023062
                     AND lvl = 1
                     AND koagrp = 5
                     AND erloes = 'N')
    WHERE DI023119 IN
             (SELECT di001001
                FROM dt461
               WHERE di001052 IN (SELECT PROJEKT FROM T_PPMS_RISIKO_VERD));

   -- ERLOESE
   UPDATE dt861
      SET (di041660,                                       -- AKT Erloese-Soll
           di041661,
           di041662,
           di041663) =
             (SELECT DISTINCT akt_soll_kosten,
                              akt_ist_kosten,
                              akt_rest_kosten,
                              akt_gesamt_kosten
                FROM T_PPMS_KOST_VERD
               WHERE     projekt = dt861.di023119
                     AND jahr = di023062
                     AND lvl = 1
                     AND koagrp = 0
                     AND erloes = 'J')
    WHERE DI023119 IN
             (SELECT di001001
                FROM dt461
               WHERE di001052 IN (SELECT PROJEKT FROM T_PPMS_RISIKO_VERD));
/*
-- Obligio von 861 auf 461 ÃƒÅ’bertragen
update dt461 dt set (di041540, di041541, di041542, di041543, di041544) =
( select
case when sum(di041638) > 0 then sum(di041638)
else dt.di041540 end,
case when sum(di041643) > 0 then sum(di041643)
else dt.di041541 end,
case when sum(di041648) > 0 then sum(di041648)
else dt.di041542 end,
case when sum(di041653) > 0 then sum(di041653)
else dt.di041543 end,
case when sum(di041658) > 0 then sum(di041658)
else dt.di041544 end
from dt861 where dt861.di023119 = dt.di001001)
where
di001052 = p_hauptprojekt;
*/
END p_upd_kost_aufw_erl_akt;