-- 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_GetCIsRelatedToWI'
)
DROP PROCEDURE dbo.ServiceManager_Report_Common_SP_GetCIsRelatedToWI
GO
CREATE PROCEDURE dbo.ServiceManager_Report_Common_SP_GetCIsRelatedToWI
@LangCode nvarchar(16) = 'ENU',
@WorkItemDimKey int = -1,
@TypeName nvarchar(256) = NULL,
@IncludeDeleted bit = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
Select distinct
CI.DisplayName,
CI.ConfigItemDimKey,
ISNULL(ObjectStatusEnumDS.DisplayName, CI.ObjectStatus) AS Status,
E.LastModified,
TypeName = MT.TypeName,
TypeDisplayName = COALESCE(MT_DS.DisplayName, MT.TypeName)
from
dbo.WorkItemDimvw WI
INNER JOIN dbo.WorkItemRelatesToConfigItemFactvw I2CIFact
ON I2CIFact.WorkItemDimKey = WI.WorkItemDimKey
AND (@IncludeDeleted = 1 OR I2CIFact.DeletedDate IS NULL)
INNER JOIN dbo.ConfigItemDimvw CI
ON I2CIFact.WorkItemRelatesToConfigItem_ConfigItemDimKey = CI.ConfigItemDimKey
INNER JOIN dbo.EntityDimvw E
ON CI.EntityDimKey = E.EntityDimKey
INNER JOIN dbo.EntityManagedTypeFactvw EMTFact
ON EMTFact.EntityDimKey = E.EntityDimKey
INNER JOIN dbo.ManagedTypeDimvw MT
ON EMTFact.ManagedTypeDimKey = MT.ManagedTypeDimKey
LEFT JOIN dbo.DisplayStringDimvw MT_DS
ON MT_DS.BaseManagedEntityId = MT.ManagedTypeId
AND MT_DS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ConfigItemObjectStatusvw ObjectStatusEnum
ON ObjectStatusEnum.ConfigItemObjectStatusId = CI.ObjectStatus_ConfigItemObjectStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw ObjectStatusEnumDS
ON ObjectStatusEnum.EnumTypeId = ObjectStatusEnumDS.BaseManagedEntityId
AND ObjectStatusEnumDS.LanguageCode = @LangCode
WHERE WI.WorkItemDimKey = @WorkItemDimKey
AND
(
(@TypeName IS NULL)
OR
(MT.TypeName = @TypeName)
)
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_Common_SP_GetCIsRelatedToWI TO reportuser
GO
Home