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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`itemShelvingSale_deleteAdded`(
	vSelf INT(11)
)
proc: BEGIN
/**
 * Borra una reservea devolviendo la cantidad al itemShelving
 *
 * @param vSelf Identificador del itemShelvingSale
 */
	DECLARE vSaleFk INT;
	DECLARE vHasSalesPicked BOOL;

	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
		RESIGNAL;
	END;

	START TRANSACTION;

	SELECT iss.saleFk INTO vSaleFk
		FROM itemShelvingSale iss
			JOIN sale s ON s.id = iss.saleFk
		WHERE iss.id = vSelf AND s.isAdded
		FOR UPDATE;

	IF vSaleFk IS NULL THEN
		CALL util.throw('The sale can not be deleted');
	END IF;

	SELECT COUNT(*) INTO vHasSalesPicked
		FROM itemShelvingSale 
		WHERE saleFk = vSaleFk AND isPicked;

	IF vHasSalesPicked THEN
		CALL util.throw('A sale with picked sales cannot be deleted');
	END IF;

	UPDATE itemShelvingSale iss
				JOIN itemShelving ish ON ish.id = iss.itemShelvingFk
			SET ish.available = ish.available + iss.quantity
			WHERE iss.saleFk = vSaleFk;

	DELETE FROM sale WHERE id = vSaleFk;

	COMMIT;
END$$
DELIMITER ;