Adding Key Flexfields
You can use key flexfield references to replace the clauses appearing after SELECT, FROM, WHERE, ORDER BY, or HAVING.
Use a flexfield reference when you want the parameter to replace multiple values at runtime. The data model editor supports the following flexfield types:
-
Where - This type of lexical is used in the WHERE section of the statement. It is used to modify the WHERE clause such that the SELECT statement can filter based on key flexfield segment data.
-
Order by - This type of lexical is used in the ORDER BY section of the statement. It returns a list of column expressions so that the resulting output can be sorted by the flex segment values.
-
Select - This type of lexical is used in the SELECT section of the statement. It is used to retrieve and process key flexfield (kff) code combination related data based on the lexical definition.
-
Filter - This type of lexical is used in the WHERE section of the statement. It is used to modify the WHERE clause such that the SELECT statement can filter based on Filter ID passed from Oracle Enterprise Scheduling Service.
-
Segment Metadata - This type of lexical is used to retrieve flexfield-related metadata. Using this lexical, you are not required to write PL/SQL code to retrieve this metadata. Instead, define a dummy SELECT statement, then use this lexical to get the metadata. This lexical should return a constant string.
After you set up the flexfield components of your data model, create a flexfield lexical reference in the SQL query using the following syntax:
&LEXICAL_TAG ALIAS_NAME
for example:
&FLEX_GL_BALANCING alias_gl_balancing
After entering the SQL query, when you click OK
- Enter the following:
-
Lexical Name - Enter a name for the flexfield component.
-
Flexfield Type - Select Key Flexfield..
-
Lexical Type - Select the type from the list. Your selection here determines the additional fields required. See Entering Flexfield Details.
-
Application Short Name - Enter the short name of the Oracle Application that owns this flexfield, for example, GL.
-
Flexfield Code - Enter the flexfield code defined for this flexfield. In Oracle E-Business Suite this code is defined in the Register Key Flexfield form, for example, GL#.
-
ID Flex Number - Enter the name of the source column or parameter that contains the flexfield structure information. For example: 101. To use a parameter, prefix the parameter name with a colon, for example, :PARAM_STRUCT_NUM.
-
Entering Flexfield Details
The Details region displays appropriate fields depending on the Lexical Type you chose.
Fields for Key Flexfield Type: Segment Metadata
The table describes the detail fields for segmented metadata.
| Field | Description |
|---|---|
|
Structure Instance Number |
Enter the name of the source column or parameter that contains the flexfield structure information. For example: 101. To use a parameter, prefix the parameter name with a colon, for example, :PARAM_STRUCT_NUM. |
|
Segments |
(Optional) Identifies for which segments this data is requested. Default value is "ALL". See Oracle E-Business Suite Developer's Guide for syntax. |
|
Show Parent Segments |
Select this box to automatically display the parent segments of dependent segments even if it is specified as not displayed in the segments attribute. |
|
Metadata Type |
Select the type of metadata to return: Above Prompt of Segments — Above prompt of segment(s). Left Prompt of Segments — Left prompt of segment(s) |
Fields for Key Flexfield Type: Select
The table below shows the detail fields for the Select flexfield type.
| Field | Description |
|---|---|
|
Enable Multiple Structure Instances |
Indicates whether this lexical supports multiple structures. Checking this box indicates all structures are potentially used for data reporting. The data engine uses <code_combination_table_alias>.<set_defining_column_name> to retrieve the structure number. |
|
Code Combination Table Alias |
Specify the table alias to prefix to the column names. Use TABLEALIAS if your SELECT joins to other flexfield tables or uses a self-join. |
|
Structure Instance Number |
Enter the name of the source column or parameter that contains the flexfield structure information. For example: 101. To use a parameter, prefix the parameter name with a colon, for example, :PARAM_STRUCT_NUM. |
|
Segments |
(Optional) Identifies for which segments this data is requested. Default value is "ALL". See Oracle E-Business Suite Developer's Guide for syntax. |
|
Show Parent Segments |
Select this box to automatically display the parent segments of dependent segments even if it is specified as not displayed in the segments attribute. |
|
Output Type |
Select from the following:
|
Fields for Key Flexfield Type: Where
The table below shows the detail fields for the Where key flexfield type.
| Field | Description |
|---|---|
|
Code Combination Table Alias |
Specify the table alias to prefix to the column names. You use TABLEALIAS if your SELECT joins to other flexfield tables or uses a self-join. |
|
Structure Instance Number |
Enter the name of the source column or parameter that contains the flexfield structure information. For example: 101. To use a parameter, prefix the parameter name with a colon, for example, :PARAM_STRUCT_NUM. |
|
Segments |
(Optional) Identifies for which segments this data is requested. Default value is "ALL". See Oracle E-Business Suite Developer's Guide for syntax. |
|
Operator |
Select the appropriate operator. |
|
Operand1 |
Enter the value to use on the right side of the conditional operator. |
|
Operand2 |
(Optional) High value for the BETWEEN operator. |
Fields for Key Flexfield Type: Order By
The table below shows the detail fields for the Order by flexfield type.
| Field | Description |
|---|---|
|
Enable Multiple Structure Instances |
Indicates whether this lexical supports multiple structures. Selecting this box indicates all structures are potentially used for data reporting. The data engine uses <code_combination_table_alias>.<set_defining_column_name> to retrieve the structure number. |
|
Structure Instance Number |
Enter the name of the source column or parameter that contains the flexfield structure information. For example: 101. To use a parameter, prefix the parameter name with a colon, for example, :PARAM_STRUCT_NUM. |
|
Code Combination Table Alias |
Specify the table alias to prefix to the column names. You use TABLEALIAS if your SELECT joins to other flexfield tables or uses a self-join. |
|
Segments |
(Optional) Identifies for which segments this data is requested. Default value is "ALL". See Oracle E-Business Suite Developer's Guide for syntax. |
|
Show Parent Segments |
Select this box to automatically display the parent segments of dependent segments even if it is specified as not displayed in the segments attribute. |
Fields for Key Flexfield Type: Filter
The table below shows the detail fields for the Filter flexfield type.
| Field | Description |
|---|---|
|
Code Combination Table Alias |
Specify the table alias to prefix to the column names. You use TABLEALIAS if your SELECT joins to other flexfield tables or uses a self-join. |
|
Structure Instance Number |
Enter the name of the source column or parameter that contains the flexfield structure information. For example: 101. To use a parameter, prefix the parameter name with a colon, for example, :PARAM_STRUCT_NUM. |