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.

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.

Oracle BI Publisher (XML Publisher) 5.6.3 is out. Check out Tim Dexter’s Blog.
Patch is 5472959 – XDO/ORACLE XML PUBLISHER OA ROLLUP PATCH 5.6.3

A selection of the new features:

• Template Builder Enhancements and Name Change
• Report Migration Utility
• Support for Rotated Text in RTF Templates
• Conditional Formatting Enhancement in RTF Templates
• Data Template Enhancements

I’m particularly interested in the Report Migration Utility:

XML Publisher provides a utility to facilitate the conversion of Oracle Reports (version 9i and later) to XML Publisher Reports

I will be coming out with a white paper on how to use this utility soon.