Skip Headers

Oracle E-Business Suite Developer's Guide
Release 12.1
Part Number E12897-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Flexfields

Overview of Flexfields

A flexfield is a field made up of segments. Each segment has a name you or your end users assign, and a set of valid values. There are two types of flexfields: key flexfields and descriptive flexfields.

For an explanation of flexfields features and concepts, as well as information on setting up flexfields in Oracle E-Business Suite, see the Oracle E-Business Suite Flexfields Guide. For information on entering and querying data using flexfields, see the Oracle E-Business Suite User's Guide.

Key Flexfields

Most businesses use codes made up of meaningful segments (intelligent keys) to identify accounts, part numbers, and other business entities. 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 users with a flexible "code" data structure that users can set up however they like using key flexfield segments. Key flexfields let your users 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.

In another example, Oracle General Ledger uses a key flexfield called the Accounting Flexfield to uniquely identify a general ledger account. At Oracle, we have customized this Accounting Flexfield to include six segments: company code, cost center, account, product, product line, and sub-account. We have also defined valid values for each segment, as well as cross-validation rules to describe valid segment combinations. However, other companies might structure their general ledger account fields differently. By including the Accounting Flexfield key flexfield, Oracle General Ledger can accommodate the needs of different companies. One company can customize the Accounting Flexfield to include six segments, while another company includes twelve segments, all without programming.

A key flexfield represents an intelligent key that uniquely identifies an application entity. Each key flexfield segment has a name you assign, and a set of valid values you specify. Each value has a meaning you also specify. Oracle General Ledger's Accounting Flexfield is an example of a key flexfield used to uniquely identify a general ledger account.

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 only be available in certain colors.

Descriptive Flexfields

Descriptive flexfields let you satisfy different groups of users without having to reprogram your application, by letting you provide customizable "expansion space" on your forms. For example, suppose you have a retail application that keeps track of customers. Your Customers form would normally include fields such as Name, Address, State, Customer Number, and so on. However, your form might not include extra fields to keep track of customer clothing size and color preferences, or regular salesperson, since these are attributes of the customer entity that depend on how your users use your application. For example, if your retail application is used for a tool company, a field for clothing size would be undesirable. Even if you initially provide all the fields your users need, your users might later identify even more customer attributes that they want to keep track of. You add a descriptive flexfield to your form so that your users have the desired expansion space. Your users can also take advantage of the fact that descriptive flexfields can be context sensitive, where the information your application stores depends on other values your users enter in other parts of the form.

A descriptive flexfield describes an application entity, providing form and database expansion space that you can customize. Each descriptive segment has a name you assign. You can specify valid segment values or set up criteria to validate the entry of any value.

Oracle General Ledger includes a descriptive flexfield in its journal entry form to allow end users to add information of their own choosing. For example, end users might want to capture additional information about each journal entry, such as source document number or the name of the person who prepared the entry.

You could use a descriptive flexfield in a fixed assets application you build to allow further description of a fixed asset. You could let the structure of your assets flexfield depend on the value of an asset type field. For example, if asset type were "desk", your descriptive flexfield could prompt for style, size and wood type. If asset type were "computer", your descriptive flexfield could prompt for CPU chip and memory size.

Easy Customization

Flexibility is important. There is no way for you to anticipate all the form and database fields your end users might want, nor how each field should look as end user needs change. Using key and descriptive flexfields, you give end users the ability to customize your application to match their business needs, without programming. You should build a flexfield into your application whenever you need a flexible data structure.

Customizing a flexfield means specifying the prompt, length and data type of each flexfield segment. It also includes specifying valid values for each segment, and the meaning of each value to your application. You or your end users can even define cross-validation rules to specify valid combinations of segment values.

Ordinarily, your end users customize flexfields during application installation. However, you, as a developer, can customize flexfields while you are developing your application. Even if end users never change a flexfield once you have customized it, they can take advantage of useful flexfield features such as automatic segment validation, automatic segment cross-validation, multiple segment structures, and more.

Multiple Structures for a Single Flexfield

In some applications, different users need different segment structures for the same flexfield. Or, you might want different segments in a flexfield depending on, for example, the value of another form or database field.

Flexfields lets you define multiple segment structures for the same flexfield. Your flexfield can display different prompts and fields for different end users based on a data condition in your form or application data.

Oracle General Ledger, for example, provides different Accounting Flexfield structures for users of different sets of books. Oracle General Ledger determines which flexfield structure to use based on the value of a Set of Books user profile option.

Standard Request Submission Parameters

Most of the features used with your flexfield segments also apply to your parameter window for Standard Request Submission programs. For example, you can define security rules and special value sets for your report parameters.

Definitions

For more explanation of flexfields features and concepts, see the Oracle E-Business Suite Flexfields Guide.

Segment

For a key flexfield, a segment is a single piece of the complete code. For a descriptive flexfield, a segment is a single field or a single attribute of the entity. A segment is represented by a single column in a table.

Combination

For a key flexfield, a combination of segment values that make up the complete code or key. You can define valid combinations with simple cross-validation rules when you customize your key flexfield. Groups of valid combinations can be expressed as ranges.

Structure

A flexfield structure is a particular arrangement of flexfield segments. The maximum size of the structure depends on the individual flexfield. A flexfield may have one or more structures. Both key and descriptive flexfields can have more than one structure. Users can tailor structures for specific needs.

Combinations Table

For a key flexfield, a database table you include in your application to store valid combinations of key flexfield segment values. Each key flexfield must have a combinations table. It contains columns for each flexfield segment, as well as other columns. This is the same table you use as your entity table.

Combinations Form

For a key flexfield, a combinations form is the form whose base table (or view) is the combinations table. The only purpose of the combinations form is to maintain the combinations table. Most key flexfields have one combinations form, although some key flexfields do not have a combinations form. Key flexfields without combinations forms are maintained from other forms using dynamic insertion.

Dynamic Insertion

Dynamic insertion is the insertion of a new valid combination into a key flexfield combinations table from a form other than the combinations form.

For key flexfields whose combinations table does not contain any mandatory columns other than flexfield and WHO columns, you can choose to allow dynamic inserts when you set up your key flexfield. If you allow dynamic inserts, your user can enter new combinations of segment values using the flexfield window from a form other than the combinations form. If your end user enters a new combination that satisfies cross-validation rules, your flexfield dynamically inserts it into the combinations table. Otherwise, a message appears and the user is required to correct the segment values that violate the cross-validation rules.

If you create your key flexfield using a combinations table that contains mandatory columns other than flexfield or WHO columns, you cannot allow dynamic inserts, and your end user cannot enter new combinations through the flexfield window from any form other than the combinations form.

Flexfield Qualifier

A flexfield qualifier identifies a segment your end user should define when customizing your key flexfield. By specifying flexfield qualifiers when you build your application, you ensure your end user customizes your flexfield to include key segments that your application needs.

For example, suppose you build a general ledger accounting application that uses a key flexfield to uniquely identify accounts. Your application requires that one key segment be an account segment, and one be a balancing segment. You ensure your end user defines these key segments by defining two flexfield qualifiers, account and balancing. When customizing your accounting flexfield, your end user ties the account and balancing flexfield qualifiers to particular key segments. You, as the developer, need not know which key segment becomes the account or balancing segment, because the key flexfield takes care of returning account and balancing information to your application at run-time.

Segment Qualifier

A segment qualifier describes characteristics of key segment values. You use segment qualifiers to obtain information about segment values your end user enters while using your application.

For example, suppose your end user enters a value in the account segment of a flexfield that uniquely identifies general ledger accounts. Since you, as the developer, do not know which segment represents account, your application cannot reference the account value directly. However, you can construct your application so that each account value has an associated segment qualifier called "Account type" that your application can easily reference.

Assume that account value 1000 (which means "Cash") has an account type of "Asset". Your application can reference this account type because your key flexfield returns it to a column you designate in your generic combinations table. Your application can contain logic that is conditional on account type.

You can define segment qualifiers when you define flexfield qualifiers. You can assign one or more segment qualifiers to each flexfield qualifier.

Structure Defining Column

A column you include in a combinations table or entity table so the flexfield can support multiple segment structures. You can construct your application so that it places a value in a structure defining column to determine the flexfield segment structure your end user sees.

For example, Oracle General Ledger places a "Chart of Accounts" identifier in the structure defining column of the combinations table for the Accounting Flexfield. As a result, Oracle General Ledger can provide different Accounting Flexfield structures (different charts of accounts) for different users.

Building a Flexfield into Your Application

To include a flexfield in an application you are building, you perform the following steps.

First, you decide which application entities require key or descriptive flexfields. You use a key flexfield to uniquely identify an entity that needs an intelligent key.

Important: We provide key flexfield information such as combinations table structure and form syntax. You may use this information to integrate your custom forms and applications with key flexfields that Oracle E-Business Suite provides. For example, you may build foreign key forms that call Oracle E-Business Suite key flexfields. However, the API for key flexfields may change in future versions of Oracle E-Business Suite, so we recommend that you do not create any new key flexfields that are not provided by Oracle E-Business Suite.

You use a descriptive flexfield to provide context-sensitive expansion space for carrying additional information about an entity. To maximize your user's flexibility, you should consider defining a descriptive flexfield for every entity in your application.

After deciding that an application entity requires a flexfield, you design the flexfield into your applications database. You register the flexfield with Oracle Application Object Library, and if you like, assign flexfield and segment qualifiers for your key flexfields. Then, you develop application forms that include your flexfield and call Oracle Application Object Library routines to activate it.

After you are done defining a flexfield, you or your end user can customize it to include a specific set of segments.

Designing Flexfields into Your Application Database

You include flexfield columns in the database table that represents the application entity for which you are defining a flexfield. You include one column for each flexfield segment you or your end user might wish to customize. You need at least as many columns as the maximum number of segments a user would ever want in a single flexfield structure. If you have more segments than can fit on your screen when the flexfield window is open, you can scroll through them vertically.

For a key flexfield, a combinations table represents the application entity. A combinations table includes flexfield segment columns as well as other columns a key flexfield requires. Key flexfields provided by Oracle E-Business Suite already have combinations tables defined.

To permit the use of flexfield combinations from different application forms, you must include foreign key references to your combination table's unique ID column in other application 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 Oracle E-Business Suite key flexfields, you would include foreign key references in your custom application tables wherever you reference the flexfield.

To define a descriptive flexfield, you include descriptive segment columns in the application table you choose. You also include a structure defining column (sometimes called a context column), in case your end user wants to define multiple segment structures.

See: Implementing Key Flexfields Implementing Descriptive Flexfields

Registering a Flexfield

You register a flexfield with Oracle Application Object Library after you design it into your database. By registering a flexfield, you notify Object Library that your flexfield exists in the database, and provide some basic information about it.

When you register a flexfield, you give it a name that end users see when they open your flexfield pop-up window (for example, "Accounting Flexfield" or "Vendor Flexfield"). End users can change the flexfield name you provide when they customize your flexfield.

Building a Flexfield into a Form

To add a flexfield to a form, you define hidden form fields to represent the flexfield columns you defined in your application table (that is, unique ID, structure defining, segment, and other columns). You also define a visible form field to hold the concatenated segment value string that appears on your form after your end user enters segment values. You can optionally include a visible form field to hold a concatenated string of the meanings of each segment.

To activate your flexfield, you call Oracle Application Object Library routines from your form's triggers.

See: Implementing Key Flexfields Implementing Descriptive Flexfields

Flexfields and Application Upgrades

Application upgrades do not affect the flexfields you have defined or customized. However, you may have to recompile your flexfields for some application upgrades. You recompile your key flexfields using the Key Flexfield Segments form, and you use the Descriptive Flexfield Segments form to recompile descriptive flexfields. Simply scroll through and save each row that defines your flexfield, and the form automatically recompiles your flexfield.

You can also recompile all of your frozen flexfields in one step from the operating system. See your installation manual for more information about compiling all your flexfields in one step after an application upgrade.

See:

Implementing Key Flexfields

Oracle E-Business Suite Flexfields Guide

Implementing Key Flexfields

To implement a key flexfield you must:

Key flexfields can be implemented for the following three types of forms, which are each implemented differently:

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.

Key Flexfield Range

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.

Defining Key Flexfield Database Columns

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 E-Business Suite 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.

Combinations table

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:

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.

Table with a foreign key reference

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.

Table for a form with a key flexfield range

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.

