Next ADF Training
Join Demo Session
Toggle Bar
  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Archives
    Archives Contains a list of blog posts that were created previously.
  • Login
    Login Login form

Sending emails from Oracle Apps

Posted by on in OA Framework
  • Font size: Larger Smaller
  • Hits: 5134
  • Subscribe to this entry
  • Print
  • Report this post

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.


# # 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...
# 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"
   echo "Specified directory $APPLCSF$SUBDIR does not exist"
   exit 1
fcount=`ls -C1 *$FILE_NAME* | wc -w`
if [ $fcount -eq 0 ]
   echo "Specified file pattern $FILE_NAME not found in `pwd`"     exit 1
# getting email addresses
string="set echo off feedback off heading off
select ffv.flex_value
        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 ]
   echo "Could not get email addresses from database"
   exit 1
# loop through files and emails to send everything
for accnt in $addresses
   for filename in `ls -C1 *$FILE_NAME*`
      mailx -s "$filename" $accnt < $filename
      if [ $? -gt 0 ]
         echo "Could not send $filename to $accnt"
         echo "File $filename sent to  $accnt"
# check for errors
echo "Done with mail"
if [ $errstr = 'Y' ]
   echo "There is at least one error during execution of $FunctionName"
   exit 1
echo "Email Job Completed"

Steps to Implement
1. Create an executable (XXCOM_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 XXCOM_TOP and in your bin directory, create a shell script with name XXCOM_MAILER using the above code.

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.

Any questions or concerns, let me know.

Trackback URL for this blog entry.
  • No comments found