/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCOCountByStatus] Script Date: 02/25/2010 04:43:59 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCOCountByStatus]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCOCountByStatus]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCOCountByStatus] Script Date: 02/25/2010 04:43:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MilindMa
-- Create date:
-- Last Updated: 10-26-2009 by milindma
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCOCountByStatus]
-- Add the parameters for the stored procedure here
@StartDate DATETIME,
@EndDate DATETIME,
@ProgramId INT=null,
@ADValue INT=null,
@AssignedTo INT=null,
@OwnedBy INT=null,
@ADCategoryId nvarchar(max) = '-1'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @tableADCategoryForCOCount TABLE (value nvarchar(256))
INSERT @tableADCategoryForCOCount (value)
SELECT * FROM dbo.fn_CSVToTableInt(@ADCategoryId)
DECLARE @Error int
-- Insert statements for procedure here
IF OBJECT_ID(N'tempdb..#COCNTCAT', N'U') IS NOT NULL
BEGIN
DROP TABLE #COCNTCAT
END;
WITH COCNTCATData (ProgramDimKey, CategoryDimKey)
AS
(
SELECT ProgramDimKey, ProgramHasCategory_CategoryDimKey AS CategoryDimKey
FROM ProgramHasCategoriesFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
UNION ALL
SELECT ProgramDimKey, CategoryContainsCategory_CategoryDimKey as CategoryDimKey
FROM dbo.CategoryContainsCategoryFactvw AS CC
INNER JOIN COCNTCATData AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
)
SELECT ProgramDimKey, CategoryDimKey into #COCNTCAT FROM COCNTCATData;
IF OBJECT_ID(N'tempdb..#COCNTDimKey', N'U') IS NOT NULL
BEGIN
DROP TABLE #COCNTDimKey
END;
WITH COCNTDimKeyData (CategoryDimKey, ControlObjectiveDimKey)
AS
(
SELECT A.CategoryDimKey, A.CategoryContainsControlObjective_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM CategoryHasCOFactvw A
INNER JOIN #COCNTCAT B
ON A.CategoryDimKey = B.CategoryDimKey
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = A.CategoryContainsControlObjective_ControlObjectiveDimKey
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
WHERE
(0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND ((-1 IN (Select value from @tableADCategoryForCOCount )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCOCount)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
UNION ALL
SELECT CategoryDimKey, CC.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactVw AS CC
INNER JOIN COCNTDimKeyData AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
)
SELECT CategoryDimKey, ControlObjectiveDimKey into #COCNTDimKey FROM COCNTDimKeyData;
IF OBJECT_ID(N'tempdb..#COCNTPHasCO', N'U') IS NOT NULL
BEGIN
DROP TABLE #COCNTPHasCO
END;
WITH COCNTPHasCO (ProgramDimKey, ControlObjectiveDimKey)
AS
(
SELECT ProgramDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM ProgramHasCOFactvw
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND ((-1 IN (Select value from @tableADCategoryForCOCount )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCOCount)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
UNION ALL
SELECT ProgramDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactvw AS CC
INNER JOIN COCNTPHasCO AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
)
SELECT ProgramDimKey, ControlObjectiveDimKey into #COCNTPHasCO FROM COCNTPHasCO;
IF OBJECT_ID(N'tempdb..#COCNTList', N'U') IS NOT NULL
BEGIN
DROP TABLE #COCNTList
END;
INSERT INTO #COCNTList
SELECT DISTINCT ControlObjectiveDimKey
FROM #COCNTDimKey
UNION
SELECT DISTINCT ControlObjectiveDimKey
FROM #COCNTPHasCO
SELECT '"' + CONVERT(varchar, (SELECT COUNT(Distinct COFilter.Id)
FROM (
SELECT DISTINCT
CO.ControlObjectiveDimKey,
CO.Id,
CO.Title,
CO.COStatusValue,
CO.CreatedDate,
CO.LastModifiedDateTime AS ModifiedDate,
CO.UserName AS Owner
FROM
(SELECT DISTINCT
COSD.ControlObjectiveDimKey,
CO.Id,
CO.Title AS Title,
CS.COStatusId,
CS.COStatusValue,
CO.CreatedDate,
COSD.LastModifiedDateTime,
COSD.StartDateTime,
COSD.FinishDateTime,
OwnedBy.UserName
FROM ControlObjectiveStatusDurationFactvw COSD
JOIN ControlObjectiveDimvw CO
ON CO.ControlObjectiveDimKey = COSD.ControlObjectiveDimKey
JOIN #COCNTList AS CODimKey
ON CODimKey.ControlObjectiveDimKey = COSD.ControlObjectiveDimKey
JOIN COStatusvw CS
ON CS.COStatusId = COSD.COStatusId
JOIN ControlObjectiveOwnedByUserFactvw COO
ON COO.ControlObjectiveDimKey = CO.ControlObjectiveDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = COO.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR COO.ConfigItemOwnedByUser_UserDimKey = @OwnedBy)) AS CO
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = CO.ControlObjectiveDimKey
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
WHERE ((DATEDIFF(DAY, CO.LastModifiedDateTime, @StartDate) <= 0 AND DATEDIFF(DAY, CO.LastModifiedDateTime, @EndDate) >=0)
OR
(CO.FinishDateTime IS NOT NULL AND DATEDIFF(DAY, CO.FinishDateTime, @StartDate) <= 0 AND DATEDIFF(DAY, CO.FinishDateTime, @EndDate) >=0))
AND CO.COStatusId = COS.COStatusId
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND ((-1 IN (Select value from @tableADCategoryForCOCount )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCOCount)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)) AS COFilter
LEFT JOIN ControlObjectiveAssignedToUserFactvw COA
ON COA.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
LEFT JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = COA.PolicyItemAssignedTo_UserDimKey
Where (0 = @AssignedTo OR COA.PolicyItemAssignedTo_UserDimKey = @AssignedTo)))
+'" ' + 'Control Objectives ' + COS.[COSTatusValue] as HeaderValue,
COS.[COSTatusId], [COStatusValue]
FROM [dbo].[COStatusvw] COS
WHERE COS.ParentId IS NOT NULL
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetCOCountByStatus TO reportuser
GO
Home