#!/usr/bin/ksh
# whattrace ()
#------------------------------------------------------------------------------#
#    Module Name:  whattrace                                                   #
#                                                                              #
#    Purpose:      Report on the ConcPgms with TRACE enabled.                  #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
	clear
	echo
	echo  " #--------------------------------------------------------#"
	echo  " # whattrace:  Oracle Apps Concurrent Pgm (Trace-Enabled) #"
	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/whattrace_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  c_stop       NOPRINT 
        COLUMN  c_time       NOPRINT

        COLUMN  c_name  FORMAT A15      Heading 'ConcPgm|ShortName' -
                                                        WORD_WRAPPED
        COLUMN  c_desc  FORMAT A48      Heading 'Concurrent Pgm Desc' -
                                                        WORD_WRAPPED
        COLUMN  c_enab  FORMAT A7       Heading 'ConcPgm|Enabled'
        COLUMN  c_trce  FORMAT A7       Heading 'ConcPgm|Trace|Enabled'

        TTITLE  CENTER  'Oracle Concurrent Pgms (Trace-Enabled)'	-
                LEFT    DATEVAR						-
		RIGHT   'Page:'         FORMAT 999 SQL.pno	skip 1	-
                CENTER  'whattrace -- Sorted by ConcPgmDescription' 	-
                LEFT    TIMEVAR                                 skip 1  

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                fcp.concurrent_program_name	c_name,
                ftl.description                 c_desc,
                fcp.enabled_flag		c_enab,
                fcp.enable_trace		c_trce
          FROM	fnd_concurrent_programs    fcp,
        	fnd_concurrent_programs_tl ftl
        WHERE	fcp.concurrent_program_id = ftl.concurrent_program_id
          AND	fcp.application_id	  = ftl.application_id
          AND	fcp.enable_trace <> 'N'
        ORDER BY c_desc;

EXIT;

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