Profile Option Value for a given Set of Profiles

This query can give you profile option values for all levels such as site, application, responsibility, user. This could serve as an example of WITH CLAUSE usage. This query could be useful when a user has
1. Two Responsibilities
2. Both responsibilities can execute a given function
3. Behavior of that function is different under different responsibilities. it may be due to profile option setting for his/her responsibilities.

WITH pov AS
  ( SELECT fpo.profile_option_name 
         , fpot.user_profile_option_name 
         , fpov.level_id 
         , fpov.level_value
         , fpov.level_value_application_id 
         , fpov.profile_option_value 
      FROM apps.fnd_profile_options       fpo 
         , apps.fnd_profile_options_tl    fpot 
         , apps.fnd_profile_option_values fpov 
     WHERE fpot.profile_option_name = fpo.profile_option_name 
       AND fpot.language            = USERENV('LANG') 
       AND fpo.profile_option_id    = fpov.profile_option_id
       AND fpo.application_id       = fpov.application_id
       and fpo.end_date_active      is null
       /* 
          Add Your condition on profile option name(s)
       */
       AND fpo.profile_option_name LIKE 'ONT%'
       /*
          End of Conditions
       */
  )
SELECT pov.profile_option_name
     , user_profile_option_name
     , 'Level->site: ' 
     , pov.profile_option_value
  FROM pov
     , fnd_application fa
 WHERE fa.application_id = pov.level_value 
   AND pov.level_id = 10001
UNION
SELECT pov.profile_option_name
     , user_profile_option_name
     , 'Level->Application: ' || fa.application_short_name
     , pov.profile_option_value
  FROM pov
     , fnd_application fa
 WHERE fa.application_id = pov.level_value 
   AND pov.level_id = 10002
UNION
SELECT pov.profile_option_name
     , user_profile_option_name
     , 'Level->Responsibility: ' || frt.responsibility_name
     , pov.profile_option_value
  FROM pov
     , apps.fnd_responsibility        fr 
     , apps.fnd_responsibility_tl     frt 
 WHERE pov.level_id = 10003
   AND fr.application_id        = pov.level_value_application_id 
   AND fr.responsibility_id     = pov.level_value 
   AND frt.application_id       = fr.application_id 
   AND frt.responsibility_id    = fr.responsibility_id 
   AND frt.language             = USERENV('LANG') 
UNION
SELECT pov.profile_option_name
     , user_profile_option_name
     , 'Level->User: ' || fu.user_name
     , pov.profile_option_value
  FROM pov
     , fnd_user fu
 WHERE fu.user_id = pov.level_value 
   AND pov.level_id = 10004