Cannot validate invoices with foreign currency

hello sraps (KN dati) ,

Please apply the attached patch and notify me that its solves your problem or not.

Thanks.

Hello,

This problem occurs also on invoice in USD for an CHF company currency.
This patch seems to correct the problem, I need to make more test.

Thanks for your help,

Vincent

This patch seems to fix the problem. Some more testing is required on our side to finally decide if the bug is fixed.

Thank you for the patch, but...

Although it seems that it would solve the problem (I am sure it would, for about 1/2 of the cases), solving this problem with the rounding is not a solution.

These cases actually are being dealt in any mature accounting software (no hard feelings), by adding another accounting move to cover the difference.

Have just opened book about Concorde XAL dated back to 1996. It says:
------
Penny Difference

When entries are made in foreign currency, the conversions of the individual parts of the invoice amount may result in a penny difference. If this is the case, a penny difference will be entered...
------
I have made an inquiry, this is the same in current systems too. This should NOT be solved by rounding.

This is similar to a Write off entry. Usually this is being set somewhere in the settings, the account where the penny difference move should be made and the allowed sum of money for the difference.

Regarding proposed patch, I would not mess with the price accuracy in the accounting moves as there are legislation rules which state what accuracy should be used in accounting. In this case as this is the final sum, this rounding probably should be done using rounding factor from particular currency.

Kaspars

Is the fix already commited? In which version?

Hi folks,

After making lots of tests around all this trouble on finance accounting, I conclude the following:

- We should accept this patch cause it makes OpenERP more consistent with the way he handle the rounding problematics (we should use the price_accuracy everywhere, and that's what it's suggested here).

- We do have an other problem on recording, storing and computing the float for finance accounting into OpenERP. We should I think use the type Decimal provided by Python to avoid all trouble around those things.

I incude this patch into my finance branch for merge proposal and will report another bug for the rounding trouble and using Decimal stuff..

Regards,

Thanks Joël!

Hello,

This patch is not good:

- if abs(amount) < 0.0001:
+ if abs(amount) < config['price_accuracy']:

is completly False. config['price_accuracy'] == 2 ! -> not 0.01

! Thanks Fabien !

Sorry, I don't see this one, I just tested this suggested patch and it work well in all my use cases... So I include it as is... I correct it into the proposal merge branch today if I have the time.

Regards,

Hi,

It's done. I correct this into the proposal merge branch.

Change:

- if abs(amount) < config['price_accuracy']:
+ if abs(amount) < 10**-config['price_accuracy']:

So now we get the correct test :) !

Again sorry for this, I really don't pay attention to this one...

Regards,

Hello,
I got this bug, too and changed the line as above :

if abs(amount) < 10**-config['price_accuracy']:

but i get the following error when running :
TypeError: bad operand type for unary -: 'str'

...
Thanks

Oooops,
did not see the first patch to apply, sorry !

Hi,
Well guys I appreciate your involvement, but this is completely wrong approach to the problem. Or at least the approach to other problem, not the one filed by me.

The functionality written by me in #4 is present in any accounting system which works more or less automatically. I have been discussing this with the people who work on MS Dynamics AX as users and analysts too (as well as other accountants, working daily on other accounting systems), this is not the problem of rounding.

This should be handled by creating write-off entry to balance the credit/debit accounts. This is present in many software packages. This is common practice and could not be corrected by touching the rounding factor.

BTW price-accuracy should not be involved in calculating final movements, as this defined by local legislation.

Next one:
Let me describe what is going on here...
- if abs(amount) < 0.0001:
+ if abs(amount) < config['price_accuracy']:

This should not be changed in any way, because value "0.0001" is not being used here to compensate rounding error, this check here exists because of the way computer stores floating point values. For reference read this (http://en.wikipedia.org/wiki/Floating_point). And because using exponent two floating point values calculated in different way are very close but not identical.
This is why this check is present here.

!!!!!!!!!!
Correct work-flow:
1. Get two sums - sum of credit and sum of debit accounts;
2. Compare them, if they are reasonably (pay attention - "reasonably", this is why it should be "0.0001"), the same amount, to compensate the storage deviance;
3. If sums differ in lets say 0.01*n (actually in smallest dividable part of currency, multiplied by the largest count of movement records from debit or credit side), and the movements are being created for foreign currency document represented in base currency, another additional write-off entry here is created to compensate the difference;
4. Probably another balance check should be performed (but without creating write-off entry, of course).

Believe me, this is accounting problem and could not be solved by mathematics alone.

no, I apply the complete patch plus the last modification but still get the error :

if abs(amount) < 10**-config['price_accuracy']:
TypeError: bad operand type for unary -: 'str'

ok, i made the following change and it seems to work :

change :

-if abs(amount) < 10**-config['price_accuracy']:

+if abs(amount) < 10**-int(config['price_accuracy']):

Can you confirm ?

Hi !

@forstera, yes for this trouble, you can correct it like this. I'll correct it into the proposal merge too

@scraps, you're completely right !! But :

- We cannot change all the workflow into that version 5.0, only for next 5.2
- We store float on only "price_accuracy" digit into the DB
- Finally, the lag of precision due to last point make this test about " < 0.0001" unusable

At the end, you cannot test that amount "0.0001" because we're loosing that precision when recording float on price_accuracy digit into the DB.

I suggest the following:

a ) For 5.0, we accept that, and make all the financial computation based on "price_accuracy"

