residual amount error with different currency in invoice

[Alberto Garcia (Factor Libre)]

There a re a problem with pay invoice and residual amount when you sale with different currency.

Base currency: EUR

You create a sale and the an invoice. You use a pricelist in USD.

For example:

A product with price 20.35 €, in invoice you have the price 28,05 USD

You pay invoice from invoice.

You put 28,05 $ and fill he rest of fields.

Then you fill the write-off dialogue and full payment.

After that you have a difference of 7.70 on residual amount that you can't solve.

why?

[Mantavya Gajjar (Open ERP) (mga)]

Hello,

problem is in the method `def _amount_residual` its not take care for the currency used in the transection (move.lines)

[Vinay Rana (OpenERP) (vra-openerp)]

Hello ,

Please Apply the Following Patch and Notify me.

Thanks.

[Alberto Garcia (Factor Libre) (agarcia-flibre)]

for me work well

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

See also :

https://bugs.launchpad.net/openobject-addons/+bug/429167

And:

https://bugs.launchpad.net/openobject-addons/+bug/432456

Those are only one huge bug on financial reconciliation !

On the second linked one (432456), pay attention on the little difference showing in the bank statement when showing the original amount (0.01 cts difference, point 2. in my bug report :

2. Bank statement using button "import invoice" => amount in EUR is correct, but CHF one is 0,01 CHF.- wrong !! (first problem). The account journal is in CHF also.
)

[Alberto Garcia (Factor Libre) (agarcia-flibre)]

what about commit for this patch?

[Alberto Garcia (Factor Libre) (agarcia-flibre)]

I think this patch nor work with suppliers invoices, With customers invoices work well.

[Samuel Bissig (Toradex) (samuel.b)]

We tested the reconciliation over the button "Pay Invoice" in the Invoice. There the payment is done correctyl now. We have not tested the automated reconciliation.

[Jay Vora (Serpent Consulting Services) (jayvora)]

Hello Subscribers,

Thank you a lot for the responses.

Here is the more suitable patch.

Would you please apply this and let us know?

Thanks.

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hello Jay !

Thanks for the effort, part of the problem seems to be solved :) When using the wizard "Pay invoice", everything is now ok !

But I still get trouble using the bank statement, from this menu :

Financial Management -> Entries by Statements -> New Statement

Please refer to my post here:

https://bugs.launchpad.net/bugs/432456

I attach some print screen to let you see the problem which is still remaining :

1. The amount in the bank statement (once you have imported the invoice) is still wrong for 1 cts

2. The amount residual in the invoice is still wrong with this method.

At least the amount on the move line is ok now.

Thanks for your work,

Regards,

Joël

[Samuel Bissig (Toradex) (samuel.b)]

Thanks a lot. We tried to test your patch. Do we have to apply both patches or only the last one?

[Jay Vora (Serpent Consulting Services) (jayvora)]

Hello Samuel,
Thank you for your interest.

Suitable patch is the second one.
We are investigating more for that problem. Sooner,we will set this fixed.

[Samuel Bissig (Toradex) (samuel.b)]

Hello Jay,
We tried with the latest patch. For us in some cases it is fine when we do a payment with the wizard. But we get an wrong numbers if we use a payment account with an other currency then our company currency has.

A short example:

Comany currency is: CHF
Invoice is in: EUR
Payment Journal is in: CHF
--> Everthing works fine (see printscreen ok.png)

Comany currency is: CHF
Invoice is in: EUR
Payment Journal is in: EUR
--> Negative residual (see printscreen failed.png)

[Samuel Bissig (Toradex) (samuel.b)]

We just noticed, that when we delete the currency (and default company currency is used) in the journal it works. But course we expect, that the journal works with other currencies, too.

[forstera (arnaud-forster-deactivatedaccount)]

Hello,
I tried to apply the 2 patches but when we try to reconciliate, we have now the following error is displayed :

Error, couldn't create move with currency different from the secondary currency of the account "...". Clear the secondary currency field of the account definition if you want to accept all currency. Our standard currency is Swiss money and we have this error for an invoice in € in an account in €, too.

Is this message a normal behaviour ?

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hello Jay,

I just want to know if you understand well my problem description about this bug... I'm worrying about because fixing the pay invoice wizard is important, but it's also important to fix it in the bank statement... The problem has probably a common root.

If you do need some more details or help for that, please let me know... We really have big trouble with this bug on our OpenERP servers... And I'm afraid we're not the only one :(

Thanks for your work,

Regards,

Joël

P.S. @ Forstera : I think you should only use the second patch...

[forstera (arnaud-forster-deactivatedaccount)]

ok, joel, thanks.

If you need someone for testing purpose, don't hesitate.

thanks

[Stephane Wirtel (OpenERP) (stephane-openerp)]

Question, this bug seems to be related with the price accuracy parameter in the server,

--price_accuracy=3

Stephane

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi,

Thanks to have a look Stephane !

I'm really surprise about your remark.. May be this could be part of the problem, but it sounds strange to me... Even if I decide to have a price accuracy of 2 (or 3 it doesn't matter), it should not produce a wrong result in residual amount or in the shown amount in the bank statement !

