Monday, September 17th, 2007

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


Category: General / Technical
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

9 Responses (last comment shown first)

August 7, 2009
virita
avatar

Hi Lava,

Here is the navigation to upload the Data Template

XML Publisher Administrator–>Dat aDefinition

Create Data Definition

Here you can enter all the deatils to create a data definition and click on ‘APPLY’ button to save the changes.The page navigates to another jsp page where you can find two sections”‘General’ and ‘files’.in the ‘Files’ section you can find a button ‘Data template’ where you can upload your data template file.

Regards,
P.R.Viriita

[Reply to this comment]


avatar

Say you might want to give the Reports6i to BIP Conversion Utilities I wrote a try. It makes the process pretty painless. The post contains a demo video and executable to do the conversion.

http://bipublisher.blogspot.com/2009/05/bi-publisher-reports6i-to-bip.html

Ike

[Reply to this comment]


July 30, 2008
Ashish
avatar

Thanks for very realistic blog. I will be working on a Migration project of Oracle reports to BIP, starting from mid of august. The report which I have to migrate form Oracle reports to BIP is a very big one having sql query of 300 pages & output files contains 40 pages. It took around 12 hrs to execute that report. The existing reports is not part of Oracle apps but a standalone report. Can you please let me know, if I can use conversion utlility as mentioned in your blog to migrate reports? Is there any usefull & realsitic document for such migration? Any other important points I need to keep in mind while working on this project?
I appreciate your reply & value your time.
Best Regards
Ashish

[Reply to this comment]


June 2, 2008
avatar

Hi,

This is really a very good post. I have a question related to XML publisher. I will be thankful if you could please answer me this question as i have asked this question to many forums and no body has even replied to me till now.

how can we create parameters in XML report in case of new report to be developed? Also if there is any standard report with parameters that we are converting into XML, then how do we proceed to create the parameters in XML? Also how can we replace formula columns (of Oracle report6i) in XML publisher?

I am desperatley looking forward for answer from you guys.

Thanks,

Regards,
Anshul Singhal

[Reply to this comment]


March 20, 2008
avatar

Hi Lava,
I am not familiar yet with 12i. I guess you can read the Oracle XML Publisher User’s Guide. Good luck.

[Reply to this comment]


March 18, 2008
Lava
avatar

Hi..

i am suposed to develop a AP Cheque report into BI publisher in 12i. I am pretty comforatble with RDF->RTF->XML report..
But with Data template i feel easy but trouble some.. And on top I am not able to find the place where we upload the Data template. Pls help…

I went to XML Publisher resp-> Data Template-> I cud see only the general Section.. I cant find the files Section. Pls help

-Lava

[Reply to this comment]


March 12, 2008
avatar

Hi Kirk,
It seems that you are missing some of the java classes in your classpath. Not sure exactly how the pieces fit together, but I did manage to get it working.
I finally solved the problem. It turns out the DataTemplateGenerator and RFTTemplateGenerator classes are not provided with BI Publisher download, but is a part of the XML Publisher 5.6.3 download. I downloaded the XMLP 5.6.3 and pulled out the file j5472959_xdo.zip. I included j5472959_xdo.zip in my class pathstring and everything worked as it should have.
I put the following in files in my classpath:
Collections.zip, xmlparserv2-904 – available from the JAVA_TOP directories, aolj.jar, xdocore.jar, xmlparserv2-904.jar and collections.jar. In an APPS environment you can also add a pointer to the Apps JAVA_TOP – under here are the necessary XML Publisher libraries.
The command I ultimately used was:
java.exe -verbose -cp xdoparser.jar;collections.jar;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparser.RTFTemplateGenerator C:\ReportTest\[REPORTNAME].xml
There’s a lot of references on OTN forums by the way.
As for the the BIPBatchConversion utility: I beleive this isn’t a jar file or an executable as part of some BIP installation but a simple script oracle developed internally and uses as part of their own Migration offerering.
http://www.oracle.com/technology/products/xml-publisher/docs/BIPBurstingImplementationService.ppt
It looks something like this:
#!/bin/sh
# This script will generate a report for each template presnet in the current directory
# Create a variable to hold the classpath
classpath=”DIR/collections.zip:DIR/xmlparserv2-904.zip:JAVA_TOP directory”
if [ $# -eq 0 ]
then
for file in *.rdf
do
echo “Processing … $file”
if test -f $file
then
# Convert the rdf to xml
echo yes | $ORACLE_HOME/bin/rwconverter.sh batch=yes source=$file dest=$file dtype=xmlfile overwrite=yes; \
# Create a variable to hold the new xml file name, this is just a simple replace
# statement
xfile=”${file//rdf/xml}”;
# Generate the data template plus plsql
echo yes | /local/java/jdk1.5.0_06/bin/java -classpath $classpath oracle.apps.xdo.rdfparser.DataTemplateGenerator $xfile;
# Generate the RTF template
echo yes | /local/java/jdk1.5.0_06/bin/java –classpath $classpath oracle.apps.xdo.rdfparser.RTFTemplateGenerator $xfile;
fi
done
else
echo usage: $0
echo this script will generate a data template and supporting plsql and an RTF template in the current directory
fi
regards,

[Reply to this comment]


March 11, 2008
Kirk Shorting
avatar

Kasper,

I have read every Oralce document, blog and technical forum I can find on creating an RFT template from an RDF. The process is always described as simple and the instuctions seem straight forward and yet I cannot get this to work. The most frequent error is “Exception in thread main java.Lang.NoCLassDefFoundError: oracle/apps/xdo/rdfparser/RTFTemplateGenerator”. Evidently, the classes are not being defined properly, but I have made every attempt to define these and nothing works. I have the classes installed. I am new to using java so I assume I have missed something elemental.
This should be as simple as installing the software and running it, but it isn’t. Any help would be appreciated. I don’t look forward to rewriting hundreds of custom reports using MS-Word and BI Publisher, but right now, that is our only option. We have converted 10 reports so far manually.

Thanks,

PS There are references to an executable called “BIBatchconverter”. This is also described as “simple” to use and yet I cannot find this file on any installation of BI that I have installed on my laptop (5.6.3 through 10.1.3.3.1) and there is no reference to it’s existence on MetaLink. Odd!

[Reply to this comment]


March 4, 2008
rupali
avatar

i m designing new database from existing database so i need to migrate
all data from old database to new database so can you hemp me hoe to do that?

[Reply to this comment]