Integration JasperReports into Oracle E-Business Suite (OEBS)

Some years ago I was asked to design a report in MS Excel format in OEBS. Also received xls file had to be zipped and sent by email. Last two requirements could be easy implemented using Java. Since I had already decided to use Java I attempted to integrate JasperReports into OEBS too. In my oppinion JasperReports is more powerful reports' generator than Oracle Reports 6. Withal JasperReports has build-in export a report in xls format.

In those days I used OEBS 11.5.10.2. It had jdk 1.4.2_04 on board. There were some troubles with choice of JasperReports version. Although JasperSoft declared that last version compatible with jdk 1.4 was 3.7.1, in my case only 3.2.1 version shew stable work. Unfortunately I can't find respective link on Jasper Forum now to prove this assertion.

Well, let's do, step by step, all things we need to receive a simple report in xls format.

Preparing environment on OEBS server

To connect with OEBS server I used WinSCP and putty and, of course, you must have privileges on access to server.
1. Download JasperReports 3.1.2, unzip it.
2. Copy the following jars from /lib to //comn/util/java/1.4/j2sdk1.4.2_04/jre/lib/ext (for example, /work/comn/util/java/1.4/j2sdk1.4.2_04/jre/lib/ext)

• jasperreports-3.1.2.jar
• commons-collections-2.1.jar
• commons-logging-1.0.2.jar
• iText-2.1.0.jar
• poi-3.0.1-FINAL-20070705.jar

3. (Optional) I used .jasper files have been compiled on my workstation. Of course you can use pure .jrxml files and compile them in Java program on server side. To be able to do this you have to copy the following jars to the same location

• commons-beanutils-1.7.jar
• commons-digester-1.7.jar
• xercesImpl.jar
• xml-apis.jar

4. Copy these two .properties files to any location defined in CLASSPATH server variable

• default.jasperreports.properties
• jasperreports_extension.properties

For example, into /work/comn/java
5. Create the folder where your compiled report files will be stored. This location will be used in Java program of a report (for example, /work/comn/java/reports)
6. If you have not used any custom java classes yet, create the structure of folders for java packages you wish to use (for example, /work/comn/java/xx/report)

Preparing environment on client workstation to design reports

1. Download and install iReport 3.1.2. Version of iReport must be the same as version of JasperReports we are using in OEBS.

2. Download JDBC for Oracle (ojdbc14.jar) to be able to connect to OEBS database via iReport.

3. Apply some custom settings for iReport

• usually, to correct behavior with UTF codepage, I add -J-Dfile.encoding=UTF-8 to default_options property in /etc/ireport.conf file

for example, default_options="-J-Xms24m -J-Xmx64m -J-Dfile.encoding=UTF-8"

• in iReport menu select Tools -> Options, "Classpath" tabsheet, "Add JAR" button, select ojdbc14.jar has been downloaded before
• in iReport menu select Tools -> Report Datasources, create new datasource (New button), select "Database JDBC connection", set its name, select JDBC driver for Oracle (it should be available after previous step has been done) and set "JDBC URL"

URL is composed according to this template jdbc:oracle:thin:@:: (for example, jdbc:oracle:thin:@localhost:1521:study)
Then set "Username" and "Password", and test created connection (Test button).

Designing simple report in iReport

This report just selects currencies from OEBS database. It has some input parameters of different data types to show how they should be processed in Java program for the report. The report's template is quite simple - one table with headers and report's title.

