#!/bin/ksh
#whosupaudsid ()
#------------------------------------------------------------------------------#
#    Module Name:  whosupaudsid    (c)  Copyright 2012 Michael Barone          #
#                                                                              #
#    Purpose:      Report on the Java Users/Report Server Oracle AppsTier:     #
#                     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  " # whosupaudsid:     E-Business ICX and AUDSID Connection #"
            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
            DATTIM=$2

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


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whosupaudsid_$$.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 (whosupAUDSID)                              -#
        prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN usernam  FORMAT A10	HEADING 'UserName'          WORD_WRAPPED
        COLUMN respnam  FORMAT A11	HEADING 'Responsi-|bility'  WORD_WRAPPED
        COLUMN modules  FORMAT A11	HEADING 'JavaServ|Module'   WORD_WRAPPED
        COLUMN audsids  FORMAT 9999999	HEADING 'AUDSID'
        COLUMN atprocn  FORMAT A6	HEADING 'AppsTier|Server|Process'
        COLUMN dtprocn  FORMAT A6	HEADING 'DBTier|Server|Process'
        COLUMN oracsid  FORMAT 9999	HEADING 'Data-|base|SID'
        COLUMN oracser  FORMAT 99999	HEADING 'Data-|base|Serial'
        COLUMN lstconn  FORMAT A9	HEADING 'Last|Activity'     WORD_WRAPPED

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps ICX/AUDSID Connections' -
                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 			usernam,
		FNDR.responsibility_name	respnam,
		VSES.module			modules,
		VSES.audsid			audsids,
		VSES.process			atprocn,
		VPRO.spid 			dtprocn,
		VSES.sid			oracsid,
		VSES.serial#			oracser,
		TO_CHAR(ICXS.last_connect, 'DD-MON-YY HH:MI:SS')  lstconn
	FROM	icx_sessions 			ICXS,
		fnd_logins 			FNDL,
		fnd_appl_sessions		FNDA,
		fnd_user 			FNDU,
		fnd_responsibility_tl		FNDR,
		fnd_form_functions		FNDF,
		gv\$process			VPRO,
		gv\$session			VSES
	WHERE	ICXS.disabled_flag 		= 'N'
	  AND	ICXS.login_id 			= FNDL.login_id
	  AND	ICXS.user_id 			= FNDU.user_id
	  AND	FNDL.login_id 			= FNDA.login_id
	  AND	FNDA.audsid 			= VSES.audsid
	  AND	FNDL.pid 			= VPRO.pid
	  AND	FNDL.serial# 			= VPRO.serial#
	  AND	ICXS.responsibility_application_id = FNDR.application_id(+)
	  AND	ICXS.responsibility_id 		= FNDR.responsibility_id(+)
	  AND	ICXS.function_id 		= FNDF.function_id(+)
	  AND	ICXS.last_connect		LIKE UPPER('$DATTIM')
	  AND  FNDR.language(+) = 'US'
	ORDER BY user_name, responsibility_name;

EXIT;
END_FILE

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