

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/TM_GetBackupEntityCountDataSet.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_GetBackupEntityCountDataSet.sp,v $ $Id: TM_GetBackupEntityCountDataSet.sp,v 1.1.2.16.4.5 2021/06/10 04:07:09 rengu Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='TM_GetBackupEntityCountDataSet')
	delete from GXDBVersions where aliasname = 'TM_GetBackupEntityCountDataSet'
GO
print '... Creating Procedure: TM_GetBackupEntityCountDataSet'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure TM_GetBackupEntityCountDataSet
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
--
--   PARAMETERS   &   OUTPUTS
--
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	IF OBJECT_ID('tempdb.dbo.#TM_GetBackupEntityCountDataSet_taskTble') IS NULL
	BEGIN
		PRINT 'TM_GetBackupEntityCountDataSet: Warning no external inputted table #TM_GetBackupEntityCountDataSet_taskTble'
		RETURN 1
	END
	BEGIN TRY
--- CODE BELOW converted to Data Set Op from TM_GetBackupEntityConfigDataSet.spb  ----------
BEGIN
		DECLARE @dbgPrint	TINYINT = 0	-- normally 0
		DECLARE @i_initFrom 	INT = 0
		DECLARE @entityCount	INT = 1
		DECLARE @queueJobsIfActivityDisabled INT = 0
		DECLARE @runTaskOptionIgnoreActivityCheck INT = 0
		-- 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
		-- External Inputted Table
		--IF OBJECT_ID('tempdb.dbo.#TM_GetBackupEntityCountDataSet_taskTble') is not null
		--	DROP TABLE #TM_GetBackupEntityCountDataSet_taskTble
		--CREATE TABLE #TM_GetBackupEntityCountDataSet_taskTble (
		--	taskId				INT,
		--	subTaskId			INT,
		--	subTaskType			INT,
		--	entityCount			INT DEFAULT NULL,
		--	PRIMARY KEY (taskId, subTaskId)
		--)
		-- Simulate test data set
		--INSERT INTO #TM_GetBackupEntityCountDataSet_taskTble (taskId, subTaskId, subTaskType)
		--	SELECT taskId, subTaskId, subTaskType FROM Aaron_taskTable
		IF OBJECT_ID('tempdb.dbo.#TaskTableData') is not null
			DROP TABLE #TaskTableData
		CREATE TABLE #TaskTableData (
			taskId				INT,
			subTaskId			INT,
			subTaskType			INT,
			operationType		INT DEFAULT NULL,
			taskType			INT DEFAULT NULL,
			policyType			INT DEFAULT NULL,
			initiatedFrom		INT DEFAULT NULL,
			isEdgeDrivePolicy	TINYINT DEFAULT NULL,
			freqInterval		INT DEFAULT NULL,
			backupLevel			INT DEFAULT NULL,
			aeProcessIt			TINYINT DEFAULT 1,		-- Used only by TM_GetAdminEntityCountDataSet SP, but needs to created in TM_GetBackupEntityCountDataSet SP and default to 1 instead for SPBody
			isPlanSchedulePolicy INT DEFAULT 0,
			patternType			INT DEFAULT 0,
			isLogSchedulePolicy INT DEFAULT 0
			PRIMARY KEY (taskId, subTaskId)
		)
		INSERT INTO #TaskTableData (
			taskId,
			subTaskId,
			subTaskType,
			operationType,
			taskType,
			policyType,
			initiatedFrom,
			isEdgeDrivePolicy,
			freqInterval,
			backupLevel,
			isPlanSchedulePolicy,
			patternType,
			isLogSchedulePolicy
		)
			SELECT
				et.taskId,
				et.subTaskId,
				et.subTaskType,
				stk.operationType,
				tk.taskType,
				tk.policyType,
				tk.initiatedFrom,
				CASE
					WHEN(tk.flags & 0x40) <> 0		-- TASK_FLAGS_EDGE_DRIVE
						THEN 1
					ELSE 0
				END isEdgeDrivePolicy,
				ISNULL((
					SELECT
						(P.freq_interval*60)
					FROM TM_PatternAssoc PA WITH (NOLOCK)
						INNER JOIN TM_Pattern P WITH (NOLOCK) ON
							P.patternId = PA.patternId
							AND PA.subTaskId = et.subtaskId
							AND P.freq_type = 4096
				), 0) freqInterval,
				sto.value backupLevel,
				CASE
