ServiceManager.Report.Compliance.ControlManagement.SP.GetProgramScopeServices.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.Compliance.ControlManagement.SP.GetProgramScopeServices.Install" Accessibility="Public" FileName="ServiceManager.Report.GRC.ControlManagement.SP.GetProgramScopeServices.Install.sql"/>

File Content: ServiceManager.Report.GRC.ControlManagement.SP.GetProgramScopeServices.Install.sql

/****** Object:  StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeServices]    Script Date: 03/29/2010 02:08:04 ******/


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeServices]') AND type in (N'P', N'PC'))


DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeServices]


GO





/****** Object:  StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeServices]    Script Date: 03/29/2010 02:08:04 ******/


SET ANSI_NULLS ON


GO





SET QUOTED_IDENTIFIER ON


GO








-- =============================================


-- Author:		MICROSOFT


-- Create date: 03-26-2010


-- Description:	To get the list of services associated with Program


-- =============================================








CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeServices]


	@ProgramId int = null,


	@StartDate Datetime,


	@EndDate Datetime


	


AS


BEGIN





	-- SET NOCOUNT ON added to prevent extra result sets from


	-- interfering with SELECT statements.


  SET NOCOUNT ON





  DECLARE @Error int


   


   


SELECT	DISTINCT


	CID.DisplayName,


	CID.ConfigItemDimKey,


	


	S.BusinessDetailedDescription,


	S.AvailabilitySchedule,


	S.OwnedByOrganization,


	S.ServiceDescription,


	


	SC.ServiceClassificationValue AS Classification,	


	SP.ServicePriorityValue AS Priority,	


	SS.ServiceStatusValue AS [Status],	





	UDO.DisplayName AS OwnedBy,


	UDS.DisplayName AS ServicedBy	





FROM ProgramScopeFactvw PSF


	JOIN ConfigItemDimvw CID


	ON PSF.ProgramScope_ConfigItemDimKey = CID.ConfigItemDimKey 


	AND CID.IsDeleted = 0


	


	INNER JOIN 


	dbo.ServiceDimvw S


	ON S.EntityDimKey = CID.EntityDimKey


	AND S.IsDeleted = 0


	


	LEFT OUTER JOIN 


	dbo.ServiceClassificationvw SC


	ON S.Classification_ServiceClassificationId = SC.ServiceClassificationId 


	


	LEFT OUTER JOIN 


	dbo.ServicePriorityvw SP


	ON S.Priority_ServicePriorityId = SP.ServicePriorityId


	


	LEFT OUTER JOIN 


	dbo.ServiceStatusvw SS


	ON S.Status_ServiceStatusId = SS.ServiceStatusId 


	


	LEFT OUTER JOIN 


	ConfigItemOwnedByUserFactvw COU


	ON CID.ConfigItemDimKey = COU.ConfigItemDimKey 


	AND COU.DeletedDate IS NULL





	LEFT OUTER JOIN 


	UserDimvw UDO


	ON UDO.UserDimKey = COU.ConfigItemOwnedByUser_UserDimKey 


	AND UDO.IsDeleted = 0





	LEFT OUTER JOIN 


	ConfigItemServicedByUserFactvw CSU


	ON CID.ConfigItemDimKey = CSU.ConfigItemDimKey 


	AND CSU.DeletedDate IS NULL





	LEFT OUTER JOIN 


	UserDimvw UDS


	ON UDS.UserDimKey = CSU.ConfigItemServicedByUser_UserDimKey 


	AND UDS.IsDeleted = 0





WHERE ProgramDimKey = @ProgramId 


	AND (DATEDIFF(DAY, PSF.CreatedDate, @StartDate) <= 0 AND DATEDIFF(DAY, PSF.CreatedDate, @EndDate) >=0)	 


	AND PSF.DeletedDate IS NULL





          


  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END











GO











GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeServices TO reportuser


GO