Search This Blog

Monday, June 9, 2008

Basics of Value Sets in Oracle Applications

Explained below is the basics of Value Set.

What is a value set and where is it used?
Value set is primarily the List of Values(LOV) to restrict and mantain consistencies in entering or selecting the values. It is also the place holders to allow user enter a value. Oracle Application Object Library uses value sets as important components of key flexfields, descriptive flexfields, and Concurrent Request Submission.

What are the Format Types the value set have?
* Character
* Number
* Time
* Standard Date, Standard Date Time
* Date, Date Time
Note that Date and Date Time value set formats are obsolete and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard Date Time.

What are the validation types?

None:

* There is no validation done for this type of value set, hence allows user to enter any value.
Independent:

* It provides a list of pre-defined values. The predefined values are entered seperately.
Dependent:

* Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
* Must define your independent value set before you define the dependent value set that depends on it.
* Must create at least one dependent value for each independent value.
Table:

* The list of value is created based on database tables
* Allows to write simple queries, joins, order by etc
* The value, meaning and ID can be used to display a value, description to the value but return ID to the calling program or screen.
* Additional columns can also be displayed. The syntax is column "column title(size)",.... e.g. order_type "SO Order Type(40)"
* Can also create dependent values to filter LOV data based on parameter value selected earlier. This can be done using :$FLEX:.value_set_name in the where clause.
Special & Pair:
Pair validation value set allows to select a range of concatenated Flex field segments as parameters to the report. The special value set is used to perform special validation. This is used to enter the entire key flexfield segment in the single parameter of the report/calling entity.
Translatable Independent & Translatable Dependent:

* This is similar to Independent and Dependent value set except that translated values can be displayed to the user.

APIs to create value set
The FND_FLEX_VAL_API package can be used to create different types of value sets.
* VALUESET_EXISTS - To check if value set exists
* DELETE_VALUESET - To delete value set. The value set can only be deleted if it is not being referenced by any program or entity.
* CREATE_VALUESET_NONE
* CREATE_VALUESET_INDEPENDENT
* CREATE_VALUESET_DEPENDENT
* CREATE_VALUESET_TABLE
* CREATE_VALUESET_SPECIAL
* CREATE_VALUESET_PAIR

What are the oracle tables that store value set information?
* FND_FLEX_VALUE_SETS
* FND_ID_FLEX_SEGMENTS
* FND_FLEX_VALUE
* FND_FLEX_VALIDATION_EVENTS
* FND_FLEX_VALUE_RULE_LINES
* FND_FLEX_VALUE_RULE
* FND_FLEX_VALUE_RULE_USAGE
* FND_FLEX_VALIDATION_TABLES

19 Comments:

Servane said...

Hi Suresh,

Thanks for your website. I think that is very kind of you to share informations about Oracle Applications. I appreciate that.
Please, keep going !

See you,
Servane

Suresh Vaishya said...

Thanks for your lovely feedback.

Santhosh Tadavai said...

Gud Worked..Really Appreciated..n also Reffered to my frnds...

Suresh Vaishya said...

Thanks .. appreciate that.

Unknown said...

Hi Suresh,

I am very new to Oracle Apps. I have very good information. Thanks a lot and i appreciate it.

Suresh Vaishya said...

Thanks a lot .. appreciate your feedback.

Anonymous said...

Hi Shuresh,
Great website!

I have a question regarding this subject.
I am trying to update a value set attribute with a PLSQL script.
Is there an API I can use, because the only procedure I found is FND_FLEX_VAL_API.Update_Independent_Vset_Value , and it can only update the flex_value, but not the attributes.

Thanks in advance,
Roy.

Anonymous said...

I just found the correct procedure - fnd_flex_values_pkg.UPDATE_ROW

Thanks anyway,
Roy.

Suresh Vaishya said...

Thats good and appreciate you sharing that.


Suresh

Anonymous said...

is there a way to find out where all the value set is being used. scripts, logical apps rules etc. I am new to Oracle have no idea how to fnd out where a value set is getting used, we need to have the logic change to not look at the value set anymore and I am trying to see what all we need to change to make a smooth change, want to make sure it doesn't end up breaking any process. Thanks - AKS

Unknown said...

Dear Suresh,

Can u explain what is the major difference between oracle 11i and R12?

Thanks and Regards,
Venkat

Unknown said...

Hey Suresh, I have a question:
For Value set of type Table
I need Distinct of 3 columns.
Therz no PRimary key for the table.
I gave value, Description.
Checked the check Box ALLOW PARENT VALUES. In the additional columns, I am displaying my third column.
Will this give me distinct of 3 cols?
Or Is it Just Distinct of Value, Description, and just displays third column?

Thanks,
Pam

Suresh Vaishya said...

Please check my post

http://sureshvaishya.blogspot.com/2008/06/display-distinct-value-in-value-set.html

Barbs said...

Hi Suresh

thanks for the valuable information you publish on your site, it has been invaluable to me!

I have a question about mass update of values in value sets. Is there an easy way that I could change all the values in 100 value sets to upper case? We use these values in a program to create our descriptions and they would like to change them all to upper case.

I have been using Fnd_load so far but it will be painful to set this up for all these values and I am not sure how to update existing values.

Would be very grateful for your help.

Barbara Murray

Unknown said...

Dear Suresh,

Can u plz tel me, what is the difference between special valueset and pair valueset?

Thanks and Regards,
Venkat

Anonymous said...

Actually I am searching for the stuf who can explain like you fid.Really thankful to you.

"Knowing the things is an Ordinary but teachihng them to others who dont know is Extrodinary".

ThanQ,
Arun

Anonymous said...

Suresh,
Thanks for elaborating on every aspect of value sets. I appreciate your effort for this and all other topics you have covered

Anonymous said...

Hi suresh,
One value set not shows values in one form A but shows in other form B in same responsibility.

when i browse values from : Application -> Validation - > Values , no values are there in list.

but from form B it shows valid values.

how to use same values in form A.

Anonymous_Abhi said...

Hi, Can I have a value set with out any values attached to it but that has been assigned to a segment in a KFF.

Also is there any other way to include a list of values for a flex structure. For eg, the Item categories flex field has 'PO_ITEM_CATEGORIES' flex structure that is a seeded one. It has value set 'PO_ITEM_CATEGORY'(same name as flex st) assigned to it which is empty (has no values defined). But this flex field obtains values from a category set (Category sets window) defined in PO application. Is this possible and can you explain me the phenomenon taking place in here.

Also, I have a basic question here.
I know 'PO_ITEM_CATEGORIES' is a seeded oracle flex structure defined for PO categories. But how do I check to ensure myself that my PO categories still use the same flex structure to populate values but not a custom one. From the Purchase orders form, is there any way the flex structure for the fields.

Hope I am clear with my questions. Thanks in advance.

Abhishek.

Copyright (c) All rights reserved. Presented by Suresh Vaishya