

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/APPGetSubclientCommonProperties.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.
--
-- ----------------------------------------------------------------------*/
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='APPGetSubclientCommonProperties')
	delete from GXDBVersions where aliasname = 'APPGetSubclientCommonProperties'
GO
print '... Creating Procedure: APPGetSubclientCommonProperties'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure APPGetSubclientCommonProperties
  @subclientId INT,
  @propertyLevel INT,
  @userId INT,
  @xmlResult XML OUTPUT, 
  @mode INTEGER,
  @localeId INTEGER,
  @includeContentInfo INT = 0,
  @withBackup INT = 0
AS
	DECLARE		@backupsetId					INT
	DECLARE		@instanceId						INT
	DECLARE		@applicationId					INT
	DECLARE		@clientId						INT
	DECLARE		@userDescription				NVARCHAR(MAX)
	DECLARE		@encryptionFlag					NVARCHAR(128)
	DECLARE		@preScanCommand					NVARCHAR(MAX)
	DECLARE		@postScanCommand				NVARCHAR(MAX)
	DECLARE		@preBackupCommand				NVARCHAR(MAX)
	DECLARE		@postBackupCommand				NVARCHAR(MAX)
	DECLARE		@preSNAPCommand					NVARCHAR(MAX)
	DECLARE		@postSNAPCommand				NVARCHAR(MAX)
	DECLARE		@preLogCommand					NVARCHAR(MAX)
	DECLARE		@postLogCommand					NVARCHAR(MAX)
	DECLARE		@runPostBackup					INT
	DECLARE		@runPostScan					INT
	DECLARE		@snapProcessOn					INT
	DECLARE		@preSnapProcessOn				INT
	DECLARE		@postSnapProcessOn				INT
	DECLARE		@runPostLog						INT
	DECLARE		@prepostSelectionLevel			NVARCHAR(510)
	DECLARE		@prepostUserName				NVARCHAR(510)
	DECLARE		@useSeparateProxyForSnapToTape	INT
	DECLARE		@separateProxyForSnapToTape		INT
	DECLARE		@separateProxyForSnapToTapeName	NVARCHAR(510)
	DECLARE		@proxyESXServer					NVARCHAR(510)
	DECLARE		@proxyESXHost					NVARCHAR(510)
	DECLARE		@backupForGranularRecovery		INT
	DECLARE		@truncateExDBLogs				INT
	DECLARE		@transportModeForVMWare			INT
	DECLARE		@useProxy						INT
	DECLARE		@useProxyClientName				NVARCHAR(510)
	DECLARE		@dataBackupStoragePolicy		INT
	DECLARE		@commandLineStoragePolicy		INT
	DECLARE		@logBackupStoragePolicy			INT
	DECLARE		@softwareCompression			INT
	DECLARE		@networkAgents					INT
	DECLARE		@throttleNetworkBandwidth		INT
	DECLARE		@isRMANEnableForTapeMovement	NVARCHAR(128)
	DECLARE		@backupCopyInterface			NVARCHAR(16)
	DECLARE		@IsOracleSposDriverEnabled		NVARCHAR(16)
	DECLARE		@snapToTapeProxyToUseSource		NVARCHAR(510)
	DECLARE		@snapToTapeProxyToUse			INT
	DECLARE		@snapToTapeProxyToUseClientName	NVARCHAR(510)
	DECLARE		@SnapShotEngineId				INT
	DECLARE		@SnapShotEngineName				NVARCHAR(256)
	DECLARE		@isSnapbackupEnabled			INT
	DECLARE		@replicationOptionFlag			INT
	DECLARE		@associatedSnapShotSCId			INT
	DECLARE		@associatedSnapShotSCName		NVARCHAR(256)
	DECLARE		@softwareCompressionDestination INT
	DECLARE		@readBuffersize					INT
	DECLARE		@isKeepItemPastExpiryTime		INT
	DECLARE		@expandSymbolicLinks			INT
	DECLARE		@enableAppleDoubleSupport		INT
	DECLARE		@onlyBackupQualifiedArchivingFiles INT
	DECLARE		@subclientStatus				INT
	DECLARE		@isDefaultSubclient				INT
	DECLARE		@isSubclientDeleted				INT
	DECLARE		@isSubclientUninstalled			INT
	DECLARE		@isSubclientHidden				INT
	DECLARE		@numberOfBackupStreams			INT
	DECLARE		@maxStreamsForSnapBackup		INT
	DECLARE		@allowMultipleDataReaders		INT
	DECLARE		@allowVssForSnapbackup			INT
	DECLARE		@enableBackup					INT
	DECLARE		@enableBackupAfterDelay			INT = 0
	DECLARE		@enableTZID						INT = 0
	DECLARE		@enableTZIDName					VARCHAR(255)
	DECLARE		@enableTime						INT = 0
	DECLARE		@strEnableTime					VARCHAR(64)
	DECLARE		@enableDeduplication			INT
	DECLARE		@generateSignature				INT
	DECLARE		@onDemand						INT
	DECLARE		@overrideDataPath				NVARCHAR(128)
	DECLARE		@overrideIncrementalDataPath	NVARCHAR(128)
	DECLARE		@isDecoupleSCPContent			INT
	DECLARE		@isReferenceCopySubclient		INT
	DECLARE		@isTurboSubclient				INT
	DECLARE		@isSystemStateSubclient			INT
	DECLARE		@isEdgeDriveSubclient			INT
	DECLARE		@indexCycleToEveryBackupJobs	INT
	DECLARE		@turboNASClient					INT
	DECLARE		@turboNASClientName				NVARCHAR(510)
	DECLARE		@lastBackupTime					INT
	DECLARE		@firstBackupTime				INT
	DECLARE		@nextBackupTime					INT
	DECLARE		@lastBackupSize					BIGINT
	DECLARE		@totalBackupSize				BIGINT
	DECLARE		@lastBackupType					INT
	DECLARE		@lastFULLUnCompBytes			BIGINT
    DECLARE		@lastDiffUnCompBytes			BIGINT
	DECLARE		@isIndexingBackupSubclient		INT
	DECLARE		@isAdAppAwareSubclient			INT
	DECLARE		@excludeFromSLA					INT
	DECLARE		@ignoreStrikesBefore			INT
	DECLARE		@associatedPolicyId				INT = 0
   DECLARE		@isActivityDisabledFromPolicy		INT = 0
	DECLARE		@useDumpBasedBackupCopy			INT
	DECLARE		@useConfiguredInstance			INT
	DECLARE		@createAuxSybaseServer			INT
	DECLARE		@dumpBasedBackupCopyOption		INT
	DECLARE		@configuredSybaseInstance		NVARCHAR(1024)
	DECLARE		@sybaseHome						NVARCHAR(MAX)
	DECLARE		@sybaseASE						NVARCHAR(MAX)
	DECLARE		@sybaseOCS						NVARCHAR(MAX)
	DECLARE		@sybaseUser						NVARCHAR(510)
	DECLARE		@dataBackupStoragePolicyID		INT
	DECLARE		@dataBackupStoragePolicyName	NVARCHAR(288)
	DECLARE		@logBackupStoragePolicyID		INT
	DECLARE		@logBackupStoragePolicyName		NVARCHAR(288)
	DECLARE		@subclientPropertiesExFlag		BIGINT = 0
	DECLARE		@refCopyFlag					INT = 0
	DECLARE		@refFirstBackupDone				INT = 0
	DECLARE		@isTrueUpEnabled				INT = 0
	DECLARE		@runTrueUpAfterDays				INT = 0
	DECLARE		@dataPaths					TABLE( datapathId					INT,
													library						NVARCHAR(256),
													mediaAgent					NVARCHAR(510),
													drivePoolName				NVARCHAR(128),
													scratchPoolName				NVARCHAR(384),
													priority					INT,
													dataPathOfIncSP				INT)
	DECLARE		@subclientSecurity XML
	DECLARE		@subclientSecuritynew XML
	DECLARE     @dbDumpConfig XML
	DECLARE     @timezone XML
	DECLARE     @attrTimezoneId nvarchar(30) = N'timezone Id'
	--Performance Tab Options
	--Client params
	DECLARE		@perfClientName 					NVARCHAR(MAX)
