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.
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.
Attention: 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.
Attention: 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.
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.
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.
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
or
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.
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)
If you allow parent values, you can create them for the values in your validation table using the Segment Values window.
Suggestion: 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.