

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/TblScript/APP_CCSXMLMapping.script] ---------- 


SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

-- ALLOW_COMMENTS_BELOW_HERE:

-- ----------------------------------------------------------------------

--                                                                      

--           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/TblScript/APP_CCSXMLMapping.script,v $ $Id: APP_CCSXMLMapping.script,v 1.12.2.23.14.1 2021/06/25 22:51:11 abilbrey Exp $';

--TABLENAME APP_CCSXMLMapping

-- V11.60 - for use where table rows contain invalid characters that cannot be encoded into XML directly and 

-- require an override column schema to perform the invalid character encoding first

-- Macro for adding Include and Exclude Filters to a CCS Table

-- Macro for updating Insert orderering

SET  NOCOUNT ON

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 0)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'UNKNOWN',								
				primaryId = '',								
				primaryName = '',							
				xmlNodeName = 'UNKNOWN',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 0													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(0, 'UNKNOWN', '', '', 'UNKNOWN', '', '', '', '')	
	GO

--				TableType	TableName				PrimaryId		PrimaryName		XMLNodeName						CPKId1Name		CPKId2Name		ColumnSpec

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 1)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_Client',								
				primaryId = 'id',								
				primaryName = 'name',							
				xmlNodeName = 'clientTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 1													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(1, 'APP_Client', 'id', 'name', 'clientTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 2)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_ClientProp',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'clientPropTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId'							
		WHERE id = 2													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(2, 'APP_ClientProp', 'id', '', 'clientPropTableRowList', '', '', 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 3)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_Platform',								
				primaryId = '',								
				primaryName = '',							
				xmlNodeName = 'platformTableRowList',							
				cpkId1Name = 'clientId',								
				cpkId2Name = 'platformType'								
		WHERE id = 3													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(3, 'APP_Platform', '', '', 'platformTableRowList', 'clientId', 'platformType', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 4)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_Application',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'applicationTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 4													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(4, 'APP_Application', 'id', '', 'applicationTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 5)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_SubClientProp',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'subClientPropTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId, cs_attrName'							
		WHERE id = 5													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(5, 'APP_SubClientProp', 'id', '', 'subClientPropTableRowList', '', '', 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId, cs_attrName', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 6)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_IDAName',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'idaNameTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 6													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(6, 'APP_IDAName', 'id', '', 'idaNameTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 7)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'GXGlobalParam',								
				primaryId = 'id',								
				primaryName = 'name',							
				xmlNodeName = 'gxGlobalParamTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 7													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(7, 'GXGlobalParam', 'id', 'name', 'gxGlobalParamTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 8)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_iDAType',								
				primaryId = 'type',								
				primaryName = 'name',							
				xmlNodeName = 'idaTypeTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 8													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(8, 'APP_iDAType', 'type', 'name', 'idaTypeTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 9)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'SchedTimeZone',								
				primaryId = 'TimeZoneID',								
				primaryName = 'TimeZoneStdName',							
				xmlNodeName = 'schedTimeZoneTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 9													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(9, 'SchedTimeZone', 'TimeZoneID', 'TimeZoneStdName', 'schedTimeZoneTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 10)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_IDAProp',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'idaPropTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId'							
		WHERE id = 10													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(10, 'APP_IDAProp', 'id', '', 'idaPropTableRowList', '', '', 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 11)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_BackupSetName',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'backupSetNameTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 11													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(11, 'APP_BackupSetName', 'id', '', 'backupSetNameTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 12)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_BackupSetProp',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'backupSetPropTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId'							
		WHERE id = 12													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(12, 'APP_BackupSetProp', 'id', '', 'backupSetPropTableRowList', '', '', 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 13)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_InstanceName',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'instanceNameTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'id, dbo.EncodeInvalidXMLChar(name) name, refTime, modified, status, ccpId, ccpTime, origCCId, GUID'							
		WHERE id = 13													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(13, 'APP_InstanceName', 'id', '', 'instanceNameTableRowList', '', '', 'id, dbo.EncodeInvalidXMLChar(name) name, refTime, modified, status, ccpId, ccpTime, origCCId, GUID', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 14)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_InstanceProp',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'instancePropTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId'							
		WHERE id = 14													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(14, 'APP_InstanceProp', 'id', '', 'instancePropTableRowList', '', '', 'id, componentNameId, dbo.EncodeInvalidXMLChar(attrName) attrName, attrType, dbo.EncodeInvalidXMLChar(attrVal) attrVal, created, modified, ccpId', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 15)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_BackupSetFilterFile',								
				primaryId = 'componentNameId',								
				primaryName = '',							
				xmlNodeName = 'backupSetFilterFileTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 15													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(15, 'APP_BackupSetFilterFile', 'componentNameId', '', 'backupSetFilterFileTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 16)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_InstFilterFile',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'instFilterFileTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'componentNameId, type, dbo.EncodeInvalidXMLChar(fileName) fileName, created, modified, id'							
		WHERE id = 16													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(16, 'APP_InstFilterFile', 'id', '', 'instFilterFileTableRowList', '', '', 'componentNameId, type, dbo.EncodeInvalidXMLChar(fileName) fileName, created, modified, id', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 17)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_ScFilterFile',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'scFilterFileTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'id, componentNameId, type, dbo.EncodeInvalidXMLChar(fileName) fileName, created, modified, ccpId, cs_fileName'							
		WHERE id = 17													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(17, 'APP_ScFilterFile', 'id', '', 'scFilterFileTableRowList', '', '', 'id, componentNameId, type, dbo.EncodeInvalidXMLChar(fileName) fileName, created, modified, ccpId, cs_fileName', '')	
	GO

-- APP_Commcell::encNumber column can have illegal XML Characters

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 18)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_CommCell',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'commCellTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'id, number, clientId, parentId, aliasName, flags, refTime, userName, userPassword, smtpServer, timeZone, ipAddress, modified, ccOpMode, dripAddress,  dbo.EncodeInvalidXMLChar(encNumber) encNumber, csGUID, type'							
		WHERE id = 18													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(18, 'APP_CommCell', 'id', '', 'commCellTableRowList', '', '', 'id, number, clientId, parentId, aliasName, flags, refTime, userName, userPassword, smtpServer, timeZone, ipAddress, modified, ccOpMode, dripAddress,  dbo.EncodeInvalidXMLChar(encNumber) encNumber, csGUID, type', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 19)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'App_FileTypeAndExtensions',								
				primaryId = '',								
				primaryName = '',							
				xmlNodeName = 'fileTypeAndExtensionsTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 19													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(19, 'App_FileTypeAndExtensions', '', '', 'fileTypeAndExtensionsTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 20)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_ComponentProp',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'componentPropTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 20													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(20, 'APP_ComponentProp', 'id', '', 'componentPropTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 21)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_ExtendedProperties',								
				primaryId = '',								
				primaryName = '',							
				xmlNodeName = 'extendedPropertiesTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'clientId, appTypeId, instance, backupSet, subclientId, attrType, dbo.EncodeInvalidXMLChar(attrName) attrName, dbo.EncodeInvalidXMLChar(attrVal) attrVal, MD5, created, modified, ccpId'							
		WHERE id = 21													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(21, 'APP_ExtendedProperties', '', '', 'extendedPropertiesTableRowList', '', '', 'clientId, appTypeId, instance, backupSet, subclientId, attrType, dbo.EncodeInvalidXMLChar(attrName) attrName, dbo.EncodeInvalidXMLChar(attrVal) attrVal, MD5, created, modified, ccpId', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 22)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'JMBkpStats',								
				primaryId = '',								
				primaryName = '',							
				xmlNodeName = 'jmBkpStatsTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 22													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(22, 'JMBkpStats', '', '', 'jmBkpStatsTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 23)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_AppTypeGroup',								
				primaryId = '',								
				primaryName = '',							
				xmlNodeName = 'appTypeGroupTableRowList',							
				cpkId1Name = 'typeOfGroup',								
				cpkId2Name = 'appTypeGroupId'								
		WHERE id = 23													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(23, 'APP_AppTypeGroup', '', '', 'appTypeGroupTableRowList', 'typeOfGroup', 'appTypeGroupId', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 24)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_AppTypeGroupAssoc',								
				primaryId = '',								
				primaryName = '',							
				xmlNodeName = 'appTypeGroupAssocTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = ''											
		WHERE id = 24													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(24, 'APP_AppTypeGroupAssoc', '', '', 'appTypeGroupAssocTableRowList', '', '', '', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 25)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_JobErrorDecisionRule',								
				primaryId = 'id',								
				primaryName = '',							
				xmlNodeName = 'jobErrorDecisionRuleTableRowList',							
				cpkId1Name = '',										
				cpkId2Name = '',										
				columnSchema = 'id, EntityId, EntityType, AppEntityType, AppTypeId, isEnabled, CAST(decisionRule AS NVARCHAR(MAX)) decisionRule'							
		WHERE id = 25													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(25, 'APP_JobErrorDecisionRule', 'id', '', 'jobErrorDecisionRuleTableRowList', '', '', 'id, EntityId, EntityType, AppEntityType, AppTypeId, isEnabled, CAST(decisionRule AS NVARCHAR(MAX)) decisionRule', '')	
	GO

