whosid ()
#------------------------------------------------------------------------------#
#    Module Name:  whosid                                                      #
#                                                                              #
#    Purpose:      Report on the NCA Users/Report Server Oracle Apps 11i:      #
#                     User, Responsibility and FormName.                       #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)

        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 1 ]
        then
            ORANAM=$1
        else
            ORANAM=${ORACLE_SID}
        fi

        clear

#------------------------------------------------------------------------------#
#  Initialize Database System-ID (SID)                                         #
#------------------------------------------------------------------------------#
	appsel  ${ORANAM}     > /dev/null
        db920   > /dev/null     ## RapidMagic Function: 8.1.7 Oracle Home     ##
	getpass                 ## RapidMagic Function: Get Passwords         ##

	SPOOLNM=/tmp/whosid_$$.htm

#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  @<<-END_FILE

	CONNECT  APPS/$PWDAPP

	clear	BREAKS
	clear	BUFFER
	clear	COLUMNS
	clear	COMPUTES
	clear	SCREEN
	clear	SQL
	clear	TIMING

        set     markup html     on spool on
        set     pagesize        9000
REM     set     newpage none
	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}
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

	COLUMN usernam	HEADING	'UserName'	 WORD_WRAPPED
	COLUMN shrtnam	HEADING	'Apps|Short|Name'
	COLUMN respnam	HEADING	'Responsibility' WORD_WRAPPED
	COLUMN appserv	HEADING	'Apps|Server|Name'
	COLUMN appproc	HEADING	'Apps|Server|Proc'
	COLUMN sesssid	HEADING	'DBSvr|Server|SID'

        TTITLE  CENTER  '${ORANAM} (${HNAME}) Apps FormServer'		    -
        	LEFT    DATEVAR                    		    skip 1  -
        	CENTER  'Sorted by DBSvr Server SID'			    -	
                LEFT    TIMEVAR					    skip 1  -

	SELECT TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
	       TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
               fu.user_name                    usernam,
               fa.application_short_name       shrtnam,
               fr.responsibility_key           respnam,
               vs.machine                      appserv,
               vs.process                      appproc,
               vs.sid                          sesssid
          FROM v\$session vs,
               v\$process vp,
               applsys.fnd_logins fl,
               icx.icx_sessions icxs,
               applsys.fnd_user fu,
               applsys.fnd_login_responsibilities flr,
               applsys.fnd_responsibility fr,
               applsys.fnd_application fa
         WHERE 1=1
           AND vs.paddr=vp.addr
           AND vp.pid=fl.pid
           AND vp.serial#=fl.serial#
           AND fl.end_time IS NULL
           AND vs.process IS NOT NULL
           AND fl.login_id=icxs.login_id
           AND fl.user_id=fu.user_id
           AND fl.login_id=flr.login_id(+)
           AND (vs.audsid=flr.audsid OR flr.audsid IS NULL)
           AND flr.end_time IS NULL
           AND flr.responsibility_id=fr.responsibility_id(+)
           AND flr.resp_appl_id=fr.application_id(+)
           AND fr.application_id=fa.application_id(+)
	 ORDER   BY vs.sid;

EXIT;
END_FILE

URLNME=$(dirname $FORMS60_MAPPING)
print
print " Please use your WebBrowser to access the [7mWHOSID[0m File:"
print " ${URLNME}/OA_HTML/${ORACLE_SID}_whosid.htm"
print

mv ${SPOOLNM}  ${OA_HTML}/${ORACLE_SID}_whosid.htm

print
}