I just copy / paste my bug description from bug 432456... So everything is here to describe the full problem...

Regards,

Joël

--------------------------------------------------------------------------------------------------------------

Hi,

This bug report concern the reconciliation in multi-currency on last LP version 5.0.5: Wrong amount for reconciled part !

This is I think a very critical one...

Procedure Senario I :

1. Company in CHF, invoice in EUR (rate: 0,6), with 7,6%VAT for 1076EUR.- => account move is right : 1793.34CHF.-

2. Bank statement using button "import invoice" => amount in EUR is correct, but CHF one is 0,01 CHF.- wrong !! (first problem). The account journal is in CHF also.

3. Confirm the statement and go back to the invoice, the following amount are found:

  - Untaxed : 1000EUR.- => Correct
  - Tax : 76 EUR => Correct
 - Residual : -717.34 => TOTALLY WRONG !!! Expected amount is 0 !

At that point, the account.move is right in CHF, only the invoice showing amount is wrong... In the account.move you see there is no currency amount, even if the account is set with currency : EUR. This is also a trouble : amount in currency must be there if I set it on the account.

Note that if I correct the currency amount, the residual amount on the invoice is correct !

Senario II :

The same, but with a journal in the bank statement in EUR => No problem at all. When all currency are the same, seems to be ok...

Senario III:

The same than 1., but with a journal in USD : Same trouble than in point 1., the residual value is not correct !

--------------------------------------------------------------------------------------------------------------

[forstera (arnaud-forster-deactivatedaccount)]

Hello,