IF EXISTS (Select * from APP_CCSXMLMapping WHERE id = 26)			
		UPDATE APP_CCSXMLMapping										
			SET tableName = 'APP_ClientGroupAssoc',								
				primaryId = '',								
				primaryName = '',							
				xmlNodeName = 'clientGroupAssocTableRowList',							
				cpkId1Name = 'clientGroupId',								
				cpkId2Name = 'clientId'								
		WHERE id = 26													
	ELSE							
		INSERT APP_CCSXMLMapping (id, tableName, primaryId, primaryName, xmlNodeName, cpkId1Name, cpkId2Name, columnSchema, filters)	
			VALUES(26, 'APP_ClientGroupAssoc', '', '', 'clientGroupAssocTableRowList', 'clientGroupId', 'clientId', '', '')	
	GO

--===============================================================================================

-- Now add filters to CCS Tables that require them

-- NOTE: Filtering is hardcoded implementations in some CCS SPs, so verify when adding new filters or nodes below.

-- Code to check:

--		APPCCSCreateClientLoadXMLDocument.sp

--		AppCCSTrigerToWorkQueue.sp

--		AppGetCCSXMLTableRowOutput.sp

--		AppCCSAssocaitedSubClientPolicyRows.sp

--		CCS_Md5TableHash.cpp

--		CCS Trigger template files???

--

-- NOTE: Filtering is hardcoded implementations in some CCS SPs, so verify when adding new nodes.

--

-- Changes for filter readability

DECLARE @xmlFilter NVARCHAR(MAX)

SET @xmlFilter = '

<Filters>

	<Column name="attrName" dataType="NSTRING" opType="EXCLUDE">

		<Filter value="CCS Reload Attempts" />

		<Filter value="Client IP Addresses" />

		<Filter value="SP Version and Patch Info" />

		<Filter value="Client Geo Location" />

		<Filter value="CCSDB Last Init Load Success" />

		<Filter value="Next Run Time In" />

		<Filter value="Last offline time" />

	</Column>

</Filters>

'

UPDATE APP_CCSXMLMapping								
			SET filters = @xmlFilter								
		WHERE id = 2 AND CAST(filters AS NVARCHAR(MAX)) <> @xmlFilter

SET @xmlFilter = '

<Filters>

	<Column name="attrName" dataType="NSTRING" opType="EXCLUDE">

		<Filter value="Application Size" />

		<Filter value="Last Backup Job Info" />

		<Filter value="SynthFullFinishPending" />

		<Filter value="Verify Synthetic Full" />

		<Filter value="Last Backup Start Time" />

		<Filter value="Compute Dynamic priority" />

		<Filter value="RunSFImmediately" />

		<Filter value="Last Backup Size" />

		<Filter value="Consecutive Backup Failures Count" />

		<Filter value="sys:full cycle num" />

		<Filter value="sys:jobIds updated" />

		<Filter value="sys:last full" />

		<Filter value="sys:last full jobId" />

		<Filter value="sys:last incr" />

		<Filter value="sys:last incr jobId" />

		<Filter value="sys:last synthFull jobId" />

		<Filter value="sys:non full cycle num" />

		<Filter value="sys: pre post option" />

		<Filter value="turbo:last full" />

		<Filter value="turbo:last incr" />

	</Column>

	<Column name="attrName" dataType="NSTRING" opType="NOT LIKE">

		<Filter value="Estimated Run Time%" />

		<Filter value="Anomalous Run Time%" />

	</Column>

</Filters>

'

UPDATE APP_CCSXMLMapping								
			SET filters = @xmlFilter								
		WHERE id = 5 AND CAST(filters AS NVARCHAR(MAX)) <> @xmlFilter

SET @xmlFilter = '

<Filters>

	<Column name="name" dataType="NSTRING" opType="INCLUDE">

		<Filter value="AllowSubclientLocalContent" />

		<Filter value="CSFirstReleaseId" />

		<Filter value="DisableGeoLocation" />

		<Filter value="Windows FS Exclude Filters" />

		<Filter value="Use Global Filters for all Subclients" />

	</Column>

</Filters>

'

UPDATE APP_CCSXMLMapping								
			SET filters = @xmlFilter								
		WHERE id = 7 AND CAST(filters AS NVARCHAR(MAX)) <> @xmlFilter

SET @xmlFilter = '

<Filters>

	<Column name="attrName" dataType="NSTRING" opType="EXCLUDE">

		<Filter value="Last Backup Job Info" />

	</Column>

</Filters>

'

UPDATE APP_CCSXMLMapping								
			SET filters = @xmlFilter								
		WHERE id = 10 AND CAST(filters AS NVARCHAR(MAX)) <> @xmlFilter

-- Removed duplicate "Application Size" filter node

SET @xmlFilter = '

<Filters>

	<Column name="attrName" dataType="NSTRING" opType="EXCLUDE">

		<Filter value="Pre 7.0 System State Archive Files Exist" />

		<Filter value="File System Quota Usage" />

		<Filter value="Application Size" />

	</Column>

</Filters>

'

UPDATE APP_CCSXMLMapping								
			SET filters = @xmlFilter								
		WHERE id = 12 AND CAST(filters AS NVARCHAR(MAX)) <> @xmlFilter

SET @xmlFilter = '

<Filters>

	<Column name="id" dataType="INTEGER" opType="INCLUDE">

		<Filter value="1" />

		<Filter value="2" />

	</Column>

</Filters>

'

UPDATE APP_CCSXMLMapping								
			SET filters = @xmlFilter								
		WHERE id = 18 AND CAST(filters AS NVARCHAR(MAX)) <> @xmlFilter

SET @xmlFilter = '

<Filters>

	<Column name="appTypeGroupId" dataType="INT" opType="INCLUDE" SQLCMD="1">

		<Filter value="SELECT DISTINCT atga.appGroupId FROM APP_AppTypeGroupAssoc atga WITH(NOLOCK) WHERE atga.appTypeId IN (33,63)" />

	</Column>

</Filters>

'

UPDATE APP_CCSXMLMapping								
			SET filters = @xmlFilter								
		WHERE id = 23 AND CAST(filters AS NVARCHAR(MAX)) <> @xmlFilter

SET @xmlFilter = '

<Filters>

	<Column name="appTypeId" dataType="INT" opType="INCLUDE">

		<Filter value="33" />

		<Filter value="63" />

	</Column>

</Filters>

'

UPDATE APP_CCSXMLMapping								
			SET filters = @xmlFilter								
		WHERE id = 24 AND CAST(filters AS NVARCHAR(MAX)) <> @xmlFilter

