Adding Flexfields

This chapter covers the following topics:

About Flexfields
Adding Flexfields

About Flexfields

Flexfields are unique to Oracle Applications. If you are reporting on data from the Oracle Applications, use this component of the data model to retrieve flexfield data.

To use a flexfield in your data model:

Define the SELECT statement to use for the report data.
Within the SELECT statement, define each flexfield as a lexical. Use the &LEXICAL_TAG to embed flexfield related lexicals into the SELECT statement.
Add the flexfield to the data model.

You can use 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 - Use this type of lexical 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 reference in your SQL query using the following syntax:

&LEXICAL_TAG ALIAS_NAME

for example:

&FLEX_GL_BALANCING alias_gl_balancing

Adding Flexfields

Enter the following:

Name - enter a name for the flexfield component.
Type - select the flexfield type from the list. The type you select here will determine 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).
ID Flex Code - enter the flexfield code defined for this flexfield 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.

Entering Flexfield Details

Select Segment Metadata, Select, Where, Order By, Filter. Depending on the type you select, the detail pane will display the appropriate fields.

Detail Fields for Segment Metadata
Field	Description
Segments	(Optional) Identifies for which segments this data is requested. Default value is "ALL". See the 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 - above prompt of segment(s). Left Prompt - left prompt of segment(s)

The following table shows the detail fields for the Select flexfield type:

Detail Fields for Select
Field	Description
Multiple ID Flex Num	Indicates whether this lexical supports multiple structures or not. Checking this box indicates all structures are potentially used for data reporting and it will use <code_combination_table_alias>.<set_def ining_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.
Segments	(Optional) Identifies for which segments this data is requested. Default value is "ALL". See the 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: Value - segment value as it is displayed to user. Padded Value - padded segment value as it is displayed to user. Number type values are padded from the left. String type values are padded on the right. Description - segment value's description up to the description size defined in the segment definition. Full Description - Segment value's description (full size). Security - Returns Y if the current combination is secured against the current user, N otherwise.

The following table shows the detail fields for the Where flexfield type:

Detail Fields for Where
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. .
Segments	(Optional) Identifies for which segments this data is requested. Default value is "ALL". See the Oracle E-Business Suite Developer's Guide for syntax.
Operator	Choose 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.

The following table shows the detail fields for the Order by flexfield type:

Detail Fields for Order By
Field	Description
Multiple ID Flex Num	Indicates whether this lexical supports multiple structures or not. Checking this box indicates all structures are potentially used for data reporting and it will use <code_combination_table_alias>.<set_def ining_column_name> to retrieve the structure number.
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 the 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.

The following table shows the detail fields for the Filter flexfield type:

Detail Fields for Filter
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.
Flex Filter ID	(Required) Enter the unique Key internal code of the key flexfield.
Flex Filter Comment	(Optional) Enter a comments or description.