BI Publisher Integration with Oracle Apps

BI Publisher Integration with Oracle Apps

Before BI Publisher was released as a Product that was integrated with Oracle Applications, Oracle Reports 6i was the most widely used Tool for generating Report outputs and print documents like PO’s, Invoices, statements etc…

Complex Report layouts often took longer to create and test using the Oracle Reports Tool. With the launch of BI Publisher (originally XML Publisher), Oracle has made a breakthrough with the ease and simplicity by which report outputs can be generated.

To be able to use BI Publisher with Oracle Apps, Patches need to be applied which are available for download from metalink.

This article provides an insight into the components involved in creating an end to end BI Publisher solution for Oracle Apps Concurrent Programs.

BI Publisher makes use of XML data files and templates to generate a PDF output.

XML Data file: The XML Data file can be generated in multiple ways from Oracle.

One way would be to set the option on the Concurrent Program Definition to generate an XML file. Another way would be to use plsql code to generate the XML. This XML file then becomes the input for the XML Publisher Concurrent Programs which processes it with the associated template to produce the output.

Pseudo XML data file

<?xml version=”1.0″?>

<!– Generated by Oracle Reports version 6.0.8.25.0 –>

<XX_PA_XML_INV_PRT>

<LIST_G_INV_HEADER>

<G_INV_HEADER>

<SOURCE></SOURCE>

<INVOICE_FORMAT></INVOICE_FORMAT>

<CUSTOMER>10000   ABC.</CUSTOMER>

<PRODUCT>000   ABC PRODUCT.</PRODUCT>

<DRAFT_INVOICE_NUM>1</DRAFT_INVOICE_NUM>

<INV_CURRENCY_CODE>USD</INV_CURRENCY_CODE>

XML Publisher templates: These templates can be any of MS Word (.rtf )RTF, Adobe Acrobat(.pdf)PDF, XML, eText, MS-Excel

PDF and RTF templates Creation

• PDF templates:-

PDF templates require the Adobe Standard Software. An already existing .pdf file can be used to prepare a template for XML Publisher. In most cases there is little or no amount of work involved in designing the layout, as the form fields are placed on a predesigned available blank layout. It is important to note that no matter what the version of the Adobe Acrobat Standard software you use to create the templates, all templates must be converted to Acrobat PDF Version 1.4. As of date only PDF templates Version 1.4 are supported with XML Publisher. This can be achieved by Navigating to AdvancedàPDF Optimizer. Note the current version and use the option Make compatible with: Acrobat 5.0 and later.

No logic of any kind can be used on PDF templates, which makes it rigid and pushes back the task of including complex logic in the source generating the XML Data. However it is recommended to use PDF templates in standard Forms (Government or other eg W2,), where the template for the form is subject to very little or no change at all.

It is therefore imperative to analyze business requirements thoroughly and decide on the usability of  rtf or pdf templates at the outset of the implementation.

Creation of PDF Templates:

To create a pdf template for BI Publisher, form fields need to be added.

These form fields contain XML tags that assign XML data values to the fields at runtime.

To add form fields, open a .pdf file in Acrobat standard.

Navigate ToolsàFormsàText field tool.

Draw the text form field on the pdf where you want the data to appear. Double click on the form field and add the XML tag to the Name field in the Properties box. Note that the Name added should be available in the XML Data tree file.eg: If POH_PO_NUM is an XML tag that holds the Purchase Order Number in XML Data file, you will need to add POH_PO_NUM to the Name field in the Properties for that form field on the PDF template. If you wish to use names that are different from the XML file, a mapping feature is available in the Template Definition to map the PDF template form field names with the XML tag names.

Below is a screenshot of a PDF template. You can set the properties for the data that will be displayed by this form field. Some of the properties you can set are the font type, font size, multi-line…

• RTF templates:-

RTF templates are easier to use and support complex logic. These templates can be created with any Version of MS Word. The template file should be of .rtf format.

Designing a complex template structurally can be a little more tedious as compared to PDF templates, as MS Word is primarily not a design tool and most rtf template designs would have to be created from scratch.RTF templates can be used where

–there are many groups and subgroups with data,

–conditional formatting of form fields is required

–conditional display of data is required

–spooling multiple pages with different header/footer.

The above is a list off of the many more uses of the rtf template.

Creation of RTF Templates.

To create a rtf template for XML Publisher, you need to first design the template placing tables, nested tables, assigning the header/footer, setting the type landscape/portrait, setting the margins…Once the layout design is ready form fields need to be added.

In MS Word 2008 version Navigate to Developer, the form fields can be found under the Control option.

These form fields contain XML tags that assign XML data values to the fields at runtime.

Draw the text form field on the rtf template. Double click on the form field to add the XML tag. You can enter in the XML tag name or any name in the Default Text field if you want to mask the actual XML name or if you want to give the xml tag a more meaningful name.

Click on Add help text to enter the XML tag in the format <?TRANSACTION_DATE?>, transaction_date being an xml tag in the input XML data file.

