Monday, July 4, 2011

Value Set


Define:
Set of values

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.
For Example: when you register in any websites it will ask for Country, City,
If the designer provides a free text box like this..

Then if user enters like this which is wrong and it will get stored into database.So to avoid this oracle provides a feature called Value sets .So designer can create a value set naming “Country”  and having values like India ,USA ,UK ,Australia , Srilanka etc
The navigation path: Application Developer :-  Application :- Validation :- Sets
 

Important Tables in Value Set:
1) select * from FND_FLEX_VALUE_SETS;
This table stores the value set names and each value set can be identified by a primary key FLEX_VALUE_SET_ID.
NOTE: Displayed only important columns :

FLEX_VALUE_SET_ID
FLEX_VALUE_SET_NAME
DESCRIPTION
WHO COLUMNS
VALIDATION TYPE
1017586
XX_COUNTRY
Country Names
************
++++++++++++




***********: WHO columns are used to track the information about who updated or inserted by the data against the tables.
  1. created_by ( User Who created the value set)
  2. creation_date ( value set creation date)
  3. last_update_date
  4. last_updated_by
  5. last_update_login
+++++++:Validation Types
1. None (not validated at all) ---------------------------‘N’
2. Independent --------------------------------------------- ‘I’
3. Dependent ----------------------------------------------- ‘D’
4. Table ------------------------------------------------------- ‘F’
5. Special (advanced) -------------------------------------‘U’
6. Pair (advanced) ------------------------------------------‘P’
7. Translatable Independent ---------------------------- ‘Y’
8. Translatable Dependent-------------------------------‘X’
To know more about this table refer

2) Select * from FND_FLEX_VALUES where flex_value_set_id=1017586

FLEX_VALUE_SET_ID
FLEX_VALUE_ID
FLEX_VALUE
WHO COLUMNS
1017586
20137
India
************
1017586
20138
China
1017586
20139
Australia

For each value there Is a primary key FLEX_VALUE_ID.

SELECT
 FFVS.FLEX_VALUE_SET_NAME, FFV.FLEX_VALUE
FROM
FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_VALUES FFV
WHERE
FFVS.FLEX_VALUE_SET_ID=FFV.FLEX_VALUE_SET_ID

To create a value set and adding values in it… please refer the below link

Flex value description can be found on FND_FLEX_VALUES_VL table.

TYPES OF VALIDATION

1)  None (not validated at all)
2)  Independent
  • Provides a predefined list of values.
  • Independent values are stored in an Oracle Application Object Library table.
Independent Value Set Creation
Responsibility sysadmin-> Application -> Validation -> ValueSet

This Form Information gets stored in FND_FLEX_VALUE_SETS.


Values Stored
Responsibility sysadmin-> Application -> Validation -> Values


 This Table Values stored in FND_FLEX_VALUES table.


 3) 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.
  • Advisable to create your independent values first.
  • Must create at least one dependent value for each independent value, or else it wont allow you to enter into that segment or field.
  • ex: Country, State, District Here district can be filtered by state and state can be filtered by country.
Scenario:
     3 countries (XX_SAMPLE_INDEPENDENT) (Type: Independent)
  • India
  • China
  • Australia
Create a Dependent value Set XX_SAMPLE_STATES

Click Edit Information


It shows that the States are dependent on the Above value set.
Now we going to set the state values for each country like below

Country
States
India


Kerala

Tamil Nadu
China


Beijing

Hainan

Hebel
Australia


Adelaide

Brisbane

Kingston




Press down arrow key to go for next country in the Independent Value Field

Press down arrow key to go for next country in the Independent Value Field

 Save...So Now you Created a dependent Value Set and you can use it for any concurrent programs.
Youe parameter window looks like this


I Selected India as Country
Now I will click the States Parameter field

This query might be a helpful one.
select ffv.flex_value_set_name,ffvs.flex_value,ffv.validation_type
from   fnd_flex_value_sets ffv,
       fnd_flex_values ffvs
where  ffv.flex_value_set_name like '%XX_SAMPLE%'
AND    ffv.flex_value_set_id=ffvs.flex_value_set_id;

 dependent Value Set Done.

4) Table
Table type value set is often used by all technical consultants.

  • It use your own application tables as value sets for flex field segments and report parameters instead of the special values tables which Oracle Applications provides.
  • You can also use validation tables with other special arguments to make your segments depend on profile options or field values.
  • You can use any existing application table, view, or synonym as a validation table.
  • If we are using non registered table for your value set, then we have to Create the necessary grants and synonyms to APPS Schema.
  • The value column and the defined ID column in the table must return a unique row for a given value or ID.
  • If the Hidden Id column is provided the value passed to the report will be Hidden and not the Value column.
  • Similarly, when you specify :$FLEX$.Value_Set_Name, your flex field segment or report parameter defaults to always use the hidden ID column to compare with your WHERE clause .
  • We can use Special BIND variable such as :$PROFILES$.Option_name, :$FLEX$.Value_set_name, :block.field in the WHERE clause.
For More Information On Value Sets

Thank you

4 comments:

  1. Thanks. Got some idea about this after google search.

    ReplyDelete
  2. I have read your blog and I got very useful and knowledgeable information from your blog. It’s really a very nice article. We are also providing the best services click on below links to visit our website.

    Oracle Fusion HCM Training
    Workday Training
    Okta Training
    Palo Alto Training
    Adobe Analytics Training

    ReplyDelete