Currency formatting support provides a flexible, consistent method to format a numeric value according to its associated currency. The currency value appears with the correct thousands separator and radix character (decimal point) of the user's country. The value appears with positive and negative indicators of the user's choice.
Displayed currency values are never rounded or truncated except when explicitly specified to be scaled. If the formatted value (which includes the thousands separator) does not fit in the output field, then the currency value without the thousands separator is used. If this value is still too large for the output field, then asterisk characters (***) are displayed in the field to alert you of an overflow condition.
You use the same methodology to add support for report regions with one currency type (for example, US dollar amounts) and for report regions with mixed currency types (for example, US dollar and Japanese yen amounts). However, when reporting on mixed currency amounts you include a special argument to indicate that you wish to align all different currency types at a standard point (usually the precision of the currency with the greatest precision). This precision is defined by a profile option or set by the user of the report at execution time.
A report based on a single currency type should display currency amounts aligned along the radix character as illustrated in the following example:
Currency Value Code -------------- ---- 120,300.00 USD -4,201.23 USD or 120,300.00 USD (4,201.23) USD or 120,300.00+ USD -4,201.23 USD
If the user chooses a negative or a positive indicator such as parentheses that appears at the right of the currency amount, then values are not flushed with the right margin but are shifted to the left to accommodate the indicator.
A mixed currency report should display currency amounts aligned along the radix character (or implied radix for currencies with no precision like JPY).
Currency Value Code -------------- ---- 300.00 USD 105.250 DNR 1,000 JPY -24,000.34 FRF
Call the FND FORMAT_CURRENCY user exit to format the Currency Value column. In this mixed currency report, the minimum precision (specified in the MINIMUM_PRECISION token in the user exit) is set to 3.
This user exit formats the currency amount dynamically depending upon the precision of the actual currency value, the standard precision, whether the value is in a mixed currency region, the user's positive and negative format profile options, and the location (country) of the site. The location of the site determines the thousands separator and radix to use when displaying currency values. An additional profile determines whether the thousands separator is displayed.
Use the Currencies window to set the standard, extended, and minimum precision of a currency.
You obtain the currency value from the database into an Oracle Reports column. Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value. A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.
See: Oracle Applications General Ledger User's Guide
FND FORMAT_CURRENCY
CODE=":column containing currency code"
DISPLAY_WIDTH="field width for display"
AMOUNT=":source column name"
DISPLAY=":display column name"
[MINIMUM_PRECISION=":P_MIN_PRECISION"]
[PRECISION="{STANDARD|EXTENDED}"]
[DISPLAY_SCALING_FACTOR="":P_SCALING_FACTOR"]
| CODE | Specify the column which contains the currency code for the amount. The type of this column is CHARACTER. |
| DISPLAY_ WIDTH | Specify the width of the field in which you display the formatted amount. |
| AMOUNT | Specify the name of the column which contains the amount retrieved from the database. This amount is of type NUMBER. |
| DISPLAY | Specify the name of the column into which you want to display the formatted values. The type of this column is CHARACTER. |
| MINIMUM_ PRECISION | Specify the precision to which to align all currencies used in this report region. You specify the MINIMUM_PRECISION token in mixed currency report regions to ensure all currency values align at the radix character for easy readability. Your user can adjust the report by setting an input parameter when submitting the report to specifically tailor the report output to a desired minimum precision or accept the default which is determined from the profile option CURRENCY:MIXED_PRECISION (Currency:Mixed Currency Precision). Your report submission must pass the value as a report argument. You use P_MIN_PRECISION as the name of this lexical. |
| PRECISION | If specified as STANDARD, then standard precision is used. If the value is EXTENDED then the extended precision of the currency is used to format the number. |
| DISPLAY_ SCALING_ FACTOR | Optionally, specify the scaling factor to be applied to the amount in that column. If this token is not specified or is negative no scaling is performed. You use P_SCALING_FACTOR as the name of this lexical parameter.
Attention: Colon ":" is placed in front of column names and lexical parameters for token values. This indicates that the value of that token is retrieved from the column or lexical parameter. If it is omitted the value within double quotes itself is used. For example, CODE=":C_CODE" indicates that currency code should be retrieved from column CODE while CODE="C_CODE" indicated that currency code is C_CODE. |
Every report with a mixed currency region should allow the user to override the default setting of the mixed currency precision profile option at submission time. Define a report argument that accepts this value.
The default value of this argument should be the profile option CURRENCY:MIXED_PRECISION (Currency:Mixed Currency Precision) so the user does not always have to set a value explicitly.
The following graphic illustrates various input values and the currency amounts displayed using the user exit (negative amounts are surrounded by parentheses) assuming the report was run in the United States.
Item Code Input Number Output Field Notes ---- ---- ----------------- --------------- ----- 01 USD 123456.76 123,456.76 02 USD 156.7 156.70 03 USD 12345 12,345.00 04 BHD 123456.764 123,456.764 05 JPY 12345676 12,345,676 06 BHD 12345.768 12,345.768 07 BHD -12345.768 (12,345.768) 08 BHD 123456.768 123,456.768 09 BHD -123456.768 (123,456.768) 10 BHD 1234567.768 1,234,567.768 11 BHD -1234567.768 (1,234,567.768) 12 BHD 12345678.768 12,345,678.768 13 BHD -12345678.768 (12345678.768) [1] 14 BHD 123456789.768 123,456,789.768 [2] 15 BHD -123456789.768 (123456789.768) 16 BHD 1234567890.768 1234567890.768 17 BHD -1234567890.768 *************** [3] 18 BHD 12345678901.768 12345678901.768 [1,2] 19 BHD -12345678901.768 *************** [3] 20 BHD 123456789012.768 *************** [3] 21 USD 123456.765 123,456.765 22 USD 123456.7654 123,456.7654 [2] 23 USD 123456.76543 123,456.76543 [2,4]
Code Name Precision USD US dollars 2 BHD Bahraini dinars 3 JPY Japanese yen 0 [1] - Thousands indicators are stripped [2] - Digit occupies space normally reserved for positive or negative indicator [3] - Value cannot fit in the field: overflow condition [4] - Radix is shifted to the left due to the precision of the number exceeding MINIMUM_PRECISION
If the precision of the input number is less than the precision of the currency then the number is padded with 0's to match the precision of the currency.
If the precision of the input number is greater than the precision of the currency then the radix of that number might get misaligned from other currency amounts in the column.
If there is one space allocated on the right for displaying the positive or negative format character (for example the profile option for displaying negative number is set to "()" or "<>") and the current number does not use that space (if the number is positive) then that space is used. If this is not sufficient, then the number is left shifted to display the full precision.
If the formatted number does not fit within the DISPLAY_WIDTH then the user exit strips off all thousands separators so as to fit the number in the allowable DISPLAY_WIDTH. The amount may again be misaligned. If it still does not fit then asterisks are printed in that field to indicate an overflow condition.
Currency values are never rounded or truncated on display by default. However, the values can be scaled by a number if explicitly specified by the DISPLAY_SCALING_FACTOR token.
The tokens MINIMUM_PRECISION=":P_MIN_PRECISION" (where the lexical argument was designated as 3) and DISPLAY_WIDTH="15" apply to all items.
Items 1 through 5 show how various currencies display in a mixed currency region with a MINIMUM_PRECISION of 3. All values align along the radix character.
Items 6 through 20 depict how positive and negative values are displayed as both amounts progressively increase in magnitude (DISPLAY_WIDTH is a constant 15). When the formatted value exceeds DISPLAY_WIDTH the thousands separators are removed as in items 13, 15, 16, and 18. When the unformatted value exceeds DISPLAY_WIDTH asterisks are displayed indicating an overflow as in items 17, 19, and 20. Notice also that the positive value shifts right into the space normally reserved for the negative indicator.
Items 21 through 23 show the effects when displaying a value which exceeds the MINIMUM_PRECISION. Since the negative indicator uses a space on the right, a positive value must exceed MINIMUM_ PRECISION+1 before it shifts to the left.