Pay invoice wizard regression in foreign currency

[Joël Grand-Guillaume @ camptocamp]

Hi !

There is a regression introduce by commit 2537 into addons on the pay invoice wizard !

The amount in currency are wrongly computed !!!

Here the test case :
  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: make_and_validate_payments_with_pay_invoice_wizard
      Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 1000,0 CHF without tax called MySupplierInvoicePayWizard
      When I press the validate button
      Then I should see the invoice MySupplierInvoicePayWizard open

      When I call the Pay invoice wizard
      And I partially pay 200.0 CHF.- on the 10 jan 2009
      Then I should see a residual amount of 800.0 CHF.-

      When I call the Pay invoice wizard
      And I partially pay 200.0 USD.- on the 11 jan 2009
      Then I should see a residual amount of 561.48 CHF.-

CRASH HERE expected: 561.48,
           got: 600.0 (using ==)

      When I call the Pay invoice wizard
      And I partially pay 200.0 EUR.- on the 12 jan 2009
      Then I should see a residual amount of 232.68 CHF.-

      When I call the Pay invoice wizard
      And I completely pay the residual amount in CHF on the 13 sep 2009
      Then I should see a residual amount of 0.0 CHF.-
      And I should see the invoice MySupplierInvoicePayWizard paid

THANKS TO REVERT THIS COMMIT ASAP !!

Regards,

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

Hello Joel,

This is the patch which will ease your worries.

However,I am investigating more for write_off related issues with different currencies.

Thanks.

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

Hello Joel,

I have followed Jay (Open ERP) attached above patch and added write-off related issues with different currencies so can you apply the attached patch and notify me.

Thanks.

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

Hi,

The first patch is perfect ! The wizard is now computing the things correctly and all my tests case work well (except the validate a non-balanced entry) : Good !

But the second one breaks part of what was done with the first one...

I suggest the following :

- Commit the first patch
- Close this bug report as it's fixed.
- Use https://bugs.launchpad.net/openobject-addons/+bug/452854 to solve what the second patch try to address.

I give you here what breaks with the second patch (you can test it by yourself using OERPScenario, stable branch):

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: make_and_validate_payments_with_pay_invoice_wizard
      Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 1000,0 CHF without tax called MySupplierInvoicePayWizard
      When I press the validate button
      Then I should see the invoice MySupplierInvoicePayWizard open

      When I call the Pay invoice wizard
      And I partially pay 200.0 CHF.- on the 10 jan 2009
      Then I should see a residual amount of 800.0 CHF.-

      When I call the Pay invoice wizard
      And I partially pay 200.0 USD.- on the 11 jan 2009
      Then I should see a residual amount of 561.48 CHF.-

      When I call the Pay invoice wizard
      And I partially pay 200.0 EUR.- on the 12 jan 2009
      Then I should see a residual amount of 232.68 CHF.-

      When I call the Pay invoice wizard
      And I completely pay the residual amount in CHF on the 13 sep 2009
      Then I should see a residual amount of 0.0 CHF.-

expected: 0.0,
           got: -9.75 (using ==)

       Diff:
      @@ -1,2 +1,2 @@
      -0.0
      +-9.75
       (Spec::Expectations::ExpectationNotMetError)
      ./features/account/wizard/step_definitions/wizard_pay_invoice.rb:78:in `/^I should see a residual amount of (.*) (\w+)\.\-$/'
      features/account/wizard/pay_invoice.feature:34:in `Then I should see a residual amount of 0.0 CHF.-'

      And I should see the invoice MySupplierInvoicePayWizard paid

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

Hello Joel,

The Difference show in minus because of rate fluctuation, means the final write-off entry is not as a write-off entry as we paid full amount but due to rate changes when the total is done, it will not same as final paid amount so the wizard take the remaining amount as a write-off(which is actually rate difference total) so it will be done entry as write-off.Finally when the residual amount will be calculated it will show the remaining extra amount with minus sign.

Hope you understand what i wanted to tell you.
So it will needed to define some help in the residual field or show this rate difference in separate field.
Thanks.

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

Hello vra,

Ok, I think I get what you mean ! According to my test case :

- I have the following payment line in Payments tabs of invoice (see attached print screen "print1.png") before your patch.

- After your patch, I have what you see in the "print2.png"

What I deduce here is that all credit / debit amount are correct in both cases which is the most important ! Then, I think adding the amount currency and the currency on the line is correct also, and this wasn't the case before your patch : good !

I also check the residual amount function of the invoice, and this is still correct.

I found the problem here, in the line amount. You don't take the right currency rate...

Example:

On the 13.09.2009, you fully pay the invoice for 232.68 CHF, which mean a write-off => ok. But then you don't take the right rating for this write-off :

