How To Send Emails From Oracle Apps.

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.

 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.