Registering Your Key Flexfield Table

After you create your combinations table, you must register your table with Oracle Application Object Library using the Table Registration API.

Registering Your Key Flexfield

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 E-Business Suite are already registered.

Defining Qualifiers for Key Flexfields

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 E-Business Suite Flexfields Guide.

Derived Column

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.

Add Your Flexfield to Your Forms

Once you have the appropriate table columns and your flexfield is registered, you can build your flexfield into your application forms.

See: Adding Flexfields to Your Forms

Implementing Descriptive Flexfields

You add a descriptive flexfield to provide customizable "expansion space" for your entity. For example, suppose you have a retail application that keeps track of customer entities. Your entity table, CUSTOMERS, would normally include columns such as Name, Address, State, Sex, Customer Number, and so on. However, your table might not include extra columns to keep track of a customer's size and color preferences, or regular salesperson, since these are attributes of the customer entity that depend on how your users use your application. In fact, your users might later identify even more customer attributes that they want to keep track of. You add descriptive flexfield columns to your entity table (CUSTOMERS) so that your users have the desired expansion space. Your users can also take advantage of the fact that descriptive flexfields can be context sensitive, where the information your application stores depends on other values your users enter in the Customers form.

To implement a descriptive flexfield you must:

Planning for Reference Fields

Reference fields are fields from which a descriptive flexfield can get a context field value (optional, but recommended). Reference fields must be separate fields from the structure defining field (typically ATTRIBUTE_CATEGORY). Frequently, most of the existing (non-flexfield) fields in your form can also serve as reference fields. In general, fields that make good reference fields are those that have a short, fairly static list of possible values. You specify fields as reference fields when you register your descriptive flexfield in the Register Descriptive Flexfield form. Your users then have the option of using a reference field or not when they set up your flexfield.

For example, suppose you have a retail application that keeps track of "customer" entities. Your Customers form would normally include fields such as Name, Address, State, Sex, Customer Number, and so on. Your end users may want to make the descriptive flexfield context-sensitive depending on what a user enters in the State field (if the state is Colorado, for example, you may want to keep track of customer preferences in ski-wear, while if the state is Florida, you may want to keep track of preferences in warm-weather-wear). Alternatively, your end users may want to make the descriptive flexfield context-sensitive depending on what a user enters in the Sex field (if the customer is female, for example, you may want to keep track of her size preferences using standard women's sizes, while if the customer is male, you may want to keep track of size preferences using standard men's sizes). By specifying both the State field and the Sex field as reference fields when you register your descriptive flexfield in the Register Descriptive Flexfield form, you give your users the option to set up the flexfield either way.

Tip: A descriptive flexfield can use only one form field as a reference field. You may derive the context field value for a descriptive flexfield based on more than one field by concatenating values in multiple fields into one form field and using this concatenated form field as the reference field.

Defining Descriptive Flexfield Database Columns

To make your application very flexible, you should add descriptive flexfield columns to all of your entity tables.

Oracle Application Object Library reserves table names that contain the string "_SRS_" for the Standard Request Submission feature, so you should not give your descriptive flexfield table a name that includes this string.

To add descriptive flexfield columns into your database table, you:

You should ensure you initially add enough segment columns to cover any future uses for your descriptive flexfield, since you cannot add extra segment columns to your flexfield later.

You determine the maximum number of segments you can have within a single structure when you define your ATTRIBUTEn columns in your table. You can define a maximum of 200 ATTRIBUTEn columns in one table. 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 descriptive flexfield structure.

Adding a Descriptive Flexfield to a Table with Existing Data

You can add flexfield columns to a table that has never had any flexfield columns but already contains data. However, you must be very careful not to create data inconsistencies in your application when you do so. To add your flexfield, you add columns, form fields, and invoke descriptive flexfield routines exactly the same as if you were creating a descriptive flexfield from the beginning. However, when you define your flexfield using the Descriptive Flexfield Segments form, you must consider whether any of the segments should use value sets that require values. If none of your new segments requires a value, your users will simply see an empty descriptive flexfield when they query up existing records. For this case, no further action is necessary.

For the case where one or more of your segments require values, you need to perform extra steps to prevent data inconsistencies. The simplest way to do this is to define your segment structures completely, navigate to your form with the new descriptive flexfield, query up each record in your table, and enter values in the descriptive flexfield for each record. Save your changes for each record. This method, while tedious, ensures that all values go into the correct columns in your entity table, including the structure defining (context) column.

For very large tables, you can add the values to your table directly using SQL*Plus. You need to update each row in your table to include a context field value (the structure defining column) as well as segment values, so you must first determine the segment/column correspondences for your structures. Your context (structure) values must exactly match your context field values in the Descriptive Flexfield Segments form. For example, if your context field value is mixed case, what you put in the structure column must match the mixed case. If you put an invalid context value into the structure column, a purely context-sensitive flexfield does not pop up at all for that record. If you have global segments enabled, the flexfield window will open. If Override Allowed is set to Yes, you will see the bad context field value in the context field of the window.

Note that you should never use SQL*Plus to modify data in Oracle Application Object Library tables.

Protected Descriptive Flexfields

In some cases, you may want to create a descriptive flexfield that cannot be inadvertently changed by an installer or user. This type of flexfield is called a protected descriptive flexfield. You build a protected descriptive flexfield the same way you build a normal descriptive flexfield. The main difference is that you check the Protected check box in the Descriptive Flexfields form after defining your segment structures. Once a descriptive flexfield is protected, you cannot query or change its definition using the Descriptive Flexfield Segments form. You should define your descriptive flexfield segments before you check the Protected check box in the Descriptive Flexfields form.

In a case where your database table already includes a descriptive flexfield, you need to define segment columns that have names other than ATTRIBUTEn. For special purpose flexfields such as protected descriptive flexfields, you can name your columns anything you want. You explicitly enable these columns as descriptive flexfield segment columns when you register your descriptive flexfield. Note that you must also create a structure-defining column for your second flexfield. Flexfields cannot share a structure column.

If your database table contains segment columns with names other than ATTRIBUTEn, you create hidden fields corresponding to those columns instead.

Registering Your Descriptive Flexfield Table

After you add descriptive flexfield columns to your table, you must register your table with Oracle Application Object Library using the Table Registration API.

See: Table Registration API.

Registering Your Descriptive Flexfield

You must register your descriptive flexfield with Oracle Application Object Library. You register your descriptive flexfield using the Register Descriptive Flexfield form. When you register a descriptive flexfield, you identify the application table in which it resides and the name of the structure defining column. If you have created reference fields in your form, you should enter their names as "context fields" when you register your flexfield.

Add Your Flexfield to Your Forms

Once you have the appropriate table columns and your flexfield is registered, you can build your flexfield into your application forms.

See:Adding Flexfields to Your Forms

Adding Flexfields to Your Forms

There are four basic parts to calling a flexfield from an Oracle Forms window. These steps assume that your flexfield is already registered and defined in Oracle Application Object Library and that the flexfield table and columns already exist. These steps apply to both key and descriptive flexfields.

To code a flexfield into your form:

Create Your Hidden Fields

In general, you create your hidden flexfield fields as part of creating your default form block from the database table (or view). Set the canvas property of the flexfield fields to null (so they do not appear on a canvas).

Your hidden ID (for key flexfields only), structure field, and segment or attribute fields must be text items on the null canvas. Note that these must be text items rather than display items, and that they should use the TEXT_ITEM property class. Set the field query lengths to 255 for most fields, with a query length of 2000 for hidden ID fields.

Important: You should never create logic that writes values to the hidden fields directly. Since the flexfield keeps track of whether a record is being inserted, updated, etc., putting values in these fields by any method other than the flexfield itself (or a query from the database) may cause errors and data corruption.

In some foreign key forms for key flexfields, you may need to create extra non-database fields that represent the segment columns (SEGMENT1 through SEGMENTn) in your combinations table. Put your SEGMENT1 through SEGMENTn fields on the null canvas (field length the same as your SEGMENTn columns). These fields help Oracle Application Object Library to create new code combinations from your form with a foreign key reference (using dynamic insertion).

