Script To List The Values Of A Profile Option At All Levels

Script:

SELECT fpo.profile_option_id, fpot.profile_option_name profile_short_name 
, substr(fpot.user_profile_option_name,1,60) profile_name 
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level 
, substr(DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name 
,10003,fr.responsibility_name, 10004,fu.user_name),1,30) level_value 
, fpov.profile_option_value profile_value 
FROM fnd_profile_option_values fpov 

Instance Migration Scripts - Profile Options

--  Check Profile Option Values

SELECT DECODE (fpov.level_id,

               10001, 'Site',

               10002, 'Appl',

               10003, 'Resp',

               10004, 'User',

               'Unkown'

              ) "Level",

       DECODE (fpov.level_id,

               10002, fa.application_name,

               10003, fr.responsibility_name,

               10004, fu.user_name,

               '-'

              ) "Location",

       fpov.profile_option_value "Value"

  FROM apps.fnd_profile_option_values fpov,

       apps.fnd_profile_options fpo,

       apps.fnd_profile_options_tl fpot,

       apps.fnd_responsibility_tl fr,

       apps.fnd_user fu,

       apps.fnd_application_tl fa

 WHERE fpov.profile_option_id = fpo.profile_option_id

   AND fpo.profile_option_name = fpot.profile_option_name

   AND fpov.level_value = fr.responsibility_id(+)

   AND fpov.level_value = fu.user_id(+)

   AND fpov.level_value = fa.application_id(+)

   AND fpot.user_profile_option_name = 'XYZ:Billing Term Column';

  

-- Profile Queries

SELECT *

  FROM apps.fnd_profile_options_vl

 WHERE profile_option_name IN

          ('XYZ_OOPS_ERROR_HOLD',

           'XYZ_OOPS_HOLD_COMMENT',

           'XYZ_OOPS_HOLD_REL_REASON',

           'XYZ_OOPS_HOLD_REL_COMMENT'

          )