salix/db/routines/vn/functions/itemShelvingPlacementSupply_ClosestGet.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`itemShelvingPlacementSupply_ClosestGet`(vParkingFk INT)
	RETURNS int(11)
	NOT DETERMINISTIC
	READS SQL DATA
BEGIN

	/* Devuelve el parking más cercano.*/
	DECLARE vColumn INT;
    DECLARE vClosestParkingFk INT;
    DECLARE vSectorFk INT;
    
	SELECT p.column, sectorFk INTO vColumn, vSectorFk
		FROM vn.parking p
        WHERE p.id = vParkingFk;
    
    SELECT itemShelvingFk INTO vClosestParkingFk
		FROM
		(
			SELECT ABS(p.column - vColumn) as distance, itemShelvingFk
				FROM vn.itemShelvingPlacementSupplyStock ispss
					JOIN vn.parking p ON p.id = ispss.parkingFk
                    JOIN vn.itemPlacementSupplyList ipsl ON ipsl.sectorFk = ispss.sectorFk AND ipsl.itemFk = ispss.itemFk
				WHERE p.sectorFk = vSectorFk
					AND ipsl.saldo > 0
		) sub 
        ORDER BY distance
        LIMIT 1;
            
	RETURN vClosestParkingFk;
    
END$$
DELIMITER ;