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, ...).
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.
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.
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.
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.