DECLARE		@lookAheadReaderSlots				INT = 4
DECLARE		@numOfPipelineBuffers				INT = 90
DECLARE		@pipelineBufferSize			 		INT = ((64*1024)/1024)
	--copy dependent params
	DECLARE 	@copyId								INT = 0
	DECLARE		@multiplexingValid					NVARCHAR(MAX)
	DECLARE		@enableMultiplexing					NVARCHAR(MAX)
	DECLARE		@muxFactor							INT = 0
	DECLARE		@streamsOverMux						NVARCHAR(MAX)
	DECLARE		@perfSPName							NVARCHAR(MAX)
	DECLARE		@perfCopyName 						NVARCHAR(MAX)
	DECLARE 	@perfMaList                			TABLE
													(perfMaId INT,
													 perfMa NVARCHAR(MAX),
													 crcOverNetwork NVARCHAR(MAX),
													 crcOverMedia NVARCHAR(MAX))
	DECLARE @impersonate INT
	DECLARE @quiesceUserName NVARCHAR(MAX)
	DECLARE @useSSHKey INT
	DECLARE @vmApplUserName NVARCHAR(MAX)
	DECLARE @vmSSHKeyFilePath NVARCHAR(MAX)
	DECLARE @appAware INT
	DECLARE	@appValidation INT
	DECLARE @vmAllocPolicyID INT
	DECLARE @useSourceESXMount	INT
	DECLARE @backupValidationTaskId INT
	DECLARE @backupValidationSubTaskId INT
	DECLARE @isCustomAppSet INT
	DECLARE @customAppXML XML
	DECLARE @runAsDevTestGroup INT
	DECLARE	@backupValidationThreadCount INT =0
	DECLARE @guestCredentialsID	INT =0
	DECLARE @guestCredentialsName NVARCHAR(MAX)
	DECLARE @uncCredentialsID	INT =0
	DECLARE @uncCredentialsName NVARCHAR(MAX)
	DECLARE @uncScriptCredentialUsername NVARCHAR(MAX)
	DECLARE	@parentSubclientId	INT = 0
	DECLARE		@db2UseACSInterface		INT = 0
	DECLARE @slaStatus	INT = 3 --Excluded by default. For new subclients we may not have an entry in RptSlaSubclient table.
	DECLARE @slaCategory INT = 0
	DECLARE @slaCategoryDescription	NVARCHAR(255)
	DECLARE @lastBackupJobInfo XML
	--- SQL Snap Properties
	DECLARE @checkProxyForSQLIntegrity INT
	DECLARE @sqlIntegrityServerName NVARCHAR(MAX)
	DECLARE @useSqlCredentials INT
	DECLARE @useProxyForSQLIntegrity INT
	DECLARE @useProxyForSQLIntegrityName NVARCHAR(510)
	DECLARE @sqlUserName NVARCHAR(MAX)
	DECLARE	@onBoardCompanyId INT
	--Credential Manager Properties
	DECLARE		@uncPathCredId	INT = 0
	DECLARE		@prePostCredId  INT = 0
	DECLARE		@prePostCredSelectionLevel	INT = 0
	DECLARE		@uncPathCredName NVARCHAR(MAX) = ''
	DECLARE		@prePostCredname NVARCHAR(MAX) = ''
	DECLARE		@scpIdaType		INT = 0
	DECLARE		@planId INT = 0
	DECLARE		@jobStartTime INT = -1
	DECLARE		@isV2VSAClient	INT = 0
	DECLARE		@disableArchivingTurboRule INT = 0
	-- Index server options
	CREATE TABLE #idxOpts(entityId INT, curIdxServer XML, indexMAs XML, indexBkpSp XML, indexBkpEligibleSPs XML, indexPruning INT DEFAULT 0, retDays	INT DEFAULT -1, retCycles INT DEFAULT -1, pruningType INT DEFAULT 0)
--Gather SubClient common properties------------------------------------------------------------------------
	SET @backupsetId = (SELECT backupSet FROM APP_Application WITH (NOLOCK) WHERE APP_Application.id = @subclientId)
	SET @instanceId = (SELECT instance FROM APP_Application WITH (NOLOCK) WHERE APP_Application.id = @subclientId)
	SET @applicationId = (SELECT appTypeId FROM APP_Application WITH (NOLOCK) WHERE APP_Application.id = @subclientId)
	SET @clientId = (SELECT clientId FROM APP_Application WITH (NOLOCK) WHERE APP_Application.id = @subclientId)
	DECLARE @discoveredVMInstanceId INT=0
	SET @discoveredVMInstanceId = ISNULL((select componentNameId from APP_InstanceProp INNER JOIN App_InstanceName ON App_instancename.id=componentNameId and attrName = 'Discovered VM Instance' and APP_InstanceProp.modified=0 and status=0x200000), 0)
IF @applicationId = 106
	BEGIN
		SET @vmAllocPolicyID = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = N'Validation Policy Id' AND modified = 0), 0)
		IF @instanceId = @discoveredVMInstanceId
			SET @parentSubclientId = ISNULL((select top 1 CAST(attrVal AS INT) from APP_ClientProp CP WITH (NOLOCK) WHERE CP.componentNameId = @clientId and CP.attrName='VSA Discover Subclient ID' and modified=0 order by created desc),0)
		SET @isV2VSAClient = ISNULL((SELECT 1 FROM APP_ClientProp WHERE componentNameId=@clientId AND attrName = 'IndexingV2_VSA' AND attrval='1' AND modified = 0), 0)
	END
SET @associatedPolicyId = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = N'Associated subclient Policy' AND modified = 0), 0)
IF(@associatedPolicyId = 0 and @applicationId = 1030)
	BEGIN
		SET @associatedPolicyId = @backupsetId
SET @scpIdaType = ISNULL((SELECT attrVal from APP_BackupSetProp WITH (NOLOCK) where componentNameId = @backupsetId and attrName = N'Associated subclient Policy Ida Type' AND modified = 0 ),0)
	END
	IF @associatedPolicyId <> 0
	BEGIN
		SET @isActivityDisabledFromPolicy =
			CASE ISNULL((SELECT action FROM JMJobAction WITH (NOLOCK) WHERE clientId = 1 AND mediaAgentID = 1 AND appType = 0 AND optype = 4 AND appId = @associatedPolicyId), 0)
				WHEN 0 THEN 0
				WHEN 2 then 0
				WHEN 1 then 1
			END
	END
	SET @planId = ISNULL((SELECT CAST(attrVal AS INT) FROM APP_SubClientProp WITH(NOLOCK)
WHERE componentNameId = @subclientId AND attrName = 'Associated Plan' AND
cs_attrName = checksum(N'Associated Plan') AND modified = 0),0)
	-- The following info is used for "Instant Clone option on CC"
	IF (@propertyLevel = 5 AND @includeContentInfo = 1) OR @propertyLevel >= 20
	BEGIN
		IF @associatedPolicyId > 0
		BEGIN
			SET @isSnapbackupEnabled = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'Enable Snap Backups' AND modified = 0)
			SET @snapToTapeProxyToUse = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'Snap copy : proxy to use' AND modified = 0)
			SET @SnapShotEngineId = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'Snap Engine' AND modified = 0)
			IF @SnapShotEngineId IS NOT NULL
				SET @SnapShotEngineName = (SELECT SnapEngineName FROM SMSnapShotEngine WITH (NOLOCK) WHERE SnapShotEngineId = @SnapShotEngineId)
		END
		ELSE
		BEGIN
			DECLARE @tmpSubclientId INT
			SET @tmpSubclientId = @subclientId
			IF @parentSubclientId <> 0
			BEGIN
				SET @tmpSubclientId = @parentSubclientId
			END
			SET @isSnapbackupEnabled = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @tmpSubclientId AND attrName = 'Enable Snap Backups' AND modified = 0)
			SET @snapToTapeProxyToUse = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @tmpSubclientId AND attrName = 'Snap copy : proxy to use' AND modified = 0)
			SET @SnapShotEngineId = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @tmpSubclientId AND attrName = 'Snap Engine' AND modified = 0)
			IF @SnapShotEngineId IS NOT NULL
				SET @SnapShotEngineName = (SELECT SnapEngineName FROM SMSnapShotEngine WITH (NOLOCK) WHERE SnapShotEngineId = @SnapShotEngineId)
		END
	END
	IF @propertyLevel>=5
	BEGIN
		IF EXISTS(SELECT * FROM APP_Application WITH (NOLOCK) WHERE id = @subclientId AND  (subclientStatus & 0x8) = 0x8)
			SET @isDefaultSubclient = 1
		SET @onDemand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'On Demand Subclient' AND modified = 0)
		SET @lastBackupTime = ISNULL((SELECT ABP.attrval FROM APP_SubclientProp ABP WITH(NOLOCK)
										WHERE ABP.componentNameId = @subclientId
										AND ABP.attrName = 'Last Data Protected Time' AND ABP.modified=0 AND cs_attrName = CHECKSUM(N'Last Data Protected Time')), 0)
		-- RETURN ONLY THE SUBCLIENTS WITH VALID BACKUP IF FLAG withBackup IS SET
		IF @withBackup = 1 AND @lastBackupTime = 0
		RETURN
		SET @firstBackupTime = ISNULL((select min(servStartDate) from JMBkpStats WITH (NOLOCK)
										where status in (1, 3, 14) AND appId = @subclientId
                                        AND commCellId = (select origCCId from APP_Client WITH (NOLOCK) where id = @clientId)), 0)
		SET @lastBackupSize = ISNULL((select totalUncompBytes from JMBkpStats WITH (NOLOCK) where commCellId = (select origCCId from APP_Client WITH (NOLOCK) where id = @clientId)
                                            and jobId = (select max(jobid) from JMBkpStats WITH (NOLOCK) where commCellId = (select origCCId from APP_Client WITH (NOLOCK) where id = @clientId)
                                                            and appId = @subclientId AND status in (1, 3, 14)  AND ((datastatus = 0) OR (@isV2VSAClient=1))) ), 0)
		SET @lastBackupJobInfo = (SELECT ABP.attrval FROM APP_SubclientProp ABP WITH(NOLOCK)
										WHERE ABP.componentNameId = @subclientId
										AND ABP.attrName = 'Last Backup Job Info' AND ABP.modified=0 AND cs_attrName = CHECKSUM(N'Last Backup Job Info'))
		--Get Storage devices
		SELECT @dataBackupStoragePolicyID = ag.id, @dataBackupStoragePolicyName = ag.name
		FROM APP_Application subclient WITH (NOLOCK)
		INNER JOIN archgroup ag WITH(NOLOCK) ON subclient.dataArchGrpID = ag.id
		AND subclient.id = @subclientId
		IF @dataBackupStoragePolicyID = 1
		BEGIN
			SET @dataBackupStoragePolicyID = NULL
			SET @dataBackupStoragePolicyName = NULL
		END
