-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE OR ALTER FUNCTION fn_sub(@vls varchar(100))  
RETURNS varchar(100)  
AS  
BEGIN  
	DECLARE @OLDCOD varchar(100) = '';
	DECLARE @NEWCOD varchar(100) = '';
	DECLARE @TMP varchar(100) = '';
	DECLARE @Cursor as CURSOR;
	DECLARE @c int

	SET @vls = REPLACE(@vls, 'AV0001', '1');
	SET @vls = REPLACE(@vls, 'AG0001', '3');
	SET @vls = REPLACE(@vls, 'CB0001', '4');
	SET @vls = REPLACE(@vls, 'CN01'  , '2');

	if(@vls = 'LF00041M'	) begin SET @vls = '123100041';	 end
	if(@vls = 'LF0004.P'	) begin SET @vls = '123100104';	 end
	if(@vls = 'LF0018.P'	) begin SET @vls = '123100118';	 end
	if(@vls = 'LF00119'		) begin SET @vls = '123100119';  end
	if(@vls = 'LF00026.P'	) begin SET @vls = '123100126';	 end
	if(@vls = 'LF00042.P'	) begin SET @vls = '123100142';	 end
	if(@vls = 'LM01'		) begin SET @vls = '125100001';	 end
	if(@vls = 'TF0010.P'	) begin SET @vls = '210100110';	 end
	if(@vls = 'BM01'		) begin SET @vls = '240100001';	 end
	if(@vls = 'RM0001'		) begin SET @vls = '251100001';	 end
	if(@vls = 'RF01'		) begin SET @vls = '253100001';	 end
	if(@vls = 'UVI08'		) begin SET @vls = '274100008';	 end
	if(@vls = 'MC01'		) begin SET @vls = '290100001';	 end
	if(@vls = 'BP01'		) begin SET @vls = '333100001';	 end
	if(@vls = 'BF008'		) begin SET @vls = '341100008';	 end
	if(@vls = 'BF009'		) begin SET @vls = '341100009';	 end
	if(@vls = 'BF00010'		) begin SET @vls = '341100010';	 end
	if(@vls = 'SC01'		) begin SET @vls = '983100001';  end	


	SET @Cursor = CURSOR FOR
	select OLDCOD, NEWCOD
	from [Alphaind].[dbo].NOVOCODIGO2 where TAMCOD > 0 order by TAMCOD desc

	OPEN @Cursor;
    FETCH NEXT FROM @Cursor INTO @OLDCOD, @NEWCOD;
    WHILE @@FETCH_STATUS = 0
    BEGIN
		
		--if(CHARINDEX('.', @vls) = 0)
		--begin
			if(CHARINDEX(@OLDCOD, @vls) = 1)
			begin
				SET @vls = REPLACE(@vls, @OLDCOD, '');
				SET @TMP = @vls;

				SET @c=65
				 WHILE @c<(65+62) BEGIN
				  SET @TMP=replace(@TMP,char(@c),'')
				  SET @c=@c+1
				 END
				
				SET @vls = @TMP;

				if(CHARINDEX('.', @vls) > 0)
				begin
					SET @vls = CAST(replace(@vls,'.','') AS INT) + 100;
				end

				--if(@TMP = @vls)
				--begin
					SET @vls = CONCAT( @NEWCOD, FORMAT(CAST(@vls AS INT), '000000'));
				--end

			end
		--end

		FETCH NEXT FROM @Cursor INTO @OLDCOD, @NEWCOD;
	END

	SET @vls = REPLACE(@vls, 'AV0001', '1');
	SET @vls = REPLACE(@vls, 'AG0001', '3');
	SET @vls = REPLACE(@vls, 'CB0001', '4');
	SET @vls = REPLACE(@vls, 'CN01'  , '2');

	RETURN (@vls);

END
GO