--CV_COMPONENT_CLIENT_IP_ADDRESSES	2102

--CV_COMPONENT_CLIENT_HOSTNAMES		2103

--CV_COMPONENT_PROP_INSTALL_HISTORY	3624

--CV_COMPONENT_PROP_SLA_PREDICTIO	3625

SET @xmlFilter = '

<Filters>

	<Column name="propertyTypeId" dataType="INT" opType="EXCLUDE">

		<Filter value="2102" />

		<Filter value="2103" />

		<Filter value="3624" />

		<Filter value="3625" />

	</Column>

</Filters>

'

UPDATE APP_CCSXMLMapping								
			SET filters = @xmlFilter								
		WHERE id = 20 AND CAST(filters AS NVARCHAR(MAX)) <> @xmlFilter

--==========================================================================================

-- Now update Insertion Ordering

-- Level 0 Ordering - no FK to other tables

UPDATE APP_CCSXMLMapping								
			SET ordering = 0							
		WHERE id = 1 AND ordering <> 0		-- APP_Client

UPDATE APP_CCSXMLMapping								
			SET ordering = 0							
		WHERE id = 7 AND ordering <> 0		-- GXGlobalParam

UPDATE APP_CCSXMLMapping								
			SET ordering = 0							
		WHERE id = 8 AND ordering <> 0		-- APP_iDAType

UPDATE APP_CCSXMLMapping								
			SET ordering = 0							
		WHERE id = 9 AND ordering <> 0		-- SchedTimeZone

UPDATE APP_CCSXMLMapping								
			SET ordering = 0							
		WHERE id = 11 AND ordering <> 0		-- APP_BackupSetName

UPDATE APP_CCSXMLMapping								
			SET ordering = 0							
		WHERE id = 13 AND ordering <> 0		-- APP_InstanceName

UPDATE APP_CCSXMLMapping								
			SET ordering = 0							
		WHERE id = 18 AND ordering <> 0		-- APP_CommCell

UPDATE APP_CCSXMLMapping								
			SET ordering = 0							
		WHERE id = 19 AND ordering <> 0		-- App_FileTypeAndExtensions

-- Level 50 Ordering - FK on tables with lower ordering

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 2 AND ordering <> 50		-- APP_ClientProp

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 3 AND ordering <> 50		-- APP_Platform

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 4 AND ordering <> 50		-- APP_Application

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 6 AND ordering <> 50		-- APP_IDAName

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 12 AND ordering <> 50		-- APP_BackupSetProp

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 14 AND ordering <> 50		-- APP_InstanceProp

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 15 AND ordering <> 50		-- APP_BackupSetFilterFile

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 16 AND ordering <> 50		-- APP_InstFilterFile

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 23 AND ordering <> 50		-- APP_AppTypeGroup

UPDATE APP_CCSXMLMapping								
			SET ordering = 50							
		WHERE id = 26 AND ordering <> 50		-- APP_ClientGroupAssoc

-- Level 100 Ordering - FK on tables with lower ordering

UPDATE APP_CCSXMLMapping								
			SET ordering = 100							
		WHERE id = 5 AND ordering <> 100		-- APP_SubClientProp

UPDATE APP_CCSXMLMapping								
			SET ordering = 100							
		WHERE id = 10 AND ordering <> 100		-- APP_IDAProp

UPDATE APP_CCSXMLMapping								
			SET ordering = 100							
		WHERE id = 17 AND ordering <> 100		-- APP_ScFilterFile

UPDATE APP_CCSXMLMapping								
			SET ordering = 100							
		WHERE id = 20 AND ordering <> 100		-- APP_ComponentProp

UPDATE APP_CCSXMLMapping								
			SET ordering = 100							
		WHERE id = 21 AND ordering <> 100		-- APP_ExtendedProperties

UPDATE APP_CCSXMLMapping								
			SET ordering = 100							
		WHERE id = 24 AND ordering <> 100		-- APP_AppTypeGroupAssoc

-- Level 500 Ordering - FK on tables with lower ordering

-- These tables are possibly indirectly needed by CCSDb but are only populated by remote queries if needed.

UPDATE APP_CCSXMLMapping								
			SET ordering = 500							
		WHERE id = 22 AND ordering <> 500		-- JMBkpStats

UPDATE APP_CCSXMLMapping								
			SET ordering = 500							
		WHERE id = 25 AND ordering <> 500		-- APP_JobErrorDecisionRule

--==========================================================================================

-- Now update MD5 Hash Query

-- NOTE: need a new GUID if any of the MD5 Hash Queries updated in a Service Pack!

--

-- SP21 ALL Full Reset for cleanup for change comments from -- to /*...*/ in dynamic code

DECLARE @ClearMD5QueryGUID	VARCHAR(40) = '218374FE-E799-4DD6-B94C-E84FF50D8A5E'	--'AD087079-8494-4303-B7A4-A2E1D8DE7559', '65EE961A-9214-41D2-AECC-C1346078D60B', 'AF7F60EE-718B-43F1-9BD4-5085A8A7D5D8'

DECLARE @ClearMD5QueryTime	INT =  DATEDIFF(second, '01/01/1970', GETUTCDATE())

IF NOT EXISTS (SELECT 1 FROM GXUniqueSQLScriptExecTracking WHERE SqlScriptGUID = @ClearMD5QueryGUID)

BEGIN

	-- Clear all rows of MD5 Queries so that they can be reloaded

	UPDATE APP_CCSXMLMapping

		SET md5Query = N''

	WHERE

		md5Query <> N''
	INSERT INTO GXUniqueSQLScriptExecTracking(SqlScriptGUID, Name, Description, CreatedTime, CreatedRelId, ExpireTime, ExpireRelId)


VALUES (@ClearMD5QueryGUID, 'CCS MD5 Query Reset for Reload', 'Update script to retrofit CCS MD5 Queries', @ClearMD5QueryTime, 16, 0, (16 + 2))

END

ELSE

BEGIN

-- Performance change

-- NOT reseting ALL so check individual resets

	-- For reseting just an individual MD5 Hash Query

	DECLARE @IndClearMD5Query4GUID	VARCHAR(40) = 'CCCE55AD-128B-4A15-8E2C-8AFF953BD676'

	IF NOT EXISTS (SELECT 1 FROM GXUniqueSQLScriptExecTracking WHERE SqlScriptGUID = @IndClearMD5Query4GUID)

	BEGIN

		-- Clear individual table of MD5 Query so that they can be reloaded

		UPDATE APP_CCSXMLMapping

			SET md5Query = N''

		WHERE

			md5Query <> N''

			AND id = 4
		INSERT INTO GXUniqueSQLScriptExecTracking(SqlScriptGUID, Name, Description, CreatedTime, CreatedRelId, ExpireTime, ExpireRelId)


VALUES (@IndClearMD5Query4GUID, 'CCS MD5 Query Reset for Reload Table 4', 'Update script to retrofit CCS Table 4 MD5 Query', @ClearMD5QueryTime, 16, 0, (16 + 2))

	END

	-- Hash fixes to support CCS Forced ON Agents

	DECLARE @IndClearMD5Query6GUID	VARCHAR(40) = '92AFC30E-5743-484F-8312-005982032A03'

	IF NOT EXISTS (SELECT 1 FROM GXUniqueSQLScriptExecTracking WHERE SqlScriptGUID = @IndClearMD5Query6GUID)

	BEGIN

		-- Clear individual table of MD5 Query so that they can be reloaded

		UPDATE APP_CCSXMLMapping

			SET md5Query = N''

		WHERE

			md5Query <> N''

			AND id = 6
		INSERT INTO GXUniqueSQLScriptExecTracking(SqlScriptGUID, Name, Description, CreatedTime, CreatedRelId, ExpireTime, ExpireRelId)


