#!/bin/ksh
#
# File:      fndpurge.ksh
# Author:    Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
#            Copyright (C) 1999 AppsDBA Consulting
#
# Date:      Unknown
#
# Location:  <applmgr $HOME>/script
#            Set permissions 700
#
# Description:
#            This script runs the Purge Concurrent requests report and 
#            deletes any leftover files, analyzes stats on 
#            fnd_concurrent_processes to speed the query for Concurrent 
#            Manager Administration, purges signon data, and then emails the results. 
#
#            This scripts was formerly called prglgout.ksh
#            This script can be called by cron, the following entry can be used as an
#            example:
#
#            00 06 * * 2 /applmgr6/script/fndpurge > /dev/null 2>&1
#
# Modifications:
#
#   A. Rivenes, 06/25/99, Fixed email section to properly format the email.
#   A. Rivenes, 10/12/99, Added the purge of signon data and renamed to fndpurge.ksh
#
function MAIL_REPORT {
  case $ORACLE_TRACE in
    T)  set -x
        ;;
  esac
  if [ -f /tmp/purglog.txt ] ;
  then
  cat  << END_HEADER | cat - /tmp/purglog.txt | /usr/lib/sendmail ${1}
From: $LOGNAME
Subject: FND Purge Utility

END_HEADER
  fi
  return
}
#
case $ORACLE_TRACE in
  T)  set -x
      ;;
esac
#
# Machine dependent variables
#
APPLMGR_BASE="/u01/app/applmgr"
APPSID="apps"
APPSPWD="apps"
MAILTO="dba@company.com"
RETDT=45
#
set +u
umask 077
#
exec 2>&1 > /tmp/purglog.txt
#
# Insert environment setup scripts here
. $APPLMGR_BASE/script/OADEV
#
# Purge Concurrent manager and/or program data
# 1st Parm: Entity - ALL (Manager,Request data from db & file system), MANAGER, REQUEST
# 2nd Parm: Mode - AGE (Number of days), COUNT (Number of records)
# 3rd Parm: Mode Value - Number of days or Number of records
#
print " "
print "Submitting Purge Concurrent Manager and/or Program Data program"
print " "
#
# Change applsys to apps for Release 10.7+
CONCSUB $APPSID/$APPSPWD \
SYSADMIN "System Administrator" \
SYSADMIN \
WAIT=Y \
CONCURRENT \
 FND FNDCPPUR "ALL" "AGE" $RETDT 
#
# Assumes common log and out files
# Run a file system delete to catch the files the concurrent manager misses
#
print " "
print "Deleting common log and out files the were missed by the "
print "Purge Concurrent Manager and/or Program Data program"
print " "
#
find $APPLCSF/$APPLLOG -mtime +$RETDT -exec rm -f {} \;
find $APPLCSF/$APPLOUT -mtime +$RETDT -exec rm -f {} \;
#
# Release 10.6, improves Concurrent Manager Adminstrate screen query if
#  database is in choose mode
# For later releases, use if statistics are not being generated for the 
# applsys schema, regardless of optimizer mode 
# $ORACLE_HOME/bin/sqlplus /NOLOG << STOP
# CONNECT $APPSID/$APPSPWD
# ANALYZE TABLE fnd_concurrent_processes COMPUTE STATISTICS;
# EXIT;
# STOP
#
# Purge Signon Audit Data
#   In order to avoid date arithmetic in UNIX a SQL script is generated
#   and creates the command file that is then issued to perform the 
#   CONCSUB
#
# Create a SQL script to perform the date arithmetic for the concurrent 
# program submission
cat  << END_HEADER > /tmp/purge.sql 
SET HEAD off;
SET PAGESIZE 9999;
SET TRIMSPOOL on;
SET LINESIZE 132;
SET VERIFY off;
SET FEEDBACK off;
SET ECHO off;
SPOOL /tmp/fndpurge_cmd.lst;
SELECT 'CONCSUB $APPSID/$APPSPWD SYSADMIN "System Administrator" SYSADMIN WAIT=Y CONCURRENT FND \',
       'FNDSCPRG "'||TO_CHAR(SYSDATE-$RETDT,'DD-MON-YY')||'"' FROM dual;
SPOOL off;
END_HEADER
#
# Don't echo the password to the log file
exec 2>&1 > /dev/null
#
# Run the SQL script to create the concurrent program command script
$ORACLE_HOME/bin/sqlplus /NOLOG << STOP
CONNECT $APPSID/$APPSPWD
@/tmp/purge.sql
EXIT;
STOP
#
exec 2>&1 >> /tmp/purglog.txt
#
# Remove the SQL script and submit the CONCSUB to purge the signon data
rm /tmp/purge.sql
chmod 700 /tmp/fndpurge_cmd.lst
#
print " "
print "Submitting Purge Signon Audit data program"
print " "
#
/tmp/fndpurge_cmd.lst
rm /tmp/fndpurge_cmd.lst
#
exec 1<&-
#
# Mail Output
MAIL_REPORT $MAILTO
if [ -f /tmp/purglog.txt ]
then
   rm /tmp/purglog.txt
fi
#
case $ORACLE_TRACE in
  T)  set +x
      ;;
esac
#
# exit