REM
REM DBAToolZ NOTE:
REM This script was obtained from DBAToolZ.com
REM It's configured to work with SQL Directory (SQLDIR).
REM SQLDIR is a utility that allows easy organization and
REM execution of SQL*Plus scripts using user-friendly menu.
REM Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM a_profile_opt_R11i.sql
REM
REM APPS_ADMIN
REM
REM Author:
REM Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM
REM Purpose:
REM
REM Reports Profile Options For Oracle Applications
REM with NOT NULL values, groups by Profile Option Name
REM breaks by SITE, RESPONSIBILITY, APPLICATION, USER
REM Takes Parameter Name as search string
REM Allows you to view ALL possible values for profile
REM including SITE, RESPONSIBILITY, APPLICATION and USER.
REM This is impossible in Oracle Apps GUI mode
REM
REM
REM
REM Usage:
REM a_profile_opt_R11i.sql
REM
REM Example:
REM a_profile_opt_R11i.sql
REM
REM
REM History:
REM 06-01-2000 VMOGILEV Created
REM 10-31-2001 Eric Santos Run in 11i and NLS
REM
REM
clear col
clear breaks
set pages 9000
set lines 132
set verify off
col pov format a45 word_wrapped heading "Profile Value"
col lo format a5 heading "Level"
col lov format a40 heading "Value"
col pon noprint new_value n_pon
col upon noprint new_value n_upon
col sda noprint new_value n_sda
col eda noprint new_value n_eda
col cd noprint new_value n_cd
col cb noprint new_value n_cb
col d format a78 word_wrapped noprint new_value n_d
break on pon skip page
ttitle -
"Creation Date: " n_cd " Created By: " n_cb -
skip 1 -
"Date Active From:" n_sda " To:" n_eda -
skip 1 -
"Profile Option Name: " n_pon -
skip 1 -
"User Profile Name: " n_upon -
skip 1 -
"Profile Description: " -
skip 1 -
n_d -
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'Site' lo
, 'SITE' lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.application_id = fpov.application_id
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10001 /* Site Level */
union all
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'Apps' lo
, fa.application_name lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
, FND_APPLICATION_TL fa
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10002 /* Application Level */
and fpov.level_value = fa.application_id
union all
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'Resp' lo
, frt.responsibility_name lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
, FND_RESPONSIBILITY_TL frt
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and frt.language = Userenv('Lang')
and fpot.language = Userenv('Lang')
and fpov.level_id = 10003 /* Responsibility Level */
and fpov.level_value = frt.responsibility_id
and fpov.level_value_application_id = frt.application_id
union all
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'User' lo
, fu2.user_name lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
, FND_USER fu2
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpov.level_id = 10004 /* User Level */
and fpov.level_value = fu2.user_id
and fpot.language = Userenv('Lang')
order by upon, lo, lov
/
undefine profile_like