Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Kishore Ryali
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

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

 


Kishore Ryali

Comments   

+1 #1 ramakrishna a 2008-09-10 02:40
Very nice article..
Quote
0 #2 Akash Jain 2008-09-11 06:37
Hi,
When i tried to relpcate the above steps ,i got an errror as
exception in thread "main" java.lang.NoCla ssDefFoundError : oracle/apps/xdo /rdfparser/Data TemplateGenerat or

As such i am not able to generate the pl/sql code .
Can you please help.
Quote
0 #3 kishore Ryali 2008-09-11 06:59
Hi Akash,

Have you included all the mandatory jar/zip files? DataTemplateGen erator is found in j5472959_xdo.zi p, you can download this patch 5472959 from metalink and include in classpath.

Tha nks
Kishore
Quote
0 #4 ellya 2008-09-16 06:10
i did the above steps ...but not sucessfull...ge tting below mentioned error

Exceptio n in thread "main" java.lang.NoCla ssDefFoundError : oracle/apps/xdo /rdfpa
rser/BIP BatchConversion ..

can u please tell me where to place these files

xdocore.jar
Core BIP/XMLP library

aolj.jar
Oracle EBS library




xdoparser.jar
XML parser and XSLT 2.0 engine

xmlparserv2-90 4.jar
Main XML parser/XSLT engine. Available from the JAVA_TOP directory

versioninfo.ja r


collections.ja r
Java Collections

j5472959_xdo.z ip
XDO/ORACLE XML Publisher OA Rollup Patch 5.6.3


thanks
ellya
Quote
0 #5 kishore Ryali 2008-09-16 13:22
Ellya,

You can place required jar/zip files in a folder and add the folder to CLASSPATH env variable.

Kiso re
Quote
0 #6 Shrawani 2008-09-21 11:09
Hi Kisore,
While searching thorugh forums for information on BI Publisher on Siebel 8.1, I came across your blog.This article is very good,the way you have provided detailed information,I liked it very much. I was wondering if you have some information or blog dedicated to BI Publisher on Siebel environment or Migration of ACtuate Reports to BI publisher. I would be glad to get your reply.

Thanks.
Quote
0 #7 lavanya y 2008-09-29 17:30
i am supposed change the Java bursting file. i.e the .java file. I am new to Java.I modified the .java file, how will the class file be updated.
Quote
0 #8 venky 2008-11-03 20:09
Hi,

When i run oracle.apps.xdo .rdfparser.RTFT emplateGenerato r API, i am not getting the output files .rtf and .log files. i got below errors. I don;t know how to fix them

Any help is appreciated.


[Loaded java.text.Numbe rFormat$Field from D:\OraHome_1\jr e\1.4.2\libt.j ar]
[110308_072 136396][][ERROR ] Table structure is not correct. start/end cell does
not match.
[110308_ 072136396][][ER ROR] Table structure is not correct. Cannot start a table
in a row.
[110308_07 2136396][][ERRO R] Table structure is not correct. start/end row does n
ot match.
java.lan g.StringIndexOu tOfBoundsExcept ion: String index out of range: 133
[Loaded java.lang.Stack TraceElement from D:\OraHome_1\jr e\1.4.2\libt.j ar]
at java.lang.Strin g.substring(Unk nown Source)

Venkat
Quote
0 #9 ap 2009-03-12 17:34
I have gone through the first approach, I have got all the files as you mentioned. Form this point am little confused as how to upload what needs to be upload. Could I get step by step instructions? I would really appreciate if you could help to complete my last task.

Thanks in advance.
Quote
0 #10 Ike Wiggins, Minneapolis, MN 2009-06-11 08:19
Why make life harder, might I suggest using these utilities I wrote. It makes the process pretty painless so anyone can do it "TechDev." The post contains a video and the small swing app.

http://bipublisher.blogspot.com/2009/05/bi-publisher-reports6i-to-bip.html
Quote
0 #11 seo company 2009-07-07 10:21
I agree with you.

