Skip Headers
Oracle® Fusion Applications Developer's Guide
11g Release 1 (11.1.3)

Part Number E15524-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

25 Using Key Flexfields

This chapter discusses how to use key flexfields in Oracle Fusion applications to access data that is represented by different organizations using different combinations of fields, and to customize the presentation of that information to end users in a way that is most appropriate for their organizations. This chapter also discusses the development activities for taking advantage of key flexfield partial usages, code combination filters, and other advanced features.

This chapter includes the following sections:

25.1 Introduction to Key Flexfields

A key flexfield is an intelligent key comprised of segments, in which one or more segments may have a meaning. An intelligent key, or code, uniquely identifies an object such as an account, an asset, a part, or a job, that implementors can configure to validate any way they wish. The definition of a key flexfield provides a list of possible combinations of key flexfield segment values, known as code combinations. Each type of code combination is called a structure. Each structure is identified by a string that you provide called the structure code. Much like the context values in descriptive flexfields, a key flexfield structure code indicates how database columns are organized to store the code combinations.

25.1.1 The Benefits of Key Flexfields

Key flexfields provide a way for Oracle Fusion applications to represent objects such as accounting codes, part numbers, or job descriptions, which combine multiple fields (or segments) into a single object of concatenated segments.

Most businesses use codes made up of meaningful segments (intelligent keys) to identify various business objects. For example, a company might have a part number "PAD-NR-YEL-8 1/2x14" indicating a notepad, narrow-ruled, yellow, and 14" by 8 1/2". A key flexfield lets you provide your end users with a flexible code data structure that implementors can set up however they like using key flexfield segments. Key flexfields let your implementors customize your application to show their codes any way they want them. For example, a different company might have a different code for the same notepad, such as "8x14-PD-Y-NR", and they can easily customize your application to meet that different need. Key flexfields let you satisfy different customers without having to reprogram your application.

You can use key flexfields in many applications. For example, you could use a Part flexfield in an inventory application to uniquely identify parts. Your Part flexfield could contain such segments as product class, product code, size, color and packaging code. You could define valid values for the color segment, for example, to range from 01 to 10, where 01 means red, 02 means blue, and so on. You could even specify cross validation rules to describe valid combinations of segment values. For example, products with a specific product code may be available only in certain colors.

25.1.2 How Key Flexfields are Modeled in Oracle Application Development Framework

Flexfields are modeled as a collection of Oracle Application Development Framework (Oracle ADF) polymorphic view rows, as described in the "Working with Polymorphic View Rows" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework. In a polymorphic collection, each view row can have its own set of attributes, and all rows have at least one common attribute, the discriminator. The discriminator determines which view row type should be used. Given a collection of polymorphic view rows, each row can be a different type.

The attribute sets that are associated with the discriminator are predefined. In fact, Oracle ADF enables each view row to have its own view definition. When a polymorphic collection of rows is created, Oracle ADF selects a view definition for the row to be added based on the value of the discriminator attribute.

Key flexfield segments are exposed as view row attributes in the order that they are defined in the flexfield's metadata. The code combination ID (CCID) and structure instance number (SIN) segments are exposed as attributes in the base view object of the polymorphic collection. Every structure is modeled as an extended view object of the base view object. That is, an extended view object is created for every structure instance number. These extended view objects, which are referred to as subtype view objects, expose the key flexfield segments as subtype-specific view attributes. The structure instance number is exposed as the discriminator attribute of the polymorphic view rows.

You use a wizard to generate a polymorphic base view object that is based on the key flexfield definition, then create a view link to connect the product view object and the base view object. You can then use the base view object to add the flexfield to a user interface page. For more information about the generation of base and subtype view objects, see Section 25.2.4, "How to Create Key Flexfield Business Components."

Because flexfield view objects are modeled as polymorphic view objects, you can use key flexfield view objects in the same manner that you use any other polymorphic view objects, and they will behave in the same way. This includes support for flexfields in ADF Desktop Integration. For more information, see the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework.

25.1.3 Partial Usage Feature

A key flexfield configuration can be shared with other product tables through the partial usage feature. To share a configuration with another product table, you reuse the key flexfield definition over the product table by including one or all of the master usage segment columns in the product table. The table that contains the redefined segment columns is referred to as a partial table. The master usage is sometimes referred to as reference mode and partial usage is referred to as partial mode.

There are two types of partial usages:

  • All-segment partial usage: In this mode, the product table has columns for all of the key flexfield segments.

  • Single-segment partial usage: In this mode, the product table has only one key flexfield segment column.

25.1.4 Participant Roles

As mentioned Section 22.2, "Participant Roles," this document uses the owner and implementor roles to clarify and group flexfield development activities. This chapter breaks the owner role into two categories:

Producer

The key flexfield producer is the developer who determines that a particular flexfield is needed or would be useful within a particular application, and makes available a flexfield of the appropriate design. The producer's product owns the combinations table for that flexfield.

Consumer

A key flexfield consumer incorporates a flexfield into their application, which is typically different from the producer's application. The consumer typically stores the CCID on a transaction table, and works with the structural and seed data and the business components that have been configured by the key flexfield producer.

25.1.5 Completing the Key Flexfield Development Process

Before you start to incorporate key flexfields into your application, you need to determine whether you should complete the producer portion or the consumer portion of the key flexfield development process.

To incorporate key flexfield partial usages into your application, see Section 25.5, "Completing the Development Tasks for Key Flexfields in Partial Mode".

To employ key flexfield code combination filters in your application, see Section 25.6, "Working with Combination Filters for Key Flexfields".

Figure 25-1 provides an overview of producer and consumer roles as they apply to the creation and configuration of the necessary key flexfield business components and associated artifacts. Section 25.1.5.3, "Understanding the Key Flexfield Producer Development Tasks" and Section 25.1.5.4, "Understanding the Key Flexfield Consumer Development Tasks" define the producer phases and summarize the steps for creating the components and artifacts shown in this figure. Note that the maintenance application module should be registered in the flexfield metadata if the key flexfield is configured for dynamic combination insertion.

Figure 25-1 Key Flexfield Development Roles, Business Components and Supporting Artifacts

Roles, Business Components and Supporting Artifacts

Oracle Fusion Middleware Extensions for Applications provides key flexfield information such as combinations table structure. You can use this information to integrate your custom applications with key flexfields that are delivered with Oracle Fusion applications. For example, you can build foreign key pages that call an Oracle Fusion application's key flexfields.

25.1.5.1 Maintenance Mode and Dynamic Combination Insertion

By default, key flexfield user interface elements do not allow new code combination values entered into the application user interface to be saved. However, you might want to enable the entry of new code combinations in either of the following ways:

  • A code combination maintenance page enables application implementors and administrators to manage key flexfield code combinations, including the ability to enter new code combinations and update existing code combinations for a flexfield. This is called working in maintenance mode.

  • You can enable end users to enter values on an application page that constitute ad-hoc new code combinations, even if the users are not authorized to perform maintenance tasks directly. This is known as dynamic combination insertion.

    For example, when entering a transaction, a General Ledger user can enter a new expense account code combination for an account that does not yet exist. Your application creates the new account by inserting the new combination into the combinations table behind the scenes.

The key flexfield producer builds the appropriate models to support maintenance mode and dynamic combination insertion.

25.1.5.2 Cross Validation Rules and Custom Validation

When you decide to support maintenance mode or dynamic combination insertion for a key flexfield, you can also implement advanced validation capability for the new code combinations that are entered, as follows:

Cross Validation Rules

Cross validation rules leverage the code combination filter infrastructure to apply a pair of filters to new code combinations that are proposed for a key flexfield by administrators or end users.

At registration time, you need to enable the key flexfield for cross validation. Then you create a maintenance user interface that administrators of your application can subsequently use to define each cross validation rule as a pair of code combination filters: one to establish the condition for evaluating the rule, and the other to specify which code combinations are valid under that condition.

Custom Validation Callouts

There are two PL/SQL custom validation callout procedures that can be defined for a given key flexfield: one for application development use, and one reserved for customers. These callouts can be used to enforce any custom validation logic that you want to apply to new code combinations beyond what has been defined for cross validation rules.

You define custom validation logic with a standard signature for the customer callout. You then register your callout with the key flexfield. The custom validation callout will automatically be called before any new combination is inserted using dynamic insert in C and PL/SQL.

25.1.5.3 Understanding the Key Flexfield Producer Development Tasks

If you have determined that a particular key flexfield is needed within an application, and there are not yet columns in the product table to support it, you need to define the necessary metadata and provide the appropriate business components so that flexfield consumers can make use of your flexfield.

To complete the producer development tasks:

  1. Develop the key flexfield.

    See Section 25.2.1, "How to Develop Key Flexfields".

    Optionally, you can also do the following at registration time:

  2. Create and configure the key flexfield business components.

    As shown in Figure 25-1, the producer activities occur in two phases. The first phase produces a writable maintenance model, and the second phase produces a read-only reference model:

    • In producer phase 1, you create an updatable entity object over your combinations table and a master view object based on the updatable entity object. Next you create maintenance model key flexfield business components over the updatable entity object, and define a view link between your code combination master view object and the key flexfield view objects. Then you create the maintenance application module.

    • In producer phase 2, you optionally configure the maintenance application module to accept dynamic combination insertion calls, and implement the appropriate Java class in the user interface to invoke dynamic insertion. You create a read-only reference entity object over your combinations table, and create reference model key flexfield business components over the read-only entity object.

    See Section 25.2.4, "How to Create Key Flexfield Business Components".

    Tip:

    After completing this task, you can regenerate the flexfield business components programmatically at runtime to update your key flexfield implementation without manual intervention. For more information, see Section 26.4, "Regenerating Flexfield Business Components Programmatically".

  3. Optionally, share your key flexfield business components with other developers using an ADF library.

    For more information, see Section 25.2.5, "How to Share Key Flexfield Business Components".

  4. Optionally, build a key flexfield maintenance user interface.

    For more information, see Section 25.2.6, "How to Build a Key Flexfield Maintenance User Interface".

  5. Optionally, implement key flexfield advanced features such as code combination constraints or API access to segment labels, or access flexfields from a worksheet using ADF Desktop Integration.

    For more information, see Section 25.4, "Using Key Flexfield Advanced Features in Reference Mode".

  6. Optionally, define, implement, and invoke key flexfield code combination filters.

    For more information, see Section 25.6, "Working with Combination Filters for Key Flexfields".

25.1.5.4 Understanding the Key Flexfield Consumer Development Tasks

You can incorporate a producer's flexfields in your own application. For example, you might have an expenses table that references an account key flexfield in the General Ledger application.

If there are already columns in your product table to support the key flexfield that you want to implement, and the flexfield producer who owns that metadata has provided you with the appropriate business components, you can proceed to incorporate the flexfield into your application. You should have already created a foreign key entity object and view object for your application.

To complete the consumer development tasks:

  1. As shown in Figure 25-1, create a view link between your application's foreign key view object and the reference model key flexfield polymorphic view objects.

    See Section 25.3.1, "How to Create Key Flexfield View Links."

  2. Nest the key flexfield application module instance in the product application module.

    See Section 25.3.2, "How to Nest an Instance of the Key Flexfield Application Module in the Product Application Module."

  3. Add a key flexfield view object instance to the product application module.

    See Section 25.3.3, "How to Add an Instance of a Key Flexfield View Object to the Product Application Module."

  4. Add your key flexfield to an application page.

    See Section 25.3.4, "How to Employ Key Flexfield UI Components on a Page."

  5. Configure the key flexfield user interface components.

    See Section 25.3.5, "How to Configure Key Flexfield UI Components."

After completing these tasks, you can define seed or test value sets for the flexfield, and you can create a model that you can use to test it. For more information, see Section 26.1.2, "How to Test Flexfields."

After you have completed the key flexfield development process and delivered your application, implementors can use the Manage Key Flexfields task flow to define and configure the structures, structure instances, segments and segment instances for each key flexfield. This will determine how the flexfield's segments will be populated, organized, and made available to end users within the application.

To make the Manage Key Flexfields task flow available to application implementors and administrators, you register it with Oracle Fusion Functional Setup Manager. For more information, see Section 26.5, "Integrating Flexfield Task Flows into Oracle Fusion Functional Setup Manager."

25.2 Completing the Producer Tasks for Key Flexfields

To prepare key flexfields for modeling in Oracle JDeveloper, you must ensure that columns for the flexfields you require are defined in your application database. You also might need to define more advanced features such as key flexfield partial usages, code combination filters, or the enabling of cross validation rules and custom validation callouts. All of these features require you to modify your application database.

Note:

To incorporate a key flexfield partial usage into your application, you must have already defined and registered the key flexfield master usage on which it is based. See Section 25.2.1.5, "Registering and Defining Key Flexfields Using the Setup APIs", then continue to Section 25.5, "Completing the Development Tasks for Key Flexfields in Partial Mode".

To employ key flexfield code combination filters in your application, see Section 25.6, "Working with Combination Filters for Key Flexfields".

The product table and its key flexfield columns must be registered in the Oracle Fusion Middleware Extensions for Applications (Applications Core) data dictionary before a flexfield can be defined on it. For more information, see Chapter 57, "Using the Database Schema Deployment Framework."

Any implementation of flexfields in Oracle Fusion applications typically requires application seed data, which is the essential data to enable flexfields to work properly in applications. Flexfield seed data can be uploaded and extracted using Seed Data Loader.

After you complete the registration process described in Section 25.2.1, "How to Develop Key Flexfields," your flexfield seed data consists of the information that you registered for your flexfield, such as the tables and columns reserved for your flexfield. For a customer flexfield, the seed data contains only this registration data.

If your flexfield is a developer flexfield, you also serve the role of the implementor. In addition to the registration data, your flexfield seed data might include structures and value sets that you have defined for your flexfield.

For information about extracting and loading seed data, see Chapter 56, "Initializing Oracle Fusion Application Data Using the Seed Data Loader".

25.2.1 How to Develop Key Flexfields

Key flexfields enable you to represent objects such as accounting codes, part numbers, or job descriptions, that combine multiple columns (or segments) into a single object of concatenated segments.

To develop a key flexfield:

  1. Create a combinations table that includes the key flexfield segments.

  2. Create foreign key columns to associate a product table with the combinations table.

  3. Optionally, include the key flexfield segments in product tables for partial usages.

  4. Optionally, create filter columns for defining which key flexfields the user can filter.

  5. Enable the use of flexfield combinations on application pages.

  6. Register and define the key flexfield.

  7. Optionally enable the multiple structure and data set features.

  8. Optionally partially reuse the key flexfield.

  9. Register the entity details for each usage.

25.2.1.1 Creating the Combinations Table

Each key flexfield must have one corresponding table known as the combinations table.

Note:

The product table and its key flexfield columns must be registered in the Applications Core data dictionary before a flexfield can be defined on it. For more information, see Chapter 57, "Using the Database Schema Deployment Framework."

The combinations table must have a code combination ID (CCID) column (type NUMBER) that identifies each data row.

The combinations table can have an optional structure instance number (SIN) column (type NUMBER) with generated values that identify different validation sources for a given structure. These generated values are unique within a given flexfield. Multiple SIN values exist for a key flexfield if you elect to define the flexfield with multiple alternate structure instances.

A given structure (arrangement of segments) can have several structure instances. The structure instances share the same arrangement of segments but use different value sets to validate the segments (for example, one group of value sets for the U.S. and another for France). Each structure instance is identified by an SIN.

The combinations table might also have a data set number (DSN) column (type NUMBER), but only if you have elected to data set–enable your key flexfield code combinations. This is a way of adding simple striping to the combinations table. You insert the DSN column to enable you to tag sets of combination codes with your own numeric IDs, and ADF Business Components supports the DSN by including it as part of the table's primary key. Your SQL code can then select code combinations from this table using a more qualified primary key.

Note:

Data sets are used by specific application-development teams. If your team does not use data sets, you can ignore the references to DSNs in this documentation.

A DSN is not the same thing as a set ID. Set ID partitioning is not supported by flexfields. For information about set IDs, see Chapter 8, "Managing Reference Data with SetIDs".

The table's primary key is composed of a combination of the CCID, SIN, and DSN columns depending on the conditions listed in Table 25-1.

Table 25-1 Primary Key Configuration

Column Include in the Primary Key

CCID

Always

SIN

When the flexfield is multiple structure–enabled or is multiple structure instance–enabled

DSN

When the flexfield is DSN-enabled


The combinations table must include the columns listed in Table 25-2. These columns indicate whether a combination is enabled and active. The column names and data types must match exactly.

Table 25-2 Required Combinations Table Columns

Column Data Type Description

ENABLED_FLAG

VARCHAR2(1) NOT NULL

A 'Y' value indicates that the combination is enabled. Any other value indicates that the combination is not enabled.

START_DATE_ACTIVE

DATE

If a date is specified and the current validation date is earlier than the specified date, the combination is not active. There must not be a default database value for this column.

END_DATE_ACTIVE

DATE

If a date is specified and the current validation date is later than the specified date, the combination has expired. There must not be a default database value for this column.


Include one column for each flexfield segment that you or your customers might wish to customize. You need at least as many columns as the maximum number of segments an end user would ever want in a single key flexfield structure. The columns must be of type VARCHAR2 or NUMBER. If the type is VARCHAR2, the length must be at least 30 characters.

Tip:

There are no constraints on how to name the segment columns. However, these columns are typically named using the patterns SEGMENTn_VARCHAR2 and SEGMENTn_NUMBER. This convention makes it easy to identify the key flexfield segments. It also makes it easier to name the columns for partial usages of the key flexfield.

If the key flexfield defines value attributes, you must include one derived value attribute column of type VARCHAR2 for each value attribute. For more information about value attributes, see Section 25.2.2, "How to Implement Key Flexfield Segment Labels."

Note:

The combinations table may contain other columns than those described here. If the key flexfield is dynamic insert–enabled, these other columns should either be nullable or they should have default database values.

25.2.1.2 Creating Foreign Key Columns to Enable the Use of Flexfield Combinations on Application Pages

To permit the use of flexfield combinations on different application pages, you must include foreign key references to your combinations table's primary key configuration, as shown in Table 25-1, in other product tables. That way, you can display or enter valid combinations using forms not based on your combinations table. When you build a custom application that uses key flexfields, you include foreign key references in your custom product tables wherever you reference the flexfield.

Note:

Pages whose underlying entity objects contain a foreign key reference to the combinations table are referred to as foreign key pages, while pages whose underlying entity objects use the combinations table itself are referred to as combinations pages or maintenance pages.

25.2.1.3 Including Segment Columns in Partial Tables

You can reuse a key flexfield definition over a product table by including some or all of the key flexfield's segment columns in the product table. The product table that contains the redefined segment columns is referred to as a partial table. If a SIN or DSN is used, the partial table must either include those columns or a column from which the SIN or DSN can be derived.

25.2.1.4 Creating Filter Columns

You can use the key flexfield filter feature to represent a subset of combinations. For each filter that you want to include in the application user interface, you define a dedicated column of type XMLType. You can define the column in an existing reference table or you can create one or more dedicated tables just to store filter columns.

For more information, see Section 25.6, "Working with Combination Filters for Key Flexfields."

25.2.1.5 Registering and Defining Key Flexfields Using the Setup APIs

Before you can use a key flexfield in an application, you must first define and register the flexfield using procedures from the FND_FLEX_KF_SETUP_APIS PL/SQL package. This package also has procedures for updating, deleting, and querying about flexfield definitions.

The definition of a key flexfield includes the following information:

  • The code, name, and description of the flexfield.

  • The master usage code. This code is typically the same code as the flexfield.

  • The name of the combinations database table.

  • The names of the database table columns to be used as flexfield segments.

  • The name of the CCID column.

  • The names of the SIN and DSN columns, if they exist.

Before you begin: 

Create the combinations table as described in Section 25.2.1.1, "Creating the Combinations Table."

To learn how to access documentation about using the procedures in the following steps, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

To register and define a key flexfield: 

  1. Run the fnd_flex_kf_setup_apis.create_flexfield(...) procedure to register the key flexfield and its master usage.

  2. Run the fnd_flex_kf_setup_apis.create_segment_column_usage(...) procedure for each segment column to register the segment columns.

  3. (Optionally) Register the entity details as described in Section 25.2.1.9, "Registering Entity Details Using the Setup APIs." This step must be completed before you can generate the flexfield usage's business components.

25.2.1.6 What You May Need to Know About the Key Flexfield Setup API

In the key flexfield development process, you use the FND_FLEX_KF_SETUP_APIS PL/SQL package to manage flexfield registration data.

You can learn about the FND_FLEX_KF_SETUP_APIS PL/SQL package by running the following command, which outputs package documentation and usage examples to the <db_name>_<user_name>_FND_FLEX_KF_SETUP_APIS_<date>.plsqldoc file.

sqlplus <fusion_user>/<fusion_pwd>@<fusion_db> \
@/ORACLE/fusionapps/atgpf/applcore/db/sql/flex/fnd_flex_pkg_doc.sql \ 
FND_FLEX_KF_SETUP_APIS 

25.2.1.7 Enabling Multiple Structure, Multiple Structure Instance, and Data Set Features

In order to enable the multiple structure, multiple structure instance, or data set features for a registered key flexfield, you must run the enable_feature(...) procedure from the FND_FLEX_KF_SETUP_APIS PL/SQL package. To enable the multiple structure feature or multiple structure instance feature, you provide the SIN column name. To enable the data set feature, you provide the DSN column name.

To learn how to access documentation about using the enable_feature(...) procedure, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

25.2.1.8 Partially Reusing a Key Flexfield on Another Table

Key flexfield partial usage enables you to capture the values of a key flexfield's segments in a product table. You can capture all of the flexfield's segments, or just one.

For information about partial reuse of a key flexfield, see Section 25.5, "Completing the Development Tasks for Key Flexfields in Partial Mode."

25.2.1.9 Registering Entity Details Using the Setup APIs

When you build the flexfield business components and create flexfield-specific application module instances, the flexfield modeler requires the following information about the flexfield usage:

  • The full class name of the entity object. For the master usage, this is the entity object that was defined over the combinations table. For a partial usage, this is the entity object that was defined over the partial table.

  • A prefix from which to derive the names of generated objects.

  • The package in which to place the generated business components. Each usage can have its own package name.

You register entity details using the create_adfbc_usage(...) procedure from the FND_FLEX_KF_SETUP_APIS PL/SQL package.

Before you begin: 

  1. Register the usage as described in Section 25.2.1.5, "Registering and Defining Key Flexfields Using the Setup APIs."

  2. Ensure that the entity object for the usage's table exists.

To learn how to access documentation about using the create_adfbc_usage(...) procedure, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

To register the entity details using the registration application: 

  • Run the fnd_flex_kf_setup_apis.create_adfbc_usage(...) procedure to register the entity object, package name, and object name prefix for the flexfield usage.

25.2.2 How to Implement Key Flexfield Segment Labels

A segment label identifies the purpose of a particular segment in a key flexfield.

Usually an application needs some method of identifying a particular segment for some application purpose such as security or computations. However, because a key flexfield can be customized so that segments appear in any order with any prompts, the application needs a mechanism other than the segment name or order to use for segment identification. Segment labels serve this purpose.

You can think of a segment label as an identification tag for a segment. It identifies a segment that application implementors and administrators should include when customizing the key flexfield. By defining segment labels when you define your key flexfield, you ensure that implementors customize the flexfield to include the segments that your application needs.

For example, the General Ledger application needs to be able to identify which segment in the Accounting Flexfield contains the primary balance information and which segment contains natural account information. Because you can customize the Accounting flexfield so segments appear in any order with any prompts, General Ledger needs a segment label to internally specify the correct segment for each purpose. When you define your Accounting flexfield, you must specify which segment labels apply to which segments.

You ensure that the implementor or administrator will define these key segments by defining two segment labels, GL_BALANCING and GL_ACCOUNT. When customizing your accounting flexfield, the implementor ties the GL_BALANCING and GL_ACCOUNT segment labels to particular key segments. As the developer, you need not know which key segment becomes the natural account or primary balance segment, because the key flexfield takes care of returning natural account and primary balance information to your application at runtime.

General Ledger also uses key flexfields that have segment labels identifying the cost center segment (FA_COST_CTR), management segment (GL_MANAGEMENT), and intercompany segment (GL_INTERCOMPANY). Other applications, such as Human Resources, use segment labels as well. Human Resources uses segment labels to control who has access to confidential information in its flexfield segments.

When you use segment labels with a key flexfield, you might also need to define value attributes in which you qualify a value by applying a value attribute to it when the value set is used with a segment that has a segment label.

Note:

For information about retrieving segment label information, see Section 25.4.2, "How to Access Segment Labels Using the Java API".

25.2.2.1 Defining Key Flexfield Segment Labels

You should define and register segment labels if you want to ensure that the application implementor or administrator customizes your key flexfield to include the segments that your application needs. For example, General Ledger defines "account" and "balancing" segment labels in the Accounting flexfield to ensure that implementors define the account and balancing segments.

When you register a key flexfield, you can define segment labels for it.

Segment labels can be unique, required, or global. You specify a segment label as unique if you want the implementor to tie it to at most one segment of the flexfield. You specify a segment label as required if you want the implementor to tie it to at least one segment. You specify a segment label as global if you want it to apply to all segments. Any key flexfield segment can have any number of segment labels applied.

Table 25-3 presents the results of setting these flags on a segment label in various combinations.

Table 25-3 Segment Label Flag Combinations

Global Flag Required Flag Unique Flag Result
N
N
N

0+ (Zero or more segments)

N
N
Y

0,1 (Zero or one segment)

N
Y
N

1+ (One or more segments)

N
Y
Y

1 (Exactly one segment)

Y
-
-

ALL (All segments; global flag overrides the other flags)


For example, in General Ledger's Accounting flexfield, the Account segment label is required and unique because General Ledger requires one and only one account segment.

You create segment labels using the create_segment_label(...) procedure from the FND_FLEX_KF_SETUP_APIS PL/SQL package.

Before you begin: 

Define the key flexfield as described in Section 25.2.1.5, "Registering and Defining Key Flexfields Using the Setup APIs."

