fnd_form f, fnd_application a
where f.application_id = a.application_id
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 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.
If you do not specify a hidden ID column, Oracle Applications 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 Applications 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 Applications 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 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
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 Applications.
sql_expression_such_as_column_name "Column Title Alias"(width)
where either the column title alias or the width is optional. If you specify only the SQL fragment but no alias or width, your column does not show up. 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)
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 Applications. 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.
See: Segment Values Window
Choosing a Validation Type for Your Value Set
Overview of Implementing Table-Validated Value Sets
Using Validation Tables
Creating Grants and Synonyms for Your Table
WHERE Clauses and Bind Variables for Validation Tables
Example of $FLEX$ Syntax
Value Set Windows