ServiceManager.Report.IncidentManagement.SP.GetListOfIncidents.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.IncidentManagement.SP.GetListOfIncidents.Install" Accessibility="Public" FileName="ServiceManager.Report.IncidentManagement.SP.GetListOfIncidents.Install.sql"/>

File Content: ServiceManager.Report.IncidentManagement.SP.GetListOfIncidents.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_IncidentManagement_SP_GetListOfIncidents' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents


GO





CREATE PROCEDURE dbo.ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents


	@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,


    @IncludeDeleted bit = 0,


	@LanguageCode nvarchar(max)= 'ENU'


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)


  


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








FROM         


	dbo.IncidentDimvw I 


	


	INNER JOIN dbo.WorkItemDimvw WI


	ON I.EntityDimKey = WI.EntityDimKey


	


	LEFT OUTER JOIN	


	dbo.WorkItemAssignedToUserFactvw


	ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey 


	AND (@IncludeDeleted = 1 OR 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 (@IncludeDeleted = 1 OR dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL)


	


	LEFT OUTER JOIN 


	dbo.WorkItemAboutConfigItemFactvw CIFctForFilter ON 


	CIFctForFilter.WorkItemDimKey = WI.WorkItemDimKey 


	AND (@IncludeDeleted = 1 OR 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





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)))





  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO





GRANT EXECUTE ON dbo.ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents TO reportuser


GO