REM RMINC_LogDirectoryProfileOptions.sql
REM Copyright 2006, All Rights Reserved, Reed-Matthews, Inc.

set linesize  180
set pagesize 999
column today new_value _date noprint
select to_char(SYSDATE,'DD-MON-RR') today from dual;

ttitle CENTER 'Log Directory Profile Options'  -
RIGHT _date ' Page:' FORMAT 999 sql.pno SKIP 2 -

Column app_short format a6 heading "APP"
column optname format a40 heading "PROFILE"
column d_level format a15 heading "WHO HAS IT SET"
column optval format a60 heading "SET TO"
column updated format a10 heading "UPDATED ON"
select  distinct
        a.application_short_name app_short,
        user_profile_option_name optname,
        decode(level_id,
        10001,'SITE',
        10002,'APP : '||a2.application_short_name,
        10003,'RESP: '||r.responsibility_key,
        10004,'USER: '||u.user_name,
        'Unknown') d_level,
        profile_option_value optval,
        v.last_update_date updated
from fnd_profile_options_vl o,
        fnd_profile_option_values v,
        fnd_application a,
        fnd_application a2,
        fnd_responsibility r,
        fnd_user u
where (
        o.user_profile_option_name like '%Log%Directory%'
     or o.user_profile_option_name like '%log%directory%'
        )
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
-- Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
order by 2,1,3,4;