Currency Function
Use the Currency Function to convert a currency value in a source column from one currency to another. The source column must be defined as numeric, but not floating point. Two conversion methods are available:
- Direct Conversion
- Provide
conversion parameters based on values defined in a Currency Definition.
Use the Currency Function to convert a monetary value in a column
(replacing the prior value) or, by defining different source and destination
columns, retain the original value and the converted value. You can
explicitly define the source and destination currency types or you
can identify a reference column to indicate the currency type.
The first calculation preference is to use the conversion rate for the source currency to the destination currency. The second calculation conversion preference is to use the conversion rate for the destination currency to the source.
- Triangulation
- Convert the value in a column from the source currency to the euro dollar and then convert the euro dollar to the destination currency. Both rates must be provided in the rate table: one between the euro dollar and the source currency, and one between the euro dollar and the destination currency. The specification expression is TRIANG or TR.
The Currency Function is formatted as follows:
CURRENCY( {ST=code | SS=(column-name,Types Table number)}
{DT=code | DS=(column-name,Types Table number)}
[SC=column-name] [TR] [CU=Currency Definition name]
[TD=transaction-date-column-name] [DF='format']
[NS=scale] )
The Currency Function must include at least a combination of the source currency type (ST) or source specification (SS) and the destination currency type (DT) or destination specification (DS). All other parameters are optional.
The source and destination currency types can be specified in one of two ways:
- Use the ST/DT keywords to allow explicit specification of the currency using the three-character ISO 4217 Currency Code. The code is searched for on the Rates tab of the Currency Definition (see Rates Tab).
- Use the SS/DS keywords to allow indirect specification of the currency type where a value in a named column in the row is used as a key. The key is correlated with a currency type as defined in the specified Currency Definition Type Table.
If you specify a transaction date (TD) and the transaction date column does not use the DATE format, you must also specify a date format (DF). If a specified transaction date is outside the date ranges specified in the Currency Definition Rates Table, the nearest date range is used for conversion calculations.
The following table describes the valid format and allowed values for the Currency Function parameters. Parameters can be specified in any order. Use commas or spaces to separate parameters in the Currency Function.
Parameter | Format |
---|---|
Source Column | SC=column-name SRCCOL=column name |
Source Currency Type | ST=code SRCTYP=code where code = ISO 4217 Currency Code |
Destination Currency Type | DT=code DSTTYP=code where code = ISO 4217 Currency Code |
Source Specification | SS=column name, Types
Table number SRCSPC=column name, Types Table number where column name, Types Table number = Types Table column and number (defined in the Currency Definition) to be used to specify the source currency type |
Destination Specification | DS=column name, Types
Table number DSTSPC=column name, Types Table number where column name, Types Table number = Types Table column and number (defined in the Currency Definition) to be used to specify the destination currency type |
Triangulation (Forces conversion via the Euro dollar) | TR TRIANG |
Currency Definition | CU=Currency Definition
name CURTBL=Currency Definition name where Currency Definition name = Currency Definition that contains the appropriate conversion parameters. (Overrides the Currency Definition specified in the Insert request. See Using the Currency Editor.) |
Transaction Date | TD=column name TRNDAT=column name where column name = Transaction Date column name to identify the conversion date |
Date Format | DF='format' DATFMT='format' where format = format of transaction date column, if not Date type. |
Numeric Scale | NS=scale NUMSCL=scale where scale = scale to be applied to Oracle numeric destination columns with an undefined scale. |
Example 1
To convert from Finnish Markkas to Euro Dollars, format the Currency Function as:
CURRENCY(ST=FIM DT=EUR)
If the original value must be preserved, use the Currency Function to provide a value for a different column in the destination table.
Example 2
To convert from Finnish Markkas to Euro Dollars, and create a new column to retain the original source value (in Finnish Markkas) in a column labeled ITEM_COST, format the Currency Function as:
CURRENCY(ST=FIM
DT=EUR SC=ITEM_COST)
Example 3
The preceding examples assume a simple conversion from a known and fixed source currency to a target currency. Consider a more complex scenario where a column in a table has a numeric monetary value, and a separate column in the table has a key to indicate the type of currency. The monetary value in one row could be Euro Dollars and in another row it could be Finnish Markkas.
To support this scenario, the Types tab of the Currency Definition is used to create a Types Table. (See Types Tab.) The Types Table defines the key and the corresponding currency type for the key. The Currency Function must include a source specification that identifies the column that contains the key, and the number of the Types Table that defines the key, as shown:
CURRENCY(SC=ITEM_COST SS=(ITEM_BASIS,1)
DT=EUR)
- SC=ITEM_COST
- The source value is in the column ITEM_COST.
- SS=(ITEM_BASIS,1)
- The key for the type of currency is in the column ITEM_BASIS, and Types Table 1 in the Currency Definition is used to correlate the key to the currency type.
- DT=EUR
- The result of the calculation displays as euro dollars.