CREATE PROCEDURE [dbo].[MIG_SET_BP_DEFAULTS_FIELD_VALUE_Sp]
(
	@InputTable NVARCHAR(MAX),
	@InputTemplateID NVARCHAR(MAX),
	@InputClassID NVARCHAR(MAX),
	@InputColumnName NVARCHAR(MAX),
	@InputAttrName NVARCHAR(MAX),
	@InputAttrType NVARCHAR(MAX),
	@InputAttrRefType NVARCHAR(MAX),
	@InputUdtpReference NVARCHAR(MAX),
	@InputClassReference NVARCHAR(MAX),
	@InputAttrReference NVARCHAR(MAX),
	@InputDefaultValue NVARCHAR(MAX)
)
AS    
BEGIN 
	
	DECLARE @MigInterimTable NVARCHAR(MAX)
	DECLARE @UdcdValue NVARCHAR(MAX)
	DECLARE @SQL_QUERY NVARCHAR(MAX)
	DECLARE @D001 NVARCHAR(MAX)

	SET @UdcdValue = NULL

	IF @InputTable = 'EO'
		SET @MigInterimTable = 'MIG_BUSINESS_PROCESS_DEFAULTS'

	-- SELECT '            AND @InputColumnName <> ''' + COLUMN_NAME + '''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EO' ORDER BY COLUMN_NAME

	IF @InputTable = 'EO'
	BEGIN
		IF		@InputColumnName <> 'AUTHOR'
            AND @InputColumnName <> 'CREATE_DATE'
            AND @InputColumnName <> 'DATE1'
            AND @InputColumnName <> 'DATE2'
            AND @InputColumnName <> 'DATE3'
            AND @InputColumnName <> 'DB_ZZ_ID'
            AND @InputColumnName <> 'DESCRIPTION'
            AND @InputColumnName <> 'DESIGN_HOURS'
            AND @InputColumnName <> 'EFFECT_DATE'
            AND @InputColumnName <> 'EO_EFFECT_DATE'
            AND @InputColumnName <> 'EO_EXPR_DATE'
            AND @InputColumnName <> 'EO_ID'
            AND @InputColumnName <> 'EO_INIT_DATE'
            AND @InputColumnName <> 'EO_INIT_TIME'
            AND @InputColumnName <> 'EO_TYPE'
            AND @InputColumnName <> 'ERR_SND_DATE'
            AND @InputColumnName <> 'ERR_SND_TIME'
            AND @InputColumnName <> 'EXCEPTION'
            AND @InputColumnName <> 'EXCEPTION_EO'
            AND @InputColumnName <> 'EXPR_DATE'
            AND @InputColumnName <> 'IMPACT_ANALYSIS'
            AND @InputColumnName <> 'INTEGER1'
            AND @InputColumnName <> 'INTEGER2'
            AND @InputColumnName <> 'INTEGER3'
            AND @InputColumnName <> 'INTEGER4'
            AND @InputColumnName <> 'INTEGER5'
            AND @InputColumnName <> 'INVEST_COST'
            AND @InputColumnName <> 'LIST1'
            AND @InputColumnName <> 'LIST2'
            AND @InputColumnName <> 'LIST3'
            AND @InputColumnName <> 'MAX_DURATION'
            AND @InputColumnName <> 'OBJECT_TYPE'
            AND @InputColumnName <> 'PRIORITY'
            AND @InputColumnName <> 'PROCESS_COST'
            AND @InputColumnName <> 'PROD_COST_PER_PROJ'
            AND @InputColumnName <> 'PROJECT_ID'
            AND @InputColumnName <> 'REASON_FOR_CHANGE'
            AND @InputColumnName <> 'REJECT_BY'
            AND @InputColumnName <> 'REJECT_TO'
            AND @InputColumnName <> 'REMARKS'
            AND @InputColumnName <> 'RESPONSIBLE'
            AND @InputColumnName <> 'ROUTE_ID'
            AND @InputColumnName <> 'SERIAL_FROM'
            AND @InputColumnName <> 'SERIAL_TO'
            AND @InputColumnName <> 'STATUS'
            AND @InputColumnName <> 'STRING1'
            AND @InputColumnName <> 'STRING2'
            AND @InputColumnName <> 'STRING3'
            AND @InputColumnName <> 'STRING4'
            AND @InputColumnName <> 'STRING5'
            AND @InputColumnName <> 'TIME_UNIT'
            AND @InputColumnName <> 'UPDATED_ON'
            AND @InputColumnName <> 'WARN_BEFORE'
            AND @InputColumnName <> 'WARN_TIME_UNIT'
            AND @InputColumnName <> 'WEIGHT'
            AND @InputColumnName <> 'WRN_SND_DATE'
            AND @InputColumnName <> 'WRN_SND_TIME'
            AND @InputColumnName <> 'ZZ_COUNTER'
            AND @InputColumnName <> 'ZZ_ID'
			BEGIN
				RETURN
			END
	END

	----IF @InputAttrType = '' AND @InputDefaultValue = 'FOREVER'
	----	SET @InputDefaultValue = ''

	IF @InputDefaultValue = '(**Current-user**)' OR @InputDefaultValue = '(**Today**)'
		BEGIN
			--TODO:: Update D001 column
			IF @InputTable = 'EO'
				BEGIN
					SET @D001 = ( SELECT D001 FROM MIG_BUSINESS_PROCESS_DEFAULTS WHERE TEMPLATE_ID = @InputTemplateID )
				
					IF @D001 IS NULL
						SET @D001 = @InputColumnName
					ELSE
						SET @D001 = @D001 + '|' + @InputColumnName

					--UPDATE MIG_BUSINESS_PROCESS_DEFAULTS SET D001 = @D001 WHERE TEMPLATE_ID = @InputTemplateID

					SET @SQL_QUERY = 'UPDATE ' + @MigInterimTable + ' SET '+ @InputColumnName + ' = @InputDefaultValue, 
									  D001 = @D001 
									  WHERE TEMPLATE_ID = @InputTemplateID'

		
					exec sp_executesql	@SQL_QUERY,
										N'@InputDefaultValue NVARCHAR(MAX), 
										@InputTemplateID NVARCHAR(MAX),
										@D001 NVARCHAR(MAX)',
										@InputDefaultValue = @InputDefaultValue,
										@InputTemplateID = @InputTemplateID,
										@D001 = @D001
			

				END
			
			RETURN
		END

	IF @InputTable = 'EO'
		BEGIN
			IF @InputColumnName = 'REVISION' AND @InputDefaultValue = '(**Auto-number**)'
				BEGIN
					SET @InputDefaultValue = '(AUTO)'
				END
		END

	IF @InputUdtpReference IS NOT NULL
		BEGIN
			SELECT @UdcdValue =	(	SELECT TOP 1 ZZ_UDCDTYPE.VALUE_ID
									FROM ZZ_UDCDTYPE 
									WHERE TYPE_NAME = @InputUdtpReference
									AND SHORT_DESCR = @InputDefaultValue	)

		END

	IF @InputDefaultValue = ''
		RETURN

	IF @InputTable = 'EO' AND @UdcdValue IS NOT NULL
		BEGIN
			SET @SQL_QUERY = 'UPDATE ' + @MigInterimTable + ' SET '+ @InputColumnName + ' = @UdcdValue WHERE TEMPLATE_ID = @InputTemplateID'

			exec sp_executesql	@SQL_QUERY,
								N'@InputTable NVARCHAR(MAX),
								@InputColumnName NVARCHAR(MAX), 
								@UdcdValue NVARCHAR(MAX), 
								@InputTemplateID NVARCHAR(MAX)',
								@InputTable = @InputTable,
								@InputColumnName = @InputColumnName,
								@UdcdValue = @UdcdValue,
								@InputTemplateID = @InputTemplateID
		END
	
	ELSE IF @InputTable = 'EO' AND @UdcdValue IS NULL
	BEGIN		
		SET @SQL_QUERY = 'UPDATE ' + @MigInterimTable + ' SET '+ @InputColumnName + ' = @InputDefaultValue WHERE TEMPLATE_ID = @InputTemplateID'

		--IF @InputColumnName = 'ITEM_KEY'
			--BEGIN
				--SET @SQL_QUERY =	'UPDATE MIG_BUSINESS_PROCESS_DEFAULTS
				--					SET ITEM_KEY = @InputDefaultValue
				--					WHERE TEMPLATE_ID = @InputTemplateID'

				IF UPPER(@InputDefaultValue) = 'FOREVER' 
					SET @InputDefaultValue = '99991231'

				exec sp_executesql	@SQL_QUERY,
									N'@InputDefaultValue NVARCHAR(MAX), 
									@InputTemplateID NVARCHAR(MAX)',
									@InputDefaultValue = @InputDefaultValue,
									@InputTemplateID = @InputTemplateID
			--END
	END

	
	 -- UPDATE MIG_BP_DEFAULT_USERS TABLE INFO
	IF @InputTable = 'EO' AND @InputDefaultValue <> '(**Current-user**)' AND @InputColumnName IN ( 'AUTHOR','REJECT_BY','REJECT_TO','RESPONSIBLE_USER')
	BEGIN																									 		
																											 
		SET @SQL_QUERY = 'UPDATE MIG_BP_DEFAULT_USERS SET ' +@InputColumnName +' = (SELECT LN_USER FROM MIG_USERS WHERE USER_NAME = '''+@InputDefaultValue+''' ) 	WHERE TEMPLATE_ID = ' + @InputTemplateID
 
		EXEC (@SQL_QUERY)
	END

END



