/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetInherentRiskMapSets] Script Date: 08/09/2010 22:28:26 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetInherentRiskMapSets]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetInherentRiskMapSets]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetInherentRiskMapSets] Script Date: 08/09/2010 22:28:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MilindMa
-- Create date: 10/7/2009
-- Last Updated: 10/29/2009
-- Last Updated by: MilindMa
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_RiskManagement_SP_GetInherentRiskMapSets]
@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..#InhRiskCATData', N'U') IS NOT NULL
BEGIN
DROP TABLE #InhRiskCATData
END;
WITH InhRiskCATData (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 InhRiskCATData AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
)
SELECT ProgramDimKey, CategoryDimKey INTO #InhRiskCATData FROM InhRiskCATData;
IF OBJECT_ID(N'tempdb..#InhRiskDimKey', N'U') IS NOT NULL
BEGIN
DROP TABLE #InhRiskDimKey
END;
WITH InhRiskDimKey (CategoryDimKey, RiskDimKey)
AS
(
SELECT A.CategoryDimKey, A.CategoryContainsRisk_RiskDimKey AS RiskDimKey
FROM CategoryContainsRiskFactvw A
INNER JOIN #InhRiskCATData B
ON A.CategoryDimKey = B.CategoryDimKey
UNION ALL
SELECT RD.CategoryDimKey, RR.PolicyItemContainsCompliancePolicyItem_RiskDimKey AS RiskDimKey
FROM RiskContainsRiskFactvw AS RR
INNER JOIN InhRiskDimKey AS RD
ON RR.RiskDimKey = RD.RiskDimKey
)
SELECT CategoryDimKey, RiskDimKey into #InhRiskDimKey FROM InhRiskDimKey;
IF OBJECT_ID(N'tempdb..#IRPHasRisk', N'U') IS NOT NULL
BEGIN
DROP TABLE #IRPHasRisk
END;
WITH IRPHasRisk (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 IRPHasRisk AS RD
ON RR.RiskDimKey = RD.RiskDimKey
)
SELECT ProgramDimKey, RiskDimKey into #IRPHasRisk FROM IRPHasRisk;
IF OBJECT_ID(N'tempdb..#InhRiskMapRisks', N'U') IS NOT NULL
BEGIN
DROP TABLE #InhRiskMapRisks
END;
CREATE TABLE #InhRiskMapRisks
(Risk int)
INSERT INTO #InhRiskMapRisks
SELECT DISTINCT RiskDimKey
FROM #InhRiskDimKey
UNION
SELECT DISTINCT RiskDimKey
FROM #IRPHasRisk
IF @ProgramId = 0 AND @AssignedTo = 0
BEGIN
INSERT #InherentRiskMapReportSets
-- For First set we will use x=Min to Mid an y= Min to Mid
SELECT RD.RiskDimKey
, RI.RiskImpactValue AS x1
, RP.RiskLikeLihoodValue 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 CAST (RI.RiskImpactValue AS tinyint) BETWEEN @xAxisMin AND @xAxisMid
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x2
, RP.RiskLikeLihoodValue 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 CAST (RI.RiskImpactValue AS tinyint) > @xAxisMid
AND CAST (RI.RiskImpactValue AS tinyint) <= @xAxisMax
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue AS tinyint) >= @yAxisMin
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x3
, RP.RiskLikeLihoodValue AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) BETWEEN @xAxisMid AND @xAxisMax
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x4
, RP.RiskLikeLihoodValue AS Y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) >= @xAxisMin
AND CAST (RI.RiskImpactValue AS tinyint) < @xAxisMid
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue AS tinyint) > @yAxisMid
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.ParentId IS NOT NULL
END
ELSE IF @ProgramId = -1 AND @AssignedTo != -1
BEGIN
INSERT #InherentRiskMapReportSets
-- For First set we will use x=Min to Mid an y= Min to Mid
SELECT RD.RiskDimKey
, RI.RiskImpactValue AS x1
, RP.RiskLikeLihoodValue 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 CAST (RI.RiskImpactValue AS tinyint) BETWEEN @xAxisMin AND @xAxisMid
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x2
, RP.RiskLikeLihoodValue 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 CAST (RI.RiskImpactValue AS tinyint) > @xAxisMid
AND CAST (RI.RiskImpactValue AS tinyint) <= @xAxisMax
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue AS tinyint) >= @yAxisMin
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x3
, RP.RiskLikeLihoodValue AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) BETWEEN @xAxisMid AND @xAxisMax
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x4
, RP.RiskLikeLihoodValue AS Y4
FROM RiskDimvw RD
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) >= @xAxisMin
AND CAST (RI.RiskImpactValue AS tinyint) < @xAxisMid
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue AS tinyint) > @yAxisMid
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.ParentId IS NOT NULL
END
ELSE IF @ProgramId != 0 AND @AssignedTo != 0
BEGIN
INSERT #InherentRiskMapReportSets
-- For First set we will use x=Min to Mid an y= Min to Mid
SELECT RD.RiskDimKey
, RI.RiskImpactValue AS x1
, RP.RiskLikeLihoodValue AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #InhRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) BETWEEN @xAxisMin AND @xAxisMid
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x2
, RP.RiskLikeLihoodValue AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #InhRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) > @xAxisMid
AND CAST (RI.RiskImpactValue AS tinyint) <= @xAxisMax
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue AS tinyint) >= @yAxisMin
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x3
, RP.RiskLikeLihoodValue AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #InhRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) BETWEEN @xAxisMid AND @xAxisMax
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x4
, RP.RiskLikeLihoodValue AS Y4
FROM RiskDimvw RD
JOIN #InhRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) >= @xAxisMin
AND CAST (RI.RiskImpactValue AS tinyint) < @xAxisMid
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue AS tinyint) > @yAxisMid
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.ParentId IS NOT NULL
END
ELSE IF @ProgramId != 0 AND @AssignedTo = 0
BEGIN
INSERT #InherentRiskMapReportSets
-- For First set we will use x=Min to Mid an y= Min to Mid
SELECT RD.RiskDimKey
, RI.RiskImpactValue AS x1
, RP.RiskLikeLihoodValue AS y1
, null AS x2, null AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #InhRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) BETWEEN @xAxisMin AND @xAxisMid
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x2
, RP.RiskLikeLihoodValue AS y2
, null AS x3, null AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #InhRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) > @xAxisMid
AND CAST (RI.RiskImpactValue AS tinyint) <= @xAxisMax
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue AS tinyint) >= @yAxisMin
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x3
, RP.RiskLikeLihoodValue AS y3
, null AS x4, null AS y4
FROM RiskDimvw RD
JOIN #InhRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) BETWEEN @xAxisMid AND @xAxisMax
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.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
, RI.RiskImpactValue AS x4
, RP.RiskLikeLihoodValue AS Y4
FROM RiskDimvw RD
JOIN #InhRiskMapRisks IRMRS
ON RD.RiskDimKey = IRMRS.Risk
JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
AND CAST (RI.RiskImpactValue AS tinyint) >= @xAxisMin
AND CAST (RI.RiskImpactValue AS tinyint) < @xAxisMid
AND ((-1 IN (Select value from @inherentRiskStatus)) OR (RD.Status_RiskStatusId IN (Select value from @inherentRiskStatus)))
AND DATEDIFF(DAY, CreatedDate, @StartDate) <= 0
AND DATEDIFF(DAY, CreatedDate, @EndDate) >=0
JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
AND CAST (RP.RiskLikeLihoodValue AS tinyint) > @yAxisMid
AND CAST (RP.RiskLikeLihoodValue 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 RI.ParentId IS NOT NULL
AND RP.ParentId IS NOT NULL
END
--Fix for Bug 192321
DELETE FROM #InherentRiskMapReportSets WHERE x1=3 and y1=3
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS RowNum, Id, x1, y1, x2, y2, x3, y3, x4, y4
FROM #InherentRiskMapReportSets
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_RiskManagement_SP_GetInherentRiskMapSets TO reportuser
GO
Home