/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCOs] Script Date: 06/08/2010 03:04:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCOs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCOs]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCOs] Script Date: 06/08/2010 03:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Last Updated: 10-26-2009 by milindma
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCOs]
-- Add the parameters for the stored procedure here
@StatusId INT,
@StartDate DATETIME,
@EndDate DATETIME,
@ProgramId INT=null,
@ADCategoryId nvarchar(max) = '-1',
@ADValue INT=NULL,
@AssignedTo INT=null,
@OwnedBy INT=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Error int
DECLARE @ExecError int
IF OBJECT_ID(N'tempdb..#CATData', N'U') IS NOT NULL
BEGIN
DROP TABLE #CATData
END;
WITH CATData (ProgramDimKey, CategoryDimKey)
AS
(
SELECT ProgramDimKey, ProgramHasCategory_CategoryDimKey AS CategoryDimKey
FROM ProgramHasCategoriesFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
UNION ALL
SELECT ProgramDimKey, CategoryContainsCategory_CategoryDimKey as CategoryDimKey
FROM dbo.CategoryContainsCategoryFactvw AS CC
INNER JOIN CATData AS CD
ON CC.CategoryDimKey = CD.CategoryDimKey
)
SELECT ProgramDimKey, CategoryDimKey into #CATData FROM CATData;
IF OBJECT_ID(N'tempdb..#CODimKey', N'U') IS NOT NULL
BEGIN
DROP TABLE #CODimKey
END;
WITH CODimKey (CategoryDimKey, ControlObjectiveDimKey)
AS
(
SELECT A.CategoryDimKey, A.CategoryContainsControlObjective_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM CategoryHasCOFactvw A
INNER JOIN #CATData B
ON A.CategoryDimKey = B.CategoryDimKey
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = A.CategoryContainsControlObjective_ControlObjectiveDimKey
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
WHERE
(0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND ((-1 IN (Select value from @tableADCategoryForCOList )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCOList)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
UNION ALL
SELECT CategoryDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactvw AS CC
INNER JOIN CODimKey AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
)
SELECT CategoryDimKey, ControlObjectiveDimKey into #CODimKey FROM CODimKey;
IF OBJECT_ID(N'tempdb..#PHasCO', N'U') IS NOT NULL
BEGIN
DROP TABLE #PHasCO
END;
WITH PHasCO (ProgramDimKey, ControlObjectiveDimKey)
AS
(
SELECT ProgramDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey AS ControlObjectiveDimKey
FROM ProgramHasCOFactvw
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND ((-1 IN (Select value from @tableADCategoryForCOList )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCOList)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)
UNION ALL
SELECT ProgramDimKey, PolicyItemContainsCompliancePolicyItem_ControlObjectiveDimKey as ControlObjectiveDimKey
FROM COHasCOFactvw AS CC
INNER JOIN PHasCO AS CD
ON CC.ControlObjectiveDimKey = CD.ControlObjectiveDimKey
)
SELECT ProgramDimKey, ControlObjectiveDimKey into #PHasCO FROM PHasCO;
SELECT DISTINCT COFilter.*,
AssignedTo.UserName As AssignedTo,
CreatedBy.UserName AS CreatedBy,
ModifiedBy.UserName AS ModifiedBy,
COType.COTypeValue,
Priority.PriorityValue,
Level.LevelValue
FROM (SELECT DISTINCT
CO.ControlObjectiveDimKey,
CO.Id,
CO.Title,
CO.COStatusValue,
CO.CreatedDate,
CO.ExternalId AS SourceId,
CO.ExternalName AS SourceName,
CO.ExternalVersion AS SourceVersion,
CO.LastModifiedDateTime AS ModifiedDate,
CO.UserName AS Owner,
CO.Type_COTypeId AS TypeId,
CO.Priority_PriorityId AS PriorityId,
CO.Level_LevelId AS LevelId,
CO.EntityDimKey
FROM (SELECT DISTINCT
COSD.ControlObjectiveDimKey,
CO.Id,
CO.Title as Title,
CS.COStatusId,
CS.COStatusValue,
CO.CreatedDate,
CO.ExternalId,
CSN.ComplianceSourceNameValue AS ExternalName,
CO.ExternalVersion,
COSD.LastModifiedDateTime,
COSD.StartDateTime,
COSD.FinishDateTime,
OwnedBy.UserName,
CO.Type_COTypeId,
CO.Priority_PriorityId,
CO.Level_LevelId,
CO.EntityDimKey
FROM ControlObjectiveStatusDurationFactvw COSD
JOIN ControlObjectiveDimvw CO
ON CO.ControlObjectiveDimKey = COSD.ControlObjectiveDimKey
JOIN #CODimKey AS CODimKey
ON CODimKey.ControlObjectiveDimKey = COSD.ControlObjectiveDimKey
JOIN COStatusvw CS
ON CS.COStatusId = COSD.COStatusId
AND (-1 = @StatusId OR CS.COStatusId = @StatusId)
LEFT JOIN ComplianceSourceNamevw CSN
ON CSN.ComplianceSourceNameId = CO.ExternalName_ComplianceSourceNameId
JOIN ControlObjectiveOwnedByUserFactvw COO
ON COO.ControlObjectiveDimKey = CO.ControlObjectiveDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = COO.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR COO.ConfigItemOwnedByUser_UserDimKey = @OwnedBy)) AS CO
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = CO.ControlObjectiveDimKey
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
WHERE ((DATEDIFF(DAY, CO.LastModifiedDateTime, @StartDate) <= 0 AND DATEDIFF(DAY, CO.LastModifiedDateTime, @EndDate) >=0)
OR (CO.FinishDateTime IS NOT NULL AND DATEDIFF(DAY, CO.FinishDateTime, @StartDate) <= 0 AND DATEDIFF(DAY, CO.FinishDateTime, @EndDate) >=0))
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND ((-1 IN (Select value from @tableADCategoryForCOList )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCOList)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)) AS COFilter
LEFT JOIN COType COType
ON COType.COTypeId = COFilter.TypeId
LEFT JOIN Priority Priority
ON Priority.PriorityId = COFilter.PriorityId
LEFT JOIN Level Level
ON Level.LevelId = COFilter.LevelId
LEFT JOIN ControlObjectiveCreatedByUserFactvw COCU
ON COCU.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
AND COCU.DeletedDate IS NULL
LEFT JOIN UserDimvw CreatedBy
ON CreatedBy.userDimKey = COCU.PolicyItemCreatedBy_UserDimKey
LEFT JOIN ControlObjectiveModifiedByUserFactvw COMU
ON COMU.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
AND COMU.DeletedDate IS NULL
LEFT JOIN UserDimvw ModifiedBy
ON ModifiedBy.userDimKey = COMU.PolicyItemModifiedBy_UserDimKey
LEFT JOIN ControlObjectiveAssignedToUserFactvw COA
ON COA.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
LEFT JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = COA.PolicyItemAssignedTo_UserDimKey
Where (0 = @AssignedTo OR COA.PolicyItemAssignedTo_UserDimKey = @AssignedTo)
UNION
SELECT DISTINCT COFilter.*,
AssignedTo.UserName As AssignedTo,
CreatedBy.UserName AS CreatedBy,
ModifiedBy.UserName AS ModifiedBy,
COType.COTypeValue,
Priority.PriorityValue,
Level.LevelValue
FROM (SELECT DISTINCT
CO.ControlObjectiveDimKey,
CO.Id,
CO.Title,
CO.COStatusValue,
CO.CreatedDate,
CO.ExternalId AS SourceId,
CO.ExternalName AS SourceName,
CO.ExternalVersion AS SourceVersion,
CO.LastModifiedDateTime AS ModifiedDate,
CO.UserName AS Owner,
CO.Type_COTypeId AS TypeId,
CO.Priority_PriorityId AS PriorityId,
CO.Level_LevelId AS LevelId,
CO.EntityDimKey
FROM (SELECT DISTINCT
COSD.ControlObjectiveDimKey,
CO.Id,
CO.Title as Title,
CS.COStatusId,
CS.COStatusValue,
CO.CreatedDate,
CO.ExternalId,
CSN.ComplianceSourceNameValue AS ExternalName,
CO.ExternalVersion,
COSD.LastModifiedDateTime,
COSD.StartDateTime,
COSD.FinishDateTime,
OwnedBy.UserName,
CO.Type_COTypeId,
CO.Priority_PriorityId,
CO.Level_LevelId,
CO.EntityDimKey
FROM ControlObjectiveStatusDurationFactvw COSD
JOIN ControlObjectiveDimvw CO
ON CO.ControlObjectiveDimKey = COSD.ControlObjectiveDimKey
JOIN #PHasCO AS PHasCODimKey
ON PHasCODimKey.ControlObjectiveDimKey = COSD.ControlObjectiveDimKey
JOIN COStatusvw CS
ON CS.COStatusId = COSD.COStatusId
AND (-1 = @StatusId OR CS.COStatusId = @StatusId)
LEFT JOIN ComplianceSourceNamevw CSN
ON CSN.ComplianceSourceNameId = CO.ExternalName_ComplianceSourceNameId
JOIN ControlObjectiveOwnedByUserFactvw COO
ON COO.ControlObjectiveDimKey = CO.ControlObjectiveDimKey
JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = COO.ConfigItemOwnedByUser_UserDimKey
AND (0 = @OwnedBy OR COO.ConfigItemOwnedByUser_UserDimKey = @OwnedBy)) AS CO
LEFT JOIN ControlObjectiveCitationFactvw COC
ON COC.ControlObjectiveDimKey = CO.ControlObjectiveDimKey
LEFT JOIN AuthorityDocumentHasCitationFactvw ADHC
ON ADHC.ComplianceDocumentHasCitation_AuthorityDocumentCitationDimKey = COC.ControlObjectiveCitation_AuthorityDocumentCitationDimKey
LEFT JOIN AuthorityDocumentDimvw AD
ON AD.AuthorityDocumentDimKey = ADHC.AuthorityDocumentDimKey
WHERE ((DATEDIFF(DAY, CO.LastModifiedDateTime, @StartDate) <= 0 AND DATEDIFF(DAY, CO.LastModifiedDateTime, @EndDate) >=0)
OR
(CO.FinishDateTime IS NOT NULL AND DATEDIFF(DAY, CO.FinishDateTime, @StartDate) <= 0 AND DATEDIFF(DAY, CO.FinishDateTime, @EndDate) >=0))
AND (0 = @ADValue OR AD.AuthorityDocumentDimKey = @ADValue OR AD.AuthorityDocumentDimKey IS NULL)
AND ((-1 IN (Select value from @tableADCategoryForCOList )) OR (AD.DocumentCategory_AuthorityDocumentCategoryId IN (Select value from @tableADCategoryForCOList)) OR AD.DocumentCategory_AuthorityDocumentCategoryId IS NULL)) AS COFilter
LEFT JOIN COType COType
ON COType.COTypeId = COFilter.TypeId
LEFT JOIN Priority Priority
ON Priority.PriorityId = COFilter.PriorityId
LEFT JOIN Level Level
ON Level.LevelId = COFilter.LevelId
LEFT JOIN ControlObjectiveCreatedByUserFactvw COCU
ON COCU.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
AND COCU.DeletedDate IS NULL
LEFT JOIN UserDimvw CreatedBy
ON CreatedBy.userDimKey = COCU.PolicyItemCreatedBy_UserDimKey
LEFT JOIN ControlObjectiveModifiedByUserFactvw COMU
ON COMU.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
AND COMU.DeletedDate IS NULL
LEFT JOIN UserDimvw ModifiedBy
ON ModifiedBy.userDimKey = COMU.PolicyItemModifiedBy_UserDimKey
LEFT JOIN ControlObjectiveAssignedToUserFactvw COA
ON COA.ControlObjectiveDimKey = COFilter.ControlObjectiveDimKey
LEFT JOIN UserDimvw AssignedTo
ON AssignedTo.UserDimKey = COA.PolicyItemAssignedTo_UserDimKey
Where (0 = @AssignedTo OR COA.PolicyItemAssignedTo_UserDimKey = @AssignedTo)
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetListOfCOs TO reportuser
GO
Home