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