To learn how to access documentation about using the create_segment_label(...)procedure, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

To define key flexfield segment labels: 

  • Run the fnd_flex_kf_setup_apis.create_segment_label(...) procedure to register the label and label code for the key flexfield.

25.2.2.2 Using Value Attributes

When you use segment labels with a key flexfield, you might also need to define value attributes.

Every value in a value set has accompanying properties that provide supplemental information about the value, such as a description, an internal code, and start and end dates. In addition to these standard properties, you can further qualify a value by applying a value attribute to it when the value set is used with a segment that has a segment label. There are three types of value attributes:

  • Flexfield value attributes — the FND_VS_VALUES_B table contains 20 available value attribute columns, called FLEX_VALUE_ATTRIBUTE1 through FLEX_VALUE_ATTRIBUTE20, which are globally defined across all Oracle Fusion applications.

  • Custom value attributes — the FND_VS_VALUES_B table also contains 10 additional value attribute columns, called CUSTOM_VALUE_ATTRIBUTE1 through CUSTOM_VALUE_ATTRIBUTE10. Customers cannot modify or reassign the standard value attribute columns, but they can use these custom columns for their own implementations of value attributes.

  • SUMMARY_FLAG — this is a predefined system value attribute, for use with the GL# key flexfield only.

Create value attributes using a procedure from the FND_FLEX_KF_SETUP_APIS PL/SQL package.

Before you begin: 

  1. Define the key flexfield as described in Section 25.2.1.5, "Registering and Defining Key Flexfields Using the Setup APIs."

  2. Define the segment label as described in Section 25.2.2.1, "Defining Key Flexfield Segment Labels."

To define key flexfield segment labels 

  • Run the fnd_flex_kf_setup_apis.create_value_attribute(...) procedure to register the value attribute and attribute code for the segment label.

25.2.3 How to Implement Cross Validation Rules and Custom Validation

Use procedures from the FND_FLEX_KF_SETUP_APIS PL/SQL API to prepare the application database for cross validation rules and custom validation. When you register a key flexfield in your application database, you can also enable cross validation rules and register a customer custom validation callout for the flexfield, so new code combinations entered on a maintenance page or using dynamic combination insertion can be validated.

At runtime, when a new code combination is entered, the validation APIs are called in the following order:

  1. Cross validation rules

  2. Developer custom validation callout

  3. Customer custom validation callout

25.2.3.1 Implementing Cross Validation Rules

To implement a cross validation rule for a key flexfield, use a procedure from the FND_FLEX_KF_SETUP_APIS PL/SQL package to enable the flexfield to use cross validation rules in your application database. Then, build a maintenance user interface that administrators can use to maintain their own rule definitions.

Before you begin: 

Before you can build a cross validation rule maintenance user interface, you must first have created and configured the business components for the key flexfield to which the rule will apply.

For more information, see Section 25.2.4, "How to Create Key Flexfield Business Components."

To learn how to access documentation about using the FND_FLEX_KF_SETUP_APIS PL/SQL package, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

To implement cross validation rules: 

  1. To enable a key flexfield to use cross validation rules, set the value of the flexfield's CVR_ENABLED_FLAG column in the FND_KF_FLEXFIELDS_B table to Y. This flag is a required VARCHAR2(1).

    Note:

    Setting the value of CVR_ENABLED_FLAG to Y enables support for any cross validation rules you define for the flexfield. Support for cross validation is somewhat resource-intensive, so assess each key flexfield to determine whether cross validation is really necessary.

    For example, if the creation of new code combinations for a given key flexfield will be a tightly controlled process that requires organizational oversight, cross validation might be redundant.

  2. To enable administrators to define cross validation rules that are appropriate for their organizations, develop a runtime maintenance utility that they can use to define and maintain their own rows in a dedicated repository table, FND_KF_CROSS_VAL_RULES, as shown in Table 25-4.

    Table 25-4 FND_KF_CROSS_VAL_RULES Cross Validation Repository Table

    Column Type Nullable? Description

    ENTERPRISE_ID

    NUMBER(18)

    No

    (PK) Enterprise ID.

    STRUCTURE_INSTANCE_ID

    NUMBER(18)

    No

    (PK) Structure Instance ID.

    RULE_CODE

    VARCHAR2(30)

    No

    (PK) Developer key for this rule.

    DESCRIPTION

    VARCHAR2(240)

    Yes

    Rule description.

    CONDITION_FILTER

    XMLTYPE

    Yes

    Flexfield filter defining where the rule should be applied. NULL means globally applied.

    VALIDATION_FILTER

    XMLTYPE

    YesFoot 1 

    Flexfield filter defining the validation that must be true.

    ERROR_MSG_APPLICATION_ID

    NUMBER(18)

    Yes

    Message application.

    ERROR_MSG_NAME

    VARCHAR2(30)

    Yes

    Message to display if rule is violated. If NULL, display default message.

    ENABLED_FLAG

    VARCHAR2(1)

    No

    Y/N

    START_DATE_ACTIVE

    DATE

    Yes

    Standard start date.

    END_DATE_ACTIVE

    DATE

    Yes

    Standard end date.

    WHO columnsFoot 2 

    WHO

    WHO

    Standard WHO columns.


    Footnote 1 Although the validation filter must be made nullable in the data model, it is still a required value that is logically necessary for cross validation to work.

    Footnote 2 For more information about WHO columns, see Section 9.3, "Using WHO Column Features."

    The primary key for this table is the combination of ENTERPRISE_ID, STRUCTURE_INSTANCE_ID, and RULE_CODE.

    The cross validation rule itself is the combination of a condition filter and a validation filter in the corresponding XMLType columns of the repository table. These filters are compatible with, and supported by, the key flexfield combination filter infrastructure.

    The value of each of these filters should be a logical combination of boolean expressions. At runtime, all filters from this table that match the application, key flexfield, and SIN of the newly submitted code combination are retrieved, converted into SQL fragments, and used to validate the proposed code combination.

    The condition filter establishes the condition that a proposed new code combination must fulfill to qualify for validation. If it qualifies, the code combination is evaluated against the validation filter. This is demonstrated by the pseudocode in Example 25-1.

    Example 25-1 Applying a Cross Validation Rule

    The condition filter specifies a value range for one segment:

    segment1 <= '10'
     
    

    If the condition is met, the validation filter is applied:

    (segment2 = '20') OR (segment2 = '30')
     
    

    If the proposed code combination is three segments with the following values, the validation will succeed:

    segment1 = '8'
    segment2 = '30'
    segment3 = '70'
     
    

    If the proposed values are as follows, the condition is not met, and the code combination will not be subject to the validation filter:

    segment1 = '12'
    segment2 = '40'
    segment3 = '70'
     
    

    This means that even though this combination would have failed the validation filter, it is still considered valid because the validation filter was not applied. A code combination fails cross validation only if it passes the condition filter, but fails the validation filter.

    Note:

    There are no artificial restrictions on what each filter can contain. If you set the condition filter to NULL, all new code combinations for the flexfield will qualify to be evaluated with the validation filter.

    Use the key flexfield combination filter infrastructure to create a separate maintenance page for each key flexfield that supports cross validation rules.

    For more information, see Section 25.6.2, "How to Add Combination Filters to Your Application", Section 25.6.6, "How to Remove Combination Filters from Your Application", and Section 25.6.3, "How to Employ Combination Filters on an Application Page".

25.2.3.2 Implementing Custom Validation

To implement custom validation, register a PL/SQL validation procedure.

To implement custom validation with the custom validation callout: 

  1. Write a PL/SQL custom validation procedure.

  2. Register the procedure as the customer callout along with the key flexfield to which it will apply.

The PL/SQL validation procedure must have the signature shown in Example 25-2.

Example 25-2 PL/SQL Validation Procedure Signature

type FLEX_VAL_CTX_RECORD is record (
  VALIDATION_DATE DATE);
 
procedure MY_VALIDATION_CALLOUT (
  NEW_CODE_COMBINATION in my_comb_table%ROWTYPE,
  VALIDATION_CONTEXT in FLEX_VAL_CTX_RECORD);

my_comb_table is the name of the combinations table for this key flexfield. When passed, every column in the NEW_CODE_COMBINATION record will be populated with the values of the combination that is about to be inserted. Payload columns in the combinations table that are not related to the flexfield will be passed as null.

VALIDATION_CONTEXT is a record containing any additional usage specific context that may be useful. Currently this record contains only a VALIDATION_DATE field. If there is no validation date, a null value will be passed for VALIDATION_DATE.

The API is expected to raise an exception (with an error message) if validation fails. If an exception is raised then dynamic insert will be aborted, and the message in the exception displayed to the end user. The log will also record the entire call stack, including the fact that the exception was raised from a custom callout. If the API returns without exception it will be considered a success.

After you have written the custom validation callout procedure, you can register it with the key flexfield. The custom validation callouts are registered in the FND_KEY_FLEXFIELDS_B key flexfield registration table as shown in Table 25-5.

Table 25-5 Key Flexfield Custom Validation Callouts

Column Type Nullable? Description

DEVELOPER_VAL_CALLOUT

VARCHAR2(80)

Yes

PL/SQL validation callout procedure for development use.

CUSTOMER_VAL_CALLOUT

VARCHAR2(80)

Yes

PL/SQL validation callout procedure for customer use.


To learn how to access documentation about using the FND_FLEX_KF_SETUP_APIS PL/SQL package, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

25.2.4 How to Create Key Flexfield Business Components

You need to define view objects based on each key flexfield combinations table. The base key flexfield view object has the code combination ID (CCID) column and the optional structure instance number (SIN) column as its only attributes. The SIN, if applicable, is also the discriminator.

Figure 25-2 shows a sample configuration of a product view object using key flexfields.

The base view object is extended to define view object rows of different structure codes. Each structure code corresponds to a view object definition that includes the appropriate flexfield columns for that structure, in addition to the inherited CCID and SIN columns. Although flexfield view objects carry both SINs and structure codes, only SINs are used to link to the product view object.

Figure 25-2 Key Flexfields Modeled as ADF Business Components

Key flexfields modeled as ADF business components

If the combinations table has other fixed (nonflexfield) columns, they are not included in these view objects.

No Java implementation classes are generated for key flexfield view objects. The product view object may or may not have Java implementation classes.

When you create and configure your key flexfield business components, you can decide whether to support maintenance mode (for administrators) or dynamic combination insertion (for end users). For most implementations of a key flexfield, there are two major tasks that you will typically need to complete:

  1. Build a writable maintenance model.

    This model supports building a maintenance mode application, and it supports dynamic combination insertion. It is always required unless you want all end user access to code combinations to be strictly read-only.

  2. Build a read-only reference model.

    This is needed so that you or a consumer of your key flexfield can build a page with a foreign key reference to the combinations table, which is the most likely way that end users will access the key flexfield.

    You can build this model in one of the following ways:

    • Without dynamic combination insertion support.

      To accomplish this, build your read-only reference model.

    • With dynamic combination insertion support.

      To accomplish this, you must enable dynamic combination insertion in the maintenance model and then build the read-only reference model.

Before you begin:

One or more required libraries might have not been automatically included in your project. You must ensure that all required libraries, notably the BC4J Service Runtime, Java EE 1.5 and Java EE 1.5 API libraries, are included.

Using the standard wizard, create application entity objects based on the combinations tables you have defined. Make sure of the following:

  • At least one customization class is included in adf-config.xml. This inclusion serves to ensure correct application behavior. It does not matter which customization class you include.

    For information about customization layers, see the "Understanding Customization Layers" section in the Oracle Fusion Applications Extensibility Guide.

  • These entity objects are directly modeled on the combinations tables; hence they contain the fixed (nonflexfield) columns, if any, along with all of the flexfield columns. In general, all columns should be included.

  • The entity objects have primary keys defined.

  • The Persistent property of every flexfield-related attribute is set to true.

  • The CCID column is of data type java.lang.Long.

  • The SIN column, if it exists, is of data type java.lang.Long.

  • The DSN column, if it exists, is of data type java.lang.Long.

  • NUMBER type segment columns are of data type java.math.BigDecimal.

  • VARCHAR2 type segment columns are of data type java.lang.String.

  • The package name and the object name prefix for each entity object are registered with the ADF Business Components usage to which it will provide data, as described in Section 25.2.1.9, "Registering Entity Details Using the Setup APIs".

25.2.4.1 Building a Writable Maintenance Model

A writable maintenance model is the first element required to support a code combination maintenance page and dynamic combination insertion in your application.

To build a writable maintenance model:

  1. Create the maintenance model key flexfield business components.

  2. Link the business components to the master view object.

  3. Create the maintenance application module.

  4. Optionally, manage combination locking to override the default automatic combination locking with your own implementation.

25.2.4.1.1 How to Create Key Flexfield Business Components for a Maintenance Model

The first element in a writable maintenance model is a set of business components.

To implement this model, you must be sure to select the Maintenance Mode checkbox when you encounter it on the Usage Settings page, as described in the following procedure.

Before you begin:

  1. Create an updatable entity object over your combinations table and add it as an ADF Business Components usage for your key flexfield, as described in Section 25.2.1.9, "Registering Entity Details Using the Setup APIs."

    The entity object that you select must allow maintenance operations such as Update or Insert. The entity object class must extend oracle.apps.fnd.applcore.oaext.model.KFFMEntityImpl, and the entity definition class must extend oracle.apps.fnd.applcore.oaext.model.KFFMEntityDefImpl.

    Caution:

    Disable the delete capability for the code combinations table, as the deletion of previously created combinations might invalidate foreign key references. If you want to disallow the use of a combination, disable the combination instead of deleting it.

  2. Create a master view object based on the same updatable entity object.

    This view object typically contains your payload attributes, and should not include flexfield attributes.

    In the master view object, ensure that the CCID attribute's Display control hint is set to Hide.

To create key flexfield business components:

  1. Build your project to ensure that the entity objects are available in classes. The modeler relies on what is in your classes.

  2. In the New Gallery, navigate to Business Tier > ADF Business Components and select Flexfield Business Components.

  3. Click OK to access the Create Flexfield Business Components wizard.

  4. On the Role page, select the role that you are taking as you create the flexfield business components:

    • Developer — select this role if you are incorporating the flexfield into an application. The business components must be stored in one of your projects. Select the desired project location from the Project Source Path dropdown list.

    • Tester — select this role if you are planning to test or share your flexfield. In the Output Directory field, specify the path of your desired location for the generated business components.

      For more information about testing flexfields, see Chapter 26, "Testing and Deploying Flexfields". For more information about sharing and importing shared flexfields, see Section 25.2.5, "How to Share Key Flexfield Business Components".

    Note:

    This is not a role in the security sense. It exists only during this procedure, for the purpose of specifying where your generated flexfield business components should be stored.

  5. Click Next. The Flexfield page appears, as shown in Figure 25-3.

    Figure 25-3 Create Flexfield Business Components Wizard — Flexfield Page

    Business Components wizard - Flexfield page
  6. From the Type dropdown list, select Key.

  7. In the Application field, specify the full name of the application to which your key flexfield belongs.

    You can browse for the name, and filter by ID, Short Name, or Name.

  8. In the Code field, specify the code of the key flexfield you want to use.

    You can browse for and filter by Code.

  9. In the Usage section, select the table row that contains the master usage of the key flexfield as it is defined on its combinations table. Every key flexfield has exactly one master usage.

    To identify the master usage, the Usage Code field for this type is typically the same as the flexfield code. The Table Name field displays the name of the combinations table, and the Description field does not contain the prefix (Partial) or (Partial Single) in parentheses.

    You must select the master usage in this procedure because you are generating key flexfield business components over your combinations table for your maintenance model.

  10. Click Next. The Entity Object page appears, as shown in Figure 25-4.

    Figure 25-4 Create Flexfield Business Components Wizard — Entity Object Page

    Business Components wizard - Entity Object page
  11. Expand the tree of available models and select an entity object to use as the data source for the key flexfield.

    Select the entity object for the combinations table. It must allow maintenance operations such as update or insert, and include all of the attributes that will be referenced by the flexfield. For the key flexfield master usage, this includes attributes that represent the CCID, SIN, and segment columns, and the DSN column if it exists in the combinations table.

  12. You might wish to select an entity object for which the key flexfield attributes are defined as transient (not based on database table columns). If you need to do this, select the checkbox labeled Use the entity attributes named after their corresponding flexfield database columns. This checkbox is unselected by default.

    When a key flexfield entity object attribute is transient, there is no matching underlying column name. When you select this checkbox, the system will match the entity object attribute names to the key flexfield column names, and use the matching attributes to access the flexfield data. Make sure that the entity object has a full set of attributes with matching names before you select this option.

    This entity object must be registered under the master usage. There is no need to register another table for this purpose, even if the entity object is based on some other table. See Section 25.2.1.9, "Registering Entity Details Using the Setup APIs," for more information about registering ADF Business Components usage.

    Caution:

    The Create Flexfield Business Components wizard is case-sensitive. All column names — and the names of the flexfield entity object attributes associated with them — must be upper case.

  13. Click Next. The Usage Settings page appears.

    Because you specified the master usage of the key flexfield on the Flexfield page of this wizard, this page contains a Maintenance Mode checkbox.

    Select Maintenance Mode to build your maintenance model.

  14. Click Next. The Naming page appears.

    To create business components, the package name and the object name prefix for the selected entity object must first be registered with the key flexfield master usage. Text on the Naming page indicates whether this is the case:

    • If the selected entity object is registered with the flexfield usage, the Naming page displays the package name and the object name prefix for the entity object. Click Next and continue to Step 15.

    • If the selected entity object is not registered as an ADF Business Components usage, the Naming page displays a message to that effect. Take one of the following actions:

  15. On the Summary page, review your choices and click Finish.

    The business components generated will replace any existing ones that are based on the same flexfield.

    Note:

    This wizard might fail with a "ClassNotFound" exception message. This indicates that one or more required libraries have not been automatically included in your project, notably the BC4J Service Runtime, Java EE 1.5 and Java EE 1.5 API libraries. You can resolve this issue by manually adding any missing libraries; then you can complete this procedure successfully.

  16. Refresh the project to see the newly created flexfield business components in the Application Navigator.

25.2.4.1.2 How to Link Your Maintenance Model Key Flexfield Business Components to Your Code Combination Master View Object

You need to create a flexfield view link from your code combination master view object to the maintenance key flexfield business components. This enables your maintenance user interface to access all of the combinations table columns using the linked view objects over the combinations table entity object.

The master view object and the base key flexfield view object are linked through the combination of a CCID, and SIN, and if present, a DSN.

To create a key flexfield maintenance model view link:

  1. In the New Gallery, navigate to Business Tier > ADF Business Components and select Flexfield View Link.

  2. Click OK to access the Flexfield View Link wizard.

  3. On the Name page, from the Package dropdown list, specify a package for the view link.

  4. In the Name field, enter a name for the view link.

  5. Click Next. The View Objects page appears, as shown in Figure 25-5.

    Figure 25-5 Create Flexfield View Link Wizard — View Objects Page

    View Link wizard - View Objects page
  6. In the Select Source View Object tree, expand the available objects from your current project and select your code combination master view object.

  7. In the Select Destination Flexfield tree, expand the available flexfield view objects from your project and select your maintenance key flexfield view object as the destination.

  8. In the View Link Accessor Name field, enter an appropriate name for the view link accessor.

  9. Click Next to access the Source Attributes page.

    Note:

    For key flexfields in maintenance mode, the Source Attributes page is informational only. The primary key attributes of the source view object will be used to define the view link.

    If you see any controls on this page for selecting source attributes, you are not using maintenance mode business components. Return to Section 25.2.4.1, "Building a Writable Maintenance Model" and re-create your maintenance mode business components according to the instructions.

  10. Click Finish to go to the Summary page.

    Note:

    You can skip the Properties page because view link-specific properties are not supported.

  11. On the Summary page, review the summary, then click Finish.

25.2.4.1.3 How to Create the Maintenance Application Module

You need to create the maintenance application module for the key flexfield. The application module contains the combination view object, the maintenance model view link, and the key flexfield's application module that was created when you created the business components in Section 25.2.4.1.1, "How to Create Key Flexfield Business Components for a Maintenance Model."

For more information about creating application modules and nesting application model instances, see the "Implementing Business Services with Application Modules" chapter in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

Before you begin:

  1. Create the business components as described in Section 25.2.4.1.1, "How to Create Key Flexfield Business Components for a Maintenance Model."

  2. Create the view link as described in Section 25.2.4.1.2, "How to Link Your Maintenance Model Key Flexfield Business Components to Your Code Combination Master View Object."

To create the maintenance application module:

  1. Use the standard wizard to create an application module.

  2. In the Data Model Components page, move the combination master view object and the maintenance model view link to the Data Model list, as shown in Figure 25-6.

    Figure 25-6 Create Application Module Wizard — Data Model Page

    Application Module wizard - Data Model page

    Tip:

    The object name prefix and package name are used to name the flexfield business components, and are defined in the database along with the key flexfield.

  3. Click Next. The Application Modules page appears.

  4. On the Application Modules page, in the Available tree, move the appropriate key flexfield application modules to the Selected list, as shown in Figure 25-7. Be sure to include the key flexfield application module that was created when you created the business components in Section 25.2.4.1.1, "How to Create Key Flexfield Business Components for a Maintenance Model."

    Figure 25-7 Create Application Module Wizard — Application Modules Page

    Application Module wizard - Application Modules page

    Note:

    For each key flexfield, only one instance of the application module is needed. For example, even though two view links may have been created to access the same flexfield, only one instance of the flexfield application module is needed in the product application module.

  5. When you complete the Create Application Module wizard, right-click the new application module instance and choose Run to test it.

Note:

The maintenance application module must have a configuration named appmodule_nameLocal. By default, this is created for you. For example, if the application module is called MyKffMaintAM, then a configuration named MyKffMaintAMLocal must exist.

Tip:

You can publish a key flexfield application module instance as a web service. For more information about creating and testing a key flexfield service interface, see Section 25.4.4, "How to Publish Key Flexfield Application Modules as Web Services."

25.2.4.1.4 How to Manage Combination Locking

Automatic combination locking is enabled by default in oracle.apps.fnd.applcore.oaext.model.KFFMEntityImpl, which is extended by your code combination entity object class. The method doDML(int, TransactionEvent) is overridden to lock the combination to be inserted or updated. The lock is removed when the transaction is committed or rolled back.

If you wish to completely overwrite doDML with your own implementation, you can turn the automatic locking off by calling setAutoCombinationLockEnabled(false), then calling lockCombination(DBTransaction) on your own. For more information, refer to the Java documentation for oracle.apps.fnd.applcore.oaext.model.KFFMEntityImpl.

25.2.4.2 Enabling Dynamic Combination Insertion

This task is necessary only if you want to permit end users to create new code combinations extemporaneously on an application page. You must have already built a writable key flexfield maintenance model. For more information, see Section 25.2.4, "How to Create Key Flexfield Business Components."

To enable this feature, you define an application module that you configure for dynamic combination insertion, then implement the appropriate Java class in the user interface. You can create a basic implementation of dynamic combination insertion under the simplest conditions, craft a more sophisticated version that includes added combination attributes, or, if custom validation procedures or cross validation rules are registered with the flexfield, create a version that makes information available to the custom validation procedures.

25.2.4.2.1 Enabling Dynamic Combination Insertion

To enable dynamic combination insertion, the key flexfield must be set to allow dynamic combination insertion, and the full name of the maintenance application module that will implement KFFCombinationCreator must be registered with the key flexfield.

To enable dynamic combination insertion:

  1. Issue the following SQL update statement to enable dynamic combination insertion:

    update fnd_kf_flexfields_b
    set dynamic_combo_creation_flag = 'Y'
    where application_id = :app_id
    and key_flexfield_code = :kff_code
    

    Set the :app_id to the application_id that was specified when the flexfield was created, and set :kff_code to the flexfield's key_flexfield_code. For more information, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

  2. Issue the following SQL statement to set the name of the application module to be used for dynamic combination insertion:

    update fnd_kf_flexfields_b
    set application_module_name = 'fully qualified name of application module'
    where application_id = :app_id
    and key_flexfield_code = :kff_code
    

    The name of the application module must be fully qualified; for example, mycompany.myproduct.flex.kff1.applicationModule.Kff1AM.

    Set the :app_id to the application_id that was specified when the flexfield was created, and set :kff_code to the flexfield's key_flexfield_code. For more information, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

25.2.4.2.2 Inserting a Code Combination — the Simplest Case

In the simplest case, you need only replace the existing base object class, oracle.apps.fnd.applcore.oaext.model.OAApplicationModuleImpl, with oracle.apps.fnd.applcore.oaext.model.KFFCombinationCreatorImpl. KFFCombinationCreatorImpl extends OAApplicationModuleImpl.

This implementation is possible only under the following conditions:

  • You have no custom Java application module class for this application module.

  • You have only one nested key flexfield application module instance in this application module, and this nested instance is the one that represents the key flexfield of interest.

  • You do not need to update any columns of the combinations table, including the value attribute columns.

25.2.4.2.3 Inserting a Code Combination with Added Combination Attributes

