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:

  1. Use the ST/DT keywords to allow explicit specification of the currency using the three-character ISO 4217 Currency Code.
  2. 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.

Note: If any required data (for example, currency types, rates) are missing, conversion errors will result at run time.

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.

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)



Feedback