#!/bin/ksh
#whatuseractivity_detail ()
#------------------------------------------------------------------------------#
#    Module Name:  whatuseractivity_detail (c)  Copyright 2012 Michael Barone  #
#                                                                              #
#    Purpose:      Report on the Oracle E-Business Suite User Activity         #
#                     User and ICX (Internet-Connections).                     #
#                                                                              #
#    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  " # whatuseractivity_detail:         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

            clear
        else
            PASSWD=$1
            clear
        fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whatuseractivity_detail_$$.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 (whatuseractivity_detail)  +01 to 07 Days   -#
        prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN usernam  FORMAT A20      HEADING 'UserName'          WORD_WRAPPED
        COLUMN userd07  FORMAT 99999    HEADING 'Last|07Days'
        COLUMN userd14  FORMAT 99999    HEADING 'Days 08-14'
        COLUMN userd21  FORMAT 99999    HEADING 'Days 15-21'

        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,
		count(*)                                            userd07
          FROM  fnd_user                fndu,
                fnd_responsibility_vl   fndr,
                icx_sessions            icxs,
                fnd_nodes               fndn
         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  fndn.node_id            = icxs.node_id
           AND  icxs.FIRST_CONNECT      > SYSDATE -07
	GROUP BY fndu.user_name;


        prompt
        prompt
        prompt
        prompt #---------------------------------------------------------------#
        prompt #- ICX Connections (whatuseractivity_detail)  +07 to 14 Days   -#
        prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN usernam  FORMAT A20      HEADING 'UserName'          WORD_WRAPPED
        COLUMN userd07  FORMAT 99999    HEADING 'Last|07Days'
        COLUMN userd14  FORMAT 99999    HEADING 'Last|14Days'
        COLUMN userd21  FORMAT 99999    HEADING 'Last|21Days'

        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,
		count(*)                                            userd14
          FROM  fnd_user                fndu,
                fnd_responsibility_vl   fndr,
                icx_sessions            icxs,
                fnd_nodes               fndn
         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  fndn.node_id            = icxs.node_id
           AND  (icxs.FIRST_CONNECT     > SYSDATE -14
		AND icxs.FIRST_CONNECT  < SYSDATE -07)
	GROUP BY fndu.user_name;


        prompt
        prompt
        prompt
        prompt #---------------------------------------------------------------#
        prompt #- ICX Connections (whatuseractivity_detail)  +14 to 21 Days   -#
        prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN usernam  FORMAT A20      HEADING 'UserName'          WORD_WRAPPED
        COLUMN userd07  FORMAT 99999    HEADING 'Last|07Days'
        COLUMN userd14  FORMAT 99999    HEADING 'Last|14Days'
        COLUMN userd21  FORMAT 99999    HEADING 'Last|21Days'

        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,
		count(*)                                            userd21
          FROM  fnd_user                fndu,
                fnd_responsibility_vl   fndr,
                icx_sessions            icxs,
                fnd_nodes               fndn
         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  fndn.node_id            = icxs.node_id
           AND  (icxs.FIRST_CONNECT     > SYSDATE -21
		AND icxs.FIRST_CONNECT  < SYSDATE -14)
	GROUP BY fndu.user_name;

EXIT;
END_FILE

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