ServiceManager.Report.Compliance.ControlManagement.SP.GetComplianceIncidentsList.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.Compliance.ControlManagement.SP.GetComplianceIncidentsList.Install" Accessibility="Public" FileName="ServiceManager.Report.GRC.IncidentManagement.SP.ListOfIncidents.Install.sql"/>

File Content: ServiceManager.Report.GRC.IncidentManagement.SP.ListOfIncidents.Install.sql

SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO








-- Drop stored procedure if it already exists


IF EXISTS (


  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 


   WHERE SPECIFIC_SCHEMA = N'dbo'


     AND SPECIFIC_NAME = N'ServiceManager_Report_GRC_IncidentManagement_SP_ListOfIncidents'


)


   DROP PROCEDURE dbo.ServiceManager_Report_GRC_IncidentManagement_SP_ListOfIncidents


GO





-- =============================================


-- Author:		Microsoft


-- Create date: 


-- Description:	Incident List report procedure for Compliance Related Items


-- =============================================


CREATE PROCEDURE dbo.ServiceManager_Report_GRC_IncidentManagement_SP_ListOfIncidents


	@DateFilter	nvarchar(256) = null,


	@StartDate	datetime,


	@EndDate	datetime,


	@Source		nvarchar(max) = '-1',


	@Status		nvarchar(max) = '-1', 


	@Priority	nvarchar(max) = '-1',


	@Urgency	nvarchar(max) = '-1',


	@Impact		nvarchar(max) = '-1',


	@Classification nvarchar(max) = '-1',


	@SupportGroup nvarchar(max) = '-1',


	@ResolutionCategory	nvarchar(max) = '-1',


	@AssignedTo	int = null,


	@ContactMethod nvarchar(max) = null,


	@Description	nvarchar(max) = null,


	@ResolutionDescription nvarchar(max) = null,


	@RelatedCIs  nvarchar(max) = null,


	@ID  nvarchar(max) = null,


	@LanguageCode nvarchar(max)= 'ENU',


	-- Compliance Program to scope the Incidents related to Compliance


	-- instances that are part of the program


	@ProgramId INT --Required Field


		


AS


BEGIN


  SET NOCOUNT ON





  DECLARE @Error int


  DECLARE @ExecError int


 


  SET @EndDate = DateAdd(Day, 1, @EndDate)


 


  DECLARE @tableID TABLE(value nvarchar(256))


  INSERT @tableID (value)


  Select * FROM dbo.fn_CSVToTableString(ISNULL(@ID, ''))


 


  DECLARE @tableSource TABLE (value nvarchar(256))  


  INSERT @tableSource (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Source) 


 


  DECLARE @tableStatus TABLE (value nvarchar(256))  


  INSERT @tableStatus (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Status)


  


  DECLARE @tablePriority TABLE (value nvarchar(256))  


  INSERT @tablePriority (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Priority)


  


  DECLARE @tableUrgency TABLE (value nvarchar(256))  


  INSERT @tableUrgency (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Urgency)


  


  DECLARE @tableImpact TABLE (value nvarchar(256))  


  INSERT @tableImpact (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Impact)





  DECLARE @tableClassification TABLE (value nvarchar(256))  


  INSERT @tableClassification (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Classification)





  DECLARE @tableSupportGroup TABLE (value nvarchar(256))  


  INSERT @tableSupportGroup (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@SupportGroup)


  


  DECLARE @tableResCategory TABLE (value nvarchar(256))  


  INSERT @tableResCategory (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ResolutionCategory)


  


  DECLARE @tableRelatedCIs TABLE (value nvarchar(256))  


  INSERT @tableRelatedCIs (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@RelatedCIs)





-- GRC Incident changes  