IF (@applicationId = 81 OR
@applicationId = 22 OR
@applicationId = 80 OR
@applicationId = 37 OR
@applicationId = 103 OR
@applicationId = 104 OR
@applicationId = 125 OR
@applicationId = 5 OR
@applicationId = 126 OR
@applicationId = 135 OR
@applicationId = 61 OR
@applicationId = 79 OR
@applicationId = 61 OR
@applicationId = 3 OR
@applicationId = 128)
		BEGIN
			SELECT @logBackupStoragePolicyID = ag.id, @logBackupStoragePolicyName = ag.name
			FROM APP_Application subclient WITH (NOLOCK)
			INNER JOIN archgroup ag WITH(NOLOCK) ON subclient.logArchGrpID = ag.id
			AND subclient.id = @subclientId
			IF @logBackupStoragePolicyID = 1
			BEGIN
				SET @logBackupStoragePolicyID = NULL
				SET @logBackupStoragePolicyName = NULL
			END
		END
		SET @associatedSnapShotSCId = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Associated SnapShot Subclient' AND modified = 0)
		SET @isSystemStateSubclient = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'System State Subclient'  AND modified = 0), NULL)
		SET @isIndexingBackupSubclient = CASE WHEN EXISTS(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subClientId AND APP_SubclientProp.attrName = 'Index SubClient' AND attrVal = '1' AND modified = 0) THEN 1 ELSE 0 END
SET @isSubclientUninstalled = ISNULL((SELECT 1 FROM APP_Application WITH(NOLOCK) WHERE id = @subclientId AND (subclientStatus & 0x00002) = 0x00002), NULL)
		SET @userDescription = (SELECT attrVal FROM APP_SubclientProp asp WITH (NOLOCK) WHERE asp.componentNameId = @subclientId AND asp.attrName = N'User Description' AND asp.cs_attrName = CHECKSUM(N'User Description') AND modified = 0)
		IF @isActivityDisabledFromPolicy = 0
		BEGIN
			SET @enableBackup = ISNULL((SELECT action FROM JMJobAction WITH (NOLOCK) WHERE clientId = 1 AND mediaAgentID = 1 AND appType = 0 AND optype = 4 AND appId = @subclientId),0)
			SET @enableBackup = CASE @enableBackup WHEN 1 THEN 0 ELSE 1 END
		END
		ELSE
		BEGIN
			SET @enableBackup = ISNULL((SELECT action FROM JMJobAction WITH (NOLOCK) WHERE clientId = 1 AND mediaAgentID = 1 AND appType = 0 AND optype = 4 AND appId = @associatedPolicyId),0)
			SET @enableBackup = CASE @enableBackup WHEN 1 THEN 0 ELSE 1 END
		END
		--Update SLA status
		-- status 1 - Met; 2 - Missed; 3 - Excluded;
		SELECT  @slaStatus = (CASE WHEN S.status = 1 THEN 1 WHEN S.status = 2 THEN 2 ELSE 3 END),
				@slaCategory = S.Category,
				@slaCategoryDescription	= dbo.WR_GetSLACategoryString(@localeId,S.Category)
		FROM RptSLASubclient S WHERE  S.clientId = @clientId AND S.appId = @subclientId AND S.fullJobsOnly = 0
		SELECT
				@slaStatus = 5,/*MIGHT_MISS_SLA*/
				@slaCategoryDescription = cast(stringVal as xml).value('(/App_MightMissSLAList[reasonList/entity/@subclientId=sql:variable("@subclientId")]/reasonList/@mightMissReason)[1]','varchar(255)')
FROM APP_ComponentProp WHERE componentId = @clientId AND componentType =  3 AND propertyTypeId = 3625 AND modified = 0
		AND CAST(stringVal as xml).value('(/App_MightMissSLAList[reasonList/entity/@subclientId=sql:variable("@subclientId")]/reasonList/@mightMissReason)[1]','varchar(255)') IS NOT NULL
	END
	IF @propertyLevel >= 10 OR @includeContentInfo = 1
	BEGIN
			--Activity Control Tab
		IF @isActivityDisabledFromPolicy = 0
		BEGIN
			SELECT @enableTZID = enableTimeZone, @enableTime = UNCenableTime FROM JMJobAction WITH (NOLOCK) WHERE clientId = 1 AND mediaAgentID = 1 AND appType = 0 AND optype = 4 AND appId = @subclientId
			SET @enableBackupAfterDelay = (CASE @enableTime WHEN 0 THEN 0 ELSE 1 END)
			IF @enableBackupAfterDelay = 1
			BEGIN
				SET @enableTZIDName = (SELECT TimeZoneStdName FROM SchedTimeZone WHERE TimeZoneID = @enableTZID)
				SET @strEnableTime = CONVERT(NVARCHAR, dbo.GetDateTime(@enableTime),20)
			END
		END
		ELSE
		BEGIN
			SELECT @enableTZID = enableTimeZone, @enableTime = enableTime FROM JMJobAction WITH (NOLOCK) WHERE clientId = 1 AND mediaAgentID = 1 AND appType = 0 AND optype = 4 AND appId = @associatedPolicyId
			SET @enableBackupAfterDelay = (CASE @enableTime WHEN 0 THEN 0 ELSE 1 END)
			IF @enableBackupAfterDelay = 1
			BEGIN
				SET @enableTZIDName = (SELECT TimeZoneStdName FROM SchedTimeZone WHERE TimeZoneID = @enableTZID)
				SET @strEnableTime = CONVERT(NVARCHAR, dbo.GetDateTime(@enableTime),20)
			END
		END
	END
	IF @propertyLevel >= 10
	BEGIN
		SET @isAdAppAwareSubclient = CASE WHEN EXISTS(SELECT attrVal FROM APP_SubclientProp asp WITH (NOLOCK) WHERE asp.componentNameId = @subClientId AND asp.attrName = N'AD AppAware Subclient' AND asp.cs_attrName = CHECKSUM(N'AD AppAware Subclient') AND attrVal = '1' AND modified = 0) THEN 1 ELSE 0 END
		IF EXISTS(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'Associated subclient Policy'  AND modified = 0)
		BEGIN
			SET @isDecoupleSCPContent = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'Decouple SCP Content'  AND modified = 0), NULL)
		END