========= =============== ===========
seo company uk
Quote
0 #12 SandeepReddy 2009-11-29 19:59
Hi Everyone
Please help me ,Iam very to new Oracle Apps.I did simple BackorderReport (RDF)..I'm migrating RDF to BIP..I was followed the two approaches .Iam almost close to last step .Which above mention in this article.I have Mandatory jar/zip file in my local system...
First Approach:
Pleas e see my command prompt>>C:\Docu ments and Settings\samull >java.exe -verbose -cp aolj.jar;xdopar ser.jar;co
llec tions.jar;j5472 959_xdo.zip;ver sioninfo.jar;xm lparserv2-904.j ar oracle.apps.x
d o.rdfparser.Dat aTemplateGenera tor C:\oracleBI\out put\Backorder_F inal.xml

Error >>Could not find the main class: oracle.apps.xdo .rdfparser.Data TemplateGenerat or.
Program will exit.

Second Approach>>>>>

C:\Documents and Settings\samull >java.exe -cp aolj.jar;xdocor e.jar;collectio ns.z
ip;j547295 9_xdo.zip;versi oninfo.jar;xmlp arserv2-904.jar oracle.apps.xdo .rdfparse
r.BIP BatchConversion -source C:\OracleBI\out put -target C:\OracleBI\out put inal

Error>>Could not find the main class: oracle.apps.xdo .rdfparser.Data TemplateGenerat or.
Program will exit.

I very happy some body could help me.But one thing I can say even zero knowledge in Oracle Apps by seeing this website apps2fusion we can learn easily like me ..:)

Thanks,
S andeep
Email:
Quote
0 #13 kishorenine 2009-12-07 10:58
Hi kishore,
This is the command I am using for DATA Template generatation..
java.exe -verbose -cp C:\OracleBI\oc4 j_bi\j2ee\home\ applications\xm lpserver\xmlpse rver\WEB-INF\li b\aolj.jar;C:\O racleBI\oc4j_bi \j2ee\home\appl ications\xmlpse rver\xmlpserver \WEB-INF\lib\xd ocore.jar;C:\Or acleBI\oc4j_bi\ j2ee\home\appli cations\xmlpser ver\xmlpserver\ WEB-INF\lib\xdo parser.jar;C:\O racleBI\oc4j_bi \j2ee\home\appl ications\xmlpse rver\xmlpserver \WEB-INF\lib\co llections.jar;C :\OracleBI\oc4j _bi\j2ee\home\a pplications\xml pserver\xmlpser ver\WEB-INF\lib \versioninfo.ja r;C:\OracleBI\o c4j_bi\j2ee\hom e\applications\ xmlpserver\xmlp server\WEB-INF\ lib\xmlparserv2 .jar;C:\Documen ts and Settings\201927 \Desktop\p54729 59_11i_WINNT\ p5472959_11i_WI NNT\5472959\ j5472959_xdo.zi p oracle.apps.xdo .rdfparser.Data TemplateGenerat or C:\Narayanan\B2 B_Reports\BOM Reports\Codes\E MEA11i Code\MOTBOMBSR. XML

But this is showing following error...after including all the jar/zip file

Exception in thread "main" java.lang.NoCla ssDefFoundError : and
Caused by: java.lang.Class NotFoundExcepti on: and
at java.net.URLCla ssLoader$1.run( URLClassLoader. java:200)
at java.security.A ccessController .doPrivileged(N ative Method)
at java.net.URLCla ssLoader.findCl ass(URLClassLoa der.java:188)
at java.lang.Class Loader.loadClas s(ClassLoader.j ava:306)
at sun.misc.Launch er$AppClassLoad er.loadClass(La uncher.java:276 )
at java.lang.Class Loader.loadClas s(ClassLoader.j ava:251)
at java.lang.Class Loader.loadClas sInternal(Class Loader.java:319 )
[Loaded java.util.Abstr actList$Itr from shared objects file]
[Loaded java.util.Ident ityHashMap$KeyS et from shared objects file]
[Loaded java.util.Ident ityHashMap$Iden tityHashMapIter ator from shared objects fi
le]
[Loaded java.util.Ident ityHashMap$KeyI terator from shared objects file]
[Loaded java.io.DeleteO nExitHook from shared objects file]
[Loaded java.util.Linke dHashSet from shared objects file]
[Loaded java.util.HashM ap$KeySet from shared objects file]
[Loaded java.util.Linke dHashMap$Linked HashIterator from shared objects file]
[Loaded java.util.Linke dHashMap$KeyIte rator from shared objects file]

