tax calculation incorrect in vendor invoice if calc. is on line item level and price-list is "includes tax"

Description

Scenario 1:

  • supplier's price list has "prices include tax" checked

  • an vendor invoice is created with a line item that contains a product which is NOT from the supplier price list or which is a charge

how to reproduce:

  • create demo supplier "A"

  • create default price list for demo supplier "A" with checkbox "prices include tax" checked

  • create vendor invoice

  • in the vendor invoice create line item (charge or product) that is not on the price list

  • in the vendor's invoice line-item enter gross amount and choose a tax rate

Expected (correct) result:
Doc Total = 100.-
Line Amount = 100.-
Line Total = 100.-
Tax = 17.36

Effective (wrong) Result:
Doc Total = 117.36 (wrong)
Line Amount = 117.36 (wrong)
Line total = 117.36 (wrong)
Tax = 17.36 (correct)

Scenario 2:

  • supplier's price list has "prices include tax" checked

  • an vendor invoice is created with a line item that contains a product which is from the supplier price-list

how to reproduce:

  • create demo supplier "A"

  • create product and default price list for demo supplier "A" with checkbox "prices include tax" checked

  • create vendor invoice

  • in the vendor invoice create line item with product that is from the supplier's price list

  • add a line item with product from the supplier's price-list, enter gross amount CZK 100 and choose 21% VAT tax

Expected (correct) result:
Doc Total = 100.-
Line Amount = 100.-
Line Total = 100.-
Tax = 17.36

Effective (wrong) Result:
Doc Total = 100.-
Line Amount = 100.-
Line total = 117.36 <- cosmetic error only 'cos doc total is correct
Tax = 17.36

Assumption / interpretation:
It appears iDempiere is making two calculation operations.
Operation 1: First it calculates the tax amount based on the supplier's default price list settings ("prices include tax").
Operation 2: Second it adds the tax amount to the Line Total and the Doc Total (again based on price-list settings like "prices include tax").

Assumed issue: if the product or charge is not on the supplier's price list (a charge doesn't belong to any price-list) it is treated as "prices exclude tax" for operation 2.
But for operation1 it seems that it is calculating accordingly to the settings of the supplier's default price-list (the price list that appears automatically in the header section of the vendor invoice document). Therefore the tax is calculated correctly (17.36 from 100 instead of 21 on 100).

Solution:
Both operations need to follow the settings of the default price-list defined for the supplier.

Workaround:
in order to workaround this bug, it is necessary to refrain from using charges for a scenario where the supplier's price-list is "tax included". It is necessary to define charges for postal services as a product of type service that is included in the supplier's price-list.

Priority: putting it to minor because I found a dirty workaround by adding all such "charges" as products of type "service" to the supplier's price-list if the price-list is set to "includes tax".

Improvement suggestion:

  • once fixed, I would add a field "net line amount excluding tax" above "Line Amount" and "Line Total" in the line-item detail view.

Environment

Ubuntu 14.04 VM
-----------------------
iDempiere 2.1.0.v20150118-0548
Host = erpp.hq.rvg
Database = psql.hq.rvg : 5432 / idempiere_p
OpenJDK 64-Bit Server VM 23.25-b01
java.io.tmpdir=/tmp
Linux 3.13.0-44-generic unknown
Max Heap = 1394M
Allocated Heap = 1394M
Free Heap = 1291M
Active Threads = 146

Activity

Show:
Peter Snizek
September 4, 2015, 8:37 PM

As an alternative solution to the problem it might be worth while to think about implementing the logic behind the disabled "Price includes Tax" checkbox in the "Charge" form.

In that case it would be necessary to implement a filter for all relevant document forms to filter for charges only that correspond to the tax included or tax excluded setting in the default business partner price-list. This is in order to make impossible that the user can choose a charge with a tax setting that diverges from the business partner's default price list when drafting an invoice document (or any other doc such as PO, SO etc...).

Peter Snizek
September 7, 2015, 5:51 PM

Carlos Ruiz suggested following solution on the idempiere group forum on google:

Hi:

Sometime ago I had to write to core the following changes (in order to fix the tax problem). In my country taxes are calculated like your case.

MInvoiceTax.java line 241
// Set Base
if (isTaxIncluded())
//setTaxBaseAmt (taxBaseAmt.subtract(taxAmt));//changed by me
setTaxBaseAmt (taxBaseAmt);
else
setTaxBaseAmt (taxBaseAmt);
return true;

MTax.java Line 268

if (!taxIncluded) // $100 * 6 / 100 == $6 == $100 * 0.06
{
tax = amount.multiply (multiplier);
}
else // $106 - ($106 / (100+6)/100) == $6 == $106 - ($106/1.06)
{
tax = amount.multiply (multiplier);//added line
//THIS WAS CHANGED BY ME PLEASE REVIEW AND DELETE IT LATER. CARLOS COLLAZOS
/*multiplier = multiplier.add(Env.ONE);
BigDecimal base = amount.divide(multiplier, 12, BigDecimal.ROUND_HALF_UP);
tax = amount.subtract(base);*/
}

MOrderTax.java line 248

// Set Base
if (isTaxIncluded())
//setTaoxBaseAmt (taxBaseAmt.subtract(taxAmt));//OTRO CAMBIO PARA LOS IMPUESTOS
setTaxBaseAmt (taxBaseAmt);
else
setTaxBaseAmt (taxBaseAmt);

It would be great to have this functionality as an option for tax calculi inserted into core.

Regards,

Carlos Ruiz
September 13, 2015, 7:16 PM

Thanks , is who suggested that

Carlos Ruiz
May 16, 2018, 8:53 AM

Hi , I think this was solved in

Assignee

Carlos Ruiz

Reporter

Peter Snizek

Labels

Tested By

None

Components

Affects versions

Priority

Minor
Configure