Value Sets in Oracle Apps

Oracle Application Object Library uses values, value sets and validation tables as important components of key flexfields, descriptive flexfields, and Standard Request Submission.

Value sets are a list of values used for validation of data. 

1. Define a Value set

Navigation:- System Administrator --> Application --> Validation --> Set

2. Enter a unique name for this value set   

If you plan to refer to your value set name in a WHERE clause for a validation table value set, you should use only letters, numbers, and underscores (_) in your value set name. 

• You should not include any spaces, quotes, or other special characters in your value set name.
• Do not use the string $FLEX$ as part of your value set name. 

Note that validation tables are case-sensitive for value set names you use in validation table WHERE clauses.

Suggestion: Make your value set names contain only one case (either upper or lower case) to avoid case-sensitivity problems.

3.  Specify the List Type for your value set.

• Poplist - fewer than 10 values expected
  
• List of Values - between 10 and 200 values expected
  
• Long List of Values - more than 200 values expected

4.  Specify the Security Type 

• No Security - All security is disabled for this value set
• Hierarchical Security :- any security rule that applies to a parent value also applies to its child values.
• Non-Hierarchical Security :-  a security rule that applies to a parent value does not "cascade down" to its child values. 

5. Enter the type of format 

     Valid choices include: Char, Date, DateTime, Number, Standard Date, Standard DateTime, and Time.

6. Select your validation type

    Validation type 'None' in Value sets

    Validation type 'Independent' in Value sets

    Validation type 'Dependent' in Value sets

    Validation type 'Table' in Value sets

    Validation type 'Special' in Value sets

    Validation type 'Translatable Independent' in Value sets

    Validation type 'Translatable Dependent' in Value sets

Using $FLEX$ in Value sets
Using $PROFILES$ in Value sets


Which Oracle table store Value sets and underline information

• FND_FLEX_VALUE_HIERARCHIES
• FND_FLEX_VALUE_SETS
• FND_ID_FLEX_SEGMENTS
• FND_FLEX_VALUE_NORM_HIERARCHY
• FND_FLEX_HIERARCHIES
• FND_FLEX_VALUE
• FND_FLEX_VALIDATION_EVENTS
• FND_FLEX_VALUE_RULE_LINES
• FND_FLEX_VALUE_RULE
• FND_FLEX_VALUE_RULE_USAGE
• FND_ RESPONSIBILITY
• FND_TABLES
• FND_FLEX_VALIDATION_TABLES

Do we have any restriction on value set?

Yes, here are some listed one:

• Table Validated Value Sets
  • We cannot use table-validated id value sets for any accounting flexfield or any other key flexfields.
  • We cannot use :$FLEX$, :$PROFILES$ in table name, value and id of table validated value sets.
  • We cannot use DISTINCT clause in any of the column fields or in the WHERE clause of a table validate value set.
  • In an id value set, the value can be non-unique but id should be unique. In a non-id value set, value should be unique.
  • We can only use columns selected for the table-validated value set must be of type NUMBER, DATE or VARCHAR2.
  • Support for SQL expression in columns of Table Validated value sets will be obsolete in future release.
• Translatable Independent and Translatable Dependent Value-sets
  • The Numbers Only and Uppercase Only option cannot be used.
  • Must have "Char" format type.
• Special/Pair value-sets
  • Special/Pair value sets are user-exit value sets . PL/SQL APIs will not be able to validate them.

You should never make these types of changes (old value set to new value set) because you will corrupt your existing key flex-field combinations data:


1. Independent to Dependent
2. Dependent to Independent
3. None to Dependent
4. Dependent to Table
5. Table to Dependent
6. Translatable Independent to Translatable Dependent
7. Translatable Dependent to Translatable Independent
8. None to Translatable Dependent
9. Translatable Dependent to Table
10. Table to Translatable Dependent


How to create XML Report in Oracle

Leave a comment for any query.