Foreign Currency in Custom Tables

[KevinCourtney]

Hello there,

I’ve created a custom table containing a field based on the alias ACR (Amount – Currency)

In the F03B11 Customer Ledger, it has a field based on the same ACR alias that holds the amount that represents the foreign currency.

My UBE reads through the F03B11 and inserts records into my table. In debug I can see the F03B11.ACR has a value of 72205 (no decimals) and moves this value into my worktable’s.ACR in the insert.

When I go to UTB I see the F03B11.ACR has the 72205 with no decimals but my worktable has its ACR = 722.05.

In UTB, properties of both ACRs says their display decimals is 2.

In creating custom tables, how do I tell JDE that a foreign currency amount field should have its values to be based on a certain Currency Code in the file?

Have any of you come across this and/or have any ideas?

Thanks in advance,

 

[sam]

Hi Kevin,

Your worktable is displaying ACR correctly formatted. If I display my F03B11 the ACR is displayed with 2 decimals in formatted mode. Perhaps you should check that the file field definition on the AS400 is correct, and matches your OW specs.

The second part of your question requires that you create additional columns, as JDE does, which holds the designated currency for the amount - use a CRCD alias to hold this value. JDE also use a currency mode CRRM alias which indicates domestic or foreign - you can decide not to use this if you want. The other column that JDE use is the currency conversion CRR.

Regards
Sean

 

[KevinCourtney]

Hi Sean,

Thanks for your input.

I’m under the impression that my worktable is showing it incorrectly and the F03B11 is showing it correctly without the decimals because the CRCD is ‘JPY’ which looks to not have decimals in its formatting.

In the F03B11 record that I’m looking at, it has BCRC=USD, CRCD=JPY and the ACR is displayed with no decimals.

If I look at another record where both BCRC=USD and CRCD=USD, then ACR is displayed with two decimals.

Somehow JDE knows to look at the CRCD’s value (and not the BCRC’s) and apply it’s formatting to the ACR.

I have both BCRC & CRCD in my worktable but they’re after the ACR field. At this point I’m guessing that for JDE to apply the foreign currency formatting, that it looks for a currency code field before the ACR field. But that’s a long shot… I’ll give it a try later today.

Anyone have any ideas on how JDE knows which currency code to use in a file for formatting the foreign currency fields?

Thanks,

 

[wojcik]

Hi Kevin

When you created your file, did you add the same table triggers that exist for the amount fields in F03B11? Without these your currencies will not be stored correctly.

1. I suggest that you compare and update the values in your work file with exactly the same values from F03B11. Use DFU or SQL on the AS/400 to do this. Make sure that you have at least two records, one with a JPY amount and another with a USD amount.

2. Use UTB to display these records in your work file and also the same records in F03B11. If they are not shown correctly, then you will need to correct your currency triggers in your work file.

The domestic amounts should be formatted using the base currency for the company in F03B11 while the foreign amounts are from the CRCD field in F03B11.

3. Go into Start Table Trigger Design Aid and look at the Currency Conversion Is On trigger for F03B11. Add the same trigger to your work file.

4. Go back to step 2 and if you have added the triggers correctly your amounts should now be shown correctly in UTB. Don't confuse these JDE triggers with triggers on the AS/400.

 

[KevinCourtney]

Hi Rob,

Table triggers, hadn't thought of them coming into play here. That makes a lot of sense.

Thanks for the suggestions and I'll give them a try.

 

[KevinCourtney]

Hi,

Well Rob was right on in solving this problem. All I had to do was go into the table trigger event rules for my custom table, in the ‘Currency Conversion is On’ event and add a call to ‘Decimals Trigger Get by CO CRCD’ (B1100007) business function.

Works great now.

JDEList comes through again…

Thanks!

 

[wojcik]

Thanks for the feedback