salix/db/routines/vn/functions/hasSomeNegativeBase.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`hasSomeNegativeBase`(vTicket INT)
	RETURNS tinyint(1)
	NOT DETERMINISTIC
	READS SQL DATA
BEGIN
	DECLARE vCountry INT;
	DECLARE hasSomeNegativeBase BOOLEAN;

	SELECT s.countryFk 
			INTO vCountry
		FROM supplier s
			JOIN ticket t ON t.companyFk = s.id
		WHERE t.id = vTicket;

	SELECT COUNT(*) INTO hasSomeNegativeBase
		FROM (
			SELECT SUM(ROUND(s.quantity * s.price * (100 - s.discount)/100,2)) taxableBase
				FROM sale s 
					JOIN item i ON i.id = s.itemFk
					JOIN itemTaxCountry itc 
						ON itc.itemFk = i.id AND itc.countryFk = vCountry
					WHERE s.ticketFk = vTicket
				GROUP BY itc.taxClassFk
				HAVING taxableBase < 0
		) t1 ;

	RETURN hasSomeNegativeBase;
END$$
DELIMITER ;