SET @isReferenceCopySubclient = ISNULL((SELECT 1 FROM APP_Application WITH(NOLOCK) WHERE id = @subclientId AND (subclientStatus & 0x10000) = 0x10000), NULL)
		IF @isReferenceCopySubclient = 1
		BEGIN
			SET @refCopyFlag = ISNULL((SELECT attrVal From APP_SubClientProp(NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Reference Copy Flag' and modified = 0), 1)
IF @refCopyFlag & 0x2 = 0x2
SET @subclientPropertiesExFlag = @subclientPropertiesExFlag | 0x8000000
			SET @refFirstBackupDone = ISNULL((SELECT attrVal From APP_SubClientProp(NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'RefCopy First backup done' and modified = 0), 0)
			IF @refFirstBackupDone = 1
SET @subclientPropertiesExFlag = @subclientPropertiesExFlag | 0x4000
		END
		SET @isTurboSubclient = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'Is Turbo Subclient'  AND modified = 0), NULL)
SET @isEdgeDriveSubclient = ISNULL((SELECT 1 FROM APP_Application WITH(NOLOCK) WHERE id = @subclientId AND (subclientStatus & 0x20000) = 0x20000), NULL)
		----------------------------------------------------------------
		-- BackupInfo for vWeb
		SET @lastBackupType = (SELECT TOP 1 bkpLevel FROM JMBkpStats WITH (NOLOCK) WHERE appId = @subclientId ORDER BY fullCycleNum DESC,cycleSequence DESC)
		SELECT @totalBackupSize=  ISNULL(SUM(JMB.totalUncompBytes),0)
								  FROM JMBkpStats JMB WITH(NOLOCK)
								 WHERE JMB.appid=@subclientId AND JMB.status in (1,3,14) and datastatus=0
		--Credential Manager Properties
		SELECT @uncPathCredId = apc.credentialId FROM APP_CredentialAssoc apc with (nolock) INNER JOIN APP_SubclientProp asp WITH (NOLOCK) ON CAST(asp.attrVal AS INT) = apc.assocId AND attrName = 'UNCPath Credential Assoc Id' AND modified=0 AND componentNameId = @subclientId
		SELECT @prePostCredId = apc.credentialId FROM APP_CredentialAssoc apc with (nolock) INNER JOIN APP_SubclientProp asp WITH (NOLOCK) ON CAST(asp.attrVal AS INT) = apc.assocId AND attrName = 'prepost Credential Assoc Id' AND modified=0 AND componentNameId = @subclientId
		SELECT @prePostCredSelectionLevel = CAST(ISNULL(attrVal,0) AS INT) FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Use Credential Record' AND modified = 0
		IF @uncPathCredId>0
			SELECT @uncPathCredName =  credentialName from APP_Credentials with (nolock)  where credentialId=@uncPathCredId
		IF @prePostCredId>0
			SELECT @prePostCredname =  credentialName from APP_Credentials with (nolock)  where credentialId=@prePostCredId
	END
	IF @propertyLevel >= 20
	BEGIN
	DECLARE @sizeONMEdia BIGINT=0
	DECLARE @totalUncompBytes FLOAT=0
	DECLARE @dedupePer FLOAT=0
	SELECT @sizeONMEdia= ISNULL(SUM(SM.attributeValueInt),0), @totalUncompBytes =ISNULL(SUM(JMB.totalUncompBytes),0)
	FROM JMBkpStats JMB WITH(NOLOCK)
		INNER JOIN JMJobOptions SM WITH(NOLOCK) ON JMB.jobId = SM.jobId AND JMB.commCellId = SM.commCellId AND SM.attributeId = 73  and  JMB.appid=@subclientId AND JMB.status in (1,3,14)
	IF(@totalUncompBytes<>0)
	BEGIN
		SELECT  @dedupePer = ((@totalUncompBytes-@sizeONMEdia)/@totalUncompBytes)*100
	END
		SET @allowVssForSnapbackup = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Allow VSS for Snap Backup' AND modified = 0), 0)
	END
	IF @propertyLevel >= 30
	BEGIN
		--Security tab
		SET @subclientSecurity = (SELECT @clientId AS '@clientId',
					  @applicationId AS '@applicationId',
					  @instanceid AS '@instanceId',
					  @backupsetid AS '@backupsetId',
					  @subclientid AS '@subclientId'
			   FOR XML PATH ('entity'))
		EXEC [dbo].[APPGetEntitySecurityXML] @subclientSecurity OUTPUT
		EXEC [dbo].[sec_getSecurityForEntity] @userId, @subclientSecuritynew OUTPUT, 3, @clientId, 4, @applicationId, 5, @instanceId, 6, @backupSetId, 7, @subclientId
		--Get Prepost tab items
		SET @preSNAPCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:pre PreSnap'  AND modified = 0)
		SET @postSNAPCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:post PostSnap'  AND modified = 0)
		--Set DB Dump Config
		SET @dbDumpConfig = CAST((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'DB Dump Config' AND modified = 0) AS XML)
		SET @dbDumpConfig = CAST(REPLACE(CAST(@dbDumpConfig AS VARCHAR(MAX)), 'App_DbDumpConfig', 'dbDumpConfig') AS XML)
		--timezone
		SET @timezone = (SELECT TimeZoneName AS '@TimeZoneName', TimeZoneID AS '@TimeZoneID' FROM SchedTimeZone(NOLOCK)
						  WHERE TimeZoneID = (SELECT TOP 1 CAST(attrVal AS INT) FROM APP_SubclientProp WITH (NOLOCK)
												WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = @attrTimezoneId  AND APP_SubclientProp.cs_attrname = CHECKSUM(@attrTimezoneId) AND modified = 0)
						FOR XML PATH ('TimeZone'), TYPE)
		--user Id for TAPI
		SET @onBoardCompanyId = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subClientId AND APP_SubclientProp.attrName = 'OnBoarded Company ID'  AND modified = 0),0)
		IF @associatedPolicyId > 0 AND @planId = 0
		BEGIN
			SET @preBackupCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @associatedPolicyId AND APP_SubclientProp.attrName = 'sys:pre PreBackup'  AND modified = 0)
			SET @postBackupCommand = (SELECT attrVal FROM APP_SubclientProp	WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @associatedPolicyId AND APP_SubclientProp.attrName = 'sys:post PostBackup'  AND modified = 0)
			SET @prepostSelectionLevel = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @associatedPolicyId AND APP_SubclientProp.attrName = 'use prepost username'  AND modified = 0)
			IF @prepostSelectionLevel = 2
			BEGIN
				SET @prepostUserName = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @associatedPolicyId AND APP_SubclientProp.attrName = 'prepost username'  AND modified = 0)
			END
			SET @preScanCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @associatedPolicyId AND APP_SubclientProp.attrName = 'sys:pre PreIfind'  AND modified = 0)
			SET @postScanCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @associatedPolicyId AND APP_SubclientProp.attrName = 'sys:post PostIfind'  AND modified = 0)
			SET @snapProcessOn = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'sys: pre post option' AND modified = 0),0)
		END
		ELSE
		BEGIN -- For non-zero plan ids, read from subclient as we allow setting value directly
			SET @preBackupCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:pre PreBackup'  AND modified = 0)
			SET @postBackupCommand = (SELECT attrVal FROM APP_SubclientProp	WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:post PostBackup'  AND modified = 0)
			SET @prepostSelectionLevel = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'use prepost username'  AND modified = 0)
			IF @prepostSelectionLevel = 2
			BEGIN
				SET @prepostUserName = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'prepost username'  AND modified = 0)
			END
			SET @preScanCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:pre PreIfind'  AND modified = 0)
			SET @postScanCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:post PostIfind'  AND modified = 0)
			SET @snapProcessOn = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'sys: pre post option' AND modified = 0),0)
		END
		SET @preSNAPCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:pre PreSnap'  AND modified = 0)
		SET @postSNAPCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:post PostSnap'  AND modified = 0)
		SET @excludeFromSLA = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'Exclude From SLA'  AND modified = 0)
		SET @ignoreStrikesBefore = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'Ignore Strikes Before' AND modified = 0)
		--SET @runPostBackup = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'sys: pre post option' AND modified = 0),0)
		SET @preLogCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:pre PreLog'  AND modified = 0)
		SET @postLogCommand = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'sys:post PostLog'  AND modified = 0)
		SET @isTrueUpEnabled = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'Is true up enabled'  AND modified = 0), 0)
		SET @runTrueUpAfterDays = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'run true up after days'  AND modified = 0), 30)
		IF (@snapProcessOn & 0x4) = 0x4
			SET @preSnapProcessOn = 1
		IF (@snapProcessOn & 0x8) = 0x8
			SET @postSnapProcessOn = 1
		IF (@snapProcessOn & 0x1) = 0x1
			SET @runPostScan = 1
		else
			SET @runPostScan = 0
		IF (@snapProcessOn & 0x2) = 0x2
			SET @runPostBackup = 2
		ELSE
			SET @runPostBackup = 0
		IF (@snapProcessOn & 0x10) = 0x10
			SET @runPostLog = 1
		ELSE
			SET @runPostLog = 0
		--Get SnapProtect items
		IF @associatedPolicyId > 0
		BEGIN
			SET @snapToTapeProxyToUseSource = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'Snap copy : use source on missing proxy' AND modified = 0)
			SET @separateProxyForSnapToTape = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'Snap to tape : proxy to use' AND modified = 0)
			IF @separateProxyForSnapToTape <> ''
				SET @separateProxyForSnapToTapeName = (SELECT name FROM APP_Client WHERE id = @separateProxyForSnapToTape)
			SET @checkProxyForSQLIntegrity =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'Use Proxy' AND modified = 0)
IF (@applicationId = 22 OR @applicationId = 80)
				SET @checkProxyForSQLIntegrity =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'Oracle Check SQL Integrity' AND modified = 0)
			SET @sqlIntegrityServerName  =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'MSSQL Data Integrity Server Name' AND modified = 0)
			SET @useSqlCredentials  =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'SQL Credentials' AND modified = 0)
			SET @useProxyForSQLIntegrity  =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'SQL Integrity : proxy to use' AND modified = 0)
			SET @useProxyForSQLIntegrityName = (SELECT name FROM APP_Client WITH (NOLOCK) WHERE id = @useProxyForSQLIntegrity)
			SET @sqlUserName =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'SQL User Name' AND modified = 0)
		END
		ELSE
		BEGIN
			SET @snapToTapeProxyToUseSource = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Snap copy : use source on missing proxy' AND modified = 0)
			SET @separateProxyForSnapToTape = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Snap to tape : proxy to use' AND modified = 0)
			IF @separateProxyForSnapToTape <> ''
				SET @separateProxyForSnapToTapeName = (SELECT name FROM APP_Client WHERE id = @separateProxyForSnapToTape)
			SET @checkProxyForSQLIntegrity =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Use Proxy' AND modified = 0)
IF (@applicationId = 22 OR @applicationId = 80)
				SET @checkProxyForSQLIntegrity =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Oracle Check SQL Integrity' AND modified = 0)
			SET @sqlIntegrityServerName  =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'MSSQL Data Integrity Server Name' AND modified = 0)
			SET @useSqlCredentials  =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'SQL Credentials' AND modified = 0)
			SET @useProxyForSQLIntegrity  =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'SQL Integrity : proxy to use' AND modified = 0)
			SET @useProxyForSQLIntegrityName = (SELECT name FROM APP_Client WITH (NOLOCK) WHERE id = @useProxyForSQLIntegrity)
			SET @sqlUserName =(SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'SQL User Name' AND modified = 0)
		END
		SET @useDumpBasedBackupCopy = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Sybase Snap Backup copy uses dump based approach' AND modified = 0), 0)
		IF @useDumpBasedBackupCopy = 1
		BEGIN
			SET @useConfiguredInstance = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Sybase Use Configured Instance Enabled' AND modified = 0), 0)
			SET @createAuxSybaseServer = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Sybase create an Auxiliary server' AND modified = 0), 0)
			SET @dumpBasedBackupCopyOption = 0
			IF @useConfiguredInstance = 1 AND @createAuxSybaseServer = 0
				SET @dumpBasedBackupCopyOption = 1
			ELSE IF @useConfiguredInstance = 0 AND @createAuxSybaseServer = 1
				SET @dumpBasedBackupCopyOption = 2
			IF @dumpBasedBackupCopyOption = 1
			BEGIN
				SET @configuredSybaseInstance = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Sybase Configured Instance Name' AND modified = 0)
			END
			IF @dumpBasedBackupCopyOption = 2
			BEGIN
				SET @sybaseHome = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Sybase Auxiliary server Home path' AND modified = 0)
				SET @sybaseASE = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Sybase Auxiliary server ASE path' AND modified = 0)
				SET @sybaseOCS = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Sybase Auxiliary server OCS path' AND modified = 0)
				SET @sybaseUser = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Sybase Auxiliary server User' AND modified = 0)
			END
		END
		ELSE
		BEGIN
			SET @useDumpBasedBackupCopy = NULL
		END
		SET @isRMANEnableForTapeMovement = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Use RMAN for Tape Movement' AND modified = 0), '0')
		SET @backupCopyInterface = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Oracle Backup Copy Interface' AND modified = 0), '0')
		SET @IsOracleSposDriverEnabled = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Use block level SPOS backup' AND modified = 0), '0')
		SET @useSeparateProxyForSnapToTape = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Snap to tape : proxy enabled' AND modified = 0)
		SET @proxyESXServer = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Virtual Server ESX Server Name' AND modified = 0)
		SET @proxyESXHost = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Virtual Server ESX Host name' AND modified = 0)
		SET @backupForGranularRecovery = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS Collect Vss Meta Data' AND modified = 0)
		SET @truncateExDBLogs = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS Truncate EXDB Logs' AND modified = 0)
		SET @impersonate = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS Quiesce Impersonate' AND modified = 0)
		SET @quiesceUserName = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS Quiesce Username' AND modified = 0)
		SET @useSSHKey = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Use SSH Key' AND modified = 0)
		SET @vmApplUserName = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Virtual Machine Application User' AND modified = 0)
		SET @vmSSHKeyFilePath = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Virtual Machine SSH Key FilePath' AND modified = 0)
		SET @appAware = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS Collect Vss Meta Data' AND modified = 0)
		SET @appValidation = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS App Validation' AND modified = 0), 0)
		SET @useSourceESXMount = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Use Source ESX Mount' AND modified = 0), 0)
		SET @backupValidationTaskId = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VM Backup Validation Task Id' AND modified = 0), 0)
		SET @backupValidationSubTaskId = ISNULL((SELECT subTaskId from TM_SubTask WITH (NOLOCK) WHERE taskId = @backupValidationTaskId and deleted =0), 0)
		SET @customAppXML = (SELECT CAST(REPLACE(CAST(attrVal AS VARCHAR(MAX)), 'App_ValidationApp', 'customApp') AS XML) FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS CustomApp XML' AND modified = 0)
		SET @isCustomAppSet = CASE WHEN @customAppXML IS NOT NULL AND CAST(@customAppXML as VARCHAR(MAX)) <> '' THEN 1 ELSE 0 END
		SET @runAsDevTestGroup = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Run as Dev Test Group' AND modified = 0), 0)
		SET @backupValidationThreadCount = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS Backup Validation Thread Count' AND modified = 0), 0)
		SET @guestCredentialsID = ISNULL((SELECT credentialId from APP_CredentialAssoc WHERE assocId = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS Guest Credentials Assoc Id' AND modified = 0), 0)), 0)
		SET @guestCredentialsName = CASE WHEN @guestCredentialsID >0 THEN (SELECT credentialName FROM APP_Credentials WITH (NOLOCK) WHERE credentialId = @guestCredentialsID and enabled =1) ELSE '' END
		SET @uncCredentialsID = ISNULL((SELECT credentialId from APP_CredentialAssoc WHERE assocId = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS UNC Script Credentials Assoc Id' AND modified = 0), 0)), 0)
		SET @uncCredentialsName = CASE WHEN @uncCredentialsID >0 THEN (SELECT credentialName FROM APP_Credentials WITH (NOLOCK) WHERE credentialId = @uncCredentialsID and enabled =1) ELSE '' END
		SET @uncScriptCredentialUsername = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'VS UNC Script Username' AND modified = 0), 0)
		SET @transportModeForVMWare = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Virtual Server Transport Mode' AND modified = 0)
		SET @useProxy = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Virtual Server Proxy Client' AND modified = 0)
		IF @useProxy IS NOT NULL
		BEGIN
			SET @useProxyClientName = (SELECT name FROM APP_Client WHERE id = @useProxy)
		END
		SET @disableArchivingTurboRule = ISNULL((SELECT attrVal FROM APP_SubClientProp WITH (NOLOCK) WHERE componentNameId = @subClientId AND attrName = N'Disable Archiving Turbo Rule' AND modified = 0),0)
		SET @jobStartTime = ISNULL((SELECT attrVal FROM APP_SubClientProp WITH (NOLOCK) WHERE componentNameId = @subClientId AND attrName = N'JobStartTime' AND modified = 0 AND cs_attrName = checksum(N'JobStartTime') ),-1) -- CV_PROP_JOB_START_TIME
		IF (@jobStartTime<0)
			SET @jobStartTime = NULL
		IF @snapToTapeProxyToUse IS NOT NULL
		BEGIN
			SET @snapToTapeProxyToUseClientName = (SELECT name FROM APP_Client WHERE id = @snapToTapeProxyToUse)
		END
		SET @db2UseACSInterface = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Use ACS Interface' AND modified = 0)
		--Get Storage devices
		SET	@softwareCompression = (SELECT TOP 1 compressWhere FROM archPipeConfig WITH (NOLOCK) WHERE appNumber = @subclientId)
		SET @networkAgents = (SELECT TOP 1 numNWAgents FROM archPipeConfig WITH (NOLOCK) WHERE appNumber = @subclientId)
		SET @throttleNetworkBandwidth = (SELECT TOP 1 NWWriteSpeed FROM archPipeConfig WITH (NOLOCK) WHERE appNumber = @subclientId)
		-- Default Value as per Appmgr  is 1 - Client Side Dedup
		IF @associatedPolicyId > 0
			SET @generateSignature = ISNULL((SELECT TOP 1 attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @associatedPolicyId AND APP_SubclientProp.attrName = 'Single Instancing Option'  AND modified = 0), 1)
		ELSE
			SET @generateSignature = ISNULL((SELECT TOP 1 attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'Single Instancing Option'  AND modified = 0), 1)
		SET @enableDeduplication = CASE WHEN @generateSignature >= 1 THEN 1 ELSE 0 END
		SET @encryptionFlag = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE APP_SubclientProp.componentNameId = @subclientId AND APP_SubclientProp.attrName = 'Encrypt: encryption' AND modified = 0)
		SET @overrideDataPath = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Full Failover to SP dataPaths' AND modified = 0)
		SET @overrideIncrementalDataPath = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Non-Full Failover to SP dataPaths' AND modified = 0)
		IF @overrideDataPath = '1' OR @overrideIncrementalDataPath = '1'
		BEGIN
			DECLARE @dpID AS INT
			DECLARE @priority INT
			DECLARE @dataPathOfIncSP INT
			DECLARE dataPathCur	CURSOR FOR SELECT dataPathId, precedence, backupType FROM APP_AppToDataPath WITH (NOLOCK) WHERE componentNameId = @subclientId
				OPEN dataPathCur
					FETCH NEXT FROM dataPathCur INTO @dpID, @priority, @dataPathOfIncSP
					WHILE @@FETCH_STATUS = 0
					BEGIN
							SET @dataPathOfIncSP = NULL
							--Check if datapath is of Inc SP
							IF (SELECT app.dataArchGrpID FROM MMDataPath mmdp WITH (NOLOCK), archGroupCopy AGC WITH (NOLOCK), archGroup AG WITH (NOLOCK), APP_Application app WITH (NOLOCK)
									WHERE mmdp.DataPathId = @dpID
									AND mmdp.CopyId = AGC.id
									AND AGC.copy = 1
									AND AGC.archGroupId = AG.incrSP
									AND AG.id = app.dataArchGrpID
									AND app.id = @subclientId ) <> ''
							BEGIN
								SET @dataPathOfIncSP = 1
							END
							--Check if datapath is of data SP
							IF( SELECT app.dataArchGrpID FROM MMDataPath mmdp WITH (NOLOCK), archGroupCopy AGC WITH (NOLOCK), archGroup AG WITH (NOLOCK), APP_Application app WITH (NOLOCK)
									WHERE mmdp.DataPathId = @dpID
									AND mmdp.CopyId = AGC.id
									AND AGC.copy = 1
									AND AGC.archGroupId = AG.id
									AND AG.id = app.dataArchGrpID
									AND app.id = @subclientId ) <> ''
							BEGIN
								SET @dataPathOfIncSP = 0
							END
							INSERT INTO @dataPaths(datapathId, library, mediaAgent, drivePoolName, scratchPoolName, priority, dataPathOfIncSP)
							VALUES (@dpID,
										(SELECT MML.AliasName
											FROM MMDataPath MDP WITH (NOLOCK), MMDrivePool MDrP WITH (NOLOCK), MMMasterPool MMP WITH (NOLOCK), MMLibrary MML WITH (NOLOCK)
											WHERE MDP.DataPathId = @dpID
												AND MDP.DrivePoolId = MDrP.DrivePoolId
												AND MDrP.MasterPoolId = MMP.MasterPoolId
												AND MMP.LibraryId = MML.LibraryId),
										(SELECT APPCli.name
											FROM APP_Client APPCli WITH (NOLOCK), MMDataPath MMDp WITH (NOLOCK)
											WHERE MMDp.DataPathId = @dpID
												AND MMDp.HostClientId = APPCli.id),
(SELECT CASE MMDrp.DrivePoolType WHEN 10001 THEN 'N/A' ELSE MMDrp.DrivePoolName END
											FROM MMDrivePool MMDrp WITH(NOLOCK)
											INNER JOIN MMDataPath MMDP WITH(NOLOCK) ON MMDP.DrivePoolId = MMDrp.DrivePoolId
											AND MMDP.DataPathId = @dpID),
										(SELECT MMSGp.SpareGroupName
											FROM MMSpareGroup MMSGp WITH(NOLOCK)
											INNER JOIN MMDataPath MMDP WITH(NOLOCK) ON MMDP.SpareGroupId = MMSGp.SpareGroupId
											AND MMDP.DataPathId = @dpID),
										@priority,
										@dataPathOfIncSP)
					FETCH NEXT FROM dataPathCur INTO @dpID, @priority, @dataPathOfIncSP
				END--END dataPathCur fetch loop
			CLOSE dataPathCur
			DEALLOCATE dataPathCur
			UPDATE @dataPaths
			SET scratchPoolName = drivePoolName
			WHERE drivePoolName = 'N/A'
		END
IF @mode = 4
		begin
			-- Get the next backup time for the subclient only on details since its expensive call
			set @nextBackupTime = 0
			DECLARE @tblNextBackupTime TABLE (nextBackupTime int)
			delete from @tblNextBackupTime
			INSERT  INTO @tblNextBackupTime
			EXEC    @nextBackupTime = TM_GetNextScheduleRunTime @subclientId, 7, 0, 0, 0 --- Get the next run time for incremental, full and differential backup schedule.
			if (@nextBackupTime<dbo.getunixtime(getutcdate()))
				set @nextBackupTime=0
			declare @nextBackupTimeOsc int = 0
			select @nextBackupTimeOsc = CAST(attrVal AS INT) FROM APP_ClientProp
				WHERE componentNameId=@clientId AND attrName='Next Run Time In'
				AND modified=0 AND ISNUMERIC(attrVal) = 1
			if (@nextBackupTimeOsc<dbo.getunixtime(getutcdate()))
				set @nextBackupTimeOsc=0
			-- Take the least backup time
			if ( (@nextBackupTime=0 OR @nextBackupTime>@nextBackupTimeOsc) AND (@nextBackupTimeOsc>0))
			begin
				set @nextBackupTime = @nextBackupTimeOsc
			end
		end
		--Get General tab items
		IF @associatedPolicyId > 0
		BEGIN
			DECLARE @isAutomaticModeEnabled INT = 0
			-- Default value to match CV_APPLICATION_DEFAULT_N_STREAMS
			SET @numberOfBackupStreams = (SELECT CAST(attrVal AS INT) FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = N'sys:nStreams' AND cs_attrName = checksum(N'sys:nStreams') AND modified = 0)
			IF @numberOfBackupStreams IS NULL OR @numberOfBackupStreams = 0
			BEGIN
				SET @isAutomaticModeEnabled = 1
				SET @numberOfBackupStreams = dbo.[GetNumberOfStreamsSupported](@subclientId)
			END
			SET @maxStreamsForSnapBackup = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = N'sys:nStreamsSnapBackup' AND cs_attrName = checksum(N'sys:nStreamsSnapBackup') AND modified = 0)
			IF @maxStreamsForSnapBackup IS NULL OR @maxStreamsForSnapBackup = 0
			BEGIN
				-- We don't have to compute the maxStreams again if it is already computed for numberOfBackupStreams.
				-- We will use the same value here.
				IF @isAutomaticModeEnabled = 1
					SET @maxStreamsForSnapBackup = @numberOfBackupStreams
				ELSE
					SET @maxStreamsForSnapBackup = dbo.[GetNumberOfStreamsSupported](@subclientId)
			END
			SET @allowMultipleDataReaders = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = N'sys:UseMultipleStreamsWithinDrive' AND cs_attrName = checksum(N'sys:UseMultipleStreamsWithinDrive') AND modified = 0)
			IF @allowMultipleDataReaders IS NULL
			BEGIN
				IF @isAutomaticModeEnabled = 1
				BEGIN
					IF @numberOfBackupStreams = 0
						SET @allowMultipleDataReaders = 1
					ELSE
						SET @allowMultipleDataReaders = 0
				END
				ELSE
				BEGIN
					DECLARE @numStreams INT = dbo.[GetNumberOfStreamsSupported](@subclientId)
					IF @numStreams = 0
						SET @allowMultipleDataReaders = 1
					ELSE
						SET @allowMultipleDataReaders = 0
				END
			END
		END
		ELSE
		BEGIN
			SET @numberOfBackupStreams = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = N'sys:nStreams' AND cs_attrName = checksum(N'sys:nStreams') AND modified = 0), 1)
			SET @allowMultipleDataReaders = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = N'sys:UseMultipleStreamsWithinDrive' AND cs_attrName = checksum(N'sys:UseMultipleStreamsWithinDrive') AND modified = 0)
			SET @maxStreamsForSnapBackup = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = N'sys:nStreamsSnapBackup' AND cs_attrName = checksum(N'sys:nStreamsSnapBackup') AND modified = 0), 1)
		END
		SET @indexCycleToEveryBackupJobs = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'New Index Cycle' AND modified = 0)
		SET @turboNASClient = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Turbo NAS Proxy Client' AND modified = 0)
		SET @turboNASClientName = (SELECT name FROM APP_Client WHERE id = @turboNASClient)
		-- FLR_REPLICATION_OPTIONS_ITEM
		SET @replicationOptionFlag = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'FS FLR Replication Options' AND modified = 0)
		-- SNAPSHOT_VOLUME_UNIT_NAME_ITEM
		IF @associatedSnapShotSCId IS NOT NULL
		BEGIN
			SET @associatedSnapShotSCName = (SELECT subclientName FROM APP_Application WHERE id = @associatedSnapShotSCId)
		END
		-- SINGLE_INSTANCING_ITEM
		--SET @softwareCompressionDestination = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Single Instancing Option' AND modified = 0)
		-- READ_BUFFER_SIZE_ITEM
		IF @associatedPolicyId > 0
			SET @readBuffersize = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'Read Buffer Size' AND modified = 0)
		ELSE
			SET @readBuffersize = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Read Buffer Size' AND modified = 0)
		--IS_KEEP_PAST_EXPIRY_ITEM
		SET @isKeepItemPastExpiryTime = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Keep items past their expiration' AND modified = 0)
		--SYMBOLIC_LINK_SUPPORT flag
		SET @expandSymbolicLinks = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Follow Symbolic link' AND modified = 0 )
		--APPLE_DOUBLE_SUPPORT
		IF @associatedPolicyId > 0
			SET @enableAppleDoubleSupport = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @associatedPolicyId AND attrName = 'Enable Apple Double Support' AND modified = 0 )
		ELSE
			SET @enableAppleDoubleSupport = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Enable Apple Double Support' AND modified = 0 )
		--BACKUP_FILES_QUALIFY_ARCHIVE
		SET @onlyBackupQualifiedArchivingFiles = (SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @subclientId AND attrName = 'Backup Only Files qualified for archiving' AND modified = 0 )
		SET @subclientStatus = (SELECT subclientStatus FROM APP_Application WITH (NOLOCK) WHERE id = @subclientId)
		-- Index tab (only basic props)
		DECLARE @idxOptsSupported INT = 0
		DECLARE @pruningEnabled INT = 0
		INSERT INTO #idxOpts SELECT * FROM dbo.GetIndexServerOptions (@clientId, @instanceId, @applicationId, @backupsetId, @subclientId, 1, @propertyLevel)
		IF EXISTS (SELECT TOP 1 entityId FROM #idxOpts)
		BEGIN
			SELECT @pruningEnabled = CAST(ISNULL(value,0) AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'Enable Index Pruning' AND modified = 0
			SET @idxOptsSupported = 1
		END
		--Performance Tab Variables
		SELECT TOP 1 @copyId 		=	ISNULL(AGC.id,0),
					 @perfCopyName 	= 	AGC.name,
					 @perfSPName	=	AG.name
					 FROM	APP_Application APP WITH(READUNCOMMITTED)
					 JOIN	archGroup AG WITH(READUNCOMMITTED) ON APP.dataArchGrpID = AG.id
					 JOIN	archGroupCopy AGC WITH(READUNCOMMITTED) ON AG.defaultCopy = AGC.id
					 WHERE	APP.id = @subclientId
		SELECT @perfClientName = name from APP_Client where id = @clientId
		INSERT 	INTO @perfMaList
		SELECT	AC.id AS perfMaId,
				AC.name AS perfMa,
(CASE WHEN (MH.Attribute&256 = 256) THEN 1 ELSE 0 END) AS crcOverNetwork,
(CASE WHEN (MH.Attribute&512 = 512) THEN 1 ELSE 0 END) AS crcOverMedia
		FROM	MMHost MH
		JOIN	APP_Client AC ON MH.ClientId = AC.id
		WHERE	clientid IN (SELECT DISTINCT HostClientId FROM MMDataPath WHERE CopyId = @copyId)
		IF @copyId > 0
		BEGIN
			SELECT	@multiplexingValid				=	(CASE WHEN (ISNULL(dedupeFlags,0) > 0) THEN 0 ELSE 1 END),
					@muxFactor						= 	ISNULL(maxMultiplex,0),
@streamsOverMux 				=   (CASE WHEN (ISNULL(flags,0) & 268435456 = 268435456) THEN 1 ELSE 0 END)
														FROM archGroupCopy WITH(READUNCOMMITTED)
														WHERE id = @copyId
			IF @muxFactor = 0
				SET @enableMultiplexing = 0
			ELSE
				SET @enableMultiplexing = 1
SELECT @lookAheadReaderSlots			=	CASE WHEN COUNT(*) <> 1 THEN 4 ELSE SUM(VALUE) END
														FROM
(SELECT	DISTINCT ISNULL(CAST(CAST(value AS NVARCHAR(MAX)) AS INT),4) AS value
														FROM	MMHost WITH(READUNCOMMITTED)
LEFT	OUTER JOIN APP_AdvanceSettings WITH(READUNCOMMITTED) ON ClientId = entityId AND	keyName = 'DataMoverLookAheadLinkReaderSlots' AND enabled = 1
														WHERE	clientid IN (SELECT DISTINCT HostClientId FROM MMDataPath WHERE CopyId = @copyId)) VALS
			--For current design, shows default value when all clients don't have the same value set for the regKey
SELECT @numOfPipelineBuffers			=	CASE WHEN COUNT(*) <> 1 THEN 90 ELSE SUM(VALUE) END
														FROM
(SELECT	DISTINCT ISNULL(CAST(CAST(value AS NVARCHAR(MAX)) AS INT),90) AS value
														FROM	MMHost WITH(READUNCOMMITTED)
LEFT	OUTER JOIN APP_AdvanceSettings WITH(READUNCOMMITTED) ON ClientId = entityId AND	keyName = 'nNumPipelineBuffers' AND enabled = 1
														WHERE	clientid IN (SELECT DISTINCT HostClientId FROM MMDataPath WHERE CopyId = @copyId UNION SELECT @clientId)) VALS
SELECT	@pipelineBufferSize				=	CASE WHEN COUNT(*) <> 1 THEN ((64*1024)/1024) ELSE (SUM(VALUE)/1024) END
														FROM
(SELECT	DISTINCT ISNULL(CAST(CAST(value AS NVARCHAR(MAX)) AS INT),(64*1024)) AS value
														FROM	MMHost WITH(READUNCOMMITTED)
LEFT	OUTER JOIN APP_AdvanceSettings WITH(READUNCOMMITTED) ON ClientId = entityId AND	keyName = 'nPipelineBufferSize' AND enabled = 1
														WHERE	clientid IN (SELECT DISTINCT HostClientId FROM MMDataPath WHERE CopyId = @copyId UNION SELECT @clientId)) VALS
		END
	END
--Construct output XML
	SET	@xmlResult =(SELECT
						(SELECT @userDescription AS '@description',
								@encryptionFlag AS '@encryptionFlag',
								@replicationOptionFlag AS '@fsFLRReplicationOptions',
								@readBuffersize	AS '@readBuffersize',
								@isKeepItemPastExpiryTime AS '@isKeepItemPastExpiryTime',
								@expandSymbolicLinks AS '@expandSymbolicLinks',
								@enableAppleDoubleSupport AS '@enableAppleDoubleSupport',
								@onlyBackupQualifiedArchivingFiles AS '@onlyBackupQualifiedArchivingFiles',
								@isDefaultSubclient   AS '@isDefaultSubclient',
								@isSubclientDeleted AS '@isSubclientDeleted',
								@isSubclientUninstalled	AS '@isSubclientUninstalled',
								@isSubclientHidden AS '@isSubclientHidden',
								@numberOfBackupStreams AS '@numberOfBackupStreams',
								@allowMultipleDataReaders AS '@allowMultipleDataReaders',
								@onDemand AS '@onDemandSubClient',
								@totalBackupSize AS '@totalBackupSize',
								@lastBackupSize AS '@lastBackupSize',
								@lastBackupTime AS '@lastBackupTime',
								@firstBackupTime AS '@firstBackupTime',
								@sizeONMEdia AS '@sizeOnMedia',
								@dedupeper    AS '@dedupeRatio',
								@nextBackupTime AS '@nextBackupTime',
								@excludeFromSLA AS '@excludeFromSLA',
								@ignoreStrikesBefore AS '@ignoreStrikesBefore',
								@enableBackup AS '@enableBackup',
								@enableBackupAfterDelay AS '@enableBackupAfterDelay',
   							@isActivityDisabledFromPolicy AS '@isActivityDisabledFromPolicy',
								@isDecoupleSCPContent AS '@isDecoupleSCPContent',
								@isReferenceCopySubclient AS '@isReferenceCopySubclient',
								@subclientPropertiesExFlag AS '@subClientPropertiesFlag',
								@isTurboSubclient AS '@isTurboSubclient',
								@isSystemStateSubclient AS '@isSystemStateSubclient',
								@isEdgeDriveSubclient AS '@isEdgeDriveSubclient',
								@indexCycleToEveryBackupJobs AS '@indexCycleToEveryBackupJobs',
								@isIndexingBackupSubclient AS '@indexingBackupSubClient',
								@isAdAppAwareSubclient AS '@isAdAppAwareSubclient',
								@allowVssForSnapbackup AS '@allowVssForSnapbackup',
								@prePostCredSelectionLevel AS '@runAs',
								@isTrueUpEnabled AS '@isTrueUpOptionEnabled',
								@runTrueUpAfterDays AS '@runTrueUpJobAfterDays',
								@scpIdaType AS '@idaType',
								@slaStatus AS '@slaStatus',
								@slaCategory AS '@slaCategory',
								@slaCategoryDescription AS '@slaCategoryDescription',
								@onBoardCompanyId AS '@onBoardCompanyId',
								@disableArchivingTurboRule AS '@disableArchivingTurboRule',
								@jobStartTime AS '@jobStartTime',
								@prePostCredId AS 'prepostCredentialinfo/@credentialId',
								@prePostCredname AS 'prepostCredentialinfo/@credentialName',
								@uncPathCredId AS 'impersonateUserCredentialinfo/@credentialId',
								@uncPathCredName AS 'impersonateUserCredentialinfo/@credentialName',
								CAST(@lastBackupJobInfo AS XML),
								(SELECT @enableTZID	 AS '@TimeZoneID',
										@enableTime AS '@time',
										@enableTZIDName AS '@TimeZoneName',
										@strEnableTime AS '@timeValue'
								FOR XML PATH('enableBackupAtDateTime'),TYPE),
                                (SELECT @associatedPolicyId AS '@subclientPolicyId'
								FOR XML PATH('associatedPolicy'),TYPE),
								(SELECT @turboNASClient AS '@clientId',
										@turboNASClientName AS '@clientName',
										3 AS '@_type_'
								FOR XML PATH('turboNASClient'),TYPE),
								(SELECT
									@preScanCommand	AS '@preScanCommand',
									@postScanCommand AS '@postScanCommand',
									@preBackupCommand AS '@preBackupCommand',
									@postBackupCommand AS '@postBackupCommand',
									@preSNAPCommand	AS '@preSNAPCommand',
									@postSNAPCommand AS '@postSNAPCommand',
									@runPostBackup AS '@runPostBackup',
									@runPostScan AS '@runPostScan',
									@preSnapProcessOn AS '@preSnapProcessOn',
									@postSnapProcessOn AS '@postSnapProcessOn',
									@prepostSelectionLevel AS '@runAs',
									@preLogCommand	AS '@preLogCommand',
									@postLogCommand AS '@postLogCommand',
									@runPostLog		AS '@runPostLog',
									(SELECT
										@prepostUserName AS '@userName'
									FOR XML PATH('prepostUserName'),TYPE)
								FOR XML PATH('prepostProcess'),TYPE),
								(SELECT
									@softwareCompression AS '@softwareCompression',
									@networkAgents AS '@networkAgents',
									@throttleNetworkBandwidth AS '@throttleNetworkBandwidth',
									@readBuffersize	AS '@applicableReadSize',
									@overrideDataPath AS '@overrideDataPath',
									@overrideIncrementalDataPath AS '@overrideIncrementalDataPath',
									(SELECT library AS '@library',
											mediaAgent AS '@mediaAgent',
											drivePoolName AS '@drivePool',
											scratchPoolName AS '@scratchPool',
											priority AS '@priority',
											dataPathOfIncSP AS '@dataPathOfIncSP'
											FROM @dataPaths
									FOR XML PATH('dataPath'),TYPE),
									(SELECT @dataBackupStoragePolicyID AS '@storagePolicyId',
											@dataBackupStoragePolicyName AS '@storagePolicyName',
											17 AS '@_type_'
									FOR XML PATH('dataBackupStoragePolicy'),TYPE),
									(SELECT @logBackupStoragePolicyID	AS '@storagePolicyId',
											@logBackupStoragePolicyName AS '@storagePolicyName',
											17 AS '@_type_'
									FOR XML PATH('logBackupStoragePolicy'),TYPE),
									(SELECT @enableDeduplication AS '@enableDeduplication',
											@generateSignature AS '@generateSignature'
									FOR XML PATH('deDuplicationOptions'),TYPE),
									(SELECT		@multiplexingValid AS '@multiplexingValid',
												@enableMultiplexing AS '@enableMultiplexing',
												@muxFactor AS '@muxFactor',
												@streamsOverMux AS '@streamsOverMux',
												@lookAheadReaderSlots AS '@lookAheadReaderSlots',
												@numOfPipelineBuffers AS '@numOfPipelineBuffers',
												@pipelineBufferSize AS '@pipelineBufferSize',
												@perfSPName AS '@perfSPName',
												@perfCopyName AS '@perfCopyName',
												@perfClientName AS '@perfClientName',
												(SELECT perfMaId AS '@perfMaId',
														perfMa AS '@perfMa',
														crcOverNetwork AS '@crcOverNetwork',
														crcOverMedia AS '@crcOverMedia'
														FROM @perfMaList
														FOR XML PATH('perfCRCDetails'),TYPE)
                                    FOR XML PATH('performanceMode'),TYPE)
								FOR XML PATH('storageDevice'),TYPE),
								(SELECT
									@useSeparateProxyForSnapToTape AS '@useSeparateProxyForSnapToTape',
									@proxyESXServer AS '@proxyESXServer',
									@proxyESXHost AS '@proxyESXHost',
									@backupForGranularRecovery AS '@backupForGranularRecovery',
									@truncateExDBLogs AS '@truncateExDBLogs',
									@transportModeForVMWare AS '@transportModeForVMWare',
									@snapToTapeProxyToUseSource AS '@snapToTapeProxyToUseSource',
									@isRMANEnableForTapeMovement AS '@isRMANEnableForTapeMovement',
									@backupCopyInterface AS '@backupCopyInterface',
									@IsOracleSposDriverEnabled AS '@IsOracleSposDriverEnabled',
									@isSnapbackupEnabled AS '@isSnapBackupEnabled',
									@useDumpBasedBackupCopy AS '@useDumpBasedBackupCopy',
									@useConfiguredInstance AS '@useConfiguredInstance',
									@createAuxSybaseServer AS '@createAuxSybaseServer',
									@dumpBasedBackupCopyOption AS '@dumpBasedBackupCopyOption',
									@sybaseHome AS '@sybaseHome',
									@sybaseASE AS '@sybaseASE',
									@sybaseOCS AS '@sybaseOCS',
									@impersonate AS '@impersonate',
									@appAware AS '@applicationAware',
									@checkProxyForSQLIntegrity AS '@checkProxyForSQLIntegrity',
									@sqlIntegrityServerName AS '@sqlIntegrityServerName',
									@useSqlCredentials AS '@useSqlCredentials',
									@db2UseACSInterface AS '@useDB2ACSInterface',
									(SELECT @useProxyForSQLIntegrity AS '@clientId',
											@useProxyForSQLIntegrityName AS '@clientName'
									FOR XML PATH('useProxyForSQLIntegrity'),TYPE),
									(SELECT @sqlUserName AS '@userName'
									FOR XML PATH('sqlUser'),TYPE),
									(SELECT @sybaseUser AS '@userName'
									FOR XML PATH('sybaseUser'),TYPE),
									(SELECT	@configuredSybaseInstance AS '@instanceName',
											5 AS '@_type_'
									FOR XML PATH('configuredSybaseInstance'),TYPE),
									(SELECT	@separateProxyForSnapToTape AS '@clientId',
											@separateProxyForSnapToTapeName AS '@clientName',
											3 AS '@_type_'
									FOR XML PATH('separateProxyForSnapToTape'),TYPE),
									(SELECT	@useProxy AS '@clientId',
											@useProxyClientName AS '@clientName',
											3 AS '@_type_'
									FOR XML PATH('useProxy'),TYPE),
									(SELECT @SnapShotEngineId AS '@snapShotEngineId',
											@SnapShotEngineName AS '@snapShotEngineName',
											110 AS '@_type_'
									FOR XML PATH('snapToTapeSelectedEngine'),TYPE),
									(SELECT @snapToTapeProxyToUse AS '@clientId',
											@snapToTapeProxyToUseClientName AS '@clientName',
											3 AS '@_type_'
									FOR XML PATH('snapToTapeProxyToUse'),TYPE),
									(SELECT @quiesceUserName AS '@userName'
                                                        FOR XML PATH('quiesceImpersonateUser'), TYPE),
									(SELECT  @vmApplUserName AS '@userName'
                                                        FOR XML PATH('vmApplicationUser'), TYPE),
									(SELECT @vmSSHKeyFilePath AS '@sshKeyFilePath',
											@useSSHKey AS '@useSSHKey'
									FOR XML PATH('sshKey'),TYPE)
								FOR XML PATH('snapCopyInfo'),TYPE),
								(SELECT @associatedSnapShotSCId AS '@subclientId',
										@associatedSnapShotSCName AS '@subclientName',
										7 AS '@_type_'
								FOR XML PATH('associatedSnapShotSCId'),TYPE),
								(SELECT @subclientSecurity AS 'security'
								FOR XML PATH(''),TYPE),
								(SELECT @subclientSecurityNew
								FOR XML PATH('securityAssociations'),TYPE),
								@dbDumpConfig,
								@timezone,
								(CASE WHEN @idxOptsSupported = 1 THEN
										(SELECT @pruningEnabled AS '@isPruningEnabled',
										 BackupSetListTBL.indexPruning AS '@overrideIndexPruning',
										 BackupSetListTBL.retDays AS '@indexRetDays',
										 BackupSetListTBL.retCycles AS '@indexRetCycle',
										 BackupSetListTBL.pruningType AS '@indexPruningType',
										 BackupSetListTBL.curIdxServer.query('/currentIndexServer'),
										 BackupSetListTBL.indexMAs.query('/indexMAs/qualifyingIndexServers'),
 										 BackupSetListTBL.indexBkpSp.query('/currentIndexBackupSP'),
										 BackupSetListTBL.indexBkpEligibleSPs.query('/indexBackupSPs/eligibleIndexBackupSP')
										 FROM #idxOpts BackupSetListTBL
										 FOR XML PATH ('indexSettings'), TYPE) END),
								(SELECT @guestCredentialsID as '@credentialId', @guestCredentialsName as '@credentialName' FOR XML PATH('guestCredentialinfo'), TYPE),
								(SELECT @uncCredentialsID as '@credentialId', @uncCredentialsName as '@credentialName' FOR XML PATH('UNCScriptCredentialinfo'), TYPE),
								(SELECT @appValidation as '@isAppValidationEnabled',
										@backupValidationTaskId as '@taskId',
										@backupValidationSubTaskId as '@subTaskId',
										@useSourceESXMount as '@useSourceVMEsxToMount',
										@runAsDevTestGroup as '@runAsDevTestGroup',
										@backupValidationThreadCount as '@backupValidationThreadCount',
										@isCustomAppSet as '@isCustomAppEnabled',
										(CASE WHEN @isCustomAppSet = 1 THEN (SELECT @customAppXML) END),
										(CASE WHEN @useSourceESXMount = 0 THEN
											(SELECT vxml.value('/policy[1]/entity[1]/@vmAllocPolicyId', 'int') AS '@vmAllocPolicyId',
													vxml.value('/policy[1]/entity[1]/@vmAllocPolicyName', 'nvarchar(max)') AS '@vmAllocPolicyName',
													vxml.value('/policy[1]/entity[1]/@policyType', 'int') AS '@policyType'
													FROM App_VmAllocationPolicy where id = @vmAllocPolicyID
												FOR XML PATH('vmallocPolicy'),TYPE) END),
										(SELECT @uncScriptCredentialUsername as '@userName' FOR XML PATH('uncScriptImpersonateUser'), TYPE)
								FOR XML PATH('vmBackupValidationSettings'),TYPE)
						FOR XML PATH('commonProperties'),TYPE)
				FOR XML PATH('subclientCommonProperties'),TYPE)
SET @xmlResult.modify('delete /subclientCommonProperties/commonProperties/prepostProcess[empty(./*) and not(./@*)]')
SET @xmlResult.modify('delete /subclientCommonProperties/commonProperties/storageDevice/deDuplicationOptions[empty(./*) and not(./@*)]')
SET @xmlResult.modify('delete /subclientCommonProperties/commonProperties/storageDevice[empty(./*) and not(./@*)]')
SET @xmlResult.modify('delete /subclientCommonProperties/commonProperties/snapCopyInfo[empty(./*) and not(./@*)]')
SET @xmlResult.modify('delete /subclientCommonProperties/commonProperties/security[empty(./*) and not(./@*)]')
SET @xmlResult.modify('delete /subclientCommonProperties/commonProperties/securityAssociations[empty(./*) and not(./@*)]')
SET @xmlResult.modify('delete /subclientCommonProperties/commonProperties[empty(./*) and not(./@*)]')
GO

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

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

insert into GXDBVersions values(2, 'APPGetSubclientCommonProperties',  '00000000000000000000', 'APPGetSubclientCommonProperties', '00000000000000000000')
GO

