Value Sets in oracle apps Creation of user defined value sets

Value Sets :     System provides built-in ( predefined ) value sets
Value set is a list of values with validations which will be used to restrict the user without entering the invalid data in the Parameters
                        value sets are used in two scenarios.
                                    1)Concurrent Program parameters
                                    2)Flex fields

                        These are of 8 types of Validation types
1)      NONE
2)      Dependent
3)      Independent
4)      Table
5)      Translate dependent
6)      Translate Independent
7)      Special
8)      Pair
                        Note :  the 1st four are mostly used.
                                    All pre-defined are NONE type.
Creation of user defined value sets
            These will be created based on some conditions.
1)                  NONE  type
Eg 1:  conditions
1)      only digits ( 0-9)
2)      Max 4 digits
3)      Min  Value : 150
4)      Max Value : 5600

Steps to create value sets        
System Administrator à Application à validation à set






Value St Nme : CUST_4DIG_NUM
Description : Customized value set for 4 digits number ( as desired )
List of Values : list of values  ( we can others such as poplist, long list of values )
                                                pplist à if values are <10
                                                list of values à 10 to 200
                                                long list of values  > 200
            Format Type : Number                                                Maximum Size  : 4 ( digits )    Precession : 4


 ( format Types are
1)      Char 2) Date 3) Date Time 4) Number 5) Standard Date 6) Standard Date Time 7) Number  )
             Numbers only : enable ( 0-9)
             Min Value       : 150   ( as desired )                            Max Value : 5600 ( as desired )
             Validation type : None

 Note :   After define use in the concurrent program, while defining parameters
             Eg 2:  conditions
5)      Max 15 chars
6)      Upper
7)      If user input is number, it should precede with zeros
Value set name : CUST_15CHARS
Description : 15 chars text  ( as desired )
List of Values : list of values
            Format Type : Char                        Max Size : 15      ( max no.of chars)
             Upper Case only : enable ( A-Z)
             Validation type : None

Note : After define use with parameters of concurrent program
2)                  INDEPENDENT
                        Independent  provides the list of values, user has to select the value from the list
                                   
                                    Value set Name  : CUST_DEPT_LIST
                                      List Type : List of Values
                                      Format Type : Number            Max Size : 5
                                      Validation Type : Independent                                                          
                                    Find By Value : select Value Set        Name :  us_dept_list
                                    Click on    find

                      Entering values
            System Administrator à Application à validation à set à Values



Find Value By                                                 Name : CUST_DEPT_LIST
Check Value Set

            Select Find


Enter the values
 Supply the desired values in the value column
Value:                         Translated Value               Description
10
200
500

Note : The above use with parameters of concurrent program



3)                  DEPENDENT  
            Provides list of values but depending on another Independent values
            Eg:
                        Country : India            à City : Hyderabad
                                                                           Banglore
                                                                           Chennai
                                                                          Delhi
                                                                          Mumbai
                        Country : USA             àCity : Dellos
                                                                           California
                                                                           Detroit
                        Country  : UK              à City : London
                                                                            Anderson
                        Report output
                        First Page:
                                                            Employees information
                                                            India   : Hyderabad
                        Second Page :
                        EmpNo            EmpName                   Salary               Comm
                                   
                       
                        Last Page:
                                                Total Employees : 50

            Step1 : create the independent values set ( list = India, US, UK )
                        System Administrator à Application à Validation à Set
            Value Set ( form )
                        Value Set Name : CUST_COUNTRY
                        Description :  List of Countries ( as desired )
                        List of values :            List of values
                        Format type : Char                 Max Value : 50 ( as desired )
                        Validation Type : Independent
                        SAVE THE FORM
                       




Prepare the values :
                        System Administrator à Application à Validationà Values

                        Find By Value : Check Value Set        Name :  CUST_COUNTRY
                                    Click on    find
                                                           
                        Value                           Translated Value        Description     Enabled          
                        INDIA                                     INDIA
                        UK                                          UK
                        US                                           US
Save the above
            Step2 :
            Create the dependent values set
                       
                                    Country : India            à City : Hyderabad
                                                                           Banglore
                                                                           Chennai
                                                                          Delhi
                                                                          Mumbai
            Value Set ( form )
                        Value Set Name : CUST_CITY
                        Description :  List of cities in a country
                        List of values :            List of values
                        Format type : Char                 Max Value : 50 ( as desired )
                        Validation Type : dependent                         
           

Select Edit Information

            Dependent value set information
                        Independent value set : CUST_COUNTRY
                        Dependent Default value : INDIA ( as desired )
                        Description         : Default value
Preparing Values :

            Values
                        Check Value Set
                        Name : CUST_CITY ( auto )
                        1) Independent Value : INDIA ( default,  use down arrow key to list )
                        Select Find


                        Value
                        Hyderabad, Chennai, Banglore, Delhi, Mubai
                        SAVE THE FORM
                        2)  Independent Value :  US
                        Select Find
                             Values ( California, Chicago, Detroit )
                        SAVE THE FORM
                        3) Independent Value : UK ( London, Anderson )

            Use the above dependent and independent value sets in the application.
Note : use the above in Concurrent Program Parameters
                       
           

                                   

4)                  Tables : Values will be provided from the database table
5)                  Translated Independent  and    6) Translated Dependent  à are to display translation values
7)                  Speed  and  8) Pairs à   are to display flex fields data values

TABLE value set
                        Defining table type value set
                        Here values will be obtained from the table ( query )
Eg:
            User Name  à should be obtained from the list
The following should be identified
1) Table Name   à FND_USER
2) Column Name à USER_NAME
3) Data Type      à VARCHAR2
4) Size             à 100
5) Application Name à Application Object Library

1) Table Name
 System Administrator à User à Define

After getting above form Query for all users ( Ctrl + F11 ) ) user down arrow key to move to next user.
Place the cursor in the User Name filed go to Help à Record History  get the Table Name : FND_USER
2) Column Name
            Go to Help à Diagnostics à Examine  à Password : apps
            Get the Column Name : USER_NAME
3) Data Type
            Application Developer à Application à Database à Table
            Table Name : FND_USER ( Query for it )

            Get the Column Type : VARCHAR2
                        Width : 100
                        Application Name : Application Object Library


Creation of Table Value Set
System Administrator à Application à Validation à Set


Value Set Name : CUST_USER_NAMES_TAB
List Type : List of Values
Format Type : Char                  Max Size : 100 ( must be less then or equal to size of the column )
Validation Type :  Table

Select      Edit Information

Table Application : Application Object Library                                   Table Name : FND_USER

Table Columns
                        Name
            Value  : USER_NAME

                                    Where/Order by     :    USER_NAME LIKE    '%USER%'
            Select    Test


Save the above and close the form

Post a Comment