Values and Value Sets

Overview of Implementing Table-Validated Value Sets

Changing the Value Set of an Existing Flexfield Segment

Planning Values and Value Sets

To plan values and value sets:

Choose a format for your values. See: Choosing Value Formats.
Decide whether your segment should have a list of values. See: Decide What Your User Needs.
Choose an appropriate validation type for your segment. See: Choosing a Validation Type for Your Value Set.
Consider using values that group neatly into ranges so that using range-based features (value security, value hierarchies, and so on) will be easier. See: Plan Values to Use Range Features.
Plan both values and descriptions as appropriate.
Plan any value hierarchies, cross-validation rules, value security rules, and so on as appropriate.

Choosing Value Formats

Since a value set is primarily a "container" for your values, you define your value set such that it can control the types of values that are allowed into the value set (whether predefined or non-validated). You can specify the format of your values:

Character
Number
Time
Standard Date
Standard DateTime
Date
DateTime

Warning: Date and DateTime will be obsolete in a future release and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard DateTime.

You can also specify the maximum length your values can be, as well as a minimum and maximum value that can be used with your value set. Choosing the maximum size for your value set depends on what flexfield you plan to use with your value set. Your value set size must be less than or equal to the size of the underlying segment column in the flexfield table. Oracle E-Business Suite does not allow you to assign a value set whose values would not fit in the flexfield table.

You want to specify a maximum size for your values that fits the way your organization works. Generally, if you use values with descriptions, your values tend to be short and have longer descriptions. For example, you might have a value of 02 that has a description of New Orleans Region. If you plan to have Oracle E-Business Suite right justify and zero-fill your values (so a three-character value set value of 7 automatically comes 007), you want your maximum size to be short enough so that your users are not overwhelmed by zeros, but long enough so that your organization has room to add more values later.

Values never change; descriptions can. For example, a department code of 340 cannot change, but its description may change from Sales to Corporate Accounts. Disable values and create new ones as needed.

The following diagram shows how some of these formatting options interact.

Formatting Options

the picture is described in the document text

You have several other options from which to choose. See: Value Formats.

Value set options include the following:

Name
Description
List Type
Security Type

Format options include:

Format Type
Maximum Length
Precision
Numbers Only?
Uppercase Only?
Right-Justify and Zero-Fill Numbers?
Minimum Value
Maximum Value

Validation types include:

Independent
Dependent
None
Table
Special
Pair
Translatable Independent
Translatable Dependent

Related Topics

Relationship Between Independent and Dependent Values

Parent and Child Values and Rollup Groups, Oracle General Ledger User's Guide

Overview of Implementing Table-Validated Value Sets

Changing the Value Set of an Existing Flexfield Segment

Value Formats

The format type you specify in the Format Type field is the format for the segment or parameter value. If you use a validation table for this value set, this format type corresponds to the format type of the value column you specify in the Validation Table Information region, regardless of whether you also specify a hidden ID column.

Because your changes affect all flexfields and report parameters that use the same value set, you cannot change the format type of an existing value set.

All of these format options affect both the values you can enter in the Segment Values windows and the values you can enter in flexfield segments and report parameters.

Format Types

Below are the available Format Types:

Char

Char lets you enter any character values, including letters, numbers, and special characters such as # $ % ( ) . / , & and *. If you choose this format type but enter values that appear to be numbers, such as 100 or 20, you should be aware that these values will still behave as character values. For example, the value 20 will be "larger" than the value 100. If you want such values to behave (and be sorted) more like numeric values, you should check the Numbers Only check box or check the Right-justify and Zero-fill Numbers check box. If you choose this format type but enter values that appear to be dates, such as DD-MON-RR or DD-MON-YYYY, you should be aware that these values will still behave as character values. For example, the value 01-SEP-2007 will be "larger" than the value 01-DEC-2007. If you want such values to behave (and be sorted) like date values, you should use the Standard Date format type.

If you use the Char format type, you can also specify character formatting options. See: Character Formatting Options.

Number

Number lets you ensure that users enter a numeric value. The numeric format allows a radix character ('D' or decimal separator) and a plus or minus sign (although the plus sign is not displayed in the segment). All leading zeros and plus signs are suppressed, and entered data behaves as in a NUMBER field in Oracle Forms or a NUMBER column in the database. Note that this format behaves differently than a "Numbers Only" format, which is actually a character format.

Real numbers are stored with '.' internally and displayed using the current radix separator. Group separators are not used by flexfields. This is also true for Char format, Numbers Only value sets.

Once you have chosen a Number format, you can enter a value in the Precision field. Precision indicates the number of places that should appear after the decimal point in a number value. For example, to display 18.758, you choose a precision of 3. Similarly, to display 1098.5, you choose a precision of 1. To display an integer such as 7, you choose a precision of 0.

Time

Time enforces a time format such as HH24:MI, depending on the maximum size for this value set. The following table lists the supported time formats and value set maximum sizes you can use:

Supported Value Set Maximum Sizes and Time Formats
Maximum Size	Time Format
5	HH24:MI
8	HH24:MI:SS

You can use corresponding default values for segments whose value sets use one of the above sizes. You define these defaults when you define your segments or report parameters.

These values are treated and sorted as time values.

Standard Date

Standard Date enforces the user's preferred date format. Users see the dates in the dates in their preferred format while entering data, querying data and using the List of Values.

For flexfield segments using value sets with this format type, the date values are stored in the application tables in the format YYYY/MM/DD HH24:MI:SS if the columns where the values are stored are of type VARCHAR2. For report parameters using these value sets the concurrent manager will pass dates in this format to the report. Because there is no time component in the Standard Date type value set values, the time component is 00:00:00.

Note: The underlying column size must be at least 20.

Value sets with the "Standard Date" and "Standard DateTime" formats can have validation types of "None", "Table", "Independent", "Dependent", "Special", or "Pair".

You can specify minimum and maximum boundary values for these value sets in the current NLS date format while defining the value set.

Table validated value sets using the "Standard Date" or "Standard DateTime" formats cannot use the ID column. The VALUE column should be a DATE column or a VARCHAR2 column (which should have the date values in the canonical format YYYY/MM/DD HH24:MI:SS). If the existing values in the table are not in the canonical format you should create a view that will do the conversion to the canonical format or to a date column and the value set should be defined on this view.

These values are treated and sorted as date values, so 01-DEC-2007 is "larger" than 01-SEP-2007.

Standard DateTime

Standard DateTime enforces the user's date/time format. Users see the dates in the dates in their preferred format while entering data, querying data and using the List of Values.

Note: The underlying column size must be at least 20.

Value sets with the "Standard Date" and "Standard DateTime" formats can have validation types of "None", "Table", "Independent", "Dependent", "Special", or "Pair".

You can specify minimum and maximum boundary values for these value sets in the current session's date format while defining the value set.

These values are treated and sorted as date-time values, so 01-DEC-2007 00:00:00 is "larger" than 01-SEP-2007 00:00:00.

Date

Important: Date and DateTime value set formats will be obsolete in a future release and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard DateTime.

Date enforces a date format such as DD-MON-RR or DD-MON-YYYY, depending on the maximum size for this value set. The following table lists the supported value set maximum sizes and date formats you can use:

Supported Value Set Maximum Sizes and Date Formats
Maximum Size	Date Format
9	DD-MON-RR
11	DD-MON-YYYY

You can use corresponding default values for segments whose value sets use one of the above sizes. You define these defaults when you define your segments or report parameters.

These values are treated and sorted as date values, so 01-DEC-2007 is "larger" than 01-SEP-2007.

Note: Date value sets use a fixed date format depending on their maximum size regardless of the user's date format.

DateTime

DateTime enforces a date format such as DD-MON-RR HH24:MI, depending on the maximum size for this value set. The following table lists the supported value set maximum sizes and formats you can use for DateTime:

Supported Value Set Maximum Sizes and DateTime Formats
Maximum Size	DateTime Format
15	DD-MON-RR HH24:MI
17	DD-MON-YYYY HH24:MI
18	DD-MON-RR HH24:MI:SS
20	DD-MON-YYYY HH24:MI:SS

You can use corresponding default values for segments whose value sets use one of the above sizes. You define these defaults when you define your segments or report parameters.

These values are treated and sorted as date-time values, so 01-DEC-2007 is "larger" than 01-SEP-2007.

Note: Date value sets use a fixed date format depending on their maximum size regardless of the user's date format.

Value Set Maximum Size

This size represents the longest value you can enter into a segment that uses this value set, as well as the longest Display Size you can specify when you define your flexfield segment or report parameter.

Note: This size is the number of bytes, not characters.

In most cases, this maximum size cannot exceed the size of the segment column in the underlying table for the flexfield that uses this value set. If you set the maximum size longer than that column size, you cannot choose this value set when you define your flexfield segments or report parameters.

If you define your segments or report parameters using a Display Size less than this maximum size, then your pop-up window displays the leftmost characters of the value in the segment. Your user scrolls through the segment to see any remaining characters.

For report parameters, the largest maximum size you can use is 240.

If your Format Type is Standard Date, your maximum size is 11. If your Format Type is Standard DateTime, you maximum size is 20

If you are defining a value set that uses a validation table, your maximum size should reflect the size of the column you specify as your value column. The maximum size must also be equal to or less than the width of the destination segment column. Therefore, after you choose your value column size, you may get a message instructing you to modify the value set maximum size to match your value column width.

However, if you also specify a hidden ID column for your value set, the flexfield determines if the hidden ID value will fit into the underlying column rather than the value column. For example, if you specify your maximum size as 60, which is also the size of your value column, but you also specify a hidden ID column whose size is 15, you could still use that value set for a flexfield whose underlying segment column size is only 20. Such value sets do appear in the value set list of values you see when you define your flexfield segments or report parameters.

Precision

For value sets that contain numeric values (Number format, or Character format with Numbers Only selected), this attribute represents the number of digits after the radix character. Values are stored with exactly this number of digits following the radix character, with zeroes added or rounding applied as needed. If this field is left empty ("NULL precision"), then the radix character may appear anywhere in the value, as long as the other size and value constraints are met.

Character Formatting Options

The following are the available character formatting options:

Numbers Only (0-9)

With the Numbers Only option, you may not enter the characters A-Z, a-z, or special characters such as ! , @, or # , in the segment that uses this value set. You may enter only the values 0-9, minus signs, plus signs, the radix separator (D), and the group separator (G) in any segment or parameter that uses this value set. Note also that your Char format type value set remains Char even without alphabetic characters, and your values will behave and sort as character values.

Important: If you want to restrict users from entering a negative sign for a value set where you do not allow alphabetic characters, you should enter zero (0) as this value set's minimum value. However, you cannot prevent users from entering a value that contains the radix character (D).

If you are defining a value set that uses a validation table, you should set the value in this field to reflect the characteristics of the values in the value column you specify for your validation table.

Note: The Numbers Only option cannot be used in Translatable Independent and Translatable Dependent value sets.

Uppercase Only

Indicate whether any alphabetic characters you enter as values for a segment using this value set should automatically change to uppercase.

Note: The Uppercase Only option cannot be used in Translatable Independent and Translatable Dependent value sets.

Right-justify and Zero-fill Numbers

Indicate whether your flexfield should automatically right-justify and zero-fill numbers when you enter values for this value set. This option affects values that include only the characters 0-9, regardless of whether you select the Numbers Only option. This option has no effect on values that contain alphabetic characters or special characters such as a period or a hyphen.

For example, if you have a five-character value set, and you define a value of 7, your flexfield stores and displays your value as 00007. If you define your flexfield segment to have a display size less than the maximum size and you want to Right-justify and Zero-fill Numbers, your flexfield segment may often display only zeroes (your flexfield segment displays only the number of characters specified by the display size). In these cases, your users need to scroll through the flexfield segment to see a meaningful value, thus slowing data entry or inquiries.

