salix/db/routines/hedera/procedures/order_getTax.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `hedera`.`order_getTax`()
	READS SQL DATA
BEGIN
/**
 * Calcula el IVA, y el recargo de equivalencia de un pedido
 * desglosados por tipos.
 *
 * @param vOrder El identificador del pedido
 * @return tmp.orderTax Bases imponibles, IVA y recargo de equivalencia
 */
 	-- No poner create or replace, ya que da problemas
	DROP TEMPORARY TABLE IF EXISTS tmp.addressCompany;
	CREATE TEMPORARY TABLE tmp.addressCompany
		(INDEX (addressFk, companyFk))
		ENGINE = MEMORY 
		SELECT DISTINCT o.address_id addressFk, o.company_id companyFk
			FROM tmp.`order` tmpOrder
				JOIN `order` o ON o.id = tmpOrder.orderFk;

	CALL vn.addressTaxArea();

	-- Calcula el IVA y el recargo desglosado.
	CREATE OR REPLACE TEMPORARY TABLE tmp.orderTax
		(PRIMARY KEY (orderFk, code, rate))
		ENGINE = MEMORY
		WITH orders AS (
			SELECT tor.orderFk,
					oro.amount * oro.price total,
					s.countryFk,
					ata.areaFk,
					itc.taxClassFk
				FROM hedera.orderRow oro
					JOIN tmp.order tor ON tor.orderFk = oro.orderFk
					JOIN hedera.`order` o ON o.id = tor.orderFk
					JOIN vn.item i ON i.id = oro.itemFk
					JOIN vn.`client` c ON c.id = o.customer_id
					JOIN vn.supplier s ON s.id = o.company_id
					JOIN tmp.addressTaxArea ata ON ata.addressFk = o.address_id
						AND ata.companyFk = o.company_id
					JOIN vn.itemTaxCountry itc ON itc.itemFk = i.id
						AND itc.countryFk = s.countryFk
				HAVING total
		)
		SELECT o.orderFk,
				tc.code,
				SUM(o.total) taxableBase,
				pgc.rate
			FROM orders o
				JOIN vn.bookingPlanner bp ON bp.countryFk = o.countryFk
					AND bp.taxAreaFk = o.areaFk
					AND bp.taxClassFk = o.taxClassFk
				JOIN vn.pgc ON pgc.code = bp.pgcFk
				JOIN vn.taxClass tc ON tc.id = bp.taxClassFk
			GROUP BY o.orderFk, pgc.code, pgc.rate
			HAVING taxableBase
			ORDER BY bp.priority;
					
	-- No poner create or replace, ya que da problemas
	DROP TEMPORARY TABLE IF EXISTS tmp.orderAmount;
	CREATE TEMPORARY TABLE tmp.orderAmount
		(INDEX (orderFk))
		ENGINE = MEMORY
		SELECT orderFk, taxableBase, `code`,
				SUM(CAST(taxableBase * rate / 100 AS DECIMAL(10, 2))) tax
			FROM tmp.orderTax
			GROUP BY orderFk, `code`;
		
	DROP TEMPORARY TABLE tmp.addressTaxArea;
END$$
DELIMITER ;