#!/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