Oracle Reports

LOGON_DATE Your user’s logon date for the current session.
LAST_LOGON_DATE Your user’s logon date for the previous session.
LOGIN_ID Your user’s Sign–On Audit login ID in Oracle Application Object Library.
CONC_REQUEST_ID The request ID associated with a particular instance of your running current program. You

can only use this profile option in a concurrent program.

CONC_PROGRAM_ID The program ID associated with a running current program.

User Profile APIs (FND_PROFILE)

User profile APIs you can use in your PL/SQL procedures. You can use these user profile routines to

manipulate the option values stored in client and server user profile caches.

FND_PROFILE.PUT

procedure FND_PROFILE.PUT
name IN varchar2,
value IN varchar2);

Arguments
name - The (developer) name of the profile option you want to set.
Value - The value to set in the specified profile option.

Description

Puts a value to the specified user profile option. If the option does not exist, you can also create it with

PUT.

FND_PROFILE.GET

procedure FND_PROFILE.GET

(name IN varchar2,

value OUT varchar2);

Example -F N D _ P R O F I L E . G E T ( ’ U S E R _ I D ’, u s e r _ i d ) ;

Description

Gets the current value of the specified user profile option, or NULL if the profile does not exist.

FND_PROFILE.VALUE

function FND_PROFILE.VALUE

(name IN varchar2) return varchar2;

Description

VALUE works exactly like GET, except it returns the value of the specified profile option as a function
result.

FND SRWINIT and FND SRWEXIT

FND SRWINIT and FND SRWEXIT are used to access profile values, multiple organizations, or Oracle Applications user exits, and for your program to be used with concurrent processing. The first and last user exits called by your Oracle Reports program be FND SRWINIT and FND SRWEXIT.

FND SRWINIT sets your profile option values and allows Oracle Application Object Library user exits to

detect that they have been called by a Oracle Reports program.

FND SRWEXIT ensures that all the memory allocated for Oracle Application Object Library user exits

has been freed up properly.

•

Create a lexical parameter P_CONC_REQUEST_ID with the datatype Number. The concurrent

manager passes the concurrent request ID to your report using this parameter.

•

Call FND SRWINIT in the ”Before Report Trigger.”

Call FND SRWEXIT in the ”After Report Trigger.”

The following AOL USER EXITS help us to access user profile values and perform calculations in Oracle

Reports program:

1. FND FORMAT_CURRENCY
2. FND FLEXSQL
3. FND FLEXIDVAL

You can call many Oracle Applications PL/SQL routines, such as user profiles routines, from your Oracle Reports programs as well as these user exits. In general, you should use PL/SQL routines instead of user exits where possible.

You can test your Oracle Reports program that calls Oracle Applications user exits by running

ar25run - for chararcter mode reports

ar25runb - for bitmap mode reports. Ex., HTML,PDF FILES

ar25des- This is the bitmap designer to build and modify reports which use Oracle

Applications user exits.

User Exits Used in Oracle Reports

•

FND SRWINIT

•

FND SRWEXIT

•

FND FORMAT_CURRENCY

•

FND FLEXIDVAL

•

FND FLEXSQL

FND SRWINIT / FND SRWEXIT

FND SRWINIT sets your profile option values and allows Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program. FND SRWINIT also allows your report to use the correct organization automatically. FND SRWEXIT ensures that all the memory allocated for Oracle Application Object Library user exits has been freed up properly.

FND FLEXIDVAL / FND FLEXSQL

These user exits allow you to use flexfields in your reports. They are documented in theOracle

Applications Flexfields Guide.

Using Dynamic Currency in Oracle Reports

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. 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.0

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.

FND FORMAT_CURRENCY User Exit

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.

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.

Syntax

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.

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.

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 separators 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

•

The territory information can be find in preferences menu in oracle application home page.

Set the territory location as United States and required format to display the currency.

Information from the territory:

– Thousand Separator: ’,’ (comma)

– Radix Character: ’.’ (decimal)

Profile option settings:

– Negative Format: <>

– Minimum Precision: 3

Display Thousands Separator: Yes