Query to Extract Profile Options Enabled at Site / Application / Responsibility / User Level's

Profile’s Enabled at Site Level

SELECT fpot.user_profile_option_name, fpov.profile_option_value

  FROM fnd_profile_option_values fpov,

       fnd_profile_options fpo,

       fnd_profile_options_tl fpot

 WHERE     fpo.profile_option_id = fpov.profile_option_id

       AND fpo.profile_option_name = fpot.profile_option_name

       AND fpot.language = 'US'

       AND fpov.level_id = 10001                              -- Site Level ID

Profile’s Enabled at Application Level

SELECT fat.application_name,

       fpot.user_profile_option_name,

       fpov.profile_option_value

  FROM fnd_profile_option_values fpov,

       fnd_profile_options fpo,

       fnd_profile_options_tl fpot,

       fnd_application_tl fat

 WHERE     fpo.profile_option_id = fpov.profile_option_id

       AND fpo.profile_option_name = fpot.profile_option_name

       AND fpot.language = 'US'

       AND fpov.level_value = fat.application_id

       AND fat.language = 'US'

       AND fpov.level_id = 10002                       -- Application Level ID

       AND UPPER (fat.application_name) LIKE '%HUMAN%RES%'

Profile’s Enabled at Responsibility Level

SELECT frt.responsibility_name,

       fpot.user_profile_option_name,

       fpov.profile_option_value

  FROM fnd_profile_option_values fpov,

       fnd_profile_options fpo,

       fnd_profile_options_tl fpot,

       fnd_responsibility_tl frt

 WHERE     fpo.profile_option_id = fpov.profile_option_id

       AND fpo.profile_option_name = fpot.profile_option_name

       AND fpot.language = 'US'

       AND fpov.level_value = frt.responsibility_id(+)

       AND frt.language = 'US'

       AND fpov.level_id = 10003                     -- Resposibility Level ID

       AND UPPER (frt.responsibility_name) LIKE

              '%XXX IRECRUITMENT RECRUITER%'

Profile’s Enabled at User Level

SELECT fu.user_name, fpot.user_profile_option_name,fpov.profile_option_value

  FROM fnd_profile_option_values fpov,

       fnd_profile_options fpo,

       fnd_profile_options_tl fpot,

       fnd_user fu

 WHERE     fpo.profile_option_id = fpov.profile_option_id

       AND fpo.profile_option_name = fpot.profile_option_name

       AND fpot.language = 'US'

       AND fpov.level_value = fu.user_id

       AND fu.user_name = 'BIJOYJ'

       AND fpov.level_id = 10004  -- User Level ID