#!/usr/bin/ksh
# whatproductinfo ()
#-----------------------------------------------------------------------#
# Module Name: whatproductinfo (c) Copyright 2012 #
# #
# Purpose: Report on the Oracle eBusiness Suite Products. #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ ------------------------------------- #
# 10-May-2012 B.Matthews Module design/creation #
#-----------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
clear
echo
echo " #------------------------------------------------------#"
echo " # whatproductinfo: Oracle eBusiness Products #"
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
clear
ORANAM=${ORASID}
#-----------------------------------------------------------------------#
# Initialize #
#-----------------------------------------------------------------------#
SPOOLNM=/tmp/whatproductinfo_$$.txt
#-----------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#-----------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT APPS/$PASSWD
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 #- What (whatproductinfo) -#
prompt #--------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN sname format A8 HEADING 'App|Short|Name' WORD_WRAPPED
COLUMN aname format A48 HEADING 'Application Name' WORD_WRAPPED
COLUMN stats format A14 HEADING 'Install|Status' WORD_WRAPPED
TTITLE CENTER '${ORASID} (${HNAME}) Apps Product Info' skip 1 -
CENTER 'eBusiness Suite' skip 2
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT UPPER(product_abbreviation) sname,
product_name aname,
'Pseudo' stats
FROM ad_pm_product_info
WHERE pseudo_product_flag <> 'N'
UNION
SELECT decode(nvl(a.APPLICATION_short_name,'Not Found'),
'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id '||to_char(fpi.application_id),
a.APPLICATION_short_name) sname,
b.application_name aname,
decode(fpi.status,'I','Installed','S','Shared',
'N','Inactive',fpi.status) stats
FROM fnd_oracle_userid o,
fnd_application a,
fnd_product_installations fpi,
fnd_application_tl b
WHERE fpi.application_id = a.application_id(+)
AND fpi.oracle_id = o.oracle_id(+)
AND b.application_id=a.application_id
ORDER BY sname;
EXIT;
END_FILE
print
print "Please see /tmp/whatproductinfo_$$.txt"
print
}