Example Report Using FND FORMAT_CURRENCY

The following report illustrates how various currencies are formatted using the FND FORMAT_CURRENCY user exit for a report which displays mixed currency values. This document explains how you develop such a report. Information about the radix character and thousands separator are determined from the location of the user. The special display for negative and positive currency values is specified by two profile options. Hence, a report can appear differently depending upon the location of the user and the profile options values set. The following reports, one run by a user in United States and the other by a user in Germany, depict this difference. In Germany the radix character and thousand separator are switched from the US counterpart. In these reports, both Manama and Seattle had a loss and the negative numbers display in parentheses () or angle brackets <> depending upon the user's preference.

 

Sample Report Output

Report 1 Run in The United States Settings include:

• Information from the territory:

• Thousand Separator: ',' (comma)

• Radix Character: '.' (decimal)

• Profile option settings:

• Negative Format: ()

• Minimum Precision: 3

• Display Thousands Separator: Yes

Net Income for January 2008

---------------------------

Office     Net Income    Currency

------     ---------------      --------

Boston 12,345.00 USD

Chicago 123,456.76 USD

Manama (23,456.764) BHD

Isa Town 12,345,678.766 BHD

Seattle (12,345.50) USD

Tokyo 12,345,676 JPY

Report 2: Run in Germany Settings include:

• Information from the territory:

• Thousand Separator: '.' (decimal)

• Radix Character: ',' (comma)

• Profile option settings:

• Negative Format: -XXX

• Minimum Precision: 3

• Display Thousands Separator: Yes

Net Income for January 2008

---------------------------

Office    Net Income    Currency

------     ---------------      --------

Boston 12.345,00 USD

Chicago 123.456,76 USD

Manama -23.456,764 BHD

Isa Town 12.345.678,766 BHD

Seattle -12.345,50 USD

Tokyo 12.345.676 JPY

Procedure for Sample Report

1. First define all the parameters (using the Oracle Reports Parameter Screen). Use these parameters in the user exit calls and SQL statements.

Name: P_CONC_REQUEST_ID

Data Type: NUMBER

Width: 15

Initial Value: 0

You always create this lexical parameter. "FND SRWINIT" uses this parameter to retrieve information about this concurrent request.

Name: P_MIN_PRECISION

Data Type: NUMBER

Width: 2

Initial Value:

You reference this lexical parameter in your FND FORMAT_CURRENCY user exit call.

2. Call FND SRWINIT

You always call FND SRWINIT from the Before Report Trigger as follows:

SRW.USER_EXIT('FND SRWINIT');

This user exit sets up information for use by profile options and other AOL features.

You always call FND SRWEXIT from the After Report Trigger as follows:

SRW.USER_EXIT('FND SRWEXIT');

This user exit frees all the memory allocation done in other AOL exits.

3. Create the Currency Code Query

Create a query which selects the currency code and the currency amount from your table. In this case you might use:

SELECT OFFICE,

　　　　SUM(AMOUNT) C_INCOME,

　　　　CURRENCY_CODE C_CURRENCY

FROM OFFICE_INCOME

WHERE TRANSACTION_DATE = '01/92'

ORDER BY BY OFFICE

4. Create a column for the currency call.
Create one column (C_NET_INCOME) which contains the user exit (FND FORMAT_CURRENCY) call. This is a formula column which formats the number and displays it. The user exit call looks like the following:

SRW.REFERENCE(:C_CURRENCY);

SRW.REFERENCE(:C_INCOME);

SRW.USER_EXIT('FND FORMAT_CURRENCY

                               CODE=":C_CURRENCY"

                               DISPLAY_WIDTH="15"

                               AMOUNT=":C_INCOME"

                               DISPLAY=":C_NET_INCOME"

                               MINIMUM_PRECISION=":P_MIN_PRECISION"');

RETURN(:C_NET_INCOME);

Tip: Always reference any source column/parameter which is used as a source for data retrieval in the user exit. This guarantees that this column/parameter will contain the latest value and is achieved by "SRW.REFERENCE" call as shown above.

Here the column name containing currency code is "C_CURRENCY" and the field width of the formatted amount field is 15. The source column is "C_INCOME" and the resulting formatted output is placed in "C_NET_INCOME". The minimum precision of all the currencies used for this report is retrieved from the lexical P_MIN_PRECISION (which in this case is set to 3). At the end of the user exit call remember to reference the column "C_NET_INCOME" by RETURN(:C_NET_INCOME), otherwise the column may not contain the current information.

You do not include the MINIMUM_PRECISION token for single currency reports.

5. Hide the amount.

In Default layout, unselect the amount column (C_INCOME) so that it is not displayed in the report. Do not display this amount because it contains the unformatted database column value. In the layout painter update the boiler plate text for each displayed currency field (which in this case are C_CURRENCY and C_NET_INCOME)

Important: Repeat steps 4 and 5 for each displayed currency field.

6. Create the title.

In the layout painter paint the boiler plate text title as follows moving previous fields and boiler plate text as necessary:

Net Income for January 1992

---------------------------

7. Define your report with Oracle Application Object Library.

Define your report with Standard Request Submission. Ensure you define an argument P_MIN_PRECISION which defaults to $PROFILE$.MIXED_PRECISION.

The report is now ready to be run.

Summary:

A brief summary of the report specifications:

Lexical Parameters:

• P_CONC_REQUEST_ID (required)

• P_MIN_PRECISION (needed for mixed currency reports)

Column Names:

• C_CURRENCY

• C_NET_INCOME

Application Object Library User Exits:

• FND SRWINIT (required)

• FND FORMAT_CURRENCY

• FND SRWEXIT (required)