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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`cmrPallet_add`(vExpeditionPalletFk INT, vCmrFk INT)
BEGIN
/**
 * Añade registro a tabla cmrPallet.
 *
 * @param vExpeditionPalletFk Id de expeditionPallet
 * @param vCmrFk Id de cmrPallet
 */
	DECLARE	vIsACurrentExpeditionPallet BOOL;
	DECLARE	vIsACurrentCMR BOOL;
	DECLARE vYesterday DATE;

	SET vYesterday = util.yesterday();

	SELECT COUNT(*) INTO vIsACurrentExpeditionPallet
	FROM vn.expeditionPallet cp
		WHERE cp.id = vExpeditionPalletFk
				AND cp.built >= vYesterday;

	IF !vIsACurrentExpeditionPallet THEN
		CALL util.throw('expeditionPalletNotExist');
	END IF;

	SELECT COUNT(*) INTO vIsACurrentCMR
	FROM vn.cmr c
		WHERE c.id = vCmrFk
				AND c.created >= vYesterday;

	IF vIsACurrentCMR THEN
		INSERT INTO cmrPallet (cmrFk, expeditionPalletFk)
					VALUES(vCmrFk, vExpeditionPalletFk);
	ELSE
		Call util.throw('cmrNotExist');
	END IF;
END$$
DELIMITER ;