WHEN(tk.flags & 536870912 ) <> 0		-- PLAN_ASSOCIATED_ENTITY
						THEN 1
					ELSE 0
				END isPlanSchedulePolicy,
				ISNULL((
					SELECT
						P.freq_type
					FROM TM_PatternAssoc PA WITH (NOLOCK)
						INNER JOIN TM_Pattern P WITH (NOLOCK) ON
							P.patternId = PA.patternId
							AND PA.subTaskId = et.subtaskId
				), 0),
				ISNULL((
	            	SELECT 1
	                	FROM APP_PlanProp PP WITH(NOLOCK)
WHERE PP.attrName = 'Log schedule policy'
	                    AND CAST(et.taskId AS VARCHAR(20)) = PP.attrVal
	                    AND PP.modified = 0
	                ),0)
			FROM #TM_GetBackupEntityCountDataSet_taskTble et
				INNER JOIN TM_Task tk WITH (NOLOCK) ON
					tk.taskId = et.taskId
					--AND et.subTaskType NOT IN (1, 3)		-- filtered by external SP
				INNER JOIN TM_SubTask stk WITH(NOLOCK) ON
					stk.subTaskId = et.subTaskId
				INNER JOIN TM_SubTaskOptions sto WITH (NOLOCK) ON
					sto.subTaskId = et.subTaskId
					AND sto.optionId = 458405394	--  BACKUPOPTION_BACKUP_LEVEL
		IF OBJECT_ID('tempdb.dbo.#SubTaskOptions') is not null
			DROP TABLE #SubTaskOptions
		CREATE TABLE #SubTaskOptions (
			subTaskId			INT,
			dataOptAutoCopy		INT,
			cdrOptionsvolRecCreation INT,
			backupLevel			INT,
			useAutomaticIntervalSF	INT ,
			enableRunFullConsolidationBackup INT,
			useStorageSpaceFromMA INT,
			sapMaxDBBackupType INT,
			useJobStartTime	   INT,
			minBackupInterval  INT,
			minBackupIntervalMins INT,
			maxBackupInterval  INT,
			maxBackupIntervalMins INT,
			PRIMARY KEY (subTaskId)
		)
		INSERT INTO #SubTaskOptions(subTaskId, dataOptAutoCopy,cdrOptionsvolRecCreation, backupLevel, useAutomaticIntervalSF,enableRunFullConsolidationBackup,useStorageSpaceFromMA,sapMaxDBBackupType,useJobStartTime,
		minBackupInterval, minBackupIntervalMins, maxBackupInterval, maxBackupIntervalMins)
		SELECT DISTINCT subTaskId,[1013376701], [433543038], [458405394], [1778633837], [328367274], [1299309857],[150853164], ISNULL( [414240771],0),
			ISNULL([1611532840],0), ISNULL([1110217321],0),ISNULL([703512134],0),IsNULL([1384124987],0)
			FROM
			(
				SELECT sto.subTaskId,sto.optionId,sto.value
				FROM TM_SubTaskOptions sto WITH (NOLOCK)
				INNER JOIN #TaskTableData et ON et.subTaskId = sto.subtaskId
WHERE sto.optionId IN (1013376701, 433543038, 458405394, 1778633837, 328367274, 1299309857, 150853164, 414240771,
1611532840, 1110217321, 703512134,1384124987 )
				-- DATAOPT_AUTO_COPY, CDROPTIONS_VOL_RECOVERY_POINT_CREATION, BACKUPOPTION_BACKUP_LEVEL, DATABKPOTION_USE_AUTOMATIC_INTERVAL_FOR_SYNTHETIC_FULL, DATABKPOTION_ENABLE_RUN_FULL_CONSOLIDATION_BACKUP, AUTOMATICSCHEDULEPATTERN_USE_STORAGE_SPACE_FROM_MA
				-- DBBACKUPOPTIONS_MAX_DB_BACKUP_TYPE, COMMONOPTION_USE_JOB_START_TIME_FOR_CONTINUOUS_SCHEDULE, AUTOMATICSCHEDULEPATTERN_MIN_BACKUP_INTERVAL, AUTOMATICSCHEDULEPATTERN_MIN_BACKUP_INTERVAL_MINUTES, AUTOMATICSCHEDULEPATTERN_MAX_BACKUP_INTERVAL,
				-- AUTOMATICSCHEDULEPATTERN_MAX_BACKUP_INTERVAL_MINUTES
			) Sto
			PIVOT
			(
				MAX(value)
				FOR OptionID in ([1013376701], [433543038], [458405394], [1778633837], [328367274], [1299309857], [150853164], [414240771], [1611532840],[1110217321],
					[703512134],[1384124987] )
				-- DATAOPT_AUTO_COPY, CDROPTIONS_VOL_RECOVERY_POINT_CREATION, BACKUPOPTION_BACKUP_LEVEL, DATABKPOTION_USE_AUTOMATIC_INTERVAL_FOR_SYNTHETIC_FULL, DATABKPOTION_ENABLE_RUN_FULL_CONSOLIDATION_BACKUP, AUTOMATICSCHEDULEPATTERN_USE_STORAGE_SPACE_FROM_MA, COMMONOPTION_USE_JOB_START_TIME_FOR_CONTINUOUS_SCHEDULE,
				-- AUTOMATICSCHEDULEPATTERN_MIN_BACKUP_INTERVAL, AUTOMATICSCHEDULEPATTERN_MIN_BACKUP_INTERVAL_MINUTES,AUTOMATICSCHEDULEPATTERN_MAX_BACKUP_INTERVAL,AUTOMATICSCHEDULEPATTERN_MAX_BACKUP_INTERVAL_MINUTES
			) AS pvt
		IF OBJECT_ID('tempdb.dbo.#TaskCfg') is not null
			DROP TABLE #TaskCfg
		CREATE TABLE #TaskCfg (
			taskId				INT,
			subTaskId			INT,
			subTaskType			INT,
			operationType		INT,
			taskType			INT,
			autoCopy			INT DEFAULT 0,
			fanInRP				INT DEFAULT 0,
			autoSyntheticDay	INT DEFAULT 0,
			backupLevel			INT DEFAULT 2,		-- BACKUP_LEVEL_INCREMENTAL
			enableRunConsolidateFull	INT DEFAULT 0,--,
			isSPAMAXDBBackupSupported INT DEFAULT 0,
			isDumpSweepEnabled INT DEFAULT 0
			--PRIMARY KEY (taskId, subTaskId)
		)
		CREATE CLUSTERED INDEX TaskCfg_idx ON #TaskCfg (taskId, subTaskId)
		INSERT INTO #TaskCfg (
			taskId,
			subTaskId,
			subTaskType,
			operationType,
			taskType,
			autoCopy,
			fanInRP,
			autoSyntheticDay,
			backupLevel,
			enableRunConsolidateFull,
			isSPAMAXDBBackupSupported,
			isDumpSweepEnabled
		)
			SELECT DISTINCT
				et.taskId,
				et.subTaskId,
				et.subTaskType,
				et.operationType,
				et.taskType,
				ISNULL(StoOuter.dataOptAutoCopy,0)	autoCopy,	-- DATAOPT_AUTO_COPY
				ISNULL(StoOuter.cdrOptionsvolRecCreation,0)	fanInRP , -- CDROPTIONS_VOL_RECOVERY_POINT_CREATION
				ISNULL(StoOuter.useAutomaticIntervalSF,0)	autoSyntheticDay , -- DATABKPOTION_USE_AUTOMATIC_INTERVAL_FOR_SYNTHETIC_FULL
				ISNULL(StoOuter.backupLevel,2)	backupLevel , -- BACKUPOPTION_BACKUP_LEVEL
				ISNULL(StoOuter.enableRunFullConsolidationBackup,0)	enableRunConsolidateFull , -- DATABKPOTION_ENABLE_RUN_FULL_CONSOLIDATION_BACKUP
CASE WHEN StoOuter.sapMaxDBBackupType !='0' THEN 1		-- DBBACKUPOPTIONS_MAX_DB_BACKUP_TYPE
					ELSE 0
				END isSPAMAXDBBackupSupported,
CASE WHEN StoOuter.useStorageSpaceFromMA !='0' THEN 1		-- AUTOMATICSCHEDULEPATTERN_USE_STORAGE_SPACE_FROM_MA
					ELSE 0
				END isDumpSweepEnabled
			FROM #TaskTableData et
			INNER JOIN #SubTaskOptions StoOuter
			ON et.subTaskId = StoOuter.subtaskId
			UPDATE et
			SET isSPAMAXDBBackupSupported = 1
			FROM #TaskCfg et
			INNER JOIN TM_SubTaskOptions sto WITH (NOLOCK) ON
			sto.subTaskId = et.subTaskId
AND sto.optionId = 150853164
			AND sto.value != '0' -- unknown
			AND EXISTS(SELECT 1 FROM WF_Definition (NOLOCK) where name='SAPMaxDB' AND (flags&1) = 0) -- to check if its not disabled.
			INNER JOIN TM_PatternAssoc (NOLOCK) ON et.subTaskId = TM_PatternAssoc.subtaskID
			INNER JOIN TM_Pattern (NOLOCK) ON TM_Pattern.patternId = TM_PatternAssoc.patternId
AND TM_Pattern.freq_type != 4096 -- We dont support SAP hanna backup for continuous schedules
			AND et.autoCopy = 0 --We dont support SAP hanna backup for automatic syncthetic full schedules
END
--- CODE ABOVE converted to Data Set Op from TM_GetBackupEntityConfigDataSet.spb  ----------
--- CODE BELOW FROM TM_GetBackupEntityCntIncludeDataSet.spb  ----------
BEGIN
	-------------------------------
	--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 (
		taskId			INT,
		stId			INT, -- include subtaskID also since filters vary on schedule options.
		appId			INT,
		appTypeId		INT,
		clientId		INT,
		instanceId		INT,
		backupsetId		INT,
		status			INT,
		bkpLevel		INT,
		storagePolicy	INT,
		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.
		checkOpWindow	INT	DEFAULT 1,
		timezoneName  NVARCHAR(255) DEFAULT '',
		jobStartTime    INT DEFAULT -1
	)
	CREATE CLUSTERED INDEX appIdTbl_idx ON #appIdTbl (appId, taskId, stId)
	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)
	IF OBJECT_ID('tempdb.dbo.#taskAllEntities') IS NOT NULL
		DROP TABLE #taskAllEntities
	CREATE TABLE #taskAllEntities (
		taskId		INT PRIMARY KEY
	)
	INSERT INTO #taskAllEntities (taskId)
		SELECT DISTINCT
			ae.taskId
		FROM TM_AssocEntity ae
			INNER JOIN #TaskTableData tc ON
				ae.taskId = tc.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
	----------------------------------
	--GET ALL ASSOCIATED CLIENTS------
	----------------------------------
	INSERT INTO #appIdTbl (
		taskid,
		stId,
		appId,
		appTypeId,
		clientId,
		instanceId,
		backupsetId,
		status,
		bkpLevel,
		storagePolicy
	)
		--CLIENT GROUP
		SELECT DISTINCT
			tc.taskId,
			tc.subTaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			tc.backupLevel,
			APP.dataArchGrpID
		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.entityId = 0
			INNER JOIN #TaskTableData tc ON
				AE.taskId = tc.taskId
				AND tc.aeProcessIt = 1
		--ALL APPID WITH SAME GROUPID
		UNION ALL
		SELECT DISTINCT
			tc.taskId,
			tc.subTaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			tc.backupLevel,
			APP.dataArchGrpID
		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
			INNER JOIN #TaskTableData tc ON
				AF.taskId = tc.taskId
				AND tc.aeProcessIt = 1
			INNER JOIN #taskAllEntities tae ON
				AF.taskId = tae.taskId
		--ALL APPID WITH SAME APPTYPES
		UNION ALL
		SELECT DISTINCT
			tc.taskId,
			tc.subTaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			tc.backupLevel,
			APP.dataArchGrpID
		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
			INNER JOIN #TaskTableData tc ON
				AF.taskId = tc.taskId
				AND tc.aeProcessIt = 1
				AND AF.taskId IN (
					SELECT
						tae.taskId
					FROM #taskAllEntities tae
				)
		UNION ALL
		SELECT DISTINCT
			tc.taskId,
			tc.subTaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			tc.backupLevel,
			APP.dataArchGrpID
		FROM APP_Application AS APP		-- corrected full outer join error
			INNER JOIN APP_iDAType IT ON
				APP.appTypeId = IT.type
			LEFT OUTER JOIN (
				SELECT DISTINCT
					AF.taskId,
					AF.filter_value
				FROM #TaskTableData tc
					INNER JOIN TM_AssocFilter AF ON
						tc.taskId = AF.taskId
						AND tc.aeProcessIt = 1
						AND AF.filter_type = 2
			) AF ON
				IT.type = AF.filter_value
			CROSS JOIN (
				SELECT DISTINCT
					ot.taskId,
					ot.backupLevel,
					ot.subTaskId
				FROM #TaskTableData ot
					INNER JOIN #taskAllEntities t ON
						t.taskId = ot.taskId
						AND ot.aeProcessIt = 1
					LEFT OUTER JOIN TM_AssocFilter AF ON
						ot.taskId = AF.taskId
						AND AF.filter_type = 2
				WHERE
					AF.taskId IS NULL
			) tc
			--== May need to filter against IT.type again???
		WHERE
			AF.taskId IS NULL
		--ALL APPID WITH APPTYPEID = 0
		UNION ALL
		SELECT DISTINCT
			tc.taskId,
			tc.subTaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			tc.backupLevel,
			APP.dataArchGrpID
		FROM APP_Application AS APP
			INNER JOIN TM_AssocEntity AS TM ON
				APP.clientId = TM.clientId
				AND TM.appTypeId = 0
				AND TM.exclude = 0
				AND TM.entityId = 0
			INNER JOIN #TaskTableData tc ON
				TM.taskId = tc.taskId
				AND tc.aeProcessIt = 1
		--ALL APPID WITH SAME APPTYPE AND CLIENTID = 0
		UNION ALL
		SELECT DISTINCT
			at.taskId,
			at.subTaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			at.backupLevel,
			APP.dataArchGrpID
		FROM APP_Application AS APP
			INNER JOIN (
				SELECT DISTINCT
					te.appTypeId,
					tc.taskId,
					tc.subTaskId,
					tc.backupLevel
				FROM TM_AssocEntity te
					INNER JOIN #TaskTableData tc ON
						te.taskId = tc.taskId
						AND tc.aeProcessIt = 1
						AND te.clientid = 0
						AND te.exclude = 0
						AND te.entityId = 0
				) at ON
					at.appTypeId = APP.appTypeId
		--ALL APPID WITH INSTANCE = 0
		UNION ALL
		SELECT DISTINCT
			tc.taskId,
			tc.subTaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			tc.backupLevel,
			APP.dataArchGrpID
		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 TM.exclude = 0
				AND TM.entityId = 0
			INNER JOIN #TaskTableData tc ON
				TM.taskId = tc.taskId
				AND tc.aeProcessIt = 1
		--ALL APPID WITH BACKUPSET = 0
		UNION ALL
		SELECT DISTINCT
			tc.taskId,
			tc.subtaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			tc.backupLevel,
			APP.dataArchGrpID
		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 TM.exclude = 0
				AND TM.entityId = 0
			INNER JOIN #TaskTableData tc ON
				TM.taskId = tc.taskId
				AND tc.aeProcessIt = 1
		--ALL APPID WITH SUBCLIENT = 0
		UNION ALL
		SELECT DISTINCT
			tc.taskId,
			tc.subTaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			tc.backupLevel,
			APP.dataArchGrpID
		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 TM.exclude = 0
				AND TM.entityId = 0
			INNER JOIN #TaskTableData tc ON
				TM.taskId = tc.taskId
				AND tc.aeProcessIt = 1
		--ALL APPID
		UNION ALL
		SELECT DISTINCT
			tc.taskId,
			tc.subTaskId,
			APP.id,
			APP.appTypeId,
			APP.clientId,
			APP.instance,
			APP.backupset,
			APP.subclientStatus,
			tc.backupLevel,
			APP.dataArchGrpID
		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 TM.exclude = 0
				AND TM.entityId = 0
			INNER JOIN #TaskTableData tc ON
				TM.taskId = tc.taskId
				AND tc.aeProcessIt = 1
		--plan Associated entities
		UNION ALL
			SELECT DISTINCT
				tc.taskId,
				tc.subTaskId,
				APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupset,
				APP.subclientStatus,
				tc.backupLevel,
				APP.dataArchGrpID
			FROM APP_Application AS APP WITH(NOLOCK)
			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
			INNER JOIN #TaskTableData tc ON
				AE.taskId = tc.taskId
				AND tc.aeProcessIt = 1
			WHERE SCP.attrName = 'Associated Plan' AND SCP.modified = 0 AND SCP.cs_attrName = CHECKSUM(N'Associated Plan')
			AND AE.taskId = tc.taskId
			AND AE.exclude = 0
			AND AE.entityId > 0
