Bug - Foreign currency vendor invoice paid in foreign currency may remain open even when settled in foreign currency

[Ashley-Butcher]

Bug

When creating a vendor invoice in a foreign currency, and settling that invoice in that foreign currency, the invoice may remain "unpaid" even then the amount in foreign currency is settled in the foreign currency (currency of the document).

This occurs because Dolibarr only evaluates the value of the document in the company currency, and not the document currency. For example, the invoice may be fully paid (so remainder in foreign currency is 0.00), but the invoice may remain open for 0.01 in company currency.

In most ERP systems, this the foreign currency is referred to as "document currency" and it's this document currency that should be evaluated when determining if the document is settled (paid) or not.

Environment Version

18.0.1 (also older versions)

Environment OS

Debian 12.1

Environment Web server

Apache 2.4.57

Environment PHP

7.4.33

Environment Database

MySQL 8.0.33

Environment URL(s)

No response

Expected and actual behavior

Actual: Dolibarr tests for settlement of the invoice (i.e. if the invoice has been paid) based on the company currency values. If a document is in foreign currency, it relies on currency conversion, which may be inadequate for this role.

Expected: Dolibarr should test for settlement (payment completion) on document currency values (the currency of the invoice) instead of the company currency.

Steps to reproduce the behavior

Example 1:

Company currency = EUR Document currency = SEK

Prerequisites:

• You have the multicurrency module enabled

• You have a bank account in SEK

  1. Create a new vendor invoice in SEK (Swedish Kroner)
  2. Specify the exchange rate to be 11.681 (i.e. 11.681 SEK = 1 EUR)
  3. Specify a free text line (service or product) with no tax and a value of 92.59 in the field U.P (net) (currency) Note: You will need to save the line, edit the line, and specify the value a second time, otherwise the line will have a zero value due to bug #25897.
  4. Your invoice total should be 7.92 EUR / 92.59 kr
  5. Make a payment for the total amount (92.59) using your SEK bank account
  6. Observe: Your remaining unpaid is now SEK 0.00 but the invoice remains unpaid due to a payment remainder in EUR.
  7. Close the invoice manually - you may be prompted for why you want to close the invoice before it has been completely paid.

Example 2:

Company currency = GBP Document currency = EUR

• You have the multicurrency module enabled

• You have a bank account in EUR

  1. Create a new vendor invoice in EUR (Euros)
  2. Specify the exchange rate to be 1.16686 (i.e. 1.16686 EUR = 1 GBP)
  3. Specify a free text line (service or product) with no tax and a value of 222.57 in the field U.P (net) (currency) Note: You will need to save the line, edit the line, and specify the value a second time, otherwise the line will have a zero value due to bug #25897.
  4. Your invoice total should be 190.75 GBP / 222.57 EUR
  5. Make a payment for the total amount (222.57) using your EUR bank account
  6. Observe: Your remaining unpaid is now EUR 0.00 but the invoice remains unpaid due to a payment remainder in GBP.
  7. Close the invoice manually - you may be prompted for why you want to close the invoice before it has been completely paid.

Attached files

No response

[Ashley-Butcher]

This is a part of a larger problem Dolibarr has in multicurrency environments, but given the high number of open issues on github I'm only posting some issues here and won't get into the larger issues yet.

[Ashley-Butcher]

Another example. Dolibarr makes the mistake of working in company currency instead of document currency, resulting in these errors. ERP systems normally work in the currency of the document to avoid these errors, because this is the true value of the document (the value in company currency is the realised value).

When settling the total amount in document currency (in this case, SEK), the document cannot even be closed because of an apparent rounding error in company (system) currency (EUR):

The document should be settled and the open amount should be nil. This is a ridiculous situation easily overcome by always working in document currency.

[Ashley-Butcher]

Another example from a user, settled entirely in SEK, but this time it can be marked as closed, further highlighting the rounding discrepancies created by not working entirely in document currency.

[leninrivas]

It seems to be an accuracy issue. If increase total MAIN_MAX_DECIMALS_TOT from 2 to 3?

• Remember update each line after update limits.

[Ashley-Butcher]

@leninrivas No the problem is related to how Dolibarr uses document currency in general. As with any ERP system (SAP, Dynamics AX, JD Edwards, Sage, ...) a document (invoice/sales order/etc.) needs to be treated in its actual currency and not the company currency. Dolibarr only ever considers the system currency - this is wrong.

When dealing with a document in a currency other than the company's currency, it's normal for there to be rounding errors, especially when dealing with currencies with wide rate difference ratios (such as SEK to EUR). Dolibarr should first consider the document in the document's currency (i.e. SEK) and see if the balance in that currency is zero when considering if the document is closed (settled). Then, the amount in the company currency (i.e. EUR) can be checked - if there is a cent or two, it can offset into a ledger account as a penny difference on currency exchange (or ignored, in the case of accounting being switched off).

Normally the rounding rules are followed based on the rules of the currency with a few exceptions (pricing, for example). This is how you can settle a document in its original currency but it's also acceptable to have penny differences in the company currency. This happens all the time, and it isn't an accuracy issue, but instead Dolibarr is simply looking at the wrong amounts.

Dolibarr should instead be only treating the documents in their source currency, and the value of the document in the company currency should be facultative.

These rules are very standard in business, and I would assume that it's a legacy issue from the days before Dolibarr supported multicurrency.

This of course leads me to further problems with Dolibarr's multicurrency implementation, such exchange rate adjustments on documents, and a future need for unrealised exchange adjustments (on open documents, i.e. at the end of the month, for valuation and reporting purposes) and realised adjustments (at settlement, for example), but this is a whole other issue.

Unfortunately I don't have the free time to spend on developing a fix for Dolibarr :(