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. |
| 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:
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.
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)