BI Publisher Report Migration Utility

Oracle BI Publisher provides a utility for converting reports from RDF reports to BI Publisher reports using RTF templates. This utility can come really handy in migration projects involving large number of oracle reports. Though this process greatly reduces time and effort, all the migrated reports have to be code reviewed to ensure there is no buggy generated code. I will discuss at the end of the article of the scenarios where that can happen.

In Oracle Reports, the data model (SQL query or extraction logic) and layout are contained in a single file. In Oracle BI Publisher the data model and the layout are separate objects, which is a major feature of XML Publisher architecture. It gives a flexible to use the same data model and generate multiple presentations using templates.

The conversion utility generates several files that will make up your report in Oracle BI Publisher. In most cases this will include a PL/SQL specification and body that you will need to create in the database that contains the report trigger code. The utility will also generate a report definition file and a layout template file that you will upload to the Oracle BI Publisher repository.

Once uploaded, test the report to ensure that the output is as expected and make any changes to the report as needed. Some reports will contain structures that the utility cannot convert. These must be manually implemented in the converted reports.

The overall flow for the conversion process is as follows:

1. Run the conversion utility.

2. Load the PL/SQL package into the database.

3. Upload the report to the Oracle BI Publisher repository.

4. Test the report and check the conversion log files to identify any manual modifications needed to complete the conversion.

Pre-requisites:

This section describes the steps required before running the conversion utility.

1. Oracle Reports in XML format:

During the conversion process, the source reports must eventually be in Oracle Reports XML format, which is supported by Reports 9i and above. If your reports are not in XML format, you can use “rwconverter” executable which comes with Reports Installation. rwconverter is available in bin folder in the report installation folder.

I will be using Oracle Developer 10g for this article.

1. Mandatory jar/zip files:

To run the conversion utilities you need the following jar files in your CLASSPATH:

Library	Function
xdocore.jar	Core BIP/XMLP library
aolj.jar	Oracle EBS library
xdoparser.jar	XML parser and XSLT 2.0 engine
xmlparserv2-904.jar	Main XML parser/XSLT engine. Available from the JAVA_TOP directory
versioninfo.jar
collections.jar	Java Collections
j5472959_xdo.zip	XDO/ORACLE XML Publisher OA Rollup Patch 5.6.3

Though Oracle documentation mentions only few of the below jar files, you require the comprehensive list to use the conversion utility. You can get these libraries from JAVA_TOP if your EBS has patch 5472959 installed. Alternatively you can also download the patch from metalink, unzip it in your local machine and put these files in your CLASSPATH individually.

I also got the above files from installing BI Suite Enterprise Edition 10.1.3.3.2 (biee_windows_x86_101332_disk1.zip).The OTN downloads link for BI Suite Enterprise Edition is here. These files are available under $BIPUB_HOME\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib folder.

Include these jar files in CLASSPATH environment variable.

Conversion Process:

This report conversion can be done using two approaches; rather I would say two different APIs.

1. Using combination of DataTemplateGenerator and RTFTemplateGenerator APIs.
2. Using BIPBatchConversion API.

Actually BIPBatchConversion is a wrapper around DataTemplateGenerator and RTFTemplateGenerator APIs.

I will use a simple sales report XXONT_SO with just one field in the layout, for conversion of BI Publisher report.

I will convert the report using the first approach. DataTemplateGenerator API converts the Oracle Reports data model to an XML Publisher data template. RTFTemplateGenerator API converts the Oracle Reports layout to an XML Publisher RTF template.

Approach 1: Using DataTemplateGenerator and RTFTemplateGenerator Combo

Following is step-by-step approach for the conversion:

1. Convert RDF report to XML report.

I’ve created a folder ‘output’ to place all the files from the conversion of oracle report.

Running rwconverter from command prompt, to create xm l version of oracle report.

rwconverter batch=yes source=D:\bip\rmu\XXONT_SO.rdf dest=D:\bip\rmu\output\XXONT_SO.xml dtype=xmlfile overwrite=yes

This creates XXONT_SO.xml in output folder.

1. Creating Data template from Oracle Report using DataTemplateGenerator

Use the below command at command prompt.

java.exe -verbose -cp aolj.jar;xdoparser.jar;collections.jar;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparser.DataTemplateGenerator D:\bip\rmu\output\XXONT_SO.xml

The output lists the data template name and any PL/SQL packaged code created.

Common errors here would be missing of the required jar files. This step generated the Data Template (XXONT_SO_template.xml) and PL/SQL package (XXONT_SOS.pls and XXONT_SOB.pls) for any report trigger code.