- 232.68/155.12 = 1.5
- 8.9 / 13.59 = 0.65

Hope it helps !

Regards,

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

Note : I ask the accounting-expert team on that one, cause after thinking for a while, it's may be more complicated than expected...

Thanks to wait on the answer...

Regards,

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

Joel, can you follow this bug ?

Thanks

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

Hi Stephane,

What do you expect from me ?

I'm ready to help if I can, let me know !

Regards,

Joël

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

Hello Joel,

We are working on a more suitable patch and will ask you for your suggestion on that.

Thanks.

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

Hello Experts,

Would you please try and check with the attached patch?

Thanks.

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

Hi Jay,

I just tested your patch and unfortunately, it doesn't pass my test on OERPScenario, here are the results :

      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|

      Given a cash journal in USD exists
      And a cash journal in CHF exists
      And a cash journal in EUR exists
      And on all journal entries can be canceled

    Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 1000,0 CHF without tax called
    When I press the validate button
    Then I should see the invoice MySupplierInvoicePayWizard open
    When I call the Pay invoice wizard
    And I partially pay 200.0 CHF.- on the 10 jan 2009
    Then I should see a residual amount of 800.0 CHF.-
    When I call the Pay invoice wizard
    And I partially pay 200.0 USD.- on the 11 jan 2009
    Then I should see a residual amount of 561.48 CHF.-
    When I call the Pay invoice wizard
    And I partially pay 200.0 EUR.- on the 12 jan 2009
    Then I should see a residual amount of 232.68 CHF.-
    When I call the Pay invoice wizard
    And I completely pay the residual amount in CHF on the 13 sep 2009
    Then I should see a residual amount of 0.0 CHF.-
      expected: 0.0,
           got: -9.75 (using ==)

       Diff:
      @@ -1,2 +1,2 @@
      -0.0
      +-9.75
       (Spec::Expectations::ExpectationNotMetError)
      ./features/account/wizard/step_definitions/wizard_pay_invoice.rb:84:in `/^I should see a residual amount of (.*) (\w+)\.\-$/'
      features/account/wizard/pay_invoice.feature:39:in `Then I should see a residual amount of 0.0 CHF.-'
    And I should see the invoice MySupplierInvoicePayWizard paid

I strongly suggest you work from this test case which is probably representing on of the most complex case with this wizard... Good luck for this one, as it seems to be really tricky !!

Regards,

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

Ok sure Joel,
I will give another try.
Thanks.

[forstera (arnaud-forster-deactivatedaccount)]

Hello all,

I wrote yesterday about an exchange rate problem with our invoices vra redirected me on this bug. After reading the problem, I'm not sure it exactly the same problem. Here's what's happend to us.

Our main currency is CHF. We've installed the module 'c2c_currency_rate_update' and it retrieves the exchange rate everyday at 11 am.
We had to pay a supplyer invoice that was in €. The amount was € 2782. When the invoice was made, this was on february the 17th at 10:52 am. So the system took the exhange rate of february the 16th.

When the accounting paid the invoice on march the 3rd, that was difference due to the different exchange rate (which is normal) and they had to add a bookeeping entry for the difference of rate. But the problem is that even afte that, we had a residual amount that should not be there.
I've found that the problem (the residual amount) is in fact the difference between the amount calculated on february the 16th and february the 17th. In all tables (account_invoice, account_move_line, ...) the values are caculated with the exchange rate of februaray the 16th but somwehre, this is the exchange rate of february the 17th that is used which causes this residual amount.

I don't know if we can joing these 2 cases ...

Thanks

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

Hello Guys,

Here is an updated patch.

I have tested with many cases, tell me if still it fails.

Thanks.

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

Some Corrections added.

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

Hi Jay,

I'm sorry, but I'm afraid we still get a trouble with this one :( Here is my results according to my test:

Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 1000,0 CHF without tax called MySupplierInvoicePayWizard # features/account/step_definitions/invoice_steps.rb:27
    When I press the validate button
    Then I should see the invoice MySupplierInvoicePayWizard open
    When I call the Pay invoice wizard
    And I partially pay 200.0 CHF.- on the 10 jan 2009
    Then I should see a residual amount of 800.0 CHF.-
    When I call the Pay invoice wizard
    And I partially pay 200.0 USD.- on the 11 jan 2009
    Then I should see a residual amount of 561.48 CHF.-
      expected: 561.48,
           got: 705.01 (using ==)

I also investigate a bit morre my-self... The residual amount is still wrong computed after the second payment (in USD). The details:

Invoice amount : 1000 CHF => 608.27 EUR with the actual rate (1.644)
Pay 200 CHF => 145.09 EUR with rate (1.644)
Pay 200 USD => 121.65 EUR with rate (1.3785)

=> So to compute the remaining total you do :

(EUR Invoice total - EUR Payments) convert in CHF at the invoice date :

(608.27 - 145.09 - 121.65) = 341.53 EUR => 341.53 * 1.644 = 561.47

This prove there is still a problem with it...

Good Luck,

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

Hi again Jay,

I just was curious about why touching the method _amount_residual in invoice.py. I remember I spend quite a lot of time to fix it properly... So I decided to apply your patch, but without the invoice.py part.

This means, I only patch the wizard, and guess what : The tests succeed :) !

