Oracle BI Report Migration Utility

BI Publisher provides a utility to facilitate the conversion of Oracle Reports (version 9i and later) to BI Publisher Reports. The conversion is a two-step process requiring a data model conversion and a layout conversion using the following new APIs:

• 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.

While using this utility, I ran accross a number of errors and an incomplete user guide. Migrating Apps Reports is certainly not a straightforward task. So lets look into this migration utility.

In Oracle Report the Data Model (data extraction logic) and Layout (presentation), both are embedded together as an rdf file. In BI Publisher both of these layers are separated.

The migration APIs accept an Oracle Report in XML Format, this format is only supported in Oracle Reports 9i and above. Therefore you need to have a 9i+ version of Oracle Reports available. For R12 the Oracle Reports version in the techstack is 10.1.3 so the conversion to RDF XML is straightforward. For Release 11i the version is 6i, so you will need a higher version of Oracle Reports available somewhere on your system.

To get the Oracle Report in XML format from RDF format, either use Oracle Report Designer or Oracle Report Rwconverter.exe utility under $ORACLE_HOME/bin (by starting Rwconverter a small program starts with which you can convert reports to a number of different formats).

For this example I used the Packing Slip Report (WSHRDPAK.rdf) in the shipping execution application in apps (the wshrdpak report is probably one of those corner cases Tim Dexter talks about in his post).

Example:

%ORACLE_HOME%\bin\rwconverter batch=yes source=E:\Temp\wshrdpak.rdf dest=E:\Temp\wshrdpak.xml dtype=xmlfile overwrite=yes

This will convert the binary RDF file into an RDF-XML format that can then be consumed by the conversion APIs.

I had some problems with using these APIs locally. I needed some extra libraries to be in my class path to be able to work with the APIs without annoying compile errors (or worse incoherent java errors). After some searching I found this list:

xdocore.jar – the core BIP/XMLP library
aolj.jar – this is an Oracle EBS library, we need it whether you’re developing in EBS or not
i18nAPI_v3.jar – this is the i18n library used for localization functions
xdoparser.jar – this is the scalable XML parser and XSLT 2.0 engine
xmlparserv2-904.jar – the main XML parser/XSLT engine – available from the JAVA_TOP directory
bipres.jar – charting library
bicmn.jar – charting library
jewt.jar – charting support library
share.jar – charting support library
collections.jar – you only need this if you are working with the delivery APIs or bursting engine.

You can get these libraries on your EBS installation but this requires patch 5472959 – XDO/ORACLE XML PUBLISHER OA ROLLUP PATCH 5.6.3. You can also download the patch, unzip it and put these files in your classpath individually(or get Jdeveloper and use these files as custom libraries in your project).

For example:

java.exe -verbose -cp xdoparser.jar;collections.jar;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparser.RTFTemplateGenerator H:\report\wshrdpak.xml

The next step is to use the DataTemplateGenerator API to migrate the Oracle Reports Data Model to a DataTemplate and associated PL/SQL logic to PL/SQL Package (specification and body).

The API can be called through the command line. This will generate following output files.

• DataTemplate (wshrdpak_template.xml)
• Default PL/SQL package specification (wshrdpakS.pls)
• Default PL/SQL package body (wshrdpakB.pls).

Example :

Javaw.exe oracle.apps.xdo.rdfparser.DataTemplateGenerator E:\Temp\wshrdpak.xml

Next we can use RTFTemplateGenerator API to migrate the Oracle Reports layout to an XML Publisher RTF template. Since there is no support of PL/SQL in RTF Template, the process does not migrate any format trigger logic present in the report. Instead the generator writes all the format trigger code to log file. You will need to implement any corresponding PL/SQL logic as XSL code. The majority of Oracle Reports use simple ‘if’ formatting logic that can be converted relatively easily. To aid in this process, the resulting RTF template will contain formfields that hold the format trigger names that are called, these fields will be highlighted in red. You can then refer to the log to find the actual PL/SQL code used in the original Oracle Report.

The API can be called through the command line or through a shell script. This will generate following output files.

• RTF Template
• Log file

Example :

javaw.exe oracle.apps.xdo.rdfparser.RTFTemplateGenerator E:\Temp\wshrdpak.xml

Output files (I didn’t get any of these, but for the time being couldn’t care because I had to build my own customized RTF anyway, other more simple reports did generate a RTF).

• RTF Template: E:\Temp\wshrdpak.rtf
• Log File : E:\Temp\wshrdpak.log

When converting the Packing Slip Report I came to the following conclusions:

