#!/usr/bin/ksh
# whatuser_activity ()
#-----------------------------------------------------------------------#
#    Module Name:  whatuser_activity  (c) 2012                          #
#                                                                       #
#    Purpose:      Report on the User Connections 30-Days, 60-Days      #
#                  and 90-Days. Also show User Location and Supervisor. #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   F.Bender      Module design/creation                 #
#-----------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

        clear
        echo
        echo  " #-------------------------------------------------------#"
        echo  " # whatuser_activity:          Oracle Apps User Activity #"
        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
        ORANAM=${ORASID}

#-----------------------------------------------------------------------#
#  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     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 /tmp/whatuser_activity_$$.txt

       COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
       COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

       COLUMN  c_stop       NOPRINT
       COLUMN  c_time       NOPRINT

       TTITLE  CENTER  'E-Business Suite User Activity - 30/60/90 Days' -
               LEFT    DATEVAR                                          -
               RIGHT   'Page:'         FORMAT 999 SQL.pno       skip 1  -
               CENTER  'whatuser_activity -- 30/60/90 Days'             -
               LEFT    TIMEVAR                                  skip 2

        COMPUTE SUM LABEL 'Total' OF ccount ON ccount

	COLUMN	uloc	FORMAT  A10	HEADING 'User|Location'
	COLUMN	usup	FORMAT  A10	HEADING 'User|Supervisor'
	COLUMN	uemp	FORMAT  A10	HEADING 'User|EMP-Name'
	COLUMN	uusr	FORMAT  A10	HEADING 'User|Name'
	COLUMN	udes	FORMAT  A10	HEADING 'User|Description'
	COLUMN	udat	FORMAT  A10	HEADING 'User|Start|Date'
	COLUMN	uend	FORMAT  A10	HEADING 'User|End|Date'
	COLUMN	ulog	FORMAT  A10	HEADING 'User|Last|LogOn'
	COLUMN	ud30	FORMAT  A4	HEADING 'Last|30Days'
	COLUMN	ud60	FORMAT  A4	HEADING 'Last|60Days'
	COLUMN	ud90	FORMAT  A4	HEADING 'Last|90Days'

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue
	FROM	dual;

	SELECT
	DISTINCT
	       x.location_code			uloc,
	       x.supervisor_name		usup,
	       x.employee_name			uemp,
	       x.user_name			uusr,
	--     x.responsibility_name		urep,
	--     x.description			udes,
	       x.user_acct_start_date		udat,
	       x.user_acct_end_date		uend,
	       x.user_last_logon_date		ulog,
	       CASE
	         WHEN x.user_last_logon_date < SYSDATE - 30
	          AND x.user_last_logon_date >= SYSDATE - 60
	         THEN 'Y'
	         ELSE NULL
	       END ud30,
	       CASE
	         WHEN x.user_last_logon_date < SYSDATE - 60
	          AND x.user_last_logon_date >= SYSDATE - 90
	         THEN 'Y'
	         ELSE NULL
	       END ud60,
	       CASE
	         WHEN x.user_last_logon_date < SYSDATE - 90
	         THEN 'Y'
	         ELSE NULL
	       END ud90
	  FROM (
	SELECT DISTINCT
	       fu.user_name,
	       fu.description,
	       papf.full_name employee_name,
	       paaf.assignment_id,
	       papf_s.full_name supervisor_name,
	       hl.location_code,
	       fu.start_date user_acct_start_date,
	       fu.end_date user_acct_end_date,
	       fu.last_logon_date user_last_logon_date,
	       frv.responsibility_name
	--       to_char(furg.start_date, 'DD-MON-YYYY') resp_assign_start_date,
	--       to_char(furg.end_date, 'DD-MON-YYYY') resp_assign_end_date
	  FROM apps.fnd_user fu,
	       apps.fnd_user_resp_groups_all furg,
	       apps.fnd_responsibility_vl frv,
	       apps.fnd_application_vl fav,
	       apps.fnd_security_groups fsg,
	       apps.per_all_people_f papf,
	       apps.per_all_assignments_f paaf,
	       apps.per_all_people_f papf_s,
	       apps.hr_locations hl
	 WHERE 1=1
	   AND SYSDATE BETWEEN NVL(fu.start_date, SYSDATE)
	                   AND NVL(fu.end_date, SYSDATE)
	   AND fu.last_logon_date < TRUNC( SYSDATE - 30 )
	   AND fu.user_id=furg.user_id
	   AND furg.responsibility_id=frv.responsibility_id
	   AND furg.responsibility_application_id=fav.application_id
	   AND SYSDATE BETWEEN NVL(furg.start_date, SYSDATE)
	                   AND NVL(furg.end_date, SYSDATE)
	   AND furg.security_group_id=fsg.security_group_id(+)
	   AND fu.employee_id=papf.person_id(+)
	   AND SYSDATE BETWEEN papf.effective_start_date(+)
	                   AND papf.effective_end_date(+)
	   AND papf.person_id=paaf.person_id(+)
	   AND SYSDATE BETWEEN paaf.effective_start_date(+)
	                   AND paaf.effective_end_date(+)
	   AND paaf.supervisor_id=papf_s.person_id(+)
	   AND SYSDATE BETWEEN papf_s.effective_start_date(+)
	                   AND papf_s.effective_end_date(+)
	   AND paaf.location_id=hl.location_id(+)
	      ) x
	ORDER BY
	       CASE
	         WHEN x.user_last_logon_date < SYSDATE - 30
	          AND x.user_last_logon_date >= SYSDATE - 60
	         THEN 1
	         ELSE 0
	       END,
	       CASE
	         WHEN x.user_last_logon_date < SYSDATE - 60
	          AND x.user_last_logon_date >= SYSDATE - 90
	         THEN 2
	         ELSE 0
	       END,
	       CASE
	         WHEN x.user_last_logon_date < SYSDATE - 90
	         THEN 3
	         ELSE 0
	       END,
	       x.location_code,
	       x.supervisor_name,
	       x.employee_name;

EXIT;

END_FILE
print
print "Please see /tmp/whatuser_activity_$$.txt"
print
}