#!/bin/ksh
# whatconc ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconc                                                    #
#                                                                              #
#    Purpose:      Report on the ConcRequest.                                  #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    01-Feb-2007   D.Channell    Module design/creation                        #
#                  J.Sizemore    Module design/creation                        #
#                  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  " # whatconc:  Oracle Apps 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 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/whatconc_$$.txt

	prompt  #--------------------------------------------------------#
	prompt  # whatconc:  Oracle Apps Concurrent Requests (Executing) #
	prompt  #--------------------------------------------------------#
	prompt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  c_stop       NOPRINT
        COLUMN  c_time       NOPRINT

        COLUMN  c_desc  FORMAT A30      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 A8       Heading 'Submit|Time'
        COLUMN  c_wait  FORMAT 9,999.99 Heading 'Wait Time|(Minutes)'
        COLUMN  c_strt  FORMAT A8       Heading 'Start|Time'
        COLUMN  c_time  FORMAT 9,999.99 Heading 'Exec Time|(Minutes)'
        COLUMN  c_stop  FORMAT A8       Heading 'Complete|Time'

        BREAK   ON c_desc               skip 1 ON c_sbmt    skip 0

        TTITLE  CENTER  'Oracle Concurrent Requests (Running)'		-
                LEFT    DATEVAR						-
		RIGHT   'Page:'         FORMAT 999 SQL.pno   skip 1	-
                CENTER  'whatconc -- 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,   'HH24:MI:SS')
                                                c_sbmt,
                (((actual_start_date) - (requested_start_date)) *(60*24))
                                                c_wait,
                TO_CHAR(actual_start_date,      'HH24:MI:SS')
                                                c_strt,
                (((actual_completion_date) - (actual_start_date)) *(60*24))
                                                c_time,
                TO_CHAR(actual_completion_date, 'HH24:MI:SS')
                                                c_stop
        FROM    fnd_concurrent_programs_tl fcp,
                fnd_concurrent_requests    fcr
        WHERE	 fcp.application_id        = fcr.program_application_id
          AND   fcp.application_id        = fcr.program_application_id
          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/whatconc_$$.txt"
print
}
