Previous	Next		Contents	Index	Navigation	Glossary	Library

   To implement a validation table:

1. 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.

2. 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.

3. Create the necessary grants and synonyms. See: Creating Grants and Synonyms for Your Table.

4. Define a value set that uses your validation table. See: Defining Value Sets.

5. Define your flexfield structure to use that value set for a segment.

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.

Attention: 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

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.

Attention: You should 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.

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

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

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') 

or

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)

See Also