END
--- CODE ABOVE FROM TM_GetBackupEntityCntIncludeDataSet.spb  ----------
--- CODE BELOW FROM TM_GetBackupEntityCntExcludeDataSet.spb  ----------
BEGIN
	-- No need to exclude cloud laptop clients since we are pushing workqueue for them now.
	---------------------------
	--EXCLUSION----------------
	---------------------------
	IF OBJECT_ID('tempdb.dbo.#ExclApps') IS NOT NULL
		DROP TABLE #ExclApps
	CREATE TABLE #ExclApps (
		appId		INT,
		taskId		INT,
		PRIMARY KEY (appId, taskId)
	)
	INSERT INTO #ExclApps (appId, taskId)
		--CLIENT GROUP
		SELECT
			APP.id,
			ot.taskId
		FROM APP_Application APP
			INNER JOIN APP_ClientGroupAssoc cga ON
				APP.clientId = cga.clientId
			INNER JOIN TM_AssocEntity ae ON
				ae.clientGroupId = cga.clientGroupId
				AND ae.clientId = 0
				AND ae.exclude = 1
			INNER JOIN #TaskTableData ot ON
				ae.taskId = ot.taskId
				AND ot.aeProcessIt = 1
		--ALL APPID WITH APPTYPEID = 0
		UNION
		SELECT
			APP.id,
			ot.taskId
		FROM APP_Application AS APP
			INNER JOIN TM_AssocEntity AS TM ON
				APP.clientId = TM.clientId
				AND TM.appTypeId = 0
				AND TM.exclude = 1
			INNER JOIN #TaskTableData ot ON
				TM.taskId = ot.taskId
				AND ot.aeProcessIt = 1
		--ALL APPID WITH SAME APPTYPE AND CLIENTID = 0
		UNION
		SELECT
			APP.id,
			ot.taskId
		FROM APP_Application AS APP
			INNER JOIN TM_AssocEntity ae ON
				ae.appTypeId = APP.appTypeId
				AND ae.clientId = 0
				AND ae.exclude = 1
			INNER JOIN #TaskTableData ot ON
				ae.taskId = ot.taskId
				AND ot.aeProcessIt = 1
		--ALL APPID WITH INSTANCE = 0
		UNION
		SELECT
			APP.id,
			ot.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 TM.exclude = 1
			INNER JOIN #TaskTableData ot ON
				TM.taskId = ot.taskId
				AND ot.aeProcessIt = 1
		--ALL APPID WITH BACKUPSET = 0
		UNION
		SELECT
			APP.id,
			ot.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 TM.exclude = 1
			INNER JOIN #TaskTableData ot ON
				TM.taskId = ot.taskId
				AND ot.aeProcessIt = 1
		--ALL APPID WITH SUBCLIENT = 0
		UNION
		SELECT
			APP.id,
			ot.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 TM.exclude = 1
			INNER JOIN #TaskTableData ot ON
				TM.taskId = ot.taskId
				AND ot.aeProcessIt = 1
		--ALL APPID
		UNION
		SELECT
			APP.id,
			ot.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 TM.exclude = 1
			INNER JOIN #TaskTableData ot ON
				TM.taskId = ot.taskId
				AND ot.aeProcessIt = 1
		UNION
		SELECT
			APROP.componentNameId,
			OT.taskId
		FROM TM_Task task WITH (NOLOCK)
			INNER JOIN TM_AssocEntity TM WITH (NOLOCK) ON
				(task.flags & 128) = 128
				AND @i_initFrom NOT IN (1, 2)		-- INIT_FROM_TYPE_GUI INIT_FROM_TYPE_COMMANDLINE
				AND TM.taskId = task.taskId
			INNER JOIN APP_SubClientProp APROP WITH (NOLOCK) ON
				TM.subclientId = APROP.componentNameId
				AND APROP.cs_attrName = CHECKSUM(N'Index SubClient')
				AND APROP.attrName = N'Index SubClient'
				AND APROP.attrVal = N'1'
			INNER JOIN #TaskTableData ot ON ot.aeProcessIt = 1
			AND ot.taskId!=task.taskId
		UNION
		-- For case manager index copy jobs, support only case manager clients.
		SELECT
			APP.id,
			ot.taskId
		FROM APP_Application AS APP
		INNER JOIN #appIdTbl appIdTbl ON APP.Id = appIdTbl.appId
		INNER JOIN #TaskTableData ot ON
		appIdTbl.taskId = ot.taskId
		AND ot.aeProcessIt = 1
		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
		-- for plan schedule policy dont include case manager subclients
			SELECT appIdTbl.appId,
			ot.taskId
		FROM #appIdTbl appIdTbl
		INNER JOIN #TaskTableData ot ON
		appIdTbl.taskId = ot.taskId
		AND ot.aeProcessIt = 1
		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 isPlanSchedulePolicy = 1
	DELETE a
	FROM #appIdTbl a
		INNER JOIN #ExclApps ex ON
			ex.appId = a.appId
			AND ex.taskId = a.taskId
	IF OBJECT_ID('tempdb.dbo.#ExclApps') IS NOT NULL
		DROP TABLE #ExclApps
END
--- CODE ABOVE FROM Op from TM_GetBackupEntityCntExcludeDataSet.spb  ----------
--- CODE BELOW converted to Data Set Op from TM_GetBackupEntityCntFilterDataSet.spb  ----------
BEGIN
	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)
	-- 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 a
	SET bkpLevel = 1 -- FULL
	FROM #appIdTbl a
	INNER JOIN #TaskTableData td ON td.taskId = a.taskId
AND td.backupLevel = 2 -- 2 If user has modified backup level then no need for backup level conversion.
	INNER JOIN TM_PatternAssoc (NOLOCK)	ON TM_PatternAssoc.subtaskID = td.subtaskId
	INNER JOIN TM_Pattern (NOLOCK)	ON TM_PatternAssoc.patternID = TM_Pattern.patternID
AND (TM_Pattern.freq_type = 4096  OR
(TM_Pattern.freq_type = 4  AND TM_Pattern.Freq_subday_interval = 0)
	)
	WHERE
		a.bkpLevel <> 1
		AND EXISTS (
			SELECT 1
			FROM APP_instanceprop (NOLOCK)
			WHERE
				componentNameId = a.instanceId
				AND attrName = N'MSSQL Server Type'
				AND modified = 0
				AND attrVal IN (		-- Differential is not supported for blocklevel subclients
					N'Analysis Services',
					N'Azure DataBase Engine',
					N'Aws DataBase Engine',
					N'Azure Managed DataBase Engine'
				)
		)
	---------------------------
	--FILTER OF APPID
	---------------------------
	--1. Filter the appGroup
	--2. Filter the appType
	IF EXISTS (
		SELECT
			af.filter_value
		FROM TM_AssocFilter af
			INNER JOIN #TaskCfg tc ON
				af.taskId = tc.taskId
				AND af.filter_value <> 0
	)
	BEGIN
		DELETE a
		FROM #appIdTbl a
			LEFT OUTER JOIN (
				SELECT
					ATGA.appTypeId,
					tc.taskId
				FROM APP_AppTypeGroupAssoc ATGA
					INNER JOIN TM_AssocFilter AF ON
						ATGA.typeOfGroup = 0
						AND ATGA.appGroupId = AF.filter_value
						AND AF.filter_type = 1
					INNER JOIN #TaskCfg tc ON
						AF.taskId = tc.taskId
				UNION
				SELECT
					IT.type,
					tc.taskId
				FROM APP_iDAType IT
					INNER JOIN TM_AssocFilter AF ON
						IT.type = AF.filter_value
						AND AF.filter_type = 2
					INNER JOIN #TaskCfg tc ON
						AF.taskId = tc.taskId
				UNION
				SELECT
					AT.appTypeId,
					tc.taskId
				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
					INNER JOIN #TaskCfg tc ON
						AF.taskId = tc.taskId
			) q ON
				q.appTypeId = a.appTypeId
				AND q.taskId = a.taskId
		WHERE
			q.appTypeId IS NULL
			AND a.taskId IN (
				SELECT
					tc.taskId
				FROM TM_AssocFilter af
					INNER JOIN #TaskCfg tc ON
						af.taskId = tc.taskId
						AND af.filter_value <> 0
			)
	END
	-- Setup Subclient Properties in one pass
	IF OBJECT_ID('tempdb.dbo.#SCProps') IS NOT NULL
		DROP TABLE #SCProps
	CREATE TABLE #SCProps (
		appId				INT PRIMARY KEY,
		archRetention		INT DEFAULT NULL,
		ddbBackup			INT DEFAULT NULL,
		idxSubclient		INT DEFAULT NULL,
		blrReplication		INT DEFAULT NULL,
		turboSubclient		INT DEFAULT NULL,
		bkupRetention		INT DEFAULT NULL,
		extendRetention		INT DEFAULT NULL,
		sqlSubClientType	INT DEFAULT NULL,
		isOneTimeArchiving 	INT DEFAULT NULL
	)
	INSERT INTO #SCProps
		SELECT
			p.appId,
			[Is Archive Retention Mode Enabled],
			[DDB Backup],
			[Index SubClient],
			[Is BLR Replication Subclient],
			[Is Turbo Subclient],
			[Is Backup Retention Mode Enabled],
			[Extended Storage Policy Retention],
			[mssql subclient type],
			[adHoc One Time Archive]
		FROM (
				SELECT
					componentNameId appId,
					attrName,
					attrVal
				FROM
					APP_SubClientProp scp
				WHERE
					scp.modified = 0
					AND scp.attrName IN (
						 N'Is Archive Retention Mode Enabled',
						 N'DDB Backup',
						 N'Index SubClient',
						 N'Is BLR Replication Subclient',
						 N'Is Turbo Subclient',
						 N'Is Backup Retention Mode Enabled',
						 N'Extended Storage Policy Retention',
						 N'mssql subclient type',
						 N'adHoc One Time Archive'
					)
					AND scp.cs_attrName IN (
						   CHECKSUM( N'Is Archive Retention Mode Enabled'),
						   CHECKSUM(N'DDB Backup'),
						   CHECKSUM( N'Index SubClient'),
						   CHECKSUM(N'Is BLR Replication Subclient'),
						   CHECKSUM(N'Is Turbo Subclient'),
						   CHECKSUM( N'Is Backup Retention Mode Enabled'),
						   CHECKSUM( N'Extended Storage Policy Retention'),
						   CHECKSUM(N'mssql subclient type'),
						   CHECKSUM(N'adHoc One Time Archive')
					)
			) AS d
			PIVOT (MAX(attrVal) FOR attrName IN (
					[Is Archive Retention Mode Enabled],
					[DDB Backup],
					[Index SubClient],
					[Is BLR Replication Subclient],
					[Is Turbo Subclient],
					[Is Backup Retention Mode Enabled],
					[Extended Storage Policy Retention],
					[mssql subclient type],
					[adHoc One Time Archive]
				)
			)  p
	DECLARE @bMultiBackupsetAllowInc INT = 0
	SELECT TOP 1
		@bMultiBackupsetAllowInc = 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
	IF OBJECT_ID('tempdb.dbo.#FilterApps') IS NOT NULL
		DROP TABLE #FilterApps
	CREATE TABLE #FilterApps (
		appId		INT,
		taskId		INT,
		subTaskID	INT,
		PRIMARY KEY (appId, taskId, subTaskID)
	)
	INSERT INTO #FilterApps (appId, taskId, subTaskID)
		--3. Filter the dummy IDA
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
		WHERE
			appTypeId >= 1000
			OR appTypeId = 72		-- CV_APPTYPE_RECOVERY_DIRECTOR
OR appTypeId = 136		-- CV_APPTYPE_RECOVERY_DIRECTOR
		--4. Filter the uninstalled, deleted, dummy, hidden subclient
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stID
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskID = a.stID
			LEFT OUTER JOIN #SCProps p1 ON
				p1.appId = a.appId
		WHERE
			(
				(	-- Synth full are allowed on deconfigured clients
					--(
					--	@dontStartSynthFullOnDeConfClients = 1
					--	OR @backupLevel <> 4
					--	OR @entityCount = 1		--== hardcode to 1 so always true, commented out block
					--)			-- BACKUP_LEVEL_SYNTHETIC_FULL
					(a.status & 2) = 2	-- CV_STATUS_UNINSTALLED
					AND tc.operationType <> 4028	--TASK_OPERATION_TYPE_SNAP_TO_TAPE
					AND (
						--MR = 158352 - allow DDB subclient backups even if subclient is deconfigured
						p1.ddbBackup IS NULL
						OR p1.ddbBackup <> 1
					)
					AND (
						p1.idxSubclient IS NULL
						OR p1.idxSubclient <> 1
					)
				)
				OR (	-- Skip synth full on deconfigured subclients that do not have storage policy set.
					tc.backupLevel = 4		-- BACKUP_LEVEL_SYNTHETIC_FULL
					AND (a.status & 2) = 2	-- CV_STATUS_UNINSTALLED
					AND a.storagePolicy <= 1
				)
				OR (
					(a.status & 4) = 4 -- CV_STATUS_DELETED
					AND tc.operationType <> 4028	--TASK_OPERATION_TYPE_SNAP_TO_TAPE
				)
OR ( (a.STATUS & 16) = 16 AND (tc.isSPAMAXDBBackupSupported = 0 OR a.appTypeId!= 79 ) )-- CV_STATUS_DUMMY
				OR
				(
					(a.status & 32) = 32 -- CV_STATUS_HIDDEN
					AND NOT (
						a.apptypeId IN (84, 107)	-- CV_APPTYPE_CDR, CV_APPTYPE_WORKSTATION_BACKUP
						AND (a.status & 8) = 8	-- CV_STATUS_DEFAULT
					) --IGNORE CDR/WBA SINCE THEY ARE MARKED HIDDEN
					AND (
						--Allow hidden BLR subclients
						p1.blrReplication IS NULL
						OR p1.blrReplication <> 1
					)
				)
			)
		--5. Filter the default subclient for below iDA
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
		WHERE
			a.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 (a.status & 8) = 8		-- CV_STATUS_DEFAULT
			AND (a.status & 65536) = 0	-- CV_STATUS_REFCOPY_SUB
		--58
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskId = a.stId
		WHERE
			a.apptypeId IN (65, 25)		-- CV_APPTYPE_CDR, CV_APPTYPE_WORKSTATION_BACKUP
			AND ((a.status & 8) = 8)		-- CV_STATUS_DEFAULT
			OR (
				a.appTypeId IN (84, 107)	-- CV_APPTYPE_CDR, CV_APPTYPE_WORKSTATION_BACKUP
				AND (
					((a.status & 8) <> 8)	-- CV_STATUS_DEFAULT
					OR (
						tc.fanInRP = 0
						AND a.backupsetId IN (
							SELECT bs.id
							FROM APP_BackupSetName bs
							WHERE (bs.status & 40) = 40
								AND bs.id = a.backupsetId
							)
						)
				)
			)
			OR (	--MR54684 REMOVED CDR FROM SCHEDULE POLICY AGAIN WILL BE ADDED IN 10.0?
				a.appTypeId = 84		-- CV_APPTYPE_CDR
				AND (tc.taskType & 4 = 4)
			)
			OR (	--ONLY RUN JOBS FOR NON-DEFAULT BACKUPSET FOR SCHEDULE POLICIES
				a.appTypeId = 107		-- CV_APPTYPE_WORKSTATION_BACKUP
				AND (tc.taskType & 4) = 4
				AND a.backupsetId IN (
					SELECT bs.id
					FROM APP_BackupSetName bs
					WHERE (status & 40) = 40
						AND bs.id = a.backupsetId
				)
			)
			OR (	--DO NOT RUN JOBS ON PSEUDO HIDDEN BACKUP SETS
				a.appTypeId IN (37, 62, 103)		-- CV_APPTYPE_DB2, CV_APPTYPE_UNIX_DB2, CV_APPTYPE_DB2_DPF
				AND a.backupsetId IN (
					SELECT bs.id
					FROM APP_BackupSetName bs
					WHERE (bs.status & 24) = 24
						AND bs.id = a.backupsetId
					)
			)
		--6. Filter the command line and command log line subclients for below iDA
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskID = a.stId
		WHERE (tc.taskType & 4) = 4 --TASK_POLICY
			AND (a.status & (64 | 4096)) > 0 -- CV_STATUS_CMD_LINE | CV_STATUS_LOGCMD_LINE
AND (tc.isSPAMAXDBBackupSupported = 0 OR a.appTypeId!= 79 )
AND a.appTypeId NOT IN (135, 3)
		--6.a Filter only command log line subclients when dump sweep is enabled
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskID = a.stId
		WHERE (tc.taskType & 4) = 4 --TASK_POLICY
			AND ((a.status & 64) > 0 -- CV_STATUS_CMD_LINE
			OR (
					a.status & 4096 > 0 --| CV_STATUS_LOGCMD_LINE
					AND tc.isDumpSweepEnabled = 0
				)
			)
AND a.appTypeId = 135
		--6.b Filter command line subclient in case of informix when dump sweep is enabled
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stID
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskID = a.stId
		WHERE (tc.taskType & 4) = 4 --TASK_POLICY
			AND (a.status & 64) > 0 -- CV_STATUS_CMD_LINE
			AND tc.isDumpSweepEnabled = 0
