#!/usr/bin/ksh
#whatfndtablespace ()
#------------------------------------------------------------------------------#
# Module Name: whatfndtablespace #
# #
# Purpose: Report on the FND_TABLESPACES #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ -------------------------------------------- #
# 10-Oct-2009 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 " # whatfndtablespace: Oracle FND_TABLESPACES #"
 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
 else
 PASSWD=$1
 clear
 fi

#------------------------------------------------------------------------------#
# Initialize #
#------------------------------------------------------------------------------#
 SPOOLNM=/tmp/whatfndtablespace_$$.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 (whatfndtablespace) -#
 prompt #---------------------------------------------------------------#

 COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
 COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR


 COLUMN tname format A18 HEADING 'Tablespace|Name' WORD_WRAPPED
 COLUMN ttype format A20 HEADING 'Tablespace|Type' WORD_WRAPPED
 COLUMN tcust format A6 HEADING 'Custom|Flag' WORD_WRAPPED
 COLUMN tdate format A11 HEADING 'Last|Update|Date' WORD_WRAPPED
 COLUMN tuser format A12 HEADING 'Last|Update|Login' WORD_WRAPPED

 TTITLE CENTER '${ORASID} (${HNAME}) Apps FND_TABLESPACES ' -
 LEFT DATEVAR skip 1 -
 CENTER 'eBusiness FND_TABLESPACES' -
 LEFT TIMEVAR skip 2

 alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

 SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
 TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
 fndt.tablespace tname,
 fndt.tablespace_type ttype,
 fndt.custom_flag tcust,
 fndt.last_update_date tdate,
 fndu.user_name tuser
 FROM APPLSYS.FND_TABLESPACES fndt,
 APPLSYS.FND_USER fndu
 WHERE fndt.last_update_login = fndu.user_id
 ORDER BY tablespace;

END_FILE

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