ServiceManager.Report.ChangeManagement.SP.GetListOfRFCs.Install (Resource)

Element properties:

Source Code:

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

File Content: ServiceManager.Report.ChangeManagement.SP.GetListOfRFCs.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_ChangeManagement_SP_GetListOfRFCs_Install' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_Install


GO





CREATE PROCEDURE dbo.ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_Install


	@AssignedTo	int = -1, 


	@CreatedBy	int = -1, 


	@DateFilter	nvarchar(256) = null,


	@StartDate	datetime,


	@EndDate	datetime,


	@ID			nvarchar(max)  = null,


	@Description	nvarchar(max) = null,


	@Status		nvarchar(max) = null, 


	@Priority	nvarchar(max) = null,


	@Impact		nvarchar(max) = null,


	@Risk		nvarchar(max) = null,


	@Category	nvarchar(max) = null,


	@ChangeResult	nvarchar(max) = null,


	@RelatedCIs  nvarchar(max) = null,


	@LangCode nvarchar(15) = 'ENU',


	@ScheduledStartFrom datetime,


	@ScheduledStartTo	datetime,


	@ScheduledEndFrom	datetime,


	@ScheduledEndTo	datetime,


	@ActualStartFrom	datetime,


	@ActualStartTo		datetime,


	@ActualEndFrom		datetime,


	@ActualEndTo		datetime,


	@IncludeDeleted bit = 0


AS


BEGIN


  SET NOCOUNT ON





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


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


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


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


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


  


  DECLARE @Error int


  DECLARE @ExecError int


  


  DECLARE @tableID TABLE (value nvarchar(256))  


  INSERT @tableID (value)


  SELECT * FROM dbo.fn_CSVToTableString(ISNULL(@ID,''))


  


  DECLARE @tableStatus TABLE (value nvarchar(256))  


  INSERT @tableStatus (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Status)


  


  DECLARE @tablePriority TABLE (value nvarchar(256))  


  INSERT @tablePriority (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Priority)


  


  DECLARE @tableImpact TABLE (value nvarchar(256))  


  INSERT @tableImpact (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Impact)





  DECLARE @tableRisk TABLE (value nvarchar(256))  


  INSERT @tableRisk (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Risk)


  


  DECLARE @tableCategory TABLE (value nvarchar(256))  


  INSERT @tableCategory (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Category)


  


  DECLARE @tableChangeResult TABLE (value nvarchar(256))  


  INSERT @tableChangeResult (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ChangeResult)


  


  DECLARE @tableRelatedCIs TABLE (value nvarchar(256))  


  INSERT @tableRelatedCIs (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@RelatedCIs)





SELECT DISTINCT


	C.CreatedDate,  


	C.ScheduledStartDate, 


	C.ScheduledEndDate, 


	C.ActualStartDate,


	C.ActualEndDate, 


	C.Id, 


	C.Title, 	


	C.ChangeRequestDimKey,


	C.Category,


	


	ISNULL(StatusEnumDS.DisplayName, StatusEnum.ChangeStatusValue) AS Status,


    StatusEnum.ID AS StatusValue,


              


    ISNULL(CategoryEnumDS.DisplayName, CategoryEnum.ChangeCategoryValue) AS CategoryValue,


              


    ISNULL(CreatedByUserDim.DisplayName, CreatedByUserDim.UserName) AS CreatedByUser,


    


    CreatedByUserDim.UserDimKey AS CreatedByUserId,


    


    ISNULL(AssignedToUserDim.DisplayName, AssignedToUserDim.UserName) AS AssignedToUser,


    


    AssignedToUserDim.UserDimKey AS AssignedToUserId





