f Get Oracle Apps Traning Here: Value Sets In Oracle Apps

This work is licensed under a Creative Commons -NonCommercial 2.5 License.

Wednesday, December 06, 2006

Value Sets In Oracle Apps

Many thanks to Rakesh Sreenivasa for contributing yet another article for Get Apps Training

Value Sets

Value Set is a collection of values. It validates the values entered by the user for a flex-field, report parameters or a concurrent.

The navigation path: Application :- Validation :- Sets

Value Set is associated with the following validations which holds the key to the value sets.

Types of Validations:

  1. None :- Validation is minimal.

  1. Independent :- The data input is based on previously defined list of values.

  1. Dependent :- The data input is checked against a defined set of values.

  1. Table :- The data input is checked against values in a table.

  1. Special :- The data input is checked against a flex-field.

  1. Pair :- The data input is checked against two flex-field specified range of values.

  1. Translatable independent :- The data input is checked against previously defined list of values.

  1. Translatable dependent :- The data input is checked against defined set of values.

Significance of $FLEX$

$FLEX$: enables to match the prior segment with either value set name or segment name.

Let v2 be the value set definition of 2nd parameter and v1 be the value set definition for the first parameter then

In the value set definition of v2 = value $FLEX$.v1


Changes You Should Never Make

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:

. Independent to Dependent

. Dependent to Independent

. None to Dependent

. Dependent to Table

. Table to Dependent

. Translatable Independent to Translatable Dependent

. Translatable Dependent to Translatable Independent

. None to Translatable Dependent

. Translatable Dependent to Table

. Table to Translatable Dependent

Comments on "Value Sets In Oracle Apps"


Anonymous Anonymous said ... (9:23 PM) : 

The information provided in this page is very good ,especially the screen shots of the Oracle screens are very beneficial ..Pls carry on the good work !!

Thanks ...


Anonymous Anonymous said ... (6:35 PM) : 

Hi Sriram here,

How to get the validation for the dependent flex fileds? For example you have 2 values set 'My Value Set1' and 'My Value Set2'. And 2nd one is dependent on First. From PL/SQL how we can validate a perticuklar vlaue is valid as per Value set 'My value set2'?
For Example :
My Value Set1 Values: 1,2,3,4
My Value Set2 Values:
For 1 (My Value Set1 Value) - 100,200
For 2 (My Value Set1 Value) - 1000,2000
From Pl/Sql How can we validate 100 as the valid value as per 'My Value Set2'
You can mail me asrcmurthy at yahoo dot com


Anonymous Anonymous said ... (3:08 AM) : 

Information was useful.



Anonymous Anonymous said ... (3:09 AM) : 

Information was usefull.


Anonymous Anonymous said ... (9:13 AM) : 

The information given about value sets is good and very useful. Good Work..



Anonymous Anonymous said ... (7:18 AM) : 

Hi! Nice site getappstraining.blogspot.com! Thank you!
http://www11.asphost4free.com/tramadolrx/Tramadol.html tramadol tramadol


Anonymous Anonymous said ... (4:27 AM) : 

I have registered a concurrent program. Now the requirement of one of its parameter is the end user either can enter the value manually or can choose from some predefined values. Then tell me what type of value set should I create for this parameter to accomodate this requirement.
Thanks in advance!!!


Blogger Anil Passi said ... (4:34 AM) : 

interesting question. I am not aware of a mechanism to do so.

Although in D2K form, we have a item property for "Validate from LOV". However I think there is no such property for Value Sets.
You need to create two different DFF segments in this case



Anonymous Anonymous said ... (9:11 AM) : 

Hi Mr Anil infact am new to oracle ebusiness suite and am doing a form which have a drop down list which will contain a set of values. can u help me how i will access this set of values through the drop down list....

thank you in advance for your kind attention


Anonymous Anonymous said ... (3:35 AM) : 

Thanks for the info. Very useful.
I have a situation which I am not sure how to implement. Here it is:
I have a concurrent program defined, with has two input parameters, say p1 and p2. The value of "p2" will actually depend upon the value entered for "p1". For e.g. if p1 is considered as "Dept No", I would like to have a list of values for "p2" that displays only Employees (from the database table EMP) associated with the value in "p1".
Not sure whether there is a way to pass parameters to value sets.


Blogger priyanka said ... (12:06 AM) : 

I HAVE ONE REQUIREMENT , IF I GIVEN A SUPPLIER NAME in first parameter , defaulty it has to come in second parameter corresponding supplier number, if i select the supplier number in
the second parameter defaulty it has to come in first parameter corresponding supplier name,
is there any value set for that , i created one value set using flex, if i enter supplier name defaultly it's comming corresponding supplier number , if i select supplier number defaultly it's not comming supplier name , can u give the solution for this