1. Because of the complexity of Oracle Reports the Data Template or the PL/SQL package can contain errors and will require manual correction.
2. Format triggers are not supported. The format trigger logic should be implemented separately though XSLT.
3. If formula column references the summary column as a parameter and the summary column belongs to same Data Source/Data Query, this implementation is not supported in the Data Template according to the documentation. This is because of all the formula columns moved to select statement and the summary column value is not available while executing the formula.

The Packing slip report gave me a lot of headaches. First of all it didn’t even generate a decent RTF file as with other reports. Also I had to correct the XML Data Template and the packages manually, paramaters were wrongly declared or missing, the generated xml data template was also missing paramaters and many were also declared wrong. And to make things worse trying to generate a RTF file left me with incomprehensible java errors. And to top that oracle has, since the 5.6.2 version, added extra undocumented features allowing for extra element tags after the data structure element tag.

We can use extra element tags that retrieve extra data from a package that will generate extra data for our report. The Oracle BIP User’s Guide (Release 10.1.3.2) doesn’t describe these tags. But a lot of the Oracle Reports that are converted by the conversion utility contain these extra element tags.

I added these extra tags in the example data template used in chapter 4 of the BIP of the user guide:

<?xml version=”1.0″ encoding=”WINDOWS-1252″ ?>- The template is named, an optional description
- can be provided and the default package, if any, is identified:
<dataTemplate name=”Employee Listing” description=”List of
Employees” dataSourceRef=”ORCL_DB1″ defaultPackage=”employee”
version=”1.0″>
<parameters>- Defines a single parameter for the Department Number
- with default of 20:
<parameter name=”p_DEPTNO” dataType=”character”
defaultValue=”20″/>
</parameters>
<dataQuery>
<sqlStatement name=”Q1″>- This extracts the department information based on a
- where clause from a pl/sql package:
<![CDATA[SELECT DEPTNO,DNAME,LOC from dept
where &pwhereclause
order by deptno]]>
</sqlStatement>
<sqlStatement name=”Q2″>- This second query extracts the employee data and joins to
- the parent query using a bind variable, :DEPTNO
<![CDATA[SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl
(COMM,0) COMM
from EMP
WHERE DEPTNO = :DEPTNO ]]>
</sqlStatement>
</dataQuery>- A call is made to a before fetch trigger to set the
- where clause variable in the department query, &pwhereclause:
<dataTrigger name=”beforeReport”
source=”employee.beforeReportTrigger”/>
<dataStructure>- The following section specifies the XML hierarchy
- for the returning data:
– here we can add a tag to get a department name from a package instead of from the Q1 –query which in turn can be used in the element tag (notice how I can use the output
–from the first function as input for a second):
<element datatype=”varchar2″ name=”f_deptname” value=”employee_pkg.f_get_deptname(:p_deptno)”/>
<element datatype=”varchar2″ name=”f_loc” value=”employee_pkg.f_get_loc(:f_deptname)”/>
<group name=”G_DEPT” source=”Q1″
groupFilter=”employee.G_DEPTFilter(:DEPT_NUMBER)”>- There is a group filter placed on the DEPT group.
- This is returned from the employee.G_DEPTFilter plsql package.
- It passes the DEPT_NUMBER value (“name” attribute) rather
- than the DEPTNO value (“value” attribute)
<element name=”DEPT_NUMBER” value=”DEPTNO” />
<element name=”DEPT_NAME” value=”f_deptname“/>- This creates a summary total at the department level based
- on the salaries at the employee level for each department:
<element name=”DEPTSAL” value=”G_EMP.SALARY”
function=”SUM()”/>
<element name=”LOCATION” value=”f_loc” />
<group name=”G_EMP” source=”Q2″>
<element name=”EMPLOYEE_NUMBER” value=”EMPNO” />
<element name=”NAME” value=”ENAME”/>
<element name=”JOB” value=”JOB” />
<element name=”MANAGER” value=”MGR”/>
<element name= “HIREDATE” value=”HIREDATE”/>
<element name=”SALARY” value=”SAL”/>
</group>
</group>
</dataStructure>

4-32 Oracle Business Intelligence Publisher User’s Guide.

By using this extra element calling a function in our package (with parameters) to return the department name (that in turn was used as input for a second function to return the location) we can use this name in our output report. This is very usefull if you need to retrieve extra data that you don’t want or can’t get from the default queries in your XML data template. This feature is not very well documented in the BIP User’s Guide.

Here’s a part of the xml data template output from the wshrdpak.rdf report after the conversion (as you can see a number of tags like the one described above were generated and used in the report):

