

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/TM_GetAdminEntityCount.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/TM_GetAdminEntityCount.sp,v $ $Id: TM_GetAdminEntityCount.sp,v 1.2.2.40.8.3 2021/06/10 00:37:22 anguyen Exp $";
-- =============================================
-- Author:		Sergio Bonilla
-- Create date: 03/28/2010
-- Description:	Get entities on which job is going to run
-- =============================================
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='TM_GetAdminEntityCount')
BEGIN
	print '>>> Drop Stored Procedure: TM_GetAdminEntityCount <<<'
	drop procedure TM_GetAdminEntityCount
END
IF EXISTS (select * from GxQscripts where name='TM_GetAdminEntityCount')
	delete from GxQscripts where name = 'TM_GetAdminEntityCount'
GO

IF EXISTS (select * from GXDBVersions where aliasname='TM_GetAdminEntityCount')
	delete from GXDBVersions where aliasname = 'TM_GetAdminEntityCount'
GO
print '... Creating Procedure: TM_GetAdminEntityCount'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure TM_GetAdminEntityCount
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
--
--   PARAMETERS   &   OUTPUTS
--
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  @i_taskId integer,
  @i_subtaskId integer,
  @o_count integer OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON
BEGIN TRY
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	DECLARE @taskType INT = 0
	DECLARE @operationType INT = (SELECT operationType FROM TM_SubTask WHERE subTaskId = @i_subtaskId)
	DECLARE @i_timeZoneName nvarchar(100) = null
	DECLARE @i_initFrom INT = 0
	DECLARE @i_runTaskOptions XML = NULL
	DECLARE @policyType INT = 0
	DECLARE @isEdgeDrivePolicy INT = 0
	DECLARE @isDefaultDataAnalyticsSched INT = 0
	DECLARE @initiatedFrom INT = 0
	DECLARE @i_jobStartTime      INTEGER = NULL
	DECLARE @i_assocsXML XML = NULL
	DECLARE @isSchedulePolicyAssociatedToPlan INTEGER = 0
	DECLARE @freqInterval	INT = 0
	DECLARE @nowUtcTime DATETIME = GETUTCDATE()
	DECLARE @nowUtcUnix INT = datediff(second, '01/01/1970', @nowUtcTime)
	DECLARE @o_errorString NVARCHAR(MAX) = ''
	DECLARE @ownerId INT = 0
	SELECT
		@taskType = taskType,
		@policyType = policyType,
		@initiatedFrom = initiatedFrom,
		@isEdgeDrivePolicy = CASE
WHEN (flags & 0x40) <> 0		-- TASK_FLAGS_EDGE_DRIVE
									THEN 1
								ELSE 0
							END,
		@isDefaultDataAnalyticsSched = CASE
WHEN (flags & 0x10000 ) <> 0		-- TASK_FLAGS_DATA_ANALYTICS
									THEN 1
								ELSE 0
							END,
        @ownerId = ownerId
	FROM TM_Task WITH (NOLOCK)
	WHERE taskId = @i_taskId
IF (@i_initFrom NOT IN (1, 2))
	BEGIN
		SELECT @freqInterval = (P.freq_interval*60) FROM TM_PatternAssoc (NOLOCK) PA
JOIN TM_Pattern (NOLOCK) P ON P.patternId = PA.patternId AND PA.subTaskId = @i_subtaskId AND P.freq_type = 4096
	END
--- CODE BELOW HERE IS FROM TM_AppIdTblCreate.spb  ----------
		-------------------------------
		--CREATE TEMP TABLE------------
		-------------------------------
		-- None of these temp tables should ever exist when this procedure is called!
		if object_id('tempdb.dbo.#appIdTbl') is not null
    		DROP TABLE #appIdTbl
		CREATE TABLE #appIdTbl (
			appId INT,
			appTypeId INT,
			clientId INT,
			instanceId INT,
			backupsetId INT,
			STATUS INT,
			bkpLevel INT,
			storagePolicy INT,
timezoneName nvarchar(255) DEFAULT '',
			ignoreHigherLevelOpWindowRules	INT DEFAULT 0,
			jmOperationType INT DEFAULT 0,
			taskID			INT DEFAULT 0,
			parentJobId int DEFAULT 0,
			parentAppID int DEFAULT 0,
			vmDiscovered INT DEFAULT 0,
			utcdateTime     DATETIME DEFAULT '1970-01-01 00:00:00.000', -- Needed for Predict SLA project where we need to identify if backup time is outside operation window.
			stID		INT DEFAULT 0
		)
		CREATE CLUSTERED INDEX appIdTbl_idx ON #appIdTbl (appId)
		CREATE NONCLUSTERED INDEX appIdTbl_clientId_idx ON #appIdTbl ([clientId])
		CREATE NONCLUSTERED INDEX appIdTbl_appTypeId_idx ON #appIdTbl ([appTypeId])
		CREATE NONCLUSTERED INDEX appIdTbl_timezoneName_idx ON #appIdTbl ([timezoneName])
--- CODE ABOVE HERE IS FROM TM_AppIdTblCreate.spb  ----------
IF ( (@operationType = 5020 OR @operationType = 5023 )
AND ( @taskType = 4 OR @taskType = 2 ) )
	BEGIN
--- CODE BELOW HERE IS FROM TM_GetBackupEntityInclude.spb  ----------
		SELECT @operationType = operationType
		FROM TM_SubTask WITH (NOLOCK)
		WHERE subTaskId = @i_subtaskId
		DECLARE @autoCopy INT = 0
		DECLARE @fanInRP INT = 0
		DECLARE @autoSyntheticDay INT = 0
DECLARE @backupLevel INT = 2	-- BACKUP_LEVEL_INCREMENTAL
		DECLARE @enableRunConsolidateFull	INT = 0
		SELECT
			@autoCopy = CASE optionId
WHEN 1013376701 THEN value		-- DATAOPT_AUTO_COPY
							ELSE @autoCopy
						END,
			@fanInRP = CASE optionId
WHEN 433543038 THEN value		-- CDROPTIONS_VOL_RECOVERY_POINT_CREATION
							ELSE @fanInRP
						END,
			@autoSyntheticDay = CASE optionId
WHEN 1778633837 THEN value		-- DATABKPOTION_USE_AUTOMATIC_INTERVAL_FOR_SYNTHETIC_FULL
							ELSE @autoSyntheticDay
						END,
			@backupLevel = CASE optionId
WHEN 458405394 THEN value		-- BACKUPOPTION_BACKUP_LEVEL
							ELSE @backupLevel
						END,
			@enableRunConsolidateFull = CASE optionId
WHEN 328367274 THEN value		-- DATABKPOTION_ENABLE_RUN_FULL_CONSOLIDATION_BACKUP
							ELSE @enableRunConsolidateFull
						END
		FROM TM_SubTaskOptions WITH (NOLOCK)
		WHERE subTaskId = @i_subtaskId
