A BI Publisher developer's diary...

It was just last week when I moved out from BI Publisher development and join the Fusion Financial Application development group. All these years working for a great product and with a great team, it was a wonderful experience.

My association with BI Publisher is more then six years old. It was the time, when I was working as a part of Oracle Financial Globalization team on EBS Reporting Strategy project with Tim Dexter. We were trying to create Java applet based Layout Editor to modify the Oracle Report Layout without changing the underlying Data Model. This was the requirement from most of EBS customers for simple customization of report layout. Oracle Report Designer was/is available but it was hard for PMs to use it. During the same period, ATG Internationalization team came up with some exciting prototypes on RTF templates. I still remember about very first POC from Internationalization team on RTF template with one single Form field and the PDF output. That was so exciting that after the demo we dumped the Report Layout Editor project. It was decided to work together to explore further on the same line. Later the product called XML Publisher. First Tim Dexter moved to XML Publisher and subsequently I joined the team as well.

After joining, I start working on XML Publisher integration with EBS. The first integration between Publisher and EBS was through 2 steps Java Concurrent Program to submit a XML Publisher report through EBS. XDO Common Region was another integration with EBS, where XML Publisher report can be embedded within OAF pages.

During up-taking the Publisher within EBS, one of the critical questions was, what we are going to do with 3000 existing EBS reports. Are we going to re-write all these reports? The answer was RDF to RTF migration utility to migrate the RDF Layout.

Data Template (Data Engine) was another exciting contribution to the product. After migrating RDF layout section to RTF Templates, the existing Oracle Reports was still in use to generate the XML Data. It was like two reporting solution for a single report. we came up with the Data Template concept. The very first Data Engine was written in PLSQL and later we migrate it to Java. The Data Template functionality helped us to come up with a complete RDF to XML publisher migration utility. EBS teams has successfully migrated around 2000 Oracle Reports to BI Publisher report with the help of these utilities.

Bursting is one of my most lovable babies. Bursting Engine is very dynamic and supports a wide range of bursting requirement. It has two flavors. Control file based bursting, which is well integrated with EBS and JDE, is much more powerful compare to Delivery SQL based bursting supported through Enterprise Release.

Moving forward, I look after Scheduling functionality of Enterprise Release. I did contribute to enhance this area, but frankly speaking, I don’t see any major contribution over that area.

Here it comes Fusion, It was time again to work closely with Oracle Application Teams. I always feel pleasure while working with developers, customers.

While I moved out last week from the BI Publisher development, I still can’t leave the product. It is too close to my heart and the best way to be in touch with the product, I decided to share my knowledge with the community though this media.

Most of my post will be specific to developer’s point of view. I will start with one of my favorite features Bursting. I feel its is under-documented and the feature is most widely used by the developers community. So stay tune and wait for next post.

During these years with Publisher, I worked with many clients directly/indirectly to resolve there reporting issues and one of the very common requirment is about generate mutilple outputs against the single process/report. Here I am sharing one such requirment...

The company’s billing system generates around 30 thousands invoices/bills for each billing cycle. Each billing cycle correspond to a single process and generate a huge set of data for all the invoices.

The process, further consume this huge data and generate the individual Invoices. i.e. one report and multiple output.

The individual Invoice layout depends upon the recipient’s country and local preference, So it could be multiple templates and multiple local. .

The process should take care of document delivery as well. Delivery type and the destination depend on individual client’s preference. Beside it, a copy of each invoice should be save to specific file location or transferred to content management system. To achieve this, the process should allow to hook custom APIs/Logic before and after individual documents generation.

Beside the individual output, there should be another single consolidated output against the same set of data. i.e. a summary report.

So what do you think, Can we satisfy this requirement against the single process?

Yes, The Bursting take care of such type of requirements where single dataset, multiple conditional layouts/templates, multiple layout, multiple delivery and the listener to hook the custom logic at each stage of process.

There are two type of bursting implementation within BI Publisher. The Control File based bursting, which is well integrated with EBS and JDE and available through Public APIs as well. The other one is Delivery SQL based bursting, which is part of Enterprise Release. We will discuss the first one on this post.