Note that this is also the area for adding any kind of logic to be applied on the data group.

However there is a limit on the number of characters that can be added to this area.

If the logic you intend to add has too many characters, the Help Key can be used.

Below is a screenshot of an RTF template. You can set the properties for the data directly from the MS word toolbar –Home which has Text font, size, colour etc…

Some of the properties you can set are the font type, font size, multi-line…All you need to do is select the form field and set the properties from the toolbar.

Note: As of now MS word does not allow to create form fields in the header or footer section of the .rtf template. The alternative is to write the XML tag the way it is in the header and footer as can be seen with <?CUSTOMER?>.The grey areas are the form fields on the template.

XML Publisher Concurrent Programs in Oracle APPS (Ebusiness suite):

The following programs are an out of the box solution provided for XML Publisher reports. These programs make a call to java class files to integrate the XML data file and the templates to produce an output. Based on the requirements, you may use either one of the following programs

1) Program: XML Report Publisher

Short Name: XDOREPPB

This program can be used when no delivery channel like email, fax,… is required. Using this program enables the user to view the output at will from the View Output tab of the concurrent Program that runs the Oracle report. The user can also print the output by setting the print options while submitting the Concurrent Program.

2) Program: XML Publisher Report Bursting Program

Short Name: XDOBURSTREP

This program is used when there are multiple delivery channels involved, for eg: the output document needs to be printed emailed as well as faxed to the requestor/vendor…

Or when there is a range of data to be printed. In this case Bursting reuses the same template to generate a new file for each data set in the XML file.

To implement bursting, a control file is created. This control file is an XML file that contains delivery channel information like email server, print server, email address, no of copies, template, printer name….

The values can be hard coded in the file but it is best to assign these values dynamically at run time. The bursting control file reads these values from the XML data files through variables. The bursting program creates the output based on the template and the xml data file and delivers it to the channels specified.

———————————————————————————————————————

XML Publisher Setups

1) Use an existing Concurrent Program like PO Print or Invoice Print or create a new one based on your requirement.

Ensure the following are set on the Concurrent Program Definition

System Administrator ->Concurrent Program Define

Query your concurrent program. If the executable is an Oracle Report,

In the output section, set the following

Format = XML

Style= PDF Publisher

2) XML Publisher Administrator Responsibility- Create Data and Template Definitions

1. Create a new data definition

XML Publisher Administrator Data Definition

Name: Enter a name for the data definition.

Code: Enter the short_name of the concurrent program which generates the XML file.

The short_name associates XML Publisher setups with the Oracle Reports concurrent Program. The Code for the Data definition should always be the same as the short_name of the concurrent program for XML Publisher reporting to work.

Application: Enter the same application that the Oracle Report concurrent Program uses.

Click on apply to put this Data Definition into effect.

You can alternatively add the following files to the Data Definition based on your setup.

XML Schema: A .xsd file needs to be uploaded when using PDF templates where the custom form field names need to be mapped with the XML tag names.

Data Template: A sample data xml file can be uploaded to this field.

Preview Data: Adding a sample .xml data file enables to preview the output from the template Definition.

Bursting Control file:The .xml control file has to be uploaded to this field when using bursting of the XML data file for generating outputs. The XDOBURSTREP program uses this file to process the output.

Sample bursting control file (Skip this if not using Bursting)

Note: If using bursting, to use the out of the box XML Publisher Report Bursting Program,

You can use the FND_REQUEST.SUBMIT_REQUEST in the After Report trigger in Oracle Reports or in your plsql code depending on your source file which generates the xml file to automate the complete process.

1.      Create a new Template Definition

XML Publisher Administrator Template Definition

Name: Enter a name for the template definition

Code: Enter a short name for the template definition, preferably but not mandatory, the same used for the Data Definition code.

Application: Enter the Application the same as the Data Definition and Oracle Report Concurrent Program.

Data Definition: Select the Data Definition created in the above step.

Type: Enter the template type, RTF if it is an MS word template, PDF if it is a PDF template.

File: Upload the template file to be used for generating the output.

Language: Enter the language as English

Territory: Enter the territory as United States

Apply the changes to activate the Template definition.

1.         Set Administration Properties

XML Publisher Administrator Administration

Under Properties, Expand the General tab, specify a Temporary Directory Path from your file system. If this is not set, the XDOBURSTREP Program will error

3) Printer setup:-If Pasta printing is not enabled in your organization

System administrator Install Printer- Register

Query the printers- check the Type field for the Printers.

If Printer Type= ‘–PASTA Universal Printer Type’, for all your printers then you are set to use Pasta Printing and no further steps would be necessary.

If your printer type is for eg: HPLJ4SI you may proceed to follow the steps below

Add PDF Publisher style with PASTA_PDF as a driver for printer type

———————————————————————————————————————

Once the above setups are done, Navigate to the responsibility.

Submit the request.

When it completes click on View output to see the pdf generated by XML Publisher.

If using Bursting, the output will get emailed/printed/faxed based on the values provided for the bursting control file at run time.