SCRIPTS TO SEE APPLICATION PRODUCT VERSIONS AND PATCH LEVEL.

SQL>
REM =====================================================
REM BEGIN "E-BUSINESS SUITE VERSION INFO" BEGIN
REM =====================================================
SPOOL /tmp/apps_version_info.txt
set linesize 132
set pagesize 132
col NAME format A20
col HOST format A20
col RELEASE format A12
col VERSION format A12
select i.instance_name NAME, i.host_name HOST,
f.release_name RELEASE, i.version VERSION
from v$instance i,fnd_product_groups f
where upper(substr(i.instance_name,1,4)) =
upper(substr(f.applications_system_name,1,4));
SPOOL OFF

SQL>
REM ====================================
REM "BEGIN ATG PATCH VERSION CHECKING"
REM ====================================
SPOOL /tmp/atg_ver_patches.txt
set pagesize 1000;
set linesize 141;
col "BUG#" format A10;
col "PATCHES APPLIED" format A70;
select bug_number "BUG#", decode(bug_number,
3262159, 'Patch 11i.FND.H',
3262919, 'Patch 11i.FWK.H',
3126422, '11.5.9 CU1',
3171663, '11.5.9 CU2',
3140000, '11.5.10 Maintenance Pack',
3240000, '11.5.10 Consolidated Update 1 (CU1)',
3640000, '11.5.10.1 Maintenance Pack',
4017300, '11.5.10 Consolidated Update 1 (CU1) for ATG Product Family',
3460000, '11.5.10 Consolidated Update 2 (CU2)',
3480000, '11.5.10.2 Maintenance Pack',
4125550, '11.5.10 Consolidated Update (CU2) for ATG Product Family',
3438354, 'Patch 11i.ATG_PF.H ',
4334965, '11i.ATG_PF.H Rollup 3',
4676589, '11i.ATG_PF.H.RUP4', 5473858, '11i.ATG_PF.H.RUP5',
5903765, '11i.ATG_PF.H.RUP6'
) "PATCHES APPLIED", LAST_UPDATE_DATE "DATE APPLIED"
from AD_BUGS
where BUG_NUMBER in ('3262919','3262159','3126422','3171633','3140000',
'3240000','3640000','4017300','3460000','3480000',
'4125550','3438354','4334965','4676589','5473858',
'5903765')
order by BUG_NUMBER asc;
SPOOL OFF

SQL>
REM ====================================
REM "BEGIN CHECK AUTOCONFIG/TXK PATCHES"
REM ====================================
SPOOL /tmp/txk_adx.patches.txt
set pagesize 1000;
set linesize 141;
col "BUG#" format A10;
col "PATCHES APPLIED" format A70;
select bug_number "BUG#",
decode(bug_number, 4175764, '11i.ADXE.1 FEB 2005 CUMULATIVE UPDATE (autoconfig)',
3453499, 'Patch 11i.ADX.F (autoconfig)',
2682863, 'TXK (FND) AutoConfig TEMPLATE ROLLUP Patch C (March 2003)',
2757379, 'TXK (FND) AutoConfig TEMPLATE ROLLUP Patch D (June 2003)',
2902755, 'TXK (FND) AutoConfig TEMPLATE ROLLUP Patch E (August 2003)',
3104607, 'TXK (FND) AutoConfig TEMPLATE ROLLUP Patch F (December 2003)',
3239694, 'TXK (FND) AutoConfig TEMPLATE ROLLUP Patch G (February 2004)',
3416234, 'TXK (FND) AUTOCONFIG TEMPLATE ROLLUP PATCH H (May 2004)',
3594604, 'TXK (FND) AUTOCONFIG TEMPLATE ROLLUP PATCH I (October 2004)',
3950067, 'TXK (FND) AUTOCONFIG TEMPLATE ROLLUP PATCH J (FEBRUARY 2005)',
4104924, 'TXK (FND) AUTOCONFIG TEMPLATE ROLLUP PATCH K (July 2005)',
4489303, 'TXK (FND) AUTOCONFIG TEMPLATE ROLLUP PATCH L (SEP 2005)',
4709948, 'TXK (FND) AUTOCONFIG TEMPLATE ROLLUP PATCH M (APRIL 2006)',
5107107, 'TXK (FND/ADX) AUTOCONFIG ROLLUP PATCH N (AUGUST 2006)',
5478710, 'TXK (FND/ADX) AUTOCONFIG ROLLUP PATCH O (December 2006)',
5759055, 'TXK (FND/ADX) AUTOCONFIG ROLLUP PATCH P (MAR/APR 2007)',
5985992, 'TXK (FND/ADX) AUTOCONFIG ROLLUP PATCH Q (JUL/AUG 2007)',
6323691, 'TXK AUTOCONFIG AND TEMPLATES ROLLUP PATCH R (OCT/NOV 2007)',
6372396, 'TXK AUTOCONFIG AND TEMPLATES ROLLUP PATCH S (APRIL/MAY 2008)'
) "PATCHES APPLIED", b.LAST_UPDATE_DATE "DATE APPLIED"
from AD_BUGS b
where b.BUG_NUMBER in ('4175764','3453499','2682863','2757379','2902755',
'3104607','3239694','3416234','3594604','3950067',
'4104924','4489303','4709948','5107107','5478710',
'5759055','5985992','6323691','6372396')
order by bug_number asc;
SPOOL OFF

