DELIMITER $$

CREATE function sup_hierarchy(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(erep_sup_emp_number)
                INTO    @id
                FROM    hs_hr_emp_reportto
                WHERE   erep_sub_emp_number = _parent
                        AND erep_sup_emp_number > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  erep_sup_emp_number, erep_sub_emp_number
                INTO    _id, _parent
                FROM    erep_sub_emp_number
                WHERE   erep_sup_emp_number = _parent;
        END LOOP;
END;$$