AND optionId IN (1013376701, 433543038, 458405394, 1778633837, 328367274)	-- DATAOPT_AUTO_COPY, CDROPTIONS_VOL_RECOVERY_POINT_CREATION, BACKUPOPTION_BACKUP_LEVEL, DATABKPOTION_USE_AUTOMATIC_INTERVAL_FOR_SYNTHETIC_FULL, DATABKPOTION_ENABLE_RUN_FULL_CONSOLIDATION_BACKUP
		if object_id('tempdb.dbo.#runTaskOptions') is not null
    		DROP TABLE #runTaskOptions
		CREATE TABLE #runTaskOptions (
			optionId BIGINT,
			value NVARCHAR(MAX)
		)
		CREATE CLUSTERED INDEX runTaskOptions_idx ON #runTaskOptions (optionId)
		IF object_id('tempdb.dbo.#App_VmClientInfoTable') is not null
			DROP TABLE #App_VmClientInfoTable
		CREATE TABLE #App_VmClientInfoTable (vmClientId INT, parentBackupsetId INT, childBackupsetId INT, parentSubclientId INT, v2_1_Client INT DEFAULT 0)
		CREATE CLUSTERED INDEX App_VmClientInfoTable_vmClientId_Index1 ON #App_VmClientInfoTable ([vmClientId])
		IF object_id('tempdb.dbo.#App_InputAssocs') is not null
			DROP TABLE #App_InputAssocs
		CREATE TABLE #App_InputAssocs (
			appId INT,
			appTypeId INT,
			instanceId INT,
			backupsetId INT,
			clientId INT
		)
		CREATE CLUSTERED INDEX App_InputAssocs_idx ON #App_InputAssocs (appId)
		CREATE NONCLUSTERED INDEX App_InputAssocs_clientId_idx ON #App_InputAssocs ([clientId])
		IF object_id('tempdb.dbo.#TM_SyntheticInfoTable') is not null
			DROP TABLE #TM_SyntheticInfoTable
		IF object_id('tempdb.dbo.#App_SubclientInfoTable') is not null
			DROP TABLE #App_SubclientInfoTable
		CREATE TABLE #TM_SyntheticInfoTable (subclientId INT, backupsetId INT, instanceId INT, appTypeId INT, clientId INT, lastSynthetic INT DEFAULT 0, lastFull INT DEFAULT 0, lastIncremental INT DEFAULT 0, bkpLevel INT, addToRequesTable INT DEFAULT 1, status INT DEFAULT 0, syntheticInterval INT, bkpStatus INT DEFAULT 0,parentJobId INT DEFAULT 0,parentAppId INT DEFAULT 0)
		CREATE CLUSTERED INDEX TM_SyntheticInfoTable_subclientId_subTaskId_addToRequesTable_bkpStatus_Index1 ON #TM_SyntheticInfoTable ([subclientId], [addToRequesTable], [bkpStatus])
		CREATE TABLE #App_SubclientInfoTable (subclientId INT, FullRefTime INT, IncrRefTime INT, TurboFullRefTime INT, TurboIncrRefTime INT, IsTurbo INT, RunSFImmediately INT DEFAULT NULL, ExtendedRetentionDaysForAutomaticSchedule INT DEFAULT NULL)
		CREATE CLUSTERED INDEX App_SubclientInfoTable_subclientId_Index1 ON #App_SubclientInfoTable ([subclientId])
		IF object_id('tempdb.dbo.#deletedAppIdsErrorCode') is not null
			DROP TABLE #deletedAppIdsErrorCode
		CREATE TABLE #deletedAppIdsErrorCode(appId INT, errorCode INT, errorMessage NVARCHAR(1024))
		CREATE CLUSTERED INDEX deletedAppIdsErrorCode_appId_ErrorCode_Idx ON #deletedAppIdsErrorCode(appId,errorCode)
		IF object_id('tempdb.dbo.#deletedVMAppIds') is not null
			DROP TABLE #deletedVMAppIds
		CREATE TABLE #deletedVMAppIds(appId INT, clientId INT,parentJobId int DEFAULT 0,parentAppID int DEFAULT 0, errorCode INT, errorMessage NVARCHAR(1024))
		CREATE CLUSTERED INDEX deletedVMAppIds_appId_Idx ON #deletedVMAppIds(appId)
		if object_id('tempdb.dbo.#cloudclientsTbl') is not null
    		DROP TABLE #cloudclientsTbl
		CREATE TABLE #cloudclientsTbl (clientId INT)
		CREATE CLUSTERED INDEX cloudclientsTbl_clientId_idx ON #cloudclientsTbl (clientId)
		IF @i_runTaskOptions IS NOT NULL
		BEGIN
			INSERT INTO #runTaskOptions(optionId,value)
			SELECT  ref.value('@optionId', 'BIGINT') AS Agent,
				ref.value('@value', 'NVARCHAR(MAX)') AS Agent
			FROM    @i_runTaskOptions.nodes('TMMsg_RunTaskOptions/options') R ( ref )
		END
		IF @i_assocsXML IS NOT NULL
		BEGIN
			INSERT INTO #App_InputAssocs
			SELECT DISTINCT
				ISNULL(Tbl.Col.value('@subclientId', 'INTEGER'),0),
				ISNULL(Tbl.Col.value('@applicationId', 'INTEGER'),0),
				ISNULL(Tbl.Col.value('@instanceId', 'INTEGER'),0),
				ISNULL(Tbl.Col.value('@backupsetId', 'INTEGER'),0),
				CASE WHEN Tbl.Col.value('@clientGroupId', 'INTEGER') > 0 AND Tbl.Col.value('@clientId', 'INTEGER') = 0 THEN
					ACG.clientId
				ELSE Tbl.Col.value('@clientId', 'INTEGER')
				END
			FROM @i_assocsXML.nodes('App_Associations/entity') as Tbl(Col)
			LEFT JOIN APP_ClientGroupAssoc ACG ON
				ACG.clientGroupId = Tbl.Col.value('@clientGroupId', 'INTEGER')
		END
		----------------------------------
		--GET ALL ASSOCIATED CLIENTS------
		----------------------------------
		INSERT INTO #appIdTbl
		(appId, appTypeId, clientId, instanceId, backupsetId, STATUS, bkpLevel, storagePolicy, taskId)
			--CLIENT GROUP
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
				INNER JOIN APP_ClientGroupAssoc CGA ON
					APP.clientId = CGA.clientId
				INNER JOIN TM_AssocEntity AE ON
					AE.clientId = 0
					AND AE.exclude = 0
					AND CGA.clientGroupId = AE.clientGroupId
					AND AE.taskId = @i_taskId
					AND AE.entityId = 0
			--ALL APPID WITH SAME GROUPID
			UNION ALL
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
				INNER JOIN APP_AppTypeGroupAssoc ATGA ON
					APP.appNumber = ATGA.appTypeId
				INNER JOIN TM_AssocFilter AF ON
					ATGA.appGroupId = AF.filter_value
					AND AF.filter_type = 1
					AND AF.taskId = @i_taskId
				INNER JOIN TM_AssocEntity AE ON
					AF.taskId = AE.taskId
					AND AE.clientGroupId = 0
					AND AE.clientId = 0
					AND AE.appTypeId = 0
					AND AE.instanceId = 0
					AND AE.backupsetId = 0
					AND AE.subClientId = 0
					AND AE.entityId = 0
			--ALL APPID WITH SAME APPTYPES
			UNION ALL
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
				INNER JOIN APP_iDAType IT ON
					APP.appTypeId = IT.type
				INNER JOIN TM_AssocFilter AF ON
					IT.type = AF.filter_value
					AND AF.filter_type = 2
					AND AF.taskId = @i_taskId
					AND AF.taskId IN (
						SELECT taskId
						FROM TM_AssocEntity
						WHERE taskId = @i_taskId
							AND clientGroupId = 0
							AND clientId = 0
							AND appTypeId = 0
							AND instanceId = 0
							AND backupsetId = 0
							AND subClientId = 0
							AND entityId = 0
						)
			UNION ALL
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP		-- corrected full outer join error
				INNER JOIN APP_iDAType IT ON
					APP.appTypeId = IT.type
				LEFT OUTER JOIN TM_AssocFilter AF ON
					IT.type = AF.filter_value
					AND AF.filter_type = 2
					AND AF.taskId = @i_taskId
			WHERE
				AF.taskId IS NULL
				AND EXISTS (
					SELECT taskId
					FROM TM_AssocEntity
					WHERE taskId = @i_taskId
						AND clientGroupId = 0
						AND clientId = 0
						AND appTypeId = 0
						AND instanceId = 0
						AND backupsetId = 0
						AND subClientId = 0
						AND entityId = 0
					)
			--ALL APPID WITH APPTYPEID = 0
			UNION ALL
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
			INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND TM.appTypeId = 0
					AND taskId = @i_taskId
					AND exclude = 0
					AND entityId = 0
			--ALL APPID WITH SAME APPTYPE AND CLIENTID = 0
			UNION ALL
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
			WHERE appTypeId IN (
					SELECT DISTINCT (appTypeId)
					FROM TM_AssocEntity
					WHERE clientid = 0
						AND taskId = @i_taskId
						AND exclude = 0
						AND entityId = 0
					)
			--ALL APPID WITH INSTANCE = 0
			UNION ALL
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
				INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND APP.appTypeId = TM.appTypeId
					AND TM.instanceId = 0
					AND taskId = @i_taskId
					AND exclude = 0
					AND entityId = 0
			--ALL APPID WITH BACKUPSET = 0
			UNION ALL
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
			INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND APP.appTypeId = TM.appTypeId
					AND APP.instance = TM.instanceId
					AND TM.backupsetId = 0
					AND taskId = @i_taskId
					AND exclude = 0
					AND entityId = 0
			--ALL APPID WITH SUBCLIENT = 0
			UNION ALL
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
			INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND APP.appTypeId = TM.appTypeId
					AND APP.instance = TM.instanceId
					AND APP.backupset = TM.backupsetId
					AND TM.subclientId = 0
					AND taskId = @i_taskId
					AND exclude = 0
					AND entityId = 0
			--ALL APPID
			UNION ALL
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
			INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND APP.appTypeId = TM.appTypeId
					AND APP.instance = TM.instanceId
					AND APP.backupset = TM.backupsetId
					AND TM.subclientId = APP.Id
					AND taskId = @i_taskId
					AND exclude = 0
					AND entityId = 0
			-- For plan associated schedule subclients
			UNION
			SELECT APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				@backupLevel,
				APP.dataArchGrpID,
				@i_taskId
			FROM APP_Application AS APP
			INNER JOIN APP_SubClientProp SCP WITH(NOLOCK) ON SCP.componentNameId = APP.id
			INNER JOIN TM_AssocEntity AE WITH(NOLOCK) ON CAST(AE.entityId AS NVARCHAR(1024)) = SCP.attrVal AND AE.assocType = 158
			WHERE SCP.attrName = 'Associated Plan' AND SCP.modified = 0 AND SCP.cs_attrName = CHECKSUM(N'Associated Plan')
			AND AE.taskId = @i_taskId
			AND AE.exclude = 0
			AND AE.entityId > 0
		--Moved synthetic full for VSA clients to TM_GetBackupEntityFilter after job activity check
		--to avoid starting jobs for VSA subclient with activity disabled
