currency rate by document or by transaction


We discussed that on the iDempiere Chateau Chapelle Workshop 2019.

We like to set a currency rate per document because the rate may change during days or for other reasons. We like a field to set a "manual" currency rate calculation in a document header.

There is a patch from TrekGlobal that uses a spot currency rate type (e.g. "manual") that allows to enter a currency rate for every single document. Carlos likes to review the TrekGlobal implementation.




Carlos Ruiz
October 30, 2020, 11:41 AM

Thanks .

If I understand correctly - in the PDF attached you found cases that needs to be fixed in core about using the wrong function?

If yes, I'll reopen the ticket, as those seems like missing pieces.


Carlos Ruiz

Luis Amesty
October 31, 2020, 11:25 AM

Hi Carlos.
YES, Reopen the Ticket.
Just as an example (
Two Invoices 172094, 172095
172094=  Total: 411,75 USD = 188.136.810,00 VEB, Conversion=456920
172095= Total: 411,75 USD = 164.700.000,00 VEB, Conversion 400000
Both Shows on Invoice Info Windows same 188.136.810,00 VEB
See Attached Windows
Regards, Luis Amesty

Carlos Ruiz
November 10, 2020, 2:43 PM

Thanks , finally I found a bit of time to review your document.

Very detailed and well done!!! Thank you very much.

My comments about the document:
+1 - MBPartner - would be good to change the occurrences of currencybase to the new function

Functions invoicepaid, invoicepaidtodate, invoiceopen, invoiceopentodate, invoiceopentodatewithTS, paymentallocated, paymentavailable
+1 - Affects Aging reporting - would be good to change these

These info windows must be reviewed too:
Invoice Info
Payment Info

Function currencybase
not needed / GridTab - deprecated not used method getTrxInfo
not needed / InfoInvoicePanel, InfoOrderPanel, InfoPaymentPanel - deprecated classes not used anymore

Function currencyrate
not needed / there is no document in the parameters, so it's a function intended for generic conversions, not related to invoices/payments

, are you planning to work on these functions?

Luis Amesty
November 10, 2020, 3:51 PM

Hi Carlos

  1. On Functions:
    Functions invoicepaid, invoicepaidtodate, invoiceopen, invoiceopentodate, invoiceopentodatewithTS, paymentallocated, paymentavailable.
    I am working with them on PostgreSQL, because I used then on many Jasper reports. I would add invoiceopen and invoiceopentodate to the list.
    I don’t have oracle test dev, but I could give you mi comments about them or final code script when I finish testing.

  2. Function currencyrate:
    This function is used by currencyconvert function, which in turn is used by currencyconvertinvoice and currencyconvertpayment. But as you say ”intended for generic conversions, not related to invoices/payments
    NOT need to be considered.

  3. All the Functions mentioned on point 1.
    They all includes a Allocation amount calculation loop for v_PaidAmt. Like:

            v_Temp := ar.Amount + ar.DisCountAmt + ar.WriteOffAmt;

    v_PaidAmt := v_PaidAmt

            -- Allocation

    + currencyConvert(v_Temp * v_MultiplierAP,

    ar.C_Currency_ID, v_Currency_ID, ar.DateTrx, null, ar.AD_Client_ID, ar.AD_Org_ID);

          RAISE NOTICE '   PaidAmt=% , Allocation= % * %', v_PaidAmt, v_Temp, v_MultiplierAP;

    The function currencyconvert is called with null parameter on C_ConversionType_ID. So a generic and any CurrencyType value for CurrencyRate is returned.
    This is due, because C_AllocationHdr table doesn't have C_ConversionType_ID field. But in spite of, a right value must be taken from Invoice or payment allocated with, in order to calculate v_PaidAmt value correctly.
    WDYT ?

  4. Finally. I could contribute on Java Classes too. But let me Un Lock all my Jasper reports affected by this feature, First.

    Luis Amesty

Carlos Ruiz
November 10, 2020, 4:52 PM

Thanks .

I also found a problem in the bank statement, create button and is ready for peer review in pull request 372


Carlos Ruiz


Thomas Bayen


Tested By


Fix versions