Tuesday, 27 September 2011

Sending existing XML Publisher report as embedded HTML email


One of the tasks I was doing recently is to take existing XML publisher report, convert it into HTML and send to someone as embedded HTML mail.

At first , it looks difficult. Most of the reports create PDF output and there is no much you can do and specially you cannot convert PDF to HTML. Or should I say “it is possible, but requires too much effort”. Any way it was not an option. So I thought in a different direction. each output in XML publisher created by using some template. In 99% of the cases – it is RTF template. Again , no much to do with an RTF template, but Oracle knows somehow to combine it with XML source. So I did a little research and discovered the following:

1. All templates are stored inside XDO_LOBS table as a BLOB data.

2. When you upload RTF document to EBS it stored the document RAW but it also creates a xsl-fo document for RTF. Bingo!!! If I could get this xsl document I can create any outout I want

Bellow you can see 2 records for the same RTF document. One for RTF and one for XSL



So, how to extract this XSL document? One way is to write java program that converts this BLOB field to the file on the disk. But why to invent the wheel twice? Oracle already wrote such a program. It is called XDOLoader

Bellow is an example for XDOLoader usage:

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \


-DB_USERNAME: apps username for the target instance
-DB_PASSWORD: apps password for the target instance
-JDBC_CONNECTION: JDBC connection string (e.g. server:port:sid)
of the target instance
-LOB_TYPE: The XML Publisher object type.
-APPS_SHORT_NAME: Application short name used to register the XML Publisher object
-LOB_CODE: Template code i.e. concurrent program short name
-LANGUAGE: ISO language code(i.e. en) used when registering the object
-TERRITORY: ISO language code(i.e. GB) used when registering the object
-LOG_FILE: Name of the log file. The default filename is xdotmpl.log
-DEBUG: Run in Debug mode. Valid values are 'true' or 'false'


After you run this commands you will see 2 files in the same directory. One RTF file (you can delete it, we don’t need it ) and second one is XSL file – exactly what you need.

In order to produce the output you will need the XML source file that contains all the data. If you running this process from Oracle EBS it is not a problem. As I said we are working with existing templates , so they already have data templates defined and probably you can find XML source files under Concurrent Manager output directory ($APPLCSF/$APPLOUT). If XML file is not where – find a way to get it. This is behind the scope of this post.

Ok, now we need to combine XML with XSL and create an HTML output. We can use existing XML Publisher java API to achieve this.

So we will write a little Java

//you need the following import
import oracle.apps.xdo.XDOException;
import oracle.apps.xdo.template.FOProcessor;

//and use the following function
public void convertToHtml(String xmlSourceFile,
String xslFoFile,
String language)
FOProcessor processor
= new FOProcessor();
processor.setData( xmlSourceFile );
new Locale(language));
processor.setTemplate( xslFoFile );
// Start processing
catch (XDOException e)
" Cannot convert XSL template to HTML");


After execution of this code you will have a nice HTML document that contains your report without rewriting your template.

And now the final part. We want to send it by email, but not as attachment. We want to send it as embedded HTML. Embedded html is not a problem, just don’t specify “attachment” content-type. But in html , could be that you have a pictures, this pictures refer to physical files on the disk, so when you send your email , recipient get it properly, but without pictures. Need to find a way to convert this pictures to base64 format and make them part of HTML email body.

   And again, no need to invent the wheel twice. XML publisher delivery API provide this functionality out of the box.

Some more java code….

//need to following import
import oracle.apps.xdo.delivery.DeliveryException;
import oracle.apps.xdo.delivery.DeliveryManager;
import oracle.apps.xdo.delivery.DeliveryPropertyDefinitions;
import oracle.apps.xdo.delivery.DeliveryRequest;
import oracle.apps.xdo.delivery.InvalidFactoryException;
import oracle.apps.xdo.delivery.UndefinedRequestTypeException;

//and the function that will do all the work
//the logic of converting pictures to base64 and making them part
//of HTML occurs behind the scenes
public void sendEmail()
//create an instance of delivery manager
DeliveryManager dm;
= new DeliveryManager();
DeliveryRequest req
= dm.createRequest(DeliveryManager.TYPE_SMTP_EMAIL);
//define mail connection and content
req.addProperty(DeliveryPropertyDefinitions.SMTP_SUBJECT, "test");
req.addProperty(DeliveryPropertyDefinitions.SMTP_TO_RECIPIENTS, "dummy@gmail.com" );
req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_TYPE, EMAIL_CONTENT_TYPE);

//define html document location. Images being automatically embedded into HTML mail body
// submit the request
// close the request
"Email sent");

catch (InvalidFactoryException e)
// TODO Auto-generated catch block
// create a delivery request
catch (UndefinedRequestTypeException e)
// TODO Auto-generated catch block
catch (DeliveryException e)
// TODO Auto-generated catch block


Done!! Without changing existing template or developing a new one, you can have now almost any report being converted to HTML and send it to anyone by email (with pictures Smile )

No comments:

Post a Comment