DROP FUNCTION IF EXISTS GetLimitSup;
DELIMITER $$

CREATE FUNCTION GetLimitSup(
	valueIn DECIMAL(10,5),
	deckeyval VARCHAR(100) CHARSET utf8mb4
) 
RETURNS INTEGER
DETERMINISTIC
BEGIN
    DECLARE val100 DECIMAL(10,5);
	DECLARE valNum INTEGER;
	DECLARE retVal DECIMAL(10,5);
	DECLARE iRetVal INTEGER;
	SELECT 
		CAST(decvalue AS DECIMAL(10,5)) AS decvalue
	INTO val100
    FROM `decode`
    WHERE 
		    (`decode`.`decclass` = 'CONGRUITA') 
		and (`decode`.`deckey` = CONCAT(deckeyval, '.sup.100'))
	LIMIT 1;
	SELECT 
		CAST(decvalue AS SIGNED) AS decvalue
	INTO valNum
    FROM `decode`
    WHERE 
		    (`decode`.`decclass` = 'CONGRUITA') 
		and (`decode`.`deckey` = CONCAT(deckeyval, '.sup.num'))
	LIMIT 1;		
    IF val100 IS NULL THEN
	SET retVal = NULL;
        SET iRetVal = NULL;		
    ELSE
	SET retVal = valueIn * (val100 / 100000.0);
        SET iRetVal = CAST(retVal * 1000.0 AS SIGNED);
    END IF;
    IF valNum IS NOT NULL AND (iRetVal IS NULL OR iRetVal > valNum) THEN
	SET retVal = CAST(valNum AS DECIMAL(10,5)) / 1000.0;
    END IF;	
    IF (retVal IS NULL) THEN
        RETURN NULL;
    ELSE
	RETURN CAST((valueIn + retVal) * 1000.0 AS SIGNED);
    END IF;
END$$
DELIMITER ;
