Skip to content

Green color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color

Main Menu

Home
Technical Articles
Training Articles
Receive Email for New Articles
Contributors
Hands-On-Training
Consultancy Services

Related Items

Home arrow Technical Articles arrow Miscellaneous Technology arrow Value set basics in Oracle Apps
We are glad to announce the launch of Forum for Customizations and Extensions. Click here to visit http://apps2fusion.com/forums This website has now moved to http://apps2fusion.com


Our OA Framework, BPEL Development & Apps DBA Trainings from USD 299 only [on weekends] . Click here for details.
Also see here fully verifiable feedbacks/testimonials
Value set basics in Oracle Apps | Print |  E-mail
Written by Anil Passi   
Thursday, 26 October 2006
An article on Value Sets, for beginners that follow http://getappstraining.blogspot.com
Question: What is value set?
Answer: It is a set of values

Question: Why do we need value sets?
Answer: You do not always want a user to enter junk free text into all the fields. Hence, Oracle Apps uses value set to validate that correct data is being entered in the fields in screen.

Question: Is value set attached to all the fields that require validations?
Answer : A big NO

Question: Then where lies the usage of value sets?
Answer: Broadly speaking, value sets are attached to segments in Flexfields. You can argue that value sets are also attached to parameters of concurrent program(but in reality oracle treats parameters as Descriptive Flexfields)

Question: This is insane, flexfields haven’t been covered in http://getappstraining.blogspot.com as yet?
Answer: Agreed, hence let’s restrict value set explanation to their usage in concurrent program parameters.

Question: Any examples?
Answer: For the namesake, lets add a Parameter to the concurrent program that we defined in “Concurrent Program Training Lesson link”. Lets add a parameter named “cost centre”, the values to this parameter must be restricted to one of the three values, i.e. HR, SEC, IT.

At the time of submission of the concurrent program the user should be able to pick a cost centre from a list. This is where value set gets used.

Lets now define a simple value set as described above.

Step 1. Go to Application Developer, and select menu /Validation/Set
Image


Step 2. Now define a value set of type Independent. We will cover the other most widely used Type “Table” latter.
Image


Step 3. Now, lets add three independent values to the value set for this Cost Centre list. Hence click on menu Values within Validation
Image

Step 4. Here we add the values for IT, HR, SEC to this independent value set.
Image

“CONTROL-S” to save the data

Step 5. Now let us go back to Concurrent Program that we created in earlier training lesson and Click on Parameters
Image


Step 6. Now lets create a parameter, and attach the value set that we created to this parameter.
Image


Step 7.
Now to test this, lets go to receivables manager and click on Requests.
Image
Click on Request,

Step 8.
Submit New Request, and then click on OK.
Image


Step 9
Now, we can see the values defined in the value set here.
Image

Comments (50)add
...
written by reddy , December 12, 2006
hi anil
once again i appreciate urs effort .but is these finicial stuff.please can u tell what r the question they would ask for specialy for oracle hrms functional consultant in london.i mean to say process of inteview .i have done my mba(hr) .is that to apply for oracle hrms in UK we should have previous UK experience please guide me .
i will be waiting for urs reply
thanks bye
...
written by Anil Passi , December 12, 2006
Hi Reddy,

The rules of the game are the same weather in UK or US or in India.
The best way to pass interviews is to impress them with your knowledge. To that effect you must do as much reading as possible. The interview questions are there to help you with some ideas. However the ingredient for guaranteed success is your knowledge.

Thanks,
Anil Passi
...
written by reddy , December 12, 2006
hi anil
once again i appreciate urs effort .but is these finicial stuff.please can u tell what r the question they would ask for specialy for oracle hrms functional consultant in london.i mean to say process of inteview .i have done my mba(hr) .is that to apply for oracle hrms in UK we should have previous UK experience please guide me .
i will be waiting for urs reply
thanks bye
...
written by Anil Passi , December 12, 2006
Hi Reddy,

The rules of the game are the same weather in UK or US or in India.
The best way to pass interviews is to impress them with your knowledge. To that effect you must do as much reading as possible. The interview questions are there to help you with some ideas. However the ingredient for guaranteed success is your knowledge.

Thanks,
Anil Passi
...
written by reddy , December 13, 2006
thanks anil
for valuable suggestion . if u have time can u please can u post interview questions for functional role for oracle hrms not payroll .becaz u have more idea .beacz i dont have previous experience in hrms .i am going to aplly for jobs these week.i will be happy if u can.
thanks
reddy
...
written by reddy , December 13, 2006
thanks anil
for valuable suggestion . if u have time can u please can u post interview questions for functional role for oracle hrms not payroll .becaz u have more idea .beacz i dont have previous experience in hrms .i am going to aplly for jobs these week.i will be happy if u can.
thanks
reddy
...
written by balkrishna , December 14, 2006
ihave uderstand why oracle consider parameter as discriptive flexfield

