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