

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/TM_GetBackupEntity.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_GetBackupEntity.sp,v $ $Id: TM_GetBackupEntity.sp,v 1.111.2.126.4.16 2021/06/11 21:05:06 rengu Exp $";
-- =============================================
-- Author:		Anand Vibhor
-- Create date: 01/28/2006
-- Description:	Get the appId on which job is going to run
-- =============================================
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='TM_GetBackupEntity')
	delete from GXDBVersions where aliasname = 'TM_GetBackupEntity'
GO
print '... Creating Procedure: TM_GetBackupEntity'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure TM_GetBackupEntity
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
--
--   PARAMETERS   &   OUTPUTS
--
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  @i_taskId integer,
  @i_subtaskId integer,
  @i_timeZoneName nvarchar(100),
  @i_getLevel integer=1,
  @i_initFrom integer=0,
  @i_runTaskOptions XML = NULL,
  @o_errorString nvarchar(MAX) = NULL OUTPUT ,
  @i_jobStartTime INTEGER = NULL ,
  @i_assocsXML XML = NULL,
  @i_localeID INT = 0
AS
  DECLARE @o_appId INT
  DECLARE @o_appTypeId INT
  DECLARE @o_clientId INT
  DECLARE @o_instanceId INT
  DECLARE @o_backupsetId INT
  DECLARE @o_status INT
  DECLARE @o_bkpLevel INT
  DECLARE @o_parentJobId INT
  DECLARE @o_parentAppId INT
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON
BEGIN TRY
	DECLARE @checkActivity	INT = 0
	DECLARE @userId			INT = 1
	DECLARE @freqInterval	INT = 0
	DECLARE @taskType 		INT = 0
	DECLARE @operationType 	INT = 0
	DECLARE @entityCount	INT = 0
	DECLARE @nowUtcTime 	DATETIME = GETUTCDATE()
	DECLARE @nowUtcUnix 	INT = datediff(second, '01/01/1970', @nowUtcTime)
	DECLARE @freqIntervalForSQL INT = 0
	DECLARE @queueJobsIfActivityDisabled INT = 0
	DECLARE @runTaskOptionIgnoreActivityCheck INT = 0
	--MR77799 SPEED UP LOOKUP, ON SCALE SETUP IT WAS SOMETIMES TAKING OVER 3 HOURS CAUSING InvalidOperationException ERROR
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	DECLARE @jmOpTypeIgnore INT = 1048576/*OPERATION_ALL*/ -- We pass this in appIdTbl for TL backups to avoid matching against any opType selected.
	SET @jmOpTypeIgnore = @jmOpTypeIgnore *2
	DECLARE @currentTime INT = dbo.GetUnixTime(GETUTCDATE())
	DECLARE @numOfDays INT = ISNULL((SELECT CAST(value AS NVARCHAR(MAX)) FROM GXGlobalParam WITH (NOLOCK) WHERE name='NumberOfDaysBetweenSynthicFullJobs'), 30)
	DECLARE @numForKilledDays INT = ISNULL((SELECT CAST(value AS NVARCHAR(MAX)) FROM GXGlobalParam WITH (NOLOCK) WHERE name='NumberOfDaysBetweenFKilledSynthicFullJobs'), 1)
SET @numOfDays = ISNULL((SELECT value FROM TM_SubTaskOptions WITH (NOLOCK) WHERE subTaskId = @i_subtaskId AND optionId = 1765126965), @numOfDays)
	DECLARE @syntheticInterval INT = @currentTime-(@numOfDays*24*60*60)
	DECLARE @syntheticKilledInterval INT = @currentTime-(@numForKilledDays*24*60*60)
	DECLARE @jobsQueuedIfJobsRunning INT = ISNULL((SELECT CAST(value AS NVARCHAR(MAX)) FROM GXGlobalParam WITH (NOLOCK) WHERE name='JobsQueuedIfJobsRunning'), 0)
	DECLARE @enableSyntheticFullWithoutIncrementalBackup INT = ISNULL((SELECT CAST(value AS NVARCHAR(MAX)) FROM GXGlobalParam WITH (NOLOCK) WHERE name='enableSyntheticFullWithoutIncrementalBackup'), 0)
	DECLARE @disableSyntheticFullWithoutIncrementalBackup INT = 1 -- Default disabled
	IF @enableSyntheticFullWithoutIncrementalBackup = 1
		SET @disableSyntheticFullWithoutIncrementalBackup = 0
	DECLARE @runParallelTLogAndFullForSybase INT = ISNULL((SELECT CAST(value AS NVARCHAR(MAX)) FROM GXGlobalParam WITH (NOLOCK) WHERE name='JMSybaseConcurrentLogBackupsEnabled'), 0)
	DECLARE @isPlanDataSchedulePolicy INT = 0 -- We use this to indicate RPO schedule not modified from commcell console. Like pattern or backup level modification.
	DECLARE @numOfDefaultDaysForAutoconvertBackupLevel INT = ISNULL((SELECT CAST(value AS NVARCHAR(MAX)) FROM GXGlobalParam WITH (NOLOCK) WHERE name='NumberOfDaysBetweenAutoConvertBackupLevel'), 7)
	DECLARE @consolidateAutoSyntheticOptions INT = 0
	DECLARE @isSchedulePolicyAssociatedToPlan INT = 0
	DECLARE @errorMessageForLogOnly NVARCHAR(MAX) = ''
	DECLARE @errorCode INT = 0
	DECLARE @policyType		INT = 0
	DECLARE @isEdgeDrivePolicy INT = 0
	DECLARE @initiatedFrom INT = 0
	DECLARE @isImportMailBox INT = 0
	DECLARE @isPromoteToFull INT = 0
	DECLARE @runProxy INT = 0
	DECLARE @useSpaceFromMA INT = 0
	DECLARE @sweepMAClienId INT = 0
	--Media Agent online properties
	DECLARE @HOST_STATUS_POWER_MANAGED_VM       INTEGER
	SET     @HOST_STATUS_POWER_MANAGED_VM       = 7
	DECLARE @isPowerVMFeatureEnabled INT
	SET @isPowerVMFeatureEnabled = ISNULL ((SELECT value FROM MMConfigs WHERE name LIKE 'MMCONFIG_CLOUD_VM_MANAGEMENT_ENABLED_FLAG'), 0)
	SELECT
		@isImportMailBox = CASE optionId
WHEN 497810577 THEN value		-- EXCHANGEONEPASSBKPOPTION_IMPORT_JOB
						ELSE @isImportMailBox
					END,
		@isPromoteToFull = CASE optionId
WHEN 900859578 THEN value		-- BACKUPOPTION_PROMOTE_TO_FULL
						ELSE @isPromoteToFull
					END,
		@runProxy = CASE optionId
WHEN 160000 THEN value		-- TM_RUN_TASK_PROXY
						ELSE @runProxy
					END,
		@useSpaceFromMA = CASE optionId
WHEN 1299309857 THEN value		-- AUTOMATICSCHEDULEPATTERN_USE_STORAGE_SPACE_FROM_MA
						ELSE @useSpaceFromMA
					END,
		@sweepMAClienId = CASE optionId
WHEN 2019396311 THEN value		-- COMMONOPTION_FINAL_SWEEP_MACLIENT_ID
						ELSE @sweepMAClienId
					END
	FROM TM_SubTaskOptions WITH (NOLOCK)
	WHERE subTaskId = @i_subtaskId
AND optionId IN (497810577, 900859578, 160000, 1299309857, 2019396311)	-- DATAOPT_AUTO_COPY, CDROPTIONS_VOL_RECOVERY_POINT_CREATION, BACKUPOPTION_BACKUP_LEVEL, DATABKPOTION_USE_AUTOMATIC_INTERVAL_FOR_SYNTHETIC_FULL, DATABKPOTION_ENABLE_RUN_FULL_CONSOLIDATION_BACKUP
	SELECT
		@taskType = taskType,
		@policyType = policyType,
		@initiatedFrom = initiatedFrom,
		@isEdgeDrivePolicy = CASE
WHEN (flags & 0x40) <> 0		-- TASK_FLAGS_EDGE_DRIVE
									THEN 1
								ELSE 0
							END,
		@isSchedulePolicyAssociatedToPlan = CASE
WHEN (flags & 536870912 ) <> 0		-- _PLAN_ASSOCIATED_ENTITY
									THEN 1
								ELSE 0
							END
	FROM TM_Task WITH (NOLOCK)
	WHERE taskId = @i_taskId
	SET @consolidateAutoSyntheticOptions = ISNULL((SELECT CAST(value AS NVARCHAR(MAX)) FROM GXGlobalParam WITH (NOLOCK) WHERE name='consolidateSytheticFullAutomaticSchedulePattern'), @isSchedulePolicyAssociatedToPlan)
IF (@i_initFrom NOT IN (1, 2, 3,4  ))
	BEGIN
		DECLARE @freqTypeFromPattern INT , @freqIntervalFromPattern INT, @freqSubDayIntervalFromPattern INT
		SELECT @freqIntervalFromPattern = P.freq_interval, @freqTypeFromPattern = P.freq_type, @freqSubDayIntervalFromPattern = P.Freq_subday_interval
				FROM TM_PatternAssoc (NOLOCK) PA JOIN TM_Pattern (NOLOCK) P ON P.patternId = PA.patternId AND PA.subTaskId = @i_subtaskId
IF @freqTypeFromPattern = 4096
		BEGIN
			SET @freqInterval = (@freqIntervalFromPattern*60)
		END
		-- If this schedule isnt RPO schedule (indicated by SUBTASK_FLAGS_DEFAULT_DAILYINCR) OR if this schedule isnt database RPO schedule then freqInterval for SQL is the same as interval for other agents.
IF (NOT EXISTS(SELECT 1 FROM TM_SubTask WHERE subTaskId = @i_subtaskId AND ( flags & 0x10000 ) > 0) OR
NOT EXISTS(SELECT 1 FROM APP_PlanProp WHERE attrName = 'Schedule policy' AND attrVal = CAST(@i_taskId AS NVARCHAR(10) ) ) OR
EXISTS(SELECT 1 FROM TM_SubTaskOptions (NOLOCK) WHERE subTaskId = @i_subtaskId AND optionId = 458405394 AND Value != '2' ) -- 2 If user has modified backup level then no need for special handling.
		)
		BEGIN
			SET @freqIntervalForSQL = @freqInterval
		END
		ELSE
		BEGIN
			-- Else its RPO schedule. Now we have to check whether they modified pattern. From admin console, only daily/ continuous freqTypes supported.
			IF (@freqInterval>0)
			BEGIN
				SET @freqIntervalForSQL = 3600*24  --- For DB RPO continous schedule (for SQL agents), we want to trigger it only once a day.
				SET @isPlanDataSchedulePolicy = 1
			END
ELSE IF @freqTypeFromPattern = 4 AND @freqSubDayIntervalFromPattern = 0
			BEGIN
				SET @isPlanDataSchedulePolicy = 1
			END
ELSE IF @freqTypeFromPattern = 8 AND @freqSubDayIntervalFromPattern = 0
			BEGIN
				SET @isPlanDataSchedulePolicy = 1
			END
		END
	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  ----------
--- 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  ----------
--- CODE BELOW HERE IS FROM TM_GetBackupEntityFilter.spb  ----------
	DECLARE @dontStartSynthFullOnDeConfClients INT = ISNULL((SELECT CAST(value AS NVARCHAR(MAX)) FROM GXGlobalParam WITH (NOLOCK) WHERE name='JMDontStartSynthFullOnDeConfClients'), 1)
	DECLARE @doNotStartBackupOnSCWithNoStoragePolicy INT = ISNULL((SELECT CAST(value AS INT) FROM GxGlobalParam WITH (NOLOCK) WHERE name = 'doNotStartBackupOnSCWithNoStoragePolicy'), 0)
DECLARE @isSystemCreatedDDBSchedule INT = ISNULL((SELECT 1 FROM TM_Task WHERE (flags&(CAST(0x20 AS INT)|CAST(0x4 AS INT))) = (CAST(0x20 AS INT)|CAST(0x4 AS INT)) AND taskId = @i_taskId),0)
	DECLARE @isSPAMAXDBBackupSupported INT = 0
	DECLARE @isDumpSweepEnabled INT = ISNULL((SELECT CAST(value AS INT) FROM TM_SubTaskOptions WHERE subTaskId = @i_subtaskId AND optionId = 1299309857 /*AUTOMATICSCHEDULEPATTERN_USE_STORAGE_SPACE_FROM_MA*/),0)
	DECLARE @isVSAV2ParentfilterDone INT = 0
	-- First check whether setup has SAPMAXDB workflow present and if so, then Check whether schedule has SAP MAX DB backup level specified.
	IF EXISTS(SELECT 1 FROM WF_Definition (NOLOCK) where name='SAPMaxDB' AND (flags&1) = 0 -- to check if its not disabled.
	AND @freqInterval = 0 -- We dont support SAP MAX DB backup for continuous incremental schedules.
	AND @autoCopy != 1) -- We dont support SAP MAX DB backup for automatic synthetic full schedules schedules)
	BEGIN
		SELECT @isSPAMAXDBBackupSupported = 1
		FROM TM_SubTaskOptions WITH (NOLOCK)
		WHERE subTaskId = @i_subtaskId