-- Traverse through the Control Objectives, Control Activities and Risk in the Program





	BEGIN


		IF OBJECT_ID(N'tempdb..#CATCurrent', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #CATCurrent


		END            


	


		IF OBJECT_ID(N'tempdb..#CODimKeyCurrent', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #CODimKeyCurrent


		END


	    


		IF OBJECT_ID(N'tempdb..#CAListCurrent', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #CAListCurrent


		END


    


		IF OBJECT_ID(N'tempdb..#PHasCOCurrent', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #PHasCOCurrent


		END


    


		IF OBJECT_ID(N'tempdb..#PCAListCurrent', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #PCAListCurrent


		END


        


        IF OBJECT_ID(N'tempdb..#PCAALLList', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #PCAALLList


		END;


    


        IF OBJECT_ID(N'tempdb..#RiskCATData', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #RiskCATData


		END


	


		IF OBJECT_ID(N'tempdb..#RiskDimKey', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #RiskDimKey


		END;


	


		IF OBJECT_ID(N'tempdb..#PHasRisk', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #PHasRisk


		END;


		


		IF OBJECT_ID(N'tempdb..#PRiskALLList', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #PRiskALLList


		END;


		


		


		IF OBJECT_ID(N'tempdb..#PCOALLList', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE #PCOALLList


		END;


		


		IF OBJECT_ID(N'tempdb..#ComplianceIncidentList', N'U') IS NOT NULL


		BEGIN 


			DROP TABLE ComplianceIncidentList


		END;








		CREATE TABLE #PCAALLList


		(ProgramDimKey int,


		 CategoryDimKey int,


		 ControlObjectiveDimKey int,


		 ControlActivityDimKey int)


		 


		CREATE TABLE #PRiskALLList


		(ProgramDimKey int,


		 CategoryDimKey int,


		 RiskDimKey int)





		CREATE TABLE #PCOALLList


		(ProgramDimKey int,


		 ControlObjectiveDimKey int)


		 


		 CREATE TABLE #ComplianceIncidentList


		(IncidentDimKey int,		


		 RelatedObjectDimKey int,


		 RelatedObjectID nvarchar(256),


		 ComplianceType nvarchar(256))





	END;


  	


  	WITH CATDataCurrent (ProgramDimKey, CategoryDimKey)


	AS


	(


		SELECT ProgramDimKey


		,ProgramHasCategory_CategoryDimKey AS CategoryDimKey


		FROM ProgramHasCategoriesFactvw


		WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)  


		AND DeletedDate IS NULL


		


		UNION ALL


		


		SELECT ProgramDimKey


		,CategoryContainsCategory_CategoryDimKey as CategoryDimKey


		FROM dbo.CategoryContainsCategoryFactvw AS CC    


		INNER JOIN CATDataCurrent AS CD


		ON CC.CategoryDimKey = CD.CategoryDimKey	


		WHERE DeletedDate IS NULL	


	)


	SELECT  ProgramDimKey, CategoryDimKey into #CATCurrent FROM CATDataCurrent;





	WITH CODimKeyCurrent (ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey)


	AS


	(


		SELECT B.ProgramDimKey


		,A.CategoryDimKey


		,A.CategoryContainsControlObjective_ControlObjectiveDimKey AS ControlObjectiveDimKey


		FROM CategoryHasCOFactvw A


		INNER JOIN #CATCurrent B


		ON A.CategoryDimKey = B.CategoryDimKey


		WHERE A.DeletedDate IS NULL 


				


		UNION ALL


		


		SELECT CD.ProgramDimKey


		,CategoryDimKey


		,PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey


		FROM COHasCOFactvw AS CC    


		INNER JOIN CODimKeyCurrent AS CD


		ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey


		WHERE CC.DeletedDate IS NULL


	)


	SELECT  ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey into #CODimKeyCurrent FROM CODimKeyCurrent; 


	


	WITH CACurrent (ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey, ControlActivityDimKey)


	AS


	(


		SELECT A.ProgramDimKey


		,A.CategoryDimKey


		,A.ControlObjectiveDimKey


		,ISNULL(B.ControlObjectiveContainsControlActivity_ControlActivityDimKey, 0) AS ControlActivityDimKey


		FROM #CODimKeyCurrent A


		LEFT JOIN COHasCAFactvw B


		ON A.ControlObjectiveDimKey = B.ControlObjectiveDimKey


		AND B.DeletedDate IS NULL


		


		UNION ALL


		


		SELECT CD.ProgramDimKey


		,CD.CategoryDimKey


		,CD.ControlObjectiveDimKey


		,CC.ControlActivityContainsControlActivity_ControlActivityDimKey as ControlActivityDimKey


		FROM CAHasCAFactvw AS CC    


		INNER JOIN CACurrent AS CD


		ON CC.ControlActivityDimKey = CD.ControlActivityDimKey


		WHERE CC.DeletedDate IS NULL


	)


	SELECT  ProgramDimKey,CategoryDimKey,ControlObjectiveDimKey, ControlActivityDimKey into #CAListCurrent FROM CACurrent; 


	


	WITH PHasCOCurrent (ProgramDimKey, ControlObjectiveDimKey)


	AS


	(


		SELECT PCO.ProgramDimKey


		,PCO.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey AS ControlObjectiveDimKey


		FROM ProgramHasCOFactvw PCO


		WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)   


		AND PCO.DeletedDate IS NULL


		


		UNION ALL


		


		SELECT ProgramDimKey


		,PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey


		FROM COHasCOFactvw AS CC    


		INNER JOIN PHasCOCurrent AS CD


		ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey


		WHERE CC.DeletedDate IS NULL


	)


	SELECT  ProgramDimKey, ControlObjectiveDimKey into #PHasCOCurrent FROM PHasCOCurrent;





	INSERT INTO #PCOALLList


	SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey FROM #CODimKeyCurrent


	UNION


	SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey FROM #PHasCOCurrent;








	WITH PCACurrent (ProgramDimKey, ControlObjectiveDimKey, ControlActivityDimKey)


	AS


	(


		SELECT A.ProgramDimKey


		,A.ControlObjectiveDimKey


		,ISNULL(B.ControlObjectiveContainsControlActivity_ControlActivityDimKey, 0)


		FROM #PHasCOCurrent A


		LEFT JOIN dbo.COHasCAFactvw B


		ON A.ControlObjectiveDimKey = B.ControlObjectiveDimKey


		AND B.DeletedDate IS NULL


		


		UNION ALL


		


		SELECT CD.ProgramDimKey


		,ControlObjectiveDimKey


		,CC.ControlActivityContainsControlActivity_ControlActivityDimKey as ControlActivityDimKey


		FROM CAHasCAFactvw AS CC    


		INNER JOIN PCACurrent AS CD


		ON CC.ControlActivityDimKey = CD.ControlActivityDimKey


		WHERE CC.DeletedDate IS NULL


	)


	SELECT  ProgramDimKey, 0 AS CategoryDimKey, ControlObjectiveDimKey, ControlActivityDimKey into #PCAListCurrent FROM PCACurrent; 


	 


	INSERT INTO #PCAALLList


	SELECT DISTINCT ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey, ControlActivityDimKey FROM #CAListCurrent


	UNION


	SELECT DISTINCT ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey, ControlActivityDimKey FROM #PCAListCurrent;


		             





	WITH RiskCATData (ProgramDimKey, CategoryDimKey)


	AS


	(


		  SELECT ProgramDimKey


		  ,ProgramHasCategory_CategoryDimKey AS CategoryDimKey


		  FROM ProgramHasCategoriesFactvw


		  WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)   


		  AND DeletedDate IS NULL 


		  


		  UNION ALL


		  


		  SELECT CD.ProgramDimKey


		  ,CC.CategoryContainsCategory_CategoryDimKey as CategoryDimKey


		  FROM CategoryContainsCategoryFactvw AS CC    


		  INNER JOIN RiskCATData AS CD


		  ON CC.CategoryDimKey = CD.CategoryDimKey


		  AND DeletedDate IS NULL


	  )


	SELECT  ProgramDimKey, CategoryDimKey INTO #RiskCATData FROM RiskCATData; 


  


	WITH RiskDimKey (ProgramDimKey, CategoryDimKey, RiskDimKey)


	AS


	(


		SELECT B.ProgramDimKey


		,A.CategoryDimKey, A.CategoryContainsRisk_RiskDimKey AS RiskDimKey


		FROM CategoryContainsRiskFactvw A


		INNER JOIN #RiskCATData B


		ON A.CategoryDimKey = B.CategoryDimKey


		AND A.DeletedDate IS NULL


		


		UNION ALL


		


		SELECT RD.ProgramDimKey


		,RD.CategoryDimKey


		,RR.PolicyItemContainsCompliancePolicyItem_RiskDimKey AS RiskDimKey


		FROM RiskContainsRiskFactvw AS RR    


		INNER JOIN RiskDimKey AS RD


		ON RR.RiskDimKey = RD.RiskDimKey


		WHERE RR.DeletedDate IS NULL


	)


	SELECT  ProgramDimKey, CategoryDimKey, RiskDimKey into #RiskDimKey FROM RiskDimKey;


  


	WITH PHasRisk (ProgramDimKey, RiskDimKey)


	AS


	(


		SELECT ProgramDimKey, PolicyItemRelatesToCompliancePolicyItem_RiskDimKey AS RiskDimKey


		FROM ProgramContainsRisksFactvw


		WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)   


		AND DeletedDate IS NULL


		


		UNION ALL


		


		SELECT RD.ProgramDimKey, RR.PolicyItemContainsCompliancePolicyItem_RiskDimKey AS RiskDimKey


		FROM RiskContainsRiskFactvw AS RR    


		INNER JOIN PHasRisk AS RD


		ON RR.RiskDimKey = RD.RiskDimKey


		WHERE RR.DeletedDate IS NULL


	)


	SELECT ProgramDimKey, 0 AS CategoryDimKey, RiskDimKey into #PHasRisk FROM PHasRisk;





	INSERT INTO #PRiskALLList


	SELECT DISTINCT ProgramDimKey, CategoryDimKey, RiskDimKey FROM #RiskDimKey


	UNION


	SELECT DISTINCT ProgramDimKey, CategoryDimKey, RiskDimKey FROM #PHasRisk


  


	INSERT INTO #ComplianceIncidentList


	SELECT DISTINCT COI.IncidentDimkey, COLIST.ControlObjectiveDimKey, CODIM.Id, MT.TypeName as ComplianceType


	FROM dbo.ControlObjectiveRelatedToIncidentFactvw COI


		INNER JOIN  #PCOALLList COLIST	


		ON COLIST.ControlObjectiveDimKey = COI.WorkItemRelatesToConfigItem_ControlObjectiveDimKey		


		JOIN ControlObjectiveDimvw CODIM


		ON CODIM.ControlObjectiveDimKey = COLIST.ControlObjectiveDimKey				


		INNER JOIN EntityManagedTypeFactvw EMT


				ON CODIM.EntityDimKey  = EMT.EntityDimKey


		JOIN ManagedTypeDimvw MT 


				ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey		


				


	UNION


	


	SELECT DISTINCT CAI.WorkItemRelatesToWorkItem_IncidentDimKey, CALIST.ControlActivityDimKey, CADIM.Id, MT.TypeName as ComplianceType


	FROM dbo.ControlActivityRelatedToIncidentFactvw CAI


		INNER JOIN  #PCAALLList CALIST


		ON CALIST.ControlActivityDimKey = CAI.ControlActivityDimKey


		JOIN ControlActivityDimvw CADIM


		ON CADIM.ControlActivityDimKey = CALIST.ControlActivityDimKey				


		INNER JOIN EntityManagedTypeFactvw EMT


				ON CADIM.EntityDimKey  = EMT.EntityDimKey


		JOIN ManagedTypeDimvw MT 


				ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey		


		


	UNION


	


	SELECT DISTINCT RSI.IncidentDimkey, RISKLIST.RiskDimKey, RIDIM.Id, MT.TypeName as ComplianceType


	FROM dbo.RiskRelatedToIncidentFactvw RSI


		INNER JOIN  #PRISKALLList RISKLIST


		ON RISKLIST.RiskDimKey = RSI.WorkItemRelatesToConfigItem_RiskDimKey		


		JOIN RiskDimvw RIDIM


		ON RIDIM.RiskDimKey = RISKLIST.RiskDimKey


		INNER JOIN EntityManagedTypeFactvw EMT


				ON RIDIM.EntityDimKey  = EMT.EntityDimKey


		JOIN ManagedTypeDimvw MT 


				ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey


	  


	SELECT DISTINCT


	I.IncidentDimKey,


	I.CreatedDate,


	Description=NULL, 


	I.ResolvedDate, 


	I.Priority, 


	I.Id, 


	I.Title,





	Source = ISNULL(SourceDS.DisplayName, SourceEnum.IncidentSourceValue) ,


	SourceEnum.IncidentSourceId AS SourceId,





	Status = ISNULL(StatusDS.DisplayName, StatusEnum.IncidentStatusValue) , 


	StatusEnum.IncidentStatusId AS StatusId, 





	Impact = ISNULL(ImpactDS.DisplayName, ImpactEnum.IncidentImpactValue),


	ImpactEnum.IncidentImpactId AS ImpactId,





	AssignedTo.UserDimKey AssignedToUserId,


	AssignedTo.DisplayName AssignedToUserName





	,COMPINCL.ComplianceType AS ComplianceType,


	COMPINCL.RelatedObjectDimKey AS RelatedObjectDimKey,


	COMPINCL.RelatedObjectID AS RelatedObjectID,


	Urgency = ISNULL(UrgencyDS.DisplayName, UrgencyEnum.IncidentUrgencyValue),


	UrgencyEnum.IncidentUrgencyId AS UrgencyId





	FROM         


		dbo.IncidentDimvw I 


		


		INNER JOIN dbo.WorkItemDimvw WI


		ON I.EntityDimKey = WI.EntityDimKey





		INNER JOIN #ComplianceIncidentList COMPINCL


		ON I.IncidentDimKey = COMPINCL.IncidentDimKey





		LEFT OUTER JOIN	


		dbo.WorkItemAssignedToUserFactvw


		ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey 


		AND dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL


		


		LEFT OUTER JOIN


		dbo.UserDimvw AS AssignedTo 


		ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey 


		


		LEFT OUTER JOIN 


		dbo.WorkItemAboutConfigItemFactvw ON 


		dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey 


		AND dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL


		


		LEFT OUTER JOIN 


		dbo.WorkItemAboutConfigItemFactvw CIFctForFilter ON 


		CIFctForFilter.WorkItemDimKey = WI.WorkItemDimKey 


		AND CIFctForFilter.DeletedDate IS NULL





		LEFT OUTER JOIN


		dbo.IncidentSourcevw AS SourceEnum 


		ON SourceEnum.IncidentSourceId = I.Source_IncidentSourceId  


		


		LEFT OUTER JOIN 


		dbo.DisplayStringDimvw SourceDS


		ON SourceEnum.EnumTypeId=SourceDS.BaseManagedEntityId


		AND SourceDS.LanguageCode = @LanguageCode


		


		LEFT OUTER JOIN


		dbo.IncidentStatusvw AS StatusEnum 


		ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId  


		


		LEFT OUTER JOIN 


		dbo.DisplayStringDimvw StatusDS


		ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId


		AND StatusDS.LanguageCode = @LanguageCode


		


		LEFT OUTER JOIN


		dbo.IncidentImpactvw AS ImpactEnum 


		ON ImpactEnum.IncidentImpactId = I.Impact_IncidentImpactId


		


		LEFT OUTER JOIN 


		dbo.DisplayStringDimvw ImpactDS


		ON ImpactEnum.EnumTypeId=ImpactDS.BaseManagedEntityId


		AND ImpactDS.LanguageCode = @LanguageCode


		


		LEFT OUTER JOIN


		dbo.IncidentUrgencyvw AS UrgencyEnum 


		ON UrgencyEnum.IncidentUrgencyId = I.Urgency_IncidentUrgencyId


		


		LEFT OUTER JOIN 


		dbo.DisplayStringDimvw UrgencyDS


		ON UrgencyEnum.EnumTypeId=UrgencyDS.BaseManagedEntityId


		AND UrgencyDS.LanguageCode = @LanguageCode


		


	WHERE


	(


		(@DateFilter = 'ResolvedOn' AND ((I.ResolvedDate >= @StartDate) AND (I.ResolvedDate < @EndDate))) OR


		(@DateFilter = 'ClosedOn' AND ((I.ClosedDate >= @StartDate) AND (I.ClosedDate < @EndDate)))  OR


		(@DateFilter = 'CreatedOn' AND ((I.CreatedDate >= @StartDate) AND (I.CreatedDate < @EndDate))) OR


		(@DateFilter = 'All')


	) AND 


	(@StartDate <= @EndDate) AND


	((-1 IN (Select value from @tableSource)) OR (I.Source_IncidentSourceId IN (Select value from @tableSource))) AND


	((-1 IN (Select value from @tableStatus)) OR (I.Status_IncidentStatusId IN (Select value from @tableStatus))) AND


	((-1 IN (Select value from @tableImpact)) OR (I.Impact_IncidentImpactId IN (Select value from @tableImpact))) AND


	((-1 IN (Select value from @tableUrgency)) OR (I.Urgency_IncidentUrgencyId IN (Select value from @tableUrgency))) AND


	((-1 IN (Select value from @tableClassification)) OR (I.Classification_IncidentClassificationId IN (Select value from @tableClassification))) AND


	((-1 IN (Select value from @tableSupportGroup)) OR (I.TierQueue_IncidentTierQueuesId IN (Select value from @tableSupportGroup))) AND


	((-1 IN (Select value from @tablePriority)) OR (I.Priority IN (Select value from @tablePriority))) AND 


	(


		(-1 in (Select value from @tableResCategory)) 


		OR 


		(


			I.ResolutionCategory_IncidentResolutionCategoryId IN (Select value from @tableResCategory) 


			OR 


			(	


				(I.ResolutionCategory_IncidentResolutionCategoryId IS NULL) 


				AND 


				('' IN (Select value from @tableResCategory))


			)


		)


	) AND


	((@AssignedTo = 0) OR AssignedTo.UserDimKey = @AssignedTo) AND


	((@ID IS NULL) OR (I.Id IN (Select value from @tableID)))


	AND ((0 IN (select value from @tableRelatedCIs)) OR (CIFctForFilter.WorkItemAboutConfigItem_ConfigItemDimKey in (select value from @tableRelatedCIs)))	


	Order by COMPINCL.RelatedObjectID, I.Id





	  SET @Error = @@ERROR





	QuitError:


	  


	  RETURN @Error


END


GO





GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_IncidentManagement_SP_ListOfIncidents TO reportuser


GO