CREATE PROCEDURE [dbo].[MIG_SET_ITEM_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 = 'ITEM'
		SET @MigInterimTable = 'MIG_ITEM_DEFAULTS'

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

	IF @InputTable = 'ITEM'
	BEGIN
		IF      @InputColumnName <> 'ASSESSMENT_BY'
            AND @InputColumnName <> 'ASSESSMENT_DATE'
            AND @InputColumnName <> 'AUTHOR'
            AND @InputColumnName <> 'BAAN_PROJECT'
            AND @InputColumnName <> 'BAAN_SIZE'
            AND @InputColumnName <> 'BAAN_TMPLT_ITEM'
            AND @InputColumnName <> 'BOM_UNIT'
            AND @InputColumnName <> 'CAD_FILE'
            AND @InputColumnName <> 'CAD_INSTANCE_NAME'
            AND @InputColumnName <> 'CAGE_CODE'
            AND @InputColumnName <> 'CATEGORY_ID'
            AND @InputColumnName <> 'CATEGORY_NAME'
            AND @InputColumnName <> 'CERTIFIED_DATE'
            AND @InputColumnName <> 'CLASSIFICATION'
            AND @InputColumnName <> 'COMPLIANCE_CMNT'
            AND @InputColumnName <> 'COMPLIANCE_STATUS'
            AND @InputColumnName <> 'CONFIG_ID'
            AND @InputColumnName <> 'COST'
            AND @InputColumnName <> 'CREATE_DATE'
            AND @InputColumnName <> 'CURRENCY'
            AND @InputColumnName <> 'DATE1'
            AND @InputColumnName <> 'DATE2'
            AND @InputColumnName <> 'DATE3'
            AND @InputColumnName <> 'DATE_APPROVAL'
            AND @InputColumnName <> 'DATE_EFF_FROM'
            AND @InputColumnName <> 'DATE_EFF_FROM_2'
            AND @InputColumnName <> 'DATE_EFF_FROM_T'
            AND @InputColumnName <> 'DATE_EFF_THRU'
            AND @InputColumnName <> 'DATE_EFF_THRU_2'
            AND @InputColumnName <> 'DATE_EFF_THRU_T'
            AND @InputColumnName <> 'DB_ZZ_ID'
            AND @InputColumnName <> 'DERIVED_FROM'
            AND @InputColumnName <> 'DERIVED_FROM_REV'
            AND @InputColumnName <> 'DRAWING_REF'
            AND @InputColumnName <> 'EFF_INDICATOR'
            AND @InputColumnName <> 'ELEMENT_ID'
            AND @InputColumnName <> 'END_ITEM_FLG'
            AND @InputColumnName <> 'ENGINEER'
            AND @InputColumnName <> 'ENG_ITEM_GROUP'
            AND @InputColumnName <> 'ENG_ITEM_REF'
            AND @InputColumnName <> 'EO_ID'
            AND @InputColumnName <> 'EXEMPTION_REASON'
            AND @InputColumnName <> 'EXP_DURATION'
            AND @InputColumnName <> 'EXP_DURATION_UNIT'
            AND @InputColumnName <> 'FORMULA_TYPE'
            AND @InputColumnName <> 'GENERIC'
            AND @InputColumnName <> 'INTEGER1'
            AND @InputColumnName <> 'INTEGER2'
            AND @InputColumnName <> 'INTEGER3'
            AND @InputColumnName <> 'INTEGER4'
            AND @InputColumnName <> 'INTEGER5'
            AND @InputColumnName <> 'INTERCHANGEABLE'
            AND @InputColumnName <> 'IN_HOUSE'
            AND @InputColumnName <> 'ITEM_CLASS'
            AND @InputColumnName <> 'ITEM_GROUP'
            AND @InputColumnName <> 'ITEM_KEY'
            AND @InputColumnName <> 'ITEM_NAME'
            AND @InputColumnName <> 'ITEM_TYPE'
            AND @InputColumnName <> 'KEY_FORMULA'
            AND @InputColumnName <> 'LIFE_CYCLE'
            AND @InputColumnName <> 'LIST1'
            AND @InputColumnName <> 'LIST2'
            AND @InputColumnName <> 'LIST3'
            AND @InputColumnName <> 'LLI'
            AND @InputColumnName <> 'LOCKED_BY'
            AND @InputColumnName <> 'MANUFACTURERS'
            AND @InputColumnName <> 'MASS_GRAVITY_UNIT'
            AND @InputColumnName <> 'MATERIAL'
            AND @InputColumnName <> 'MBC_ID'
            AND @InputColumnName <> 'NATIONAL_ST_NO'
            AND @InputColumnName <> 'OBJECT_TYPE'
            AND @InputColumnName <> 'ORDER_POLICY'
            AND @InputColumnName <> 'ORIGINATING_APP'
            AND @InputColumnName <> 'ORIGINATING_ITEM'
            AND @InputColumnName <> 'PHANTOM'
            AND @InputColumnName <> 'PLANNER'
            AND @InputColumnName <> 'PREV_REVISION'
            AND @InputColumnName <> 'PRODUCT_CLASS'
            AND @InputColumnName <> 'PRODUCT_CODE'
            AND @InputColumnName <> 'PRODUCT_LINE'
            AND @InputColumnName <> 'PRODUCT_TYPE'
            AND @InputColumnName <> 'PROJECT_ID'
            AND @InputColumnName <> 'QUANTITY_BASE'
            AND @InputColumnName <> 'QUOTE_ID'
            AND @InputColumnName <> 'QUOTE_REV'
            AND @InputColumnName <> 'RECYCLABILITY'
            AND @InputColumnName <> 'REVISION'
            AND @InputColumnName <> 'SEARCH_KEY1'
            AND @InputColumnName <> 'SEARCH_KEY2'
            AND @InputColumnName <> 'SELECTION_CODE'
            AND @InputColumnName <> 'SERIALIZED'
            AND @InputColumnName <> 'SERIES_PRESENT'
            AND @InputColumnName <> 'SERVICE'
            AND @InputColumnName <> 'SIGNAL_CODE'
            AND @InputColumnName <> 'STANDARD'
            AND @InputColumnName <> 'STATE'
            AND @InputColumnName <> 'STATUS'
            AND @InputColumnName <> 'STRING1'
            AND @InputColumnName <> 'STRING2'
            AND @InputColumnName <> 'STRING3'
            AND @InputColumnName <> 'STRING4'
            AND @InputColumnName <> 'STRING5'
            AND @InputColumnName <> 'STRING6'
            AND @InputColumnName <> 'TRANS_BAAN'
            AND @InputColumnName <> 'UNIT'
            AND @InputColumnName <> 'UNIT_EFFECTIVE'
            AND @InputColumnName <> 'UNIT_OF_MEASURE'
            AND @InputColumnName <> 'UNIT_SET'
            AND @InputColumnName <> 'UPDATED_ON'
            AND @InputColumnName <> 'VOLUME'
            AND @InputColumnName <> 'VOL_GRAVITY_UNIT'
            AND @InputColumnName <> 'WAREHOUSE'
            AND @InputColumnName <> 'WEIGHT'
            AND @InputColumnName <> 'WEIGHT_UNIT'
            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 = 'ITEM'
				BEGIN
					SET @D001 = ( SELECT D001 FROM MIG_ITEM_DEFAULTS WHERE TEMPLATE_ID = @InputTemplateID )
				
					IF @D001 IS NULL
						SET @D001 = @InputColumnName
					ELSE
						SET @D001 = @D001 + '|' + @InputColumnName

					--UPDATE MIG_ITEM_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 = 'ITEM'
		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 = 'ITEM' 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 = 'ITEM' 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_ITEM_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_ITEM_DEFAULTS_EXTRA TABLE INFO
	IF @InputTable = 'ITEM' AND ISNULL(@UdcdValue,'') <> '' AND @InputColumnName IN ( 'CURRENCY',          
																						'ITEM_GROUP', 		
																						'MANUFACTURERS',
																						'PRODUCT_TYPE',
																						'PRODUCT_CLASS',		
																						'PRODUCT_LINE',
																						'SELECTION_CODE',
																						'SIGNAL_CODE',
																						'UNIT_OF_MEASURE',
																						'UNIT_SET',
																						'WAREHOUSE',			
																						'WEIGHT_UNIT')
	BEGIN
		SET @SQL_QUERY = 'UPDATE MIG_ITEM_DEFAULTS_EXTRA SET '+ @InputColumnName + '= (SELECT TYPE_CODE FROM ZZ_UDCDTYPE WHERE TYPE_NAME = ''' +@InputUdtpReference+ ''' AND VALUE_ID = '+@UdcdValue+' ) 	WHERE TEMPLATE_ID = ' + @InputTemplateID
 
		EXEC (@SQL_QUERY)

		 
	END	
	
	 -- UPDATE MIG_ITEM_DEFAULTS_EXTRA TABLE INFO
	IF @InputTable = 'ITEM' AND @InputDefaultValue <> '(**Current-user**)' AND @InputColumnName IN (   'AUTHOR',
																										'ASSESSMENT_BY',
																										'LOCKED_BY',
																										'ENGINEER',
																										'PLANNER')
	BEGIN
		
		SET @SQL_QUERY = 'UPDATE MIG_ITEM_DEFAULT_USERS SET ' +@InputColumnName +' = (SELECT LN_USER FROM MIG_USERS WHERE USER_NAME = '''+@InputDefaultValue+''' ) 	WHERE TEMPLATE_ID = ' + @InputTemplateID
 
		EXEC (@SQL_QUERY)
	END	
										  
													  
END	

	