The Bursting Control file holds the Meta Data or the bursting rules in XML format.Lets have a look on following control file and see, how far it satisfies the above requirement.

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
 <xapi:globalData location="stream">
 </xapi:globalData >
    <xapi:request select="/DATA/LIST_DEPT/DEPT/LIST_EMPLOYEE/EMPLOYEE">
 <xapi:delivery>
  <xapi:email server="${EMAIL_SERVER}" port="${EMAIL_PORT}" from="${FROM_EMAIL_ADDRESS}"
    reply-to ="${REPL_TO_ADDRESS}">
 <xapi:message id="123"  to="${TO_EMAIL_ADDRESS}" cc="${CC_EMAIL_ADDRESS}" 
 attachment="true"  content-type="html/text" 
 subject="Employee Report for Empno ${EMPNO}">  Dear ${ENAME}, 
    Please review the attached document.</xapi:message>
  </xapi:email>
  <xapi:print id="printer1"  printer="${PRINT_URL}" copies="2"
   orientation-requested="3" />
 </xapi:delivery>
 <xapi:document key="${EMPNO}"  output="c:\burst\sample\${EMPNO}_${ENAME}.pdf" 
  output-type="pdf" delivery="123">
    <xapi:template type="rtf" location="${EMP_TEMPLATE}">
    </xapi:template>
  </xapi:document>
 </xapi:request>
</xapi:requestset>

This will not exactly satisfy the above requirement but it gives an idea, how we can proceed to achieve the above requirement.

Here is the sample usage of Bursting Engine. We can call bursting engine either directly or through DocumentPrecoessor. Calling directly gives more control over some of the internal methods. API accept the Control file, Data File and Temporary directory path.

Using BurstingProcessorEngine directly..

try {
BurstingProcessorEngine dp = new BurstingProcessorEngine();
dp.deleteTempOutputFile(false);
dp.setTempDirectory("c:\\burst\\sample");//Set the temp file
dp.setXMLAPI("C:\\burst\\sample\\SampleControlFile.xml"); //Set Bursting Control file.
dp.setData("c:\\burst\\sample\\employee.xml"); //Data File
dp.registerListener(this); //Set the listener
Properties prop = new Properties(); //add properties and variables//
prop.put("user-variable:EMAIL_SERVER", "myemailserver.com");
prop.put("user-variable:EMAIL_PORT", "25");
prop.put("user-variable:FROM_EMAIL_ADDRESS", “admin@xyz.com");
prop.put("user-variable:CC_EMAIL_ADDRESS", "cc@xyz.com");
prop.put("user-variable:PRINTER_URL", "ipp://myprintserver:631/printers/printer1");
prop.put("user-variable:EMP_TEMPLATE", "c:\\burst\\sample\\employee.rtf");
dp.setConfig(prop);
dp.process();
dp.deletTemporaryOutputFiles();
} catch (Exception e) {
Logger.log(e);
}

Using DocumentProcessor:

try {
DocumentProcessor dp = new DocumentProcessor("C:\\burst\\sample\\SampleControlFile.xml","c:\\burst\\sample\\employee.xml", "c:\\burst\\sample");
dp.setTempDirectory("c:\\burst\\sample");//Set the temp file
dp.setXMLAPI("C:\\burst\\sample\\SampleControlFile.xml"); //Set Bursting Control file.
dp.setData("c:\\burst\\sample\\employee.xml"); //Data File
dp.registerListener(this); //Set the listener
Properties prop = new Properties(); //add properties and variables//
prop.put("user-variable:EMAIL_SERVER", "myemailserver.com");
prop.put("user-variable:EMAIL_PORT", "25");
prop.put("user-variable:FROM_EMAIL_ADDRESS", “admin@xyz.com");
prop.put("user-variable:CC_EMAIL_ADDRESS", "cc@xyz.com");
prop.put("user-variable:PRINTER_URL", "ipp://myprintserver:631/printers/printer1");
prop.put("user-variable:EMP_TEMPLATE", "c:\\burst\\sample\\employee.rtf");
dp.setConfig(prop);
dp.process();
} catch (Exception e) {
Logger.log(e);
}

A sample test case including sample java application, control file, data file, rtf template is available here. Please make sure you have following libraries in class path
Collection.jar, xdo.jar, Xmlparserv2.jar, mail.jar, Activaton.jar, aolj.jar, i18n, xdoparser.jar,Versioninfo.jar and use 10.1.3.4+ release.

Try it out and let me know your experience. We will see some more interesting example during coming post, so stay tune…