ServiceManager.Report.ReleaseManagement.SP.GetListOfReleaseRecords.Install (Resource)

Element properties:

Source Code:

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

File Content: ServiceManager.Report.ReleaseManagement.SP.GetListOfReleaseRecords.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_ReleaseManagement_SP_GetListOfReleaseRecords' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_ReleaseManagement_SP_GetListOfReleaseRecords


GO





CREATE PROCEDURE dbo.ServiceManager_Report_ReleaseManagement_SP_GetListOfReleaseRecords


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


	@Type		nvarchar(max) = null,


	@Title		nvarchar(max) = null,


	@Template		nvarchar(max) = null,


	@CausesDowntime		bit = 0,


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


	@ScheduledDowntimeStartFrom datetime,


	@ScheduledDowntimeStartTo	datetime,


	@ScheduledDowntimeEndFrom	datetime,


	@ScheduledDowntimeEndTo	datetime,


	@ActualDowntimeStartFrom	datetime,


	@ActualDowntimeStartTo		datetime,


	@ActualDowntimeEndFrom		datetime,


	@ActualDowntimeEndTo		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)


  


  IF @CausesDowntime = 1


  BEGIN


	SET @ScheduledDowntimeStartTo = DATEADD(DAY, 1, @ScheduledDowntimeStartTo)


	SET @ScheduledDowntimeEndTo = DATEADD(DAY, 1, @ScheduledDowntimeEndTo)


	SET @ActualDowntimeStartTo= DATEADD(DAY, 1, @ActualDowntimeStartTo)


	SET @ActualDowntimeEndTo = DATEADD(DAY, 1, @ActualDowntimeEndTo)


  END


  ELSE


  BEGIN


	SET @ScheduledDowntimeStartFrom = NULL


	SET @ScheduledDowntimeStartTo = NULL


	SET @ScheduledDowntimeEndFrom = NULL


	SET @ScheduledDowntimeEndTo = NULL


	SET @ActualDowntimeStartFrom = NULL


	SET @ActualDowntimeStartTo= NULL


	SET @ActualDowntimeEndFrom = NULL


	SET @ActualDowntimeEndTo = NULL


  END


   


  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 @tableType TABLE (value nvarchar(256))  


  INSERT @tableType (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Type)





  DECLARE @tableTemplate TABLE (value nvarchar(256))  


  INSERT @tableTemplate (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Template)





  DECLARE @tableImplementationResults TABLE (value nvarchar(256))  


  INSERT @tableImplementationResults (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ImplementationResults)


  


  DECLARE @tableRelatedCIs TABLE (value nvarchar(256))  


  INSERT @tableRelatedCIs (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@RelatedCIs)





SELECT DISTINCT


	R.CreatedDate,  


	R.ScheduledStartDate, 


	R.ScheduledEndDate, 


	R.ActualStartDate,


	R.ActualEndDate, 


	R.Id, 


	R.Title,


	R.ReleaseRecordDimKey,


	


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


    StatusEnum.ID AS StatusValue,


              


    ISNULL(TypeEnumDS.DisplayName, TypeEnum.ReleaseTypeValue) AS Type,


    ISNULL(CategoryEnumDS.DisplayName, CategoryEnum.ReleaseCategoryValue) AS Category,


    ISNULL(ReleaseImplementationResultsEnumDS.DisplayName, ReleaseImplementationResultsEnum.ReleaseImplementationResultsValue) AS ImplementationResults,


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


    


    CreatedByUserDim.UserDimKey AS CreatedByUserId,


    


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


    


    AssignedToUserDim.UserDimKey AS AssignedToUserId





FROM 


	dbo.ReleaseRecordDimvw R


	


	INNER JOIN


	dbo.WorkItemDimvw WI ON


		WI.EntityDimKey = R.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.ReleaseStatusvw AS StatusEnum ON 


		StatusEnum.ReleaseStatusId = R.Status_ReleaseStatusId 


	


	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS StatusEnumDS ON


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


	


	LEFT OUTER JOIN


	dbo.ReleasePriorityvw AS PriorityEnum ON 


		PriorityEnum.ReleasePriorityId = R.Priority_ReleasePriorityId 


	


	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS PriorityEnumDS ON


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


	


	LEFT OUTER JOIN


	dbo.ReleaseImpactvw AS ImpactEnum ON 


		ImpactEnum.ReleaseImpactId = R.Impact_ReleaseImpactId 


	


	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS ImpactEnumDS ON


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


	


	LEFT OUTER JOIN


	dbo.ReleaseCategoryvw AS CategoryEnum ON 


		CategoryEnum.ReleaseCategoryId = R.Category_ReleaseCategoryId 


		


	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS CategoryEnumDS ON


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


				


	LEFT OUTER JOIN


	dbo.ReleaseTypevw AS TypeEnum ON 


		TypeEnum.ReleaseTypeId = R.Type_ReleaseTypeId 





	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS TypeEnumDS ON


		TypeEnum.EnumTypeId = TypeEnumDS.BaseManagedEntityId AND TypeEnumDS.LanguageCode = @LangCode





	LEFT OUTER JOIN


	dbo.ReleaseTemplatevw AS TemplateEnum ON 


		TemplateEnum.ReleaseTemplateId = R.TemplateId 





	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS TemplateEnumDS ON


		TemplateEnum.EnumTypeId = TemplateEnumDS.BaseManagedEntityId AND TemplateEnumDS.LanguageCode = @LangCode





	LEFT OUTER JOIN


	dbo.ReleaseImplementationResultsvw AS ReleaseImplementationResultsEnum ON 


		ReleaseImplementationResultsEnum.ReleaseImplementationResultsId = R.ImplementationResults_ReleaseImplementationResultsId 


		


	LEFT OUTER JOIN


	dbo.DisplayStringDimvw AS ReleaseImplementationResultsEnumDS ON


		ReleaseImplementationResultsEnum.EnumTypeId = ReleaseImplementationResultsEnumDS.BaseManagedEntityId AND ReleaseImplementationResultsEnumDS.LanguageCode = @LangCode





WHERE	


	(


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


	) AND


	(@StartDate <= @EndDate) AND


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





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


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


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


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





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


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


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


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


	


	((@ScheduledDowntimeStartFrom IS NULL) OR  (R.ScheduledDowntimeStartDate >=  @ScheduledDowntimeStartFrom)) AND


	((@ScheduledDowntimeStartTo IS NULL) OR (R.ScheduledDowntimeStartDate < @ScheduledDowntimeStartTo)) AND


	((@ScheduledDowntimeEndFrom IS NULL) OR (R.ScheduledEndDate >= @ScheduledDowntimeEndFrom)) AND


	((@ScheduledDowntimeEndTo IS NULL) OR (R.ScheduledEndDate < @ScheduledDowntimeEndTo)) AND





	((@ActualDowntimeStartFrom IS NULL) OR (R.ActualStartDate >= @ActualDowntimeStartFrom)) AND


	((@ActualDowntimeStartTo IS NULL) OR (R.ActualStartDate < @ActualDowntimeStartTo)) AND


	((@ActualDowntimeEndFrom IS NULL) OR (R.ActualDowntimeEndDate >= @ActualDowntimeEndFrom)) AND


	((@ActualDowntimeEndTo IS NULL) OR (R.ActualDowntimeEndDate < @ActualDowntimeEndTo)) AND


	


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


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





	((-1 IN (Select value from @tableStatus)) OR (R.Status_ReleaseStatusId  IN (Select value from @tableStatus))) AND


	((-1 IN (Select value from @tablePriority)) OR (R.Priority_ReleasePriorityId IN (Select value from @tablePriority))) AND


	((-1 IN (Select value from @tableImpact)) OR (R.Impact_ReleaseImpactId  IN (Select value from @tableImpact))) AND


	((-1 IN (Select value from @tableRisk)) OR (R.Risk_ReleaseRiskId  IN (Select value from @tableRisk))) AND


	((-1 IN (Select value from @tableCategory)) OR (R.Category_ReleaseCategoryId IN (Select value from @tableCategory))) AND


	((R.ImplementationResults_ReleaseImplementationResultsId IN (Select value from @tableImplementationResults)) OR (-1 in (Select value from @tableImplementationResults))) AND


	((-1 IN (Select value from @tableType)) OR (R.Type_ReleaseTypeId  IN (Select value from @tableType))) AND


	((-1 IN (Select value from @tableTemplate)) OR (R.TemplateId IN (Select tmplvw.ID from dbo.ReleaseTemplatevw tmplvw INNER JOIN @tableTemplate tblTmpl ON tmplvw.ReleaseTemplateId = tblTmpl.value))) AND


	((@Title IS NULL) OR (@Title = '') OR (R.Title LIKE '%'+@Title+'%')) AND


	((@Description IS NULL) OR (@Description = '') OR (R.Description LIKE '%'+@Description+'%')) AND


	(


		(


			(


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


			) 


			= 0


		) 


		OR 


		(


			WIACI.WorkItemAboutConfigItem_ConfigItemDimKey in (select value from @tableRelatedCIs)


		)


	)


	


ORDER BY R.Id


  


  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO





GRANT EXECUTE ON dbo.ServiceManager_Report_ReleaseManagement_SP_GetListOfReleaseRecords TO reportuser


GO