CREATE FUNCTION [dbo].[fGetRefMB]
(
	@ENTIDADE VARCHAR(5),
	@SUBENTIDADE VARCHAR(3),
	@ID VARCHAR(4),
	@VALOR money
)
RETURNS NVARCHAR(9)
AS
BEGIN
	DECLARE @REF NVARCHAR(9)
	
	DECLARE @TMP VARCHAR(20)
	SET @TMP=RIGHT('00000' + @ENTIDADE, 5) + RIGHT('000' + @SUBENTIDADE, 3) + RIGHT('0000' + @ID, 4) + RIGHT('00000000' + CONVERT(VARCHAR,CONVERT(INT,@VALOR*100)), 8)
	
	/*##### CALCULO DO CHECKDIGIT #####*/
	DECLARE @CHECKDIGIT INT
	SET @CHECKDIGIT = 98 - 
	(
		51 * CONVERT(INT, SUBSTRING(@TMP, 1, 1)) +
		73 * CONVERT(INT, SUBSTRING(@TMP, 2, 1)) +
		17 * CONVERT(INT, SUBSTRING(@TMP, 3, 1)) +
		89 * CONVERT(INT, SUBSTRING(@TMP, 4, 1)) +
		38 * CONVERT(INT, SUBSTRING(@TMP, 5, 1)) +
		62 * CONVERT(INT, SUBSTRING(@TMP, 6, 1)) +
		45 * CONVERT(INT, SUBSTRING(@TMP, 7, 1)) +
		53 * CONVERT(INT, SUBSTRING(@TMP, 8, 1)) +
		15 * CONVERT(INT, SUBSTRING(@TMP, 9, 1)) +
		50 * CONVERT(INT, SUBSTRING(@TMP, 10, 1)) +
		 5 * CONVERT(INT, SUBSTRING(@TMP, 11, 1)) +
		49 * CONVERT(INT, SUBSTRING(@TMP, 12, 1)) +
		34 * CONVERT(INT, SUBSTRING(@TMP, 13, 1)) +
		81 * CONVERT(INT, SUBSTRING(@TMP, 14, 1)) +
		76 * CONVERT(INT, SUBSTRING(@TMP, 15, 1)) +
		27 * CONVERT(INT, SUBSTRING(@TMP, 16, 1)) +
		90 * CONVERT(INT, SUBSTRING(@TMP, 17, 1)) +
		 9 * CONVERT(INT, SUBSTRING(@TMP, 18, 1)) +
		30 * CONVERT(INT, SUBSTRING(@TMP, 19, 1)) +
		 3 * CONVERT(INT, SUBSTRING(@TMP, 20, 1)) 
	) % 97;
	
	/*##### RESULTADO FINAL #####*/
	SET @REF = RIGHT('000' + @SUBENTIDADE,3) + RIGHT('0000' + @ID, 4) + RIGHT('00' + CONVERT(VARCHAR,@CHECKDIGIT), 2);
	
	RETURN @REF;
END
