#!/usr/bin/ksh
# whatsrunning ()
#-----------------------------------------------------------------------#
#    Module Name:  whatsrunning (c) Copyright May 2012                  #
#                                                                       #
#    Purpose:      Report on the Running ConcRequest.                   #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   M.Barone      Module design/creation                 #
#                                                                       #
# FND_CONCURRENT_REQUESTS (Phase_Code):                                 #
#  Completed (C), Pending (P), Running (R), Inactive (P)                #
#                                                                       #
# FND_CONCURRENT_REQUESTS (Status_Code):                                #
#  Cancelled (D)                                                        #
#  Disabled                                                             #
#  Error (E)                                                            #
#  Inactive (I)                                                         #
#  Normal (C), (I) or (R)                                               #
#  No Manager                                                           #
#  Paused (W)                                                           #
#  Resuming   (I or Q)  And Phase_Code = 'R')                           #
#  Scheduled  (I or Q)  And Phase_Code = 'P')                           #
#  Standby    (I or Q)  And Phase_Code = 'P')                           #
#  Suspended  (S), Terminated (X)                                       #
#  Terminating(T)                                                       #
#  Waiting    (Q)                                                       #
#  Warning    (G)                                                       #
#                                                                       #
#  No Manager,    On Hold: Hold_Flag 'Y'                                #
#-----------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
	clear
	echo
	echo  " #--------------------------------------------------------#"
	echo  " # whatsrunning:   Oracle Concurrent Requests (Executing) #"
	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 APPS Password: " 
            
	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/whatconc_$$.txt

        COLUMN  datevalue    NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue    NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  c_stop       NOPRINT 
        COLUMN  c_time       NOPRINT

        COLUMN  c_desc  FORMAT A27      Heading 'Concurrent Pgm Desc' -
                                                        WORD_WRAPPED
        COLUMN  c_rqid  FORMAT 99999999 Heading 'ConcReq'
        COLUMN  c_proc  FORMAT A8       Heading 'OS Proc'
        COLUMN  c_sbmt  FORMAT A9       Heading 'Submit|Time'
        COLUMN  c_wait  FORMAT 9,999.99 Heading 'Wait Time|(Minutes)'
        COLUMN  c_strt  FORMAT A9       Heading 'Start|Time'
        COLUMN  c_time  FORMAT 9,999.99 Heading 'Exec Time|(Minutes)'
        COLUMN  c_stop  FORMAT A9       Heading 'Complete|Time'

        BREAK   ON c_desc               skip 1 ON c_sbmt skip 0

        TTITLE  CENTER  'E-Business Concurrent Requests (Running)'	-
                LEFT    DATEVAR						-
		RIGHT   'Page:'         FORMAT 999 SQL.pno	skip 1	-
                CENTER  'whatsrunning -- Sorted by Start Time' 		-
                LEFT    TIMEVAR                               skip 1  

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                fcp.description                 c_desc,
                fcr.request_id                  c_rqid,
                fcr.os_process_id               c_proc,
                TO_CHAR(requested_start_date,   'DD-MON-YY HH24:MI:SS')
                                                c_sbmt,
                (((actual_start_date) - (requested_start_date)) *(60*24))
                                                c_wait,
                TO_CHAR(actual_start_date,      'DD-MON-YY HH24:MI:SS')
                                                c_strt,
                (((actual_completion_date) - (actual_start_date)) *(60*24))
                                                c_time,
                TO_CHAR(actual_completion_date, 'DD-MON-YY HH24:MI:SS')
                                                c_stop
        FROM    fnd_concurrent_programs_tl fcp,
                fnd_concurrent_requests    fcr
        WHERE	fcp.application_id	= fcr.program_application_id
          AND   DECODE(fcp.language,
		'US', 'AMERICAN',
		'D',  'GERMAN',
		'F',  'FENCH',
		'PTB','BRAZILIAN',
		'E',  'SPANISH')		= fcr.nls_language
          AND fcp.concurrent_program_id = fcr.concurrent_program_id
          AND fcr.phase_code  		= 'R'
        ORDER BY c_strt DESC;

EXIT;

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