IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPGetServicesForServiceOverview')
	DROP PROCEDURE LD0SPGetServicesForServiceOverview
GO
CREATE PROCEDURE LD0SPGetServicesForServiceOverview
  @PersonId VARCHAR(12),
  @FromDate VARCHAR(8),
  @ToDate VARCHAR(8)
AS
BEGIN
	
	DECLARE @SERVICEINFO AS 
	TABLE (INSNR INT NOT NULL, 
		   AEVERKNR INT NOT NULL, 
		   AKTUALNR INT NOT NULL,
		   AKTPNR VARCHAR(12) NOT NULL, 
		   FOMDAT VARCHAR(8), 
		   TOMDAT VARCHAR(8), 
		   NAMN VARCHAR(180), 
		   KLARTEXT VARCHAR(180), 
		   ORGNAMN VARCHAR(250), 
		   TELE1 VARCHAR(12), 
		   INSTYP INT, 
		   MAXRESTOMDAT VARCHAR(8), 
		   AKTI VARCHAR(180), 
		   PHASE INT, 
		   PHASEID INT, 
		   VERKNR INT, 
		   AKTUALTYP INT);		   


	--Get Service, Secision and Investigation id
	INSERT INTO @SERVICEINFO 
	SELECT S.INSNR,  I.AEVERKNR, 0, S.AKTPNR, S.FOMDAT, S.TOMDAT, S.NAMN, S.KLARTEXT, S.ORGNAMN, S.TELE1, S.INSTYP, S.MAXRESTOMDAT, S.AKTI, S.PHASE, S.PHASEID, S.VERKNR, -1 
	FROM LD0V0CSERVICES S 
	INNER JOIN LD0V0E D ON S.INSNR = D.INSNR
	INNER JOIN LD0V0CINVESTIGATIONS I ON I.AEVERKNR = D.AEVERKNR AND D.AEVERKNR <> 0	
	WHERE S.AKTPNR = @PersonId AND S.FOMDAT <= @ToDate AND S.TOMDAT >= @FromDate;

	--Set actualization id = 0, actualization codetype = -1 IF investigation not connected to any actualization	
	--actualization id = -1, actualization codetype = -1 IF multiple actualizations are connected to the investigation
	UPDATE @SERVICEINFO SET 
	AKTUALTYP = CASE WHEN AKTINV.CNT = 1 THEN AKTINV.AKTUALTYP 
				ELSE -1
				END,
	AKTUALNR =  CASE WHEN AKTINV.CNT = 1 THEN AKTINV.AKTUALNR 
				WHEN (AKTINV.CNT > 1) THEN -1 
				ELSE 0 
				END 
	FROM 
	(SELECT AI.CNT, H.* FROM LD0V0H H INNER JOIN (SELECT A.AEVERKNR, COUNT(A.AEVERKNR) CNT 
											      FROM LD0V0H A 
												  INNER JOIN (SELECT DISTINCT(AEVERKNR) FROM @SERVICEINFO) I 
												  ON A.AEVERKNR = I.AEVERKNR
												  GROUP BY A.AEVERKNR) AI ON H.AEVERKNR = AI.AEVERKNR) AKTINV
	INNER JOIN @SERVICEINFO S ON S.AEVERKNR = AKTINV.AEVERKNR;
	
	SELECT * FROM @SERVICEINFO;
END
GO
GRANT ALL ON LD0SPGetServicesForServiceOverview TO LD0ROANV
GO