ServiceManager.Report.Common.SP.GetServicesImpactedByWI.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.Common.SP.GetServicesImpactedByWI.Install" Accessibility="Public" FileName="ServiceManager.Report.Common.SP.GetServicesImpactedByWI.Install.sql"/>

File Content: ServiceManager.Report.Common.SP.GetServicesImpactedByWI.Install.sql

SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO





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


)


   DROP PROCEDURE dbo.ServiceManager_Report_Common_SP_GetServicesImpactedByWI


GO





CREATE PROCEDURE dbo.ServiceManager_Report_Common_SP_GetServicesImpactedByWI


	@LangCode nvarchar(16) = 'ENU',


	@WorkItemDimKey			int  = -1,


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


	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 (@IncludeDeleted = 1 OR WIICI.DeletedDate IS NULL)


	


	INNER JOIN dbo.ServiceDimvw Service


		ON Service.EntityDimKey = CI.EntityDimKey


	


	INNER JOIN dbo.EntityDimvw CIEntity


		ON CIEntity.EntityDimKey = CI.EntityDimKey


		


	INNER JOIN dbo.EntityManagedTypeFactvw CITypeFact


		ON CITypeFact.EntityDimKey = CIEntity.EntityDimKey


		AND (@IncludeDeleted = 1 OR CITypeFact.DeletedDate IS NULL)


		


	INNER 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


	


	WHERE WI.WorkItemDimKey = @WorkItemDimKey


	 


  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO








GRANT EXECUTE ON dbo.ServiceManager_Report_Common_SP_GetServicesImpactedByWI TO reportuser


GO