--- CODE ABOVE HERE IS FROM TM_GetBackupEntityInclude.spb  ----------
--- CODE BELOW HERE IS FROM TM_GetBackupEntityExclude.spb  ----------
    ------------------------------------------------------
	--REMOVE CLIENTS FOR WHICH CLOUD LAPTOP IS ENABLED
    ------------------------------------------------------
	DECLARE @temp INTEGER;
	--ALLOW SYNTHETIC FULL JOBS FOR CL CLIENTS
IF @backupLevel <> 4
	BEGIN
	--Get the client for which only one package is being pushed
	WITH ClientWithPackageInstall_CTE(ClientID,attrName,NoOfPackage)
	AS
	(
		SELECT componentNameId,attrName,COUNT(*) AS NoOfPackages FROM APP_ClientProp WITH(NOLOCK)
		WHERE attrName = 'PackagesToInstall' AND modified = 0
		GROUP BY componentNameId,attrName
		HAVING COUNT(*) = 1
	)
	-- No need to delete from subclients list. We need to trigger workqueue for such clients to kick of backup
	INSERT INTO #cloudclientsTbl
		SELECT ClientID
		FROM CommCellCloudLaptopClients
		UNION
		--Get the clients where package is being installed
		SELECT DISTINCT componentNameId AS ClientID FROM APP_ClientProp cProp WITH(NOLOCK) INNER JOIN ClientWithPackageInstall_CTE packageInstall
		ON cProp.componentNameId = packageInstall.ClientID AND cProp.attrName = 'PackagesToInstall' AND cProp.modified = 0 AND cProp.attrVal IN ('54','1305')
		INNER JOIN APP_ClientGroupAssoc cgAssoc WITH(NOLOCK) ON cgAssoc.clientId = cProp.componentNameId
	END
    ---------------------------
	--EXCLUSION FOR TIMEZONE---
	---------------------------
	-- Sometimes unix clients have timezone names in app_clientprop (timezone attribute) which do not match windows timezone list. So they do not get backed up in schedules using client timezone. So converting them to null below
	-- so that they can run in CS timezone
	IF(@i_timeZoneName <> '')
	begin
		UPDATE #appIdTbl
		SET timezoneName = tzName
		FROM
		(
			SELECT dbo.GetClientTimeZone(clientId) tzName, clientId cl
			FROM
			(
				SELECT DISTINCT clientId
				FROM #appIdTbl
			) C
		) T
		WHERE clientId = cl