b) For the next release, We really make something better for all those stuff. See my blueprint and add your comment here please :

https://blueprints.launchpad.net/openobject-server/+spec/generic-accuracy-improvement

What do you think about that ?

Regards,

@Joël
well, now I see that there is at least reasonable idea of solving this :). Even though I do not see why this could not be fixed now, as this does not change any interfaces or API. System is completely ready for this, it could be done inside one function, without affecting external interfaces.

In other systems this is working behind the scenes fully automatic, there is no user interaction required at all. There is just another movement entry created to balance existing entries.

This even does not break existing movements, as we are changing neither object structure, nor introducing new objects (classes).

Actually this could be done by the user himself, he can create those records manually, right?

Anyway there should not be any change done for this, if we are not implementing correct functionality, as there should not be price_accuracy present in this function. To my opinion price_accuracy should be eliminated at some point as this is simplified approach to the the problem throughout the system, because calculation accuracy are not the same all over the functionality in system. In different parts it should be different, probably set as default property for each module (functionality).

P.S. I have not been reading your blueprint, I will read it and post comments on it a bit later.

best regards...

Hello,
All was working fine, I changed my code but today we received the same error message (You can not validate a non balanced entry !) but with a supplier invoice. Do have I to change the formula somewhere else in the code ?

Thanks

Arnaud

Hello,

I just test the case which failed before the fix, and it seems to be ok according to my test. I do the following:

1. Create a 1000.- invoice in USD with company in CHF
2. Confirm it and then cancel it
3. Change the amount from 1000 to 1100 USD.-
4. Confirm it again

At this point, it failed before with "You can not validate a non balanced entry !", now it says : compute the tax again, or something like this. When you re-compute the tax, everything is alright...

Regards,

Joël

Hello,
We still have the error message 'Integrity Error' for supplier invoices. here's the case :

Our company in CHF
Supplier invoice of 912.01 €

When trying to Validate the invoice, here are the amounts for the test " if abs(amount) < 10**-int(config['price_accuracy']):"

abs(amount) : 0.0100000000001
10**-int(config['price_accuracy']) : 0.01

I dont know if this can help, but in the function def validate(), there's a test at the end : if journal.centralisation: in this case, the value is always false which create the error message.

Hi,

I tested this and it seems it's fixed in last stable of today (at least according to my test scenario).

So I close it.

Regards

Hello, we stil have the bug. We made some tests and we've found that the problem is depending of the exchange rate :
We made a foreign invoice (€) dated of 20.10.2009. At this time the exchange rate was 0.652571. We we try to validate the invoice, we receive an error message about the integrity error : you cannot validate a non-blanced entry .
Then, we changed the date of the invoice to doday The exchange rate is now 0.668717 and here, when we are validating the invoice, no problem...
so, there's really a problem depending of the exchange rate ..

Hi !

I confirm this is still a problem. As Forstera said, this bug is still present. We investigate it quite a lot and saw that there is here a bigger trouble than "just" this one...

Two problems here:

1. The validation of a balanced entry

2. The precision trouble when working on only 2 digits in both DB and display

For the first one, we have in the code (account.py, Line 1024):

if abs(amount) < 0.0001 => Raise an error, amount = sum (debit-credit) of all move lines.

Why taking this value instead of a value based on the server accuracy value !? I suggest this in my last merge on finance improvement, but that was refused ! Now, we cannot validate some invoice...

For the second one, the trouble comes from accuracy in computation :

The sum of debit move line are : 1'983.71 (140.11+96.23+1747.37)
The value of credit move line is : 1'983.702459

This come from the rounding we have on each debit move line, which result in the end as a difference between the sum of the line and the total amount. Normally, the sum(rounded debit lines) == total invoice amount * current currency rate.

So to summarize, we need to find a way to solve those problem together because :

- The first one disallow people to encode some invoice !!!
- The second will cause difference when making the reconciliation, which is incorrect.

At the end, we need a way to handle those accuracy difference, for example by adding a line for accuracy difference or by changing the amount of on of the debit line to make the debit perfectly equal to the credit.

Please, have a look on the complete test case here (you can also simply run the OERPScenario https://launchpad.net/oerpsenario, trunk branch here bzr branch -r54 lp:oerpsenario ) :

Background:
      Given I am loged as admin user with password admin used
      And the company currency is set to EUR
      And the following currency rate settings are:
      |code|rate|name|
      |EUR|1.000|01-01-2009|
      |CHF|1.644|01-01-2009|
      |CHF|1.500|09-09-2009|
      |CHF|0.6547|10-10-2009|
      |USD|1.3785|01-01-2009|
      And a cash journal in USD exists
      And a cash journal in CHF exists
      And a cash journal in EUR exists

Scenario: check_rounding_diff_multi_line_inv
    Given I have recorded on the 11 oct 2009 a supplier invoice (in_invoice) of 1144.0 CHF without tax called MySupplierInvoiceRounding
    And I add a line on the last created invoice of 91.73
    And I add a line on the last created invoice of 63.00
    And correct the total amount of the invoice according to changes
    When I press the validate button
    Then I should see the invoice MySupplierInvoiceRounding open
    And the total credit amount must be equal to the total debit amount
    # Here we check the rounding to see if sum(rounded lines) == total invoice amount * current currency rate
    And the total amount convert into company currency must be same amount than the credit line on the payable/receivable account

Regards,

Hello Guys,

Its just the issue when debit and credit comes into calculation,they come with limited accuracy.

We have used all your examples, and concluded that this patch solved the problem.
Make sure you have to update the account module(For letting the DB know about the precision,if not 2).

Thank you.

sorry, we just applied the patch (after having downloade the last files from the launchpad) but we still get error 'you cannot validate a non-blanced entry .

Hi !

I just tested it also with the last sources and the problem is still here also....

You can not validate a non-balanced entry !

This is may be because I don't create a DB, but use update=base....

Don't have time to tests now with a new one...

I hope I ill tomorrow...

Thanks anyway Jay !

Regards,

Joël

Hello,

Here is my response I have put to the accounting expert team mailing list :

Hello,

Ferdinand, I am agree with you that we should have 2 possibilities :

1) new entry for write off
2) modification of one entry => but not the total entry line because if we do that, we will still have the problem with the reconciliation because it will calculate base on the total in currency and will have a diference => we should modify one of the entry but not the total.

