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:


$PROFILES$.CONC_REQUEST_ID
$PROFILES$.PRINTER
$PROFILES$.CONC_COPIES
$PROFILES$.TITLE
$PROFILES$.FILENAME
$PROFILES$.RESP_ID
$PROFILES$.USER_ID


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

$PROFILES$.CONC_REQUEST_ID $PROFILES$.FILENAME tim.dexter@oracle.com

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

(DeliveryManager.TYPE_SMTP_EMAIL);
        // set email subject
         delReq.addProperty(DeliveryPropertyDefinitions.SMTP_SUBJECT,

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

"ebsadmin@oracle.com");
        // set the destination email address
         delReq.addProperty(DeliveryPropertyDefinitions.SMTP_TO_RECIPIENTS,

getEmail(userid) );
        // set the content type of the email body
         delReq.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_TYPE,

"application/pdf");
        // set the document file name appeared in the email
         delReq.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_FILENAME,

repTitle+rRequestID+".pdf");
        // set the document to deliver
         delReq.setDocument(rFile);
        // submit the request
         delReq.submit();
        // close the request
         delReq.close();

    }
    catch (Exception e) {
    e.printStackTrace();

    }
  }

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

        Class.forName(fDriverName).newInstance();
        Connection dbconn = DriverManager.getConnection
            ("jdbc:oracle:thin:@"+fServer+":"+fPort+":"

+fDbName, fUserName, fPassword);
        return dbconn;
  }
 
  private String getReportTitle(int requestID)
  {
    String reportName = "";
    try
    {
      // 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
      getTitle.setInt(1,requestID);
    
      // 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();
      titleRslt.next();
      reportName = titleRslt.getString(1);
      System.out.println(reportName);
     
      //Clean up
      titleRslt.close();
      getTitle.close();
      conn.close();
     
     
    }
    catch (SQLException eSQL)
    {
      System.err.println("Could not create connection");
      eSQL.printStackTrace();
    }
    catch (Exception e)
    {
     System.err.println("Exception thrown");
     e.printStackTrace();
    }
    
    return reportName;
  }
 
 
  private String getEmail(int userID)
  {
    String eMailID = "";
    try
    {
      // Try and geta connection to the db
      Connection conn = getConnection();
      PreparedStatement getEmail = conn.prepareStatement(

"select email_address from fnd_user where user_id = ?");
      getEmail.setInt(1,userID);
    
      // 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();
      emailRslt.next();
      eMailID = emailRslt.getString(1);
      System.out.println(eMailID);
     
      //Clean up
      emailRslt.close();
      getEmail.close();
      conn.close();
     
     
    }
    catch (SQLException eSQL)
    {
      System.err.println("Could not create connection");
      eSQL.printStackTrace();
    }
    catch (Exception e)
    {
     System.err.println("Exception thrown");
     e.printStackTrace();
    }
         
    return eMailID;
  }
  public static final void main(final String[] args)
  {
    // Arguments passed
    //1.$PROFILES$.CONC_REQUEST_ID
    //2.$PROFILES$.FILENAME
    //3.$PROFILES$.USER_ID

    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:

CLASSPATH=/apps/vis11510comn/java/oracle/apps/xdo/ebsdelivery:$CLASSPATH
export CLASSPATH

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

# 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

Comments:

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? Thanks! K. Hale

Posted by K. Hale on July 28, 2008 at 02:17 AM MDT #

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. Tim

Posted by Tim on August 04, 2008 at 03:17 AM MDT #

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.

Posted by SALLY on July 14, 2010 at 01:16 PM MDT #

Thanks from sweden for this post

Posted by t shirt tryck on August 18, 2010 at 05:18 AM MDT #

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

Posted by Guy Bonnel on September 25, 2010 at 07:44 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today