To insert a code combination with added combinations, you implement the KFFCombinationCreator Java class in the maintenance application module, and you create a Java implementation of the maintenance application module. You can optionally initialize the columns.

  1. In the maintenance application module, implement the Java class oracle.apps.fnd.applcore.oaext.model.KFFCombinationCreator from the oracle.apps.fnd.applcore.oaext.model package.

    The class has only one method defined:

    public void createKeyFlexfieldCombination(Long sin, Long dsn, List<Object> segValues);
    

    This method has the following parameters:

    • sin: The structure instance number. This should be null if this key flexfield does not allow multiple structures.

    • dsn: The data set number. This should be null if this key flexfield does not use data set numbers.

    • segValues: A read-only list of segment values.

    If an error occurs during creation, throw the exception FlexfieldJboException.

  2. Generate a Java application module class to extend your existing base object class. By default, the base class is OAApplicationModuleImpl from the oracle.apps.fnd.applcore.oaext.model package. An example of an application module class is shown in Example 25-3.

    Example 25-3 Implementing KFFCombinationCreator

    public class MyKffMaintenanceAM 
      extends OAApplicationModuleImpl 
      implements KFFCombinationCreator
    {
      /**
       * Container's getter for MyKffAM1.
       *
       * @return MyKffAM1
       */
      public ApplicationModuleImpl getMyKffAM1()
      {
        return (ApplicationModuleImpl) findApplicationModule("MyKffAM1");
      }
     
      public void createKeyFlexfieldCombination(
      Long sin, Long dsn, List<Object> segValues)
      {
        KFFCombinationAttributes combAttrs =
          ((KFFMApplicationModuleImpl) 
          getMyKffAM1()).insertCombination(sin, dsn, segValues);
      }
    }
    

    By default, JDeveloper creates accessor methods (such as getMyKffAM1()) to all of your nested application modules. You can make use of these accessor methods to access the key flexfield application module, as shown in bold in Example 25-3.

    If you do not need to update any combination attributes, the implementation in the example is sufficient; otherwise you can use the KFFCombinationAttributes object to update the value attribute columns, or use the master view object to update any other columns of the combinations table, as described in the next steps.

  3. Optionally, initialize value attribute columns using KFFCombinationAttributes.

    The KFFCombinationAttributes object enables you to:

    • Get the segment values and their value-attribute values.

    • Get the default values of the value attributes used.

    • Get the list of value attribute codes for a label used in this flexfield.

    • Get the current value of a value attribute column of the combinations table.

    • Update a value attribute column of the combinations table.

    By default, the standard value attribute columns such as START_DATE_ACTIVE, END_DATE_ACTIVE, and ENABLED_FLAG are initialized in the insertCombination call. The ENABLED_FLAG is initialized to Y. The START_DATE_ACTIVE value is set to the maximum of the START_DATE_ACTIVE values for the segments, or NULL if all values are null. The END_DATE_ACTIVE value is set to the minimum of the END_DATE_ACTIVE values for the segments, or NULL if all values are null. You have full access to these value-attribute values and can update these columns of the combinations table if you wish.

    Example 25-4 demonstrates how to use the KFFCombinationAttributes object to access the value-attribute values and update the value attribute columns of the combinations table.

    Example 25-4 Using the KFFCombinationAttributes Object

    public void createKeyFlexfieldCombination(
      Long sin, Long dsn, List<Object> segValues)
    {
        KFFCombinationAttributes combAttrs =
          ((KFFMApplicationModuleImpl) 
          getMyKffAM1()).insertCombination(sin, dsn, segValues);
     
        final String myLabel = "MY_LABEL";
        // Get the segment values and their value-attribute values for the label.
        List<FlexfieldSegmentValue> segValueList = combAttrs.getSegmentValues(myLabel);
        // Loop through each segment value.
        for (FlexfieldSegmentValue segValue: segValueList)
        {
          // Get the segment code if needed.
          System.out.println("SegmentCode = " + segValue.getSegmentCode());
          // Get the segment value if needed.
          System.out.println("SegmentValue = " + segValue.getValue());
          // Iterate through each value attribute code for the label if needed.
          Iterator<String> it = combAttrs.getValueAttrCodeIterator(myLabel);
          while (it.hasNext())
          {
            String valAttrCode = it.next();
            FlexfieldSegmentValue.ValueAttributeValue valAttrValue =
              segValue.getValueAttributeValue(valAttrCode);
            // Value attribute code is also available in the value object.
            System.out.println("  ValueAttrCode = "
                               + valAttrValue.getValueAttributeCode());
            // Get the default value of the value attribute.
            System.out.println("  ValueAttrDefaultValue = "
                               + valAttrValue.getDefaultValue());
            // Get the value of the value attribute.
            System.out.println("  ValueAttrValue = "
                               + valAttrValue.getValue());
          }
        }
     
        System.out.println();
     
        final String myValueAttrCode = "MY_VALUE_ATTRIBUTE1";
     
        // Get the current combination value attribute.
        System.out.println(myLabel + ":" + myValueAttrCode + " = "
                           + combAttrs.getValueAttribute(myLabel, myValueAttrCode));
     
        // Update the combination value attribute.
        Map<String, Object> valueMap = new HashMap<String, Object>(1);
        valueMap.put(myValueAttrCode, "N");
        combAttrs.setValueAttributes(myLabel, valueMap);
     
        System.out.println();
     
        /*** Dealing with standard value attributes. ***/
     
        // Get the segment values with the standard value attributes.
        List<FlexfieldSegmentValue> segValueListStd = combAttrs.getSegmentValues();
        // Loop through each segment value.
        for (FlexfieldSegmentValue segValue: segValueListStd)
        {
          System.out.println("SegmentCode = " + segValue.getSegmentCode());
     
          // Get the START_DATE_ACTIVE attribute.
          FlexfieldSegmentValue.ValueAttributeValue startDateActive =
            segValue.getValueAttributeValue(
              FlexfieldSegmentValue.VALUE_ATTR_START_DATE_ACTIVE);
          // Get the END_DATE_ACTIVE attribute.
          FlexfieldSegmentValue.ValueAttributeValue endDateActive =
            segValue.getValueAttributeValue(
              FlexfieldSegmentValue.VALUE_ATTR_END_DATE_ACTIVE);
          // Get the ENABLED_FLAG attribute.
          FlexfieldSegmentValue.ValueAttributeValue enabledFlag =
            segValue.getValueAttributeValue(
              FlexfieldSegmentValue.VALUE_ATTR_ENABLED_FLAG);
     
          System.out.println("  StartDateActive = " + startDateActive.getValue());
          System.out.println("  EndDateActive = " + endDateActive.getValue());
          System.out.println("  EnabledFlag = " + enabledFlag.getValue());
        }
     
        System.out.println();
     
        // Get the current combination start date.
        System.out.println("StartDateActive = "
                           + combAttrs.getValueAttribute(null,
                              FlexfieldSegmentValue.VALUE_ATTR_START_DATE_ACTIVE));
        // Get the current combination end date.
        System.out.println("EndDateActive = "
                           + combAttrs.getValueAttribute(null,
                              FlexfieldSegmentValue.VALUE_ATTR_END_DATE_ACTIVE));
        // Get the current combination enabled flag.
        System.out.println("EnabledFlag = "
                           + combAttrs.getValueAttribute(null,
                              FlexfieldSegmentValue.VALUE_ATTR_ENABLED_FLAG));
     
        // You can update the standard value attributes by calling
        // combAttrs.setValueAttributes(Map).
    }
    
  4. If you want to initialize other columns of the combinations table, first include them in the master view object. After insertCombination is called, the new entity will be available to the master view object as well, as shown in the following example, an alternative version of MyKffMaintenanceAM.java.

    Example 25-5 Calling insertCombination to Make the New Entity Available to the Master View Object

    public void createKeyFlexfieldCombination(
      Long sin, Long dsn, List<Object> segValues)
    {
        KFFCombinationAttributes combAttrs =
          ((KFFMApplicationModuleImpl) 
          getMyKffAM1()).insertCombination(sin, dsn, segValues);
     
        // In this example, the key flexfield allows multiple structures.
        // The order of the key values must match the order of the keys;
        // see ADF Business Components Java documentation for more details.
        Key keyValues = new Key(new Object[] {combAttrs.getCodeCombinationID(),
                                              sin});
     
        // getMyKffComboAttrVO() is generated when a view object instance named
        // "MyKffComboAttrVO" is present.  You can always call 
        // findViewObject to find the
        // view object if no accessor method is available.
        OAViewObjectImpl vo = getMyKffComboAttrVO();
     
        Row[] rows = vo.findByKey(keyValues, 1);
        if (rows != null && rows.length == 1)
        {
          rows[0].setAttribute("MyCombinationAttr1", "Y");
        }
        else
        {
          throw new FlexfieldJboException(
            "Unable to find the newly created combination: CCID = "
            + combAttrs.getCodeCombinationID()
            + ", SIN = "
            + sin);
        }
    }
    
25.2.4.2.4 Inserting a Code Combination that Uses Custom Validation Procedures or Cross Validation Rules

If custom validation procedures or cross validation rules are registered with the flexfield, you must create a Java class for the maintenance application module that implements KFFCombinationCreatorProxy. This makes the information in the KFFCombinationCreatorProxy.Context object, such as validation date, available to the custom validation procedures.

  1. In the maintenance application module, generate a Java class that implements KFFCombinationCreatorProxy from the oracle.apps.fnd.applcore.oaext.model package and extends your existing base object class. By default, the base class is OAApplicationModuleImpl from the same package. An example of an application module class is shown in Example 25-3.

    Note:

    KFFCombinationCreatorProxy is a sub-interface of KFFCombinationCreator.

    Example 25-6 Implementing KFFCombinationCreatorProxy

    public class MyKffMaintenanceAM extends OAApplicationModuleImpl implements KFFCombinationCreatorProxy
    {
      /**
       * Container's getter for MyKffAM1.
       *
       * @return MyKffAM1
       */
      public ApplicationModuleImpl getMyKffAM1()
      {
        return (ApplicationModuleImpl) findApplicationModule("MyKffAM1");
      }
     
      @Override
      public void createKeyFlexfieldCombination(
        Long sin, Long dsn, List<Object> segValues)
      {
        // Delegated to the method that takes "context".
        this.createKeyFlexfieldCombination(sin, dsn, segValues, null);
      }
     
      @Override
      public void createKeyFlexfieldCombination(final Long sin, final Long dsn, 
                                                final List<Object> segValues,
                                                final Context context)
      {
        KFFCombinationAttributes combAttrs =
          ((KFFMApplicationModuleImpl) getMyKffAM1()).insertCombination(
              sin, dsn, segValues, context);
      }
    }
    
  2. Optionally, initialize value attributes, as described in Step 3 of Section 25.2.4.2.3, "Inserting a Code Combination with Added Combination Attributes."

  3. Optionally, initialize other columns of the combinations table, as described in Step 4 of Section 25.2.4.2.3, "Inserting a Code Combination with Added Combination Attributes."

25.2.4.3 Building a Read-Only Reference Model

A set of business components that constitute a read-only reference model is needed so that you or a consumer developer can build a page with a foreign key reference to the code combinations table.

Note:

If you want to permit end users to create new code combinations extemporaneously on an application page, you must have already built a writable key flexfield maintenance model and have enabled dynamic combination insertion.

For more information, see Section 25.2.4, "How to Create Key Flexfield Business Components."

Before you begin: 

Create a read-only entity object over your combinations table and add it as an ADF Business Components usage for your key flexfield, as described in Section 25.2.1.9, "Registering Entity Details Using the Setup APIs."

To create key flexfield business components for a read-only reference model: 

  1. Complete Steps 1 through 10 in Section 25.2.4.1.1, "How to Create Key Flexfield Business Components for a Maintenance Model."

  2. On the Entity Object page, expand the tree of available models and select the read-only entity object that you created for the combinations table.

    The entity object you select must include all of the attributes that will be referenced by the flexfield. For the key flexfield master usage, this includes attributes that represent the CCID, SIN, and segment columns, and the DSN column if it exists in the combinations table.

  3. Complete Steps 12 through 16 in Section 25.2.4.1.1, "How to Create Key Flexfield Business Components for a Maintenance Model."

25.2.5 How to Share Key Flexfield Business Components

Sharing flexfield business components is just like sharing any other ADF Business Components objects. You can share the objects through an ADF library JAR file. The developers then can import the business components that are contained in the JAR file.

For more information, see the "Packaging a Reusable ADF Component into an ADF Library" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

25.2.5.1 Creating an ADF Library JAR File

If you are the owner of the flexfield business components you want to share, you can create a JAR file containing those business components. Generally, an entire JDeveloper project is deployed as an ADF Library JAR file.

Create your shared ADF library containing the business components from the read-only reference model you just built, then add the business components from the writable maintenance model as well.

To create an ADF Library JAR file: 

  1. Right-click the project you wish to share and select Project Properties from the menu.

  2. Select Deployment.

  3. If a deployment profile is already listed, you can verify that it is for an ADF Library JAR file. Open the profile for editing and observe the window title to confirm that it says "Edit JAR Deployment Profile Properties."

    If you do not already have an appropriate deployment profile, you can create one:

    1. In the New gallery, select General > Deployment Profiles > ADF Library JAR File and click OK.

    2. Enter a name for the profile and click OK.

  4. Right-click the project you wish to share and select Deploy > deployment_profile_name > To JAR File.

    The JAR file is created in your project's deploy directory as deployment_profile_name.jar. You can send it to other developers for use in their projects.

25.2.5.2 Importing Business Components From an ADF Library

After an ADF Library JAR file has been created by one developer, another developer can import the business components that are contained in the file.

For more information, see the "Adding ADF Library Components into Projects" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

To Import business components from an ADF library: 

  1. Obtain an ADF Library JAR file from another developer and save it to an accessible directory for importing.

  2. Right-click the project where you want to import the components and select Project Properties.

  3. Select Business Components > Imports.

  4. Click Import, then navigate to the location of the ADF Library JAR file.

  5. Select the ADF Library JAR file and click Open.

    The business components in the JAR file are imported into your project.

25.2.6 How to Build a Key Flexfield Maintenance User Interface

You can use the business components from a writable maintenance model to build a key flexfield code combination maintenance page. Building a maintenance page is fairly straightforward. If you have already inserted a key flexfield component into a page in either form or table layout, building a maintenance page follows a similar pattern.

Before you begin:

Create a maintenance application module over the combinations table and a writable maintenance model for the flexfield usage as described in Section 25.2.4.1, "Building a Writable Maintenance Model."

To build a key flexfield maintenance page:

  1. From the Data Controls panel, expand the maintenance application module that you created over the combinations table.

  2. Drag the master view object from the application module onto the page, and add it as either a form or a table.

  3. Select the key flexfield view object and drag it onto the page. Drop the flexfield view object into the form or table that you just created.

For more information, see Section 25.3.4, "How to Employ Key Flexfield UI Components on a Page".

25.2.7 What Happens at Runtime: Creating New Combinations

At runtime, an instance of the registered application module is created. Whenever a new combination needs to be created, the following happens:

  1. The createKeyFlexfieldCombination method is invoked. When KFFApplicationModuleImpl.insertCombination is called in the implementation, a lock is created to ensure that no one else can insert the same combination.

  2. The transaction of the registered application module is committed or rolled back. The lock is always removed.

  3. One of the following occurs:

    • If no exception is thrown, the transaction is committed.

    • If any exception occurs, the transaction is rolled back.

25.3 Completing the Consumer Tasks for Key Flexfields in Reference Mode

You can reference flexfields from another (producer) application into your (consumer) application. The consumer tasks for a key flexfield master usage (also referred to as reference mode) are:

  1. Create a view link from your (consumer) product view object to the producer's key flexfield view object.

  2. Nest the producer's key flexfield application module instance in the (consumer) application module for the application.

  3. Add a key flexfield view object instance to the (consumer) application module for your application.

  4. Add your key flexfield to an application page.

  5. Configure the flexfield user interface components.

If you want changes in your key flexfield to trigger a partial update of another component, set the AutoSubmit UI property of the flexfield to True, and add the key flexfield ID to the PartialTriggers UI property of the other component.

Caution:

To ensure that the trigger works, you must append "CS" to the key flexfield ID. For example, if you want changes in the MyKeyFlex01 flexfield to trigger an update in another component, add "MyKeyFlex01CS" to that component's PartialTriggers property.

For more information about setting user interface properties, see Section 25.3.5.1, "Configuring Flexfield-Level User Interface Properties."

25.3.1 How to Create Key Flexfield View Links

A view link is needed whenever one of your product view objects references the producer's key flexfield. The product view object and the base key flexfield view object are linked through the combination of a CCID, an SIN, and if present, a DSN. The key flexfield view object can have many incoming view links from various product view objects, as a key flexfield is usually referenced by many product tables. For example an ExpenseLines view object might have a foreign key reference to the GLKff view object.

By default, when a value set is security-enabled, any key flexfield code combination segment that uses that value set will automatically be secured. Security rules defined on the value set are propagated automatically to the combinations table, and also to any product table that references the combinations table. This means that when an end user does a search on the product table, the results shown are limited to the data referencing the code combination entries to which the user has access. You can add a custom property to the view link to disable the propagation of the security rules to the product table.

Before you begin:

  • Ensure that the flexfield library jars that were created by the producer team have been added to your project. For more information about library jars, see Section 25.2.5.1, "Creating an ADF Library JAR File" and Section 25.2.5.2, "Importing Business Components From an ADF Library."

  • You should have already created a foreign key entity object and view object for your foreign key product table that references the key flexfield.

    Ensure that the view object does not include flexfield attributes such as SEGMENT1_VARCHAR2, SEGMENT2_NUMBER, and so on. Ensure that you include the attributes that are needed for the foreign key reference, such as CCID, SIN, and, if present, DSN. Ensure that the CCID attribute's Display control hint is set to Hide.

To create a key flexfield view link:

  1. In the New Gallery, navigate to Business Tier > ADF Business Components and select Flexfield View Link.

  2. Click OK to access the Flexfield View Link wizard.

  3. On the Name page, from the Package dropdown list, specify a package for the view link.

  4. In the Name field, enter a name for the view link.

  5. Click Next. The View Objects page appears.

  6. In the Select Source View Object tree, expand the available objects from your current project and select a source view object.

  7. In the Select Destination Flexfield tree, expand the available flexfield view objects from your project and select a destination base key flexfield view object.

  8. In the View Link Accessor Name field, enter an appropriate name for the view link accessor.

  9. Click Next. The Source Attributes page appears, as shown in Figure 25-8.

    Figure 25-8 Create Flexfield View Link Wizard — Source Attributes Page for Key Flexfields

    Wizard - Source Attributes page for key flexfields
  10. From the Code-Combination ID dropdown list, select the source attribute that corresponds to the CCID of the destination key flexfield.

    The CCID must be mapped to type java.lang.Long.

  11. If your destination key flexfield supports multiple structure instances, the Structure Instance Number dropdown list appears on the Source Attributes page. You must specify a structure instance as an additional source attribute. From the dropdown list, select the source attribute that corresponds to the SIN of the destination key flexfield.

    The SIN must be mapped to type java.lang.Long.

    Note:

    The source attribute must be an entity attribute that is either persistent or is SQL-derived.

  12. If your destination key flexfield supports multiple structure instances and is data set–enabled, the Data Set Number dropdown list appears on the Source Attributes page. You must specify a data set as an additional source attribute. From the dropdown list, select the source attribute that corresponds to the DSN of the destination key flexfield.

    The DSN must be mapped to type java.lang.Long.

  13. Click Finish to go to the Summary page.

    Note:

    You can skip the Properties page because view link-specific properties are not supported.

  14. On the Summary page, review the summary, then click Finish to create the view link.

  15. Optionally, disable the automatic propagation of value set security rules to the product table by adding a custom property to the view link between the product view object and the key flexfield view object, as follows:

    <propertyname="FND_ACFF_MasterSecuredByFlexfield" Value="false"/>
    

25.3.2 How to Nest an Instance of the Key Flexfield Application Module in the Product Application Module

Use the overview editor for the product application module to nest the application module instance for the key flexfield. This is the application module instance that was created when you created the flexfield business component and was named using the prefix that you specified when you defined the usage's entity details. The nested key flexfield application module instance shares the same transaction and entity object caches as the application module.

Before you begin:

You should have already created a product application module.

To nest an instance of the key flexfield application module in the product application module:

  1. In the Application Navigator, double-click the product application module.

  2. In overview editor, click the Data Model navigation tab.

  3. On the Data Model Components page, expand the Application Module Instances section and, in the Available list, select the key flexfield application module.

    The New App Module Instance field below the list shows the name that will be used to identify the next instance that you add. You can change this name.

  4. With the desired application module selected, move the key flexfield application module to the Selected list.

25.3.3 How to Add an Instance of a Key Flexfield View Object to the Product Application Module

You need to add a flexfield view object instance that reflects the hierarchy of the view link that you created in Section 25.3.1, "How to Create Key Flexfield View Links" to the product application module. You can use the data model that the application module overview editor displays to create the master-detail hierarchy of view instances. The master view object is the view object for your foreign key product table, and the detail view object is the view object for the flexfield. For example if you created a view link from the ExpenseLines view object to the GLKff view object, ExpenseLines is the master and GLKff is the detail.

For more information about creating a hierarchy of view instances, see "Adding Master-Detail View Object Instances to an Application Module" in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

Before you begin:

  1. Add an instance of the view object for your foreign key product table to the product application module, as described in the "How to Add a View Object to an Application Module" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

  2. Create a view link between the view object for the foreign key product table and the key flexfield view object as described in Section 25.3.1, "How to Create Key Flexfield View Links."

To add an instance of a key flexfield view object to the product application module:

  1. In the Application Navigator, double-click the product application module.

  2. In the overview editor, click the Data Model navigation tab.

  3. Expand the View Object Instances section.

  4. If an instance of the view object for your foreign key product table does not appear in the Data Model list, select it in the Available View Objects list and move it over. This is the master view object instance.

  5. In the Data Model list, select the instance of the view object for your foreign key product table (the master view object instance) so that it appears highlighted. This will be the target of the detail flexfield view instance you will add.

  6. In the Available View Objects list, expand the view object for your foreign key product table and move the nested key flexfield view object (the detail view object) to the Data Model list. The flexfield view instance appears nested under master view instance.

    Figure 25-9 Flexfield View Instance Nested Under Master View Instance

    Flexfield view instance nested under master view instance

25.3.4 How to Employ Key Flexfield UI Components on a Page

To employ a key flexfield UI component on an application page, you add the flexfield to a form component or a table component, then configure the properties of the flexfield.

Note:

This section assumes you are using the data-first method of adding flexfields to your application, in which you build the data model first, then create the user interface by dragging data controls onto a page. The UI-first method is also available, but is not documented here.

Key flexfields can be implemented on the following types of pages:

  • A page with a foreign key reference

    The base table (or view) for this type of page contains a foreign key reference to a combinations table that contains the actual flexfield segment columns. You create a page with a foreign key reference if you want to use your page to manipulate rows containing code combination IDs.

    The primary purpose of foreign key pages is generally unrelated to the fact that some fields might be key flexfields. That is, the purpose of the page is to accomplish whatever business function is required (such as entering orders, receiving parts, and so on). You might have many foreign key pages that use a given key flexfield.

  • A page with partial usage of a key flexfield

    You can invoke the partial usage feature of key flexfields on a page. Partial usage occurs when one or all segments of a key flexfield that have already been defined over a combinations table are redefined over a product table. In this way you can reuse a key flexfield definition over a transactional table as if it is a descriptive flexfield.

  • A code combination maintenance page

    The only purpose of a code combination maintenance page (often referred to as a combinations page) is to create and maintain code combinations. This page is typically built by the producer. The combinations table (or a view of it) is the base table of this page and contains all the key flexfield segment columns. The combinations table also contains a unique ID column. For information about creating a code combination maintenance page, see Section 25.2.6, "How to Build a Key Flexfield Maintenance User Interface."

    A typical application has one and only one combinations page. An application might not have a combinations page if it does not support maintenance mode for administrators.

  • A page containing a search form

    An advanced search form enables end users to define criteria to search for metadata in the application's master view object and its linked key flexfield view object. Users can select which attributes of the key flexfield view object to use as criteria. See Section 25.3.6, "How to Incorporate Key Flexfields Into a Query Search Form" for information about using key flexfields in a search form.

Note:

You cannot use a key flexfield in a tree table component.

In a typical application, you would have one combinations page that maintains the key flexfield, where the key flexfield is the representation of an entity in your application. You would also have one or more pages with foreign key references to the same key flexfield. For example, in an order entry/inventory application, you might have a combinations page where you define new parts with a key flexfield for the part numbers.

You would also have a page with a foreign key reference where you enter orders for parts, using the key flexfield to indicate what parts are included in the order. The page might also contain a key flexfield combination filter, which you use to determine the acceptable values of your part numbers. This combination filter references the same key flexfield as the combinations page and the foreign key page.

The order of key flexfield segments in the application user interface corresponds to the order in which they were defined in the key flexfield metadata. You cannot reliably change that order at runtime. The user interface dynamically reads the displayed attributes from the view object and displays them in the same order that they occur in the view object. There are no attribute UI hints that you can use to override this behavior.

Reordering key flexfield segments is not supported and can potentially create data integrity issues for code combinations, which are sequence aware. Because of this, it is important that you plan the segment order of your key flexfields in advance.

The tasks to employ a key flexfield on a page include:

  • Adding the key flexfield UI component to a form or a table

  • Defining a default value for every SIN attribute to prevent application errors when a new row that contains key flexfield columns is added on an application page

  • For ADF Form pages, adding code to ensure proper updating of reference mode and partial mode SIN values

  • For partial flexfield segments or segments on a combinations page, where the flexfield is in an ADF Table that is wrapped in an Applications Table component, adding functionality that dynamically refreshes the segment columns whenever the Applications Table component is refreshed by another component, such as a button or a search query.

25.3.4.1 Adding Key Flexfield UI Components to a Form or a Table

To incorporate a key flexfield into a UI form or table, you add the master view object to the page as a form or a table, and you drop the key flexfield view object onto the form or table.

Note that when the page creates a new row for the master view object, the value of the primary key of the row that references the key flexfield must be generated automatically, and this value cannot not be changed.

To add a key flexfield UI component to a form or a table: 

  1. Create the user interface for the master view object:

    In the Data Controls panel, select the master view object and drag it onto the page.

  2. When prompted, select the type of user interface that you want to create:

    • ADF Form

    • Applications > Panel

    • ADF Table

    • Applications > Table

    For an ADF Table, select the Row Selection option in the Edit Table Columns dialog.

  3. In the Data Controls panel, select the key flexfield view object and drag it onto the page. Drop the flexfield view object into a form or a table, and select the appropriate flexfield UI component. Figure 25-10 shows a key flexfield being dropped into a form.

    Figure 25-10 Key Flexfield Dropped into a Form

    Key flexfield dropped into a form
  4. Using either sequence generation or default values, ensure that when the end user adds a row to the page, a valid value is generated for the master view object's primary key before the row is created. Also ensure that the primary key cannot be entered or edited by the user.

    Caution:

    You cannot use the code combination ID as part of the generated primary key.

  5. If creating an editable table, select the table in the Structure window, expand the Behavior section of the Property Inspector and set the EditingMode attribute. If you want all the rows to be editable, select editAll. If you want the end user to click into a row to make it editable, select clickToEdit.

    If you select clickToEdit, the editable row displays the concatenated flexfield segment values in an input text component. The end user can click an icon that is next to the editable flexfield to open a dialog box that has an input field for each segment. The flexfield values in the non-editable rows are displayed as read-only values. The user can click the icon that is next to a read-only flexfield value to open a window that displays the segment labels, values, and descriptions.