AND optionId = 150853164
		AND value!='0'
	END
	DECLARE @isAutomaticSchedule INT = 1
	IF @autoCopy = 0
		OR
		(
			@autoCopy <> 0	--NO NEED TO GET JOBID FOR AUTOMATIC SCHEDULES
			AND
@i_initFrom IN (1, 2, 6)	--UNLESS REQUEST IS NOT STARTED BY SCHEDULER
		)
	BEGIN
		SET @isAutomaticSchedule = 0
	END
	---------------------------
	--FILTER OF APPID
	---------------------------
	--1. Filter the appGroup
	--2. Filter the appType
	IF EXISTS (
		SELECT filter_value
		FROM TM_AssocFilter
		WHERE taskId = @i_taskId
			AND filter_value <> 0
		)
	BEGIN
		DELETE
		FROM #appIdTbl
		WHERE apptypeId NOT IN (
				SELECT ATGA.appTypeId
				FROM APP_AppTypeGroupAssoc ATGA
					INNER JOIN TM_AssocFilter AF ON
						ATGA.typeOfGroup = 0
						AND ATGA.appGroupId = AF.filter_value
						AND AF.filter_type = 1
						AND AF.taskId = @i_taskId
				UNION ALL
				SELECT IT.type
				FROM dbo.APP_iDAType IT
					INNER JOIN TM_AssocFilter AF ON
						IT.type = AF.filter_value
						AND AF.filter_type = 2
						AND AF.taskId = @i_taskId
				UNION ALL
				SELECT AT.appTypeId
				FROM dbo.GetAppTypesForAppGroup(22, 0) AT	-- corrected full outer join error
					INNER JOIN TM_AssocFilter AF ON
						AF.filter_value = AT.appTypeId
						AND AF.filter_type = 2
						AND taskId = @i_taskId
			) --APPGRP_W2KFileSystem
	END
	--3. Filter the dummy IDA
	DELETE appIdTbl
	OUTPUT DELETED.appId,DeletedAppIds.ErrorCode, DeletedAppIds.ErrorMessage INTO #deletedAppIdsErrorCode
	FROM #appIdTbl appIdTbl
	INNER JOIN
	(
			--3. Filter the dummy IDA
			SELECT
				appId,0 as ErrorCode,'' as ErrorMessage
			FROM #appIdTbl
			WHERE appTypeId >= 1000
OR appTypeId = 72		-- CV_APPTYPE_RECOVERY_DIRECTOR
OR appTypeId = 136
			--4. Filter the uninstalled, deleted, dummy, hidden subclient
			UNION ALL
			SELECT
appId, CASE WHEN @backupLevel = 4 THEN (4568 | (CAST(POWER(2, 24) AS BIGINT) * 35))  ELSE 0 END, '' -- (4568 | (CAST(POWER(2, 24) AS BIGINT) * 35))
			FROM #appIdTbl
			WHERE (	-- Synth full are allowed on deconfigured clients
(@dontStartSynthFullOnDeConfClients = 1 OR @backupLevel <> 4 OR @entityCount = 1)			-- BACKUP_LEVEL_SYNTHETIC_FULL
						AND (STATUS & 2) = 2	-- CV_STATUS_UNINSTALLED
AND @operationType <> 4028	--TASK_OPERATION_TYPE_SNAP_TO_TAPE
						AND NOT EXISTS (	--MR = 158352 - allow DDB subclient backups even if subclient is deconfigured
							SELECT 1 FROM APP_SubClientProp
							WHERE componentNameId = appId
							AND modified = 0
							AND attrName = 'DDB Backup'
							AND cs_attrName = CHECKSUM(N'DDB Backup')
							AND attrVal = '1'
						)
						AND NOT EXISTS (
								SELECT 1 FROM APP_SubClientProp
								WHERE componentNameId = appId
								AND modified = 0
								AND attrName = 'Index SubClient'
								AND cs_attrName = CHECKSUM(N'Index SubClient')
								AND attrVal = '1'
						)
					)
			UNION ALL
			SELECT
				appId, 0 , ''
			FROM #appIdTbl
			WHERE (
					(	-- Skip synth full on deconfigured subclients that do not have storage policy set.
						@backupLevel = 4		-- BACKUP_LEVEL_SYNTHETIC_FULL
						AND (STATUS & 2) = 2	-- CV_STATUS_UNINSTALLED
						AND storagePolicy <= 1
					)
					OR (
						(STATUS & 4) = 4 -- CV_STATUS_DELETED
AND @operationType <> 4028	--TASK_OPERATION_TYPE_SNAP_TO_TAPE
					)
OR ( (STATUS & 16) = 16 AND (@isSPAMAXDBBackupSupported = 0 OR appTypeId!= 79 ) )-- CV_STATUS_DUMMY
					OR
					(
						(STATUS & 32) = 32 -- CV_STATUS_HIDDEN
						AND NOT (
apptypeId IN (84, 107)	-- CV_APPTYPE_CDR, CV_APPTYPE_WORKSTATION_BACKUP
							AND (STATUS & 8) = 8	-- CV_STATUS_DEFAULT
						) --IGNORE CDR/WBA SINCE THEY ARE MARKED HIDDEN
						AND NOT EXISTS( --Allow hidden BLR subclients
							SELECT 1 FROM APP_SubClientProp
							WHERE componentNameId = appId
							AND modified = 0
							AND attrName = 'Is BLR Replication Subclient' --CV_BLR_IS_REPLICATION_SUBCLIENT
							AND cs_attrName = CHECKSUM(N'Is BLR Replication Subclient')
							AND attrVal = '1'
						)
					)
				)
			--5. Filter the default subclient for below iDA
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE apptypeId IN (24, 27, 31, 40, 47, 48, 67, 68, 87 )
				-- UNUSED_CV_APPTYPE_24, UNUSED_CV_APPTYPE_27, UNUSED_CV_APPTYPE_31, LEGACY_CV_APPTYPE_UNIX_PROXYHOST, CV_APPTYPE_QR_AGENT,
				-- UNUSED_CV_APPTYPE_48, CV_APPTYPE_MS_EXCHANGE_ARCHIVER, UNUSED_CV_APPTYPE_68,  CV_APPTYPE_PROXYHOST_IMAGELEVEL_UNIX
				AND (STATUS & 8) = 8		-- CV_STATUS_DEFAULT
				AND (STATUS & 65536) = 0	-- CV_STATUS_REFCOPY_SUB
			--58
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE apptypeId IN (65, 25)		-- CV_APPTYPE_CDR, CV_APPTYPE_WORKSTATION_BACKUP
				AND ((STATUS & 8) = 8)		-- CV_STATUS_DEFAULT
				OR (
					appTypeId IN (84, 107)	-- CV_APPTYPE_CDR, CV_APPTYPE_WORKSTATION_BACKUP
					AND (
						((STATUS & 8) <> 8)	-- CV_STATUS_DEFAULT
						OR (
							@fanInRP = 0
							AND backupsetId IN (
								SELECT id
								FROM APP_BackupSetName
								WHERE (STATUS & 40) = 40
									AND id = backupsetId
								)
							)
					)
				)
				OR (	--MR54684 REMOVED CDR FROM SCHEDULE POLICY AGAIN WILL BE ADDED IN 10.0?
					appTypeId = 84		-- CV_APPTYPE_CDR
					AND (@taskType & 4 = 4)
				)
				OR (	--ONLY RUN JOBS FOR NON-DEFAULT BACKUPSET FOR SCHEDULE POLICIES
					appTypeId = 107		-- CV_APPTYPE_WORKSTATION_BACKUP
					AND (@taskType & 4) = 4
					AND backupsetId IN (
						SELECT id
						FROM APP_BackupSetName
						WHERE (STATUS & 40) = 40
							AND id = backupsetId
					)
				)
				OR (	--DO NOT RUN JOBS ON PSEUDO HIDDEN BACKUP SETS
					appTypeId IN (37, 62, 103)		-- CV_APPTYPE_DB2, CV_APPTYPE_UNIX_DB2, CV_APPTYPE_DB2_DPF
					AND backupsetId IN (
						SELECT id
						FROM APP_BackupSetName
						WHERE STATUS & 24 = 24
							AND id = backupsetId
						)
				)
			--6. Filter the command line and command log line subclients for below iDA
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE (@taskType & 4) = 4 --TASK_POLICY
				AND (STATUS & (64 | 4096)) > 0 -- CV_STATUS_CMD_LINE | CV_STATUS_LOGCMD_LINE
AND (@isSPAMAXDBBackupSupported = 0 OR appTypeId!= 79 )
AND appTypeId NOT IN(135, 3)
			--6.a Filter only command log line subclients when dump sweep is enabled for SAP HANA
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE (@taskType & 4) = 4 --TASK_POLICY
				AND ((STATUS & 64) > 0 -- CV_STATUS_CMD_LINE
				OR (
						STATUS & 4096 > 0 --| CV_STATUS_LOGCMD_LINE
						AND @isDumpSweepEnabled = 0
					)
				)
AND appTypeId = 135
			--6.b Filter command line subclient in case of Informix when dump sweep is enabled
			UNION ALL
            SELECT
                appId,0,''
            FROM #appIdTbl
            WHERE (@taskType & 4) = 4 --TASK_POLICY
	            AND (STATUS & 64) > 0 -- CV_STATUS_CMD_LINE
				AND @isDumpSweepEnabled = 0
AND appTypeId = 3
			--7. No policy job for QR
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE (@taskType & 4) = 4
				AND @taskType <> 5	--TASK_POLICY, 5 MEANS workflow
				AND apptypeId IN (47, 50)	-- CV_APPTYPE_QR_AGENT, CV_APPTYPE_UNIX_REPLICATION_IDA
			--8. Skip on demand job when job is running from policy
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE (@taskType & 4) = 4 --TASK_POLICY
				AND backupsetId IN (
					SELECT componentnameid
					FROM APP_BackupSetProp
					WHERE modified = 0
						AND attrName = N'OnDemand Backupset'
						AND attrVal = N'1'
				)
			--9. PART1 If operation type is transaction log, skip all the other subclients. BKP_OP_TRAN_LOG = 5
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE appTypeId IN (6, 51, 59, 77)	-- UNUSED_CV_APPTYPE_6,UNUSED_CV_APPTYPE_51,CV_APPTYPE_LOTUS_DOMINO6_DATABASE,CV_APPTYPE_UNIX_LOTUSNOTES6_DB
				AND (STATUS & 128) <> 128	--CV_STATUS_TRAN_LOG			0x0080
				AND (
					@backupLevel = 6		-- BACKUP_LEVEL_TRANSACTION_LOG
					--MR:152132 - Schedule policy should allow both Lotus Notes full and Transaction-log backup
					OR
					(
						EXISTS (
						SELECT filter_value
						FROM TM_AssocFilter
						WHERE filter_type = 1
							AND filter_value = 104
							AND taskId = @i_taskId --FILTER_AppGroup= 3
						)
						AND
						--CHECK FOR OTHER APPTYPE SELECTED TO NOT DELETE
						NOT EXISTS (
						SELECT filter_value
						FROM TM_AssocFilter
						WHERE filter_type = 1
							AND filter_value <> 104
							AND taskId = @i_taskId --FILTER_AppGroup= 3
						)
					)
				)
			--12. Filter the default subclient - Will be used by SQL and FLR
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE (STATUS & 8) = 8		-- CV_STATUS_DEFAULT
				AND EXISTS (
					SELECT filter_value
					FROM TM_AssocFilter
					WHERE filter_type = 3
						AND filter_value = 3
						AND taskId = @i_taskId --FILTER_Subclient= 3
				)
			-- Edge drive clients now support synthetic full.
			-- In case its not synthetic full and its a edge drive schedule, remove all Edge drive clients
			-- Edge drive clients now support synthetic full.
			-- In case its not synthetic full and its a edge drive schedule, remove all Edge drive clients
			-- Remove all edge drive clients where backup level <> BACKUP_LEVEL_SYNTHETIC_FULL
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE @isEdgeDrivePolicy = 0
				AND @backupLevel <> 4		-- BACKUP_LEVEL_SYNTHETIC_FULL
				AND clientId IN (
					SELECT id
					FROM app_client
					WHERE (STATUS & 268435456) > 0		-- CV_STATUS_EDGE_DRIVE
						AND (STATUS & 1073741824) = 0	-- CV_STATUS_SYSTEM_EDGE_DRIVE
				)
			--DO NOT COUNT AGENTS THAT DO NOT SUPPORT SYNTHETIC FULL SCHEDULES
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE @autoCopy = 1
				AND bkpLevel <> 4	-- BACKUP_LEVEL_SYNTHETIC_FULL
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE  @autoCopy = 1
				AND appTypeId = 3	-- CV_APPTYPE_INFORMIX
            -- Filter default backupset for Edge drive clients.
			UNION ALL
            SELECT
                appId,0,''
            FROM #appIdTbl APP
            INNER JOIN App_Client C ON C.id = APP.clientId
            INNER JOIN APP_BackupSetName B ON B.id = APP.backupsetId
WHERE C.status & 0x10000000 = 0x10000000
            AND B.status & 0x00008 = 0x00008
			--15. Block Synthetic Full for subclient only when ESP (Is Turbo Subclient) is set AND that has properties "Is Archive Retention Mode Enabled" as enabled and "Is Backup Retention Mode Enabled" as disabled
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE
			@backupLevel = 4        -- BACKUP_LEVEL_SYNTHETIC_FULL
			AND
			EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId = appId AND modified = 0 AND attrName = 'Is Turbo Subclient' AND attrVal = '1' AND cs_attrName = CHECKSUM(N'Is Turbo Subclient') )
			AND
			EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId = appId AND modified = 0 AND attrName = 'Is Archive Retention Mode Enabled' AND attrVal = '1' AND cs_attrName = CHECKSUM(N'Is Archive Retention Mode Enabled') )
			AND
			EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId = appId AND modified = 0 AND attrName = 'Is Backup Retention Mode Enabled' AND attrVal = '0' AND cs_attrName = CHECKSUM(N'Is Backup Retention Mode Enabled') )
			UNION ALL
			SELECT
				appId,0,''
			FROM #appIdTbl
			WHERE
			@backupLevel = 4        -- BACKUP_LEVEL_SYNTHETIC_FULL
			AND
			EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId = appId AND modified = 0 AND attrName = 'Extended Storage Policy Retention' AND attrVal = '1' AND cs_attrName = CHECKSUM(N'Extended Storage Policy Retention') )
			AND
			EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId = appId AND modified = 0 AND attrName = 'Is Archive Retention Mode Enabled' AND attrVal = '1' AND cs_attrName = CHECKSUM(N'Is Archive Retention Mode Enabled') )
			AND
			EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId = appId AND modified = 0 AND attrName = 'Is Backup Retention Mode Enabled' AND attrVal = '0' AND cs_attrName = CHECKSUM(N'Is Backup Retention Mode Enabled'))
			UNION
			--16. For non-Netapp Vendor Allow jobs ONLY on default backup set
			SELECT
				appId,0,''
			FROM #appIdTbl APP
			INNER JOIN MMNDMPHostInfo DMP ON DMP.ClientId = APP.clientId
AND APP.appTypeId = 13
				AND DMP.OSType <> 2	--OS_TYPE_NETAPP
			JOIN APP_BackupSetName BS ON BS.id = APP.backupsetId AND (BS.status & 8) <> 8		-- CV_STATUS_DEFAULT
			WHERE
			EXISTS (SELECT 1 FROM APP_AdvanceSettings WHERE keyName = 'bMultiBackupsetAllowInc' AND relativePath = 'CommServDB.Console' AND type = 'BOOLEAN' AND cast([value] as nvarchar(32)) = N'true' AND enabled = 1 AND deleted = 0 AND entityType = 3 AND entityId = 2)
			--17. Filter the default subclient for below iDA which is non commandline subclient
			UNION
			SELECT
				appId,0,''
			FROM #appIdTbl
WHERE apptypeId = 79
				AND (STATUS & 8) = 8		-- CV_STATUS_DEFAULT
				AND (STATUS & 64) = 0	    -- CV_STATUS_CMD_LINE
		) DeletedAppIds
		ON DeletedAppIds.appID = appIdTbl.appId
	--9. PART2 If operation type is transaction log, skip all the other subclients. BKP_OP_TRAN_LOG = 5
	--MR:152132 - Allow transaction log to run with other lotus notes bakcups when job is started from schedule policy
	-- If it is not transaction log, skipp TL subclient when doing backup for all sub clients
	IF EXISTS (
				SELECT 1
				FROM TM_AssocEntity
				WHERE taskId = @i_taskId
					AND (@taskType & 4) = 0
					AND subclientId = 0
					AND (
						clientGroupId <> 0
						OR clientId <> 0
					)
				UNION
				--SCHEDULE IS FOR ANY NON TL AGENT, WE NEED TO REMOVE TL SUBCLIENT
				--WHEN SCHEDULE IS FOR ALL AGENTS THERE WON'T BE ANY ENTRY IN TM_AssocFilter
				SELECT 1
				FROM TM_AssocEntity E
				JOIN TM_AssocFilter F ON F.taskId = E.taskId
				WHERE E.taskId = @i_taskId
					AND (@taskType = 4)
					AND F.filter_value <> 104
			)
	BEGIN
		DELETE
		FROM #appIdTbl
		WHERE appTypeId IN (6, 51, 59, 77)		-- UNUSED_CV_APPTYPE_6,UNUSED_CV_APPTYPE_51,CV_APPTYPE_LOTUS_DOMINO6_DATABASE,CV_APPTYPE_UNIX_LOTUSNOTES6_DB
			AND NOT EXISTS (
				SELECT filter_value
				FROM TM_AssocFilter
				WHERE filter_type = 1
					AND filter_value = 104
					AND taskId = @i_taskId --FILTER_AppGroup= 3
				)
			AND (STATUS & 128) = 128	--CV_STATUS_TRAN_LOG			0x0080
	END
	--10. Filter the FFG subclient MR 72616 - Backup from instance level for all s/c should exclude FFG s/c
	DELETE
	FROM #appIdTbl
	WHERE appTypeId = 81	--_CV_APPTYPE_MSSQL
		AND (
			EXISTS (
				SELECT filter_value
				FROM TM_AssocFilter
				WHERE filter_type = 3
					AND filter_value = 1
					AND taskId = @i_taskId --FILTER_Subclient= 3 --FIL_SC_SQL_FFG = 2
				)
			OR NOT EXISTS (
				SELECT *
				FROM TM_AssocEntity TA
				WHERE TA.taskId = @i_taskId
					AND TA.subclientId = #appIdTbl.appId
				)
		)
		AND appId IN (
			SELECT componentnameid
			FROM APP_SubclientProp
			WHERE componentnameid = appId
				AND modified = 0
				AND attrName = N'mssql subclient type'
				AND cs_attrName = CHECKSUM(N'mssql subclient type')
				AND attrVal IN (N'2', N'3')	--CV_FILE_FILEGROUP_SUBCLIENT, CV_DB_ARCHIVE
			)
	--11. Filter the DB subclient
	DELETE
	FROM #appIdTbl
	WHERE appTypeId = 81	--_CV_APPTYPE_MSSQL
		AND EXISTS (
			SELECT filter_value
			FROM TM_AssocFilter
			WHERE filter_type = 3
				AND filter_value = 2
				AND taskId = @i_taskId --FILTER_Subclient= 3 --FIL_SC_SQL_DB  = 3
		)
		AND appId IN (
			SELECT componentnameid
			FROM APP_Subclientprop
			WHERE componentnameid = appId
				AND modified = 0
				AND attrName = N'mssql subclient type'
				AND cs_attrName = CHECKSUM(N'mssql subclient type')
				AND attrVal = N'1'
		)
	--Filter if client is a VSA discovered V2 VM and backup level is not synthfull
	--Filter only if the instance is a discovered instance.
	DELETE a
	FROM #appIdTbl a
		INNER JOIN App_ClientProp CP(NOLOCK) ON CP.componentNameId = a.clientId
			AND CP.attrName = 'Virtual Server Discovered Clients'
			AND CP.attrVal = '1'
			AND CP.modified = 0
		INNER JOIN App_ClientProp CP1(NOLOCK) ON CP1.componentNameId = a.clientId
			AND CP1.attrName = 'IndexingV2_VSA'
			AND CP1.attrVal = '1'
			AND CP1.modified = 0
		INNER JOIN APP_IDAName IDA(NOLOCK) ON IDA.clientId = a.clientId
		LEFT JOIN APP_InstanceProp PROP(NOLOCK) ON PROP.componentNameId = a.instanceId
			AND PROP.attrName = 'Virtual Server Instance Type'
			AND PROP.attrVal <> ''
			AND PROP.modified = 0
		WHERE PROP.attrVal IS NULL
AND @backupLevel <> 4
AND a.appTypeId = 106
	-- For Office 365 subclients filter out all subclients if the request is for FULL or Synthfull
