Value Set types in Oracle Applications

Value Sets

Validation Types in Value Set:

1. Dependent (In order to create a dependent values set you need to create one Independent value set first)
2. Independent
3. None
4. Pair
5. Special
6. Table
7. Translatable Independent
8. Translatable Dependent

• None: This type allows users to enter any value as long as the value meets value set formatting rules. As this value set is not validated, a segment using this value set does not provide list of values. Ex: If the value set does not allow alphabetic characters, we cannot enter “ABC” but can enter “123”.
• Independent: This type provides a predefined list of values associated with descriptions. Ex: The meaning of value in this value set does not depend on any other value of any other segment.
• Table: This type provides a predefined list of values like an independent set but its values are stored in an application table. We define which table to use along with a “where” clause to limit the values to be used.
• Dependent: This type is similar to independent value set except that the available values and their meanings depend on the independent value that was selected in the prior segment of the flex field. Ex: The Item type depends on what type is chosen in the Item Code field.
• Special and Pair Value sets: These provide a mechanism to allow “Flex field within a Flex Field” structure. These value sets are primarily used for Standard Request Submission (SRS) parameters, and are not used for normal flex fields. For a Pair value set, two flex fields provide a range of valid values.
• Translatable Dependent and Translatable Independent: These value types are similar to Dependent and Independent value sets except that they provide a predefined list of values for the segment, also these values can be translated into different languages

Questions on Oracle Value sets

1. Use of Special type of value set.

>> Special type is used for flexifields. Below are some examples for reference:

NOTE: Validate type of Pair will also have similar structure.

Value Set Name: AR_ARZACS_VAL_GLDATE_STANDARD
Validation Type: Special
Click on Edit Information button.
Event = Validate
Funtion =
AR VAL_GLDATE
GL_DATE=:!VALUE
SET_OF_BOOKS_ID=:$PROFILES$.GL_SET_OF_BKS_ID
VGL_DATE_FORMAT="DD-MON-YYYY"

Value Set Name: AP_GL_SRS_FLEXFIELD
Validation Type: Special
Click on Edit Information button.
Event = Edit
Funtion =
FND POPID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=:$FLEX$.GL_SRS_CHART_OF_ACCOUNTS_ID
REQUIRED=Y
VALIDATE=FULL
ID=:!ID
SEG=:!VALUE
DESC=:!MEANING
NAVIGATE=!DIR
DINSERT=N

Event = Load
Funtion =
FND LOADID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=:$FLEX$.GL_SRS_CHART_OF_ACCOUNTS_ID
REQUIRED=Y
VALIDATE=FULL
ID=:!ID
SEG=:!VALUE
DESC=:!MEANING
NAVIGATE=!DIR
DINSERT=N


Value Set Name: AP_SRS_APXINREV_COMPANY_SEGMENT
Validation Type: Special
Click on Edit Information button.
Event = Edit
Funtion =
FND POPID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=:$FLEX$.AP_APXINREV_CHART_OF_ACCOUNTS_NO_VALIDATION
REQUIRED=Y
VALIDATE=NONE
ID=:!VALUE
SEG=:!VALUE
DESC=:!MEANING
DINSERT=N
DISPLAY=GL_BALANCING
ALLOWNULLS="Y"
BOX=Y

Event = Validate
Funtion =
FND VALID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=:$FLEX$.AP_APXINREV_CHART_OF_ACCOUNTS_NO_VALIDATION
REQUIRED=N
VALIDATE=NONE
ID=:!VALUE
SEG=:!VALUE
DESC=:!MEANING
DINSERT=N
BOX=Y
DISPLAY=GL_BALANCING


2. Use of $FLEX$ in value set

>> :$FLEX$ is used for dependent condition of the parent value set which is of type table.

This example is from flexfield user guide.

Example of $FLEX$ Syntax
Here is an example of using :$FLEX$.Value_Set_Name to set up value sets where one segment depends on a prior segment that itself depends on a prior segment ("Cascading Dependencies")

Assume you have a three-segment flexfield where the first segment is car manufacturer, the second segment is car model, and the third segment is car color. You could limit your third segment’s values to only include car colors that are available for the car specified in the first two segments. Your three value sets might be defined as follows:

Segment Name Manufacturer
Value Set Name Car_Maker_Name_Value_Set
Validation Table CAR_MAKERS
Value Column MANUFACTURER_NAME
Description Column MANUFACTURER_DESCRIPTION
Hidden ID Column MANUFACTURER_ID
SQL Where Clause (none)

Segment Name Model
Value Set Name Car_Model_Name_Value_Set
Validation Table CAR_MODELS
Value Column MODEL_NAME
Description Column MODEL_DESCRIPTION
Hidden ID Column MODEL_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set

Segment Name Color
Value Set Name Car_Color_Name_Value_Set
Validation Table CAR_COLORS
Value Column COLOR_NAME
Description Column COLOR_DESCRIPTION
Hidden ID Column COLOR_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set AND
MODEL_ID = :$FLEX$.Car_Model_Name_Value_Set

In this example, MANUFACTURER_ID is the hidden ID column and MANUFACTURER_NAME is the value column of the car_Maker_Name_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Car_Maker_Name_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example.

Another example found on the internet, which talks about using PL/SQL code or calling database functions:

Oracle Special Value Set

Special value set configuration and uses.

I have observed that many times we need to restrict users to a limited, conditional value entries either in DFF or when submitting concurrent request. Normally we can use dependent value set, but when dynamic or some specific check is required, then special value set is better and only choice. Below I have described all the steps for configuring value set. Assign this value set to DFF or concurrent req. parameter as required.

1. Go to Application Developer --> Application --> Validation --> Set.
2. Create a new value set - Enter value set name, description, List type = List of values.
3. Select Validation Type = Special from left bottom of the screen.
4. Click on Edit information.
5. Select Event = Validate.
6. Now in function, you can write pl/sql code or call any function from database.
7. The logic of validation will be as per requirement.
8. The code syntax will be FND PLSQL " entire function "
9. To read the value which user has entered in DFF or as conc. request parameter, use lc_in := :!value;
10. To raise error if the enter value is not correct, use fnd_message.raise_error;
11. Use Application message. Display appropriate message.
12. Raising error makes sure that user cannot continue with the invalid value and he will be forced to correct entry.
13. The character size is limited, but by calling database function, one can put complex validation.

-- Below is the code to validate that user can select a date which is 3 months before sysdate. This is a parementer in report in which user should be allowed to see data for 3 months or before, but he cannot see recent quater data.

FND PLSQL "declare
  l_value varchar2( 20 ) := :!value ;
  l_valid NUMBER := 0 ;
BEGIN
  SELECT (sysdate - to_date(l_value,'dd-mon-yyyy'))
  INTO l_valid
  FROM dual;
  IF (l_valid <= 91) THEN
      fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE' ) ;
      fnd_message.set_token( 'MESSAGE', 'Date must be atleast 3 calendar months prior to current date' );
      fnd_message.raise_error ;
  END IF ;
END;
"


3. How to create a value set which is dependent of another value set. (let us consider a value set which displays me the country name, state, in this the country value set is the parent and state value set is child . pls do explain me how to create these two value sets.)

>> You can create dependent value sets in 2 ways.
1) Create one independent then create one dependent and mention the independent one.
2) Create on table type, create another table type and specify the where clause as mentioned above.


4. How to create a value set using 3 tables?

>> You can create value set using multiple table. Give the table names seperated by , in the table name field.
Go through any seeded value set which is based on multiple tables.
Example: