#!/bin/ksh
#whosupnode ()
#-----------------------------------------------------------------------#
#    Module Name:  whosupnode (c) Copyright May 2012                    #
#                                                                       #
#    Purpose:      Report on the E-Business Users/Forms                 #
#                  eBusiness User, Responsibility and FormName.         #
#                                                                       #
#                  Forms-Connections will only be displayed/displayable #
#                  when the Profile_Option_Value 'Sign-On:Audit Level'  #
#                  is set to 'FORM' at the SITE level.                  #
#                                                                       #
#    Possible Error:  gv$sesstat (Table or View Does Not Exist)         #
#                     Resolution: DBTier:  sqlplus / as sysdba          #
#                     GRANT SELECT on GV_$SESSTAT to APPS;              #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   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  " # whosupnode:  Oracle Apps Internet 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 DATTIM?"        Optional ReportDate DD-MON-YY: "
 
            if [[ $DATTIM == '' ]]
            then
               DATTIM=$(date '+%d-%b-%y')
            fi
 
            clear
        else
            PASSWD=$1
            clear
        fi
 
 
#-----------------------------------------------------------------------#
#  Initialize                                                           #
#-----------------------------------------------------------------------#
        SPOOLNM=/tmp/whosupnode_$$.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
 
        SPOOL ${SPOOLNM}
 
        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING
 
        set     feedback        on
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        9000
 
        prompt #--------------------------------------------------------#
        prompt #- ICX Connections (whosupnode)                         -#
        prompt #--------------------------------------------------------#
 
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
 
        COLUMN nodenam  FORMAT A10 HEADING 'Server|Name'       WORD_WRAPPED
        COLUMN usernam  FORMAT A10 HEADING 'UserName'          WORD_WRAPPED
        COLUMN userdsc  FORMAT A18 HEADING 'User Description'  WORD_WRAPPED
        COLUMN respnam  FORMAT A18 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 Node-Name Connections' -
                LEFT    DATEVAR                              skip 1 -
                CENTER  'Sorted by NodeName UserName'               -
                LEFT    TIMEVAR                              skip 1
 
 
        SELECT  DISTINCT(fndu.user_name)                     usernam,
                fndu.description                             userdsc,
                fndn.node_name                               nodenam,
                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,
                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.last_connect       LIKE UPPER('$DATTIM')
        ORDER BY fndn.node_name, fndu.user_name;
 
        prompt
        prompt
        prompt #--------------------------------------------------------#
        prompt #- Forms Connections (whos)                             -#
        prompt #--------------------------------------------------------#
 
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
 
        COLUMN usernam  FORMAT A8  HEADING   'UserName'       WORD_WRAPPED
        COLUMN appserv  format A7  HEADING   'Apps|Server|Name'
        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 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,
                ses.machine                             appserv,
                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 HH24:MI:SS') formtim,
                ses.process                             appproc,
                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 ses.machine, frm.user_name, frm.user_form_name;
 
EXIT;
END_FILE
 
print
print  "Please see  /tmp/whosupnode_$$.txt"
print
}
