/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCAs] Script Date: 06/08/2010 03:06:35 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCAs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCAs]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCAs] Script Date: 06/08/2010 03:06:35 ******/
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_GetListOfCAs]
-- Add the parameters for the stored procedure here
@StatusId INT,
@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;
IF OBJECT_ID(N'tempdb..#CAT', N'U') IS NOT NULL
BEGIN
DROP TABLE #CAT
END;
WITH CATData (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 CATData AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
)
SELECT ProgramDimKey, CategoryDimKey into #CAT FROM CATData;
IF OBJECT_ID(N'tempdb..#CODimKey', N'U') IS NOT NULL
BEGIN
DROP TABLE #CODimKey
END;
WITH CODimKeyData (CategoryDimKey, ControlObjectiveDimKey)
AS
(
SELECT A.CategoryDimKey, A.CategoryContainsControlObjective_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM CategoryHasCOFactvw A
INNER JOIN #CAT 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 @tableADCategory )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategory)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
UNION ALL
SELECT CategoryDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactVw AS CC
INNER JOIN CODimKeyData AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
)
SELECT CategoryDimKey, ControlObjectiveDimKey into #CODimKey FROM CODimKeyData;
IF OBJECT_ID(N'tempdb..#CAList', N'U') IS NOT NULL
BEGIN
DROP TABLE #CAList
END;
WITH CAList (ControlObjectiveDimKey, ControlActivityDimKey)
AS
(
SELECT A.ControlObjectiveDimKey, B.ControlObjectiveContainsControlActivity_ControlActivityDimKey AS ControlActivityDimKey
FROM #CODimKey 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 CAList AS CD
ON CC.ControlActivityDimKey = CD.ControlActivityDimKey
)
SELECT ControlObjectiveDimKey, ControlActivityDimKey into #CAList FROM CAList;
IF OBJECT_ID(N'tempdb..#PHasCO', N'U') IS NOT NULL
BEGIN
DROP TABLE #PHasCO
END;
WITH PHasCO (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 @tableADCategory )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategory)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
UNION ALL
SELECT ProgramDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactvw AS CC
INNER JOIN PHasCO AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
)
SELECT ProgramDimKey, ControlObjectiveDimKey into #PHasCO FROM PHasCO;
IF OBJECT_ID(N'tempdb..#PCAList', N'U') IS NOT NULL
BEGIN
DROP TABLE #PCAList
END;
WITH PCA (ControlObjectiveDimKey, ControlActivityDimKey)
AS
(
SELECT A.ControlObjectiveDimKey, B.ControlObjectiveContainsControlActivity_ControlActivityDimKey
FROM #PHasCO 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 PCA AS CD
ON CC.ControlActivityDimKey = CD.ControlActivityDimKey
)
SELECT ControlObjectiveDimKey, ControlActivityDimKey into #PCAList FROM PCA;
SELECT DISTINCT CAFilter.ControlActivityDimKey
, CAFilter.Id
, CAFilter.Title
, CAFilter.CAStatusValue
, CAFilter.CreatedDate
, AssignedTo.UserName As AssignedTo
, OwnedBy.UserName AS Owner
, CAFilter.Type
, CAFilter.ProcedureType
, CAFilter.Result
, CAFilter.ResultDate
, CAFilter.ExternalId AS SourceId
, CAFilter.ExternalName AS SourceName
, CAFilter.ExternalVersion AS SourceVersion
, CAFilter.LastModifiedDateTime AS ModifiedDate
, Priority.PriorityValue
, Level.LevelValue
, CreatedBy.UserName AS CreatedBy
, ModifiedBy.UserName AS ModifiedBy
FROM (SELECT DISTINCT CA.*
FROM (SELECT CA.ControlActivityDimKey
, CA.Id
, CA.Title as Title
, CS.CAStatusValue
, CA.CreatedDate
, 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
, CAR.MERResultValue AS Result
, CA.ResultDate
, CA.ExternalId
, CSN.ComplianceSourceNameValue AS ExternalName
, CA.ExternalVersion
, CASD.LastModifiedDateTime
, CAList.ControlObjectiveDimKey As ControlObjectiveDimKey
, CA.ControlActivityPriority_PriorityId
, CA.Level_LevelId
, CA.EntityDimKey
FROM ControlActivityStatusDurationFactvw CASD
INNER JOIN ControlActivityDimvw CA
ON CA.ControlActivityDimKey = CASD.ControlActivityDimKey
INNER JOIN CAStatusvw CS
ON CS.CAStatusId = CASD.CAStatusId
INNER JOIN #CAList CAList
ON CAList.ControlActivityDimKey = CA.ControlActivityDimKey
LEFT JOIN MERResultvw CAR
ON CAR.MERResultId = CA.Result_MERResultId
LEFT JOIN CATypevw CAT
ON CAT.CATypeId = CA.Type_CATypeId
LEFT JOIN ComplianceSourceNamevw CSN
ON CSN.ComplianceSourceNameId = CA.ExternalName_ComplianceSourceNameId
WHERE (-1 = @StatusId OR CS.CAStatusId = @StatusId)
AND ((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
WHERE ((-1 IN (Select value from @tableADCategory )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategory)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)) 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
LEFT JOIN Priority Priority
ON Priority.PriorityId = CAFilter.ControlActivityPriority_PriorityId
LEFT JOIN Level Level
ON Level.LevelId = CAFilter.Level_LevelId
LEFT JOIN ControlActivityCreatedByUserFactvw CACU
ON CACU.ControlActivityDimKey = CAFilter.ControlActivityDimKey
AND CACU.DeletedDate IS NULL
LEFT JOIN UserDimvw CreatedBy
ON CreatedBy.UserDimKey = CACU.ControlActivityCreatedBy_UserDimKey
LEFT JOIN ControlActivityModifiedByUserFactvw CAMU
ON CAMU.ControlActivityDimKey = CAFilter.ControlActivityDimKey
AND CAMU.DeletedDate IS NULL
LEFT JOIN UserDimvw ModifiedBy
ON ModifiedBy.UserDimKey = CAMU.ControlActivityModifiedBy_UserDimKey
WHERE (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
AND (0 = @AssignedTo OR AssignedTo.UserDimKey = @AssignedTo)
UNION
SELECT DISTINCT CAFilter.ControlActivityDimKey
, CAFilter.Id
, CAFilter.Title
, CAFilter.CAStatusValue
, CAFilter.CreatedDate
, AssignedTo.UserName As AssignedTo
, OwnedBy.UserName AS Owner
, CAFilter.Type
, CAFilter.ProcedureType
, CAFilter.Result
, CAFilter.ResultDate
, CAFilter.ExternalId AS SourceId
, CAFilter.ExternalName AS SourceName
, CAFilter.ExternalVersion AS SourceVersion
, CAFilter.LastModifiedDateTime AS ModifiedDate
, Priority.PriorityValue
, Level.LevelValue
, CreatedBy.UserName AS CreatedBy
, ModifiedBy.UserName AS ModifiedBy
FROM (SELECT DISTINCT CA.*
FROM (SELECT CA.ControlActivityDimKey
, CA.Id
, CA.Title as Title
, CS.CAStatusValue
, CA.CreatedDate
, 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
, CAR.MERResultValue AS Result
, CA.ResultDate
, CA.ExternalId
, CSN.ComplianceSourceNameValue AS ExternalName
, CA.ExternalVersion
, CASD.LastModifiedDateTime
, PCA.ControlObjectiveDimKey As ControlObjectiveDimKey
, CA.ControlActivityPriority_PriorityId
, CA.Level_LevelId
, CA.EntityDimKey
FROM ControlActivityStatusDurationFactvw CASD
INNER JOIN ControlActivityDimvw CA
ON CA.ControlActivityDimKey = CASD.ControlActivityDimKey
INNER JOIN #PCAList PCA
ON PCA.ControlActivityDimKey = CA.ControlActivityDimKey
INNER JOIN CAStatusvw CS
ON CS.CAStatusId = CASD.CAStatusId
LEFT JOIN MERResultvw CAR
ON CAR.MERResultId = CA.Result_MERResultId
LEFT JOIN CATypevw CAT
ON CAT.CATypeId = CA.Type_CATypeId
LEFT JOIN ComplianceSourceNamevw CSN
ON CSN.ComplianceSourceNameId = CA.ExternalName_ComplianceSourceNameId
WHERE (-1 = @StatusId OR CS.CAStatusId = @StatusId)
AND ((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
WHERE ((-1 IN (Select value from @tableADCategory )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategory)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)) 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
LEFT JOIN Priority Priority
ON Priority.PriorityId = CAFilter.ControlActivityPriority_PriorityId
LEFT JOIN Level Level
ON Level.LevelId = CAFilter.Level_LevelId
LEFT JOIN ControlActivityCreatedByUserFactvw CACU
ON CACU.ControlActivityDimKey = CAFilter.ControlActivityDimKey
AND CACU.DeletedDate IS NULL
LEFT JOIN UserDimvw CreatedBy
ON CreatedBy.UserDimKey = CACU.ControlActivityCreatedBy_UserDimKey
LEFT JOIN ControlActivityModifiedByUserFactvw CAMU
ON CAMU.ControlActivityDimKey = CAFilter.ControlActivityDimKey
AND CAMU.DeletedDate IS NULL
LEFT JOIN UserDimvw ModifiedBy
ON ModifiedBy.UserDimKey = CAMU.ControlActivityModifiedBy_UserDimKey
WHERE (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
AND (0 = @AssignedTo OR AssignedTo.UserDimKey = @AssignedTo)
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCAs TO reportuser
GO
Home