-- 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_GetServicesAffectedByWI'
)
DROP PROCEDURE dbo.ServiceManager_Report_Common_SP_GetServicesAffectedByWI
GO
CREATE PROCEDURE dbo.ServiceManager_Report_Common_SP_GetServicesAffectedByWI
@LangCode nvarchar(16) = 'ENU',
@WorkItemDimKey int = -1,
@IncludeDeleted bit = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
SELECT DISTINCT
S.SourceId AS ID,
S.ServiceDimKey,
S.DisplayName,
S.ServiceDescription,
ISNULL(StatusEnumDS.DisplayName, S.Status) AS Status,
S.OwnedByOrganization AS Owner,
SType.TypeName,
ISNULL(STypeDS.DisplayName, SType.DisplayName) TypeDisplayName
FROM dbo.WorkItemDimvw WI
INNER JOIN dbo.WorkItemImpactsServiceFactvw WIIS
ON WIIS.WorkItemDimKey = WI.WorkItemDimKey
INNER JOIN dbo.ServiceDimvw S
ON WIIS.WorkItemImpactsService_ServiceDimKey = S.ServiceDimKey
AND (@IncludeDeleted = 1 OR WIIS.DeletedDate IS NULL)
INNER JOIN dbo.EntityDimvw SEntity
ON SEntity.EntityDimKey = S.EntityDimKey
LEFT OUTER JOIN dbo.EntityManagedTypeFactvw STypeFact
ON STypeFact.EntityDimKey = SEntity.EntityDimKey
AND (@IncludeDeleted = 1 OR STypeFact.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.ManagedTypeDim SType
ON SType.ManagedTypeDimKey = STypeFact.ManagedTypeDimKey
LEFT OUTER JOIN dbo.DisplayStringDimvw STypeDS
ON SType.ManagedTypeId = STypeDS.BaseManagedEntityId
AND STypeDS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ServiceStatusvw StatusEnum
ON StatusEnum.ServiceStatusId = S.Status_ServiceStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw StatusEnumDS
ON StatusEnum.EnumTypeId = StatusEnumDS.BaseManagedEntityId
AND StatusEnumDS.LanguageCode = @LangCode
WHERE WI.WorkItemDimKey = @WorkItemDimKey
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_Common_SP_GetServicesAffectedByWI TO reportuser
GO
Home