Usually you use this option to ensure that character values that appear to be numbers will be sorted and appear in order as if they were actually number values (for cross-validation rules, value security rules, and reporting, for example). You may also use this option to ensure that numeric-looking values all have the same number of characters so they line up nicely in reports.

If you set Right-Justify and Zero-fill Numbers to Yes, you should ensure that the values in this value set use Right-justify and Zero-fill.

Tip: We recommend that you set Right-justify and Zero-fill Numbers to Yes for value sets you use with the Accounting Flexfield and to No for most other value sets.

If you are defining a value set that uses a validation table, you should set the value in this field to reflect the characteristics of the values in your validation table.

If you set the Right-Justify and Zero-Fill Numbers flag to Yes, the values in your value columns should also be right-justified and zero-filled; that is, there should be an exact match in formatting.

Minimum and Maximum Value Range

The following describes value ranges.

Min Value

Enter the minimum value a user can enter in a segment that uses this value set. Your minimum value may not violate formatting options such as the maximum value size you specify for this value set.

You can use the Minimum Value and Maximum Value fields to define a range of valid values for your value set. Once you specify a range of values, you cannot define a new valid value that falls outside this range. The Minimum Value and Maximum Value fields can therefore allow you to create a value set with a validation type of None (non-validated, where any value is valid) where the user cannot enter a value outside the specified range.

For example, you might create a value set with format type of Number where the user can enter only the values between 0 and 100. Or, you might create a value set with format type of Standard Date where the user can enter only dates for a specific year (a range of 01-JAN-2007 to 31-DEC-2007, for example). Since the minimum and maximum values enforce these limits, you need not define a value set that contains each of these individual numbers or dates.

You can define a range of values for a value set that already contains values. Existing combinations or existing data that use values outside the valid range are treated as if they contain expired segment values.

Your minimum or maximum value can differ depending on your format type. For example, if your format type is Char, then 1000 is less than 110, but if your format type is Number, 110 is less than 1000. In addition, when you use a Char format type for most platforms (ASCII platforms), numeric characters are "less" than alphabetic characters (that is, 9 is less than A), but for some platforms (EBCDIC platforms) numeric characters are "greater" than alphabetic characters (that is, Z is less than 0). This window gives you an error message if you specify a larger minimum value than your maximum value for your platform.

Max Value

Enter the maximum value a user can enter in a segment that uses this value set. Your maximum value may not be longer than the maximum size you specify for this value set.

If you leave this field blank, the maximum value for this value set is automatically the largest value possible for your value set.

Examples of Minimum and Maximum Values

If your value set uses Char format, with Numbers Only and maximum size of 3, then your minimum value is '-99' and your maximum value is '999'.

If your value set uses Number format, with maximum size is 5 with precision of 2, then your minimum value is '-9.99' and your maximum value is ' '99.99' (using the US radix character '.').

Related Topics

Overview of Implementing Table-Validated Value Sets

Decide What Your User Needs

First, you should decide whether your users need a predefined list of values from which to choose, or whether they can enter any value that fits the value set formatting conditions. If you want to provide a list of values, you choose from independent, dependent, translatable independent, translatable dependent, or table value sets. If you do not want a list, use a non-validated (None) value set.

Once you have chosen to provide a list of values for a segment, you choose whether to use independent, dependent, translatable independent, or translatable dependent or table validation. You would only use a dependent set if you want your segment values to depend upon the value chosen in a prior independent segment (a segment that uses an independent value set). You would only use a translatable dependent set if you want your segment values to depend upon the value chosen in a prior translatable independent segment (a segment that uses a translatable independent value set). Whether you use an independent or table set depends on where you intend to get your values. If you already have suitable values in an existing table, you should choose a table set. If you were to use an independent set and you already maintain those values in an application table, you would need to perform double maintenance on your values. For example, if you need to disable an invalid value, you would need to disable it in both the Segment Values window (for your value set) and in your application form that maintains your existing table (for use by your application). If you do not already have a suitable table, you should probably use an independent set and maintain your values using the Segment Values window.

The following table lists each value set type, whether it uses a list of values, and where these values, if any, are stored.

Value Set Type Characteristics
Value Set Type	List of Values	Values Stored
Independent	Yes	FND table
Dependent	Yes	FND table
Table	Yes	Application Table
None	No	No
Special/Pair	Depends on value set	Depends on value set
Translatable Independent	Yes	FND table
Translatable Dependent	Yes	FND table

Related Topics

Using Translatable Independent and Translatable Dependent Value Sets

Qualifiers

Choosing a Validation Type for Your Value Set

There are several validation types that affect the way users enter and use segment or parameter values:

None (not validated at all)
Independent
Dependent
Table
Special (advanced)
Pair (advanced)
Translatable Independent
Translatable Dependent

Important: The Accounting Flexfield only supports Independent, Dependent, and Table validation (table validation cannot have any additional WHERE clauses).

You cannot change the validation type of an existing value set, since your changes affect all flexfields and report parameters that use the same value set.

Examples of Validation Types

the picture is described in the document text

None

You use a None type value set when you want to allow users to enter any value so long as that value meets the value set formatting rules. That is, the value must not exceed the maximum length you define for your value set, and it must meet any format requirements for that value set. For example, if the value set does not allow alphabetic characters, your user could not enter the value ABC, but could enter the value 456 (for a value set with maximum length of three). The values of the segment using this value set are not otherwise validated, and they do not have descriptions.

Because a None value set is not validated, a segment that uses this value set does not provide a list of values for your users. A segment that uses this value set (that is, a non-validated segment) cannot use flexfield value security rules to restrict the values a user can enter.

Independent

An Independent value set provides a predefined list of values for a segment. These values can have an associated description. For example, the value 01 could have a description of "Company 01". The meaning of a value in this value set does not depend on the value of any other segment. Independent values are stored in an Oracle Application Object Library table. You define independent values using an Oracle E-Business Suite window, Segment Values.

Table

A table-validated value set provides a predefined list of values like an independent set, but its values are stored in an application table. You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set. Typically, you use a table-validated set when you have a table whose values are already maintained in an application table (for example, a table of vendor names maintained by a Define Vendors form). Table validation also provides some advanced features such as allowing a segment to depend upon multiple prior segments in the same structure.

Dependent

A dependent value set is similar to an independent value set, except that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the flexfield structure. You can think of a dependent value set as a collection of little value sets, with one little set for each independent value in the corresponding independent value set. You must define your independent value set before you define the dependent value set that depends on it. You define dependent values in the Segment Values windows, and your values are stored in an Oracle Application Object Library table. See: Relationship Between Independent and Dependent Values.

Example of a Dependent Value Set

the picture is described in the document text

Special and Pair Value Sets

Special and pair value sets provide a mechanism to allow a "flexfield-within-a-flexfield". These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal flexfield segments.

Example of special and pair value sets

the picture is described in the document text

Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another flexfield as a value set for a single segment or to provide a range flexfield as a value set for a pair of segments.

Translatable Independent and Translatable Dependent

A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used.

A Translatable Dependent value set is similar to Dependent value set in that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the flexfield structure. However, a translated value can be used.

Flexfield Value Security cannot be used with Translatable Independent or Translatable Dependent value sets.

For format validation, translatable value sets must use the format type Char. The maximum size must be no greater than 150. The Number Only option and the Right-justify and Zero-Fill Numbers option cannot be used with translatable value sets.

Range flexfields cannot use Translatable Independent or Translatable Dependent value sets.

You cannot create hierarchies or rollup groups with Translatable Independent or Translatable Dependent value sets.

Note: The Accounting Flexfield does not support Translatable Independent and Translatable Dependent value sets.

Related Topics

Overview of Implementing Table-Validated Value Sets

Plan Values to Use with Range Features

Use sensible ranges of values by grouping related values together to simplify implementing features such as cross-validation and security rules.

It is a good idea to plan your actual values while keeping cross-validation, security, and reporting ranges ("range features") in mind (also parent or summary values that would fall at one end of a given range, for example). For example, you may want to base security on excluding, say, all values from 1000 to 1999. Keep in mind, though, that if you use the Character format for your value set, your values and ranges are sorted by characters. So, 001 < 099 < 1 < 100 < 1000 <12 < 120 < 1200, which is different from what you expect if these were really numbers (using a Number format value set).

Note: You cannot use ID-validated value sets with range features. ID-validated value sets include table-validated value sets with an ID column specified; translatable independent and translatable dependent value sets; numeric value sets using precision; and date, date/time, and time value sets.

Parent and Child Values and Rollup Groups, Oracle General Ledger User's Guide

Value Set Naming Conventions

If you plan to refer to your value set name in a WHERE clause for a validation table value set, you should use only letters, numbers, and underscores (_) in your value set name. You should not include any spaces, quotes, or other special characters in your value set name. Do not use the string $FLEX$ as part of your value set name. Note that validation tables are case-sensitive for value set names you use in validation table WHERE clauses.

Tip: Make your value set names contain only one case (either upper or lower case) to avoid case-sensitivity problems.

Oracle E-Business Suite includes many predefined value sets. These are primarily value sets for Standard Request Submission parameters. During an upgrade, Oracle E-Business Suite will overwrite your value sets that use the same names as Oracle E-Business Suite value sets. While Oracle E-Business Suite provides a list of reserved value set names before an upgrade so that you can rename your sets to prevent their being overwritten, you should name your value sets carefully to make upgrades easier.

Oracle E-Business Suite reserves certain naming patterns. Oracle E-Business Suite reserves the patterns of either two or three characters immediately followed by either an underscore or hyphen, as in AP_VALUE_SET or PER-Value Set.

Note that Oracle E-Business Suite products do not completely follow these guidelines, so you will still need to check and possibly rename your value sets before upgrades. However, if you name your value sets with names we are unlikely to use, your future upgrades will be simpler. For example, you might want to give your value sets names that begin with a six-character name for your site.

Predefined Value Sets

Many Oracle E-Business Suite reports use predefined value sets that you may also use with your flexfield segments. If your flexfield segment uses a value set associated with a Standard Request Submission report parameter, any changes you make to its value set also affect any reports that use the same value set. Also, your changes to Oracle E-Business Suite value sets may be overwritten by a future upgrade.

Oracle E-Business Suite provides two predefined values sets, FND_STANDARD_DATE and FND_STANDARD_DATETIME that you can choose for your segments. These special values sets ensure that you enter a properly-formatted date, instead of any set of characters, in your flexfield segment. These value sets have a validation type of None, so they accept any date value in the correct format. Date values using this value set will appear in the user's session date display mask. If your flexfield segment or report parameter uses FND_STANDARD_DATE or FND_STANDARD_DATETIME it must have the correct length for the display format to avoid truncation of the dates.

For backwards compatibility, Oracle E-Business Suite provides some predefined value sets, FND_DATE and FND_DATE4 that you can choose for your date segments. These special value sets ensure that you enter a properly-formatted date, instead of any set of characters, in your flexfield segment. FND_DATE provides a date format of DD-MON-RR, and FND_DATE4 provides a date format of DD-MON-YYYY. Both of these value sets have a validation type of None, so they accept any date value in the correct format. If your flexfield segment or report parameter uses FND_DATE or FND_DATE4, it must have a length of 9 or 11 characters (respectively) to avoid truncation of the dates. However, we recommend that you create your own date value sets for any new flexfield segments.

Note: The FND_DATE and FND_DATE4 value sets are for backwards compatibility only. The DATE format type will be obsolete in a future release. Also, your users do not have flexibility with the display format for the values in these value sets.