I have look after other ERP (Ms Axapta) and it seams that the first solution is done.

For my point of view we should put in place the solution 1 and add into the company a new paramter "Default currency write off" for this special currency write-off entry.

This "default currency write-off" account will also be used when we will put in place the automatic write-off calculation on reconciliation if the currency rate hase changed (difference between invoice and reconciliation rate).

What is the accounting expert position ? It is really a BIG problem and it should be solved for the next version too !

Regards

Hello Guys,

As the debit and credit of move line are set with static digits=(16,2), This patch handles the situation.

Thanks.

Hello,
I'm going to test it right now. But what do we do with the patch provided the other day ? (validate_balanced_entry.patch from 2010-01-19)
Thanks

Hello all,
sorry again, I applied the patch but I still get the error 'cannot validate a non balanced entry ...'

Me too !

and what is very serious is that this patch caused to round every amount of invoices, so for every amounts, this patch removed the cents !! so I received a desparation call from the accounting department before removing this patch !!

Please, we are here in production with the accounting. We really need that the patchs are tested before delivery.
thanks

Hello,
I just receive a desesparate email from my accounting department. They have to create the invoices for our foreign clients but they can't. Is there a way to passtrough temporarly ?
This is urgent ! Thanks for your help

Hi everyone,

I post the result f the expert team accouting here:

If I agree with most aspect of what you spoke about (the way to properly handle it, OFBiz specs, and so ...),
this is only possible on the next coming version 5.2. I already write a blueprint to centralize every one's ideas here :

https://blueprints.launchpad.net/openobject-addons/+spec/generic-accuracy-improvement

So please, post your advice and though here, so will try to find a "community" solution for that !

But for now, we still have a big trouble : Depending on currency exchange rate, we still can't confirm some invoices !!!!

I'll suggest to handle it the following:

When the balance isn't respected on the invoice confirmation (raising an error), OpenERP automatically add the difference
on the line with the greatest amount. That means, in my example, OpenERP will add 0,01 CHF.- on the 1144.0.- Line.

Doing that will avoid the error, and allow the user to confirm the invoice. I know this is not perfect, but as we saw, I
can't suggest a better solution, or my merge proposal will be refuse... Saying it's too much for a stable version... And the
worst is that I'll agree on that.

Implementing a good write-off system per currency AND implementing Decimal should be the good one in my own opinion...

So know the decision is : adding the difference to the biggest line. Just pay attention for incoming invoices we just have to make sure that the total VAT calculated by OpenERP corresponds to Total VAT on the invoice, this avoids discussion with fiscal authorities.

@forstera : The only way I know before this fix is to change the currency rate... Then pass a write-off for it !

Regards,

Joël

P.S. As a proof/reminder, I put my Scenario test case to let you reproduce and understand things:

  Background:
      Given I am loged as admin user with password admin used
      And the company currency is set to EUR
      And the following currency rate settings are:
      |code|rate|name|
      |EUR|1.000|01-01-2009|
      |CHF|1.644|01-01-2009|
      |CHF|1.500|09-09-2009|
      |CHF|0.6547|10-10-2009|
      |USD|1.3785|01-01-2009|
      And a cash journal in USD exists
      And a cash journal in CHF exists
      And a cash journal in EUR exists

  Scenario: check_rounding_diff_multi_line_inv
    Given I have recorded on the 11 oct 2009 a supplier invoice (in_invoice) of 1144.0 CHF without tax called MySupplierInvoiceRounding
    And I add a line on the last created invoice of 91.73
    And I add a line on the last created invoice of 63.00
    And correct the total amount of the invoice according to changes
    When I press the validate button
    Then I should see the invoice MySupplierInvoiceRounding open
    And the total credit amount must be equal to the total debit amount
 # Here we check the rounding to see if sum(rounded lines) == total invoice amount * current currency rate
    And the total amount convert into company currency must be same amount than the credit line on the payable/receivable account

Hi!

For a quick fix, in our case it was enough to revert that "famous" change in res_currency:

return round(amount / currency.rounding,6) * currency.rounding
to
return round(amount / currency.rounding) * currency.rounding

I just filed a bug which was marked as a duplicate of this one so here is an additional use case:
Using a fresh DB and the latest 5.0 branches from LP, I get an error "Integrity Error ! You can not validate a non-balanced entry !" when I try to enter an invoice with:
- --price_accuracy=3
- unit price=444,444
- qty=1
- 1 tax = 19.6% (VAT as defined in the French accounting l10n module)
Lionel.