So, may be I'm not aware of all troubles regarding this problematic, but at least, from my point of view, the patch is good as soon as you remove the invoice.py part.

I submit exactly the one I applied. Make your own test and keep me inform.

Regards,

Joël

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

Hi Joël,

Thank you for your quick response.
The change has to be done in invoice.py. It is good to do when we use more than 2 currencies and pay via different currencies.

Kindly test your complex case. We have succeeded here and hoping for this bug to die soon :).

Thanks.

P.S : I just corrected the patch by comment #16.

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

Hi Jay,

I just re-tested your last patch and it don't work for me: (

It still works if I only apply the wizard patch... This is really strange !

Have you a test case which needs the invoice.py one ? I don't understand the purpose of those corrections...

Thanks in advance,

Regards,

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

Hello Joel,

I have added one more thing in 'Jay' attached patch in #commet:16 regarding restriction for amount payment limits in partial or full payment.

Can you please check and notify me?

Thanks.

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

Hello Joel,

I have added one more thing in 'Jay' attached patch in #comment:16 regarding restriction for amount payment limits in partial or full payment.

Can you please check and notify me?

Thanks.

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

Hello,

Earlier it was the case where the wizard was not sending the currency of Payment journal(the currency of the journal selected on payment wizard). So everytime it was assuming that the currency is always the invoice's currency. Unfortunately, it is not the case always. Remove the patch and try to see the Payment Lines of Invoice(Many2Many field).

So, the cur_diff variable does the trick and it saves lot of people.

Regarding the change in residual calculation.
we only need to work upon the currency of Invoice and payment line(and that is what the current patch does.) Earlier, it was not checking currency of payment line.

Amount currency has to be counted when it concerns same currency of invoice on journal of pay wizard.
I'm sure that it will not work on old partially paid invoices.

Queries Invited.
Thanks.

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

Hi !

Trying the last patch on post #22, I get the following errors when making the scenario reported on comment #12 :

Error !
The amount you pay :200.0 in CHF, exceeds the invoice amount :1000.0

This is not true at all cause I only pay 200.0 CHF which are 145.09 EUR with rate (1.644)...

This bug start to drive us crazy, but keep going, I think we're close to find a good solution !

Good luck,

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

Hello Joel,

Please check the attached patch with some correction and notify me.

Thanks.

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

Hi,

I still get the same error message as in comment # 24 :(

Please, I'm really happy to help on those problematic, and even suggest patches when I can, but could you at least make some little tests before asking me to do some ?

I mean, at least try to make the 2 first payments as explain in comment #12, and see i it pass...

May be we've already done some and it only fail in my own test instance... in this case, sorry for my remark.

Regards,

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

Hello Joel,

Would you please apply this patch and Notify me?

I have tested your case successfully.I have done some amount adjustment which is '0.01'.
If it is is allowed then this patch works smoothly.

Thanks.

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

Hi,

This still seems wrong to me, I'm sorry :(

The first payment of 200 CHF (121.65 EUR) is alright and the residual amount also. For the second payment of 200.0 USD (145.09 EUR), the residual amount is wrong.

We should have 561.48 CHF.- left, and I got 582.37 CHF.- => Should be compute like : (145.09+121.65)*1.644-1000, where 1,644 is the rate of CHF on the invoice date.

But, we're really close now.. I don't find how you get the 582.37 CHF left, may be the wrong rating is taken...

Regards,

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

Hello Jeol,

I have got following amount difference between actual amount and paid entry amount (I have got 232.67 in residual before full payment.)so may be its currency rate fluctuation:
'amount_paid'(Total paid amount)>> 621.85
'inv_amount_company_currency'(Total amount in invoice company currency)>>> 608.27.

Currently i have put warning message for not allowing this. But there should be another solution for this issue.
May be we can maintain separate account move for currency rate fluctuation.

I need your suggestion please for finalizing this issue.

Thanks.

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

Hi vra,

I send you here an .ods file which regroup, I hope, all the needed informations to let you understand how it should be compute. I currently don't really understand your problematic as describe in post #29.

