salix/db/routines/cache/procedures/available_updateItem.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `cache`.`available_updateItem`(
	`vItem` INT,
	`vWarehouse` INT,
	`vDated` DATE,
	`vQuantity` INT
)
BEGIN
/**
 * Immediately deduct/add an amount from the available cache (if exists).
 *
 * @param vItem The item id
 * @param vWarehouse The warehouse id
 * @param vDated Available cache date
 * @param vQuantity The amount to be deducted from the cache
 */
	DECLARE vCalc INT;

	SELECT id INTO vCalc
		FROM cache_calc
		WHERE cacheName = 'available'
			AND params = CONCAT_WS('/', vWarehouse, vDated);

	IF vCalc IS NOT NULL THEN
		UPDATE available
			SET available = available - vQuantity
			WHERE calc_id = vCalc
				AND item_id = vItem;
	END IF;
END$$
DELIMITER ;