details: https://code.openbravo.com/erp/devel/pi/rev/8281d757d8ba changeset: 31691:8281d757d8ba user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Wed Mar 15 14:36:31 2017 +0100 summary: Fixes issue 35486: Net Amount not properly rounded in ERP with PostgreSQL
Problem was in following calculation: Net Amount = Gross Amount * (Gross Amount / (Gross Amount + Tax Amount)). In case Gross Amount = 357.99 and Tax Amount = 71.598, Net Amount = 357.99 * (357.99 / (357.99 + 71.598)) = 298.325. It was correct in Oracle as it retrieves 298.325 but it was failing in PostgreSQL as it retrieves 298.3249999999999999988067: SELECT 357.99 * (357.99 / (357.99 + 71.598)) FROM dual; If we do multiplication before division, we avoid this problem and both PostgreSQL and Oracle retrieves the correct result (298.325): SELECT (357.99 * 357.99) / (357.99 + 71.598) FROM dual; details: https://code.openbravo.com/erp/devel/pi/rev/bb77b1e335cb changeset: 31692:bb77b1e335cb user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Wed Mar 15 14:54:07 2017 +0100 summary: Related to issue 35486: Add some automated tests diffstat: src-db/database/model/functions/C_GET_NET_AMOUNT_FROM_GROSS.xml | 3 +- src-db/database/model/functions/C_GET_NET_PRICE_FROM_GROSS.xml | 5 +- src-test/src/org/openbravo/test/taxes/TaxesTest.java | 24 ++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData161.java | 80 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData162.java | 80 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData163.java | 80 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData164.java | 80 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData165.java | 80 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData166.java | 80 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData167.java | 80 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData168.java | 80 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData169.java | 81 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData170.java | 81 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData171.java | 81 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData172.java | 81 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData173.java | 81 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData174.java | 81 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData175.java | 81 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData176.java | 81 ++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData177.java | 93 ++++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData178.java | 93 ++++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData179.java | 93 ++++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData180.java | 93 ++++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData181.java | 93 ++++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData182.java | 93 ++++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData183.java | 93 ++++++++++ src-test/src/org/openbravo/test/taxes/data/TaxesTestData184.java | 93 ++++++++++ 27 files changed, 2061 insertions(+), 3 deletions(-) diffs (truncated from 2197 to 300 lines): diff -r 1549a54a672f -r bb77b1e335cb src-db/database/model/functions/C_GET_NET_AMOUNT_FROM_GROSS.xml --- a/src-db/database/model/functions/C_GET_NET_AMOUNT_FROM_GROSS.xml Wed Mar 15 09:49:13 2017 -0400 +++ b/src-db/database/model/functions/C_GET_NET_AMOUNT_FROM_GROSS.xml Wed Mar 15 14:54:07 2017 +0100 @@ -42,7 +42,8 @@ END IF; v_TaxAmount := C_GET_TAX_AMT_FROM_NET(p_tax_id, p_grossamt, p_alternatetaxbaseamt, v_calcPrecision, 0); - v_NetAmount := ROUND(p_grossamt * (p_grossamt / (p_grossamt + v_TaxAmount)), v_calcPrecision); + -- Do multiplication before division to avoid wrong calculation in PostgreSQL. See Issue 35486. + v_NetAmount := ROUND((p_grossamt * p_grossamt) / (p_grossamt + v_TaxAmount), v_calcPrecision); IF (p_stdprecision IS NOT NULL) THEN v_NetAmount := ROUND(v_NetAmount, p_stdprecision); END IF; diff -r 1549a54a672f -r bb77b1e335cb src-db/database/model/functions/C_GET_NET_PRICE_FROM_GROSS.xml --- a/src-db/database/model/functions/C_GET_NET_PRICE_FROM_GROSS.xml Wed Mar 15 09:49:13 2017 -0400 +++ b/src-db/database/model/functions/C_GET_NET_PRICE_FROM_GROSS.xml Wed Mar 15 14:54:07 2017 +0100 @@ -28,7 +28,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-2017 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ @@ -46,7 +46,8 @@ END IF; v_TaxAmount := C_GET_TAX_AMT_FROM_NET(p_tax_id, p_grossamt, p_alternatetaxbaseamt, v_calcPrecision, 0); DBMS_OUTPUT.PUT_LINE('Unit tax ' || v_TaxAmount); - v_NetPrice:= ROUND(p_grossamt * (p_grossamt / (p_grossamt + v_TaxAmount)) / p_qty, p_priceprecision); + -- Do multiplication before division to avoid wrong calculation in PostgreSQL. See Issue 35486. + v_NetPrice:= ROUND(((p_grossamt * p_grossamt) / (p_grossamt + v_TaxAmount)) / p_qty, p_priceprecision); --TODO:Call function to recalculate alternate taxbase amount. diff -r 1549a54a672f -r bb77b1e335cb src-test/src/org/openbravo/test/taxes/TaxesTest.java --- a/src-test/src/org/openbravo/test/taxes/TaxesTest.java Wed Mar 15 09:49:13 2017 -0400 +++ b/src-test/src/org/openbravo/test/taxes/TaxesTest.java Wed Mar 15 14:54:07 2017 +0100 @@ -280,6 +280,30 @@ { "158", "PriceIncludingTaxes 158: Doc Cascade+Dependant 3 -", new TaxesTestData158() }, // { "159", "PriceIncludingTaxes 159: Line Cascade+Dependant 3 +", new TaxesTestData159() }, // { "160", "PriceIncludingTaxes 160: Line Cascade+Dependant 3 -", new TaxesTestData160() }, // + { "161", "PriceExcludingTaxes 161: Doc 20% positive", new TaxesTestData161() }, // + { "162", "PriceExcludingTaxes 162: Doc 20% negative", new TaxesTestData162() }, // + { "163", "PriceExcludingTaxes 163: Line 20% positive", new TaxesTestData163() }, // + { "164", "PriceExcludingTaxes 164: Line 20% negative", new TaxesTestData164() }, // + { "165", "PriceIncludingTaxes 165: Doc 20% positive", new TaxesTestData165() }, // + { "166", "PriceIncludingTaxes 166: Doc 20% negative", new TaxesTestData166() }, // + { "167", "PriceIncludingTaxes 167: Line 20% positive", new TaxesTestData167() }, // + { "168", "PriceIncludingTaxes 168: Line 20% negative", new TaxesTestData168() }, // + { "169", "PriceExcludingTaxes 169: Doc Small 10% positive", new TaxesTestData169() }, // + { "170", "PriceExcludingTaxes 170: Doc Small 10% negative", new TaxesTestData170() }, // + { "171", "PriceExcludingTaxes 171: Line Small 10% positive", new TaxesTestData171() }, // + { "172", "PriceExcludingTaxes 172: Line Small 10% negative", new TaxesTestData172() }, // + { "173", "PriceIncludingTaxes 173: Doc Small 10% positive", new TaxesTestData173() }, // + { "174", "PriceIncludingTaxes 174: Doc Small 10% negative", new TaxesTestData174() }, // + { "175", "PriceIncludingTaxes 175: Line Small 10% positive", new TaxesTestData175() }, // + { "176", "PriceIncludingTaxes 176: Line Small 10% negative", new TaxesTestData176() }, // + { "177", "PriceExcludingTaxes 177: Doc Big 10% positive", new TaxesTestData177() }, // + { "178", "PriceExcludingTaxes 178: Doc Big 10% negative", new TaxesTestData178() }, // + { "179", "PriceExcludingTaxes 179: Line Big 10% positive", new TaxesTestData179() }, // + { "180", "PriceExcludingTaxes 180: Line Big 10% negative", new TaxesTestData180() }, // + { "181", "PriceIncludingTaxes 181: Doc Big 10% positive", new TaxesTestData181() }, // + { "182", "PriceIncludingTaxes 182: Doc Big 10% negative", new TaxesTestData182() }, // + { "183", "PriceIncludingTaxes 183: Line Big 10% positive", new TaxesTestData183() }, // + { "184", "PriceIncludingTaxes 184: Line Big 10% negative", new TaxesTestData184() }, // }); } diff -r 1549a54a672f -r bb77b1e335cb src-test/src/org/openbravo/test/taxes/data/TaxesTestData161.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-test/src/org/openbravo/test/taxes/data/TaxesTestData161.java Wed Mar 15 14:54:07 2017 +0100 @@ -0,0 +1,80 @@ +/* + ************************************************************************* + * The contents of this file are subject to the Openbravo Public License + * Version 1.1 (the "License"), being the Mozilla Public License + * Version 1.1 with a permitted attribution clause; you may not use this + * file except in compliance with the License. You may obtain a copy of + * the License at http://www.openbravo.com/legal/license.html + * Software distributed under the License is distributed on an "AS IS" + * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the + * License for the specific language governing rights and limitations + * 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 Rights Reserved. + * Contributor(s): ______________________________________. + ************************************************************************ + */ + +package org.openbravo.test.taxes.data; + +import java.math.BigDecimal; +import java.util.HashMap; + +public class TaxesTestData161 extends TaxesTestData { + + @Override + public void initialize() { + + // Header info + setTaxDocumentLevel(true); + setPriceIncludingTaxes(false); + + // Line info + TaxesLineTestData line = new TaxesLineTestData(); + line.setProductId(ProductDataConstants.FINAL_GOOD_A); + line.setQuantity(BigDecimal.ONE); + line.setPrice(new BigDecimal("357.99")); + line.setQuantityUpdated(new BigDecimal("2")); + line.setPriceUpdated(new BigDecimal("357.99")); + line.setTaxid(TaxDataConstants.TAX_VAT_20); + + // Taxes for line level are provided + // taxID - {taxableAmtDraftAfterInsert, taxAmtDraftAfterInsert, taxableAmtCompletedAfterInsert, + // taxAmtCompletedAfterInsert, taxableAmtDraftAfterUpdate, taxAmtDraftAfterUpdate, + // taxableAmtCompletedAfterUpdate, taxAmtCompletedAfterUpdate} + HashMap<String, String[]> lineTaxes = new HashMap<String, String[]>(); + lineTaxes.put(TaxDataConstants.TAX_VAT_20, new String[] { "357.99", "71.60", "357.99", "71.60", + "715.98", "143.20", "715.98", "143.20" }); + line.setLinetaxes(lineTaxes); + + // Amounts for line level are provided + // {totalGrossDraftAfterInsert, totalNetDraftAfterInsert, totalGrossCompletedAfterInsert, + // totalNetCompletedAfterInsert, totalGrossDraftAfterUpdate, totalNetDraftAfterUpdate, + // totalGrossCompletedAfterUpdate, totalNetCompletedAfterUpdate} + String[] lineAmounts = new String[] { "357.99", "357.99", "357.99", "357.99", "715.98", + "715.98", "715.98", "715.98" }; + line.setLineAmounts(lineAmounts); + + // Add lines + setLinesData(new TaxesLineTestData[] { line }); + + // Taxes for document level are provided + // taxID - {taxableAmtDraftAfterInsert, taxAmtDraftAfterInsert, taxableAmtCompletedAfterInsert, + // taxAmtCompletedAfterInsert, taxableAmtDraftAfterUpdate, taxAmtDraftAfterUpdate, + // taxableAmtCompletedAfterUpdate, taxAmtCompletedAfterUpdate} + HashMap<String, String[]> taxes = new HashMap<String, String[]>(); + taxes.put(TaxDataConstants.TAX_VAT_20, new String[] { "357.99", "71.60", "357.99", "71.60", + "715.98", "143.20", "715.98", "143.20" }); + setDoctaxes(taxes); + + // Amounts for document level are provided + // {totalGrossDraftAfterInsert, totalNetDraftAfterInsert, totalGrossCompletedAfterInsert, + // totalNetCompletedAfterInsert, totalGrossDraftAfterUpdate, totalNetDraftAfterUpdate, + // totalGrossCompletedAfterUpdate, totalNetCompletedAfterUpdate} + String[] amounts = new String[] { "429.59", "357.99", "429.59", "357.99", "859.18", "715.98", + "859.18", "715.98" }; + setDocAmounts(amounts); + } +} diff -r 1549a54a672f -r bb77b1e335cb src-test/src/org/openbravo/test/taxes/data/TaxesTestData162.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-test/src/org/openbravo/test/taxes/data/TaxesTestData162.java Wed Mar 15 14:54:07 2017 +0100 @@ -0,0 +1,80 @@ +/* + ************************************************************************* + * The contents of this file are subject to the Openbravo Public License + * Version 1.1 (the "License"), being the Mozilla Public License + * Version 1.1 with a permitted attribution clause; you may not use this + * file except in compliance with the License. You may obtain a copy of + * the License at http://www.openbravo.com/legal/license.html + * Software distributed under the License is distributed on an "AS IS" + * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the + * License for the specific language governing rights and limitations + * 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 Rights Reserved. + * Contributor(s): ______________________________________. + ************************************************************************ + */ + +package org.openbravo.test.taxes.data; + +import java.math.BigDecimal; +import java.util.HashMap; + +public class TaxesTestData162 extends TaxesTestData { + + @Override + public void initialize() { + + // Header info + setTaxDocumentLevel(true); + setPriceIncludingTaxes(false); + + // Line info + TaxesLineTestData line = new TaxesLineTestData(); + line.setProductId(ProductDataConstants.FINAL_GOOD_A); + line.setQuantity(BigDecimal.ONE.negate()); + line.setPrice(new BigDecimal("357.99")); + line.setQuantityUpdated(new BigDecimal("-2")); + line.setPriceUpdated(new BigDecimal("357.99")); + line.setTaxid(TaxDataConstants.TAX_VAT_20); + + // Taxes for line level are provided + // taxID - {taxableAmtDraftAfterInsert, taxAmtDraftAfterInsert, taxableAmtCompletedAfterInsert, + // taxAmtCompletedAfterInsert, taxableAmtDraftAfterUpdate, taxAmtDraftAfterUpdate, + // taxableAmtCompletedAfterUpdate, taxAmtCompletedAfterUpdate} + HashMap<String, String[]> lineTaxes = new HashMap<String, String[]>(); + lineTaxes.put(TaxDataConstants.TAX_VAT_20, new String[] { "-357.99", "-71.60", "-357.99", + "-71.60", "-715.98", "-143.20", "-715.98", "-143.20" }); + line.setLinetaxes(lineTaxes); + + // Amounts for line level are provided + // {totalGrossDraftAfterInsert, totalNetDraftAfterInsert, totalGrossCompletedAfterInsert, + // totalNetCompletedAfterInsert, totalGrossDraftAfterUpdate, totalNetDraftAfterUpdate, + // totalGrossCompletedAfterUpdate, totalNetCompletedAfterUpdate} + String[] lineAmounts = new String[] { "-357.99", "-357.99", "-357.99", "-357.99", "-715.98", + "-715.98", "-715.98", "-715.98" }; + line.setLineAmounts(lineAmounts); + + // Add lines + setLinesData(new TaxesLineTestData[] { line }); + + // Taxes for document level are provided + // taxID - {taxableAmtDraftAfterInsert, taxAmtDraftAfterInsert, taxableAmtCompletedAfterInsert, + // taxAmtCompletedAfterInsert, taxableAmtDraftAfterUpdate, taxAmtDraftAfterUpdate, + // taxableAmtCompletedAfterUpdate, taxAmtCompletedAfterUpdate} + HashMap<String, String[]> taxes = new HashMap<String, String[]>(); + taxes.put(TaxDataConstants.TAX_VAT_20, new String[] { "-357.99", "-71.60", "-357.99", "-71.60", + "-715.98", "-143.20", "-715.98", "-143.20" }); + setDoctaxes(taxes); + + // Amounts for document level are provided + // {totalGrossDraftAfterInsert, totalNetDraftAfterInsert, totalGrossCompletedAfterInsert, + // totalNetCompletedAfterInsert, totalGrossDraftAfterUpdate, totalNetDraftAfterUpdate, + // totalGrossCompletedAfterUpdate, totalNetCompletedAfterUpdate} + String[] amounts = new String[] { "-429.59", "-357.99", "-429.59", "-357.99", "-859.18", + "-715.98", "-859.18", "-715.98" }; + setDocAmounts(amounts); + } +} diff -r 1549a54a672f -r bb77b1e335cb src-test/src/org/openbravo/test/taxes/data/TaxesTestData163.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-test/src/org/openbravo/test/taxes/data/TaxesTestData163.java Wed Mar 15 14:54:07 2017 +0100 @@ -0,0 +1,80 @@ +/* + ************************************************************************* + * The contents of this file are subject to the Openbravo Public License + * Version 1.1 (the "License"), being the Mozilla Public License + * Version 1.1 with a permitted attribution clause; you may not use this + * file except in compliance with the License. You may obtain a copy of + * the License at http://www.openbravo.com/legal/license.html + * Software distributed under the License is distributed on an "AS IS" + * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the + * License for the specific language governing rights and limitations + * 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 Rights Reserved. + * Contributor(s): ______________________________________. + ************************************************************************ + */ + +package org.openbravo.test.taxes.data; + +import java.math.BigDecimal; +import java.util.HashMap; + +public class TaxesTestData163 extends TaxesTestData { + + @Override + public void initialize() { + + // Header info + setTaxDocumentLevel(false); + setPriceIncludingTaxes(false); + + // Line info + TaxesLineTestData line = new TaxesLineTestData(); + line.setProductId(ProductDataConstants.FINAL_GOOD_A); + line.setQuantity(BigDecimal.ONE); + line.setPrice(new BigDecimal("357.99")); + line.setQuantityUpdated(new BigDecimal("2")); + line.setPriceUpdated(new BigDecimal("357.99")); + line.setTaxid(TaxDataConstants.TAX_VAT_20); + + // Taxes for line level are provided + // taxID - {taxableAmtDraftAfterInsert, taxAmtDraftAfterInsert, taxableAmtCompletedAfterInsert, + // taxAmtCompletedAfterInsert, taxableAmtDraftAfterUpdate, taxAmtDraftAfterUpdate, + // taxableAmtCompletedAfterUpdate, taxAmtCompletedAfterUpdate} + HashMap<String, String[]> lineTaxes = new HashMap<String, String[]>(); + lineTaxes.put(TaxDataConstants.TAX_VAT_20, new String[] { "357.99", "71.60", "357.99", "71.60", + "715.98", "143.20", "715.98", "143.20" }); + line.setLinetaxes(lineTaxes); + + // Amounts for line level are provided + // {totalGrossDraftAfterInsert, totalNetDraftAfterInsert, totalGrossCompletedAfterInsert, + // totalNetCompletedAfterInsert, totalGrossDraftAfterUpdate, totalNetDraftAfterUpdate, + // totalGrossCompletedAfterUpdate, totalNetCompletedAfterUpdate} + String[] lineAmounts = new String[] { "357.99", "357.99", "357.99", "357.99", "715.98", + "715.98", "715.98", "715.98" }; + line.setLineAmounts(lineAmounts); + ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits