The values of context fields and/or reference fields influence both the behavior and the appearance of descriptive flexfields.

All descriptive flexfields have a hidden context field that holds structure information for the descriptive flexfield (this field is often called ATTRIBUTE_CATEGORY or CONTEXT). Depending on how you set up the flexfield, a user may also be able to see and change the context field in the descriptive flexfield window.

With earlier versions of Oracle E-Business Suite (formerly Oracle Applications), you allow users to see and modify the value in the context field in the descriptive flexfield window by checking the "Override Allowed (Display Context)" check box. This check box is now called "Displayed" though its effect is unchanged.

Typically, you set up context field values by typing them into the Descriptive Flexfield Segments window individually, and you then set up context-sensitive segments for each context field value. In some cases, however, you may have an existing table of the values that would be valid context field values but would not all have corresponding context-sensitive segments (for example, a table of countries), and you do not want to duplicate the contents of the existing table by creating a new context field value for each existing value in your table (each country name, for example). In this case, you can set up a value set containing your existing values and use the value set to populate the context field. You must still type in the context field value when you set up any context-sensitive segments for that value.

Value sets used for context fields must obey certain restrictions or they will not be available to use in the Value Set field in the Context Field region of the Descriptive Flexfield Segments window:

• Format Type must be Character (Char)

• Numbers Only must not be checked (alphabetic characters are allowed)

• Uppercase Only must not be checked (mixed case is allowed)

• Right-justify and Zero-fill Numbers must not be checked

• Validation Type must be Independent or Table

If the validation type is Independent:

• the value set maximum size must be less than or equal to 30

If the validation type is Table:

• the ID Column must be defined, it must be Char or Varchar2 type, and its size must be less than or equal to 30. The ID column corresponds to the context field value code (the internal, non-translated context field value).

• the Value Column must be defined, it must be Char or Varchar2 type, and its size must be less than or equal to 80. The Value column corresponds to the context field value name (the displayed context field value).

• the value set maximum size must be less than or equal to 80

All context field values (the code values) you intend to use must exist in the value set. If you define context field values in the Context Field Values block of the Descriptive Flexfield Segments window that do not exist in the context field value set, they will be ignored, even if you have defined context-sensitive segments for them.

In the case where the context field is displayed, there are no global segments, and a context field value is in the value set but does not have any context-sensitive segments, only the context field is displayed. The context field value the user chooses from the value set would then be stored in the structure column of the underlying descriptive flexfield table, but no values would be stored in the ATTRIBUTEn segment columns.

Using table-validated value sets with your context field allows you to make your context field values conditional, such as by restricting the values by the value of a profile option bind variable in the WHERE clause of the value set.

Example of using a value set with a context field

Suppose we have a table that has all the countries defined, and the table is called MY_COUNTRIES_TABLE. The following table shows some sample data:

Also, suppose that depending on some profile option we want our users to see only a subset of the country data. Here is the value set definition:

MY_COUNTRIES_VALUE_SET
Format Type     : Char
Maximum Size    : 80
Validation Type : Table
Table Name      : MY_COUNTRIES_TABLE
Value Column    : COUNTRY_NAME/Varchar2/80
Meaning Column  : DESCRIPTION/Varchar2/100
ID Column       : COUNTRY_CODE/Varchar2/30

WHERE/ORDER BY Clause :
      WHERE region = :$PROFILES$.CURRENT_REGION
           ORDER BY country_name

Now, when a user logs in from a site in the Europe region, for example, he or she would be able to see only European countries in the context field list of values.

Example of combining table values and context values in a value set

Suppose you defined some countries in the Context Field Values block of the Descriptive Flexfield Segments window (these values will be in the view FND_DESCR_FLEX_CONTEXTS_VL), and you have other countries in MY_COUNTRIES_TABLE. However, some of the context values in FND_DESCR_FLEX_CONTEXTS_VL do not exist in MY_COUNTRIES_TABLE. If you do not define them in your context field value set then you will not be able to use them, but you do not want to add (duplicate) them in your custom table. The solution is to create a view that is a union of the two tables, and to create a table-validated value set using that view. Here is an example:

Define the following view:

MY_COUNTRIES_UNION_VIEW
CREATE OR REPLACE VIEW MY_COUNTRIES_UNION_VIEW
        (region, country_code,
        country_name, description)
AS
SELECT  'N/A', descriptive_flex_context_code,
                descriptive_flex_context_name,
                description
FROM    FND_DESCR_FLEX_CONTEXTS_VL
WHERE   application_id = 123  -- Assume DFF's app id is 123
AND     descriptive_flexfield_name = 
        'Address Descriptive Flexfield'
AND     global_flag = 'N'
AND     enabled_flag = 'Y'
UNION
SELECT  region, country_code
        country_name,
        description
FROM    MY_COUNTRIES_TABLE
WHERE   enabled_flag = 'Y'

Then define the following value set.

MY_COUNTRIES_VALUE_SET
Format Type     : Char
Maximum Size    : 80
Validation Type : Table
Table Name      : MY_COUNTRIES_UNION_VIEW
Value Column    : COUNTRY_NAME/Varchar2/80
Meaning Column  : DESCRIPTION/Varchar2/100
ID Column       : COUNTRY_CODE/Varchar2/30

