CREATE PROCEDURE [dbo].[MIG_SET_DOC_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 = 'DOC'
		SET @MigInterimTable = 'MIG_DOC_DEFAULTS'

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

	IF @InputTable = 'DOC'
	BEGIN
		IF      @InputColumnName <> 'APP_FORMAT_TYPE'
            AND @InputColumnName <> 'AUTHOR'
            AND @InputColumnName <> 'BOM_DEFINING'
            AND @InputColumnName <> 'CAGE_CODE'
            AND @InputColumnName <> 'CATEGORY_ID'
            AND @InputColumnName <> 'CATEGORY_NAME'
            AND @InputColumnName <> 'CLASSIFICATION'
            AND @InputColumnName <> 'CREATE_DATE'
            AND @InputColumnName <> 'CUST_DOC_NO'
            AND @InputColumnName <> 'CUST_DOC_REV'
            AND @InputColumnName <> 'DATE1'
            AND @InputColumnName <> 'DATE2'
            AND @InputColumnName <> 'DATE3'
            AND @InputColumnName <> 'DATE_APPROVAL'
            AND @InputColumnName <> 'DATE_EFF_FROM'
            AND @InputColumnName <> 'DATE_EFF_THRU'
            AND @InputColumnName <> 'DB_ZZ_ID'
            AND @InputColumnName <> 'DERIVED_FROM'
            AND @InputColumnName <> 'DERIVED_FROM_REV'
            AND @InputColumnName <> 'DOC_CLASS'
            AND @InputColumnName <> 'DOC_KEY'
            AND @InputColumnName <> 'DOC_NAME'
            AND @InputColumnName <> 'DOC_NAME_COMPUTED'
            AND @InputColumnName <> 'DOC_TYPE'
            AND @InputColumnName <> 'EO_ID'
            AND @InputColumnName <> 'INTEGER1'
            AND @InputColumnName <> 'INTEGER2'
            AND @InputColumnName <> 'INTEGER3'
            AND @InputColumnName <> 'INTEGER4'
            AND @InputColumnName <> 'INTEGER5'
            AND @InputColumnName <> 'LIFE_CYCLE'
            AND @InputColumnName <> 'LIST1'
            AND @InputColumnName <> 'LIST2'
            AND @InputColumnName <> 'LIST3'
            AND @InputColumnName <> 'LOCKED_BY'
            AND @InputColumnName <> 'MANUF_ID'
            AND @InputColumnName <> 'MPN'
            AND @InputColumnName <> 'NO_OF_ECN'
            AND @InputColumnName <> 'OBJECT_TYPE'
            AND @InputColumnName <> 'OBSOLETE'
            AND @InputColumnName <> 'ORIGINATING_APP'
            AND @InputColumnName <> 'PREV_REVISION'
            AND @InputColumnName <> 'PROJECT_ID'
            AND @InputColumnName <> 'REVISION'
            AND @InputColumnName <> 'STATUS'
            AND @InputColumnName <> 'STRING1'
            AND @InputColumnName <> 'STRING2'
            AND @InputColumnName <> 'STRING3'
            AND @InputColumnName <> 'STRING4'
            AND @InputColumnName <> 'STRING5'
            AND @InputColumnName <> 'STRING6'
            AND @InputColumnName <> 'UPDATED_ON'
            AND @InputColumnName <> 'UPDATE_DATE'
            AND @InputColumnName <> 'ZZ_COUNTER'
            AND @InputColumnName <> 'ZZ_ID'
            AND @InputColumnName <> 'ZZ_SYSTIME'
			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 = 'DOC'
				BEGIN
					SET @D001 = ( SELECT D001 FROM MIG_DOC_DEFAULTS WHERE TEMPLATE_ID = @InputTemplateID )
				
					IF @D001 IS NULL
						SET @D001 = @InputColumnName
					ELSE
						SET @D001 = @D001 + '|' + @InputColumnName

					--UPDATE MIG_DOC_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 = 'DOC'
		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 = 'DOC' 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 = 'DOC' 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_DOC_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_DOC_DEFAULT_USERS TABLE INFO
	IF @InputTable = 'DOC' AND @InputDefaultValue <> '(**Current-user**)' AND @InputColumnName IN (   'AUTHOR','LOCKED_BY')
	BEGIN
		
		SET @SQL_QUERY = 'UPDATE MIG_DOC_DEFAULT_USERS SET ' +@InputColumnName +' = (SELECT LN_USER FROM MIG_USERS WHERE USER_NAME = '''+@InputDefaultValue+''' ) 	WHERE TEMPLATE_ID = ' + @InputTemplateID
 
		EXEC (@SQL_QUERY)
	END	
			
END



