/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramReadinessStatus] Script Date: 08/10/2010 04:03:11 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramReadinessStatus]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramReadinessStatus]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramReadinessStatus] Script Date: 08/10/2010 04:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: 03-31-2010
-- Description: To get the Control Assertion List
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramReadinessStatus]
-- Add the parameters for the stored procedure here
@StatusId nvarchar(max) = '-1',
@StartDate DATETIME,
@EndDate DATETIME,
@ProgramId INT = null,
@ADCategoryId nvarchar(max) = '-1',
@ADValue INT = null,
@AssignedTo INT = null,
@OwnedBy INT = null,
@CategoryId INT = null,
@TypeId nvarchar(max) = '-1',
@LevelId nvarchar(max) = '-1',
@PriorityId nvarchar(max) = '-1',
@ReviewStartDate DATETIME,
@ReviewEndDate DATETIME,
@ReviewStageId 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..#CategoryPath', N'U') IS NOT NULL
BEGIN
DROP TABLE #CategoryPath
END;
IF OBJECT_ID(N'tempdb..#ActivityStatusDurationList', N'U') IS NOT NULL
BEGIN
DROP TABLE #ActivityStatusDurationList
END;
CREATE TABLE #ActivityStatusDurationList
(ActivityDimKey INT
,ActivityStatusId INT
,MaxStatusDate DATETIME)
END;
WITH CATDataNoHis (ProgramDimKey, CategoryDimKey)
AS
(
SELECT ProgramDimKey, ProgramHasCategory_CategoryDimKey AS CategoryDimKey
FROM ProgramHasCategoriesFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND (0 = @CategoryId OR ProgramHasCategory_CategoryDimKey = @CategoryId)
AND DeletedDate IS NULL
UNION ALL
SELECT ProgramDimKey, CategoryContainsCategory_CategoryDimKey as CategoryDimKey
FROM dbo.CategoryContainsCategoryFactvw AS CC
INNER JOIN CATDataNoHis AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
WHERE CC.DeletedDate IS NULL
)
SELECT ProgramDimKey, CategoryDimKey into #CATNoHis FROM CATDataNoHis;
WITH CODimKeyNOHis (CategoryDimKey, ControlObjectiveDimKey)
AS
(
SELECT A.CategoryDimKey, A.CategoryContainsControlObjective_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM CategoryHasCOFactvw A
INNER JOIN #CATNoHis 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 @tableProgramReviewADCategory ) OR AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableProgramReviewADCategory ) 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 CODimKeyNOHis AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
WHERE CC.DeletedDate IS NULL
)
SELECT CategoryDimKey, ControlObjectiveDimKey into #CODimKeyNoHis FROM CODimKeyNOHis;
WITH PHasCONoHis (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 @tableProgramReviewADCategory ) OR AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableProgramReviewADCategory ) 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 PHasCONoHis AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
WHERE CC.DeletedDate IS NULL
)
SELECT ProgramDimKey, ControlObjectiveDimKey into #PHasCONoHis FROM PHasCONoHis;
INSERT INTO #ProgramCOList
SELECT DISTINCT ControlObjectiveDimKey FROM #CODimKeyNoHis
UNION
SELECT DISTINCT ControlObjectiveDimKey FROM #PHasCONoHis WHERE (0=@CategoryId)
;WITH CategoryPath (CategoryDimKey, TitlePath)
AS
(
SELECT DISTINCT ProgramHasCategory_CategoryDimKey AS CategoryDimKey
,CAST(C.DisplayName AS VARCHAR(MAX)) AS TitlePath
FROM ProgramHasCategoriesFactvw
INNER JOIN Categorydimvw C
ON ProgramHasCategory_CategoryDimKey = C.CategoryDimKey
WHERE C.IsDeleted = 0 AND DeletedDate IS NULL
AND (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND (0=@CategoryId OR ProgramHasCategory_CategoryDimKey = @CategoryId)
UNION ALL
SELECT CC.CategoryContainsCategory_CategoryDimKey AS CategoryDimKey
,CP.TitlePath + '; ' + CAST(C.DisplayName AS VARCHAR(MAX)) AS TitlePath
FROM CategoryContainsCategoryFactvw AS CC
INNER JOIN CategoryPath AS CP
ON CC.CategoryDimKey = CP.CategoryDimKey
INNER JOIN Categorydimvw C
ON CC.CategoryContainsCategory_CategoryDimKey = C.CategoryDimKey
WHERE CC.DeletedDate IS NULL AND CC.DeletedDate IS NULL
)
SELECT CategoryDimKey, TitlePath into #CategoryPath FROM CategoryPath;
INSERT INTO #ActivityStatusDurationList
(ActivityDimKey
,ActivityStatusId
,MaxStatusDate)
SELECT CCM.ControlObjectiveManualActivity_ActivityDimKey
, NULL AS ActivityStatusId
, MAX(ASDF.StartDateTime) AS MaxStatusDate
FROM ActivityStatusDurationFactvw ASDF
RIGHT JOIN ControlObjectiveContainsManualActivityFactvw CCM
ON CCM.ControlObjectiveManualActivity_ActivityDimKey = ASDF.ActivityDimKey
INNER JOIN #ProgramCOList COList
ON COList.ControlObjectiveDimKey = CCM.ControlObjectiveDimKey
WHERE
((DATEDIFF(DAY, ASDF.StartDateTime, @ReviewStartDate) <= 0
AND DATEDIFF(DAY, ASDF.StartDateTime, @ReviewEndDate) >=0)
OR (ASDF.FinishDateTime IS NOT NULL AND DATEDIFF(DAY, ASDF.FinishDateTime, @ReviewStartDate) <= 0
AND DATEDIFF(DAY, ASDF.FinishDateTime, @ReviewEndDate) >=0)
OR (ASDF.StartDateTime is null))
AND CCM.DeletedDate IS NULL
GROUP BY CCM.ControlObjectiveManualActivity_ActivityDimKey
--Added to replace the calls to MER Count Function
UPDATE TempResultPRS
SET ActivityStatusId = ASDF.ActivityStatusId
FROM #ActivityStatusDurationList TempResultPRS
JOIN ActivityStatusDurationFactvw ASDF
ON TempResultPRS.ActivityDimKey = ASDF.ActivityDimKey
AND TempResultPRS.MaxStatusDate = ASDF.StartDateTime
IF OBJECT_ID(N'tempdb..#MERList', N'U') IS NOT NULL
BEGIN
DROP TABLE #MERList
END;
IF OBJECT_ID(N'tempdb..#CAResultList', N'U') IS NOT NULL
BEGIN
DROP TABLE #CAResultList
END;
IF OBJECT_ID(N'tempdb..#COCountList', N'U') IS NOT NULL
BEGIN
DROP TABLE #COCountList
END;
INSERT INTO #MERList
EXEC dbo.[ServiceManager_Report_GRC_ControlManagement_SP_GetMerResultCount] @ProgramId, @StartDate, @EndDate
INSERT INTO #CAResultList
SELECT RC.ControlActivityDimKey,
dbo.ufnGetMerLastResult (@ProgramId,RC.ControlActivityDimKey,RC.[CompliantResult],RC.[NonCompliantResult], RC.[ErrorResult], RC.[UnknownResult])
FROM #MERList RC
JOIN COHasCAFactvw CCFV ON RC.ControlActivityDimKey = CCFV.ControlObjectiveContainsControlActivity_ControlActivityDimKey
JOIN #ProgramCOList PCL ON PCL.ControlObjectiveDimKey = CCFV.ControlObjectiveDimKey
UNION
SELECT CCFV.ControlObjectiveContainsControlActivity_ControlActivityDimKey,
dbo.ufnGetManualCALastResult(MCADim.ManualControlActivityDimKey,@StartDate, @EndDate)
FROM COHasCAFactvw CCFV
JOIN #ProgramCOList PCL ON PCL.ControlObjectiveDimKey = CCFV.ControlObjectiveDimKey
JOIN ControlActivityDim CAD ON CAD.ControlActivityDimKey = CCFV.ControlObjectiveContainsControlActivity_ControlActivityDimKey
JOIN ManualControlActivityDim MCADim on CAD.BaseManagedEntityId = MCADim.BaseManagedEntityId
SELECT CC.ControlObjectiveDimKey,
SUM(CASE WHEN CARL.CAResult = 'Non-Compliant' THEN 1 END) AS CACountFailExp,
SUM(CASE WHEN CARL.CAResult = 'Compliant' THEN 1 END) AS CACountMeetExp,
SUM(CASE WHEN CARL.CAResult = 'Unknown' OR CARL.CAResult = 'Error' OR CARL.CAResult IS NULL THEN 1 END) AS CACountUnknown
INTO #COCountList
FROM COHasCAFactvw CC
JOIN #ProgramCOList PCL ON PCL.ControlObjectiveDimKey = CC.ControlObjectiveDimKey
LEFT JOIN #CAResultList CARL ON (CARL.ControlActivityDimKey = CC.ControlObjectiveContainsControlActivity_ControlActivityDimKey)
GROUP BY CC.ControlObjectiveDimKey
-- End of Add
--Query to get Prog Display Name
DECLARE @ProgDispKey varchar(max)
SET @ProgDispKey = (SELECT DisplayName FROM EntityDim WHERE EntityDimKey =
(SELECT EntityDimKey FROM ProgramDim WHERE ProgramDimKey = @ProgramId))
SET @ProgDispKey= LEFT(@ProgDispKey,CharIndex(' ' ,@ProgDispKey)-1)
SET @ProgDispKey = @ProgDispKey + '_%'
SELECT DISTINCT COFilter.*
,ADV.Id AS WorkItemId
,WorkItemOwner.UserName AS WorkItemOwnedBy
,ASV.ActivityStageValue AS WorkItemStage
,AStatus.ActivityStatusValue AS WorkItemStatus
,ADV.ActualStartDate AS WorkItemStartDate
,ADV.ActualEndDate AS WorkItemCompletiondate
,ADV.Notes AS WorkItemNotes
,AssignedTo.UserName As AssignedTo
,OwnedBy.UserName As Owner
,OwnedBy.UserName AS CreatedBy
,COType.COTypeValue
,Priority.PriorityValue
,Level.LevelValue
,(SELECT STUFF (( SELECT ',' + CP.TitlePath FROM CategoryHasCOFactvw CC JOIN #CategoryPath CP
ON CP.CategoryDimKey = CC.CategoryDimKey
WHERE CC.CategoryContainsControlObjective_ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
AND CC.DeletedDate IS NULL FOR XML PATH('')),1,1,'')) AS [Category]
,ISNULL(RC.CACountMeetExp,0) AS [CACountMeetExp]
,ISNULL(RC.CACountFailExp,0) AS [CACountFailExp]
,ISNULL(RC.CACountUnknown,0) AS [CACountUnknown]
,(SELECT sum(A.COCount) FROM
(SELECT COUNT(1) AS COCount
FROM COHasCOFactvw
WHERE ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
UNION
SELECT COUNT(1) AS COCount
FROM COHasCOFactvw
WHERE PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey) A)AS [RelatedCOCount]
,(SELECT COUNT(ControlObjectiveContainsControlActivity_ControlActivityDimKey)
FROM COHasCAFactvw WHERE ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey) AS [RelatedCAcount]
,(SELECT COUNT(PolicyItemRelatesToCompliancePolicyItem_RiskDimKey)
FROM ControlObjectiveRelatesToRiskFactvw
WHERE ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey) AS [RelatedRiskCount]
,(SELECT COUNT(RiskDimKey)
FROM RiskRelatesToControlObjectiveFactvw
WHERE PolicyItemRelatesToCompliancePolicyItem_ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey) AS [RelatedCORiskCount]
FROM (SELECT DISTINCT
CO.ControlObjectiveDimKey,
CO.Id,
CO.Title,
CO.ObjectStatus,
CO.COStatusValue,
CO.CreatedDate,
CO.SourceId,
SourceName,
SourceVersion,
CO.Type_COTypeId AS TypeId,
CO.Priority_PriorityId AS PriorityId,
CO.Level_LevelId AS LevelId,
CO.EntityDimKey,
(Select MAX(LastModifiedDateTime)
From ControlObjectiveStatusDurationFactVw
Where ControlObjectiveDimKey = CO.ControlObjectiveDimKey) AS ModifiedOn
FROM
(SELECT DISTINCT
CO.ControlObjectiveDimKey,
CO.Id,
CO.Title as Title,
CO.ObjectStatus,
CS.COStatusId,
CS.COStatusValue,
CO.CreatedDate,
CO.ExternalId AS SourceId,
CSN.ComplianceSourceNameValue AS SourceName,
CO.ExternalVersion AS SourceVersion,
CO.Type_COTypeId,
CO.Priority_PriorityId,
CO.Level_LevelId,
CO.EntityDimKey
FROM ControlObjectiveDimvw CO
INNER JOIN COStatusvw CS
ON CS.COStatusId = CO.Status_COStatusId
INNER JOIN #ProgramCOList MatchingCOs
ON MatchingCOs.ControlObjectiveDimKey = CO.ControlObjectiveDimKey
LEFT JOIN ComplianceSourceNamevw CSN
ON CSN.ComplianceSourceNameId = CO.ExternalName_ComplianceSourceNameId
WHERE CO.IsDeleted = 0) AS CO
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = CO.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 (-1 IN (Select value from @tableProgramReviewStatus ) OR CO.COStatusId IN (Select value from @tableProgramReviewStatus ))
AND ((DATEDIFF(DAY, CO.CreatedDate, @StartDate) <= 0 AND DATEDIFF(DAY, CO.CreatedDate, @EndDate) >=0))
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND (-1 IN (Select value from @tableProgramReviewADCategory ) OR AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableProgramReviewADCategory ) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)) AS COFilter
LEFT JOIN ControlObjectiveAssignedToUserFactvw COA
ON COA.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
AND COA.DeletedDate IS NULL
LEFT JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = COA.PolicyItemAssignedTo_UserDimKey
AND AssignedTo.IsDeleted = 0
LEFT JOIN ControlObjectiveOwnedByUserFactvw COO
ON COO.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
AND COO.DeletedDate IS NULL
LEFT JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = COO.ConfigItemOwnedByUser_UserDimKey
AND OwnedBy.IsDeleted = 0
LEFT JOIN COType COType
ON COType.COTypeId = COFilter.TypeId
LEFT JOIN Priority Priority
ON Priority.PriorityId = COFilter.PriorityId
LEFT JOIN Level Level
ON Level.LevelId = COFilter.LevelId
LEFT JOIN ControlObjectiveContainsManualActivityFactvw COMA
ON COMA.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
AND COMA.DeletedDate IS NULL
LEFT JOIN #ActivityStatusDurationList ASDL
ON ASDL.ActivityDimKey = COMA.ControlObjectiveManualActivity_ActivityDimKey
LEFT JOIN ActivityDimvw ADV
ON ADV.ActivityDimKey = ASDL.ActivityDimKey
AND ADV.IsDeleted = 0
LEFT JOIN WorkItemDimvw WD
ON ADV.EntityDimKey = WD.EntityDimKey
AND WD.IsDeleted = 0
LEFT JOIN WorkItemAssignedToUserFactvw WAU
ON WAU.WorkItemDimKey = WD.WorkItemDimKey
AND WAU.DeletedDate IS NULL
LEFT JOIN UserDimvw WorkItemOwner
ON WAU.WorkItemAssignedToUser_UserDimKey = WorkItemOwner.UserDimKey
AND WorkItemOwner.IsDeleted = 0
LEFT JOIN ActivityStagevw ASV
ON ASV.ActivityStageId = ADV.Stage_ActivityStageId
LEFT JOIN ActivityStatusvw AStatus
ON AStatus.ActivityStatusId = ASDL.ActivityStatusId
LEFT JOIN #COCountList RC
ON RC.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
WHERE (0 = @AssignedTo OR COA.PolicyItemAssignedTo_UserDimKey = @AssignedTo)
AND (0 = @OwnedBy OR COO.ConfigItemOwnedByUser_UserDimKey = @OwnedBy)
AND (ADV.Id like @ProgDispKey)
AND (-1 IN (Select value from @tableProgramReviewType ) OR COType.COTypeId IN (Select value from @tableProgramReviewType ))
AND (-1 IN (Select value from @tableProgramReviewPriority ) OR Priority.PriorityId IN (Select value from @tableProgramReviewPriority ))
AND (-1 IN (Select value from @tableProgramReviewLevel ) OR Level.LevelId IN (Select value from @tableProgramReviewLevel ))
AND (-1 IN (Select value from @tableProgramReviewStage ) OR ADV.Stage_ActivityStageId IN (Select value from @tableProgramReviewStage ))
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetProgramReadinessStatus TO reportuser
GO
Home