0038163: Tax calculation adjustment must be done in the highest variance tax

# HG changeset patch
# User Alvaro Ferraz <alvaro.ferraz@openbravo.com>
# Date 1521656056 -3600
#      Wed Mar 21 19:14:16 2018 +0100
# Node ID a2fa7a2bca240d5e3589c1049e69a67ef0771583
# Parent  922ddf36e81c56a1320e2d9b66ecd6c54988be5c
Fixes issue 38163

diff --git a/src-db/database/model/functions/C_INVOICELINETAX_ROUNDING.xml b/src-db/database/model/functions/C_INVOICELINETAX_ROUNDING.xml
--- a/src-db/database/model/functions/C_INVOICELINETAX_ROUNDING.xml
+++ b/src-db/database/model/functions/C_INVOICELINETAX_ROUNDING.xml
@@ -25,7 +25,7 @@
 * under the License.
 * The Original Code is Openbravo ERP.
 * The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2012-2016 Openbravo SLU
+* All portions are Copyright (C) 2012-2018 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -42,11 +42,21 @@
   FROM c_invoicelinetax
   WHERE c_invoiceline_id = p_invoiceline_id and line > p_linefrom;
   IF (v_expected_tax_amt <> v_current_tax_amt) THEN
+    -- Adjust the tax line that stays closest to the original amount (the one with the lowest rounded tax amount - not rounded tax amount + adjustment amount)
     FOR cur_invoicelinetax IN (
-      SELECT c_invoicelinetax_id, c_tax_id
-      FROM c_invoicelinetax
-      WHERE c_invoiceline_id = p_invoiceline_id and line > p_linefrom
-      ORDER BY ABS(taxamt) desc
+      SELECT ilt.c_invoicelinetax_id, ilt.c_tax_id
+      FROM c_invoice i
+      JOIN c_currency c
+      ON i.c_currency_id = c.c_currency_id
+      JOIN c_invoiceline il
+      ON i.c_invoice_id = il.c_invoice_id
+      JOIN c_invoicelinetax ilt
+      ON il.c_invoiceline_id = ilt.c_invoiceline_id
+      JOIN c_tax t
+      ON ilt.c_tax_id = t.c_tax_id
+      WHERE ilt.c_invoiceline_id = p_invoiceline_id
+      AND ilt.line > p_linefrom
+      ORDER BY ABS(ilt.taxamt - (round(ilt.taxbaseamt, c.stdprecision) * (t.rate/100)) - (v_current_tax_amt - v_expected_tax_amt)) ASC, ABS(ilt.taxamt) DESC, t.line DESC
     ) LOOP
       UPDATE c_invoicelinetax
       SET taxamt = taxamt - (v_current_tax_amt - v_expected_tax_amt)
diff --git a/src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml b/src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml
--- a/src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml
+++ b/src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml
@@ -22,7 +22,7 @@
 * under the License.
 * The Original Code is Openbravo ERP.
 * The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2017 Openbravo SLU
+* All portions are Copyright (C) 2017-2018 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -89,7 +89,7 @@
       ORDER BY it.Line
     )
     LOOP
-      -- Adjust TaxAmt
+      -- Adjust the tax that stays closest to the original amount (the one with the lowest rounded tax amount - not rounded tax amount + adjustment amount)
       IF (CUR_Tax.TaxAdjustment <> 0) THEN
         FOR CUR_Line IN (
           SELECT it.C_InvoiceTax_ID, it.C_Tax_ID, t.Line
@@ -99,7 +99,7 @@
           WHERE it.C_Invoice_ID = p_invoice_id
           AND it.Recalculate = 'Y'
           AND c_tax_get_root(it.C_Tax_ID) = CUR_Tax.C_Tax_ID
-          ORDER BY ABS(it.TaxAmt) DESC, t.Line DESC
+          ORDER BY ABS(it.TaxAmt - (it.TaxBaseAmt * (t.Rate/100)) - CUR_Tax.TaxAdjustment) ASC, ABS(it.TaxAmt) DESC, t.Line DESC
         )
         LOOP
           UPDATE C_INVOICETAX
