/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_RiksManagement_SP_GetRiskDetails] Script Date: 08/05/2010 02:58:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_RiksManagement_SP_GetRiskDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_RiksManagement_SP_GetRiskDetails]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_RiksManagement_SP_GetRiskDetails] Script Date: 08/05/2010 02:58:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Milind Mahajan
-- Create date: 10/14/2009
-- Description: To Get the Risk Details
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_RiksManagement_SP_GetRiskDetails]
@RiskId int=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT RD.Id AS 'Risk Id'
, RD.Title AS Title
, RD.CauseDescription AS CauseDescription
, RD.Description AS ImpactDescription
, RiskImpactValue AS Impact
, RP.RiskLikelihoodValue AS LikeLihood
, RC.RiskControlLevelValue AS 'Control Level'
, RD.InherentRisk AS 'Inherent Risk'
, RD.ResidualRisk AS 'Residual Risk'
, PS.PolicyItemSharedValue AS Shared
, RCL.RiskClassificationValue AS 'Risk Response'
, RD.DueDate AS DueDate
, OwnedBy.DisplayName AS 'Owned By'
, AssignedTo.DisplayName AS 'Assigned To'
, RD.ResponsePlan AS ResponsePlan
, CreatedBy.UserName AS CreatedBy
, ModifiedBy.UserName AS ModifiedBy
FROM RiskDimvw RD
LEFT JOIN RiskImpactvw RI
ON RD.Impact_RiskImpactId = RI.RiskImpactID
LEFT JOIN RiskLikelihoodvw RP
ON RD.Likelihood_RiskLikelihoodId = RP.RiskLikeLiHoodId
LEFT JOIN RiskControlLevelvw RC
ON RD.ControlLevel_RiskControlLevelId = RC.RiskControlLevelId
LEFT JOIN PolicyItemShared PS
ON RD.Shared_PolicyItemSharedId = PS.PolicyItemSharedId
LEFT JOIN RiskClassificationvw RCL
ON RD.RiskResponse_RiskClassificationId = RCL.RiskClassificationId
LEFT JOIN RiskOwnedByUserFactvw ROBU
ON ROBU.RiskDimKey = RD.RiskDimKey
LEFT JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = ROBU.ConfigItemOwnedByUser_UserDimKey
LEFT JOIN RiskAssignedToUserFactvw RATU
ON RATU.RiskDimKey = RD.RiskDimKey
LEFT JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = RATU.PolicyItemAssignedTo_UserDimKey
LEFT JOIN RiskCreatedByUserFactvw RCU
ON RCU.RiskDimKey = RD.RiskDimKey
AND RCU.DeletedDate IS NULL
LEFT JOIN UserDimvw CreatedBy
ON CreatedBy.UserDimKey = RCU.PolicyItemCreatedBy_UserDimKey
LEFT JOIN RiskModifiedByUserFactvw RMU
ON RMU.RiskDimKey = RD.RiskDimKey
AND RMU.DeletedDate IS NULL
LEFT JOIN UserDimvw ModifiedBy
ON ModifiedBy.UserDimKey = RMU.PolicyItemModifiedBy_UserDimKey
WHERE RD.RiskDimKey = @RiskId
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_RiksManagement_SP_GetRiskDetails TO reportuser
GO
Home