#!/usr/bin/ksh
# whatuser_connect ()
#-----------------------------------------------------------------------#
#    Module Name:  whatresp_activity  (c) 2012                          #
#                                                                       #
#    Purpose:      Report on the Application-Responsibility-Language    #
#                  Activity Total-Connections since Installation.       #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   A.Teoh        Module design/creation                 #
#-----------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

        clear
        echo
        echo  " #-------------------------------------------------------#"
        echo  " # whatuser_connect:      Oracle Responsibility 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/whatresp_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 Responsibility-Activity’       -
               LEFT    DATEVAR                                          -
               RIGHT   'Page:'         FORMAT 999 SQL.pno       skip 1  -
               CENTER  'whatresp_activity (Application-Responsibility)  -
               LEFT    TIMEVAR                                  skip 2

        COMPUTE SUM LABEL 'Total' OF ccount ON ccount

	COLUMN	aname	FORMAT  A20	HEADING 'Application Name'
	COLUMN	aresp	FORMAT  A20	HEADING 'Responsibility Name'
	COLUMN	adesc	FORMAT  A20	HEADING 'Responsibility|Description'
	COLUMN	alang	FORMAT  A8	HEADING 'Language'
	COLUMN	acntr	FORMAT  99999	HEADING 'Total|Connects'

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
		FAT.APPLICATION_NAME		aname,
		FRT.RESPONSIBILITY_NAME		aresp,
		FRT.DESCRIPTION			adesc,
		FRT.LANGUAGE			alang,
		COUNT(FRT.RESPONSIBILITY_NAME)	acntr
	  FROM FND_LOGINS                  FL,
	       FND_LOGIN_RESPONSIBILITIES  FLR,
	       FND_RESPONSIBILITY          FR,
	       FND_RESPONSIBILITY_TL       FRT,
	       FND_APPLICATION_TL          FAT
	WHERE  1 = 1
	AND    FL.LOGIN_ID = FLR.LOGIN_ID
	AND    FLR.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
	AND    FR.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
	AND    SYSDATE BETWEEN NVL(FR.START_DATE, SYSDATE - 1)
	       AND NVL(FR.END_DATE, SYSDATE + 1)
	AND    FR.APPLICATION_ID = FAT.APPLICATION_ID
	AND    FRT.LANGUAGE = FAT.LANGUAGE
	AND    FRT.SOURCE_LANG = FAT.SOURCE_LANG
	--AND  FAT.LANGUAGE = 'US'
	AND    INSTR(UPPER(FAT.APPLICATION_NAME), 'OBSOL') = 0
	GROUP BY FAT.APPLICATION_NAME, FRT.RESPONSIBILITY_NAME,
	         FRT.DESCRIPTION, FRT.LANGUAGE
	ORDER BY FAT.APPLICATION_NAME, FRT.RESPONSIBILITY_NAME,
	         FRT.DESCRIPTION, FRT.LANGUAGE;

EXIT;

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