Someone is working on this one ?

Please advice, the status is in progress, so I supose it's the case. Just want a confirmation before having a look !?

Thanks,

Hello Joel,

Yes , I am working on this issue.

I guess, these bugs suffer from the same disease:
https://bugs.launchpad.net/openobject-addons/+bug/511104
https://bugs.launchpad.net/openobject-addons/+bug/496889

The root sounds to be price_accuracy issue and different currencies.

I feel the(16,2) of debit and credit of MoveLines are the real culprits.

However, I am looking for the best suitable solution.

Thanks.

Here is a unit test scenario exhibiting the problem stated on comment #37 :
http://bazaar.launchpad.net/~numerigraphe/openobject-addons/unittest-5.0/revision/2422

@Joel, all right, you cents tweaking is just a temp fix for multi-currency before a write-off so I perfectly agree with that I mean I think it doesn't fix at all what @sraps call issue 1) here.

@sraps, I agree with you. Now, if that's possible, we would still need a way to make the "perfect accounting counter case" look more appealing. Indeed, in ecommerce, it means you can buy a product 9 euros because the ecommerce used a VAT included method, while OpenERP will give you 9.0.1 euros (no matter if you try to use the tax_included modules). So that's not very appealing to ecommerce usage and we suspect that fixing a few things into the tax_included modules could at least make it possible to show same VAT included lines and invoices totals, while eventually having the cents delta only in VAT excluded lines which would be OK to the customer.

Finally we started a thread about that in the accounting expert mailing list ( https://launchpad.net/~openerp-expert-accounting ) because it scales better for debate, we also gave a clear example why extended floating precision on invoice amounts is a dead end.

Hello guys,

Here is a patch that seems working for us (more than 900 invoices imported from ecommerce successfully, with balanced entries with price_accuracy= 3 or 4).
Now it deals only with the mono-currency case and we do not consider it clean code, but we wonder if OpenERP 5.0 would allow anything cleaner than that (not sure).

What we do here is we explicitly make the sum of the tax be the the sum of the rounded line tax values (what we call method 2) in our expert list message), as they are rounded to 2 digits in the account move line (by legal requirements) and as we need to match that VAT total to match it in the accounting too.

link expert team : http://n3.nabble.com/Floating-point-precisions-balanced-entries-and-VAT-included-reflexions-td430873.html#a433465

The issue is that this VAT total and untaxed total amount seem to be computed at several places for reasons that do not look obvious at all, that's why we had to force rounding to 2 digits at several places:
1) at the end of the patch, we make the "VAT base" and "VAT total" from the down left tax panel widget be correct
2) in the middle, we get the the VAT included total of the customer account move line to be computed properly
3) on the top of the patch, we get the total untaxed displayed on the down right side invoice panel computed properly

Please notice, from our expert team discussion, it seems that the 2 decimal digits accounting accuracy shouldn't be hardcoded in general because some countries might require something different. Now, in our case, we didn't want to take the responsibility to introduce a new config param that would not be used properly by the rest of the code, so this is a general remark for 5.2, for now, we just hard-coded 2 digits in that patch.

You can test the patch with this exemple :
Price_accuracy = 3
Taxe rate : 19,6%
Product 1 : untaxed = 3,854
Product 2 : untaxed = 5,301

Total untaxed amount will be 9,15
Total taxe amount will be 1,80
Total amount will be 10,95
And the invoice can be validaded without problem

So what do you think about that proposal?

I don't know if this could help but here in spain online stores could work
with or without tax include prices (oscommerce standar feature)

But for product lines on order, I'm always downloading prices without tax
taking price from product table.

For subtotal lines. I just download all subtotal lines that are not VAT,
subtotal or total (like shipping cost or discounts or coupons) as a product
on sales order line on OpenERP. I calculate this lines price without tax
before downloading.

Accuracy is 2 in all test cases and all orders are downloaded with exact
price shown on shop. ... my customers have not reported this kind of problem
to me. Our VAT tax is 16%. I don't know if this could be because of that.
Maybe with this tax there is less rounding problems, I don't know.

On addition, I totally agree with kn dati... we are on similar situation on
a steel manufacturing company.
They manage Tons to buy and sale, but prices have to be on kg because you
can't set 0,525€/ton. So you have to set 525€/tm
We would need accuracy=3 to manage this situation.

Thank you:

Ana

2010/3/10 Sébastien BEAU - http://www.akretion.com <
<email address hidden>>

> Hello guys,
>
> Here is a patch that seems working for us (more than 900 invoices imported
> from ecommerce successfully, with balanced entries with price_accuracy= 3 or
> 4).
> Now it deals only with the mono-currency case and we do not consider it
> clean code, but we wonder if OpenERP 5.0 would allow anything cleaner than
> that (not sure).
>
> What we do here is we explicitly make the sum of the tax be the the sum
> of the rounded line tax values (what we call method 2) in our expert
> list message), as they are rounded to 2 digits in the account move line
> (by legal requirements) and as we need to match that VAT total to match
> it in the accounting too.
>
> link expert team : http://n3.nabble.com/Floating-point-precisions-
> balanced-entries-and-VAT-included-reflexions-td430873.html#a433465<http://n3.nabble.com/Floating-point-precisions-%0Abalanced-entries-and-VAT-included-reflexions-td430873.html#a433465>
>
> The issue is that this VAT total and untaxed total amount seem to be
> computed at several places for reasons that do not look obvious at all,
> that's why we had to force rounding to 2 digits at several places:
> 1) at the end of the patch, we make the "VAT base" and "VAT total" from the
> down left tax panel widget be correct
> 2) in the middle, we get the the VAT included total of the customer account
> move line to be computed properly
> 3) on the top of the patch, we get the total untaxed displayed on the down
> right side invoice panel computed properly
>
> Please notice, from our expert team discussion, it seems that the 2
> decimal digits accounting accuracy shouldn't be hardcoded in general
> because some countries might require something different. Now, in our
> case, we didn't want to take the responsibility to introduce a new
> config param that would not be used properly by the rest of the code, so
> this is a general remark for 5.2, for now, we just hard-coded 2 digits
> in that patch.
>
>
> You can test the patch with this exemple :
> Price_accuracy = 3
> Taxe rate : 19...

Sébastien, the patch you provided does pass the xml test I proposed, and
it lets me confirm a big invoice that used to be blocked (>3M€, 33
lines, various taxes)
So as far a I can tell, it fixes the price_accuracy issue.
Lionel

However partial, will this fix be part of 5.0.8?

Hello Everyone,

     I have checked the things and have found that the patches suggested have given us a way towards the solution. Finally I have a solution in the attached patch. Please check it and notify.

Thank you.

Anup (Open ERP) a écrit :
> Hello Everyone,
>
> I have checked the things and have found that the patches suggested have given us a way towards the solution. Finally I have a solution in the attached patch. Please check it and notify.
>
> Thank you.
>
> ** Patch added: "price_accuracy_patch.diff"
> http://launchpadlibrarian.net/42770860/price_accuracy_patch.diff
>
>
Hi Anup,
I'm so glad to see there is still someone working on the terrible issue.
At least the current bzr with this patch passes the xml test I wrote
with price_accuracy =4 - the current bzr without the patch does NOT and
makes unbalanced moves.
Lionel

Hello,

With this patch, We have no more non-balanced entries.

For me this patch works

Thanks for your help,

Vincent

We are waiting for confirmation that this patch fixes the bug.

Thank you

Stephane

Hi,

This seems to work in some cases, but OERPScenario still fail with price_accuracy = 2 with this test:

      Given the company currency is set to EUR
      And the following currency rate settings are:
      |code|rate|name|
      |EUR|1.000|01-01-2009|
      |CHF|1.644|01-01-2009|
      |CHF|1.500|09-09-2009|
      |CHF|0.6547|10-10-2009|
      |USD|1.3785|01-01-2009|

@rounding @bug452854
  Scenario: Validate the rouding computation on an invoice with 3 lines
    Given I have recorded on the 11 oct 2009 a supplier invoice (in_invoice) of 1144.0 CHF without tax called MySupplierInvoiceRounding
    And I add a line called MyFirstLine on the last created invoice of 91.73
    And I add a line called MySecondLine on the last created invoice of 63.00
    And I correct the total amount of the invoice according to changes
    When I press the validate button
    Then I should see the invoice MySupplierInvoiceRounding open
    And the total credit amount must be equal to the total debit amount
    And the total amount convert into company currency must be same amount than the credit line on the payable/receivable
account

      expected: 1983.71,
           got: 1983.7 (using ==)

       Diff:
      @@ -1,2 +1,2 @@
      -1983.71
      +1983.7

This fix the incoherence in price_accurace define in the server and the computation, but doesn't handle the trouble with 2 digit I think.

On two digit, you still have a difference between debit and credit moves...

I think it's ok to correct that, it's better than nothing, but don't close the bug as it did fix everything.

Regards,

Hi all,

I don't want to apply this patch because it only fixes one scenario of OERPScenario but not all.

I prefer to move this bug to 5.0.9 and implement a real solution to this issue.

Regards,

Then maybe the two problems should not made distinct again, and bug 513278 "Cannot validate Invoice with price_accuracy=3" should not be marked as a duplicate?
Then you could mark it solved and release the fix.
Lionel

Yes, good idea.

So, if we find the solution to this issue then we might release a new version for the next weeks.

@Numérigraphe,

Can you remove the duplicata flag on the bug 513278 and check with the patch of Anup ?
and we try to fix this issue with an other solution.

Are you agree ?

Oh - I mean, they //should be// made distinct again.

Numérigraphe a écrit :
> Then maybe the two problems should not made distinct again, and bug 513278 "Cannot validate Invoice with price_accuracy=3" should not be marked as a duplicate?
> Then you could mark it solved and release the fix.
> Lionel
>
>

Hi,

I finally spend half a day on this, and I give you what I got. The attached patch combine the last one of Anup and add a check/correction on debit and credit value according to the amount total of the invoice.

The point is the following (for me at least) :

- The Anup Patch seems to fix the sum debit != sum credit => Good !

- But the amount total of the invoice (in foreign currency) converted into company currency is still not = to credit or debit value on the receivable/payable account => Not good.

This issue let an invoice with an inconsistence when making the reconciliation.

My patch suggest the following:

- Once everything is ready to create the move line, it check that the condition is true (amount_total in company currency == credit or debit on the receivable/payable account).

