whatprofilechange
What E-Business Suite Profiles or Profile-Values Changed Since DD-MON-YY.
whatprofilechange uses the Profile and Profile-Value Last_Update_Date
So, if the Profile or the Profile-Value is changed multiple-times, only the latest Last_Update_Date can be shown.
Please Execute whatprofilechange daily, if you are concerned that E-Business Suite Profiles or Profile-Values are changing and impacting your E-Business Suite environment.
Example:
19-JUN-13 OATC (oratest) Profile Values Changed Since
12:00:37 Sorted by ProfileName
Profile Value
User Last Last
Profile Profile Update Update Profile
Name Level Context Date Date Value
--------------------- ---------- ---------- --------- --------- ----------
ICX: Limit connect Site 06-JUL-06 19-JUN-13 1000
ICX: Limit time Site 06-JUL-06 19-JUN-13 480
ICX:Session Timeout Site 08-FEB-08 19-JUN-13 480
ICX:Session Timeout User BDUNHAM 08-FEB-08 19-JUN-13 480
ICX:Session Timeout User MBARONE 08-FEB-08 19-JUN-13 480
#!/usr/bin/ksh
#whatprofilechange ()
#-----------------------------------------------------------------------#
# Module Name: whatprofilechange (c) Copyright 2012 #
# #
# Purpose: Report on the eBusiness Suite Profile Values that #
# have a change-date (LAST_UPDATE_DATE) greater-than #
# or equal-to the argument-date specified. #
# #
# 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 " # whatprofilechange: Oracle Apps Profiles Changed #"
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 Passwd Protection #
#-----------------------------------------------------------#
trap 2
trap 3
read PROFDT?" Enter [Profile-LastChanged DD-MON-YY]: "
clear
else
PASSWD=$1
PROFDT=$2
clear
fi
#-----------------------------------------------------------------------#
# Initialize #
#-----------------------------------------------------------------------#
SPOOLNM=/tmp/whatprofilechange_$$.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 12.1 (whatprofilechange) -#
prompt #--------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN upname FORMAT A21 HEADING 'User|Profile|Name'
COLUMN pvname FORMAT A10 HEADING 'Profile|Name'
COLUMN lvlset FORMAT A10 HEADING 'Profile|Level'
COLUMN vcntxt FORMAT A10 HEADING 'Context'
COLUMN pupdat FORMAT A9 HEADING 'Profile|Last|Update|Date'
COLUMN vupdat FORMAT A9 HEADING 'Value|Last|Update|Date'
COLUMN vvalue FORMAT A16 HEADING 'Profile|Value'
TTITLE CENTER '${ORASID} (${HNAME}) Profile Values Changed' -
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,
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(ptl.last_update_date,'DD-MON-RR') pupdat,
TO_CHAR(val.last_update_date,'DD-MON-RR') vupdat,
val.profile_option_value vvalue
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 ((ptl.last_update_date) >= TO_DATE('${PROFDT}')
or
(val.last_update_date) >= TO_DATE('${PROFDT}'))
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/whatprofilechange_$$.txt"
print
}
No comments:
Post a Comment