#!/bin/ksh
#whosuprange ()
#------------------------------------------------------------------------------#
#    Module Name:  whosuprange     (c)  Copyright 2012 Michael Barone          #
#                                                                              #
#    Purpose:      Report on the NCA Users/Report Server Oracle E-Business     #
#                     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  " # whosuprange:                   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 FRDATE?"        Optional FROM ReportDate DD-MON-YY: "
            read TODATE?"        Optional TO   ReportDate DD-MON-YY: "

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

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

            clear
        else
            PASSWD=$1
            clear
        fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whosuprange_$$.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}

        prompt #---------------------------------------------------------------#
        prompt #- ICX Connections (whosuprange)                               -#
        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 A24      HEADING 'Responsibility'    WORD_WRAPPED
        COLUMN nodenam  FORMAT A5       HEADING 'Node|Name'         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,
                SUBSTR(fndn.node_name,10,5)                         nodenam,
                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       BETWEEN
		TO_DATE ('$FRDATE', 'DD-MON-YY')
		AND
		TO_DATE ('$TODATE', 'DD-MON-YY')
        ORDER BY fndu.user_name;

        CLEAR SCREEN
        prompt
        prompt
        prompt #---------------------------------------------------------------#
        prompt #- Forms Connections (whosuprange)                             -#
        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	frm.time		BETWEEN
		TO_DATE ('$FRDATE', 'DD-MON-YY')
		AND
		TO_DATE ('$TODATE', 'DD-MON-YY')
        ORDER BY frm.user_name,
                 frm.user_form_name;

EXIT;
END_FILE

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