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

REM Run this report to see if any Debug or Trace profile options
REM have been set to Y, meaning that they are enabled. Some profile
REM options may be required to be set to Y, but others should be
REM changed back to N once the debugging exercise is completed. 


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

ttitle CENTER 'Debug and Trace Profile Options Set to YES'  -
RIGHT _date ' Page:' FORMAT 999 sql.pno SKIP 2 -

Column app_short format a6 heading "APP"
column optname format a35 heading "PROFILE"
column d_level format a36 heading "WHO HAS IT SET"
column optval format a6 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 '%Debug%' or
        o.user_profile_option_name like '%DEBUG%' or
        o.user_profile_option_name like '%Trace%' or
        o.user_profile_option_name like '%TRACE%'
        )
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
and profile_option_value = 'Y'
order by 2,1,3,4;