Validation Table Information Window (User, System Administrator, and Flexfields Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Validation Table Information Window

Defining Value Sets

To define validation table information:

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.

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

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

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

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

6. Enter a SQL WHERE clause or an ORDER BY clause, or both.

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

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

Column Type Fields

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.

Column Size Fields

The three Size fields automatically display the sizes of the columns you select.

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.

WHERE / ORDER BY Field

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

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

Additional Columns Field

What you specify here should be of the general syntax:

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)

Allow Parent Values Field

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