Thursday, February 5, 2015

User Exits in Oracle Reports.

As the name indicates user exits are the programs written in any of the language like Java C++ Pro C to perform certain action.
User exit when called from Report triggers moves the control from report to this outside program there it performs the action programmed and returns back to report environment. 
The program which makes USER to EXIT from ongoing environment to perform certain action is USER EXIT. 
We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.
These are the user exits available in Oracle Reports that makes AOL integration.
FND SRWINIT:
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 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"]

FND FLEXSQL:

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

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. We have to create these variables.
P_CONC_REQUEST_ID Number(20) --
P_Struct_Num Number(20) --
P_Flex_Data Char(6000) (Segment1||' '||Segment2......||Segmentn)
3. The user exits are case sensitive we have to write them in capital letters only otherwise they will raise an error.

No comments: