-- 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_Common_SP_GetWIsRelatedToWI'
)
DROP PROCEDURE dbo.ServiceManager_Report_Common_SP_GetWIsRelatedToWI
GO
CREATE PROCEDURE dbo.ServiceManager_Report_Common_SP_GetWIsRelatedToWI
@LangCode nvarchar(16) = 'ENU',
@WorkItemDimKey int = -1,
@IncludeDeleted bit = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
SELECT DISTINCT
WorkItems.Id,
WorkItems.Title,
E.LastModified LastModified,
AssignedTo.DisplayName AS AssignedToUserName,
ISNULL(WITypeDS.DisplayName, WIType.TypeName) TypeDisplayName,
WIType.TypeName,
StatusValue =
CASE
WHEN(CRStatusEnum.ChangeStatusValue IS NOT NULL)
THEN ISNULL(ChangeStatusEnumDisplayString.DisplayName, CRStatusEnum.ChangeStatusValue)
WHEN (RRStatusEnum.ReleaseStatusValue IS NOT NULL)
THEN ISNULL(ReleaseStatusEnumDisplayString.DisplayName, RRStatusEnum.ReleaseStatusValue)
WHEN(IncidentStatusEnum.IncidentStatusValue IS NOT NULL)
THEN ISNULL(IncidentStatusEnumDisplayString.DisplayName, IncidentStatusEnum.IncidentStatusValue)
WHEN(PRStatusEnum.ProblemStatusValue IS NOT NULL)
THEN ISNULL(ProblemStatusEnumDisplayString.DisplayName, PRStatusEnum.ProblemStatusValue)
WHEN(AcStatusEnum.ActivityStatusValue IS NOT NULL)
THEN ISNULL(ActivityStatusEnumDisplayString.DisplayName, AcStatusEnum.ActivityStatusValue)
ELSE NULL
END
FROM
(
SELECT
dimWI.WorkItemDimKey,
dimWI.EntityDimKey,
dimWI.Id,
dimWI.Title
FROM dbo.WorkItemRelatesToWorkItemFactvw factWI
INNER JOIN dbo.WorkItemDimvw dimWI
ON (dimWI.WorkItemDimKey = ISNULL(NULLIF(factWI.WorkItemDimKey, @WorkItemDimKey), factWI.WorkItemRelatesToWorkItem_WorkItemDimKey))
WHERE
(factWI.WorkItemDimKey = @WorkItemDimKey OR factWI.WorkItemRelatesToWorkItem_WorkItemDimKey = @WorkItemDimKey)
AND(@IncludeDeleted = 1 OR factWI.DeletedDate IS NULL)
) As WorkItems
LEFT OUTER JOIN
dbo.IncidentDimvw i on (WorkItems.EntityDimKey = i.EntityDimKey)
LEFT OUTER JOIN
dbo.ChangeRequestDimvw c on (c.EntityDimKey = WorkItems.EntityDimKey)
LEFT OUTER JOIN
dbo.ReleaseRecordDimvw r on (r.EntityDimKey = WorkItems.EntityDimKey)
LEFT OUTER JOIN
dbo.ProblemDimvw p on (p.EntityDimKey = WorkItems.EntityDimKey)
LEFT OUTER JOIN
dbo.ActivityDimvw a on (a.EntityDimKey = WorkItems.EntityDimKey)
LEFT OUTER JOIN
dbo.WorkItemAssignedToUserFactvw IAssignedUserFact
ON (IAssignedUserFact.WorkItemDimKey = WorkItems.WorkItemDimKey AND IAssignedUserFact.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.UserDimvw AssignedTo
ON IAssignedUserFact.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey
LEFT OUTER JOIN
dbo.IncidentStatusvw IncidentStatusEnum
ON IncidentStatusEnum.IncidentStatusId = i.Status_IncidentStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw IncidentStatusEnumDisplayString
ON (IncidentStatusEnum.EnumTypeId = IncidentStatusEnumDisplayString.BaseManagedEntityId AND
IncidentStatusEnumDisplayString.LanguageCode = @LangCode)
LEFT OUTER JOIN
dbo.ChangeStatusvw CRStatusEnum
ON CRStatusEnum.ChangeStatusId = c.Status_ChangeStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw ChangeStatusEnumDisplayString
ON (CRStatusEnum.EnumTypeId = ChangeStatusEnumDisplayString.BaseManagedEntityId AND
ChangeStatusEnumDisplayString.LanguageCode = @LangCode)
LEFT OUTER JOIN
dbo.ReleaseStatusvw RRStatusEnum
ON RRStatusEnum.ReleaseStatusId = r.Status_ReleaseStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw ReleaseStatusEnumDisplayString
ON (RRStatusEnum.EnumTypeId = ReleaseStatusEnumDisplayString.BaseManagedEntityId AND
ReleaseStatusEnumDisplayString.LanguageCode = @LangCode)
LEFT OUTER JOIN
dbo.ProblemStatusvw PRStatusEnum
ON PRStatusEnum.ProblemStatusId = p.Status_ProblemStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw ProblemStatusEnumDisplayString
ON (PRStatusEnum.EnumTypeId = ProblemStatusEnumDisplayString.BaseManagedEntityId AND
ProblemStatusEnumDisplayString.LanguageCode = @LangCode)
LEFT OUTER JOIN
dbo.ActivityStatusvw AcStatusEnum
ON AcStatusEnum.ActivityStatusId = a.Status_ActivityStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw ActivityStatusEnumDisplayString
ON (AcStatusEnum.EnumTypeId = ActivityStatusEnumDisplayString.BaseManagedEntityId AND
ActivityStatusEnumDisplayString.LanguageCode = @LangCode)
LEFT OUTER JOIN
dbo.EntityDim E
ON E.EntityDimKey = WorkItems.EntityDimKey
LEFT OUTER JOIN dbo.EntityManagedTypeFactvw WITypeFact
ON WITypeFact.EntityDimKey = E.EntityDimKey
AND (@IncludeDeleted = 1 OR WITypeFact.DeletedDate IS NULL)
INNER JOIN dbo.ManagedTypeDim WIType
ON WIType.ManagedTypeDimKey = WITypeFact.ManagedTypeDimKey
LEFT OUTER JOIN dbo.DisplayStringDimvw WITypeDS
ON WIType.ManagedTypeId = WITypeDS.BaseManagedEntityId
AND WITypeDS.LanguageCode = @LangCode
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_Common_SP_GetWIsRelatedToWI TO reportuser
GO
Home