Wednesday, September 26th, 2007

Integration of Oracle Apps and XML Publisher

Oracle XML PublisherXML Publisher is important for Oracle Applications, especially for Oracle Reports. Tim dexter’s post mentions the fact that Oracle is now internally converting all of the EBS Oracle Reports to XML Publisher. For all intents and purposes we can safely say that Oracle Reports in the Ebusiness suite are dead and burried (As will Oracle Forms be in a while in favour of the J2EE stack. That at least is my humble opinion. But that’s food for another post). In this post I will cover the issues on how to integrate Oracle E-business Suite Reports and XML Publisher.

The main reason for wanting to use XML Publisher is to produce a report that has identical content to one of Oracle’s standard Reports, but with a much better look-and-feel and one that supports PDF and XLS output, tables, charts, graphics, color, etc. Most customers I know seldom use outputs of the Oracle reports because they were not very user-friendly and hard to use. The output of Oracle reports is always fixed font, they are not an open standard and its user interface is difficult to use. Not to mention the fact that Oracle Reports cannot be used in combination with peoplesoft or siebel. And to make things worse, if you ever needed an extra item of data for your Apps report, the only thing you could do was create a complete new version of the RDF and then alter the code in there, which meant maintaining a parallel version of the report. And to be honest who wants to work with Oracle Reports nowadays anyways.

XML Publisher offers a neat way to run (and convert) your reports under apps. XML Publisher is “hot pluggable” – meaning it is completely based on open standards. As such it can run on any J2EE compliant application server and can access data from practically any database. I think that XML Publisher will become the reporting tool in Oracle Fusion.

But first lets get into generating a standard report in Apps with a RTF template.

It is actually very simple to get some XML out of the eBusiness Suite. The first thing we need to do is get some XML data out of an existing report. To get a sample XML data file, you can run the Oracle report and download the output. Once the data file is loaded, you can start creating a report.

These simple steps show how:

  • Log into Apps with System Administrator Responsibility
  • Navigate to > Concurrent > Program > Define and search for your favorite Oracle Report
  • Locate the required Concurrent Program definition and using the COPY
    button at the bottom left of the form copy the Concurrent Program and
    give it a new Name and Short Name. The main reason for doing this is
    that if Oracle releases a patch that updates the original Concurrent
    Program, it will not affect your new custom one.
  • Change the output format of your new Concurrent Program from the default ‘Text’ to ‘XML’ and Save.
  • Make a note our you new Concurrent Program Short Name as you will need this later.
  • Add your new Concurrent Program to the required Request Group so it
    can be seen by the responsibility you want to run it in.
  • Run the report and save the XML Output to a location on your local
    machine.

Next you must install the XML Publisher Desktop Utility (you can download it on OTN or on Oracle E-delivery). Once you have successfully installed the plug-in you will find a new tool bar and menu entry in MSWord. The XML Publisher Desktop Utility is a plug-in to Microsoft Word that installs XML Publisher Template Builder that help you build templates for your Reports. The templates can be stored as RTF files. To create the RTF template, you must follow these simple steps:

  • Using the MS Word Template Builder Plug in, load the XML Data File by clicking on the Data button and selecting “Load XML Data” and selecting the xml file that you saved earlier.
  • To insert a field in the report use the menu: Insert > Field
  • Now you can select a field and double-click it or drag it to the place in the report. Before you can preview the report you have to save the file as a RTF-file.
  • To insert a table in the report you can use the Table Wizard or Table/Form. (for a good preview on how to that see the XML Publisher Templates by Example document by Tim Dexter on OTN).
  • You can test your RTF template by using the Preview functionality in the Template Builder plugin.

Once satisfied with the created RTF Template, save the template locally and go on and register both the Data Definition and the Template at XML Publisher. The steps are:

Login to eBusiness Suite with the XML Publisher Administrator responsibility and

  • Navigate to > Home > Data Definitions
  • Click on the Create Data Definition button

The Name and Code must match the Short Name of the new Concurrent Program you have created. If this is incorrect XML Publisher will not be able to pick up the Data Definition when the Concurrent Program is run!! Also choose the Application definition under which you registered the Concurrent Program in the Request Group. Click Save/Apply and then that step is completed.

