#!/usr/bin/ksh
#whostrace ()
#------------------------------------------------------------------------------#
#    Module Name:  whostrace                                                   #
#                                                                              #
#    Purpose:      Report on E-Business Users that have TURNED-ON SQLTrace.    #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    11-Feb-2013   J.Saunders    Module 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  " # whosqltrace:  E-Business Users with Turned-ON SQLTrace #"
            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 Password: " 

            stty echo
            echo 
            #------------------------------------------------------------------#
            #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
            #------------------------------------------------------------------#
            trap        2
            trap        3


            clear
        else
            PASSWD=$1
            clear
        fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whostrace$$.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 #- E-Business Suite Users with SQL-Trace Turned-On             -#
        prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN usrnam  FORMAT A11      HEADING 'UserName'
        COLUMN applid  FORMAT 9999999  HEADING 'Appl-ID' 
        COLUMN profid  FORMAT 9999999  HEADING 'Profile-ID'
        COLUMN prlvid  FORMAT 9999999  HEADING 'ProLevelID'
        COLUMN prlval  FORMAT 9999999  HEADING 'Profile|Level'
        COLUMN prupdt  FORMAT A10      HEADING 'Profile|Last Update'
        COLUMN propvl  FORMAT A79      HEADING 'Profile Value'

        TTITLE  CENTER  'Apps Users With SQL-Trace Turned-ON'           skip 1 -
                CENTER  '(Perforamnce MAY Suffer From SQL-Trace)'       skip 2

        SELECT  fu.user_name                            usrnam,
                fpov.application_id                     applid,
                fpov.profile_option_id                  profid,
                fpov.level_id                           prlvid,
                fpov.level_value                        prlval,
                fpov.last_update_date                   prupdt,
                fpov.profile_option_value               propvl 
          FROM  fnd_profile_options_tl fpot,
                fnd_profile_options fpo,
                fnd_profile_option_values fpov,
                fnd_user fu
        WHERE   fpot.user_profile_option_name LIKE 
                'Initialization SQL Statement%'
        AND     fpot.profile_option_name = fpo.profile_option_name
        AND     fpot.language = 'US'
        AND     fpo.profile_option_id = fpov.profile_option_id 
        AND     fpo.application_id = fpov.application_id
        AND     fpov.level_id = 10004
        AND     fpov.level_value = fu.user_id 
        UNION ALL 
        SELECT  frt.responsibility_name name, fpov.application_id,
                fpov.profile_option_id, fpov.level_id,
                fpov.level_value, fpov.last_update_date,
                fpov.profile_option_value
        FROM    fnd_profile_options_tl fpot,
                fnd_profile_options fpo,
                fnd_profile_option_values fpov,
                fnd_responsibility_tl frt
        WHERE   fpot.user_profile_option_name LIKE
                'Initialization SQL Statement%'
        AND     fpot.profile_option_name = fpo.profile_option_name
        AND     fpot.language = 'US'
        AND     fpo.profile_option_id = fpov.profile_option_id
        AND     fpo.application_id = fpov.application_id
        AND     fpov.level_id = 10003
        AND     fpov.level_value = frt.responsibility_id
        AND     fpov.level_value_application_id = frt.application_id;


EXIT;
END_FILE

print
print  "Please see  /tmp/whostrace$$.txt"
print
}