#!/usr/bin/ksh
#whatprofile_lastupdatedby ()
#-----------------------------------------------------------------------#
#    Module Name:  whatprofile_lastupdatedby   (c) Copyright May 2012   #
#                                                                       #
#    Purpose:      Report on the eBusiness Suite Profile Values.        #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   M.Barone      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  " # whatprofile_lastupdatedby:   Oracle Apps Profiles #"
	    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

	    read PROFNM?"        Enter the Oracle [Profile-Name]: "

	    clear
	else
	    PASSWD=$1
	    clear
	fi


#-----------------------------------------------------------------------#
#  Initialize                                                           #
#-----------------------------------------------------------------------#
	SPOOLNM=/tmp/whatprofile_lastupdatedby_$$.txt

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

	CONNECT  APPS/$PASSWD

       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

	prompt #--------------------------------------------------------#
	prompt #- eBusiness Suite (whatprofile_lastupdatedby)                        -#
	prompt #--------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN upname  FORMAT A14 HEADING 'User|Profile|Name' WORD_WRAPPED
        COLUMN pvname  FORMAT A10 HEADING 'Profile|Name'      WORD_WRAPPED
        COLUMN lvlset  FORMAT A10 HEADING 'Profile|Level'     WORD_WRAPPED
        COLUMN vcntxt  FORMAT A10 HEADING 'Context'           WORD_WRAPPED
        COLUMN vupdat  FORMAT A9  HEADING 'Last|Update|Date'  WORD_WRAPPED
        COLUMN vvalue  FORMAT A14 HEADING 'Profile|Value'     WORD_WRAPPED
        COLUMN vvalue  FORMAT A8  HEADING 'Updated|By'        WORD_WRAPPED

        TTITLE  CENTER  '${ORASID} (${HNAME}) Profile Values'          -
                LEFT    DATEVAR                              skip 1    -
                CENTER  'Sorted by ProfileName'                        -
                LEFT    TIMEVAR                              skip 2

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   	datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  	timevalue,
		ptl.user_profile_option_name  		upname,
		pro.profile_option_name			pvname,
		DECODE(val.level_id,
		10001, 'Site',
		10002, 'Application',
		10003, 'Responsibility',
		10004, 'User',
		10005, 'Server',
		10007, 'SERVRESP',
		'UnDef') 		lvlset,
		DECODE(to_char(val.level_id),
		'10001', '',
		'10002', app.application_short_name,
		'10003', rsp.responsibility_key,
		'10005', svr.node_name,
		'10006', org.name,
		'10004', usr.user_name,
		'10007', 'Serv/resp',
                'UnDef') 				vcntxt,
		TO_CHAR(val.last_update_date,'DD-MON-RR HH24:MI:SS') vupdat,
		val.profile_option_value		vvalue,
		pro.last_updated_by			updtby
	FROM	fnd_profile_options 		pro,
		fnd_profile_option_values	val,
		fnd_profile_options_tl	ptl,
		fnd_user			usr,
		fnd_application		app,
		fnd_responsibility		rsp,
		fnd_nodes			svr,
		hr_operating_units		org
	WHERE	 pro.profile_option_id	= val.profile_option_id (+)
	  AND	 pro.profile_option_name	= ptl.profile_option_name
	  AND	 ptl.language			= 'US'
	  and    upper(ptl.user_profile_option_name) like upper('%${PROFNM}%')
	  and    usr.user_id (+)		= val.level_value
	  and    rsp.application_id (+)	= val.level_value_application_id
	  and    rsp.responsibility_id (+)= val.level_value
	  and    app.application_id (+)	= val.level_value
	  and    svr.node_id (+)		= val.level_value
	  and    org.organization_id (+)	= val.level_value
	ORDER BY 1,3,4;

EXIT;
END_FILE

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