Create new report in iReport, name it fnd_currencies, go to XML page (XML button on report's toolbar), replace existing xml by provided one and save the report. 

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="fnd_currencies" pageWidth="595" pageHeight="842" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
 <parameter name="P_ACTIVE_TO_DATE" class="java.util.Date"/>
 <parameter name="P_PRECISION" class="java.math.BigDecimal"/>
 <parameter name="P_NAME_MASK" class="java.lang.String"/>
 <queryString>
  <![CDATA[
     select c_tl.currency_code
   , c_tl.name
                 , c_tl.description
              from fnd_currencies c
                 , fnd_currencies_tl c_tl
             where c.currency_code = c_tl.currency_code
               and c_tl.language = 'US'
               and (($P{P_ACTIVE_TO_DATE} is null) or (nvl(c.end_date_active, date '2099-12-31') > $P{P_ACTIVE_TO_DATE}))
               and (($P{P_PRECISION} is null) or (c.precision = $P{P_PRECISION}))
               and (($P{P_NAME_MASK} is null) or (upper(c_tl.name) like '%' || upper($P{P_NAME_MASK}) || '%'))
             order by c_tl.currency_code
  ]]>
 </queryString>
 <field name="CURRENCY_CODE" class="java.lang.String" />
 <field name="NAME" class="java.lang.String" />
 <field name="DESCRIPTION" class="java.lang.String" />
 <title>
  <band height="46">
   <staticText>
    <reportElement x="0" y="16" width="555" height="20"/>
    <textElement textAlignment="Center" verticalAlignment="Middle">
     <font size="14" isBold="true"/>
    </textElement>
    <text><![CDATA[Active Currencies]]></text>
   </staticText>
  </band>
 </title>
 <columnHeader>
  <band height="20">
   <staticText>
    <reportElement mode="Opaque" x="25" y="0" width="48" height="20" backcolor="#CCCCCC"/>
    <box leftPadding="3" rightPadding="3">
     <topPen lineWidth="1.0"/>
     <leftPen lineWidth="1.0"/>
     <bottomPen lineWidth="1.0"/>
     <rightPen lineWidth="1.0"/>
    </box>
    <textElement textAlignment="Center" verticalAlignment="Middle">
     <font isBold="true"/>
    </textElement>
    <text><![CDATA[Code]]></text>
   </staticText>
   <staticText>
    <reportElement mode="Opaque" x="73" y="0" width="128" height="20" backcolor="#CCCCCC"/>
    <box leftPadding="3" rightPadding="3">
     <topPen lineWidth="1.0"/>
     <leftPen lineWidth="1.0"/>
     <bottomPen lineWidth="1.0"/>
     <rightPen lineWidth="1.0"/>
    </box>
    <textElement textAlignment="Center" verticalAlignment="Middle">
     <font isBold="true"/>
    </textElement>
    <text><![CDATA[Name]]></text>
   </staticText>
   <staticText>
    <reportElement mode="Opaque" x="201" y="0" width="329" height="20" backcolor="#CCCCCC"/>
    <box leftPadding="3" rightPadding="3">
     <topPen lineWidth="1.0"/>
     <leftPen lineWidth="1.0"/>
     <bottomPen lineWidth="1.0"/>
     <rightPen lineWidth="1.0"/>
    </box>
    <textElement textAlignment="Center" verticalAlignment="Middle">
     <font isBold="true"/>
    </textElement>
    <text><![CDATA[Description]]></text>
   </staticText>
  </band>
 </columnHeader>
 <detail>
  <band height="20">
   <textField hyperlinkType="None">
    <reportElement x="25" y="0" width="48" height="20"/>
    <box leftPadding="3" rightPadding="3">
     <topPen lineWidth="1.0"/>
     <leftPen lineWidth="1.0"/>
     <bottomPen lineWidth="1.0"/>
     <rightPen lineWidth="1.0"/>
    </box>
    <textElement textAlignment="Center" verticalAlignment="Middle"/>
    <textFieldExpression class="java.lang.String"><![CDATA[$F{CURRENCY_CODE}]]></textFieldExpression>
   </textField>
   <textField hyperlinkType="None">
    <reportElement x="73" y="0" width="128" height="20"/>
    <box leftPadding="3" rightPadding="3">
     <topPen lineWidth="1.0"/>
     <leftPen lineWidth="1.0"/>
     <bottomPen lineWidth="1.0"/>
     <rightPen lineWidth="1.0"/>
    </box>
    <textElement verticalAlignment="Middle"/>
    <textFieldExpression class="java.lang.String"><![CDATA[$F{NAME}]]></textFieldExpression>
   </textField>
   <textField hyperlinkType="None">
    <reportElement x="201" y="0" width="329" height="20"/>
    <box leftPadding="3" rightPadding="3">
     <topPen lineWidth="1.0"/>
     <leftPen lineWidth="1.0"/>
     <bottomPen lineWidth="1.0"/>
     <rightPen lineWidth="1.0"/>
    </box>
    <textElement verticalAlignment="Middle"/>
    <textFieldExpression class="java.lang.String"><![CDATA[$F{DESCRIPTION}]]></textFieldExpression>
   </textField>
  </band>
 </detail>
</jasperReport>

Select created datasource (combobox on main iReport toolbar) and look how the report works (Preview button on report's toolbar). Everytime when you preview the report it will has been compiled and .jasper file will has been created (in the same folder where .jrxml file is).

Copy fnd_currencies.jasper file to server in the folder where you are going to store your jasper reports (in my case it's /work/comn/java/reports).

Developing Java program for the report

It could sound curiously but I prefer to code Java program for a report in simple text editor, then copy it to OEBS server and compile it there.

Of course, you can use you favorite IDE (for example, Eclipse) to achive the same. In this case on your workstation you have to

• deploy JDK with the same version that is used on OEBS server
• switch on this JDK in your IDE
• copy from server all neccessary classes (oracle.apps.fnd.*) 
• include copied classes to your project
• include JasperReports to your project

To be used as OEBS concurrent program, Java program has to implement oracle.apps.fnd.cp.request.JavaConcurrentProgram interface. The interface describes only one method - runProgram. As input parameter the method gets a context - an object of oracle.apps.fnd.cp.request.CpContext class.

From the context we can obtain

• list of concurrent program's parameters
• log and out files (these files are availabe from OEBS GUI after concurrent program has finished)
• connection to database, etc.

The following listing shows completed Java program that executes designed report and returns the result in xls format. I commented each action.

package xx.report;
 
import java.util.HashMap;
import java.util.ArrayList;
 
import java.sql.Connection;
 
import oracle.apps.fnd.cp.request.CpContext;
import oracle.apps.fnd.cp.request.JavaConcurrentProgram;
import oracle.apps.fnd.cp.request.LogFile;
import oracle.apps.fnd.cp.request.OutFile;
import oracle.apps.fnd.cp.request.ReqCompletion;
 
import oracle.apps.fnd.util.ParameterList;
import oracle.apps.fnd.util.NameValueType;
 
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperFillManager;
 
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
 
import oracle.apps.fnd.i18n.text.AppsDateFormatter;
import oracle.apps.fnd.i18n.text.AppsNumberFormatter;
 
 
public class XXFndCurrenciesReport implements JavaConcurrentProgram {
 
    public void runProgram(CpContext ctx) {
        // obtain log file
        LogFile log = ctx.getLogFile();
        // obtain connection to database
        Connection connection = ctx.getJDBCConnection();
 
        // obtain input parameters of the concurrent program
 // they are stored in the same order as they were described in the concurrent program
        ArrayList parameters = new ArrayList();    
        ParameterList parameterList = ctx.getParameterList();
 
        while (parameterList.hasMoreElements()) {
            NameValueType currentParameter = parameterList.nextParameter(); 
            parameters.add(currentParameter.getValue());
        }
 
        // form map of parameters to pass into jasper report
        HashMap reportParameters = new HashMap();
        reportParameters.put("P_ACTIVE_TO_DATE", AppsDateFormatter.parseCanonical((String) parameters.get(0)));
        reportParameters.put("P_PRECISION", AppsNumberFormatter.parseCanonical(parameters.get(1).equals("") ? null : (String) parameters.get(1)));
        reportParameters.put("P_NAME_MASK", (String) parameters.get(2));
 
        // run the jasper report
        JasperPrint jasperPrint = null;
        try {
            jasperPrint = JasperFillManager.fillReport("/work/comn/java/reports/fnd_currencies.jasper", reportParameters, connection);
        } catch (Exception _ex) {
            log.writeln("Error while data has been obtaining : " + _ex.getMessage(), 0);
            StackTraceElement ste[] = _ex.getStackTrace();
            for (int i = 0; i < ste.length; i++) log.writeln(ste[i].toString(), 0);
 }  
 
        // export the report's result in xls format
        JRXlsExporter excelExporter = new JRXlsExporter(); 
 
 // set object with returned data 
        excelExporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
 // direct data in xls format to out file of the concurrent program
        excelExporter.setParameter(JRXlsExporterParameter.OUTPUT_FILE_NAME, ctx.getOutFile().getFileName());
 // (optional) cells in exported xls file should have respective format (number, string, date, etc.)
        excelExporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
 // (optional) suppress empty cells between rows (for example, empty cells show breaks between report's pages)
        excelExporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);      
 
        try {         
            excelExporter.exportReport();
        } catch (Exception _ex) {
            log.writeln("Error while data has been exporting into xls : " + _ex.getMessage(), 0);
            StackTraceElement ste[] = _ex.getStackTrace();
            for (int i = 0; i < ste.length; i++) log.writeln(ste[i].toString(), 0);
 }   
 
 // set concurrent program's status
 ctx.getReqCompletion().setCompletion(ReqCompletion.NORMAL, "Completed.");
    }
}

Paste the listing in empty text file and name it XXFndCurrenciesReport.java. Then copy the file to OEBS server to respective folder we have created when prepared environment on OEBS server (in my case it's /work/comn/java/xx/report). Make sure that path to Java compiler exists in PATH system variable (try execute javac -version in command line) or add the path to PATH otherwise. Being in the folder, where .java file is, compile .java file to .class one (execute javac XXFndCurrenciesReport.java in command line). If there is no any exception let's go to the last step.

Register concurrent program in OEBS

Now on server side we have compiled report and compiled java file that runs the report and export its result as output xls file. The last thing we have to do - register new concurrent program in OEBS to afford run the report and get its result from OEBS GUI.

We will register the concurrent program in System Administrator responsibility, change it if you wish.

Login to OEBS and do the following:

• select System Administrator responsibility
• run Concurrent Program Executable form (Concurrent -> Program -> Executable)
• create new executable with the following values and Save it
• Executable : XX Currencies (JasperReports)
• Short Name : XXFNDCRJR
• Application : Application Object Library
• Description : XX Currencies (JasperReports)
• Execution Method : Java Concurrent Program
• Execution File Name : XXFndCurrenciesReport
• Execution File Path : xx.report

• run Concurrent Programs form (Concurrent -> Program -> Define)
• create new concurrent program with the following values and Save it
• Program : XX Currencies (JasperReports)
• Short Name : XXFNDCRJR
• Application : Application Object Library
• Description : XX Currencies (JasperReports)
• Executable -> Name : XXFNDCRJR
• Output -> Format : PostScript
• describe three parameters in Concurrent Program Parameters form (Parameters button):

First parameter

• Seq : 10
• Parameter : P_ACTIVE_TO_DATE
• Value Set : FND_STANDARD_DATE
• Display Size : 11
• Concatenated Description Size : 25
• Prompt : End Date
• Token : P_ACTIVE_TO_DATE

Second parameter

• Seq : 20
• Parameter : P_PRECISION
• Value Set : 2_Number
• Display Size : 2
• Concatenated Description Size : 25
• Prompt : Precision
• Token : P_PRECISION

Third parameter

• Seq : 30
• Parameter : P_NAME_MASK
• Value Set : FND_CHAR100
• Display Size : 25
• Concatenated Description Size : 25
• Prompt : Name Mask
• Token : P_NAME_MASK

• add created concurrent program into request group
• find and remember the request group for System Administrator responsibility
• run Responsibilities form (Security -> Responsibility -> Define)
• find System Administrator responsibility
• remember Name of Request Group - it's System Administrator Reports
• run Request Groups form (Security -> Responsibility -> Request)
• find System Administrator Reports report group
• add new record in Requests table with the following values and Save it
• Type : Program
• Name : XX Currencies (JasperReports)

Run created report

It's time to see created report in action:

• run Requests form (View in main menu -> Requests)
• submit new request (Submit a New Request... button), choose Single Request
• in Submit Request form find XX Currencies (JasperReports) concurrent program (Name field)
• set desired values for parameters
• submit request (Submit button)

• wait while the request is processed
• check out if any exceptions are in log file (View Log... button)
• view result xls file (View Output... button):
• choose Excel as Viewer in appeared form
• choose Open in appeared dialog box

If you want PDF instead of XLS...

To get report's result in PDF format we should change the following in java program

...
//import net.sf.jasperreports.engine.export.JRXlsExporter;
//import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
 
import net.sf.jasperreports.engine.export.JRPdfExporter;
import net.sf.jasperreports.engine.export.JRPdfExporterParameter;
...
        // export the report's result in xls format
        //JRXlsExporter excelExporter = new JRXlsExporter(); 
 JRPdfExporter pdfExporter = new JRPdfExporter(); 
 
 // set object with returned data 
        //excelExporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
 pdfExporter.setParameter(JRPdfExporterParameter.JASPER_PRINT, jasperPrint);
 // direct data in xls format to out file of the concurrent program
        //excelExporter.setParameter(JRXlsExporterParameter.OUTPUT_FILE_NAME, ctx.getOutFile().getFileName());
 pdfExporter.setParameter(JRPdfExporterParameter.OUTPUT_FILE_NAME, ctx.getOutFile().getFileName());      
 
 // (optional) cells in exported xls file should have respective format (number, string, date, etc.)
        //excelExporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
 // (optional) suppress empty cells between rows (for example, empty cells show breaks between report's pages)
        //excelExporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);      
 
        try {         
            //excelExporter.exportReport();
     pdfExporter.exportReport();
...

Also change Output Format in concurrent program definition:

• run Concurrent Programs form (Concurrent -> Program -> Define)
• find program by Short Name XXFNDCRJR
• select PDF in Output -> Format combo box
• save changes

And one more problem I had faced with when investigated the export to PDF. It concerns alphabets which are not supported in fonts are used by iText library (namely it implements export in various formats including PDF). In may case it was cyrillic symbols. All data in cyrillic were not displayed in exported PDF file.

The approach to get cyrillic in PDF report is:

• find family of TrueType fonts which supports cyrillic (I chose Arial font family: arial.ttf, arialbd.ttf, arialbi.ttf, ariali.ttf)
• create a folder on OEBS server to store font's files and copy them into (in my case it was /work/comn/java/reports/fonts)
• open .jrxml report file in iReport, in Report Inspector select all static text and text fields and change font to Arial (combobox on report's toolbar), preview the report to get compiled .jasper file and replace it on OEBS server
• add the following in Java program, copy it on OEBS server and compile:

...
import net.sf.jasperreports.engine.export.FontKey;
import net.sf.jasperreports.engine.export.PdfFont;
...
 JRPdfExporter pdfExporter = new JRPdfExporter(); 
 
 // map font specified in report and font's files
 HashMap fontMap = new HashMap(); 
 // Normal Arial font
 FontKey keyNormal = new FontKey("Arial", false, false); 
 PdfFont fontNormal = new PdfFont("/work/comn/java/reports/fonts/arial.ttf", "Cp1251", false); 
 fontMap.put(keyNormal, fontNormal);
 // Bold Aroal font
 FontKey keyBold = new FontKey("Arial", true, false); 
 PdfFont fontBold = new PdfFont("/work/comn/java/reports/fonts/arialbd.ttf", "Cp1251", false); 
 fontMap.put(keyBold, fontBold);
 // Italic Arial font
 FontKey keyItalic = new FontKey("Arial", false, true); 
 PdfFont fontItalic = new PdfFont("/work/comn/java/reports/fonts/ariali.ttf", "Cp1251", false); 
 fontMap.put(keyItalic, fontItalic);
 // Bold and Italic Arial font
 FontKey keyBoldItalic = new FontKey("Arial", true, true); 
 PdfFont fontBoldItalic = new PdfFont("/work/comn/java/reports/fonts/arialbi.ttf", "Cp1251", false); 
 fontMap.put(keyBoldItalic, fontBoldItalic);
...
 // set object with returned data 
 pdfExporter.setParameter(JRPdfExporterParameter.JASPER_PRINT, jasperPrint);
 // direct data in pdf format to out file of the concurrent program
 pdfExporter.setParameter(JRPdfExporterParameter.OUTPUT_FILE_NAME, ctx.getOutFile().getFileName());      
 
 // set formed map of fonts to exporter
 pdfExporter.setParameter(JRPdfExporterParameter.FONT_MAP, fontMap); 
...

After that PDF report displays cyrillic.

Code snippets have been tested on OEBS 11.5.10.2, JasperReports 3.2.1