/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCurrentCAs] Script Date: 08/10/2010 01:51:38 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCurrentCAs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCurrentCAs]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCurrentCAs] Script Date: 08/10/2010 01:51:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Milind Mahajan
-- Create date: 10-25-2009
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCurrentCAs]
-- Add the parameters for the stored procedure here
@StatusId nvarchar(max) = '-1',
@StartDate DATETIME,
@EndDate DATETIME,
@ProgramId INT = null,
@ADValue INT = null,
@AssignedTo INT = null,
@OwnedBy INT = null,
@ADCategoryId NVARCHAR(MAX) = '-1',
@ResultStartDate DATETIME,
@ResultEndDate DATETIME,
@TypeId NVARCHAR(MAX) = '-1',
@LevelId NVARCHAR(MAX) = '-1',
@PriorityId NVARCHAR(MAX) = '-1',
@LastResult NVARCHAR(MAX) = '-1',
@ControlObjectiveDimKey INT = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
WITH CATDataCurrent (ProgramDimKey, CategoryDimKey)
AS
(
SELECT ProgramDimKey, ProgramHasCategory_CategoryDimKey AS CategoryDimKey
FROM ProgramHasCategoriesFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND DeletedDate IS NULL
UNION ALL
SELECT ProgramDimKey, CategoryContainsCategory_CategoryDimKey as CategoryDimKey
FROM dbo.CategoryContainsCategoryFactvw AS CC
INNER JOIN CATDataCurrent AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
WHERE CC.DeletedDate IS NULL
)
SELECT ProgramDimKey, CategoryDimKey into #CATCurrent FROM CATDataCurrent;
WITH CODimKeyCurrent (CategoryDimKey, ControlObjectiveDimKey)
AS
(
SELECT A.CategoryDimKey, A.CategoryContainsControlObjective_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM CategoryHasCOFactvw A
INNER JOIN #CATCurrent B
ON A.CategoryDimKey = B.CategoryDimKey
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = A.CategoryContainsControlObjective_ControlObjectiveDimKey
AND COC.DeletedDate IS NULL
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
AND ADHC.DeletedDate IS NULL
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
AND IsDeleted = 0
WHERE
(0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND (-1 IN (Select value from @tableCurrentCA_ADCategory) OR AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableCurrentCA_ADCategory) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
AND A.DeletedDate IS NULL
UNION ALL
SELECT CategoryDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactvw AS CC
INNER JOIN CODimKeyCurrent AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
WHERE CC.DeletedDate IS NULL
)
SELECT CategoryDimKey, ControlObjectiveDimKey into #CODimKeyCurrent FROM CODimKeyCurrent;
WITH CACurrent (ControlObjectiveDimKey, ControlActivityDimKey)
AS
(
SELECT A.ControlObjectiveDimKey, B.ControlObjectiveContainsControlActivity_ControlActivityDimKey AS ControlActivityDimKey
FROM #CODimKeyCurrent A
INNER JOIN COHasCAFactvw B
ON A.ControlObjectiveDimKey = B.ControlObjectiveDimKey
WHERE B.DeletedDate IS NULL
UNION ALL
SELECT ControlObjectiveDimKey, CC.ControlActivityContainsControlActivity_ControlActivityDimKey as ControlActivityDimKey
FROM CAHasCAFactvw AS CC
INNER JOIN CACurrent AS CD
ON CC.ControlActivityDimKey = CD.ControlActivityDimKey
WHERE CC.DeletedDate IS NULL
)
SELECT ControlObjectiveDimKey, ControlActivityDimKey into #CAListCurrent FROM CACurrent;
WITH PHasCOCurrent (ProgramDimKey, ControlObjectiveDimKey)
AS
(
SELECT ProgramDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM ProgramHasCOFactvw
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey
AND COC.DeletedDate IS NULL
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
AND ADHC.DeletedDate IS NULL
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
AND AD.IsDeleted = 0
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND (-1 IN (Select value from @tableCurrentCA_ADCategory) OR AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableCurrentCA_ADCategory) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
AND ProgramHasCOFactvw.DeletedDate IS NULL
UNION ALL
SELECT ProgramDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactvw AS CC
INNER JOIN PHasCOCurrent AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
WHERE CC.DeletedDate IS NULL
)
SELECT ProgramDimKey, ControlObjectiveDimKey into #PHasCOCurrent FROM PHasCOCurrent;
WITH PCACurrent (ControlObjectiveDimKey, ControlActivityDimKey)
AS
(
SELECT A.ControlObjectiveDimKey, B.ControlObjectiveContainsControlActivity_ControlActivityDimKey
FROM #PHasCOCurrent A
INNER JOIN dbo.COHasCAFactvw B
ON A.ControlObjectiveDimKey = B.ControlObjectiveDimKey
WHERE B.DeletedDate IS NULL
UNION ALL
SELECT ControlObjectiveDimKey, CC.ControlActivityContainsControlActivity_ControlActivityDimKey as ControlActivityDimKey
FROM CAHasCAFactvw AS CC
INNER JOIN PCACurrent AS CD
ON CC.ControlActivityDimKey = CD.ControlActivityDimKey
WHERE CC.DeletedDate IS NULL
)
SELECT ControlObjectiveDimKey, ControlActivityDimKey into #PCAListCurrent FROM PCACurrent;
INSERT INTO #CAList
SELECT DISTINCT ControlObjectiveDimKey, ControlActivityDimKey FROM #CAListCurrent
UNION
SELECT DISTINCT ControlObjectiveDimKey, ControlActivityDimKey FROM #PCAListCurrent
INSERT INTO #MERList
EXEC dbo.[ServiceManager_Report_GRC_ControlManagement_SP_GetMerResultCount] @ProgramId, @ResultStartDate ,@ResultEndDate
SELECT DISTINCT CALastResultList.*
FROM
(SELECT DISTINCT CAFilter.ControlActivityDimKey
, CAFilter.Id
, CAFilter.Title
, CAFilter.CAStatusValue
, CAFilter.CreatedDate
, AssignedTo.UserName As AssignedTo
, OwnedBy.UserName AS Owner
, CAFilter.Type
, CAFilter.ProcedureType
, (CASE
WHEN EXISTS (SELECT MT.TypeName
FROM ManagedTypeDimvw MT
INNER JOIN EntityManagedTypeFactvw EMT
ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey
AND CAFilter.EntityDimKey = EMT.EntityDimKey
WHERE MT.TypeName = 'System.Compliance.ManualControlActivity')
THEN
(SELECT dbo.ufnGetManualCALastResult(
(Select TOP 1 ManualControlActivityDimKey from ManualControlActivityDim Where BaseManagedEntityId = CAFilter.BaseManagedEntityId), @ResultStartDate, @ResultEndDate))
ELSE
(SELECT dbo.ufnGetMerLastResult (@ProgramId
,CAFilter.ControlActivityDimKey
,ISNULL(RC.[CompliantResult],0)
,ISNULL(RC.[NonCompliantResult],0)
,ISNULL(RC.[ErrorResult],0)
,ISNULL(RC.[UnknownResult],0))
) END)AS Result
, CAFilter.ResultDate
, CAFilter.ExternalId AS SourceId
, CAFilter.ExternalName AS SourceName
, CAFilter.ExternalVersion AS SourceVersion
, CreatedBy.UserName AS CreatedBy
, ModifiedBy.UserName AS ModifiedBy
, Priority.PriorityValue
, Level.LevelValue
, (Select MAX(LastModifiedDateTime)
From ControlActivityStatusDurationFactVw
Where ControlActivityDimKey = CAFilter.ControlActivityDimKey) AS ModifiedOn
, (CASE
WHEN EXISTS (SELECT MT.TypeName
FROM ManagedTypeDimvw MT
INNER JOIN EntityManagedTypeFactvw EMT
ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey
AND CAFilter.EntityDimKey = EMT.EntityDimKey
WHERE MT.TypeName = 'System.Compliance.ManualControlActivity')
THEN -1 ELSE ISNULL(RC.[CompliantResult],0) END) AS CompliantResultCount
, (CASE
WHEN EXISTS (SELECT MT.TypeName
FROM ManagedTypeDimvw MT
INNER JOIN EntityManagedTypeFactvw EMT
ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey
AND CAFilter.EntityDimKey = EMT.EntityDimKey
WHERE MT.TypeName = 'System.Compliance.ManualControlActivity')
THEN -1 ELSE ISNULL(RC.[NonCompliantResult],0) END) AS NonCompliantResultCount
, (CASE
WHEN EXISTS (SELECT MT.TypeName
FROM ManagedTypeDimvw MT
INNER JOIN EntityManagedTypeFactvw EMT
ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey
AND CAFilter.EntityDimKey = EMT.EntityDimKey
WHERE MT.TypeName = 'System.Compliance.ManualControlActivity')
THEN -1 ELSE ISNULL(RC.[ErrorResult],0) END) AS ErrorResultCount
, (CASE
WHEN EXISTS (SELECT MT.TypeName
FROM ManagedTypeDimvw MT
INNER JOIN EntityManagedTypeFactvw EMT
ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey
AND CAFilter.EntityDimKey = EMT.EntityDimKey
WHERE MT.TypeName = 'System.Compliance.ManualControlActivity')
THEN -1 ELSE ISNULL(RC.[UnknownResult],0) END) AS UnknownResultcount
FROM (SELECT DISTINCT CA.* FROM
(SELECT CA.ControlActivityDimKey,
CA.Id,
CA.BaseManagedEntityId,
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,
CAListCurrent.ControlObjectiveDimKey AS ControlObjectiveDimKey,
CA.ControlActivityPriority_PriorityId AS PriorityId,
CA.Level_LevelId AS LevelId,
CA.EntityDimKey
FROM ControlActivityDimvw CA
INNER JOIN CAStatusvw CS
ON CS.CAStatusId = CA.ControlActivityStatus_CAStatusId
INNER JOIN #CAList CAListCurrent
ON CAListCurrent.ControlActivityDimKey = CA.ControlActivityDimKey
LEFT OUTER JOIN MERResultvw CAR
ON CAR.MERResultId = CA.Result_MERResultId
LEFT OUTER JOIN CATypevw CAT
ON CAT.CATypeId = CA.Type_CATypeId
LEFT OUTER JOIN ComplianceSourceNamevw CSN
ON CSN.ComplianceSourceNameId = CA.ExternalName_ComplianceSourceNameId
WHERE (-1 IN (Select value from @tableCurrentCAStatus) OR CS.CAStatusId IN (Select value from @tableCurrentCAStatus))
AND ((DATEDIFF(DAY, CA.CreatedDate, @StartDate) <= 0 AND DATEDIFF(DAY, CA.CreatedDate, @EndDate) >=0))
AND CA.IsDeleted = 0
AND (0 = @ControlObjectiveDimKey OR CAListCurrent.ControlObjectiveDimKey = @ControlObjectiveDimKey)) AS CA
LEFT OUTER JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = CA.ControlObjectiveDimKey
AND COC.DeletedDate IS NULL
LEFT OUTER JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
AND ADHC.DeletedDate IS NULL
LEFT OUTER JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
AND AD.IsDeleted = 0
WHERE (-1 IN (Select value from @tableCurrentCA_ADCategory) OR AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableCurrentCA_ADCategory) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)) AS CAFilter
LEFT OUTER JOIN ControlActivityOwnedByUserFactvw CAOW
ON CAOW.ControlActivityDimKey = CAFilter.ControlActivityDimKey
AND CAOW.DeletedDate IS NULL
LEFT OUTER JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = CAOW.ControlActivityOwnedBy_UserDimKey
AND OwnedBy.IsDeleted = 0
LEFT OUTER JOIN ControlActivityAssignedToUserFactvw CAA
ON CAA.ControlActivityDimKey = CAFilter.ControlActivityDimKey
AND CAA.DeletedDate IS NULL
LEFT OUTER JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = CAA.WorkItemAssignedToUser_UserDimKey
AND AssignedTo.IsDeleted = 0
LEFT OUTER JOIN Priority Priority
ON Priority.PriorityId = CAFilter.PriorityId
LEFT OUTER JOIN Level Level
ON Level.LevelId = CAFilter.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
LEFT JOIN #MERList RC
ON RC.ControlActivityDimKey = CAFilter.ControlActivityDimKey
WHERE (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
AND (0 = @AssignedTo OR AssignedTo.UserDimKey = @AssignedTo)
AND (-1 IN (Select value from @tableCurrentCAPriority) OR Priority.PriorityId IN (Select value from @tableCurrentCAPriority))
AND (-1 IN (Select value from @tableCurrentCALevel) OR Level.LevelId IN (Select value from @tableCurrentCALevel))) AS CALastResultList
WHERE ('-1' IN (Select value from @tableCurrentCALastResult) OR (SELECT MERREsultID from MERResultvw WHERE MERResultValue = CALastResultList.Result) IN (Select value from @tableCurrentCALastResult))
AND ('-1' IN (Select value from @tableCurrentCAType) OR (SELECT MERResultTypeId FROM MERResultTypevw WHERE MERResultTypeValue = CALastResultList.ProcedureType) IN (Select value from @tableCurrentCAType))
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCurrentCAs TO reportuser
GO
Home