Work with Lexicals/Flexfields
Publisher supports lexical parameters for Oracle Fusion Cloud Applications. Lexical parameters enable you to create dynamic queries.
In Publisher, lexical parameters are defined as:
Lexical – PL/SQL packaged variable defined as a data model parameter.
Key Flexfield (KFF) – Lexical token in a data set query. KFF creates a "code" made up of meaningful segment values and stores a single value as a code combination id. Key Flexfields always return as a single column when used in SELECT / SEGMENT METADATA type or condition when used in WHERE clause. Key Flexfields execute at run time to extract the lexical definition and then are substituted in the SQL query.
Descriptive Flexfields (DFF) – Customizable expansion space to track additional information that is important and unique to the business. DFFs can be context sensitive, where the information stored in the application depends on the other values of the user input. Unlike Key Flexfields, Descriptive Flexfields can have multiple context-sensitive segments.
When you define any lexical, name the lexical to match the usage so that when the editor dialog pops up it will be easier to enter the default values for the SQL query. For example, if you are using a lexical in a SELECT clause, use "_select" as a suffix. The default values must be valid to get metadata.
The following example demonstrates the usage of a lexical:
When you create the data set query for the select columns, specify column alias,
SELECT gcc.CODE_COMBINATION_ID,
GCC.ATTRIBUTE_CATEGORY,
gcc.segment1 seg1,
gcc.segment2 seg2,
gcc.segment3 seg3,
gcc.segment4 seg4,
gcc.segment5 seg5,
&KFF_SELECT account
FROM GL_CODE_COMBINATIONS GCC
WHERE gcc.CHART_OF_ACCOUNTS_ID = 101
AND &KFF_WHERE
When you save the query, a pop-up dialog prompts you for the default values. To get SQL metadata at design time you must specify the default values that can form a valid SQL query. For example,
-
if the lexical usage is a SELECT clause then you could enter null
-
if the lexical usage is a WHERE clause then you could enter 1 = 1 or 1 =2
-
if the lexical usage is ORDER BY clause then you could enter 1
