SQL script to lists all the profile settings (all levels) (Version 3.0)

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

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.