-- 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_GetCIsImpactedByWI'
)
DROP PROCEDURE dbo.ServiceManager_Report_Common_SP_GetCIsImpactedByWI
GO
CREATE PROCEDURE dbo.ServiceManager_Report_Common_SP_GetCIsImpactedByWI
@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.ConfigItemDimKey,
CI.DisplayName,
ISNULL(AssetStatusEnumDS.DisplayName, CI.AssetStatus) AS AssetStatus,
ISNULL(ObjectStatusEnumDS.DisplayName, CI.ObjectStatus) AS ObjectStatus,
CIEntity.LastModified,
CIType.TypeName,
ISNULL(CITypeDS.DisplayName, CIType.DisplayName) TypeDisplayName
FROM
dbo.WorkItemDimvw WI
INNER JOIN dbo.WorkItemAboutConfigItemFactvw WIICI
ON WIICI.WorkItemDimKey = WI.WorkItemDimKey
INNER JOIN dbo.ConfigItemDimvw CI
ON WIICI.WorkItemAboutConfigItem_ConfigItemDimKey = CI.ConfigItemDimKey
AND WIICI.DeletedDate IS NULL
INNER JOIN dbo.EntityDimvw CIEntity
ON CIEntity.EntityDimKey = CI.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.ConfigItemAssetStatusvw AssetStatusEnum
ON AssetStatusEnum.ConfigItemAssetStatusId = CI.AssetStatus_ConfigItemAssetStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw AssetStatusEnumDS
ON AssetStatusEnum.EnumTypeId = AssetStatusEnumDS.BaseManagedEntityId
AND AssetStatusEnumDS.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
(CIType.TypeName = @TypeName)
)
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_Common_SP_GetCIsImpactedByWI TO reportuser
GO
Home