Validation Type Options for Value Sets

Validation and usage of value sets determine where and how users access valid values for attributes represented by flexfield segments.

Tip: As a flexfield guideline, define value sets before configuring the flexfield, because you can assign value sets to each segment as you configure a flexfield. With descriptive and extensible flexfield segments, you can create value sets when adding or editing a segment on the UI where the flexfield appears.

The following aspects are important in defining value sets:

• Value sets for context segments

• Format-only validation

• Interdependent value sets

• Table validation

• Range

• Security

• Testing and maintenance

Value Sets for Context Segments

When assigning a value set to a context segment, you can only use table-validated or independent value sets.

You can use only table and independent value sets to validate context values. The data type must be character and the maximum length of the values being stored must not be larger than the context's column length. If you use a table value set, the value set can't reference flexfield segments in the value set's WHERE clause, other than the flexfield segment to which the value set is assigned.

Format Only Validation

You can use the format only validation type to enter any value as long as it meets your specified formatting rules. The value must not exceed the maximum length specified for that value set, and it must meet any format requirements for that value set. For example, if the value set permits only numbers, you can enter the value 456 for a value set with a maximum length of three or more, but not the value ABC.

You can also define a range of valid values for your value set by using the Minimum Value and Maximum Value fields. After you've defined a range of values, you can’t define a new valid value that falls outside this range. For example, you might create a value set with a number type format where the user can enter only the values between 0 and 100.

Note: For value sets with the value data type of Character, you can enforce minimum and maximum values only if the value set code ends with _VALIDATE_CHAR_MINMAX.

Interdependent Value Sets

Use an independent value set to validate data against a list that isn't stored in an application table, and not dependent on a subset of another independent value set. You can't specify a dependent value set for a given segment without having first defined an independent value set that you apply to another segment in the same flexfield. Use a dependent value set to limit the list of values for a given segment based on the value that the user has defined for a related independent segment. The available values in a dependent list and the meaning of a given value depend on which value was selected for the independently validated segment.

For example, you could define an independent value set of the states in the USA with values such as CA, NY, and so on. Then you define a dependent value set of cities in the USA with values such as San Francisco and Los Angeles that are valid for the independent value CA. Similarly, New York City and Albany are valid for the independent value NY. In the UI, only the valid cities can be selected for a given state.

Because you define a subset value set from an existing independent value set, you must define the independent value set first. Users don't have to select a value for another segment first to have access to the subset value set.

Independent, dependent, and subset value sets require a user-defined list of valid values. Use the Manage Values page to create and manage a value set's valid values and the order in which they appear.

Tip: You can configure the Manage Value Sets page to capture additional attributes for each valid value by adding context-sensitive segments in a new context for FND_VS_VALUES_B descriptive field.

Table Validation

Typically, you use a table-validated set when the values you want to use are already maintained in an application table, such as a table of supplier names. Specify the table column that contains the valid value. You can optionally specify the description and ID columns, a WHERE clause to limit the values to use for your set, and an ORDER BY clause.

If you specify an ID column, then the flexfield saves the ID value, instead of the value from the value column, in the associated flexfield segment. If the underlying table supports translations, you can enable the display of translated text by basing the value set's value column on a translated attribute of the underlying table. You should also define an ID column that's based on an attribute that isn't language-dependent so that the value's invariant ID (an ID that doesn't change) is saved in the transaction table. The run time displays the corresponding translated text from the value column for the run time session's locale.

Table validation lets you enable a segment to depend on multiple prior segments in the same context structure. You can't reference other flexfield segments in the table-validated value set's WHERE clause. Which means, the WHERE clause can't reference SEGMENT.segment_code or VALUESET.value_set_code.

Table-validated value sets have unique values across the table, irrespective of bind variables. The WHERE clause fragment of the value set is considered if it doesn't have bind variables. If it has bind variables, the assumption is that the values are unique in the value set. If you use table validated value sets for key flexfields, then you can't use all integration options supported for key flexfields, such as:

• Data security

• Oracle Transactional Business Intelligence (OTBI)

• Extended Spread Sheet Database (ESSbase)

• Tree or hierarchy integration

To use these integration options for key flexfields, you must use independent value sets only.

Range

In the case of format, independent, or dependent value sets, you can specify a range to limit which values are valid. You can specify a range of values that are valid within a value set. You can also specify a range validated pair of segments where one segment represents the low end of the range and another segment represents the high end of the range.

For example, you might specify a range for a format-only value set with format type Number where the user can enter only values between 0 and 100.

Security

In the case of independent and dependent values, you can specify that data security be applied to the values in segments that use a value set. Based on the roles provisioned to users, data security policies determine which values of the flexfield segment users can view or modify.

To enable security on a value set, specify a database resource, typically the code value for the value set. Using the Manage Database Security Policies task, specify conditions, such as filters or SQL predicates, and policies that associate roles with conditions. You can use a filter for simple conditions. For more complex conditions, use a SQL predicate.

Value set data security policies and conditions differ from data security conditions and policies for business objects in the following ways:

• You can grant only read access to users. You can't specify any other action.

• When defining a condition that's based on a SQL predicate, use VALUE, VALUE_NUMBER, VALUE_DATE, VALUE_TIMESTAMP, or VALUE_ID to reference the value from a dependent, independent, or subset value set. For table value sets, use a table alias to define the table, such as &TABLE_ALIAS category=70.

When you enable security on table-validated value sets, the security rule that's defined is absolute and not contingent upon the bind variables (if any) that may be used by the WHERE clause of the value set. For example, suppose a table-validated value set has a bind variable to further filter the value list to x, y and z from a list of x, y, z, xx, yy, zz. The data security rule or filter written against the value set must not assume anything about the bind variables. Instead the whole list of values must be available and you write the rule, for example, to permit x, or to permit y and z. By default in data security, all values are denied and show only rows to which access has been provided.

Testing and Maintenance

You don't have to define or maintain values for a table-validated value set, as the values are managed as part of the referenced table or independent value set, respectively.

You can't manage value sets in a sandbox.

When you change an existing value set, the deployment status for all affected flexfields changes to Edited. You must redeploy all flexfields that use that value set to make the flexfields reflect the changes. In the UI pages for managing value sets, the value set's usages show which flexfields are affected by the value set changes.

If your application has more than one language installed, or there is any possibility that you might install one or more additional languages for your application in the future, select Translatable. This doesn't require you to provide translated values now, but you can't change this option if you decide to provide them later.