Taking each line at its own rating and currency should give you the good results. Please have a look inside the cells formula of the Sheet to know how I compute the residual amount in invoice currency and in company currency.

Feel free to annotate this file and send it back to me with your questions / remarks if it's still not clear enough !

Thanks for the work,

Good luck,

Joël

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

Hello Joel,

I have checked the '.ods' file and same result i have got at my end.
So thats way i am asking regarding minus amount because the total amount payment is greater then invoice amount.
The clear reason is currency rate fluctuation.
We need to settled this minus amount either making this as a separate move or ignore this amount.

Can you please provide me your view regarding this issue.

Thanks.

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

Hi vra,

Ok, now, I get your point :) ! What's certain here is that we need to be able to pay more than the invoice amount ! As you said, due to rate fluctuation, this situation can happened.

At the beginning, when there was a difference between invoice amount and paid amount, the wizard asked the user for a write-off. So I will suggest to restore this "feature" when using the "Full payment" in both cases :

- When paid amount is < than invoice amount
- When pais amount is > then invoice total

This will allow the user to choose how to handle it, where to pass this difference as a separate move. May be it can be done automatically once we would be able to define a company / currency default write-off account.

Thanks for everything !

Regards,

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

Hello Joel,

Can you please check the more improved patch.
I have followed your suggestion and now it will take all amount as write-off.

Thanks for you support.

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

Hi vra,

After my tests, everything seems to be perfect except one last things. All computed amount are now perfectly done : Congratulations.

The only remaining issue concern the residual amount once the last full payment is done. It indicate 20.38 on the invoice (in CHF) and should be 0.0.

Otherwise, everything is alright for me : computed amount in foreign currency and in company currency, the residual amount on each step !

So, just correct the residual amount computation to display 0.0 when the invoice is completely paid and it's done !

Thanks !

[Samuel I. (Toradex) (samsimg)]

Hey OpenERP team,

may we remind you of this problem ?

It would be greatly appreciated if there would be some attention on this.

Thanks.

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

Hello All,

Can you please apply the new improved patch compare to previous patches and notify me?

Thanks.

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

Hi vra,

Thanks for coming back on this one ! I just tested it with OERPScenario and it seems that we still have the same trouble I reported on #34.

The last residual amount is stil <> 0.0. I remember you the currency exchange rate to use are described in #12.

I also remember you of the document I wrote on #30. This one will help you to test the result of your patch, to know if it's good or not.

Here is the traceback I got :

Scenario: Make payments in different currency with the pay invoice wizard
    Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 1000,0 CHF without tax called
    When I press the validate button
    Then I should see the invoice MySupplierInvoicePayWizard open
    When I call the Pay invoice wizard
    And I partially pay 200.0 CHF.- on the 10 jan 2009
    Then I should see a residual amount of 800.0 CHF.-
    When I call the Pay invoice wizard
    And I partially pay 200.0 USD.- on the 11 jan 2009
    Then I should see a residual amount of 561.48 CHF.-
    When I call the Pay invoice wizard
    And I partially pay 200.0 EUR.- on the 12 jan 2009
    Then I should see a residual amount of 232.68 CHF.-
    When I call the Pay invoice wizard
    And I completely pay the residual amount in CHF on the 13 sep 2009
    Then I should see a residual amount of 0.0 CHF.-
      expected: 0.0,
           got: 20.38 (using ==)

       Diff:
      @@ -1,2 +1,2 @@
      -0.0
      +20.38
       (Spec::Expectations::ExpectationNotMetError)
      ./features/account/wizard/step_definitions/wizard_pay_invoice.rb:84:in `/^I should see a residual amount of (.*) (\w+)\.\-$/'
      features/account/wizard/pay_invoice.feature:39:in `Then I should see a residual amount of 0.0 CHF.-'
    And I should see the invoice MySupplierInvoicePayWizard paid

Feel free to ask me more informations or remarks if you disagree with my opinion. Good luck to you !

Regards,

Joël

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

Hello Joel,

I have Implemented your complete example very smoothly with similar rates including date of currency.
You can check the attachment which contains the screen shots of invoice with payment line.

Thanks.

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

Hi vra,

I just updated my stable test version to the last of the day. I applied your patch and recreate a fresh DB. Then I tested it again, and I still get another value than you :(

I join my screen shots, as you can see, I get all the same than you, except the last "write-off" lines. You seems to get 2 lines where I get only one...

I did the test through OERPScenario and manually to be sure of my results... I really don't understand why we don't get similar values !? If you have any idea ?

I think when you make the last payment, the wizard ask you for the write-off. Here you need to choose a journal (no matters) and an account. This account should NOT be the "creditor" one (440000 main payable in your example), but something like "Change differences" or something like that (an expense account at least). I say that cause in your screen shots, you have 2 lines for the write-off entries and you should have only one as me.

I hope this helps !

Good luck to you, I know this going to be a while we're working on this bug... But we're close to close it :) !

