5 Replies Latest reply on Jul 24, 2017 1:28 PM by John_K

    Need help in uploading profile option values

    CRF_1000_L-Oracle

      We are building a script to migrate new profile option values from one environment to another. We will be getting the profile option name and the display value (say, Org Name for MO: Default Operating Unit) from the source environment and will have to derive the derived value (Org ID) from the target environment. MO: Default Operating Unit will be just one among the many profile options we need to set. Is there any option to get the derived value for each profile option other than writing derivation query for each profile option? We are looking for automating this activity.

        • 1. Re: Need help in uploading profile option values
          John_K

          The derived value based on your context can be retrieved using fnd_profile.value('PROFILE_VALUE_NAME'). So if you've set your context for a particular user and that user has a profile value set at user level then the call will return that value, otherwise it will work through the precedence (i.e. responsibility, application, ...).

          • 2. Re: Need help in uploading profile option values
            CRF_1000_L-Oracle

            That would not work. We are talking about setting a value at a particular responsibility level. While running the script, the value for the profile option will not be set.

            • 3. Re: Need help in uploading profile option values
              John_K

              If you need to retrieve the value outside of context then use "fnd_profile.value_specific". If you need a specific level such as site or responsibility (irrespective of the user level) then don't pass in the lower level parameters.

              • 4. Re: Need help in uploading profile option values
                CRF_1000_L-Oracle

                Here is a background on what we are trying to achieve.

                 

                We have a test environment in which the functional team will create new responsibilities and set profile options for them. These responsibilities along with the profile options need to be migrated to other environments. Currently this is a manual process and we are trying to automate this through a script. We have automated the responsibility migration, so that the responsibility in the test environment can be migrated to other environments. Now we have to set the profile options for them based on the profile values set in this test environment. We will be receiving the profile option name and values to be set (Displayed value from the profile option LOV) in an excel sheet based on which we have to set the profile option value (derived) through the API 'FND_PROFILE>SAVE' at the responsibility level.

                • 5. Re: Need help in uploading profile option values
                  John_K

                  You don't want to be receiving the display names - that could be anything defined in the LOV. It would be a nightmare to map.

                   

                  Why not just read from fnd_profile_option_values in the source system for whatever responsibilities you want? Then use fnd_profile.save() to load them into the other system?

                  I.e. extract the keys using:

                  Select fpo.profile_option_name, fpov.profile_option_value, frv.responsibility_key
                    From fnd_profile_options fpo, fnd_profile_option_values fpov, fnd_responsibility_vl frv
                  Where fpo.profile_option_id = fpov.profile_option_id
                    And frv.responsibility_id = fpov.level_value
                    And fpov.level_id = 10003
                    And frv.responsibility_name In ('System Administrator');
                  

                   

                  Then in your target environment, generate the load commands using:

                   

                  Select cmd
                    From (With load_this_in_from_excel
                               As (Select fpo.profile_option_name, fpov.profile_option_value, frv.responsibility_key
                                     From fnd_profile_options fpo, fnd_profile_option_values fpov, fnd_responsibility_vl frv
                                    Where fpo.profile_option_id = fpov.profile_option_id
                                      And frv.responsibility_id = fpov.level_value
                                      And fpov.level_id = 10003
                                      And frv.responsibility_name In ('System Administrator'))
                          Select 1 ord, 'declare a boolean; begin ' cmd From Dual
                          Union All
                          Select Rownum ord,
                                    'a:=fnd_profile.save(x_name=>'''
                                 || e.profile_option_name
                                 || ''', x_value=>'''
                                 || e.profile_option_value
                                 || ''', x_level_name=>''RESP'', x_level_value=>'
                                 || fr.responsibility_id
                                 || ', x_level_value_app_id=>'
                                 || fr.application_id
                                 || ');'
                                   cmd
                            From load_this_in_from_excel e, fnd_responsibility fr
                           Where fr.responsibility_key = e.responsibility_key
                          Union All
                          Select 999999999, 'end;' From Dual
                          Order By ord);
                  

                   

                  That way it doesn't matter if the ID's have changed across instances. Obviously you're free to check for the API call failing.