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

Reply via email to