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

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE LD0SPListOngoingClientProcesses
(@PERSONKEY INT,
@ServiceCategoriesXml varchar(MAX),
@ProcessTypesXml varchar(MAX),
@ServiceTypesXml varchar(MAX))
AS
	BEGIN
	/* CLEAR TEMP TABLES */
	IF OBJECT_ID('TEMPDB..#CLIENTPROCESSES') IS NOT NULL
		DROP TABLE #CLIENTPROCESSES;	
	
	DECLARE @ACTUALIZATIONTYPE INT = 1, @INVESTIGATIONTYPE INT = 2, @SERVICETYPE INT = 3;
	DECLARE @TODAY NVARCHAR(10);
	
	SET @TODAY = convert(varchar, getdate(), 23); 
	
	/* Save input Service categories from XML parameter to temp table */
	DECLARE @XMLlist XML; SELECT @XMLList = @ServiceCategoriesXml;
    DECLARE @ServiceCategoriesTable AS TABLE ([CodeId] VARCHAR(201) NOT NULL);
    INSERT INTO @ServiceCategoriesTable SELECT x.y.value('.','VARCHAR(201)') AS CodeId FROM @XMLList.nodes('/serviceCategories/serviceCategory') AS x(y);
		
	/* Save input process types from XML parameter to temp table */
	SELECT @XMLList = @ProcessTypesXml;
    DECLARE @ProcessTypesTable AS TABLE ([CodeId] VARCHAR(201) NOT NULL);
    INSERT INTO @ProcessTypesTable SELECT x.y.value('.','VARCHAR(201)') AS CodeId FROM @XMLList.nodes('/processTypes/processType') AS x(y);

	/* Save input service types from XML parameter to temp table */
	SELECT @XMLList = @ServiceTypesXml;
    DECLARE @ServiceTypesTable AS TABLE ([CodeId] VARCHAR(201) NOT NULL);
    INSERT INTO @ServiceTypesTable SELECT x.y.value('.','VARCHAR(201)') AS CodeId FROM @XMLList.nodes('/serviceTypes/serviceType') AS x(y);
	
	CREATE TABLE #CLIENTPROCESSES(PROCESSID INT, PROCESSNAME VARCHAR(201), OWNERTYPE INT, OWNERID INT, PERSONKEY INT, STARTDATE NVARCHAR(10), ENDDATE NVARCHAR(10), 
	SERVICECATEGORYOID VARCHAR(201), PROCESSTYPEOID VARCHAR(201), SERVICETYPEOID VARCHAR(201) NULL);

	/* INSERT THE RESULT OF LD0V1PROCESSMETADATA IN TEMP TABLE #CLIENTPROCESSES */	
	INSERT INTO #CLIENTPROCESSES(PROCESSID, PROCESSNAME, OWNERTYPE, OWNERID, PERSONKEY, STARTDATE, ENDDATE, SERVICECATEGORYOID, PROCESSTYPEOID, SERVICETYPEOID) 
	SELECT PROCESSID, PROCESSNAME, OWNERTYPE, OWNERID, PERSONKEY, STARTDATE, ENDDATE, SERVICECATEGORYOID, PROCESSTYPEOID, SERVICETYPEOID 
	FROM LD0V1PROCESSMETADATA
	WHERE PERSONKEY = @PERSONKEY 
	AND	(ENDDATE = '' OR ENDDATE IS NULL OR ENDDATE >= @TODAY)
		
	/* FINAL RESULT SET WHICH WILL BE CONSUMED BY API/SERVICE*/
	SELECT DISTINCT CP.PROCESSID, CP.PROCESSNAME, CP.OWNERTYPE, CP.OWNERID, CP.PERSONKEY, CP.STARTDATE, CP.ENDDATE, CP.SERVICECATEGORYOID, CP.PROCESSTYPEOID, CP.SERVICETYPEOID
	FROM #CLIENTPROCESSES CP
	JOIN @ServiceCategoriesTable t ON CP.SERVICECATEGORYOID = t.CodeId OR CP.SERVICECATEGORYOID = ''                                                                                                                       
    JOIN @ProcessTypesTable pt ON CP.PROCESSTYPEOID = pt.CodeId
    JOIN @ServiceTypesTable st ON CP.SERVICETYPEOID = st.CodeId OR CP.SERVICETYPEOID = '' -- filter based on SERVICETYPEOID (It can be empty for ACT/INV owner)
	ORDER BY CP.PROCESSID;		
END
GO

GRANT ALL ON LD0SPListOngoingClientProcesses TO LD0ROANV
GO