- If there is a difference, then I add that difference on one credit and one debit line (the receivable and the biggest amount).

In another word, it correct the credit / debit total amount according to the rounding of the foreign currency. This avoid to have 0,01 cents to write-off when making the reconciliation.

For the future version 6.0, I'll strongly suggest to provide an account per currency / per company to deal with that, as it is in other software...

Please, review my patch cause we need something good for everyone.. I'm not that proud of my python coding on this one, advices welcome !

Regards,

FYI this patch passes my tests OK.
Lionel

confirm its working for me as well - 4 digits accuracy, invoice with many lines and in non base currency

This patch seems acceptable.
We are testing more.

Feedbacks invited.
Thanks.

Hello Joel,

I have a little confusion regarding the 'corrected' variable used by you. Please clarify.

Thanks.

Hi Jay,

I'm confusing too ;) I think I miss this one... I remember it was quite difficult to fix this bug. Reviewing my code, this part was to ensure correction has not already been done.

_correct_rounding_with_amount_total correct the amount according to the following:

- Find the biggest credit (or debit) line in the invoice move line and add the difference.
- To let the writing balanced, take the counter part (receivable or payable) and add
that same difference.

Doing this, credit is always = to debit, but without rounding trouble. BUT, we need to be sure that the difference is only added once on the receivable/payable account. Imagine you get 2 payable line in the invoice, you don't want to add the difference twice.

I post a new patch which take this into account.

Sorry for the inconvenient.

Regards,

Hello Experts,

Kindly check this patch and let us know please.

I vote for this patch.

Thanks.

Fix landed in:
 - rev <email address hidden> in addons (+ regression tests on next revision)
 - rev <email address hidden> in server

Warning for CampToCamp: the current test for this bug in oerpscenario is not correct!
This line specifically is implemented incorrectly:
  And the total amount convert into company currency must be same amount than the credit line on the payable/receivable account

You cannot compare the converted total with the credit total, you must sum explicitly the debit on all lines, otherwise you introduce your own rounding errors when there are none.

Your current error shows:
      expected: 1983.71,
           got: 1983.7 (using ==)

but after the fix you'll see on the confirmed invoice that the move does balance up to the final cent at 1983.71.

Also we merged the proposed branch of Numerigraphe with unit tests and fixed his test for this bug to mimic the data of the oerpscenario scenario. This should ensure we prevent regressions!

Hello all,
I installed the patch rouding2.patch from Joel but still got an error last friday with a supplie invoice :

Our main currency is CHF, the invoice was in €.

Amount : 4757.58
Taxes : 0
Date of invoice : 31.03.2010
Exchange rate : 0.690084
Rounding : 4

I tried to change the date of invoice with the 30.03.2010, exhange rate : 0.688942 and there, no problems the invoice is accepted ...

Thanks

@Olivier Dony

First thanks for your work, this is good to see... I miss the digit parameters in the rounding function of res_currency : Well done !

But I still get a trouble and don't agree on your remark about OERPScenario... Your last bugfix correct the balance trouble by making the debit always equal to credit which is good, I agree. Unfortunately, we still have a trouble with the credit value (in case of a supplier invoice) or the debit value (in case of a customer invoice)...

The amount on payable / receivable account should be the exact value in company currency of the total invoice, otherwise you introduce a rounding error, even with debit= to credit.

Please have a look on the attached print screen (note that the currency conversion is 0,6547 as in the test case you take from OERPScenario). You'll see that the amount on the credit is 1983.71 instead of 1983.70 (made by 1298.73/06547 = 1983.70245914 => 1983.70 on 2 digits).

This results in a rounding difference which SHOULD NOT appear in the payable/receivable account ! This is overall true when you'll reconcile this invoice... You'll introduce int the bank statement the exact amount in currency (here 1298.73 CHF), and OpenERP will say there is a difference because of the 0,01 rounding issue... it's not true from the user point of view ! If I invoice 1298.73 CHF and receive this exact amount in CHF on the same date, I expect the invoice to be reconcile without passing a write-off amount !!!

In fact, I expect OpenERP to make the following for this amount "1298.73":

- 1983.70 on credit on the payable account
- 0,01 on credit on the "rounding account"

So I can reconcile the real receive amount (1298.37) without passing a write-off...

That's why we discuss on the expert list the following solution:

- For trunk : Make something perfect, adding an account properties on res_currency to allow a "rounding account" per company. This will be use to pass the 0,01 difference.

- For stable : Add or Substract the 0,01 on the biggest debit/credit line to have the amount balanced

I hope I'm clear enough, this is not that easy to explain. Don't hesitate to call me if you want to discuss it !

Regards,

Joël

I just make some tests and it seems that applying both your patch and mine correct the problem in every known way :) !!! I vote for applying them ASAP.

By the way I just commit the fix that correct the little trouble in OERPScenario regarding this test. The last stable and trunk revision do the things properly.

Regards,

Joël

On 27/04/10 15:07, Joël Grand-Guillaume @ CampToCamp wrote:
> The amount on payable / receivable account should be the exact value in
> company currency of the total invoice, otherwise you introduce a
> rounding error, even with debit= to credit

No, the difference is acceptable and due to the way the totals are
calculated in multi-currency.
IMHO there is no reason to introduce a rounding adjustment at the time
of the invoice, because this is already the responsibility of the
reconciliation process.

