SRW.USER_EXIT in Oracle Reports

1) The program which makes USER to EXIT from ongoing environment to perform certain action is called USER EXIT.

2) User exits are the programs written in any of the language like Java C++ Pro C to perform certain action.

3) 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.

4)User Exit plays very important role in Oracle apps with of help this we can capture value at runtime with profile option.

5) P_conc_request_id is the mandatory parameter. It is used to generate the concurrent request id.

SRW Package:

SRW(Sql Report Writer). SRW is a package name used while creating a report using rdf. it helps to perform action at runtime.

SRW package have the following procedures

SRW.ADD_DEFINITION
SRW.APPLY_DEFINTION
SRW.BREAK
SRW.CONTEXT_FAILURE
SRW.DO_SQL
SRW.DO_SQL_FAILURE
SRW.GETTERR_RUN
SRW.GET_PAGE_NUM
SRW.INTEGER_ERROR
SRW.MAXROW_INERR
SRW.MAXROW_UNSET
SRW.MESSAGE
SRW.NULL_ARGUMENTS

SRW.PROGRAM_ABORT

SRW.REFERENCE
SRW.RUN_REPORT
SRW.RUN_REPORT_BATCHNO
SRW.RUN_REPORT_FAILURE
SRW.SETattributes
SRW.TRACE_ADD_OPTION
SRW.TRACE_END
SRW.TRACE_REM_OPTION
SRW.TRACE_START
SRW.TRACEOPTS.MASK
SRW.TRUNCATED_VALUE
SRW.UNKNOWN_QUERY
SRW.UNKNOWN_USER_EXIT

SRW.USER_EXIT
SRW.USER_EXIT20
SRW.USER_EXIT_FAILURE

Types:

User Exists are 5 Types.

1. SRW.USER_EXIT('FND SRWINIT')
2. SRW.USER_EXIT('FND SRWEXIT’)
3. SRW.USER_EXIT('FND FORMAT_CURRENCY ‘)
4. SRW.USER_EXIT('FND FLEXIDVAL’)
5. SRW.USER_EXIT('FND FLEXSQL’)

1) SRW.USER_EXIT('FND SRWINIT')

        The SRW.USER_EXIT('FND SRWINIT') is used in AOL report and is used to setup your user profile. If you are in a muilti_org environment, this ensures that your report display data from the user's organization and this is set in Before Report Trigger(R11i) (OR)  after parameter form trigger(R12i).. 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.

2) SRW.USER_EXIT('FND SRWEXIT’)

You call SRW.USER_EXIT in after report trigger to make sure that all the memory allocated for AOL is freed up.

3) SRW.USER_EXIT('FND FORMAT_CURRENCY ‘)

REFERENCE:  http://www.scribd.com/doc/4586413/SRW-FORMAT-CURRENCY

Used to Format the Currency Value column.Is used for MULTIPLE CURRNECY REPORTING (MRC) [formula Column P_MIN_PRECISION lexical parameter]

Example :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”

DESCRIPTION:

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.

4) SRW.USER_EXIT('FND FLEXIDVAL’)

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

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

CODE means Key flexfield code(GL# is for Accounting Flex field, for all other check the table FND_ID_FLEXS)
NUM is the structure of the key flex field(Chart of Accounts Number)
DATA is where you store the retrieved data (your sql output).

5) SRW.USER_EXIT('FND FLEXSQL’)

        This API is used for get ELECT/WHERE/HAVING/ORDER BY/GROUP BY from flex field  tables.

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

Example

SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
OUTPUT=":P_SEC_SEG"
MODE="SELECT"
DISPLAY=":P_SEC_SEG_VAL" ')

The userexit call FND FLEXIDVAL :

SRW.REFERENCE(:SEC_SEG);
SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#"
DATA=":SEC_SEG"
APPL_SHORT_NAME="SQLGL"
VALUE=":SEC_SEG_DISP"
DISPLAY="ALL"
NUM=":P_STRUCT_NUM" ');
return(:sec_seg_disp);