Blogger Prabhakar said ... (8:57 AM) : 


It is very easy to understand, especially by seeing screen shots.

Thank You.


Blogger Prabhakar said ... (8:57 AM) : 


It is very easy to understand, especially by seeing screen shots.

Thank You.


Blogger Hari said ... (1:36 PM) : 

you can get the list of employees on p2 based on what value you have chosen on p1 by using reference fields.

You can create a Table based value set which gets values of Employees and in the where condition you can include dept_no=:$FLEX$.P1

I think it should work...


Anonymous Anonymous said ... (10:54 AM) : 

The screenshots are good....But i think a little more description is needed for beginners to understand the different validation types.....I couldnt understand Dependent,Special and Pair validations...


Blogger kapil said ... (3:53 AM) : 

The information and the screen shto are good one and provide enough information on value set.
But it did not give information on the different types of value set


Blogger Konstantin said ... (5:22 AM) : 

Can I create validation set based on select, or view ?


Blogger narayana said ... (1:09 AM) : 

i have a small question here how to create a table valueset by using multiple tabales but not bycreating a view


Blogger narayana said ... (1:12 AM) : 

Hi Anil

Here I had a small quetion is it possible to create a tablevalueset by multipletables but not creating by view with those tables, if possible how?


Blogger sivaramraju said ... (2:46 AM) : 

Sivaram here
The information in this page is
very good. But the oracle screens
are very beneficially
Carry on !


What is Flexfield ?
Why the Flexfields are useful ?
send me the total Flexfields


Blogger sivaramraju said ... (2:47 AM) : 

Sivaram here
The information in this page is
very good. But the oracle screens
are very beneficially
Carry on !



Anonymous Anonymous said ... (4:42 AM) : 

Hi anil
my requirement like this i have two parameters 2nd parameter should dependent on 1st parameter and it should accept the value what ever user passes.

suppose i have x and y parameters

x paramter is independent parameter

and y parameter is free text value parameter(it should accept what ever user pass the value at run time) and it should be dependent on the 1st parameter


Anonymous Anonymous said ... (10:18 AM) : 


Values sets are also used for reports


Anonymous Anonymous said ... (7:44 AM) : 

Hi Anil,

I am getting following error:

Test of value set XX_PAY_PERIOD failed with following error message :
ORA-00936: missing expression
select distinct p.period_name from PER_TIME_PERIODS p where rownum=1 and distinct p.period_name is NULL


In Value Sets Under "Validation Table Information" I have got following entries:

Table Columns - Value - distinct p.period_name



Blogger Anil Passi said ... (7:47 AM) : 

As per your SQL, you have distinct in where clause, please remove that.

Also, you have rownum=
This will never return you any record, please remove that too.


Blogger sharmila said ... (6:23 AM) : 

Hi Anil,

I have a question regarding special value set. I have a location flexfield and I would like to pass it as parameter to my report. it has 4 segments, say street, city, county, state. I know I have to use special value set but I don't know what I should be putting in NUM=$FLEX$(????)
Could you please help me with this...



Blogger Anil Passi said ... (7:40 AM) : 

Hi Sharmila

Please see this article
Value sets

Does that answer your question.



Blogger sharmila said ... (1:48 PM) : 

Thanks for the quick reply Anil. It helped me to an extent, but what I am looking for is, a flexfield as parameter. there is only one parameter location_id for my report, but during run time when user clicks on that it should pop up a small window with all the segments and LOV in location flexfield(as how it appears for accounting flexfield). I hope I am clear.


Anonymous Jay said ... (12:44 PM) : 

Hi Anil,

I have been reading your blogs from the day i thought of getting into Oracle apps. Your articles are very good and I should appreciate you effort in helping others.

I was trying to enable DFF for the Budget lines in Projects and add two segments to it where the second segment is dependent on the 1st one.

I have to get the Department ID's into the first segment from the 'fnd_flex_values' based on the segment name 'Department' in the Accounting KFF. Based on the 1st segment's value the user has selected, I have to get a value from column 'attribute1' in the same table 'fnd_flex_values' to the second segment for which i have used the following queries.

1st segment (Value set name is Department)

Select ffv.flex_value
fnd_id_flex_segments ffs,
fnd_flex_values_vl ffv
where ffs.segment_name = 'Department'

2nd segment

Select attribute1
where flex_value = :$FLEX$.Department;

When i did a test for the Value sets, it was succesful but when i go to the budgets window, I get the following error even before opening the DFF -

APP-FND-01242: Cannot read value from field $flex$.Department

