#!/bin/ksh
#whosupnode ()
#-----------------------------------------------------------------------#
#    Module Name:  whosup     (c) Copyright May 2012                    #
#                                                                       #
#    Purpose:      Report on the NCA Users/Report Server Oracle         #
#                  eBusiness User, Responsibility and FormName.         #
#                                                                       #
#                  Forms-Connections will only be displayed/displayable #
#                  when the Profile_Option_Value 'Sign-On:Audit Level'  #
#                  is set to 'FORM' at the SITE level.                  #
#                                                                       #
#    Possible Error:  gv$sesstat (Table or View Does Not Exist)         #
#                     Resolution: DBTier:  sqlplus / as sysdba          #
#                     GRANT SELECT on GV_$SESSTAT to APPS;              #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   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  " # whosup:                   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 APPS Passwd:  "

	    stty echo
	    echo
	    #------------------------------------------------------------#
	    #  Trap: Reset CNTL-C CNTL-D CNTL-\After Password Protection #
	    #------------------------------------------------------------#
	    trap        2
	    trap        3

	    read DATTIM?"        Optional ReportDate DD-MON-YY: "

	    if [[ $DATTIM == '' ]]
	    then
	       DATTIM=$(date '+%d-%b-%y')
	    fi

	    clear
	else
	    PASSWD=$1
	    clear
	fi


#-----------------------------------------------------------------------#
#  Initialize                                                           #
#-----------------------------------------------------------------------#
	SPOOLNM=/tmp/whosupnode_$$.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 #- ICX Connections (whosup)                             -#
	prompt #--------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN usernam  FORMAT A10 HEADING 'UserName'          WORD_WRAPPED
        COLUMN userdsc  FORMAT A18 HEADING 'User Description'  WORD_WRAPPED
        COLUMN respnam  FORMAT A30 HEADING 'Responsibility'    WORD_WRAPPED
        COLUMN fstconn  FORMAT A9  HEADING 'ICX|Connect'       WORD_WRAPPED
        COLUMN lstconn  FORMAT A9  HEADING 'Last|ICX|Activity' WORD_WRAPPED

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps ICX Connections' -
                LEFT    DATEVAR                              skip 1 -
                CENTER  'Sorted by UserName'                        -
                LEFT    TIMEVAR                              skip 1


        SELECT  DISTINCT(fndu.user_name)                     usernam,
                fndu.description                             userdsc,
                SUBSTR(fndr.responsibility_name,1,55)        respnam,
                TO_CHAR(icxs.FIRST_CONNECT, 'DD-MON-YY HH24:MI:SS') fstconn,
                TO_CHAR(icxs.LAST_CONNECT, 'DD-MON-YY HH24:MI:SS')  lstconn,
                TO_CHAR(sysdate, 'DD-MON-YY')                datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')               timevalue
          FROM  fnd_user                fndu,
                fnd_responsibility_vl   fndr,
                icx_sessions            icxs
         WHERE  fndu.user_id            = icxs.user_id
           AND  fndr.responsibility_id  = icxs.responsibility_id
           AND  icxs.responsibility_id  IS NOT NULL
           AND  icxs.last_connect       LIKE UPPER('$DATTIM')
	ORDER BY fndu.user_name;

	CLEAR SCREEN
	prompt
	prompt
	prompt #--------------------------------------------------------#
	prompt #- Forms Connections (whos)                             -#
	prompt #--------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

	COLUMN usernam	FORMAT A8  HEADING   'UserName'       WORD_WRAPPED
	COLUMN formnam	FORMAT A15 HEADING   'FormName'       WORD_WRAPPED
	COLUMN shrtnam	FORMAT A5  HEADING   'Apps|Short|Name'
	COLUMN respnam	FORMAT A15 HEADING   'Responsibility' WORD_WRAPPED
	COLUMN formtim	format A9  HEADING   'FormStart|Time' WORD_WRAPPED
	COLUMN appproc	format A6  HEADING   'Apps|Server|ProcID'
	COLUMN appserv	format A7  HEADING   'Apps|Server|Name'
	COLUMN faudsid	format 9999999 HEADING  'Form|Server|AUDSID'

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps FormServer'    -
        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,
		frm.user_name				usernam,
		frm.user_form_name			formnam,
		avl.application_short_name 	shrtnam,
		frm.responsibility_name 		respnam,
		TO_CHAR(frm.time, 'DD-MON-YY HH:MI:SS')	formtim,
		ses.process				appproc,
		ses.machine				appserv,
		frm.audsid				faudsid
	  FROM	gv\$sesstat		st1,
		gv\$session 		ses,
		fnd_form_sessions_v	frm,
		fnd_application_vl	avl
	WHERE   st1.sid		= ses.sid
	  AND   st1.inst_id		= ses.inst_id
	  AND   ses.audsid		= frm.audsid
	  AND   avl.application_id	= frm.form_appl_id
	  AND   st1.statistic#	= 9
	  AND	TO_CHAR(frm.time, 'DD-MON-YY')	LIKE UPPER('$DATTIM')
	ORDER BY frm.user_name,
		 frm.user_form_name;

EXIT;
END_FILE

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