Next we register the XML Template

  • Click the Template Manager tab at the top right of the page and create a new Template Definition.
  • Click on the Create Template button.
  • Here the Code, Name and Short Name can be anything you like but I prefer to use the same name in the Code and Short Name as I used in the Data Template
  • Lookup the name of the Data Definition you have just created.
  • The template type should be RTF for an RTF Word template.
  • Upload the RTF file you created in MSWord (browse for your file and choose the language and territory settings.
  • Click Save/Apply. (we will not go into subtemplates right now)

Now when you run this report through the Concurrent Manager, follow the same steps as when you would run a standard Oracle Report by selecting the Single Request Screen and entering the desired run paramaters. Here you have an additional option to select the desired output format of the report. From the Single Request Screen, click on the Options button and select the desired output. When you run your Concurrent Program and it should automatically pick up your template and output your XML Report.

But what if we wanted to add a new item in our report or change the query in the original report?

What we need to do is to take an RDF file and create a Data Template (an XML
version of the RDF query), and an RTF Template (a template version of
the RDF Layout). You can generate a Data Template using the Report Migration Utility (see my previous post) or build one manually.

These can then be uploaded into eBusiness Suite in much the same way as described in the steps above. The difference is that you have to change the Concurrent
Program and instead of using an Oracle Reports Concurrent EXECUTABLE,
you use a Java Concurrent Program (XML Publisher data engine executable: XDODTEXE).

So in a few simple steps:

  1. Take the report we have just run, in the above scenario and change the Concurrent Program output to XDODTEXE.
  2. Now all we need to do is upload a Data Template in our Data Definition in the XML Publisher Administrator responsibility. (we already uploaded the RTF template in the previous steps).
  3. We can either upload a manually created Data Template or upload one that has been generated by the Reports Migration Utility described in my previous post.
  4. Optionally we can also upload an XML Schema (to validate the xml output) or a preview data xml file (for example the output xml file from our report).

What happens is, when running the Report, the Concurrent manager looks ups the concurrent program definition, finds the XDODTEXE executable and calls it. This could be a Data Template, an Oracle Report, or a pl/sql or any other extraction routine, just as long as its getting XML data. Once the program has completed the Output Post Processor(OPP) comes into the picture. This is the new concurrent manager that handles the XML Publisher requests. It is used to handle to the Data File and calls the XML Publisher APIs to process the data to then generate the output based on the users choice of template, format, etc. The output document is then generated and the XML data is preserved.

I had some dificulty the first time I used the Output Post Processor(OPP). This manager must be up and running and it wasn’t. Bounce your Concurrent Manager and ensure OPP is runnning.

Also, run this SQL: SELECT FND_PROFILE.VALUE(‘CONC_PP_RESPONSE_TIMEOUT’) FROM DUAL;

And set this profile to a value of say 1200, and then bounce the Concurrent Manager again. The full name of this profile option is “Concurrent:OPP Response Timeout”

The user can then either view the document from the SRS forms and print locally or they might have asked that the document be printed directly to a printer.

Thats the integration of apps in XML Publisher in a nutshell.

 Viewed 86889 times by 22132 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.

19 Responses (last comment shown first)

May 24, 2012
RAJA
avatar

Hello plz i want XML publisher template builder .so please tell me whats website where i can download it

[Reply to this comment]


November 21, 2011
avatar

hello,
As I have read the blog above, I was glad because the topic was great. I learned a lot from it. Hope to read more likely this one.

[Reply to this comment]


October 26, 2011
Akil
avatar

Hi,

I have to add more fields in the template file. I couldn’t get the XML output of a Java Concurrent Program. How can I get the XML output so that I can Load XML Data in the Word and insert the field in the rtf template? I did try the process by setting the output type of the Java Concurrent Program (Separate Remittance Advice). But the output is not showing as XML structure. Thanks for any help.

[Reply to this comment]


September 19, 2011
Usman
avatar

Dear,

I want to know in which software i can write or built the data template for XDO engine, like parameters and sql in xml file.

regards,

UA

[Reply to this comment]


July 15, 2010
anil
avatar

anybody know how to find the standard *.rdf (UNDER$APPL_TOP) files kept either in WEB/FORMSERVER OR db/cONC PROGRAM SERVER ?

[Reply to this comment]


May 17, 2010
Dean McCarthy
avatar

Hi,

Yes, I thought the article was informative, although I’m having an issue running the concurrent program as a request. But I also would like to know how to use the MO table views. I see from some of the XML pkg reports that are included with R12.1.1 that the fnd user exits used in the oracle reports have been commented out. So, how do we keep MO security and how do we access the MO views?

Thanks,

[Reply to this comment]


May 7, 2010
Zakia
avatar

Hi,

Thank you for this article. In method one can we not make a change to the XML to accomodate the new item or modification in query? I have used method one successfully to convert a custom RDF in the Oracle Apps.

I am confused as to what the difference is between method one and method two. When would we use method one and when would we use method two.

Thanks,
Zakia

[Reply to this comment]


February 10, 2010
Mike
avatar

I’ve created a check print report using XML publisher. It turned out well and looks really nice. The problem i’m having is when I print more than 10 pages, a mysterious page break shows up. Only 2/3rds of the check will print and then the rest will print on the next page. Not really sure what to do here.

[Reply to this comment]


January 15, 2010
Sarfraz R Ramay
avatar

Hello Mr. Kasper Kombrink,

I am using Oracle EBS 12i. I have successfully installed everything and generated the xml file. I have also successfully installed the BI publisher add-in. I generated a custom report in the xml output format and saved the output to my local machine but when i load the xml in the bi publisher it says that data loaded successfully but no data is showing in MS Word 2007. Since no data is showing i cannnot proceed further to format it or do anything with it. Can you please help me? Can anyone please help me?
here is a screen shot of my problem
http://www.freeimagehosting.net/image.php?5bcfd968ad.jpg

[Reply to this comment]

avatar

Kasper Kombrink Reply:

Hi Sarfraz,
There are a few problems.
1. Your XML is not a usable XML for BI Publisher Desktop.
Your XML is neither a converted RDF nor a Data XML. It contains HTML and other things.
Thats why you have problems opening the XML in BIP Desktop in Word 2007!
What you need to do is a few things:
A) Convert the RDF to XML format using the Migration utility. Depending on the complexity of your XML file you may have to adjust and change this XML because it may have errors. B) once you have done that you can upload this XML into Oracle Apps Template as a Data Template.
C) Then you must generate an XML output file (this is a XML with data) from your report in APPS.
D) This is the file you use in your BI Publisher Word2007 Desktop tool to create a RTF.
When you have done all this then you can change you Concurrent program to use the XML Template you have made in step A and B. GoodLuck.

