Queries Arsenel for Oracle Apps DBA - Part 5

Queries Arsenel for Oracle Apps DBA - Part 5

**********************************************************

PROFILE OPTION

**********************************************************

-- Get Profile option value at site,appl,resp & User level using below query



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

ORDER BY "Profile", "Levl", "Location", "Value"

(+)

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%'));



-- Profile option with modification date and user who changed it:-

SELECT t.user_profile_option_name, profile_option_value,

v.creation_date,

v.last_update_date,

v.creation_date - v.last_update_date "Change Date",

(SELECT UNIQUE user_name

FROM fnd_user

WHERE user_id = v.created_by) "Created By",

(SELECT user_name

FROM fnd_user

WHERE user_id = v.last_updated_by) "Last Update By"

FROM fnd_profile_options o,

fnd_profile_option_values v,

fnd_profile_options_tl t

WHERE o.profile_option_id = v.profile_option_id

AND o.application_id = v.application_id

AND start_date_active <= SYSDATE

AND NVL (end_date_active, SYSDATE) >= SYSDATE

AND o.profile_option_name = t.profile_option_name

AND level_id = 10003 --for resp level, change it to 10001 for site level, 10002 for'Appl' and 10004 for'User'

and v.last_update_date > '10-SEP-2009'

AND t.LANGUAGE IN (SELECT language_code

FROM fnd_languages

WHERE installed_flag = 'B'

UNION

SELECT nls_language

FROM fnd_languages

WHERE installed_flag = 'B')

ORDER BY user_profile_option_name;