Regards,

Joël

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

Hi Joël,

We have been working on it today, we will post some patches here sooner.

However, your suggestion of choosing a special account for write-off is quite fair.

The investigation ended to the fact that,currency difference has not been checked at few places and that can cause troubles to accountants.

Thanks.

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

Hi Joël,

There seems a little worry on writeoff lines creation (with the account_id attached to it).

However, we got to reach to the situation where we got residual=20.38 !

Kudos, fighting to finish this one.

Thanks.

[filsys (office-filsystem)]

Hi Jay,
You have some good news from Kudos? I'm very interested.
Thanks.

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

Hi Joël,

Would you please check the attached patch and suggest me whether this is the right way to remove the effect of 'Currency Profit/Loss' from the residual amount.For other cases it will behave normally as before.

There is one more bug which is solved in this patch.The bug is if you assign the write-off account equal to the invoice account then it will show you both the entries of write-off in the payment lines.

Thanks.

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

Hi vra,

This time the patch looks great and work perfectly :) !!! This is a really good news as we're waiting on it since a long time. I'm really happy with that.

As you said, it'll show you both entries in case of a write-off on the same account than the invoice account. This doesn't seems a problem to me cause it is not supposed to happen. The write-off will normally be done on a specific account for that purpose.

Quentin (qdp) was even suggesting (when I met him in BE this week) that we should include a default write-off account in the demo data, as part of the "minimal account chart".

I want to thanks all people, and mainly Jay and vra, which have worked on this issue since the beginning. This was a great community job : Well done !

Best regards,

Joël

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

Thanks for the feedback Joël.

We need to make sure it should work well with payments from bank statements too.

In addition, beahvior of difference in currency rates while reconcilation,payments,refund as well.

We are investigating all corners of this issue and will post here more for all the community members to test and respond,thus we dont miss anything,and hit the target well.

Thanks again.

[Vincent Renaville@camptocamp (vrenaville-c2c)]

Hello,

We have found an other problem in this use case (The company currency is CHF) :