IF @backupLevel IN ( 4, 1)
	BEGIN
		DELETE a
		FROM #appIdTbl a
			INNER JOIN App_ClientProp CP(NOLOCK) ON CP.componentNameId = a.clientId
				AND CP.attrName = 'Office 365 Pseudo Client'
				AND CP.attrVal = '1'
				AND CP.modified = 0
	END
	--For Activate FSO do not start any jobs for schedules which are not command line
	DELETE a
		FROM #appIdTbl a
		INNER JOIN APP_SubclientProp SCP(NOLOCK) ON SCP.componentNameId = a.appId
			AND SCP.attrName = 'adHoc One Time Archive'
			AND SCP.attrVal = '1'
			AND SCP.modified = 0
AND @i_initFrom <> 3
			AND @taskType in (2,4)
			AND SCP.cs_attrName = checksum(N'adHoc One Time Archive')
	-- For SQL Analysis services and Azure DataBase and Azure Managed Database Engines, only FULL backup is supported. If backup level is something other than FULL, then filter all analysis services and azure db engine subclients.
	-- ANALYSIS_SERVER
	-- For cloud SQL subclients associated to plan data schedule policy backup level is converted to full in tm_getbackupentity
	IF @isPlanDataSchedulePolicy <> 1
	BEGIN
DELETE FROM #appIdTbl WHERE  bkpLevel <> 1 AND
		(
			(
				EXISTS (SELECT 1 FROM APP_instanceprop WHERE componentNameId=instanceId AND attrName='MSSQL Server Type' AND modified=0 AND (attrVal in ('Analysis Services', 'Azure DataBase Engine', 'Aws DataBase Engine', 'Azure Managed DataBase Engine'  ) ) ) -- Differential is not supported for blocklevel subclients
			)
		)
	END
	--16. Do not run backups for subclients with no storage policy
	IF (@doNotStartBackupOnSCWithNoStoragePolicy=1)
	BEGIN
		DELETE appIdTbl FROM #appIdTbl appIdTbl INNER JOIN archGroup ARG WITH (NOLOCK) ON appIdTbl.storagePolicy = ARG.id WHERE (appIdTbl.storagePolicy = 1 AND ARG.defaultCopy = 0)
AND (@isSPAMAXDBBackupSupported = 0 OR appTypeId!= 79 ) -- For SAP MAX DB default subclient doesnt have storage policy set.
	END
	IF @isSchedulePolicyAssociatedToPlan = 1
	BEGIN
DECLARE @logSchedPolicy INT = ISNULL((SELECT 1 FROM APP_PlanProp WITH(NOLOCK) WHERE attrName = 'Log schedule policy' AND attrVal = CAST(@i_taskId AS NVARCHAR(10))),0)
		--Plan based association filter for both data schedule policy and log schedule policy
		DELETE appIdTbl
		FROM #appIdtbl appIdTbl
		INNER JOIN
		(
			-- fIsLogCmdSubclient AND NOT SAP HANA
			SELECT A.appId FROM #appIdTbl A
WHERE A.STATUS & 0x1000 > 0
AND A.appTypeId != 135
			-- fIsCmdSubClient AND NOT INFORMIX
			UNION ALL
			SELECT A.appId FROM #appIdTbl A
WHERE A.STATUS & 0x0040 > 0
AND A.appTypeId != 3
			--bIsDumpBackupSetSubclient
			UNION ALL
			SELECT A.appId FROM #appIdTbl A
WHERE A.appTypeId = 125
			AND NOT EXISTS (SELECT 1 FROM APP_BackupSetProp WITH(NOLOCK) WHERE componentNameId=A.backupsetId AND attrName='FS BackupSet' AND attrVal='1' AND modified=0)
			AND NOT EXISTS (SELECT 1 FROM APP_ClientProp WITH(NOLOCK) WHERE componentnameId = A.clientId AND attrName = 'Client App Type' AND attrVal = '1' AND modified = 0)
			--isDBThinClient
			UNION ALL
			SELECT A.appId FROM #appIdTbl A
			INNER JOIN APP_ClientProp CP WITH(NOLOCK) ON A.clientId = CP.componentnameId
			WHERE CP.attrName = 'DB Thin Client' AND CP.modified = 0 AND CP.attrVal ='1'
			--bIsIBMIDRSubclient
			UNION ALL
			SELECT A.appId FROM #appIdTbl A
			INNER JOIN APP_Client C WITH(NOLOCK) ON A.clientId = C.id AND C.simOperatingSystemId = 118 --N_CV_OS_ID_ISERIES
WHERE A.STATUS & 0x0100 = 0x0100
		)TBD
		ON TBD.appId = appIdTbl.appId
		IF OBJECT_ID('tempdb.dbo.#planAppIDsToDel') IS NOT NULL DROP TABLE #planAppIDsToDel
		CREATE TABLE #planAppIDsToDel( appId INT )
		CREATE CLUSTERED INDEX planAppIDsToDel_ids ON #planAppIDsToDel (appId)
		-- Temp table to remove duplicate operation in below 2 deletes
		INSERT INTO #planAppIDsToDel(appId)
		-- fIsLogCmdSubclient AND SAP HANA
		SELECT A.appId FROM #appIdTbl A
WHERE A.STATUS & 0x1000 > 0
AND A.appTypeId = 135
		-- fIsCmdSubClient AND INFORMIX
		UNION ALL
		SELECT A.appId FROM #appIdTbl A
WHERE A.STATUS & 0x0040 > 0
AND A.appTypeId = 3
		--Plan based association filter for data schedule policy
		IF @logSchedPolicy = 0
		BEGIN
			DELETE appIdTbl
			FROM #appIdtbl appIdTbl
			INNER JOIN
			(
				SELECT appId FROM #planAppIDsToDel
				--fIsLogSubClient
				UNION ALL
				SELECT A.appId FROM #appIdTbl A
WHERE A.STATUS & 0x40000 > 0
			)TBD
			ON TBD.appId = appIdTbl.appId
		END
		--Plan based association filter for Log schedule Policy
		ELSE
		BEGIN
			IF @isDumpSweepEnabled = 0
			BEGIN
				DELETE appIdTbl
				FROM #appIdtbl appIdTbl
				INNER JOIN  #planAppIDsToDel PDEL ON PDEL.appId = appIdTbl.appId
			END
			--bIsOracleRegularSubClient
			DELETE A
			FROM #appIdtbl A
WHERE A.STATUS & 0x40000 = 0
AND A.appTypeId IN (22,80,61)
		END
	END
	-- Filter NFS object store subclient from plan data schedule policy
	IF @isSchedulePolicyAssociatedToPlan = 1
	BEGIN
		DELETE appIdTbl FROM #appIdTbl appIdTbl
		INNER JOIN App_ClientProp CP (nolock) ON CP.componentNameId = appIdTbl.clientid AND CP.attrname = N'System Drive Type' AND CP.modified = 0 -- #define CV_PROP_SYSTEM_DRIVE_TYPE "System Drive Type"
		AND CP.attrVal='4'
	END
	-- REMOVE SUBCLIENTS IN SCHEDULE whose activity is disabled.
	-- Sometimes when key JMDontStartBkpsOnDisabledAgents is set on CS and activity is disabled on a client, then for that subclient we wont have entries for latest jobs in jmbkpstats.
	-- So last run time for these subclients will never be updated. And TM_getjobcompletedpatterns would keep requesting backups for these subclients again and again.
	-- We are checking for disabled backup activity at subclient level here and if so, we are not triggering backups.
	DECLARE @rowCount INT = 0
	DECLARE @drSubclientId int = 1
	DECLARE @defaultInstanceId   INT = 1
	DECLARE @doNotStartBkpsOnDisabledAgents INT = 0 -- Keep default value consistent with java gui behaviour.
	SELECT @doNotStartBkpsOnDisabledAgents = value FROM GXGlobalParam (NOLOCK) WHERE name = N'JMDontStartBkpsOnDisabledAgents' AND modified = 0
	SET @drSubclientId  = ISNULL((select top 1 id from APP_Application
							where appTypeId= 1000),1) --CV_APPTYPE_COMMSRVMGMT
	SELECT TOP 1 @defaultInstanceId = id
		FROM APP_InstanceName WHERE name = 'DefaultInstanceName'
	-- In case you have all activity or backup activity disabled at commcell level then remove all entries. const string JOBS_COMPLETE_IF_ACT_DISABLED              = "JobsCompleteIfActivityDisabled";
	SET @queueJobsIfActivityDisabled = ISNULL((SELECT top 1 value FROM GXGlobalParam WHERE name = N'JobsCompleteIfActivityDisabled' AND modified = 0), 0)
SELECT @runTaskOptionIgnoreActivityCheck = CONVERT(INT,value) FROM #runTaskOptions WHERE optionID = 1559962334 AND Isnumeric(value)=1
	-- If queue jobs option isnt selected or its continous schedule, delete entities whose activity isnt enabled.
	-- Before changes in SP11 hot fix form 1197, If key JMDontStartBkpsOnDisabledAgents has value 0 then on activity disabled, stats entry is created. But after that change its no longer done. So making change to avoid checking for activity when key is set with value 0.
	-- For automatic synthetic full schedules always remove subclients whose activity is disabled. Irrespective of whether setting "Do not start backup on disabled clients" is set or not set. Thats because if we include disabled subclients,
	-- then JM does runTaskOnEntity to trigger synthetic full job and if activity is disabled then runTask method doesnt trigger job. So filtering disabled subclients from TM itself.
	IF (@freqInterval > 0 OR
		@autoCopy = 1 OR
		(@queueJobsIfActivityDisabled=0 AND @taskType in (2,4) AND @doNotStartBkpsOnDisabledAgents <> 0  ) -- TaskType:: schedule,TaskType:schedulepolicy
	) AND (@runTaskOptionIgnoreActivityCheck=0) --If run task option has option to ignore activity control STARTUPOPTIONS_FORCE_RUN_ON_DISABLED_ACTIVITY then dont do below
	BEGIN
		IF EXISTS(select 1 from JMJobAction where
clientId = 1 AND appType =  0 AND mediaAgentID = 1 AND appId = @drSubclientId AND clientGroupId = 0 AND instanceId=@defaultInstanceId
			and (opType = 1 Or opType=4) and action = 1)
		BEGIN
			-- Activity at CS level is disabled. So dont start any jobs.
			TRUNCATE TABLE #appIdTbl
		END
		ELSE
		BEGIN
			-- If activity is disabled at client level or client/ida level or at subclient level, then set it for subclient.
			-- Need to add check for instance also so that disabling activity on one instance doesnt disable backup on other instances on same client/apptype.
			DELETE appIdTbl
			from #appIdTbl appIdTbl INNER JOIN JMJobAction J with (nolock)
ON  J.opType = 4 AND J.action = 1 AND ((J.clientId = appIdTbl.ClientId AND J.appType IN (appIdTbl.AppTypeId, 0)) ) AND J.mediaAgentID = 1 AND J.instanceId=@defaultInstanceId AND J.appId = @drSubclientId
			DELETE appIdTbl
			from #appIdTbl appIdTbl INNER JOIN JMJobAction J with (nolock)
ON  J.opType = 4 AND J.action = 1 AND J.clientId = 1 AND J.appType =  0 AND J.mediaAgentID = 1 and J.appId = appIdTbl.appId and appIdTbl.appId>0 AND J.instanceId=@defaultInstanceId
			-- If activity is disabled at ida level (without client), then set it for subclient.
			DELETE appIdTbl
			from #appIdTbl appIdTbl INNER JOIN JMJobAction J with (nolock)
ON  J.clientId = 1 AND J.appType = appIdTbl.AppTypeId AND J.mediaAgentID = 1 and J.opType = 4 AND J.appId = @drSubclientId AND J.clientGroupId = 0 AND J.action = 1 AND J.instanceId=@defaultInstanceId
			-- If activity is disabled at instance level, then set it for subclient.
			DELETE appIdTbl
			from #appIdTbl appIdTbl INNER JOIN JMJobAction J with (nolock)
ON  J.opType = 4 AND J.action = 1 AND J.clientId =  1 AND J.appType=0 and J.InstanceId=appIdTbl.instanceId and J.mediaAgentID = 1
			AND J.appId = @drSubclientId AND J.clientGroupId = 0 and appIdTbl.instanceId<>1
			-- Check at client group level. And disable activity for all clients of disabled client groups
			DELETE appIdTbl
			from #appIdTbl appIdTbl INNER JOIN App_clientGroupAssoc CGA with (nolock)
			on CGA.clientId = appIdTbl.clientId
			INNER JOIN JMJobAction J with (nolock)
			on J.clientGroupId=CGA.clientGroupId
where J.opType = 4 AND J.action = 1 AND J.clientId = 1 and J.appType = 0 and J.mediaAgentId = 1 AND J.appId = @drSubclientId AND J.instanceId=@defaultInstanceId
			-- If subclient is associated to subclient policy and activity control is disabled on subclient policy sc, then dont start backup
			DELETE appIdTbl
			FROM #appIdTbl appIdTbl INNER JOIN APP_SubClientProp (NOLOCK) ON APP_SubClientProp.ComponentNameId = appIdTbl.appId AND APP_SubClientProp.attrName = 'Associated subclient Policy' AND APP_SubClientProp.modified = 0
			AND APP_SubClientProp.cs_attrName = CHECKSUM(N'Associated subclient Policy')
			INNER JOIN JMJobAction J with (nolock)
ON  J.opType = 4 AND J.action = 1 AND J.clientId = 1 AND J.appType =  0 AND J.mediaAgentID = 1 and J.appId = APP_SubClientProp.attrVal and CAST(APP_SubClientProp.attrVal AS INT)>0 AND J.instanceId=@defaultInstanceId
		END
	END
	--If it is a VSA App Aware subclient then convert synthfull to Full Moving this code here as it needs to be done before expansion
	UPDATE appIdTbl
SET appIdTbl.bkpLevel = 1
	FROM #appIdTbl appIdTbl
INNER JOIN APP_SubclientProp SCP WITH(NOLOCK) ON SCP.componentNameId = appIdTbl.appId  AND appIdTbl.bkpLevel = 4 AND appIdTbl.appTypeId = 106
	AND SCP.attrName = N'VS Collect Vss Meta Data' AND SCP.attrVal='1' AND SCP.modified = 0 AND SCP.cs_attrName = CHECKSUM(N'VS Collect Vss Meta Data')
	--CMR : 247236 when request is submitted from navigating to parent subclient for synthfull, then we should not filter the parent subclient