Normally, Oracle Application Object Library can create new code combinations (dynamic insertion) from your form with a foreign key reference using only the concatenated segment values field. However, if you expect the concatenated length of your flexfield to be defined to be larger than 2000 (the sum of the defined segments' value set maximum sizes plus segment separators), then you should create these non-database fields to support the dynamic creation of new combinations from your form.

If you do not have these fields and your users define a long flexfield (> 2000 characters), your users can experience truncation of key flexfield data when trying to create new combinations.

If your key flexfield is registered with Dynamic Inserts Feasible set to No, you do not need to add these fields, though they are recommended. If you do not create these fields, and your users define a long flexfield, your users may see empty flexfield segments upon entering the flexfield pop-up window after a query. These blank segments do not adversely affect the underlying data, nor do they adversely affect flexfield changes if your user updates those segments after querying.

If you use these fields and you have more than one key flexfield in the same row (in a block) of your form, you should also create one extra set of non-database segment fields per flexfield. So, if you have three foreign-key-reference flexfields in your block, you should have four sets of segment fields (for example, SEGMENT1 to SEGMENTn as the main set; and SEGMENT1_A to SEGMENTn_A, SEGMENT1_B to SEGMENTn_B, and SEGMENT1_C to SEGMENTn_C as the extra sets). In addition, you should use the USEDBFLDS="Y" argument for your flexfield definition routine calls. When you do so, you must write trigger logic to explicitly copy the appropriate values into or out of these fields before your flexfield routine calls. You must copy your values into the main set from the appropriate extra set before the WHEN-NEW-ITEM-INSTANCE and the PRE-INSERT and PRE-UPDATE flexfield event calls. You must copy your values out of the main set into the appropriate extra set after the POST-QUERY, WHEN-NEW-ITEM-INSTANCE, WHEN-VALIDATE-ITEM, PRE-INSERT, or PRE-UPDATE calls.

For a descriptive flexfield, it is possible (though not recommended) to create your form such that the table containing the descriptive flexfield columns is not the base table (or included in the base view) of the form. To do this, you create all the hidden fields (the ATTRIBUTEn fields and the structure defining field) as non-database fields on the null canvas. Then, code trigger and table handler logic that keeps the data in the two tables synchronized. For example, when your form updates your base table, your ON_UPDATE table handler should update the ATTRIBUTEn and structure defining columns in the descriptive flexfield table. Likewise, when your form inserts new records, you should have logic in your ON_INSERT table handler that inserts into the descriptive flexfield table. Descriptive flexfields never write directly to a table (base table or otherwise); they always write to the hidden segment fields.

Create Your Displayed Fields

Create your concatenated segments field as a 2000 character displayed, non-database text item for either key or descriptive flexfields. For a range flexfield, you create two non-database fields with the same name but with the suffixes _LOW and _HIGH.

Use the TEXT_ITEM property class for your key and range flexfields. For a descriptive flexfield, use the property class TEXT_ITEM_DESC_FLEX and name the field DESC_FLEX.

You must attach the dummy LOV from the TEMPLATE form, ENABLE_LIST_LAMP, to the displayed key or descriptive flexfield field. Make sure that "Validate from List" property (formerly "Use LOV for Validation") is set to No. This ensures that the List lamp works properly for your flexfield.

If you experience strange LOV behavior (where the LOV provides "null" as the only valid choice) or messages that the flexfield cannot be updated and/or has invalid values, check that "Validate from List" is set to No.

Create Your Flexfield Definition

Call a flexfield definition procedure from your WHEN-NEW-FORM-INSTANCE trigger to set up your flexfield. Using this procedure, you specify the block and fields for your flexfield and its related fields, the flexfield you want, and other arguments. See: Flexfield Definition Procedures.

You may need to enable, disable, or modify your flexfield definition depending on conditions in the form. For example, you may want to have a flexfield be updatable under some conditions but not under other conditions. In this case you should also call an UPDATE_DEFINITION procedure after calling the appropriate DEFINE procedure. See: Updating Flexfield Definitions.

Invoke Your Flexfield Definition from Several Event Triggers

Code handlers for special procedures into several form level triggers. These procedures fire your flexfield at certain events such as WHEN- NEW-ITEM-INSTANCE, WHEN-VALIDATE-ITEM, and PRE-INSERT.

You call your flexfields from form level triggers using the FND_FLEX.EVENT(EVENT) procedure. You can also call your flexfields using this procedure from within your own procedures. This procedure takes the event name as its argument. Call FND_FLEX.EVENT and pass the trigger name from the triggers listed in the following table:

Trigger Procedure
PRE-QUERY FND_FLEX.EVENT('PRE-QUERY');
POST-QUERY FND_FLEX.EVENT('POST-QUERY');
PRE-INSERT FND_FLEX.EVENT('PRE-INSERT');
PRE-UPDATE FND_FLEX.EVENT('PRE-UPDATE');
WHEN-VALIDATE- RECORD FND_FLEX.EVENT('WHEN-VALIDATE- RECORD');
WHEN-NEW-ITEM- INSTANCE FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE');
WHEN-VALIDATE- ITEM FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');

These calls should usually be coded into your form as form-level triggers. If you define any of these triggers at the block or field level, you need to make sure the block or field level triggers have execution style set to "Before" so the form-level flexfield calls still execute, or you should include these procedure calls in those triggers as well.

While we recommend you code all the flexfields triggers at the form level for convenience and consistency, having the triggers at form level may cause performance problems for very large or complicated forms. In that case, you may code the PRE-QUERY, POST-QUERY, PRE-INSERT, PRE-UPDATE, and WHEN-VALIDATE-RECORD triggers at the block level on all blocks that have flexfields (key or descriptive). You would then code the WHEN-NEW-ITEM- INSTANCE and WHEN-VALIDATE-ITEM at the item level for items on which the flexfields are defined.

You only need to code one set of these triggers regardless of how many flexfields you have in your form (assuming these triggers are at the form level).

Three form-level triggers in the TEMPLATE form, KEY-EDIT, KEY-LISTVAL, and POST-FORM, already have the appropriate FND_FLEX.EVENT calls performed through the APP_STANDARD.EVENT('trigger_name') routines as part of the APPCORE library. You must ensure that these APP_STANDARD.EVENT calls are not overridden by triggers at the block or item levels.

Important: If you have a block or item level POST-QUERY trigger that resets the query status of a record, you must set the Execution Style of that block or item level POST-QUERY trigger to After. Because the flexfield POST-QUERY logic updates field values for your flexfield, the record must be reset to query status after that logic has fired.

Opening a Flexfield Window Automatically

By default, descriptive flexfields open automatically without any special code so long as the profile option Flexfields:Open Descr Window is not set to No.

Normally, key flexfields do not open automatically. However, users can set the profile option, Flexfields:Open Key Window, to Yes to automatically open all key flexfields. You must not code any code in your form to open the window automatically, because the window would then be forced to open a second time.

You should remove any existing code that opens a key flexfield automatically. Such code would probably be in your WHEN-NEW-ITEM-INSTANCE trigger at the field level, instead of the form level, on the field that contains the flexfield. You should remove any "FND_FLEX.EVENT('KEY-EDIT');" call that opens the flexfield automatically.

Flexfield Definition Procedures

Flexfields packages and procedures are included in the FNDSQF library. Call item handlers from your WHEN-NEW-FORM-INSTANCE trigger to define key, range or descriptive flexfields.

When you call these procedures, you specify three types of arguments:

If you have more than one flexfield, you call a complete flexfield definition procedure for each of your flexfields from handlers in the same WHEN-NEW-FORM-INSTANCE trigger.

Key Flexfield Definition Syntax

Use FND_KEY_FLEX.DEFINE for a key flexfield on a foreign key or combinations form.

Important: We provide combinations form syntax so you can convert any existing non-Oracle E-Business Suite combinations forms you may have from SQL*Forms 2.3 to Oracle Forms 4.5 or later. However, the API for key flexfields may change in future versions of Oracle E-Business Suite, so we recommend that you do not create any new key flexfields that are not provided by Oracle E-Business Suite.

 FND_KEY_FLEX.DEFINE(
  /* Arguments that specify flexfield location */
     BLOCK=>'block_name',  FIELD=>'concatenated_segments_field_name',
 [DESCRIPTION=>'description_field_name',]
     [ID=>'Unique_ID_field',]    
     [DATA_FIELD=>'concatenated_hidden_IDs_field',] 
 /* Arguments that specify the flexfield */
   APPL_SHORT_NAME=>'application_short_name',
     CODE=>'key_flexfield_code',
     NUM=>'structure_number',
 /* Other optional parameters */
    [VALIDATE=>'{FOR_INSERT|FULL|PARTIAL|NONE|
              PARTIAL_IF_POSSIBLE}',]
    [VDATE=>'date',]
    [DISPLAYABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [INSERTABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [UPDATEABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [VRULE=>'flexfield qualifier\\n
           segment qualifier\\n
           {I[nclude]|E[xclude]}\\n
           APPL=application_short_name;
           NAME=Message Dictionary message name\\n
           validation value1\\n
           validation value2...
           [\\0flexfield qualifier\\n
           segment qualifier\\n
           {I[nclude]|E[xclude]}\\n
           APPL=application_short_name;
           NAME=Message Dictionary message name\\n
           validation value1\\n
           validation value2...]',]
    [COPY=>'block.field\\n{ALL | flexfield
           qualifier | segment_number}
           [\\0block.field\\n{ALL | flexfield
           qualifier | segment_number}]',]
    [DERIVED=>'block.field\\nSegment qualifier',]
    [DERIVE_ALWAYS=>'{Y|N}',]
    [DINSERT=>'{Y|N}',]
    [VALATT=>'block.field\\n
           flexfield qualifier\\n
           segment qualifier',]
    [TITLE =>'Title',]
    [REQUIRED=>'{Y|N}',]
    [AUTOPICK=>'{Y|N}',]
    [USEDBFLDS=>'{Y|N}',]
    [ALLOWNULLS=>'{Y|N}',]
    [DATA_SET=>'set number',]
    [COLUMN=>'{column1(n) | column1 alias(n) [, column2(n), ...] [INTO block.field]}',]
    [WHERE_CLAUSE=>'where clause',]
    [COMBQP_WHERE=>'{where clause|NONE}',]
    [WHERE_CLAUSE_MSG=>'APPL=application_short_
           name;NAME=message_name',]
    [QUERY_SECURITY=>'{Y|N|}',]
    [QBE_IN=>'{Y|N}',]
    [READ_ONLY=>'{Y|N}',]
    [LONGLIST=>'{Y|N}',]
    [NO_COMBMSG=>'APPL=application_short_
           name;NAME=message_name',]
    [AUTOCOMBPICK=>'{Y|N}',]
    [LOCK_FLAG=>'{Y|N}',]
    [HELP=>'APPL=application_short_name;
            TARGET=target_name']
   );

You should not use a colon ( : ) in block.field references for the VALATT, COPY, or DERIVED arguments. The arguments for these routines go to an Oracle Application Object Library cover routine and are not directly interpreted in PL/SQL.

Range (Type) Flexfield Definition Syntax

Use FND_RANGE_FLEX.DEFINE for a range flexfield. You use the same procedure for a "type" flexfield (which may also include range flexfield segments) that contains extra fields corresponding to each segment of the related key flexfield. For example, a type flexfield for the Accounting Flexfield might contain one field for each Accounting Flexfield segment, but you might enter only the values Yes or No in those fields, instead of normal segment values. The Assign Function Parameters form uses a type flexfield for its segment usage field (you enter "Yes" for any segment whose value you want to use). You may build a type flexfield that contains more than one "type column" (a "column" of fields in the flexfield pop-up window that correspond to the actual segment fields). If you do, you can specify your TYPE_ argument values multiple times, using \\0 to separate the values.

Important: You should not append "_LOW" or "_HIGH" to the FIELD, DESCRIPTION, DATA_FIELD or other values, since this procedure appends them automatically. When you use more than one type column, ensure that all TYPE_ arguments specify type columns in the same order to avoid having argument values applied to the wrong type column.

 FND_RANGE_FLEX.DEFINE(
  /* Arguments that specify flexfield location */
     BLOCK=>'block_name',  FIELD=>'concatenated_segments_field_name',
 [DESCRIPTION=>'description_field_name',]
     [DATA_FIELD=>'concatenated_hidden_IDs_field',] 
 /* Arguments that specify the flexfield */
   APPL_SHORT_NAME=>'application_short_name',
     CODE=>'key_flexfield_code',
     NUM=>'structure_number',
 /* Other optional parameters */
    [VALIDATE=>'{PARTIAL|NONE}',]
    [VDATE=>'date',]
    [DISPLAYABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [INSERTABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [UPDATEABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [VRULE=>'flexfield qualifier\\n
           segment qualifier\\n
           {I[nclude]|E[xclude]}\\n
           APPL=application_short_name;
           NAME=Message Dictionary message name\\n
           validation value1\\n
           validation value2...
           [\\0flexfield qualifier\\n
           segment qualifier\\n
           {I[nclude]|E[xclude]}\\n
           APPL=application_short_name;
           NAME=Message Dictionary message name\\n
           validation value1\\n
           validation value2...]',]
    [TITLE =>'Title',]
    [REQUIRED=>'{Y|N}',]
    [AUTOPICK=>'{Y|N}',]
    [USEDBFLDS=>'{Y|N}',]
    [ALLOWNULLS=>'{Y|N}',]
    [DATA_SET=>'set number',]
    [READ_ONLY=>'{Y|N}',]
 /* Parameters specific to type flexfields */
    [TYPE_FIELD=>'block.concatenated_type_values_
           field\\ntype field suffix',]
    [TYPE_VALIDATION=> 'Value set name\\n
           Required\\nDefault value',]
    [TYPE_SIZES=>'type_value_display_
           size\\nDescription_display_size',]
    [TYPE_HEADING=>'type column heading',]
    [TYPE_DATA_FIELD=>'block.type_data_field',]
    [TYPE_DESCRIPTION=>'block.type_
           description_field',]
    [SCOLUMN=>'single column title',]
    [HELP=>'APPL=application_short_name;
            TARGET=target_name']
);

Important: TYPE_FIELD, TYPE_DATA_FIELD and TYPE_DESCRIPTION require the block.fieldname construction, unlike other flexfield arguments that specify field names without block names.

Descriptive Flexfield Definition Syntax

Use FND_DESCR_FLEX.DEFINE for a descriptive flexfield.

 FND_DESCR_FLEX.DEFINE(
  /* Arguments that specify the flexfield location */
    BLOCK=>'block_name',  FIELD=>'field_name',
    [DESCRIPTION=>'description_field_name',]
    [DATA_FIELD=>'concatenated_hidden_IDs_field',] 
  /* Arguments that specify the flexfield */
 APPL_SHORT_NAME=>'application_short_name',
    DESC_FLEX_NAME=>'descriptive flexfield_name'
 /* Other optional parameters  */
    [VDATE=>'date',]
    [TITLE =>'Title',]
    [AUTOPICK=>'{Y|N}',]
    [USEDBFLDS=>'{Y|N}',]
    [READ_ONLY=>'{Y|N}',]
    [LOCK_FLAG=>'{Y|N}',]
    [HELP=>'APPL=application_short_name;
            TARGET=target_name',]
    [CONTEXT_LIKE=>'WHERE_clause_fragment'}
     );

Flexfield Definition Arguments

The following arguments apply to all types of flexfields unless noted otherwise. For those arguments that you would want to specify more than once, you separate the multiple argument values using \\0 (as noted).

Arguments that Specify the Flexfield Location

Variable Description
BLOCK Name of the block that contains your flexfield. Your value field, ID field (if any), and description field (if any) must all be in the same block.
FIELD Name of the field where you want to put your flexfield. This is a displayed, non-database form field that contains your concatenated segment values plus delimiters.
DESCRIPTION Description field for your flexfield. This is a displayed, non-database, non-enterable field that contains concatenated descriptions of your segment values. If you do not specify the DESCRIPTION parameter, your form does not display concatenated segment descriptions.
ID For a key flexfield only. Specify the field, if any, that contains the unique ID (CCID) for your key flexfield.
DATA_FIELD The concatenated hidden IDs field is a non-displayed form field that contains the concatenated segment hidden IDs.

Arguments that Specify which Flexfield to Use

Variable Description
APPL_SHORT_ NAME Shortname of the application with which your flexfield is registered.
CODE Key or range flexfields only. The short code that identifies your flexfield. This is the flexfield code specified in the Key Flexfields form. This code must match the registered code, such as GL# for the Accounting Flexfield in Oracle E-Business Suite.
NUM Key or range flexfields only. The structure number (or the :block.field reference containing the structure number) that identifies your key flexfield structure.
You can specify the non-displayed database :block.field that holds the identification number of your flexfield structure. You may also specify :$PROFILES$.your_profile_option_name to retrieve a value you set in a user profile option. You can "hardcode" a structure number, such as 101, into this parameter instead of providing a field reference, but such a number prevents you from using multiple structures for your flexfield. You must use this option if you are using multiple structures.
You can use the following SQL statement to retrieve the structure identification numbers for your flexfield:
SELECT ID_FLEX_NUM, ID_FLEX_STRUCTURE_NAME
			FROM FND_ID_FLEX_STRUCTURES
			WHERE ID_FLEX_CODE = 'flexfield code'; 

where flexfield code is the code you specify when you register your flexfield.
The default value for NUM is 101.
DESC_FLEX_ NAME Descriptive flexfields only. The registered name that identifies your descriptive flexfield.

Other Optional Arguments

If you do not specify a particular optional argument, the flexfield routine uses the usual default value for the argument.

Variable Description
VALIDATE Key or range flexfields only. For a key flexfield, you typically use FOR_INSERT for a combinations form and FULL for a foreign key form. For a range flexfield, you typically use NONE to allow users to enter any value into a segment or PARTIAL to ensure that users enter valid individual segment values that do not necessarily make up an actual valid combination.
Use a validation type of FULL for a foreign key form to validate all segment values and generate a new code combination and dynamically insert it into the combinations table when necessary. If you specify FULL, your flexfield checks the values your user enters against the existing code combinations in the code combinations table. If the combination exists, your flexfield retrieves the code combination ID. If the combination does not exist, your flexfield creates the code combination ID and inserts the combination into the combinations table. If you (or an installer) define the flexfield structure with Dynamic Inserts Allowed set to "No", then your flexfield issues an error message when a user enters a combination that does not already exist. In this case, your flexfield does not create the new code combination. FULL is the usual argument for a form with a foreign key reference.
Use PARTIAL for a form where you want to validate each individual segment value but not create a new valid combination or check the combinations table for an existing combination. You would use PARTIAL when you want to have application logic that requires flexfield segment values but does not require an actual code combination. For example, the Oracle E-Business Suite Shorthand Aliases form requires that a user enters valid values for each segment, but does not require (or check) that the actual code combination already exists in the combinations table. The Shorthand Aliases form does not create the combination, either. PARTIAL_IF_POSSIBLE is a special case of PARTIAL. If you have dependent segments in your flexfield (with independent segments), PARTIAL does not provide a list of values on the dependent segment if the user has not entered a value for the associated independent segment. PARTIAL_IF_POSSIBLE, however, will attempt to provide a list of values on the dependent segment. That list of values contains all dependent values for all values of the associated independent segment (so, you would see multiple values 000 if that were your default dependent value).
Use NONE if you wish no validation at all.
The default value for a key flexfield is FULL. The default value for a range flexfield is NONE.
VDATE date is the validation date against which the Start Date and End Date of individual segment values is checked. You enter a Start Date and End Date for each segment value you define using the Segment Values form.
For example, if you want to check values against a date that has already passed (say, the closing date of an accounting period), you might specify that date as VDATE using a field reference (VDATE=>':block.field') and compare your segment values against that date.
The default value is the current date (SYSDATE).
DINSERT Key flexfields only. Use DINSERT to turn dynamic inserts off or on for this form.
The default value is Y (the form can do dynamic inserts).
DISPLAYABLE Key or range flexfields only. The DISPLAYABLE parameter allows you to display segments that represent specified flexfield qualifiers or specified segment numbers, where segment numbers are the order in which the segments appear in the flexfield window, not the segment number specified in the Key Flexfield Segments form. For example, if you specify that you want to display only segment number 1, your flexfield displays only the first segment that would normally appear in the pop-up window (for the structure you specify in NUM).
The default value for DISPLAYABLE is ALL, which makes your flexfield display all segments. Alternatively, you can specify a flexfield qualifier name or a segment number.
You can use DISPLAYABLE as a toggle switch by specifying more than one value, separated by \\0 delimiters. For example, if you want your flexfield to display all but the first segment, you would specify:
DISPLAYABLE=>'ALL\\01' 

Note that \\0 separates 1 from ALL.
If you do not display all your segments, but you use default values to fill in your non-displayed segments, you must also have hidden SEGMENT1 through SEGMENTn fields in your form. You need these hidden fields because your flexfield writes the values for all displayed fields to the concatenated values field, but does not write the values for the non-displayed defaulted fields. Since your flexfield normally uses the values in the concatenated values field to update and insert to the database, the default values for the non-displayed fields are not committed. However, if you have the extra hidden fields (similar to a combinations form), your flexfield writes flexfield values to those fields as well as to the concatenated segment values field. The non-displayed values are written only to the hidden fields, but are used to update and insert to the database.
UPDATEABLE/INSERTABLE Key or range flexfields only. The UPDATEABLE / INSERTABLE parameters determine whether your users can update or insert segments that represent specified unique flexfield qualifiers or segment numbers, where segment numbers are the order in which the segments appear in the flexfield window, not the segment number specified in the Key Flexfield Segments form.
The default value for each is ALL, which allows your user to update/insert all segments. Alternatively, you can specify a flexfield qualifier name or a segment number. You can enter UPDATEABLE=>'' or INSERTABLE=>'' (two single quotes) to prevent your user from updating or inserting values for any segments.
You can use these parameters as toggle switches by specifying more than one value, separated by \\0 delimiters. For example, if you want your user to be able to update all but the first segment, you would specify:
UPDATEABLE=>'ALL\\01' 

Note that \\0 separates 1 from ALL.
If you use INSERTABLE=>'' to prevent your user from inserting values for any segments, Shorthand Flexfield Entry is disabled for that form.
TITLE Specify the window title you want to appear at the top of the pop-up window. The default value for a key flexfield is the Structure Name you specify when you set up this flexfield using the Key Flexfield Segments form. For a descriptive flexfield, the default value is the flexfield title you specify when you set up this flexfield using the Descriptive Flexfield Segments form.
REQUIRED Key or range flexfields only. Specify whether your user can exit the flexfield window without entering segment values.
The default value is Y.
If you specify Y, then your flexfield prevents your user from leaving any required segment (a segment whose value set has Value Required set to Yes) without entering a valid value for that segment. Also, if your user tries to save a row without ever entering the flexfield pop-up window, your flexfield attempts to use default values to fill in any required segments and issues an error message if not all required segments can be filled.
If you specify Y and VALIDATE as FULL, then when your user queries up a row with no associated flexfield (the foreign key flexfield ID column contains NULL), your flexfield issues an error message to warn the user that a NULL ID has been returned for a required flexfield.
If you specify N, your flexfield allows your user to save a row without ever entering the flexfield pop-up window. If you specify N, your user can navigate (without stopping) through a flexfield window without entering or changing any values. However, if a user enters or changes any segment value in the flexfield, the user cannot leave the flexfield window until all required segments contain valid values. If you specify N and a user does not open or enter values in the window, the user can save the row regardless of whether the flexfield has required segments. In this case, your flexfield does not save default values as segment values for the required segments, and it does not issue an error message.
If you specify N and VALIDATE as FULL, then when your user queries up a row with no associated flexfield (the foreign key flexfield ID column contains NULL), your flexfield validates the individual segment values returned by the query. Specify N if you want to query up non-required flexfields without getting an error message.
Note that even if REQUIRED is set to N, a user who starts entering segment values for this flexfield must either fill out the flexfield in full, or abandon the flexfield.
AUTOPICK Determines whether a list of values window appears when your user enters an invalid segment value. The default value is Y.
COPY
Key flexfields only. Copies a non-null value from block.field into the segment representing the specified flexfield qualifier or segment number before the flexfield window pops up. Alternatively, if you specify ALL, COPY copies a set of non-null, concatenated set of segment values (and their segment separators) that you have in block.field into all of your segments. For example, if you have a three-segment flexfield, and your block.field contains 001.ABC.05, COPY puts 001 into the first segment, ABC into the second segment, and 05 into the third segment.
The value you COPY into a segment must be a valid value for that segment. The value you COPY overrides any default value you set for your segment(s) using the Key Flexfield Segments form. However, shorthand flexfield entry values override COPY values. COPY does not copy a NULL value over an existing (default) value. However, if the value you copy is not a valid value for that segment, it gives the appearance of overriding a default value with a NULL value: the invalid value overrides the default value, but your flexfield then erases the copied value because it is invalid. You should ensure that the field you copy from contains valid values.
When the flexfield window closes, your flexfield automatically copies the value in the segment representing the specified flexfield qualifier or segment number into block.field. Alternatively, if you specify ALL, your flexfield automatically copies the concatenated values of all your segments into block.field.
You can specify one or more COPY parameter values, separated by \\0 delimiters. Later COPY values override earlier COPY values. For example, assume you have a field that holds concatenated flexfield values, called Concatenated_field, and it holds the string 01-ABC-680. You also have a field, Value_field, that holds a single value that you want to copy into your second segment, and it holds the value XYZ. You specify:
COPY=>'block.Concatenated_field\\nALL\\0
      block.Value_field\\n2'

Note that \\0 separates the different parameter values.
When your user opens the flexfield window, Oracle Application Object Library executes the two COPY parameters in order, and your user sees the values in the window as:
01
XYZ
680

After the flexfield window closes, your flexfield copies the values back into the two fields as 01-XYZ-680 and XYZ respectively. Note that XYZ overrides ABC in this case.
DERIVED Key flexfields only. Use DERIVED to get the derived value of segment qualifiers for a combination that a user types in. Use block.field to specify the block and field you want your flexfield to load the derived value into. Use Segment qualifier to specify the segment qualifier name you want. Note: do not put spaces around \\n, and \\n must be lowercase.
Your flexfield uses the following rules to get the derived qualifier value from the individual segment qualifier values: if the segment qualifier is unique, the derived value is the segment qualifier value; for non-unique segment qualifiers, if any segment's qualifier value = N, then the derived value is N, otherwise, the derived value is Y. The only exception to this rule is for the internal SUMMARY_FLAG segment qualifier; the rule for this is if any segment value is a parent, then the derived value of SUMMARY_FLAG is Y. Your flexfield loads derived values into the combinations table qualifier column that you specify when you define your qualifier.
You can specify one or more groups of DERIVED parameters separated by \\0.
DERIVE_ ALWAYS Key flexfields only. Use with the DERIVED parameter. If you specify Y, the derived values are computed even if the user navigates through the flexfield without changing any values (choosing the same value that is already in a segment does mark the flexfield as having changed).
The default value is N, where the derived values are calculated only if the flexfield is modified.
VRULE Key or range flexfields only. Use VRULE to put extra restrictions on what values a user can enter in a flexfield segment based on the values of segment qualifiers (which are attached to individual segment values). You can specify the name of a flexfield qualifier and a segment qualifier, whether to Include or Exclude the validation values, and the Message Dictionary application short name and message name for the message your flexfield displays if the user enters an improper value. The delimiter \\n must be lowercase, and you separate the application name from the message name using a semicolon.
For example, suppose you build a form where you want to prevent your users from entering segment values for which detail posting is not allowed into all segments of Oracle General Ledger's Accounting Flexfield. DETAIL_POSTING_ALLOWED is the segment qualifier, based on the global flexfield qualifier GL_GLOBAL, that you want to use in your rule. You want to exclude all values where the value of DETAIL_POSTING_ALLOWED is N (No). Your message name is "GL Detail Posting Not Allowed", and it corresponds to a message that says "you cannot use values for which detail posting is not allowed." You would specify your rule as:
VRULE='GL_GLOBAL\\nDETAIL_POSTING_ALLOWED\\nE
				\\nAPPL=SQLGL;
				NAME=GL Detail Posting Not Allowed\\nN' 

Do not use line breaks (newline characters) in your VRULE argument. The previous example includes them for clarity, but in your code it should all be one line. If it cannot fit on one line, use the following format:
vrule => 'first line' || 
         'second line'; 

When your user enters an excluded value in one of the segments affected by this qualifier, your user gets the message you specify. In addition, the excluded values do not appear in the list of values on your segments. All other values, not being specifically excluded, are included.
You can specify one or more groups of VRULE parameters separated by \\0 (zero). Oracle Application Object Library checks multiple VRULE parameters bottom-up relative to the order you list them. You should order your rules carefully so that your user sees the most useful error message first.
VALATT Key flexfields only. VALATT copies the segment qualifier value of the segment representing the unique flexfield qualifier into block.field when the flexfield window closes. The delimiter \\n must be lowercase.
USEDBFLDS For a combinations form, specify this parameter only if your combinations table contains both a full set of key flexfield columns (the primary flexfield) and a column that is a foreign key reference to another key flexfield (with a different combinations table). You set this parameter to N to keep the foreign key flexfield from using the database segment fields belonging to the primary flexfield (that your combinations form maintains).
For a foreign key form, specify this parameter if your form is based on a table that has foreign key references to two or more flexfields, and if you have non-database SEGMENT1 through N fields on your form (where N is the number of segments in your combinations table). If such fields exist, your flexfield by default will load values into them that correspond to the combination of segment values in the current flexfield. If you set this parameter to N, your flexfield will not load the segment fields for the current flexfield. If you have more than one flexfield on your form, use this parameter to specify which one should use the segment fields (specify Y for one flexfield's routine calls, and specify N for other flexfields' routine calls).
For a descriptive flexfield, specify N for this parameter to prevent the descriptive flexfield from using hidden segment fields (such as ATTRIBUTEn).
The default value is Y.
COLUMN Key flexfields only. Use COLUMN to display other columns from the combinations table in addition to the current segment columns, where n is the display width of the column. You can place the values of the other columns into fields on the current form. The value is automatically copied into the field when the user selects an existing flexfield combination.
For example, to display a description column called SEG_DESC and an error message from E_FLAG with the column headings DESCRIPTION and ERROR FLAG, you could set
COLUMN=>'SEG_DESC DESCRIPTION(15), 
					E_FLAG \"ERROR_FLAG\"(*)'

The (*) sets a dynamic column width, with the size determined by the value selected.
If you wanted to place the description into the field block_1.field_1 and the error message into block_1.field_2, you would set
COLUMN=>'SEG_DESC DESCRIPTION(15) 
					INTO BLOCK_1.FIELD_1, 
					E_FLAG \"ERROR_FLAG\" (*) 
					into BLOCK1_FIELD_2'

You may only use 32 distinct INTO columns in your COLUMN= clause. Your maximum width for additional columns is 240 characters.
WHERE_ CLAUSE Key flexfields only. Specify a WHERE clause to restrict which code combinations to display in the list of values window. This argument also prevents a user from entering a combination that does not fit the WHERE clause. This argument should not normally be used for a flexfield on the combinations form, since you would usually want to display all combinations on the combinations form.
Do not specify the word "WHERE" in this WHERE clause argument. You should use this token with flexfields that do not allow dynamic inserts, either using DINSERTS as N or preventing dynamic inserts at the structure level.
You should not use the WHERE_CLAUSE argument for a flexfield that allows dynamic inserts.
Use the WHERE_CLAUSE_MSG argument to specify an appropriate message to display to the user when a combination violates your WHERE clause.
COMBQP_ WHERE Key flexfields only. The primary use of this argument is to disable the combination list of values for your flexfield on this form. Specify NONE to disable the combination list of values.
Alternatively, you could use this argument to specify any additional WHERE clause to further restrict which code combinations to display in the list of values window. This WHERE clause is appended to your WHERE_CLAUSE argument using an AND expression. It affects only the combination list of values however, and does not affect a combination that a user enters manually.
Do not specify the word "WHERE" in this WHERE clause argument.
WHERE_ CLAUSE_MSG Key flexfields only. Use with the WHERE_CLAUSE argument. If you wish to display your own message when a user enters an invalid combination restricted by your WHERE clause, specify the applications short name and message name here. Otherwise flexfields uses the standard Oracle E-Business Suite message that displays the entire WHERE clause to the user (not recommended).
DATA_SET Key or range flexfields only. Specify the :block.field that holds the set identifier for your flexfield. DATA_SET specifies which set of code combinations to use for this flexfield. For each flexfield structure, you can divide code combinations in your combinations table into sets (for example, parts with high prices, medium prices, and low prices).
You can only use DATA_SET if you implement a structure defining column (that is, you must specify NUM). The default for DATA_SET is your structure number (as specified in NUM). If you use DATA_SET, your application must maintain a separate table that contains the correspondences between sets and key flexfield structures. For example, your correspondences table could contain values such as those in the table at the end of this section.
If you use DATA_SET, your flexfield stores the set number in the structure defining column instead of the structure number. Note that you cannot have duplicate set numbers in your correspondences table, though you can have more than one set number for a given structure number. You must derive DATA_SET and NUM from different :block.fields (or profile options, or "hardcoded" numbers) since they are distinctly different numbers.
ALLOWNULLS Determines whether NULLs should be allowed into any segment. ALLOWNULLS only overrides the segment definition (Value Required is Yes) for each segment if you specify PARTIAL or NONE for the VALIDATE parameter.
QUERY_ SECURITY Key flexfields only. Determines whether flexfield value security applies to queries as well as inserts and updates. If you specify Y, your users cannot query up existing code combinations that contain restricted values. If you specify N, your users can query and look at code combinations containing restricted values, but they cannot update the restricted values. The default value is N. This option has no effect unless your users have enabled and defined flexfield value security for your flexfield's value sets.
QBE_IN Key flexfields only. Controls the type of subquery your flexfield uses to select the desired rows in flexfield query-by-example.
The default value is N.
If you specify N, your flexfield generates a correlated subquery. This query is effectively processed once for each row returned by the main query (generated by the rest of the form), and it uses the code combination ID as a unique index. Choose N if you expect your main query to return a small number of rows and you expect your flexfield query-by-example to return many rows.
If you specify Y, your flexfield generates a non-correlated subquery using the "IN" SQL clause. Your query is processed only once, but returns all the rows in your combinations table that match your flexfield query-by-example criteria. Choose Y when you expect your main query to return many rows and you expect your flexfield query-by-example to return a small number of rows (less than about 100). Such a condition usually corresponds to a small number of rows in the combinations table and many rows in the application table. For example, assume you have a Part Flexfield, where your company handles only a limited number of parts (say, 75), but you have thousands of orders for your parts (and a correspondingly large Orders table). For this case, choosing Y would greatly improve your application performance on flexfield queries-by-example.
LONGLIST Key flexfields only. Specify Y or N to allow or disallow using LongList with this flexfield. LongList allows users to specify a partial value when querying a flexfield combination using Combination LOV.
NO_COMBMSG Key or range flexfields only. If you wish to display your own message when a user enters an invalid combination, specify the message name here. Otherwise flexfields uses the standard Oracle E-Business Suite message.
If you use the WHERE_CLAUSE argument, use the WHERE_CLAUSE_MSG argument instead of NO_COMBMSG to specify an appropriate message to display to the user when a combination violates your WHERE clause.
READ_ONLY Specify Y to prevent any updating of your flexfield segment values, whether by shorthand alias, copy, or any other method.
AUTO- COMBPICK Key flexfields only. Determines the behavior of the combination list of values for direct entry flexfields with no dynamic inserts allowed when the user enters a non-existing code combination. If you specify Y, the combination list of values appears if the user enters an incorrect value in a single segment flexfield, or if there are non-constant values (%) or null segments in a multi-segment flexfield. If you specify N, the combination list of values does not appear, and the error message "This combination does not exist..." is generated. The default value is Y.
LOCK_FLAG Normally, when a user types a character into a flexfield segment, that action locks the base table of the form. However, in some cases you might want to avoid locking the table; for example, you might have several inquiry forms that use the same base table, and you do not want other users to have to wait until the table is unlocked. The default value is Y. Specify N to turn off the locking behavior, or specify D to lock the table only if the flexfield-related field is a database field.
HELP Use the HELP argument to specify a target name for online help specific to this instance of this flexfield. You specify the application short name for the application that owns the help file (not necessarily the same application that owns the flexfield or the form). You also specify the target name in your help file where the help resides. If the corresponding help target is not found, the user may receive an error message. If the HELP argument is not specified, the online help displays generic flexfields help. For example, to show specific help for the Accounting Flexfield from the Oracle General Ledger help file, you would specify the following:
HELP=>'APPL=SQLGL;TARGET=FLEX.GL#' 
CONTEXT_LIKE Descriptive flexfields only. Specify a fragment of a WHERE clause to restrict which context codes to display in the list of values window of the context field. This argument also prevents a user from entering a context that does not fit the WHERE clause. The resulting WHERE clause for the LOV of the context field is like the following:
WHERE ...  
AND DESCRIPTIVE_FLEX_CONTEXT_CODE LIKE 
<CONTEXT_LIKE>...      

The default value is '%'. If this argument is used to restrict context values then the Override Allowed (Display Context) should be turned on (checked) in the descriptive flexfield definition.
Flexfields do not use this constraint in the POST-QUERY event. Therefore, a user can query up existing data that would now be invalid under the CONTEXT_LIKE part of the WHERE clause. However, as in all flexfields where the user queries up now-invalid flexfield data, if the user presses OK (with or without changing flexfield values), the flexfield is marked as changed and the invalid value must be corrected. If the user presses the Cancel button, the data is unaffected and does not need to be corrected (even if the user changes other non-flexfield parts of the record).
Note that, as always, any reference field for the descriptive flexfield is only evaluated the first time the descriptive flexfield is opened (or validated upon commit if the user does not open the flexfield before committing) for a new record. If the user opens the flexfield, the context field is populated with the value of the reference field. If the user presses OK to exit the flexfield window, then returns to the reference field and changes its value, the context field value does not change to reflect the new value in the reference field. Further, the existing context field value is not re-evaluated according to the value of the CONTEXT_LIKE argument at that time. To avoid creating apparently-inconsistent behavior, you should avoid modifying the CONTEXT_LIKE argument at any time after initially setting it in the flexfield definition at form startup (for example, do not base its value on the value of a field the user can modify).
For example, this argument can be used to restrict country-specific contexts in a given country.
SELECT_COMB_ FROM_VIEW Key flexfields only. Flexfields use code combination table names to create select statements for validation and lists of values. If your key flexfield code combination table is the base table (_B table) of a translated entity and if you want to get additional columns from the translated table (_TL table) by using the COLUMN token, then use the SELECT_COMB_FROM_VIEW token to specify the translated view name (the _VL view).
If the value specified in SELECT_COMB_FROM_VIEW is different from the key flexfield's code combination table name then dynamic inserts will be turned off automatically.
Table of Examples of Correspondences
Structure Set Set Description
101 1 Low-priced truck parts
101 2 Medium-priced truck parts
101 3 High-priced truck parts
102 4 Low-priced car parts
102 5 High-priced car parts
103 6 Low-priced motorcycle parts
103 7 High-priced motorcycle parts

Additional Optional Arguments for Type Flexfields

If you are building a type flexfield, you use these arguments in addition to other optional and required arguments. If you do not specify a particular optional argument, the flexfield routine uses the usual default value for the argument. You may build a type flexfield that contains more than one "type column" (a "column" of fields in the flexfield pop-up window that correspond to the actual segment fields). If you do, you can specify your TYPE_ argument values multiple times, using \\0 to separate the values. SCOLUMN can have only one value, however.

Variable Description
TYPE_FIELD Range (type) flexfields only. Name of the field where you want to put your "type" flexfield. This is a displayed, non-database form field that contains your concatenated segment type values plus delimiters.
You can include a suffix for all the fields related to your type field. If you include a suffix, such as TYPE1, your flexfield appends that suffix to all field names automatically. If you specify a suffix, you should not include the suffix in any of the type-related field names for your FND_RANGE_FLEX.DEFINE call. Note that if you specify a suffix, your flexfield expects to store each type value in a form field (one type field for each segment), so you should specify a suffix if you use those fields, but you should not specify a suffix if you use only the concatenated fields.
If you specify TYPE_FIELD, you must also specify TYPE_HEADING, TYPE_VALIDATION, and TYPE_SIZES. TYPE_DESCRIPTION and other type arguments are optional.
You can specify more than one type field and suffix. Each field and suffix must be unique so that the different types do not share the same underlying fields and columns. Separate your first field and suffix from your second field and suffix (and so on) using \\0.
TYPE_ DESCRIPTION Range (type) flexfields only. Description field for your type flexfield. This is a displayed, non-database, non-enterable field that contains concatenated descriptions of your type segment values. If you do not specify this parameter, your form does not display concatenated type segment descriptions. If you specified a suffix for TYPE_FIELD, do not include it for TYPE_DESCRIPTION.
TYPE_DATA_ FIELD Range (type) flexfields only. Name of the non-displayed form field that contains the concatenated type segment hidden IDs. If you specified a suffix for TYPE_FIELD, do not include it for this argument.
TYPE_ VALIDATION Range (type) flexfields only. Specify the name of a value set, such as Yes_No, that you want to use for your type column (for all fields in the type column). You also specify Y if the user is required to enter a value for each field in the type column; specify N if values are not required. Finally, specify a single default value for all fields in your type column. This default value appears in each of the type fields when the pop-up window opens. You may use either a hardcoded constant value or a field reference (:block.field) for your default value.
If you have more than one type column, specify subsequent groups of values separated by \\0 delimiters.
TYPE_SIZES Range (type) flexfields only. Specify the maximum display size for the value set your type field uses, as well as the maximum display size for the value description. The value display size must be at least 1 and not larger than the maximum size of the corresponding value set (whose maximum size must not be greater than the size of the underlying database column). The description display size may be 0 or larger.
If you have more than one type column, you specify sizes for each pair of values and descriptions, separated by the \\0 delimiter.
TYPE_HEADING Range (type) flexfields only. Specify a title that you want to appear above the type segments in the flexfield pop-up window.
If you have more than one type column, specify additional headings separated by the \\0 delimiter.
SCOLUMN Range (type) flexfields only. The presence of the SCOLUMN argument indicates that this is a "single column type flexfield" (a flexfield that uses only SEGMENTn_LOW and one or more type columns, but does not use SEGMENTn_HIGH). Specify a title for the SEGMENTn_LOW fields that you want to display in the flexfield pop-up window. The flexfield still assumes that the _LOW suffix applies to each SEGMENTn field and related concatenated fields, regardless of the title you specify.

Flexfield Definition Examples

Simple Key Flexfield Example

Here is an example of a simple key flexfield definition. This definition provides the default structure (101) of the Accounting Flexfield.

 FND_KEY_FLEX.DEFINE(
      BLOCK=>'ORDERS', 
      FIELD=>'KFF_CONCATENATED_VALUES',

      APPL_SHORT_NAME=>'SQLGL',
      CODE=>'GL#',
      NUM=>'101');

Key Flexfield Example with Additional Arguments

Here is an example of a more complex key flexfield definition. This definition provides the default structure (101) of the Accounting Flexfield.

 FND_KEY_FLEX.DEFINE(
      BLOCK=>'ORDERS', 
      FIELD=>'KFF_CONCATENATED_VALUES',
 
      APPL_SHORT_NAME=>'SQLGL',
      CODE=>'GL#',
      NUM=>'101',

      DISPLAYABLE=>'ALL'
      INSERTABLE=>'ALL'
      UPDATEABLE=>'');

Key Flexfield Example with Variable Arguments

Here is an example from the Shorthand Aliases form, which overrides several of the arguments and uses :block.field references to pass field values to the procedure. Note that this example also provides three fields related to the flexfield (FIELD, DESCRIPTION, and DATA_FIELD):

FND_KEY_FLEX.DEFINE(  
     BLOCK=>'ALIASES',
     FIELD=>'SEGMENTS',
     DESCRIPTION=>'SEGMENT_DESCRIPTIONS',
     DATA_FIELD=>'CONCATENATED_SEGMENTS',

     APPL_SHORT_NAME=>':FLEX.APPLICATION_SHORT_NAME',
     CODE=>':FLEX.ID_FLEX_CODE',
     NUM=>':FLEX.ID_FLEX_NUM',

     REQUIRED=>'Y',
     USEDBFLDS=>'N', 
     VALIDATE=>'PARTIAL',
     ALLOWNULLS=>'Y');

In this example you override the default values for the arguments REQUIRED, USEDBFLDS, VALIDATE and ALLOWNULLS.

Descriptive Flexfield Example

Here is an example of a simple descriptive flexfield definition. This definition provides the descriptive flexfield on the Shorthand Aliases form in the Oracle E-Business Suite.

 FND_DESCR_FLEX.DEFINE(
      BLOCK=>'ALIASES', 
      FIELD=>'DF',

      APPL_SHORT_NAME=>'FND',
      DESC_FLEX_NAME=>'FND_SHORTHAND_FLEX_ALIASES');

Range Flexfield Example

Here is an example of a simple range flexfield definition.

 FND_RANGE_FLEX.DEFINE(
      BLOCK=>'RANGES', 
      FIELD=>'SEGMENTS',
      DESCRIPTION=>'DESCRIPTIONS'

      APPL_SHORT_NAME=>'SQLGL',
      CODE=>'GL#',
      NUM=>'101',

      VALIDATE=>'PARTIAL');

Note that the actual form fields corresponding to FIELD and DESCRIPTION are SEGMENTS_LOW, SEGMENTS_HIGH, DESCRIPTIONS_LOW and DESCRIPTIONS_HIGH.

Range with Types Flexfield Example

The following example uses the Accounting Flexfield with two type fields.

FND_RANGE_FLEX.DEFINE(
  BLOCK=>'RANGES',
  FIELD=>'SEGMENTS',
  DESCRIPTION=>'DESCRIPTIONS',

  APPL_SHORT_NAME=>'SQLGL',
  CODE=>'GL#',
  NUM=>'101',

  VALIDATE=>'PARTIAL',
  TYPE_FIELD=>'RANGES.SEGMENTS\\n_TYPE1\\0
       RANGES.SEGMENTS\\n_TYPE2',
  TYPE_DATA_FIELD=>'RANGES.TYPE_DATA\\0
       RANGES.TYPE_DATA',
  TYPE_DESCRIPTION=>'RANGES.TYPE_DESC\\0
       RANGES.TYPE_DESC',
  TYPE_HEADING=>'Type 1\\0Type 2',
  TYPE_VALIDATION=>'Yes_No\\nN\\nYes\\0
       Yes_No\\nN\\nNo',
  TYPE_SIZES=>'4\\n4\\04\\n4');

Single Range Column with Types Flexfield Example

The SCOLUMN argument is used to define a "Single Column Flexfield". If SCOLUMN has a value, instead of having the "Low", "High" and "Type" columns this flexfield will have only the "Low" and "Type" columns. Since the title "Low" is not appropriate here (since we don't have a "High" column), the value passed in through the SCOLUMN argument is used as the column title. The range flexfield still writes to the underlying segments appended with the suffix "_LOW", and assumes that the "_LOW" suffix is appended to the concatenated segments, description and data_field fields.

The same flexfield as above but when only one column is used.

FND_RANGE_FLEX.DEFINE(
  BLOCK=>'RANGES',
  FIELD=>'SEGMENTS',
  DESCRIPTION=>'DESCRIPTIONS',

  APPL_SHORT_NAME=>'SQLGL',
  CODE=>'GL#',
  NUM=>'101',

  VALIDATE=>'PARTIAL',
  SCOLUMN=>'Accounting Flexfield',
  TYPE_FIELD=>'RANGES.SEGMENTS\\n_TYPE1\\0
       RANGES.SEGMENTS\\n_TYPE2',
  TYPE_DATA_FIELD=>'RANGES.TYPE_DATA\\0
       RANGES.TYPE_DATA',
  TYPE_DESCRIPTION=>'RANGES.TYPE_DESC\\0
       RANGES.TYPE_DESC',
  TYPE_HEADING=>'Type 1\\0Type 2',
  TYPE_VALIDATION=>'Yes_No\\nN\\nYes\\0
       Yes_No\\nN\\nNo',
  TYPE_SIZES=>'4\\n4\\04\\n4');

Updating Flexfield Definitions

Normally you define a flexfield only once in your form, usually at the form startup event. However, sometimes you need to change this definition later. For example, you may want to make the flexfield non-updatable and non-insertable. Instead of redefining the entire flexfield with UPDATEABLE=>'' and INSERTABLE=>'' and all the other arguments the same as before, you can use the following update procedures to change only the arguments that need to be modified.

You can use the update procedures to control any of the "other optional arguments" that you specify in your flexfield definition procedures. You cannot use these procedures to change arguments such as which fields your flexfield uses, since those arguments essentially identify the flexfield rather than modify it. For example, you may specify new values for the VALIDATE argument, but you may not specify new values for the DESCRIPTION or DATA_FIELD arguments.

Enabling or Disabling a Flexfield

Once a flexfield has been defined in your form, whenever the FND_FLEX.EVENT calls occur at various block or form level triggers, these events apply to all flexfields defined in the block or form. This makes it difficult to handle situations where you want to make FND_FLEX.EVENT calls for some flexfields but not others. For example, you may not want to call VALID for a particular key flexfield in PRE-UPDATE, but want to call it for all other flexfields in the block. Using the update procedures you can enable and disable a flexfield definition so that the FND_FLEX.EVENT calls do not apply to disabled flexfield definitions.

The update procedures provide a special argument, ENABLED, in addition to the optional arguments you can specify. You specify N for this argument to disable the flexfield, and you specify Y to enable the flexfield. You cannot use ENABLED in your normal flexfield definition procedure calls (which automatically enable the flexfield).

Update Key Flexfield Definition Syntax

Use FND_KEY_FLEX.UPDATE_DEFINITION to update the definition for a key flexfield on a foreign key or combinations form. Other than the ENABLED argument, which you can only use for update procedures, the arguments are the same as you use for the flexfield definition procedures..

 FND_KEY_FLEX.UPDATE_DEFINITION(
  /* Arguments that specify flexfield location and
           thus identify the flexfield */
     BLOCK=>'block_name',  FIELD=>'concatenated_segments_field_name',

 /* Argument to enable or disable flexfield */  [ENABLED=>'{Y|N}',] 
 /* Other optional parameters */
    [VALIDATE=>'{FOR_INSERT|FULL|PARTIAL|NONE|
              PARTIAL_IF_POSSIBLE}',]
    [VDATE=>'date',]
    [DISPLAYABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [INSERTABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [UPDATEABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [VRULE=>'flexfield qualifier\\n
           segment qualifier\\n
           {I[nclude]|E[xclude]}\\n
           APPL=application_short_name;
           NAME=Message Dictionary message name\\n
           validation value1\\n
           validation value2...
           [\\0flexfield qualifier\\n
           segment qualifier\\n
           {I[nclude]|E[xclude]}\\n
           APPL=application_short_name;
           NAME=Message Dictionary message name\\n
           validation value1\\n
           validation value2...]',]
    [COPY=>'block.field\\n{ALL | flexfield
           qualifier | segment_number}
           [\\0block.field\\n{ALL | flexfield
           qualifier | segment_number}]',]
    [DERIVED=>'block.field\\nSegment qualifier',]
    [DINSERT=>'{Y|N}',]
    [VALATT=>'block.field\\n
           flexfield qualifier\\n
           segment qualifier',]
    [TITLE =>'Title',]
    [REQUIRED=>'{Y|N}',]
    [AUTOPICK=>'{Y|N}',]
    [USEDBFLDS=>'{Y|N}',]
    [ALLOWNULLS=>'{Y|N}',]
    [DATA_SET=>'set number',]
    [COLUMN=>'{column1(n) | column1 alias(n) [, column2(n), ...]}',]
    [WHERE_CLAUSE=>'where clause',]
    [COMBQP_WHERE=>'{Y|N}',]
    [WHERE_CLAUSE_MSG=>'APPL=application_short_
           name;NAME=message_name',]
    [QUERY_SECURITY=>'{Y|N}',]
    [QBE_IN=>'{Y|N}',]
    [READ_ONLY=>'{Y|N}',]
    [LONGLIST=>'{Y|N}',]
    [NO_COMBMSG=>'{Y|N}',]
    [LOCK_FLAG=>'{Y|N}',]
    [AUTOCOMBPICK=>'{Y|N}',]
    [DERIVE_ALWAYS=>'{Y|N}',]
    [HELP=>'APPL=application_short_name;
            TARGET=target_name']
   );

Update Range (Type) Flexfield Definition Syntax

Use FND_RANGE_FLEX.UPDATE_DEFINITION for a range flexfield. You use the same procedure for a "type" flexfield (which may also include range flexfield segments) that contains extra fields corresponding to each segment of the related key flexfield.

Other than the ENABLED argument, which you can only use for update procedures, the arguments are the same as you use for the flexfield definition procedures. See: Flexfield Definition Arguments.

Important: You should not append "_LOW" or "_HIGH" to the FIELD, DESCRIPTION, DATA_FIELD or other values, since this procedure appends them automatically. When you use more than one type column, ensure that all TYPE_ arguments specify type columns in the same order to avoid having argument values applied to the wrong type column.

 FND_RANGE_FLEX.UPDATE_DEFINITION(

  /* Arguments that specify flexfield location */
     BLOCK=>'block_name',  FIELD=>'concatenated_segments_field_name', 
 /* Argument to enable or disable flexfield */  [ENABLED=>'{Y|N}',] 
 /* Other optional parameters */
    [VALIDATE=>'{PARTIAL|NONE}',]
    [VDATE=>'date',]
    [DISPLAYABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [INSERTABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [UPDATEABLE=>'{ALL | flexfield_qualifier | 
           segment_number}[\\0{ALL |
           flexfield_qualifier | segment_number}]',]
    [VRULE=>'flexfield qualifier\\n
           segment qualifier\\n
           {I[nclude]|E[xclude]}\\n
           APPL=application_short_name;
           NAME=Message Dictionary message name\\n
           validation value1\\n
           validation value2...
           [\\0flexfield qualifier\\n
           segment qualifier\\n
           {I[nclude]|E[xclude]}\\n
           APPL=application_short_name;
           NAME=Message Dictionary message name\\n
           validation value1\\n
           validation value2...]',]
    [TITLE =>'Title',]
    [REQUIRED=>'{Y|N}',]
    [AUTOPICK=>'{Y|N}',]
    [USEDBFLDS=>'{Y|N}',]
    [ALLOWNULLS=>'{Y|N}',]
    [DATA_SET=>'set number',]
    [READ_ONLY=>'{Y|N}',]

 /* Parameters specific to type flexfields */
    [TYPE_FIELD=>'block.concatenated_type_values_
           field\\ntype field suffix',]
    [TYPE_VALIDATION=> 'Value set name\\n
           Required\\nDefault value',]
    [TYPE_SIZES=>'type_value_display_
           size\\nDescription_display_size',]
    [TYPE_HEADING=>'type column heading',]
    [TYPE_DATA_FIELD=>'block.type_data_field',]
    [TYPE_DESCRIPTION=>'block.type_
           description_field',]
    [SCOLUMN=>'single column title']
    [HELP=>'APPL=application_short_name;
            TARGET=target_name']
);

Important: TYPE_FIELD, TYPE_DATA_FIELD and TYPE_DESCRIPTION require the block construction, unlike other flexfield arguments that specify field names without block names.

Update Descriptive Flexfield Definition Syntax

Use FND_DESCR_FLEX.UPDATE_DEFINITION for a descriptive flexfield. Other than the ENABLED argument, which you can only use for update procedures, the arguments are the same as you use for the flexfield definition procedures. See: Flexfield Definition Arguments.

 FND_DESCR_FLEX.UPDATE_DEFINITION(
  /* Arguments that specify the flexfield location */
    BLOCK=>'block_name',  FIELD=>'field_name',
 /* Argument to enable or disable flexfield */  [ENABLED=>'{Y|N}',] 
 /* Other optional parameters  */
    [VDATE=>'date',]
    [TITLE =>'Title',]
    [AUTOPICK=>'{Y|N}',]
    [USEDBFLDS=>'{Y|N}',]
    [READ_ONLY=>'{Y|N}',]
    [LOCK_FLAG=>'{Y|N}',]
    [HELP=>'APPL=application_short_name;
            TARGET=target_name',]
    [CONTEXT_LIKE=>'WHERE_clause_fragment'}
     );

Updating Flexfield Definition Example

Suppose you do not want to call VALID for a particular key flexfield in PRE-UPDATE, but want to call it for all other flexfields in the block. Here is an example of disabling and enabling a simple key flexfield definition. This definition provides the default structure (101) of the Accounting Flexfield. You would code your PRE-UPDATE trigger for the block as:

 FND_KEY_FLEX.UPDATE_DEFINITION(
      BLOCK=>'ORDERS', 
      FIELD=>'KFF_CONCATENATED_VALUES',
      ENABLED=>'N');    

    FND_FLEX.EVENT('PRE-UPDATE');   

    FND_KEY_FLEX.UPDATE_DEFINITION(
      BLOCK=>'ORDERS', 
      FIELD=>'KFF_CONCATENATED_VALUES',
      ENABLED=>'Y'); 

Using Key Flexfields in Find Windows

You can use a key flexfield in your Find window if you wish to restrict your query to certain segment values. Create a concatenated segments field in your Find window as a 2000 character displayed text item. You do not need the individual SEGMENTn fields in your Find window. Define the key flexfield you want on this field using the FND_KEY_FLEX.DEFINE procedure. This can be done at the same point where you define the flexfield in your base block. Do not pass values for the ID, DESCRIPTION and DATA_FIELD arguments. The following arguments should be set to the values specified below:

VALIDATE   => 'PARTIAL_IF_POSSIBLE',
REQUIRED   => 'N',
USEDBFLDS  => 'N',
ALLOWNULLS => 'Y'
INSERTABLE => 'ALL', -- Default value
UPDATEABLE => 'ALL', -- Default value

Important: You should set DISPLAYABLE to the same value you used in the definition of the flexfield in your base block.

The above definition allows users to choose values for some segments and leave other segments blank.

Follow the steps described for coding Find windows. In the PRE-QUERY trigger of your base block call the procedure FND_FLEX_FIND.QUERY_KFLEX. The arguments to this function are the application short name, the flexfield code, the structure number, the concatenated segment values and the name of the concatenated segments field in your base block. The procedure specification is given below.

 PROCEDURE query_kflex(appl_short_name VARCHAR2,
                        code            VARCHAR2,
                        num             NUMBER,
                        segments        VARCHAR2,
                        segments_field  VARCHAR2);

Important: The call to FND_FLEX.EVENT('PRE-QUERY') must be made after the FND_FLEX_FIND.QUERY_KFLEX procedure is called.

Query Find Window Example Using Key Flexfields

The following example shows how the Accounting Flexfield can be used in a Find window.

FND_KEY_FLEX.DEFINE(
  		BLOCK           => 'MY_BLOCK_QF',
  		FIELD           => 'SEGMENTS',
  		APPL_SHORT_NAME => 'SQLGL',
  		CODE            => 'GL#',
  		NUM             => 101,
  		VALIDATE        => 'PARTIAL_IF_POSSIBLE',
  		REQUIRED        => 'N',
  		USEDBFLDS       => 'N',
  		ALLOWNULLS      => 'Y');

The PRE-QUERY trigger on MY_BLOCK will be:

...
	IF (:parameter.G_query_find = 'TRUE') THEN
  	  ...
  	  fND_FLEX_FIND.QUERY_KFLEX('SQLGL', 'GL#', 101,
                                    :MY_BLOCK_QF.SEGMENTS,
                                    'MY_BLOCK.SEGMENTS');
  	  ...
  	  :parameter.G_query_find = 'FALSE';
	END IF;
	...
	FND_FLEX.EVENT('PRE-QUERY');

Using Range Flexfields in Query Find Windows

It is often useful to code a range flexfield in your Find window so that users can specify a value range for the flexfield segments instead of a single value. Create two concatenated segments fields (for low and high values) in your Find window as 2000 character displayed text items. The field names should be of the form XXXX_LOW and XXXX_HIGH. You do not need the individual SEGMENTn fields in your Find window. Define the range flexfield you want on this field using the FND_RANGE_FLEX.DEFINE procedure. This can be done at the same point where you define the flexfield in your base block. Do not pass values for the ID, DESCRIPTION and DATA_FIELD arguments. The following arguments to the define call should be set to the values specified below:

VALIDATE   => 'NONE',
REQUIRED   => 'N',
USEDBFLDS  => 'N',
ALLOWNULLS => 'Y'
INSERTABLE => 'ALL', -- Default value
UPDATEABLE => 'ALL', -- Default value

Important: You should set DISPLAYABLE to the same value you used in the definition of the flexfield in your base block.

The value for the VALIDATE argument can be 'PARTIAL' if you want users to enter valid segment values as the upper and lower limit of the ranges they want to query on.

The above definition will allow users to choose values for some segments and leave other segments blank. They can also leave either the high or the low segment value blank to set either the lower limit or the upper limit on the segment values. They can enter the same value for both the low and high fields to query on a specific segment value.

Follow the steps for coding Find windows. In the PRE-QUERY trigger of you base block call the procedure FND_FLEX_FIND.QUERY_ KFLEX_RANGE. The arguments to this function are the application short name, the flexfield code, the structure number, the concatenated low segment values, the concatenated high segment values and the name of the concatenated segments field in your base block. The procedure specification is given below.

 PROCEDURE query_kflex_range(appl_short_name VARCHAR2,
                              code            VARCHAR2,
                              num             NUMBER,
                              low_segments    VARCHAR2,
                              high_segments   VARCHAR2,
                              segments_field  VARCHAR2);

Important: The call to FND_FLEX.EVENT('PRE-QUERY') must be made after the FND_FLEX_FIND.QUERY _KFLEX_RANGE procedure is called.

Query Find Window Example Using Range Flexfields

If you choose to use a range flexfield instead of a key flexfield in the preceding example the flexfield definition and the PRE-QUERY trigger will be:

FND_RANGE_FLEX.DEFINE(
  		BLOCK           => 'MY_BLOCK_QF',
  		FIELD           => 'SEGMENTS',
  		APPL_SHORT_NAME => 'SQLGL',
  		CODE            => 'GL#',
  		NUM             => 101,
  		VALIDATE        => 'NONE',
  		REQUIRED        => 'N',
  		USEDBFLDS       => 'N',
  		ALLOWNULLS      => 'Y');

The PRE-QUERY trigger on MY_BLOCK will be:

...
	IF (:parameter.G_query_find = 'TRUE') THEN
  	  ...
  	  FND_FLEX_FIND.QUERY_KFLEX_RANGE('SQLGL', 'GL#', 101, 
                                 :MY_BLOCK_QF.SEGMENTS_LOW, 
                                 :MY_BLOCK_QF.SEGMENTS_HIGH,
                                 'MY_BLOCK.SEGMENTS');
  	  ...
  	  :parameter.G_query_find = 'FALSE';
	END IF;
	...
	FND_FLEX.EVENT('PRE-QUERY');

Troubleshooting Flexfields

Incorrect Behavior of Flexfields

If you are experiencing strange behavior of your flexfields, the first thing to check is that each of the flexfield triggers pass the correct event name to the flexfields routines. The flexfields routines perform different behavior for different event arguments, and incorrect arguments can cause unusual and unpredictable results.

For example, your FND_FLEX.EVENT call in the WHEN-NEW-ITEM-INSTANCE trigger must pass 'WHEN-NEW-ITEM-INSTANCE' to the flexfield routine. But if you were to pass the 'POST-QUERY' argument in the WHEN-NEW-ITEM-INSTANCE or WHEN-NEW-RECORD-INSTANCE trigger, the segment value defaulting behavior would not work. Always pass the correct event names in your flexfield triggers.

Flexfield Fails to Pop Open

It is the standard behavior of flexfields to not pop open automatically when the user places the cursor in the field (unless the profile options Flexfields:Open Descr Window and Flexfields:Open Key Window are set to do so), so there is not necessarily a coding problem for this behavior. However, if the flexfield fails to open when the user chooses the Edit button on the toolbar or the list of values button, you should verify that you have the correct APP_STANDARD.EVENT code in the following two triggers and that the two triggers are not being overridden by a lower-level trigger:

Flexfields FNDSQF Debugger

If any of the Flexfields FNDSQF library calls (FND_FLEX.EVENT, FND_KEY_FLEX.DEFINE, etc.) is returning errors (i.e. raising unhandled exceptions), you can get debug information by using the Flexfields FNDSQF debugger. The debugger is controlled by a global variable, GLOBAL.FND_FLEX_FNDSQF_DEBUG.

The global variable GLOBAL.FND_FLEX_FNDSQF_DEBUG takes one of the following values: 'OFF', 'EXCEPTION', 'FAILURE', 'DEBUG'.

You set GLOBAL.FND_FLEX_FNDSQF_DEBUG through the Examine window before you open your form.

  1. From the Help menu, navigate to Diagnostics > Examine.

  2. Enter GLOBAL for Block, and FND_FLEX_FNDSQF_DEBUG for Field. Tab to Value field. (If you get a "variable doesn't exist" error, ignore it.) Enter one of the values below and click OK.

The following are valid values for GLOBAL.FND_FLEX_FNDSQF_DEBUG:

Register Key Flexfields

the picture is described in the document text

Register a key flexfield after defining the flexfield combinations table in the database, and after registering your table using the table registration API.

Important: Do not modify the registration of any key flexfield supplied with Oracle E-Business Suite. Doing so can cause serious application errors. To enable an Oracle E-Business Suite key flexfield, define and freeze it using the Key Flexfield Segments window.

Important: Do not attempt to make a copy of an Oracle E-Business Suite key flexfield (using the same table, same title, or same flexfield code), since the duplicates may cause errors in forms that call these flexfields.

If you are using segment qualifiers with your flexfield, you should define the QuickCode values for your segment types using the Lookups window.

You name your flexfield and associate it with an application and a database table. You also specify which table column you want to use as a unique ID column and which table column you want to use as a structure column.

See: Defining Key Flexfields, Oracle E-Business Suite Flexfields Guide

Defining Key Flexfield Structures, Oracle E-Business Suite Flexfields Guide

Key Flexfield Segments window, Oracle E-Business Suite Flexfields Guide

Table Registration API

Register Key Flexfields Block

Application

An application installer sees this application name when defining your flexfield segments in the Define Key Segments window. Forms and flexfield routines use the combination of application and flexfield name to uniquely identify your flexfield. You use this application name when you use flexfield routines to call your key flexfield from your forms or programs.

Code

You use this short, unique code to invoke a key flexfield from a form trigger.

Title

An installer may modify the user-friendly title of your flexfield using the Define Key Segments form. You see this title whenever you choose this flexfield in a flexfield window.

Table Application

Enter the name of the application with which your flexfield combinations table is registered.

Table Name

Enter the name of your combinations table. Your combinations table must already exist in the database, and it must have the appropriate flexfield columns.

You must register your combinations table with Oracle E-Business Suite before you can use it in this field.

Unique ID Column

Enter the name of the column in your combinations table that contains the unique ID for this flexfield. Other tables which reference this flexfield should use this column as a foreign key.

Structure Column

Enter the name of the column in your combinations table that your flexfield can use to differentiate among flexfield structures. If you enter a column in this field you must also use the NUM= parameter in all of the flexfield calls in your forms.

Dynamic Inserts Feasible

Indicate whether dynamic inserts are feasible for this key flexfield. Dynamic inserts are feasible only if your combinations table contains no mandatory, non-flexfield columns.

Dynamic inserts cannot be feasible if your application requires special validation of new segment combinations.

Allow ID Value Sets

Indicate whether to allow values sets that use a hidden ID in your flexfield.

Detail Buttons

Variable Description
Qualifiers Choose this button to open the Qualifies window where you define flexfield and segment qualifiers.
Columns Choose this button to open the Columns window where you enable the columns to use with your flexfield segments

Qualifiers Window

the picture is described in the document text

Define flexfield and segment qualifiers. A flexfield qualifier applies to specific segments your user define, and a segment qualifies applies to specific values in your flexfield segments. You must define a flexfield qualifier before you can define segment qualifiers.

Qualifier Name

Use this unique name to reference key flexfield structure information.

Prompt

When you set up your key segments this prompt asks you for the qualifiers information for your key flexfield. Since flexfield qualifiers use check boxes in the Define Key Segments form, you should specify your prompt so it makes sense as the prompt of a Yes/No field.

When you set up your key segments this prompt asks you for the qualifiers information for your key flexfield. Since flexfield qualifiers use check boxes in the Define Key Segments form, you should specify your prompt so it makes sense as the prompt of a check box.

Global

Global flexfield qualifiers apply to all segments, and provide a convenient mechanism for assigning a group of segment qualifiers to all segments.

Required

Required flexfield qualifiers must apply to at least one but possibly more segments.

Unique

Unique flexfield qualifiers apply to one segment only.

Segment Qualifiers

A segment qualifier applies to specific values your end user defines using the Define Key Segment Values window. Segment qualifiers expect QuickCodes values.

Name

Use this unique name to reference key segment value information in flexfield routine calls and your application logic.

Prompt

The Segment Values window displays this prompt to ask you for information about each segment value when you define key segment values. Since segment qualifiers receive QuickCode values in the Segment Values window, you should specify your prompt so it makes sense to your end user.

Derived Column

Enter the name of a database column in your combinations table that holds the derived value of this segment qualifier. Flexfields automatically derives a value for your segment qualifier into this column whenever your end user enters a new valid combination.

QuickCode Type

Enter a Special QuickCode type for this segment qualifier. A Special QuickCode type defines the group of values you wish to allow for this segment qualifier. For example, if you have a segment qualifier called "Account Type" you might want a Special QuickCode type called "ACCOUNT_TYPE" that has several codes and meanings. You define Special QuickCode values using the Define Special QuickCodes form.

Default Value

A default value must be one of the defined Special QuickCode values for the Special QuickCode type you choose in the QuickCode Type field.

Columns Window

the picture is described in the document text

Specify the columns your key flexfield can use as segment columns. This window automatically queries up most of the columns you registered when you registered your table. If you have recently added columns to your table, you should reregister your table to ensure you see all your columns. The table columns you specify as your unique ID column or your structure column in the Key Flexfield zone do not appear.

If your table contains columns with names of the form SEGMENT1, SEGMENT2, SEGMENT3, and so on, those columns are automatically Enabled for your flexfield. You must enable any other column you want to use for your segment columns by changing the value of the Enabled check box.

For example, if you have more than one key flexfield, your second key flexfield may have different segment column names such as TAX1, TAX2, TAX3 and TAX4. In this case, you would enable TAX1, TAX2, TAX3 and TAX4 and disable SEGMENT1, SEGMENT2, SEGMENT3, and so on for your second key flexfield.

Warning: If you are redefining the Accounting Flexfield for Oracle General Ledger (this key flexfield is used by most of the Oracle E-Business Suite products), you must not use any columns other than those named SEGMENT1 through SEGMENT30. Since the names of these columns are embedded in the Oracle E-Business Suite products, using other columns may adversely affect your application features such as summarization.

Enabled

Indicate whether this column can be used as a segment column for your key flexfield. If you enable a column as a segment column for a key flexfield, you should not enable the same column for another key flexfield that uses the same table.

Register Descriptive Flexfields

the picture is described in the document text

Register your flexfield after adding the descriptive flexfield columns to your table and registering your table. You must register a descriptive flexfield before you can use it in an application.

Use this window to provide information about your descriptive flexfield. Give your flexfield a name and associate it with an application and a database table. Also specify which table column you want to use as a structure column.

Register Descriptive Flexfields Block

Forms and flexfield routines use the combination of application name and flexfield name to uniquely identify your flexfield.

Application

An application installer sees this application name when defining your descriptive flexfield in the Define Descriptive Segments window. Use this application name when you use flexfield routines to call your descriptive flexfield from your forms or programs.

Name

Use this name when you use flexfield routines to call your descriptive flexfield from your forms or programs.

Title

Flexfields displays this unique title at the top of the flexfield window when your users enter your descriptive flexfield. An application installer can modify this title using the Define Descriptive Segments window.

Table Name

Enter the name of the table that contains your descriptive flexfield columns. Your table must already exist in the database, and it should already have columns for your descriptive flexfield segments, as well as a structure column. These segment columns are usually called ATTRIBUTE1, ATTRIBUTE2, ..., ATTRIBUTEn.

You must register your table with Oracle E-Business Suite before you can use it in this field.

Structure Column

Enter the name of the column, such as ATTRIBUTE_CATEGORY, in your table that your flexfield uses to differentiate among descriptive flexfield structures. Your descriptive flexfield uses this column to let your users see different descriptive flexfield structures based on data supplied by the form or the user. You must have a structure column even if you only intend to use one descriptive flexfield structure.

Context Prompt

Enter a default context field prompt that asks your user which descriptive flexfield structure to display. Depending upon how your application installer defines your descriptive flexfield, your user may or may not see a context field as part of the descriptive flexfield pop-up window. Descriptive flexfield windows display this context field prompt if the installer allows the end user to override the default context field value.

If your application installer defines it, the context field appears to the user as if it were simply another flexfield segment (with the prompt you specify here). Your user enters a value in the context field, and other descriptive flexfield segments pop up based on that value. The installer can modify the context field prompt using the Define Descriptive Segments window.

Protected

In some cases, you may want to create a descriptive flexfield that cannot be inadvertently changed by an installer or user. This type of flexfield is called a protected descriptive flexfield. You build a protected descriptive flexfield the same way you build a normal descriptive flexfield. The main difference is that you check the Protected check box after defining your segment structures. Once a descriptive flexfield is protected, you cannot query or change its definition using the Descriptive Flexfield Segments window. You should define your descriptive flexfield segments before you change the Protected check box. For more information, see the Oracle E-Business Suite Flexfields Guide.

Detail Buttons

Variable Description
Reference Fields Choose this button to open the Reference Fields window where you select possible reference fields for your descriptive flexfield.
Columns Choose this button to open the Columns window where you enable table columns for your descriptive flexfield segments.

Reference Fields Window

the picture is described in the document text

Use this window to specify any form fields that might serve as descriptive flexfield reference fields. Your flexfield can use values in one of these fields (context field values) to determine which flexfield structure to display.

An installer using the Define Descriptive Segments window can choose to use one of these window fields to obtain the context field value for your descriptive flexfield.

You should specify all form fields that contain information an installer might use to obtain a context field value. For example, the descriptive flexfield in an application form may be used to capture different information based on which country is specified in a field on that form, or based on a name specified in another field. In this case, both the country field and the name field should be listed as potential reference fields, and the installer can decide which reference field to use (or neither).

An installer typically defines different structures of descriptive flexfield segments for each value that the reference field would contain. Though the installer does not necessarily define a structure for all the values the reference field could contain, a field that has thousands of possible values may not be a good reference field. In general, you should only list fields that will contain a relatively short, static list of possible values, such as a field that offers a list of countries.

A good reference field usually has a defined List of Values. You should not list fields that could contain an infinite number of unique values, such as a PO Number field. Often the business uses of the particular form dictate which fields, if any, are acceptable reference fields.

You may specify additional fields to be available as reference fields even after you have registered your flexfield.

Important: This zone will not be included in a future release of the Oracle E-Business Suite. An installer will be able to use any field of the form (that contains the flexfield) as a reference field.

Field Name

Enter the name of a reference field your flexfield can use to obtain context field values.

Enter the actual (hidden) Oracle Forms name of the field, rather than the boilerplate name of the field (the field prompt). Do not include the block name. The Define Descriptive Segments window displays this field name in a list an installer sees when defining descriptive flexfield segments.

This field must exist in the same block as the descriptive flexfield. In addition, if you call your descriptive flexfield from several different forms or zones, the same field must exist in all form blocks that contain this descriptive flexfield.

Description

Since the actual Oracle Forms field names often do not match the boilerplate prompts for your fields, we recommend that you enter the visible field prompt as part of your description of your context reference field so an installer can easily tell which field to define as the reference field for your descriptive flexfield.

Columns Window

the picture is described in the document text

Use this window to specify the columns your descriptive flexfield can use as segment columns. When you navigate into this block, this window automatically queries up most of the columns you registered when you registered your table.

If you have recently added columns to your table, you should reregister your table to ensure you see all your columns in this zone. This window does not display the table column you specify as your structure column in the Descriptive Flexfield zone.

If your table contains columns with names ATTRIBUTE1, ATTRIBUTE 2, ATTRIBUTE3, and so on, those columns are automatically Enabled. To use other columns for your flexfield segments, you must set explicitly enable them.

For example, if you have more than one descriptive flexfield, your second descriptive flexfield may be a protected descriptive flexfield with different segment column names such as TAX1, TAX2, TAX3 and TAX4. In this case, you would enable TAX1, TAX2, TAX3 and TAX4 and disable ATTRIBUTE1, ATTRIBUTE 2, ATTRIBUTE3, and so on for your protected descriptive flexfield.

Enabled

Indicate whether this column can be used as a segment column for your descriptive flexfield. If you enable a column as a segment column for a descriptive flexfield, you should not enable the same column for another descriptive flexfield that uses the same table.

Any columns you enable here appear when an installer defines segments using the Define Descriptive Segments window.