Oracle Reports Migration to BI Publisher in EBS – Part 2

Run the Conversion Command Line Tool

The conversion utility is a Java command line tool and requires a set of BI Publisher related Java library (JAR) files, which you can find from your BI Publisher Server or Desktop installation. In order to run the conversion utility you need to set CLASSPATH with the library files first. If you have BI Publisher server installed then the below is an example location where you can find such JAR files.

e.g. D:\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib

Here is a list of the library files that are required to be set in the CLASSPATH.

• Collections.jar
• xmlparserv2-904.jar
• xdocore.jar
• aolj.jar

The conversion command line tool takes the following parameters:

Parameter Name	Required	Description
source	Must	Source directory for Oracle Reports files. All reports must be in the same format - either RDF or XML.
target	Must	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. This folder must exist before the command is run.
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.
debug	Optional	To run the utility in debug mode and write debug statements to the log file.

Here is an example of how you can run the command line tool. First make sure you have set the CLASSPATH, then run the tool.

>set CLASSPATH= D:\lib\collections.jar; D:\lib\xmlparserv2-904.jar; D:\lib\xdocore.jar;D:\lib\aolj.jar >java.exe oracle.apps.xdo.rdfparser.BIPBatchConversion -source D:\Reports\ARCUST.rdf –target C:\Reports\output -oraclehome D:\DevSuiteHome_1012 –debug

After you run the tool successfully you should get a set of files including Data Template, RTF Template, PLSQL package files, log file, etc. Now you need to review the log file, validate each file and perform the post conversion tasks.

Validate and Compile PLSQL Package

PLSQL Package specification and body files are generated from the conversion tool. This PLSQL package can be called from BI Publisher Data Template as a report trigger. It can be called before the report run (Before Trigger) or after (After Trigger). This PLSQL package needs to be reviewed and you need to manually run the files against a database to create. This is required only when you use Data Template to generate the reporting data. If you will keep using Oracle Reports to generate the data then you don’t need the PLSQL package to be created in the database.

 

Validate Data Template

Now you need to validate the Data Template file. Make sure all the SQL queries are right and the SQL columns and XML elements are mapped appropriately. When you have multiple queries and they have parent-child relationship the XML structure definition might get complex so you want to make sure the grouping structure in the XML structure definition is defined correctly. Also, the Before/After triggers are called in this Data Template so you need to review the PLSQL package and function names. Lastly the parameter definition needs to be reviewed carefully. Any parameters which are used in the PLSQL package are also need to be defined here correctly.

Validate RTF Template

The layout and formatting are very hard to be precise especially when the reports are pixel-perfect and have complicated logics. So you want to review the converted RTF Template and start updating it to mimic the original report layout or follow the newly defined report layout requirements.

When you validate the RTF Template you would need a XML data file to preview the report final output in a certain format such as PDF. You can download the XML data file by running the original concurrent program after changing its output format to ‘XML’.

Post Migration Tasks - Check Log File

When you look at the log file, which is generated by the conversion tool, you will find the issues encountered during the conversion and manual changes required to complete the report. The below is a part of an example log file content, which shows one object called ‘F_COMM’ was not converted by the tool. 

The following format triggers which are the part of RDF, has not been supported in this Template: -------------------------------------------------------------------------- Object Name : F_COMM ========================================================= PL/Sql code function F_COMMFormatTrigger return boolean is … …

This is because the ‘F_COMM’ uses ‘F_COMMFormatTrigger’ formatting trigger, in Oracle Reports and that is not supported by the conversion tool to be converted. So you need to manually convert this functionality into the RTF Template file by using ‘Conditional Formatting’ functionality.

So make sure that you review the log file and see what is not converted and manually add such functionality to the RTF Template file if necessary. Not being converted by the tool doesn’t mean BI Publisher can not support such. Any functionality available in Oracle Reports can be supported by BI Publisher, it’s just a different way to implement the same functionality. One thing to note, since BI Publisher report consists of two parts, one is a data generation and another is layout and formatting part. So something to do with data such as calculation, aggregation, condition, etc can be done by either Data Template or RTF Template. It really depends on the requirements. In general it’s better to do such in Data Template because 1) you can use the database power to do such calculation hence the performance tend to be better 2) once you have implemented the logic in the Data Template then you can reuse with multiple RTF Template files instead of implementing it in each RTF Template files.

Post Migration Tasks – Others

There might be some other items that you want to add to the converted RTF Template file than the ones you find in the log file. For example you might want to add images or do an appropriate number/date formatting. When you find some differences after the validation of the RTF Template file this is the time to fill the gap by implementing such functionality with BI Publisher methodology.

Next Step

After you have completed all the tasks above now you are ready to start registering the converted BI Publisher report template files to Oracle EBS environment to complete the overall conversion process. I will cover how to register the files to EBS tomorrow. So stay tuned!