salix/db/routines/stock/procedures/outbound_sync.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `stock`.`outbound_sync`(vSelf INT)
BEGIN
/**
 * Attaches a outbound with available inbounds.
 *
 * @param vSelf The outbound reference
 */
	DECLARE vDated DATETIME;
	DECLARE vItem INT;
	DECLARE vWarehouse INT;
	DECLARE vLack INT;
	DECLARE vSupplied INT;
	DECLARE vSuppliedFromRequest INT;
	DECLARE vInboundFk INT;
	DECLARE vAvailable INT;
	DECLARE vHasPicks BOOL;
	DECLARE vDone BOOL;

	DECLARE vInbounds CURSOR FOR
		SELECT id, available, available < quantity
			FROM inbound
			WHERE warehouseFk = vWarehouse
				AND itemFk = vItem
				AND dated <= vDated
				AND (expired IS NULL OR expired > vDated)
			ORDER BY dated;

	DECLARE CONTINUE HANDLER FOR NOT FOUND
		SET vDone = TRUE;

	SELECT warehouseFk, itemFk, dated, lack
		INTO vWarehouse, vItem, vDated, vLack
		FROM outbound
		WHERE id = vSelf;

	OPEN vInbounds;

	myLoop: LOOP
		SET vDone = FALSE;
		FETCH vInbounds INTO vInboundFk, vAvailable, vHasPicks;

		IF vDone THEN
			LEAVE myLoop;
		END IF;

		SET vSupplied = LEAST(vLack, vAvailable);

		IF vSupplied > 0 THEN
			SET vLack = vLack - vSupplied;
			UPDATE inbound
				SET available = available - vSupplied
				WHERE id = vInboundFk;
		END IF;

		IF vHasPicks AND vLack > 0 THEN
			CALL inbound_requestQuantity(vInboundFk, vLack, vDated, vSuppliedFromRequest);
			SET vSupplied = vSupplied + vSuppliedFromRequest;
			SET vLack = vLack - vSuppliedFromRequest;
		END IF;

		IF vSupplied > 0 THEN
			CALL inbound_addPick(vInboundFk, vSelf, vSupplied);
		END IF;

		IF vLack = 0 THEN
			LEAVE myLoop;
		END IF;
	END LOOP;

	CLOSE vInbounds;

	UPDATE outbound
		SET isSync = TRUE,
			lack = vLack
		WHERE id = vSelf;
END$$
DELIMITER ;