VALUES (@IndClearMD5Query6GUID, 'CCS MD5 Query Reset for Reload Table 6', 'Update script to retrofit CCS Table 6 MD5 Query', @ClearMD5QueryTime, 16, 0, (16 + 2))

	END

	DECLARE @IndClearMD5Query8GUID	VARCHAR(40) = 'E007B709-23C2-407E-9780-4CA57DFCE99F'		--'269D9900-1B15-4F82-8267-F7599266A189'

	IF NOT EXISTS (SELECT 1 FROM GXUniqueSQLScriptExecTracking WHERE SqlScriptGUID = @IndClearMD5Query8GUID)

	BEGIN

		-- Clear individual table of MD5 Query so that they can be reloaded

		UPDATE APP_CCSXMLMapping

			SET md5Query = N''

		WHERE

			md5Query <> N''

			AND id = 8
		INSERT INTO GXUniqueSQLScriptExecTracking(SqlScriptGUID, Name, Description, CreatedTime, CreatedRelId, ExpireTime, ExpireRelId)


VALUES (@IndClearMD5Query8GUID, 'CCS MD5 Query Reset for Reload Table 8', 'Update script to retrofit CCS Table 8 MD5 Query', @ClearMD5QueryTime, 16, 0, (16 + 2))

	END

	DECLARE @IndClearMD5Query10GUID	VARCHAR(40) = 'D6DA2F61-6712-4DE1-A01D-C218570E5B41'

	IF NOT EXISTS (SELECT 1 FROM GXUniqueSQLScriptExecTracking WHERE SqlScriptGUID = @IndClearMD5Query10GUID)

	BEGIN

		-- Clear individual table of MD5 Query so that they can be reloaded

		UPDATE APP_CCSXMLMapping

			SET md5Query = N''

		WHERE

			md5Query <> N''

			AND id = 10
		INSERT INTO GXUniqueSQLScriptExecTracking(SqlScriptGUID, Name, Description, CreatedTime, CreatedRelId, ExpireTime, ExpireRelId)


VALUES (@IndClearMD5Query10GUID, 'CCS MD5 Query Reset for Reload Table 10', 'Update script to retrofit CCS Table 10 MD5 Query', @ClearMD5QueryTime, 16, 0, (16 + 2))

	END

	DECLARE @IndClearMD5Query20GUID	VARCHAR(40) = 'FB189785-6B56-4093-A0CF-F3400DCFAF55'		--'D13FB13D-F966-43F4-9557-43D4CBCBE6A6'

	IF NOT EXISTS (SELECT 1 FROM GXUniqueSQLScriptExecTracking WHERE SqlScriptGUID = @IndClearMD5Query20GUID)

	BEGIN

		-- Clear individual table of MD5 Query so that they can be reloaded

		UPDATE APP_CCSXMLMapping

			SET md5Query = N''

		WHERE

			md5Query <> N''

			AND id = 20
		INSERT INTO GXUniqueSQLScriptExecTracking(SqlScriptGUID, Name, Description, CreatedTime, CreatedRelId, ExpireTime, ExpireRelId)


VALUES (@IndClearMD5Query20GUID, 'CCS MD5 Query Reset for Reload Table 20', 'Update script to retrofit CCS Table 20 MD5 Query', @ClearMD5QueryTime, 16, 0, (16 + 2))

	END

	DECLARE @IndClearMD5Query21GUID	VARCHAR(40) = '172A00A8-CE0C-4611-99CF-A9ED7509B904'

	IF NOT EXISTS (SELECT 1 FROM GXUniqueSQLScriptExecTracking WHERE SqlScriptGUID = @IndClearMD5Query21GUID)

	BEGIN

		-- Clear individual table of MD5 Query so that they can be reloaded

		UPDATE APP_CCSXMLMapping

			SET md5Query = N''

		WHERE

			md5Query <> N''

			AND id = 21
		INSERT INTO GXUniqueSQLScriptExecTracking(SqlScriptGUID, Name, Description, CreatedTime, CreatedRelId, ExpireTime, ExpireRelId)


VALUES (@IndClearMD5Query21GUID, 'CCS MD5 Query Reset for Reload Table 21', 'Update script to retrofit CCS Table 21 MD5 Query', @ClearMD5QueryTime, 16, 0, (16 + 2))

	END

END

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT

		CAST(id AS NVARCHAR(MAX))

		+ CAST(name AS NVARCHAR(MAX))

		+ CAST(simOperatingSystemId AS NVARCHAR(MAX))

		+ CAST(net_hostname AS NVARCHAR(MAX))

		+ CAST(client_password AS NVARCHAR(MAX))

		+ CAST(refTime AS NVARCHAR(MAX))

		+ CAST(modified AS NVARCHAR(MAX))

		+ CAST(offlineEntity AS NVARCHAR(MAX))

		+ CAST(offlineTimeStamp AS NVARCHAR(MAX))

		+ CAST(csHostName AS NVARCHAR(MAX))

		+ CAST(clientDescription AS NVARCHAR(MAX))

		+ CAST(jobResultDir AS NVARCHAR(MAX))

		+ CAST(clientSkewTime AS NVARCHAR(MAX))

		+ CAST(cvdPort AS NVARCHAR(MAX))

		+ CAST(evmgrcPort AS NVARCHAR(MAX))

		+ CAST(status AS NVARCHAR(MAX))

		+ CAST(releaseId AS NVARCHAR(MAX))

		+ CAST(spaceCheckFlags AS NVARCHAR(MAX))

		+ CAST(ccpId AS NVARCHAR(MAX))

		+ CAST(ccpTime AS NVARCHAR(MAX))

		+ CAST(origCCId AS NVARCHAR(MAX))

		+ CAST(GUID AS NVARCHAR(MAX))

		+ CAST(specialClientFlags AS NVARCHAR(MAX))

		+ CAST(displayName AS NVARCHAR(MAX))

	FROM APP_Client  WITH(NOLOCK)

	WHERE id  IN (1, 2, @inClientId)

	ORDER BY id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 1 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(id AS NVARCHAR(MAX))

		+ CAST(componentNameId AS NVARCHAR(MAX))

		+ CAST(attrName AS NVARCHAR(MAX))

		+ CAST(attrType AS NVARCHAR(MAX))

		+ CAST(attrVal AS NVARCHAR(MAX))

		+ CAST(created AS NVARCHAR(MAX))

		+ CAST(modified AS NVARCHAR(MAX))

		+ CAST(ccpId AS NVARCHAR(MAX)) 

	FROM APP_ClientProp WITH(NOLOCK)

	WHERE componentNameId = @inClientId

		AND attrName NOT IN (

			SELECT

				f.value(''@value'', ''NVARCHAR(128)'') filter

			FROM APP_CCSXMLMapping WITH(NOLOCK)

				CROSS APPLY filters.nodes(''/Filters/Column/Filter'') n(f)

			WHERE

				id = 2

				AND f.value(''../@name'', ''NVARCHAR(128)'') = ''attrName''

				AND f.value(''../@opType'', ''NVARCHAR(128)'') = ''EXCLUDE''

		)

	ORDER BY id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 2 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(clientId AS NVARCHAR(MAX))

		+ CAST(platformType AS NVARCHAR(MAX))

		+ CAST(install_dir AS NVARCHAR(MAX))

		+ CAST(refTime AS NVARCHAR(MAX))

	FROM APP_Platform  WITH(NOLOCK)

	WHERE clientId IN (2,  @inClientId)

	ORDER BY clientId ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 3 AND md5Query = N''

-- SQL Server went brain dead in the subquery below and returned the wrong result set row that overflowed an integer CAST operation.

