#!/usr/bin/ksh
#whotimeout ()
#------------------------------------------------------------------------------#
#    Module Name:  whotimeout                                                  #
#                                                                              #
#    Purpose:      Report on the ICX Connect-Time, Idle-Time and Time-Out.     #
#                                                                              #
#    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  " # whotimeout:                      Oracle Apps ICX 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
            DATTIM=$(date '+%d-%b-%y')
            clear
        fi


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

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN dflag    FORMAT A7       HEADING 'ICX|Session|Disable'
        COLUMN sttim    FORMAT A10      HEADING 'Start|Date Time'
        COLUMN lstim    FORMAT A10      HEADING 'Last|Activity|Time'
        COLUMN uname    FORMAT A9       HEADING 'User|Name'
        COLUMN idtim    FORMAT 99999.99 HEADING 'Idle|Time|(Minutes}'
        COLUMN tmout    FORMAT A9  HEADING 'ICX|Profile|Idle|Timeout|(Minutes)'
        COLUMN cntim    FORMAT 99999.99 HEADING 'Connect|Time|Total|(Hours}'
        COLUMN tmlim    FORMAT A7  HEADING 'ICX|Profile|Limit|Total|{Hours}'

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps ICX 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,
                user_name                                       uname,
                disabled_flag                                   dflag, 
                to_char(first_connect,'MM/DD/YYYY HH24:MI:SS')  sttim, 
                to_char(last_connect,'MM/DD/YYYY HH24:MI:SS')   lstim, 
                (SYSDATE - last_connect)*24*60                  idtim, 
                fnd_profile.value_specific 
                        ('ICX_SESSION_TIMEOUT', 
                          a.user_id, 
                          a.responsibility_id, 
                          a.responsibility_application_id, 
                          a.org_id, 
                          NULL 
                        )                                       tmout, 
                (SYSDATE - first_connect)*24                    cntim, 
                fnd_profile.value_specific 
                        ('ICX_LIMIT_TIME', 
                          a.user_id, 
                          a.responsibility_id, 
                          a.responsibility_application_id, 
                          a.org_id, 
                          NULL 
                        )                                       tmlim 
        FROM    icx_sessions a,
                fnd_user b 
        WHERE   a.user_id               =  b.user_id 
                and last_connect        >= TO_DATE('${DATTIM}')
        ORDER BY user_name, first_connect;

END_FILE

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