IF EXISTS (SELECT 1 FROM #appIdTbl WHERE appTypeId IN (106))
		BEGIN
			;WITH CTE_Timezone
			AS
			(
				SELECT DISTINCT
					CASE
						WHEN tz.TimeZoneId IS NOT NULL THEN tz.TimeZoneStdName
						WHEN P.componentNameId IS NOT NULL THEN RIGHT(P.attrVal, CHARINDEX(':', REVERSE(P.attrVal)) - 1)
						ELSE NULL
					END tzName,
				appId subclientId
				FROM #appIdTbl E
JOIN APP_Application A ON E.apptypeId = 106 AND A.id = E.appId
				LEFT OUTER JOIN APP_SubClientProp P ON P.componentNameId = A.id AND P.attrName = N'timezone' AND P.modified = 0 AND P.cs_attrName = CHECKSUM(N'timezone')
				LEFT OUTER JOIN APP_SubClientProp P2 ON P2.componentNameId = A.id AND P2.attrName = N'timezone id'AND P2.modified = 0 AND P2.cs_attrName = CHECKSUM(N'timezone id')
				LEFT OUTER JOIN SchedTimeZone tz ON P2.attrVal IS NOT NULL AND P2.attrVal = CAST(tz.TimeZoneID AS VARCHAR(12))
WHERE E.appTypeId = (106)
			)
			UPDATE #appIdTbl
			SET timezoneName = CTE_Timezone.tzName
			FROM CTE_Timezone
WHERE appTypeId IN (106)
			AND CTE_Timezone.subclientId = appId
			AND CTE_Timezone.tzName IS NOT NULL
		END
		-- Set timezone name to null if its not in standard timezone list.
		update #appIdTbl
		set timezoneName = null
		from #appIdTbl clientTimezoneTbl
left join SchedTimeZone on SchedTimeZone.TimeZoneStdName=clientTimezoneTbl.timezoneName and (SchedTimeZone.Flags & CONVERT(int, 0x1)) <> 0x1
		where SchedTimeZone.TimeZoneStdName is null
	end
	-- For patterns with client timezone set, Only if timezone is CS timezone, then we consider clients without any timezone set. Else we remove them.
	IF(@i_timeZoneName <> '' and (@i_timeZoneName<>DBO.GetClientTimeZone(2)))
		DELETE appIdTbl FROM #appIdTbl appIdTbl
		WHERE timezoneName is null
	IF(@i_timeZoneName <> '')
		DELETE appIdTbl FROM #appIdTbl appIdTbl
		WHERE timezoneName <>  @i_timeZoneName
	IF(@i_jobStartTime IS NOT NULL )
	BEGIN
		-- Schedule policy is not associated to plan. But still runtime assoc has job start time set. So we can remove all entries from appIDTbl
		IF @isSchedulePolicyAssociatedToPlan = 0 AND @i_jobStartTime!= -1
		BEGIN
			DELETE FROM #appIdTbl
		END
		ELSE
		BEGIN
			DECLARE @patternID INT = 0
			DECLARE @freqType INT = 0
			DECLARE @tzID INT = 0
			SELECT @patternID = TM_Pattern.patternID, @freqType = TM_Pattern.freq_Type, @tzID = TM_Pattern.tzID
			FROM TM_PatternAssoc  (NOLOCK)
			INNER JOIN TM_Pattern ON TM_Pattern.PatternID = TM_PatternAssoc.PatternID
			AND TM_PatternAssoc.subtaskID = @i_subTaskId
			-- Do below for schedules and schedule policies only.
			IF (@taskType IN (2,4) AND @tzID = 1001 AND @freqType IN (4,8,16,32,64,128,2048) ) -- Daily,Weekly, Monthly, Monthly_Relative,Yearly , Yearly_Relative, Every
			BEGIN
-- -- SP BODY BEGIN : TM_ComputeStartTimeForClientTbl.spb ----------------------------------------------------------------------------------------------------------------------------------
IF object_id('tempdb.dbo.#Clients_Timezone_StartTime') is NOT NULL
BEGIN
	DROP TABLE #Clients_Timezone_StartTime
END
CREATE TABLE #Clients_Timezone_StartTime(clientId INT, timezoneName NVARCHAR(MAX), jobStartTime INT DEFAULT -1, taskID INT DEFAULT 0, patternID INT DEFAULT 0 )
CREATE CLUSTERED INDEX Clients_Timezone_StartTime_clientId_taskID_Index ON #Clients_Timezone_StartTime (clientId, taskID)
IF object_id('tempdb.dbo.#SubClients_Timezone_StartTime') is NOT NULL
BEGIN
	DROP TABLE #SubClients_Timezone_StartTime
END
CREATE TABLE #SubClients_Timezone_StartTime(appId INT, timezoneName NVARCHAR(MAX), jobStartTime INT DEFAULT -1, taskID INT DEFAULT 0)
CREATE CLUSTERED INDEX SubClients_Timezone_StartTime_appId_Index ON #SubClients_Timezone_StartTime (appId)
-- -- SP BODY END : TM_ComputeStartTimeForClientTbl.spb ------------------------------------------------------------------------------------------------------------------------------------
				INSERT INTO #Clients_Timezone_StartTime(clientId , timezoneName , jobStartTime , taskID , patternID )
				SELECT DISTINCT clientId, NULL, -1, @i_taskId, @patternID
				FROM #appIdTbl
				IF (@isSchedulePolicyAssociatedToPlan>0)
				BEGIN
					INSERT INTO #SubClients_Timezone_StartTime(appId, timezoneName, taskID)
					SELECT DISTINCT appId, NULL, @i_taskId
					FROM #appIdTbl
				END
				-- Call stored procedure to compute timezone and start time for clients.
				EXEC TM_ComputeStarttimeForClient
				-- Remove subclients where job start time doesnt match.
				DELETE appIdTbl FROM #appIdTbl appIdTbl
				INNER JOIN #SubClients_Timezone_StartTime allSubClientsTable
				ON appIdTbl.appId = allSubClientsTable.appId
				AND allSubClientsTable.jobStartTime <> @i_jobStartTime
				AND allSubClientsTable.jobStartTime IS NOT NULL
				AND allSubClientsTable.jobStartTime >= 0
				-- If job start time is set at subclient level honour that alone. Else look at start time set at client level.
				DELETE appIdTbl FROM #appIdTbl appIdTbl
				INNER JOIN #Clients_Timezone_StartTime allClientsTable
				ON appIdTbl.clientID = allClientsTable.clientID
				AND allClientsTable.jobStartTime <> @i_jobStartTime
				LEFT JOIN #SubClients_Timezone_StartTime allSubClientsTable
				ON appIdTbl.appId = allSubClientsTable.appId
				AND allSubClientsTable.jobStartTime IS NOT NULL
				AND allSubClientsTable.jobStartTime >= 0
				WHERE allSubClientsTable.appId IS NULL
			END
		END
	END
	---------------------------
	--EXCLUSION----------------
	---------------------------
	DELETE FROM #appIdTbl
	WHERE appId IN (
			--CLIENT GROUP
			SELECT APP.id
			FROM APP_Application AS APP
			WHERE clientId IN (
					SELECT DISTINCT (clientId)
					FROM APP_ClientGroupAssoc
					WHERE clientgroupid IN (
							SELECT clientGroupId
							FROM TM_AssocEntity
							WHERE taskId = @i_taskId
								AND clientId = 0
								AND exclude = 1
							)
					)
			--ALL APPID WITH APPTYPEID = 0
			UNION ALL
			SELECT APP.id
			FROM APP_Application AS APP
				INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND TM.appTypeId = 0
					AND taskId = @i_taskId
					AND exclude = 1
			--ALL APPID WITH SAME APPTYPE AND CLIENTID = 0
			UNION ALL
			SELECT APP.id
			FROM APP_Application AS APP
			WHERE appTypeId IN (
					SELECT DISTINCT (appTypeId)
					FROM TM_AssocEntity
					WHERE clientid = 0
						AND taskId = @i_taskId
						AND exclude = 1
					)
			--ALL APPID WITH INSTANCE = 0
			UNION ALL
			SELECT APP.id
			FROM APP_Application AS APP
				INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND APP.appTypeId = TM.appTypeId
					AND TM.instanceId = 0
					AND taskId = @i_taskId
					AND exclude = 1
			--ALL APPID WITH BACKUPSET = 0
			UNION ALL
			SELECT APP.id
			FROM APP_Application AS APP
				INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND APP.appTypeId = TM.appTypeId
					AND APP.instance = TM.instanceId
					AND TM.backupsetId = 0
					AND taskId = @i_taskId
					AND exclude = 1
			--ALL APPID WITH SUBCLIENT = 0
			UNION ALL
			SELECT APP.id
			FROM APP_Application AS APP
				INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND APP.appTypeId = TM.appTypeId
					AND APP.instance = TM.instanceId
					AND APP.backupset = TM.backupsetId
					AND TM.subclientId = 0
					AND taskId = @i_taskId
					AND exclude = 1
			--ALL APPID
			UNION ALL
			SELECT APP.id
			FROM APP_Application AS APP
				INNER JOIN TM_AssocEntity AS TM ON
					APP.clientId = TM.clientId
					AND APP.appTypeId = TM.appTypeId
					AND APP.instance = TM.instanceId
					AND APP.backupset = TM.backupsetId
					AND TM.subclientId = APP.Id
					AND taskId = @i_taskId
					AND exclude = 1
			UNION ALL
			SELECT DISTINCT componentNameId
			FROM TM_Task task WITH (NOLOCK)
				JOIN TM_AssocEntity TM WITH (NOLOCK) ON
(TASK.flags & 128) = 128 AND @i_initFrom NOT IN (1, 2)
					AND TM.taskId = TASK.taskId and
					@i_taskId <> task.taskId
				JOIN APP_SubClientProp APROP WITH (NOLOCK) ON
					TM.subclientId = APROP.componentNameId
					AND APROP.cs_attrName = CHECKSUM(N'Index SubClient')
					AND (APROP.attrName = 'Index SubClient' and APROP.attrVal = 1)
			-- Index backups should only start via system created index backup schedule
			UNION ALL
			-- For case manager index copy jobs, support only case manager clients.
			SELECT APP.id
			FROM APP_Application AS APP
			INNER JOIN #appIdTbl  appIdTbl ON
			appIdTbl.appId = APP.id
				LEFT JOIN App_ClientProp ON
					APP.clientId = App_ClientProp.ComponentNameId
					AND App_ClientProp.attrName = 'Case Manager Pseudo Client'  --- CV_PROP_CASE_MANAGER_PSEUDO_CLIENT = "Case Manager Pseudo Client";
WHERE @operationType = 5023
			AND (App_ClientProp.attrVal IS NULL OR App_ClientProp.attrVal = '0')
			UNION ALL
			-- for plan schedule policy dont include case manager subclients
			SELECT appIdTbl.appid
			FROM #appIdTbl appIdTbl
				INNER JOIN App_ClientProp ON
					appIdTbl.clientId = App_ClientProp.ComponentNameId
					AND App_ClientProp.attrName = 'Case Manager Pseudo Client'  --- CV_PROP_CASE_MANAGER_PSEUDO_CLIENT = "Case Manager Pseudo Client";
					AND App_ClientProp.attrVal = '1'
WHERE @operationType != 5023
			AND @isSchedulePolicyAssociatedToPlan = 1
		)
--- CODE ABOVE HERE IS FROM TM_GetBackupEntityExclude.spb  ----------
		SELECT @o_count = COUNT(appid) FROM #appIdTbl
	END
	ELSE
	BEGIN
--- CODE BELOW HERE IS FROM TM_GetAdminEntity.spb  ----------
	DECLARE @ddbVerificationLevel NVARCHAR(MAX) = '0'
	DECLARE @isDataAnalytics2Job INT = 0
	DECLARE @onlineCrawlJobOption NVARCHAR(32) = '0'
	DECLARE @isOnlineCrawlJob INT = 0
	DECLARE @indexServerClientIDOpt INT = 0
	DECLARE @subClientBasedAnalytics INT = 0
	DECLARE @excludeStatus INT = 54 -- (CV_STATUS_UNINSTALLED | CV_STATUS_DELETED | CV_STATUS_DUMMY | CV_STATUS_HIDDEN)
	DECLARE @errorCode INT
	DECLARE @errorMsg VARCHAR(2000)
	DECLARE @i_useScalableResourceManagement INT = 0
	SELECT
			@ddbVerificationLevel = CASE optionId
WHEN 1616894445 THEN value		-- ARCHIVECHECKOPTION_DDB_VERIFICATION_LEVEL
							ELSE @ddbVerificationLevel
						END,
			@subClientBasedAnalytics = CASE optionId
WHEN 300510029 THEN value		-- CONTENTINDEXINGOPTION_SUB_CLIENT_BASED_ANALYTICS
							ELSE @subClientBasedAnalytics
						END,
			@i_useScalableResourceManagement = CASE optionId
WHEN 449788653 THEN value		-- AUXCOPYOPTION_USE_SCALLABLE_RESOURCE_MANAGEMENT
							ELSE @i_useScalableResourceManagement
						END,
			@onlineCrawlJobOption = CASE optionId
WHEN 136250708 THEN value		-- CONTENTINDEXINGOPTION_ONLINE_CRAWL_JOB
							ELSE @onlineCrawlJobOption
						END,
			@indexServerClientIDOpt = CASE optionId
WHEN 2101836446 THEN value		--  INDEXSERVER_CLIENT_ID
							ELSE @indexServerClientIDOpt
						END
		FROM TM_SubTaskOptions WITH (NOLOCK)
		WHERE subTaskId = @i_subtaskId
AND optionId IN (1616894445,300510029,
449788653,136250708,2101836446 )
	-- ARCHIVECHECKOPTION_DDB_VERIFICATION_LEVEL, CONTENTINDEXINGOPTION_SUB_CLIENT_BASED_ANALYTICS, AUXCOPYOPTION_USE_SCALLABLE_RESOURCE_MANAGEMENT
	-- CONTENTINDEXINGOPTION_ONLINE_CRAWL_JOB, INDEXSERVER_CLIENT_ID
IF @operationType IN ( 4022,5025) AND @subClientBasedAnalytics <> '0'
		SET @isDataAnalytics2Job = 1
IF @operationType IN ( 4022,5025) AND @onlineCrawlJobOption <> '0'
		SET @isOnlineCrawlJob = 1
	-------------------------------
	--CREATE TEMP TABLE------------
	-------------------------------
	-- None of these temp tables should ever exist when this procedure is called!
	if object_id('tempdb.dbo.#assocTbl') is not null
			DROP TABLE #assocTbl
	CREATE TABLE #assocTbl ( policyId INT DEFAULT 0,
							 copyId INT DEFAULT 0,
							 srmReportSet INT DEFAULT 0,
							 srmReportType INT DEFAULT 0,
							 trackingPolicyId INT DEFAULT 0,
							 mediaAgentId INT DEFAULT 0,
							 agentLessPolicyId INT DEFAULT 0,
							 workFlowId INT DEFAULT 0,
							 sidbStoreId INT DEFAULT 0,
							 libraryId INT DEFAULT 0,
							 logMonitoringPolicyId INT DEFAULT 0,
							 entityId INT DEFAULT 0,
							 entityType INT DEFAULT 0,
							 dataSourceId INT DEFAULT 0,
							 clientGroupId INT DEFAULT 0,
							 clientId INT DEFAULT 0,
							 appTypeId INT DEFAULT 0,
							 instanceId INT DEFAULT 0,
							 backupsetId INT DEFAULT 0,
							 appId INT DEFAULT 0,
							 exclude INT DEFAULT 0,
							 workflowOption NVARCHAR(MAX)
							)