Cause: The field $flex$.Department could not be located or read.

Action: This error is normally the result of an incorrectly-entered field name string in a trigger, or a field name string that does not uniquely specify a field in your form. Correct your trigger logic to precisely specify a valid field.

Could you please help me with this?

Thanks & appreciate your help,


Anonymous Saurabh Gautam said ... (2:26 PM) : 

Hi Anil,

I have a requirement to create a conc prog with two parameters. Say the parameters are PARAM1 and PARAM2. The PARAM1 has values 'X' and 'Y'. Now param2 should behave the following way: If param1 value is 'X', param2 should be a free form entry field and if param1 value is 'Y', the value entered in param2 should be validated against a table. Is it possible to achieve this in oracle apps ?

Thanks in advance.
Saurabh Gautam


Blogger Anil Passi said ... (2:31 PM) : 

Hi Saurabh

Please look out for Arun Sista's articles and video demos on apps2fusion.com

Anil Passi


Blogger Muhammad Waseem said ... (12:54 AM) : 

how i find the value set name in oracle apps ....


Anonymous satya said ... (1:16 PM) : 

Hi satya here,
what is the difference between dependent and translatable dependent ||y between Independent and translatable Independent?


Blogger Praveen said ... (11:24 PM) : 

Hii anil,
Suppose i want to a lov on a column of form


Blogger Praveen said ... (11:25 PM) : 

Hii suppose i want add a lov on a form. is it poosible using value set or i have to customize the form


Blogger Kanvik said ... (3:44 AM) : 

In table value set in edit information whats the meaning and id column????


Blogger Kanvik said ... (9:44 PM) : 

I am using table value set and in the list i want the information coming from two tables. So how should i retrieve??


Anonymous Anonymous said ... (12:45 PM) : 

Hi Mustapha here.
I am new in apps and i am working in descriptive flexfields and transaction window in AR.
can somebody help me how to find and input a percentage of a value of an existing segment into another segment?

Also, how can i do the sum of 3 segment values and input the sum value in a 4th segment?

Plz help me...


Blogger Razia said ... (5:15 AM) : 

Hi Anil,
I have a requirement that there are four parameters based on value of second third should populate(and also on based on first )
The dependancy of 2-3 and 3-4 is well established but relation for 3 with 1 and 2 is not happening


Anonymous Anonymous said ... (9:55 PM) : 


I didn't get the difference between translatable dependent and translatable independent.

Could you specify it clearly?




Anonymous Anonymous said ... (1:45 AM) : 


I didn't get the difference between translatable dependent and translatable independent.

Could you specify it clearly?


Anonymous 出張ホスト said ... (12:33 AM) : 



Anonymous Anonymous said ... (8:43 AM) : 

Your blog keeps getting better and better! Your older articles are not as good as newer ones you have a lot more creativity and originality now keep it up!


Anonymous Anonymous said ... (7:11 PM) : 

You may probably be very curious to know how one can manage to receive high yields on investments.
There is no need to invest much at first.
You may commense to get income with a sum that usually goes
for daily food, that's 20-100 dollars.
I have been participating in one project for several years,
and I'll be glad to let you know my secrets at my blog.

Please visit my pages and send me private message to get the info.

P.S. I make 1000-2000 per day now.

http://theinvestblog.com [url=http://theinvestblog.com]Online Investment Blog[/url]


Anonymous Anonymous said ... (11:44 PM) : 


information is very helpful..specially with the screenshots. Can anyone please tell me what are the steps involved in details for table validation of value sets.

kindly mail me at
shreya85.roy@gmail.com for this.

Thanks in advance...


Anonymous グリー said ... (1:16 PM) : 



Anonymous Anonymous said ... (11:56 PM) : 

the information is not sufficent


Anonymous オークション said ... (6:17 PM) : 



Blogger Rutuja Deshmukh said ... (11:05 PM) : 

You really did a great job. I found your blog very interesting and very informative. I think your blog is great information source & I like your way of writing and explaining the topics. Keep it up.
oracle fussion middleware


Anonymous Anonymous said ... (9:50 AM) : 

seo backlink seo services uk backlink service quality backlinks


Anonymous Anonymous said ... (1:04 AM) : 

unlock iphone 4
how to unlock iphone 4

unlock iphone 4 unlock iphone 4 how to unlock iphone 4
unlock iphone 4

unlock iphone 4 unlock iphone 4 [url=http://theunlockiphone4.com]how to unlock iphone 4 [/url] unlock iphone 4


Anonymous ritu said ... (1:59 AM) : 


i want to konw use of pair type validation with some , if possible.



post a comment