For backwards compatibility, Oracle E-Business Suite provides another predefined value set, NUMBER15, that you can choose for your numeric segments. This special value set ensures that you enter a positive or negative number, instead of any set of characters, in your flexfield segment. This value set has a validation type of None, so it accepts any positive or negative number value up to fifteen characters long (including the minus sign). If you use this value set, your flexfield strips any leading zeros from the values you enter and ensures that your numbers have only one radix character ('.' in the US format, for example). However, we recommend that you create your own number value sets for any new flexfield segments.

Related Topics

Overview of Implementing Table-Validated Value Sets

Defining Values and Value Sets

Before defining your values and value sets, perform the following prerequisite steps:

Plan your flexfield structures and segments.
Thoroughly plan your values and value sets. See: Planning Values and Value Sets.

To define values and value sets:

Navigate to the Value Sets window.
Define your value set. See: Defining Value Sets.
Define your values. See: Defining Segment Values.

Related Topics

Overview of Implementing Table-Validated Value Sets

Changing the Value Set of an Existing Flexfield Segment

Qualifiers

Relationship Between Independent and Dependent Values

Independent and dependent value sets have a special relationship. While you can have the same dependent values for any of your independent values, the meanings (or descriptions) - as well as any segment qualifier values, enabled/activation information and descriptive flexfield data for that value - of the dependent values depend on which of the independent values you choose in the prior independent segment. For example, you could have value sets with the values (dependent default value of 0) as described in the following table:

Example of a Relationship Between an Independent Value Set and a Dependent Value Set
Independent Value Set (Account Segment) Value	Independent Value Set (Account Segment) Description	Dependent Value Set (Sub-Account Segment) Value	Dependent Value Set (Sub-Account Segment) Description
01	Cash accounts	0	Default Value
01	Cash accounts	1	Bank of California
01	Cash accounts	2	Bank of Denver
01	Cash accounts	3	First Federal Bank
02	Equipment accounts	0	Misc equipment
02	Equipment accounts	1	Computers
02	Equipment accounts	2	Printers
02	Equipment accounts	3	Delivery Vehicles
03	Other asset accounts	0	Default value

You must set up your independent-dependent value sets carefully using the following sequence:

Create your independent value set first
Create your dependent value set, specifying a default value
Define your independent values
Define your dependent values

When you define each of your independent values, Oracle E-Business Suite automatically creates a default dependent value that goes with your independent value. For example, the previous diagram shows a default value of zero (0). If for some reason you create a dependent value set after your independent value set has values, you must manually create a default value in your dependent set for each of your independent values, since each independent value must have a default dependent value. If necessary, create your default dependent values manually using the Segment Values form (you also use this form to create all dependent values other than the default value). You must create at least one dependent value for each independent value, or else your user will be unable to enter segment value combinations in the flexfield. However, we recommend that you carefully follow the above order for creating your value sets so that you never have to create default dependent values manually, since manually creating default dependent values is both tedious and error-prone.

Important: For independent-dependent value sets, the size of a value entered for the independent value set must not exceed 60 characters. Failure to adhere to this limit may result in unexpected behavior.

"Dependent" Values with Table Validation

Flexfields uses a special mechanism to support table-validated segments whose values depend on the value in a prior segment (a different mechanism from that used for independent value sets with dependent value sets). You can use flexfield validation tables with a special WHERE clause (and the $FLEX$ argument) to create value sets where your segments depend on prior segments. You can make your segments depend on more than one segment (cascading dependencies). However, you cannot use parent value/child value features with these value sets, nor can you use this mechanism with the Accounting Flexfield.

Related Topics

WHERE Clauses and Bind Variables for Validation Tables

Overview of Implementing Table-Validated Value Sets

Parent and Child Values and Rollup Groups, Oracle General Ledger User's Guide

Dependent Value Set Information Window

WHERE Clauses and Bind Variables for Validation Tables

Parent and Child Values and Rollup Groups

Only Oracle General Ledger and Oracle Public Sector General Ledger use these features, and only with the Accounting Flexfield. Parent and child value sets have a relationship different from the relationship between independent and dependent values. For information on these features, see the Oracle General Ledger Implementation Guide.

Related Topics

Rollup Groups Window

Overview of Implementing Table-Validated Value Sets

Table-validated value sets let you use your own application tables as value sets for flexfield segments and report parameters instead of the special values tables Oracle E-Business Suite provides. You need not enter each value manually using the Segment Values window. Value sets you base on validation tables can be similar to Independent value sets, where values in your Table type value sets are independent of the values in all other segments. Or, depending on how you define your validation table's WHERE clause, they can depend on one or more previous segments in your flexfield.

In general, you should use a validation table if you want a key or descriptive flexfield segment, or report parameter, to use values that your application already requires or maintains for other application purposes. Using a validation table then lets you avoid maintaining two copies of the same values (one in your application's table and the other in Oracle Application Object Library's tables).

You can use many advanced features with your table-validated value sets. You can use validation tables for flexfield segments or report parameters whose values depend on the value in a prior segment. You use flexfield validation tables with a special WHERE clause (and the $FLEX$ argument) to create value sets where your segments depend on prior segments. You can make your segments depend on more than one segment, creating cascading dependencies. You can also use validation tables with other special arguments to make your segments depend on profile options or field values.

Note: Table-validated value sets with WHERE clauses cannot be used with the Accounting Flexfield.

If you want to make use of key flexfield features such as rollup groups and parent-child relationships, you can store the child values in your validation table, but you should use the Segment Values windows Oracle E-Business Suite provides to add or define the parent values and rollup groups.

Related Topics

Defining Your Validation Table

Creating Grants and Synonyms for Your Table

WHERE Clauses and Bind Variables for Validation Tables

Precedence of Default Values, Shorthand Entry Values, and COPY Values in Key Flexfields

Parent and Child Values and Rollup Groups, Oracle General Ledger User's Guide

Using Validation Tables

Use the Table Validation Information window to define the characteristics of a table you want to use to validate your segment or report parameter.

To implement a validation table:

Create or select a validation table in your database. You can use any existing application table, view, or synonym as a validation table. See: Defining Your Validation Table.
Register your table with Oracle Application Object Library (as a table). You may use a non-registered table for your value set, however. If your table has not been registered, you must then enter all your validation table information in this region without using defaults.
Create the necessary grants and synonyms. See: Creating Grants and Synonyms for Your Table.
Define a value set that uses your validation table. See: Defining Value Sets.
Define your flexfield structure to use that value set for a segment.

You can use the same table for more than one value set, using different SQL WHERE clauses to limit which values are used for flexfield and report parameter validation. For example, if you wish to validate different segments against different rows of the same table, you would use the same table twice but select different rows of the table for each value set by using different SQL WHERE clauses.

Note: The value column and the defined ID column in the table must return a unique row for a given value or ID.

If the ID column is used, then each value in the ID column must be unique. If the ID column is not used then each value in the value column must be unique.

Warning: You should not use any WHERE clause and/or ORDER BY clause at all for a value set you intend to use with the Accounting Flexfield.

In general, you may use a WHERE clause and/or an ORDER BY clause for validation tables you intend to use with key flexfields other than the Accounting Flexfield.

Important: If you need a complex SQL clause to select your values from a table, you should instead first define a view over the table which selects the rows you need, and then define the value set over the view.

See: WHERE Clauses and Bind Variables for Validation Tables for detailed information on using WHERE clauses with special bind variables.

Using hidden ID columns with value sets

If you specify a hidden ID column in addition to your value column, the flexfield saves your hidden ID value, instead of the value from the value column, in the segment column (in your ATTRIBUTEn column or SEGMENTn column) of the underlying flexfield table.

Generally, you use value sets with hidden ID columns only for report parameters. You would not normally use them for most key flexfields. In fact, most key flexfields prevent you from using a value set with a hidden ID column by not displaying those value sets in the list of values you use to assign a value set to a segment.

Important: You should not specify a hidden ID column for value sets you use with your Accounting Flexfield or most other key flexfields.

Important: In defining value sets to be used for Oracle Reports parameters that must contain a number in which precision is entered as part of the value, you must define these value sets with the format type Character with the Numbers Only option selected. Oracle Reports parameters cannot use value sets with format type Number if precision is to be entered as part of the value.

If you specify a hidden ID column in addition to your value column, the report parameter window passes your hidden ID value, instead of the value from the value column, to your report.

Table validated value sets using the "Standard Date" or "Standard DateTime" formats cannot use the ID column.

Using multiple tables in a single value set

For value sets that use multiple tables, you should always include the table aliases with all your column names. You must enter the column name directly, since your list of values cannot retrieve any column names for a "table name" that is not a registered single table. For example, you might enter:

f.column_name

For value sets that use multiple tables, you can and should leave the Table Application field blank, since it is effectively ignored in this case. You enter the table names and aliases you want in the Table Name field. Then, you enter the Value Column and Description Column column names directly, with table aliases, since your list of values cannot retrieve any column names for a "table name" that is not a registered single table.

Displaying additional columns in your list of values

You can design your value set to display several columns in the segment value or report parameter value list of values, and these columns may be in different tables. If all your columns exist in the same table, you simply list the additional columns in the Additional Columns field. If your columns exist in different tables, you must specify more than one table name in the Table Name field. You should always use table names or aliases with your column names for your Additional Columns and WHERE clause.

Finally, you can enter the names of the extra columns you want, with their table aliases, in the Additional Columns field. You can specify column widths to display.

In some cases you may want to use a SQL expression instead of specifying a single column name. For example, you may want to use a DECODE statement instead of a simple column name, such as:

DECODE(FORM.FORM_NAME, 'OEDEOR', 'Enter Orders', 'Not available')

DECODE(FORM.FORM_ID, 1234, 1234, NULL)

You can also use message names as alias names; this functionality allows for ease of translation of column titles. The syntax for using a message name as an alias name is:

E_FLAG "APPL=<Application Short Name>;NAME=<Message Name>"(width)

Defining Your Validation Table

Create a new flexfield validation table, or use an existing application table, that includes the following columns:

A column that holds segment values, type VARCHAR2, DATE or NUMBER
A column that holds descriptions for the segment values, type VARCHAR2, DATE or NUMBER

Your table can also include the following optional columns:

ENABLED_FLAG, type VARCHAR2, length 1, NOT NULL
START_DATE_ACTIVE and END_DATE_ACTIVE, type DATE, NULL ALLOWED

If you use these optional columns, they must be defined with the listed characteristics. When you register your validation table, Oracle Application Object Library checks your table to see if these columns exist. If they do, Oracle Application Object Library uses them as part of the flexfield validation information. If you add the ENABLED_FLAG column to an existing table, you must populate the column (with Y or N) for all rows.

Normally, you should use the values form Oracle Application Object Library provides, Define Segment Values, to contain parent values and rollup group information (together with child values contained in your validation table as described in the previous section).

If you have certain special columns, such as SUMMARY_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, STRUCTURED_HIERARCHY_LEVEL, COMPILED_VALUE_ATTRIBUTES or ENABLED_FLAG, in your registered table, your value set uses those columns automatically once you set up your table as a validation table.

If you do not want your value set to use those columns automatically, you should use an alias with your table name in the Table Name field.

Important: If you need to use SQL functions or very complex WHERE clauses with your table, you should instead first define a view over the table and then use the view.

Related Topics

Precedence of Default Values, Shorthand Entry Values, and COPY Values in Key Flexfields

Parent and Child Values and Rollup Groups, Oracle General Ledger User's Guide

Creating Grants and Synonyms for Your Table

Your validation table resides in your application's ORACLE account. Oracle E-Business Suite requires access to your flexfield validation table, as follows:

Create a synonym for your validation table in the APPS schema (ORACLE account). Your synonym should be the same name as your table name.

Grant SELECT privileges on the table from your application's ORACLE account to the APPS schema.

Ensure that your responsibilities connect to the APPS schema.

Related Topics

Overview of Implementing Table-Validated Value Sets

WHERE Clauses and Bind Variables for Validation Tables

You can use validation tables with WHERE clauses to set up value sets where one segment depends on a prior segment that itself depends on a prior segment ("cascading dependencies").

Validation Tables with WHERE Clauses

the picture is described in the document text

Using bind variables in WHERE/ORDER BY clauses

You may use special bind variables, such as :block.field, :$PROFILES$.Option_name, or :$FLEX$.Value_set_name, in your WHERE/ORDER BY clause. However, you may not use them in the Value Column or Hidden ID Column fields (where you would normally specify a column name), even if you do specify a SQL fragment instead of specifying a single column name. You may use bind variables in the Description Column and Additional Columns fields.

Important: If you are using flexfields server-side validation, you cannot use form field references (:block.field). You must either remove your field references or turn off flexfields server-side validation using the profile option Flexfields:Validate on Server.

Important: You may not use a DISTINCT clause in any of the column fields or in your WHERE/ORDER BY clause (you should use a view with a GROUP BY clause instead of your actual table).

If you are using a validation table with special arguments such as :$FLEX$.Value_Set_Name for your value set, you should specify No Security in the Security Type field, since any security rules you have for your value set would ignore the values of these special arguments, and your rules could have effects other than what you intend.

Related Topics

Values and Value Sets

Overview of Implementing Table-Validated Value Sets

Flexfields:Validate on Server

Bind Variables

You can put special arguments (bind variables) in your WHERE clause that allow you to base your values on other values. These bind variables include :block.field, :$PROFILES$.Option_name, or :$FLEX$.Value_set_name. You may not use bind variables in the Value Column or Hidden ID Column fields (where you would normally specify a column name). You may use bind variables in the Description Column and Additional Columns fields.

Note that a bind variable, by default, is required; that is, it must have a value for the statement, expression, or user exit which uses it to have meaning. A bind variable can be made optional by using the :NULL suffix; so that if the bind variable is NULL, the segment/parameter using it will be disabled, and its required property (if enabled) will be ignored. The :NULL suffix is discussed at the end of this section.

:$FLEX$.Value_ Set_Name

Value_Set_Name is the name of either the value set for a prior segment, or the segment name of a prior segment in the same flexfield or parameter window that you want your validation table-based values to depend on. When you define your flexfield structure or report parameter window, you define the segment or parameter that uses value set Value_Set_Name to have a lower sequence number than the segment that uses your validation table-based value set. The $FLEX$ mechanism uses the "closest" prior segment with either a matching value set name or segment name (it looks for the value set name first, and uses the segment name second if there are no matching value set names).

Value_Set_Name is case-sensitive, so you must ensure that the name you specify here exactly matches the value set name you define in the Define Value Set form. Note that you can only use letters, numbers, and underscores (_) in your value set names if you want to use them with a :$FLEX$.Value_Set_Name clause. You cannot use quotes, spaces, or other special characters in these value set names, so you should be careful to define your value sets with names that do not contain spaces, quotes, or other special characters.

You can specify more than one :$FLEX$.Value_Set_Name in a single WHERE clause, thereby creating a segment whose list of possible values depends upon more than one previous segment.

When you specify :$FLEX$.Value_Set_Name, your flexfield segment or report parameter defaults to always use the hidden ID column (of the previous value set) to compare with your WHERE clause. The end user would never see the hidden ID value, however. If you do not specify a hidden ID column, your segment defaults to use the value in the value column instead.

When you specify :$FLEX$.Value_Set_Name, you can also explicitly choose which column for which you want :$FLEX$.Value_Set_Name to return a value. You do this by specifying :$FLEX$.Value_Set_Name.OUTPUT, where OUTPUT can be ID, VALUE, or MEANING (to return the value of the description column).

When you specify your validation table value sets, you can also use an INTO clause in the Additional Columns field (after your entire list of columns and aliases) to put the value into a variable you use with :$FLEX$.segment_name.OUTPUT, where OUTPUT is a name you choose. You can then retrieve that value using :$FLEX$.segment_name.OUTPUT (where OUTPUT is the same name) from another segment's value set WHERE clause. You cannot use OUTPUT to put a value directly into a field, but a value that a flexfield segment retrieves may be put into a hidden form field that the segment corresponds to once the popup window closes. If you do not specify an INTO clause in your Additional Columns field, your value is not placed anywhere other than being displayed in the list of values (it goes INTO NULL).

Important: If you are using flexfields server-side validation, you cannot use the INTO clause for your value set. You must either remove your INTO clauses or turn off flexfields server-side validation using the profile option Flexfields:Validate on Server.

:block.field

:block.field is the SQL*Forms/Oracle Forms name of a field on the form where your descriptive flexfield appears. You can use this argument to make your value set context-sensitive to a field on a form. While this is somewhat similar to using a reference field with a descriptive flexfield, using a reference field with a descriptive flexfield provides a choice between different structures of context-sensitive segments (and indirectly, their value sets). Using this :block.field argument, however, gives you the same segments that would normally appear, but changes the contents of the value set attached to the segment depending on what appears in your :block.field. In some cases, you may wish to use a :block.field value set instead of a descriptive flexfield reference field with many different context-sensitive structures.

Note that if you use this argument, you must have the same :block.field on every form where a value set based on this validation table could be used. For example, if the same flexfield appears on seven forms, then all seven forms must have this block.field. Similarly, if you share your value set among more than one flexfield, then all forms that use any of those flexfields must have this block.field. Though it is possible to use this argument for a key flexfield segment or report parameter, the same restriction applies; that is, you must have the same block.field wherever the value set can be used.

Warning: The :block.field mechanism is present for backward compatibility only. Value sets that use this mechanism will not be compatible with a future release of Oracle E-Business Suite. If you are using flexfields server-side validation, you cannot use form field references (:block.field). You must either remove your field references or turn off flexfields server-side validation using the profile option Flexfields:Validate on Server.

:$PROFILES$.profile_option_name

Profile_option_name is the internal option name of a user profile option such as CONC_COPIES (for Concurrent:Report Copies) or GL_SET_OF_BKS_ID. For example, you could define your WHERE clause as:

WHERE SET_OF_BOOKS_ID = 
:$PROFILES$.GL_SET_OF_BKS_ID

:NULL.suffix

Use the :NULL suffix to make your bind variable optional, that is, allow null values. Instead of :block.field, :$PROFILES$.Option_name, or :$FLEX$.Value_set_name, you would use :block.field:NULL, $PROFILES$.Option_name:NULL, or :$Flex$.Value_set_name:NULL, respectively. For example, if your value set name is Car_Maker_Name_Value_Set, you would use :$FLEX$.Car_Maker_Name_Value_Set:NULL.

Special Treatment for WHERE Clauses

Behind the scenes, the flexfield adds an AND... clause to the WHERE clause you define for your table validated value set. If your WHERE clause contains an OR, then the appended AND clause might not apply to your whole WHERE clause (without the parentheses), and might not produce the correct results. So, flexfields implicitly put parentheses around your WHERE clause.

Related Topics

Flexfields:Validate on Server

Overview of Implementing Table-Validated Value Sets

Creating Grants and Synonyms for Your Table

Precedence of Default Values, Shorthand Entry Values, and COPY Values in Key Flexfields

Example of $FLEX$ Syntax

Here is an example of using :$FLEX$.Value_Set_Name to set up value sets where one segment depends on a prior segment that itself depends on a prior segment ("cascading dependencies"). Assume you have a three-segment flexfield where the first segment is car manufacturer, the second segment is car model, and the third segment is car color. You could limit your third segment's values to only include car colors that are available for the car specified in the first two segments. Your three value sets might be defined as follows:

Segment Name Manufacturer  
Value Set Name Car_Maker_Name_Value_Set  
Validation Table CAR_MAKERS  
Value Column MANUFACTURER_NAME  
Description Column MANUFACTURER_DESCRIPTION  
Hidden ID Column MANUFACTURER_ID  
SQL Where Clause (none)

Segment Name Model  
Value Set Name Car_Model_Name_Value_Set  
Validation Table CAR_MODELS  
Value Column MODEL_NAME  
Description Column MODEL_DESCRIPTION  
Hidden ID Column MODEL_ID  
SQL Where Clause WHERE MANUFACTURER_ID = 
:$FLEX$.Car_Maker_Name_Value_Set

Segment Name Color  
Value Set Name Car_Color_Name_Value_Set  
Validation Table CAR_COLORS  
Value Column COLOR_NAME  
Description Column COLOR_DESCRIPTION  
Hidden ID Column COLOR_ID  
SQL Where Clause WHERE MANUFACTURER_ID = 
:$FLEX$.Car_Maker_Name_Value_Set AND 
MODEL_ID = :$FLEX$.Car_Model_Name_Value_Set

In this example, MANUFACTURER_ID is the hidden ID column and MANUFACTURER_NAME is the value column of the Car_Maker_Name_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Car_Maker_Name_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example.

WHERE Clauses and Bind Variables for Validation Tables

Precedence of Default Values, Shorthand Entry Values, and COPY Values in Key Flexfields

Using Translatable Independent and Translatable Dependent Value Sets

Translatable Independent and Translatable Dependent value sets are similar to Independent and Dependent value sets except that translated values can be displayed to the user. Translatable Independent and Translatable Dependent value sets allow you to use hidden values and displayed (translated) values in your value sets. In this way your users can see a value in their preferred languages, yet the values will be validated against a hidden value that is not translated.

Implementation

Define Your Translatable Value Set

Define your Translatable Independent or Translatable Dependent value set in the Value Sets form. Choose Translatable Independent or Translatable Dependent for your Validation Type.

Translatable Dependent value sets behave like Dependent value sets except that they must be dependent on a Translatable Independent value set. A Translatable Independent value set can have only Translatable Dependent value sets dependent on it.

Your value set must use the Char format type. The maximum size for any translatable set is 150 characters. You can specify your values to be Uppercase only. The maximum size applies to your translated values as well as the hidden values.

The following features are disabled for translatable value sets: Security, Numbers Only, Right-justify and Zero-Fill Numbers.

Important: The Accounting Flexfield does not support Translatable Independent or Translatable Dependent Value Sets.

Define Your Values

Navigate to the Segment Values form to define your values and translated values.

In the Values, Effective tabbed region, the Value column contains the "hidden" untranslated value.

The Translated Value field contains the current translated value. The hidden value defaults in the Translated Value field if no other value is defined. The Translated Value field is enabled for Translatable Independent and Translatable Dependent value sets only.

You can update the translated value for the current session language in the Translated Value field. To update the translated value for a language other than the current session language, use the Translation icon in the Toolbar.

Related Topics

Dependent Value Set Information Window

Limitations on Translatable Value Sets

Flexfield Value Security cannot be used with Translatable Independent or Translatable Dependent value sets.

Range flexfields cannot use Translatable Independent or Translatable Dependent value sets.

You cannot create hierarchies or rollup groups with Translatable Independent or Translatable Dependent value sets.

Note: The Accounting Flexfield does not support Translatable Independent and Translatable Dependent value sets.

Converting Independent/Dependent Value Sets to Translatable Independent/Dependent Value Sets

You can convert an Independent value set to a Translatable Independent value set, or a Dependent value set to a Translatable Dependent value set. These are the only types of conversions allowed. All limitations for translatable value sets apply to your updated value sets.

You convert an Independent/Dependent value set to a Translatable Independent/Dependent value set using the affupg1.sql script. Your new value set will have the validation type Translatable Independent or Translatable Dependent. This is the only change made, and values are not affected.

The difference between the old value set and the new value set can be seen in the Segment Values form. The Translated Value column will be enabled for the new, translatable value set.

To run affupg1.sql, perform the following at the command line:

$ cd $FND_TOP/sql
$ sqlplus <APPS username> @afffupg1.sql

Enter the password when prompted.

Choose the appropriate menu option to change your value set.

After you have created your new translatable value set, you can use the Segment Values form to enter translated values for the value set.

Using Special and Pair Value Sets

Use the Special Validation Routines window to define special validation for a Special value set. You also use this window to define validation routines for a Pair value set.

Warning: You should never change or delete a predefined value set that Oracle E-Business Suite supplies. Such changes may unpredictably affect the behavior of your application features such as reporting.

You can use this region to define a value set that lets your users enter an entire key flexfield combination within a single report parameter. For example, you may want to pass concatenated Accounting Flexfield segments as a parameter to a report. With this type of value set, a user can enter the report parameter and then see the "normal" behavior of a key flexfield, such as the key flexfield window and segment validation associated with that key flexfield. You use Oracle Application Object Library flexfield routines for these special value sets.

Warning: Special/Pair value sets are user-exit value sets (that is, they can be validated by the C engine only). They cannot be validated by PL/SQL APIs. Concurrent programs using Special/Pair value sets may only be run from Forms-based applications.

You should take special care to avoid a situation where you have a value set that contains a flexfield which in turn contains a flexfield (as a value set of one of its segments). There are two situations where this could cause a problem. The first situation (recursion) is where a flexfield calls itself as one of its segments, leading to an infinite chain of pop-up windows. Such a loop may also be indirect. The second potential problem may lead to data truncation and data corruption problems: since a flexfield is often passed as its concatenated flexfield values, the length of these concatenated flexfields can quickly exceed the maximum size of the value set and the underlying segment column in the flexfield table. This is less likely to cause a problem for key flexfields than for descriptive flexfields or range flexfields, because key flexfields are usually passed as a single code combination ID number instead of as concatenated segment values and therefore take less space. Though the Value Sets windows and the Flexfield Segments windows do not prevent you from defining flexfield loops or multiple flexfields within flexfields, you can cause serious truncation problems and possible data corruption problems in your application by allowing this to occur.

Warning: Plan and define your value sets carefully to avoid value sets within value sets as described above.

See: Special Validation Value Sets for information on using these validation types. This section contains information on the various types of events and flexfield routine arguments and syntax you use with special validation. It also contains a worked example of using special validation for the Accounting Flexfield.

Related Topics

Key Flexfield Segments

Descriptive Flexfield Segments

Special Validation Routines Window

Defaulting Flexfield Values

This section describes the various methods of defaulting flexfield values with their respective precedence.

Note: If a key flexfield segment value is optional (that is, the Required check box for the segment is unchecked), then the default value will populate the segment only if the user first opens the flexfield window. This behavior is unlike that for required flexfield segments, where the default value will be entered regardless of whether the user opens the flexfield window. Note that for descriptive flexfield segments the flexfield segment value can be optional and the default value will populate the segment without the user opening the flexfield window.

Precedence of Default Values, Shorthand Entry Values, and COPY Values in Key Flexfields

There are four ways you can put a value into a key flexfield segment (in order of precedence, where the first overrides the second, which overrides the third, which in turn overrides the fourth):

Enter a value manually into the segment once the flexfield window has popped open.
Insert a value using a shorthand flexfield entry alias
Copy a value into the segment from a form field using the COPY parameter to POPID (Implementing Key Flexfields)
Define a default value for the segment using the Key Flexfield Segments form

The value you copy using the COPY parameter in POPID overrides any default value you set for your segment(s) using the Key Flexfield Segments form. COPY does not copy a NULL value over an existing (default) value. However, if the value you copy is not a valid value for that segment, it gives the appearance of overriding a default value with a NULL value: the invalid value overrides the default value, but the flexfield then erases the copied value because it is invalid. You should ensure that the field you copy from contains valid values. However, shorthand flexfield entry values override COPY values.

If your key or descriptive flexfield has required segments (where a value set requires values and, for a key flexfield, the REQUIRED parameter in POPID is set to Yes), the flexfield uses your default values in certain cases. If you try to save a record without ever entering the flexfield pop-up window, then the flexfield (in the VALID or VALDESC routine) attempts to fill in all the required segments with your default values. If you have not specified valid default values for all your required segments, the flexfield generates an error message and requires your user to enter any missing values before saving the row. The default values never override a value your user enters manually.

Note: If you copy a record with a descriptive flexfield, the flexfield information may not be copied along with it, depending on the form or program used. For example, Oracle Purchasing does not copy descriptive flexfields from a requisition to a purchase order during AutoCreate. That is, if there's a required descriptive flexfield on a requisition, Purchasing does not prompt you to enter the flexfield or default a value in the flexfield when you autocreate the purchase order.

Changing the Value Set of an Existing Flexfield Segment

In general, once you have set up and begun to use a flexfield, you should never change anything about its structure or its value sets (other than defining, enabling, and disabling values, shorthand aliases, and cross-validation and security rules). In particular, once you have any rules or data, you should avoid changing the number or arrangement of your segments, and you should avoid changing the value set that a segment points to. Even changing cross-validation rules or flexfield security rules can cause inconsistencies with existing data.

Warning: Changing your flexfield definition once you have used it to acquire data can cause serious inconsistencies with existing data.

This section does not include all possible ways you could change your value sets, nor does it contain complete information on all the data changes you might need to do if you were to make such changes. Since flexfields data is used throughout the Oracle E-Business Suite, you should carefully consider what forms, tables, and entities such changes might affect. Because of the risk of damaging the integrity of your existing data, you should never change Oracle E-Business Suite data using SQL*Plus.

In general, when you change your segment to use a different value set than it used before, you need to be careful not to invalidate your existing flexfield data. Before you make such a change you should back up all of your existing data, including Oracle Application Object Library data, before attempting any value set changes.

Oracle E-Business Suite prevents you from inadvertently invalidating your flexfield value set data by preventing you from changing the validation type of an existing value set. However, sometimes your business needs change unforeseeably, and you may need to change the validation type of your value set by defining a new value set and attaching it to your flexfield segment in place of your old value set. Whether you can change your value set depends on your value set's current type and the type you want to change to. See the following lists to determine if you can make such changes to your flexfield.

Oracle E-Business Suite also prevents you from inadvertently invalidating your flexfield value set data by preventing you from deleting an existing value set under some conditions. If you define and save a value set and then immediately re-query it, you can delete it. However, once you use your value set in any of the following ways, you cannot delete your value set:

assign it to a key or descriptive flexfield segment
assign it to report parameter
assign one or more values to it (even if it is not assigned to a segment)
assign a security rule to it (through the segment to which your value set is attached)

If you must change a value set after it has been attached to a flexfield segment(s) or a concurrent program parameter(s), you must first detach it from the segment(s) or parameter(s). You can use the Usages button in the Value Set window to find out which segments/parameters use the value set.

Changing to a Non-validating ("None") Value Set

When you replace an old value set with a new non-validating ("None" type) value set, these types of changes do not cause a problem with existing flexfield data so long as the format conditions are not violated (character, number, date, numbers only, uppercase only, and so on). Note that the values in the new value set do not have descriptions (meanings) at all, and that any value is now valid:

Independent to None (do not make this change if you have an associated dependent value set or if you need segment qualifier information for those values)
Table to None
Dependent to None

You may need to convert any existing application data that uses value descriptions, since you will no longer have descriptions or segment qualifiers for your segment values.

Changing from a None Value Set to Independent or Table Value Sets

When you replace an old value set with a new value set, you can make these types of changes as long as you ensure that your new value set contains every single value that you ever used for that segment and that is now in the combinations table as parts of your code combinations. If you are missing any values that had been in the original value set, your users will get error messages upon querying up any old records whose values are now missing.

None to Independent
None to Table

Changing Between Independent and Table Value Sets

You can make these types of changes as long as you ensure that the new value set contains every single value that the old value set contained. If you are missing any values that had been in the original value set, your users will get error messages upon querying up old code combinations whose values are now missing.

Independent to Table
Table to Independent

Changes You Should Never Make

You should never make these types of changes (old value set to new value set) because you will corrupt your existing key flexfield combinations data:

Independent to Dependent
Dependent to Independent
None to Dependent
Dependent to Table
Table to Dependent
Translatable Independent to Translatable Dependent
Translatable Dependent to Translatable Independent
None to Translatable Dependent
Translatable Dependent to Table
Table to Translatable Dependent

Changing the Maximum Size of Your Value Set

Oracle E-Business Suite prevents you from invalidating your existing flexfields data by preventing you from decreasing the maximum size of an existing value set. You should never attach a new value set to your segment where the maximum size of the new value set is smaller than the maximum size of the old value set. You will cause data corruption because your existing segment values will be truncated.

In general, increasing the maximum size of an existing value set (or replacing your value set with a bigger one instead) does not cause any problem with your existing flexfields data so long as your new maximum size is still small enough to fit in the underlying flexfield table's segment columns. However, you should never change to a value set with a larger (or smaller) maximum size if your value set is Right-justify Zero-fill, since 001 is not the same as 0000001, and all of your existing values would become invalid. Oracle E-Business Suite products prevent you from invalidating your existing flexfields data by preventing you from changing the maximum size of an existing value set at all if the value set is Right-justify Zero-fill.

Value Set Windows

The value sets you define using these windows appear in lists of values you see when you define flexfield segments using the Key Flexfield Segments window or the Descriptive Flexfield Segments window.

If you are defining reports that your users run from the Submit Requests window, use this window to define value sets for your report arguments. The value sets you define using this window also appear when you define report parameters using the Concurrent Programs window.

Overview of Value Set Windows

You can share value sets among segments in different flexfields, segments in different structures of the same flexfield, and even segments within the same flexfield structure. You can share value sets across key and descriptive flexfields. You can also share value sets with parameters for your concurrent programs that use the Standard Request Submission feature. Many Oracle E-Business Suite reports use predefined value sets that you may also use with your flexfield segments. However, any changes you make to a value set also affect all requests and segments that use the same value set.

Use the Usages button to view which flexfield segment or concurrent program parameter uses a particular value set.

This window prevents you from changing the validation type or format type of an existing value set because your changes affect other flexfields that use the same value set. In addition, other changes may affect the values in your value set in ways other than you expect. You cannot delete a value set that a flexfield or parameter currently uses.

If you make any changes to your value set after you have used your flexfield or concurrent program that uses this value set, you must either change responsibilities or exit to the operating system and log back in before you can see your changes take effect.

Related Topics

Dependent Value Set Information Window

Special Validation Routines Window

Defining Value Sets

Value Sets Window

the picture is described in the document text

To define a value set:

Navigate to the Value Sets window.
Enter a unique name for this value set. See: Value Set Naming Conventions.
If you are modifying an existing value set, you can, with the proper privileges, view where the value set is used. See: Value Set Usages.
Specify the List Type for your value set.

Choose List of Values if your value set should not provide the LongList feature in Oracle Forms applications. A user will not see a poplist in Oracle Self-Service applications.

Choose Long List of Values if your value set should provide the LongList feature in Oracle Forms Applications. The LongList feature requires a user to enter a partial segment value before the list of values retrieves all available values. You may not enable LongList for a value set that has a validation type of None. A user will not see a poplist in Oracle Self-Service applications.

Choose Poplist if your value set should not provide the LongList feature in Oracle Forms applications, but should provide a poplist in Oracle Self-Service applications.

Here are guidelines for the List Type field:
- Poplist - fewer than 10 values expected
- List of Values - between 10 and 200 values expected
- Long List of Values - more than 200 values expected
Specify the Security Type you plan to use with any segments that use this value set. Security does not apply to value sets of validation type None, Special, or Pair. See: Defining Security Rules.

