ALTER PROCEDURE [dbo].[P_UPD_KOST_AUFW_ERL_AKT]
AS 
	SET NOCOUNT ON
BEGIN
	
	UPDATE  tkv  
	SET	akt_soll_kosten = dt.soll,
		akt_ist_kosten = dt.ist,	
		akt_rest_kosten = dt.rest,	
		akt_gesamt_kosten = dt.gesamt,	
		akt_obligo_kosten = NULL,	
		akt_soll_aufw = dt.soll_aufw,	
		akt_ist_aufw = dt.ist_aufw,	
		akt_rest_aufw = dt.rest_aufw
	FROM ##T_PPMS_KOST_VERD_39440 tkv left join (	
		SELECT
			di001001 AS pr,
			di023062 AS jahr,
			di001003 AS ueb_pr,
			dt470.di029478 AS koagrp,
			ROUND(SUM(di001413),0) AS soll,
			ROUND(SUM(di001419),0) AS ist,
			ROUND(SUM(di001426),0) AS rest,
			ROUND(SUM(di001432),0) AS gesamt,
			ROUND (SUM (CASE WHEN DT467.DI003414 = '9' THEN 0 ELSE di001509 END),0) AS soll_aufw,
            ROUND (SUM (CASE WHEN DT467.DI003414 = '9' THEN 0 ELSE di001510 END),0) AS ist_aufw,
            ROUND (SUM (CASE WHEN DT467.DI003414 = '9' THEN 0 ELSE di001511 END),0) AS rest_aufw
		FROM  dt472 INNER JOIN dt461 ON (di001395  = di001001)  
			INNER JOIN DT470 ON (di001366  = di001518)  
			INNER JOIN dt861 ON (di001395  = di023119 AND di023062 = RIGHT(CONVERT(VARCHAR(10), DI001519, 103), 4))  
			INNER join DT467 on (DT472.DI001517 = DT467.DI001218)
		WHERE	(dt472.di001432  <> 0 or dt472.di001509+dt472.di001510+dt472.di001511 <> 0)
			AND	dt461.di001052 IN (SELECT PROJEKT FROM ##T_PPMS_RISIKO_VERD_39440 WHERE SESSIONID = @@SPID)
		GROUP BY di001001,
			di023062,
			di001003,
			di029478 
		) dt 
	ON tkv.projekt  = dt.pr
		AND	tkv.jahr  = dt.jahr
		AND	tkv.koagrp  = dt.koagrp AND SESSIONID = @@SPID
	;
	
   /***************************     überflüssige Jahre in DT861 löschen      **************************/

    delete from dt861 where RTRIM(DI023119) + CAST(DI023062 AS VARCHAR) not in
    (select distinct RTRIM(projekt) + CAST(jahr AS VARCHAR) from ##T_PPMS_KOST_VERD_39440 
       where projekt in (SELECT PROJEKT FROM ##T_PPMS_RISIKO_VERD_39440 WHERE SESSIONID = @@SPID))
       and DI023119 in (SELECT PROJEKT FROM ##T_PPMS_RISIKO_VERD_39440 WHERE SESSIONID = @@SPID)
    and
       (ISNULL(DI023165,0) + ISNULL(DI023178,0) + ISNULL(DI023166,0) + ISNULL(DI023179,0) + ISNULL(DI028558,0) + 
       ISNULL(DI040198,0) + ISNULL(DI028563,0) + ISNULL(DI040199,0) + ISNULL(DI028568,0) + ISNULL(DI040200,0) + 
       ISNULL(DI028573,0) + ISNULL(DI040201,0) + ISNULL(DI028578,0) + ISNULL(DI040202,0) + ISNULL(DI041590,0) + 
       ISNULL(DI041591,0) + ISNULL(DI041579,0) + ISNULL(DI041580,0) + ISNULL(DI041581,0) + ISNULL(DI041582,0) + 
       ISNULL(DI041583,0) + ISNULL(DI028831,0) + ISNULL(DI023173,0))  = 0 


	INSERT INTO  ##T_PPMS_KOST_VERD_39440 ( 
			SESSIONID , 
			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 )  
	SELECT
						 @@SPID
			 ,			 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

	FROM (	SELECT
				 di001001 AS pr,
				 di023062 AS jahr,
				 di001003 AS ueb_pr,
				 dt470.di029478 AS koagrp,
				 ROUND(SUM(di001413),0) AS soll,
				 ROUND(SUM(di001419),0) AS ist,
				 ROUND(SUM(di001426),0) AS rest,
				 ROUND(SUM(di001432),0) AS gesamt,
				 ROUND (SUM (CASE WHEN DT467.DI003414 = '9' THEN 0 ELSE di001509 END),0) AS soll_aufw,
			     ROUND (SUM (CASE WHEN DT467.DI003414 = '9' THEN 0 ELSE di001510 END),0) AS ist_aufw,
                 ROUND (SUM (CASE WHEN DT467.DI003414 = '9' THEN 0 ELSE di001511 END),0) AS rest_aufw
		FROM  dt472 INNER JOIN dt461 ON (di001395  = di001001)  
					INNER JOIN DT470 ON (di001366  = di001518)  
					INNER JOIN dt861 ON (di001395  = di023119 AND di023062 = RIGHT(CONVERT(VARCHAR(10), DI001519, 103), 4))   
					INNER join DT467 on (DT472.DI001517 = DT467.DI001218)
		WHERE	(dt472.di001432  <> 0 or dt472.di001509+dt472.di001510+dt472.di001511 <> 0)
		 AND	dt461.di001052 IN (SELECT PROJEKT FROM ##T_PPMS_RISIKO_VERD_39440 WHERE SESSIONID = @@SPID)
		GROUP BY di001001,
			 di023062,
			 di001003,
			 di029478 
	) dt 
	WHERE NOT EXISTS (SELECT 1 FROM  ##T_PPMS_KOST_VERD_39440 WHERE SESSIONID = @@SPID AND
	 dt.pr = ##T_PPMS_KOST_VERD_39440.PROJEKT AND dt.jahr = ##T_PPMS_KOST_VERD_39440.JAHR AND dt.koagrp = ##T_PPMS_KOST_VERD_39440.KOAGRP)


	UPDATE  t   
	SET	di041635 = akt_soll_kosten,
		di041636 = akt_ist_kosten,
		di041637 = akt_rest_kosten,
		di041639 = COALESCE(akt_gesamt_kosten, 0) + COALESCE(di041638, 0),
		di041627 = akt_soll_aufw,
		di041628 = akt_ist_aufw,
		di041629 = akt_rest_aufw,
		di041630 = (akt_ist_aufw + akt_rest_aufw) 
	FROM DT861 t LEFT JOIN ##T_PPMS_KOST_VERD_39440 ON
		SESSIONID = @@SPID
		AND t.di023119 = projekt
		AND	t.di023062 = jahr
		AND	lvl = 1
		AND	koagrp = 1
	WHERE di023119 IN
		(
			SELECT di001001
			FROM  dt461 
			WHERE	 di001052 IN (SELECT PROJEKT FROM ##T_PPMS_RISIKO_VERD_39440 WHERE SESSIONID = @@SPID)
		) 
		
	UPDATE  t   
	SET	di041640 = akt_soll_kosten,
		di041641 = akt_ist_kosten,
		di041642 = akt_rest_kosten,
		di041644 = COALESCE(akt_gesamt_kosten, 0) + COALESCE(di041643, 0),
		di041631 = akt_soll_aufw,
		di041632 = akt_ist_aufw,
		di041633 = akt_rest_aufw,
		di041634 = (akt_ist_aufw + akt_rest_aufw) 
	FROM DT861 t LEFT JOIN ##T_PPMS_KOST_VERD_39440 ON
		SESSIONID = @@SPID
		AND t.di023119 = projekt
		AND	t.di023062 = jahr
		AND	lvl = 1
		AND	koagrp = 2
	WHERE di023119 IN
		(
			SELECT di001001
			FROM  dt461 
			WHERE	 di001052 IN (SELECT PROJEKT FROM ##T_PPMS_RISIKO_VERD_39440 WHERE SESSIONID = @@SPID)
		) 
		
	UPDATE  t   
	SET	di041645 = akt_soll_kosten,
		di041646 = akt_ist_kosten,
		di041647 = akt_rest_kosten,
		di041649 = COALESCE(akt_gesamt_kosten, 0) + COALESCE(di041648, 0) 
	FROM DT861 t LEFT JOIN ##T_PPMS_KOST_VERD_39440 ON
		SESSIONID = @@SPID
		AND t.di023119 = projekt
		AND	t.di023062 = jahr
		AND	lvl = 1
		AND	koagrp = 3
	WHERE di023119 IN
		(
			SELECT di001001
			FROM  dt461 
			WHERE	 di001052 IN (SELECT PROJEKT FROM ##T_PPMS_RISIKO_VERD_39440 WHERE SESSIONID = @@SPID)
		) 
		
	UPDATE  t   
	SET	di041650 = akt_soll_kosten,
		di041651 = akt_ist_kosten,
		di041652 = akt_rest_kosten,
		di041654 = COALESCE(akt_gesamt_kosten, 0) + COALESCE(di041653, 0)
	FROM DT861 t LEFT JOIN ##T_PPMS_KOST_VERD_39440 ON
		SESSIONID = @@SPID
		AND t.di023119 = projekt
		AND	t.di023062 = jahr
		AND	lvl = 1
		AND	koagrp = 4
	WHERE di023119 IN
		(
			SELECT di001001
			FROM  dt461 
			WHERE	 di001052 IN (SELECT PROJEKT FROM ##T_PPMS_RISIKO_VERD_39440 WHERE SESSIONID = @@SPID)
		) 
		
	UPDATE  t   
	SET	di041655 = akt_soll_kosten,
		di041656 = akt_ist_kosten,
		di041657 = akt_rest_kosten,
		di041659 = COALESCE(akt_gesamt_kosten, 0) + COALESCE(di041658, 0)
	FROM DT861 t LEFT JOIN ##T_PPMS_KOST_VERD_39440 ON
		SESSIONID = @@SPID
		AND t.di023119 = projekt
		AND	t.di023062 = jahr
		AND	lvl = 1
		AND	koagrp = 5
	WHERE di023119 IN
		(
			SELECT di001001
			FROM  dt461 
			WHERE	 di001052 IN (SELECT PROJEKT FROM ##T_PPMS_RISIKO_VERD_39440 WHERE SESSIONID = @@SPID)
		) 
		
	UPDATE  dt861   
	SET	di041660 = akt_soll_kosten,
		di041661 = akt_ist_kosten,
		di041662 = akt_rest_kosten,
		di041663 = akt_gesamt_kosten 
	FROM DT861 t LEFT JOIN ##T_PPMS_KOST_VERD_39440 ON
		SESSIONID = @@SPID
		AND t.di023119 = projekt
		AND	t.di023062 = jahr
		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_39440 WHERE SESSIONID = @@SPID)
		) 

		DELETE FROM ##T_PPMS_KOST_VERD_39440 WHERE SESSIONID = @@SPID;
END
