General Methodology (User, System Administrator, and Flexfields Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

General Methodology

You use a two step method to report on flexfield values. The first step creates the appropriate SQL statement dynamically based upon the user's flexfield. The output of the first step is used as input to the second step. The second step formats this raw data for display.

Step 1 (Construction):

The first step requires you to include one or more lexical parameters (Oracle Reports variables that can be changed at runtime) in your SQL statement. You call the user exit FND FLEXSQL 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. The query then returns site- and runtime-specific flexfield information. For example, suppose you have the following query:

SELECT &LEXICAL1 alias, column 
FROM table 
WHERE &LEXICAL2

The preliminary calls to FND FLEXSQL replace values of LEXICAL1 and LEXICAL2 at execution time with the SQL fragments. For example, LEXICAL1 becomes "SEGMENT1||'\n'||SEGMENT2" and LEXICAL2 becomes "SEGMENT1 < 2" (assuming the user's flexfield is made up of two segments and the user requested that the segment value of SEGMENT1 be less than 2). The actual executed SQL query might be:

SELECT SEGMENT1||'\n'||SEGMENT2 alias, column 
FROM table 
WHERE SEGMENT1 < 2

The SQL statement for a user with a different flexfield structure might be:

SELECT SEGMENT5||'\n'||SEGMENT3||'\n'||SEGMENT8 alias, column 
FROM table 
WHERE SEGMENT3 < 2

With this step you can alter the SELECT, ORDER BY, GROUP BY, or WHERE clause. You use this step to retrieve all the concatenated flexfield segment values to use as input to the user exit FND FLEXIDVAL in step 2 (described below).

You call this user exit once for each lexical parameter you use, and you always call it at least once to get all segments. This raw flexfield information is in an internal format and should never be displayed (especially if the segment uses a "hidden ID" value set).

Step 2 (Display):

The second step requires you to call another user exit, FND FLEXIDVAL, on a "post-record" basis. You create a new formula column to contain the flexfield information and include the user exit call in this column. This user exit determines the exact information required for display and populates the column appropriately. By using the flexfield routines the user exit can access any flexfield information. Use this step for getting descriptions, prompts, or values. This step derives the flexfield information from the already selected concatenated values and populates the formula column on a row by row basis.

You call FND FLEXIDVAL once for each record of flexfield segments.

The flexfield user exits for Oracle Reports are similar to their Oracle Application Object Library (using SQL*Forms) counterparts LOADID(R) or LOADDESC and POPID(R) or POPDESC; one to construct or load the values (FLEXSQL), the other to display them (FLEXIDVAL). The token names and meanings are similar.

General Methodology

Step 1 (Construction):

Step 2 (Display):

See Also