SQL> @a_profile_opt_R11i.sql
Enter value for profile_like: Utili%Diag%
Creation Date: 27-DEC-99 Created By: AUTOINSTALL
Date Active From: 01-JAN-80 To:
Profile Option Name: DIAGNOSTICS
User Profile Name: Utilities:Diagnostics
Profile Description:
Value determines whether diagnostic utilities, such as Examine, may be used
Level Value Profile Value
------- -------------- ----------
Site SITE N
User JJOHN Y
Code from the Oracle document:
Program
-------
- - - - - - - - - - - Code begins here - - - - -- - - - - - - - -
rem
rem File: a_profile_opt_R11i.sql
rem Created: Vitaliy Mogilevskiy (vit100gain@earthlink.net)
rem Modified: Eric Santos, Run in 11i and NLS
rem Desc: 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
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
ttitle off
- - - - - - - - - - - - Code ends here - - - - - - - - - - - -
No comments:
Post a Comment