Note: Flexfield value security is not available for Translatable Independent and Translatable Dependent value sets.

The possible security types are:
- No Security - All security is disabled for this value set.
- Hierarchical Security - Hierarchical security is enabled. With hierarchical security, the features of value security and value hierarchies are combined. With this feature any security rule that applies to a parent value also applies to its child values.
  
  Warning: Within a hierarchical tree of values, a value is subject to a security rule if any parent above it is subject to that security rule.
- Non-Hierarchical Security - Security is enabled, but the rules of hierarchical security do not apply. That is, a security rule that applies to a parent value does not "cascade down" to its child values.
Enter the type of format you want to use for your segment values. Valid choices include: Char, Date, DateTime, Number, Standard Date, Standard DateTime, and Time.

Note: Translatable Independent and Translatable Dependent value sets must have the Char format.
Enter formatting information appropriate to your format type, including information such as whether your values should include numbers only and whether they must fall within a certain range.

Note: The maximum size for Translatable Independent and Translatable Dependent value set values is 150. You cannot use the Numbers Only feature or the Right-Justify and Zero-fill feature with translatable value sets.
Select your validation type: Independent, Dependent, Table, None (non-validated), Special, Pair, Translatable Independent, or Translatable Dependent. See: Choosing a Validation Type for Your Value Set.
If you are creating a Dependent, Translatable Dependent, Table, Special or Pair value set, choose the Edit Information button to open the appropriate window. Enter any further information required for your validation type. See: Dependent Value Set Information Window, Validation Table Information Window, Special Validation Routines Window.
Save your changes.

Related Topics

Overview of Implementing Table-Validated Value Sets

Changing the Value Set of an Existing Flexfield Segment

Dependent Value Set Information Window

Dependent Value Set Information Window

the picture is described in the document text

Ensure the following has been done before you define your dependent value set information.

Define your independent value set. You should not define individual independent values for the corresponding independent value set before defining your dependent value set. See: Defining Value Sets.
Define your dependent value set name and formatting options. See: Defining Value Sets.

Note: This window is also used to enter information for Translatable Dependent value sets. Translatable Dependent value sets must be dependent on Translatable Independent value sets. Translatable Independent value sets can have only Translatable Dependent value sets dependent on them.

To define dependent value set information:

Enter the name of an independent value set on which this dependent value set depends.

You can only enter the name of a value set you have already defined. You must save the value set definition of your independent value set before you can select it in this field. An independent value set may have more than one dependent value set depending upon it, but a dependent set cannot depend on another dependent set.

The Segment Values window automatically creates your dependent default values at the time you create your independent values. To ensure that the Segment Values window creates a dependent default value for each of your independent values, you should create the values in your independent value set only after you create all of the dependent value sets that depend on that independent set. If you create a new dependent set for an independent set that already contains values, you must manually enter the dependent default value for each existing independent value using the Segment Values window. See: Segment Values Window.

Tip: First define all of the independent value sets your application needs, then define all of your dependent value sets. Create all of your value sets before you create any of your values.
Enter a default value for your dependent value set.

This value is the default for any segments that use this dependent value set. Usually, you enter zero. You must make sure that the value you enter here fits the value set information you enter. For example, if this dependent value set does not allow alphabetic characters, your default value may not contain any alphabetic characters.

All the values in the independent set must have at least one dependent value. So, whenever a user creates a new value in the independent value set (using the Segment Values form), it must have at least one dependent value. The Segment Values window automatically creates the required dependent value by using the default value you enter here. See: Segment Values Window.

For example, suppose you have an independent value set called "Account" with a dependent value set called "Sub-Account." You may wish to create a new independent value, 99, for "Account" with description "Receivables" without creating any associated sub-account values. Since your flexfield requires a dependent value of some sort to go with the independent value, it uses the default value you enter here, such as 00 with description "No Sub-Account."
Enter a description for your default dependent value. The Segment Values window creates this description with the dependent default value it creates whenever you create a new independent value. For example, suppose you have an independent value set called "Account" with a dependent value set called "Sub-Account." You may wish the "Sub-Account" default value 00 to have the description "No Sub-Account." See: Segment Values Window.

Related Topics

Relationship Between Independent and Dependent Values

Validation Table Information Window

Validation Table Information Window

the picture is described in the document text

Ensure the following prerequisites have been completed:

Create a database table or view that holds valid values and value descriptions in CHAR, VARCHAR2, NUMBER, or DATE type columns.
Use the Register Tables window to register your table with Oracle Application Object Library. This step is recommended but not required.
Create a synonym for your validation table in any application ORACLE account that will access a flexfield or report that uses a value set based upon your validation table.
Grant SELECT privileges on the table from your application's ORACLE account to any application ORACLE accounts that will use a value set based upon the table.
Define your value set name and formatting options. See: Defining Value Sets.

To define validation table information:

Enter the name of the application with which your validation table is registered. Application name and table name uniquely identify your table.

If you plan to display columns from more than one table in your list of values, you should leave this field blank, since it is effectively ignored in this case.
Enter the name of an application table, view or synonym you want to use as a validation table. If your table is not registered with Oracle E-Business Suite, you should type in the entire name of the table you wish to use.

You can define your value set to display several columns, and these columns may be in different tables. If your columns exist in different tables, you must specify more than one table name, separated by commas, in this field. You may use table aliases if desired. For example, you might enter the following information in this field (using two tables):
```
fnd_form f, fnd_application a 
```
Then, in the Value Column, Description Column, Hidden ID Column, WHERE / ORDER BY, and Additional Columns fields, you would use the corresponding table aliases (for a WHERE clause):
```
where f.application_id = a.application_id 
```
Enter the name of the column in your validation table that contains values you want to use to validate a value a user enters for a flexfield segment or a report parameter.

Your selection of available columns depends on the Format Type you specify, and doesn't necessarily match your Format Type. For example, if you specify a Format Type of Standard Date, you select from those columns that have been registered as Date or Char type columns. Similarly, if you specify a Format Type of Number, you select from only those columns that have been registered as Number or Char type columns. If you specify a format type of Character, however, you see only columns of type Char. The format type you specify in the Format Type field is the format for the segment or parameter value.

You may use a SQL expression in place of a column name, but you may not use any special bind variables.

Note: If possible, avoid using a SQL expression in place of a column name because support for SQL expressions will be obsolete in a future release.
Enter the name of the column in your validation table that contains descriptions for the values in the Value Column. If you leave this field blank, your value set automatically uses the value column as the description column (but does not display it twice).

Your flexfield or report parameter window displays a meaning from this column when you enter the corresponding value for a flexfield segment or report parameter.
Enter the name of the column in your validation table that contains values you want to use to validate a value a user enters for a flexfield segment or a report parameter, but that you do not want to display for the user.

If you specify a hidden ID column in addition to your value column, the flexfield saves your hidden ID value, instead of the value from the value column, in the segment column (in your ATTRIBUTEnn column or SEGMENTnn column) of the underlying flexfield table.

Important: Do not specify a hidden ID column for value sets you use with your Accounting Flexfield or most other key flexfields.

If you specify a hidden ID column in addition to your value column, the report parameter window passes your hidden ID value, instead of the value from the value column, to your report.

Important: In defining value sets to be used for Oracle Reports parameters that must contain a number in which precision is entered as part of the value, you must define these value sets with the format type Character with the Numbers Only option selected. Oracle Reports parameters cannot use value sets with format type Number if precision is to be entered as part of the value.
Enter a SQL WHERE clause or an ORDER BY clause, or both.
Enter any additional columns you want to display for a segment that uses this value set. These are columns other than the columns you specify for Value Column, Description Column, or Hidden ID Column.
Indicate whether to allow parent values to be stored in the Oracle Application Object Library FND_FLEX_VALUES table and displayed in the list for a segment that uses this value set.
Select the Test button to validate the query that is executed to provide the list of values in your value set. The Test button feature constructs the actual LOV query from the details provided in Validation Table Information window, such as table name, column fields, and WHERE/ORDER BY clause. It then validates the SQL syntax and semantics of the definition and reports any issues with your current value set definition.

Column Type Fields

The three Type fields automatically display the types of the columns you select. You should never change the displayed column types.

If you specify a SQL expression (or a column in a non-registered table) in a Column field instead of a registered single column name, you must specify the type of value (character, number, or date) you expect your expression to return. You must specify the type because this window cannot retrieve this information for a "column name" that is not a registered single column.

Column Size Fields

The three Size fields automatically display the sizes of the columns you select.

If you do not specify a hidden ID column, Oracle E-Business Suite uses the value set maximum size to determine if a value can fit in the underlying flexfield segment column. The maximum size for your value set changes automatically to the column size you specify in the Size field for the Value column. If the value cannot fit, you cannot use your value set when you define a flexfield segment.

If you use a hidden ID column, the size you specify for the hidden ID column becomes the "effective" maximum size for this value set for a flexfield, since Oracle E-Business Suite uses the size of the hidden ID column to determine if a value can fit in the underlying flexfield segment column. If the value cannot fit, you cannot use your value set when you define a flexfield segment.

Generally, you should avoid changing the displayed column size. However, in some cases you may want to change it if you want to use this value set for a flexfield whose underlying column size is less than the actual size of your value (or hidden ID) column in the validation table. For example, if you are using a lookup code column of a lookup table (List of Values), and you know that all of your lookup codes are two characters long or less, you may want to specify 2, even though the column in the lookups table can actually contain 30 characters. You can then use this value set for a flexfield whose underlying segment column size is between 2 and 30.

You may only change the displayed size for a column if you know that the maximum size of the values in that column will always be equal to or shorter than the length you specify in this field. You should not attempt to "trick" Oracle E-Business Suite by specifying a size that is smaller than your actual potential value size, since you may cause data truncation errors, "value not defined" errors, or other errors.

If you specify a SQL expression (or a column in a non-registered table) in a Column field instead of specifying a registered single column name, you must specify the length of the value (size) you expect your expression to return. You must specify the size because this window cannot retrieve this information automatically for a "column name" that is not a registered single column.

WHERE / ORDER BY Field

Use a SQL WHERE clause to limit the set of valid values to a subset of the values in the table. For example, if you have a table that contains values and meanings for all of your employees but you only want to validate against entries for employees located in California, you can enter a SQL WHERE clause that limits valid values to those rows WHERE LOCATION = 'CALIFORNIA'. You may want to choose your value set name to reflect the limitation, such as "California Employees" for this example.

Use an ORDER BY clause to ensure that your values appear in a non-standard order in your list of values on a segment that uses your value set. The "standard" order depends on the format type for your value set. For example, if you have a table containing the days of the week, you might want the list of values to display them in the chronological order "Monday, Tuesday, Wednesday, ..." instead of in the alphabetical order "Friday, Monday, Saturday, ..." that would be used for a Character format type value set. To display them in chronological order, you might have a second column in your table (which you might also use as the hidden value column) that identifies each day by a number. So, if you call that column of numbers DAY_CODE, your ORDER BY clause would be ORDER BY DAY_CODE.

Warning: You should not use a WHERE clause and/or ORDER BY clause at all for a value set you intend to use with the Accounting Flexfield. In general, you may use a WHERE clause and/or an ORDER BY clause for validation tables you intend to use with key flexfields other than the Accounting Flexfield.

If you use a WHERE clause you must have the word "WHERE" as the first word of the clause. If you use ORDER BY, you must have the words "ORDER BY" in the clause.

You may not use HAVING or GROUP BY in your clause. You may not use UNION, INTERSECT, MINUS, PLUS, or other set operators in your clause, unless they are within a subquery.

