/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetControlObjectiveRelatedItems] Script Date: 06/10/2010 02:46:28 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetControlObjectiveRelatedItems]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetControlObjectiveRelatedItems]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetControlObjectiveRelatedItems] Script Date: 06/10/2010 02:46:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: <Create Date,,>
-- Description: To get CO Related Items
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetControlObjectiveRelatedItems]
@ControlObjectiveDimKey INT,
@Type NVARCHAR(MAX)
AS
BEGIN
IF @Type = 'Incidents'
BEGIN
SELECT
ID.Id as FieldOne,
ID.Title AS FieldTwo,
ISV.IncidentStatusValue AS FieldThree,
NULL AS FieldFour,
NULL AS FieldFive
FROM ControlObjectiveDimvw COD
LEFT JOIN ControlObjectiveRelatedToIncidentFactvw COI
ON COD.ControlObjectiveDimKey = COI.WorkItemRelatesToConfigItem_ControlObjectiveDimKey
LEFT JOIN IncidentDimvw ID
ON ID.IncidentDimKey = COI.IncidentDimKey
LEFT JOIN IncidentStatusvw ISV
ON ISV.IncidentStatusId = ID.Status_IncidentStatusId
WHERE COD.ControlObjectiveDimKey = @ControlObjectiveDimKey
END
ELSE IF @Type = 'Risk'
BEGIN
SELECT
RD.Id AS FieldOne,
RD.Title AS FieldTwo,
UD.DisplayName AS FieldThree,
RR.RiskRankValue AS FieldFour,
RD.RiskDimKey AS FieldFive
FROM ControlObjectiveDimvw COD
LEFT JOIN ControlObjectiveRelatesToRiskFactvw COR
ON COD.ControlObjectiveDimKey = COR.ControlObjectiveDimKey
LEFT JOIN RiskDimvw RD
ON RD.RiskDimKey = COR.PolicyItemRelatesToCompliancePolicyItem_RiskDimKey
LEFT JOIn RiskOwnedByUserFactvw ROU
ON ROU.RiskDimKey = RD.RiskDimKey
LEFT JOIN UserDim UD
ON UD.UserDimKey = ROU.ConfigItemOwnedByUser_UserDimKey
LEFT JOIN RiskRankvw RR ON
RR.RiskRankId = RD.RankEnum_RiskRankId
WHERE COD.ControlObjectiveDimKey = @ControlObjectiveDimKey
UNION
SELECT
RD.Id AS FieldOne,
RD.Title AS FieldTwo,
UD.DisplayName AS FieldThree,
RR.RiskRankValue AS FieldFour,
RD.RiskDimKey AS FieldFive
FROM ControlObjectiveDimvw COD
LEFT JOIN RiskRelatesToControlObjectiveFactvw RCO
ON COD.ControlObjectiveDimKey = RCO.PolicyItemRelatesToCompliancePolicyItem_ControlObjectiveDimKey
LEFT JOIN RiskDimvw RD
ON RD.RiskDimKey = RCO.RiskDimKey
LEFT JOIn RiskOwnedByUserFactvw ROU
ON ROU.RiskDimKey = RD.RiskDimKey
LEFT JOIN UserDim UD
ON UD.UserDimKey = ROU.ConfigItemOwnedByUser_UserDimKey
LEFT JOIN RiskRankvw RR ON
RR.RiskRankId = RD.RankEnum_RiskRankId
WHERE COD.ControlObjectiveDimKey = @ControlObjectiveDimKey
END
ELSE IF @Type = 'WorkItem'
BEGIN
SELECT
WD.Id AS FieldOne,
WD.Title AS FieldTwo,
UD.DisplayName AS FieldThree,
NULL AS FieldFour, -- Check This
ED.LastModified AS FieldFive
FROM ControlObjectiveDimvw COD
LEFT JOIN ControlObjectiveRelatedToWorkItemFactvw COW
ON COD.ControlObjectiveDimKey = COW.WorkItemRelatesToConfigItem_ControlObjectiveDimKey
LEFT JOIN WorkItemDimvw WD
ON WD.WorkItemDimKey = COW.WorkItemDimKey
LEFT JOIN WorkItemAssignedToUserFactvw WIA
ON WIA.WorkItemDimKey = WD.WorkItemDimKey
LEFT JOIN UserDimvw UD
ON UD.UserDimKey = WIA.WorkItemAssignedToUser_UserDimKey
LEFT JOIN EntityDimvw ED
ON ED.EntityDimKey = WD.EntityDimKey
WHERE COD.ControlObjectiveDimKey = @ControlObjectiveDimKey
END
ELSE IF @Type = 'AttachedFiles'
BEGIN
SELECT
ED.DisplayName AS FieldOne,
NULL AS FieldTwo,
NULL AS FieldThree,
NULL AS FieldFour,
NULL AS FieldFive
FROM ControlObjectiveDimvw COD
LEFT JOIN ControlObjectiveContainsFileAttachmentFactvw COF
ON COD.ControlObjectiveDimKey = COF.ControlObjectiveDimKey
LEFT JOIN EntityDimvw ED
ON ED.EntityDimKey = COF.ConfigItemHasFileAttachment_EntityDimKey
WHERE COD.ControlObjectiveDimKey = @ControlObjectiveDimKey
END
ELSE IF @Type = 'ReviewActivity'
BEGIN
SELECT
RAD.Id AS FieldOne,
NULL AS FieldTwo, -- Check This
ASV.ActivityStatusValue AS FieldThree,
RAD.Title AS FieldFour,
NULL AS FieldFive
FROM ControlObjectiveDimvw COD
LEFT JOIN ControlObjectiveContainsReviewActivityFactvw CRA
ON COD.ControlObjectiveDimKey = CRA.ControlObjectiveDimKey
LEFT JOIN ReviewActivityDimvw RAD
ON RAD.ReviewActivityDimKey = CRA.ControlObjectiveReviewActivity_ReviewActivityDimKey
LEFT JOIN ActivityStatusvw ASV
ON RAD.Status_ActivityStatusId = ASV.ActivityStatusId
WHERE COD.ControlObjectiveDimKey = @ControlObjectiveDimKey
END
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetControlObjectiveRelatedItems TO reportuser
GO
Home