11i update for "SQL script to lists all the profile settings (all levels)".

--
-- DESC: List the values of a Profile Option, or all Profile Options for a
--       user, or application.
--

SET heading OFF
COLUMN "Profile" FORMAT A33 word_wrapped
COLUMN "Value" FORMAT A30  word_wrapped
COLUMN "Levl" FORMAT A4
COLUMN "Location" FORMAT A10  word_wrapped
BREAK ON "Profile" ON "Value" ON "Levl"

ACCEPT v_profile PROMPT "Enter a PROFILE substring value to search (default
'ALL PROFILES') : "
ACCEPT v_username PROMPT "Enter a USER / LOCATION or substring to search
(default 'ALL USERS') : "

SELECT 'Querying ' || name || TO_CHAR( sysdate, '" on" dd-Mon-yyyy "for"')
 || 'Profile like ''%' || UPPER( '&&v_profile')
 || '%'' and User / Location like ''%' || UPPER( '&&v_username')   || '%'''
--Showing the query parameters
FROM v$database,   dual
/

SET heading ON
set pagesize 60
set newpage  0
--Now set the column headers on that we have specified above
SELECT pot.user_profile_option_name "Profile"
 , DECODE( a.profile_option_value
          , '1', '1 (may be "Yes")'
          , '2', '2 (may be "No")'
          , a.profile_option_value) "Value"
 , DECODE( a.level_id
          , 10001, 'Site'
          , 10002, 'Appl'
          , 10003, 'Resp'
          , 10004, 'User'
          , '????') "Levl"
 , DECODE( a.level_id
          , 10002, e.application_name
          , 10003, c.responsibility_name
          , 10004, d.user_name
          , '-') "Location"
FROM applsys.fnd_application_tl e
 , applsys.fnd_user d   , applsys.fnd_responsibility_tl c
 , applsys.fnd_profile_option_values a   , applsys.fnd_profile_options b
 , applsys.fnd_profile_options_tl pot
WHERE UPPER( pot.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
 AND pot.profile_option_name = b.profile_option_name
 AND b.application_id = a.application_id (+)
 AND b.profile_option_id = a.profile_option_id (+)
 AND a.level_value = c.responsibility_id (+)
 AND a.level_value = d.user_id (+)   AND a.level_value = e.application_id
(+)
 AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
 OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
 OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
 ORDER BY "Profile", "Levl", "Location", "Value"
/

CLEAR COLUMN

 Version 2.0

  *********************Start of Profiler.SQL
  SET heading OFF

  COLUMN "Profile" FORMAT A35
  COLUMN "Value" FORMAT A30
  COLUMN "Levl" FORMAT A4
  COLUMN "Location" FORMAT A10

  BREAK ON "Profile" ON "Value" ON "Levl"

  ACCEPT v_profile PROMPT "Enter a PROFILE substring value to search (default 'ALL PROFILES') : "
  ACCEPT v_username PROMPT "Enter a USER / LOCATION or substring to search (default 'ALL USERS') : "

  SELECT 'Querying ' || name || TO_CHAR( sysdate, '" on" dd-Mon-yyyy "for"')
  || 'Profile like ''%' || UPPER( '&&v_profile')
  || '%'' and User / Location like ''%' || UPPER( '&&v_username')
  || '%'''
  --Showing the query parameters
  FROM v$database,
  dual
  /

  SET heading ON
  --Now set the column headers on that we have specified above
  SELECT b.user_profile_option_name "Profile"
  , DECODE( a.profile_option_value
  , '1', '1 (may be "Yes")'
  , '2', '2 (may be "No")'
  , a.profile_option_value) "Value"
  , DECODE( a.level_id
  , 10001, 'Site'
  , 10002, 'Appl'
  , 10003, 'Resp'
  , 10004, 'User'
  , '????') "Levl"
  , DECODE( a.level_id
  , 10002, e.application_name
  , 10003, c.responsibility_name
  , 10004, d.user_name
  , '-') "Location"
  FROM applsys.fnd_application e
  , applsys.fnd_user d
  , applsys.fnd_responsibility c
  , applsys.fnd_profile_option_values a
  , applsys.fnd_profile_options b
  WHERE UPPER( b.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
  AND b.application_id = a.application_id (+)
  AND b.profile_option_id = a.profile_option_id (+)
  AND a.level_value = c.responsibility_id (+)
  AND a.level_value = d.user_id (+)
  AND a.level_value = e.application_id (+)
  AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
  OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
  OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
  ORDER BY "Profile", "Levl", "Location", "Value"
  /

CLEAR COLUMN
 

Download Script

Version 1
It asks for a profile name (a wild card will be added to end,
and input will be converted to upper compare).

If profile is valid, BUT NOT assigned then "level" will be
"????".
 

SELECT b.user_profile_option_name profil,
       a.profile_option_value val,
       decode(a.level_id,10001,'Site',
      10002,'Appl',
      10003,'Resp',
      10004,'User',
      '????') Lev,
       decode(a.level_id,10002,e.application_name,
       10003,c.responsibility_name,
       10004,d.user_name,
     '  ') loc
FROM   applsys.fnd_application e,
       applsys.fnd_user d,
       applsys.fnd_responsibility c,
       applsys.fnd_profile_option_values a,
       applsys.fnd_profile_options b
WHERE upper(b.user_profile_option_name) like upper('&profile')||'%'
  AND b.application_id = a.application_id (+)
  AND b.profile_option_id = a.profile_option_id (+)
  AND a.level_value = c.responsibility_id (+)
  AND a.level_value = d.user_id (+)
  AND a.level_value = e.application_id (+)
ORDER BY 1, a.level_id, 4

Return to : Oracle Database, SQL, Application, Programming Tips