/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCAsRelatedToCO] Script Date: 03/25/2010 05:16:48 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCAsRelatedToCO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCAsRelatedToCO]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCAsRelatedToCO] Script Date: 03/25/2010 05:16:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCAsRelatedToCO]
-- Add the parameters for the stored procedure here
@ControlObjectiveDimKey varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT DISTINCT
CA.Id AS CAId,
CA.Title AS CATitle,
CAS.CAStatusValue AS CAStatus,
CA.CreatedDate AS CACreatedDate,
CA.ControlActivityDimKey
FROM ControlActivityDimvw CA
INNER JOIN COHasCAFactvw COCA
ON COCA.ControlObjectiveContainsControlActivity_ControlActivityDimKey = CA.ControlActivityDimKey
INNER JOIN CAStatusvw CAS
ON CAS.CAStatusId = CA.ControlActivityStatus_CAStatusId
INNER JOIN ControlObjectiveDimvw CO
ON CO.ControlObjectiveDimKey = COCA.ControlObjectiveDimKey
WHERE CO.ControlObjectiveDimKey = @ControlObjectiveDimKey
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetCAsRelatedToCO TO reportuser
GO
Home