My previous post was about the integration between Siebel and BI Publisher that was available since Siebel 8.1. As I mentioned back then, the integration is promising, but presented  some serious limitations to BI as a report generation tool for Siebel, and I promised to touch this issue again in a new post. Well, here we go.

With the patch to Siebel 8.1.1.1, some of the isssues have been resolved, while some serious new ones have been created. Our main reason to upgrade (Siebel to 8.1.1.1 and BI Publisher to  10.1.3.4.1) were issues with Active Directory. The Siebel Security Model provided by BI was not documented nor functioning properly in the earlier version. This has been resolved in the new version. The Siebel Security Model basically works like this: a user requests a report, BI then checks in Siebel whether the user has the right responsibility (yes, you need to add the XMLP_DEVELOPER responsibility to each user that needs to create reports) and then BI creates the report and sends it back to Siebel. Works a like charm!

As of version 8.1, Oracle BI Publisher (formerly known as XMLP Publisher) has replaced Actuate as report generating tool for Siebel. During a Siebel Case Management implementation, I got some insights which I’d like to share.

The first step was a rather smooth installation and configuration process. We had BI Publisher up and running within no-time, and only very minimal configuration was required to integrate it with Siebel. By pointing the web services to the right URL’s and adjusting some BI settings, we could start making the first reports.

Siebel ships with a large number of out-of-the-box reports, and it’s easy to create your own. The reports combine RTF-templates from the BI Server with XML data from Siebel into various document types, such as PDF. The templates are easy to maintain using BI Publisher Desktop, a simple plugin for MS Word. XML data is composed by Siebel Integration Objects.

Did you ever had to revise custom reports? DId you ever try to move the address a few millimetres to the right because off the introduction of new corporate stationery? Did you ever get headaches because the reporting tool Oracle Reports Developer was crashing once more destroying your recent modifications?

There is a new kid in (reporting) town…

With the introduction of the reporting tool BI Publisher (formerly XML Publisher) these problems are history. Using the tool introduced by Oracle in 2004 as XML Publisher the main disadvantages of the classic reporting environment become outdated:

1. No open standard
2. Not a user-friendly interface
3. No automatic report translations
4. No support for all formats
5. Extraction of data from a non-Oracle DB is problematic
6. Extensive tool training
7. High development costs
8. ‘Eternal’ maintenance
9. High upgrade costs

You don’t need a program like Oracle XML Publisher to present a PeopleSoft user a formatted MS Word file, filled with PeopleSoft data. If you want to export PeopleSoft data to a formatted MS Word file, just follow these few simple steps:

1. create a formatted xml file in MS Word containing placeholders for the PeopleSoft data. Store this xml in the database.
2. Create a page containing an export button, which calls an IScript
3. Create an IScript which retrieves the template from the database, fetches the data to be inserted from the database and substitutes the placeholders for the data.

In detail:

1. Open MS Word and create and format a page normally. Create placeholders that will contain the data from the database or PeopleCode. For example {NAME}. Be sure to save the file as an XML document. We want to store the plain text “source” of the formatted XML document in the database. For this reason open the file in a plain text editor like Notepad. You can check your placeholder(s) here. Copy the contents and save them in the database. This can be in a custom table or perhaps in the message catalog. In this example we’ll use the message catalog 20.000 and item 1.
2. Create a page in Application Designer and insert a button or hyperlink. The destination of the button is a PeopleCode command of a field in a derived record. For example DERIVED_REC.CALL_ISCRIPT. In the FieldChange event of the above mentioned field, call the ViewURL(URL_str) function. The parameter of this function is an IScript which we’ll create in the next step.

Example:

&sUrl = %Request.RequestURI | “?ICType=Script&ICScriptProgramName=DERIVED_REC.CALL_ISCRIPT.FieldFormula.ISCRIPT_XML_RESPONSE”;

ViewURL(&sUrl);

In newer versions you can use functions like GenerateScriptContentURL to compose the IScript URL.

3. Create an IScript with the following content:

Function ISCRIPT_XML_RESPONSE

/* set filename */

&XMLFileName = “xml_export”;

/* set headers so the browser sends a save dialog box */

&contDisposition = “attachment; filename=” | &XMLFileName | “.doc”;

%Response.SetHeader(“content-disposition”, &contDisposition);

/* set content type to word, so MS Word can be opened */

%Response.SetContentType(“application/msword”);

/* get the template xml file */

&sXMLTpl = MsgGetExplainText(20000, 1, “XML not found”);

/* get the data */

&sName = “John Doe”;

/* substitute placeholder for the data */

&sXMLTpl = Substitute(&sXMLTpl, “{NAME}”, &sName);

/* send to browser */

%Response.Write(&sXMLTpl);

End-Function;

A couple of notes:

• Because we are using an IScript we can’t access the component buffer data. So if you want to use content specific variables in your IScript, create some global variables in step 2 which can be addressed in the IScript.
• Don’t forget to add the IScript to the weblibrary of your permission list.
• When creating the xml template, turn off any spelling or grammar checks for this can disturb the xml placeholders.
• When you’ve made an error in a placeholder, completely retype the placeholder. A partial correction can disturb the xml.

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.