You should always include the table names or aliases in your clause when you refer to a column, even if you are using only one validation table and have not used an alias for that table in the Table Name field. For example, you might enter:

where f.application_id = a.application_id

where form_table_name.application_id =
      application_table_name.application_id

You can use special variables in your WHERE clause that allow you to base your values on other values. The special variables you can use include

:$FLEX$.Value_Set_Name
:block.field
:$PROFILES$.profile_option_ name

Warning: The :block.field mechanism is present for backward compatibility only. Value sets that use this mechanism will not be compatible with a future release of Oracle E-Business Suite.

See the section WHERE Clauses and Bind Variables for Validation Tables for detailed information on using these special bind variables.

Additional Columns Field

What you specify here should be of the general syntax:

sql_expression_such_as_column_name  "Column Title Alias"(width)

You must specify either a 'column title alias' or a 'column width' for the additional column field to display. If you specify the SQL fragment without either a column title alias or a column width then your additional column field will not display. You can specify several such expressions, separated by commas, as follows:

column_name_1 "Column 1 Title"(width), column_name_2 "Column 2 Title"(width), ...

You can also use message names as alias names, this functionality allows for ease of translation of column titles. The syntax for using a message name as an alias name is:

sql_expression_such_as_message name "APPL=<Application Short Name>;NAME=<Message Name>"(width)

You should specify the column widths you want to display. You can use (*) to specify a column whose display width depends on the values it contains. You should always use an alias for any SQL expression that is not a simple column name. For value sets that use multiple tables, you should always include the table aliases in your column names. For example:

f.user_form_name "Form Title"(30), a.application_name "Application Name"(*)

If the segment or parameter is displayed, the Value Column appears with the parameter or segment prompt as the column title.

You can include more complex SQL fragments, such as concatenated column names and constants. For example:

 f.user_form_name "Form Title"(30), 
'Uses table: ' || t.user_table_name "Table Used"(30)

Allow Parent Values Field

If you allow parent values, you can create them for the values in your validation table using the Segment Values window.

Tip: We recommend that you allow parent values for segments in your Accounting Flexfield. Parent values are used to create summary accounts and to increase the productivity of Oracle E-Business Suite. However, we recommend that you do not allow parent values for other value sets. Allowing them for other value sets may have an adverse performance impact because the flexfield must validate against the union of the values in your table and the related values in the FND_FLEX_VALUES table and use an extra query for normal validation. For example, if a user uses the list of values on the segment, the list must retrieve the values from both tables.

If you specify additional columns in the Additional Columns field, or you specify a hidden ID column in the Hidden ID Column field, or you have a SUMMARY_FLAG column in your validation table, you must specify No in this field.

Related Topics

Overview of Implementing Table-Validated Value Sets

Creating Grants and Synonyms for Your Table

WHERE Clauses and Bind Variables for Validation Tables

Descriptive Flexfield Segments

See: Segment Values Window

Special Validation Routines Window

Special Validation Routines Window

the picture is described in the document text

See Special Validation Value Sets for information on using this region. The section contains information on the various types of events and flexfield routine arguments and syntax you use with special validation. It also contains a worked example of using special validation for the Accounting Flexfield.

Value Set Usages

Value Set Usages Window

the picture is described in the document text

You can view which flexfield segments or concurrent program parameters use a given value set with the Usages button on the Value Sets window.

To use this feature, you must first have privileges to the relevant flexfield segment or concurrent program parameter form. In particular:

You must have access to the Descriptive Flexfields Segments form to view the descriptive flexfields that use the value set.
You must have access to the Key Flexfields Segments form to view the key flexfields that use the value set.
You must have access to the Concurrent Programs (Developer Mode) or the Concurrent Programs (System Administrator Mode) form to view the concurrent program parameters that use the value set.

If you do not have access to any of the above forms, the Usages button is disabled.

Segment Values Window

Segment Values Window

the picture is described in the document text

Use this window to define valid values for a key or descriptive flexfield segment or report parameter. You must define at least one valid value for each validated segment before you can use a flexfield. These validated segments provide users with a list of predefined valid segment values, and have a validation type of Independent, Dependent, Translatable Independent, Translatable Dependent, or Table.

You should use this window to define values that belong to Independent, Dependent, Translatable Independent, Translatable Dependent, or Table value sets. You can define new segment values, specify value descriptions for your values and to enable or disable existing values as well.

The values you define for a given flexfield segment automatically become valid values for any other flexfield segment that uses the same value set. Many Oracle E-Business Suite reports use predefined value sets that you may also use with your flexfield segments. If your flexfield segment uses a value set associated with a Standard Request Submission report parameter, creating or modifying values also affects that parameter. If you use the same value set for parameter values, the values you define here also become valid values for your report parameter.

You also specify segment value qualifiers, rollup groups, and child value ranges.

You can also view and maintain segment value hierarchies for the Accounting Flexfield or for any custom application flexfields that use the value hierarchies feature.

Important: Because the Accounting Flexfield is the only Oracle E-Business Suite key flexfield that uses the parent, rollup group, hierarchy level and segment qualifier information, you need only enter this information for values that are associated with your Accounting Flexfield.

For certain types of changes to value hierarchies, a concurrent request is submitted to rebuild the value hierarchies. One request per value set that the change affects (the value set attached to the segment for which you are defining or maintaining values) may be submitted. For example, if you make hierarchy structure changes for five different key flexfield segments, all of which use different value sets, up to five concurrent requests may be submitted.

A concurrent request is submitted for the following changes to value hierarchies:

A new hierarchy range is defined, or an existing hierarchy range is updated or deleted.
A hierarchy range is moved to another value.
The value definition for non-parent values is updated in some way. For example, the description is changed.

Tip: For ease of maintenance, you should carefully plan your value hierarchy structures before you define your values, so that your structures follow a logical pattern you can expand later as you need more values.

Important: You cannot modify values for a value set if that value set is currently being modified by another user, either using the Segment Values Window or the Account Hierarchy Editor with Oracle General Ledger. If you get a message saying that the value set is already being modified, you can try again at a later time.

If your value set is based on a flexfield validation table (validation type Table) and you have defined your value set to allow parent values, then you can use this window to define parent values for the values in your table. This window stores your parent values and rollup groups for you and does not add them to your validation table. You can define child value ranges for the parent values you define, and you can assign your parent values to rollup groups. The values in your validation table can be child values, but they cannot be parent values, and you cannot assign them to rollup groups. You cannot create new values in your validation table using this window.

Ensure the following prerequisites have been completed before entering in your segment values.

Use the Value Set window to define your independent value sets, any dependent value sets that depend on them, and any table-validated value sets your flexfield needs
Use the Key Flexfield Segments window to define your flexfield structure and segments

Use the Descriptive Flexfield Segments window to define your flexfield structure and segments
Define your rollup groups, if any. See: Rollup Groups Window.

Tip: First use this window to define all of the independent values your application needs, then define your dependent values.

This window does not allow you to choose an independent value that would violate any flexfield security rules that are enabled for your responsibility.

Related Topics

Value Set

Key Flexfield Segments

Segment Values Block

Use this block to define valid values, to specify values for rollup groups and segment qualifiers, if any, and to enable and disable values. If you define a value you use as a default value for your segment or dependent value set, you must not specify a start or end date for that value. Also, you should not define security rules that exclude your default values.

Some key flexfields use segment qualifiers to hold extra information about individual key segment values. For example, the Accounting Flexfield in Oracle E-Business Suite products uses segment qualifiers to determine the account type of an account value or whether detail budgeting and detail posting are allowed for an Accounting Flexfield combination containing a given value.

You cannot define values that would violate any flexfield security rules that are enabled for your responsibility.

Related Topics

Relationship Between Independent and Dependent Values

Parent and Child Values and Rollup Groups, Oracle General Ledger User's Guide

Defining Segment Values

For most flexfield segments and report parameters, defining values is very simple if they use independent value sets and their value sets are not used with the Accounting Flexfield.

To define segment values:

Navigate to the Segment Values window.
Query the value set to which your values (will) belong. You can locate values either by their value set or by the flexfield segment or concurrent program parameter that uses their value set for validation.
Enter a segment value that is valid for your application. A valid value can be a word, phrase, abbreviation, or numeric code. Users can enter this value in a flexfield segment or a report parameter that uses this value set. Users also see this value whenever they select a value in a flexfield segment that uses this value set.

Any value you define must conform to the criteria you defined for your value set. For example, if your value set can only accept values one character long with no alphabetic or special characters allowed, you can only enter the values 0 through 9 in this field.

If you enter a value that contains the segment separator character defined for the flexfield that uses this value set, application windows display the character in your value as a ^ (or another non-alphanumeric character, depending on your platform) in your concatenated value fields to differentiate it from the segment separator. This change is for concatenated display purposes only and does not affect your value.

Since individual values can be referenced from many places in your applications, you cannot delete valid values that have already been defined, nor can you change those values. You can, however, change the description of a valid value in the Description field after you query up the value (or the translated value of a Translatable Independent or Translatable Dependent value set).

You cannot define values that would violate any flexfield security rules that are enabled for your responsibility.

If your value set is a Translatable Independent or Translatable Dependent value set, this value is "hidden" from the user in the flexfield windows.
If your value set has the type Translatable Independent or Translatable Dependent, the Translated Value field is enabled. The value from the previous step defaults in. You can update the Translated Value for all installed languages using the Translation icon in the Toolbar.

Validation is done for the translated values as well as the hidden values. For example, if you have defined your value set to have a maximum size of 50 characters, no translated value may be larger than 50 characters.
Enter a description for your value. Users see this description along with your value whenever they select a value in a flexfield segment that uses this value set.
Check the Enabled check box to make your value effective.
If you want to have the value effective for a limited time, you can enter a start date and/or an end date for it. The value is valid for the time including the From and To dates.

You cannot delete values from this window because they are referenced elsewhere in the system, but you can disable them at any time. You should not disable or have effective dates for a segment value that you use as a segment default or a default dependent value.
If you are defining values whose value set will be used with the Accounting Flexfield, define hierarchy and qualifiers information. See: Defining Hierarchy and Qualifiers Information.
Save your changes.

Related Topics

Qualifiers

Defining Hierarchy and Qualifiers Information

You only need to define hierarchy and qualifiers information if you are defining values whose value set will be used with the Accounting Flexfield.

Define your segment value before entering in hierarchy and qualifiers information. See: Defining Segment Values.

To define hierarchy and qualifiers information:

Determine whether this value is a parent value. If so, you can define and move child value ranges for this value, and you can assign this value to a rollup group. If not, you cannot define and move child value ranges for this value, and you cannot assign this value to a rollup group.
Enter the name of a rollup group to which you want to assign this flexfield segment value. You can use a rollup group to identify a group of parents for reporting or other application purposes. You can enter a rollup group name only if this flexfield segment value is a parent value and Freeze Rollup Groups in the Key Segments window is set to No. You can enter a range of child values for this flexfield segment value in the Define Child Ranges zone. You create rollup groups using the Rollup Groups window. See: Rollup Groups Window.
Enter the level for this value. This can be a description of this value's relative level in your hierarchy structure. This level description is for your purposes only.
If you are defining values for a value set used with the Accounting Flexfield, you must define segment qualifier information for each value. See: Qualifiers.

Qualifiers

Some key flexfields use segment qualifiers to hold extra information about individual key segment values. For example, the Accounting Flexfield uses segment qualifiers to determine the account type of an account value or whether detail budgeting and detail posting are allowed for an Accounting Flexfield combination containing a given value.

If you are defining values for any value set that is used by a key flexfield that uses segment qualifiers, you see the Segment Qualifiers pop-up window prompting you for this information. If you share this same value set with additional flexfields, such as a descriptive flexfield, you see the Segment Qualifiers pop-up window regardless of how you identified your value set in this window. Segment qualifiers contain information about a value rather than the segment that uses the value.