-- First time seen this issue happen during a result processing, but have seen cases in WHERE Clause conditions and have been correcting during CRs.

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(a.id AS NVARCHAR(MAX))

		+ CAST(a.clientId AS NVARCHAR(MAX))

		+ CAST(a.appTypeId AS NVARCHAR(MAX))

		+ CAST(a.instance AS NVARCHAR(MAX))

		+ CAST(a.backupSet AS NVARCHAR(MAX))

		+ CAST(a.appNumber AS NVARCHAR(MAX))

		+ CAST(a.dataArchGrpID AS NVARCHAR(MAX))

		+ CAST(a.logArchGrpID AS NVARCHAR(MAX))

		+ CAST(a.refTime AS NVARCHAR(MAX))

		+ CAST(a.modified AS NVARCHAR(MAX))

		+ CAST(a.subclientName AS NVARCHAR(MAX))

		+ CAST(a.subclientStatus AS NVARCHAR(MAX))

		+ CAST(a.ccpId AS NVARCHAR(MAX))

		+ CAST(a.ccpTime AS NVARCHAR(MAX))

		+ CAST(a.origCCId AS NVARCHAR(MAX))

		+ CAST(a.GUID AS NVARCHAR(MAX))

	FROM APP_Application a WITH(NOLOCK)

	WHERE 

		a.clientId = @inClientId

		OR (

			a.clientId = 2

			AND a.appTypeId = 1030

			AND CAST(a.id AS NVARCHAR(12)) IN (

				SELECT 

					cp.attrVal

				FROM APP_Application a WITH(NOLOCK)

					INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

						a.clientId = @inClientId

						AND cp.componentNameId = a.id

						AND cp.attrName = N''Associated subclient Policy''

						AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

						AND ISNUMERIC(cp.attrVal) = 1

			)

		)

	ORDER BY a.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 4 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

IF OBJECT_ID(''tempdb.dbo.#SCPNotLikeIds'') IS NOT NULL

	DROP TABLE #SCPNotLikeIds

CREATE TABLE #SCPNotLikeIds (

	id		INT PRIMARY KEY

)