25.3.4.2 Ensuring Proper Handling of New Rows

When a new row that contains key flexfield columns is added on an application page, every Structure Instance Number attribute must contain a valid value, so that the key flexfield user interface can be rendered with appropriate structures. Without default structures in reference mode, end users will not be able to select key combinations for the new row. Without default structures in partial mode, users will not be able to select segment values for the new row.

You can prevent application errors by defining a default value for each Structure Instance Number. Edit the foreign key entity object Structure Instance Number attribute or the foreign key view object Structure Instance Number attribute, and do one of the following:

  • If the Structure Instance Number is static, set the Value Type to Literal, and specify the static value as the default.

  • If the Structure Instance Number is dynamic, set the Value Type to Expression, and enter a Groovy expression to retrieve the appropriate Structure Instance Number value and set it as the default.

Note:

For partial flexfields in table components, you can use the defaultSIN attribute in the JSPX file to define the default Structure Instance Number value for situations where no rows exist.

For an Applications Table component in reference mode, the default Structure Instance Number and structure for a new or modified row is just a starting point. You can always allow for runtime selection of a new structure by LOV or input field.

Caution:

For key flexfield partial usages, the Structure Instance Number value of the first row of a user interface table that contains partial mode columns determines the partial mode column structure to be used for the table. For any additional row that contains a different Structure Instance Number, the partial mode columns that are not also part of the first row's structure will not render in the table. If there are no rows, the value of the defaultSIN tag attribute from the JSPX file, if set, determines the partial mode column structure.

For an Applications Table component, if the end user deletes all rows from the table, your application can again set a new default Structure Instance Number value for the first new row, and the partial mode column structure corresponding to that Structure Instance Number will be the valid structure for the table.

For an ADF Table, the partial mode column structure (determined by the initial Structure Instance Number value) cannot be changed after the table has been created, even if all rows are deleted.

For more information about setting attribute defaults, see the discussion about defining default values in the "Setting Attribute Properties" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

25.3.4.3 Ensuring Proper Updating of Reference Mode SIN values in an ADF Form or ADF Applications Table

It is best to not display the CCID or, if it is displayed, to make it a read-only field. When the end user clicks the popup icon, the popup requires the user to select a code combination whenever the SIN is changed for a row, and the CCID is set based on the user's selection.

If the CCID is not displayed in the user interface or if it is displayed as a read-only value, the developer must ensure that the CCID for the current row in the view object is set to null whenever the SIN is changed by the end user.

If the form does display the CCID in an editable mode, then the application must force the end user to set the CCID to null, change the SIN value, and then enter the CCID value in order for the concatenated value to be updated.

25.3.4.4 Ensuring Proper Updating of Partial Mode SIN Values in an ADF Form

When an end user makes changes to an existing ADF Form row that contains key flexfield partial usage attributes, it might result in the need for that row to use a different structure instance. The row's underlying view object retains the old SIN value, which produces a mismatch with the data and generates runtime errors. Your application must change the SIN value in the row so it uses the new structure instance. You add code to your page to ensure that this happens, as shown in Example 25-7.

Example 25-7 Code for Updating Modified SIN Values

//Add the partial target for the parent of the key flexfield partial usage
    AdfFacesContext.getCurrentInstance().addPartialTarget(pfl1);
 
//Get the handle of the child iterator binding. This is the same iterator 
//that you get when you drag the key flexfield partial usage onto a jspx page
    DCIteratorBinding childBinding = bindingControl.findIteratorBinding("Kff1PaInstanceIterator");
 
//Get the view object from the child iterator
    ViewObject childVO = childBinding.getViewObject();
 
//Get the current row from this view object
    ViewRowImpl childRow = (ViewRowImpl) childVO.getCurrentRow();
 
//Update the SIN in the child view object
    KFFPViewDefImpl childViewDef = (KFFPViewDefImpl)childRow.getViewDef();  
childRow.setAttribute((childViewDef.getStructureInstanceNumberAttribute()).getName(), sinValue);

Caution:

You cannot use this solution in an ADF Table or an Applications Table component. Dynamically changing the SIN at runtime is supported only for an ADF Form.

25.3.4.5 Dynamically Refreshing Partial Flexfield Segments and Segments on a Combinations Page

If you have segments on a combinations page or partial flexfield segments, and those segments are in an ADF Table that is wrapped in an Applications Table component that is refreshed by another component, such as a button or a search query, You must add functionality to dynamically refresh the segment columns.

To refresh the flexfield segments based on the current iterator rowset data, create a listener handler method in the flexfield's backing bean and bind the listener to the component that is initiating the table refresh. The listener must first call the default listener and then call DescriptiveFlexfield.updateFlexColumns(RichTable), where RichTable is the binding for the table that contains the flexfield.

Example 25-8 shows an example of a custom flexfield handler for a query event. The method first calls invokeMethodExpression to call the original query listener, and then calls updateFlexColumns with the table component that contains the flexfield as the parameter. Example 25-9 shows the binding of the custom flexfield handler to the query component.

Example 25-8 Flexfield Listener

public void customKffSearchQueryListener(QueryEvent queryEvent)
{
  invokeMethodExpression(
    "#{bindings.KffCriteriaQuery.processQuery}",
    Object.class,QueryEvent.class,queryEvent);
  DescriptiveFlexfield.updateFlexColumns(appTable);
}

Example 25-9 Binding the Flexfield Listener to the Search Query

<af:query id="qryId1"
  headerText="#{applcoreBundle.QUERY_SEARCH_HEADER_TEXT}"
  disclosed="true"
  value="#{bindings.criteriaQuery.queryDescriptor}"
  model="#{bindings.criteriaQuery.queryModel}"
  queryListener="#{backingBeanScope.dffBean.customKffSearchQueryListener}"
  queryOperationListener="#{bindings.KffCriteriaQuery.processQueryOperation}"
  resultComponentId="::AT2:_ATp:ATt2"/>

Note:

You do not need to handle flexfield refresh for standard Applications Table create and delete operations. However, custom create and delete operations must handle the refreshing of flexfields.

25.3.4.6 What Happens When You Add a Key Flexfield to a Page

Key flexfields are implemented in the user interface as code combination LOVs rather than as individual segments on the page. You can enter a combination code directly into the code combination LOV input.

Figure 25-11 shows an example of a key flexfield used in a form on an application page:

Figure 25-11 Example of a Key Flexfield In a Form

Example of a key flexfield in a form

Figure 25-12 shows an example of a key flexfield used in a table on an application page:

Figure 25-12 Example of a Key Flexfield in a Table

Example of a key flexfield in a table

Caution:

When your flexfield is in a table that is displayed within a popup, and the table's contentDelivery attribute is set to immediate, you must also set the popup's contentDelivery attribute to immediate to ensure that the key flexfield UI component renders in the table. For any other value of the popup's contentDelivery attribute, the flexfield column in the table will be blank.

For more information about tables and popups, see the "Using Tables and Trees" and "Using Popup Dialogs, Menus, and Windows" chapters of the Oracle Fusion Middleware Web User Interface Developer's Guide for Oracle Application Development Framework.

Key flexfield segments always appear as form fields or table columns in the same order that their corresponding attributes appear in the underlying view object.

In screenreader mode, a labeled icon of three horizontal bars appears next to the key flexfield input text field. When the end user clicks the icon, instead of the standard popup, a page displays that shows the segment details. The user clicks Done to return to the prior page.

For key flexfields in forms and in tables, you can click the search icon to select a valid new flexfield code combination using individual segment values as criteria, as shown in Figure 25-13.

Figure 25-13 Example of a Search for a Key Flexfield Code Combination

A search for a key flexfield code combination

Note:

You do not need to enter values for all segments when searching for a key flexfield code combination.

25.3.5 How to Configure Key Flexfield UI Components

You can configure various aspects of a key flexfield UI component to customize the behavior of the flexfield as a whole, or on a segment by segment basis. You can control your key flexfield's behavior in the application user interface by modifying properties at the flexfield level, at the segment label level, and at the partial usage level.

25.3.5.1 Configuring Flexfield-Level User Interface Properties

Right-click a key flexfield's UI component on the page, then select Properties from the context menu to view and modify its properties in the Property Inspector, as shown in Figure 25-14.

Figure 25-14 Key Flexfield Property Inspector — Common Tab

Key Flexfield Property Inspector - Common tab

The significant properties on the Common, Data, Style, Behavior and Other property tabs are listed in Table 25-6.

Table 25-6 Key Flexfield Properties

Tab > Property Description

Common > Id

This property gives the ID of the flexfield.

Common > Rendered

This property indicates whether the flexfield is rendered on the application page. Values can be True (default) or False.

When this property is set to False, the flexfield is not sent to the client.

EL expressions are allowed on ADF Form and ADF Table components. On ADF Table components, you use expressions to control this property on a row by row basis.

Common > Label

This property is the prompt that should be rendered on the page. Also used for the title of popup components. Note that if the Label property does not have a value, the title of a popup component defaults to Key Flexfield. Therefore, you must set this value to the name of the flexfield to ensure that popup components display the correct title, and not the default. The value should be applied from a resource bundle.

Common > Value

This property is the value of the flexfield. This should be an EL expression pointing to an iterator object. This field is also visible on the Data tab.

Data > Accessor

This property is the name of the accessor between the (consumer) product view object and the flexfield view object.

Style > StyleClass

This property is the style class of the flexfield. A style class allows you to group a set of inline styles.

Style > InlineStyle

This property is the inline style of the component. This is a string of CSS styles that can set individual properties such as background color, font style, or padding

Style > Width

This property is the width in characters of the text field in which the key flexfield value is displayed on the page. This value is 30 by default.

Behavior > Required

This property indicates whether the key flexfield must have a value. Values of this property can be True or False (default).

When this property is set to True, the page containing this key flexfield cannot be submitted unless the flexfield has a value.

EL expressions are allowed on ADF Form and ADF Table components. On ADF Table components, you use expressions to control this property on a row-by-row basis.

Behavior > ReadOnly

This property indicates whether the key flexfield is rendered as read-only. Values can be True or False (default).

When this property is set to True, the flexfield segment values are rendered, but they cannot be modified, and the associated popup or LOV lookup controls do not appear. Instead, an icon is displayed. When the mouse hovers over the icon, a window appears that displays the segment labels, values, and descriptions. This behavior overrides the Disabled property.

EL expressions are allowed on ADF Form and ADF Table components. On ADF Table components, you use expressions to control this property on a row-by-row basis.

Behavior > Disabled

This property indicates whether the UI control associated with this key flexfield can be operated. Values can be True or False (default).

When this property is set to True, the flexfield segment values and the associated popup or LOV lookup controls are rendered, but are dimmed and cannot be modified or operated. The ReadOnly property, when set to True, takes precedence over this property.

EL expressions are allowed on ADF Form and ADF Table components. On ADF Table components, you use expressions to control this property on a row-by-row basis.

Note that flexfield will be disabled if the current master row for the flexfield does not have a valid SIN value defined.

Behavior > PartialTriggers

This property contains the IDs of the components that should trigger a partial update in the flexfield (String[]).Foot 1  EL expressions are allowed.

Behavior > ValueChangeListener

This property is a method reference to a value change listener (javax.faces.el.MethodBinding). Requires an EL expression.

The value change listener takes effect if the value of the key flexfield is changed either manually in the key flexfield text field, or by using the key flexfield LOV popup.

Behavior > Binding

This property is an EL reference that will store the component instance on a bean (oracle.apps.fnd.applcore.flex.ui.KFFComp). This property requires an EL expression.

Other > AutoSubmit

This property indicates whether key flexfield values that are entered by the end user should automatically be submitted directly upon entry. Values can be True or False (default).

When this property is set to True, and the end user changes the value of the flexfield in the key flexfield text field or by using the key flexfield LOV popup, the new value will be submitted to the product view object immediately. When this property is set to False, the new value will be submitted only when the entire page is submitted.Footref 1

EL expressions are allowed on ADF Form and ADF Table components. On ADF Table components, you use expressions to control this property on a row-by-row basis.

Other > DefaultSIN

This property is for partial key flexfields only. It defines the default SIN value to use to define the structure when no rows exist.

Other > Changed

This property indicates whether the changed indicator icon is displayed on the component. Values can be True or False (default).

When this property is set to True, the changed indicator icon is displayed.

EL expressions are allowed on ADF Form and ADF Tables. On ADF Table components, you use expressions to control this property on a row-by-row basis.

Other > Simple

This property indicates whether the key flexfield's label should be hidden. Values of this property can be True or False (default).

When this property is set to True, the label is hidden. Note that if the Simple property is set to True and the flexfield is placed inside a PanelLabelAndMessage component, the flexfield might not align properly with the other components in the PanelLabelAndMessage component.

EL expressions are allowed on ADF Form and ADF Table components. On ADF Table components, you use expressions to control this property on a row-by-row basis.

Other > Visible

This property indicates whether the key flexfield appears on the page. Values can be True (default) or False.

When this property is set to False, the key flexfield is sent to the client but the client does not display it.

EL expressions are allowed on ADF Form and ADF Table components. On ADF Table components, you use expressions to control this property on a row-by-row basis.


Footnote 1 If you want changes in your key flexfield to trigger a partial update of another component, set the AutoSubmit UI property of the flexfield to True, and add the key flexfield ID to the PartialTriggers UI property of the other component. To ensure that the trigger works, you must append "CS" to the key flexfield ID. For example, if you want changes in the MyKeyFlex01 flexfield to trigger a partial update in another component, add "MyKeyFlex01CS" to that component's PartialTriggers property.

Note:

The Behavior > Mode property defines the user interface mode of the key flexfield component.

Only the single default value is supported, which renders the key flexfield as a single LOV.

25.3.5.2 Configuring Label-Based Segment UI Properties

Key flexfields support finer control of segments in the user interface based on their segment labels, using a number of additional properties that you can set in the flexfield XML with literal values or EL expressions. These properties are attributes of the flexfieldLabeledSegmentHint element.

Note:

This element can be used only to configure the segment UI properties of key flexfield partial usages, and only if a segment label is applied.

The following properties can be used to define usage specific behavior for one or more key flexfield segments, identified by segment label. These property settings apply to all segments that have the specified segment label assigned.

  • SegmentLabel: This string property specifies the segment label of the segment being configured. This string property is required.

  • Rendered: This boolean property indicates whether the segment is visible on the application page.

  • Required: This boolean property indicates whether the segment must have a value.

  • Readonly: This boolean property indicates whether end users can modify the segment.

  • Label: This string property provides a display label for the UI component.

  • ShortDesc: This string property provides a short description of the UI component. This text is commonly used by user agents to display tooltip help text, in which case the behavior for the tooltip is controlled by the user agent.

  • Columns: This integer specifies the width of the text control, in terms of the number of characters shown. The number of columns is estimated based on the default font size of the browser.

Note:

If you set a segment's required property to True in the flexfield metadata, for validation purposes you cannot override this by resetting it to False in the page metadata. You can, however, do the reverse: change a non-required segment to required in the page metadata.

The Label, ShortDesc and Columns properties are expected to apply to a single segment, so it is best to use them when only one segment has this segment label assigned.

The default values of these properties are derived from the flexfield metadata, but you can override them by inserting customization elements into the UI metadata.

For information about using EL expressions, see the "Creating ADF Data Binding EL Expressions" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

You apply these properties by dragging and dropping the following element from the Component Palette into the key flexfield element, as a child of the keyFlexfieldpartial element:

<fnd:flexfieldLabeledSegmentHint propertyname1="value" [propertyname2="value" [propertyname3="value" [propertyname4="value" [propertyname5="value" [propertyname6="value" [propertyname7="value"]]]]]]>

25.3.5.3 Configuring Partial Usage UI Properties

Key flexfields support finer control of partial usages in the user interface with a number of additional properties that you can set in the flexfield XML with literal values or EL expressions. These properties are attributes of the keyFlexfieldPartial element. By using EL expressions at the .jspx page level, you can programmatically override the key flexfield metadata at runtime.

For example, Oracle Assets has a single page that is used for both the Create Asset and Update Asset activities. When creating an asset, the Asset Category key flexfield on this page should be updatable; when updating an asset, the flexfield should be read-only. This setting can be programmatically managed using the readonly property based on a page parameter that indicates whether the page is in Create mode or Update mode.

The following boolean properties can be used to specify usage specific behavior for the entire key flexfield partial usage:

  • rendered: Indicates whether the flexfield is visible on the application page.

  • required: Indicates whether the flexfield must have a value.

  • readonly: Indicates whether end users can modify the flexfield.

The default values of these properties are derived from the flexfield metadata, but you can override them by inserting customization elements into the UI metadata.

Note:

If you set a segment's required property to True in the flexfield metadata, for validation purposes you cannot override this by resetting it to False in the page metadata. You can, however, do the reverse: change a non-required segment to required in the page metadata.

For information about using EL expressions, see the "Creating ADF Data Binding EL Expressions" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

You apply these properties by dragging and dropping the following element from the Component Palette into the key flexfield element:

<fnd:keyFlexfieldPartial propertyname1="value" [propertyname2="value" [propertyname3="value"]]>

25.3.6 How to Incorporate Key Flexfields Into a Query Search Form

In reference mode, you can include key flexfield view object attributes as search criteria in an advanced mode query search form. This form enables end users to define extemporaneously the criteria to search for metadata in the foreign key view object and its linked key flexfield view object. Users can select which attributes of the key flexfield view object to use as search criteria.

To incorporate key flexfields into a query search form:

  1. Set up the business component model layer.

  2. Create the query search form.

25.3.6.1 Setting Up the Business Component Model Layer

To set up the business component model layer for the search form, you define the view criteria in the foreign key view object, generate the row implementation class for the foreign key view object, and override the getCriteriaItemClause() method in that row implementation class.

For more information about defining view criteria, see the "Defining SQL Queries Using View Objects" chapter of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

Before you begin: 

  1. Create a view link between your application's foreign key view object and the key flexfield polymorphic view object as described in Section 25.3.1, "How to Create Key Flexfield View Links."

  2. Add the key flexfield's view instance to the product application module as described in Section 25.3.3, "How to Add an Instance of a Key Flexfield View Object to the Product Application Module."

  3. Nest the key flexfield application module instance in the product application module, as described in Section 25.3.2, "How to Nest an Instance of the Key Flexfield Application Module in the Product Application Module."

  4. Ensure that the discriminator attribute in the foreign key view object, such as the Sin attribute, is enabled to display a list of values. You can find this information on the Attributes navigator tab for the view object. Also, ensure that the Auto Submit property for the discriminator attribute is set to true in Control Hints.

    For information about enabling a list of values for an attribute and setting control hints, see the "Defining SQL Queries Using View Objects" chapter of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

To set up the business component model layer: 

  1. In the Application Navigator, double-click the foreign key view object.

  2. In the overview editor, click the Query navigation tab.

  3. In the View Criteria section, click the Add icon.

  4. In the Create View Criteria dialog, enter the name of the view criteria to identify its usage in your application.

  5. Click Add Group, and click Add Item.

  6. Select the discriminator, such as Sin, from the Attribute dropdown list.

  7. Accept the default values of Equal to for the Operator and Literal for the Operand.

  8. Select the Group node that you just added, and click Add Item.

  9. From the Attribute dropdown list, select the view accessor for the view link between your application's foreign key view object and the key flexfield view object.

  10. Accept the default Exists value for Operator and Inline View Criteria for Operand.

  11. Select the bottom node in the View Criteria tree as shown in Figure 25-15.

    Figure 25-15 Bottom Node in View Criteria Tree

    Bottom node selected in view criteria tree
  12. Select the attribute that corresponds to the discriminator, such as _STRUCTURE_INSTANCE_NUMBER, from the Attribute dropdown list.

  13. Accept the default values of Equal to for the Operator and Literal for the Operand.

  14. Click OK.

  15. Click the Java navigation tab.

  16. If the Java Classes section does not display a view object class, click the Edit icon to generate and configure Java implementation classes, and complete the following steps:

    1. In the Select Java Options dialog, select Generate View Object Class.

    2. Optionally select Include bind variable accessors, Include custom java data source methods, or both.

    3. Click OK.

  17. In the Java navigation tab, click the path shown for the View Object Class to open it in the source editor. This is the class that ends with VOImpl.

  18. In the source editor, override the getCriteriaItemClause() method from the oracle.jbo.ViewCriteriaItem package as shown in Example 25-10.

    Set VIEW_CRITERIA_NAME to the name of the view criteria that you just created, and set KFF_ACCESSOR_NAME to the view accessor from the view link between the foreign key view object and the key flexfield polymorphic view object.

    Note:

    If the foreign key view object contains more than one key flexfield, the getCriteriaItemClause() method must call getCriteriaItemClauseWhenKffExposedinQueryPanel() for each key flexfield, passing the appropriate criteria name and KFF Accessor Name.

    Example 25-10 getCriteriaItemClause() Method

    private static String VIEW_CRITERIA_NAME="PartsKFFQueryCriteria";
    private static String KFF_ACCESSOR_NAME="PartsAcctKffKffVL";
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem vci) {
     
      String returnString;
      returnString = null;
      returnString =
        this.getCriteriaItemClauseWhenKffExposedinQueryPanel(
          vci, VIEW_CRITERIA_NAME, KFF_ACCESSOR_NAME);
     
          return returnString;
    }
    
  19. Save your changes.

25.3.6.2 Creating the Query Search Form

To create a query search form that contains a key flexfield, add an ADF Query Panel with Table component to the page and drop the key flexfield into the table. Then, create a custom bean and attach the bean to the query.

For more information about working with search forms, see the "Creating ADF Databound Search Forms" chapter in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

Before you begin: 

  1. Prepare the business model component as described in Section 25.3.6.1, "Setting Up the Business Component Model Layer."

  2. If you are working with partial flexfield segments and the flexfield is an ADF Table that is wrapped in an Applications Table component, review Section 25.3.4.2, "Ensuring Proper Handling of New Rows" and Section 25.3.4.5, "Dynamically Refreshing Partial Flexfield Segments and Segments on a Combinations Page."

To create the query search form: 

  1. Open the JSPX page to which you want to add the search form.

  2. From the Data Controls panel, select the foreign key view object's data collection and expand the Named Criteria node to display a list of named view criteria.

  3. Drag the view criteria that you created in Section 25.3.6.1, "Setting Up the Business Component Model Layer" and drop it onto the page or onto the Structure window.

  4. From the context menu, choose Query > ADF Query Panel with Table, as shown in Figure 25-16.

    Figure 25-16 Query Context Menu

    Query Context Menu with ADF Query Panel choice
  5. In the Edit Table Columns dialog, you can rearrange any column and select table options.

  6. In the Data Controls panel, select the key flexfield view object, drop it into the table, and choose Create > Oracle Key Flexfield Column to add the key flexfield to the table, as shown in Example 25-10.

    Figure 25-17 Key Flexfield Column Added to Table

    Query form with key flexfield column
  7. In the user interface project, create a custom bean that implements oracle.adf.view.rich.event.QueryOperationListener as shown in Example 25-11.

    Set VIEW_CRITERIA_NAME to the name of the view criteria, and set KFF_ACCESSOR_NAME to the view accessor from the view link between the foreign key view object and the key flexfield view object.

    Example 25-11 Custom Listener Java Class

    package oracle.apps.fnd.applcore.flex.test.backing;
     
    import java.util.List;
     
    import javax.faces.event.AbortProcessingException;
     
    import oracle.adf.model.BindingContext;
    import oracle.adf.model.binding.DCBindingContainer;
    import oracle.adf.view.rich.event.QueryOperationEvent;
    import oracle.adf.view.rich.event.QueryOperationListener;
    import oracle.adf.view.rich.model.AttributeCriterion;
    import oracle.adf.view.rich.model.Criterion;
    import oracle.adf.view.rich.model.QueryDescriptor;
     
    import oracle.jbo.uicli.binding.JUFormBinding;
     
    import oracle.jbo.ViewCriteria;
    import oracle.jbo.ViewCriteriaItem;
    import oracle.jbo.ViewCriteriaRow;
    import oracle.jbo.common.ViewCriteriaItemImpl;
    import oracle.jbo.uicli.binding.JUSearchBindingCustomizer;
     
    public class CustomBean implements QueryOperationListener{
        public CustomBean() {
            super();
        }
        
        private static final String BINDING_SUFFIX="Query";
        private static String VIEW_CRITERIA_NAME="PartsKFFQueryCriteria";
        private static String KFF_ACCCESSOR_NAME="PartsAcctKffKffVL";
        private static String KFF_DISCRIMINATOR_ATTR_NAME=
          "_STRUCTURE_INSTANCE_NUMBER";
        private static String MASTER_VO_ATTR_FROM_WHICH_KFF_DISC_DERIVED="Sin";
        
        public void processQueryOperation(QueryOperationEvent queryOperationEvent)
           throws AbortProcessingException{
           QueryDescriptor descriptor = 
             (QueryDescriptor) queryOperationEvent.getDescriptor();
           AttributeCriterion  attr = queryOperationEvent.getAttributeCriterion();
           if (queryOperationEvent.getOperation()==
             QueryOperationEvent.Operation.CRITERION_UPDATE){
              
              BindingContext bcx= BindingContext.getCurrent();
              DCBindingContainer bc = 
                (DCBindingContainer) bcx.getCurrentBindingsEntry();
              JUFormBinding bnd =
                     (JUFormBinding) 
                     bc.findExecutableBinding(VIEW_CRITERIA_NAME+BINDING_SUFFIX);
              if(bnd!=null){
                  String vcName = JUSearchBindingCustomizer.getCriteriaName(bnd);
                  applyDiscriminator(bnd, vcName);
              }      
           }
        }
     
         public void applyDiscriminator(JUFormBinding ctr, String vcName){
             ViewCriteria vc = JUSearchBindingCustomizer.getViewCriteria(ctr, vcName);
             ViewCriteriaRow r = (ViewCriteriaRow) vc.getCurrentRow();
             List<ViewCriteriaItem> criteriaItemList = r.getCriteriaItems();
             Object proxyval = null;
             for(ViewCriteriaItem item : criteriaItemList){
                   
                  if (item.getName().equals(
                     MASTER_VO_ATTR_FROM_WHICH_KFF_DISC_DERIVED)){
                     proxyval = item.getValue();
                  }
     
                  if (item instanceof ViewCriteriaItem){
     
                    ViewCriteriaItemImpl itemimpl = (ViewCriteriaItemImpl) item;
                    if(itemimpl.getName().equals(KFF_ACCCESSOR_NAME)){
                          ViewCriteria nvc = itemimpl.getNestedViewCriteria();
     
                          ViewCriteriaRow nvcr = 
                           (ViewCriteriaRow) nvc.getCurrentRow();
                          List<ViewCriteriaItem> ncriteriaItemsList =
                            nvcr.getCriteriaItems();
                          for(ViewCriteriaItem nitem: ncriteriaItemsList){
                            if (nitem.getName().equals(KFF_DISCRIMINATOR_ATTR_NAME))
                               nitem.setValue(proxyval);   
                          }  
                    }
     
                  }//if instanceof
     
             }//end for
         }
        
    }
    

    This custom bean will be triggered when a value is selected from the LOV component for the discriminator attribute, such as the LOV for the SIN attribute. When invoked, the processQueryOperation() method is called. The JUFormBinding that is associated with the view criteria is accessed to extract the view criteria.

    The applyDiscriminator() method extracts the ViewCriteriaItem for the discriminator attribute, gets the value that was selected from the discriminator's LOV component, and loads into the query panel the key flexfield's subtypes with a matching discriminator value.

  8. Complete the following steps to attach the custom bean to the query.

    1. Open the JSPX page.

    2. In the Structure window, select af:query.

    3. In the Property Inspector, expand the Behavior section.

    4. In the QueryOperationListener field, enter an EL expression that resolves to the custom bean's processQueryOperation() method, such as #{CustomBean.processQueryOperation}.