IF (@i_useScalableResourceManagement = 0 AND @operationType = 4003
		-- 16 OPERATION_AUX_COPY
		AND EXISTS(SELECT 1 FROM dbo.IsOperationAllowedAndMatchedLevel(16, 2, 2, 0, 0, 0, 0, @i_taskId ,1) WHERE isOperationAllowed = 0)
	)
	BEGIN
		-- If operation window doesnt allow aux copy jobs to kick off, then dont kick off. Return empty list.
		-- Since its spb we cant return from here.
		GOTO RETURN_SELECT
	END
	-- Check for offline content index also below.
IF (@isDataAnalytics2Job = 0 AND @isOnlineCrawlJob = 0 AND @operationType = 4022
		-- OPERATION_OFFLINE_CONTENT_INDEXING          = 2048
		AND EXISTS(SELECT 1 FROM dbo.IsOperationAllowedAndMatchedLevel(2048, 2, 2, 0, 0, 0, 0, @i_taskId ,1) WHERE isOperationAllowed = 0)
	)
	BEGIN
		-- If operation window doesnt allow offline CI jobs to kick off, then dont kick off. Return empty list.
		-- Since its spb we cant return from here.
		GOTO RETURN_SELECT
	END
	CREATE CLUSTERED INDEX assocTbl_policyId_sidbStoreId_Index1 ON #assocTbl ([policyId], [sidbStoreId])
	IF object_id('tempdb.dbo.#copyEntitiesToRun') is not null
		DROP TABLE #copyEntitiesToRun
	CREATE TABLE #copyEntitiesToRun (
		policyId INT,
		copyId INT,
		error INT,
		archGroupName NVARCHAR(300),
		archGroupCopyName NVARCHAR(300) )
	CREATE INDEX copyEntitiesToRun_policyId_copyId_Index1 ON #copyEntitiesToRun ([policyId],[copyId])
	IF object_id('tempdb.dbo.#crawlEntities') is not null
		DROP TABLE #crawlEntities
	CREATE TABLE #crawlEntities (
		projectId INT,
		dataSourceId INT,
		actualClientId INT,
		entityType INT,
		entityId INT
	)
	CREATE INDEX crawlEntities_entityType_entityId_Index1 ON #crawlEntities ([entityType],[entityId])
	------------------------------------------
	--GET ALL ASSOCIATED ADMIN ENTITIES ------
	------------------------------------------
	-- Analytics Job
	-- Inventory crawl is common for both Inventory data source and project entity
	-- For Inventory there will only be one association, but for Project entity there can be multiple associated entities like
	-- Storage policy, Exchange Mailbox, Subclient we need to fetch all of them to make sure we run seperate jobs for each entity.
IF @operationType = 5025
	BEGIN
		DECLARE @projectList VARCHAR(250) = NULL
        DECLARE @clientGroupId INT = 0
		DECLARE @planId	INT = 0
        SELECT @clientGroupId = clientGroupId
        		FROM TM_AssocEntity
		WHERE exclude <> 1
		AND taskId = @i_taskId
		SELECT @planId = entityId
        		FROM TM_AssocEntity
		WHERE exclude <> 1
		AND taskId = @i_taskId
AND assocType = 158
		SELECT @projectList = coalesce(@projectList+',','')+convert(varchar(12),clientId)
		FROM TM_AssocEntity
		WHERE exclude <> 1
		AND taskId = @i_taskId
		IF (@projectList IS NOT NULL) OR (@planId > 0)
		BEGIN
			INSERT INTO #crawlEntities (projectId,dataSourceId,actualClientId,entityType,entityId)
			EXEC SEAGetEDiscoveryProjectsEntitiesDetails @ownerId,@projectList, @clientGroupId, @errorCode OUTPUT,@errorMsg OUTPUT, @planId
		END
		INSERT INTO #assocTbl (clientId, entityId, entityType, dataSourceId, appTypeId, instanceId, backupsetId)
		SELECT clientId, entityId, assocType, entityId, appTypeId, instanceId, backupsetId
				FROM TM_AssocEntity WHERE taskId = @i_taskId
AND exclude <> 1 AND assocType = 132
		UNION
		SELECT actualClientId, entityId, entityType, dataSourceId, 0, 0, 0
			FROM #crawlEntities crawlEntities
	END
	ELSE IF @isDataAnalytics2Job = 1 OR @isOnlineCrawlJob = 1
	BEGIN
		DECLARE @isClientLevelJob INT  = 0
		IF (@isDefaultDataAnalyticsSched=1 AND @isDataAnalytics2Job = 1 )
		BEGIN
			INSERT INTO #assocTbl(clientId, appTypeId, instanceId, backupsetId, appId)
			SELECT clientId, appTypeId, instance, backupset, subclientID
			FROM dbo.TM_AssocForDefaultDATask
		END
		IF NOT EXISTS(SELECT 1
					  FROM TM_AssocEntity as TM
					  WHERE taskId = @i_taskId AND exclude = 0
					  AND (clientId = 0 OR clientGroupId <> 0))
			SET @isClientLevelJob = 1 --Instance or client level
		IF @isClientLevelJob = 1
		BEGIN
			--== execute function once to get data rows
			DECLARE @AppTypesForAppGroup35 TABLE (
				appTypeId	INT PRIMARY KEY
			)
			INSERT INTO @AppTypesForAppGroup35 (appTypeId)
				SELECT DISTINCT
					appTypeId
				FROM GetAppTypesForAppGroup(35, 0)
				UNION
SELECT 134
			INSERT INTO #assocTbl
			(clientId, appTypeId, instanceId, backupsetId, appId)
			SELECT APP.clientId, APP.appTypeId, APP.instance, APP.backupset, APP.id
			FROM APP_Application AS APP WITH(NOLOCK)
			--INNER JOIN App_BackupSetName AS BS
--	ON (APP.backupSet = BS.id AND BS.status & 0x00008 = 0x00008) -- Submit for all Subclient
			INNER JOIN TM_AssocEntity as TM
				ON (APP.clientId = TM.clientId AND
						(
							(TM.subclientId <> 0 AND TM.subclientId = APP.id) --SubClient Level
							OR
							(TM.instanceId <> 0 AND TM.appTypeId = APP.appTypeID AND TM.instanceId = APP.instance AND TM.backupsetId = 0 AND TM.subclientId = 0) -- TM.instanceId <> -> Instance level Job.
							OR
							(TM.instanceId = 0 AND TM.appTypeId = 0)-- For Client level
						)
						AND TM.clientGroupId = 0 AND taskId = @i_taskId AND exclude = 0
					)
			AND APP.subclientStatus & @excludeStatus = 0 -- Exclude invalid subclients
			AND APP.appTypeId IN (SELECT DISTINCT appTypeId FROM GetAppTypesForAppGroup(137,0)) -- APPGRP_Analytics2JobSupported
			-- Give preference to onlineCrawl job option if set.
			IF @isOnlineCrawlJob = 1
			BEGIN
			   --Delete all non -default subclients if 'Indexing datasource id' is not present
			   -- It should be present either at subclient or client level.
				DELETE assoc
				FROM #assocTbl assoc
				INNER JOIN APP_Application app WITH(NOLOCK) ON assoc.appId = app.id
				INNER JOIN APP_BackupSetName bkpSet WITH(NOLOCK) ON bkpSet.id = app.backupSet
				LEFT JOIN APP_ClientProp cProp WITH(NOLOCK)
						ON assoc.clientId = cProp.componentNameId AND cProp.attrName = 'Indexing datasource id'
				LEFT JOIN APP_SubClientProp sProp WITH(NOLOCK)
						ON assoc.appId = sProp.componentNameId AND sProp.attrName = 'Indexing datasource id' AND sProp.modified = 0
						AND sProp.cs_attrName = CHECKSUM(N'Indexing datasource id')
				WHERE (sProp.componentNameId IS NULL AND cProp.componentNameId IS NULL)
						AND assoc.appTypeId IN (SELECT appTypeId FROM @AppTypesForAppGroup35)
				--Online Crawl is supported only for FS
				DELETE assoc
				FROM #assocTbl assoc
				WHERE assoc.appTypeId NOT IN (SELECT appTypeId FROM @AppTypesForAppGroup35)
			END
			ELSE --@isDataAnalytics2Job = 1
			BEGIN
				--Delete the entries for not supported type
				DELETE assoc
				FROM #assocTbl assoc INNER JOIN APP_InstanceProp instProp WITH(NOLOCK)
					ON assoc.instanceId = instProp.componentNameId AND instProp.attrName = 'Cloud Apps Instance Type' and instProp.modified=0
WHERE assoc.appTypeId = 134 AND instProp.attrVal NOT IN(1,2,5,6,7)	--GOOGLE_MAIL,GOOGLE_DRIVE,AMAZON_S3,AZURE_BLOB,ONEDRIVE
				DELETE assoc
				FROM #assocTbl assoc INNER JOIN APP_BackupSetName BS WITH(NOLOCK)
					ON assoc.backupsetId = BS.id
WHERE assoc.appTypeId = 78
AND (BS.status & 0x4000) = 0 AND (BS.status & 0x80000) = 0
				--Delete all non -default subclients for FS
				DELETE assoc
				FROM #assocTbl assoc
				INNER JOIN APP_Application app WITH(NOLOCK) ON assoc.appId = app.id
				INNER JOIN APP_BackupSetName bkpSet WITH(NOLOCK) ON bkpSet.id = app.backupSet
WHERE (((app.subclientStatus & 0x00008) = 0)
OR  ((bkpSet.status & 0x00008) =0 ))
							AND assoc.appTypeId IN (SELECT DISTINCT appTypeId FROM GetAppTypesForAppGroup(35,0))
				-- Data Analytics jobs for VSA agent is NOT support at client level but only supported at client group level.
				-- So dont spawn DA job for vsa agent at client level.
				-- To identify Analytics job do below only when index server attribute is set. We dont want to affect file indexing MR 271273
				IF (@indexServerClientIDOpt>0)
				BEGIN
					DELETE #assocTbl
WHERE appTypeId= 106
				END
			END
		END
		ELSE
		BEGIN
			INSERT INTO #assocTbl
			(clientGroupId, clientId, appTypeId, instanceId, backupsetId, appId)
			SELECT clientGroupId, clientId, apptypeId, instanceId, backupsetId, subclientId
			FROM TM_AssocEntity TA WITH (NOLOCK)
			WHERE TA.taskId = @i_taskId AND TA.exclude = 0
		END
		--Deleting associations which do not have content to be indexed for continuous schedules
		IF @isDataAnalytics2Job = 1 AND @freqInterval > 0
		BEGIN
			;WITH JobSD (appId, servStartDate) AS
			(
				SELECT
					JM.appId,
					MAX(JM.servStartDate) servStartDate
				FROM JMBkpStats JM
					INNER JOIN #assocTbl AE ON
AE.appId = JM.appId AND JM.commcellId = 2
				WHERE JM.status in (1, 3, 14) -- JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
				GROUP BY
					JM.appId
			),
			Jobs (appId, jobId) AS
			(
				SELECT
					JM.appId,
					JM.jobId
				FROM JMBkpStats JM
					INNER JOIN JobSD j ON
						j.appId = JM.appId
						AND j.servStartDate = JM.servStartDate
				WHERE
					JM.status in (1, 3, 14) -- JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
AND JM.commcellId = 2
			)
			DELETE AE
			FROM #assocTbl AE
			INNER JOIN Jobs J ON J.appId = AE.appId
