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:
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.
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:
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. |
To convert from Finnish Markkas to Euro Dollars, format the Currency Function as:
If the original value must be preserved, use the Currency Function to provide a value for a different column in the destination table.
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:
The examples above 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: