whopid ()
#------------------------------------------------------------------------------#
#    Module Name:  whopid                                                      #
#                                                                              #
#    Purpose:      Report on the SQLText Area SQL                              #
#                                                                              #
#    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
                ORD_LNE="PROC.username, SESS.username, PROC.spid, SESS.sid"
                ORD_LNE="${ORD_LNE}, SESS.serial#, SESS.status, SQLA.piece"
                SQL_LNE="AND  PROC.spid = '${1}'    ORDER BY ${ORD_LNE}"
        else
                ORD_LNE="PROC.username, SESS.username, PROC.spid, SESS.sid"
                ORD_LNE="${ORD_LNE}, SESS.serial#, SESS.status, SQLA.piece"
                SQL_LNE="ORDER BY ${ORD_LNE}"
        fi

        clear
        ORANAM=${ORACLE_SID}

#------------------------------------------------------------------------------#
#  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/whopid_$$.htm

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

	CONNECT  SYS/$PWDORA

	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  PROC_username   Heading 'Unix|User'
        COLUMN  SESS_username   Heading 'Oracle|User'
        COLUMN  PROC_spid       Heading 'Unix|ID'
        COLUMN  SESS_sid        Heading 'Oracle|Session'
        COLUMN  SESS_serial#    Heading 'Session|Serial'
        COLUMN  SESS_status     Heading 'Session|Status'
        COLUMN  SESS_terminal   Heading 'User|Terminal'
        COLUMN  SQLA_text       Heading 'SQL Text'      WORD_WRAPPED

        BREAK   ON PROC_username skip 1 ON SESS_username skip 0         -
                ON SESS_status   skip 0 ON SESS_sid      skip 0         -
                ON SESS_serial#  skip 0 ON PROC_spid     skip 1

        TTITLE  CENTER  '${ORANAM} (${HNAME}) SQLText Area'		    -
        	LEFT    DATEVAR                    		    skip 1  -
        	CENTER  'Sorted by Unix-ID'				    -	
                LEFT    TIMEVAR					    skip 1  -

       SELECT  TO_CHAR(sysdate, 'DD-MON-YY')    datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                PROC.username                   PROC_username,
                SESS.username                   SESS_username,
                PROC.spid                       PROC_spid,
                SESS.sid                        SESS_sid,
                SESS.serial#                    SESS_serial#,
                SESS.status                     SESS_status,
                SESS.terminal                   SESS_terminal,
                SUBSTR(SQLA.sql_text, 1, 4000)  SQLA_text
          FROM  v\$process  PROC,
                v\$session  SESS,
                v\$sqltext  SQLA
         WHERE  PROC.addr               = SESS.paddr
           AND  SESS.username   IS NOT    NULL
           AND  SESS.sql_address        = SQLA.address(+)
           AND  SESS.sql_hash_value     = SQLA.hash_value(+)
         ${SQL_LNE};

EXIT;
END_FILE

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

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

print
}
