Solved

External Supplier Invoice import: non-USD currency

S
Rank
Userlevel 6
Badge +12

Hey all,

We have successfully been using an external file template to import Manual Supplier Invoices (with authorized postings) for over a year. But for the first time we are needing to create an invoice that uses EUR instead of USD, and so obviously employs a currency conversion rate that is not 1.0000.

I am able to make the invoice create with EUR as the currency, but I was still forcing a conversion rate of 1.0000. So, the invoice is created, but is obviously incorrect and has to be canceled and completely re-created.

My next try was to leave currency rate null (blank in the text file), thinking IFS would go get the rate the same way it would when entering an invoice in IEE. This resulted in a file load error:

 

Net Amount does not match Net Amount in Accounting Currency for Item No 1.

 

I cannot get much detail on just what exactly IFS is trying to do or what is wrong. And I do not know what values to convert so that IFS thinks things balance, because it seems to do that automatically when an invoice is entered in IEE (thus I expected it to “just work” in an external file import).

Finally, I tried leaving both the currency and currency rate as null in the text file, because our suppliers are properly set up with the currency that should be used (these are invoices for expense reports being paid out). But when I leave both currency and currency rate empty I get a warning:

 

Warning - Currency was missing in the transaction so it was set to the supplier's default currency.

 

and despite the promising sound of that -- invoices are not created.

So, I’m stuck. I looked at the External File Parameters, and there does not appear to be a way to ignore warnings (and I am not sure I would want to). As far as the rate to use, there is a parameter called Use Currency Rate Valid for Specified Voucher Date, and it is set to FALSE, so would setting that to TRUE tell IFS to figure it out on its own? I am not eager to start messing with external parameters because that means generating another scheduled task and changing my import to use that (I call it on demand), so I was hoping someone could tell me that is the right tree to bark up, first.

Surely IFS can import non-USD invoices, and we certainly need to figure it out at some point. If anyone has tips on this or can point to some documentation, let me know! All ideas much appreciated!

 

ADDITIONAL NOTE: I realized I could just try an Online process via the assistant, and try setting that Use Currency Rate parameter to TRUE and leave rate empty in the file. Gave it a shot, and still get the “Net Amount does not match…” error.

 

Thanks,

Joe Kaufman

icon

Best answer by Ieva Rituma 14 June 2022, 12:39

View original
Just because something is part of a plan doesn't make it right.

7 replies

S
Rank
Userlevel 6
Badge +12

Figure I’d give this one bump before letting it fall into the bit bucket… Has anyone gotten non-USD currencies to work in external supplier invoice file templates, or at least something involving a currency conversion rate not equal to 1.000? Just knowing it is possible would be extremely helpful.

Anyone? Is this something you think IFS support could assist with?

 

Thanks,

Joe Kaufman

Just because something is part of a plan doesn't make it right.
Ieva Rituma
Rank
Userlevel 6
Badge +16

I’ve successfully imported invoices in non-domestic currency before. We set the currency rate in the file, as far as I remember and set the setting Use Currency Rate Valid for Specified Voucher Date to FALSE. What did you have as the currency rate in the import file when you first tried this?

IRituma
S
Rank
Userlevel 6
Badge +12

@IRituma 

 

Thanks for the response!

I have tried several things for the conversion rate.

I have tried not sending the rate in at all, and that errors out.

I have tried a rate of 1.000, and that works, but the values are incorrect because they have been saved as USD.

And I have tried the conversion rate for USD to EUR, whatever was saved for the year: 0.8696.

All scenarios were run as a one time load, with the Use Currency Rate Valid for Specified Voucher Date tried with both TRUE and FALSE.

Using the non-1.000 rate results in the error “Net Amount does not match Net Amount in Accounting Currency for Item No 1.”

You have gotten it to work by changing the currency and the conversion rate, without changing any other dollar amounts? I am not sure what I am doing differently, if that is the case. Everything should balance, the values should just be converted to EUR.

 

Thanks,

Joe Kaufman

Just because something is part of a plan doesn't make it right.
Ieva Rituma
Rank
Userlevel 6
Badge +16

Do you mind sharing a screenshot of the values you’re using, because there are 2 places to apply the currency rate and show the currency amount (posting amount and the invoice amount)?

 

And once this the record is imported into IFS, it will be stuck in External Supplier Invoices with an error, but you can still view what the system has created. Worth checking the amounts there. You can RMB and see the “Invoice Line Information” and from there the “Invoice Posting Information” and “Invoice Tax Information”. Worth checking whether the values there have been applied to all places correctly, and if any are missing.

 

The last thing I’d check is the External Supplier Invoice template we’re using. Is it STDSIN1 or STDSIN2? Worth looking at the template in IFS to check all the columns are there for us in the Excel file, and maybe any amount columns that are defined there are missing from the template you’re using. I’d export the total template details tab into Excel, transpose and compare whether any columns are missing.

IRituma
S
Rank
Userlevel 6
Badge +12

@IRituma 

We cloned a template from STDSIN1. I am really not at all good with External Files, so I am not sure about many things you are saying here. I didn’t want to have to use an external file in the first place, but the creation of Manual Supplier Invoices was the one thing I could not get working via manual manipulation of the Oracle database (and PL/SQL package API functions).

I am not sure what a screenshot would show…  You mean for the parameters? Because the file is just a comma-delimited text file. I only see Currency Conversion Rate as part of the “H” (header) record, but you could be right that I left out a field when I cloned STDSIN1, and the rate is also supposed to be on the “I” line and/or “P” lines? I suppose I can look into this when I get back around to it, but have been placed on about 10 other priority migrations and integrations since then.

 

Thanks,

Joe Kaufman

Just because something is part of a plan doesn't make it right.
Ieva Rituma
Rank
Userlevel 6
Badge +16

Hi, This sounds like maybe you would benefit from raising an IFS support case so they can look at your actual data and find out the cause easier.

 

The screenshot I meant is of the text delimited file or from the data this file gets generated from. Here is an example of one I’ve tested an STDSIN1 file import with:

 

I’m also attaching a template example that you could use to compare with your template and maybe that helps.

There is no field for the rate in the I and P lines, but there are columns for the currency amounts and for the domestic currency amounts, which can only be calculated by applying the rate. The total I and P amounts per each amount column should equal the amounts on the H line, per each invoice ID.

1 Attachment
IRituma
S
Rank
Userlevel 6
Badge +12

@IRituma 

Thanks again! I am going to call this the answer, because I am pretty sure it must be that I am not applying the rate to the I and P lines to keep everything in balance. Now that I know it works, when I am asked to revisit this I will have finance staff get an external file working then will model my process off that. At least I now it can be done, and we can pursue whatever support avenues we need to get it working.

Thanks for taking the time to walk through all this!

 

Thanks,

Joe Kaufman

Just because something is part of a plan doesn't make it right.

Reply


Terms & ConditionsCookie settings