--EXEC MIG_VALIDATE_BOM_LINES_LEGAL_CHARACTERS_Sp '', ''
CREATE OR ALTER PROCEDURE MIG_VALIDATE_BOM_LINES_LEGAL_CHARACTERS_Sp
(
	@STATUS INT = 0 OUT,
	@MESSAGE NVARCHAR(999) = NULL OUT
)
AS 
BEGIN
	DECLARE @CNT INT
	
	SET @STATUS = 0
	SET @MESSAGE = NULL

	SELECT @CNT = COUNT(*)
	FROM ITEM_PS_DEF 
	WHERE SUBSTRING(FIND_NO, 1, 1) NOT IN ( '0','1','2','3','4','5','6','7','8','9')
	OR SUBSTRING(FIND_NO, 2, 1) NOT IN ( '0','1','2','3','4','5','6','7','8','9')
	OR SUBSTRING(FIND_NO, 3, 1) NOT IN ( '0','1','2','3','4','5','6','7','8','9')
	OR SUBSTRING(FIND_NO, 4, 1) NOT IN ( '0','1','2','3','4','5','6','7','8','9')


	IF @CNT > 0
		BEGIN
			SET @STATUS = 1
			IF @MESSAGE IS NULL
				SET @MESSAGE = ''

			SET @MESSAGE = @MESSAGE + 'BOM Lines contain illegal character (legal characters are 0, 1, 2... 8, 9).'
		END

	SELECT @STATUS, @MESSAGE
END