25.4 Using Key Flexfield Advanced Features in Reference Mode

Key flexfield advanced features include code combination constraints, programmatic access to segment labels, making key flexfields available for use in Oracle Business Intelligence, and working with flexfields from a worksheet using ADF Desktop Integration.

25.4.1 How to Define Code Combination Constraints

Code combination constraints are criteria for filtering the list of code combinations that can be referenced in a given combinations table. While the set of code combinations in the table is not changed, each table with foreign key references to these code combinations can have its own associated code combination constraints.

For example, the key flexfield MTL_SYSTEM_ITEMS has a Purchasable flag, which can be set to the value Y or N. You can implement an extra WHERE clause on the Oracle Purchasing product view object that enables Order Management to restrict the displayed items to only those with Purchasable set to Y.

Code combination constraints are applied in the following situations:

  • When an end user launches a key flexfield pop-up window to search for a code combination.

  • When the code-combination ID attribute of a foreign key view object is set programmatically.

Code combination constraints are not applied when an existing foreign key reference to a code combination is resolved into individual segments (or a concatenated string) for display.

Combination constraints are view object properties and are not applied on any entity objects.

You create a view accessor to define a code combination constraint. You can define the following types of code combination constraints:

  • Extra WHERE clause

  • Validation date

  • Validation rules

  • Dynamic combination creation allowed

25.4.1.1 Creating a View Accessor to Define a Code Combination Constraint

In a key flexfield ADF Business Components model, the code combination constraints are defined in a view object (the foreign key view object) that references the code combinations. Although these constraints are, in a way, validation rules for code combinations, they are not ADF Business Components validators.

You define code combination constraints as bind parameter values in a view accessor. The name of this view accessor is derived from the name of the view link accessor to the key flexfield view object for which you want to constrain code combinations.

To define a code combination constraint: 

  1. Open the foreign key view object that references the code combinations.

  2. Click the Add icon in the View Accessors section to display the View Accessors dialog.

  3. Create a view accessor to the base key flexfield view object, as shown in Figure 25-18.

    Figure 25-18 Code Combination Constraint View Accessor

    Code combination constraint view accessor

    The destination of the view accessor is the base key flexfield view object. The name of the view accessor must be derived from the name of the view link accessor to the key flexfield view object, and must take the following form:

    viewlinkaccessornameConstraints
    

    For example, for a view link accessor named AcctKff, Figure 25-18 shows the accessor name AcctKffConstraints.

  4. Edit the view accessor to define the bind parameter values, as shown in Figure 25-19.

    Note:

    You do not need to select any view criteria for this activity. Only the bind parameter values are needed to define a code combination constraint.

    If you do not see any bind parameters, it is likely that you have just re-created the business components and overwritten the old ones. You can close the application and remove it from JDeveloper. When you open the application again, JDeveloper should load the latest definitions, including the bind parameters.

    Figure 25-19 Code Combination Constraint Bind Parameter Values

    Code combination constraint bind values

    There are four types of code combination constraints. To apply a constraint type, provide a value for the appropriate bind parameter for the constraint type as shown in the following list. If no value is provided, that constraint type is not enabled.

    Edit only the bind parameters that you need, and leave the others blank. You can use Groovy expressions as bind-parameter values. This means that the constraints can come indirectly from a view attribute, the view object, or a Java method.

    Note:

    You can ignore the Row-level bind values exist option, because the frequency of evaluation of bind parameters is predetermined, as follows:

    • View object level (evaluated once)

      • Bind_ExtraWhereClause

      • Bind_ValidationRules

    • Row level (evaluated every time)

      • BindVar0 through BindVar9

      • Bind_ValidationDate

      • Bind_DynamicCombinationCreationAllowed

25.4.1.2 Constraining Code Combinations by an Extra WHERE Clause

You can use the view accessor's Bind_ExtraWhereClause parameter to filter the list of code combinations that can be referenced in a given combinations table. The extra WHERE clause is appended to the existing WHERE clause of the key flexfield view object.

To set the Bind_ExtraWhereClause parameter 

  1. Create the view accessor and open for editing as described in Section 25.4.1, "How to Define Code Combination Constraints."

  2. In the Edit View Accessor dialog, set the Bind_ExtraWhereClause value.

    The extra WHERE clause can use bind parameters. The value of Bind_ExtraWhereClause should be a SQL fragment that may contain references to columns of the combinations table, or the predefined bind parameters.

    To refer to the combinations table, use ${COMBINATION_TABLE}]; for example, ${COMBINATION_TABLE}.MY_COLUMN.

    To refer to one of the ten pre-defined bind parameters, BindVar0 to BindVar9, use a colon and the bind parameter name; for example, :BindVar3.

    Following is an example of an extra WHERE clause code combination constraint as a SQL expression:

    (:BindVar0 IS NULL) OR (${COMBINATION_TABLE}.MY_COLUMN = :BindVar0)
    

    You can also express this as a Groovy string constant. Be sure to escape the dollar sign with a backslash:

    "(:BindVar0 IS NULL) OR (\${COMBINATION_TABLE}.MY_COLUMN = :BindVar0)"
    

25.4.1.3 Constraining Code Combinations by Validation Date

You can use the view accessor's Bind_ValidationDate parameter to filter the list of code combinations that can be referenced in a given combinations table. If you provide a value for Bind_ValidationDate, this validation date is used instead of the current database date when searching for a code combination. The code combinations returned are those that are active on the specified date.

If a code combination's start_date_active attribute is NULL, it is considered to have always been active in the past, up to its end_date_active date. If a code combination's end_date_active attribute is NULL, it is considered to be active starting from its start_date_active date indefinitely into the future.

Note:

Note that a date constraint is always required when searching for a code combination. If you don't supply a validation date, the current database date will be used.

To set the Bind_ValidationDate parameter 

  1. Create the view accessor and open for editing as described in Section 25.4.1, "How to Define Code Combination Constraints."

  2. In the Edit View Accessor dialog, set the Bind_ValidationDate value.

    The value of Bind_ValidationDate should be a normalized java.sql.Date object; that is, the hour, minute, second and millisecond should be set to zero. You can use the method oracle.apps.fnd.applcore.oaext.model.OAUtility#getSQLDate to normalize the date.

    One way to construct a normalized date for testing purposes is to use java.sql.Date.valueOf(String s) with the date as a literal string in the form yyyy-mm-dd.

    In a search user interface, the supplied validation date also affects the list of values of a segment. For example, the end user may pick a value for a segment from a list of values, then use the segment value to search for a code combination. The list of values of the segment will be constrained by the supplied validation date.

25.4.1.4 Constraining Code Combinations by Validation Rules

You use validation rules to constrain code combinations. The validation rules for a given key flexfield are authored by the product team that owns the flexfield. They are valid only for use as code combination constraints, and should not be confused with other types of validation rules. Validation rules are stored in the flexfield metadata table FND_KF_VRULES and are delivered in the loader file along with the key flexfield definition. The rule authors are your best source of information about the applicability of the validation rules for a flexfield, and the rule codes you should use to reference them.

You can use the view accessor's Bind_ValidationRules parameter to filter the list of code combinations that can be referenced in a given combinations table. If you provide a value for Bind_ValidationRules, the validation rules are translated into a SQL fragment, and the SQL fragment is appended to the WHERE clause of the key flexfield view object.

Note:

Because key flexfield partial usages do not include a code combination, and validation rule constraints currently apply only to code combinations, they do not apply in the case of key flexfield partial usages.

25.4.1.4.1 How to Create Validation Rules

Use the create_vrule(...) procedure from the FND_FLEX_KF_SETUP_APIS PL/SQL package to register a flexfield segment's validation rule. Validation rules only apply to segments that are validated against a list-validated value set. If the segment is validated against a format-only value set, the validation rules are ignored.

Note that when a segment is labeled with multiple segment labels, its validation rules are joined with an AND in the WHERE clause.

When the ALWAYS_APPLIED_FLAG is set to Y, the validation rule is always applied, such as when a combination is validated by C or PL/SQL validation APIs or a combination is validated by a business component. When the ALWAYS_APPLIED_FLAG is set to N, the validation rule is applied only when the rule is included in the list of validation rules as an argument to C or PL/SQL validation APIs or as a Bind_ValidationRules parameter as described in Section 25.4.1.4.2, "How to Set the Bind_ValidationRules Parameter."

Because the names of the segment columns that the customer will use for the code combinations are not known during development, you must use the lexical references listed in Table 25-7 to refer to the segment column and value attributes in the rule's WHERE clause. In addition to the lexical references, the FLEXFIELD.VALIDATION_DATE bind variable can be used in validation rule WHERE clauses. No other flexfield bind variables can be used.

Table 25-7 Lexical References

Lexical Type Lexical Code Example Notes

VALUE

VALUE

&{VALUE.VALUE}

Represents the VALUE column in segment lists of values and represents the segment column in combination lists of values.

VALUE_ATTRIBUTE

value attribute code

&{VALUE_ATTRIBUTE.GL_ACCOUNT_TYPE}

Represents the value table value attribute column in segment lists of values and represents the combination table value attribute column in combination lists of values.


For example, if the following WHERE clause was registered as a validation rule for a segment, the derived SQL query to retrieve the segment's list of values would be similar to Example 25-12, and the derived SQL query to retrieve the combination list of values would be similar to Example 25-13.

GL_AFF_AWC_API_PKG.gl_valid_flex_values(
  :{FLEXFIELD.VALIDATION_DATE}, &{VALUE.VALUE}) = 'Y')

Example 25-12 SQL Query to Retrieve Segment's List of Values

SELECT ...
FROM fnd_vs_values_vl fvvv
WHERE fvvv.value_set_id = :Bind_ValueSetId
AND fvvv.value like :Bind_Value
AND (GL_AFF_AWC_API_PKG.gl_valid_flex_values(
  :Bind_ValidationDate, fvvv.value) = 'Y'))

Example 25-13 SQL Query to Retrieve Combination List of Values

SELECT ...
FROM gl_code_combinations glcc
WHERE glcc.chart_of_accounts_id = :Bind_SIN
AND ...
AND 
(GL_AFF_AWC_API_PKG.gl_valid_flex_values(
  :Bind_ValidationDate, glcc.segment5) = 'Y'))

Tip:

To learn how to access documentation about using the FND_FLEX_KF_SETUP_APIS PL/SQL package, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

25.4.1.4.2 How to Set the Bind_ValidationRules Parameter

Edit the view accessor's Bind_ValidationRules parameter to specify the validation rules to be applied to constrain the code combination filters.

To set the Bind_ValidationRules Parameter:

  1. Create the view accessor and open for editing as described in Section 25.4.1, "How to Define Code Combination Constraints."

  2. In the Edit View Accessor dialog, set the Bind_ValidationRules value.

    The value of Bind_ValidationRules should be a semicolon-separated list of rule codes; for example:

    VALIDATION_RULE1;VALIDATION_RULE2
    

    The validation rules are pre-defined as part of the key flexfield definition. The supplied list is the list of rules that need to be applied when searching for a code combination.

    Note the following caveats when constructing this list:

    • The validation rule codes are case sensitive.

    • Space characters are preserved. For example, "VRULE1; VRULE2" will be parsed into "VRULE1" and " VRULE2" (with a leading space).

    • Unrecognized and unused rules are discarded silently. For example, if you have a validation rule for an optional label, and the label has not been assigned yet in the current flexfield definition, the rule will be ignored at runtime.

    In a search user interface, the supplied validation rules also affect the list of values of a segment. For example, the end user may pick a value for a segment from a list of values, then use the segment value to search for a code combination. The list of values of the segment will be constrained by the supplied validation rules.

25.4.1.5 Enabling or Disabling Dynamic Combination Creation for a Specific Usage

You can use the Bind_DynamicCombinationCreationAllowed parameter to control the runtime entry of new code combinations for a key flexfield usage. This constraint type takes effect only when the key flexfield allows dynamic combination insertion. For more information, see Section 25.2.4.2, "Enabling Dynamic Combination Insertion".

To set the Bind_DynamicCombinationCreationAllowed parameter 

  1. Create the view accessor and open for editing as described in Section 25.4.1, "How to Define Code Combination Constraints."

  2. In the Edit View Accessor dialog, set the Bind_DynamicCombinationCreationAllowed value.

    The value of Bind_DynamicCombinationCreationAllowed can be TRUE, FALSE or null.

    By setting this value to TRUE or FALSE, you can control whether your specific usage of the key flexfield allows dynamic insertion even though the key flexfield as a whole is enabled for dynamic insertion. Set the value to TRUE if you want your usage of the key flexfield to allow dynamic insertion. Set the value to FALSE if you do not want your usage of the key flexfield to allow dynamic insertion. Set the value to null to indicate that the key flexfield itself should determine whether dynamic combination insertion is allowed or not.

    If the key flexfield does not allow dynamic combination insertion, this constraint is ignored. Bind_DynamicCombinationCreationAllowed is a row-level bind parameter.

25.4.2 How to Access Segment Labels Using the Java API

Segment labels (previously known as key flexfield qualifiers) that have been assigned to segments by customers can be accessed programmatically. The information can be access using the flexfield application module or the flexfield view row.

Example 25-14 is an example of Java code that retrieves segment label information from a deployed flexfield using the flexfield application module.

Example 25-14 Retrieving Segment Label Information Using the Flexfield Application Module

// First find the flexfield application module:
FlexfieldApplicationModuleImpl flexAM = (FlexfieldApplicationModuleImpl)
  rootAM.findApplicationModule("Kff1nAM1");
 
// Find the attributes labeled as "SEGMENT_LABEL_G1" in structure
// "VS_FRM_CHR_ON_CHR".
// If you wish to use a structure instance number, you need to further
// cast the application module into KFFApplicationModuleImpl and call
// getStructureCode(long) to find the code.
// For example,
// KFFApplicationModuleImpl kffAM = (KFFApplicationModuleImpl) flexAM;
// String code = kffAM.getStructureCode(12345);
List<AttributeDef> attrs = flexAM.getLabeledAttributes("VS_FRM_CHR_ON_CHR",
                                                       "SEGMENT_LABEL_G1");
for (AttributeDef attr: attrs)
{
  System.out.println(attr.getName());
 
  // You can get the segment code through a static method.
  System.out.println(FlexfieldViewDefImpl.getSegmentCode(attr));
 
  // If you somehow need to construct a WHERE clause using this attribute,
  // this is the identifier you should use.
  System.out.println(attr.getColumnNameForQuery());
 
  // You can find the "column name" defined in the entity.  The column name
  // is typically the database column name.
  System.out.println(attr.getColumnName());
}

Example 25-15 is an example of Java code that retrieves segment label information from a deployed flexfield using the flexfield view row.

Example 25-15 Retrieving Segment Label Information Using the View Row

// This is just for illustration.  In a real application, the 
// flexfield view row should be retrieved through the view link accessor.
ViewObject vo = rootAM.findViewObject("Kff1nAM1.DefaultFlexViewUsage");
vo.executeQuery();
while (vo.hasNext())
{
  FlexfieldViewRowImpl row = (FlexfieldViewRowImpl) vo.next();
  // Given a KFF view row, you can find the labeled attributes through
  // the view def. An empty list is returned if the given label is not used
  // in the row.
  List<AttributeDef> labeledAttrs =
    row.getFlexfieldViewDef().getLabeledAttributes("SEGMENT_LABEL_RU1");
  for (AttributeDef attr: labeledAttrs)
  {
    System.out.print(attr.getName() + "=" + 
      row.getAttribute(attr.getName()) + ";");
  }
  System.out.println();
}

For more information about segment labels, see Section 25.2.2, "How to Implement Key Flexfield Segment Labels." For more information about the Java API, refer to the Javadoc.

25.4.3 How to Prepare Key Flexfield Business Components for Oracle Business Intelligence

Oracle Business Intelligence is a comprehensive collection of enterprise business intelligence functionality that provides the full range of business intelligence capabilities including interactive dashboards, full ad hoc, proactive intelligence and alerts, enterprise and financial reporting, real-time predictive intelligence, and more.

While key flexfields are modeled using polymorphic view objects, flexfield technology is not compatible with Oracle Business Intelligence, which also requires reference data, such as lookups, to be modeled as view-linked child view objects. For a key flexfield to be used by Oracle Business Intelligence, it must be flattened into a usable static form. To make this possible you must designate the flexfield as business intelligence–enabled. When you create business components for this key flexfield, the business component modeler recognizes the business intelligence–enabled setting, and a view object that is flattened for business intelligence (BI) is generated alongside the standard key flexfield polymorphic view object. You must also slightly modify the process of creating key flexfield view links and application modules.

When the business intelligence–enabled and flattened key flexfield is configured as part of an application, the implementor or administrator can select which individual flexfield segments to make available for use with Oracle Business Intelligence.

25.4.3.1 Enabling a Key Flexfield for Oracle Business Intelligence

If you want customers to be able to do business intelligence queries on whatever segments they configure for a flexfield, you must enable the flexfield and its segments.

You can set the business intelligence–enabled flag at registration time using the fnd_flex_kf_setup_apis.create_flexfield(...) procedure, or you can set the flag later using the fnd_flex_kf_setup_apis.update_flexfield(...) procedure. To learn how to access documentation about using these procedures, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

You can enable the segments for business intelligence using the Manage Key Flexfields task, which is accessed from the Oracle Fusion Applications Setup and Maintenance work area.

An alternative method to business intelligence–enable a key flexfield and its segments is to set the BIEnabledFlag to Y at both the key flexfield level and the segment level in the key flexfield seed data file (SDF), as shown in Example 25-16.

Example 25-16 BI Enabled Key Flexfield

<KeyFlexfield>
    <ApplicationId>0</ApplicationId>
    <KeyFlexfieldCode>KFF1</KeyFlexfieldCode>
    ...
    <TreeStructureCode>FND_FLEX_TEST_TREE_STRUCTURE1</TreeStructureCode>
    <BIEnabledFlag>Y</BIEnabledFlag>
    ...
    <StructureInstance>
      ...
      <SegmentInstance>
        <SegmentCode>L10_ZEROFILL</SegmentCode>
        ...
        <DefaultValue isNull="true"></DefaultValue>
        <BIEnabledFlag>Y</BIEnabledFlag>
        ...
      </SegmentInstance>
      ...
    <StructureInstance>
    ...
  </KeyFlexfield>

25.4.3.2 Producing a Business Intelligence–Enabled Flattened Key Flexfield Model

When you create business components for a business intelligence–enabled key flexfield, the business component modeler recognizes the business intelligence–enabled setting, and a view object that is flattened for Oracle Business Intelligence is generated alongside the standard key flexfield polymorphic view object. You must also slightly modify the process of creating key flexfield view links and application modules.

Note:

When you make changes to a business intelligence-enabled flexfield, use the Import Metadata Wizard to import the changes into the Oracle Business Intelligence repository as described in the "Using Incremental Import to Propagate Flex Object Changes" section in the Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition) (Oracle Fusion Applications Edition).

Before you begin:

  1. Enable the flexfield and the desired segments for Oracle Business Intelligence as described in Section 25.4.3.2, "Producing a Business Intelligence–Enabled Flattened Key Flexfield Model."

  2. If your flexfield will use hierarchical (tree structured) value sets, create column-flattened versions of the affected view objects and import them into your project before continuing.

    If the flattened tree view objects are not in your project, the Create Flexfield Business Components wizard will report the missing view objects as errors.

    For more information, see Section 60.8.1, "Designing a Column-Flattened View Object for Oracle Business Intelligence."

To produce a business intelligence–enabled flattened key flexfield model:

  1. Create key flexfield business components as described in Section 25.2.4, "How to Create Key Flexfield Business Components."

    When a flexfield is business intelligence–enabled, the Create Flexfield Business Components wizard generates a business intelligence–specific view object and other business components under a directory called analytics in the package root directory. These are generated in addition to the typical key flexfield view object.

  2. Create a view link using the procedure described in Section 25.3.1, "How to Create Key Flexfield View Links." Keep the following in mind:

    • The master view object that you create with the standard wizard can be the same master view object that you create for the core key flexfield model.

    • Create the view link from the master view object to the business intelligence–enabled flexfield base view object. The business intelligence–enabled flexfield is distinguished from the core flexfield by the prefix "BI:" as shown in Figure 25-20.

      Figure 25-20 Create Flexfield View Link Wizard — View Objects Page

      View Link wizard - View Objects page
  3. Create an application module for use with Oracle Business Intelligence, as described in Section 25.2.4.1.3, "How to Create the Maintenance Application Module." Make the following changes:

    1. On the Data Model page of the Create Application Module wizard, when you create an instance of the master view object, there is no need for a child view object.

    2. On the Application Modules page of the wizard, add an instance of the key flexfield Oracle Business Intelligence application module as a nested instance of this application module. You can identify the Oracle Business Intelligence application module by the analytics subpackage under the package root.

    Note:

    If you already have a product Oracle Business Intelligence application module, you may use it.

  4. Define the custom properties required to link the master view object instance to the default view instance.

    This is done on the General tab of the nested business intelligence–enabled flexfield application module instance definition, as shown in Figure 25-21.

    Figure 25-21 Custom Properties for Business Intelligence–Enabled Application Module

    Business intelligence application module custom properties

    As you do this, keep the following points in mind:

    • The default view instance inside the business intelligence–enabled flexfield application module is typically called DefaultFlexViewUsage.

    • The custom property names should be formatted as BI_VIEW_LINK_mypropertyname.

    • The custom property values should be formatted as source_viewobjectinstance_name, viewlink_definition_name, destination_viewobjectinstance_name.

    • Use the fully qualified view object instance names for the source view object and destination view object, and the fully qualified package name for the view link definition.

    • Business intelligence joins between the view object instances you specify in different application modules are created during import from Oracle ADF.

25.4.4 How to Publish Key Flexfield Application Modules as Web Services

You can make access to a key flexfield available through web services, which will enable you to perform create, read, update, and delete (CRUD) operations on the flexfield data rows. You accomplish this by exposing a key flexfield application module as a web service and adding flexfield service data object support utility methods to the product application module.

When you generate a flexfield business component, the key flexfield business component and other artifacts are developed based on the information in the flexfield metadata. As illustrated in Figure 25-2, a base view object is created for the context and global segments. If any contexts have been configured, subtype view objects are generated for each configured context.

As an example, suppose that an application module has the master view object Fkt1 and a view link from the master view object to the detailed key flexfield view object, Global1, which is a polymorphic view object.

The Business Component Browser view shown in Figure 25-22 corresponds to a particular row in the master view object, displaying the segment structure in the key flexfield with SIN of 11.

Figure 25-22 Business Component Browser View of Flexfield Row with SIN = 11

AM Tester view of flexfield row with SIN = 11

The Business Component Browser view shown in Figure 25-23 corresponds to a different row in the master view object, displaying the segment structure in the key flexfield with SIN of 25.

Figure 25-23 Business Component Browser View of Flexfield Row with SIN = 25

AM Tester view of flexfield row with SIN = 25

To make a key flexfield accessible through a web service:

  1. Expose the key flexfield application module as a web service.

  2. Test the web service.

25.4.4.1 Exposing a Key Flexfield Application Module as a Web Service

You make key flexfield access available through web services by setting a custom property for the flexfield view link, adding a transient attribute to the master view object to store the concatenated flexfield key, service-enabling the master view object, creating the service interface for the product application module within which the key flexfield application module is nested, and adding flexfield service data object support utility methods to the product application module.

Note:

In this section, master view object refers to the product foreign key view object as illustrated by Figure 25-1.

