To implement a key flexfield you must:
Define key flexfield columns in your database
Register your table with Oracle Application Object Library
Register your key flexfield with Oracle Application Object Library
Create key flexfield fields in your forms
Add key flexfield routines to your forms
Key flexfields can be implemented for the following three types of forms, which are each implemented differently:
Combinations form - The only purpose of a combinations form is to create and maintain code combinations. The combinations table (or a view of it) is the base table of this form and contains all the key flexfield segment columns. The combinations table also contains a unique ID column. This type of form is also known as a maintenance form for code combinations. You would have only one combinations form for a given key flexfield (though you might not have one at all). You cannot implement shorthand flexfield entry for a combinations form.
Form with foreign key reference - The base table (or view) of the form contains a foreign key reference to a combinations table that contains the actual flexfield segment columns. You create a form with a foreign key reference if you want to use your form to manipulate rows containing combination IDs. The primary purpose of foreign key forms is generally unrelated to the fact that some fields may be key flexfields. That is, the purpose of the form is to do whatever business function is required (such as entering orders, receiving parts, and so on). You might have many foreign key forms that use a given key flexfield. You can choose to implement shorthand flexfield entry only for a form with a foreign key reference.
Form with key flexfield range - The base table is a special "combinations table" that contains two columns for each key flexfield segment so it can support both low and high values for each segment of your key flexfield. This type of form is rare.
For many applications, you would have one combinations form that maintains the key flexfield, where the key flexfield is the representation of an entity in your application. Then, you would also have one or more forms with foreign key references to the same key flexfield. For example, in an Order Entry/Inventory application, you might have a combinations form where you define new parts with a key flexfield for the part numbers. You would also have a form with foreign key reference where you enter orders for parts, using the key flexfield to indicate what parts make up the order.
Further, you can have another form, a form with a key flexfield range, that you use to manipulate ranges of your part numbers. This range flexfield form refers to the same key flexfield as both your combinations forms and your foreign key forms, though the ranges of segment values (a low value and a high value for each segment) are stored in the special range flexfield table that serves as the range form's base table.
A special kind of key flexfield you can include in your application to support low and high values for each key segment rather than just single values. Ordinarily, a key flexfield range appears on your form as two adjacent flexfields, where the leftmost flexfield contains the low values for a range, and the rightmost flexfield contains the high values.
In Oracle Application Object Library, we use a key flexfield range to help you specify cross-validation rules for valid combinations.
For each key flexfield you design into your application, you must create a combinations table to store the flexfield combinations that your users enter. You can build a special form to let them define valid combinations (the combinations form), or you can let Oracle Application Object Library dynamically create the combinations when users attempt to use a new one (from a form with a foreign key reference). You must have the combinations table even if you do not build a combinations form to maintain it. Key flexfields provided by Oracle Applications already have combinations tables defined.
In addition to the combinations table for your key flexfield, you may also have one or more tables for forms with foreign key references and for forms with key flexfield ranges.
Key flexfields support a maximum of 70 segment columns in a combinations table. For example, a combinations table includes a column for the unique ID that your key flexfield assigns to each valid combination. It also includes a structure defining column, in case your end user wants to define multiple structures. If you want to use segment qualifiers in your application, your table should include a derived column for each segment qualifier you define.
To create a key flexfield combinations table for your application entity, you must:
Define an ID column to uniquely identify a row in your database table (type NUMBER, length 38, NOT NULL). You should name this column XXX_ID, where XXX is the name of your entity (for example, PART_ID). This column holds the unique ID number of a particular combination of segment values (also known as a code combination). The unique ID number is also known as a code combination ID, or CCID. Note that even though this column is a NUMBER(38) column, Oracle Application Object Library only supports code combination IDs up to two billion (2,000,000,000).
Define a column for each key segment, SEGMENT1 through SEGMENTn, where n is the number of segments you want for your key flexfield (type VARCHAR2, length 1 to 60, all columns the same length, NULL ALLOWED). As a rule of thumb, you should create about twice as many segment columns as you think your users might ever need for a single key flexfield structure. The maximum number of key flexfield segment columns that Oracle Application Object Library supports in a combinations table is 70. However, for a combinations table that you want to use with a form with a foreign key reference, the number of segments is also limited by the maximum size of the field that holds the concatenated segment values and segment separators. That field is normally 2000 characters, so if you have 40 segments and 40 separators, each segment could only have an average width of about 49 characters. Having more segment columns than you need does not significantly impact either space requirements or performance. In fact, since you cannot add more segment columns to a flexfield combinations table once you have registered your flexfield, you should add at least a few "extra" segment columns to your combinations table initially to allow for future needs.
Define SUMMARY_FLAG and ENABLED_FLAG (type VARCHAR2, length 1, NOT NULL).
Define START_DATE_ACTIVE and END_DATE_ACTIVE (type DATE, NULL).
Define a structure defining column (structure ID column) to allow multiple structures (type NUMBER, length 38, NOT NULL). You should name this column XXX_STRUCTURE_ID, where XXX is the name of your entity (for example, PART_STRUCTURE_ID). This column is optional but strongly recommended.
Define a unique index on the unique ID column.
Create an ORACLE sequence for your column with the same grants and synonyms as your combinations table (for insert privileges). Name your sequence YOUR_TABLE_NAME_S.
Define the Who columns, LAST_UPDATE_DATE (type DATE, NOT NULL) and LAST_UPDATED_BY (type NUMBER, length 15, NOT NULL). All other Who columns should have NULL ALLOWED.
If you want your application to allow dynamic insertion of new valid combinations from a form with a foreign key reference, you must not include any mandatory application-specific columns in your combinations table. Your combinations table contains only the columns you need to define a key flexfield, such as unique ID, structure defining, and segment columns. It can, however, include non-mandatory application-specific columns and columns for derived segment qualifier values. If you include mandatory application-specific columns in your combinations table, you cannot allow dynamic insertion of new valid combinations from a form with a foreign key reference. If your table does not allow dynamic insertion, you must create a combinations form, based on your combinations table, for your users to create their valid combinations.
If you do not ever want to allow dynamic insertion of new valid combinations, you should develop a single form that allows your end user to directly display, enter, or maintain valid combinations in your combinations table (a combinations form). You can set up your key flexfield to not allow dynamic inserts (on a structure-by-structure basis) even if dynamic inserts are possible.
Warning: You should never insert records into a code combinations table through any mechanism other than Oracle Application Object Library flexfield routines. Doing so could lead to serious data corruption problems and compromise your applications.
For each table you use as a base table for a form with a foreign key reference (to a combinations table's unique ID column), define one database column with the same name as the unique ID column in the corresponding combinations table (type NUMBER, length 38, and NULL or NOT NULL depending on your application's needs).
If you have a structure column in your combinations table, you also need to include a structure column in your foreign key table (with a corresponding form field), or provide some other method for passing the structure ID number to the NUM parameter in your calls to key flexfield routines. For example, you could store the structure number in a profile option and use the option value in the NUM parameter.
You do not need any SEGMENTn columns or other key flexfield columns for this type of table.
To create a table that supports a key flexfield range instead of a foreign key reference to a single combination, define SEGMENTn_LOW and SEGMENTn_HIGH columns, one pair for each SEGMENTn column in your combinations table (type VARCHAR2, length 1 to 60, all columns the same length, NULL).
If you have a structure column in your combinations table, you also need to include a structure column in your range table (with a corresponding form field), or provide some other method for passing the structure ID number to the NUM parameter in your calls to key flexfield routines. For example, you could store the structure number in a profile option and use the option value in the NUM parameter.
You do not need any other flexfield columns for this table.
After you create your combinations table, you must register your table with Oracle Application Object Library using the Table Registration API.
Once your table is successfully registered, you register your key flexfield with Oracle Application Object Library. You register your key flexfield using the Key Flexfields window.
When you register a key flexfield, you identify the combinations table in which it resides, as well as the names of the unique ID and structure defining columns. Key flexfields provided by Oracle Applications are already registered.
When you register a key flexfield, you can define flexfield and segment qualifiers for it.
You should define flexfield qualifiers if you want to ensure your end user customizes your key flexfield to include segments your application needs. For example, Oracle General Ledger defines account and balancing flexfield qualifiers in the Accounting Flexfield to ensure that end users would define account and balancing segments.
You should define segment qualifiers if your application needs to know semantic characteristics of key segment values your end user enters. You assign one or more segment qualifiers to each flexfield qualifier. For example, Oracle General Ledger assigns a segment qualifier of "account type" to the flexfield qualifier "account" in the Accounting Flexfield. As a result, end users can define account value 1000 to mean "Cash," and assign it a segment qualifier value of "Asset."
Note that flexfield qualifiers can be unique or global, and required or not. You describe a flexfield qualifier as unique if you want your end user to tie it to one segment only. You describe a flexfield qualifier as global if you want it to apply to all segments. You can use a global flexfield qualifier as a convenient means for assigning a standard set of segment qualifiers to each of your flexfield's segments. You describe a flexfield qualifier as required if you want your end user to tie it to at least one segment.
In Oracle General Ledger's Accounting Flexfield, the "Account" flexfield qualifier is required and unique because Oracle General Ledger requires one and only one account segment. Oracle General Ledger defines a flexfield qualifier as "global" so the segment qualifiers "detailed posting allowed" and "detailed budgeting allowed" apply to each Accounting Flexfield segment. For more information, see: Oracle General Ledger User's Guide, Oracle Applications Flexfields Guide.
A column you include in a combinations table into which your flexfield derives a segment qualifier value. You specify the name of a derived column when you define a segment qualifier.
Once you have the appropriate table columns and your flexfield is registered, you can build your flexfield into your application forms.