[Reply to this comment]


September 25, 2009
kon
avatar

This blog does not explain how to extract data from ebs by bi publisher. It only explain that you need to produce a xml data source by running a oracle report. so its ebs quering the database and not bi.

How do you query a multi-org view in EBS from within BI Publisher. For example “select * from ap_invoices”?

[Reply to this comment]


May 28, 2009
kamesh
avatar

I am getting the error unable to find the published output for this request. Please some one help me out to over come this issue and could able to run my xml publisher report.

Thanks,
Kamesh.

[Reply to this comment]


October 13, 2008
K Navin
avatar

I am not able to view the out put if i save the out put format as XML in my concurent program defination .It give the following error:

A semi colon character was expected. Error processing resource ‘http://appsprod.excel.com:9001/OA_CGI/FNDWRR.exe?temp_id=138…

—————————————^

[Reply to this comment]


August 20, 2008
Bhupinder
avatar

You need to add TCA tables in AR tables list

[Reply to this comment]


June 23, 2008
Amit Kumar
avatar

This document is really very good…..
It really helped me when i didn’t know about XML.

Thanks a lot to team who developed this document.
Regards,
Amit

[Reply to this comment]


April 15, 2008
yash
avatar

hi,
i am getting only header tag in my xml output (data is coming if i am running the original report with same parameter) please suggest if this is an error and how to remove this.

[Reply to this comment]


April 15, 2008
norhappy
avatar

anybody know how to read .csv file using oracle form6i?

[Reply to this comment]


October 14, 2007
avatar

The very reason that Oracle introduced BI Publisher (forrmerly known as XML Publisher) is that Oracle has aquired quite a number of product suites (peoplesoft, JD Edwards, Siebel), everyone of them with their own reporting tool. I dont think Oracle wants to support all of these tools and was in need for a one-fit-all reporting solution which should be based on Open Standards, is compatible with all Oracle products, has a better user interface and can produce translatable reports (e.g. xml, rtf, pdf etc). So my belief is that yes BI Publisher works for all the Oracle Products and that eventually BI Publisher will replace all of these different reporting tools.

[Reply to this comment]


October 10, 2007
avatar

Did we do this for PeopleSoft Apps as well?

[Reply to this comment]