After you have saved your segment qualifier values, the values for your segment qualifiers appear in the Qualifiers field in the main window. You can click in the Qualifiers field to bring up the Segment Qualifiers window and see the qualifiers.

The Allow Budgeting, Allow Posting, and Account Type fields are segment qualifiers for the Accounting Flexfield.

Note: Oracle General Ledger has an Inherit Segment Value Attributes concurrent program that can automatically update an account combination's detail budgeting allowed, detail posting allowed, global reconciliation flag, enabled flag, start date, and end date attributes whenever these attributes change for a segment value in that account combination.

See the Oracle General Ledger documentation for more information.

Allow Budgeting

Indicate whether to allow detailed budgeting to GL accounts with this segment value. When you accept this value, you can perform detailed budgeting to GL accounts with this segment value. When you enter No, you can neither assign GL accounts with this segment value to budget organizations nor define budget formulas for GL accounts with this segment value.

When you are defining a parent segment value, enter No here, since you cannot budget amounts to a segment value which references other segment values where detail budgeting is already allowed.

When you change this field for a segment value that you have already defined, you should also make a corresponding change to all GL accounts which include that value. Use the GL Account Combinations window to allow or disallow detail budgeting to your flexfield combinations.

Allow Posting

Enter Yes or No to indicate whether Oracle E-Business Suite should allow detailed posting to GL accounts with this segment value. The default value for this field is Yes. When you accept this value, you can post directly to GL accounts with this segment value. When you enter No, you can neither use this segment value in GL accounts on the Enter Journals window, nor define formula journal entries that affect GL accounts with this segment value.

When you are defining a parent segment value, enter No here.

Account Type

You see this qualifier, which requires a value, for the natural account segment only. Enter the type of your proprietary account (Asset, Liability, Owners' Equity, Revenue or Expense) or the type of your budgetary account (Budgetary Dr or Budgetary Cr) your segment value represents. Choose any proprietary balance sheet account type if you are defining a statistical account segment value. If you choose a proprietary income statement account type for a statistical account segment value, your statistical balance will zero-out at the end of the fiscal year.

Your GL account combinations have the same account type as the account segment which they include. Changing the account type only affects new GL accounts created with the reclassified account segment. Changing the account type does not change the account type of existing GL accounts.

For more information on setting up the Accounting Flexfield, refer to the Oracle General Ledger Implementation Guide.

Hierarchy Details Buttons

The Hierarchy Details buttons open the windows you use to define and maintain detailed information about your value hierarchies.

You use the Hierarchy Details zone and the following zones primarily for values you use in segments of the Accounting Flexfield.

Variable	Description
Define Child Ranges	Choose this button to define child ranges for your parent value. The button is disabled unless your value is already a parent value.
Move Child Ranges	Choose this button to move child ranges from one parent value to another parent value. The button is disabled unless your value is already a parent value.
View Hierarchies	Choose this button to view the hierarchy structure to which your selected value belongs. You cannot make changes in this window. The button is disabled unless your value belongs to a hierarchy structure (it is either a parent value or a child value of another parent value).

Define Child Ranges

Child Ranges Window

the picture is described in the document text

Use this window to define child values for the value you defined in the Segment Values zone. Oracle E-Business Suite use child values to sum families of data or report on groups of data. You specify child values by entering a set of ranges. If you want to specify a single child value, set the low and high ends of the range equal to that value.

You cannot open this window if the value belongs to a rollup group and rollup groups are frozen. You freeze rollup groups using the Key Flexfield Segments window.

You can create networked hierarchies; that is, you can create hierarchy structures where a particular value may be a child that belongs to more than one parent. You should plan your value hierarchy structures carefully to avoid unwanted duplication of information caused by reporting or counting the same value more than once.

For example, suppose you want to define a hierarchy structure as shown in the following diagram.

Example of a Hierarchy Structure

the picture is described in the document text

In this structure the parent value 1000 has child values of 100, 200, and 300; and the value 300 is a parent to child values 301, 302, and 303. For the parent value 300, you could specify the child value range 301 (Low) to 303 (High). Since all three values 301, 302 and 303 are not parent values, you give this range a range type of Child.

For the parent value 1000, you need to specify two ranges so that you include both non-parent values (100 and 200) and parent values (300). First, you specify the child value range 100 (Low) to 200 (High) and give this range a range type of Child to include the values 100 and 200 as well as all the values between them (alternatively, you could specify these two values individually by specifying the same value for both Low and High). Then, to include the parent value 300, you specify the child value range 300 (Low) to 300 (High) and give this range a range type of Parent.

Enter the low and high ends of your child value range. You can enter any value that meets the validation criteria you define for this value set using the Define Value Set window. The high end of your child value range must be greater than or equal to the low end. Your ranges behave differently depending on your value set format type. For example, in a value set with a Character format type, 100 is less than 99 (even though they appear to be numbers). Similarly, a range that includes values from 100 to 200 would also include the value 1000.

Important: The Accounting Flexfield uses value sets that have a format type of Character, so you should specify your child ranges carefully for those value sets. For example, 100 is less than 99 (even though they appear to be numbers).

To specify a range that contains only a single value, enter the same value for both Low and High.

Range Type

If you select Child, any child values that fall in your specified range are considered to be children of your parent value. If you select Parent, any parent values that fall in your specified range are considered to be children of your parent value. Specifying Parent lets you create tree-structured hierarchies.

If you have existing child ranges from a previous version of Oracle E-Business Suite, those ranges automatically receive a range type of Child and they behave exactly as they did with your previous version.

View Hierarchies

Value Hierarchy Window

the picture is described in the document text

Use this window only for values you use in segments of the Accounting Flexfield in Oracle General Ledger.

You cannot make changes to your hierarchy structures in this zone.

The Value field displays the value that is a child of the parent value displayed in the Parent Value field.

The Parent field displays whether the child value is itself a parent value. If so, you can choose the Down button in the Navigate to view any values that are children of this value.

Navigate Buttons - Up/Down

Choose Up to view the values at the level just above your current value. If this value is a parent value, you can choose Down to view the child values that belong to the current value. If this value has more than one parent, you see a list of the parent values to which you can navigate. If you choose Up after navigating down a networked hierarchy, you move up to the parent you navigated down from most recently.

If you move up or down in the hierarchy structure, this window automatically changes the parent value displayed in the Parent Value field to show you the parent value in the level immediately above the level of the values you are viewing.

For example, suppose you have a hierarchy structure (in this case a networked structure) as shown in the following diagram:

Example of a Hierarchy Structure in Which a Value Has Multiple Parents

the picture is described in the document text

In this structure, the parent value 1000 has the child values 100, 200, and 300; the value 300 is in turn a parent to the values 301, 302 and 303. The value 303 has child values of 303A, 303B (which is a parent to the value 303BB), and 303C. The value 00003 is a parent value of 303 as well, and also has the child values of 403 and 503. Suppose you want to look at the structure starting with the value 1000 in the Segment Values zone. When you open the View Hierarchies window, you see the parent value 1000 with the values 100, 200, and 300 below it, as shown in the following diagram:

Example of a Hierarchy Structure in the Value Hierarchy Window

the picture is described in the document text

You choose Down with your cursor on 300, as shown above (Down is your only choice for this value). Once you choose Down, you then see (immediately):

Example of Navigating Down

the picture is described in the document text

You choose Down with your cursor on 303, as shown above (you can choose from Up or Down for this value). Once you choose Down, you then see its child values 303A, 303B, and 303C, as shown in the following diagram:

Example of Navigating Down Another Level

the picture is described in the document text

You choose Down with your cursor on 303B, as shown above (you can choose from Up, Down, or Network for this value). Once you choose Down, you then see the value of 303B listed with its child value of 303BB, as shown in the following diagram:

Example of Navigating Down a Third Level

You choose Up, as shown above (you can only choose Up for this value). Once you choose Up, you then see the value 303 listed as a parent value with its children values of 303A, 303B and 303C as shown in the following diagram:

Example of Navigating Up One Level

the picture is described in the document text

At this point, your cursor is next to the value 303B and the parent displayed in the Parent Value zone is 303. When you choose up, you can either go back up to your original parent value (303, which has the parent value 300), or you can go over to the other hierarchy path that leads to the parent value 00003. Once you choose 303B, you see a window offering you the two choices 300 and 00003 (these choices indicate the values that would appear in the Parent Value field. You will see 303 in the Children block if you make either choice), and 300 is highlighted. You choose 00003 this time, and then you see the parent value 00003 with the child values 303, 403, and 503, as shown in the following diagram:

Example of Navigating to Another Parent

the picture is described in the document text

At this point you cannot go up any further in the hierarchy structure.

Move Child Ranges

Move Child Ranges Window

the picture is described in the document text

Use this window to move a range of child values from one parent value (the source value) to another parent value (the destination value). When you move a range of child values from one parent value to another, you also move any child values that belong to the child values in the range you move. In other words, when you move a child to a different parent, you also move any "grandchild" values with it.

Use this window only for values you use in segments of the Accounting Flexfield.

For example, suppose you have defined hierarchy structures as shown in the following diagram:

Example of Hierarchy Structures

the picture is described in the document text

The value 1000 is a parent of the child values 100, 200 and 300; the value 300 is in turn a parent of the child values 301, 302, and 303. A separate structure consists of the parent value 003 with no child values. If you move the parent value 300 from the parent value 1000 to the parent value 003, you also move the child value range 301 (Low) to 303 (High). All three values 301, 302 and 303 are now grandchild values of 003 instead of 1000.

Enter the value from which you want to move a child range.

This field defaults to display the selected parent value from the Segment Values window.
Choose which child ranges you want to move to the destination value's child ranges.

The Type field displays the type of values this child range includes. If the field contains Child, any child values that fall in the specified range are considered to be children of your parent value. If the field contains Parent, any parent values that fall in the specified range are considered to be children of your parent value.

The Destination block displays the child value ranges that currently belong to the destination parent value.
Enter the parent value to which you want to move child value ranges. You can only choose a value that is already a parent value.

The Type field displays the type of values this child range includes. If the field contains Child, any child values that fall in the specified range are considered to be children of your parent value. If the field contains Parent, any parent values that fall in the specified range are considered to be children of your parent value.
Choose the Move button to move the child ranges you selected in the Source block to the destination parent value you specified in the Destination block.

Related Topics

Rollup Groups Window

Parent and Child Values and Rollup Groups, Oracle General Ledger User's Guide

Rollup Groups Window

Use this window to define rollup groups to which you can assign key flexfield values. You can use a rollup group to identify a group of parent values for reporting or other application purposes. You assign key flexfield segment values to rollup groups using the Segment Values window.

In Oracle E-Business Suite, only the Accounting Flexfield uses rollup groups. Rollup groups are used to create summary accounts for reporting purposes.

For more information on defining rollup groups, see the Oracle General Ledger Implementation Guide.

Related Topics

Value Sets

Defining Rollup Groups

Creating Summary Accounts, Oracle General Ledger Implementation Guide

Defining Rollup Groups

Perform the following before defining rollup groups:

Use the Value Set window to define your independent value sets, any dependent value sets that depend on them, and any table-validated value sets your flexfield needs. See: Value Set Windows.
Use the Key Flexfield Segments window to define your key flexfield structure and segments. See: Key Flexfield Segments.

To define rollup groups:

Perform the following steps:

Enter a code for your rollup group. The code is required and used internally.
Enter a name and description for your rollup group.
Save your changes.
Apply your rollup group name to particular values using the Segment Values window. See: Defining Segment Values.

For more information on defining rollup groups, see the Oracle General Ledger Implementation Guide.

Related Topics