details: https://code.openbravo.com/erp/devel/pi/rev/f7cf83905689 changeset: 33752:f7cf83905689 user: Mark <markmm82 <at> gmail.com> date: Sun Mar 25 18:46:54 2018 -0400 summary: Fixes issue 38163: Tax adjustment must be done in the closest variance tax
Adjusted the tax line that stays closest to the original amount (the one with the lowest rounded tax amount - not rounded tax amount + adjustment amount). details: https://code.openbravo.com/erp/devel/pi/rev/ca0f109a23f9 changeset: 33753:ca0f109a23f9 user: Mark <markmm82 <at> gmail.com> date: Sun Mar 25 18:46:54 2018 -0400 summary: Related to issue 38163: Fixes TaxesTest suite Fixes some tests of the TaxesTest suite, adapting them to the new adjustment logic. diffstat: src-db/database/model/functions/C_INVOICELINETAX_ROUNDING.xml | 21 ++- src-db/database/model/functions/C_INVOICELINETAX_ROUNDING_AMT.xml | 9 +- src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml | 6 +- src-db/database/model/functions/C_INVOICETAX_ROUNDING.xml | 20 ++- src-db/database/model/functions/C_ORDERLINETAX_ROUNDING.xml | 21 ++- src-db/database/model/functions/C_ORDERLINETAX_ROUNDING_AMT.xml | 9 +- src-db/database/model/functions/C_ORDERTAX_ADJUSTMENT.xml | 6 +- src-db/database/model/functions/C_ORDERTAX_ROUNDING.xml | 16 ++- src-db/database/model/triggers/C_INVOICELINE_TRG2.xml | 4 +- src-db/database/model/triggers/C_ORDERLINE_TRG2.xml | 4 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData117.java | 12 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData118.java | 16 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData119.java | 16 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData120.java | 20 ++-- src-test/src/org/openbravo/test/taxes/data/TaxesTestData125.java | 16 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData126.java | 16 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData127.java | 20 ++-- src-test/src/org/openbravo/test/taxes/data/TaxesTestData128.java | 20 ++-- src-test/src/org/openbravo/test/taxes/data/TaxesTestData133.java | 10 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData134.java | 8 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData135.java | 8 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData136.java | 8 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData141.java | 38 +++--- src-test/src/org/openbravo/test/taxes/data/TaxesTestData142.java | 38 +++--- src-test/src/org/openbravo/test/taxes/data/TaxesTestData143.java | 48 +++++----- src-test/src/org/openbravo/test/taxes/data/TaxesTestData144.java | 48 +++++----- src-test/src/org/openbravo/test/taxes/data/TaxesTestData149.java | 12 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData150.java | 12 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData151.java | 20 ++-- src-test/src/org/openbravo/test/taxes/data/TaxesTestData152.java | 20 ++-- src-test/src/org/openbravo/test/taxes/data/TaxesTestData157.java | 8 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData158.java | 8 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData159.java | 8 +- src-test/src/org/openbravo/test/taxes/data/TaxesTestData160.java | 8 +- 34 files changed, 283 insertions(+), 271 deletions(-) diffs (truncated from 1441 to 300 lines): diff -r ecca97a382c8 -r ca0f109a23f9 src-db/database/model/functions/C_INVOICELINETAX_ROUNDING.xml --- a/src-db/database/model/functions/C_INVOICELINETAX_ROUNDING.xml Mon Mar 26 13:00:11 2018 +0000 +++ b/src-db/database/model/functions/C_INVOICELINETAX_ROUNDING.xml Sun Mar 25 18:46:54 2018 -0400 @@ -10,8 +10,8 @@ <parameter name="p_netamount" type="NUMERIC" mode="in"> <default/> </parameter> - <parameter name="p_linefrom" type="NUMERIC" mode="in"> - <default><![CDATA[0]]></default> + <parameter name="p_stdprecision" type="NUMERIC" mode="in"> + <default/> </parameter> <body><![CDATA[/************************************************************************* * The contents of this file are subject to the Openbravo Public License @@ -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): ______________________________________. ************************************************************************/ @@ -40,18 +40,21 @@ v_expected_tax_amt := p_grossamount - p_netamount; SELECT SUM(taxamt) INTO v_current_tax_amt FROM c_invoicelinetax - WHERE c_invoiceline_id = p_invoiceline_id and line > p_linefrom; + WHERE c_invoiceline_id = p_invoiceline_id; 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_invoicelinetax ilt + JOIN c_tax t + ON ilt.c_tax_id = t.c_tax_id + WHERE ilt.c_invoiceline_id = p_invoiceline_id + ORDER BY ABS(ilt.taxamt - (round(ilt.taxbaseamt, p_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) WHERE c_invoicelinetax_id = cur_invoicelinetax.c_invoicelinetax_id ; - C_INVOICELINETAX_ROUNDING_AMT(p_invoiceline_id, cur_invoicelinetax.c_tax_id, v_expected_tax_amt - v_current_tax_amt, p_linefrom); + C_INVOICELINETAX_ROUNDING_AMT(p_invoiceline_id, cur_invoicelinetax.c_tax_id, v_expected_tax_amt - v_current_tax_amt); EXIT; END LOOP; END IF; diff -r ecca97a382c8 -r ca0f109a23f9 src-db/database/model/functions/C_INVOICELINETAX_ROUNDING_AMT.xml --- a/src-db/database/model/functions/C_INVOICELINETAX_ROUNDING_AMT.xml Mon Mar 26 13:00:11 2018 +0000 +++ b/src-db/database/model/functions/C_INVOICELINETAX_ROUNDING_AMT.xml Sun Mar 25 18:46:54 2018 -0400 @@ -10,9 +10,6 @@ <parameter name="p_diff_amount" type="NUMERIC" mode="in"> <default/> </parameter> - <parameter name="p_linefrom" type="NUMERIC" mode="in"> - <default/> - </parameter> <body><![CDATA[/************************************************************************* * The contents of this file are subject to the Openbravo Public License * Version 1.1 (the "License"), being the Mozilla Public License @@ -25,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) 2015 Openbravo SLU +* All portions are Copyright (C) 2015-2018 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ @@ -38,13 +35,13 @@ 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 + WHERE c_invoiceline_id = p_invoiceline_id AND c_tax_id = (SELECT tb.c_tax_id FROM c_tax tb WHERE tb.c_taxbase_id = p_tax_id) ) LOOP UPDATE c_invoicelinetax SET taxbaseamt = taxbaseamt + p_diff_amount WHERE c_invoicelinetax_id = cur_invoicelinetax.c_invoicelinetax_id; - C_INVOICELINETAX_ROUNDING_AMT(p_invoiceline_id, cur_invoicelinetax.c_tax_id, p_diff_amount, p_linefrom); + C_INVOICELINETAX_ROUNDING_AMT(p_invoiceline_id, cur_invoicelinetax.c_tax_id, p_diff_amount); END LOOP; EXCEPTION WHEN OTHERS THEN diff -r ecca97a382c8 -r ca0f109a23f9 src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml --- a/src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml Mon Mar 26 13:00:11 2018 +0000 +++ b/src-db/database/model/functions/C_INVOICETAX_ADJUSTMENT.xml Sun Mar 25 18:46:54 2018 -0400 @@ -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 -r ecca97a382c8 -r ca0f109a23f9 src-db/database/model/functions/C_INVOICETAX_ROUNDING.xml --- a/src-db/database/model/functions/C_INVOICETAX_ROUNDING.xml Mon Mar 26 13:00:11 2018 +0000 +++ b/src-db/database/model/functions/C_INVOICETAX_ROUNDING.xml Sun Mar 25 18:46:54 2018 -0400 @@ -10,6 +10,9 @@ <parameter name="p_totallines" type="NUMERIC" mode="in"> <default/> </parameter> + <parameter name="p_stdprecision" type="NUMERIC" mode="in"> + <default/> + </parameter> <body><![CDATA[/************************************************************************* * The contents of this file are subject to the Openbravo Public License * Version 1.1 (the "License"), being the Mozilla Public License @@ -22,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-2015 Openbravo SLU +* All portions are Copyright (C) 2012-2018 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ @@ -42,16 +45,19 @@ SELECT COALESCE(p_totallines, 0) + COALESCE(SUM(taxamt),0) INTO v_current_gross_amt FROM c_invoicetax WHERE c_invoice_id = p_invoice_id - AND recalculate = 'Y'; + AND recalculate = 'Y'; 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_invoicetax it + 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, p_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 -r ecca97a382c8 -r ca0f109a23f9 src-db/database/model/functions/C_ORDERLINETAX_ROUNDING.xml --- a/src-db/database/model/functions/C_ORDERLINETAX_ROUNDING.xml Mon Mar 26 13:00:11 2018 +0000 +++ b/src-db/database/model/functions/C_ORDERLINETAX_ROUNDING.xml Sun Mar 25 18:46:54 2018 -0400 @@ -10,8 +10,8 @@ <parameter name="p_netamount" type="NUMERIC" mode="in"> <default/> </parameter> - <parameter name="p_linefrom" type="NUMERIC" mode="in"> - <default><![CDATA[0]]></default> + <parameter name="p_stdprecision" type="NUMERIC" mode="in"> + <default/> </parameter> <body><