#!/usr/bin/ksh
#whosup_date ()
#------------------------------------------------------------------------------#
#    Module Name:  whosup_date       (c)  Copyright 2012 Michael Barone        #
#                                                                              #
#    Purpose:      Report on the NCA Users/Report Server Oracle Apps 11i:      #
#                     User, Responsibility and FormName.                       #
#                                                                              #
#    Possible Error:  gv$sesstat (Table of View Does Not Exist)                #
#                     Resolution: sysdba: GRANT SELECT on GV_$SESSTAT to APPS; #
#                                                                              #
#                     E-Business Profile 'Sign-On:Audit Level'                 #
#                      must be 'FORM' at the  SITE level.                      #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   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_date:           Oracle Apps ICX and 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 DATIM1?"        User-Connection (FROM) Date DD-MON-YY: "
	    read DATIM2?"        User-Connection (TO)   Date DD-MON-YY: "

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

	    clear
	else
	    PASSWD=$1
	    clear
	fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
	SPOOLNM=/tmp/whosup_date_$$.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_date)                               -#
	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.disabled_flag      ='N'
           AND  icxs.responsibility_id  IS NOT NULL
	   AND	(icxs.last_connect	>=  TO_DATE('$DATIM1','DD-MON-YY'))
	  	 AND
	   	(icxs.last_connect	<=  TO_DATE('$DATIM2','DD-MON-YY'))
	ORDER BY fndu.user_name;

	CLEAR SCREEN
	prompt
	prompt
	prompt #---------------------------------------------------------------#
	prompt #- Forms Connections (whosup_date_date)                        -#
	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	(frm.time		>=  TO_DATE('$DATIM1','DD-MON-YY'))
	  	 AND
	  	(frm.time		<=  TO_DATE('$DATIM2','DD-MON-YY'))
	ORDER BY frm.user_name,
		 frm.user_form_name;

EXIT;
END_FILE

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