FROM 


	dbo.ChangeRequestDimvw C


	


	INNER JOIN


	dbo.WorkItemDimvw WI ON


		WI.EntityDimKey = C.EntityDimKey


	


	LEFT OUTER JOIN 


	dbo.WorkItemAssignedToUserFactvw WIATU ON	


		WI.WorkItemDimKey = WIATU.WorkItemDimKey AND (@IncludeDeleted = 1 OR WIATU.DeletedDate IS NULL)


	


	LEFT OUTER JOIN	


	dbo.UserDimvw AS AssignedToUserDim ON 


		WIATU.WorkItemAssignedToUser_UserDimKey = AssignedToUserDim.UserDimKey





	LEFT OUTER JOIN


	dbo.WorkItemCreatedByUserFactvw WICBU ON


		WI.WorkItemDimKey = WICBU.WorkItemDimKey AND (@IncludeDeleted = 1 OR WICBU.DeletedDate IS NULL)


	


	LEFT OUTER JOIN


	dbo.UserDimvw AS CreatedByUserDim ON 


		WICBU.WorkItemCreatedByUser_UserDimKey = CreatedByUserDim.UserDimKey 


		


    LEFT OUTER JOIN


    dbo.WorkItemAboutConfigItemFactvw AS WIACI	ON


    WIACI.WorkItemDimKey = WI.WorkItemDimKey AND (@IncludeDeleted = 1 OR WIACI.DeletedDate IS NULL)


	


	LEFT OUTER JOIN


	dbo.ChangeStatusvw AS StatusEnum ON 


		StatusEnum.ChangeStatusId = C.Status_ChangeStatusId 


	


	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS StatusEnumDS ON


		StatusEnum.EnumTypeId = StatusEnumDS.BaseManagedEntityId AND StatusEnumDS.LanguageCode = @LangCode


	


	LEFT OUTER JOIN


	dbo.ChangePriorityvw AS PriorityEnum ON 


		PriorityEnum.ChangePriorityId = C.Priority_ChangePriorityId 


	


	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS PriorityEnumDS ON


		PriorityEnum.EnumTypeId = PriorityEnumDS.BaseManagedEntityId AND PriorityEnumDS.LanguageCode = @LangCode


	


	LEFT OUTER JOIN


	dbo.ChangeImpactvw AS ImpactEnum ON 


		ImpactEnum.ChangeImpactId = C.Impact_ChangeImpactId 


	


	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS ImpactEnumDS ON


		ImpactEnum.EnumTypeId = ImpactEnumDS.BaseManagedEntityId AND ImpactEnumDS.LanguageCode = @LangCode


	


	LEFT OUTER JOIN


	dbo.ChangeCategoryvw AS CategoryEnum ON 


		CategoryEnum.ChangeCategoryId = C.Category_ChangeCategoryId 


		


	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS CategoryEnumDS ON


		CategoryEnum.EnumTypeId = CategoryEnumDS.BaseManagedEntityId AND CategoryEnumDS.LanguageCode = @LangCode


				


WHERE	


	(


		(@ID IS NULL) OR (C.Id IN (Select value from @tableID))


	) AND


	(@StartDate <= @EndDate) AND


	((C.CreatedDate >= @StartDate) AND (C.CreatedDate < @EndDate)) AND


	((@ScheduledStartFrom IS NULL) OR  (C.ScheduledStartDate >=  @ScheduledStartFrom)) AND


	((@ScheduledStartTo IS NULL) OR (C.ScheduledStartDate < @ScheduledStartTo)) AND





	((@ScheduledEndFrom IS NULL) OR (C.ScheduledEndDate >= @ScheduledEndFrom)) AND


	((@ScheduledEndTo IS NULL) OR (C.ScheduledEndDate < @ScheduledEndTo)) AND





	((@ActualStartFrom IS NULL) OR (C.ActualStartDate >= @ActualStartFrom)) AND


	((@ActualStartTo IS NULL) OR (C.ActualStartDate < @ActualStartTo)) AND





	((@ActualEndFrom IS NULL) OR (C.ActualEndDate >= @ActualEndFrom)) AND


	((@ActualEndTo IS NULL) OR (C.ActualEndDate < @ActualEndTo)) AND


	


	((@AssignedTo = 0) OR @AssignedTo = AssignedToUserDim.UserDimKey) AND


	((@CreatedBy = 0) OR @CreatedBy = CreatedByUserDim.UserDimKey) AND





	((-1 IN (Select value from @tableStatus)) OR (C.Status_ChangeStatusId  IN (Select value from @tableStatus))) AND


	((-1 IN (Select value from @tablePriority)) OR (C.Priority_ChangePriorityId IN (Select value from @tablePriority))) AND


	((-1 IN (Select value from @tableImpact)) OR (C.Impact_ChangeImpactId  IN (Select value from @tableImpact))) AND


	((-1 IN (Select value from @tableRisk)) OR (C.Risk_ChangeRiskId  IN (Select value from @tableRisk))) AND


	((-1 IN (Select value from @tableCategory)) OR (C.Category_ChangeCategoryId IN (Select value from @tableCategory))) AND


	((C.ImplementationResults_ChangeImplementationResultsId IN (Select value from @tableChangeResult)) OR (-1 in (Select value from @tableChangeResult))) AND


	(


		(


			(


				select COUNT (*) from @tableRelatedCIs where value > 0


			) 


			= 0


		) 


		OR 


		(


			WIACI.WorkItemAboutConfigItem_ConfigItemDimKey in (select value from @tableRelatedCIs)


		)


	)


	


ORDER BY C.Id


  


  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO





GRANT EXECUTE ON dbo.ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_Install TO reportuser


GO