Wednesday, April 17, 2013

E-Business Suite: Who is Connected (Internet-Connections and Forms-Connections)


#!/usr/bin/ksh
#whosup ()
#------------------------------------------------------------------------------#
#    Module Name:  whosup                                                      #
#                                                                              #
#    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; #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   M.Barone      Modle 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/whosup_$$.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.disabled_flag      ='N'
           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 (whosup)                                  -#
       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
}

No comments:

Post a Comment