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.

# # 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"

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.



Account Payables,1,Advance Table In OAF,1,AME,1,AOL Concepts,19,AP Holds,1,Apps Initialization,1,Blanket Purchase Orders,1,Blanket Releases,1,Careers,9,Common Errors and Solutions,2,Concurrent Programs,10,Contract Purchase Orders,1,Corporate Culture,3,Corporate Ladder,3,Customer Interface,3,Customization,1,De-Normalization,1,EAM,1,EBS Tutorials,10,Education,16,Emterprise Asset Management,1,English,1,Enterprise Asset Management,1,Entrepreneur,1,Error Fixes,1,Executables,2,Extension,1,fnd_user,1,FNDLOAD,1,Global Agreements,1,Guest Posts,7,Handy Scripts,63,Health Tips,1,Important Apps Queries,1,Infographic,3,Interview Preparation,62,Interview Q/A,68,Inventory Org ID,1,Iproc,4,Iprocurement,4,IT,3,jDeveloper,1,Job Search,1,Key Tables In Oracle Apps,5,Learning English,1,MDS,1,Metadata Tables,2,Multi Org Architecture,2,Normalization,1,O2C,1,OAF Interview,50,OAF Personalization,1,OAF Tutorials,52,OCI,1,Operating Unit,1,Oracle 10g,1,Oracle 11g,1,Oracle Access Manager,1,Oracle ADF,1,Oracle Alerts,2,Oracle AP,12,Oracle Apex,1,Oracle APIs,4,Oracle Application Object Library,19,Oracle Applications,10,Oracle Approvals Management,1,Oracle Apps,4,Oracle Apps DBA,2,Oracle Apps Interviews,36,Oracle Apps Tips,9,Oracle Apps Tutorials,98,Oracle AR,18,Oracle Assets,2,Oracle BAM,1,Oracle BI Publisher,1,Oracle BPEL,1,Oracle BPM,1,Oracle Cloud,1,Oracle Cloud Infrastructure,1,Oracle Dabase Structure,2,Oracle Database Architecture,2,Oracle Database Key Tables,3,Oracle EAM,2,Oracle EBS,5,Oracle Financials,2,Oracle Forms And Reports,17,Oracle Forms Interview Questions,6,Oracle Forms Tutorials,6,Oracle GL,4,Oracle Holds,1,Oracle HRMS,10,Oracle Human Resources,1,Oracle Inventory,4,Oracle Payables,1,Oracle Payroll,1,Oracle POTutorials,1,Oracle Purchasing,7,Oracle Reports Interview,9,Oracle Reports Tutorials,3,Oracle SOA,1,Oracle System Administrator,4,Oracle Workflow,4,Order Management,2,Organization ID,3,Organizations,3,Performance Tuning,2,Personalization,1,PL/SQL Block,1,PL/SQL Interview Questions,11,PL/SQL Scripts,44,PL/SQL Tutorials,53,Planned Purchase Orders,1,PO,1,PO Action History,1,PO_ACTION_HISTORY,1,POracle Purchasing,1,PR,1,Procedures And Functions,2,Profile Options,2,Purchase Orders,1,Purchase Requisition,2,Receipts,1,Request Groups,1,Responsibility,1,Shell Script,1,SOA Tutorials,4,Social Media,1,Software Industry,2,Software Tools,1,SQL And PL/SQL Interview,38,SQL Interview,52,SQL Scripts,38,SQL Tutorials,43,Standard Purchase Orders,1,Troubleshooting,1,Unexpected error,1,Unix/Linux Commands,3,User,1,Value Sets,1,Weblogic,1,XML Publisher,4,
Oracle Apps Guy: How To Send Emails From Oracle Apps.
How To Send Emails From Oracle Apps.
How To Send Emails From Oracle Apps.sending emails from oracle apps R12
Oracle Apps Guy
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content