Previous	Next		Contents	Index	Navigation	Glossary	Library

Step 1 Call FND SRWINIT from your Before Report Trigger

You call the user exit FND SRWINIT from your Before Report Trigger. FND SRWINIT fetches concurrent request information and sets up profile options. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 2 Call FND SRWEXIT from your After Report Trigger

You call the user exit FND SRWEXIT from your After Report Trigger. FND SRWEXIT frees all the memory allocation done in other Oracle Applications user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 3 Call FND FLEXSQL from the Before Report Trigger

You need to pass the concatenated segment values from the underlying code combinations table to the user exit so that it can display appropriate data and derive any descriptions and values from switched value sets as needed. You get this information by calling the AOL user exit FND FLEXSQL from the Before Report Trigger. This user exit populates the lexical parameter that you specify with the appropriate column names/SQL fragment at run time. You include this lexical parameter in the SELECT clause of your report query. This enables the report itself to retrieve the concatenated flexfield segment values. You call this user exit once for each lexical to be set. You do not display this column in your report. You use this "hidden field" as input to the FND FLEXIDVAL user exit call. This user exit can also handle multi-structure flexfield reporting by generating a decode on the structure column. If your report query uses table joins, this user exit can prepend your code combination table name alias to the column names it returns.

SELECT &LEXICAL alias, column

becomes, for example,

SELECT SEGMENT1||'\n'||SEGMENT2 alias, column

Note: Oracle Reports needs the column alias to keep the name of column fixed for the lexicals in SELECT clauses. Without the alias, Oracle Reports assigns the name of the column as the initial value of the lexical and a discrepancy occurs when the value of the lexical changes at run time.

Step 4 Restrict report data based upon flexfield values

You call the user exit FND FLEXSQL with MODE="WHERE" from the Before Report Trigger. This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.

WHERE tax_flag = 'Y' and &LEXICAL < &reportinput

becomes, for example,

WHERE tax_flag = 'Y' and T1.segment3 < 200 

The same procedure can be applied for a HAVING clause.

Step 5 Order by flexfield columns

You call the user exit FND FLEXSQL with MODE="ORDER BY" from the Before Report Trigger. This user exit populates the lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the ORDER BY clause of your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.

ORDER BY column1, &LEXICAL

becomes, for example,

ORDER BY column1, segment1, segment3

Step 6 Display flexfield segment values, descriptions, and prompts

Create a Formula Column (an Oracle Reports 2.5 data construct that enables you to call a user exit). Call the user exit FND FLEXIDVAL as the Formula for this column. This user exit automatically fetches more complicated information such as descriptions and prompts so that you do not have to use complicated table joins to the flexfield tables. Then you create a new field (an Oracle Reports 2.5 construct used to format and display Columns), assign the Formula Column as its source, and add this field to your report using the screen painter. You need to include this field on the same Repeating Frame (an Oracle Reports 2.5 construct found in the screen painter that defines the frequency of data retrieved) as the rest of your data, where data could be actual report data, boilerplate, column headings, etc. The user exit is called and flexfield information retrieved at the frequency of the Repeating Frame that contains your field. In the report data case, the user exit is called and flexfield information retrieved once for every row retrieved with your query.

All flexfield segment values and descriptions are displayed left justified. Segment values are not truncated, that is, the Display Size defined in Define Key Segments screen is ignored. Segment value descriptions are truncated to the description size (if one is displayed) or the concatenated description size (for concatenated segments) defined in the form.

See Also

Overview of Flexfield Views

Oracle Reports 2.5 Flexfield Support API

General Methodology

FND FLEXSQL

FND FLEXIDVAL

Oracle Reports and Flexfields Report-Writing Steps

Flexfield Report Examples