User Exits in Oracle Report

User Exits in Oracle Report

User Exits are 3GL programs used to transfer the control from the report builder to Oracle Applications or
any and where it will perform desired actions and return to the report builder.

It is an API, which provides a way to pass control from Reports Builder to a 3GL program.

Using these we can integrate Oracle reports with Oracle apps AOL, and run them as concurrent programs.
When the concurrent manager runs an Oracle Report concurrent request, it passes the concurrent request id
in the P_CONC_REQUEST_ID parameter. When  FND SRWINIT user exit is called in the report's
 Before Report trigger, the user exit uses the passed request id to query the Oracle Applications context
 that the concurrent request was submitted in (via the FND_CONCURRENT_REQUESTS table) and then
log on with that context.If  we try calling the FND SRWINIT user exit in  report without the 
P_CONC_REQUEST_ID parameter defined as a user parameter on  report, the user exit will fail.

This allows to use operating specific views, access profile option values, etc within the report. 

Following are the user exits available in Oracle Reports that makes the AOL integration:

FND SRWINIT

FND SRWEXIT

FND FORMAT_CURRENCY

FND FLEXSQL

FND FLEXIDVAL

FND SRWINIT: 

It is used to fetch the concurrent request information and also to set the profile options.

It sets the profile option values and allows Oracle AOL user exits to detect that they have been called by
 a Oracle Reports program.

When "FND SRWINIT" user exit is called  in the report's Before Report trigger, the user exit uses the
passed request id to query the Oracle Applications context that the concurrent request was submitted
in (via the FND_CONCURRENT_REQUESTS table) and then log on with that context.

function BeforeReport return boolean is

begin

  SRW.user_exit('FND SRWINIT');

  return (TRUE);

end;

FND SRWEXIT:

It is used to free the memory which has been allocated by the other user exits.

FND SRWEXIT ensures that all the memory allocated for Oracle AOL user exits has been freed up properly.
 It is called in After Report trigger.

function AfterReport return boolean is

begin

  srw.user_exit( 'FND SRWEXIT' );

    RETURN (TRUE);

  --return (TRUE);

end;

FND FORMAT_CURRENCY:

It is used for MULTIPLE CURRNECY REPORTING (MRC).

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.

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"]

FND FLEXSQL:

This fragment allows to SELECT flexfield values or to create a WHERE, ORDER BY, GROUP BY, or
HAVING clause to limit or sort the flexfield values returned by the SELECT statement.This user exit is called
once for each fragment you need for your select statement. All flexfield columns are defined in your report as
type CHARACTER even though the table may use NUMBER or DATE or some other datatype.
This user exit FND FLEXSQL is called  with different arguments to specify that part of the query you would like
to build.The user exit retrieves the appropriate column names (SQL fragment) and inserts it into the lexical
parameter at runtime before the SQL query is executed.

Syntax:

FND FLEXSQL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
OUTPUT=":output lexical parameter name"
MODE="{ SELECT | WHERE | HAVING | ORDER BY}"
[DISPLAY="{ALL | flexfield qualifier | segment
    number}"]
[SHOWDEPSEG="{Y | N}"]
[NUM=":structure defining lexical" |
    MULTINUM="{Y | N}"]
[TABLEALIAS="code combination table alias"]
[OPERATOR="{ = | < | > | <= | >= | != | "||" |
    BETWEEN | QBE}"]
[OPERAND1=":input parameter or value"]
[OPERAND2=":input parameter or value"]

FND FLEXIDVAL:

This API is used for get descriptions from flexfields, gets input from FND FLEXSQL.

This user exit is called to populate fields for display. The key flexfields data retrieved by the query is passed
 into this exit from the formula column. With this exit values, descriptions and prompts can be displayed by
 passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).

Syntax:

FND FLEXIDVAL

CODE="flexfield code"

APPL_SHORT_NAME="application short name"

DATA=":source column name"

[NUM=":structure defining source column/lexical"]

[DISPLAY="{ALL|flexfield qualifier|segment number}"]

[IDISPLAY="{ALL|flexfield qualifier|segment

    number}"]

[SHOWDEPSEG="{Y | N}"]

[VALUE=":output column name"]

[DESCRIPTION=":output column name"]

[APROMPT=":output column name"]

[LPROMPT=":output column name"]

[PADDED_VALUE=":output column name"]

[SECURITY=":column name"]

Note:

1. We use the User Exits in the Report triggers like before report, after report, before parameter form,
after parameter form.

2. We have to create a mandatory parameter called P_CONC_REQUEST_ID when we work with FND SRWINIT,
 FND SRWEXIT.

3. The user exits are case sensitive we have to write them in capital letters only otherwise they will raise an error.