INNER JOIN archFile AF ON J.jobId = AF.jobId AND (AF.flags & 64) > 0
		END
		-- Check for Operation window and remove entries which dont meet operation window.
		-- Apply operation window and filter here.
		-- Delete for which operation window for restore operation type isnt met.
		DELETE FROM #appIdTbl
		INSERT INTO #appIdTbl(clientId, appTypeID, taskId, jmOperationType)
		SELECT DISTINCT ClientId, appTypeId, @i_taskId, 65536 --OPERATION_DATA_ANALYTICS = 65536,
		FROM #assocTbl
		EXEC TMFilterSubclientsByOperationWindow 1
		-- Remove entries where operation window isnt met.
		DELETE assocTbl FROM #assocTbl assocTbl
		LEFT JOIN #appIdTbl appIdTbl ON assocTbl.clientId = appIdTbl.clientID AND assocTbl.appTypeId = appIdTbl.appTypeID
		AND @i_taskId = appIdTbl.taskId
		WHERE appIdTbl.taskId IS NULL
	END
ELSE IF @operationType = 4007 AND @ddbVerificationLevel <> '0'
	BEGIN
		--SIDB STORE ID
		INSERT INTO #assocTbl
		(policyId, copyId, sidbStoreId, exclude)
		-- SIDB STORE ID IS SELECTED
		SELECT TA.policyId, TA.copyId, TA.sidbStoreId, TA.exclude
		FROM TM_AssocEntity TA WITH (NOLOCK)
		WHERE TA.taskId = @i_taskId
		--AND TA.policyId = 0 AND TA.copyId = 0
		AND TA.agentlessPolicyId = 0 AND TA.clientGroupId = 0 AND TA.clientId = 0
		AND TA.trackingPolicyId = 0 AND TA.MediaAgentId = 0 AND TA.agentlessPolicyId = 0
		AND TA.libraryId = 0 AND TA.logMonitoringPolicyId = 0
		AND TA.sidbStoreId > 0
		UNION	--STORAGE POLICY IS SELECTED BUT NOT COPY ID
		SELECT AC.archGroupId, AC.id, CS.SIDBStoreId, TA.exclude
		FROM archGroupCopy AC WITH (NOLOCK)
JOIN archCopySIDBStore CS WITH (NOLOCK) ON AC.id = CS.copyId AND (CS.flags & 4 > 0)
		JOIN TM_AssocEntity TA WITH (NOLOCK) ON TA.policyId = AC.archGroupId AND TA.taskId = @i_taskId
		WHERE TA.agentlessPolicyId = 0 AND TA.clientGroupId = 0 AND TA.clientId = 0
		AND TA.trackingPolicyId = 0 AND TA.MediaAgentId = 0 AND TA.agentlessPolicyId = 0
		AND TA.sidbStoreId = 0 AND TA.libraryId = 0 AND TA.logMonitoringPolicyId = 0
		AND TA.workflowId = 0 AND AC.type <> 5 -- REMOVE TRANSITIVE COPY
		AND TA.policyId = AC.archGroupId AND TA.copyId = 0
AND AC.extendedFlags & 32 = 0
		UNION	--STORAGE POLICY AND COPY ID ARE SELECTED
		SELECT AC.archGroupId, AC.id, CS.SIDBStoreId, TA.exclude
		FROM archGroupCopy AC WITH (NOLOCK)
JOIN archCopySIDBStore CS WITH (NOLOCK) ON AC.id = CS.copyId AND (CS.flags & 4 > 0)
		JOIN TM_AssocEntity TA WITH (NOLOCK) ON TA.policyId = AC.archGroupId AND TA.copyId = AC.id AND TA.taskId = @i_taskId
		WHERE TA.agentlessPolicyId = 0 AND TA.clientGroupId = 0 AND TA.clientId = 0
		AND TA.trackingPolicyId = 0 AND TA.MediaAgentId = 0 AND TA.agentlessPolicyId = 0
		AND TA.sidbStoreId = 0 AND TA.libraryId = 0 AND TA.logMonitoringPolicyId = 0
		AND TA.workflowId = 0 AND AC.type <> 5 -- REMOVE TRANSITIVE COPY
		UNION	--SELECT ALL SIDB STORE IDS
		SELECT AC.archGroupId, AC.id, CS.SIDBStoreId, TA.exclude
		FROM archGroupCopy AC WITH (NOLOCK)
JOIN archCopySIDBStore CS WITH (NOLOCK) ON AC.id = CS.copyId AND (CS.flags & 4 > 0)
JOIN TM_AssocEntity TA WITH (NOLOCK) ON TA.taskId = @i_taskId AND TA.assocType = 155
		WHERE TA.agentlessPolicyId = 0 AND TA.clientGroupId = 0 AND TA.clientId = 0
		AND TA.trackingPolicyId = 0 AND TA.MediaAgentId = 0 AND TA.agentlessPolicyId = 0
		AND TA.sidbStoreId = 0 AND TA.libraryId = 0 AND TA.logMonitoringPolicyId = 0
		AND CS.SIDBStoreId > 0
AND AC.extendedFlags & 32 = 0
	END
ELSE IF @policyType = 13
	BEGIN
		INSERT INTO #assocTbl
		(workFlowId, clientId, exclude, workflowOption)
		SELECT workflowId, clientId, exclude, workflowOption
		FROM TM_AssocWorkFlowEntity (NOLOCK)
		WHERE taskId = @i_taskId
	END
	ELSE
	BEGIN
		--STORAGE POLICY JOBS
		INSERT INTO #assocTbl
		(policyId, copyId)
--- CODE BELOW HERE IS FROM TM_GetAdminEntityAuxCopy.spb  ----------
		--STORAGE POLICY JOBS
		SELECT TA.policyId, Ta.copyId			--GET STORAGE POLICY ASSOCIATIONS WITH NO EXCLUDE COPY
		FROM TM_AssocEntity TA WITH (NOLOCK)
		WHERE TA.taskId = @i_taskId AND TA.exclude = 0
		AND TA.agentlessPolicyId = 0 AND TA.clientGroupId = 0 AND TA.clientId = 0
		AND TA.trackingPolicyId = 0 AND TA.MediaAgentId = 0 AND TA.agentlessPolicyId = 0
		AND TA.sidbStoreId = 0 AND TA.libraryId = 0 AND TA.logMonitoringPolicyId = 0
		AND NOT EXISTS(SELECT 1 FROM TM_AssocEntity WITH (NOLOCK) WHERE taskId = TA.taskId AND exclude=1 AND TA.policyId = policyId)
		AND TA.policyId>0
		UNION
		SELECT AC.archGroupId, AC.id 	--GET STORAGE POLICY ASSOCIATIONS WITH EXCLUDE COPY
		FROM archGroupCopy AC WITH (NOLOCK)
		JOIN TM_AssocEntity TA WITH (NOLOCK) ON TA.policyId = AC.archGroupId AND TA.taskId = @i_taskId
		WHERE TA.agentlessPolicyId = 0 AND TA.clientGroupId = 0 AND TA.clientId = 0
		AND TA.trackingPolicyId = 0 AND TA.MediaAgentId = 0 AND TA.agentlessPolicyId = 0
		AND TA.sidbStoreId = 0 AND TA.libraryId = 0 AND TA.logMonitoringPolicyId = 0
AND TA.workflowId = 0 AND AC.type <> 5 -- REMOVE TRANSITIVE COPY
		AND exclude=1 AND TA.policyId = AC.archGroupId
		AND NOT EXISTS(SELECT 1 FROM archGroup WITH (NOLOCK) WHERE defaultCopy = AC.id)  -- REMOVE BACKUPCOPY ARCH COPY
		AND EXISTS(SELECT 1 FROM TM_AssocEntity WITH (NOLOCK) WHERE taskId = TA.taskId AND exclude=1 AND TA.policyId = AC.archGroupId)
