ServiceManager.Report.ProblemManagement.SP.GetListOfProblems.Install (Resource)

Element properties:

Source Code:

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

File Content: ServiceManager.Report.ProblemManagement.SP.GetListOfProblems.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_ProblemManagement_SP_GetListOfProblems' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_ProblemManagement_SP_GetListOfProblems


GO





CREATE PROCEDURE dbo.ServiceManager_Report_ProblemManagement_SP_GetListOfProblems


 


@DateFilter		nvarchar(256) = null,


@StartDate		datetime,


@EndDate		datetime,


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


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


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


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


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


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


@AssignedTo		nvarchar(256) = null,


@Description	nvarchar(max) = null,


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


@ResolutionDescription	nvarchar(max) = null,


@KnownError		bit = null,


@MajorReview	bit = null,


@AutoResolveIncidents	bit = null,


@LangCode nvarchar(16) = 'ENU',


@RelatedCIs  nvarchar(max) = '0',


@Id	nvarchar(max) = null,


@IncludeDeleted bit = 0





AS


BEGIN


  SET NOCOUNT ON





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





  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 @tableStatus TABLE (value nvarchar(256))  


  INSERT @tableStatus (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Status)


  


  DECLARE @tableUrgency TABLE (value nvarchar(256))  


  INSERT @tableUrgency (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Urgency)


  


  DECLARE @tablePriority TABLE (value nvarchar(256))  


  INSERT @tablePriority (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Priority)


  


  DECLARE @tableSource TABLE (value nvarchar(256))  


  INSERT @tableSource (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Source)


  


  DECLARE @tableClassification TABLE (value nvarchar(256))  


  INSERT @tableClassification (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Classification)


  


  DECLARE @tableImpact TABLE (value nvarchar(256))  


  INSERT @tableImpact (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Impact)





  DECLARE @tableResolutionCategory TABLE (value nvarchar(256))  


  INSERT @tableResolutionCategory(value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ResolutionCategory)





  DECLARE @tableRelatedCIs TABLE (value nvarchar(256))  


  INSERT @tableRelatedCIs (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@RelatedCIs)





SELECT DISTINCT


P.ProblemDimKey,


P.CreatedDate,     


P.Description, 


P.Priority, 


P.Id,


P.Title,


P.KnownError,


P.RequiresMajorProblemReview,


P.AutoResolve,


P.ReviewNotes,


P.Workarounds,





ClassificationEnum.ProblemClassificationId AS ClassificationId,


ISNULL(ClassificationEnumDS.DisplayName, ClassificationEnum.ProblemClassificationValue) AS Classification,





ISNULL(StatusEnumDS.DisplayName, StatusEnum.ProblemStatusValue) AS Status, 


StatusEnum.ProblemStatusId AS StatusId, 





AssignedTo.UserDimKey AS AssignedTo_UserDimKey,


AssignedTo.DisplayName AS AssignedTo_UserName,





AffectedCI.DisplayName AS AffectedCI_DisplayName,


AffectedCI.ConfigItemDimKey AS AffectedCI_ConfigItemDimKey,


ISNULL(CITypeDS.DisplayName,CIType.DisplayName) AS AffectedCI_TypeName