WHERE/ORDER BY Clause :
           WHERE (region = 'N/A' OR 
                  region = :$PROFILES$.CURRENT_REGION)
           ORDER BY country_name

This gives the correct union. Note that you cannot do a union in the value set WHERE/ORDER BY clause.

Example of conditional context field values without a separate table

Suppose you already defined all of your context field values, and you do not need another table. However, you want to make the values in the context field list of values conditional on some criteria (data striping).

Suppose you defined your context values using a pattern such as "<CountryCode>.<ApplicationShortName>.<FormName>. <BlockName>", where a context field value might be something like "US.SQLPO.POXPOMPO.HEADER" (this pattern is similar to that used for some globalization features of ). You want users located at U.S. sites to see only 'US.%' contexts. Here is the value set that you might define:

Custom_Globalization_Value_set 

Format Type     : Char
Maximum Size    : 80
Validation Type : Table
Table Name      : FND_DESCR_FLEX_CONTEXTS_VL
Value Column    : DESCRIPTIVE_FLEX_CONTEXT_NAME/Varchar2/80
Meaning Column  : DESCRIPTION/Varchar2/240
ID Column       : DESCRIPTIVE_FLEX_CONTEXT_CODE/Varchar2/30

WHERE/ORDER BY Clause :
            WHERE application_id = 123
            AND   descriptive_flexfield_name = 
                     'My Descriptive Flexfield'
            AND   global_flag = 'N'
            AND   enabled_flag = 'Y'
            AND   descriptive_flex_context_code LIKE 'US.%'
            ORDER BY descriptive_flex_context_name

Note That 'US.%' in the WHERE clause can be replaced with :$PROFILES$.COUNTRY_CODE || '.%' to make it conditional by the users' country.

Using a field as a reference field has no effect on the field itself. That is, the reference field is simply a normal form field that has nothing to do with the flexfield unless you define the flexfield to use it as a reference field. Typically, an application developer specifies one or more fields on the form as potential reference fields while building the descriptive flexfield into the form, and then you decide which, if any, reference field you want to use. Reference fields provide a way for you to tie the context-sensitivity of descriptive flexfield information you capture to existing conditions in your business data.

If you use a reference field, the value of that field populates its own column. For example, if the reference field on your form is the "Country" field, it populates the "country" column in the table (remember that the reference field is just an ordinary field on the form before you choose to use it as a reference field). However, that reference field value also populates the structure (context) column in the table, since that value specifies which structure the flexfield displays. If you provide a context field in the flexfield pop-up window, in addition to using the reference field, the reference field essentially provides a default value for the context field, and the user can choose a different context value. In this case, the reference field column and the structure column might contain different values. If you use the reference field without a displayed context field, the values in the two columns would be the same. The form also contains a hidden context field that holds the structure choice, regardless of whether you choose to display a context field in the pop-up window.

The field you choose must exist in the same block as the descriptive flexfield. In addition, if the descriptive flexfield appears in several different windows or blocks, the same field must exist in all blocks that contain this descriptive flexfield. You can specify your field using either the field name by itself or using the :block.field notation.

For example, the descriptive flexfield in an application window may be used to capture different information based on which country is specified in a field on that window. In this case, the country field could be used as a reference field.

Typically, you would define different structures of descriptive flexfield segments for each value that the reference field would contain. Though you do 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 use fields that will contain a relatively short, static list of possible values, such as a field that offers only the choices of Yes and No or perhaps a list of countries. You should not use fields that could contain an infinite number of unique values, such as a PO Number field or a date field (unless that date field has a list of a few particular dates, such as quarter end dates, that would never change). Often the business uses of the particular window dictate which fields, if any, are acceptable reference fields.

You can optionally set up your flexfield so that the context field value is always synchronized with the reference field value. You may have instances where, when querying existing records, you want the context field value to match the original reference field value, but with new records, you want the context field value to be derived from the current reference field value.

For example, say the context field is Country Code (US for United States, IN for India, AU for Australia, and so on). This information should be constant for the lifetime of a given record, such as an Expense Report; that is, it remains constant from the original data entry (and saving of the record) to subsequent queries on the record.

In this example, the Country Code value could be captured as a profile option value specific to the user. The desired behavior of which Country Code value is used by the descriptive flexfield may depend on the action at runtime. For example, say an expense report is created by an employee in India, with a Country Code of IN. If payment processing is done in Australia and the Australian (Country Code: AU) Payables Approver queries the Indian employee's expense report, the expected default Country Code context field value is IN; it is not derived from the Australian Payables Approver's profile option value of AU. However, if the Australian Payables Approver enters in his/her own expense report, then the expected default Country Code context field value is AU.

The context field value defaulting behavior is configurable for each descriptive flexfield. How you configure each descriptive flexfield determines whether the old context field value is retained or whether the new context information is accepted.

The actual end-user runtime behavior depends on whether the context field is synchronized with the reference field and whether it is displayed. The following table lists the possible behaviors: