#!/usr/bin/ksh
# whatdebug ()
#------------------------------------------------------------------------------#
#    Module Name:  whatdebug                                                   #
#                                                                              #
#    Purpose:      Report on the Debug Options turned "ON" in eBusiness Suite  #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
	clear
	echo
	echo  " #--------------------------------------------------------#"
	echo  " # whatdebug:     Oracle Apps Debug Options Turned  ON    #"
	echo  " #--------------------------------------------------------#"
	echo

	#------------------------------------------------------------------#
	#  Trap:  Set CNTL-C CNTL-D CNTL-\ Before Password Protection      #
	#------------------------------------------------------------------#
	trap        "stty echo; return"     2
	trap        "stty echo; return"     3
	stty -echo

	read PASSWD?" Please Enter the Oracle [7mAPPS[0m Passwd: " 
            
	stty echo
	echo
	#------------------------------------------------------------------#
	#  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
	#------------------------------------------------------------------#
	trap        2
	trap        3

	clear
	ORANAM=${ORASID}
	
#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

	CONNECT  APPS/$PASSWD

	clear	BREAKS
	clear	BUFFER
	clear	COLUMNS
	clear	COMPUTES
	clear	SCREEN
	clear	SQL
	clear	TIMING

	set	feedback	off
	set	flush		on
	set	heading		on
	set	pause		off
	set	space		1
	set	termout		on
	set	verify		on
	set	linesize	80
	set	pagesize	999

        SPOOL /tmp/whatdebug_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  datevalue  NOPRINT NEW_VALUE    DATEVAR
        COLUMN  timevalue  NOPRINT NEW_VALUE    TIMEVAR
        COLUMN  app_nam    FORMAT A5            Heading 'Appl|Short|Name'
        COLUMN  opt_id     FORMAT 9999999       Heading 'Option|ID'
        COLUMN  opt_nam    FORMAT A23           Heading 'Debug Option Name' -
                                                WORD_WRAPPED
        COLUMN  lvl_id     FORMAT A5            Heading 'Debug|Level'
        COLUMN  lvl_usr    FORMAT A14           Heading 'User-Desc'     -
                                                WORD_WRAPPED
        COLUMN  opt_val    FORMAT A20           Heading 'Option Value'  -

        BREAK   ON app_nam        skip 1 ON opt_id skip 0

        TTITLE  CENTER  'Oracle Debug Options Turned "On"'		-
                LEFT    DATEVAR						-
		RIGHT   'Page:'         FORMAT 999 SQL.pno	skip 1	-
                CENTER  'whatdebug -- Apps Short Name' 			-
                LEFT    TIMEVAR                                 skip 2 

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
        	FAP.application_short_name              app_nam,
                OPT.profile_option_id                   opt_id,
                OPT.profile_option_name                 opt_nam,
                DECODE(VAL.level_id,    10001, 'Site',
                                        10002, 'Appl',
                                        10003, 'Resp',
                                        10004, 'User',
                                        10005, 'Srvr',
                                        10006, 'Org ',
                        VAL.level_id)                   lvl_id,
                FUS.description                         lvl_usr,
                VAL.profile_option_value                opt_val
        FROM    applsys.fnd_application                 FAP,
                applsys.fnd_user                        FUS,
                applsys.fnd_profile_options             OPT,
                applsys.fnd_profile_option_values       VAL
        WHERE   FAP.application_id      = OPT.application_id
        AND     OPT.application_id      = VAL.application_id
        AND     OPT.profile_option_id   = VAL.profile_option_id
        AND     VAL.level_value         = FUS.user_id (+)
        AND     (OPT.profile_option_name  like  UPPER('%debug%')
           OR   OPT.profile_option_name   like  LOWER('%debug%')
           OR   VAL.profile_option_value  like  UPPER('%debug%')
           OR   VAL.profile_option_value  like  LOWER('%debug%'))
        AND     VAL.profile_option_value  NOT IN ('0', 'N')
        ORDER BY  FAP.application_short_name,
                  OPT.profile_option_id,
                  VAL.level_id;

EXIT;

END_FILE
print
print "Please see /tmp/whatdebug_$$.txt"
print
}
