# HG changeset patch # User Alvaro Ferraz # 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))