Data template XXONT_SO_template.xml generated looks like below:

1. Creating RTF template from Oracle Report using RTFTemplateGenerator.

Use the below command at the command prompt.

java.exe -verbose -cp aolj.jar;xdoparser.jar;collections.jar;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparser.RTFTemplateGenerator D:\bip\rmu\output\XXONT_SO.xml

The output lists the rtf template name and log created.

This step generated the RTF Template (XXONT_SO.rtf)

XXONT_SO.rtf with the form fields, in concurrence with oracle report layout.

Approach 2: Using BIPBatchConversion

BIPBatchConversion is a wrapper around rwconverter, DataTemplateGenerator and RTFTemplateGenerator APIs. We can use this single utility instead of the above approach.

If your source reports are not in Oracle Reports XML format, this utility will do this conversion automatically. However, this requires Oracle Reports Designer 9i or later on the same machine where you will do the conversion so that the Oracle BI Publisher conversion utility can call the rwconverter executable to get the reports into Oracle Reports XML format.

To get Oracle Reports Designer, download Oracle Developer Suite 10g (10.1.2.0.2) from: http://www.oracle.com/technology/software/products/ids/index.html and install Reports Designer.

If your reports are already in Oracle Reports XML format, there is no need to install Oracle Reports.

It takes the following parameters:

· -source — (required) Source directory for Oracle Reports files. All reports must be in the same format - either RDF or XML.

· -target — (required) Target directory to create Oracle BI Publisher report objects. This includes the Oracle BI Publisher Report file (.xdo), the layout template file (.rtf), the PL/SQL package, and log file.

· -oraclehome — (optional) If your reports are in Oracle Reports XML format do not specify this parameter. If your reports are not in Oracle Reports XML format, specify the Oracle home path where Oracle Report Designer (9i or later version) is installed. BIPBatchConversion assumes that rwconverter is contained in the bin directory beneath the Oracle Home path.

BIPBatchConversion requires rwconverter from Oracle Reports to convert the report from RDF format to XML format.

· -debug — (optional) To run the utility in debug mode and write debug statements to the log file.

Command Line Usage:

java ... BIPBatchConversion [-debug] -source SourceDirectory -target TargetDirectory [-oraclehome OracleHomePath]

For our example, I issue the below command at command prompt and generate the same set of files as in the first approach. Note that I didn’t specify the report name, this utility will convert all reports under the source folder.

java.exe -cp aolj.jar;xdocore.jar;collections.zip;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparser.BIPBatchConversion -source D:\bip\rmu -target D:\bip\rmu\bipoutput -oraclehome D:\oracle\Ora10g –debug

Tweaking/Reviewing the converted report:

When converting a more complex Oracle Reports report, the Data Template or PL/SQL may contain minor errors and require manual correction.

• The conversion utility will move all formula columns to the select clause of the SQL query in the data model. In most cases this will not cause a problem. If any of the arguments to the formula column is a summary column that belongs to same Data Source/Data Query, this will not work because the summary column will not be calculated at the time the query is executed.

To correct this problem you will need to remove this formula from the select clause and implement the formula as XSL in your layout template. Most of these formulas are used either for simple addition or summation or currency conversion, formatting, and rounding.

• The conversion utility does not convert any PL/SQL format trigger logic present in the report. Instead the conversion utility writes all the format trigger code to a log file. You will need to implement any corresponding PL/SQL logic as XSL code. As the majority of formatting use simple ‘if’ logic, they can be implemented separately in RTF template using IF/ELSE.

• Minor errors in PL/SQL generated code, if unnoticed can turn into a major mishap. For Example: If a function exists in oracle report which fetches Item Description from a given item_id and organization_id report parameters.

Assume the function looks like below

The code is using organization_id and item_id report parameters, to fetch the item description. Now when this report is converted into BI Publisher report, the pl/sql package has a similar function defined with parameters for item_id and organization_id. Also it uses the name identifiers for the function parameters, which might cause problem if unnoticed.

The generated packaged code for above function, may looks like below

For untrained eye, the above code looks perfectly fine. But the code fails due to the highlighted condition which will always return TRUE and the code goes into exception block because of multiple rows fetched from the select query. So care should be taken for code reviewing the generated code and compare the output of BI Publisher with the oracle report output.

Once the output files are generated, follow the below steps to register and run the BI Publisher report:

• Upload PL/SQL package to the database.
• In XML Publisher Administrator responsibility, create data definition and assign the data template file.
• Create RTF template and assign rtf template file.
• Create concurrent program with short name same as above data definition name and assign XDODTEXE as executable.
• Run the concurrent program and test the output with the original oracle report output.