An Oracle blog about BI Publisher

Email from EBS

Trying to write this article for the third time today ... first time I forgot to commit it and my browser crashed ... serves me right for playing with IE7. Second time, I dont know what happened ... anyway trying again.

Apologies for the longer and longer titles ... hopefully the usefullness of the article outweighs the silly titles, got myself into a hole here. This time we're discussing emailing documents from the EBS, we need to pull together the Delivery Manager APIs with the shell scripts techniques we looked at last time.

But you can email already !

Little known fact but you can already allow your users to get emails from the concurrent manager. When submitting a request they can open the 'Options' popup and specify users that should receive a notification upon completion of the request. Thats great but how do they get hold of the output without logging back in and searching through the massive list of requests? If you set the 'Concurrent:Attach URL' to Yes then users will get a mail with a link in it to the output file. However, there are a couple of caveats:
1. The email can only be sent to a user registered in the system (remember to enter their email id)
2. The user has to be connected to the system to retrieve the output file.

Gotta thank Mr Azzopardi for this tidbit!

So what can I do?

To get around these limitations and attach an output file to an email to any user we can use the script print driver and the Delivery Manager APIs. Remember we can pass several parameters to the java via the script:


We could just add the email address we want to send the report to :


But that would mean either one person is going to get every report output from the system that uses that driver or you'll need to create a personal driver for every user, not a bad idea for smaller user groups. It would be better to derive the email address so we can have one email driver to maintain.
Using a combination of the PROFILES we can make a trip to the database to retrieve pretty much anything we want. With the USER_ID we can fetch the email address for that user; with the RESP_ID we could fetch all the emails for the users that have that responsibility. Using the CONC_REQUEST_ID we can get a hold of the concurrent program and maybe store the emails for the report in the Options field of the program definition form. We could even use the derived program id to look up emails against a custom table where we have stored users and programs ... we now have a nice report subscription model for EBS ... cool!

OK, let's do it ...

For this example this just tackle looking up the email address of the user ... the other options are just a variation on the same theme, its just getting the sql right.
So I have built the following java class:

package oracle.apps.xdo.ebsdelivery;

import oracle.apps.xdo.delivery.DeliveryManager;
import oracle.apps.xdo.delivery.DeliveryRequest;
import oracle.apps.xdo.delivery.DeliveryPropertyDefinitions;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class EBSEmailDelivery
  public EBSEmailDelivery(String rRequestID, String rFile, String rUser)
        int userid =  Integer.parseInt(rUser);
        int requestid = Integer.parseInt(rRequestID);
    try {
        //get the report title
        String repTitle = getReportTitle(requestid);
        // create delivery manager instance
         DeliveryManager delMgr = new DeliveryManager();
        // create a delivery request
         DeliveryRequest delReq = delMgr.createRequest

        // set email subject

"EBS Report:"+repTitle +" for request: "+ rRequestID);
        // set SMTP server host
         DeliveryPropertyDefinitions.SMTP_HOST, "mail.yourcompany.com");
        // set the sender email address

        // set the destination email address

getEmail(userid) );
        // set the content type of the email body

        // set the document file name appeared in the email

        // set the document to deliver
        // submit the request
        // close the request

    catch (Exception e) {


  static Connection getConnection() throws SQLException, Exception
        String fDriverName = "oracle.jdbc.driver.OracleDriver";
        String fDbName = "vis11510";
        String fServer = "xdodemo.us.oracle.com";
        String fPort = "1521";
        String fUserName = "apps";
        String fPassword = "apps";

        Connection dbconn = DriverManager.getConnection

+fDbName, fUserName, fPassword);
        return dbconn;
  private String getReportTitle(int requestID)
    String reportName = "";
      // Try and geta connection to the db
      Connection conn = getConnection();
      // fetch the report name based on the request id
      PreparedStatement getTitle = conn.prepareStatement(

"select user_concurrent_program_name \n" +
"from fnd_concurrent_requests fcr,\n" +
 "fnd_concurrent_programs_vl fcpv\n" +
 "where fcr.concurrent_program_id = fcpv.concurrent_program_id\n" +
 "and request_id = ?");

