Sometimes we may need to send emails to the users after the concurrent program generates the output. Instead of scrambling with PL/SQL it would be easy to send the mail through Shell Script. The script mainly makes use of the "mailx" feature available in Unix/Linux operating systems.
Steps to Implement
1. Create an executable (XXAB_MAILER) with Execution Method as Host
2. Create a concurrent program with three parameters namely
10. Value Set Name (this stores the email addresses of people to whom we need to send email - You can ignore this if you can derive it systematically from shell script using SQL Query.
20. SUBDIR - This will be log or out directory in APPLCSF path.
30. FILE_NAME - This is going to be filename which we want to email it to the users. It can concurrent request log file or output file.
3. Go to your XXAB_TOP and in your bin directory, create a shell script with name XXAB_MAILER using the above code.
1. Create an executable (XXAB_MAILER) with Execution Method as Host
2. Create a concurrent program with three parameters namely
10. Value Set Name (this stores the email addresses of people to whom we need to send email - You can ignore this if you can derive it systematically from shell script using SQL Query.
20. SUBDIR - This will be log or out directory in APPLCSF path.
30. FILE_NAME - This is going to be filename which we want to email it to the users. It can concurrent request log file or output file.
3. Go to your XXAB_TOP and in your bin directory, create a shell script with name XXAB_MAILER using the above code.
Code:
#!/bin/ksh
# # Following things are derived to send email
# Script takes three parameters SET_NAME, SUBDIR, File_Name
# $SET_NAME - holds name of value set with addressees # $SUBDIR - holds directory path under $APPLCSF
# $FILE_NAME - holds file name pattern to send
#----------------------------------------------------------#
# Either derive password or hardcode here...
$usernm="apps"
$passwd="appsd"
#----------------------------------------------------------#
# Parsing and validating parameters passed from apps
#echo $* SET_NAME=`echo $* |awk '{print $(NF-2)}' | tr -d '["]'`
SUBDIR=`echo $* |awk '{print $(NF-1)}' | tr -d '["]'`
FILE_NAME=`echo $* |awk '{print $NF}' | tr -d '["]'`
echo " Email set is $SET_NAME, dir is $APPLCSF$SUBDIR and file is $FILE_NAME"
#
if [ -d $APPLCSF$SUBDIR ]
then
cd $APPLCSF$SUBDIR
else
echo "Specified directory $APPLCSF$SUBDIR does not exist"
exit 1
fi
#
fcount=`ls -C1 *$FILE_NAME* | wc -w`
#
if [ $fcount -eq 0 ]
then
echo "Specified file pattern $FILE_NAME not found in `pwd`" exit 1
fi
#----------------------------------------------------------#
# getting email addresses
string="set echo off feedback off heading off
select ffv.flex_value
from
fnd_flex_values ffv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name = '$SET_NAME' and ffv.flex_value_set_id =ffvs.flex_value_set_id
and ffv.enabled_flag ='Y'
and (start_date_active and (end_date_active > sysdate or end_date_active is null)
/"
addresses=`echo "$string" | sqlplus -s $usernm/$passwd`
if [ $? -ne 0 ]
then
echo "Could not get email addresses from database"
exit 1
fi
#--------------------------------------------------------#
# loop through files and emails to send everything
#
for accnt in $addresses
do
for filename in `ls -C1 *$FILE_NAME*`
do
mailx -s "$filename" $accnt < $filename
if [ $? -gt 0 ]
then
echo "Could not send $filename to $accnt"
errstr='Y'
else
echo "File $filename sent to $accnt"
fi
done
done
#---------------------------------------------------------#
# check for errors
echo "Done with mail"
if [ $errstr = 'Y' ]
then
echo "There is at least one error during execution of $FunctionName"
exit 1
fi
#----------------------------------------------------------#
echo "Email Job Completed"
Things to Note:1. Above script hardcodes User Id and password to connect to Oracle database. You can modify according to your needs.
2. Instead of retrieving the email from Value set you can retrieve it from any other table by modifying the SQL Query.
COMMENTS