diff --git a/src-db/database/model/functions/C_INVOICETAX_ROUNDING.xml b/src-db/database/model/functions/C_INVOICETAX_ROUNDING.xml
--- a/src-db/database/model/functions/C_INVOICETAX_ROUNDING.xml
+++ b/src-db/database/model/functions/C_INVOICETAX_ROUNDING.xml
@@ -22,7 +22,7 @@
 * under the License.
 * The Original Code is Openbravo ERP.
 * The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2012-2015 Openbravo SLU
+* All portions are Copyright (C) 2012-2018 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -46,12 +46,19 @@
   END IF;
   
   IF (p_grandtotal <> v_current_gross_amt) THEN
+    -- Adjust the tax that stays closest to the original amount (the one with the lowest rounded tax amount - not rounded tax amount + adjustment amount)
     FOR cur_invoicetax IN (
-      SELECT c_invoicetax_id
-      FROM c_invoicetax
-      WHERE c_invoice_id = p_invoice_id
-        AND recalculate = 'Y'
-      ORDER BY ABS(taxamt) desc
+      SELECT it.c_invoicetax_id
+      FROM c_invoice i
+      JOIN c_currency c
+      ON i.c_currency_id = c.c_currency_id
+      JOIN c_invoicetax it
+      ON i.c_invoice_id = it.c_invoice_id
+      JOIN c_tax t
+      ON it.c_tax_id = t.c_tax_id
+      WHERE it.c_invoice_id = p_invoice_id
+      AND it.recalculate = 'Y'
+      ORDER BY ABS(it.taxamt - (round(it.taxbaseamt, c.stdprecision) * (t.rate/100)) - (v_current_gross_amt - COALESCE(p_grandtotal, 0))) ASC, ABS(it.taxamt) DESC, t.line DESC
     ) LOOP
       UPDATE c_invoicetax
       SET taxamt = taxamt - (v_current_gross_amt - COALESCE(p_grandtotal, 0))
diff --git a/src-db/database/model/functions/C_ORDERLINETAX_ROUNDING.xml b/src-db/database/model/functions/C_ORDERLINETAX_ROUNDING.xml
--- a/src-db/database/model/functions/C_ORDERLINETAX_ROUNDING.xml
+++ b/src-db/database/model/functions/C_ORDERLINETAX_ROUNDING.xml
@@ -25,7 +25,7 @@
 * under the License.
 * The Original Code is Openbravo ERP.
 * The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2012-2016 Openbravo SLU
+* All portions are Copyright (C) 2012-2018 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -42,11 +42,21 @@
   FROM c_orderlinetax
   WHERE c_orderline_id = p_orderline_id and line > p_linefrom;
   IF (v_expected_tax_amt <> v_current_tax_amt) THEN
+    -- Adjust the tax line that stays closest to the original amount (the one with the lowest rounded tax amount - not rounded tax amount + adjustment amount)
     FOR cur_orderlinetax IN (
-      SELECT c_orderlinetax_id, c_tax_id
-      FROM c_orderlinetax
-      WHERE c_orderline_id = p_orderline_id and line > p_linefrom
-      ORDER BY ABS(taxamt) desc
+      SELECT olt.c_orderlinetax_id, olt.c_tax_id
+      FROM c_order o
+      JOIN c_currency c
+      ON o.c_currency_id = c.c_currency_id
+      JOIN c_orderline ol
+      ON o.c_order_id = ol.c_order_id
+      JOIN c_orderlinetax olt
+      ON ol.c_orderline_id = olt.c_orderline_id
+      JOIN c_tax t
+      ON olt.c_tax_id = t.c_tax_id
+      WHERE olt.c_orderline_id = p_orderline_id
+      AND olt.line > p_linefrom
+      ORDER BY ABS(olt.taxamt - (round(olt.taxbaseamt, c.stdprecision) * (t.rate/100)) - (v_current_tax_amt - v_expected_tax_amt)) ASC, ABS(olt.taxamt) DESC, t.line DESC
     ) LOOP
       UPDATE c_orderlinetax
       SET taxamt = taxamt - (v_current_tax_amt - v_expected_tax_amt)
diff --git a/src-db/database/model/functions/C_ORDERTAX_ADJUSTMENT.xml b/src-db/database/model/functions/C_ORDERTAX_ADJUSTMENT.xml
--- a/src-db/database/model/functions/C_ORDERTAX_ADJUSTMENT.xml
+++ b/src-db/database/model/functions/C_ORDERTAX_ADJUSTMENT.xml
@@ -22,7 +22,7 @@
 * under the License.
 * The Original Code is Openbravo ERP.
 * The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2017 Openbravo SLU
+* All portions are Copyright (C) 2017-2018 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -86,7 +86,7 @@
       ORDER BY ot.Line
     )
     LOOP
-      -- Adjust TaxAmt
+      -- Adjust the tax that stays closest to the original amount (the one with the lowest rounded tax amount - not rounded tax amount + adjustment amount)
       IF (CUR_Tax.TaxAdjustment <> 0) THEN
         FOR CUR_Line IN (
           SELECT ot.C_OrderTax_ID, ot.C_Tax_ID, t.Line
@@ -95,7 +95,7 @@
           ON ot.C_Tax_ID = t.C_Tax_ID
           WHERE ot.C_Order_ID = p_order_id
           AND c_tax_get_root(ot.C_Tax_ID) = CUR_Tax.C_Tax_ID
-          ORDER BY ABS(ot.TaxAmt) DESC, t.Line DESC
+          ORDER BY ABS(ot.TaxAmt - (ot.TaxBaseAmt * (t.Rate/100)) - CUR_Tax.TaxAdjustment) ASC, ABS(ot.TaxAmt) DESC, t.Line DESC
         )
         LOOP
           UPDATE C_ORDERTAX
diff --git a/src-db/database/model/functions/C_ORDERTAX_ROUNDING.xml b/src-db/database/model/functions/C_ORDERTAX_ROUNDING.xml
--- a/src-db/database/model/functions/C_ORDERTAX_ROUNDING.xml
+++ b/src-db/database/model/functions/C_ORDERTAX_ROUNDING.xml
@@ -22,7 +22,7 @@
 * under the License.
 * The Original Code is Openbravo ERP.
 * The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2012-2015 Openbravo SLU
+* All portions are Copyright (C) 2012-2018 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -50,11 +50,18 @@
   END IF;
 
   IF (p_grandtotal <> v_current_gross_amt) THEN
+    -- Adjust the tax that stays closest to the original amount (the one with the lowest rounded tax amount - not rounded tax amount + adjustment amount)
     FOR cur_ordertax IN (
-      SELECT c_ordertax_id
-      FROM c_ordertax
-      WHERE c_order_id = p_order_id
-      ORDER BY ABS(taxamt) desc
+      SELECT ot.c_ordertax_id
+      FROM c_order o
+      JOIN c_currency c
+      ON o.c_currency_id = c.c_currency_id
+      JOIN c_ordertax ot
+      ON o.c_order_id = ot.c_order_id
+      JOIN c_tax t
+      ON ot.c_tax_id = t.c_tax_id
+      WHERE ot.c_order_id = p_order_id
+      ORDER BY ABS(ot.taxamt - (round(ot.taxbaseamt, c.stdprecision) * (t.rate/100)) - (v_current_gross_amt - COALESCE(p_grandtotal, 0))) ASC, ABS(ot.taxamt) DESC, t.line DESC
     ) LOOP
       UPDATE c_ordertax
       SET taxamt = taxamt - (v_current_gross_amt - COALESCE(p_grandtotal, 0))