IF @i_initFrom = 1
	BEGIN
		IF EXISTS( SELECT 1 FROM #App_InputAssocs)
		BEGIN
			IF EXISTS(
					 SELECT 1 FROM #App_InputAssocs A
						JOIN #appIdTbl APP ON
						(
							(A.appId = APP.appId OR A.appId = 0) AND
							(A.instanceId = APP.instanceId OR A.instanceId = 0) AND
							(A.backupsetId = APP.backupsetId OR A.backupsetId = 0) AND
							(A.appTypeId = APP.appTypeId OR A.AppTypeId = 0) AND
							(A.clientId = APP.clientId)
						)
						JOIN APP_InstanceProp IPROP (NOLOCK) ON IPROP.componentNameId = APP.instanceId AND IPROP.attrName = 'Virtual Server Instance Type'  AND IPROP.attrVal <> '' AND IPROP.modified = 0
						JOIN APP_ClientProp (NOLOCK) CP ON CP.componentNameId = APP.clientId AND CP.attrName = 'IndexingV2_VSA' AND CP.modified = 0 AND CP.attrVal = '1' -- Indexing V2
WHERE APP.bkpLevel = 4 -- Synthetic full
AND APP.appTypeId =  106 -- Virtual Server Agents
					 )
			BEGIN
				--For parent subclients associated to the schedule policy, remove ones that are absent in the immediate request
				 DELETE appIdTbl
				 FROM #appIdTbl appIdTbl
				 LEFT JOIN #App_InputAssocs InputAssocs
				 ON (InputAssocs.appId = appIdTbl.appId OR InputAssocs.appId = 0) AND
					(InputAssocs.instanceId = appIdTbl.instanceId OR InputAssocs.instanceId = 0) AND
					(InputAssocs.backupsetId = appIdTbl.backupsetId OR InputAssocs.backupsetId = 0) AND
					(InputAssocs.appTypeId = appIdTbl.appTypeId OR InputAssocs.appTypeId = 0) AND
					(InputAssocs.clientId = appIdTbl.clientId)
				 WHERE InputAssocs.clientId IS NULL
				 SET @isVSAV2ParentfilterDone = 1
			END
		END
	END
	--For synthetic full jobs get all the VM clients created from VSA from discovery
	INSERT INTO #App_VmClientInfoTable
	(vmClientId, parentBackupsetId, childBackupsetId, parentSubclientId)
	SELECT VB.VMClientId, VB.ParentBackupSetId, VB.ChildBackupSetId, A.appId
	FROM #appIdTbl A
	JOIN APP_VMBackupSet (NOLOCK) VB ON VB.ParentBackupSetId = A.backupsetId
AND A.appTypeId = 106
AND A.bkpLevel = 4
	JOIN ( SELECT componentNameId, appId
			FROM
			(
				SELECT componentNameId, CAST(attrVal  AS INT) appId, ROW_NUMBER() OVER (PARTITION BY P.componentNameId ORDER BY P.created DESC) rowNum
				FROM APP_ClientProp P (NOLOCK)
				WHERE P.attrName = 'VSA Discover Subclient ID'
				AND P.modified = 0
				AND ISNUMERIC(P.attrVal) = 1
				AND LEN(P.attrVal) <= 10
			) CP WHERE CP.rowNum = 1 -- WHEN VM IS MOVED TO ANOTHER SUBCLIENT THERE COULD BE TWO ENTRIES IN PROPERTY TABLE WHEN BACKUP IS RUN BEFORE IN THE NEW SUBCLIENT THAN THE OLD SUBCLIENT.
									 -- MAKE SURE TO GET THE LATEST PROPERTY PER VM CLIENT
		  )DISC ON  DISC.componentNameId = VB.VMClientId AND A.appId = DISC.appId
	SET @rowCount = @@ROWCOUNT
	--MOVED FROM TM_GetBackupEntityInclude TO AVOID STARTING JOBS FOR VSA SUBCLIENT WITH ACTIVITY DISABLED
	--IF @rowCount > 0	--ALWAYS DO CHECK BECAUSE SCHEDULE OR POLICY CAN HAVE ASSOCIATION ONLY AT VM LEVEL
	BEGIN
		--MOVED VM CODE HERE SO THAT WE CHECK FOR ACTIVITY IN BELOW CHECK
		DECLARE @o_jobId INT = 0
		DECLARE @t_JobId TABLE( jobId bigint )	--Temp table
		DECLARE @parentVSABkpSetId INT = 0
		DECLARE @parentSubclientId INT = 0
		UPDATE #appIdTbl
		SET vmDiscovered  = 1
		WHERE clientId
		IN (
			SELECT A.clientId
			FROM #appIdTbl A
			JOIN APP_ClientProp P (NOLOCK) ON P.componentNameId = A.clientId
AND A.appTypeId = 106
AND A.bkpLevel = 4
			AND P.modified = 0
			AND ISNUMERIC(P.attrVal) = 1
			AND LEN(P.attrVal) <= 10
			AND P.attrName = 'VSA Discover Subclient ID'
			--AND P.attrVal = A.appId	--FOR ASSOCIATION AT VM LEVEL THERE MAY BE NO ASSOCIATION OF THE VSA SUBCLIENT
			JOIN App_ClientProp CP1(NOLOCK) ON CP1.componentNameId = A.clientId --We should also check for VSA V2 in case of discovered VM
			AND CP1.attrName = 'IndexingV2_VSA'
			AND CP1.attrVal = '1'
			AND CP1.modified = 0
		)
		UPDATE #App_VmClientInfoTable
		SET v2_1_Client = 1
		WHERE parentSubclientId IN
		(
			SELECT A.appId FROM #appIdTbl A
			JOIN APP_ClientProp (NOLOCK) P ON P.componentNameId = A.clientId
			AND P.attrName = 'Using Pseudo client version V2.1'
			AND P.attrVal = '1'
		)
		--ADD VM CLIENTS TO THE LIST OF ENTITIES.  VSA PARENT BACKUPSET WILL BE REMOVED IN FILTER BODY
		INSERT INTO #appIdTbl
		(appId, appTypeId, clientId, instanceId, backupsetId, STATUS, bkpLevel, storagePolicy, timezoneName, taskId, vmDiscovered)
		SELECT DISTINCT APP.id, APP.appTypeId, APP.clientId, APP.instance, APP.backupSet, APP.subclientStatus, A.bkpLevel, APP.logArchGrpID, A.timezoneName, @i_taskId, 1 vmDiscovered
		FROM #App_VmClientInfoTable VB
		JOIN APP_Application APP (NOLOCK) ON APP.backupSet = VB.childBackupsetId
JOIN #appIdTbl A ON A.backupsetId = VB.parentBackupsetId AND A.appTypeId = 106 AND A.bkpLevel = 4 --Do this for only synthetic full
		WHERE
		--MR:215294 - DO NOT ADD IF SUBCLIENT DOES NOT ALLOW BACKUP
		(APP.subclientStatus & 32) = 0	--CV_STATUS_HIDDEN
		AND (APP.subclientStatus & 16) = 0 --CV_STATUS_DUMMY
		AND (APP.subclientStatus & 4) = 0 --CV_STATUS_DELETED
		SET @rowCount = @@ROWCOUNT
		--For synthfulls if it is from Run immediately or command line or JM submitted OSC, we need to filter out based on what is provided in input associations
		--Requests from JM might contain child vm subclients as part of request, we wait for expansion above and then filter.
		IF @isVSAV2ParentfilterDone = 0
		BEGIN
			IF EXISTS( SELECT 1 FROM #App_InputAssocs)
			BEGIN
			 DELETE appIdTbl
			 FROM #appIdTbl appIdTbl
			 LEFT JOIN #App_InputAssocs InputAssocs
			 ON (InputAssocs.appId = appIdTbl.appId OR InputAssocs.appId = 0) AND
				(InputAssocs.instanceId = appIdTbl.instanceId OR InputAssocs.instanceId = 0) AND
				(InputAssocs.backupsetId = appIdTbl.backupsetId OR InputAssocs.backupsetId = 0) AND
				(InputAssocs.appTypeId = appIdTbl.appTypeId OR InputAssocs.appTypeId = 0) AND
				(InputAssocs.clientId = appIdTbl.clientId)
			 WHERE InputAssocs.clientId IS NULL
			END
		END
		IF @isAutomaticSchedule = 0
		BEGIN
			DECLARE getParentVSAJobId CURSOR FOR
				SELECT DISTINCT parentBackupsetId, parentSubclientId FROM #App_VmClientInfoTable WHERE v2_1_Client = 1
			OPEN getParentVSAJobId
			FETCH NEXT FROM getParentVSAJobId INTO @parentVSABkpSetId, @parentSubclientId
			WHILE @@FETCH_STATUS = 0
			BEGIN
				INSERT INTO @t_JobId EXEC @o_jobId = TM_GetNextJobId
				UPDATE #appIdTbl
				SET parentJobId = @o_jobId,
				parentAppID = @parentSubclientId
WHERE bkpLevel = 4
AND appTypeId = 106
				AND backupsetId = @parentVSABkpSetId
				AND appId = @parentSubclientId
				FETCH NEXT FROM getParentVSAJobId INTO @parentVSABkpSetId, @parentSubclientId
			END
			CLOSE getParentVSAJobId
			DEALLOCATE getParentVSAJobId
		END
		UPDATE #appIdTbl
		SET parentJobId = T.parentJobId,
		parentAppId = T.parentSubclientId
		FROM
		(
			SELECT V.childBackupsetId, V.parentSubclientId, A.parentJobId, V.v2_1_Client
			FROM #appIdTbl A
			JOIN #App_VmClientInfoTable V ON V.parentSubclientId = A.appId
		) T
		JOIN #appIdTbl  A ON A.backupsetId = T.childBackupsetId
WHERE bkpLevel = 4
AND appTypeId = 106
		AND backupsetId = T.childBackupsetId
		AND vmDiscovered = 1
		AND v2_1_Client = 1
		--NOW WE NEED TO RECHECK ACTIVITY FOR THE VM CLIENTS
		-- For automatic synthetic full schedules always remove subclients whose activity is disabled. Irrespective of whether setting "Do not start backup on disabled clients" is set or not set. Thats because if we include disabled subclients,
		-- then JM does runTaskOnEntity to trigger synthetic full job and if activity is disabled then runTask method doesnt trigger job. So filtering disabled subclients from TM itself.
		IF @rowCount > 0 AND @queueJobsIfActivityDisabled=0 AND @taskType in (2,4) AND (@doNotStartBkpsOnDisabledAgents <> 0  OR @autoCopy=1) -- TaskType:: schedule,TaskType:schedulepolicy
		BEGIN
			DELETE #appIdTbl
			OUTPUT DELETED.appId,DELETED.clientId, DELETED.parentjobId, DELETED.parentAppid, 0, '' INTO #deletedVMAppIds -- GUIMSG_SYNTHETIC_FULL_VM_ERROR
			WHERE appId IN
			(
				-- If activity is disabled at client level or client/ida level or at subclient level, then set it for subclient.
				SELECT appIdTbl.appId
				from #appIdTbl appIdTbl INNER JOIN JMJobAction J with (nolock)
ON  J.opType = 4 AND J.action = 1 AND ((J.clientId = appIdTbl.ClientId AND J.appType IN (appIdTbl.AppTypeId, 0)) ) AND J.mediaAgentID = 1
				UNION
				SELECT appIdTbl.appId
				from #appIdTbl appIdTbl INNER JOIN JMJobAction J with (nolock)
ON  J.opType = 4 AND J.action = 1 AND J.clientId = 1 AND J.appType =  0 AND J.mediaAgentID = 1 and J.appId = appIdTbl.appId and appIdTbl.appId>0
				-- If activity is disabled at ida level (without client), then set it for subclient.
				UNION
				SELECT appIdTbl.appId
				from #appIdTbl appIdTbl INNER JOIN JMJobAction J with (nolock)
ON  J.clientId = 1 AND J.appType = appIdTbl.AppTypeId AND J.mediaAgentID = 1 and J.opType = 4 AND J.appId = @drSubclientId AND J.clientGroupId = 0 AND J.action = 1
				-- If activity is disabled at instance level, then set it for subclient.
				UNION
				SELECT appIdTbl.appId
				from #appIdTbl appIdTbl INNER JOIN JMJobAction J with (nolock)
ON  J.opType = 4 AND J.action = 1 AND J.clientId =  1 AND J.appType=0 and J.InstanceId=appIdTbl.instanceId and J.mediaAgentID = 1
				AND J.appId = @drSubclientId AND J.clientGroupId = 0 and appIdTbl.instanceId<>1
				-- Check at client group level. And disable activity for all clients of disabled client groups
				UNION
				SELECT appIdTbl.appId
				from #appIdTbl appIdTbl INNER JOIN App_clientGroupAssoc CGA with (nolock)
				on CGA.clientId = appIdTbl.clientId
				INNER JOIN JMJobAction J with (nolock)
				on J.clientGroupId=CGA.clientGroupId
where J.opType = 4 AND J.action = 1 AND J.clientId = 1 and J.appType = 0 and J.mediaAgentId = 1
			)
		END
		-- Querying below only for VSA synthetic full scenario.
IF EXISTS(SELECT 1 FROM #appIdTbl WHERE appTypeId = 106
AND bkpLevel = 4 )
		AND
		(
			@autoCopy = 0 --NOT AN AUTOMATIC SCHEDULE
			OR
			(
				@autoCopy = 1 --AND AUTOMATIC SCHEDULE AND IT IS A MANUAL REQUEST
				AND
@i_initFrom IN (1, 2, 6)
			)
		)
		BEGIN
			INSERT INTO #App_SubclientInfoTable
			(subclientId, FullRefTime, IncrRefTime)
			SELECT componentNameId, ISNULL( [sys:last full], 0) AS FullRefTime, ISNULL( [sys:last incr], 0) AS IncrRefTime
			FROM
			(
				SELECT componentNameId, attrName, attrVal
				FROM #appIdTbl APP
				JOIN APP_SubClientProp P WITH (NOLOCK) ON P.componentNameId = APP.appId
				WHERE P.modified = 0
				AND P.attrName IN ('sys:last full', 'sys:last incr')
				AND P.cs_attrName IN (CHECKSUM(N'sys:last full'), CHECKSUM(N'sys:last incr'))
AND APP.appTypeId = 106
AND APP.bkpLevel = 4
				AND APP.vmDiscovered = 1
			) AS subclient
			PIVOT
			(
				MAX(attrVal)
				FOR attrName IN ([sys:last full], [sys:last incr])
			) AS PVT
			--INSERT FOR AGENTS THAT SUPPORT SYNTHETIC FULL
			INSERT INTO #TM_SyntheticInfoTable
			(subclientId, backupsetId, instanceId, appTypeId, clientId, lastSynthetic, lastFull, lastIncremental, bkpLevel, status, syntheticInterval)
			SELECT T.appId, T.backupsetId, T.instanceId, T.appTypeId, T.clientId, T.lastSynthetic, ISNULL(T.fullRefTime, 0) lasFull, ISNULL(T.incrRefTime, 0) lastIncremental, T.bkpLevel, T.STATUS, @syntheticInterval
			FROM
			(
			SELECT APP.appId, APP.backupsetId, APP.instanceId, APP.appTypeId, APP.clientId, ISNULL(MAX(JS.servStartDate), 0) lastSynthetic,
				SPROP.FullRefTime fullRefTime, SPROP.incrRefTime incrRefTime,
			APP.bkpLevel,
			APP.status
			FROM #appIdTbl APP
			LEFT OUTER JOIN JMBkpStats JS (NOLOCK) ON JS.appId = APP.appId
				AND JS.bkpLevel IN (1/*FULL*/, 64/*SYNTHETIC_FULL*/, 128 /*SYNTHETIC_FULL_AND_INCR*/)
				AND JS.status in (1, 3, 14) -- JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
				AND JS.opType <> 60 --DON'T CONSIDER BACKUP COPY JOBS
				--AND JS.dataStatus = 0 -- Considering Aged Job Status
			LEFT OUTER JOIN #App_SubclientInfoTable SPROP ON SPROP.subclientId = APP.appId
			GROUP BY APP.appId, APP.backupsetId, APP.instanceId, APP.appTypeId, APP.clientId, SPROP.FullRefTime, SPROP.IncrRefTime, APP.bkpLevel, APP.status
			) T
			--REMOVE ANY SUBCLIENTS WITHOUT FULL BACKUP JOB OR NEW INCREMENTAL
			DELETE #appIdTbl
OUTPUT DELETED.appId,DELETED.clientId, DELETED.parentjobId, DELETED.parentAppid, (4569 | (CAST(POWER(2, 24) AS BIGINT) * 35)), '' INTO #deletedVMAppIds
			WHERE appId
			IN
			(
				SELECT JS.subclientId
				FROM #TM_SyntheticInfoTable JS
				JOIN #appIdTbl A ON A.appId = JS.subclientId
				WHERE
A.appTypeId = 106 -- Virtual Server Agents
				AND
A.bkpLevel = 4 -- Synthetic full
				AND
				A.vmDiscovered = 1 -- This property is for VSA V2 clients only
				AND
				(
					(
						--DELETE WHEN NO BACKUP JOB HAS RUN
						(
							JS.lastFull = 0
							--IF ALL FULLS HAVE BEEN DELETED CHECK FOR EXISTENCE OF OTHER JOB
							AND
							(
								JS.lastSynthetic = 0
								OR
								NOT EXISTS (
											SELECT TOP 1 1 FROM JMBkpStats (NOLOCK)
											WHERE status IN (1, 3, 14) --JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
											AND subclientId = JMBkpStats.appId
											AND dataStatus = 0 -- DATA_STATUS_VALID -- If there are no valid (unaged) jobs, then no need to run synthetic full on the VM.
											)
							)
						)
						OR
						(
							JS.lastSynthetic >= JS.lastIncremental
						)
					)
				)
			)
		END
	END
	INSERT INTO #deletedAppIdsErrorCode
	SELECT DISTINCT appId, errorCode, errorMessage
	FROM #deletedVMAppIds
	WHERE errorCode!=0
	--17. Remove parent VSA backupset for synthetic full jobs
	-- MR:245677 - Remove parent VSA subclients for V2 synthetic full always.
	DELETE A
OUTPUT DELETED.appId,(4570 | (CAST(POWER(2, 24) AS BIGINT) * 35)), '' INTO #deletedAppIdsErrorCode -- (4570 | (CAST(POWER(2, 24) AS BIGINT) * 35))
	FROM #appIdTbl A
	JOIN APP_InstanceProp IPROP (NOLOCK) ON IPROP.componentNameId = A.instanceId AND IPROP.attrName = 'Virtual Server Instance Type'  AND IPROP.attrVal <> '' AND IPROP.modified = 0
	JOIN APP_ClientProp (NOLOCK) CP ON CP.componentNameId = A.clientId AND CP.attrName = 'IndexingV2_VSA' AND CP.modified = 0 AND CP.attrVal = '1' -- Indexing V2
WHERE A.bkpLevel = 4 -- Synthetic full
AND A.appTypeId = 106 -- Virtual Server Agents
	-- If request comes from VSA subclient and you find GUIMSG_SYNTHETIC_FULL_VSA_SUBCLIENT_ERROR in #deletedAppIdsErrorCode, then remove from #deletedAppIdsErrorCode
	-- where errorCode is GUIMSG_SYNTHETIC_FULL_VM_ERROR
IF	EXISTS(SELECT 1 FROM #deletedAppIdsErrorCode WHERE errorCode = (4570 | (CAST(POWER(2, 24) AS BIGINT) * 35)))
			AND (
( @i_initFrom IN (1, 2 )
					AND (@taskType = 2 OR EXISTS( SELECT 1 FROM #App_InputAssocs) )
				)
				OR @taskType = 1
			)
	BEGIN
DELETE FROM #deletedAppIdsErrorCode WHERE errorCode = (4569 | (CAST(POWER(2, 24) AS BIGINT) * 35))
	END
	--- Remove subclients from deletedappids where subclients are not in input list.
	IF EXISTS( SELECT 1 FROM #App_InputAssocs)
	BEGIN
	 DELETE appIdTbl
	 FROM #deletedAppIdsErrorCode appIdTbl
	 INNER JOIN App_Application ON appIdTbl.appID = App_Application.id
	 LEFT JOIN #App_InputAssocs InputAssocs
	 ON (InputAssocs.appId = appIdTbl.appId OR InputAssocs.appId = 0) AND
		(InputAssocs.instanceId = App_Application.instance OR InputAssocs.instanceId = 0) AND
		(InputAssocs.backupsetId = App_Application.backupset OR InputAssocs.backupsetId = 0) AND
		(InputAssocs.appTypeId = App_Application.appTypeId OR InputAssocs.appTypeId = 0) AND
		(InputAssocs.clientId = App_Application.clientId)
	 WHERE InputAssocs.clientId IS NULL
	END
	-- If its immediate or run immediate or scheduled VSA backup job (and not automatic synthetic full)
	-- and parent job is created, then create stats for skipped VMs
	IF @isAutomaticSchedule = 0 AND EXISTS(SELECT 1 FROM #appIdTbl)
	BEGIN
		DECLARE @commcellID INT = 2
		-- 14 OpType : SYNTHFULL
		INSERT INTO JMJobStats(jobid, status, opType, commCellId, jobCategory, jobDescription, xmlJobInfo, subOpType, foreignToken)
		SELECT DISTINCT parentJobID, 1/*ACTIVE*/, 14 , @commCellID, 1 , '', '', 147 /*SYNTH_FULL_ADMIN*/, ''
		FROM #deletedVMAppIds
		WHERE parentJobId>0
		-- Populate JMQinetixUpdateStatus now based on indexing V2 child/parent jobs
		INSERT INTO JMQinetixUpdateStatus
        SELECT DISTINCT parentJobID, 2 /*commcellId*/, clientId, CL.name, 10 /*VMClientBkupStatus_SKIPPED*/, 9 /*QCATEGORY_SKIPPING*/, 0, 0, '', '', '', '', 0
		FROM #deletedVMAppIds deletedVMAppIds
		LEFT OUTER JOIN APP_Client CL ON CL.id = deletedVMAppIds.clientID
		WHERE parentJobId>0
	END
--- CODE ABOVE HERE IS FROM TM_GetBackupEntityFilter.spb  ----------
--- CODE BELOW HERE IS FROM AppFilterSubclientsWithoutContent.spb  ----------
	-----------------------------------------------
	--Filter Office365 subclients with no content--
	-----------------------------------------------
	if object_id('tempdb.dbo.#o365Apps') is not null DROP TABLE #o365Apps
	CREATE TABLE #o365Apps (appId INT,  appTypeId INT, instanceId INT, backupsetId INT, clientId INT, clVersion INT )
	CREATE CLUSTERED INDEX o365Apps_idx ON #o365Apps (appId)
	if object_id('tempdb.dbo.#o365Clients') is not null DROP TABLE #o365Clients
	CREATE TABLE #o365Clients (clientId INT)
IF EXISTS ( SELECT TOP 1 appId FROM #appIdTbl WHERE appTypeId IN (134, 137 , 78) )
	BEGIN
	--Get office 365 Pseudoclients
	INSERT INTO #o365Clients SELECT A.clientId FROM #appIdTbl A
	INNER JOIN App_ClientProp (NOLOCK) CP ON CP.componentNameId = A.clientId
	WHERe CP.attrname = 'Office 365 Pseudo Client' AND CP.attrVal = '1' AND CP.modified = 0
	--Get O365 subclients from V2 clients
	INSERT INTO #o365Apps SELECT A.appId, A.appTypeId, A.instanceId, A.backupsetId, A.clientId, 2
	FROM #appIdTbl A
	INNER JOIN #o365Clients C On A.clientId = C.clientId
	--Get Exchange non-O365 Clients
	INSERT INTO #o365Apps SELECT A.appId, A.appTypeId, A.instanceId, A.backupsetId, A.clientId, 1
	FROM #appIdTbl A
WHERE A.appTypeId = 137 AND A.clientId NOT IN (SELECT clientId FROm #o365Clients)
	--Get oneDrive V1 subclients
	INSERT INTO #o365Apps SELECT A.appId, A.appTypeId, A.instanceId, A.backupsetId,A.clientId , 1
	FROM #appIdTbl A
	INNER JOIN APP_InstanceProp (NOLOCK) I ON I.componentNameId = A.instanceId
WHERE  A.appTypeId = 134 AND I.attrName = 'Cloud Apps Instance Type' AND I.attrVal = '7' AND I.modified = 0
	AND A.clientId NOT IN (SELECT clientId from #o365Clients)
	--Get Sharepoint V1 subclients
	INSERT INTO #o365Apps SELECT A.appId, A.appTypeId, A.instanceId, A.backupsetId, A.clientId, 1
	FROM #appIdTbl A JOIN App_BackupsetName (NOLOCK) B ON B.id = A.backupsetId
WHERE  A.appTypeId = 78 AND (B.status & 0x80000 = 0x80000)
	AND A.clientId NOT IN (SELECT clientId from #o365Clients)
	--Filter Case manager clients, GDPR clients
    DELETE T FROM #o365Apps T
    JOIN APP_ClientProp (NOLOCK) CP ON CP.componentNameId = T.clientId
    WHERE  (CP.attrName = 'EDiscovery Subtype' AND CP.attrVal IN ('1', '2')) OR (CP.attrname = 'Case Manager Pseudo Client' AND CP.attrVal = '1') AND CP.modified = 0
	DELETE A
OUTPUT DELETED.appId, (195 | (CAST(POWER(2, 24) AS BIGINT) * 72)), '' INTO #deletedAppIdsErrorCode
	FROM #appIdTbl A
	JOIN App_Application (NOLOCK) App On A.appId = App.id
	  WHERE A.appId IN (SELECT distinct appId from #o365Apps)
AND (((App.subclientStatus & 0x00002) = 0x00002) OR ((App.subclientStatus & 0x00004) = 0x00004) OR ((App.subclientStatus & 0x00020) = 0x00020)
	  OR A.appId NOT IN
	  (
		--Exchange
		SELECT distinct A.appId FROM #o365Apps A INNER JOIN App_EmailConfigPolicyAssoc (NOLOCK) E ON A.appId = E.subclientId
WHERE  A.appTypeId = 137 AND E.modified = 0 AND E.status = 0
		UNION
		--PST
		SELECT distinct A.appId FROM #o365Apps A INNER JOIN App_SubclientProp (NOLOCK) S ON A.appId = S.componentNameId
WHERE A.apptypeId = 137 AND S.attrType = 136 /*PST*/ AND S.modified = 0
		UNION
		--V2: Sharepoint/Onedrive/Teams/D365
		SELECT distinct A.appId	FROM #o365Apps A
		INNER JOIN App_CloudAppUserDetails (NOLOCK) E ON A.appId = E.subclientId
WHERE  A.clVersion = 2	AND ((A.appTypeId = 78 AND (E.flags&2 = 0)) OR (A.appTypeId = 134))	AND E.modified = 0 AND E.status = 0
		UNION
		--V1: OneDrive
		SELECT distinct A.appId	FROM #o365Apps A
		INNER JOIN App_InstanceProp IP (NOLOCK) ON A.instanceId = IP.componentNameId
		INNER JOIN App_SubclientProp SP (NOLOCK) ON SP.componentNameId = A.appId
WHERE A.appTypeId = 134 AND A.clVersion = 1 AND IP.attrname = 'Cloud Apps Instance Type' AND IP.attrval = '7' AND IP.modified = 0 AND SP.attrType = 134 AND SP.modified = 0
		UNION
		--V1: Sharepoint
		SELECT distinct A.appId	FROM #o365Apps A
		JOIN App_ScfilterFIle F (NOLOCK) ON A.appId = F.componentNameId
WHERE A.appTypeId = 78 AND A.clVersion = 1 AND F.type = 21 /*Sharepoint Content*/ AND F.modified = 0
	  ))
	END
	if object_id('tempdb.dbo.#o365Apps') is not null DROP TABLE #o365Apps
	if object_id('tempdb.dbo.#o365Clients') is not null DROP TABLE #o365Clients
	--- CODE ABOVE HERE IS FROM AppFilterSubclientsWithoutContent.spb  ----------
	IF (@i_getLevel = 1)
	BEGIN
		---------------------------
		--UPDATE BACKUP LEVEL
		---------------------------
		UPDATE #appIdTbl SET bkpLevel = @backupLevel
		--Backup Level needs to be reset again for VSA App Aware subclients
		UPDATE appIdTbl
SET appIdTbl.bkpLevel = 1
		FROM #appIdTbl appIdTbl
INNER JOIN APP_SubclientProp SCP WITH(NOLOCK) ON SCP.componentNameId = appIdTbl.appId  AND appIdTbl.bkpLevel = 4 AND appIdTbl.appTypeId = 106
	    AND SCP.attrName = N'VS Collect Vss Meta Data' AND SCP.attrVal='1' AND SCP.modified = 0 AND SCP.cs_attrName = CHECKSUM(N'VS Collect Vss Meta Data')
		--UPDATE #appIdTbl SET bkpLevel = ISNULL((SELECT dbo.getbackuplevel(@bkpLevel) FROM TM_SubTask where subTaskId = @i_subTaskId), BACKUP_LEVEL_INCREMENTAL)
		--UPDATE #appIdTbl SET bkpLevel = (SELECT dbo.getbackuplevel(operationType) FROM TM_SubTask where subTaskId = @i_subTaskId)
		-- Change the backup level to Full for MySQL cloud clients when associated with plan schedule incremental
		IF @isSchedulePolicyAssociatedToPlan=1
		BEGIN
UPDATE #appIdTbl SET bkpLevel = 1
WHERE bkpLevel = 2 AND appTypeId = 104
			AND EXISTS(	SELECT 1
					FROM APP_ClientProp WITH(NOLOCK)
					WHERE componentNameId = clientId
					AND attrName = 'Client App Type'
					AND modified = 0
					AND ISNUMERIC(attrVal) = 1
					AND CAST(attrVal AS INT) > 0
				  )
		END
		-- For incremental schedule policy (not log) associated to plan, if its assigned to SQL subclients then we need to change their backup level to differential
		IF @isPlanDataSchedulePolicy =1
		BEGIN
			-- Change the backup level to differential for SQL by default. JM will convert it to full once every 7 days
UPDATE #appIdTbl SET bkpLevel = 3 WHERE bkpLevel <> 3 AND appTypeId = 81
			-- Change the backup level to differential for Sybase by default for plan incremental schedule.
UPDATE #appIdTbl SET bkpLevel = 3 WHERE bkpLevel = 2 AND appTypeId = 5
			-- Update backup level to full where differential isnt supported
			-- For SQL Analysis services and Azure DataBase and Azure Managed Database Engines, only FULL backup is supported. If backup level is something other than FULL, then filter all analysis services and azure db engine subclients.
			-- ANALYSIS_SERVER
UPDATE #appIdTbl SET bkpLevel = 1
WHERE appTypeId = 81
			AND
			(
				EXISTS (SELECT 1 FROM APP_instanceprop WHERE componentNameId=instanceId AND attrName='MSSQL Server Type' AND modified=0 AND (attrVal in ('Analysis Services', 'Azure DataBase Engine', 'Aws DataBase Engine', 'Azure Managed DataBase Engine'  ) ) ) -- Differential is not supported for blocklevel subclients
			)
			-- For continuous incremental schedule associated to plan check for operation window from TM itself. So for subclients where backup isnt done yet we need to convert backup level to full so that op window is checked
			IF @isSchedulePolicyAssociatedToPlan=1 AND @freqInterval>0
			BEGIN
				UPDATE appIdTbl
SET bkpLevel = 1
				FROM #appIdTbl appIdTbl
				LEFT JOIN App_SubclientProp (NOLOCK)
				ON App_SubclientProp.componentNameId = appIdTbl.appId AND App_SubclientProp.attrName='sys:full cycle num' --CV_PROP_FULL_BACKUP_CYCLE_NUM
				AND App_SubclientProp.cs_attrName = CHECKSUM(N'sys:full cycle num')
				AND App_SubclientProp.modified = 0
				WHERE App_SubclientProp.componentNameId IS NULL
			END
		END
		---------------------------------
		--UPDATE THE LEVEL BASED ON RULES
		---------------------------------
		--1. NDS appType can have FULL backup only
UPDATE #appIdTbl SET bkpLevel = 1 where appTypeId = 10
		--2. EXCHANGE DB JOB LEVEL ARE BASED ON PRE SELECTED TYPE
			-- TAKEN CARE IN JM
			-- JUST TREAT INCREMENTAL\DIFFERENTIAL AS PRE-SELECTED FOR EXCHANGE
			--APPTYPE_W2K_XDB 15 APPTYPE_TITANIUM_DB 53 APPTYPE_MSEXCHANGE_DATABASE 9
			UPDATE #appIdTbl
SET bkpLevel = 7
WHERE appTypeId IN (15, 53, 9)
AND bkpLevel IN (2, 3)
--		--2. ORACLE HAS ONLINE SELECTIVE FULL BACKUP
--		UPDATE #appIdTbl  SET bkpLevel = ISNULL((SELECT dbo.getbackuplevel(
--			(SELECT (ISNULL(
--				(SELECT 32768 FROM APP_Subclientprop WHERE modified = 0 AND attrName = N'Oracle Online Selective Full' AND attrVal = N'1' AND
--					componentNameId = #appIdTbl.appId), BACKUP_LEVEL_INCREMENTAL)))
--				)), BACKUP_LEVEL_INCREMENTAL) --DEFAULT VALUE IS INCR
--		WHERE appTypeId IN (CV_APPTYPE_ORACLE_RAC, CV_APPTYPE_ORACLE8, CV_APPTYPE_ORACLE8I)
--		AND EXISTS (SELECT 1 FROM APP_SubClientProp WHERE modified = 0 AND attrName = N'Oracle Online Selective Full' AND attrVal = N'1' AND
--				componentNameId = #appIdTbl.appId)
IF (@operationType <> 4028)
		BEGIN
			--3. LOTUS NOTES TRANSACTION LOG WILL HAVE INCR BACKUP ONLY INCR = 2
UPDATE #appIdTbl  SET bkpLevel = 2 where appTypeId IN (6,51,59,77) AND(( status & 0x0080 ) = 0x0080)  --CV_STATUS_TRAN_LOG			0x0080
			--3. Convert INCR to FULL for old SQL FFG backup
UPDATE #appIdTbl  SET bkpLevel = 1 where bkpLevel = 2 AND  appTypeId IN (4,16,70) AND  (status &2 <> 2)
			--3.1 Convert INCR to DIFF for SHAREPOINT DB belonging to plan
			--for sharepoint db we need to check CV_STATUS_SPDB_BSET to say that subclient is DB subclient
UPDATE appIdTbl  SET appIdTbl.bkpLevel = 3
			FROM #appIdTbl appIdTbl
INNER JOIN APP_BackupSetName BS WITH(NOLOCK) ON appIdTbl.backupsetId = BS.id AND (BS.status&0x0800) > 0
WHERE appIdTbl.bkpLevel = 2 AND @isSchedulePolicyAssociatedToPlan = 1
			--3.2 convert synthfull to Full for SHAREPOINT DB belonging to Plan
UPDATE appIdTbl  SET appIdTbl.bkpLevel = 1
			FROM #appIdTbl appIdTbl
INNER JOIN APP_BackupSetName BS WITH(NOLOCK) ON appIdTbl.backupsetId = BS.id AND (BS.status&0x0800) > 0
WHERE appIdTbl.bkpLevel = 4 AND @isSchedulePolicyAssociatedToPlan = 1
			--3. Convert INCR to FULL for SHAREPOINT DB
UPDATE #appIdTbl  SET bkpLevel = 1 where bkpLevel = 2 AND  appTypeId IN (32,69)
			--4 and 5 are handled in jobmanager
			--4.Convert INCR to UPDATE REPLICA for QR Job
			--UPDATE #appIdTbl  SET bkpLevel = 8192 where bkpLevel = BACKUP_LEVEL_INCREMENTAL AND  appTypeId IN (CV_APPTYPE_QR_AGENT,CV_APPTYPE_UNIX_REPLICATION_IDA)
			--5.Convert FULL to CREATE REPLICA for QR Job
			--UPDATE #appIdTbl  SET bkpLevel = 2048 where bkpLevel = BACKUP_LEVEL_FULL AND  appTypeId IN (CV_APPTYPE_QR_AGENT,CV_APPTYPE_UNIX_REPLICATION_IDA)
			--5.IF run TRUNC LOG AFTER INCR selected FOR SQL AND SYBASE
			--UPDATE #appIdTbl  SET bkpLevel = 256 where apptypeId in ( UNUSED_CV_APPTYPE_4, CV_APPTYPE_SYBASE,LEGACY_CV_APPTYPE_16, UNUSED_CV_APPTYPE_70, CV_APPTYPE_MSSQL)   AND
			--EXISTS( SELECT value from TM_SubTaskOptions where  subTaskId = @i_subTaskId AND optionId = BACKUPOPTION_DO_NOT_TRUNCATE_LOG)
			--6. If Incr is not supported change to Differential
UPDATE #appIdTbl  SET bkpLevel = 3 WHERE bkpLevel = 2 AND
				appTypeId IN ( SELECT appTypeId FROM dbo.GetAppTypesForAppGroup(88, 0))  --APPGRP_IncrementalNotSupported
				AND (NOT EXISTS (SELECT 1 FROM APP_BackupSetProp WHERE componentNameId=backupsetId AND attrName='FS BackupSet' AND attrVal='1' AND modified=0 )
				)
			--6.1. Convert differential backup level to incremental for cloud apps app type
UPDATE #appIdTbl  SET bkpLevel = 2 WHERE  bkpLevel = 3
AND appTypeId = 134
			--7. If Differential is not supported then change to Full
UPDATE #appIdTbl  SET bkpLevel = 1 WHERE  bkpLevel = 3 AND
				(
appTypeId IN ( SELECT appTypeId FROM dbo.GetAppTypesForAppGroup(87, 0) WHERE appTypeId NOT IN (5))  --APPGRP_DifferntialNotSupported
					OR
					(
						appTypeId IN ( SELECT appTypeId FROM dbo.GetAppTypesForAppGroup(82, 0))	--APPGRP_SqlServerAppType
						AND
						EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId=appId AND attrName='Enable Snap Backups' AND attrVal = '1' AND modified=0 AND cs_attrName = CHECKSUM(N'Enable Snap Backups') )	--INTELLISNAP IS ENABLED
					)
				)
			--8. If Synthetic Full is not supported then change to Full
UPDATE #appIdTbl  SET bkpLevel = 1 WHERE  bkpLevel = 4 AND
				appTypeId IN (SELECT appTypeId FROM dbo.GetAppTypesForAppGroup(33, 0))  --APPGRP_SynthFullNotSupported
				AND NOT EXISTS (SELECT 1 FROM APP_BackupSetProp WHERE componentNameId=backupsetId AND attrName='NAS Turbo Backupset' AND attrVal='1' AND modified=0)
				AND NOT EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId=appId AND attrName='Is Turbo Subclient' AND attrVal='1' AND modified=0 AND cs_attrName = CHECKSUM(N'Is Turbo Subclient')  )
				AND NOT EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId=appId AND attrName='Use block level backup' AND attrVal='1' AND modified=0
								AND cs_attrName = CHECKSUM(N'Use block level backup')
AND appTypeId IN (22, 80, 104, 125, 37 , 62, 5, 135, 81)
								)
				AND (status&0x10000=0) --CV_STATUS_REFCOPY_SUB
			--8a. If Synthetic Full is not supported then change to Full
UPDATE #appIdTbl  SET bkpLevel = 1 WHERE  bkpLevel = 4
			AND
			(
				EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId=appId AND attrName='Oracle Subclient Type' AND attrVal='2' AND modified=0 AND cs_attrName=CHECKSUM(N'Oracle Subclient Type') )	--DbSubclientType_LOGICAL_DUMP CONVERT TO FULL FOR ORACLE LOGICAL DUMP SUBCLIENT
				OR
				EXISTS (SELECT 1 FROM APP_InstanceProp WHERE componentNameId=instanceId AND attrName='Cloud Apps Instance Type' AND attrVal='3' AND modified=0)	--CloudAppsInstanceType_SALESFORCE CONVERT TO FULL FOR CLOUD SALESFORCE INSTANCE
			)
			--9. Object Server IDA only supports Incremental jobs
UPDATE #appIdTbl  SET bkpLevel = 2 WHERE  bkpLevel <> 2 AND
appTypeId IN (124)  --CV_APPTYPE_CONTENT_STORE_
		END
		--11 Make sure backup level is set correctly for SRM IDAs.
UPDATE #appIdTbl  SET bkpLevel = CASE	WHEN bkpLevel=2 THEN 12
WHEN bkpLevel=1 THEN 11
												ELSE bkpLevel
										 END
WHERE appTypeId >= 600 AND appTypeId <= 699
		--12. SRM IDAs where backup level is a subclient property
UPDATE #appIdTbl  SET bkpLevel = 1 WHERE
appTypeId IN (600, 603)  --CV_APPTYPE_SRM_WINFS_, CV_APPTYPE_SRM_NAS_
            AND clientId IN (SELECT id FROM APP_Client WHERE releaseId > 13)
		--13. For records manager always submit incremental job. Let JM take care of first full.
UPDATE #appIdTbl SET bkpLevel = 2 WHERE bkpLevel <> 2
		AND EXISTS(	SELECT 1
					FROM APP_ClientProp
					WHERE componentNameId = clientId
					AND attrName = 'System Drive Type'
					AND modified = 0
					AND attrVal = '3' --Records Manager
				  )
		--13.1. Change the backup level to incremental for Exchange entity associated to plan by default.
		UPDATE #appIdTbl
SET bkpLevel = 2
WHERE bkpLevel IN( 4,1)
AND appTypeId = 137
		AND @isImportMailBox = 0
		--13.2. Change the backup level to full for Sweep schedule.
		UPDATE #appIdTbl
SET bkpLevel = 1
WHERE appTypeId IN (135, 3)
		AND (@useSpaceFromMA > 0 OR @sweepMAClienId > 0)
		--13.3. Convert differential to full when extent backup is configured on SQL subclient.
UPDATE #appIdTbl SET bkpLevel = 1
WHERE appTypeId = 81 AND bkpLevel = 3
			AND
			(
				EXISTS (SELECT 1 FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = appId AND attrName = N'Use block-level backup with optimize recovery' AND attrVal = N'1' AND modified = 0 AND cs_attrName = CHECKSUM(N'Use block-level backup with optimize recovery'))
			)
		-- We stopped supporting Differentials for Nutanix files client, removing them
UPDATE #appIdTbl SET bkpLevel = 2
WHERE bkpLevel = 3
			AND
			(
				EXISTS(SELECT 1 FROM APP_ClientProp WITH(NOLOCK) WHERE componentNameId=clientId AND attrName='Client App Type' AND modified=0 AND attrVal='2')
			)
		-- Edge drive clients now support synthetic full.
		-- In case its not synthetic full and its a edge drive schedule, remove all Edge drive clients
		if (@isEdgeDrivePolicy=0)
		begin
			-- Remove all edge drive clients where backup level <> BACKUP_LEVEL_SYNTHETIC_FULL
delete FROM #appIdTbl WHERE bkpLevel <> 4
			and clientId in
			(
select id from app_client where (status&0x10000000)>0 and (status&0x40000000)=0
			)
		end
		--14. Skip Blocklevel subclients if Backup level is differential
DELETE FROM #appIdTbl WHERE  bkpLevel = 3 AND
				(
					(
						EXISTS (SELECT 1 FROM APP_SubClientProp WHERE componentNameId=appId AND attrName='Use block level backup' AND attrVal = '1' AND modified=0 AND cs_attrName=CHECKSUM(N'Use block level backup') ) -- Differential is not supported for blocklevel subclients
					)
				)
		--15. For SAP Oracle subclients do not run only for plan data backup incremental schedule
		IF @isPlanDataSchedulePolicy = 1
		AND
		EXISTS(
			SELECT 1 FROM #appIdTbl appIdTbl
				INNER JOIN APP_SubClientProp SCP WITH(NOLOCK) ON SCP.componentnameId = appIdTbl.appId
				WHERE SCP.attrName='Oracle Backup Device'
					AND SCP.cs_attrName=CHECKSUM(N'Oracle Backup Device')
					AND SCP.attrVal != '0' AND SCP.modified=0
AND appIdTbl.appTypeId = 61
			)
		BEGIN
			DELETE appIdTbl
			FROM #appIdTbl appIdTbl
				INNER JOIN
				(
					SELECT J.appId, ISNULL(MAX(J.servStartDate),0) servStartDate
						FROM JMBkpStats J WITH(NOLOCK)
						INNER JOIN APP_SubClientProp SCP WITH(NOLOCK) ON SCP.componentnameId = J.appId
						WHERE SCP.attrName='Oracle Backup Device' AND SCP.attrVal != '0' AND SCP.modified=0
						AND SCP.cs_attrName=CHECKSUM(N'Oracle Backup Device')
						AND J.bkpLevel IN (1/*FULL*/, 64/*SYNTHETIC_FULL*/, 128 /*SYNTHETIC_FULL_AND_INCR*/, 32768 /*SEL_ONLINE_FULL*/ )
AND J.appType = 61
						GROUP BY J.appId
				)JM
				ON JM.appId = appIdTbl.appId
				WHERE (JM.servStartDate + (@numOfDefaultDaysForAutoconvertBackupLevel * 24*60*60)) > @nowUtcUnix
AND appIdTbl.appTypeId = 61
			-- If there is a subclient then convert incremental to full
			UPDATE appIdTbl
SET appIdTbl.bkpLevel = 1
			FROM #appIdTbl appIdTbl
			INNER JOIN APP_SubClientProp SCP WITH(NOLOCK) ON SCP.componentnameId = appIdTbl.appId
						WHERE SCP.attrName='Oracle Backup Device' AND SCP.attrVal != '0' AND SCP.modified=0
						AND SCP.cs_attrName=CHECKSUM(N'Oracle Backup Device')
AND appIdTbl.bkpLevel = 2
AND appIdTbl.appTypeId = 61
		END
		-- For SQL Analysis services and Azure DataBase and Azure Managed Database Engines, only FULL backup is supported. If backup level is something other than FULL, then filter all analysis services and azure db engine subclients.
		-- ANALYSIS_SERVER
DELETE FROM #appIdTbl WHERE  bkpLevel <> 1 AND
				(
					(
						EXISTS (SELECT 1 FROM APP_instanceprop WHERE componentNameId=instanceId AND attrName='MSSQL Server Type' AND modified=0 AND (attrVal in ('Analysis Services', 'Azure DataBase Engine', 'Aws DataBase Engine', 'Azure Managed DataBase Engine'  ) ) ) -- Differential is not supported for blocklevel subclients
					)
				)
		-- For MSSQL when transaction log to promote to full is set, we should block if any full backup job is running on subclient.
		DELETE appIdTbl
			FROM #appIdTbl appIdTbl
			INNER JOIN JMBkpJobInfo (NOLOCK) ON
				appIdTbl.appId = JMBkpJobInfo.applicationId
AND JMBkpJobInfo.bkpLevel = 1
				AND @isPromoteToFull = 1
AND appIdTbl.appTypeId = 81
AND appIdTbl.bkpLevel IN (6)
	END
	IF @freqInterval > 0
	BEGIN
		-- Delete SAP MAX DB Agents from backup for continuous incremental schedules.
DELETE FROM #appIdTbl WHERE  AppTypeID = 79
		--REMOVE SUBCLIENTS IN SCHEDULE FOR WHICH JM HAS NOT PICKED THE JOB
		DECLARE @useJobStartTime INT = 0
		SELECT @useJobStartTime = CASE WHEN O.Value = '1' THEN 1 ELSE 0 END
		FROM TM_SubTaskOptions O (NOLOCK) WHERE O.subTaskId = @i_subtaskId
AND O.optionId = 414240771	--_BACKUPOPTION_BACKUP_LEVEL
		DELETE #appIdTbl
		FROM #appIdTbl
INNER JOIN TM_JobOptions (NOLOCK) ON TM_JobOptions.OptionId = 100001 AND CAST(#appIdTbl.appId AS VARCHAR(10)) = TM_JobOptions.value
		INNER JOIN TM_Jobs J ON J.JobId = TM_JobOptions.JobID
		INNER JOIN TM_JobRequest R ON R.jobRequestId = J.jobRequestId AND R.subTaskId = @i_subtaskId AND J.status IN (0,1)
		-- Compute the next run time for all subclients.
		EXEC TM_ComputeNextStartTime 0,@i_subtaskId
		--REMOVE SUBCLIENTS IN SCHEDULE FOR WHICH JOBS ARE RUNNING
		IF @jobsQueuedIfJobsRunning = 0
		BEGIN
			-- For SQL Ida they allow full and TL to be run at the same time. For TL backup level set on schedule , we should check whether TL backup is running on same subclient and if so block.
			-- For full or differential backup level set on schedule, we should block if any full/differential backup job is running on subclient.
			DELETE appIdTbl
				FROM #appIdTbl appIdTbl
				INNER JOIN JMBkpJobInfo (NOLOCK) ON
					appIdTbl.appId = JMBkpJobInfo.applicationId
AND appIdTbl.appTypeId != 137   --_CV_APPTYPE_EXCHANGE_ONEPASS
					AND (
							(
(appIdTbl.appTypeId != 5 AND appIdTbl.appTypeId != 81) OR
(appIdTbl.appTypeId = 5 AND @runParallelTLogAndFullForSybase = 0)
							) OR
(appIdTbl.bkpLevel = 2 AND 2 = JMBkpJobInfo.bkpLevel ) OR -- Enum  CVBkpLevel::INCR = JMBkpJobInfo.bkpLevel
(appIdTbl.bkpLevel != 2 AND JMBkpJobInfo.bkpLevel != 2 ) -- Enum  CVBkpLevel::INCR = JMBkpJobInfo.bkpLevel
						)  --CV_APPTYPE_SYBASE
		END
		ELSE
		BEGIN
			DELETE A
			FROM #appIdTbl A
			INNER JOIN JMBkpJobInfo BI WITH(NOLOCK) ON
				BI.applicationId = A.appId
				AND BI.backupTaskId = @i_subtaskId
		END
		--FOR EXCHANGE AGENT ALLOW MULTIPLE JOBS FROM DIFFERENT SCHEDULES.  CHECK BASE ON SCHEDULE ID
		DELETE #appIdTbl
		FROM
		(
			SELECT A.appId subclientId, A.bkpLevel backupLevel
			FROM #appIdTbl A
			JOIN JMBkpJobInfo BI (NOLOCK) ON BI.applicationId = A.appId
			AND BI.backupTaskId = @i_subtaskId
AND A.appTypeId = 137	--_CV_APPTYPE_EXCHANGE_ONEPASS
		) T
		WHERE T.subclientId = appId
		AND T.backupLevel = bkpLevel
AND appTypeId = 137	--_CV_APPTYPE_EXCHANGE_ONEPASS
		--FOR CONTINUOUS SCHEDULE REMOVE ANY SUBCLIENTS WHICH HAVE NOT PASSED THE INTERVAL TIME
		-- If use job start time isnt there then we pick up from JMBkpStats. else we pick up from app_subclientprop
		IF @useJobStartTime = 0
		BEGIN
			DELETE FROM #appIdTbl
WHERE @nowUtcUnix < CASE WHEN appTypeId = 81 THEN @freqIntervalForSQL ELSE @freqInterval END
			+ (
											SELECT MAX(endDate)
											FROM
											(
												-- For SQL Ida they allow full and TL to be run at the same time. For TL backup level set on schedule , last backup time is last TL backup time.
												-- For full or differential backup level set on schedule, last backup time is last full/differential backup time. Backup time to be picked based on the level.
												SELECT MAX(servEndDate) endDate
												FROM JMBkpStats (NOLOCK) BS
												WHERE BS.appId = #appIdTbl.appId
AND appType != 137	--_CV_APPTYPE_EXCHANGE_ONEPASS
AND (appType != 81 OR dbo.GetBackupLevel(#appIdTbl.bkpLevel) = BS.bkpLevel) --_CV_APPTYPE_MSSQL
												UNION
												SELECT MAX(servEndDate) endDate
												FROM JMBkpStats (NOLOCK) BS
												WHERE BS.appId = #appIdTbl.appId
AND appType = 137	--_CV_APPTYPE_EXCHANGE_ONEPASS
												AND BS.subTaskId = @i_subtaskId
											) T
										)
		END
		ELSE
		BEGIN
			-- For synth full refer to jmbkpstats
			DELETE FROM #appIdTbl
			WHERE @nowUtcUnix < @freqInterval + (
											SELECT MAX(endDate)
											FROM
											(
												-- For SQL Ida they allow full and TL to be run at the same time. For TL backup level set on schedule , last backup time is last TL backup time.
												-- For full or differential backup level set on schedule, last backup time is last full/differential backup time. Backup time to be picked based on the level.
												SELECT MAX(servEndDate) endDate
												FROM JMBkpStats (NOLOCK) BS
												WHERE BS.appId = #appIdTbl.appId
AND appType != 137	--_CV_APPTYPE_EXCHANGE_ONEPASS
AND (appType != 81 OR dbo.GetBackupLevel(#appIdTbl.bkpLevel) = BS.bkpLevel) --_CV_APPTYPE_MSSQL
AND #appIdTbl.bkpLevel = 4
												UNION
												SELECT MAX(servEndDate) endDate
												FROM JMBkpStats (NOLOCK) BS
												WHERE BS.appId = #appIdTbl.appId
AND appType = 137	--_CV_APPTYPE_EXCHANGE_ONEPASS
												AND BS.subTaskId = @i_subtaskId
AND #appIdTbl.bkpLevel = 4
											) T
										)
AND #appIdTbl.bkpLevel = 4
			DECLARE @nextBackupAttr NVARCHAR(1024) = ''
			SET @nextBackupAttr = 'Next backup start time Continuous '+CONVERT(NVARCHAR(10),@i_subtaskId )
			-- Delete all subclients for which next backup time hasnt hit yet
			DELETE appIdTbl
			FROM #appIdTbl appIdTbl
			INNER JOIN App_SubclientProp ON
			App_SubclientProp.componentnameID = appIdTbl.appID
			AND App_SubclientProp.cs_attrName = CHECKSUM(@nextBackupAttr)
			AND App_SubclientProp.attrName = @nextBackupAttr
			WHERE @nowUtcUnix < CONVERT(INT, App_SubclientProp.attrVal)
AND appIdTbl.bkpLevel != 4
			-- DELETE all subclients for which backup time attribute isnt set.
			DELETE appIdTbl
			FROM #appIdTbl appIdTbl
			LEFT JOIN App_SubclientProp ON
			App_SubclientProp.componentnameID = appIdTbl.appID
			AND App_SubclientProp.cs_attrName = CHECKSUM(@nextBackupAttr)
			AND App_SubclientProp.attrName = @nextBackupAttr
			WHERE App_SubclientProp.componentnameID IS NULL
AND appIdTbl.bkpLevel != 4
		END
		-- For continuous incremental schedule check for operation window from TM itself.
		IF @isSchedulePolicyAssociatedToPlan = 1 AND @freqInterval>0
		BEGIN
			UPDATE #appIdTbl
			SET jmOperationType = CASE WHEN bkpLevel = 1 THEN 1 -- Full backup -- OPERATION_FULL_DATA_PROTECTION
				WHEN bkpLevel = 4 THEN 4 -- synth Full backup OPERATION_SYNTHETIC_FULL
				WHEN bkpLevel = 15 THEN 524288 --  STUBBING OPERATION_STUBBING = 524288
				WHEN bkpLevel = 6  THEN @jmOpTypeIgnore -- For transaction log backup lets not filter based on operation window at all for now.
				-- For incremental backups for DB agents, dont filter by operation window. Let JM queue it
				WHEN bkpLevel = 2 AND appTypeID IN
				(
					SELECT APP_AppTypeGroupAssoc.appTypeId FROM APP_AppTypeGroupAssoc (NOLOCK)
					WHERE appGroupId=89 and typeOfGroup=0	-- 89:APPGRP_DBS
				) THEN @jmOpTypeIgnore
				ELSE 2 -- OPERATION_NON_FULL_DATA_PROTECTION
				END
			UPDATE #appIdTbl
			SET jmOperationType = 4096 -- JM optype will be ONLINECONTENTINDEX. Operation window type will be PERATION_ONLINE_CONTENT_INDEXING
WHERE  ( apptypeId = 93 OR apptypeId = 105 ) -- ONLINE_CONTENT_INDEXING = CV_APPTYPE_CONTENT_INDEXING_IDA ,OCI_EXCH_MB              = CV_APPTYPE_OCI_EXCH_MB_IDA
			EXEC TMFilterSubclientsByOperationWindow 0 -- for conitnuous schedules associated to plan, if operation window is met we dont need to start. . TaskOptype backup
		END
	END
	-- For continuous incremental schedules associated to plan, its checked above. For others filter only based on opWindows where option doNotSubmitJob is set.
	IF @freqInterval=0
	BEGIN
		UPDATE #appIdTbl
		SET jmOperationType = CASE WHEN bkpLevel = 1 THEN 1 -- Full backup -- OPERATION_FULL_DATA_PROTECTION
			WHEN bkpLevel = 4 THEN 4 -- synth Full backup OPERATION_SYNTHETIC_FULL
			WHEN bkpLevel = 15 THEN 524288 --  STUBBING OPERATION_STUBBING = 524288
			WHEN bkpLevel = 6  THEN @jmOpTypeIgnore -- For transaction log backup lets not filter based on operation window at all for now.
			-- For incremental backups for DB agents, dont filter by operation window. Let JM queue it
			WHEN bkpLevel = 2 AND appTypeID IN
			(
				SELECT APP_AppTypeGroupAssoc.appTypeId FROM APP_AppTypeGroupAssoc (NOLOCK)
				WHERE appGroupId=89 and typeOfGroup=0	-- 89:APPGRP_DBS
			) THEN @jmOpTypeIgnore
			ELSE 2 -- OPERATION_NON_FULL_DATA_PROTECTION
			END
		UPDATE #appIdTbl
		SET jmOperationType = 4096 -- JM optype will be ONLINECONTENTINDEX. Operation window type will be OPERATION_ONLINE_CONTENT_INDEXING
WHERE  ( apptypeId = 93 OR apptypeId = 105 ) -- ONLINE_CONTENT_INDEXING = CV_APPTYPE_CONTENT_INDEXING_IDA, OCI_EXCH_MB              = CV_APPTYPE_OCI_EXCH_MB_IDA
		-- Check if any operation window has option to not start job
		EXEC TMFilterSubclientsByOperationWindow 1
	END
IF @autoCopy = 1 AND EXISTS(SELECT * FROM #appIdTbl ) AND @i_initFrom NOT IN (1, 2, 6)
	BEGIN
		-- DELETE subclients for automatic synthetic full which have incremental backup level.
		-- For exchange mailbox, non import synthetic fulls are converted to incrementals. For subclient associated to server plan, its kicking of incrementals every 30 mins which is unintentional
		DELETE FROM #appIdTbl
WHERE bkpLevel NOT IN( 4,1)
		INSERT INTO #App_SubclientInfoTable
		(subclientId, FullRefTime, IncrRefTime, TurboFullRefTime, TurboIncrRefTime, IsTurbo, RunSFImmediately, ExtendedRetentionDaysForAutomaticSchedule)
		SELECT componentNameId, ISNULL( [sys:last full], 0) AS FullRefTime, ISNULL( [sys:last incr], 0) AS IncrRefTime, ISNULL( [turbo:last full], 0) AS TurboFullRefTime,
			ISNULL( [turbo:last incr], 0) AS TurboIncrRefTime, ISNULL( [Is Turbo Subclient], 0) AS IsTurbo, [RunSFImmediately], [ExtendedRetentionDaysForAutomaticSchedule]
		FROM
		(
			SELECT componentNameId, attrName, attrVal
			FROM #appIdTbl APP
			JOIN APP_SubClientProp P WITH (NOLOCK) ON P.componentNameId = APP.appId
			WHERE P.modified = 0
			AND P.attrName IN ('sys:last full', 'sys:last incr', 'turbo:last full', 'turbo:last incr', 'Is Turbo Subclient', 'RunSFImmediately', 'ExtendedRetentionDaysForAutomaticSchedule')
			AND P.cs_attrName IN (CHECKSUM(N'sys:last full'),CHECKSUM(N'sys:last incr'),CHECKSUM(N'turbo:last full'),CHECKSUM(N'turbo:last incr'),CHECKSUM(N'Is Turbo Subclient'),CHECKSUM(N'RunSFImmediately'),CHECKSUM(N'ExtendedRetentionDaysForAutomaticSchedule'))
		) AS subclient
		PIVOT
		(
			MAX(attrVal)
			FOR attrName IN ([sys:last full], [sys:last incr], [turbo:last full], [turbo:last incr], [Is Turbo Subclient], [RunSFImmediately], ExtendedRetentionDaysForAutomaticSchedule)
		) AS PVT
		--INSERT FOR AGENTS THAT SUPPORT SYNTHETIC FULL
		INSERT INTO #TM_SyntheticInfoTable
		(subclientId, backupsetId, instanceId, appTypeId, clientId, lastSynthetic, lastFull, lastIncremental, bkpLevel, status, parentJobId, parentAppId, syntheticInterval)
		SELECT T.appId, T.backupsetId, T.instanceId, T.appTypeId, T.clientId, T.lastSynthetic, ISNULL(T.fullRefTime, 0) lasFull, ISNULL(T.incrRefTime, 0) lastIncremental, T.bkpLevel, T.STATUS, T.parentJobId, T.parentAppId, @syntheticInterval
		FROM
		(
		SELECT APP.appId, APP.backupsetId, APP.instanceId, APP.appTypeId, APP.clientId, ISNULL(MAX(JS.servStartDate), 0) lastSynthetic,
		CASE SPROP.isTurbo
			WHEN 1 THEN SPROP.TurboFullRefTime
			ELSE SPROP.FullRefTime
			END fullRefTime,
		CASE SPROP.isTurbo
			WHEN 1 THEN SPROP.TurboIncrRefTime
			ELSE SPROP.incrRefTime
			END incrRefTime,
		APP.bkpLevel,
		APP.status,
		APP.parentJobId,
		APP.parentAppId
		FROM #appIdTbl APP
		LEFT OUTER JOIN JMBkpStats JS (NOLOCK) ON JS.appId = APP.appId
			AND JS.bkpLevel IN (1/*FULL*/, 64/*SYNTHETIC_FULL*/, 128 /*SYNTHETIC_FULL_AND_INCR*/, 32768 /*SEL_ONLINE_FULL*/, 1024/*SEL_OFFLINE_FULL*/ )
			--MR:213386 - START FAILED JOBS AFTER SOME TIME. GET LATEST JOB RUN REGARLESS OF STATUS.  CHECK WILL BE DONE BELOW
			--AND JS.status IN (1/*JMSUCCESS*/, 3/*PARTIALSUCCESS*/, 4/*KILLED*/)  --MR:146313 - START WHEN PREV SYNTHIC JOB IS KILLED
			AND JS.opType <> 60 --DON'T CONSIDER BACKUP COPY JOBS
			--AND JS.dataStatus = 0 --Considering aged job status
		--AND NOT EXISTS (SELECT * FROM JMBkpJobInfo JBI WITH(NOLOCK) WHERE JBI.applicationId = JS.appid)
		LEFT OUTER JOIN #App_SubclientInfoTable SPROP ON SPROP.subclientId = APP.appId
		GROUP BY APP.appId, APP.backupsetId, APP.instanceId, APP.appTypeId, APP.clientId, SPROP.IsTurbo, SPROP.TurboFullRefTime, SPROP.FullRefTime, SPROP.IncrRefTime, SPROP.TurboIncrRefTime, APP.bkpLevel, APP.status, APP.parentJobId, APP.parentAppId
		) T
		--INSERT FOR AGENTS THAT DON'T SUPPORT SYNTHETIC FULL AND HAVE BEEN CONVERTED TO FULL
		INSERT INTO #TM_SyntheticInfoTable
		(subclientId, backupsetId, instanceId, appTypeId, clientId, lastFull, lastSynthetic, bkpLevel, status, parentJobId, parentAppId, syntheticInterval)
		SELECT APP.appId, APP.backupsetId, APP.instanceId, APP.appTypeId, APP.clientId, JS.servEndDate, JS.servEndDate, APP.bkpLevel, APP.status, APP.parentJobId, APP.parentAppId, @syntheticInterval
		FROM #appIdTbl APP
		LEFT OUTER JOIN JMBkpStats JS (NOLOCK) ON JS.appId = APP.appId
			AND JS.bkpLevel IN (1/*FULL*/, 64/*SYNTHETIC_FULL*/, 128 /*SYNTHETIC_FULL_AND_INCR*/)
			AND JS.opType <> 60 --DON'T CONSIDER BACKUP COPY JOBS
WHERE APP.bkpLevel = 1
		AND NOT EXISTS (SELECT 1 FROM #TM_SyntheticInfoTable T WHERE T.subclientId = APP.appId)
		-- For V1 one drive subclients compute last full and last incremental based on application size. ignore jobs where no data is backed up
IF EXISTS(SELECT 1 FROM #appIdTbl APP WHERE APP.appTypeId = 134 )
		BEGIN
			IF object_id('tempdb.dbo.#v1OneDriveScsSynthfull') is not null
				DROP TABLE #v1OneDriveScsSynthfull
			CREATE TABLE #v1OneDriveScsSynthfull(subclientId INT, fullCycleNum INT)
			CREATE CLUSTERED INDEX v1OneDriveScsSynthfull_subclientId_idx ON #v1OneDriveScsSynthfull (subclientId)
			INSERT INTO #v1OneDriveScsSynthfull(subclientId,fullCycleNum )
			SELECT APP.appID, CAST(App_subclientProp.attrVal AS INT) FROM #appIdTbl APP
			INNER JOIN APP_InstanceProp I WITH (NOLOCK) ON APP.instanceID = I.componentnameID
			AND I.attrname='Cloud Apps Instance Type' AND I.modified = 0 -- CV_CLOUDAPPS_INSTANCE_TYPE
			AND I.attrVal='7' -- CloudAppsInstanceType::ONEDRIVE
AND APP.appTypeId = 134
AND APP.bkpLevel = 4
			INNER JOIN App_SubclientProp (NOLOCK) ON App_SubclientProp.componentNameId = APP.appId
				AND App_SubclientProp.attrName='sys:full cycle num' --CV_PROP_FULL_BACKUP_CYCLE_NUM
				AND App_SubclientProp.cs_attrName=CHECKSUM(N'sys:full cycle num')
				AND App_SubclientProp.modified = 0
			LEFT JOIN App_ClientProp (NOLOCK) ON APP.clientId = App_ClientProp.ComponentNameId AND App_ClientProp.AttrName='IndexingV2_OneDrive'
			AND App_ClientProp.attrVal = '1'
			AND App_ClientProp.modified = 0
			WHERE App_ClientProp.ComponentNameId IS NULL
			IF EXISTS(SELECT 1 FROM #v1OneDriveScsSynthfull)
			BEGIN
				-- Delete V1 one drive subclients where latest cycle doesnt have any valid incrementals.
				DELETE TS
				FROM #TM_SyntheticInfoTable TS
				INNER JOIN #v1OneDriveScsSynthfull APP ON TS.subclientID = APP.subclientId
				LEFT JOIN JMBkpStats BS (NOLOCK) ON BS.appId = TS.subclientId
					AND BS.status IN (1, 3, 14) --JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
					AND BS.bkpLevel = 2 -- INCR
					AND BS.totaluncompbytes>0
					AND BS.fullcycleNum = APP.fullcycleNum
				WHERE BS.appID IS NULL
			END
		END
		-- Change was made to remove RunSFImmediately attribute. So if schedule is configured to use option "As needed to reclaim space", because this subclient property isnt there
		-- we always end up running synthetic full. And no of days is set to 0 in this case.
		IF @enableRunConsolidateFull = 1
		BEGIN
			DELETE #TM_SyntheticInfoTable FROM #TM_SyntheticInfoTable
			INNER JOIN #App_SubclientInfoTable ON #TM_SyntheticInfoTable.subclientId = #App_SubclientInfoTable.subclientId
			AND #App_SubclientInfoTable.RunSFImmediately IS NULL
		END
		--SET LAST FULL FOR VM CLIENTS ADDED BY VSA DISCOVER
		UPDATE #TM_SyntheticInfoTable
		SET lastFull = T.fullBkp
		FROM
		(
			SELECT MAX(BS.servEndDate) fullBkp, BS.appId
			FROM #TM_SyntheticInfoTable TS
			JOIN #App_VmClientInfoTable VM ON VM.childBackupsetId = TS.backupsetId
			JOIN JMBkpStats BS (NOLOCK) ON BS.appId = TS.subclientId
			AND BS.bkpLevel = 1			--FULL
			AND BS.status IN (1, 3, 14) --JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
			GROUP BY BS.appId
		) T
		WHERE subclientId = T.appId
		--SET LAST INCREMENTAL FOR VM CLIENTS ADDED BY VSA DISCOVER
		UPDATE #TM_SyntheticInfoTable
		SET lastIncremental = T.incrBkp
		FROM
		(
			SELECT MAX(BS.servEndDate) incrBkp, BS.appId
			FROM #TM_SyntheticInfoTable TS
			JOIN #App_VmClientInfoTable VM ON VM.childBackupsetId = TS.backupsetId
			JOIN JMBkpStats BS (NOLOCK) ON BS.appId = TS.subclientId
			AND BS.bkpLevel = 2			--INCR
			AND BS.status IN (1, 3, 14) --JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
			GROUP BY BS.appId
		) T
		WHERE subclientId = T.appId
		--SET LAST SYNTHETIC FOR VM CLIENTS ADDED BY VSA DISCOVER
		UPDATE #TM_SyntheticInfoTable
		SET lastSynthetic = T.synthBkp
		FROM
		(
			SELECT MAX(BS.servStartDate) synthBkp, BS.appId
			FROM #TM_SyntheticInfoTable TS
			JOIN #App_VmClientInfoTable VM ON VM.childBackupsetId = TS.backupsetId
			JOIN JMBkpStats BS (NOLOCK) ON BS.appId = TS.subclientId
			AND opType = 14				--SYNTHFULL
			--MR:213386 - START FAILED JOBS AFTER SOME TIME. GET LATEST JOB RUN REGARLESS OF STATUS.  CHECK WILL BE DONE BELOW
			--AND BS.status IN (1, 3, 14) --JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
			GROUP BY BS.appId
		) T
		WHERE subclientId = T.appId
		--SET THE STATUS OF THE JOB
		UPDATE #TM_SyntheticInfoTable
		SET bkpStatus = T.status
		FROM
		(
			SELECT BS.appId, BS.status, BS.servStartDate synthBkp
			FROM #TM_SyntheticInfoTable S
			JOIN JMBkpStats BS (NOLOCK) ON BS.appId = S.subclientid
			AND BS.servStartDate = S.lastSynthetic
		) T
		WHERE subclientId = T.appId
		AND lastSynthetic = T.synthBkp
		--SET SYNTHETIC INTERVAL BASED ON PROPERTIES.  IF SF JOB RAN AFTER INTERVAL THEN DON'T START NEW JOB. IF SF JOB RAN ON OR BEFORE INTERVAL THE START NEW JOB
		IF @autoSyntheticDay = 1 AND @consolidateAutoSyntheticOptions = 0
		BEGIN
			UPDATE #TM_SyntheticInfoTable
			SET syntheticInterval =
				@currentTime-
				(
					ISNULL(
							(
								SELECT ExtendedRetentionDaysForAutomaticSchedule
								FROM #App_SubclientInfoTable
								WHERE #App_SubclientInfoTable.subclientId = #TM_SyntheticInfoTable.subclientId
							),
							@numOfDays
					)*24*60*60
				)
		END
		IF @consolidateAutoSyntheticOptions = 1
		BEGIN
			UPDATE #TM_SyntheticInfoTable
			SET syntheticInterval =
				@currentTime-
				(
					ISNULL(
							(
								SELECT CASE WHEN ExtendedRetentionDaysForAutomaticSchedule IS NOT NULL AND ExtendedRetentionDaysForAutomaticSchedule<@numOfDays THEN ExtendedRetentionDaysForAutomaticSchedule ELSE @numOfDays END
								FROM #App_SubclientInfoTable
								WHERE #App_SubclientInfoTable.subclientId = #TM_SyntheticInfoTable.subclientId
							),
							@numOfDays
					)*24*60*60
				)
			UPDATE #TM_SyntheticInfoTable
			SET syntheticInterval = T.synthInt
			FROM
			(
				SELECT RunSFImmediately AS synthInt, #App_SubclientInfoTable.subclientId AS componentNameId
				FROM #App_SubclientInfoTable
				JOIN #TM_SyntheticInfoTable ON #TM_SyntheticInfoTable.subclientId = #App_SubclientInfoTable.subclientId
			) T
			WHERE T.synthInt > syntheticInterval	--NEW INTERVAL IS AT A LATER TIME THAN CURRENT INTERVAL THEN USE NEW INTERVAL TIME
			AND T.synthInt > 0
			AND T.synthInt < @currentTime
			AND subclientId = T.componentNameId
		END
		-- RUN FULL CONSOLIDATION (i.e. SYNTHETIC FULL WHEN SPACE SAVED GREATER THAN THRESHOLD) WHEN THE NO JOB RAN AFTER DATE ON SUBCLIENT PROP 'RunSFImmediately'.
		-- AND NO SYNTHETIC FULL JOBS ARE RUNNING
		IF @enableRunConsolidateFull = 1 AND @consolidateAutoSyntheticOptions = 0
		BEGIN
			UPDATE #TM_SyntheticInfoTable
			SET syntheticInterval = T.synthInt
			FROM
			(
				SELECT RunSFImmediately AS synthInt, #App_SubclientInfoTable.subclientId AS componentNameId
				FROM #App_SubclientInfoTable
				JOIN #TM_SyntheticInfoTable ON #TM_SyntheticInfoTable.subclientId = #App_SubclientInfoTable.subclientId
			) T
			WHERE T.synthInt < syntheticInterval	--NEW INTERVAL IS AT A LATER TIME THAN CURRENT INTERVAL THEN USE NEW INTERVAL TIME
			AND T.synthInt > 0
			AND subclientId = T.componentNameId
		END
		--SET DIFFERENT INTERVAL FOR JOBS THAT HAVE BEEN KILLED SO THAT WE DON'T HAVE TO WAIT FULL SCHEDULE INTERVAL FOR SCHEDULES
		UPDATE #TM_SyntheticInfoTable
		SET syntheticInterval = @syntheticKilledInterval
		--MR:213386 - START FAILED JOBS AFTER SOME TIME. UPDATE NEW TIME FOR ALL NON SUCCESSFUL JOBS
		WHERE bkpStatus NOT IN (1/*JMSUCCESS*/, 3/*PARTIALSUCCESS*/, 14/*JMSUCCESSWITHWARNINGS*/)
		--REMOVE THOSE OUTSIDE OF THE INTERVAL
		DELETE  #TM_SyntheticInfoTable
		FROM #TM_SyntheticInfoTable JS
		WHERE ISNULL(JS.lastSynthetic, 0) > syntheticInterval
		UPDATE #TM_SyntheticInfoTable
		SET addToRequesTable = 0
		WHERE subclientId IN (
SELECT appId FROM #appIdTbl WHERE bkpLevel <> 4
			UNION
			SELECT appId FROM #appIdTbl WHERE appTypeId		--MR=154794 - AGENTS THAT DO NOT SUPPORT SYNTHETIC FULL
IN (3, 55, 125, 135, 61, 79,
13, 104, 76, 87)
				--_CV_APPTYPE_INFORMIX, _APPTYPE_TITANIUM_PUBLIC_FOLDER, _CV_APPTYPE_POSTGRES, _CV_APPTYPE_SAP_HANA, _CV_APPTYPE_SAP_FOR_ORACLE, _CV_APPTYPE_SAP_FOR_ORACLE_FOR_SAPDB,
				--_APPTYPE_NAS_FS, _CV_APPTYPE_MYSQL, _CV_APPTYPE_PROXYHOST_IMAGELEVEL, _CV_APPTYPE_PROXYHOST_IMAGELEVEL_UNIX
		)
		-- REMOVE ENTRIES FOR AGENTS THAT DON'T SUPPORT SYNTHETIC FULL AND FOR WHICH JOBS ARE RUNNING
		DELETE #TM_SyntheticInfoTable
		FROM #TM_SyntheticInfoTable JS
		WHERE
		addToRequesTable = 0
		AND
		EXISTS (SELECT 1 FROM JMBkpJobInfo JBI WITH(NOLOCK) WHERE JBI.applicationId = JS.subclientId)
		--REMOVE ANY SUBCLIENTS WITHOUT FULL BACKUP JOB OR NEW INCREMENTAL
		DELETE #TM_SyntheticInfoTable
		FROM #TM_SyntheticInfoTable JS
		LEFT JOIN CommCellCloudLaptopClients CLOUD ON JS.clientId = CLOUD.clientID
		WHERE
		addToRequesTable = 1
		AND
		(
			(
				--DELETE WHEN NO BACKUP JOB HAS RUN
				(
					lastFull = 0
					--IF ALL FULLS HAVE BEEN DELETED CHECK FOR EXISTENCE OF OTHER JOB
					AND
					(
						lastSynthetic = 0
						OR
						NOT EXISTS (
									SELECT TOP 1 1 FROM JMBkpStats (NOLOCK)
									WHERE status IN (1, 3, 14) --JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
									AND subclientId = JMBkpStats.appId
									AND dataStatus = 0 -- DATA_STATUS_VALID -- If there are no valid(unaged) jobs, then no need to run synthetic full on the VM.
									)
					)
				)
				--MR:219866 - START SF EVEN IF NO NEW INCREMENTAL JOB RAN
				OR
				(
					CLOUD.ClientName IS NULL -- Not a cloud laptop. Cloud laptops need synthetic full and there is no incremental for it.
					AND
					lastFull >= lastIncremental
					AND
					@disableSyntheticFullWithoutIncrementalBackup = 1
				)
			)
			OR
			--REMOVE IF ANY SYNTHETIC FULL JOBS ARE ALREADY RUNNING
			EXISTS (SELECT 1 FROM JMBkpJobInfo JBI WITH(NOLOCK) WHERE JBI.applicationId = JS.subclientId AND JBI.bkpLevel = 64/*SYNTHETIC_FULL*/)
		)
		--REMOVE ANY SUBCLIENTS WITHOUT FULL BACKUP JOB for plan associated automatic synthetic full schedules.
		-- This is done since we dont want to start full backups unless the operation window is met for plan schedules. And for subclient created newly,
		-- automatic synthetic full schedule of plan triggers full backups unless below is done.
		DELETE #TM_SyntheticInfoTable
		FROM #TM_SyntheticInfoTable JS
		WHERE addToRequesTable = 0
		AND @isSchedulePolicyAssociatedToPlan = 1
		AND lastFull = 0
		--IF ALL FULLS HAVE BEEN DELETED CHECK FOR EXISTENCE OF OTHER JOB
		AND
		(
			lastSynthetic = 0
			OR
			NOT EXISTS (
						SELECT TOP 1 1 FROM JMBkpStats (NOLOCK)
						WHERE status IN (1, 3, 14) --JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
						AND subclientId = JMBkpStats.appId
						)
		)
		IF @disableSyntheticFullWithoutIncrementalBackup = 0
		BEGIN
		--MR:219866 - START SF EVEN IF NO NEW INCREMENTAL JOB RAN
		UPDATE TM_JobSyntheticRequest
		SET status = 0
		WHERE syntheticRequestId IN (
				SELECT TJ.syntheticRequestId FROM TM_JobSyntheticRequest TJ
				JOIN #TM_SyntheticInfoTable TS
				ON TJ.subTaskId = @i_subtaskId AND TJ.subclientId = TS.subclientId
				AND TJ.lastSynthetic = TS.lastSynthetic AND TJ.lastIncremental = TS.lastIncremental AND TJ.lastFull = TS.lastFull
				AND TS.addToRequesTable = 1
				AND TJ.status = 2
		)
		END
		--For laptops index server backups if Media agent is offline don't insert into TM_JobSyntheticRequest table
		DELETE #TM_SyntheticInfoTable
		FROM #TM_SyntheticInfoTable JS
		INNER JOIN App_Application APP ON APP.id = JS.subclientId
		INNER JOIN App_IndexDBInfo idx ON idx.backupSetId = APP.backupSet
		INNER JOIN MMHost host ON host.clientId = idx.currentIdxServer
		INNER JOIN APP_Client C ON C.id = APP.clientId
		WHERE addToRequesTable = 1
AND  ((host.mmhostsoftstate = 0 AND ( host.OfflineReason <> @HOST_STATUS_POWER_MANAGED_VM OR @isPowerVMFeatureEnabled = 0)) OR host.mmhostenabled = 0  OR ((host.Attribute & 16) > 0))
AND C.status & 0x1000 > 0
		INSERT INTO TM_JobSyntheticRequest
		(taskId, subTaskId, subclientId, backupsetId, instanceId, appTypeId, clientId, userId, lastSynthetic, lastFull, lastIncremental, created, modified, flags, status)
		SELECT @i_taskId, @i_subtaskId, subclientId, backupsetId, instanceId, appTypeId, clientId, @userId, lastSynthetic, lastFull, lastIncremental, @currentTime, 0, 0, 0
		FROM #TM_SyntheticInfoTable TS
		WHERE addToRequesTable = 1
		AND NOT EXISTS(
				SELECT 1 FROM TM_JobSyntheticRequest TJ
				WHERE TJ.subTaskId = @i_subtaskId AND TJ.subclientId = TS.subclientId
				AND TJ.lastSynthetic = TS.lastSynthetic AND TJ.lastIncremental = TS.lastIncremental AND TJ.lastFull = TS.lastFull
                UNION
				SELECT 1 FROM TM_JobSyntheticRequest TJ
				WHERE TJ.subTaskId = @i_subtaskId AND TJ.subclientId = TS.subclientId
				AND TJ.status IN (0, 1)
				)
		--MERGE TM_JobSyntheticRequest AS SR
		--USING
		--(
		--	SELECT subclientId, backupsetId, instanceId, appTypeId, clientId, lastSynthetic, lastFull, lastIncremental
		--	FROM #TM_SyntheticInfoTable
		--	WHERE addToRequesTable = 1
		--) AS SI ON SI.subclientId = SR.subclientId AND SR.subTaskId = @i_subtaskId  AND SI.lastSynthetic = SR.lastSynthetic AND SI.lastIncremental = SR.lastIncremental AND SI.lastFull = SR.lastFull
		--WHEN NOT MATCHED THEN
		--	INSERT (taskId, subTaskId, subclientId, backupsetId, instanceId, appTypeId, clientId, userId, lastSynthetic, lastFull, lastIncremental, created, modified, flags, status)
		--	VALUES(@i_taskId, @i_subtaskId, SI.subclientId, SI.backupsetId, SI.instanceId, SI.appTypeId, SI.clientId, @userId, SI.lastSynthetic, SI.lastFull, SI.lastIncremental, @currentTime, 0, 0, 0);
		SELECT DISTINCT subclientId appId, appTypeId, clientId, instanceId , backupsetId, status, bkpLevel, parentJobId, parentAppId
		FROM #TM_SyntheticInfoTable
		WHERE addToRequesTable = 0
	END
	ELSE
	BEGIN
		-- First check whether there are any subclients in #appIdTbl.
		-- If not and its immediate task request or run immediate req on schedule, return appropriate error message.
		IF (NOT EXISTS(SELECT 1 FROM #appIdTbl)
			AND ( (SELECT COUNT(DISTINCT appId) FROM #deletedAppIdsErrorCode ) =1 )
			AND (
( @i_initFrom IN (1, 2 )
					AND (@taskType = 2 OR EXISTS( SELECT 1 FROM #App_InputAssocs) )
				)
				OR @taskType = 1
			)
		)
		BEGIN
			SELECT @errorCode = deletedAppIdsErrorCode.errorCode,
			@o_errorString = CASE WHEN LEN(ISNULL(errorMessage,''))>0 THEN  errorMessage ELSE EvLocaleMsgs.Message END
			FROM #deletedAppIdsErrorCode deletedAppIdsErrorCode
			LEFT JOIN EvLocaleMsgs(NOLOCK) ON deletedAppIdsErrorCode.errorCode = EvLocaleMsgs.MessageID
			AND EvLocaleMsgs.LocaleId = @i_localeID
			WHERE deletedAppIdsErrorCode.errorCode!=0
		END
		SELECT DISTINCT appId, appTypeId, clientId, instanceId , backupsetId , status , bkpLevel, parentJobId, parentAppId FROM #appIdTbl
	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;
   SET @errorMessageForLogOnly = 'Error in getting subclients for backup at ERROR_LINE(): '+ CAST(ERROR_LINE() as varchar(10))+' ERROR_MESSAGE(): ' + ERROR_MESSAGE()
    if ( CURSOR_STATUS('variable', 'getParentVSAJobId') > 0) /* cursor is open */
        close  getParentVSAJobId
    IF ( CURSOR_STATUS('variable', 'getParentVSAJobId') = -1) /* cursor is closed */
        deallocate getParentVSAJobId
END CATCH;
	IF (LEN(@o_errorString)>0 OR LEN(@errorMessageForLogOnly)>0)
	BEGIN
	     SET @o_errorString = (SELECT @errorCode '@errorCode' ,@o_errorString '@displayErrorMessage', @errorMessageForLogOnly '@errorMessage' FOR XML PATH('genericResp'),ROOT('TMMsg_GetBackupEntityResp'))
	END
	ELSE
	BEGIN
		SET @o_errorString = (SELECT clientID '@clientId' FROM #cloudclientsTbl FOR XML PATH('cloudClients'),ROOT('TMMsg_GetBackupEntityResp'))
	END
END
IF object_id('tempdb.dbo.#App_VmClientInfoTable') is not null
	DROP TABLE #App_VmClientInfoTable
IF object_id('tempdb.dbo.#parentAppIdTbl') is not null
    DROP TABLE #parentAppIdTbl
if object_id('tempdb.dbo.#appIdTbl') is not null
    DROP TABLE #appIdTbl
if object_id('tempdb.dbo.#v1OneDriveScsSynthfull') is not null
    DROP TABLE #v1OneDriveScsSynthfull
IF object_id('tempdb.dbo.#App_SubclientInfoTable') is not null
	DROP TABLE #App_SubclientInfoTable
IF object_id('tempdb.dbo.#TM_SyntheticInfoTable') is not null
	DROP TABLE #TM_SyntheticInfoTable
IF object_id('tempdb.dbo.#deletedAppIdsErrorCode') is not null
	DROP TABLE #deletedAppIdsErrorCode
IF object_id('tempdb.dbo.#deletedVMAppIds') is not null
	DROP TABLE #deletedVMAppIds
GO

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

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

insert into GXDBVersions values(2, 'TM_GetBackupEntity',  'v1.111.2.126.4.16', 'TM_GetBackupEntity', 'v1.111.2.126.4.16')
GO

