!!critical bug when rounding taxes in invoices!! -> urgent patch otherwise accounting and invoices can be wrong; possible patch included

[Raphaël Valyi - http://www.akretion.com]

Hi,

This bug sounds critical. We just saw this in prod comparing supplier invoices with OpenERP invoices.

The thing is that it seems that the total tax amount per invoice (in a tax category eg tax line) result of the sum of the taxes of that category for each invoice line.

But, instead of rounding AFTER summing the invoice lines, OpenERP first rounds at two digits at each invoice line and then sums that into a tax total for that category of taxes. So sometimes rounding errors propagate themselves from lines to lines and the tax total is simply wrong: it multiply the approximation per line by the number of invoice lines. It can easily be 5 cents wrong and 5 cents sucks in accounting (accounting along sucks, so for 5 cents, you can imagine how much it sucks). Accountants tend to be psyco-rigid frustrated people that will bug for 5 cents you know...

The error is reproducible each time you click on the 'Compute Taxes' button in an invoice. That call the account.invoice.tax#compute method. In that method, we have the following code:
        for line in inv.invoice_line:
            for tax in tax_obj.compute(cr, uid, line.invoice_line_tax_id, (line.price_unit* (1-(line.discount or 0.0)/100.0)), line.quantity, inv.address_invoice_id.id, line.product_id, inv.partner_id):

This is where it's wrong already. Going further in the stack brings us to account.tax#compute
And in that method, OpenERP is rounding at two digit with code like:
round(r['amount'] * quantity, 2)

In our production v5 OpenERP, we replaced that 2 by a 5 and got all tax amount done right. Since we preferred to be too careful, we also use 5 digit rounding for the balance field here, see attached patch.
I'm not saying this is the right fix, you are very much welcome to think about it before applying the patch, but it might be much closer to the right thing than the current implementation. Obviously, the more lines in the invoice, the more precision digit you need for each line before summing. 5 digit seems to guarantee it will work over 100 invoice lines. May be OpenERP needs to opt for even more precision. Also bare in mind that all that rounding thing (like 2 digits hardcoded) will probably not work for some currencies requiring more precision, but that's an other story.

The more invoice lines you have, the more risk you take with the current implementation. Luckily, once the patch is applied, you only need to iterate each invoice and click on the "compute taxes" button to get it fixed. But that won't fix the invoices you already sent to your customers... That's why I suggest Tiny communicate around that one and tell the community to apply that patch (or similar) quickly.

Hope this helps,

Raphaël Valyi.

hop this helps.

[qdp (OpenERP) (qdp)]

hello,

we agree on the critical dimension of this bug: the compute method should NOT do the rounding this way... It shouldn't round the float at all.

Another point for you: rounding on 2 digits is completely arbitrary and doesn't take in consideration the currency rounding factor.

Since this mod needs a in depth testing and a revision of several modules, we cannot afford to release a patch immediately. It should be available in a few days.

Thanks for the bug report

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Ok,

qdp, to be clear, we aren't expecting a quick fix from Tiny. Our customer as already been patched and he will be safe. I just shouted loudly because you'll have lot's of people impacted who will notice and whose customers might notice the bug only by the end of 2009 when closing their accounting. So that's up to Tiny, no problem with that.

Best regards,

Raphaël Valyi.

[Claude Brulé (claude-brule-syleam)]

this is absolutly NOT a bug.

You have to encode the invoice sended by supplier by keeping all data even wrong ones.
The invoice in your invoice in the account ing of your compagny must be the SAME as the invoice in your supplier's accounting for FISCAL reasons.
If there is a calculation error, then your have to keep this error. You haven't the right to change it.
NO solution at all for purcahses way

In sales way, you can use the calculation method of your choice but this method MUST be the SAME althrought the fiscal year.
You need express agreement of state or country to change it.

The way OpenERP is working has the advantage that ffoter of invoice is always good, and accounting is balanced.

Sum of rounded values is not the same result as rounding value of sum. this is a mathematical trouble witch as also NO solution.

You will have an issue if your try to sale a very big quantity of goods that have a really small price.

Your need according to the law of different coutries, with different taxation calculation.
So please don't patch anything before being absolutly sure of what your are doing.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Guys,

we got some updates on that one:
1) our customer, which is not an accountant but knows accounting very well, absolutely insisted to have that patch. He still thinks it's a bug. So he uses: ROUNDED SUM OF THE EXACT UNTAXED LINES.

2) Fabien said he looked at the French law and said that both were allowed: declaring VAT (TVA here) as the ratio of the ROUNDED SUM OF THE EXACT UNTAXED LINES or as THE SUM OF ROUNDED TAXED AMOUNTS.

3) I asked our accountants at Smile and they say: they all would absolutely declare the VAT as the ROUNDED SUM OF THE EXACT UNTAXED LINES. They would say that the other is not legal, but are not sure about that.

In any case, if you use ROUNDED SUM OF THE EXACT UNTAXED LINES (=apply the patch), then everyone I asked agree that should be fine, you would never be wrong (well at least in France). That's why I would rather make this the default (=apply the patch).

Now, I'm looking after what real accountants think about that.
Fabien told that ideally, in a future version (v5.1 ?), they would kind of apply the patch selectively depending of a global ERP property asking if you want to use the rounding of the sum or the sum of the rounded values instead. That's great. Still, I doubt the default choice is the right one and I'm alerting the community in case they have trouble because of that default choice. Having a legal confirmation on this by an accountant would of course be welcome.

Thanks a lot.

Raphaël Valyi

[Fabien (Open ERP) (fp-tinyerp)]

I close as it is not a bug, please encode a blueprint.

[Alexis de Lattre (alexis-via)]

My message is a reply to Claude Brulé's comment.

Claude, I agree that the amount of VAT in the supplier invoice in our accounting must be the same as the amount of VAT printed on the supplier invoice, whatever calculation method the supplier has chosen (ROUNDED SUM OF THE EXACT UNTAXED LINES or THE SUM OF ROUNDED TAXED AMOUNTS).

From my (long) experience at Anevia, more than 99% of suppliers use the method ROUNDED SUM OF THE EXACT UNTAXED LINES. So it would be better for OpenERP to use this method by default when generating the supplier invoice instead of using the other method. That's why Raphaël proposed the patch, and that's why I think it should be applied.

[Numérigraphe (numerigraphe)]

As stated in Bug #407332, this makes the --price-accuracy switch mostly unusable.
Lionel.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Hello, for the record, fix fro bug #407332 is also a definitive fix for that not so well admitted bug.
The commit was #2407.1.7 ID <email address hidden>

Thanks!