SQL>
REM =======================================================
REM "BEGIN Extended fnd_nodes query to cover web_host,etc."
REM =======================================================
spool /tmp/fndnodes_oss.txt
set pagesize 50
col node_name format a15
col server_id format a8
col server_address format a15
col platform_code format a4
col webhost format a12
col domain format a20
col virtual_ip format a12
set linesize 132
select node_id, platform_code, support_db D, support_cp C, support_admin A,
support_forms F, support_web W, node_name, server_id,
server_address, domain, webhost, virtual_ip
from fnd_nodes
order by node_id;
SPOOL OFF

SQL>
REM =============================
REM "BEGIN LOGIN PROFILE OPTIONS"
REM =============================

spool /tmp/login_profiles.txt

set pagesize 100;
set linesize 133;
set feedback off;
set verify off;

col profile_option_name format a35
col profile_option_value format a60
col PROF_LEVEL format a30
break on profile_option_name

select p.profile_option_name,
decode(v.level_id,
10001,'SITE',
10002, (select 'App:'||a.application_short_name
from fnd_application a
where a.application_id = v.level_value),
10003, (select 'Resp:'||f.RESPONSIBILITY_KEY
from fnd_responsibility f
where f.responsibility_id = v.level_value),
10004, (select 'User:'||u.user_name
from fnd_user u
where u.user_id = v.level_value),
10005, (select 'Server:'||n.node_name
from fnd_nodes n
where n.node_id = v.level_value),
10006, (select 'Org:'|| o.name
from hr_operating_units o
where o.organization_id = v.level_value),
10007, 'ServResp',
'NOT SET') PROF_LEVEL,
nvl(v.profile_option_value,'NOT SET') profile_option_value
from
fnd_profile_options p,
fnd_profile_option_values v
where
p.profile_option_id = v.profile_option_id (+)
and p.application_id = v.application_id (+)
and upper(p.profile_option_name) in
('APPLICATIONS_HOME_PAGE','APPS_SSO_LOCAL_LOGIN','APPS_SSO')
order by 1, v.level_id;
SPOOL OFF

SQL>
REM ===================================================
REM Shows us the values of all web like profile options
REM ===================================================
SPOOL /tmp/web_urls.txt
set linesize 132
set pagesize 132
col NAME format A25
col LEVEL_SET format a15
col CONTEXT format a10
col VALUE format A20 wrap
col Server format a10
col resp format a8 wrap
col application format a10
break on NAME
select n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Organization',
10007, 'ServResp',
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', 'depends=',
v.level_id) "CONTEXT",
v.profile_option_value VALUE,
(select n.node_name
from
fnd_nodes n
where
n.node_id=level_value2) Server,
decode(v.LEVEL_VALUE,
-1, 'Default',
rsp.responsibility_key) Resp,
decode(LEVEL_VALUE_APPLICATION_ID,
-1, 'Default',
app.application_short_name) Application
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and (upper(v.profile_option_value) like '%HTTP%')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by name, level_set;
SPOOL OFF

3a. From an UNX shell that has the Apps environment sourced on your external middle tier

[ Part I ] - cut and paste the following commands in the shell

#-------------8> Begin Cut here ------------------8> Begin Cut here -------------------------8>
echo "############ BEGIN INFO GATHERING ############" > /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------- Context Info ----------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "[CONTEXT_FILE]: "$CONTEXT_FILE >> /tmp/OS_info.txt
echo "[CONTEXT_NAME]: "$CONTEXT_NAME >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------ Machine Info ------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
uname -a >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------ Limit Info ------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
ulimit -a >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------ DMESG Info ------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
dmesg |grep -i cpu >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
dmesg |grep -i mem >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------- Host Info ----------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
cat /etc/hosts >>/tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------- nsswitch Info ----------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
grep host /etc/nsswitch.conf >>/tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------- fstab Info ----------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
cat /etc/*fstab >>/tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------- df Info [ $IAS_ORACLE_HOME/Apache/Apache/logs ] ----------------" >>
/tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
df -k $IAS_ORACLE_HOME/Apache/Apache/logs >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------ Apache Info -------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------- Java Info ----------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' \
$IAS_ORACLE_HOME/Apache/Apache/bin/java.sh` -version 2>> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "------------- Forms Info ----------------" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "[FORMS60_WEB_CONFIG_FILE]: " $FORMS60_WEB_CONFIG_FILE >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
$ORACLE_HOME/bin/f60run | grep "Runtime" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
echo "############ END INFO GATHERING ############" >> /tmp/OS_info.txt
echo "" >> /tmp/OS_info.txt
#-------------8> End Cut here ------------------8> End Cut here -------------------------8>