//get the title
      // get the query result in to a result set and then assign the
      // value to a variable we can pass back to the calling method
      ResultSet titleRslt = getTitle.executeQuery();
      reportName = titleRslt.getString(1);
      //Clean up
    catch (SQLException eSQL)
      System.err.println("Could not create connection");
    catch (Exception e)
     System.err.println("Exception thrown");
    return reportName;
  private String getEmail(int userID)
    String eMailID = "";
      // Try and geta connection to the db
      Connection conn = getConnection();
      PreparedStatement getEmail = conn.prepareStatement(

"select email_address from fnd_user where user_id = ?");
      // get the query result in to a result set and then assign the
      // value to a variable we can pass back to the calling method
      ResultSet emailRslt = getEmail.executeQuery();
      eMailID = emailRslt.getString(1);
      //Clean up
    catch (SQLException eSQL)
      System.err.println("Could not create connection");
    catch (Exception e)
     System.err.println("Exception thrown");
    return eMailID;
  public static final void main(final String[] args)
    // Arguments passed

    EBSEmailDelivery ebsMail = new EBSEmailDelivery(args[0], args[1], args[2]);



You can get the source here.
Just a note, to develop and compile your class you are going to need the following libraries:
xdocore.jar - grab it from the Template Builder install
versioninfo.jar - same place
xmlparserv2-904.jar - the Oracle XML parser
mail.jar - this is the java mail library get it from Sun
Activation.jar - a supporting library for the mail library get it from Sun 

if you want to test it on your cleint machine you'll need the Oracle JDBC driver libraries too ... these will be under your EBS JAVA_TOP, jdbc12.zip worked for me. Ar runtime all the libraries will be available for you so all you'll need to do is add the path to your class in the shell script.

Walking through the class we have several methods.

  • EBSEmailDelivery - at the top we have the delivery manager APIs for emailing. We have seen these already. You'll need to add your outgoing email server.

  • getConnection() - this method gets us a connection to the db

  • getReportTitle - this fetches the report title based on the concurrent request id. Check out the query we're executing.

  • getEmail - this fetches the email of the user that ran the report based on the user id

The class is not catching every possible situation but you get the idea.

Now we have the class we mount it into the APPL_TOP. You should have a custom area under JAVA_TOP. My class is going to sit under the xdo directory under JAVA_TOP. You should note that the package name in the top of the class i.e. oracle.apps.xdo.ebsdelivery is going to define where the class is going to sit. In this case $JAVA_TOP/oracle/apps/xdo/ebsdelivery.

The shell script now needs to call the class and pass the parameters, I have the following:


java oracle.apps.xdo.ebsdelivery.EBSEmailDelivery $1 $2 $3
if [ $RESULT -ne 0 ]
echo "`date` There was an error while delivering the document."
echo "`date` Please inform your System Administrator."
exit $RESULT

# Return success code to Oracle concurrent manager
exit 0

So there you have it, your users can get emails from the EBS with their reports attached. Of course you can get very sophisticated and grab multiple email addresses or even multiple reports if you are using a request set ... just let your imagination run riot.

Next ... faxing documents

Join the discussion

Comments ( 5 )
  • K. Hale Monday, July 28, 2008
    Is it possible to grab the TO email address(es) from the XML data file itself? If so, any examples on how to accomplish that?
    K. Hale
  • Tim Monday, August 4, 2008
    Hi K
    Look at the Faxing entry's use of the SAX parser to find a specific element in the XML that holds the fax number - this technique can be used for the email address too.
  • SALLY Wednesday, July 14, 2010
    Great post, I think a lot of people are coming into blogging as a money-making venture, and if they only have that tunnel vision they’ll be missing out on all the other benefits you mentioned.
  • t shirt tryck Wednesday, August 18, 2010
    Thanks from sweden for this post
  • Guy Bonnel Sunday, September 26, 2010
    ""we belong together"....maybe it's just cuz it applies to my life right now, but it shows her range and is a terrific love song"
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.