Skip Headers
Oracle® Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher
11g Release 1 (11.1.1)

Part Number E22258-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Adding Flexfields

This chapter describes support for flexfields in the BI Publisher data model.

This chapter includes the following sections:

6.1 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:

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:

After you set up the flexfield components of your data model, create a flexfield 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

6.2 Adding Flexfields

To add a flexfield:

  1. Enter the following:

    • Name — Enter a name for the flexfield component.

    • Type — Select the flexfield type from the list. The type you select here determines the additional fields required. See Section 6.2.1, "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. For example: 101. To use a parameter, prefix the parameter name with a colon, for example, :PARAM_STRUCT_NUM.

6.2.1 Entering Flexfield Details

Select Segment Metadata, Select, Where, Order By, Filter. Depending on the type you select, the detail pane displays the appropriate fields, described in Table 6-1.

Table 6-1 Detail Fields for Segment Metadata

Field Description

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 — Above prompt of segment(s).

Left Prompt — Left prompt of segment(s)


Table 6-2 shows the detail fields for the Select flexfield type.

Table 6-2 Detail Fields for Select

Field Description

Multiple ID Flex Num

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_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 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.


Table 6-3 shows the detail fields for the Where flexfield type.

Table 6-3 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 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.


Table 6-4 shows the detail fields for the Order by flexfield type.

Table 6-4 Detail Fields for Order By

Field Description

Multiple ID Flex Num

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.

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.


Table 6-5 shows the detail fields for the Filter flexfield type.

Table 6-5 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.