/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetResidualRiskMapSets] Script Date: 08/09/2010 22:28:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetResidualRiskMapSets]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetResidualRiskMapSets]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetResidualRiskMapSets] Script Date: 08/09/2010 22:28:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MilindMa
-- Create date: 10/9/2009
-- Last Updated: 10/29/2009
-- Last Updated by: MilindMa
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetResidualRiskMapSets]
@StartDate Datetime,
@EndDate Datetime,
@ProgramId int = null,
@AssignedTo int = null,
@OwnedBy int = null,
@StatusId 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..#RsuRiskCATData', N'U') IS NOT NULL
BEGIN
DROP TABLE #RsuRiskCATData
END;
WITH RsuRiskCATData (ProgramDimKey, CategoryDimKey)
AS
(
SELECT ProgramDimKey, ProgramHasCategory_CategoryDimKey AS CategoryDimKey
FROM ProgramHasCategoriesFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
UNION ALL
SELECT CD.ProgramDimKey, CC.CategoryContainsCategory_CategoryDimKey as CategoryDimKey
FROM CategoryContainsCategoryFactvw AS CC
INNER JOIN RsuRiskCATData AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
)
SELECT ProgramDimKey, CategoryDimKey INTO #RsuRiskCATData FROM RsuRiskCATData;
IF OBJECT_ID(N'tempdb..#RsuRiskDimKey', N'U') IS NOT NULL
BEGIN
DROP TABLE #RsuRiskDimKey
END;
WITH RsuRiskDimKey (CategoryDimKey, RiskDimKey)
AS
(
SELECT A.CategoryDimKey, A.CategoryContainsRisk_RiskDimKey AS RiskDimKey
FROM CategoryContainsRiskFactvw A
INNER JOIN #RsuRiskCATData B
ON A.CategoryDimKey = B.CategoryDimKey
UNION ALL
SELECT RD.CategoryDimKey, RR.PolicyItemContainsCompliancePolicyItem_RiskDimKey AS RiskDimKey
FROM RiskContainsRiskFactvw AS RR
INNER JOIN RsuRiskDimKey AS RD
ON RR.RiskDimKey = RD.RiskDimKey
)
SELECT CategoryDimKey, RiskDimKey into #RsuRiskDimKey FROM RsuRiskDimKey;
IF OBJECT_ID(N'tempdb..#RRPHasRisk', N'U') IS NOT NULL
BEGIN
DROP TABLE #RRPHasRisk
END;
WITH RRPHasRisk (ProgramDimKey, RiskDimKey)
AS
(
SELECT ProgramDimKey, PolicyItemRelatesToCompliancePolicyItem_RiskDimKey AS RiskDimKey
FROM ProgramContainsRisksFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
UNION ALL
SELECT RD.RiskDimKey, RR.PolicyItemContainsCompliancePolicyItem_RiskDimKey AS RiskDimKey
FROM RiskContainsRiskFactvw AS RR
INNER JOIN RRPHasRisk AS RD
ON RR.RiskDimKey = RD.RiskDimKey
)
SELECT ProgramDimKey, RiskDimKey into #RRPHasRisk FROM RRPHasRisk;
IF OBJECT_ID(N'tempdb..#RsuRiskMapRisks', N'U') IS NOT NULL
BEGIN
DROP TABLE #RsuRiskMapRisks
END;
CREATE TABLE #RsuRiskMapRisks
(Risk int)
INSERT INTO #RsuRiskMapRisks
SELECT DISTINCT RiskDimKey
FROM #RsuRiskDimKey
UNION
SELECT DISTINCT RiskDimKey
FROM #RRPHasRisk
IF @ProgramId = 0 AND @AssignedTo = 0
BEGIN
INSERT #ResidualRiskMapReportSets
-- For First set we will use x=Min to Mid an y= Min to Mid
SELECT Rd.RiskDimKey
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x1
, RC.RiskControlLevelValue AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) BETWEEN @yAxisMin AND @yAxisMid
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
WHERE (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint)) BETWEEN @xAxisMin AND @xAxisMid
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Second set we will use x=Mid to Max an y= Min to Mid
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x2
, RC.RiskControlLevelValue AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) >= @yAxisMin
AND CAST(RC.RiskControlLevelValue AS tinyint) < @yAxisMid
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
WHERE CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) > @xAxisMid
AND CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) <= @xAxisMax
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Third set we will use x=Mid to Max, y=Mid to Max
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, null AS x2, null AS y2
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x3
, RC.RiskControlLevelValue AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) BETWEEN @yAxisMid AND @yAxisMax
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
WHERE CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) BETWEEN @xAxisMid AND @xAxisMax
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Fourth set we will use x=Mid to Max, y=Mid to Max
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x4
, RC.RiskControlLevelValue AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) > @yAxisMid
AND CAST(RC.RiskControlLevelValue AS tinyint) <= @yAxisMax
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
WHERE (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) >= @xAxisMin
AND (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) < @xAxisMid
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
END
ELSE IF @ProgramId = 0 AND @AssignedTo != 0
BEGIN
INSERT #ResidualRiskMapReportSets
-- For First set we will use x=Min to Mid an y= Min to Mid
SELECT Rd.RiskDimKey
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x1
, RC.RiskControlLevelValue AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) BETWEEN @yAxisMin AND @yAxisMid
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
JOIN RiskAssignedToUserFactvw RATU
ON RATU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = RATU.PolicyItemAssignedTo_UserDimKey
AND AssignedTo.UserDimKey = @AssignedTo
WHERE (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint)) BETWEEN @xAxisMin AND @xAxisMid
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Second set we will use x=Mid to Max an y= Min to Mid
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x2
, RC.RiskControlLevelValue AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) >= @yAxisMin
AND CAST(RC.RiskControlLevelValue AS tinyint) < @yAxisMid
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
JOIN RiskAssignedToUserFactvw RATU
ON RATU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = RATU.PolicyItemAssignedTo_UserDimKey
AND AssignedTo.UserDimKey = @AssignedTo
WHERE CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) > @xAxisMid
AND CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) <= @xAxisMax
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Third set we will use x=Mid to Max, y=Mid to Max
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, null AS x2, null AS y2
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x3
, RC.RiskControlLevelValue AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) BETWEEN @yAxisMid AND @yAxisMax
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
JOIN RiskAssignedToUserFactvw RATU
ON RATU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = RATU.PolicyItemAssignedTo_UserDimKey
AND AssignedTo.UserDimKey = @AssignedTo
WHERE CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) BETWEEN @xAxisMid AND @xAxisMax
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Fourth set we will use x=Mid to Max, y=Mid to Max
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x4
, RC.RiskControlLevelValue AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) > @yAxisMid
AND CAST(RC.RiskControlLevelValue AS tinyint) <= @yAxisMax
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
JOIN RiskAssignedToUserFactvw RATU
ON RATU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = RATU.PolicyItemAssignedTo_UserDimKey
AND AssignedTo.UserDimKey = @AssignedTo
WHERE (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) >= @xAxisMin
AND (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) < @xAxisMid
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
END
ELSE IF @ProgramId != 0 AND @AssignedTo != 0
BEGIN
INSERT #ResidualRiskMapReportSets
-- For First set we will use x=Min to Mid an y= Min to Mid
SELECT Rd.RiskDimKey
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x1
, RC.RiskControlLevelValue AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #RsuRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) BETWEEN @yAxisMin AND @yAxisMid
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
JOIN RiskAssignedToUserFactvw RATU
ON RATU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = RATU.PolicyItemAssignedTo_UserDimKey
AND AssignedTo.UserDimKey = @AssignedTo
WHERE (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint)) BETWEEN @xAxisMin AND @xAxisMid
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Second set we will use x=Mid to Max an y= Min to Mid
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x2
, RC.RiskControlLevelValue AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #RsuRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) >= @yAxisMin
AND CAST(RC.RiskControlLevelValue AS tinyint) < @yAxisMid
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
JOIN RiskAssignedToUserFactvw RATU
ON RATU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = RATU.PolicyItemAssignedTo_UserDimKey
AND AssignedTo.UserDimKey = @AssignedTo
WHERE CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) > @xAxisMid
AND CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) <= @xAxisMax
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Third set we will use x=Mid to Max, y=Mid to Max
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, null AS x2, null AS y2
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x3
, RC.RiskControlLevelValue AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #RsuRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) BETWEEN @yAxisMid AND @yAxisMax
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
JOIN RiskAssignedToUserFactvw RATU
ON RATU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = RATU.PolicyItemAssignedTo_UserDimKey
AND AssignedTo.UserDimKey = @AssignedTo
WHERE CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) BETWEEN @xAxisMid AND @xAxisMax
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Fourth set we will use x=Mid to Max, y=Mid to Max
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x4
, RC.RiskControlLevelValue AS y4
FROM RiskDimvw RD
JOIN #RsuRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) > @yAxisMid
AND CAST(RC.RiskControlLevelValue AS tinyint) <= @yAxisMax
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
JOIN RiskAssignedToUserFactvw RATU
ON RATU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = RATU.PolicyItemAssignedTo_UserDimKey
AND AssignedTo.UserDimKey = @AssignedTo
WHERE (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) >= @xAxisMin
AND (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) < @xAxisMid
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
END
ELSE IF @ProgramId != 0 AND @AssignedTo = 0
BEGIN
INSERT #ResidualRiskMapReportSets
-- For First set we will use x=Min to Mid an y= Min to Mid
SELECT Rd.RiskDimKey
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x1
, RC.RiskControlLevelValue AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #RsuRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) BETWEEN @yAxisMin AND @yAxisMid
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
WHERE (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint)) BETWEEN @xAxisMin AND @xAxisMid
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Second set we will use x=Mid to Max an y= Min to Mid
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x2
, RC.RiskControlLevelValue AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #RsuRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) >= @yAxisMin
AND CAST(RC.RiskControlLevelValue AS tinyint) < @yAxisMid
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
WHERE CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) > @xAxisMid
AND CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) <= @xAxisMax
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Third set we will use x=Mid to Max, y=Mid to Max
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, null AS x2, null AS y2
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x3
, RC.RiskControlLevelValue AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #RsuRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) BETWEEN @yAxisMid AND @yAxisMax
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
WHERE CAST (RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikelihoodValue AS tinyint) BETWEEN @xAxisMid AND @xAxisMax
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
UNION
-- For Fourth set we will use x=Mid to Max, y=Mid to Max
SELECT Rd.RiskDimKey
, null AS x1, null AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) AS x4
, RC.RiskControlLevelValue AS y4
FROM RiskDimvw RD
JOIN #RsuRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND ((-1 IN (Select value from @residualRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @residualRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
AND CAST(RC.RiskControlLevelValue AS tinyint) > @yAxisMid
AND CAST(RC.RiskControlLevelValue AS tinyint) <= @yAxisMax
JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR OwnedBy.UserDimKey = @OwnedBy)
WHERE (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) >= @xAxisMin
AND (CAST(RI.RiskImpactValue AS tinyint) * CAST(RP.RiskLikeLihoodValue AS tinyint)) < @xAxisMid
AND RI.ParentId IS NOT NULL
AND RC.ParentId IS NOT NULL
END
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS RowNum, Id, x1, y1, x2, y2, x3, y3, x4, y4
FROM #ResidualRiskMapReportSets
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_RiskManagement_SP_GetResidualRiskMapSets TO reportuser
GO
Home