ServiceManager.Report.ActivityManagement.SP.GetListOfReviewActivities.Install (Resource)

Element properties:

Source Code:

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

File Content: ServiceManager.Report.ActivityManagement.SP.GetListOfReviewActivities.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_ActivityManagement_SP_GetListOfReviewActivities' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_ActivityManagement_SP_GetListOfReviewActivities


GO





CREATE PROCEDURE dbo.ServiceManager_Report_ActivityManagement_SP_GetListOfReviewActivities


	@LangCode			nvarchar(max) = null,	


	@Id					nvarchar(max) = null,


	@Name				nvarchar(max) = null,


	@Status				nvarchar(max) = '-1',


	@Stage				nvarchar(max) = '-1',


	@CreatedOnFrom		datetime = '1/1/1900',


	@CreatedOnTo		datetime = '1/1/3000',


    @IncludeDeleted bit = 0


	


AS


BEGIN


	SET NOCOUNT ON





	DECLARE @Error int


	DECLARE @ExecError int


  


	SET @CreatedOnTo = DateAdd(DAY, 1, @CreatedOnTo)


  


	DECLARE @tableStatus TABLE (value nvarchar(256))  


	INSERT @tableStatus (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Status)





	DECLARE @tableStage TABLE (value nvarchar(256))  


	INSERT @tableStage (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Stage)  


	


	SELECT DISTINCT


	A.ActivityDimKey,


	A.BaseManagedEntityId,


	A.CreatedDate,


	A.Title,


	A.Description,


	A.Id,


	A.IsDeleted,





	ISNULL(RADS.DisplayName, RAA.ReviewActivityApprovalValue) as ApprovalCondition,


	RAD.ApprovalPercentage ApprovalThreshold,


	RevAct.ReviewActivityHasReviewer_ReviewerDimKey ReviewerDimKey,


	RD.Decision Decision,





	ParentWI.Id  AS ParentWorkitem,


	ParentWI.ParentDimKey ParentWI_Key,


	ParentWI.ParentType ParentWI_Type,





	ISNULL(StatusDS.DisplayName, StatusEnum.ActivityStatusValue) as ActivityStatusValue,


	StatusEnum.ActivityStatusId,


	ISNULL(StageDS.DisplayName, StageEnum.ActivityStageValue) as ActivityStageValue, 	


	StageEnum.ActivityStageId,





	EntityType.TypeName ActivityTypeName,





	ISNULL(ActivityType.DisplayName, enuActivityType.DisplayName) as ActivityTypeDisplayName


	from


	dbo.ActivityDimvw A





	INNER JOIN


	dbo.WorkItemDimvw WI


	ON A.EntityDimKey = WI.EntityDimKey


	


	INNER JOIN


	dbo.ReviewActivityDimvw RAD


	ON A.EntityDimKey = RAD.EntityDimKey


	


    LEFT OUTER JOIN


    dbo.ReviewActivityHasReviewerFactvw RevAct


    ON RevAct.ActivityDimKey = A.ActivityDimKey


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


    


    LEFT OUTER JOIN


    dbo.ReviewerDimvw RD


    ON RevAct.ReviewActivityHasReviewer_ReviewerDimKey = RD.ReviewerDimKey


	


	LEFT OUTER JOIN


	dbo.ReviewActivityApprovalvw RAA


	on RAD.ApprovalCondition_ReviewActivityApprovalId = RAA.ReviewActivityApprovalId





	LEFT OUTER JOIN DisplayStringDimvw RADS


	ON RAA.EnumTypeId = RADS.BaseManagedEntityId


	AND RADS.LanguageCode = @LangCode





	INNER JOIN dbo.EntityManagedTypeFactvw EntityOfType


	ON EntityOfType.EntityDimKey = A.EntityDimKey





	INNER JOIN dbo.ManagedTypeDimvw EntityType


	ON EntityType.ManagedTypeDimKey = EntityOfType.ManagedTypeDimKey AND  (EntityType.TypeName = 'System.WorkItem.Activity.ReviewActivity')





	LEFT OUTER JOIN DisplayStringDimvw ActivityType


	ON ActivityType.BaseManagedEntityId = EntityType.BaseManagedEntityId


	AND ActivityType.LanguageCode = @LangCode	





	LEFT OUTER JOIN DisplayStringDimvw enuActivityType


	ON enuActivityType.BaseManagedEntityId = EntityType.BaseManagedEntityId


	AND enuActivityType.LanguageCode = 'ENU'	





	LEFT OUTER JOIN


	(


	Select


	ParentWIFact.WorkItemContainsActivity_ActivityDimKey ContainedActivityKey,


	ParentWI.Id,


	


	COALESCE(ParentCR.ChangeRequestDimKey, ParentInc.IncidentDimKey, ParentRR.ReleaseRecordDimKey) AS ParentDimKey,


	


	CASE WHEN ParentCR.EntityDimKey IS NOT NULL THEN 'System.WorkItem.ChangeRequest'


	     WHEN ParentInc.EntityDimKey IS NOT NULL THEN 'System.WorkItem.Incident'


	     WHEN ParentRR.EntityDimKey IS NOT NULL THEN 'System.WorkItem.ReleaseRecord'


	ELSE '' END ParentType	


		


	from


	dbo.WorkItemContainsActivityFactvw ParentWIFact


	INNER JOIN


	dbo.WorkItemDimvw ParentWI


	ON ParentWI.WorkItemDimKey = ParentWIFact.WorkItemDimKey


		


	LEFT OUTER JOIN


	dbo.ChangeRequestDimvw ParentCR


	ON ParentCR.EntityDimKey = ParentWI.EntityDimKey


	


	LEFT OUTER JOIN


	dbo.IncidentDimvw ParentInc


	ON ParentInc.EntityDimKey = ParentWI.EntityDimKey





	LEFT OUTER JOIN


	dbo.ReleaseRecordDimvw ParentRR


	ON ParentRR.EntityDimKey = ParentWI.EntityDimKey





	WHERE (@IncludeDeleted = 1 OR ParentWIFact.DeletedDate IS NULL)


	) ParentWI


	ON ParentWI.ContainedActivityKey = A.ActivityDimKey





	LEFT OUTER JOIN dbo.ActivityStatusvw StatusEnum


	ON StatusEnum.ActivityStatusId = A.Status_ActivityStatusId


	


	LEFT OUTER JOIN 


	dbo.DisplayStringDimvw StatusDS


	ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId


	AND StatusDS.LanguageCode = @LangCode





	LEFT OUTER JOIN dbo.ActivityStagevw StageEnum


	ON StageEnum.ActivityStageId = A.Stage_ActivityStageId


	


	LEFT OUTER JOIN 


	dbo.DisplayStringDimvw StageDS


	ON StageEnum.EnumTypeId=StageDS.BaseManagedEntityId


	AND StageDS.LanguageCode = @LangCode








	LEFT OUTER JOIN WorkItemContainsActivityFactvw WIAF


	ON A.ActivityDimKey = WIAF.WorkItemContainsActivity_ActivityDimKey





	WHERE


	((@Name IS NULL) OR (A.Title LIKE @Name)) AND


	(


		(@Id IS NULL) 


		OR 


		(


			A.Id IN 


			(


				SELECT string FROM dbo.fn_CSVToTableString(ISNULL(@Id,''))


			)


		)


	) AND


	


	((A.CreatedDate >= @CreatedOnFrom) AND (A.CreatedDate < @CreatedOnTo)) AND


	((-1 IN (Select value from @tableStatus)) OR (StatusEnum.ActivityStatusId IN (Select value from @tableStatus))) AND


	((-1 IN (Select value from @tableStage)) OR (StageEnum.ActivityStageId IN (Select value from @tableStage)))


	


	ORDER BY A.Id desc


        


  


  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO








GRANT EXECUTE ON dbo.ServiceManager_Report_ActivityManagement_SP_GetListOfReviewActivities TO reportuser


GO