SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPGetInvestigationsForEfficaService')
	DROP PROCEDURE LD0SPGetInvestigationsForEfficaService
GO

CREATE PROCEDURE LD0SPGetInvestigationsForEfficaService
  @PersonId VARCHAR(12),
  @FromDate VARCHAR(8),
  @ToDate VARCHAR(8)
AS
BEGIN

DECLARE @INVESTIGATIONINFO AS 
	TABLE ([AEVERKNR] INT NOT NULL, 
		   [AKTUALNR] INT NOT NULL,
		   [AKTPNR] VARCHAR(12) NOT NULL,
		   [STARTDAT] VARCHAR(8),
		   [STOPDAT] VARCHAR(8),
		   [ORGID] INT,		   
		   [HAND] VARCHAR(10),
		   [NAMN] VARCHAR(180),
		   [TELE1] VARCHAR(12),
		   [AEVERKTYP] INT,
		   [AEVERKTYP_T] VARCHAR(180),
		   [AKTI] VARCHAR(180),
		   [AKTUALTYP] INT);	

	--Get Investigation
	INSERT INTO @INVESTIGATIONINFO 
	SELECT I.AEVERKNR, 0, I.AKTPNR, I.STARTDAT, I.STOPDAT, I.ORGID, I.HAND, I.NAMN, I.TELE1, I.AEVERKTYP, I.AEVERKTYP_T, I.AKTI, -1
	FROM LD0V1CEFFICAINVESTIGATIONS I
	WHERE I.TMARK = 0 AND I.AKTPNR = @PersonId AND I.STARTDAT <= @ToDate AND I.STOPDAT >= @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 @INVESTIGATIONINFO 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 @INVESTIGATIONINFO) I 
												  ON A.AEVERKNR = I.AEVERKNR
												  GROUP BY A.AEVERKNR) AI ON H.AEVERKNR = AI.AEVERKNR) AKTINV 
	INNER JOIN @INVESTIGATIONINFO I ON I.AEVERKNR = AKTINV.AEVERKNR;
	
	SELECT * FROM @INVESTIGATIONINFO;

END
GO

GRANT EXECUTE ON LD0SPGetInvestigationsForEfficaService TO LD0ROANV 

GO