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

Element properties:

Source Code:

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

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

/****** Object:  StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeGroups]    Script Date: 12/02/2010 ******/


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


DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeGroups]


GO





/****** Object:  StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeGroups]    Script Date: 12/02/2010 ******/


SET ANSI_NULLS ON


GO





SET QUOTED_IDENTIFIER ON


GO








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


-- Author:		MICROSOFT


-- Create date: 12-02-2010


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


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








CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeGroups]


	@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.ConfigItemDimKey AS ConfigItemDimKey,


	CG.ConfigItemGroupDimKey AS GroupID,


	CG.DisplayName	AS GroupName





FROM ProgramScopeFactvw PSF


	JOIN ConfigItemDimvw CID


	ON PSF.ProgramScope_ConfigItemDimKey = CID.ConfigItemDimKey 


	AND CID.IsDeleted = 0


	


	INNER JOIN 


	dbo.ConfigItemGroupDimvw CG


	ON CG.EntityDimKey = CID.EntityDimKey


	AND CG.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_GetProgramScopeGroups TO reportuser


GO