> This results in a rounding difference which SHOULD NOT appear in the
> payable/receivable account ! This is overall true when you'll reconcile
> this invoice... You'll introduce int the bank statement the exact amount
> in currency (here 1298.73 CHF), and OpenERP will say there is a
> difference because of the 0,01 rounding issue... it's not true from the
> user point of view ! If I invoice 1298.73 CHF and receive this exact
> amount in CHF on the same date, I expect the invoice to be reconcile
> without passing a write-off amount !!!

Why shouldn't it be the reconciliation process that creates the
write-off? It already allows this, both in manual and automated process,
and does exactly what you want as far as I can tell.

The only thing is that it could be improved to compare the amounts in
foreign currency as well (if it does not do it yet), and perhaps use a
property for the default rounding write-off account.

> That's why we discuss on the expert list the following solution:
>
> - For trunk : Make something perfect, adding an account properties on
> res_currency to allow a "rounding account" per company. This will be use
> to pass the 0,01 difference.

Sure, why not, if the goal is to use this during reconcile process,
improving it a bit.

> - For stable : Add or Substract the 0,01 on the biggest debit/credit
> line to have the amount balanced

This definitely sounds like an ugly hack instead of using the normal
reconcile write-off system that can perfectly serve this purpose.

I think there is no point trying to avoid having rounding differences
appear in payable/receivable accounts as they are bound to happen in
multi-currency anyway, due to exchange rate variations.

And apart from that, it seems to me that you are only going to introduce
more complication in the process with your solution.

Am I missing something?

Olivier, I just saw that you recently applied this fix to the account/account.py file:

**********************************************************
- if abs(amount) < 10 ** -int(config['price_accuracy']):
+ # Check that the move balances, the tolerance for debit/credit must
+ # be smaller than the smallest value according to price accuracy
+ # (hence the +1 below)
+ # Example:
+ # difference == 0.01 is OK iff price_accuracy <= 1!
+ # difference == 0.0001 is OK iff price_accuracy <= 3!
+ if abs(amount) < 10 ** -(int(config['price_accuracy'])+1):
**********************************************************

But... shouldn't it better be "if abs(amount) < 0.5 * 10 **-(int(config['price_accuracy'])):"?

For example, with price_accuracy 2 we have this

For 0.01
    Original: 0.01 < 10 ** -2 => False
    Current: 0.01 < 10 ** -(2+1) => False
    Proposal: 0.01 < 0.5 * 10 ** -2 => False

For 0.007 (that would be rounded to 0.01 given the price accuracy)
    Original: 0.007 < 10 ** -2 => True (This was the original bug)
    Current: 0.007 < 10 ** -(2+1) => False
    Proposal: 0.007 < 0.5 * 10 ** -2 => False

For 0.003 (that would be rounded to 0.00 given the price accuracy)
    Original: 0.003 < 10 ** -2 => True
    Current: 0.003 < 10 ** -(2+1) => False (this is the problem of your patch)
    Proposal: 0.003 < 0.5 * 10 ** -2 => True

I mean, on the original version, an amount like 0.009 would pass the test, but afterwards it could be rounded to 0.01 causing havoc*
    * For an account move with two lines: 10.09 on the debit, 10.08 on the credit (unbalanced!), Python calculates a difference as 0.0099999999999997868 (that is less than 0.01!).

Your patch prevents this, but also means that an amount like 0.002 will not pass even when it would be rounded to 0.00 on the move lines!

Hi Olivier,

I understand your point of view. It seems that I don't explain well the remaining trouble... I defend the Expert point of view here, which is :

- The rounding difference introduce in the invoicing computation in foreign currency should be handled directly in this invoicing process.

This is no more a currency rounding trouble (your patch fix this), but more a business one. If the foreign currency rate has not been change between the invoicing and the reconciliation, why should the end user pass a write-off when making his bank statement ? This should not happened, cause nothing has change !

Because OpenERP round each line, and then make a sum, it introduce a rounding difference compare to sum all the line and then round the total... This result in a 0,01 difference (with price_accuracy=2)..

This should not be like this... So we need to correct the rounding "error" by adding or subtracting this 0,01 somewhere... The question at the expert list was how to handle this difference, 2 answers:

- Add a line with this amount in the invoice
or
- Add or substract it to the biggest line of the invoice

The expert choose the second option, and that's what I've done in my patch.

Is this trouble more clear now ? I would suggest to apply my patch as well, which (according to my tests) seems to work as expected by the expert team... Both of your and mine correction are the complete solution to this huge bug report I think...

Let me know if your opinion or if you need more (clearer) explanations...

Regards,

Joël

On 27/04/10 19:46, Borja López Soilán (Pexego) wrote:
> But... shouldn't it better be "if abs(amount) < 0.5 * 10
> **-(int(config['price_accuracy'])):"?
> (...)
> I mean, on the original version, an amount like 0.009 would pass the test, but afterwards it could be rounded to 0.01 causing havoc*
> * For an account move with two lines: 10.09 on the debit, 10.08 on the credit (unbalanced!), Python calculates a difference as 0.0099999999999997868 (that is less than 0.01!).
>
> Your patch prevents this, but also means that an amount like 0.002 will
> not pass even when it would be rounded to 0.00 on the move lines!

Technically you're right, but I think we don't need to allow it because
a difference of 0.002 is not supposed to happen if the rounding has been
done correctly on each line.
So to be on the safe side we prevent it completely.

Do you have a working example of a move that contains *balanced* and
*rounded* lines but that would not pass this test?

BTW if you look at the history this check used to be hardcorded to:
    abs(amount) < 0.0001
in all cases.

Hi everyone,

To finish the discussion on this issue, and specifically the remaining question of whether to have the invoice automatically handle additional "rounding moves" somehow, here is an explanation of the strategy that will be used in OpenERP.

We have considered all possible options, and came to the following conclusions:

 - OpenERP accounting is specific and does not need 'rounding moves' to be able
   to validate a multi-currency invoices with everything balanced (as opposed to other
   accounting systems that perhaps need it).
 - Accounting is a very sensitive and complex topic, but our goal is to try keeping the main
   process as simple as possible, and avoid bloating it with all kinds of exceptions and specific
   behaviors.
 - Specifically, we know that in many countries the law lets you handle these situations as you
   see fit. For example some "legally defined" charts of accounts do not even include dedicated
   accounts for "rounding adjustments" or "penny difference" as they were named here.
   This is why OpenERP will only implement the basic logic that is common to everyone in
   multi-currency.
 - I repeat: we want to be very clear for community and customers, OpenERP does not make errors in
   computing multi-currency invoices, and all moves are balanced.
 - Now for countries or companies that absolutely insist that the "penny difference"
   or "rounding adjustment" must be processed by the invoice itself and not to be handled by the
   normal reconciliation process (as for exchange rates write-offs), we will offer the same sort of
   solution as usual: this must be done in a separate module.

But in order to be able to cleanly implement a separate module as suggested in last item above, a refactoring of the current code is needed, to split the process in smaller chunks that will be easier to extend.
This refactoring cannot be considered for stable (5.0) of course, so it will be done in trunk (for 6.0).

However, we already want to provide an extension hook dedicated for this situation. Please see the definition of the hook that we propose in the patch in the next post. By default this hook is basically a "no-op", but you can use it to provide you own logic.
With this solution, you can directly write a module that implements everything you still wish to add. In fact you can even do this for 5.0! Your module could even add a property per company and currency to choose the account where you directly want to post the "penny difference" moves that you don't want to see on the payable account.

I think this concludes the discussion on this bug, and we can safely consider it fixed.

Sorry for the bad wrapping of the text in the previous post... Launchpad did a great job again...

Here is the patch we will introduce in account module to provide extension hooks for handling specific cases during creation of the account.move.lines that correspond to an invoice.

Hi Olivier,

Thanks you very much for everything !! This is a clean solution that perfectly fit our need, I can assume this in the l10n_ch module easily !

Best regards,

Joël

Small typo correction to original patch provided in comment #98

See also revno: 2730 - revision-id: <email address hidden> in openobject-addons for the actual change.

Am Freitag 30 April 2010 13:03:07 schrieb Olivier Dony (OpenERP):
> Sorry for the bad wrapping of the text in the previous post... Launchpad
> did a great job again...
>
> Here is the patch we will introduce in account module to provide
> extension hooks for handling specific cases during creation of the
> account.move.lines that correspond to an invoice.
>
> ** Patch added: "Extension hook patch for account.invoice"
>
> http://launchpadlibrarian.net/46566380/account_invoice_hook_move_lines.pat
> ch

it would be great to get this functionality also for bank statement line to be
"complete"

this would allow to solve the problem
* missing analytic account
* missing VAT
as a module

IMHO it should be provided for
* move line
* bank move line
separately
--
mit freundlichen Grüßen
Ferdinand Gassauer
ChriCar Beteiligungs- und Beratungs- GmbH
Strakosch GmbH
Terrain und Liegenschaftsverwertungs GmbH

--
Best Regards

ChriCar Beteiligungs- und Beratungs- GmbH
http://www.chricar.at/ChriCar/index.html
Dr. Ferdinand Gassauer
Official Tiny Partner

I am sorry, but this is ridiculous - it is not a bugfix. So I mark this bug as New.

For reference:
http://openobject.com/forum/topic17596-15.html

Hello,

It's not ridiculous. The proposed patch seems to work fine for everyone. As the bug is very big and contains lots of noise, and nobody else seems to have troubles, I propose to close it. If you have a use case where it fails, I would suggest to create a new bug with a clear scenario.

Thanks for the contributions,

Fabien,

Because OpenERP S.A. ignored all the comments we posted here (read after comment #60) + the mail we sent to the accounting expert list, I sent you a mail of the cases that still fails for us both on v5 and v6 (August the 4th), so you can't tell you don't know about it (the price accuracy > 2 part of the bug). I think we already spent like 3 days on this, we already explained the issue in like 3 comments there, 2 or 3 mails to the accounting expert list + that last mail recently, so you can't tell we never explained what the remaining problem was. Probably because we already spent so much time trying to communicate with OpenERP S.A. about this bug, unfortunately, I'm not sure I can do more now (and it's all explained in the comment + mail I sent you + Olivier and others in any case), we will eventually re-open an other bug if nothing is done as soon as we have time.

I am sorry, but this is clear case when a bug is being closed, just because Fabien has to write another statement of bugfix performance with nice and shiny performance graphs. This is not the first time.

Sorry, I will reopen this bug, and will do this every time I go for revision of related bugs.

Hi I had this issue.

Would this be appropriate:

if abs(amount) dp.get_precision('Account'):