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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`available_traslate`(
	vWarehouseLanding INT,
	vDated DATE,
	vWarehouseShipment INT)
proc: BEGIN
/**
 * Calcular la disponibilidad dependiendo del almacen
 * de origen y destino según la fecha.
 *
 * @param vWarehouseLanding Almacén de llegada
 * @param vDated Fecha del calculo para la disponibilidad de articulos
 * @param vWarehouseShipment Almacén de destino
 */
	DECLARE vDatedFrom DATE;
	DECLARE vDatedTo DATETIME;
	DECLARE vDatedReserve DATETIME;
	DECLARE vDatedInventory DATE;

	IF vDated < util.VN_CURDATE() THEN
		LEAVE proc;
	END IF;

	CALL item_getStock (vWarehouseLanding, vDated, NULL);

	-- Calcula algunos parámetros necesarios.
	SET vDatedFrom = vDated;
	SET vDatedTo = util.dayEnd (vDated + INTERVAL 4 DAY);
	SELECT inventoried INTO vDatedInventory FROM config;
	SELECT SUBTIME(util.VN_NOW(), reserveTime) INTO vDatedReserve
		FROM hedera.orderConfig;

	-- Calcula el ultimo dia de vida para cada producto.
	CREATE OR REPLACE TEMPORARY TABLE tItemRange
		(PRIMARY KEY (itemFk))
		ENGINE = MEMORY
		SELECT c.itemFk, MAX(t.landed) dated
			FROM buy c
				JOIN entry e ON c.entryFk = e.id
				JOIN travel t ON t.id = e.travelFk
				JOIN warehouse w ON w.id = t.warehouseInFk
			WHERE t.landed BETWEEN vDatedInventory AND vDatedFrom
				AND t.warehouseInFk = vWarehouseLanding
				AND NOT e.isExcludedFromAvailable
				AND NOT t.isRaid
			GROUP BY c.itemFk;

	-- Tabla con el ultimo dia de last_buy para cada producto
	-- que hace un replace de la anterior.
	CALL buy_getUltimate (NULL, vWarehouseShipment, util.VN_CURDATE());

	INSERT INTO tItemRange
		SELECT t.itemFk, tr.landed
			FROM tmp.buyUltimate t
				JOIN buy b ON b.id = t.buyFk
				JOIN entry e ON e.id = b.entryFk
				JOIN travel tr ON tr.id = e.travelFk
				LEFT JOIN tItemRange i ON t.itemFk = i.itemFk
			WHERE t.warehouseFk = vWarehouseShipment
				AND NOT tr.isRaid
	ON DUPLICATE KEY UPDATE tItemRange.dated = GREATEST(tItemRange.dated,
	tr.landed);

	CREATE OR REPLACE TEMPORARY TABLE tItemRangeLive
		(PRIMARY KEY (itemFk))
		ENGINE = MEMORY
		SELECT ir.itemFk, util.dayEnd(ir.dated + INTERVAL it.life DAY) dated
			FROM tItemRange ir
				JOIN item i ON i.id = ir.itemFk
				JOIN itemType it ON it.id = i.typeFk
			HAVING dated >= vDatedFrom OR dated IS NULL;

	-- Calcula el ATP.
	CREATE OR REPLACE TEMPORARY TABLE tmp.itemCalc
		(INDEX (itemFk,warehouseFk))
		ENGINE = MEMORY
		SELECT i.itemFk,
				vWarehouseLanding warehouseFk,
				i.shipped dated,
				i.quantity
			FROM itemTicketOut i
				JOIN tItemRangeLive ir ON ir.itemFK = i.itemFk
			WHERE i.shipped >= vDatedFrom
				AND (ir.dated IS NULL OR i.shipped <= ir.dated)
				AND i.warehouseFk = vWarehouseLanding
		UNION ALL
		SELECT b.itemFk,
				vWarehouseLanding,
				t.landed,
				b.quantity
			FROM buy b
				JOIN entry e ON b.entryFk = e.id
				JOIN travel t ON t.id = e.travelFk
				JOIN tItemRangeLive ir ON ir.itemFk = b.itemFk
			WHERE NOT e.isExcludedFromAvailable
				AND b.quantity <> 0
				AND NOT t.isRaid
				AND t.warehouseInFk = vWarehouseLanding
				AND t.landed >= vDatedFrom
				AND (ir.dated IS NULL OR t.landed <= ir.dated)
		UNION ALL
		SELECT i.itemFk, vWarehouseLanding, i.shipped, i.quantity
			FROM itemEntryOut i
				JOIN tItemRangeLive ir ON ir.itemFk = i.itemFk
			WHERE i.shipped >= vDatedFrom
				AND (ir.dated IS NULL OR i.shipped <= ir.dated)
				AND i.warehouseOutFk = vWarehouseLanding
		UNION ALL
		SELECT r.item_id, vWarehouseLanding, r.shipment, -r.amount
			FROM hedera.order_row r
				JOIN hedera.`order` o ON o.id = r.order_id
				JOIN tItemRangeLive ir ON ir.itemFk = r.item_id
			WHERE r.shipment >= vDatedFrom
				AND (ir.dated IS NULL OR r.shipment <= ir.dated)
				AND r.warehouse_id = vWarehouseLanding
				AND r.created >= vDatedReserve
				AND NOT o.confirmed;

	CALL item_getAtp(vDated);

	CREATE OR REPLACE TEMPORARY TABLE tmp.availableTraslate
		(PRIMARY KEY (item_id))
		ENGINE = MEMORY
		SELECT t.item_id, SUM(stock) available
			FROM (
				SELECT ti.itemFk item_id, stock
					FROM tmp.itemList ti
						JOIN tItemRange ir ON ir.itemFk = ti.itemFk
				UNION ALL
				SELECT itemFk, quantity
					FROM tmp.itemAtp
				) t
			GROUP BY t.item_id
			HAVING available <> 0;

	DROP TEMPORARY TABLE tmp.itemList, tItemRange, tItemRangeLive;
END$$
DELIMITER ;