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
