Integration of Oracle Apps and XML Publisher

XML 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

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