<dataTrigger name=”beforeReportTrigger” source=”xxint_wshrdpak.beforereport”/>
<datastructure>
<element datatype=”number” function=”count” name=”cs_slip_count” value=”g_c_del_customer_id.c_q2_delivery_id”/>
<element datatype=”varchar2″ name=”cp_warehouse_name” value=”xxint_wshrdpak.cp_warehouse_name_p”/>
<element datatype=”varchar2″ name=”cp_draft_or_final” value=”xxint_wshrdpak.cp_draft_or_final_p”/>
<element datatype=”date” name=”cp_print_date” value=”xxint_wshrdpak.cp_print_date_p”/>
<element datatype=”varchar2″ name=”cp_rlm_print_cum_data” value=”xxint_wshrdpak.cp_rlm_print_cum_data_p”/>
<element datatype=”varchar2″ name=”cp_source_code” value=”xxint_wshrdpak.cp_source_code_p”/>
<element datatype=”number” name=”cp_bill_to_contact_id” value=”xxint_wshrdpak.cp_bill_to_contact_id_p”/>
<element datatype=”number” name=”cp_ship_to_contact_id” value=”xxint_wshrdpak.cp_ship_to_contact_id_p”/>
<element datatype=”varchar2″ name=”cp_bill_address_line_1″ value=”xxint_wshrdpak.cp_bill_address_line_1_p”/>
<element datatype=”varchar2″ name=”cp_bill_address_line_2″ value=”xxint_wshrdpak.cp_bill_address_line_2_p”/>
<element datatype=”varchar2″ name=”cp_bill_address_line_3″ value=”xxint_wshrdpak.cp_bill_address_line_3_p”/>
<element datatype=”varchar2″ name=”cp_bill_town_or_city” value=”xxint_wshrdpak.cp_bill_town_or_city_p”/>
<element datatype=”varchar2″ name=”cp_bill_region” value=”xxint_wshrdpak.cp_bill_region_p”/>
<element datatype=”varchar2″ name=”cp_bill_postal_code” value=”xxint_wshrdpak.cp_bill_postal_code_p”/>
<element datatype=”varchar2″ name=”cp_bill_country” value=”xxint_wshrdpak.cp_bill_country_p”/>
<element datatype=”number” name=”f_del_detail_id” value=”xxint_wshrdpak.f_del_detail_id1formula(:p_delivery_id)”/>
<element datatype=”number” name=”f_oe_line_id” value=”xxint_wshrdpak.f_oe_line_id1formula()”/>
<element datatype=”varchar2″ name=”f_bill_to_cust_name” value=”xxint_wshrdpak.f_bill_to_cust_name1formula(:f_oe_line_id)”/>
<element datatype=”number” name=”f_ship_to_site_use_id” value=”xxint_wshrdpak.f_ship_to_site_use_id1formula(:f_del_detail_id)”/>
<element datatype=”varchar2″ name=”f_ship_to_cust_name” value=”xxint_wshrdpak.f_ship_to_cust_name1formula(:f_ship_to_site_use_id)”/>
<element datatype=”varchar2″ name=”cf_line_tax_code” value=”xxint_wshrdpak.cf_line_tax_code1formula(:f_oe_line_id)”/>
<element datatype=”number” name=”f_bill_to_loc_id” value=”xxint_wshrdpak.f_bill_to_loc_id1formula(:f_oe_line_id)”/>
<element datatype=”varchar2″ name=”cf_ship_to_contact” value=”xxint_wshrdpak.cf_ship_to_contact1formula()”/>
<element datatype=”varchar2″ name=”cf_bill_to_contact” value=”xxint_wshrdpak.cf_bill_to_contact1formula()”/>
<element datatype=”number” name=”cf_oe_line_id” value=”xxint_wshrdpak.cf_oe_line_id1formula()”/>
<element datatype=”number” name=”cf_bill_to_loc” value=”xxint_wshrdpak.cf_bill_to_loc1formula(:cf_oe_line_id, :f_oe_line_id)”/>
<element datatype=”varchar2″ name=”cp_bill_address_line_4″ value=”xxint_wshrdpak.cp_bill_address_line_4_p”/>
<element datatype=”varchar2″ name=”rp_report_name” value=”xxint_wshrdpak.rp_report_name_p”/>
<group datatype=”varchar2″ name=”g_f_del_detail_id” source=”q_delivery_ids”>

But my main conclusion is that I am sadly getting used to oracle delivering partially complete tools like this one. If anyone can get this report working in one smooth migration, I would really like to hear how they did it. I am going to pursue the way we can use these data templates in Apps over the next few postings and try to get into how to run and deploy these reports.

 Viewed 48353 times by 11925 visitors

Posted by: Kasper Kombrink (6 post(s)).