/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlActivityDetails] Script Date: 06/08/2010 02:58:48 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlActivityDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlActivityDetails]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlActivityDetails] Script Date: 06/08/2010 02:58:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: <Create Date,,>
-- Description: To Get CA Details
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ControlActivityDetails]
-- Add the parameters for the stored procedure here
@CAId INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT CA.ControlActivityDimKey,
CA.Id,
CA.Title AS Title,
CA.Description,
OwnedBy.UserName As Owner,
AssignedTo.UserName AS AssignedTo,
CAT.CATypeValue AS Type,
(CASE
WHEN EXISTS (SELECT MT.TypeName
FROM ManagedTypeDimvw MT
INNER JOIN EntityManagedTypeFactvw EMT
ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey
AND CA.EntityDimKey = EMT.EntityDimKey
WHERE MT.TypeName = 'System.Compliance.ManualControlActivity')
THEN 'Manual' ELSE 'Automated' END) AS ProcedureType,
CALevel.LevelValue,
Priority.PriorityValue,
CSV.PolicyItemSharedValue AS Shared,
CA.Frequency AS Frequency,
CA.ScheduledStartDate,
CA.ScheduledEndDate,
CA.ActualStartDate,
CA.ActualEndDate,
CA.CreatedDate,
CAS.CAStatusValue,
CA.ImplementationMethod AS ImplementationMethod,
CA.GapStatement AS GapStatement,
CA.AdditionalGuidance AS AdditionalGuidance,
CA.TestId AS TestId,
CA.TestName AS TestName,
CA.TestSummary AS TestSummary,
CA.TestCriteria AS TestCriteria,
CA.TestGapStatement AS TestGapStatement,
CA.ExternalId,
CSN.ComplianceSourceNameValue AS ExternalName,
CA.ExternalVersion,
CreatedBy.UserName AS CreatedBy,
ModifiedBy.UserName AS ModifiedBy
FROM ControlActivityDimvw CA
LEFT JOIN MERResultvw CAR
ON CAR.MERResultId = CA.Result_MERResultId
LEFT JOIN CATypevw CAT
ON CAT.CATypeId = CA.Type_CATypeId
LEFT JOIN PolicyItemSharedvw CSV
ON CSV.PolicyItemSharedId = CA.Shared_PolicyItemSharedId
LEFT JOIN ComplianceSourceNamevw CSN
ON CSN.ComplianceSourceNameId = CA.ExternalName_ComplianceSourceNameId
LEFT JOIN ControlActivityAssignedToUserFactvw CAA
ON CAA.ControlActivityDimKey = CA.ControlActivityDimKey
LEFT JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = CAA.WorkItemAssignedToUser_UserDimKey
LEFT JOIN CAStatusvw CAS
ON CAS.CAStatusId = CA.ControlActivityStatus_CAStatusId
LEFT JOIN ControlActivityOwnedByUserFactvw CAO
ON CAO.ControlActivityDimKey = CA.ControlActivityDimKey
LEFT JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = CAO.ControlActivityOwnedBy_UserDimKey
LEFT JOIN Priority Priority
ON Priority.PriorityId = CA.ControlActivityPriority_PriorityId
LEFT JOIN Level CALevel
ON CALevel.LevelId = CA.Level_LevelId
LEFT JOIN ControlActivityCreatedByUserFactvw CACU
ON CACU.ControlActivityDimKey = CA.ControlActivityDimKey
AND CACU.DeletedDate IS NULL
LEFT JOIN UserDimvw CreatedBy
ON CreatedBy.UserDimKey = CACU.ControlActivityCreatedBy_UserDimKey
LEFT JOIN ControlActivityModifiedByUserFactvw CAMU
ON CAMU.ControlActivityDimKey = CA.ControlActivityDimKey
AND CAMU.DeletedDate IS NULL
LEFT JOIN UserDimvw ModifiedBy
ON ModifiedBy.UserDimKey = CAMU.ControlActivityModifiedBy_UserDimKey
WHERE CA.ControlActivityDimKey = @CAId
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_ControlActivityDetails TO reportuser
GO
Home