AND AC.extendedFlags & 32 = 0
		UNION
		SELECT TA.policyId, TA.copyId
		FROM TM_AssocEntity TA WITH (NOLOCK)
		WHERE TA.agentlessPolicyId = 0 AND TA.clientGroupId = 0 AND TA.clientId = 0
		AND TA.trackingPolicyId = 0 AND TA.MediaAgentId = 0 AND TA.agentlessPolicyId = 0
		AND TA.sidbStoreId = 0 AND TA.libraryId = 0 AND TA.logMonitoringPolicyId = 0
		AND TA.workflowId = 0 AND TA.exclude = 0 AND TA.taskId = @i_taskId
		AND NOT EXISTS(SELECT 1 FROM TM_AssocEntity WITH (NOLOCK) WHERE taskId = TA.taskId AND exclude=1)
		AND TA.policyId>0
		UNION
		-- If policy or copy is excluded then above code will handle it. (--GET STORAGE POLICY ASSOCIATIONS WITH EXCLUDE COPY)
		-- Else just get all relevant policies with copyId as 0 .
		SELECT AGC.archGroupId,
		0
		FROM ArchGroup AG WITH (NOLOCK) INNER JOIN archGroupCopy AGC WITH (NOLOCK)
		ON AGC.archGroupId = AG.Id
		AND AGC.id <> AG.defaultCopy AND AGC.id <> AG.defaultSnapCopy
		AND AGC.type <> 5  -- REMOVE TRANSITIVE COPY
AND AGC.extendedFlags & 32 = 0
AND (AG.flags & ((256|16384 | 8388608 ))= 0)
		AND AGC.isActive != 0 AND AGC.startTime <= @nowUtcUnix
		JOIN TM_AssocEntity TA WITH (NOLOCK) ON TA.taskId = @i_taskId
		AND TA.agentlessPolicyId = 0 AND TA.clientGroupId = 0 AND TA.clientId = 0
		AND TA.trackingPolicyId = 0 AND TA.MediaAgentId = 0 AND TA.agentlessPolicyId = 0
		AND TA.sidbStoreId = 0 AND TA.libraryId = 0 AND TA.logMonitoringPolicyId = 0
		AND TA.workflowId = 0
		AND TA.policyId = 0 -- All storage policies
		AND TA.copyId = 0
AND TA.assocType = 16
		LEFT JOIN TM_AssocEntity ExcludedTA WITH (NOLOCK) ON ExcludedTA.taskId = @i_taskId
		AND ExcludedTA.exclude = 1
		AND ExcludedTA.policyId =  AGC.archGroupId
		WHERE ExcludedTA.taskId IS NULL
--- CODE ABOVE HERE IS FROM TM_GetAdminEntityAuxCopy.spb  ----------
	END
	---------------------------
	--EXCLUSION----------------
	---------------------------
IF @operationType = 4007 AND @ddbVerificationLevel <> '0'
	BEGIN
		--EXCLUDED SIDB STORE ID ASSOCIATION
		DELETE  #assocTbl
		FROM
		(
			SELECT policyId pId, copyId cId, sidbStoreId sdbId
			FROM #assocTbl A
			WHERE exclude = 1
		) T
		WHERE T.sdbId = sidbStoreId
		OR
		(
			T.pId = policyId AND T.cId = copyId AND T.sdbId = 0
		)
		OR
		(
			T.pId = policyId AND T.cId = 0 AND T.sdbId = 0
		)
		OR sidbStoreId = 0 		--REMOVE ENTRIES WITHOUT SIDB STORE
		--IF A GDSP STORE IS NOT ASSOCIATED TO SCHEDULE POLICY, REMOVE FROM THE LIST THE SIDB STORE ID OF THE GDSP PRIMARY COPY
		DELETE #assocTbl
		FROM #assocTbl A
		JOIN (
			SELECT	CS.SIDBStoreId, AG.id
			FROM	archGroup AG, archCopySIDBStore CS
WHERE	AG.flags & 256 > 0
					AND AG.defaultCopy = CS.copyId
AND CS.flags & 4 > 0
			AND AG.id NOT IN (
				SELECT policyId FROM #assocTbl
			)
		) T ON T.SIDBStoreId = A.sidbStoreId
		--MR151197 - FOR DUPLICATE SIDB JOB SHOULD GET LAUNCHED ON GDSP COPY ONLY
		DELETE #assocTbl
		FROM #assocTbl A
		JOIN (
			SELECT sidbStoreId, COUNT(sidbStoreId) n
			FROM #assocTbl
			GROUP BY sidbStoreId
		) T ON T.sidbStoreId = A.sidbStoreId AND T.n > 1
		JOIN archGroupCopy AC (NOLOCK) ON A.copyId = AC.id
AND AC.dedupeFlags&268435456<>268435456  --_CVA_HOST_GLOBAL_DEDUP_STORE_FLAG
	END
	ELSE IF @isDataAnalytics2Job = 0 AND @isOnlineCrawlJob = 0 -- Data analytics job is at client herirarchy level
	BEGIN
		--EXCLUDED STORAGE POLICY COPY ASSOCIATION
		DELETE assocTbl
		FROM #assocTbl assocTbl INNER JOIN TM_AssocEntity (NOLOCK) TA
		ON TA.exclude = 1 AND TA.taskId = @i_taskId AND TA.copyId > 0
		AND TA.copyId = assocTbl.copyId
		-- Handle storage policy exclusion since we are supporting all storage policies now.
		DELETE assocTbl
		FROM #assocTbl assocTbl INNER JOIN TM_AssocEntity (NOLOCK) TA
		ON TA.exclude = 1 AND TA.taskId = @i_taskId AND TA.policyId > 0
		AND TA.policyId = assocTbl.policyId
		AND TA.copyId = 0
	END
	-- Checking if aux copy job needs to run (if no job is present to be backed up)
IF @operationType = 4003 AND @freqInterval > 0
	BEGIN
		INSERT INTO #copyEntitiesToRun
		EXEC archAutoCopyTaskToBeRunEx @i_taskId, @nowUtcUnix
		DELETE AE
		FROM #assocTbl AE
		INNER JOIN #copyEntitiesToRun TBR ON AE.policyId = TBR.policyId
		WHERE TBR.error > 0 AND AE.copyId = TBR.copyId
	END
--- CODE ABOVE HERE IS FROM TM_GetAdminEntity.spb  ----------
IF @operationType = 4007 AND @taskType = 4
		BEGIN
			set @o_count = (select count(*) from ( SELECT DISTINCT * FROM #assocTbl
				WHERE sidbStoreId <> 0 ) t)
		END
ELSE IF @policyType = 13
		BEGIN
			SELECT @o_count = COUNT(workFlowId) FROM #assocTbl
		END
		ELSE IF @isDataAnalytics2Job = 1
		BEGIN
			set @o_count = (select count(1) from ( SELECT DISTINCT * FROM #assocTbl
				WHERE ClientId <> 0 ) t)
		END
		ELSE
		BEGIN
			set @o_count = ( select count(*) from ( SELECT DISTINCT * FROM #assocTbl
			WHERE policyId <> 0 ) t)
		END
	END
RETURN_SELECT:
	if @o_count is NULL
	begin
		set @o_count=0
	end
END TRY
---------------------------------------------
-- THIS IS THE RESPONSE IF ALL WENT WELL ----
---------------------------------------------
BEGIN CATCH
PRINT  'INSIDE CATCH BLOCK WITH FOLLOWING ERROR:
	ERROR CODE: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
	PROC NAME: ' + ISNULL(ERROR_PROCEDURE(), '???') + '
	ERROR LINE NO: ' + CAST(ERROR_LINE() AS VARCHAR)  + '
	ERROR MESSAGE: ' + ERROR_MESSAGE() + '
	ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +  '
	ERROR STATE: ' + CAST(ERROR_STATE() AS VARCHAR)
	-- Call procedure to print error information.
    --EXECUTE dbo.PrintError;
END CATCH;
	--DROP THE TEMP TABLE
	if object_id('tempdb.dbo.#assocTbl') is not null
		DROP TABLE	#assocTbl
	if object_id('tempdb.dbo.#appIdTbl') is not null
    	DROP TABLE #appIdTbl
END
GO

IF EXISTS (select * from GxQscripts where name = 'TM_GetAdminEntityCount')
	delete from GxQscripts where name = 'TM_GetAdminEntityCount'
GO

IF EXISTS (select * from GXDBVersions where aliasname='TM_GetAdminEntityCount')
	delete from GXDBVersions where aliasname = 'TM_GetAdminEntityCount'
GO

insert into GXDBVersions values(2, 'TM_GetAdminEntityCount',  'v1.2.2.40.8.3', 'TM_GetAdminEntityCount', 'v1.2.2.40.8.3')
GO

