/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_ProgramRiskList] Script Date: 12/22/2010 03:14:01 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_RiskManagement_SP_ProgramRiskList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_ProgramRiskList]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_ProgramRiskList] Script Date: 12/22/2010 03:14:01 ******/
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_RiskManagement_SP_ProgramRiskList]
-- Add the parameters for the stored procedure here
-- Compliance Program to scope the Risks that are contained in the Program
@ProgramId int, -- Required
@StartDate datetime,
@EndDate datetime,
@OwnedBy int=null,
@RiskRankId nvarchar(max) = '-1',
@InherentRisk int,
@RiskResponse nvarchar(max) = '-1',
@StatusId 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 @ExecError int
--defining temp table for Status
DECLARE @tableRiskStatus TABLE (value nvarchar(256))
INSERT @tableRiskStatus (value)
SELECT * FROM dbo.fn_CSVToTableInt(@StatusId)
--defining temp table for RiskRankId
DECLARE @tableRiskRank TABLE (value nvarchar(256))
INSERT @tableRiskRank (value)
SELECT * FROM dbo.fn_CSVToTableInt(@RiskRankId)
--defining temp table for Risk Response
DECLARE @tableRiskResponse TABLE (value nvarchar(256))
INSERT @tableRiskResponse (value)
SELECT * FROM dbo.fn_CSVToTableInt(@RiskResponse)
BEGIN
IF OBJECT_ID(N'tempdb..#CATCurrent', N'U') IS NOT NULL
BEGIN
DROP TABLE #CATCurrent
END
IF OBJECT_ID(N'tempdb..#CODimKeyCurrent', N'U') IS NOT NULL
BEGIN
DROP TABLE #CODimKeyCurrent
END
IF OBJECT_ID(N'tempdb..#CAListCurrent', N'U') IS NOT NULL
BEGIN
DROP TABLE #CAListCurrent
END
IF OBJECT_ID(N'tempdb..#PHasCOCurrent', N'U') IS NOT NULL
BEGIN
DROP TABLE #PHasCOCurrent
END
IF OBJECT_ID(N'tempdb..#PCAListCurrent', N'U') IS NOT NULL
BEGIN
DROP TABLE #PCAListCurrent
END
IF OBJECT_ID(N'tempdb..#PCAALLList', N'U') IS NOT NULL
BEGIN
DROP TABLE #PCAALLList
END;
IF OBJECT_ID(N'tempdb..#RiskCATData', N'U') IS NOT NULL
BEGIN
DROP TABLE #RiskCATData
END
IF OBJECT_ID(N'tempdb..#RiskDimKey', N'U') IS NOT NULL
BEGIN
DROP TABLE #RiskDimKey
END;
IF OBJECT_ID(N'tempdb..#PHasRisk', N'U') IS NOT NULL
BEGIN
DROP TABLE #PHasRisk
END;
IF OBJECT_ID(N'tempdb..#PRiskALLList', N'U') IS NOT NULL
BEGIN
DROP TABLE #PRiskALLList
END;
IF OBJECT_ID(N'tempdb..#PCOALLList', N'U') IS NOT NULL
BEGIN
DROP TABLE #PCOALLList
END;
IF OBJECT_ID(N'tempdb..#ProgramRiskList', N'U') IS NOT NULL
BEGIN
DROP TABLE #ProgramRiskList
END;
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 DeletedDate IS NULL
)
SELECT ProgramDimKey, CategoryDimKey into #CATCurrent FROM CATDataCurrent;
WITH CODimKeyCurrent (ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey)
AS
(
SELECT B.ProgramDimKey
,A.CategoryDimKey
,A.CategoryContainsControlObjective_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM CategoryHasCOFactvw A
INNER JOIN #CATCurrent B
ON A.CategoryDimKey = B.CategoryDimKey
WHERE A.DeletedDate IS NULL
UNION ALL
SELECT CD.ProgramDimKey
,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 ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey into #CODimKeyCurrent FROM CODimKeyCurrent;
WITH CACurrent (ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey, ControlActivityDimKey)
AS
(
SELECT A.ProgramDimKey
,A.CategoryDimKey
,A.ControlObjectiveDimKey
,ISNULL(B.ControlObjectiveContainsControlActivity_ControlActivityDimKey, 0) AS ControlActivityDimKey
FROM #CODimKeyCurrent A
LEFT JOIN COHasCAFactvw B
ON A.ControlObjectiveDimKey = B.ControlObjectiveDimKey
AND B.DeletedDate IS NULL
UNION ALL
SELECT CD.ProgramDimKey
,CD.CategoryDimKey
,CD.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 ProgramDimKey,CategoryDimKey,ControlObjectiveDimKey, ControlActivityDimKey into #CAListCurrent FROM CACurrent;
WITH PHasCOCurrent (ProgramDimKey, ControlObjectiveDimKey)
AS
(
SELECT PCO.ProgramDimKey
,PCO.PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM ProgramHasCOFactvw PCO
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND PCO.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;
INSERT INTO #PCOALLList
SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey FROM #CODimKeyCurrent
UNION
SELECT DISTINCT ProgramDimKey, ControlObjectiveDimKey FROM #PHasCOCurrent;
WITH PCACurrent (ProgramDimKey, ControlObjectiveDimKey, ControlActivityDimKey)
AS
(
SELECT A.ProgramDimKey
,A.ControlObjectiveDimKey
,ISNULL(B.ControlObjectiveContainsControlActivity_ControlActivityDimKey, 0)
FROM #PHasCOCurrent A
LEFT JOIN dbo.COHasCAFactvw B
ON A.ControlObjectiveDimKey = B.ControlObjectiveDimKey
AND B.DeletedDate IS NULL
UNION ALL
SELECT CD.ProgramDimKey
,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 ProgramDimKey, 0 AS CategoryDimKey, ControlObjectiveDimKey, ControlActivityDimKey into #PCAListCurrent FROM PCACurrent;
INSERT INTO #PCAALLList
SELECT DISTINCT ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey, ControlActivityDimKey FROM #CAListCurrent
UNION
SELECT DISTINCT ProgramDimKey, CategoryDimKey, ControlObjectiveDimKey, ControlActivityDimKey FROM #PCAListCurrent;
WITH RiskCATData (ProgramDimKey, CategoryDimKey)
AS
(
SELECT ProgramDimKey
,ProgramHasCategory_CategoryDimKey AS CategoryDimKey
FROM ProgramHasCategoriesFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND DeletedDate IS NULL
UNION ALL
SELECT CD.ProgramDimKey
,CC.CategoryContainsCategory_CategoryDimKey as CategoryDimKey
FROM CategoryContainsCategoryFactvw AS CC
INNER JOIN RiskCATData AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
AND DeletedDate IS NULL
)
SELECT ProgramDimKey, CategoryDimKey INTO #RiskCATData FROM RiskCATData;
WITH RiskDimKey (ProgramDimKey, CategoryDimKey, RiskDimKey)
AS
(
SELECT B.ProgramDimKey
,A.CategoryDimKey, A.CategoryContainsRisk_RiskDimKey AS RiskDimKey
FROM CategoryContainsRiskFactvw A
INNER JOIN #RiskCATData B
ON A.CategoryDimKey = B.CategoryDimKey
AND A.DeletedDate IS NULL
UNION ALL
SELECT RD.ProgramDimKey
,RD.CategoryDimKey
,RR.PolicyItemContainsCompliancePolicyItem_RiskDimKey AS RiskDimKey
FROM RiskContainsRiskFactvw AS RR
INNER JOIN RiskDimKey AS RD
ON RR.RiskDimKey = RD.RiskDimKey
WHERE RR.DeletedDate IS NULL
)
SELECT ProgramDimKey, CategoryDimKey, RiskDimKey into #RiskDimKey FROM RiskDimKey;
WITH PHasRisk (ProgramDimKey, RiskDimKey)
AS
(
SELECT ProgramDimKey, PolicyItemRelatesToCompliancePolicyItem_RiskDimKey AS RiskDimKey
FROM ProgramContainsRisksFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND DeletedDate IS NULL
UNION ALL
SELECT RD.ProgramDimKey, RR.PolicyItemContainsCompliancePolicyItem_RiskDimKey AS RiskDimKey
FROM RiskContainsRiskFactvw AS RR
INNER JOIN PHasRisk AS RD
ON RR.RiskDimKey = RD.RiskDimKey
WHERE RR.DeletedDate IS NULL
)
SELECT ProgramDimKey, 0 AS CategoryDimKey, RiskDimKey into #PHasRisk FROM PHasRisk;
INSERT INTO #PRiskALLList
SELECT DISTINCT ProgramDimKey, CategoryDimKey, RiskDimKey FROM #RiskDimKey
UNION
SELECT DISTINCT ProgramDimKey, CategoryDimKey, RiskDimKey FROM #PHasRisk
INSERT INTO #ProgramRiskList
SELECT DISTINCT PRISK.RiskDimKey, Null, Null, Null
FROM #PRiskALLList PRISK
UNION
SELECT DISTINCT CORISK.PolicyItemRelatesToCompliancePolicyItem_RiskDimKey,
COLIST.ControlObjectiveDimKey, CODIM.Id, MT.TypeName as ComplianceType
FROM dbo.ControlObjectiveRelatesToRiskFactvw CORISK
INNER JOIN #PCOALLList COLIST
ON COLIST.ControlObjectiveDimKey = CORISK.ControlObjectiveDimKey
JOIN ControlObjectiveDimvw CODIM
ON CODIM.ControlObjectiveDimKey = COLIST.ControlObjectiveDimKey
INNER JOIN EntityManagedTypeFactvw EMT
ON CODIM.EntityDimKey = EMT.EntityDimKey
JOIN ManagedTypeDimvw MT
ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey
UNION
SELECT DISTINCT RISKCO.RiskDimKey,
COLIST.ControlObjectiveDimKey, CODIM.Id, MT.TypeName as ComplianceType
FROM dbo.RiskRelatesToControlObjectiveFactvw RISKCO
INNER JOIN #PCOALLList COLIST
ON COLIST.ControlObjectiveDimKey = RISKCO.PolicyItemRelatesToCompliancePolicyItem_ControlObjectiveDimKey
JOIN ControlObjectiveDimvw CODIM
ON CODIM.ControlObjectiveDimKey = COLIST.ControlObjectiveDimKey
INNER JOIN EntityManagedTypeFactvw EMT
ON CODIM.EntityDimKey = EMT.EntityDimKey
JOIN ManagedTypeDimvw MT
ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey
UNION
SELECT DISTINCT CARISK.ControlActivityRelatesToRisk_RiskDimKey,
CALIST.ControlActivityDimKey, CADIM.Id, MT.TypeName as ComplianceType
FROM dbo.ControlActivityRelatesToRiskFactvw CARISK
INNER JOIN #PCAALLList CALIST
ON CALIST.ControlActivityDimKey = CARISK.ControlActivityDimKey
JOIN ControlActivityDimvw CADIM
ON CADIM.ControlActivityDimKey = CALIST.ControlActivityDimKey
INNER JOIN EntityManagedTypeFactvw EMT
ON CADIM.EntityDimKey = EMT.EntityDimKey
JOIN ManagedTypeDimvw MT
ON MT.ManagedTypeDimKey = EMT.ManagedTypeDimKey
SELECT Risk.*
FROM
(SELECT DISTINCT
R.RiskDimKey,
R.Id AS Id,
R.Title AS Title,
R.ObjectStatus,
P.RelatedObjectDimKey AS RelatedObjectDimKey,
P.RelatedObjectID AS RelatedObjectID,
P.ComplianceType AS ComplianceType,
RS.RiskStatusValue AS [Status],
UD.DisplayName AS [Owner],
R.DueDate AS DueDate,
RI.RiskImpactValue AS Impact,
RP.RiskLikelihoodValue AS LikeLihood,
RCL.RiskControlLevelValue AS [Control Level],
R.InherentRisk AS [Inherent Risk],
R.ResidualRisk AS [Residual Risk],
RR.RiskRankValue AS [Rank],
RC.RiskClassificationValue AS RiskResponse
FROM #ProgramRiskList P
INNER JOIN RiskDimvw R
ON P.RiskDimKey = R.RiskDimKey
LEFT JOIN RiskClassificationvw RC ON
RC.RiskClassificationId = R.RiskResponse_RiskClassificationId
LEFT JOIN RiskRankvw RR ON
RR.RiskRankId = R.RankEnum_RiskRankId
LEFT JOIN RiskStatus RS ON
RS.RiskStatusId = R.Status_RiskStatusId
LEFT JOIN RiskImpactvw RI
ON R.Impact_RiskImpactId = RI.RiskImpactID
LEFT JOIN RiskLikelihoodvw RP
ON R.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
LEFT JOIN RiskControlLevelvw RCL
ON R.ControlLevel_RiskControlLevelId = RCL.RiskControlLevelId
LEFT JOIN RiskOwnedByUserFactvw RO ON
RO.RiskDimKey = R.RiskDimKey
AND RO.DeletedDate IS NULL
LEFT JOIN UserDimvw UD ON
UD.UserDimKey = RO.ConfigItemOwnedByUser_UserDimKey
AND UD.IsDeleted = 0
LEFT JOIN RiskAssignedToUserFactvw RA ON
RA.RiskDimKey = R.RiskDimKey
AND RA.DeletedDate IS NULL
WHERE
(DATEDIFF(DAY, R.CreatedDate, @StartDate) <= 0 AND DATEDIFF(DAY, R.CreatedDate, @EndDate) >=0)
AND (0 = @OwnedBy OR UD.UserDimKey = @OwnedBy)
AND ((-1 IN (Select value from @tableRiskStatus)) OR (RS.RiskStatusId IN (Select value from @tableRiskStatus)))
AND ((-1 IN (Select value from @tableRiskRank)) OR (RR.RiskRankId IN (Select value from @tableRiskRank)))
AND ((-1 IN (Select value from @tableRiskResponse)) OR (RC.RiskClassificationId IN (Select value from @tableRiskResponse)))
AND R.InherentRisk >= @InherentRisk
AND R.IsDeleted = 0
) Risk
Order by Risk.RelatedObjectID, Risk.Id
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_RiskManagement_SP_ProgramRiskList TO reportuser
GO
Home