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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`parking_setOrder`(vWarehouseFk INT)
BEGIN
	
	/*
	 * Ordena el orden de preparacion de los parking de acuerdo con la tabla vn.corridor
	 */
	
	UPDATE vn.parking p
		JOIN (
			SELECT 	code,
					@orden := @orden + 10 AS po
				FROM (
					SELECT 	c.prepOrder,
							p.code,
							@orden := 0,
							IF(c.isAscendent, 1,-1) * CAST(SUBSTRING(p.code,3,2) AS DECIMAL(2,0)) subCorridor , 
							CAST(IF(LENGTH(p.code) > 5, SUBSTRING(p.code,6,2), 0) AS DECIMAL(2,0)) ubication 
						FROM vn.parking p
							JOIN vn.sector s ON s.id = p.sectorFk 
							JOIN vn.corridor c ON c.code = LEFT(p.code,1) COLLATE utf8_general_ci
						WHERE s.warehouseFk = vWarehouseFk) sub 
				ORDER BY sub.prepOrder, subCorridor, ubication) sub2 ON sub2.code = p.code 
		SET p.pickingOrder = sub2.po;
	
END$$
DELIMITER ;