Oracle Application Scripts

Script to collect Apache, Jserv, Forms, Jinitiator and Perl version in E-Business suite R12

# +===========================================================================+

# | FILENAME

# | R12_Instance_Version_Information.sh

# |

# | DESCRIPTION

# |   This script can be used to collect Apache, Jserv, Forms, Jinitiator and  

# |   Perl version in E-Business suite R12

# |

# | NOTES

# |   Before running the script ensure that you have sourced the environment 

# |   by running APPS<SID>_host.env file from $APPL_TOP.  

# |

# +===========================================================================+

#

(

echo "Script Started "

date

## The script shows output on screen and creates file named hostname_date.txt file in current ## directory

## Collect the Apache version

echo "*****Apache Version*****"

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v

echo " "

## Collect perl version

echo "*****perl version******"

$IAS_ORACLE_HOME/perl/bin/perl -v|grep built

echo " "

## Collect Java version

echo "******Java Version******"

sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"

echo " "

## Collect client JRE version

echo "*****Client JRE version*****"

cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35

echo " "

## Collect Forms version

echo "*****Forms Version*****"

$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

echo " "

## Collect PL/SQL Version

echo "*****PL/SQL Version****"

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

echo " "

## Collect Forms communication mode

echo "****Forms Communication Mode****"

cat $FORMS_WEB_CONFIG_FILE|grep serverURL=

echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"

echo " "

echo "Script Completed Successfully and it has generated the file  zz`hostname`_`date +%m%d%y.%H%M`.txt file in current directory"

echo "Script completed "

date

) 2>&1 | tee zz`hostname`_`date +%m%d%y.%H%M`.txt

###

### END OF SCRIPT

###

Wednesday, April 17, 2013

Query to check whether the debug option is enabled in the profile

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,

'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.user_profile_option_name like '%Debug%'
AND pov.profile_option_value='Y'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value;

Query to check the values of the given profile option

The below Query will give the value for the particular PROFILE OPTION what you give as input. For example if you give the profile name as "ICX_FORMS_LAUNCHER" Then it will show the Value of the same.


SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE '%&profile%'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value

Friday, May 25, 2012

Script that will list all Concurrent programs for which trace is enabled – output should be Program Name / Shortname / Application

#Script to list the concurrent programs with trace enabled

output_log=/tmp/practice/log.txt

export output_log

sqlplus -S apps/xxxxxxx << EOF

spool $output_log

set pages 1000 lines 1000

select a.CONCURRENT_PROGRAM_ID, b.USER_CONCURRENT_PROGRAM_NAME "Program_Name", a.CONCURRENT_PROGRAM_NAME "Short_Name", a.APPLICATION_ID

from fnd_concurrent_programs a, fnd_concurrent_programs_tl b

where a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and a.ENABLE_TRACE='Y';

exit;

spool off

EOF

Wednesday, May 9, 2012

Script to disable all scheduled requests - After cloning

update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and (status_code = 'I' OR status_code = 'Q');
and requested_start_date >= SYSDATE
and hold_flag = 'N';