/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCACountByStatus] Script Date: 02/25/2010 04:49:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCACountByStatus]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCACountByStatus]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCACountByStatus] Script Date: 02/25/2010 04:49:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Last Updated: 10/29/2009 milindma
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetCACountByStatus]
@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 @Error int
DECLARE @tableADCategoryForCACount TABLE (value nvarchar(256))
INSERT @tableADCategoryForCACount (value)
SELECT * FROM dbo.fn_CSVToTableInt(@ADCategoryId)
IF OBJECT_ID(N'tempdb..#CACNTCAT', N'U') IS NOT NULL
BEGIN
DROP TABLE #CACNTCAT
END;
WITH CACNTCATData (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 CACNTCATData AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
)
SELECT ProgramDimKey, CategoryDimKey into #CACNTCAT FROM CACNTCATData;
-- Test
--SELECT * FROM #CACNTCAT;
IF OBJECT_ID(N'tempdb..#CACntDimKey', N'U') IS NOT NULL
BEGIN
DROP TABLE #CACntDimKey
END;
WITH CACNTDimKeyData (CategoryDimKey, ControlObjectiveDimKey)
AS
(
SELECT A.CategoryDimKey, A.CategoryContainsControlObjective_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM CategoryHasCOFactvw A
INNER JOIN #CACNTCAT 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
--LEFT JOIN COAuthorityDocFactvw COAD ON
--COAD.ControlObjectiveDimKey = A.CategoryContainsControlObjective_ControlObjectiveDimKey
--LEFT JOIN AuthorityDocumentDimvw AD
--ON AD.AuthorityDocumentDimKey = COAD.PolicyItemRelatesToAuthorityDocument_AuthorityDocumentDimKey
--LEFT JOIN AuthorityDocumentCategoryvw ADC
--ON ADC.AuthorityDocumentCategoryId = AD.GRCDocumentCategory_AuthorityDocumentCategoryId
WHERE
(0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND ((-1 IN (Select value from @tableADCategoryForCACount )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCACount)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
UNION ALL
SELECT CategoryDimKey, CC.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactVw AS CC
INNER JOIN CACNTDimKeyData AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
)
SELECT CategoryDimKey, ControlObjectiveDimKey into #CACNTDimKey FROM CACNTDimKeyData;
--Test
--SELECT * FROM #CACNTDimKey
IF OBJECT_ID(N'tempdb..#CACntList', N'U') IS NOT NULL
BEGIN
DROP TABLE #CACntList
END;
WITH CACntList (ControlObjectiveDimKey, ControlActivityDimKey)
AS
(
SELECT A.ControlObjectiveDimKey, B.ControlObjectiveContainsControlActivity_ControlActivityDimKey AS ControlActivityDimKey
FROM #CACntDimKey A
INNER JOIN COHasCAFactvw B
ON A.ControlObjectiveDimKey = B.ControlObjectiveDimKey
UNION ALL
SELECT ControlObjectiveDimKey, CC.ControlActivityContainsControlActivity_ControlActivityDimKey as ControlActivityDimKey
FROM CAHasCAFactvw AS CC
INNER JOIN CACntList AS CD
ON CC.ControlActivityDimKey = CD.ControlActivityDimKey
)
SELECT ControlObjectiveDimKey, ControlActivityDimKey into #CACntList FROM CACntList;
--Test
--SELECT * FROM #CACntList
IF OBJECT_ID(N'tempdb..#CACntPHasCO', N'U') IS NOT NULL
BEGIN
DROP TABLE #CACntPHasCO
END;
WITH CACntPHasCO (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
--LEFT JOIN COAuthorityDocFactvw COAD ON
--COAD.ControlObjectiveDimKey = PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey
--LEFT JOIN AuthorityDocumentDimvw AD
--ON AD.AuthorityDocumentDimKey = COAD.PolicyItemRelatesToAuthorityDocument_AuthorityDocumentDimKey
--LEFT JOIN AuthorityDocumentCategoryvw ADC
--ON ADC.AuthorityDocumentCategoryId = AD.GRCDocumentCategory_AuthorityDocumentCategoryId
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND ((-1 IN (Select value from @tableADCategoryForCACount )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCACount)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
UNION ALL
SELECT ProgramDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactvw AS CC
INNER JOIN CACntPHasCO AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
)
SELECT ProgramDimKey, ControlObjectiveDimKey into #CACntPHasCO FROM CACntPHasCO;
--Test
--SELECT * FROM #CACntPHasCO
IF OBJECT_ID(N'tempdb..#CACntPCAList', N'U') IS NOT NULL
BEGIN
DROP TABLE #CACntPCAList
END;
WITH CACntPCA (ControlObjectiveDimKey, ControlActivityDimKey)
AS
(
SELECT A.ControlObjectiveDimKey, B.ControlObjectiveContainsControlActivity_ControlActivityDimKey
FROM #CACntPHasCO A
INNER JOIN dbo.COHasCAFactvw B
ON A.ControlObjectiveDimKey = B.ControlObjectiveDimKey
UNION ALL
SELECT ControlObjectiveDimKey, CC.ControlActivityContainsControlActivity_ControlActivityDimKey as ControlActivityDimKey
FROM CAHasCAFactvw AS CC
INNER JOIN CACntPCA AS CD
ON CC.ControlActivityDimKey = CD.ControlActivityDimKey
)
SELECT ControlObjectiveDimKey, ControlActivityDimKey into #CACntPCAList FROM CACntPCA;
--Test
--SELECT * FROM #CACntPCAList
IF OBJECT_ID(N'tempdb..#CACntAllList', N'U') IS NOT NULL
BEGIN
DROP TABLE #CACntAllList
END;
CREATE TABLE #CACntAllList
(ControlObjectiveDimKey int
, ControlActivityDimKey int)
INSERT INTO #CACntAllList
SELECT DISTINCT ControlObjectiveDimKey, ControlActivityDimKey
FROM #CACntList
UNION
SELECT DISTINCT ControlObjectiveDimKey, ControlActivityDimKey
FROM #CACntPCAList
--Test
--SELECT * FROM #CACntAllList
-- Insert statements for procedure here
SELECT '"' + Convert(varchar,(SELECT COUNT(DISTINCT CAFilter.Id)
FROM (SELECT DISTINCT CA.*
FROM (SELECT CA.ControlActivityDimKey,
CA.Id,
CA.Title AS Title,
CS.CAStatusValue,
CS.CAStatusId,
CA.CreatedDate,
CAList.ControlObjectiveDimKey
FROM ControlActivityStatusDurationFactvw CASD
JOIN ControlActivityDimvw CA
ON CA.ControlActivityDimKey = CASD.ControlActivityDimKey
JOIN #CACntAllList CAList
ON CAList.ControlActivityDimKey = CA.ControlActivityDimKey
JOIN CAStatusvw CS
ON CS.CAStatusId = CASD.CAStatusId
WHERE ((DATEDIFF(DAY, LastModifiedDateTime, @StartDate) <= 0 AND DATEDIFF(DAY, LastModifiedDateTime, @EndDate) >=0)
OR
(FinishDateTime IS NOT NULL AND DATEDIFF(DAY, FinishDateTime, @StartDate) <= 0 AND DATEDIFF(DAY, FinishDateTime, @EndDate) >=0))) AS CA
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = CA.ControlObjectiveDimKey
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
--LEFT JOIN COAuthorityDocFactvw COAD
--ON COAD.ControlObjectiveDimKey = CA.ControlObjectiveDimKey
--LEFT JOIN AuthorityDocumentDimvw AD
--ON AD.AuthorityDocumentDimKey = COAD.PolicyItemRelatesToAuthorityDocument_AuthorityDocumentDimKey
WHERE ((-1 IN (Select value from @tableADCategoryForCACount )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCACount)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND CA.CAStatusId = CASV.CAStatusId) AS CAFilter
JOIN ControlActivityOwnedByUserFactvw CAOW
ON CAOW.ControlActivityDimKey = CAFilter.ControlActivityDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = CAOW.ControlActivityOwnedBy_UserDimKey
LEFT JOIN ControlActivityAssignedToUserFactvw CAA
ON CAA.ControlActivityDimKey = CAFilter.ControlActivityDimKey
LEFT JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = CAA.WorkItemAssignedToUser_UserDimKey
WHERE (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
AND (0 = @AssignedTo OR AssignedTo.UserDimKey = @AssignedTo)))
+ '" Control Activities '+ CASV.CAStatusValue AS HeaderValue, CASV.CAStatusId, CASV.CAStatusValue
FROM CAStatusvw CASV WHERE CASV.ParentId IS NOT NULL
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetCACountByStatus TO reportuser
GO
Home