CREATE PROCEDURE [dbo].[MIG_SET_ITEM_BOM_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_PS_DEF'
		SET @MigInterimTable = 'MIG_ITEM_BOM_DEFAULTS'

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

	IF @InputTable = 'ITEM_PS_DEF'
	BEGIN
		IF      @InputColumnName <> 'AS_REQUIRED'
            AND @InputColumnName <> 'BAAN_COMPANY'
            AND @InputColumnName <> 'BAAN_PROJECT'
            AND @InputColumnName <> 'CHILD_ITEM'
            AND @InputColumnName <> 'CHILD_UNIT'
            AND @InputColumnName <> 'DATE_EFF_FROM'
            AND @InputColumnName <> 'DATE_EFF_THRU'
            AND @InputColumnName <> 'DB_ZZ_ID'
            AND @InputColumnName <> 'DISPOSITION'
            AND @InputColumnName <> 'EXCEPTIONS_PRESENT'
            AND @InputColumnName <> 'EXTRA_INFO'
            --AND @InputColumnName <> 'FIND_NO'
            AND @InputColumnName <> 'INPUT_OUTPUT'
            AND @InputColumnName <> 'MTRL_LENGTH'
            AND @InputColumnName <> 'MTRL_PERCENT'
            AND @InputColumnName <> 'MTRL_RELATION'
            AND @InputColumnName <> 'MTRL_WIDTH'
            AND @InputColumnName <> 'NOTE_FN1'
            AND @InputColumnName <> 'NOTE_FN2'
            AND @InputColumnName <> 'NOTE_FN3'
            AND @InputColumnName <> 'NOTE_FN4'
            AND @InputColumnName <> 'NOTE_FN5'
            AND @InputColumnName <> 'NO_OF_UNITS'
            AND @InputColumnName <> 'OPERATION'
            AND @InputColumnName <> 'ORIGINATING_APP'
            AND @InputColumnName <> 'PARENT_ITEM'
            AND @InputColumnName <> 'PARENT_REVISION'
            --AND @InputColumnName <> 'POSITION'
            AND @InputColumnName <> 'QUANTITY'
            AND @InputColumnName <> 'REF_MATERIAL'
            AND @InputColumnName <> 'SCALING'
            AND @InputColumnName <> 'STANDARD_CONFIG'
            AND @InputColumnName <> 'UPDATED_ON'
            AND @InputColumnName <> 'WORK_CENTER'
            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 = 'ITEM_PS_DEF'
				BEGIN
					SET @D001 = ( SELECT D001 FROM MIG_ITEM_BOM_DEFAULTS WHERE TEMPLATE_ID = @InputTemplateID )
				
					IF @D001 IS NULL
						SET @D001 = @InputColumnName
					ELSE
						SET @D001 = @D001 + '|' + @InputColumnName

					--UPDATE MIG_ITEM_BOM_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_PS_DEF'
		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_PS_DEF' 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_PS_DEF' 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_BOM_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
END