what i understand is because pop up window appear when u click on parameter or after selecting concurrent program

my mistke less concentration

regards and thaks
balkrishna
...
written by Anil Passi , December 14, 2006
When you submit a concurrent program to run, Oracle lets you enter values into its Parameters.

This happens becasue Oracle pops-up a small window that contains all the parameters for the concurrent program being submitted.

That pop-up wnidow is nothing but a descriptive flexfield screen.

Not much emphasis is needed on this topic, as Conc program parameters being Descriptive Flexfields is something that gets taken care of internally by Oracle.


Many Thanks,
Anil Passi
...
written by balkrishna , December 14, 2006
i am not uderstand oracle treat parameter as discriptive field , what it means
please advise
...
written by balkrishna , December 14, 2006
ihave uderstand why oracle consider parameter as discriptive flexfield

what i understand is because pop up window appear when u click on parameter or after selecting concurrent program

my mistke less concentration

regards and thaks
balkrishna
...
written by Anil Passi , December 14, 2006
When you submit a concurrent program to run, Oracle lets you enter values into its Parameters.

This happens becasue Oracle pops-up a small window that contains all the parameters for the concurrent program being submitted.

That pop-up wnidow is nothing but a descriptive flexfield screen.

Not much emphasis is needed on this topic, as Conc program parameters being Descriptive Flexfields is something that gets taken care of internally by Oracle.


Many Thanks,
Anil Passi
...
written by balkrishna , December 14, 2006
i am not uderstand oracle treat parameter as discriptive field , what it means
please advise
...
written by Shabeer.S , January 25, 2007
Hi Anil,

If psbl. can u differentiate the value sets...(7 types).

Regards
Shabeer.S
...
written by shabeer , January 25, 2007
Hi Anil,

Great work yaar.....

Rgds
Shabeer.S
...
written by Anil Passi , January 25, 2007
Hi Shabeer

See this link, its an article contributed by Rakesh

http://getappstraining.blogspot.com/2006/12/value-sets-in-oracle-apps.html

Thanks,
anil Passi
...
written by Shabeer.S , January 25, 2007
Hi Anil,

If psbl. can u differentiate the value sets...(7 types).

Regards
Shabeer.S
...
written by shabeer , January 25, 2007
Hi Anil,

Great work yaar.....

Rgds
Shabeer.S
...
written by Anil Passi , January 25, 2007
Hi Shabeer

See this link, its an article contributed by Rakesh

http://getappstraining.blogspot.com/2006/12/value-sets-in-oracle-apps.html

Thanks,
anil Passi
...
written by Anil Passi , February 02, 2007
Certanily you can.

If your filteration is based on profile option, then I suggest neatest way would be to create a database view which performs the filteration of data[based on profile option etc]. You can then use the database view in the SQL Query of table type value set.

thanks,
anil
...
written by Mayank , February 02, 2007
Comment: Hi Anil Again very good stuff. Just few queries : from where can I read in detail about all the types of value sets?? Oracle Documentation???. I have access to dev environment and know how to create dependent and independent value set but would like to try my hands on other types as well. Also, is it possible to restrict the values of the value set based on Organization (other than the Business Group). Suppose I have two Org under by BG - Org A, and Org B. Both the Orgs have their own Functions (e.g - IT & Technical in Org A) and (HR & Sales in Org B). While creating the position which uses Functions as one of the value sets, is it possible to restrict the value sets based on the Organization if I am using security profile based on Organizations? Thanks Mayank
...
written by Anil Passi , February 08, 2007
Hi Mayank

The steps when using View for Table Type Value set are exactly the same as that for creating VS of table type on table.

Instead of table name , you will specify view name in table name field
see links
http://getappstraining.blogspot.com/2006/12/value-sets-in-oracle-apps.html

http://photos1.blogger.com/x/blogger/1078/3631/1600/874155/valueset_07.gif
...
written by Mayank , February 08, 2007
Hi Anil

Thanks for your response. Could you please give me an example of creating a table dependent value set using a databse view (or let me know where can I read about in detail). Thanks a lot for your help as always!

Regards,
Mayank
...
written by Saily , March 01, 2007
Hi Anil,

This is really a good effort.
I would like to ask one question.
Is there any way by which we can pass multiple values for a parameter using value set.

Regards
Saily
...
written by Anil Passi , March 01, 2007
Hi Saily

I am afraid, by standard practice, you will only pass one value per valueset.

Yes, you can indeed concatenate strings with a delimiter to pass multiple values in one parameter....however there is no reason to take such approach. Please define one parameter per value being passed to concurrent program

thanks
anil
...
written by Vishal , April 27, 2007
hi anil,
thanx for such a nice blog.
i have some confussion...What is the procedure, if we want to create values in independent and dependent valuesets based on database table (for example po_headers_all (po_header_id) as INDEPENDENT value set and po_lines_all (po_line_id) as DEPENDENT valueset)
...
written by Anil Passi , April 29, 2007
hi vishal

in order to achieve your requirement, in the table type value set of po_lines_all, include a where clause such as WHERE po_header_id = :$FLEX$.PO_HEADER_ID_VS_NAME

thanks,
anil
...
written by Sanjai , May 02, 2007
Hi Anil,

Please advice on how to set up
1. Pair valuesets
2. Dependent valuesets

Thanks.
...
written by Anil Passi , May 02, 2007
Please refer to the link below that explains different types of value sets

http://getappstraining.blogspot.com/2006/12/value-sets-in-oracle-apps.html

Thanks,
Anil Passi
...
written by Amit , May 16, 2007
I have to create a value set having Maximum_SIZE =>6,Precision=>2 ,MIN_VALUE=>0.00 and MAX_VALUE=>100.00.

When i am submitting my request then in the Parameter window, if i enter 12.35 then program accept as it is but if i enter 12.356 then it shows an error message stating that U CAN ENTER UPTO 2 DECIMAL VALUES.
OR
After 2 decimal point, it will not allow us to enter any value.

Is it possible with value set?
...
written by Anil Passi , May 27, 2007
Please refer to this link to find info about Special Value Set


http://getappstraining.blogspot.com/2006/12/value-sets-in-oracle-apps.html

Thanks,
Anil
...
written by Shilpad , August 21, 2007
Hi Anil,

Really very helpful site!

I have defined independent valueset (number)with LOV as '1,5,10.15,20,25,30,35,40,45' but while running the report it is showing/sort by first digit/character not by its value as '1,10,15,20,25,30,35,40,45,5', I want to print as I mentioned above, could you please help me in this regard.

Thanks & Regards,
Shilpa
...
written by sandyhs , September 17, 2007
I am unable to see any screen-shots despite hitting 'show-picture'. Any suggestions?
...
written by vizith , September 21, 2007
hii yaar ..
i hav this prob while creating a value set .. wwhich of table type .. here is the query try to helpme out ..

(SELECT DISTINCT transfer_batch,FROM_WAREHOUSE,TO_WAREHOUSE,SCHEDULED_RE LEASE_DATE FROM ic_xfer_mst )

WHERE
TRUNC(SCHEDULED_RELEASE_DATE) BETWEEN
TO_DATE(:$FLEX$.FROM_DATE,'DD/MM/YYYY') AND
TO_DATE(:$FLEX$.TO_DATE,'DD/MM/YYYY')
AND FROM_WAREHOUSE=nvl(:$FLEX$.DV_WHSE,'%')
ORDER BY TRANSFER_BATCH
Custom Message not displaying when validated from Valueset Special
written by Ram Babu , October 08, 2007
Hi Anil,

I followed the steps:

1)From Message Form created message with language US, type ERROR, Message Text and submitted concurrent program Generate Messages.

2)Called this message through a valueset-type Special and event:validate with conditions.

3)Assigned this valueset to a concurrent program parameters to validate.

4) bounced the apache server.
Issue is the Message Name is not calling the actual message text from message form, instead displaying Message Name at runtime.

eg: fnd_message.set_name('ONT','TEST_MSG');
fnd_message.raise_error;

Showing message TEST_MSG at runtime instead of actual message text.
please suggest how to overcome from the issue.

thanks in advance,
Ram

Need an info
written by sarunraj , October 10, 2007
Hi Anil,

First let me appreciate you for the good work that you are doing. Its great!!

I just wanted to know about $FLEX$ and its usage.
Can you give me an example on the same?

Thanks in Advance,
Arun
"Impossible is Nothing"

How to gray out field?
written by Ani , October 15, 2007
Hi Anil,

I've three parameters TYPE,Project and Organization in a report.
Type is either Project or Organization.
If I select Project in Type field the Organization field grays out and Project field gets enabled. Similarly if Organization is selected then Project field grays out and Organization field gets enabled.
Please let me know how can a field be grayed out depending on the values of another field.

Thanks
Anjani
Disable a parameter on submit request
written by PavanR , November 02, 2007
Hi Anil,

How do i disable a parameter2 on submit request, depending on a lov selected value of some other parameter1. Parameter2 is not a LOV. It is a user enterable field.

Please let me know how can i do this.

Thanks
Pavan
...
written by Anil Passi , November 02, 2007
Hi Pavan

You will have to attach a value set to both those parameter. Without that, it isn't possible to have dependent segments in Descriptive Flexfields

