/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramList] Script Date: 06/08/2010 03:09:17 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramList]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramList] Script Date: 06/08/2010 03:09:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: 02-22-2010
-- Description: To get the Count of CO, CA, Risk associated to Program
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramList]
-- Add the parameters for the stored procedure here
@ProgramId int = null,
@OwnedBy int = null,
@StatusId nvarchar(max) = '-1',
@StartDate Datetime,
@EndDate Datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
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
UNION
SELECT DISTINCT CALIST.ProgramDimKey, null, CARISK.ControlActivityRelatesToRisk_RiskDimKey
FROM dbo.ControlActivityRelatesToRiskFactvw CARISK
INNER JOIN #PCAALLList CALIST
ON CALIST.ControlActivityDimKey = CARISK.ControlActivityDimKey
UNION
SELECT DISTINCT COLIST.ProgramDimKey, null, CORISK.PolicyItemRelatesToCompliancePolicyItem_RiskDimKey
FROM dbo.ControlObjectiveRelatesToRiskFactvw CORISK
INNER JOIN #PCOALLList COLIST
ON COLIST.ControlObjectiveDimKey = CORISK.ControlObjectiveDimKey
UNION
SELECT DISTINCT COLIST.ProgramDimKey, null, RISKCO.RiskDimKey
FROM dbo.RiskRelatesToControlObjectiveFactvw RISKCO
INNER JOIN #PCOALLList COLIST
ON COLIST.ControlObjectiveDimKey = RISKCO.PolicyItemRelatesToCompliancePolicyItem_ControlObjectiveDimKey
INSERT INTO #ComplianceIncidentList
SELECT DISTINCT COI.IncidentDimkey, COLIST.ProgramDimKey,StatusEnum.IncidentStatusValue
FROM dbo.ControlObjectiveRelatedToIncidentFactvw COI
INNER JOIN #PCOALLList COLIST
ON COLIST.ControlObjectiveDimKey = COI.WorkItemRelatesToConfigItem_ControlObjectiveDimKey
INNER JOIN IncidentDimvw I
ON I.IncidentDimKey = COI.IncidentDimKey
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
WHERE COI.DeletedDate IS NULL
AND I.IsDeleted = 0
UNION
SELECT DISTINCT CAI.WorkItemRelatesToWorkItem_IncidentDimKey,CALIST.ProgramDimKey,StatusEnum.IncidentStatusValue
FROM dbo.ControlActivityRelatedToIncidentFactvw CAI
INNER JOIN #PCAALLList CALIST
ON CALIST.ControlActivityDimKey = CAI.ControlActivityDimKey
INNER JOIN IncidentDimvw I
ON I.IncidentDimKey = CAI.WorkItemRelatesToWorkItem_IncidentDimKey
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
WHERE CAI.DeletedDate IS NULL
AND I.IsDeleted = 0
UNION
SELECT DISTINCT RSI.IncidentDimkey,RISKLIST.ProgramDimKey,StatusEnum.IncidentStatusValue
FROM dbo.RiskRelatedToIncidentFactvw RSI
INNER JOIN #PRiskALLList RISKLIST
ON RISKLIST.RiskDimKey = RSI.WorkItemRelatesToConfigItem_RiskDimKey
INNER JOIN IncidentDimvw I
ON I.IncidentDimKey = RSI.IncidentDimKey
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
WHERE RSI.DeletedDate IS NULL
AND I.IsDeleted = 0
SELECT PList.*
, (SELECT COUNT(DISTINCT(COD.ControlObjectiveDimKey)) FROM ControlObjectiveDimvw COD
JOIN COStatus CS ON COD.Status_COStatusId = CS.COStatusId
JOIN #PCAALLList PCA ON PCA.ControlObjectiveDimKey = COD.ControlObjectiveDimKey
WHERE PCA.ProgramDimKey = PList.ProgramDimKey AND CS.COStatusValue = 'Published'
AND COD.IsDeleted = 0 AND COD.ObjectStatus NOT LIKE '%Delete') AS PublishedCOCount
, (SELECT COUNT(DISTINCT(COD.ControlObjectiveDimKey)) FROM ControlObjectiveDimvw COD
JOIN COStatus CS ON COD.Status_COStatusId = CS.COStatusId
JOIN #PCAALLList PCA ON PCA.ControlObjectiveDimKey = COD.ControlObjectiveDimKey
WHERE PCA.ProgramDimKey = PList.ProgramDimKey AND CS.COStatusValue = 'Draft'
AND COD.IsDeleted = 0 AND COD.ObjectStatus NOT LIKE '%Delete') AS DraftCOCount
, (SELECT COUNT(DISTINCT(COD.ControlObjectiveDimKey)) FROM ControlObjectiveDimvw COD
JOIN COStatus CS ON COD.Status_COStatusId = CS.COStatusId
JOIN #PCAALLList PCA ON PCA.ControlObjectiveDimKey = COD.ControlObjectiveDimKey
WHERE PCA.ProgramDimKey = PList.ProgramDimKey AND CS.COStatusValue = 'Pending'
AND COD.IsDeleted = 0 AND COD.ObjectStatus NOT LIKE '%Delete') AS PendingCOCount
, (SELECT COUNT(DISTINCT(COD.ControlObjectiveDimKey)) FROM ControlObjectiveDimvw COD
JOIN COStatus CS ON COD.Status_COStatusId = CS.COStatusId
JOIN #PCAALLList PCA ON PCA.ControlObjectiveDimKey = COD.ControlObjectiveDimKey
WHERE PCA.ProgramDimKey = PList.ProgramDimKey AND CS.COStatusValue = 'Archived'
AND COD.IsDeleted = 0 AND COD.ObjectStatus NOT LIKE '%Delete') AS ArchivedCOCount
, (SELECT COUNT(DISTINCT(PCA.ControlActivityDimKey)) FROM #PCAALLList PCA
WHERE PCA.ProgramDimKey = PList.ProgramDimKey AND PCA.ControlActivityDimKey <> 0) AS CACount
, (SELECT COUNT(DISTINCT(PR.RiskDimKey)) FROM #PRiskALLList PR
WHERE PR.ProgramDimKey = PList.ProgramDimKey) AS RiskCount
, (SELECT COUNT(DISTINCT(CIL.IncidentDimKey)) FROM #ComplianceIncidentList CIL
WHERE CIL.StatusEnum like '%Active%' AND CIL.ProgramDimKey = PList.ProgramDimKey) AS ActiveIncidentCount
FROM
(SELECT P.ProgramDimKey AS ProgramDimKey
,P.Id AS Id
,P.Title AS Title
,P.ObjectStatus AS Objectstatus
,S.StatusValue AS [Status]
,UDO.UserName AS [Owner]
,UDA.UserName AS COOwner
,UA.UserName AS Approver
,CreatedBy.UserName AS CreatedBy
,ModifiedBy.UserName AS ModifiedBy
FROM ProgramDimvw P
JOIN [Status] S
ON P.Status_StatusId = S.StatusId
JOIN ProgramOwnedByUserFactvw PO
ON P.ProgramDimKey = PO.ProgramDimKey
JOIN UserDimvw UDO
ON PO.ConfigItemOwnedByUser_UserDimKey = UDO.UserDimKey
LEFT JOIN ProgramCoOwnedByUserFactvw PCO
ON P.ProgramDimKey = PCO.ProgramDimKey
AND PCO.DeletedDate IS NULL
LEFT JOIN UserDimvw UDA
ON PCO.PolicyItemAssignedTo_UserDimKey = UDA.UserDimKey
AND UDA.IsDeleted = 0
LEFT JOIN ProgramApproverFactvw PA
ON PA.ProgramDimKey = P.ProgramDimKey
AND PA.DeletedDate IS NULL
LEFT JOIN UserDimvw UA
ON PA.ProgramApprover_UserDimKey = UA.UserDimKey
AND UA.IsDeleted = 0
LEFT JOIN ProgramCreatedByUserFactvw PCU
ON PCU.ProgramDimKey = P.ProgramDimKey
AND PCU.DeletedDate IS NULL
LEFT JOIN UserDimvw CreatedBy
ON CreatedBy.UserDimKey = PCU.PolicyItemCreatedBy_UserDimKey
LEFT JOIN ProgramModifiedByUserFactvw PMU
ON PMU.ProgramDimKey = P.ProgramDimKey
AND PMU.DeletedDate IS NULL
LEFT JOIN UserDimvw ModifiedBy
ON ModifiedBy.UserDimKey = PMU.PolicyItemModifiedBy_UserDimKey
WHERE (0 = @ProgramId OR P.ProgramDimKey = @ProgramId)
AND ((-1 IN (Select value from @tableStatus)) OR (S.StatusId IN (Select value from @tableStatus)))
AND (0 = @OwnedBy OR PO.ConfigItemOwnedByUser_UserDimKey = @OwnedBy)
AND (DATEDIFF(DAY, P.CreatedDate, @StartDate) <= 0 AND DATEDIFF(DAY, P.CreatedDate, @EndDate) >=0)
AND P.IsDeleted = 0
AND PO.DeletedDate IS NULL
AND UDO.IsDeleted = 0) PList
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetProgramList TO reportuser
GO
Home