

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/CSView/AllSubClientScheduledEntities.view] ---------- 

SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
SET ANSI_NULLS  ON
print '... Creating View: AllSubClientScheduledEntities'
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[AllSubClientScheduledEntities]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[AllSubClientScheduledEntities]
GO

delete from GXDBVersions where name = 'AllSubClientScheduledEntities' and type=4
insert into GXDBVersions  (type, name, revision, aliasname, SafeRevision) VALUES (4,'AllSubClientScheduledEntities', '1.1.2.3.68.2', 'AllSubClientScheduledEntities', '')
GO
CREATE VIEW AllSubClientScheduledEntities
AS
	-- filter schedule and task ids to minimum set
	WITH ScheduleIds (
		scheduleId,
		taskId
	) AS (
		SELECT DISTINCT
			STASK.subTaskid,
			TASK.taskId
		FROM TM_Task AS TASK WITH(READUNCOMMITTED)
		INNER JOIN TM_SubTask AS STASK WITH(READUNCOMMITTED)
			ON TASK.taskId = STASK.taskId
		INNER JOIN TM_SubTaskOptions AS SOPT WITH(READUNCOMMITTED)
			ON STASK.subTaskId = SOPT.subTaskId
		INNER JOIN TM_PatternAssoc AS PATASSOC WITH(READUNCOMMITTED)
			ON PATASSOC.subTaskId = STASK.subTaskId
		INNER JOIN TM_Pattern AS PAT WITH(READUNCOMMITTED)
			ON PAT.patternId = PATASSOC.patternId
		INNER JOIN TM_RunTime AS RT WITH(READUNCOMMITTED)
			ON RT.patternId = PAT.patternId
		INNER JOIN TM_RunTimeAssoc AS RTASSOC WITH(READUNCOMMITTED)
			ON RTASSOC.runTimeId = RT.runTimeId
		INNER JOIN TM_AssocEntity AS ASSOC WITH(READUNCOMMITTED)
			ON ASSOC.taskId = TASK.taskId
		WHERE
			STASK.subTaskType = 2
			AND SOPT.optionId = 458405394
			AND RTASSOC.processed = 0
			AND RTASSOC.firing = 0
			AND RT.processed = 0
			AND RT.firing = 0
			AND TASK.deleted = 0
			AND TASK.invalid = 0
			AND TASK.uninstalled = 0
	),
	-- filter applications down to minimum set
	 AppIds (
		id,
		clientId,
		appTypeId,
		instanceId,
		backupSetId
	) AS (
	SELECT
			a.id,
			a.clientId,
			a.appTypeId,
			a.instance,
			a.backupSet
		FROM APP_Application a WITH(READUNCOMMITTED)
		WHERE
			a.subclientStatus & (0x00010) = 0 --Dummy
			AND a.subclientStatus & (0x00020) = 0 --Hidden
			AND a.subclientStatus & (0x00004) = 0 --deleted
			AND a.subclientStatus & (0x00002) = 0 --uninstalled
			AND a.appTypeId <> 1000 --DR Subclient
	)
		-- compute all subclient schedules
		SELECT
			s.scheduleId,
			ae.subclientId subClientId
		FROM ScheduleIds s
			INNER JOIN TM_Associations AS ae WITH(READUNCOMMITTED) -- Use view to account for plan association too
				ON ae.taskId = s.taskId
			INNER JOIN AppIds a
				ON a.id = ae.subclientId
		WHERE
			ae.subclientId <> 0
		UNION
		SELECT
			s.scheduleId,
			a.id appId
		FROM ScheduleIds s
			INNER JOIN TM_AssocEntity AS ae WITH(READUNCOMMITTED)
				ON ae.taskId = s.taskId
				AND ae.subclientId = 0
			INNER JOIN APP_ClientGroupAssoc cga WITH(READUNCOMMITTED)
				ON (
					ae.assocType = 28
					AND ae.clientGroupId = cga.clientGroupId
				)
			LEFT JOIN AppIds a
				ON ae.exclude = 0
					AND cga.clientId = a.clientId
		WHERE
			a.id IS NOT NULL
		UNION
		SELECT
			s.scheduleId,
			a.id appId
		FROM ScheduleIds s
			INNER JOIN TM_AssocEntity AS ae WITH(READUNCOMMITTED)
				ON ae.taskId = s.taskId
				AND ae.subclientId = 0
			LEFT JOIN AppIds a
				ON ae.exclude = 0
					AND ae.clientId = a.clientId
					AND ae.assocType = 3
		WHERE
			a.id IS NOT NULL
		UNION
		SELECT
			s.scheduleId,
			a.id appId
		FROM ScheduleIds s
			INNER JOIN TM_AssocEntity AS ae WITH(READUNCOMMITTED)
				ON ae.taskId = s.taskId
				AND ae.subclientId = 0
			LEFT JOIN AppIds a
				ON ae.exclude = 0
					AND ae.clientId = a.clientId
					AND ae.appTypeId = a.appTypeId
					AND ae.assocType = 4
		WHERE
			a.id IS NOT NULL
		UNION
		SELECT
			s.scheduleId,
			a.id appId
		FROM ScheduleIds s
			INNER JOIN TM_AssocEntity AS ae WITH(READUNCOMMITTED)
				ON ae.taskId = s.taskId
				AND ae.subclientId = 0
			LEFT JOIN AppIds a
				ON ae.exclude = 0
					AND ae.instanceId = a.instanceId
					AND ae.clientId = a.clientId
					AND ae.appTypeId = a.appTypeId
					AND ae.assocType = 5
		WHERE
			a.id IS NOT NULL
		UNION
		SELECT
			s.scheduleId,
			a.id appId
		FROM ScheduleIds s
			INNER JOIN TM_AssocEntity AS ae WITH(READUNCOMMITTED)
				ON ae.taskId = s.taskId
				AND ae.subclientId = 0
			LEFT JOIN AppIds a
				ON ae.exclude = 0
					AND ae.subclientId = a.id
					AND ae.assocType = 7
		WHERE
			a.id IS NOT NULL
		UNION
		SELECT
			s.scheduleId,
			a.id appId
		FROM ScheduleIds s
			INNER JOIN TM_AssocEntity AS ae WITH(READUNCOMMITTED)
				ON ae.taskId = s.taskId
				AND ae.subclientId = 0
			LEFT JOIN AppIds a
				ON ae.exclude = 0
					AND ae.assocType = 2
		WHERE
			a.id IS NOT NULL
GO

declare @retCode integer		-- Now do Error Check

set @retCode = @@error
if (@retCode <> 0)
BEGIN
	set @retCode = @@error;
	print 'RETVAL: 10 View File [AllSubClientScheduledEntities]'
END
GO

