Updating FX Exchange Rates

Epicor ERP 10
1

Hi

How do I update FX exchange rates in Epicor 10 please? Do I go to Exchange Rate Entry and set the effective date as the month end date?

Thanks

Susie

2

Yes and No. Right place, but the effective date can be any date depending on how you want to use it. We have some custom code that gets the new rates every day so our effective date is every day. You could do ‘end of month’, just saying that it doesn’t have to be the end of the month.

3

Thanks Mike, we only adjust exchange rates monthly as dictated by Group. On this basis I’m thinking if I insert a rate with our month end date it’ll only apply it to those transactions throughout the month and generate a month gain or loss?

4

Susie - I’m not sure I follow. The exchange rates are used at the transaction level, moving forward in time. Your post made it sound like you want it to work backwards.

If you set a new exchange rate up with an effective date of Jan 1, 2020 - then all M/C sales moving forward would use that rate. And any re-evaluation report you run would recalc the ‘open’ items accordingly.

But if you enter a new rate today with an effective date of 12/1/2019 - I’m not sure it’ll have ANY effect.

1 Like
5

We enter rates every day. We only have to rates to worry about, so it is manual.

Vinay Kamboj

6

Can you share your custom code that gets the rates every day? We want to do that as well.

7

We use a subscription service, and it’s really just a single web API call to them, and parse the XML returned to do the insert into erp.CurrExRate table. The solution would be custom to the API of the service you are using to get the rates. Ours does a little more ‘special’ stuff and is not something I can directly share in whole.

but the general flow is like this:

Get the Currencies in Epicor

foreach(var Currency_iterator in (from Currency_Row in Db.Currency
							  where Currency_Row.Company == Session.CompanyID
							  select Currency_Row))
{
	Currency = Currency_iterator;
	currencyCodes += "quote="+ Currency.CurrencyCode + "&";
	currencyCodesAgent += Currency.CurrencyCode + "~";
}

currencyCodesAgent = currencyCodesAgent.TrimEnd('~');

Then set up the API URL (we stored it as a UD field in the Company table)

Company = (from Company_Row in Db.Company
								where Company_Row.Company1 == Session.CompanyID
								select Company_Row).FirstOrDefault();
if (Company != null)
{
	url = Company.Character01;
	tokenID = Company.Character02;
}

Then call it and get the JSON

url += "USD.xml?"+ currencyCodes + "date=" + ((DateTime)DateTime.Now).ToString("yyyy-MM-dd") + "&fields=all";		
var request = (HttpWebRequest)WebRequest.Create(url);
string json = "";
string credentialHeader = String.Format("Bearer " + tokenID);
request.Method = "GET";
request.ContentType = "application/xml";
request.Headers.Add("Authorization", credentialHeader);
	
HttpWebResponse webresponse = (HttpWebResponse)request.GetResponse();
	
var sw = new StreamReader(webresponse.GetResponseStream(), System.Text.Encoding.ASCII);
json = sw.ReadToEnd();
sw.Close();
		
XmlDocument doc = new XmlDocument();
doc.LoadXml(json);
	
XmlNodeList nodeList;
nodeList=doc.SelectNodes("//quotes/quote");

Then you just spin through the nodes and do what you want with the data

foreach (XmlNode node in nodeList)
	{
	string currency = node.SelectSingleNode( "currency" ).InnerText; 
	decimal ask = Convert.ToDecimal(node.SelectSingleNode( "ask" ).InnerText); 
	decimal bid = Convert.ToDecimal(node.SelectSingleNode( "bid" ).InnerText);
        // and write the value to the table or something
	}

In the end, you may also need to call the Currency Re-Evaluation process too…

Also, our currencies are setup as Global, with Intercompany Direct process taking care of distributing the new rates to all of our companies.

Hope that helps enough to get you going.

3 Likes