Before you begin: 

  1. Create the master entity object and view object over the table that references the key flexfield.

  2. Create the flexfield business component as described in Section 25.2.4.3, "Building a Read-Only Reference Model."

    Note:

    When you generate a flexfield business component, the IDE automatically service enables the business component by generating a Service Data Object (SDO) for the base view object and for every subtype view object.

  3. Create a flexfield view link between the master view object and the flexfield business component as described in Section 25.3.1, "How to Create Key Flexfield View Links."

  4. Nest the key flexfield application module instance in the product application module as described in Section 25.3.2, "How to Nest an Instance of the Key Flexfield Application Module in the Product Application Module."

  5. Add the key flexfield view object instance to the application module as described in Section 25.3.3, "How to Add an Instance of a Key Flexfield View Object to the Product Application Module."

To expose a key flexfield application module as a web service: 

  1. Complete the following steps to ensure that service data objects (SDOs) exist for all subtype objects.

    1. In the Application Navigator verify that .xsd files exist for all flexfield subtype view objects.

    2. Open the .xsd file for the flexfield base view object and verify that <include> elements exist for all the flexfield subtype view objects.

      Figure 25-24 Include Elements for the Flexfield Subtype SDOs

      Includes for the subtype view object in the base VO
    3. If any subtype view object SDOs are missing, edit the flexfield base view object, and, on the Java navigation tab, click the Edit Java options icon.

      In the Select Java Options dialog shown in Figure 25-25, select Generate Service Data Object Class, ensure that the namespace is the same location that contains the flexfield view object XML files, and click OK.

      When the SDO is generated for the base view object of the key flexfield polymorphic view object, generic SDOs are automatically generated for all the base view object subtypes.

      Figure 25-25 Generating the SDO for the Key Flexfield Base View Object

      Generating SDOs for a key flexfield base view object
  2. Edit the view link between the master view object and the flexfield view object.

  3. Click the General navigation tab in the overview editor, expand the Custom Properties section, and add a SERVICE_PROCESS_CHILDREN property set to true, if one does not already exist.

  4. Edit the master view object.

  5. In the overview editor, add a transient attribute to store the key flexfield concatenated string.

    1. Click the Attributes navigation tab, and click the Add icon in the Attributes section.

    2. In the View Attribute dialog shown in Figure 25-26, enter a name for the attribute.

      Figure 25-26 Adding a Transient Attribute to the Master View Object

      Adding a transient attribute to the master view object
    3. Set the Java attribute type to String.

    4. Leave the Mapped to Column or SQL checkbox unselected.

      A transient attribute does not include a SQL expression.

    5. Select the Always radio button.

    6. Leave the Expression blank.

    7. Click OK.

  6. Click the Java navigation tab and click the Edit icon in the Java Classes section to generate classes for the master view object.

  7. In the Select Java Options dialog shown in Figure 25-27, select the following checkboxes and click OK:

    • Generate View Object Class

    • Include bind variable accessors

    • Include custom Java data source methods

    • Generate View Row Class

    • Include accessors

    • Generate View Object Definition Class

    • Generate Service Data Object Class

    Figure 25-27 Generating Java Classes for the Master View Object

    Generating Java Classes for the Master View Object
  8. In the Java navigation tab, click the link for the View Row Class to open it in the editor.

  9. Add the code shown in bold in Example 25-17 to the setter method for the transient attribute that you created. Set the viewAccessorName to the name of the view accessor from the view link between the master view object and the key flexfield view object.

    The added code stores the key flexfield concatenated string.

    Example 25-17 Setter Method for the Transient Attribute

    /**
       * Sets <code>value</code> as the attribute value for the calculated attribute KffConcatSegment
       * @param value value to set the  KffConcatSegment
       */
      public void setKffConcatSegment(String value) {
       
        String concatAttrPrefix = FlexfieldProperty.PREFIX;
        String concatAttrPostfix =
          FlexfieldProperty.CONCATENATED_STORAGE_ATTR_POSTFIX;
        // Modify next line to set viewAccessorName to name of the VL
        String viewAccessorName = "put KFF view link acccessor name here";
        String concatAttrName = 
          concatAttrPrefix + viewAccessorName + concatAttrPostfix;
        setAttributeInternal(concatAttrName, value);
    
        setAttributeInternal(KFFCONCATSEGMENT, value);
      }
    
  10. Edit the product application module in which the key flexfield application module instance, master view object instance, and flexfield view object instance are nested.

  11. Click the Service Interface navigation tab and click the Add icon to enable support for the service interface. If the icon is not enabled, click the Edit icon instead and edit the pages that are named in the following steps.

  12. In the Service Interface page of the Create Service Interface wizard, the Web Service Name and Target Namespace fields are automatically populated with appropriate values for this application module.

    Click Next to continue.

  13. In the Service Custom Methods page, select the client methods in the Available list that you want to expose as part of the service interface and move them to the Selected list.

  14. Click Next to continue.

  15. In the Service View Instances page select the view objects in the Available list that you want to expose as part of the service interface and move them to the Selected list.

  16. Highlight each view object on the Selected list to display the Basic Operations and View Criteria Find Operations lists for the operations that are available for that view object, as shown in Figure 25-28.

    Select the checkbox for each operation of the view object that you want to expose in the service interface and clear the rest.

    Figure 25-28 Create Service Interface Wizard — Service View Instances Page

    Service Interface wizard - Service View Instances page
  17. Click Next to continue.

  18. In the Summary page, review your choices and click Finish to generate the web service from the application module. You should see that the Service Interface navigation tab now reflects the custom methods, view instances, basic operations, and view criteria find operations that you chose to expose, as shown in Figure 25-29.

    Figure 25-29 Application Module Service Interface Properties

    Application module service interface properties
  19. Click Finish.

    The generated service interface components appear below the application module in the Application Navigator, as shown in Figure 25-30.

    Figure 25-30 Application Module Service Interface Structure

    Application module service interface structure
  20. In the overview editor for the product application module, click the Java navigation tab.

  21. If the Application Module Class and the Application Module Definition Class do not appear in the list of Java classes, click the Edit icon and generate the classes.

  22. In the Java Classes section, click the link for the Application Module Class.

  23. Add the utility methods shown in Example 25-18 to the application module class. These utility methods enable web service clients to obtain FlexfieldSdoSupport objects to access the flexfield's information.

    Replace Flexfield with the appropriate string for the flexfield that you are working with. Use a string that describes how the flexfield will be used by the customers. For example, getLedgerSdoNamespaceAndName is better than getGLSubtypeSdoNamespaceAndName.

    In the getFlexfieldSdoSupport and getFlexfieldStructureInstanceNumber methods, replace getKffMAM1 with the name of the getter method for the nested maintenance application module instance.

    Example 25-18 Utility Methods for Flexfield Service Data Object Support

    // Change method name as appropriate
        public List<String> getFlexfieldSdoNamespaceAndName(
              String structureInstanceCode) {
            FlexfieldSdoSupport ss= getFlexfieldSdoSupport(structureInstanceCode);
            if (ss == null) {
                return null;
            }
            return Arrays.asList(ss.getSdoNamespace(), ss.getSdoName());
        }
    
        // Change method name as appropriate
        public String getFlexfieldSdoPath() {
            FlexfieldSdoSupport ss = getFlexfieldSdoSupport(null);
            if (ss == null) {
                return null;
            }
            return ss.getDiscriminatorSdoPath();
        }
    
        // Change method name as appropriate
        public List<String> getFlexfieldSegmentSdoPaths(
              String structureInstanceCode,
              List<String> segmentCodes) {
            FlexfieldSdoSupport ss = getFlexfieldSdoSupport(structureInstanceCode);
            if (ss == null) {
                return null;
            }
            ArrayList r = new ArrayList(segmentCodes.size());
            for (String segmentCode : segmentCodes) {
                r.add(ss.getSegmentSdoPath(segmentCode));
            }
            return r;
        }
    
        // Change method name as appropriate
        private FlexfieldSdoSupport getFlexfieldSdoSupport(
              String structureInstanceCode)
        {
            /**
             * @param structureInstanceCode set to null to get the parent (base)
             */
            // Find the nested maintenance application module instance
            KFFMApplicationModuleImpl am;
            // Change getKffMAM1 to name of the getter method for the nested KFF AM
            am = (KFFMApplicationModuleImpl) getKffMAM1();
            return am.getSdoSupport(structureInstanceCode);
        } 
    
        // Change method name as appropriate
        public Long getFlexfieldStructureInstanceNumber(
            String structureInstanceCode) {
            // Find the maintenance application module instance
            KFFMApplicationModuleImpl am;
            // Change getKffMAM1 to name of the getter method for the nested KFF AM
            am = (KFFMApplicationModuleImpl) getKffMAM1();
            return am.getStructureInstanceNumber(structureInstanceCode);
        }
    
  24. In the overview editor for the product application module, click the Service Interface navigation tab for the product application module and click the Edit icon in the Service Interface Custom Methods section.

  25. In the Service Custom Methods page, move the newly added public methods to the Selected list to make them available for clients and click OK.

    The application module's remote server implementation class will be modified to expose these methods.

25.4.4.2 Testing the Web Service

You can test the key flexfield web service access by providing web server connection information, deploying and manually testing the web service, and optionally writing Java client programs to call the flexfield service data object support utility methods to test the service.

Before you begin: 

  1. Make sure that the BC4J Service Client and BC4J Service Runtime libraries are included in your project.

  2. Create a writable maintenance model as described in Section 25.2.4.1, "Building a Writable Maintenance Model."

  3. Expose the key flexfield maintenance application module as a web service as described in Section 25.4.4.1, "Exposing a Key Flexfield Application Module as a Web Service."

To test the web service: 

  1. Expand Application Resources > Descriptors > ADF Meta-INF, and open the connections.xml file.

  2. Locate the Reference element for the product application module's service (ApplicationService in this example).

    This is the service that you created in Section 25.4.4.1, "Exposing a Key Flexfield Application Module as a Web Service" for the product application module in which the key flexfield maintenance application module instance, master view object instance, and flexfield view object instance are nested.

  3. Add the StringRefAddr elements that are shown in bold in Example 25-19. Modify the host and port number in the jndiProviderURL entry to point to an instance of Oracle WebLogic Server. The port number is typically 7101.

    Example 25-19 StringRefAddr Elements to Add to Application Module Reference in connections.xml

    <Reference name="{http://xmlns.oracle.com/oracle/apps/fnd/applcore/flex/test/kff1/model/}ApplicationService"
                  className="oracle.jbo.client.svc.Service" xmlns="">
          <Factory className="oracle.jbo.client.svc.ServiceFactory"/>
          <RefAddresses>
             <StringRefAddr addrType="serviceInterfaceName">
                <Contents>oracle.apps.fnd.applcore.flex.test.kff1.model.ApplicationService</Contents>
             </StringRefAddr>
             <StringRefAddr addrType="serviceEndpointProvider">
                <Contents>ADFBC</Contents>
             </StringRefAddr>
             <StringRefAddr addrType="jndiName">
               <Contents>ApplicationServiceBean#oracle.apps.fnd.applcore.flex.test.kff1.model.ApplicationService</Contents>
             </StringRefAddr>
             <StringRefAddr addrType="serviceSchemaName">
                <Contents>ApplicationService.xsd</Contents>
             </StringRefAddr>
             <StringRefAddr addrType="serviceSchemaLocation">
                <Contents>oracle/apps/fnd/applcore/flex/test/kff1/model/</Contents>
             </StringRefAddr>
             <StringRefAddr addrType="jndiFactoryInitial">
                <Contents>weblogic.jndi.WLInitialContextFactory</Contents>
             </StringRefAddr>
             <StringRefAddr addrType="jndiProviderURL">
                <Contents>t3://localhost:port_number</Contents>
             </StringRefAddr>
             <StringRefAddr addrType="jndiSecurityPrincipal">
                <Contents>weblogic</Contents>
             </StringRefAddr>
             <StringRefAddr addrType="jndiSecurityCredentials">
                <Contents>weblogic1</Contents>
             </StringRefAddr>
          </RefAddresses>
       </Reference>
     
    
  4. Run the remote server class for the product application module to deploy the service to an Integrated WebLogic Server instance and to manually test the web service.

    Note:

    The remote server class was generated when you exposed the key flexfield as a web service in Section 25.4.4.1, "Exposing a Key Flexfield Application Module as a Web Service." This class has a name that ends with ServiceImpl.java

  5. Optionally, create and run Java client programs to test invoking the web service.

    Example 25-20 is an example of how a client test program would use the flexfield service data object support utility methods that you added in Section 25.4.4.1, "Exposing a Key Flexfield Application Module as a Web Service."

    Example 25-20 Sample Java Code to Test the Web Service

    public void testSDOAPIs()
      {
        // Flexfield service data objects are created automatically based on the
        // flexfield definition. Certain information such as segment codes must be
        // transformed in order to be used in a service data object definition. 
        AcctKffMaintService acctKffMaintService =
          (AcctKffMaintService)
          ServiceFactory.getServiceProxy(AcctKffMaintService.NAME);
        DataFactory dataFactory =
          ServiceFactory.getDataFactory(acctKffMaintService);
      
        // Get the namespace and name corresponding to a particular
        // structure instance code (SIC)
        List<String> accountSdoInfo =
          acctKffMaintService.getAcctSdoNamespaceAndName("CC_ACCT_LOC_PRJ_SI");
        System.out.println(accountSdoInfo);
       
        DataObject accountDo =
          dataFactory.create(accountSdoInfo.get(0), accountSdoInfo.get(1));
        System.out.println(accountDo);
        accountDo.set(acctKffMaintService.getAcctSDOPath(),
                      acctKffMaintService.getAcctStructureInstanceNumber(
                      "CC_ACCT_LOC_PRJ_SI"));
     
        //Get segment paths for attributes COST_CENTER and LOCATION
        List<String> segmentPaths =
          acctKffMaintService.getAcctSegmentSdoPaths("CC_ACCT_LOC_PRJ_SI",
                                                     Arrays.asList("COST_CENTER",
                                                                   "LOCATION"));
     
        System.out.println(segmentPaths);
     
        //Update COST_CENTER and LOCATION after obtaining their segmentPaths
        accountDo.set(segmentPaths.get(0), "A12");
        accountDo.set(segmentPaths.get(1), "UK");
       
      }
    

25.4.5 How to Access Key Flexfields from an ADF Desktop Integration Excel Workbook

ADF Desktop Integration makes it possible to combine desktop productivity applications with Oracle Fusion applications, so you can use a program such as Microsoft Excel as an interface to access application data.

Using ADF Desktop Integration, you can incorporate key flexfields into an integrated Excel workbook, so you can work with the flexfield data from within the workbook.

For more general information about integrating Oracle Fusion applications with desktop applications, see the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework. This guide provides most of the information you need to complete the required activities, including the following:

  • Preparing your development environment for desktop integration.

  • Creating a page definition file that will expose the Oracle ADF bindings for use in Excel.

  • Incorporating a key flexfield as a dynamic or static column in an ADF Table on the page.

  • Creating an Excel workbook to integrate with the key flexfield.

  • Preparing your Excel workbook to access the column containing the flexfield.

Note:

The Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework does not make explicit reference to technologies documented in this Oracle Fusion Applications Developer's Guide, and this guide does not repeat the content in the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework, so you must read the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework for a full understanding of how to use ADF Desktop Integration technology in general.

In addition to the standard implementation steps covered in that guide, you must modify your implementation to accommodate flexfields, as discussed in the following sections.

There are two ways to access a key flexfield in Excel:

  • Using a dynamic column in an ADF Table.

    A web page picker popup dialog can be associated with a dynamic column, enabling end users to pick flexfield segment values. Alternatively, users can enter values directly into the segment fields. No custom code is required in either case.

    This is the most typical scenario. Each key flexfield segment is displayed as a distinct column in the ADF Table. First you configure ADF Desktop Integration with a dynamic column key flexfield, and then, if necessary, you handle user-initiated structure code changes.

  • Using a static column in a popup dialog associated with a single cell. Use this approach for either of the following reasons:

    • The key flexfield is in a non-table area of the worksheet.

    • The ADF Table needs to expose same key flexfield instance more than once. In this case only one instance can be dynamic. All other instances should be exposed as static columns.

    In addition to using the popup dialog, end users can enter values directly into the segment field, with the values separated by an appropriate delimiter that you specify.

    Note:

    A static column is any column for which the DynamicColumn property is set to False.

    Individual flexfield segments do not appear in the worksheet. Instead, ADF Desktop Integration invokes a separate JSPX page on which the flexfield will be visible. You can use this scenario with an ADF Desktop Integration form, or either table type, by configuring ADF Desktop Integration with a static column key flexfield.

Note:

The titles of the popup dialog components must be set to the name of the key flexfield, such as "Account," to be consistent across Oracle Fusion Applications.

The key flexfield's segments are part of your database table, so the flexfield is generated against the same entity object on which your worksheet view object is based.

In addition to configuring ADF Desktop Integration with the dynamic or static column key flexfield, you might also need to call a custom application module to handle the update or insert of a key flexfield data row.

25.4.5.1 Configuring ADF Desktop Integration with a Dynamic Column Key Flexfield

When you configure the ADF Table, make the following changes:

  • Add the ADF Desktop Integration Model API library to your data model project.

  • In your page definition for the worksheet, add the key flexfield that you want to access to the master worksheet view object as a child node. Do not add any display attribute to the child node which expands as a dynamic column in the worksheet.

    For more information about how to create a page definition file for a desktop integration project, see the "Working with Page Definition Files for an Integrated Excel Workbook" section of the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework.

  • To make the key flexfield column of the Table component dynamic, set the DynamicColumn property in the TableColumn array of the ADF Table to True. A dynamic column in the TableColumn array is a column that is bound to a tree binding or tree node binding whose attribute names are not known at design time. A dynamic column can expand to more than a single worksheet column at runtime.

    For more information about the binding syntax for dynamic columns, see the "Adding a Dynamic Column to Your ADF Table" section of the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework.

  • For the table's UpdateComponent and InsertComponent properties, specify one of the following as the subcomponent to use:

    • Inputtext

    • OutputText

    • ModelDrivenColumnComponent

  • For the subcomponent's Value property, access the Expression Builder, expand the Bindings node and your tree binding for the table, and select the flexfield node.

  • For the subcomponent's Label property, access the Expression Builder, expand the Bindings node and your tree binding for the table, and select the flexfield node.

25.4.5.2 Handling User-Initiated Structure Code Value Changes in a Dynamic Column Key Flexfield

ADF Desktop Integration requires that to use a dynamic column implementation, the structure of the key flexfield should remain constant for all rows in a given result set. However, each time a new result set is downloaded into the table, the structure code value (and thus the structure) can be changed.

If the structure code value is set globally for the end user of the workbook, changes are not an issue. However, if the end user can control the structure code value (for example, using an LOV in a "header" form), your application must be able to respond appropriately to update the key flexfield structure.

After the end user specifies a structure code value, you must invoke the worksheet UpSync method to get the new value into the model. Then you can use the ADF Table Download method to get fresh data with the new key flexfield structure.

Note:

For an insert-only table, the Download method is undesirable. For these cases, use either the ADF Table DownloadForInsert method or the Initialize method to enable the Table component to reconfigure to accommodate the new flexfield structure.

25.4.5.3 Configuring ADF Desktop Integration with a Static Column Key Flexfield

ADF Desktop Integration supports key flexfields by using tree bindings in an ADF Table. If you are adding your key flexfield as a static column, you can alternatively use an ADF Read-Only Table. Keep in mind that ADF Read-Only Tables support static columns, but not dynamic columns. Popup dialogs support both types.

Note:

A key flexfield appears as a node in the tree binding at design time. Because flexfields are built up dynamically at runtime, you will not see any attributes under the flexfield node in the page definition, but the node itself is present.

When you configure the popup dialog, make the following changes:

  • You can use the column's action set properties to make the key flexfield web page available for editing. You should include the attributes used in the web page in the table's cached attributes unless the row will be committed immediately.

  • You must choose a fixed attribute (the key flexfield CCID) to represent the flexfield in the worksheet. Add a Dialog action to the DoubleClickActionSet of an InputText or OutputText component, then connect the Dialog action to a JSPX page that will display the key flexfield.

    For more information about how to create a page definition file for a desktop integration project, see the "Working with Page Definition Files for an Integrated Excel Workbook" section of the Oracle Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework.

For static display of a key flexfield in an ADF Desktop Integration workbook, you must create an updatable transient attribute in the view object on which the ADF Desktop Integration table is based. This transient attribute will hold the concatenated value of the key flexfield segments, separated by a delimiter. If one purpose of the worksheet is to display existing data from the database, the transient attribute should be populated using custom application module methods upon returning from a popup dialog or opening the worksheet.

25.4.5.4 Handling Update or Insert of a Key Flexfield Data Row

To handle update or insert of a data row containing a key flexfield in an ADF Desktop Integration table, you call a custom application module method which contains appropriate code, as follows:

  • To update an existing row, add your code to the UpdateRowActionId property of the table.

  • To insert a new row, add your code to the InsertAfterRowActionId property of the table.

The following examples demonstrate the code needed to accomplish these tasks. Example 25-21 and Example 25-22 apply to an ADF Desktop Integration implementation with the key flexfield exposed as a dynamic column. Example 25-23 and Example 25-24 apply to an ADF Desktop Integration implementation with the key flexfield exposed as a static column.

Example 25-21 Updating an Existing Row with a Key Flexfield Dynamic Column

You add this code as an application module method which will be invoked from the UpdateRowActionId property of an ADF Table. This code will be invoked for every row that is updated.

Row tempRow = null;  //get KFF child row information based on your KFF View Link Accessor
KFFViewRowImpl acctRow;
ViewRowImpl kffAcctRow = (KFFViewRowImpl)linerow.getAccountLineKff();
 
// if not child row (for new row case or cases where 
// KFF data is not invalid/present for existing DB Row)
// get a dummy row from ADFdi helper class
 
if (kffAcctRow == null) {
          tempRow = ModelHelper.getAdfdiTempChildRow(linerow, "AccountLineKff");
           kffAcctRow = (ViewRowImpl)tempRow;
       }
 
Long kffAcctId = null;
String acctSeg=null;
 
// check whether KFF row is instance of KFFViewRowImpl, 
// which means you are updating valid KFF data
// If not that means you are creating new KFF data
// Based on that logic of deriving updated segments from worksheet differs
 
if (kffAcctRow instanceof KFFViewRowImpl){
 acctRow = (KFFViewRowImpl)fkRow.getAccountLineKff();
 acctSeg = acctRow.getBufferedConcatenatedSegments();
} else {
 acctSeg = KFFViewRowImpl.getConcatenatedSegments(kffAcctRow);
}
kffAcctId = linerow.getKeyFlexfieldCombinationID("AccountLineKff",acctSeg);
 
 
//IF YOU NEED DYNAMIC INSERT (CREATING NEW SEGMENT COMBINATIONS) FROM ADFdi Speadhseet
//make sure end user supplied valid value for at least one segment.
 
if (kffAcctId==null) {  
  // if there was not valid ccid obtained earlier, that means you 
// are trying to add a new combination
 
   String delimiter = FlexfieldViewDefImpl.getDelimiter(kffAcctRow.getViewDef());
   List segments =
      FlexfieldViewDefImpl.getFlexfieldAttributes(kffAcctRow.getViewDef());
    StringBuffer delim = new StringBuffer();
   for (int i = 0; i < segments.size() - 1; i++) {
      delim.append(delimiter);
    }
 
    //if getConcatenatedSegments() return only delimiter information
    //that means end user has not supplier valid KFF Segment values.
 
if (!acctSeg .equals(delim.toString())) {
   linerow.setKeyFlexfieldCombinationID("AccountLineKff",
                                       acctSeg);
                                      
//Get CCID value based on segment information
 
kffAcctId =
    linerow.getKeyFlexfieldCombinationID("AccountLineKff", acctSeg );
}
} //if kffAcctId is null
 
//setting CCID column with CCID value
 
     linerow.setDistCodeCombinationId(kffAcctId);
 

Example 25-22 Inserting a New Row with a Key Flexfield Dynamic Column

You add this code as an application module method which will be invoked from the InsertAfterRowActionId property of an ADF Table. This code will be invoked for every row that is inserted.

Row tempRow = null;
//Retrieve key flexfield child row information based on your KFF view link accessor
ViewRowImpl kffAcctRow = (ViewRowImpl)linerow.getAccountLineKff();
 
//if not child row (for new row case or cases where KFF data is not invalid/present for existing DB Row)
//get a dummy row from ADFdi helper class
 
if (kffAcctRow == null) {
          tempRow = ModelHelper.getAdfdiTempChildRow(linerow, "AccountLineKff");
           kffAcctRow = (ViewRowImpl)tempRow;
 
       }
Long kffAcctId = null;
  kffAcctId =
    linerow.getKeyFlexfieldCombinationID("AccountLineKff", KFFViewRowImpl.getConcatenatedSegments(kffAcctRow));
 
//If you need dynamic insert (creating new segment combinations) in the ADFdi worksheet, 
//make sure the end user supplies a valid value for at least one segment.
if (kffAcctId==null) {
String delimiter = FlexfieldViewDefImpl.getDelimiter(kffAcctRow.getViewDef());
List segments =
FlexfieldViewDefImpl.getFlexfieldAttributes(kffAcctRow.getViewDef());
StringBuffer delim = new StringBuffer();
for (int i = 0; i < segments.size() - 1; i++) {
    delim.append(delimiter);
}
 
//if getConcatenatedSegments() return only delimiter information
//that means end user has not supplier valid KFF Segment values.
 
if (!KFFViewRowImpl.getConcatenatedSegments(kffAcctRow).equals(delim.toString())){
    linerow.setKeyFlexfieldCombinationID("AccountLineKff",
    KFFViewRowImpl.getConcatenatedSegments(kffAcctRow));
                                      
//Get CCID value based on segment information
 
kffAcctId =
    linerow.getKeyFlexfieldCombinationID("AccountLineKff", KFFViewRowImpl.getConcatenatedSegments(kffAcctRow));
    }
    }
//setting CCID column with CCID value
 
     linerow.setDistCodeCombinationId(kffAcctId);
 

Example 25-23 Updating or Inserting a Row with a Key Flexfield Static Column

This code should be added to the setter of the transient attribute in your view object RowImpl.