FROM         


	dbo.ProblemDimvw P


	


	INNER JOIN dbo.WorkItemDimvw WI


	ON WI.EntityDimKey = P.EntityDimKey


	


	LEFT OUTER JOIN	dbo.WorkItemAssignedToUserFactvw WIATU


	ON WIATU.WorkItemDimKey = WI.WorkItemDimKey AND (@IncludeDeleted = 1 OR WIATU.DeletedDate IS NULL)


	


	LEFT OUTER JOIN dbo.WorkItemAboutConfigItemFactvw WIACI


	ON WIACI.WorkItemDimKey = WI.WorkItemDimKey AND (@IncludeDeleted = 1 OR WIACI.DeletedDate IS NULL)


	


	LEFT OUTER JOIN dbo.WorkItemAboutConfigItemFactvw WIACI2


	ON WIACI2.WorkItemDimKey = WI.WorkItemDimKey AND (@IncludeDeleted = 1 OR WIACI2.DeletedDate IS NULL)


	


	LEFT OUTER JOIN dbo.ConfigItemDimvw AffectedCI


	ON AffectedCI.ConfigItemDimKey = WIACI.WorkItemAboutConfigItem_ConfigItemDimKey


	


	LEFT OUTER JOIN dbo.EntityDimvw CIEntity


	ON CIEntity.EntityDimKey = AffectedCI.EntityDimKey


		


	LEFT OUTER JOIN dbo.EntityManagedTypeFactvw CITypeFact


	ON CITypeFact.EntityDimKey = CIEntity.EntityDimKey


	AND CITypeFact.DeletedDate IS NULL


		


	LEFT OUTER JOIN dbo.ManagedTypeDim CIType


	ON CIType.ManagedTypeDimKey = CITypeFact.ManagedTypeDimKey


		


	LEFT OUTER JOIN	dbo.DisplayStringDimvw CITypeDS


	ON CIType.ManagedTypeId = CITypeDS.BaseManagedEntityId


	AND CITypeDS.LanguageCode = @LangCode


		


	LEFT OUTER JOIN	dbo.UserDimvw AS AssignedTo 


	ON WIATU.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey AND (AssignedTo.IsDeleted = 0)


	


	--Enumeration outrigger joins	


	LEFT OUTER JOIN	dbo.ProblemSourcevw AS SourceEnum 


	ON SourceEnum.ProblemSourceId = P.Source_ProblemSourceId  


	


	LEFT OUTER JOIN


	ProblemClassificationvw AS ClassificationEnum 


	ON ClassificationEnum.ProblemClassificationId = P.Classification_ProblemClassificationId  


	


	LEFT OUTER JOIN dbo.DisplayStringDimvw AS ClassificationEnumDS


	ON ClassificationEnum.EnumTypeId = ClassificationEnumDS.BaseManagedEntityId


	AND ClassificationEnumDS.LanguageCode = @LangCode


	


	LEFT OUTER JOIN


	ProblemStatusvw AS StatusEnum 


	ON StatusEnum.ProblemStatusId = P.Status_ProblemStatusId  


	


	LEFT OUTER JOIN dbo.DisplayStringDimvw AS StatusEnumDS


	ON StatusEnum.EnumTypeId = StatusEnumDS.BaseManagedEntityId


	AND StatusEnumDS.LanguageCode = @LangCode


	


	LEFT OUTER JOIN


	IncidentUrgencyvw AS UrgencyEnum 


	ON UrgencyEnum.IncidentUrgencyId = P.Urgency_IncidentUrgencyId  


	


	LEFT OUTER JOIN


	dbo.IncidentImpactvw AS ImpactEnum 


	ON ImpactEnum.IncidentImpactId = P.Impact_IncidentImpactId





WHERE


P.IsDeleted = 0 AND


((@Id IS NULL) OR (P.Id IN (select value from @tableID))) AND


(


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


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


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





) AND 


(@StartDate <= @EndDate) AND


((-1 IN (Select value from @tableStatus)) OR (StatusEnum.ProblemStatusId IN (Select value from @tableStatus))) AND


((-1 IN (Select value from @tableUrgency)) OR (UrgencyEnum.IncidentUrgencyId IN (Select value from @tableUrgency))) AND


((-1 IN (Select value from @tableImpact)) OR (ImpactEnum.IncidentImpactId IN (Select value from @tableImpact))) AND


((-1 IN (Select value from @tableClassification)) OR (ClassificationEnum.ProblemClassificationId IN  (Select value from @tableClassification))) AND


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


((-1 IN (Select value from @tableSource)) OR (P.Source_ProblemSourceId IN (Select value from @tableSource))) AND 


(


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


	OR 


	(


		P.Resolution_ProblemResolutionId IN (Select value from @tableResolutionCategory) 


		OR 


		(	


			(P.Resolution_ProblemResolutionId IS NULL) 


			AND 


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


		)


	)


) AND


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


((@Description IS NULL) OR (P.Description LIKE RTRIM(LTRIM(@Description)))) AND


((@ResolutionDescription IS NULL) OR (P.ResolutionDescription LIKE RTRIM(LTRIM(@ResolutionDescription )))) AND


((@KnownError IS NULL) OR (P.KnownError = @KnownError)) AND


((@MajorReview IS NULL) OR (P.RequiresMajorProblemReview = @MajorReview)) AND


((@AutoResolveIncidents IS NULL) OR (P.AutoResolve = @AutoResolveIncidents)) AND


(


	(


		0 IN (select value from @tableRelatedCIs)


	) 


	OR 


	(


		WIACI2.WorkItemAboutConfigItem_ConfigItemDimKey in (select value from @tableRelatedCIs)


	)


 )


ORDER BY Id





  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO








GRANT EXECUTE ON dbo.ServiceManager_Report_ProblemManagement_SP_GetListOfProblems TO reportuser


GO