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).
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.
Overview of Flexfield Views
Oracle Reports and Flexfields Report-Writing Steps
Flexfield Report Examples