Our account manange saw another problem, I dont know if the last version correct it. He tried to reconcile the same invoice twice using different processes (once manually and once using .. Oops i dont know the word in english, so in french : encodage des extraits bancaires.

..

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

@ forstera (but for all:)

This is part of all the problem... You can use many way to reconcile invoices, all of them should succeed the same way :

- Using the pay invoice wizard
- Using the bank statement (encodage des extraits bancaires)
- Manual reconciliation

This bug will be fixed when all of those methods will reconcile correctly, and with the same result, an foregin currency invoice...

Regards,

Joël

[forstera (arnaud-forster-deactivatedaccount)]

Ok, so what about the announce of the 5.0.7
is it a correction ?

[forstera (arnaud-forster-deactivatedaccount)]

Another problem found (doesn't if already rapported), please refer to the picture. When we've an invoice in Swiss Francs(1) the system make the conversion in € (2). Then, when there's the reconciliation, the swiss francs seem to be considered as € (3) but with a small difference. And finaly the new € amount is converted again in swiss francs ...

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

Fixed in r2423.

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

I may be wrong, but I don't think there is a bug in the reconciliation process ?
The bug I found was in the residual amount function field on an invoice. Fortunately, this residual amount has no accounting impact.

[forstera (arnaud-forster-deactivatedaccount)]

Hello,

We'd like to test the fix but did not find it. Where can if find it ?

Thanks

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi !

I just make the test and the problem is still here :(

But the amount residual shown is still incorrect. We do have a problem into the reconciliation process I think. Doing the following :

1. Company in EUR, CHF rate at 1,64

2. Create an invoice in CHF for 1200.-, Confirm it.

3. Create a bank statement from Finance -> Entries Encoding -> Entries by Statements -> New statement

4. Choose a EUR journal, import the invoice and confirm the statement (The amount paid is 729.93 EUR, which is correct)

5. Go back to the invoice, the state is "Done" and the residual amount is 470.07 CHF.- and should be 0.- (1200 CHF - 729.93EUR = 470.07 !!)

Looking into the partner balance, it seems to be ok and balanced ! This means that we may be still have trouble inside the residual amount function of the invoice...

Regards,

Joël

[Samuel Bissig (Toradex) (samuel.b)]

We made the update and also have still the same issue as reported in comment #15, #16 and #17. If payements are done over the Finance -> Entries Encoding -> Entries by Statements -> New statement, we have the same problem as Joël.

[Christophe Simonis (OpenERP) (kangol)]

I can't reproduce the bug.
As the value of the field is stored in database, old invoices still have a wrong amount.
You can force the recompute of the field by modify any other field in the invoice (i.e: origin).

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hello !

Could you tell me the revision number please :) I can't find your commit in the stable branch...

Thanks,

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi Christophe !

I assure you that this bug is still here :( I send you this link to a video in order to show you how I proceed :

http://screencast.com/t/kaPu5gZLs

Please, take 10 minutes to look at it, and try it yourself on a new standard DB with last server version.

Thanks in advance,

Joël

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi Christophe,

After our phone call, I do the following as you wanted:

1. Create a new DB with accounting only profile, BE accounting plan
2. Load demo data and let everything by default
3. Follow the same procedure as in post #30

At the end, I got exactly the same as always : the amount residual is 470.07 insteed of 0 :(

Here are my revision:

Addons: 2428
Server : 1877
Extra-addons : 3918

All from stable branches...

I'm waiting your video to see how the hell it's possible you get another result !!

Thanks for your time, this bug is driving me crazy !

Regards,

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi !

After re-installing a new machine from scratch with all needed dependencies, creating e new DB with demo data (accounting only profile) I finally get the expected result : 0.0 in residual amount :) !!

I don't explain my-self why it doesn't work on my server and my own machine... But on the new installed one : everything seems to be all right !

So, I close this bug... and start to search why...

Thanks to everone who has contribute to this bug,

Regards,

Joël

[forstera (arnaud-forster-deactivatedaccount)]

Hello all,

After reading the last comment of Joël, I was afraid that the fix only works for new databases because our database has hundreds of records in the accounting tables, so starting a new fresh database was not an option. People from the accounting dept. just come to me to stay that they tried the last version and the first record they tried to reconcile was false but, for all others they tried, the reconciliation is correct. So, for the while, it seems to work. They said that there's stil the VAT problem but this is not a bug ...

Thanks

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi,

I re-open this bug cause the trouble seems always here ! I found this bug :

https://bugs.launchpad.net/bugs/461617

Which is the same problem.. so I mark the number 461617 as a dupplicate of this one.

Regards,

Joël

[nullpointer (robert-dbservice)]

OK people, I am the reason for the re-opening the bug :) (https://bugs.launchpad.net/bugs/461617). So I played a bit with the payment process. I implemented the code from the comment #3 in my original post and I have seen, that the error occurs only when:

1. The invoice currency <> company currency
2. The currency rate changed between the invoice date and payment date.

Accounting process calculates the right write-off amount (currency rate loss/gain is added to the write-off account), only the residual amount on the invoice is (still) wrong. The residual bug is, at least in my case, result of the changed currency rate.

[bassamh (bassamh)]

When do you think 5.0.7 will be out?

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

@nullpointer

I agree with you, and I had the same conclusion yesterday.

I also investigate a bit more and found this:

- Why have we the write-off into payment tabs in the invoice form (when you'r currency rating has changed between invoice and payment date) ?

- The function _get_lines into account.invoice seems to provide the wrong move line to the _amount_residual function. So I think the problem come from here. The _amount_residual function is correct, but the _get_lines or the move_line_id_payment_get function have some trouble.

It's not that easy to understand what they are doing... I run out of time, but if I can, I will fix this.

Regards,

Joël

[nullpointer (robert-dbservice)]

The patch from this thread does not work for me, after applying the patch in fresh installed 5.0.6 I noticed strange behavior of the pay invoice wizard. In my scenario - company currency CHF, invoice currency EUR, payment journal currency CHF - the payment wizard ignores the journal currency and calculates using invoice currency. Now I have in my bank journal, that is defined as CHF currency journal, entries in both CHF and EUR!

In my original thread

https://bugs.launchpad.net/bugs/461617

you find code snippet from Jan Verlaan for better _amount_residual calculation. This 'patch' works for multiple currencies, when the rate does not change between invoice date and payment date. To be perfect the debit/credit calculation in the _amount residual function must apply the currency rate changes. In code it means the debit/credit values in inv.move_lines must respect the currency rate changes.

IMO the problem is in the _amount_residual calculation.

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi !

I spend lots of time on this bug and it's very complicated. I provide a patch here which correct the main issue (need to be more tested) : different currency, different rate...

For me it work fine, but because everything is recorded on 2 digit into the DB, you can have 0,01 cents difference during the payment process... At least till you pay the whole invoice completely.

Example:

Amount of 121.65450121654501 is recorded in DB as 121.65. So with a rate at 1.644 you get : 121.65 * 1.644 = 199.9926, which is 199,99 according to accuracy of 2. But the original amount was 200 which correspond to : 121.65450121654501 * 1.644

I warn everybody here : we really need to fix the problem deeply into the ORM. Python need Decimal type instead of float to handle money properly... So, because of that, I don't have a better solution than the one I suggest here.

Don't hesitate to ask question to me, I don't have more time right now to be more precise. If you've got an idea... please share it !

Regards,

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi !

I spend lots of time on this bug and it's very complicated. I provide a patch here which correct the main issue (need to be more tested) : different currency, different rate...

For me it work fine, but because everything is recorded on 2 digit into the DB, you can have 0,01 cents difference during the payment process... At least till you pay the whole invoice completely.

Example:

Amount of 121.65450121654501 is recorded in DB as 121.65. So with a rate at 1.644 you get : 121.65 * 1.644 = 199.9926, which is 199,99 according to accuracy of 2. But the original amount was 200 which correspond to : 121.65450121654501 * 1.644

I warn everybody here : we really need to fix the problem deeply into the ORM. Python need Decimal type instead of float to handle money properly... So, because of that, I don't have a better solution than the one I suggest here.

Don't hesitate to ask question to me, I don't have more time right now to be more precise. If you've got an idea... please share it !

Regards,

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

I'm sorry for the double post here... LP Copy paste my text when adding the patch.

I investigate a bit more around that problem and here are my last conclusions and a suggested solution with attached patch.I saw that there is two problem here :

- The way this residual was computed
- The rounding method included into OpenERP

This patch/bug want to fix the first one, I'll open a new bug report for the second one (which is why we could sometimes have rounding trouble into residual amount). I won't report the whole test case until someone want it, but here are a summary :

1. Create an invoice in CHF with company currency EUR, amount 1000CHF, date invoiced : 1.9.09
2. First payment with pay wizard : 200 CHF with the date 1.9.2009 with 1,644 currency exchange rate
3. Second payment : 200CHF with the date of 29.10.2009, 1.5 currency exchange rate
4. Third payment : 200 USD with the date of 29.10.2009, 1,3785 currency exchange rate
5. Fourth payment : 100 EUR with the date of 29.10.2009
6. Now full payment with suggest amount to close the invoice (232,38 CHF)

According to the following patch, the amount residual take care of the different currency correctly, but we have a rounding problem (see futur bug report). It also take care of the difference into CHF currency which was 1,644 on the 1.9.2009, but then 1,5 at 29.10.2009.

- At the step 2. we have 800,01 as a residual amount
- At step 3. we have 600,01 as a residual amount
- At the step 3. we have 382,38 as a residual amount
- At the step 4. e have 232,38 as a residual amount

When I pay 200CHF on a 1000CHF invoice, I expect the system to show a residual amount = 800,00, not 800,01. But it cannot be fixed into this bug, this need another investigation. Also note that this is a special case, because of the exchange rate set and this will not happen ofently...

How this solution handle the problem :

Give a date in the context to the currency computation if currency from company and invoice are different. Then, as long as the invoice is not fully paid, I take every amount of payment line in company currency (EUR here) and convert them to the invoice currency (CHF here). So in french it's called "devise pivot", which mean we use the company currency as a reference to deal with the residual amount. We always take during this process the date of each payment line for the exchange rate computation.

When the invoice is fully paid (according to the residual amount suggested, which come from previous computation), then the computation of the residual amount is different. We compute it like this :

Sum debit and credit in payment lines (mean amount in EUR in this case) and convert the result into the invoice currency (CHF here). This take care of the write-off and because this one is always supposed to balance the invoice, the result will always be 0.0. Anyway I think it's important to let the computation be done and not only put 0.0.

So with this solution, we are computing the residual amount correctly I think. Tests are welcome, Luc has already approved this on my test instance.

I suggest to merge this ASAP if you agree

Regards,

[Jay Vora (Serpent Consulting Services) (jayvora)]

Thank you for the work Joel.

The code is available in stable.

[forstera (arnaud-forster-deactivatedaccount)]

Hello ,

We just upgrade the module account (version from 11 of january, openobject-addons/stable_5.0...) but we still get the error message 'integrity_error'. Our accounting depatment made 2 tests under the following situation : foreign clients in €, swiss money for us. The invoices were made in €. 2 invoices were made with the same client but with different amounts (more than 60'000 € for the first invoice and less then 10'000 € for the second). In the first case, we've got the integrity error when trying to validate the invoice but not problem with the second invoice.
So, is it possible that the problem appears when the invoice is bigger than a given amount ?

Thanks for your help

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi,

What's the message "integrity_error' ?! I just try what you explain, but got no errors at all (for both 10'000 and 60'000 amount) on the last version from today.

Could you please add more infos on the error you get ?

We write a test case into OERPScenario for this bug, and got no errors... If you want, I can give you the detail of the test.

Just remember this bug report is about the residual amount, not the validation of the invoice...

Regards,

Joël

[forstera (arnaud-forster-deactivatedaccount)]

You're perfectly right, I'm on a the wrong Bug . This has to be with the (Bug #452854 ). We tested it again and the problem comes from the exchange rate. We've your module that retries automatically the exchange rate. So, with the exchange rate of 20.10.2009 (0.652571) , we receive an integrity error : you cannot validate a non-blanced entry . Now, if we change the date of the invoice to doday (0.668717) with another exchange rate, no problem !
so, there's really a problem depending of the exchange rate ..
I post this under the other bug ..