CREATE PROCEDURE [MIG_SET_USER_PREFERENCE_Sp]
(
	@InputUser NVARCHAR(MAX),
	@InputApp NVARCHAR(MAX),
	@TargetPropName NVARCHAR(MAX),
	@TargetControlType NVARCHAR(MAX),
	@TargetPropDescription NVARCHAR(MAX),
	@LegacyPropName NVARCHAR(MAX),
	@LegacyPropGuiType NVARCHAR(MAX),
	@InputPropValue NVARCHAR(MAX)
)
AS    
BEGIN 
	DECLARE @MigInterimTable NVARCHAR(MAX)
	DECLARE @SQL_QUERY NVARCHAR(MAX)
	DECLARE @TargetPropValue NVARCHAR(MAX)
	DECLARE @TargetDataType NVARCHAR(MAX)



		IF @InputApp = 'SOLIDWORKS'
			BEGIN
				SET @MigInterimTable = 'MIG_SOLIDWORKS_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_SOLIDWORKS_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'TOOLKIT'
			BEGIN
				SET @MigInterimTable = 'MIG_TOOLKIT_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_TOOLKIT_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'WEBPLM'
			BEGIN
				SET @MigInterimTable = 'MIG_PLM_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_PLM_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'INVENTOR'
			BEGIN
				SET @MigInterimTable = 'MIG_INVENTOR_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_INVENTOR_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'PROE'
			BEGIN
				SET @MigInterimTable = 'MIG_CREO_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_CREO_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'MSOFFICE'
			BEGIN
				SET @MigInterimTable = 'MIG_MSOFFICE_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_MSOFFICE_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'AUTOCAD'
			BEGIN
				SET @MigInterimTable = 'MIG_AUTOCAD_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_AUTOCAD_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'UG'
			BEGIN
				SET @MigInterimTable = 'MIG_UG_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_UG_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'SOLIDEDGE'
			BEGIN
				SET @MigInterimTable = 'MIG_SOLIDEDGE_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_SOLIDEDGE_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'CATIAV5'
			BEGIN
				SET @MigInterimTable = 'MIG_CATIAV_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_CATIAV_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'DRAFTSIGHT'
			BEGIN
				SET @MigInterimTable = 'MIG_DRAFTSIGHT_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_DRAFTSIGHT_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END
		ELSE IF @InputApp = 'ME10'
			BEGIN
				SET @MigInterimTable = 'MIG_ME_PREFERENCES'
				SET @TargetDataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MIG_ME_PREFERENCES' AND COLUMN_NAME = @TargetPropName )
			END

		ELSE
			RETURN

		
		IF @InputApp = 'SOLIDWORKS'
		BEGIN
		
			EXEC [MIG_SET_SOLIDWORKS_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
		
		END

		ELSE IF @InputApp = 'TOOLKIT'
		BEGIN
		
			EXEC [MIG_SET_TOOLKIT_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
		
		END

		ELSE IF @InputApp = 'WEBPLM'
		BEGIN
		
			EXEC [MIG_SET_PLM_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
		
		END

		ELSE IF @InputApp = 'INVENTOR'
			BEGIN
				EXEC [MIG_SET_INVENTOR_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
			END
		ELSE IF @InputApp = 'PROE' 
			BEGIN
				
				EXEC [MIG_SET_CREO_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
				
			END
		ELSE IF @InputApp = 'MSOFFICE'
			BEGIN
				
				EXEC [MIG_SET_MSOFFICE_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
				
			END
		ELSE IF @InputApp = 'AUTOCAD'
			BEGIN
				EXEC [MIG_SET_AUTOCAD_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
			END
		ELSE IF @InputApp = 'UG'
			BEGIN
				
				EXEC [MIG_SET_UG_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
				
			END
		ELSE IF @InputApp = 'SOLIDEDGE'
			BEGIN
				
				EXEC [MIG_SET_SOLIDEDGE_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
				
			END
		ELSE IF @InputApp = 'CATIAV5'
			BEGIN
				
				EXEC [MIG_SET_CATIAV_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
				
			END
		ELSE IF @InputApp = 'DRAFTSIGHT'
			BEGIN
				
				EXEC [MIG_SET_DRAFTSIGHT_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
				
			END
		ELSE IF @InputApp = 'ME10'
			BEGIN
				
				EXEC [MIG_SET_ME_USER_PREFERENCE_Sp] @TargetControlType, 
														@LegacyPropName, 
														@LegacyPropGuiType, 
														@InputPropValue, 
														@TargetPropValue OUTPUT
				
			END

		-- generate and execute update query

		IF @TargetPropValue IS NOT NULL 
			BEGIN
				--SET @TargetPropValue = @InputPropValue

				SET @SQL_QUERY =	'UPDATE ' + @MigInterimTable + ' ' + 
									'SET '+ @TargetPropName + ' = @TargetPropValue ' + 
									'WHERE ' + @MigInterimTable + '.[user_id] = @InputUser'

				IF @TargetDataType = 'int'
					exec sp_executesql		@SQL_QUERY,
											N'@TargetPropValue int,
											@InputUser NVARCHAR(MAX)',
											@TargetPropValue = @TargetPropValue,
											@InputUser = @InputUser
				ELSE IF @TargetDataType = 'varchar' OR @TargetDataType = 'nvarchar'
					exec sp_executesql		@SQL_QUERY,
											N'@TargetPropValue NVARCHAR(MAX),
											@InputUser NVARCHAR(MAX)',
											@TargetPropValue = @TargetPropValue,
											@InputUser = @InputUser
			END
END


--	MIG_SW_PROPS
--	user,	description,	effective_date,		s001,	s002,	......	s090
--	*		MIGRATION		null										
--	umesh	MIGRATION		null				
--	govind	MIGRATION		null				



--	MIG_SW_PROPS
--	user,	description,	effective_date,		s001,	s002,	......	s090
--	*		MIGRATION		null				1		P1				C:\Edit
--	umesh	MIGRATION		null				1		P1				C:\Edit
--	govind	MIGRATION		null				1		P1				C:\Edit