thanks
K ishore
Quote
0 #14 kishore s 2009-12-08 09:22
I am very happay abt some one could help prevous rquest...
I am trying to convert oracle apps standard RDF Report(huge report)... this seems to very difficult for manual conversion...
Quote
0 #15 Kishore Ryali 2009-12-08 09:49
Hi,

Did you try converting a simple rdf? java.lang.NoCla ssDefFoundError exception looks like classpath is not set to include all jar files.

kishore
Quote
0 #16 Vijay1836 2010-02-03 08:35
Hi all
I have successfully used the rwconverter utility to get the xml file. but after that when using this command i am getting error
java.exe -verbose -cp xdoparser.jar;c ollections.jar; j5472959_xdo.zi p;versioninfo.j ar;xmlparserv2- 904.jar oracle.apps.xdo .rdfparser.Data TemplateGenerat or C:\Temp\output\ POXRQSIN.xml
i have added following to my classpath
C:\migrationli b\aolj.jar;C:\m igrationlib\col lections.jar;C: \migrationlib\j 5472959_xdo.zip ;C:\migrationli b\versioninfo.j ar;C:\migration lib\xdocore.jar ;C:\migrationli b\xdoparser.jar ;C:\migrationli b\xmlparserv2-9 04.jar;
still i am getting error C:\migrationlib \aolj.jar;C:\mi grationlib\coll ections.jar;C:\ migrationlib\j5 472959_xdo.zip; C:\migrationlib \versioninfo.ja r;C:\migrationl ib\xdocore.jar; C:\migrationlib \xdoparser.jar; C:\migrationlib \xmlparserv2-90 4.jar;
Quote
0 #17 Rajeev Agrawal 2010-05-07 08:29
I am having aroung 100 reports developed in buisness objects but now i need to migrate this ton BI publisher,

Any help would be highly appriciated.

C heers,
Rajeev
Quote
0 #18 Ayodhya 2010-10-05 12:20
I converted the Oracle report using this tool, all worked fine, However duing executing the converted report the following error appear. Any Suggestion?


[ 100510_05214161 2][][ERROR] Variable 'p_donor;' is missing....
[10 0510_052141615] [][EXCEPTION] java.sql.SQLExc eption: ORA-06550: line 2, column 39:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
Quote
0 #19 Kawal 2011-03-28 07:44
Hi All,

I have suceeded in converting .rdf file to .rtf,.xdo,.pls and other required files for BI Publisher migration.
In the oracle reports, we have used many in-built report packages like SRW, EXEC_SQL.
When I try to compile .pls files in the DB, I get errors because of the function which are availabe in the above mentioned in-built packages.
Need to ask u guys, Can we use these report packages in BIP. If so, then how can we deploy these packages.

Than ks & Regards,
KP.
Quote
0 #20 RT 2012-01-20 17:49
Is there a place to download all the seeded reports and load as is into bi publisher from ebs?
Quote
0 #21 Hong Wang 2012-05-03 14:32
Hi,

I have downloaded BI Suite Enterprise Edition 10.1.3.3.2 (biee_windows_x 86_101332_disk1 .zip) and unzip on my P
C, but I did not find the files you mentioned. What did I do wrong?

thanks
Hong
Quote
0 #22 Sreeahri 2012-07-11 06:19
Hi,

I have downloaded the patch 5472959 but i didn't find the jar files. Please let me know the location?

Than ks and Regards,
Sreeha ri.
Quote
0 #23 Pier van der Graaf 2012-07-18 07:38
Hong Wang and Others trying to convert reports anno 2012,

The method explained in this blog is pretty much an old method. Take a look at this page:
http://www.oracle.com/technetwork/middleware/bi-publisher/converttobip-1599273.html

Particularly the "Report Conversion Tools" are intresting.

Re gards,
Pier
Quote
0 #24 Seomasterwww 2022-02-17 04:14
We will help you promote your site, backlinks for the site are here inexpensive www.links-for.site: http://links-for.site
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Related Items

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner