IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'tmpClientWithWebServerClient' AND OBJECTPROPERTY(id, N'IsTable') = 1)
	DROP TABLE tmpClientWithWebServerClient

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'tmpClientWithDelayUpgradeAgents' AND OBJECTPROPERTY(id, N'IsTable') = 1)
	DROP TABLE tmpClientWithDelayUpgradeAgents

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'tmpClientWithSoftDeprecatedPackages' AND OBJECTPROPERTY(id, N'IsTable') = 1)
	DROP TABLE tmpClientWithSoftDeprecatedPackages
	
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'tmpClientWithHardDeprecatedPackages' AND OBJECTPROPERTY(id, N'IsTable') = 1)
	DROP TABLE tmpClientWithHardDeprecatedPackages
GO

CREATE TABLE tmpClientWithWebServerClient
	(t_clientId INT, t_packageId INT, t_client NVARCHAR(1024), t_package NVARCHAR(1024))

CREATE TABLE tmpClientWithDelayUpgradeAgents
	(t_clientId INT, t_packageId INT, t_client NVARCHAR(1024), t_package NVARCHAR(1024))

CREATE TABLE tmpClientWithSoftDeprecatedPackages
	(t_clientId INT, t_packageId INT, t_client NVARCHAR(1024), t_package NVARCHAR(1024), t_type VARCHAR(128))

CREATE TABLE tmpClientWithHardDeprecatedPackages
	(t_clientId INT, t_packageId INT, t_client NVARCHAR(1024), t_package NVARCHAR(1024))


DELETE SImAppTypeProp_DBU WHERE attrName = 'DelayedAgentUpgradeInfo'
UPDATE simPackage_DBU SET flag = 3 WHERE id = 708

DECLARE @currentRelease	INTEGER
SET @currentRelease = (SELECT MAX(id) FROM simAllGalaxyRel)

-- IF @currentRelease = 14
BEGIN
	-- Web Server: 252; Web Service DB: 803; Web Console: 726, 1135; Web Client: 255
	INSERT INTO tmpClientWithWebServerClient
	SELECT	DISTINCT clientID, simPackageID, (select name from APP_client where id = clientID), (select name from simPackage where id = simPackageID)
	FROM	simInstalledpackages
	WHERE	clientID <> 2 AND simPackageID in (252, 255, 803, 726, 1135)

	INSERT INTO tmpClientWithSoftDeprecatedPackages
	SELECT	DISTINCT clientID, simPackageID, (select name from APP_client where id = clientID), (select name from simPackage where id = simPackageID), 'SIM_PKG_NOT_SUPPORT_UPGRADE'
	FROM	simInstalledpackages
	WHERE	simPackageID IN (select id from simPackage_DBU where (flag & 2) > 0 )

	INSERT INTO tmpClientWithHardDeprecatedPackages
	SELECT	DISTINCT clientID, simPackageID, (select name from APP_client where id = clientID), (select name from simPackage where id = simPackageID)
	FROM	simInstalledpackages
	WHERE	simPackageID NOT IN (select id from simPackage_DBU) 

	INSERT INTO tmpClientWithDelayUpgradeAgents
	SELECT	DISTINCT clientID, simPackageID, (select name from APP_client where id = clientID), (select name from simPackage where id = simPackageID)
	FROM	simInstalledpackages
	WHERE	simPackageID IN (select SimPackageId from simApptypeProp_DBU A JOIN simPackageMap_DBU P ON A.componentNameId = P.appType where attrName = 'DelayedAgentUpgradeInfo' AND A.componentNameId <> 106)
END

IF @currentRelease < 16
BEGIN
	-- t_type: ImageLevelAgent; ClassicArchivingAgent; 1TouchServer
	INSERT INTO tmpClientWithSoftDeprecatedPackages
	SELECT	DISTINCT clientID, simPackageID, (select name from APP_client where id = clientID), (select name from simPackage where id = simPackageID), 
		(CASE WHEN simPackageID IN (707, 1104) THEN 'ImageLevelAgent' 
		      WHEN simPackageID IN (158, 705, 1109) THEN 'ClassicArchivingAgent' 
			  WHEN simPackageID IN (554) THEN '1TouchServer' END)
	FROM	simInstalledpackages
	WHERE	simPackageID in (158, 554, 705, 707, 1104, 1109)	
END

DELETE tmpClientWithSoftDeprecatedPackages
WHERE	t_clientId <> 2 AND t_package like 'SRM%'

DELETE tmpClientWithHardDeprecatedPackages
WHERE	t_clientId <> 2 AND t_package like 'SRM%'