Thanks
Anil Passi
Help to create valueset with table type
written by hisham , November 15, 2007
hi anil i saw ur doc for creating independent value set type. Can u pls help to create a value set of table .Its urgent
Help needed in selecting distinct values in a Value set
written by Pradeep_B , November 27, 2007
Hi Anil,
First of all, thanx for such an informative article n u guys rock.
well, I am in a situation where i need only distinct values from a column in my value set and i dont want to create a view for it. How do i go about it? I checked some other forums and they mentioned to add a select statement with distinct in the Table Name field, but that somehow didnt seem to work very well.. is there any other way to do so and if not may be i am doing something wrong while entering the select statement. whatever the case may be, i would greatly appreciate if you can throw some light to it.
Thanx,
Pradeep.B
Need Help In Diableing Or Enabling The Parameters
written by Hari Babu , November 28, 2007
Hi,
I have tow parameters. Depending on the first parameter value the scond parameter should be in disabled or enabled. How can we achieve this?

Help needed in selecting distinct values in a Value set
written by Sandip Jadhav , December 15, 2007
Hi Pradip,

This is regarding distinct values in LOV.

At the time of creating LOV, in table name write query like this e.g. (SELECT DISTINCT element_name,reporting_name FROM PAY_ELEMENT_TYPES_F_TL) A.

Then in Name and ID, select values like this - A.element_name, A.reporting_name
Write in where clause if u need.

Save it.

This will work.

Cheers,
sandip
Can we use different tables in one value set
written by rameshk , December 28, 2007
Hi anil,
how can we change the valueset based on the previous value.

Example
if value of previous valueset is null then
populate the value form X table with different where clause
else
populate the value from y Table with different where clause


Please update me if we have a wayout to get this resolved.
regards
Ramesh
Ultimate Notes
written by Pradeep Agarwal , January 09, 2008
Hi Anil,

First of all, thanx for such an informative Article abount value set.it was very good artcle.
Anil can u help me abt concurrent program in oracle apps.actually i m a ERP fresher in this field.
Please help me ASAP.

Regards

Pradeep Agarwal
table validated valuesets
written by ramya , January 16, 2008
Hi Anil,
thanx a lot for such an informative articles.
can you please help me in creating table validated value sets.

-->in the edit information we have id,meaning and value in the colums frame which columns do we need to specify can you please illustrate with an exapmle.

Regards
Ramya
Urgent ~ Need help Anil :)
written by Richa Kulkarni , January 23, 2008
Hello Anil,
I have interview with a company in another 2 weeks on Oracle Apps. Can you please provide me with the basic questions that can be asked. Also, which things to prepare. Please please help me ...that will be a great help from you smilies/smiley.gif
Thanks
Richa
can i change the value of the parameter on each run of concurrent program
written by sumit verma , February 12, 2008
i want to attech one value set to a parameter which takes two values say A and B on each run of the concurrent program. this value should change on the ever next run of the progarm. tat is if for now it is running for A the next it should run for B. how can i degine this value set. And values A and B are constant type
...
written by kzaffar , February 26, 2008
Hi Anil,

What are (Pair, translatable dependent , transalable independent )validation types
error while running the concurrent program
written by jyotsna , March 04, 2008
I followed the above steps mentioned in your tutorial.
I was able to run it without thevalue sets ok. Record got inserted in the table. But when I run this request with value set, why do I get this error ?
I get this error :

XX_HELLOWORLD module: XX Hello World
---------------------------------------------------------------------------

Current system time is 04-MAR-2008 14:10:24

---------------------------------------------------------------------------

**Starts**04-MAR-2008 14:10:25
ORACLE error 6550 in FDPSTP

Cause: FDPSTP failed due to ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'XX_REGISTER_USER_PRC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.
dynamic report parameter
written by saurav , March 13, 2008
I have 2 parameters.

1)1st parameter has independent value set and it will have only 2 possible values 'week' or 'month'. My 1st value set name is xx_period_type

2)2nd parameter has value set which should display the
'weekno-year'of last 5 years if parameter1 value passed is 'week'.

3)2nd parameter value set should display the 'month-year' of last 5 years if parameter1 value passed is 'month'.

My code for 2nd list of value is :-

(SELECT DECODE(:parameter1,'week',(TO_CHAR(SYSDATE -((LEVEL-1)*7) ,'ww-YYYY')),TO_CHAR(ADD_MONTHS(SYSDATE,-LEVEL 1),'Month-YYYY')) week_month FROM dual CONNECT BY LEVEL< DECODE(:parameter1,'week',52,12))

This code is working fine from sqlplus on oracle client and even if I hardcode 'week'at the place of :parameter1 the value set is working fine in concurrent program,but
when I used :$FLEX$.xx_period_type at the place of :parameter1
it is giving error APP-FND-00005:INCORRECT ARGUMENTS WERE PASSED TO USER EXIT #MESSAGE_TOKEN in value set.

Thanks in advance.


You must be logged in to a comment. Please register if you do not have an account yet.

busy