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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`duaTax_doRecalc`(vDuaFk INT)
BEGIN
/**
 * Borra los valores de duaTax y los vuelve a crear en base a la tabla duaEntry
 *  
 * @param vDuaFk Id del dua a recalcular
 */
    DELETE FROM duaTax
		WHERE duaFk = vDuaFk;
        
    INSERT INTO duaTax(duaFk, taxClassFk, base)    
		SELECT vDuaFk, taxClassFk, SUM(sub.totalBase)
			FROM intrastat ist
				JOIN
				(SELECT i.intrastatFk,
						CAST(SUM(b.quantity * 
							b.buyingValue *  
							de.customsValue / 
							de.value)  
								* di.totalAmount / 
								bi.totalAmount AS DECIMAL(10,2)
						) totalBase
					FROM buy b 
						JOIN item i ON i.id = b.itemFk
						JOIN entry e ON e.id = b.entryFk
						JOIN duaEntry de ON de.entryFk = e.id
						JOIN(
							SELECT i.intrastatFk,
									CAST(SUM(b.quantity * 
										b.buyingValue * 
										de.customsValue / 
										de.value) AS DECIMAL(10,2)
									) totalAmount
								FROM buy b 
									JOIN item i ON i.id = b.itemFk
									JOIN entry e ON e.id = b.entryFk
									JOIN duaEntry de ON de.entryFk = e.id
								WHERE de.duaFk = vDuaFk
								GROUP BY i.intrastatFk
						) bi ON bi.intrastatFk = i.intrastatFk
						JOIN(
							SELECT intrastatFk, SUM(amount) totalAmount
								FROM duaIntrastat
                                WHERE duaFk = vDuaFk
                                GROUP BY intrastatFK
                        ) di ON di.intrastatFk = i.intrastatFk
					WHERE de.duaFk = vDuaFk
					GROUP BY i.intrastatFk
					HAVING totalBase 
				)sub ON ist.id = sub.intrastatFk
			GROUP BY ist.taxClassFk;
END$$
DELIMITER ;