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 \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-LOG_FILE \
-DEBUG

Parameters:

-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.
One of 'TEMPLATE', 'XML_SCHEMA', 'XML_SAMPLE' or 'DATA TEMPLATE'
-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 );
        processor.setLocale(new Locale(language));
        processor.setTemplate( xslFoFile     );
        processor.setOutputFormat(FOProcessor.FORMAT_HTML);
        processor.setOutput("/tmp/myOutput.html");
        // Start processing
        try
        {
           processor.generate();
        }
        catch (XDOException e)
        {
           System.err.println("  Cannot convert XSL template to HTML");
           e.printStackTrace();
           System.exit(1);
        }
    }

 

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;
        try 
        {
            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_HOST,    "localhost");
            req.addProperty(DeliveryPropertyDefinitions.SMTP_PORT,    25);
            req.addProperty(DeliveryPropertyDefinitions.SMTP_FROM,       "dummy@maiul.com);
            req.addProperty(DeliveryPropertyDefinitions.SMTP_TO_RECIPIENTS, "dummy@gmail.com" );
            req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_TYPE, EMAIL_CONTENT_TYPE);
            req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_FILENAME, "/tmp/myOutput.html");
            
            //define html document location. Images being automatically embedded into HTML mail body
            req.setDocument("/tmp/myOutput.html";
                // submit the request
                req.submit();
                // close the request
                req.close();
                System.out.println("Email sent");
            
        } catch (InvalidFactoryException e) 
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
         // create a delivery request
        catch (UndefinedRequestTypeException e) 
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (DeliveryException e) 
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }

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 )