/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeOthers] Script Date: 03/29/2010 02:08:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeOthers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeOthers]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeOthers] Script Date: 03/29/2010 02:08:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MICROSOFT
-- Create date: 03-26-2010
-- Description: To get the list of services associated with Program
-- =============================================
IF OBJECT_ID(N'tempdb..#GroupHasItemsTemp', N'U') IS NOT NULL
BEGIN
DROP TABLE #GroupHasItemsTemp
END;
SELECT GC.configitemdimkey AS ConfigItemDimKey, EC.configitemdimkey AS Group_ConfigItemDimKey
INTO #GroupHasItemsTemp FROM
(SELECT CG.BaseManagedEntityId AS GroupEntity,E.BaseManagedEntityId as GroupConatinsEntity from ConfigItemGroupContainsEntitiesFactvw CGE
INNER JOIN ConfigItemGroupDimvw CG
ON CG.ConfigItemGroupDimKey = CGE.ConfigItemGroupDimKey
INNER JOIN EntityDimvw E
ON CGE.InstanceGroupContainsEntities_EntityDimKey = E.EntityDimKey
WHERE CGE.DeletedDate IS NULL) AS GroupHasEntities
LEFT JOIN ConfigItemDimvw GC on GroupHasEntities.GroupEntity = GC.BaseManagedEntityId
LEFT JOIN configitemdimvw EC on GroupHasEntities.GroupConatinsEntity = EC.BaseManagedEntityId;
WITH ProgramScopeFlatList (ProgramDimKey, ProgramScope_ConfigItemDimKey, CreatedDate)
AS
(
SELECT ProgramDimKey
,ProgramScope_ConfigItemDimKey AS ProgramScope_ConfigItemDimKey
,CreatedDate AS CreatedDate
FROM ProgramScopeFactvw
INNER JOIN ConfigItemDimvw C
ON C.ConfigItemDimKey = ProgramScope_ConfigItemDimKey
INNER JOIN
(
SELECT EntityDimKey from ServiceDimVw
UNION
SELECT EntityDimKey from ConfigItemGroupDim
)AS SG
ON C.EntityDimKey = SG.EntityDimKey
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId)
AND DeletedDate IS NULL
UNION ALL
SELECT ProgramDimKey
,CC.ProgramScope_ConfigItemDimKey
,CD.CreatedDate
FROM
(SELECT ConfigItemDimKey, ServiceHasGroups_ConfigItemDimKey AS ProgramScope_ConfigItemDimKey
FROM ServiceHasGroupsFactvw WHERE DeletedDate IS NULL
UNION ALL
SELECT ConfigItemDimKey, ConfigItemContainsConfigItem_ConfigItemDimKey AS ProgramScope_ConfigItemDimKey
FROM ConfigItemContainsConfigItemFactvw WHERE DeletedDate IS NULL
UNION ALL
SELECT ConfigItemDimKey, Group_ConfigItemDimKey AS ProgramScope_ConfigItemDimKey
FROM #GroupHasItemsTemp) AS CC
INNER JOIN ProgramScopeFlatList AS CD
ON CC.ConfigItemDimKey = CD.ProgramScope_ConfigItemDimKey
WHERE CC.ConfigItemDimKey IN
(SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ServiceDimvw S ON C.EntityDimKey = S.EntityDimKey
UNION ALL
SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ConfigItemGroupDimvw G ON C.BaseManagedEntityId = G.BaseManagedEntityId
UNION ALL
SELECT ServiceHasGroups_ConfigItemDimKey FROM ServiceHasGroupsFactvw
WHERE DeletedDate IS NULL)
)
SELECT ProgramDimKey, ProgramScope_ConfigItemDimKey, CreatedDate into #ProgramScopeTemp FROM ProgramScopeFlatList;
INSERT INTO #ProgramScopeFactvw
SELECT DISTINCT ProgramDimKey, ProgramScope_ConfigItemDimKey, CreatedDate FROM #ProgramScopeTemp
UNION
SELECT DISTINCT ProgramDimKey, ProgramScope_ConfigItemDimKey, CreatedDate FROM ProgramScopeFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId) AND DeletedDate IS NULL
DELETE FROM #ProgramScopeFactvw WHERE ProgramScope_ConfigItemDimKey IN
(SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ServiceDimvw S ON C.EntityDimKey = S.EntityDimKey
UNION
SELECT ServiceHasGroups_ConfigItemDimKey FROM ServiceHasGroupsFactvw
WHERE DeletedDate IS NULL
UNION
SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ConfigItemGroupDimvw G ON C.BaseManagedEntityId = G.BaseManagedEntityId)
SELECT DISTINCT
CID.DisplayName,
UDO.DisplayName AS OwnedBy,
UDS.DisplayName AS ServicedBy
FROM #ProgramScopeFactvw PSF
JOIN ConfigItemDimvw CID
ON PSF.ProgramScope_ConfigItemDimKey = CID.ConfigItemDimKey
AND CID.IsDeleted = 0
LEFT OUTER JOIN
ConfigItemOwnedByUserFactvw COU
ON CID.ConfigItemDimKey = COU.ConfigItemDimKey
AND COU.DeletedDate IS NULL
LEFT OUTER JOIN
UserDimvw UDO
ON UDO.UserDimKey = COU.ConfigItemOwnedByUser_UserDimKey
AND UDO.IsDeleted = 0
LEFT OUTER JOIN
ConfigItemServicedByUserFactvw CSU
ON CID.ConfigItemDimKey = CSU.ConfigItemDimKey
AND CSU.DeletedDate IS NULL
LEFT OUTER JOIN
UserDimvw UDS
ON UDS.UserDimKey = CSU.ConfigItemServicedByUser_UserDimKey
AND UDS.IsDeleted = 0
LEFT JOIN ComputerDim C
ON CID.EntityDimKey = C.EntityDimKey
AND C.IsDeleted = 0
WHERE ProgramDimKey = @ProgramId
AND (DATEDIFF(DAY, PSF.CreatedDate, @StartDate) <= 0 AND DATEDIFF(DAY, PSF.CreatedDate, @EndDate) >=0)
AND C.EntityDimKey IS NULL
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeOthers TO reportuser
GO
Home