#!/bin/bash
#
# Script to bounce the Oracle Workflow Notification Mailer.
# Author: Abdul Muqeet
#
# This script stops the mailer, waits for it to stop, and then starts it again.
# It captures all its output, formats it as an HTML report, and sends it as an
# attachment via email using uuencode and mailx.
#
# Usage: ./bounce_workflow_mailer.sh <path_to_EBSapps.env> <username> <password>
# Example: ./bounce_workflow_mailer.sh /u01/app/oracle/EBSapps/appl/EBSapps.env apps apps_password
#
# IMPORTANT:
# - Ensure the FND_SVC_COMPONENTS table has a record for 'Workflow Notification Mailer'
# and that the user has execute privileges on FND_SVC_COMPONENT package.
# - The 'mailx' and 'uuencode' command-line tools must be installed and configured on the server.
# On some systems, 'mailx' might be named 'mail'.
# --- Email Configuration (Edit these values) ---
RECIPIENT_EMAIL="your_email@example.com"
SENDER_EMAIL="oracle@yourserver.com"
SUBJECT_LINE="Oracle Workflow Mailer Bounce Report"
ATTACHMENT_NAME="bounce_report.html"
# --- Script Configuration ---
# Sleep interval between status checks in seconds
SLEEP_INTERVAL=10
# Timeout for stop/start operations in seconds
TIMEOUT=300
# --- Input Validation ---
# Check if all required arguments are provided
if [ -z "$1" ] || [ -z "$2" ] || [ -z "$3" ]; then
echo "Error: Missing command-line arguments."
echo "Usage: $0 <path_to_EBSapps.env> <username> <password>"
exit 1
fi
EBS_ENV_PATH=$1
USERNAME=$2
PASSWORD=$3
# --- Environment Setup ---
# Check if the EBSapps.env file exists and source it
if [ ! -f "$EBS_ENV_PATH" ]; then
echo "Error: The specified EBSapps.env file was not found: $EBS_ENV_PATH"
exit 1
fi
echo "Sourcing Oracle EBS environment file: $EBS_ENV_PATH"
. "$EBS_ENV_PATH"
# Check if sqlplus is now in the PATH after sourcing the environment
if ! command -v sqlplus &> /dev/null
then
echo "Error: sqlplus command not found after sourcing EBS environment."
echo "Please ensure EBSapps.env is valid and sets the PATH correctly."
exit 1
fi
# Check for required email commands
if ! command -v mailx &> /dev/null
then
echo "Error: 'mailx' command not found. Please install it."
exit 1
fi
if ! command -v uuencode &> /dev/null
then
echo "Error: 'uuencode' command not found. Please install it."
exit 1
fi
# --- Function Definitions ---
# Function to check the current status of the Workflow Notification Mailer
# Returns the status as a string (e.g., 'RUNNING', 'STOPPED_ERROR')
get_mailer_status() {
local status=$(sqlplus -s $USERNAME/$PASSWORD <<EOF
SET HEADING OFF;
SET FEEDBACK OFF;
SET PAGESIZE 0;
SELECT COMPONENT_STATUS FROM FND_SVC_COMPONENTS WHERE COMPONENT_NAME = 'Workflow Notification Mailer';
EXIT;
EOF
)
# Trim whitespace and convert to uppercase for consistent checking
echo "$status" | tr -d ' ' | tr '[:lower:]' '[:upper:]'
}
# Function to stop the Workflow Notification Mailer
stop_mailer() {
echo "Attempting to stop the Workflow Notification Mailer..."
# Run the PL/SQL block to initiate the stop
sqlplus -s $USERNAME/$PASSWORD <<EOF
SET HEADING OFF;
SET FEEDBACK OFF;
SET PAGESIZE 0;
DECLARE
p_retcode NUMBER;
p_errbuf VARCHAR2(100);
m_mailerid FND_SVC_COMPONENTS.COMPONENT_ID%TYPE;
BEGIN
SELECT component_id INTO m_mailerid FROM fnd_svc_components WHERE component_name = 'Workflow Notification Mailer';
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
COMMIT;
END;
/
EXIT;
EOF
echo "Stop command sent. Waiting for mailer to stop..."
local elapsed_time=0
while [ "$elapsed_time" -lt "$TIMEOUT" ]; do
local status=$(get_mailer_status)
if [[ "$status" == "STOPPED_ERROR" || "$status" == "DEACTIVATED_USER" ]]; then
echo "SUCCESS: Mailer is now stopped with status: $status"
return 0 # Success
fi
echo "Mailer status is: $status. Waiting..."
sleep $SLEEP_INTERVAL
elapsed_time=$((elapsed_time + SLEEP_INTERVAL))
done
echo "ERROR: Timeout reached. Mailer did not stop within the specified time."
return 1 # Failure
}
# Function to start the Workflow Notification Mailer
start_mailer() {
echo "Attempting to start the Workflow Notification Mailer..."
# Run the PL/SQL block to initiate the start
sqlplus -s $USERNAME/$PASSWORD <<EOF
SET HEADING OFF;
SET FEEDBACK OFF;
SET PAGESIZE 0;
DECLARE
p_retcode NUMBER;
p_errbuf VARCHAR2(100);
m_mailerid FND_SVC_COMPONENTS.COMPONENT_ID%TYPE;
BEGIN
SELECT component_id INTO m_mailerid FROM fnd_svc_components WHERE component_name = 'Workflow Notification Mailer';
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
COMMIT;
END;
/
EXIT;
EOF
echo "Start command sent. Waiting for mailer to start..."
local elapsed_time=0
while [ "$elapsed_time" -lt "$TIMEOUT" ]; do
local status=$(get_mailer_status)
if [[ "$status" == "RUNNING" ]]; then
echo "SUCCESS: Mailer is now running."
return 0 # Success
fi
echo "Mailer status is: $status. Waiting..."
sleep $SLEEP_INTERVAL
elapsed_time=$((elapsed_time + SLEEP_INTERVAL))
done
echo "ERROR: Timeout reached. Mailer did not start within the specified time."
return 1 # Failure
}
# --- Main Script Logic ---
# Redirect all script output to a temporary file
TEMP_OUTPUT_FILE=$(mktemp)
exec > >(tee "$TEMP_OUTPUT_FILE")
# Call the functions to perform the bounce operation
stop_mailer
if [ $? -eq 0 ]; then
start_mailer
fi
# Restore stdout and send the email
exec >&1
# Get the script's exit status
EXIT_STATUS=$?
echo "Sending bounce report to $RECIPIENT_EMAIL..."
# Create a temporary HTML file from the captured output
TEMP_HTML_FILE=$(mktemp)
cat "$TEMP_OUTPUT_FILE" | sed \
-e 's/^/<li>/' \
-e 's/$/<\/li>/' \
-e 's/ERROR/<span style="color:red; font-weight:bold;">ERROR<\/span>/g' \
-e 's/SUCCESS/<span style="color:green; font-weight:bold;">SUCCESS<\/span>/g' \
-e '1s/^/<p>Oracle Workflow Mailer Bounce Report - $(date)<\/p><ul>/' \
-e '$a</ul>' > "$TEMP_HTML_FILE"
# Construct the email body and send it using uuencode and mailx
# Note: The `echo` commands form the email body, which is then piped
# to `uuencode` along with the file content.
(
echo "The Oracle Workflow Mailer bounce script has finished."
echo "Please find the detailed report attached."
uuencode "$TEMP_HTML_FILE" "$ATTACHMENT_NAME"
) | mailx -s "$SUBJECT_LINE" -r "$SENDER_EMAIL" "$RECIPIENT_EMAIL"
# Clean up the temporary files
rm "$TEMP_OUTPUT_FILE" "$TEMP_HTML_FILE"
echo "Email sent successfully. Script finished."
exit $EXIT_STATUS