DECLARE @scpIdCmd NVARCHAR(MAX) = ''INSERT INTO #SCPNotLikeIds(id)  '' + (

	SELECT

		CASE WHEN q.rn > 1 THEN '' UNION '' ELSE '' '' END

		+ ''SELECT cp.id FROM APP_SubClientProp cp WITH(NOLOCK) INNER JOIN APP_Application a WITH(NOLOCK) ON a.clientId = '' + CAST(@inClientId AS NVARCHAR(12)) + '' AND cp.componentNameId = a.id WHERE cp.attrName LIKE '' + '''''''' + q.filter + ''''''''

	FROM (

			SELECT

				f.value(''@value'', ''NVARCHAR(128)'') filter,

				ROW_NUMBER() OVER (ORDER BY f.value(''@value'', ''NVARCHAR(1024)'')) rn

			FROM APP_CCSXMLMapping WITH(NOLOCK)

				CROSS APPLY filters.nodes(''/Filters/Column/Filter'') n(f)

			WHERE

				id = 5

				AND f.value(''../@name'', ''NVARCHAR(128)'') = ''attrName''

				AND f.value(''../@opType'', ''NVARCHAR(128)'') = ''NOT LIKE''

		) q

	FOR XML PATH('''')

)

IF (@scpIdCmd IS NOT NULL AND @scpIdCmd <> N'''')

BEGIN

	/*-- Get all SCP matching these filters*/

	EXEC(@scpIdCmd)

END

DECLARE @textNode XML = (

	SELECT 

		CAST(scp.id AS NVARCHAR(MAX))

		+ CAST(scp.componentNameId AS NVARCHAR(MAX))

		+ CAST(scp.attrName AS  NVARCHAR(MAX))

		+ CAST(scp.attrType AS NVARCHAR(MAX))

		+ CAST(scp.attrVal AS NVARCHAR(MAX))

		+ CAST(scp.created AS NVARCHAR(MAX))

		+ CAST(scp.modified AS NVARCHAR(MAX))

		+ CAST(scp.cs_attrName AS NVARCHAR(MAX))

		+ CAST(scp.ccpId AS NVARCHAR(MAX))

	FROM APP_SubClientProp scp WITH(NOLOCK)

		INNER JOIN (

			SELECT

				a.id subClientId

			FROM APP_Application a WITH(NOLOCK)

			WHERE

					a.clientId = @inClientId

			UNION

			SELECT 

				ascp.id subClientId

			FROM APP_Application a WITH(NOLOCK)

				INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND cp.componentNameId = a.id

					AND cp.attrName = N''Associated subclient Policy''

					AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

					AND ISNUMERIC(cp.attrVal) = 1

				INNER JOIN APP_Application ascp WITH(NOLOCK) ON

					ascp.clientId = 2

					AND ascp.appTypeId = 1030

					AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

		) a ON

			scp.componentNameId = a.subClientId

		LEFT OUTER JOIN (

			SELECT

				f.value(''@value'', ''NVARCHAR(128)'') filter

			FROM APP_CCSXMLMapping WITH(NOLOCK)

				CROSS APPLY filters.nodes(''/Filters/Column/Filter'') n(f)

			WHERE

				id = @tt

				AND f.value(''../@name'', ''NVARCHAR(128)'') = ''attrName''

		) d ON

			scp.attrName = d.filter

	WHERE

		d.filter IS NULL

		AND scp.id NOT IN (SELECT id FROM #SCPNotLikeIds)

	ORDER BY scp.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)

IF OBJECT_ID(''tempdb.dbo.#SCPNotLikeIds'') IS NOT NULL

	DROP TABLE #SCPNotLikeIds

'

WHERE id = 5 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(id AS NVARCHAR(MAX))

		+ CAST(clientId AS NVARCHAR(MAX))

		+ CAST(appTypeId AS NVARCHAR(MAX))

		+ CAST(status AS NVARCHAR(MAX))

		+ CAST(refTime AS NVARCHAR(MAX))

		+ CAST(modified AS NVARCHAR(MAX))

		+ CAST(ccpId AS NVARCHAR(MAX))

		+ CAST(ccpTime AS NVARCHAR(MAX))

	FROM APP_IDAName WITH(NOLOCK)

	WHERE

		clientId = @inClientId

	ORDER BY id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 6 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(id AS NVARCHAR(MAX))

		+ CAST(name AS NVARCHAR(MAX))

		+ CAST(value AS NVARCHAR(MAX))

		+ CAST(created AS NVARCHAR(MAX))

		+ CAST(modified AS NVARCHAR(MAX))

	FROM GXGlobalParam gp WITH(NOLOCK)

		INNER JOIN (

			SELECT

				f.value(''@value'', ''NVARCHAR(128)'') filter

			FROM APP_CCSXMLMapping WITH(NOLOCK)

				CROSS APPLY filters.nodes(''/Filters/Column/Filter'') n(f)

			WHERE

				id = @tt

				AND f.value(''../@name'', ''NVARCHAR(128)'') = ''name''

				AND f.value(''../@opType'', ''NVARCHAR(128)'') = ''INCLUDE''

		) d ON

			name = d.filter

	ORDER BY id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 7 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		 CAST(t.type AS NVARCHAR(MAX))

		+ CAST(t.name AS NVARCHAR(MAX))

		+ CAST(t.treeType AS NVARCHAR(MAX))

		+ CAST(t.AppGroup1 AS NVARCHAR(MAX))

		+ CAST(t.priority AS NVARCHAR(MAX))

		+ CAST(t.caseSensitivity AS NVARCHAR(MAX))

		+ CAST(t.dirOrder AS NVARCHAR(MAX))

		+ CAST(t.dirDelim AS NVARCHAR(MAX))

		+ CAST(t.updateRecoSec AS NVARCHAR(MAX))

		+ CAST(t.chunkSizeMB AS NVARCHAR(MAX))

		+ CAST(t.updateIntervalSec AS NVARCHAR(MAX))

		+ CAST(t.ccpType AS NVARCHAR(MAX))

		+ CAST(t.isCWEjobValid AS NVARCHAR(MAX))

		+ CAST(t.displayName AS NVARCHAR(MAX))

		+ CAST(t.indexSpaceSaver AS NVARCHAR(MAX))

	FROM (

			SELECT

				t.*

			FROM APP_iDAType t WITH(NOLOCK)

				INNER JOIN APP_IDAName ida WITH(NOLOCK) ON

					ida.clientId = @inClientId

					AND t.type = ida.appTypeId

			UNION

			SELECT 

				t.*

			FROM APP_iDAType t WITH(NOLOCK)

			WHERE t.type = @inAT1030

		) t

	ORDER BY t.type ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 8 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(p.id AS NVARCHAR(MAX))

		+ CAST(p.componentNameId AS NVARCHAR(MAX))

		+ CAST(p.attrName AS  NVARCHAR(MAX))

		+ CAST(p.attrType AS NVARCHAR(MAX))

		+ CAST(p.attrVal AS NVARCHAR(MAX))

		+ CAST(p.created AS NVARCHAR(MAX))

		+ CAST(p.modified AS NVARCHAR(MAX))

		+ CAST(p.ccpId AS NVARCHAR(MAX))

	FROM APP_IDAProp p WITH(NOLOCK)

		INNER JOIN APP_IDAName n WITH(NOLOCK) ON

			p.componentNameId = n.id

			AND n.clientId = @inClientId

	WHERE

		p.attrName NOT IN (

			SELECT

				f.value(''@value'', ''NVARCHAR(128)'') filter

			FROM APP_CCSXMLMapping WITH(NOLOCK)

				CROSS APPLY filters.nodes(''/Filters/Column/Filter'') n(f)

			WHERE

				id = 10

				AND f.value(''../@name'', ''NVARCHAR(128)'') = ''attrName''

				AND f.value(''../@opType'', ''NVARCHAR(128)'') = ''EXCLUDE''

		)

	ORDER BY p.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 10 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(bs.id AS NVARCHAR(MAX))

		+ CAST(bs.name AS NVARCHAR(MAX))

		+ CAST(bs.refTime AS NVARCHAR(MAX))

		+ CAST(bs.modified AS NVARCHAR(MAX))

		+ CAST(bs.status AS NVARCHAR(MAX))

		+ CAST(bs.ccpId AS NVARCHAR(MAX))

		+ CAST(bs.ccpTime AS NVARCHAR(MAX))

		+ CAST(bs.origCCId AS NVARCHAR(MAX))

		+ CAST(bs.GUID AS NVARCHAR(MAX))

	FROM APP_BackupSetName bs WITH(NOLOCK)

		INNER JOIN (

			SELECT

				bs.id backupSet

			FROM APP_BackupSetName bs WITH(NOLOCK)

				INNER JOIN APP_Application a WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND a.backupSet = bs.id

			UNION

			SELECT 

				ascp.backupSet

			FROM APP_Application a WITH(NOLOCK)

				INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND cp.componentNameId = a.id

					AND cp.attrName = N''Associated subclient Policy''

					AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

					AND ISNUMERIC(cp.attrVal) = 1

				INNER JOIN APP_Application ascp WITH(NOLOCK) ON

					ascp.clientId = 2

					AND ascp.appTypeId = 1030

					AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

		) a ON

			bs.id = a.backupSet

	ORDER BY bs.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 11 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(p.id AS NVARCHAR(MAX))

		+ CAST(p.componentNameId AS NVARCHAR(MAX))

		+ CAST(p.attrName AS  NVARCHAR(MAX))

		+ CAST(p.attrType AS NVARCHAR(MAX))

		+ CAST(p.attrVal AS NVARCHAR(MAX))

		+ CAST(p.created AS NVARCHAR(MAX))

		+ CAST(p.modified AS NVARCHAR(MAX))

		+ CAST(p.ccpId AS NVARCHAR(MAX))

	FROM APP_BackupSetProp p WITH(NOLOCK)

		INNER JOIN (

			SELECT

				bs.id backupSet

			FROM APP_BackupSetName bs WITH(NOLOCK)

				INNER JOIN APP_Application a WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND a.backupSet = bs.id

			UNION

			SELECT 

				ascp.backupSet

			FROM APP_Application a WITH(NOLOCK)

				INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND cp.componentNameId = a.id

					AND cp.attrName = N''Associated subclient Policy''

					AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

					AND ISNUMERIC(cp.attrVal) = 1

				INNER JOIN APP_Application ascp WITH(NOLOCK) ON

					ascp.clientId = 2

					AND ascp.appTypeId = 1030

					AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

		) a ON

			p.componentNameId = a.backupSet

		LEFT OUTER JOIN (

			SELECT

				f.value(''@value'', ''NVARCHAR(128)'') filter

			FROM APP_CCSXMLMapping WITH(NOLOCK)

				CROSS APPLY filters.nodes(''/Filters/Column/Filter'') n(f)

			WHERE

				id = @tt

				AND f.value(''../@name'', ''NVARCHAR(128)'') = ''attrName''

		) d ON

			p.attrName = d.filter

	WHERE

		d.filter IS NULL

	ORDER BY p.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 12 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(i.id AS NVARCHAR(MAX))

		+ CAST(i.name AS NVARCHAR(MAX))

		+ CAST(i.refTime AS NVARCHAR(MAX))

		+ CAST(i.modified AS NVARCHAR(MAX))

		+ CAST(i.status AS NVARCHAR(MAX))

		+ CAST(i.ccpId AS NVARCHAR(MAX))

		+ CAST(i.ccpTime AS NVARCHAR(MAX))

		+ CAST(i.origCCId AS NVARCHAR(MAX))

		+ CAST(i.GUID AS NVARCHAR(MAX))

	FROM APP_InstanceName i WITH(NOLOCK)

		INNER JOIN (

			SELECT

				i.id instance

			FROM APP_InstanceName i WITH(NOLOCK)

				INNER JOIN APP_Application a WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND a.instance = i.id

			UNION

			SELECT 

				ascp.instance

			FROM APP_Application a WITH(NOLOCK)

				INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND cp.componentNameId = a.id

					AND cp.attrName = N''Associated subclient Policy''

					AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

					AND ISNUMERIC(cp.attrVal) = 1

				INNER JOIN APP_Application ascp WITH(NOLOCK) ON

					ascp.clientId = 2

					AND ascp.appTypeId = 1030

					AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

		) a ON

			i.id = a.instance

	ORDER BY i.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 13 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(p.id AS NVARCHAR(MAX))

		+ CAST(p.componentNameId AS NVARCHAR(MAX))

		+ CAST(p.attrName AS  NVARCHAR(MAX))

		+ CAST(p.attrType AS NVARCHAR(MAX))

		+ CAST(p.attrVal AS NVARCHAR(MAX))

		+ CAST(p.created AS NVARCHAR(MAX))

		+ CAST(p.modified AS NVARCHAR(MAX))

		+ CAST(p.ccpId AS NVARCHAR(MAX))

	FROM APP_InstanceProp p WITH(NOLOCK)

		INNER JOIN (

			SELECT

				i.id instance

			FROM APP_InstanceName i WITH(NOLOCK)

				INNER JOIN APP_Application a WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND a.instance = i.id

			UNION

			SELECT 

				ascp.instance

			FROM APP_Application a WITH(NOLOCK)

				INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND cp.componentNameId = a.id

					AND cp.attrName = N''Associated subclient Policy''

					AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

					AND ISNUMERIC(cp.attrVal) = 1

				INNER JOIN APP_Application ascp WITH(NOLOCK) ON

					ascp.clientId = 2

					AND ascp.appTypeId = 1030

					AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

		) a ON

			p.componentNameId = a.instance

	ORDER BY p.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 14 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(f.componentNameId AS NVARCHAR(MAX))

		+ CAST(f.type AS  NVARCHAR(MAX))

		+ CAST(f.fileName AS NVARCHAR(MAX))

		+ CAST(f.created AS NVARCHAR(MAX))

		+ CAST(f.modified AS NVARCHAR(MAX))

	FROM APP_BackupSetFilterFile f WITH(NOLOCK)

		INNER JOIN (

			SELECT

				bs.id backupSet

			FROM APP_BackupSetName bs WITH(NOLOCK)

				INNER JOIN APP_Application a WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND a.backupSet = bs.id

			UNION

			SELECT 

				ascp.backupSet

			FROM APP_Application a WITH(NOLOCK)

				INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND cp.componentNameId = a.id

					AND cp.attrName = N''Associated subclient Policy''

					AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

					AND ISNUMERIC(cp.attrVal) = 1

				INNER JOIN APP_Application ascp WITH(NOLOCK) ON

					ascp.clientId = 2

					AND ascp.appTypeId = 1030

					AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

		) a ON

			f.componentNameId = a.backupSet

	ORDER BY f.created ASC,

		f.fileName COLLATE Latin1_General_CS_AS ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 15 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(f.id AS NVARCHAR(MAX))

		+ CAST(f.componentNameId AS NVARCHAR(MAX))

		+ CAST(f.type AS  NVARCHAR(MAX))

		+ CAST(f.fileName AS NVARCHAR(MAX))

		+ CAST(f.created AS NVARCHAR(MAX))

		+ CAST(f.modified AS NVARCHAR(MAX))

	FROM APP_InstFilterFile f WITH(NOLOCK)

		INNER JOIN (

			SELECT

				i.id instance

			FROM APP_InstanceName i WITH(NOLOCK)

				INNER JOIN APP_Application a WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND a.instance = i.id

			UNION

			SELECT 

				ascp.instance

			FROM APP_Application a WITH(NOLOCK)

				INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND cp.componentNameId = a.id

					AND cp.attrName = N''Associated subclient Policy''

					AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

					AND ISNUMERIC(cp.attrVal) = 1

				INNER JOIN APP_Application ascp WITH(NOLOCK) ON

					ascp.clientId = 2

					AND ascp.appTypeId = 1030

					AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

		) a ON

			f.componentNameId = a.instance

	ORDER BY f.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 16 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(f.id AS NVARCHAR(MAX))

		+ CAST(f.componentNameId AS NVARCHAR(MAX))

		+ CAST(f.type AS  NVARCHAR(MAX))

		+ CAST(f.fileName AS NVARCHAR(MAX))

		+ CAST(f.created AS NVARCHAR(MAX))

		+ CAST(f.modified AS NVARCHAR(MAX))

		+ CAST(f.ccpId AS NVARCHAR(MAX))

		+ CAST(f.cs_fileName AS NVARCHAR(MAX))

	FROM APP_SCFilterFile f WITH(NOLOCK)

		INNER JOIN (

			SELECT

				a.id subClientId

			FROM APP_Application a WITH(NOLOCK)

			WHERE

				a.clientId = @inClientId

			UNION

			SELECT 

				ascp.id subClientId

			FROM APP_Application a WITH(NOLOCK)

				INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

					a.clientId = @inClientId

					AND cp.componentNameId = a.id

					AND cp.attrName = N''Associated subclient Policy''

					AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

					AND ISNUMERIC(cp.attrVal) = 1

				INNER JOIN APP_Application ascp WITH(NOLOCK) ON

					ascp.clientId = 2

					AND ascp.appTypeId = 1030

					AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

		) a ON

			f.componentNameId = a.subClientId

	ORDER BY f.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 17 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT 

		CAST(id AS NVARCHAR(MAX))

		+ CAST(number AS NVARCHAR(MAX))

		+ CAST(clientId AS NVARCHAR(MAX))

		+ CAST(parentId AS NVARCHAR(MAX))

		+ CAST(aliasName AS NVARCHAR(MAX))

		+ CAST(flags AS NVARCHAR(MAX))

		+ CAST(refTime AS NVARCHAR(MAX))

		+ CAST(userName AS NVARCHAR(MAX))

		+ CAST(userPassword AS NVARCHAR(MAX))

		+ CAST(smtpServer AS NVARCHAR(MAX))

		+ CAST(timeZone AS NVARCHAR(MAX))

		+ CAST(ipAddress AS NVARCHAR(MAX))

		+ CAST(modified AS NVARCHAR(MAX))

		+ CAST(ccOpMode AS NVARCHAR(MAX))

		+ CAST(drIpAddress AS NVARCHAR(MAX))

		+ CAST(encNumber AS NVARCHAR(MAX))

		+ CAST(csGUID AS NVARCHAR(MAX))

		+ CAST(type AS NVARCHAR(MAX))

	FROM APP_CommCell WITH(NOLOCK)

	WHERE id IN (

			SELECT

				f.value(''@value'', ''INTEGER'') filter

			FROM APP_CCSXMLMapping WITH(NOLOCK)

				CROSS APPLY filters.nodes(''/Filters/Column/Filter'') n(f)

			WHERE

				id = @tt

				AND f.value(''../@name'', ''NVARCHAR(128)'') = ''id''

				AND f.value(''../@opType'', ''NVARCHAR(128)'') = ''INCLUDE''

		)

	ORDER BY id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 18 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT

		CAST(typeName AS NVARCHAR(MAX))

		+ CAST(extension AS NVARCHAR(MAX))

	FROM App_FileTypeAndExtensions WITH(NOLOCK)

	ORDER BY typeName COLLATE Latin1_General_100_BIN ASC,

		extension COLLATE Latin1_General_100_BIN ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 19 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT

		CAST(p.id AS NVARCHAR(MAX))

		+ CAST(p.componentType AS NVARCHAR(MAX))

		+ CAST(p.componentId AS NVARCHAR(MAX))

		+ CAST(p.propertyTypeId AS NVARCHAR(MAX))

		+ CAST(p.dataType AS NVARCHAR(MAX))

		+ CAST(p.longVal AS NVARCHAR(MAX))

		+ CAST(p.longlongVal AS NVARCHAR(MAX))

		+ CAST(p.stringVal AS NVARCHAR(MAX))

		+ CAST(p.created AS NVARCHAR(MAX))

		+ CAST(p.modified AS NVARCHAR(MAX))

	FROM APP_ComponentProp p WITH(NOLOCK)

		INNER JOIN (

				SELECT

					cp.id

				FROM APP_ComponentProp cp WITH(NOLOCK)

				WHERE

					cp.componentId IN (1,2)

					AND cp.componentType = 1		/*-- CV_COMPONENT_TYPE_COMMCELL */

					AND cp.propertyTypeId IN (1, 10, 11, 12, 13) /*-- CV_COMPONENT_COMMCELL_CI_ENGINE_NAME_PROP,CV_COMPONENT_COMMCELL_CA_CERTIFICATE,CV_COMPONENT_COMMCELL_CA_RSA_PRIVATE,CV_COMPONENT_COMMCELL_COMMCELL_CERTIFICATE,CV_COMPONENT_COMMCELL_COMMCELL_RSA_PRIVATE */

				UNION ALL

				SELECT

					cp.id

				FROM APP_ComponentProp cp WITH(NOLOCK)

				WHERE

					cp.componentType = 3				/*-- CV_COMPONENT_TYPE_CLIENT */

					AND cp.componentId = @inClientId

				UNION ALL

				SELECT

					cp.id

				FROM APP_ComponentProp cp WITH(NOLOCK)

					INNER JOIN (

						SELECT

							a.id subClientId

						FROM APP_Application a WITH(NOLOCK)

						WHERE

								a.clientId = @inClientId

						UNION

						SELECT 

							ascp.id subClientId

						FROM APP_Application a WITH(NOLOCK)

							INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

								a.clientId = @inClientId

								AND cp.componentNameId = a.id

								AND cp.attrName = N''Associated subclient Policy''

								AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

								AND ISNUMERIC(cp.attrVal) = 1

							INNER JOIN APP_Application ascp WITH(NOLOCK) ON

								ascp.clientId = 2

								AND ascp.appTypeId = 1030

								AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

					) a ON

						cp.componentType = 6		/*-- CV_COMPONENT_TYPE_SUBCLIENT */

						AND cp.componentId = a.subClientId

				UNION ALL

				SELECT

					cp.id

				FROM APP_ComponentProp cp WITH(NOLOCK)

					INNER JOIN (

						SELECT

							bs.id backupSet

						FROM APP_BackupSetName bs WITH(NOLOCK)

							INNER JOIN APP_Application a WITH(NOLOCK) ON

								a.clientId = @inClientId

								AND a.backupSet = bs.id

						UNION

						SELECT 

							ascp.backupSet

						FROM APP_Application a WITH(NOLOCK)

							INNER JOIN APP_SubClientProp cp WITH(NOLOCK) ON

								a.clientId = @inClientId

								AND cp.componentNameId = a.id

								AND cp.attrName = N''Associated subclient Policy''

								AND cp.cs_attrName = CHECKSUM( N''Associated subclient Policy'')

								AND ISNUMERIC(cp.attrVal) = 1

							INNER JOIN APP_Application ascp WITH(NOLOCK) ON

								ascp.clientId = 2

								AND ascp.appTypeId = 1030

								AND cp.attrVal = CAST(ascp.id AS NVARCHAR(12))

					) a ON

						cp.componentId = a.backupSet

						AND cp.componentType = 5		/*-- CV_COMPONENT_TYPE_BACKUPSET */

				UNION ALL

				SELECT

					cp.id

				FROM APP_ComponentProp cp WITH(NOLOCK)

					INNER JOIN APP_IDAName i WITH(NOLOCK) ON

						i.clientId = @inClientId

						AND i.appTypeId = 33

						AND cp.componentType = 4		/*-- CV_COMPONENT_TYPE_IDA */

						AND cp.componentId = i.id

				UNION ALL

				SELECT		/*-- Client Group Filtering implemented in SP15*/

					cp.id

				FROM APP_ComponentProp cp WITH(NOLOCK)

					INNER JOIN APP_ClientGroupAssoc cga WITH(NOLOCK) ON 

						cga.clientGroupId = cp.componentId

				WHERE 

					cga.clientId = @inClientId

					AND cp.componentType = 8						/*-- CV_COMPONENT_TYPE_CLIENTGROUP */

					AND cp.propertyTypeId IN (3620, 3621, 3622)		/*-- CV_COMPONENT_PROP_WIN_FILTERS, CV_COMPONENT_PROP_UNIX_FILTERS, CV_COMPONENT_PROP_USE_CLIENT_GROUP_FILTER */

			) t ON

				t.id = p.id

	WHERE

		p.propertyTypeId NOT IN (

			SELECT

				f.value(''@value'', ''INTEGER'') filter

			FROM APP_CCSXMLMapping WITH(NOLOCK)

				CROSS APPLY filters.nodes(''/Filters/Column/Filter'') n(f)

			WHERE

				id = 20

				AND f.value(''../@name'', ''NVARCHAR(128)'') = ''propertyTypeId''

				AND f.value(''../@opType'', ''NVARCHAR(128)'') = ''EXCLUDE''

		)

	ORDER BY p.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 20 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT

		CAST(clientId AS NVARCHAR(MAX))

		+ CAST(appTypeId AS NVARCHAR(MAX))

		+ CAST(instance AS NVARCHAR(MAX))

		+ CAST(backupSet AS NVARCHAR(MAX))

		+ CAST(subclientId AS NVARCHAR(MAX))

		+ CAST(attrType AS NVARCHAR(MAX))

		+ CAST(attrName AS NVARCHAR(MAX))

		+ CAST(attrVal AS NVARCHAR(MAX))

		+ CAST(MD5 AS NVARCHAR(MAX))

		+ CAST(created AS NVARCHAR(MAX))

		+ CAST(modified AS NVARCHAR(MAX))

		+ CAST(ccpId AS NVARCHAR(MAX))

	FROM APP_ExtendedProperties WITH(NOLOCK)

	WHERE

		(

			clientId = @inClientId

			OR (

				clientId = 1

				AND attrType = 129

			)

		)

	ORDER BY clientId ASC,

		created ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 21 AND md5Query = N''

-- Geting duplicate rows was invalidating MD5 Hash due to Mac being added

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT

		CAST(g.typeOfGroup AS NVARCHAR(MAX))

		+ CAST(g.appTypeGroupId AS NVARCHAR(MAX))

		+ CAST(g.groupName AS NVARCHAR(MAX))

	FROM APP_AppTypeGroup g WITH(NOLOCK)

	WHERE

		g.appTypeGroupId IN (

			SELECT

				atga.appGroupId	

			FROM APP_AppTypeGroupAssoc atga WITH(NOLOCK) 

			WHERE

				atga.appTypeId IN (

					SELECT

						f.value(''@value'', ''INT'') id

					FROM APP_CCSXMLMapping m WITH(NOLOCK)

						CROSS APPLY m.filters.nodes(''/Filters/Column/Filter'') d(f)

					WHERE

						m.id = 24		/*-- yes want 24 not 23 tabletype easier filter to use */

						AND f.value(''../@name'', ''VARCHAR(128)'') = ''appTypeId''

				)

		)

	ORDER BY g.appTypeGroupId ASC,

		g.typeOfGroup ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 23 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT

		CAST(a.typeOfGroup AS NVARCHAR(MAX))

		+ CAST(a.appGroupId AS NVARCHAR(MAX))

		+ CAST(a.appTypeId AS NVARCHAR(MAX))

	FROM APP_AppTypeGroupAssoc a WITH(NOLOCK)

		INNER JOIN (

			SELECT

				f.value(''@value'', ''INT'') id

			FROM APP_CCSXMLMapping m WITH(NOLOCK)

				CROSS APPLY m.filters.nodes(''/Filters/Column/Filter'') d(f)

			WHERE

				m.id = @tt

				AND f.value(''../@name'', ''VARCHAR(128)'') = ''appTypeId''

		) f ON

			a.appTypeId = f.id	

	ORDER BY appGroupId ASC,

		appTypeId ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 24 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT

		CAST(j.id AS NVARCHAR(MAX))

		+ CAST(j.EntityId AS NVARCHAR(MAX))

		+ CAST(j.EntityType AS NVARCHAR(MAX))

		+ CAST(j.AppEntityType AS NVARCHAR(MAX))

		+ CAST(j.AppTypeId AS NVARCHAR(MAX))

		+ CAST(j.isEnabled AS NVARCHAR(MAX))

		+ CAST(j.decisionRule AS NVARCHAR(MAX))

	FROM APP_JobErrorDecisionRule j WITH(NOLOCK)

	WHERE

		(

			j.EntityType = 1	/*-- CommCell Entity Type*/

			AND j.entityId = 2

		)

		OR (

			j.entityType = 3		/*-- Client Entity Type*/

			AND j.entityId IN (2, @inClientId)

		)

		OR (

			j.entityType = 28	/*-- ClientGroup Entity Type*/

			AND j.EntityId IN (

				SELECT DISTINCT

					g.clientGroupId

				FROM APP_ClientGroupAssoc g WITH(NOLOCK)

				WHERE

					g.clientId IN (2, @inClientId)

			)

		)

	ORDER BY j.id ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 25 AND md5Query = N''

UPDATE APP_CCSXMLMapping

	SET md5Query = N'

DECLARE @textNode XML = (

	SELECT

		CAST(clientGroupId AS NVARCHAR(MAX))

		+ CAST(clientId AS NVARCHAR(MAX))

	FROM APP_ClientGroupAssoc WITH(NOLOCK)

	WHERE clientId IN (2, @inClientId)

	ORDER BY clientGroupId ASC,

		clientId ASC

	FOR XML PATH(''''), ROOT(''text''), TYPE

)'

WHERE id = 26 AND md5Query = N''

GO 

SET  NOCOUNT OFF

