#!/usr/bin/ksh
#whof60wmx ()
#------------------------------------------------------------------------------#
#    Module Name:  whof60wmx                                                   #
#                                                                              #
#    Purpose:      Report on the F60WebMX (Forms) Processes.                   #
#                                                                              #
#    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)

        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
	if      [ $# = 0 ]
	then
	    clear
	    echo 
	    echo  " #--------------------------------------------------------#"
	    echo  " # whof60wmx:                     Oracle Apps Forms Users #"
	    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
	else
	    PASSWD=$1
	    clear
	fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
	SPOOLNM=/tmp/whof60wmx_$$.txt

#------------------------------------------------------------------------------#
#  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     pagesize        9000
	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 ${SPOOLNM}

        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

	prompt #---------------------------------------------------------------#
	prompt #- F60WEBMX Connections/Sessions                               -#
	prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN unam  FORMAT A10      HEADING 'UserName'		    WORD_WRAPPED
        COLUMN udes  FORMAT A18      HEADING 'User Description'	    WORD_WRAPPED
        COLUMN ufrm  FORMAT A9       HEADING 'User|Form|Name'	    WORD_WRAPPED
        COLUMN ssid  FORMAT 9999     HEADING 'V\$SESS|SID'	    WORD_WRAPPED
        COLUMN sser  FORMAT 999999   HEADING 'V\$SESS|Serial'       WORD_WRAPPED
        COLUMN ppid  FORMAT A6       HEADING 'V\$PROC|PID'	    WORD_WRAPPED
        COLUMN fpid  FORMAT A6       HEADING 'FND|Logins|SPID'      WORD_WRAPPED
        COLUMN fstm  FORMAT A9       HEADING 'FND|Logins|StartTime' WORD_WRAPPED
        COLUMN fetm  FORMAT A9       HEADING 'FND|Logins|EndTime'   WORD_WRAPPED

        TTITLE  CENTER  '[7m${ORASID} (${HNAME}) Apps F60WebMX Details[0m' -
                LEFT    DATEVAR                                     skip 1     -
        	CENTER  'Sorted by UserName'				       -
                LEFT    TIMEVAR                                     skip 2

	SELECT	TO_CHAR(sysdate, 'DD-MON-YY')   		datevalue,
		TO_CHAR(sysdate, 'HH24:MI:SS')  		timevalue,
        	fndu.user_name					unam,
       		fndu.description				udes,
       		vses.sid 					ssid,
		vses.serial#					sser, 
		vpro.spid 					ppid,
		fndl.SPID 					fpid,
		TO_CHAR(fndl.START_TIME,'DD-MON-YY HH:MM:SS')	fstm,
		TO_CHAR(fndl.END_TIME,'DD-MON-YY HH:MM:SS')	fetm
          FROM  fnd_user                fndu,
                fnd_logins		fndl,
                v\$session 		vses,
                v\$process 		vpro
         WHERE  vses.paddr		= vpro.addr
           AND  fndl.pid		= vpro.pid
           AND  fndl.spid		= vses.process
           AND  fndu.user_id 		= fndl.user_id
           AND  (fndu.user_name = 'USER_NAME' OR 1=1)
	ORDER BY fndu.user_name, vses.sid, vses.serial#;

EXIT;
END_FILE

print
print  "Please see  /tmp/whof60wmx_$$.txt"
print
}