AND a.appTypeId = 3
		--7. No policy job for QR
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stID
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskID = a.stId
		WHERE (tc.taskType & 4) = 4
			AND tc.taskType <> 5	--TASK_POLICY, 5 MEANS workflow
			AND a.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
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskID = a.stId
		WHERE (tc.taskType & 4) = 4 --TASK_POLICY
			AND a.backupsetId IN (
				SELECT bsp.componentnameid
				FROM APP_BackupSetProp bsp
				WHERE bsp.modified = 0
					AND bsp.attrName = N'OnDemand Backupset'
					AND bsp.attrVal = N'1'
			)
		--9. PART1 If operation type is transaction log, skip all the other subclients. BKP_OP_TRAN_LOG = 5
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskID = a.stId
		WHERE
			a.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 (a.status & 128) <> 128	--CV_STATUS_TRAN_LOG			0x0080
			AND (
				tc.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 = tc.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 = tc.taskId --FILTER_AppGroup= 3
					)
				)
			)
		--12. Filter the default subclient - Will be used by SQL and FLR
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskID = a.stId
		WHERE (a.status & 8) = 8		-- CV_STATUS_DEFAULT
			AND EXISTS (
				SELECT filter_value
				FROM TM_AssocFilter
				WHERE filter_type = 3
					AND filter_value = 3
					AND taskId = tc.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
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN APP_Client c ON
				c.id = a.clientId
				AND  (c.status & 268435456) > 0		-- CV_STATUS_EDGE_DRIVE
				AND (c.status & 1073741824) = 0	-- CV_STATUS_SYSTEM_EDGE_DRIVE
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskId = a.stId
			INNER JOIN #TaskTableData td ON
				td.taskId = tc.taskId
				AND td.isEdgeDrivePolicy = 0
		WHERE
			tc.backupLevel <> 4		-- BACKUP_LEVEL_SYNTHETIC_FULL
		--DO NOT COUNT AGENTS THAT DO NOT SUPPORT SYNTHETIC FULL SCHEDULES
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stID
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskId = a.stId
		WHERE tc.autoCopy = 1
			AND a.bkpLevel <> 4	-- BACKUP_LEVEL_SYNTHETIC_FULL
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskId = a.stId
		WHERE  tc.autoCopy = 1
			AND a.appTypeId = 3	-- CV_APPTYPE_INFORMIX
        -- Filter default backupset for Edge drive clients.
		UNION
        SELECT
            a.appId,
			a.taskId,
			a.stId
        FROM #appIdTbl a
			INNER JOIN App_Client c ON
				c.id = a.clientId
			INNER JOIN APP_BackupSetName bs ON
				bs.id = a.backupsetId
		WHERE
			(c.status & 0x10000000) = 0x10000000
			AND (bs.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
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId
				AND tc.subtaskid = a.stId
			INNER JOIN #SCProps p1 ON
				p1.appId = a.appId
		WHERE
			tc.backupLevel = 4        -- BACKUP_LEVEL_SYNTHETIC_FULL
			AND p1.turboSubclient = 1
			AND p1.archRetention = 1
			AND p1.bkupRetention = 0
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN #TaskCfg tc ON
				tc.taskId = a.taskId AND
				tc.subtaskId = a.stId
			INNER JOIN #SCProps p1 ON
				p1.appId = a.appId
		WHERE
			tc.backupLevel = 4        -- BACKUP_LEVEL_SYNTHETIC_FULL
			AND p1.extendRetention = 1
			AND p1.archRetention = 1
			AND p1.bkupRetention = 0
		UNION
		--16. For non-Netapp Vendor Allow jobs ONLY on default backup set
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
			INNER JOIN MMNDMPHostInfo DMP ON
				DMP.ClientId = a.clientId
				AND a.appTypeId = 13
				AND DMP.OSType <> 2	--OS_TYPE_NETAPP
			INNER JOIN APP_BackupSetName BS ON
				BS.id = a.backupsetId
				AND (BS.status & 8) <> 8		-- CV_STATUS_DEFAULT
		WHERE
			@bMultiBackupsetAllowInc = 1
		-- 17 Filter the default subclient for below iDA which is non commandline subclient
		UNION
		SELECT
			a.appId,
			a.taskId,
			a.stId
		FROM #appIdTbl a
WHERE apptypeId = 79
			AND (STATUS & 8) = 8		-- CV_STATUS_DEFAULT
			AND (STATUS & 64) = 0	    -- CV_STATUS_CMD_LINE
	DELETE a
	FROM #appIdTbl a
		INNER JOIN #FilterApps fa ON
			fa.appId = a.appId
			AND fa.taskId = a.taskId
			AND fa.subtaskId = a.stId
	IF OBJECT_ID('tempdb.dbo.#FilterApps') IS NOT NULL
		DROP TABLE #FilterApps
	--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
	;WITH taskIdsList (taskId) AS
	(
		SELECT ae.taskId
		FROM TM_AssocEntity ae
			INNER JOIN #TaskCfg tc ON
				ae.taskId = tc.taskId
				AND (tc.taskType & 4) = 0
				AND ae.subclientId = 0
				AND (
					ae.clientGroupId <> 0
					OR ae.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 E.taskId
		FROM TM_AssocEntity E
			INNER JOIN TM_AssocFilter F ON
				F.taskId = E.taskId
			INNER JOIN #TaskCfg tc ON
				E.taskId = tc.taskId
				AND tc.taskType = 4
				AND F.filter_value <> 104
	)
	DELETE a
	FROM #appIdTbl a
	INNER JOIN taskIdsList ON a.taskId = taskIdsList.taskId
	WHERE a.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 = a.taskId --FILTER_AppGroup= 3
		)
		AND (a.status & 128) = 128	--CV_STATUS_TRAN_LOG			0x0080
	--10. Filter the FFG subclient MR 72616 - Backup from instance level for all s/c should exclude FFG s/c
	DELETE a
	FROM #appIdTbl a
		INNER JOIN #SCProps p ON
			p.appId = a.appId
			AND p.sqlSubClientType IN (2, 3)
	WHERE
		a.appTypeId = 81	--_CV_APPTYPE_MSSQL
		AND (
			EXISTS (
				SELECT filter_value
				FROM TM_AssocFilter
				WHERE filter_type = 3
					AND filter_value = 1
					AND taskId = a.taskId --FILTER_Subclient= 3 --FIL_SC_SQL_FFG = 2
			)
			OR NOT EXISTS (
				SELECT 1
				FROM TM_AssocEntity TA
				WHERE TA.taskId = a.taskId
					AND TA.subclientId = a.appId
			)
		)
	--11. Filter the DB subclient
	DELETE a
	FROM #appIdTbl a
		INNER JOIN #SCProps p ON
			p.appId = a.appId
			AND p.sqlSubClientType = 1
	WHERE appTypeId = 81	--_CV_APPTYPE_MSSQL
		AND EXISTS (
			SELECT filter_value
			FROM TM_AssocFilter
			WHERE filter_type = 3
				AND filter_value = 2
				AND taskId = a.taskId --FILTER_Subclient= 3 --FIL_SC_SQL_DB  = 3
		)
	--Filter if client is a VSA discovered V2 VM and backup level is not synthfull
	--Filter only if the instance is a discovered one
	DELETE a
	FROM #appIdTbl a
		INNER JOIN #TaskCfg tc ON tc.taskId = a.taskId
								AND tc.subTaskID = a.stId
								AND tc.backupLevel <> 4 --BACKUP_LEVEL_SYNTHETIC_FULL
								AND a.appTypeId = 106 --CV_APPTYPE_VIRTUAL_SERVER
		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
	-- 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 a
	FROM #appIdTbl a
	WHERE
		a.bkpLevel <> 1
		AND EXISTS (
			SELECT 1
			FROM APP_instanceprop
			WHERE
				componentNameId = a.instanceId
				AND attrName = N'MSSQL Server Type'
				AND modified = 0
				AND attrVal IN (		-- Differential is not supported for blocklevel subclients
					N'Analysis Services',
					N'Azure DataBase Engine',
					N'Aws DataBase Engine',
					N'Azure Managed DataBase Engine'
				)
		)
	--16. Do not run backups for subclients with no storage policy
	IF (@doNotStartBackupOnSCWithNoStoragePolicy = 1)
	BEGIN
		DELETE a
		FROM #appIdTbl a
			INNER JOIN archGroup ARG WITH (NOLOCK) ON
				a.storagePolicy = ARG.id
			INNER JOIN #TaskCfg tc ON
				a.taskId = tc.taskId AND
				a.stID = tc.subtaskID
			WHERE
				a.storagePolicy = 1
				AND ARG.defaultCopy = 0
AND (tc.isSPAMAXDBBackupSupported = 0 OR a.appTypeId!= 79 ) -- For SAP MAX DB default subclient doesnt have storage policy set.
	END
	--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 & 4096 > 0 --CV_STATUS_LOGCMD_LINE
AND A.appTypeId != 135
		-- fIsCmdSubClient AND NOT INFORMIX
		UNION ALL
		SELECT A.appId FROM #appIdTbl A
		WHERE A.STATUS & 64  > 0 --CV_STATUS_CMD_LINE
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 & 256 > 0 --CV_STATUS_SYSTEM_RECOVERY
	)TBD
	ON TBD.appId = appIdTbl.appId
	INNER JOIN #TaskTableData td ON td.taskId = appIdTbl.taskId AND td.isPlanSchedulePolicy = 1
	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
	-- fIsCmdSubClient AND INFORMIX
	SELECT A.appId FROM #appIdTbl A
	INNER JOIN #TaskTableData td ON td.taskId = A.taskId AND td.isPlanSchedulePolicy = 1
	WHERE (
(A.STATUS & 4096 > 0 AND A.appTypeId = 135) --CV_STATUS_LOGCMD_LINE
			OR
(A.STATUS & 64  > 0 AND A.appTypeId = 3) --CV_STATUS_CMD_LINE
		  )
	--Plan based association filter for data schedule policy
	DELETE appIdTbl
	FROM #appIdtbl appIdTbl
	INNER JOIN
	(
		SELECT appId FROM #planAppIDsToDel
		--fIsLogSubClient
		UNION ALL
		SELECT A.appId FROM #appIdTbl A
		WHERE A.STATUS & 262144 > 0 --CV_STATUS_LOGONLY_SC
	)TBD
	ON TBD.appId = appIdTbl.appId
	INNER JOIN #TaskTableData td ON td.taskId = appIdTbl.taskId AND td.isPlanSchedulePolicy = 1 AND td.isLogSchedulePolicy = 0
	--Plan based association filter for Log schedule Policy
	DELETE appIdTbl
	FROM #appIdtbl appIdTbl
	INNER JOIN #planAppIDsToDel PDEL ON PDEL.appId = appIdTbl.appId
	INNER JOIN #TaskTableData td ON td.taskId = appIdTbl.taskId AND td.isPlanSchedulePolicy = 1 AND td.isLogSchedulePolicy = 1
	INNER JOIN #TaskCfg tc ON tc.taskId = td.taskId AND tc.subtaskId = appIdTbl.stId AND tc.isDumpSweepEnabled = 0
	--bIsOracleRegularSubClient
	DELETE A
	FROM #appIdtbl A
	INNER JOIN #TaskTableData td ON td.taskId = A.taskId AND td.isPlanSchedulePolicy = 1 AND td.isLogSchedulePolicy = 1
	WHERE A.STATUS & 262144 = 0 --CV_STATUS_LOGONLY_SC
AND A.appTypeId IN (22,80,61)
	IF OBJECT_ID('tempdb.dbo.#planAppIDsToDel') IS NOT NULL DROP TABLE #planAppIDsToDel
	-- Filter NFS object store subclient from plan data schedule policy
	DELETE a
	FROM #appIdTbl a
	INNER JOIN #TaskTableData td ON
	td.taskId = a.taskId AND td.isPlanSchedulePolicy = 1
	INNER JOIN App_ClientProp CP (nolock) ON CP.componentNameId = a.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'
	--For Activate FSO do not include in any count for schedules/policies
	DELETE a
	FROM #appIdTbl a
	INNER JOIN #TaskCfg tc ON
		tc.taskId = a.taskId AND
		tc.subtaskId = a.stId
	INNER JOIN #SCProps p1 ON
		p1.appId = a.appId
		AND p1.isOneTimeArchiving = 1
	-- 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
	SELECT TOP 1
		@drSubclientId = id
	FROM APP_Application
	WHERE appTypeId = 1000	--CV_APPTYPE_COMMSRVMGMT
	SELECT TOP 1
		@defaultInstanceId = id
	FROM APP_InstanceName WHERE name = 'DefaultInstanceName'
	DECLARE @doNotStartBkpsOnDisabledAgents INT = 1
	SELECT @doNotStartBkpsOnDisabledAgents = value FROM GXGlobalParam (NOLOCK) WHERE name = N'JMDontStartBkpsOnDisabledAgents' AND modified = 0
	-- If queue jobs option isnt selected or its continous schedule, delete entities whose activity isnt enabled.
	IF (
		@runTaskOptionIgnoreActivityCheck = 0 --If run task option has option to ignore activity control STARTUPOPTIONS_FORCE_RUN_ON_DISABLED_ACTIVITY then dont do below
		AND EXISTS (
			SELECT TOP 1 1
			FROM #TaskTableData td
			INNER JOIN #TaskCfg tc ON
			td.taskId = tc.taskId
			WHERE
				td.freqInterval > 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.
				OR tc.autoCopy=1
				OR (
					-- TaskType:: schedule,TaskType:schedulepolicy
					@queueJobsIfActivityDisabled = 0
					AND td.taskType in (2, 4) AND @doNotStartBkpsOnDisabledAgents <> 0
				)
		)
	)
	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 IN (1, 4)
				AND action = 1
		)
		BEGIN
			-- Activity at CS level is disabled. So dont start any jobs.
			DELETE a
			FROM #appIdTbl a
				INNER JOIN #TaskTableData td ON
					td.taskId = a.taskId
					AND td.subtaskID = a.stId
				INNER JOIN #TaskCfg tc ON
					td.taskId = tc.taskId
					AND tc.subtaskID = a.stId
			WHERE
				td.freqInterval > 0
				OR tc.autoCopy=1
				OR (
					-- TaskType:: schedule,TaskType:schedulepolicy
					@queueJobsIfActivityDisabled = 0
					AND td.taskType in (2, 4)
					AND @doNotStartBkpsOnDisabledAgents <> 0
				)
		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.
			IF OBJECT_ID('tempdb.dbo.#JobActAppIds') IS NOT NULL
				DROP TABLE #JobActAppIds
			CREATE TABLE #JobActAppIds (
				appId	INT,
				taskId	INT,
				PRIMARY KEY (appId, taskId)
			)
			INSERT INTO #JobActAppIds
				SELECT
					a.appId,
					a.taskId
				FROM #appIdTbl a
				INNER JOIN JMJobAction J with (nolock) ON
					J.opType = 4
					AND J.action = 1
					AND (
						(
							J.clientId = a.ClientId
							AND J.appType IN (a.AppTypeId, 0)
						)
					)
					AND J.mediaAgentID = 1
					AND J.instanceId = @defaultInstanceId
					AND J.appId = @drSubclientId
				UNION
				SELECT
					a.appId,
					a.taskId
				FROM #appIdTbl a
				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 = a.appId
					AND a.appId > 0
					AND J.instanceId = @defaultInstanceId
				UNION
				SELECT
					-- If activity is disabled at ida level (without client), then set it for subclient.
					a.appId,
					a.taskId
				FROM #appIdTbl a
				INNER JOIN JMJobAction J with (nolock) ON
					J.clientId = 1
					AND J.appType = a.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
				UNION
				SELECT
					-- If activity is disabled at instance level, then set it for subclient.
					a.appId,
					a.taskId
				FROM #appIdTbl a
				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 = a.instanceId
					AND J.mediaAgentID = 1
					AND J.appId = @drSubclientId
					AND J.clientGroupId = 0
					AND a.instanceId <> 1
				UNION
				SELECT
					-- Check at client group level. And disable activity for all clients of disabled client groups
					a.appId,
					a.taskId
				FROM #appIdTbl a
					INNER JOIN App_clientGroupAssoc CGA with (nolock) ON
						CGA.clientId = a.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
				UNION
					SELECT
						-- If subclient is associated to subclient policy and activity control is disabled on subclient policy sc, then dont start backup
						a.appId,
						a.taskId
					FROM #appIdTbl a
					INNER JOIN APP_SubClientProp (NOLOCK) ON APP_SubClientProp.ComponentNameId = a.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
			--== Now see if the meet the criteria to be deleted
			DELETE a
			FROM #appIdTbl a
				INNER JOIN #TaskTableData td ON
					td.taskId = a.taskId AND
					td.subtaskID = a.stId
				INNER JOIN #JobActAppIds ja ON
					ja.appId = a.appId
					AND ja.taskId = a.taskId
				INNER JOIN #TaskCfg tc ON
					td.taskId = tc.taskId AND
					tc.subtaskID = a.stId
			WHERE
				td.freqInterval > 0
				OR tc.autoCopy=1
				OR (
					-- TaskType:: schedule,TaskType:schedulepolicy
					@queueJobsIfActivityDisabled = 0
					AND td.taskType in (2, 4)
					AND @doNotStartBkpsOnDisabledAgents <> 0
				)
			IF OBJECT_ID('tempdb.dbo.#JobActAppIds') IS NOT NULL
				DROP TABLE #JobActAppIds
		END
	END
	--For synthetic full jobs get all the VM clients created from VSA from discovery
	IF OBJECT_ID('tempdb.dbo.#App_VmClientInfoTable') IS NOT NULL
		DROP TABLE #App_VmClientInfoTable
	CREATE TABLE #App_VmClientInfoTable (
		vmClientId        INT,
		parentBackupsetId INT,
		childBackupsetId  INT,
		taskID			  INT -- Need to add taskId since we need to do this for schedules/policies where backup level is synthetic full
	)
	CREATE CLUSTERED INDEX App_VmClientInfoTable_vmClientId_Index1 ON #App_VmClientInfoTable (vmClientId)
	INSERT INTO #App_VmClientInfoTable (vmClientId, parentBackupsetId, childBackupsetId, taskId)
		SELECT DISTINCT
			VB.VMClientId,
			VB.ParentBackupSetId,
			VB.ChildBackupSetId	,
			A.taskId
		FROM #appIdTbl A
			INNER JOIN APP_VMBackupSet VB WITH(NOLOCK)  ON
				VB.ParentBackupSetId = A.backupsetId
				AND A.appTypeId = 106
				AND A.bkpLevel = 4
			INNER 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
	IF @rowCount > 0	--EXISTS (SELECT 1 FROM #App_VmClientInfoTable)
	BEGIN
		--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,
			taskId
		)
			SELECT DISTINCT
				APP.id,
				APP.appTypeId,
				APP.clientId,
				APP.instance,
				APP.backupSet,
				APP.subclientStatus,
				A.bkpLevel,
				APP.logArchGrpID,
				VB.taskId
			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.TaskID = VB.taskId
					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
		--NOW WE NEED TO RECHECK ACTIVITY FOR THE VM CLIENTS
		IF (
			@rowCount > 0
			AND @queueJobsIfActivityDisabled=0
			AND EXISTS (
				SELECT TOP 1 1
				FROM #TaskTableData td
				INNER JOIN #TaskCfg tc ON
				td.taskId = tc.taskId
				WHERE
					td.taskType in (2,4) -- TaskType:: schedule,TaskType:schedulepolicy
					-- 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.
					AND (@doNotStartBkpsOnDisabledAgents <> 0 OR tc.autoCopy=1)
			)
		)
		BEGIN
			IF OBJECT_ID('tempdb.dbo.#JobActAppIds2') IS NOT NULL
				DROP TABLE #JobActAppIds2
			CREATE TABLE #JobActAppIds2 (
				appId	INT,
				taskId	INT,
				PRIMARY KEY (appId, taskId)
			)
			INSERT INTO #JobActAppIds2 (appId, taskId)
				-- If activity is disabled at client level or client/ida level or at subclient level, then set it for subclient.
				SELECT
					a.appId,
					a.taskId
				FROM #appIdTbl a
					INNER JOIN JMJobAction J WITH(NOLOCK) ON
						J.opType = 4
						AND J.ACTION = 1
						AND J.clientId = a.ClientId
						AND J.appType IN (a.AppTypeId, 0)
						AND J.mediaAgentID = 1
				UNION
				SELECT
					a.appId,
					a.taskId
				FROM #appIdTbl a
					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 = a.appId
						AND a.appId > 0
				-- If activity is disabled at ida level (without client), then set it for subclient.
				UNION
				SELECT
					a.appId,
					a.taskId
				FROM #appIdTbl a
					INNER JOIN JMJobAction J WITH(NOLOCK) ON
						J.clientId = 1
						AND J.appType = a.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
					a.appId,
					a.taskId
				FROM #appIdTbl a
					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 = a.instanceId
						AND J.mediaAgentID = 1
						AND J.appId = @drSubclientId
						AND J.clientGroupId = 0
						AND a.instanceId <> 1
				-- Check at client group level. And disable activity for all clients of disabled client groups
				UNION
				SELECT
					a.appId,
					a.taskId
				FROM #appIdTbl a
					INNER JOIN App_clientGroupAssoc CGA WITH(NOLOCK) ON
						CGA.clientId = a.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
			--== Now see if meets the criteria to be deleted
			DELETE a
			FROM #appIdTbl a
				INNER JOIN #TaskTableData td ON
					td.taskId = a.taskId
					AND td.subtaskID = a.stId
				INNER JOIN #JobActAppIds2 ja ON
					ja.appId = a.appId
					AND ja.taskId = a.taskId
				INNER JOIN #TaskCfg tc ON
					td.taskId = tc.taskId
					AND tc.subtaskID = a.stId
			WHERE
				td.taskType in (2, 4)
				AND (@doNotStartBkpsOnDisabledAgents <> 0 OR tc.autoCopy=1)
		END
		--17. Remove parent VSA backupset for synthetic full jobs
		-- MR:245677 - Remove parent VSA subclients for V2 synthetic full always
		-- Even if there is no corresponding child VM subclient.
		DELETE a
		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
		-- For Office 365 subclients filter out all subclients if the request is for FULL or Synthfull
		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
WHERE a.bkpLevel IN ( 4, 1)
	END
END
--- CODE ABOVE converted to Data Set Op from TM_GetBackupEntityCntFilterDataSet.spb  ----------
	UPDATE et
		SET entityCount = ct.cnt
	FROM #TM_GetBackupEntityCountDataSet_taskTble et
		INNER JOIN (
			SELECT
				td.taskId,
				td.subTaskId,
				COUNT(DISTINCT a.appid) cnt
			FROM #TaskTableData td
				INNER JOIN #appIdTbl a ON
					a.taskId = td.taskId
			GROUP BY
				td.taskId,
				td.subTaskId
		) ct ON
			ct.taskId = et.taskId
			AND ct.subTaskId = et.subTaskId
	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)
		RETURN 1
	END CATCH;
	IF OBJECT_ID('tempdb.dbo.#TaskCfg') is not null
		DROP TABLE #TaskCfg
	IF OBJECT_ID('tempdb.dbo.#TaskTableData') is not null
			DROP TABLE #TaskTableData
	IF OBJECT_ID('tempdb.dbo.#SubTaskOptions') is not null
			DROP TABLE #SubTaskOptions
	RETURN 0
END
GO

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

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

insert into GXDBVersions values(2, 'TM_GetBackupEntityCountDataSet',  'v1.1.2.16.4.5', 'TM_GetBackupEntityCountDataSet', 'v1.1.2.16.4.5')
GO