setAttributeInternal(TRANSIENTACCOUNT, value); 
//get KFF child row information based on your KFF View Link Accessor
 
ViewRowImpl kffAcctRow = (ViewRowImpl)linerow.getAccountKff();
Row tempRow;
 
//if not child row (for new row case or cases where KFF data is not invalid/present for existing DB Row)
//get a dummy row from ADFdi helper class
if (kffAcctRow == null) {
    tempRow = ModelHelper.getAdfdiTempChildRow(this, "AccountKff");
    kffAcctRow = (ViewRowImpl)tempRow;
}
Long kffAcctId = null;
 
//If you need dynamic insert (creating new segment combinations) in the ADFdi worksheet, 
//make sure the end user supplies a valid value for at least one segment.
 
String delimiter = FlexfieldViewDefImpl.getDelimiter(kffAcctRow.getViewDef());
List segments =
FlexfieldViewDefImpl.getFlexfieldAttributes(kffAcctRow.getViewDef());
StringBuffer delim = new StringBuffer();
for (int i = 0; i < segments.size() - 1; i++) {
    delim.append(delimiter);
}
 
//If getConcatenatedSegments() returns only delimiter information
//that means the end user has not supplied a valid segment value.
 
       if (value!=null){
          if (!KFFViewRowImpl.getConcatenatedSegments(kffAcctRow).equals(delim.toString()))
                this.setKeyFlexfieldCombinationID("AccountKff",value);
 
       kffAcctId =  this.getKeyFlexfieldCombinationID("AccountKff", value);
 
//set your orignal attribute with ccid value
              this.setAcctsPayCodeCombinationId(kffAcctId);
                           }     }
 

Example 25-24 Applying Modified Segment Values to a Cell in a Key Flexfield Static Column

You add this code as a custom application module method which will be invoked from the ActionListener property of the OK button in the popup dialog JSPX page.

//Get a reference to your ADF DesktopIntegration table view object
DesktopQuickInvoicesHeaderVOImpl headerVO = 
           this.getDesktopQuickInvoicesHeader();
 
//Get a reference to the current row, which is the row from which popup dialog is opened
       DesktopQuickInvoicesHeaderVORowImpl headerRow = 
           (DesktopQuickInvoicesHeaderVORowImpl)headerVO.getCurrentRow(); 
//Get a reference to your key flexfield row
       ViewRowImpl kffAcctRow = (ViewRowImpl)headerRow.getAccountKff(); 
       Row tempRow;
 
       //If that is null (for a null CCID or an invalid CCID or a new row) 
       //Get temp row from ADFdi
       if (kffAcctRow == null) { 
           tempRow = ModelHelper.getAdfdiTempChildRow(headerRow, "AccountKff"); 
           kffAcctRow = (ViewRowImpl)tempRow; 
 
       } 
// Derive and assign value of segments to your transient attribute 
// which is created for single cell display in the ADFdi table
       headerRow.setTransientAccount(KFFViewRowImpl.getConcatenatedSegments(kffAcctRow));

25.5 Completing the Development Tasks for Key Flexfields in Partial Mode

The most common use of a key flexfield is for a product table to have a foreign key to the primary key of the combinations table. This provides the flexibility of storing all the combinations in a single table and having references to these combinations from the product tables. However, there are circumstances where application developers might need to capture segment values in a transaction table or a setup table. In this case, the key flexfield becomes a data capturing tool, and the captured data is stored in a product table. There is no direct relationship between the product table and the key flexfields combinations table. This type of usage is called partial usage.

There are two types of partial usage:

Note:

To incorporate a key flexfield partial usage into your application, you must have already defined and registered the key flexfield master usage on which it is based. See Section 25.2.1.5, "Registering and Defining Key Flexfields Using the Setup APIs," before continuing.

The development tasks for key flexfields in partial mode consist of the following steps:

  1. Complete the registration of a key flexfield partial usage (all-segments or single-segment).

  2. Create partial mode key flexfield business components based on the partial usage.

  3. Create a view link between your product view object and the partial mode key flexfield.

  4. The remainder of the development process is essentially the same as the consumer development process for key flexfield master usages. You skip the section on creating key flexfield view links and continue with the tasks described in the following sections:

    1. Section 25.3.3, "How to Add an Instance of a Key Flexfield View Object to the Product Application Module"

    2. Section 25.3.2, "How to Nest an Instance of the Key Flexfield Application Module in the Product Application Module"

    3. Section 25.3.4, "How to Employ Key Flexfield UI Components on a Page"

      Note:

      This section contains additional information specific to key flexfield partial usages.

    4. Section 25.3.5, "How to Configure Key Flexfield UI Components"

      Note:

      This section contains additional information specific to key flexfield partial usages.

25.5.1 How to Register a Key Flexfield All-Segment Partial Usage

All-segment partial usages have a column in the product table for every segment column in the combinations table.

To register an all-segment partial usage:

  1. Add columns to your product table to represent all of the key flexfield segment columns in the combinations table. The columns that you add must match exactly in number, data type, and size, the corresponding columns in the combinations table.

    Furthermore, the column names must also be exactly the same as in the combinations table, with the exception of an optional prefix.

    For example, if the column names are A1 and A2 in the combinations table, then in the partial usage they could again be A1 and A2, respectively, or with a prefix they could be X_A1 and X_A2. They cannot be B1 and Y_B2, nor any variation that does not end in the names of the combinations table columns.

  2. Use the PLSLQL registration APIs in the FND_FLEX_KF_SETUP_APIS package to register the partial usage.

    To learn how to access documentation about using the APIs, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

  3. Create an ADF Business Components usage for the flexfield table usage as described in Section 25.2.1.9, "Registering Entity Details Using the Setup APIs."

To implement a key flexfield partial usage, you select the usage at design time. For more information, see Section 25.2.4, "How to Create Key Flexfield Business Components".

If you need to change a table usage after creating it, you must delete the table usage, then re-create it.

25.5.2 How to Register a Key Flexfield Single-Segment Partial Usage

Single-segment partial usages have one column in the product table to capture a single segment column in the combinations table.

To register a single-segment partial usage:

  1. Add the key flexfield segment column that you want to capture to your product table. The table cannot be the combinations table for the flexfield.

  2. Define a segment label for the segment that you want to capture.

    The segment label must be defined as Unique to ensure that only one segment in a given structure can be associated with this label.

    For more information about segment labels, see Section 25.2.2, "How to Implement Key Flexfield Segment Labels."

  3. Use the PLSLQL registration APIs in the FND_FLEX_KF_SETUP_APIS package to register the partial usage.

    You must supply the SEGMENT_LABEL_CODE to identify the unique segment label, and COLUMN_NAME to identify the column in your table in which the segment value will be stored.

    To learn how to access documentation about using the APIs, see Section 25.2.1.6, "What You May Need to Know About the Key Flexfield Setup API."

  4. Create an ADF Business Components usage for the flexfield table usage as described in Section 25.2.1.9, "Registering Entity Details Using the Setup APIs."

To implement a key flexfield partial usage, you select the usage at design time. For more information, see Section 25.2.4, "How to Create Key Flexfield Business Components."

If you need to change a table usage after creating it, you must delete the table usage, then re-create it.

25.5.3 How to Create Partial Mode Key Flexfield Business Components

Zero or more partial usages can be defined for a given flexfield, each one potentially on a different product table.

Before you begin:

One or more required libraries might have not been automatically included in your project. You must ensure that all required libraries, notably the BC4J Service Runtime, Java EE 1.5 and Java EE 1.5 API libraries, are included.

Using the standard wizard, create application entity objects based on the combinations tables you have defined. Make sure of the following:

  • At least one customization class is included in adf-config.xml.

    Note:

    This serves to ensure correct application behavior. It does not matter which customization class you include.

    For information about customization layers, see the "Understanding Customization Layers" section in the Oracle Fusion Applications Extensibility Guide.

  • These entity objects are directly modeled on the combinations tables; hence they contain the fixed (nonflexfield) columns, if any, along with all of the flexfield columns. In general, all columns should be included.

  • The entity objects have primary keys defined.

  • CCID column is of type data type java.lang.Long.

  • The SIN column, if it exists, is of data type java.lang.Long.

    Caution:

    The SIN attribute cannot be transient with a calculated value. It can be based on a database table column, or it can be SQL-derived.

  • NUMBER type segment columns are of data type java.math.BigDecimal.

  • VARCHAR2 type segment columns are of data type java.lang.String.

  • The package name and the object name prefix for each entity object are registered with the flexfield usage to which it will provide data, as described in Section 25.2.1.9, "Registering Entity Details Using the Setup APIs."

To create partial mode key flexfield business components:

  1. Build your project to ensure that the entity objects are available in classes. The modeler relies on what is in your classes.

  2. In the New Gallery, navigate to Business Tier > ADF Business Components and select Flexfield Business Components.

  3. Click OK to access the Create Flexfield Business Components wizard.

  4. On the Role page, select the role that you are taking as you create the flexfield business components:

    • Developer — select this role if you are incorporating the flexfield into an application. The business components must be stored in one of your projects. Select the desired project location from the Project Source Path dropdown list.

    • Tester — select this role if you are planning to test your flexfield or test a shared flexfield. In the Output Directory field, specify the path of your desired location for the generated business components.

      For more information about testing flexfields, see Chapter 26, "Testing and Deploying Flexfields." For more information about sharing and importing shared flexfields, see Section 25.2.5, "How to Share Key Flexfield Business Components."

    Note:

    This is not a role in the security sense. It exists only during this procedure, for the purpose of specifying where your generated flexfield business components should be stored.

  5. Click Next. The Flexfield page appears, as shown in Figure 25-31.

    Figure 25-31 Create Flexfield Business Components Wizard — Flexfield Page

    Business Components wizard - Flexfield page
  6. From the Type dropdown list, select Key.

  7. In the Application field, specify the full name of the application to which your key flexfield belongs.

    You can browse for the name, and filter by ID, Short Name, or Name.

  8. In the Code field, specify the code of the key flexfield you want to use.

    You can browse for and filter by Code.

  9. In the Usage section, select the table row that contains your desired partial key flexfield usage. Key flexfield usage can be one of the following types:

    • An all-segment partial usage of the key flexfield on a product table other than the combinations table. Zero or more partial usages can be defined for a given flexfield, each one potentially on a different product table. You can identify partial usages by the presence of the prefix (Partial) in the Description field.

    • A single-segment partial usage of the key flexfield on a product table other than the combinations table. Zero or more single-segment partial usages can be defined for a given flexfield, each one potentially on a different product table. You can identify single-segment partial usages by the presence of the prefix (Partial Single) in the Description field.

    Do not select a flexfield usage without a prefix in the Description field. For more information about key flexfield partial usages, see Section 25.5.1, "How to Register a Key Flexfield All-Segment Partial Usage."

  10. Click Next. The Entity Object page appears, as shown in Figure 25-32.

    Figure 25-32 Create Flexfield Business Components Wizard — Entity Object Page

    Business Components wizard - Entity Object page
  11. Expand the tree of available models and select an entity object to use as the data source for the key flexfield.

    Because you selected a partial usage on the Flexfield page, you must select the entity object for the table where that usage is defined.

    The entity object you select must include all of the attributes that will be referenced by the flexfield. For partial usages, this includes attributes that represent the SIN column, the DSN column if it exists in the combinations table, and all of the flexfield segment columns.

    Caution:

    The Create Flexfield Business Components wizard is case-sensitive. All column names — and the names of the flexfield entity object attributes associated with them — must be upper case.

  12. You might wish to select an entity object for which the key flexfield attributes are defined as transient (not based on database table columns). If you need to do this, select the checkbox labeled Use the entity attributes named after their corresponding flexfield database columns. This checkbox is unselected by default.

    When a key flexfield entity object attribute is transient, there is no matching underlying column name. When you select this checkbox, the system will match the entity object attribute names to the key flexfield column names, and use the matching attributes to access the flexfield data. Make sure that the entity object has a full set of attributes with matching names before you select this option.

    Caution:

    The transient SIN attribute cannot be a calculated value; it must be SQL derived (computed using a SQL expression).

    This entity object must be registered under the master usage as described in Section 25.2.1.9, "Registering Entity Details Using the Setup APIs." There is no need to register another table for this purpose, even if the entity object is based on some other table.

    Note:

    If the entity object with transient key flexfield attributes is not based on the master usage, the transient attributes must be named using the same prefix as the other attributes of that entity object (and the corresponding table columns). For more information, see Section 25.5.1, "How to Register a Key Flexfield All-Segment Partial Usage."

  13. Click Next. The Usage Settings page appears.

    This page contains a Structure Instance Number dropdown list, as shown in Figure 25-33. From the dropdown list, select the entity attribute that corresponds to the key flexfield SIN for the partial usage. The SIN must be an attribute of type java.lang.Long.

    If the key flexfield is data set–enabled, this page will also contain a Data Set Number dropdown list. From the dropdown list, select the entity attribute that corresponds to the DSN for the partial usage. The DSN must be an attribute of type java.lang.Long.

    Figure 25-33 Create Flexfield Business Components Wizard — Usage Settings Page

    Business Components wizard - Usage Settings page
  14. Click Next. The Naming page appears.

    To create business components for the key flexfield partial usage that you previously selected, the package name and the object name prefix for the selected entity object must first be registered with that flexfield usage. Text on the Naming page indicates whether this is the case:

    • If the selected entity object is registered with the flexfield usage, the Naming page displays the package name and the object name prefix for the entity object. Click Next and continue to Step 15.

    • If the selected entity object is not registered (as an ADF Business Components usage) with the flexfield usage, the Naming page displays a message to that effect. Take one of the following actions:

  15. On the Summary page, review your choices and click Finish.

    The business components generated will replace any existing ones that are based on the same flexfield.

    Note:

    This wizard might fail with a "ClassNotFound" exception message. This indicates that one or more required libraries have not been automatically included in your project, notably the BC4J Service Runtime, Java EE 1.5 and Java EE 1.5 API libraries. You can resolve this issue by manually adding any missing libraries; then you can complete this procedure successfully.

  16. Refresh the project to see the newly created flexfield business components in the Application Navigator.

25.5.4 How to Create Partial Mode Key Flexfield View Links

A view link is needed whenever a product view object references your key flexfield. The base view object can have many incoming view links from various product view objects, as a key flexfield is usually shared by many product tables.

Before you begin:

You should have already created a master view object over your entity object using the standard wizard.

Ensure that the view object does not include flexfield attributes such as SEGMENT1_VARCHAR2, SEGMENT2_NUMBER, and so on. Ensure that you include the attributes that are needed for the foreign key reference, such as CCID, SIN, and, if present, DSN. Ensure that the CCID attribute's Display control hint is set to Hide.

To create a partial mode key flexfield view link:

  1. In the New Gallery, navigate to Business Tier > ADF Business Components and select Flexfield View Link.

  2. Click OK to access the Flexfield View Link wizard, as shown in Figure 25-34.

    Figure 25-34 Create Flexfield View Link Wizard — Name Page

    View Link wizard - Name page
  3. On the Name page, from the Package dropdown list, specify a package for the view link.

  4. In the Name field, enter a name for the partial mode view link.

  5. Click Next. The View Objects page appears, as shown in Figure 25-35.

    Figure 25-35 Create Flexfield View Link Wizard — View Objects Page

    View Link wizard - View Objects page
  6. In the Select Source View Object tree, expand the available partial mode key flexfield view objects from your current project and select a source partial mode view object.

  7. In the Select Destination Flexfield tree, expand the available flexfield view objects from your project and select a destination base key flexfield view object.

  8. In the View Link Accessor Name field, enter an appropriate name for the view link accessor.

  9. Click Next. The Source Attributes page appears, as shown in Figure 25-36.

    Figure 25-36 Create Flexfield View Link Wizard — Source Attributes Page for Partial Mode Key Flexfields

    Wizard - Source Attributes page for key flexfields

    This page is informational only. The key attributes of the source view object will be used to define the view link. The primary key attribute should be listed for this selection.

  10. Click Finish to go to the Summary page.

    Note:

    You can skip the Properties page because view link-specific properties are not supported.

  11. On the Summary page, review the summary, then click Finish.

    The partial mode key flexfield view link is generated.

25.6 Working with Combination Filters for Key Flexfields

A combination filter for a key flexfield is a set of query criteria that can be applied to a combinations table to specify a subset of code combinations. After you incorporate a combination filter into your application, end users can select key flexfield values in the user interface from the subset produced by the filter.

For example, consider the rows that are listed in Table 25-8:

Table 25-8 Example Combinations Table

SIN CCID Summary_Flag Enabled_Flag Segment1_Varchar2 Segment2_Varchar2 . .

11

77

N

Y

8.5X12

YEL

..

11

78

N

Y

8.5x14

GRN

..

14

2

N

Y

ERGO

NYLON

..

14

3

N

Y

HGBAK

LEATHER

..


You could define a filter with the following conditional logic:

SIN=14 and Segment1_Varchar2='ERGO'

When you apply this filter condition to the combinations table, the result listed in Table 25-9 is presented:

Table 25-9 Example Filter Result

SIN CCID Summary_Flag Enabled_Flag Segment1_Varchar2 Segment2_Varchar2 ..

14

2

N

Y

ERGO

NYLON

..


Combination filter conditions for key flexfields are stored in a database column of type XMLType. This column is referred to a a filter-condition column.

There are three types of combination filters that you can use in your application — standard combination filters, combination filters for Oracle Business Intelligence (BI) Publisher reports, and cross-validation filters.

Standard Combination Filters

With standard combination filters, you determine which key flexfields your end users should be able to filter, and define a dedicated filter-condition column in your application database for each filter that you want to include in the application user interface. This column can be defined in an existing reference table. You can also create one or more dedicated tables just to store filter-condition columns.

The filter condition is stored in the filter-condition column as XML. At runtime, the filter condition in the XML is converted to a ViewCriteria object and applied over the appropriate base key flexfield view object so that when the view object is executed, the filter condition is applied and the filtered query results are produced.

In JDeveloper, you prepare business objects based on the table containing the filter-condition column, then you associate a combination filter view object attribute with the key flexfield. You can associate zero, one, or many combination filters with a given key flexfield, but only one flexfield can be addressed by a given filter.

To make the combination filter accessible to application implementors or administrators, add a key flexfield combination filter UI component to an application page. Each row contains a different filter definition that can be applied to the associated key flexfield. The implementors or administrators will be responsible for populating the table with filter criteria using a provided utility.

Combination filters for key flexfields are supported by the XML schema FndFilter.xsd. This XML schema binds the filter XML that is defined. This schema is registered with the FUSION database schema at the following URI:

http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd

The XML schema is registered to the database as BINARY_XML.

You can test the filter definitions by inserting predefined XML filter criteria into the filter-condition column.

Note:

A PL/SQL API is provided so that you can apply filters to your SQL statements as WHERE clause conditions rather than applying them to the user interface. For more information, see Section 25.6.5, "How to Apply Combination Filters Using the PL/SQL Filter APIs."

Combination filters are removed from an application by removing their accessors.

Combination Filters for Oracle BI Publisher Reports

The Applications Core key flexfield filter repository enables Oracle Fusion Applications developers to include selected key flexfield segments as available parameters in an Oracle BI Publisher report submission user interface. The filter-repository mechanism translates report parameters for those segments into key flexfield combination filter criteria, which are then translated into SQL for inclusion in the report. You accomplish this by creating a flexfield filter view object over the public entity object FndKfEssFiltersPEO to access a provided common filter repository table, then adding to the report submission page a filter UI component that is based on the filter view object.

When the report job is submitted, the flexfield filter XML definition produced by the filter input criteria is saved to the filter repository. Oracle Enterprise Scheduler Service launches the reporting job with the report parameters including the filter key. The filter key is passed to the flexfield lexical API, which returns the filter criteria as a SQL WHERE clause, which Oracle BI Publisher integrates into the SQL statement for its report.

To incorporate combination filters for Oracle BI Publisher reports into a maintenance user interface, you follow much of the same process as you would to implement standard filters. The combination filter procedures that follow note which procedures do not apply to these types of filters.

The kff_filter_purge(...) procedure from the fnd_flex_xml_publisher_apis PL/SQL package enables you to remove unused filters from the filter repository.

Cross-Validation Filters

Cross validation rules leverage the code combination filter infrastructure to apply a pair of filters to new code combinations that are proposed for a key flexfield by administrators or end users, when you have enabled them to work with maintenance mode or dynamic combination insertion.

After enabling cross validation for a key flexfield at registration time, you must build a maintenance user interface that administrators can use to maintain the implementation-specific filters that comprise each rule. All filter combinations that an administrator defines for a given key flexfield are applied automatically to cross validate new code combinations as they are entered.

Note:

Cross-validation rule criteria should generally be created and modified only by application implementors and administrators. For end users, these rules automatically validate new code combinations in the same way that value sets automatically validate new segment values.

To incorporate cross-validation filters, you follow much of the same process as you would to implement standard filters. The combination filter procedures that follow note which procedures do not apply to these types of filters.

For more information about implementing cross validation rules, see Section 25.2.3, "How to Implement Cross Validation Rules and Custom Validation."

25.6.1 How to Prepare the Database for Standard Key Flexfield Combination Filters

A database column of type XMLType is required to store filter data in your database. This column is referred to as the filter-condition column. For standard combination filters, you must define a filter-condition column for the filter data in your database before you can associate combination filters with key flexfields in your application.

Note:

If you are implementing combination filters to support cross validation rules, the required filter-condition columns already exist in the FND_KF_CROSS_VAL_RULES repository table. If you are implementing combination filters for use in the key flexfield filter repository for Oracle BI Publisher reports, these columns exist in the repository.

To prepare a standard key flexfield combination filter for modeling:

  1. Select an existing table to contain your filter, or create a new one.

    To create a table called, for example, FND_MYFILTER_KFF1, execute the following script:

    Create table FND_MYFILTER_KFF1 (ID Number primary key, Info Varchar2(1000));
    
  2. Use the following Alter script to add a filter-condition column (for example, Filter) of type XMLType to your table:

    Alter table FND_MYFILTER_KFF1 add Filter xmltype
    XMLType column Filter
    Store as BINARY XML
    XMLSCHEMA "http://www.oracle.com/apps/fnd/applcore/flex/kff/FndFilter.xsd"
    ELEMENT  "KeyFlexCodeCombinationFilter";
    

    Note:

    Your new filter-condition column must be configured as nullable.

    This script is necessary because the Database Schema Deployment framework does not support the XMLType data type.

25.6.2 How to Add Combination Filters to Your Application

To add combination filters to your application, you complete the following tasks:

  1. For standard filters only, create an entity object over the table containing the filter-condition column.

  2. Create a view object over the filter entity object.

  3. Associate the combination filters with key flexfields.

  4. Configure, deploy, and test the combination filters.

25.6.2.1 Creating a Filter Entity Object for a Standard Filter

For standard filters, you must create a filter-specific entity object over the table containing the filter-condition column.

Note:

You do not need to create a filter view object if you are implementing one of the following types of filters:

  • If you are implementing combination filters for use in the key flexfield filter repository for Oracle BI Publisher reports, use the existing public entity object oracle.apps.fnd.applcore.flex.kff.model.publicEntity.FndKfEssFiltersPEO, which became available when you added the Applications Core library to your data model project.

  • If you are implementing combination filters to support cross validation rules, use the provided configured entity object:

    oracle.apps.fnd.applcore.flex.kff.model.entity.KeyFlexfieldCrossValidationRuleEO
     
    

Before you begin: 

  1. Define a database column of XMLType to store the filter as described in Section 25.6.1, "How to Prepare the Database for Standard Key Flexfield Combination Filters."

  2. To use key flexfield combination filters, you must first have completed the Create Flexfield Business Components wizard for at least one key flexfield, so that your project contains one or more key flexfield business components.

To create the filter entity object: 

  1. Create an entity object (for example, Kff1Fltr1EO) over the table containing your filter-condition column.

  2. Open the entity object, and, in the overview editor, click the General navigation tab.

  3. Expand the Custom Properties section and add the FND_FILTER property with a value of Y.

    This property enables the base classes (OAEntityImpl) to recognize that the entity object contains a filter attribute.

  4. Because the column type of the filter attribute is XMLType, which is not natively supported by ADF Business Components, you must edit the attribute to make it a transient attribute that is computed using a SQL expression.

    Click the Attributes navigation tab, select the filter attribute, and click the Edit icon to open the Edit Attribute dialog.

  5. In the Entity Attribute dialog, click the Entity Attribute node and change the attribute from a persistent type to a calculated type, as shown in Figure 25-37.

    1. Specify the ClobDomain type.

    2. Select Derived from SQL Expression.

    3. Enter an Expression such as the following expression:

      Kff1Fltr1EO.filter.getClobVal()
      

    Note:

    GetClobVal() is needed to manage the XMLType column in the database because ADF Business Components currently does not support the XMLType data type natively.

    Figure 25-37 Edit Filter Attribute — Entity Attribute Page

    Edit Filter Attribute - Entity Attribute page
  6. Click the Custom Properties node, as shown in Figure 25-38.

    Figure 25-38 Edit Filter Attribute — Custom Properties Page

    Edit Filter Attribute - Custom Properties page
  7. Add the custom filter properties that are listed in Table 25-10 to the filter attribute.

    Table 25-10 Custom Filter Properties

    Name Value Description

    FND_FILTER

    Y

    A Y value indicates that the entity attribute is a filter attribute.

    FND_FILTER_TABLE

    table-name

    Indicates the name of the underlying table on which this filter attribute is based.

    FND_FILTER_COL

    column-name

    Indicates the name of the column on which this attribute is based in the filter table. This is needed because the entity object could be based on a database view.

    FND_FILTER_TABLE_COL_PKn

    primary-key-column-id

    Indicates the primary key column of the underlying filter table.

    If the table has a composite primary key (for example: ID1, ID2), you must add an entry for each key. For example:

    FND_FILTER_TABLE_COL_PK1=ID1
    FND_FILTER_TABLE_COL_PK2=ID2
    

    FND_FILTER_TABLE_ATTR_PKn

    view-object-attribute-name

    Indicates the name of the view object attribute that corresponds to the attribute in the entity object that represents the filter table primary key.

    If the view object has attributes that correspond to multiple entity object primary key attributes, you must add an entry for each key. For example:

    FND_FILTER_TABLE_ATTR_PK1=ID1
    FND_FILTER_TABLE_ATTR_PK2=ID2
    

25.6.2.2 Creating a Filter View Object

You need to create a filter view object (for example, Kff1Fltr1VO) over the filter entity object.

  • If you are implementing a standard combination filter, create the view object over the entity object that you created in Section 25.6.2.1, "Creating a Filter Entity Object for a Standard Filter."

  • If you are implementing the filter for use in the key flexfield filter repository for Oracle BI Publisher reports, create the view object over the public entity object oracle.apps.fnd.applcore.flex.kff.model.publicEntity.FndKfEssFiltersPEO

  • If you are implementing the filter to support cross validation rules, create the view object over the provided cross validation entity object:

    oracle.apps.fnd.applcore.flex.kff.model.entity.KeyFlexfieldCrossValidationRuleEO
     
    

    In the view object for the cross validation rules, define view criteria to set the APPLICATION_ID and KEY_FLEXFIELD_CODE attributes to static values for your application and key flexfield.

25.6.2.3 Associating Combination Filters with Key Flexfields

You use the Create Flexfield Filter wizard to create a view accessor from the filter view object's combination filter attribute to a key flexfield view object definition.

Note:

If you are implementing filters to support cross validation rules, you must complete this procedure twice — once for the condition filter attribute and once for the validation filter attribute.

To associate a combination filter with a key flexfield: 

  1. In the New Gallery, navigate to Business Tier > ADF Business Components and select Flexfield Filter.

  2. In the Filter Accessor dialog, expand the available view objects in your current project on the left-hand list and select the view object attribute that corresponds to the XML Filter column, as shown in Figure 25-39.

    Figure 25-39 Filter Accessor Dialog

    Filter Accessor Dialog
  3. Expand the available flexfields in your current project on the right-hand list and select a key flexfield to be filtered.

  4. Enter a name for the filter accessor (with no spaces), then click OK.

25.6.2.4 Configuring, Deploying and Testing Combination Filters

The final task is to configure the view object, add it to a new application module for the filter, and test it.

To configure, deploy and test a combination filter: 

  1. Open the filter view object and click the General navigation tab.

    This property enables the base classes (OAViewRowImpl) to recognize that the view object row contains a filter attribute.

  2. Expand the Custom Properties section and add the property FND_FILTER with the Value set to Y.

  3. Click the Attributes navigation tab and select the filter attribute.

  4. Expand the Custom Properties section and add the property FND_ACFF_SIN for the selected filter attribute. Set the Value to the structure instance number (SIN).

    This property indicates the view object's SIN attribute that associates with this filter attribute.

  5. Create an application module for the filter. In the Data Model page, move the filter view object to the Data Model list. In the Application Modules page, move the flexfield application module, which was created when you created the flexfield business component, to the Selected list.

    Tip:

    You also can add the flexfield application module from the Application Module Instances section in the Data Model navigation tab for the application module.

  6. Run the Business Component Browser to make sure that all attributes appear.

25.6.3 How to Employ Combination Filters on an Application Page

You need to add the filter view accessors that you created to an application page. This procedure applies to conventional key flexfield filters as well as to filters that you are implementing for use in the filter repository.

25.6.3.1 Adding Your Key Flexfield Filter to an Application Page

You add a key flexfield filter to an application page by dropping the filter view object on the page and modifying the XML code for the filter component.

Note:

This procedure is also used to produce a user interface for defining and maintaining cross validation rules. Complete the procedure twice — once for the accessor of the condition filter and once for the accessor of the validation filter. Both filters can be exposed on the same page.

Before you begin: 

To add your key flexfield filter to an application page: 

  1. In your project, create a new JSPX page.

  2. Drag and drop the view object that contains your filter from the Data Controls panel onto the page as an ADF Form or an ADF Table. Figure 25-40 shows the filter view object dropped onto the page as a form.

    Figure 25-40 Filter dropped Onto a Page as an ADF Form

    Filter dropped onto a page as an ADF form
  3. Make sure the CreateInsert and Commit actions are included on the page.

    Note:

    These actions enable extemporaneous creation of new filter definitions at runtime; they also enable you to insert new records into the filter repository.

  4. If you dropped the view object onto the page as an ADF Table, select the filter column and, in the Property Inspector, set Sortable to false.

    Note:

    The sorting of filter columns is not supported.

  5. If you dropped the view object onto the page as an ADF Form, select the filter component and, in the Property Inspector, enter the name of the flexfield in the Label field. This name is used in the title of the filter popup dialog.

  6. By default, end users can choose to match on all conditions or any condition, as shown in Figure 25-41 and Figure 25-42. If you want restrict the filter to match on all conditions only, add the RestrictConjunctionToAND property and set it to true, as shown in Example 25-25 and Example 25-26

    Example 25-25 Modified Form-Based Filter Code

    <fnd:keyFlexFilter value="#{bindings.Kff1Fltr1_1Iterator}"
        accessor="kff1"
        label="#{bindings.Filter.hints.label}" 
        id="kff1"
        restrictConjunctionToAND="true"/>
    

    Example 25-26 Modified Table-Based Filter Code

    <af:column sortProperty="Filter" sortable="false"
               headerText="#{bindings.Kff1Fltr1_1.hints.Filter.label}"
               id="c2">
      <fnd:keyFlexFilter value="#{bindings.Kff1Fltr1_1Iterator}"
                         accessor="kff1" id="kff1"
        restrictConjunctionToAND="true"/>
    </af:column>
    

    When this property is set to true, the Filter dialog box does not display the Match options, as shown in Figure 25-43, and the conditions are automatically joined with an AND conjunction.

    Figure 25-41 Form-Based Combination Filter User Interface

    Form-based combination filter UI

    Figure 25-42 Table-Based Combination Filter User Interface

    Table-based combination filter UI

    Figure 25-43 Filter Dialog When RestrictConjunctionToAND is Set to true

    Filter dialog without Match options

25.6.3.2 What Happens When You Add a Filter Repository Filter to an Application Page

When you add a filter based on the public entity object FndKfEssFiltersPEO to your application page as an ADF Form, the resulting Oracle BI Publisher report submission user interface appears as shown in Figure 25-44.

Figure 25-44 Form-Based Report Submission Combination Filter UI

Form-based Report Submission combination filter UI

When you add a filter-repository filter to the application page as an ADF Table, the report submission user interface appears as shown in Figure 25-45.

Figure 25-45 Table-Based Report Submission Combination Filter UI

Table-based Report Submission combination filter UI

When you click CreateInsert, a new row is added which includes the filter XML and other required input, along with the default values for some of the columns. Your application must provide defaults for the columns as described in Table 25-11.

Table 25-11 Filter Repository Filter Attribute Columns

Column Description

KeyFlexfieldCode

The code identifying the key flexfield to which this filter will be applicable.This is a read-only value.

StructureInstanceNumber

This is the SIN, the discriminator attribute for the key flexfield which is used in the key flexfield filter. While creating a new filter definition or submitting a new job, a valid value should be defaulted for this attribute at the view object level. The SIN is required for capturing the filter XML. This is a read-only attribute.

DataSetNumber

The data set number (DSN) is a secondary discriminator to the SIN. If the key flexfield is data set–enabled, a valid DSN value should be defaulted in the filter view object. This is a read-only attribute.

FilterId

The FilterId is the primary key attribute and is a unique identifier for each filter that is inserted in the filter repository. This value can be generated using a sequence or other methods for generating unique identifiers.

ApplicationShortName

This is the application short name of the application with which the flexfield filter is associated.

You should set the default value to be the application with which your key flexfield is associated. For example, if you are using flexfield KFF1, which is associated with the Application Object Library application, your filter repository should set the default value for ApplicationShortName to be FND.

This is a read-only attribute.

Filter

This is the XML attribute containing the WHERE condition that is set for a particular FilterId. The WHERE condition has to be populated by using the filter user interface. Depending on the SIN, the filter user interface displays the related segments for a particular key flexfield structure. Various conditions for each of the segments can be applied to generate the WHERE condition.


When you click Commit, the new row is inserted in the FND_KF_ESS_FILTERS database table.

25.6.4 How to Create Combination Filter Definitions for Testing

For testing, you can use INSERT scripts to insert predefined XML filter criteria into the filter-condition column of your product table.

Note:

You can insert this data at any time after the filter-condition column has been added to the product table, and before the filter is invoked.

Use the following form to build an INSERT script:

Insert into filtercolumnname values(indexnum, 'filterconditionname', XMLType('filter_xml_code'))

The operators supported for key flexfield combination filters are the operators supported in the Query panel. This includes the following data types and their operators:

  • STRING data type — EQUALTO, NOTEQUALTO, CONTAINS, DOESNOTCONTAIN, LIKE, STARTSWITH, ENDSWITH, ISNULL, ISNOTNULL

  • NUMBER data type — EQUALTO, NOTEQUALTO, NULL, ISNOTNULL, GREATERTHAN, LESSTHAN, GREATERTHANEQUALTO, LESSTHANEQUALTO, BETWEEN, NOTBETWEEN

  • DATE data type — ISNULL, ISNOTNULL

You can also use the following hierarchical operators to query tree structures in your filter: IS_CHILD_OF, IS_DESCENDENT_OF, IS_LAST_DESCENDENT_OF, IS_PARENT_OF, IS_ANCESTOR_OF, IS_FIRST_ANCESTOR_OF, IS_SIBLING_OF.

For more information about trees, see Chapter 20, "Organizing Hierarchical Data with Tree Structures."

Example 25-27 shows some example scripts. The first one inserts a filter condition that selects for SEGMENT1_VARCHAR2 = 'Value04', and the second one selects for the inequality SEGMENT1_VARCHAR2 != 'Value02'.

Example 25-27 Scripts for Inserting Filter Conditions into the Application Database

Example of EQUALTO filter:

Insert into KFF1_FLTR values(
1, 'EqualToFilter',
XMLType('<?xml version ="1.0" encoding ="UTF-8"?>
<FndFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd">
  <KeyFlexFilter>
     <keyFlexfieldCode>KFF1</keyFlexfieldCode>
     <structureInstanceCode>VS_IND_CHR_ON_CHR</structureInstanceCode>
     <applicationShortName>FND</applicationShortName>
     <filterCriteriaRow>
        <filterCriteriaItem>
           <attributeName>_L10</attributeName>
           <columnName>SEGMENT1_VARCHAR2</columnName>
           <operator>EQUALTO</operator>
           <conjunction>AND</conjunction>
           <valueDataType>STRING</valueDataType>
           <value>Value04</value>
           <properties>
              <property>
                 <name>TestProp</name>
                 <value>ValueProp</value>
              </property>
           </properties>
        </filterCriteriaItem>
        <properties>
           <property>
              <name>TestProp</name>
              <value>ValueProp</value>
           </property>
        </properties>
     </filterCriteriaRow>
  </KeyFlexFilter>
</FndFilter>'));

Example of NOTEQUALTO filter:

Insert into KFF1_FLTR values(
2, 'NotEqualToFilter',
XMLType('<?xml version ="1.0" encoding ="UTF-8"?>
<FndFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd">
  <KeyFlexFilter>
     <keyFlexfieldCode>KFF1</keyFlexfieldCode>
     <structureInstanceCode>VS_IND_CHR_ON_CHR</structureInstanceCode>
     <applicationShortName>FND</applicationShortName>
     <properties>
        <property>
           <name>TestKff</name>
           <value>Valkff</value>
        </property>
     </properties>
     <filterCriteriaRow>
        <filterCriteriaItem>
           <attributeName>_L10</attributeName>
           <columnName>SEGMENT1_VARCHAR2</columnName>
           <operator>NOTEQUALTO</operator>
           <conjunction>AND</conjunction>
           <valueDataType>STRING</valueDataType>
           <value>Value02</value>
           <properties>
              <property>
                 <name>TestItemName</name>
                 <value>ValItem</value>
              </property>
           </properties>
        </filterCriteriaItem>
        <conjunction>AND</conjunction>
        <properties>
           <property>
              <name>TestRowName</name>
              <value>ValRow</value>
           </property>
        </properties>
     </filterCriteriaRow>
  </KeyFlexFilter>
</FndFilter>'));

Note:

You might want to test these scripts to ensure that the database is, in fact, performing schema validation on the XML document, by attempting to insert XML that does not conform to this schema.

25.6.5 How to Apply Combination Filters Using the PL/SQL Filter APIs

You can take advantage of key flexfield combination filters (including filter-repository filters) without using them in your application user interface. You use the WHERE clause API for standard and cross-validation combination filters, and you use the XML API for filter repository filters.

25.6.5.1 Applying Standard Filters Using the WHERE Clause API

Applications Core provides a PL/SQL API for filtering at the back end. This API takes a filter condition as an input parameter in XMLType format, converts it to a SQL WHERE clause snippet, and provides the clause as an output parameter for the segments upon which the filter condition has been defined. You use this API to integrate the WHERE clause snippet into your SQL statements to include the filter conditions within your SQL scripts.

The PL/SQL combination filter WHERE clause API is based on the signature shown in Example 25-28.

Example 25-28 WHERE Clause Signature

FND_KF_COMBINATION_FILTER_API.BuildWhereClause(
                               filter            IN XMLType,
                               tableAlias        IN Varchar2,
                               bindPrefix        IN Varchar2,
                               sin               OUT Number,
                               bindValues        OUT NOCOPY BIND_VAL_TAB,
                               filterWhereClause OUT NOCOPY Varchar2);
 
/** ---------------------------------------------------------------------------
--  This procedure takes the Filter as XMLType in parameter, tablealias,
--  bindprefix and computes the WHERE clause associated to the filter
--  and provides it as out parameter
--  Params
--     IN Params
--         filter      XMLType  - Filter to be converted to sql snippet
--         tableAlias  Varchar2 - Alias table name to be used in Sql snippet
--         bindPrefix  Varchar2 - Bind Prefix
--      OUT Params
--         sin         Number         - Structure Instance Number
--         bindValues  BIND_VAL_TAB   - List of Bind Values
--         filterWhereClause Varchar2 - Whereclause sql snippet
----------------------------------------------------------------------------*/
 

The bind values are defined as shown in Example 25-29.

Example 25-29 Bind Values Definition

create or replace PACKAGE FND_KF_COMBINATION_FILTER_API AS
 
  VARCHAR_TYPE CONSTANT Varchar2(20) :='VARCHAR2';
  NUMBER_TYPE  CONSTANT Varchar2(20) :='NUMBER';
  DATE_TYPE    CONSTANT Varchar2(20) := 'DATE';
 
TYPE BIND_VALUE IS RECORD(
         NAME            Varchar2(30),
         TYPE            Varchar2(20),
         VALUE_VARCHAR2  Varchar2(32767),
         VALUE_NUMBER    Number,
         VALUE_DATE      Date);
 
 TYPE BIND_VAL_TAB IS TABLE OF BIND_VALUE INDEX BY BINARY_INTEGER;

Example 25-30, Example 25-31, and Example 25-32 demonstrate how to use the WHERE clause API for an EQUALTO condition, a BETWEEN condition, and multiple conditions.

Example 25-30 Using the WHERE Clause API for an EQUALTO Condition

Suppose that a filter condition has been defined in a combinations table as follows:

  • Combinations table = FND_KF_TEST_CCT1

  • Filter column = SEGMENT1_VARCHAR2

  • Filter condition = 123

You would call the filter API as follows:

FND_KF_COMBINATION_FILTER_API.BuildWhereClause(
                              filter=>v_filter,
                              tableAlias => 'FKFF1',
                              bindPrefix => 'BND',
                              sin => v_sin,
                              bindValues => v_bind,
                              filterWhereClause => v_query);
 

The tableAlias value should be used in WHERE clause snippets to represent the combinations table name, so in this example,

FND_KF_TEST_CCT1.SEGMENT1_VARCHAR2
 

should be entered as

FKFF1.SEGMENT1_VARCHAR2
 

Similarly, the bindPrefix value should be used as a prefix when referencing individual bind values, for example, :BND1, :BND2, or :BND3.

When invoked, the filter API in this example might produce the following values for its output parameters:

filterWhereClause - FKFF1.SEGMENT1_VARCHAR2 = :BND1
sin               - 12
bindValues        - bindValues(1).NAME = BND1
                  - bindValues(1).TYPE = VARCHAR2
                  - bindValues(1).VALUE_VARCHAR2 = 123
 

With this output you can assemble the following WHERE clause for an EQUALTO filter condition:

select code_combination_id,
       Segment1_VARCHAR2
from FND_KF_TEST_CCT1 FKFF1
where FKFF1.structure_instance_number=12
and  FKFF1.SEGMENT1_VARCHAR2=123
 

Example 25-31 Using the WHERE Clause API for a BETWEEN Condition

The following listing shows an example of a BETWEEN operator used as part of a filter expression of the form "attribute BETWEEN value1 AND value2".

<?xml version ="1.0" encoding ="UTF-8"?>
<KeyFlexCodeCombinationFilter
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation=
"http://www.oracle.com/apps/fnd/applcore/flex/kff/KeyFlexCodeCombinationFilter.xsd">
  <keyFlexfieldCode>KFF1</keyFlexfieldCode>
  <structureInstanceCode>VS_FRM_NUM_ON_CHR</structureInstanceCode>
  <applicationShortName>FND</applicationShortName>
  <filterCriteriaRow>
    <filterCriteriaItem>
      <attributeName>_P6_S0</attributeName>
      <columnName>SEGMENT1_Varchar2</columnName>
      <operator>BETWEEN</operator>
      <conjunction>AND</conjunction>
      <valueDataType>NUMBER</valueDataType>
      <value>-500</value>
      <value>1000</value>
    </filterCriteriaItem>
  </filterCriteriaRow>
</KeyFlexCodeCombinationFilter>'));

The Filter expression captured in the preceding XML resolves to the following:

SEGMENT1_VARCHAR2 BETWEEN -500 and 1000

You would call the filter API as follows:

FND_KF_COMBINATION_FILTER_API.BuildWhereClause(
                              filter=>v_filter,
                              tableAlias => 'FKFF1',
                              bindPrefix => 'BND',
                              sin => v_sin,
                              bindValues => v_bind,
                              filterWhereClause => v_query);
 

When invoked, the filter API in this example might produce the following values for its output parameters:

filterWhereClause - FKFF1.SEGMENT1_VARCHAR2 = :BND1
sin               - 12
bindValues        - bindValues(1).NAME = BND1
                  - bindValues(1).TYPE = VARCHAR2
                  - bindValues(1).VALUE_VARCHAR2 = -500
                  - bindValues(2).NAME = BND2
                  - bindValues(2).TYPE = VARCHAR2
                  - bindValues(2).VALUE_VARCHAR2 = 1000
 

With this output you can assemble the following WHERE clause for a BETWEEN filter condition:

select code_combination_id,
       Segment1_VARCHAR2
from FND_KF_TEST_CCT1 FKFF1
where FKFF1.structure_instance_number=12
and  FKFF1.SEGMENT1_VARCHAR2 BETWEEN -500 AND 1000
 

Example 25-32 Using the WHERE Clause API for Multiple Conditions

The following listing shows an example of multiple operators used as part of a filter expression of the form "attribute1 EQUALTO value1 AND attribute2 EQUALTO value2."

<?xml version ="1.0" encoding ="UTF-8"?>
<KeyFlexCodeCombinationFilter
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/flex/kff/KeyFlexCodeCombinationFilter.xsd">
  <keyFlexfieldCode>KFF1</keyFlexfieldCode>
  <structureInstanceCode>VS_FRM_NUM_ON_CHR</structureInstanceCode>
  <applicationShortName>FND</applicationShortName>
  <filterCriteriaRow>
    <filterCriteriaItem>
      <attributeName>_P6_S0</attributeName>
      <columnName>SEGMENT1_Varchar2</columnName>
      <operator>EQUALTO</operator>
      <conjunction>AND</conjunction>
      <valueDataType>NUMBER</valueDataType>
      <value>123456</value>
    </filterCriteriaItem>
    <filterCriteriaItem>
      <attributeName>_P6_S2</attributeName>
      <columnName>SEGMENT2_Varchar2</columnName>
      <operator>EQUALTO</operator>
      <conjunction>AND</conjunction>
      <valueDataType>NUMBER</valueDataType>
      <value>123.45</value>
    </filterCriteriaItem>
  </filterCriteriaRow>
</KeyFlexCodeCombinationFilter>'));

The Filter expression captured in the preceding XML resolves to the following:

SEGMENT1_VARCHAR2 = 123456 and SEGMENT2_VARCHAR2 = 123.45

You would call the filter API as follows:

FND_KF_COMBINATION_FILTER_API.BuildWhereClause(
                              filter=>v_filter,
                              tableAlias => 'FKFF1',
                              bindPrefix => 'BND',
                              sin => v_sin,
                              bindValues => v_bind,
                              filterWhereClause => v_query);
 

When invoked, the filter API in this example might produce the following values for its output parameters:

filterWhereClause - FKFF1.SEGMENT1_VARCHAR2 = :BND1
                  - FKFF1.SEGMENT2_VARCHAR2 = :BND2
sin               - 12
bindValues        - bindValues(1).NAME = BND1
                  - bindValues(1).TYPE = VARCHAR2
                  - bindValues(1).VALUE_VARCHAR2 = 123456
                  - bindValues(2).NAME = BND2
                  - bindValues(2).TYPE = VARCHAR2
                  - bindValues(2).VALUE_VARCHAR2 = 123.45
 

With this output you can assemble the following WHERE clause for a BETWEEN filter condition:

select code_combination_id,
       Segment1_VARCHAR2, Segment2_VARCHAR2
from FND_KF_TEST_CCT1 FKFF1
where FKFF1.structure_instance_number=12
and  FKFF1.SEGMENT1_VARCHAR2 = 123456
and  FKFF1.SEGMENT2_VARCHAR2 = 123.45
 

25.6.5.2 Applying Repository Filters for Oracle Enterprise Scheduler Service

The kff_filter PL/SQL procedure in the fnd_flex_xml_publisher_apis.pkb package is the public procedure for processing key flexfield repository filter lexicals. The signature is shown in Example 25-33.

Example 25-33 kff_filter Signature

/* PUBLIC PROCEDURE kff_filter EXPOSED FOR THIS PACKAGE */
 
PROCEDURE kff_filter
  (p_lexical_name                 IN VARCHAR2,
   p_application_short_name       IN fnd_application.application_short_name%TYPE,
   p_key_flexfield_code           IN fnd_kf_flexfields_b.key_flexfield_code%TYPE,
   p_filter_id                    IN NUMBER,
   p_code_combination_table_alias IN VARCHAR2,
   x_where_expression             OUT nocopy VARCHAR2,
   x_numof_bind_variables         OUT nocopy NUMBER,
   x_bind_variables               OUT nocopy bind_variables);
 

Example 25-34 demonstrates how to use this API to obtain the WHERE clause and bind variable information for a filter in the filter repository.

Example 25-34 Using the Filter Repository API

REM dbdrv: none
SET SERVEROUTPUT ON
WHENEVER SQLERROR CONTINUE
DECLARE
  l_tableAlias VARCHAR2(30);
  l_applicationShortName fnd_application.application_short_name%TYPE;
  l_keyFlexfieldCode fnd_kf_flexfields_b.key_flexfield_code%TYPE;
  l_filterWhereClause         VARCHAR2(32767);
  l_filterId                  NUMBER;
  l_filterName                VARCHAR2(32);
  l_bindVariables fnd_flex_xml_publisher_apis.bind_variables;
  l_numOfBindVariables NUMBER;
  CURSOR c_filter_id
  IS
    SELECT filter_id FROM fnd_kf_ess_filters;
BEGIN
 
    l_filterName           := 'DefaultFilter';
    l_tableAlias           := 'DefaultTable';
    l_keyFlexfieldCode     := 'KFF1';
    l_applicationShortName := 'FND';
    DBMS_OUTPUT.PUT_LINE('kff_filter');
    FOR filter_id IN c_filter_id
    LOOP
 
      fnd_flex_xml_publisher_apis.kff_filter(p_lexical_name=>l_filterName,
      p_application_short_name=>l_applicationShortName,
      p_key_flexfield_code=>l_keyFlexfieldCode,
      p_filter_id=>filter_id.filter_id,
      p_code_combination_table_alias=>l_tableAlias,
      x_where_expression=>l_filterWhereClause,
      x_numof_bind_variables=>l_numOfBindVariables,
      x_bind_variables=>l_bindVariables);
      DBMS_OUTPUT.PUT_LINE('filter Id: ' || filter_id.filter_id);
      DBMS_OUTPUT.PUT_LINE('filter Where Clause: ' || l_filterWhereClause);
    END LOOP;
  END;

25.6.6 How to Remove Combination Filters from Your Application

To remove a key flexfield combination filter, you remove the accessor that was previously created to associate the filter with a particular key flexfield.

In your project, right-click the view object that contains the filter and select Remove Flexfield Filters from the menu.

If the filter view object has more than one filter attribute with an accessor defined, you will be presented with a list of those filter accessors. Select the one that you want to remove.

25.6.7 How to Remove Filters from the Filter Repository

The filter XML is stored in the FND_KF_ESS_FILTERS table. The number of rows in a filter repository can become large. You use the kff_filter_purge(...) procedure from the fnd_flex_xml_publisher_apis PL/SQL package to purge unused filters from the filter repository. This procedure takes the filter's id, as shown in Example 25-35.

Example 25-35 Removing a Filter From the Filter Repository

DBMS_OUTPUT.PUT_LINE('kff_filter_purge to delete a valid filter');
l_filterId := 1001; 
--valid filter id
fnd_flex_xml_publisher_apis.kff_filter_purge(p_filter_id=>l_filterId);
DBMS_OUTPUT.PUT_LINE('Filter Id: ' || l_filterId);
DBMS_OUTPUT.PUT_LINE('VALID FILTER_ID DELETED SUCCESSFULLY');