You want to pay an invoice in EUR of 201 EUR.
The conversion rate in CHF is 0.7125
We use the pay wizard
We select a journal in EUR as journal in the wizard
We pay the invoice completely.
We need to have 282 CHF but we have 395 CHF (the wizard convert in currency two times :( )

The patch from VRA correct this problem, I think we are close from a global solution for the Pay Invoice Wizard.
So if some expert can test this patch in order to integrate it in the next stable.

Thanks in advance,

Vincent

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

Hello Vincent,

We have scheduled one more test for this patch.

It would be really helpful,if some experts put their views here.

Thanks for your time.

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

Hi Jay,

Once again, according to Camptocamp's tests this patch fix this issue and do not come with other regressions. This is I think a good start, but as you said, other opinion are really welcome and needed here.

Thanks to give your feed-back !

Regards,

Joël

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

Fixed with this revision:

revno: 2823
revision-id: <email address hidden>
parent: <email address hidden>
fixes bug(s): https://launchpad.net/bugs/511104
author: VRA (OpenERP)
committer: Stephane Wirtel <email address hidden>
branch nick: addons
timestamp: Wed 2010-08-25 16:16:15 +0200
message:
  [FIX] account: Pay Invoice Wizard in foreign currency

thank you for the patch

[Frederic Clementi - Camptocamp (frederic-clementi)]

I am afraid we have a regression on this again.
I have done this test on the last 5.0.14 (addons rev no 2876)

Starting from a standard DB with generic Chart of Account.
Company currency is EUR
create a currency CHF (with a 2 different rate with the FY; i.e: 01/01/10 : 0.8 and 01/07/10: 0.65)
create a cash journal in CHF
create a invoice in CHF on the 01/02/10
use pay invoice wizard and pay in full in the CHF journal on the 01/10/10.

Expected : The residual amount is 0 and status "paid"
Sad Reality : residual is not zero but status is "paid"

Please do not backport to v6 in the state.

Frederic @ campocamp

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

Hi,

I saw vra try to backport this functionality into V6.0, but it seems that it is currently broken in v5.0 !!!

Here is the concerned merge proposal :

https://code.launchpad.net/~openerp-commiter/openobject-addons/vra-openobject-addons/+merge/39499

Please solve this one before going on with the merge...

Regards,

Joël

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

Can you check this one?
https://bugs.launchpad.net/openobject-addons/+bug/666154
It has just been fixed.
Hope this clears it.
Thanks.

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

Hello Joel,

Would you please check with the latest stable version : 2877 <email address hidden> code, because in this revision some improvement has been committed.

I have successfully implement the above Latest case in my side so please check the attachment.

Thanks.

[Frederic Clementi - Camptocamp (frederic-clementi)]

Hi vra,

Unfortunatly it still does not work. I see on the screenshot payment lines that the entry generated in your exemple is wrong. You should have only one line for the write off.

In the attached document you will find my DB with the exemple of the invoice 2010/003.

Scenario (of the attched DB)
- creation of new database with service company profile / company currency EUR / use directly
- in currency USD : add on the 01-jul-2010 a new rate: 1.6
- create a USD journal linked to a new USD bank account
- create a customer invoice : Agrolait / 1 basic PC at 450€- Total invoice : 450
- Pay in full with journal in USD on the 1st Oct 2010 - Write off using account "x6000" in expenses journal
Result : invoice status is "paid" but residual amount is -72.30 usd (which is the currency write off amount in the payment entry.

(I have been using the addons rev no 2877)

do not hesitate to call me

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

Hello Frederic,

I am trying and will report the status soon.

Thanks.

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

Patch has been attached to bug 666154.
Thanks.

[Vincent Renaville@camptocamp (vrenaville-c2c)]

Hello,

We have discovered new regression in Pay invoiced wizard :

Preset :
-------

you have a company in EUR
You have a rate for USD for 1 Jan 2009 at 1.3785
You have a rate for USD for 1 feb 2009 at 1.6000
You have a rate for CHF for 1 Jan 2009 at 1.55

Use Case :
------------

    Scenario: Make payments in different rate with the pay invoice wizard

      Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 450,0 USD without tax called MySupplierInvoicePayWizard
      When I press the validate button
      Then I should see the invoice MySupplierInvoicePayWizard open

      When I call the Pay invoice wizard
      And I completely pay the residual amount in USD on the 2 feb 2009
      Then I should see a residual amount of 0.0 USD.-
      And I should see the invoice MySupplierInvoicePayWizard paid
      And I should see amount in currency equal to 0

THIS TEST FAILED BECAUSE THE AMOUNT IN CURRENCY IS EQUAL TO -72.31 USD INSTEAD OF 0.0, IT WILL CAUSE A PROBLEM IN CURRENCY AMOUNT WHEN YOU WILL PRINT THE GENERAL LEDGER IN CURRENCY

      Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 450,0 USD without tax called MySupplierInvoicePayWizard2
      When I press the validate button
      Then I should see the invoice MySupplierInvoicePayWizard2 open

      When I call the Pay invoice wizard
      And I partially pay 200.0 USD.- on the 2 feb 2009
      Then I should see a residual amount of 250 USD.-

ERROR THAT RESIDUAL AMOUNT IS EQUAL TO 277.69 INSTEAD OF 250

      When I call the Pay invoice wizard
      And I completely pay the residual amount in CHF on the 2 feb 2009
      Then I should see a residual amount of 0.0 USD.-
      And I should see the invoice MySupplierInvoicePayWizard2 paid

      Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 450,0 USD without tax called MySupplierInvoicePayWizard3
      When I press the validate button
      Then I should see the invoice MySupplierInvoicePayWizard3 open

      When I call the Pay invoice wizard
      And I partially pay 200.0 USD.- on the 2 feb 2009
      Then I should see a residual amount of 250 USD.-

      When I call the Pay invoice wizard
      And I partially pay 300.0 CHF.- on the 2 feb 2009
      Then I should see a residual amount of -59.68 USD.-

      When I call the Pay invoice wizard
      And I finally paid 30.0 USD.- on the 2 feb 2009
      Then I should see a residual amount of 0.0 USD.-
      And I should see the invoice MySupplierInvoicePayWizard3 paid

ERROR : THE RESIDUAL AMOUNT IS EQUAL TO -89.68 INSTEAD OF 0.0
ERROR : THE WRITE OFF OF THE FINAL PAYMENT GENERATED TWO LINES IN PAYMENT TAB (BOTH LINES ARE RECONCILED), IT SHOULD BE THE PARTNER LINE ONLY.

You can test this bug using openerp-scenario trunk version with the tag @511104

Thanks for your help,

Vincent

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

Hi,

I'm re-opening this bug cause the trouble is still here. Now, according to the new bug management policy, I don't know if I shoud have done that. So please advice in case I made a mistake.

Regards,

Joël

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

Joël,

If there is still a problem, we should definitely fix.
I am taking a look at it soon.

Thanks to you and Vincent.

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

Vincent,

This has already been discussed.

In your case of multiple currency payment where 1st payment of 200 USD makes residual 277.69, seems accurate and correct to me.

Let me justify :
One must pay 326.44 EUR anyhow to company.
You pay 200 USD at the rate of 1.6 USD = 1 EUR ---> Leads to the payment of 125.00 EUR.
Residual is the amount which is yet to be paid to company, so company currency plays its role here which takes to 326.44-125.00 = 201.44 EUR yet to be paid.
201.44 EUR = 277.60 USD at the rate of 1.3785. (the rate at which invoice was validated).

I think, this scenario is very much acceptable and works as expected.

I would like to ask Joël what is his opinion here.

Thanks for all your interest.

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

Please make it clear that you talk about stable or trunk.
Thanks.

[Frederic Clementi - Camptocamp (frederic-clementi)]

Jay,

We are talking stable version here.

To answer you about "residual 277.69, seems accurate to me"

The invoice is in USD (not EUR)...so, you always want to see residual at invoicing currency. So, If I the invoice is 450 USD and the 1st payment is 200 USD, you want to see 250 USD residual... no matter what is posted in the ledger at company currency (EUR)

we will launch our Gherkin on trunk as well...we'll let you know

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

I am testing for better patch.
I will post soon.
Thanks.

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

Frederic,

I am doing this biggy one. I wanted to know your view here.
what does your opinion say what date should be counted when on payment of invoice?

Thanks.

[Frederic Clementi - Camptocamp (frederic-clementi)]

Sorry Jay, I do not understand the question... In the Gherkin above (comment #58) you have the payment dates, is this what you need ?

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

Frederic,

Without any patch(in the current situation), you will get 250.00 as residual after the first pay of 200.00 USD.
But, if I pay 250 now,which will obviously not the required amount for invoice,will lead residual to -72.30.

What I mean to say here is, User interprets that he has to pay 250 more,actual amount is 277.69 USD.

Let me know your view here.
I guess, this issue is again leading us towards a bug discussion.

Thanks.

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

Big discussion,I meant.

[Frederic Clementi - Camptocamp (frederic-clementi)]

Hi Jay,

About your last comments: (in our example the invoices is still 450$)
- "Without any patch(in the current situation), you will get 250.00 as residual after the first pay of 200.00 USD" --> OK

- "if I pay 250"..."will lead residual to -72.30" --> yes, you have to treat final payment a different way than partial payments...and this is the tricky part.
As long as you are posting partial payment you do not have any write off entries to generate due to exchange rate. However, when the final payment comes you have to post the ex-rate write off due to the fact that invoice has been posted at invoice day rate and each payment at payment day rate.
I do not have the time to check again but I think that, in the residual amount calculation, the system should take all payments done and put them at invoice rate (unless you manage to take in the entry the amount in the column currency directly - except write off line). and I think, right now, the system just take the write off amount in EUR x last payment ex-rate.

It is all about exchange rate difference between invoice and payments...

[qdp (OpenERP) (qdp)]

hello everyone,

did i miss something or the simple lines that follow fix the problem??
         for inv in data_inv:
+ if inv.reconciled:
+ res[inv.id] = 0.0
+ continue

I'm proposing a patch that include also a rewrite of the residual amount computation on the debit/credit in company currency and only computed once in foreign currency, which seems much more easy and less error prone.

Note that i didn't check the other proposed patch, i just check with the latest revision of the stable addons and check out the use case exposed in joel spreadsheet (there was still one error that my refactoring solves), and the latest problem of residual amounts of paid invoices...

Kindly check that with your automatized tests battery and let us know, OERPScenario folks :-)

Quentin

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

Hello Quentin,

I'm really happy to see you on that bug : it started to drive us crazy over here !

Your patch doesn't handle that well unfortunately, depending on that exchange rate, the rounding is not good on the first partial payment, making a diff of 0,01 cts. Here is where it breaks :

      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|

      Given a cash journal in USD exists
      And a cash journal in CHF exists
      And a cash journal in EUR exists
      And on all journal entries can be canceled

    Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 1000,0 CHF without tax called
    When I press the validate button
    Then I should see the invoice MySupplierInvoicePayWizard open
    When I call the Pay invoice wizard
    And I partially pay 200.0 CHF.- on the 10 jan 2009
    Then I should see a residual amount of 800.0 CHF.-

ERROR:
      expected: 800.0,
      got: 800.01 (using ==)

What's interesting here is that if I set the expected result after first payment to 800.01, the tests pass (See #12 for complete test) !!!

Another example where it doesn't work (same company & currency settings). It gives you 2 errors here, after the first payment (always a 0,01 diff), but then, it's become worst...

    Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 450,0 USD without tax called InvoicePayWizard3
    When I press the validate button
    Then I should see the invoice MySupplierInvoicePayWizard3 open
    When I call the Pay invoice wizard
    And I partially pay 200.0 USD.- on the 2 feb 2009
    Then I should see a residual amount of 250.00 USD.-

ERROR
      expected: 250.0,
      got: 249.99 (using ==)

    When I call the Pay invoice wizard
    And I partially pay 300.0 CHF.- on the 2 feb 2009
    Then I should see a residual amount of -59.68 USD.-

ERROR
       expected: -59.68,
       got: -1.56 (using ==)

    When I call the Pay invoice wizard
    And I finally paid 30.0 USD.- on the 2 feb 2009
    Then I should see a residual amount of 0.0 USD.-
    And I should see the invoice MySupplierInvoicePayWizard3 paid

Well, I strongly suggest to reproduce our test condition in order to test those complex cases. Just set the good exchange rate at the right date. Pay attention to respect the dates in all operations.

Thank you very much for taking time on this one !!

Best regards,

Joël

[qdp (OpenERP) (qdp)]

i confirm that we sometimes get a rouding error...

for the second test case, i confirm that we get an expected residual of -1.56, but actually i think that is the correct value! I do not understand where the -59.68 comes from. i'm uploading the former spreadsheet that includes now this second test case: the expected result there is -1.55 as residual (still the rounding problem).

[qdp (OpenERP) (qdp)]

Hello Joel,

as for me there isn't any problem so far except the rounding one, i improved my latest patch in order to fix it as much as possible (can't get totally rid of it... e.g if the invoice is in CHF, the accounting in EUR and the payment in USD!): now it's not using the debit/credit columns anymore but the amount_currency one if the currency of the payment is the same as the invoice one.

Then it solves all the problem, isn't it? :)

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

Hi,

Unfortunately, the problem isn't solve at all :( The rounding seems ok, the residual amount as well during the payment process, but then, the write-off is not good, the line in the "payment" tabs of the invoice neither !

I attach a file with the test case as well as print screen of payment tabs + reconcile...

We should :

- Not have a currency in the write-off
- Have balanced entries into the reconcile
- Not show both debit+credit of write-off in the payment tabs

Thanks you in advance,

Regards,

Joël

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

Hi again,

I post once again the Gehrkin we use to test that...

Pre-requisit:

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

Test case :

      Given I have recorded on the 1 jan 2009 a supplier invoice (in_invoice) of 450,0 USD without tax called MySupplierInvoicePayWizard3
      When I press the validate button
      Then I should see the invoice MySupplierInvoicePayWizard3 open

      When I call the Pay invoice wizard
      And I partially pay 300.0 CHF.- on the 2 feb 2009
      Then I should see a residual amount of 198.45 USD.-

      When I call the Pay invoice wizard
      And I partially pay 100.0 USD.- on the 9 sept 2009
      Then I should see a residual amount of 98.45 USD.-

      When I call the Pay invoice wizard
      And I partially pay 50.0 USD.- on the 9 sept 2009
      Then I should see a residual amount of 48.45 USD.-

      When I call the Pay invoice wizard
      And I completely pay the residual amount in USD on the 12 sept 2009
      Then I should see a residual amount of 0.0 USD.-
      And I should see the invoice MySupplierInvoicePayWizard3 paid
      And the payment tabs should contain 5 lines
      And the entries into the invoice's reconciliation must be balanced

Regards,

Joël

[qdp (OpenERP) (qdp)]

hi,

i hope this is the final patch! The error wasn't related to the pay invoice wizard, but to the generic reconcile() method of account.move.line.

I'm getting here the expected results, with good number of lines in payment tab and a balanced reconciliation.

[qdp (OpenERP) (qdp)]

sorry, i just realized that i misread the test case: i tested and there is still a little bug: the reconciliation is made on the 2 lines of the writeoff move, which lead to the wrong number of payment lines and the unbalanced reconciliation.

a *last* patch is expected very soon

[qdp (OpenERP) (qdp)]

this one should be correct

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

Hi,

I post here the last scenario in .ods so you can see the computation used. I also added the Gherkin a side of the values, so you can better understand how the test is done.

Good luck,

Regards,

Joël

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

Hi Quentin,

Your last patch sent by mail work as expected !

Thanks for the hard work,

Regards,

Joël

[qdp (OpenERP) (qdp)]

here is the victorious patch!

it will be soon applied to stable addons and trunk addons as well.

Thanks for your patience

[forstera (arnaud-forster-deactivatedaccount)]

Hello, I just check the update of the addons (ver 5). The last revision is the 2889 from december the 8. Where can i find the last update of the account module ?

Thanks

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

Fixed in the last revision, sorry for inconvience !