Apply Latest Exchange Rates

Find general Jiwa support here.

Apply Latest Exchange Rates

Postby AERP_TA » Fri Jun 21, 2019 3:12 pm

We are in the process of upgrading one of our clients from 6.5.13 to 7.2.5 and they have asked a question as follows: -

"in Jiwa 6 there is an option on the Record menu of "Apply Latest Exchange Rates" - where is this in Jiwa 7? (I use it for FX payments where there are dozens of transactions to update the rate to the actual payment rate) "

I can't seem to find this in Jiwa 7 at all - the closest I came is finding an unresolved bug under DEV-4850.

Can you shed any light on this please?
AERP_TA
I'm new here
I'm new here
 
Posts: 4
Joined: Sat Mar 01, 2008 5:21 pm

Re: Apply Latest Exchange Rates

Postby Mike.Sheen » Fri Jun 21, 2019 4:08 pm

Yes, we removed it back in 7.0.175 or thereabouts to address rounding / reconciliation issues and arrived at the conclusion that you don't make FX payments by entering in FX rates to work out the local currency amount.

In fact, we made the field read-only for a few builds until we opened it back up for edit again.

You need to enter both the FX Amount and the local Amount and we impute the rate. We do let you enter the rate, but doing so just calculates the local amount from the FX Amount - but the only way to avoid reconciliation issues is to enter the 2 amounts instead of the rate.

When you're paying an FX invoice via that form, you know the amount in FX you are paying because that's what you told your bank / payment facility to pay in FX currency - and they'll tell you what that worked out to be in local currency - in fact you should use whatever appears on your bank statement / transaction record. The rate is imputed and we use that to work out any FX gains or losses.

Make sense?

If it's important for the customer, it should be a super easy plugin to add back that function.

EDIT: I found the original issue calling for the removal of the rate - DEV-5582 which has a comment linking to a docs article which apparently explains things further.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Apply Latest Exchange Rates

Postby pricerc » Fri Jul 05, 2019 8:16 am

So we've caught up with the client about this.

So what he's doing is this (roughly):

1) he sets up a payment batch. Could be 3 or 4 currencies and hundreds of invoices. They get coded at the 'current' FX rate for each currency.
2) he sends the details off to an FX broker who gets him a better-than-the-bank exchange rates, and pays the creditors in their currency of choice.
3) the broker sends back a report, which includes the *actual* exchange rate used for the batch, and a bill for the local value of the batch at the time of the transaction.
4) he (currently) goes back to the FX rate screen, changes the rates, and then in the payment batch, re-applies the rates, before reverting the FX rates to his 'reporting' rate in the currency screen.

So what he really wants, is not an "Apply Latest Exchange Rates" button, but a "Change exchange rates for this batch" button; which would allow him to specify a custom rate for each currency in the batch, and to apply the special rate to just that batch.

Entering individual local amounts for hundreds of invoices isn't his idea of a good time!

At this stage, I'm thinking that I will add a little exchange rate grid to the form header in the payment batch screen, where the rates can be changed as long as the batch isn't activated.

As an aside, as I was writing this up, I was thinking that having multiple currencies in the same batch adds a level of complication to this process that would be simplified if the batch could be created with a single currency specified as a filter option for the "Find Invoices" button. This would also help if you were wanting to pay from a foreign currency account. So here's a suggestion: add a currency option to the EFT payment batch "Find Invoices" filter.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Apply Latest Exchange Rates

Postby pricerc » Tue Jul 09, 2019 4:51 pm

So I've written a little plugin which puts the affected exchange rates in a dictionary and a dialog which allows them to be changed.

It's working well, but it seems a little slow (and by 'a little', I mean 'very' :) ).

I have this loop:
Code: Select all
                For Each creditor As JiwaCreditorChqPay.Creditor In paymentBatch.Creditors
                    For Each pl As clsPayLineInvoice In creditor.InvoicePaymentLines
                        pl.CurrencyRateUsedOnBatch = currencies(creditor.Creditor.Currency.RecID).ExchangeRate
                    Next
                Next


which took a minute to process 142 lines.

is there something I can be doing to speed it up? like pausing some refresh logic until it's finished?
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Apply Latest Exchange Rates

Postby pricerc » Tue Mar 30, 2021 8:03 pm

I need to resurrect this thread.

Now that the customer is live, he's starting to notice the sluggish performance of this bit of code.

I added a progress bar, but that just lets him know it hasn't died.

Any suggestions?
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Apply Latest Exchange Rates

Postby Scott.Pearce » Thu Apr 01, 2021 8:53 am

Attach the plugin and I'll take a look.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Apply Latest Exchange Rates

Postby pricerc » Thu Apr 01, 2021 10:05 am

Scott.Pearce wrote:Attach the plugin and I'll take a look.


Ok, a bit tricky, because there are actually several plugins. I've amalgamated the code into the attached single VB file.

The performance problem is entirely within the loop I mentioned earlier:

Code: Select all
                For Each creditor As Creditor In paymentBatch.Creditors
                    If Not currencies.ContainsKey(creditor.Creditor.Currency.RecID) Then
                        Me.ProgressDialog.Value += creditor.InvoicePaymentLines.Count
                        Continue For
                    End If

                    Dim exchangeRate As Decimal = currencies(creditor.Creditor.Currency.RecID).ExchangeRate

                    For Each pl As clsPayLineInvoice In creditor.InvoicePaymentLines
                        pl.CurrencyRateUsedOnBatch = exchangeRate
                        Me.ProgressDialog.Value += 1

                        Application.DoEvents()
                        If Me.ProgressDialog.DialogResult = DialogResult.Cancel Then
                            ' user has pressed the 'Cancel' button.
                            Return
                        End If
                    Next
                Next


Everything else is just setup.

It seems to me that
Code: Select all
pl.CurrencyRateUsedOnBatch = exchangeRate
does a whole lot of calculating.

I was mostly wondering if I can suspend the line-by-line calculations before the loop, and then do a bulk calculation afterwards. I have had success doing this in other places, but it requires a fair bit of digging around in decompiled code, and I'm on another time-sensitive project at the moment, so don't have the time.
Attachments
AERP Plugin - EFT UI.txt
(26.25 KiB) Downloaded 192 times
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Apply Latest Exchange Rates

Postby Mike.Sheen » Thu Apr 01, 2021 10:55 am

Can you export it to XML for us instead of just the code in a txt file?

I know that means a bit more effort on your end to make it a stand-alone plugin which reproduces the issue - but it makes it easier to help you and it eliminates the possibility of us trying to troubleshoot the wrong thing.

Thanks
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Apply Latest Exchange Rates

Postby pricerc » Thu Apr 01, 2021 11:10 am

Here you go.
Attachments
Plugin AERP - EFT UI.xml
(48.44 KiB) Downloaded 170 times
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Apply Latest Exchange Rates

Postby pricerc » Thu Apr 01, 2021 11:12 am

BTW, the customer often has over 250 payments in a batch.

Which is why performance matters.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Next

Return to Core Product Support

Who is online

Users browsing this forum: Google [Bot] and 3 guests