salix/db/routines/vn/procedures/itemShelving_getAlternatives.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`itemShelving_getAlternatives`(
	vShelvingCode VARCHAR(10)
)
BEGIN
/**
 * Devuelve un listado de posibles ubicaciones alternativas a ubicar
 * los item de la matricula del carro que se le ha pasado.
 *
 * @param vShelvingCode Matricula del carro
 */
	DECLARE vShelvingFk INT;

	SELECT id INTO vShelvingFk
		FROM shelving
		WHERE code COLLATE utf8_unicode_ci = vShelvingCode;

	SELECT is2.id,is2.shelvingFk, p.code, is2.itemFk , is2.visible, p.pickingOrder 
		FROM itemShelving is2
			JOIN shelving sh ON sh.id = is2.shelvingFk
			JOIN parking p ON p.id = sh.parkingFk
			JOIN sector s ON s.id = p.sectorFk
			LEFT JOIN operator o ON  o.sectorFk = s.id
				AND o.workerFk = account.myUser_getId()
			JOIN warehouse wh ON wh.id = s.warehouseFk
			JOIN itemShelving is3 ON is3.itemFk = is2.itemFk
				AND is3.shelvingFk = vShelvingFk
		WHERE is2.shelvingFk <> vShelvingFk
		GROUP BY is2.id
		ORDER BY p.pickingOrder DESC;
END$$
DELIMITER ;