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.
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 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.
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.
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.
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.
For more explanation of flexfields features and concepts, see the Oracle E-Business Suite Flexfields Guide.
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.
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.
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.
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.
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 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.
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.
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.
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.
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.
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
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.
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
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:
Oracle E-Business Suite Flexfields Guide
To implement a key flexfield you must:
Define key flexfield columns in your database
Register your table with Oracle Application Object Library
Register your key flexfield with Oracle Application Object Library
Create key flexfield fields in your forms
Add key flexfield routines to your forms
Key flexfields can be implemented for the following three types of forms, which are each implemented differently:
Combinations form - The only purpose of a combinations form is to create and maintain code combinations. The combinations table (or a view of it) is the base table of this form and contains all the key flexfield segment columns. The combinations table also contains a unique ID column. This type of form is also known as a maintenance form for code combinations. You would have only one combinations form for a given key flexfield (though you might not have one at all). You cannot implement shorthand flexfield entry for a combinations form.
Form with foreign key reference - The base table (or view) of the form contains a foreign key reference to a combinations table that contains the actual flexfield segment columns. You create a form with a foreign key reference if you want to use your form to manipulate rows containing combination IDs. The primary purpose of foreign key forms is generally unrelated to the fact that some fields may be key flexfields. That is, the purpose of the form is to do whatever business function is required (such as entering orders, receiving parts, and so on). You might have many foreign key forms that use a given key flexfield. You can choose to implement shorthand flexfield entry only for a form with a foreign key reference.
Form with key flexfield range - The base table is a special "combinations table" that contains two columns for each key flexfield segment so it can support both low and high values for each segment of your key flexfield. This type of form is rare.
For many applications, you would have one combinations form that maintains the key flexfield, where the key flexfield is the representation of an entity in your application. Then, you would also have one or more forms with foreign key references to the same key flexfield. For example, in an Order Entry/Inventory application, you might have a combinations form where you define new parts with a key flexfield for the part numbers. You would also have a form with foreign key reference where you enter orders for parts, using the key flexfield to indicate what parts make up the order.
Further, you can have another form, a form with a key flexfield range, that you use to manipulate ranges of your part numbers. This range flexfield form refers to the same key flexfield as both your combinations forms and your foreign key forms, though the ranges of segment values (a low value and a high value for each segment) are stored in the special range flexfield table that serves as the range form's base table.
A special kind of key flexfield you can include in your application to support low and high values for each key segment rather than just single values. Ordinarily, a key flexfield range appears on your form as two adjacent flexfields, where the leftmost flexfield contains the low values for a range, and the rightmost flexfield contains the high values.
In Oracle Application Object Library, we use a key flexfield range to help you specify cross-validation rules for valid combinations.
For each key flexfield you design into your application, you must create a combinations table to store the flexfield combinations that your users enter. You can build a special form to let them define valid combinations (the combinations form), or you can let Oracle Application Object Library dynamically create the combinations when users attempt to use a new one (from a form with a foreign key reference). You must have the combinations table even if you do not build a combinations form to maintain it. Key flexfields provided by Oracle 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.
Key flexfields support a maximum of 70 segment columns in a combinations table. For example, a combinations table includes a column for the unique ID that your key flexfield assigns to each valid combination. It also includes a structure defining column, in case your end user wants to define multiple structures. If you want to use segment qualifiers in your application, your table should include a derived column for each segment qualifier you define.
To create a key flexfield combinations table for your application entity, you must:
Define an ID column to uniquely identify a row in your database table (type NUMBER, length 38, NOT NULL). You should name this column XXX_ID, where XXX is the name of your entity (for example, PART_ID). This column holds the unique ID number of a particular combination of segment values (also known as a code combination). The unique ID number is also known as a code combination ID, or CCID. Note that even though this column is a NUMBER(38) column, Oracle Application Object Library only supports code combination IDs up to two billion (2,000,000,000).
Define a column for each key segment, SEGMENT1 through SEGMENTn, where n is the number of segments you want for your key flexfield (type VARCHAR2, length 1 to 60, all columns the same length, NULL ALLOWED). As a rule of thumb, you should create about twice as many segment columns as you think your users might ever need for a single key flexfield structure. The maximum number of key flexfield segment columns that Oracle Application Object Library supports in a combinations table is 70. However, for a combinations table that you want to use with a form with a foreign key reference, the number of segments is also limited by the maximum size of the field that holds the concatenated segment values and segment separators. That field is normally 2000 characters, so if you have 40 segments and 40 separators, each segment could only have an average width of about 49 characters. Having more segment columns than you need does not significantly impact either space requirements or performance. In fact, since you cannot add more segment columns to a flexfield combinations table once you have registered your flexfield, you should add at least a few "extra" segment columns to your combinations table initially to allow for future needs.
Define SUMMARY_FLAG and ENABLED_FLAG (type VARCHAR2, length 1, NOT NULL).
Define START_DATE_ACTIVE and END_DATE_ACTIVE (type DATE, NULL).
Define a structure defining column (structure ID column) to allow multiple structures (type NUMBER, length 38, NOT NULL). You should name this column XXX_STRUCTURE_ID, where XXX is the name of your entity (for example, PART_STRUCTURE_ID). This column is optional but strongly recommended.
Define a unique index on the unique ID column.
Create an ORACLE sequence for your column with the same grants and synonyms as your combinations table (for insert privileges). Name your sequence YOUR_TABLE_NAME_S.
Define the Who columns, LAST_UPDATE_DATE (type DATE, NOT NULL) and LAST_UPDATED_BY (type NUMBER, length 15, NOT NULL). All other Who columns should have NULL ALLOWED.
If you want your application to allow dynamic insertion of new valid combinations from a form with a foreign key reference, you must not include any mandatory application-specific columns in your combinations table. Your combinations table contains only the columns you need to define a key flexfield, such as unique ID, structure defining, and segment columns. It can, however, include non-mandatory application-specific columns and columns for derived segment qualifier values. If you include mandatory application-specific columns in your combinations table, you cannot allow dynamic insertion of new valid combinations from a form with a foreign key reference. If your table does not allow dynamic insertion, you must create a combinations form, based on your combinations table, for your users to create their valid combinations.
If you do not ever want to allow dynamic insertion of new valid combinations, you should develop a single form that allows your end user to directly display, enter, or maintain valid combinations in your combinations table (a combinations form). You can set up your key flexfield to not allow dynamic inserts (on a structure-by-structure basis) even if dynamic inserts are possible.
Warning: You should never insert records into a code combinations table through any mechanism other than Oracle Application Object Library flexfield routines. Doing so could lead to serious data corruption problems and compromise your applications.
For each table you use as a base table for a form with a foreign key reference (to a combinations table's unique ID column), define one database column with the same name as the unique ID column in the corresponding combinations table (type NUMBER, length 38, and NULL or NOT NULL depending on your application's needs).
If you have a structure column in your combinations table, you also need to include a structure column in your foreign key table (with a corresponding form field), or provide some other method for passing the structure ID number to the NUM parameter in your calls to key flexfield routines. For example, you could store the structure number in a profile option and use the option value in the NUM parameter.
You do not need any SEGMENTn columns or other key flexfield columns for this type of table.
To create a table that supports a key flexfield range instead of a foreign key reference to a single combination, define SEGMENTn_LOW and SEGMENTn_HIGH columns, one pair for each SEGMENTn column in your combinations table (type VARCHAR2, length 1 to 60, all columns the same length, NULL).
If you have a structure column in your combinations table, you also need to include a structure column in your range table (with a corresponding form field), or provide some other method for passing the structure ID number to the NUM parameter in your calls to key flexfield routines. For example, you could store the structure number in a profile option and use the option value in the NUM parameter.
You do not need any other flexfield columns for this table.
After you create your combinations table, you must register your table with Oracle Application Object Library using the Table Registration API.
Once your table is successfully registered, you register your key flexfield with Oracle Application Object Library. You register your key flexfield using the Key Flexfields window.
When you register a key flexfield, you identify the combinations table in which it resides, as well as the names of the unique ID and structure defining columns. Key flexfields provided by Oracle E-Business Suite are already registered.
When you register a key flexfield, you can define flexfield and segment qualifiers for it.
You should define flexfield qualifiers if you want to ensure your end user customizes your key flexfield to include segments your application needs. For example, Oracle General Ledger defines account and balancing flexfield qualifiers in the Accounting Flexfield to ensure that end users would define account and balancing segments.
You should define segment qualifiers if your application needs to know semantic characteristics of key segment values your end user enters. You assign one or more segment qualifiers to each flexfield qualifier. For example, Oracle General Ledger assigns a segment qualifier of "account type" to the flexfield qualifier "account" in the Accounting Flexfield. As a result, end users can define account value 1000 to mean "Cash," and assign it a segment qualifier value of "Asset."
Note that flexfield qualifiers can be unique or global, and required or not. You describe a flexfield qualifier as unique if you want your end user to tie it to one segment only. You describe a flexfield qualifier as global if you want it to apply to all segments. You can use a global flexfield qualifier as a convenient means for assigning a standard set of segment qualifiers to each of your flexfield's segments. You describe a flexfield qualifier as required if you want your end user to tie it to at least one segment.
In Oracle General Ledger's Accounting Flexfield, the "Account" flexfield qualifier is required and unique because Oracle General Ledger requires one and only one account segment. Oracle General Ledger defines a flexfield qualifier as "global" so the segment qualifiers "detailed posting allowed" and "detailed budgeting allowed" apply to each Accounting Flexfield segment. For more information, see: Oracle General Ledger User's Guide, Oracle E-Business Suite Flexfields Guide.
A column you include in a combinations table into which your flexfield derives a segment qualifier value. You specify the name of a derived column when you define a segment qualifier.
Once you have the appropriate table columns and your flexfield is registered, you can build your flexfield into your application forms.
See: Adding Flexfields to Your Forms
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:
Define descriptive flexfield columns in your database
Register your table with Oracle Application Object Library
Register your descriptive flexfield with Oracle Application Object Library
Create descriptive flexfield fields in your forms
Add descriptive flexfield routines to your forms
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.
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:
Define a column for each descriptive segment, ATTRIBUTE1 through ATTRIBUTEn (type VARCHAR2, length 1 to 150, all columns the same length, NULL ALLOWED). In addition to VARCHAR2 columns, number and date columns are supported.
Define a structure defining column (context column) to identify your descriptive flexfield structures (type VARCHAR2, length 30, NULL ALLOWED). Although you can name this column anything you wish, we recommend that you name it ATTRIBUTE_CATEGORY.
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.
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.
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.
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.
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.
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
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
Create your displayed fields
Create your flexfield definition
Invoke your flexfield definition from several event triggers
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 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.
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.
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.
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.
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.
To define a key flexfield, use the procedure FND_KEY_FLEX.DEFINE
To define a range or type flexfield, use the procedure FND_RANGE_FLEX.DEFINE
To define a descriptive flexfield, use the procedure FND_DESCR_FLEX.DEFINE
When you call these procedures, you specify three types of arguments:
location(s) of the flexfield (block and fields, including the concatenated values field, the ID field if any, and any description or related fields)
specific registered flexfield you want (application, flexfield, and structure if necessary)
any additional 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.
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.
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.
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',] [ALLOWNULLS=>'{Y|N}'] );
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).
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. |
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. |
If you do not specify a particular optional argument, the flexfield routine uses the usual default value for the argument.
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 |
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. |
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');
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=>'');
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.
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');
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.
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');
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');
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.
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).
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'] );
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.
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'} );
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');
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.
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');
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.
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');
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.
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:
KEY-EDIT
KEY-LISTVAL
If any of the Flexfields FNDSQF library calls (FND_FLEX.EVENT, FND_KEY_FLEX.DEFINE, and so on) is returning errors (that is, 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', 'SPOOL', and 'DEBUG_SPOOL'.
The options "SPOOL' and 'DEBUG_SPOOL' are introduced in Release 12.2.10 and allow for the debug information to be written to a file. The debugger generates a log file in a defined database directory for PL/SQL file I/O. See My Oracle Support Knowledge Document 2525754.1, Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2.
A log file has a name in the format flexsqf_sessionID_DateStamp{yyyymmdd).log
; for example, flexsqf_327_20200817.log
.
You set GLOBAL.FND_FLEX_FNDSQF_DEBUG through the Examine window before you open your form.
From the Help menu, navigate to Diagnostics > Examine.
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:
OFF - The default value. The debugger is turned off. Debug messages will not be displayed.
EXCEPTION - Only exception debug messages will be displayed. These messages come from 'EXCEPTION WHEN OTHERS THEN' parts of the code. Flexfields will still RAISE the exceptions, that is, these exceptions will not be handled by the flexfields code.)
FAILURE - Failure and exception debug messages will be displayed. In general, these messages are from IF (NOT FORM_SUCCESS) THEN parts of the code.
DEBUG - All debug messages will be displayed in modal windows.
SPOOL - No information is displayed in modal windows; instead, the debugger writes the information to a log file.
DEBUG_SPOOL - The debugger displays the debug information in modal windows and also writes the information to a log file.
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
This block contains the following fields.
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.
You use this short, unique code to invoke a key flexfield.
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.
Enter the name of the application with which your flexfield combinations table is registered.
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.
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.
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.
The view name for the key flexfield view.
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.
Indicate whether to allow values sets that use a hidden ID in your flexfield.
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 |
Flexfield Qualifiers Window
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.
Use this unique name to reference key flexfield structure information.
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 flexfield qualifiers apply to all segments, and provide a convenient mechanism for assigning a group of segment qualifiers to all segments.
Required flexfield qualifiers must apply to at least one but possibly more segments.
Unique flexfield qualifiers apply to one segment only.
A segment qualifier applies to specific values your end user defines using the Define Key Segment Values window. Segment qualifiers expect QuickCodes values.
Use this unique name to reference key segment value information in flexfield routine calls and your application logic.
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.
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.
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.
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
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.
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 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.
For more information on setting up a descriptive flexfield, see Descriptive Flexfield Concepts, Oracle E-Business Suite Flexfields Guide and related topics.
Forms and flexfield routines use the combination of application name and flexfield name to uniquely identify your flexfield.
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.
Use this name when you use flexfield routines to call your descriptive flexfield from your forms or programs.
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.
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.
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.
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.
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.
The view name for the descriptive flexfield view.
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
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.
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.
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
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 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.
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.
Important: If you enable a table column here, then that column is reserved for flexfield use. Columns reserved for flexfield use may or may not be used in the flexfield definition by the end user in the Descriptive Flexfield Segments form. Columns reserved for flexfields use but are not used or disabled will be nulled out by the application. If a segment column was once used and then disabled, then that column will be overwritten with a null value and it will also be available for reuse.
In the Descriptive Flexfield Segments form, if you disable an ATTRIBUTE table column that was once enabled, then any values that were saved in that column will be overwritten with a null value by the application. That column will also be available for reuse by another segment.
See also: Descriptive Flexfields Segment Window, Oracle E-Business Suite Flexfields Guide.
Key Flexfield Registration Page
This HTML-based Key Flexfields Registration page can be used as an alternative to the Oracle Forms-based Register Key Flexfields window to register a key flexfield.
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
You can search for key flexfields by owning application, name, title, or table name.
Use the Create button to register a new key flexfield.
Enter the following for your key flexfield:
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.
You use this short, unique code to invoke a key flexfield.
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.
Indicate whether to allow values sets that use a hidden ID in your flexfield.
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.
Enter the name of the application with which your flexfield combinations table is registered.
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.
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.
Use the LOV to 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.
Note: Use the LOV to select the column from a list of existing columns.
The view name for the key flexfield view.
Specify the columns your key flexfield can use as segment columns. This region 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 moving the column from the Available list to the Enabled list. Columns in the Enabled list can be used as segment columns 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.
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.
Click Apply to save your changes.
Click Apply & Add Qualifiers to save your changes, and to navigate to the Key Flexfield Qualifiers page.
Define flexfield and segment qualifiers on this page.
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.
Use this unique name to reference key flexfield structure information.
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 flexfield qualifiers apply to all segments, and provide a convenient mechanism for assigning a group of segment qualifiers to all segments.
Required flexfield qualifiers must apply to at least one but possibly more segments.
Unique flexfield qualifiers apply to one segment only.
A segment qualifier applies to specific values your end user defines using the Define Key Segment Values window. Segment qualifiers expect QuickCodes values.
Use this unique name to reference key segment value information in flexfield routine calls and your application logic.
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.
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.
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.
A default value must be one of the defined Special QuickCode values for the Special QuickCode type you choose in the QuickCode Type field.
Descriptive Flexfield Registration Page
This HTML-based Descriptive Flexfields Registration page can be used as an alternative to the Oracle Forms-based Register Descriptive Flexfields window for registering a descriptive flexfield.
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 page 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.
For more information on setting up a descriptive flexfield, see Descriptive Flexfield Concepts, Oracle E-Business Suite Flexfields Guide and related topics.
You can search for descriptive flexfields by owning application, name, title, or table name.
Use the Create button to register a new descriptive flexfield.
Forms, HTML-based pages, and flexfield routines use the combination of application name and flexfield name to uniquely identify your flexfield.
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.
Use this name when you use flexfield routines to call your descriptive flexfield from your forms or programs.
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.
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 Flag 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 Flag box.
Enter information on the descriptive flexfield table in this region.
Enter the owning application name for the table.
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.
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.
Note: This field is called "Structure Column" in the Register Descriptive Flexfields window.
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.
The view name for the descriptive flexfield view.
Use this region to specify the columns your descriptive flexfield can use as segment columns. After you have specified the table name and context column for your descriptive flexfield table, the Available Columns list is automatically populated with most of the columns you registered when you registered your table. Move a column to the Enabled Columns list to enable it.
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 region does not display the table column you specify as your context (structure) column in the Descriptive Flexfield Table region.
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 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.
The columns in this list 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.
Important: If you enable a table column here, then that column is reserved for flexfield use. Columns reserved for flexfield use may or may not be used in the flexfield definition by the end user in the Descriptive Flexfield Segments form. Columns reserved for flexfields use but are not used or disabled will be nulled out by the application. If a segment column was once used and then disabled, then that column will be overwritten with a null value and it will also be available for reuse.
In the Descriptive Flexfield Segments form, if you disable an ATTRIBUTE table column that was once enabled, then any values that were saved in that column will be overwritten with a null value by the application. That column will also be available for reuse by another segment.
See also: Descriptive Flexfields Segment Window, Oracle E-Business Suite Flexfields Guide.
Use this region 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.
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.
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.