BI Publisher: Let Go My Logo

There have been some questions of late on how to get a logo (blob) converted to base64 in the e-business suite. The process is quite simple. Below is a sample snippet I took out of the BIP forum that can be used in ANY Data Template.

FUNCTION mob_getbase64String( P_blob BLOB )
RETURN CLOB
IS
l_result CLOB;
BEGIN
DBMS_LOB.createtemporary(lob_loc => l_result, CACHE => FALSE, dur => 0);
Wf_Mail_Util.EncodeBLOB ( P_blob
, l_result
);
RETURN ( l_result );
END mob_getbase64String;

Sample Select:

select file_name, to_char(XXPA_COST_PLUS_BILLING_PKG.mob_getbase64String(blob_content)) blob_content
from xxfnd_logo_images where file_id = :p_mlogo_id

Note: to_char has to be used for version of 5.6.2 of xml publisher, clobs are okay in 5.6.3

The syntax to add an image field to your format template is down below:

<fo:instream-foreign-object content-type="image/jpg">
<xsl:value-of select="BLOB_CONTENT"/>
</fo:instream-foreign-object>

Bursting with Web Services

Background

I come from an Oracle Forms 6i background (hardcore PL/SQL only). I'm now exploring a whole new development world using Oracle Application Express (APEX). In order to make APEX dance and sing, from a reporting perspective, you really need BI Publisher.

My requirement was to get users in my APEX application bulk printing and emailing documents seamlessly. One solution was to generate my documents as PDFs and store them as BLOBs in the database. There are APEX APIs that allow you to do this. This works fine if a user wants to print a document on an ad-hoc basis. They can just open the PDF in their browser and print it in the normal way. The problem is that I needed to both bulk print and bulk email documents. These documents needed different templates for each result of the data set so BI Publisher seemed the best way to go.

Now there are ways and means of doing this using JAVA and the BIP APIs but, I don't know Java. Sadly, I didn't have time to learn it to the requisite level in time for my project deadline so I needed to find a different way to skin this particular cat.

The beauty of IT is that there's always another way...

The first thing I needed to do was to get APEX communicating with my BIP instance. After some trawling through the forums I came across this excellent article by Tyler Muth. This gave me the starter I needed. It shows you how to get APEX communicating with BIP via Web Services.

My call to the BIP Web Service passes over a unique identifier. This is used in a temporary table in the database that I pre-populate from within my APEX application. This then gives my BIP Data Template query, amongst other things, a Key column with one or more unique IDs of the documents I want to print or email.

Within the BIP report definition I also set up several customer-specific report layout templates. Utilising the Bursting facility in BIP I can now split the reports by a Key and also pick up different templates on the fly as appropriate.

I got a printer in the office configured on C.U.P.S. Once this was set up in BIP Admin as a Print Server, it was simple enough to configure the Bursting query to send the output to the printer. Reading through the documentation (always a good idea) it also seems that it's simple enough to configure BIP to burst to file. Something that again, until I found this Web Service method, I thought I couldn't achieve without a good handle on Java.

My last requirement was to get my documents display a logo which was picked up from the database. My logos are stored as BLOBs in the database and I needed a way to get these out into the XML that BIP uses. Again, a trawl of the forums led me to the "Using Dynamic Images in PDF reports" guide on Marc Sewtz's BLOG. My post on the APEX forum highlights a subtle difference in the implementation of Marc's guide depending on how you want to use the image in your reports.

Getting the report to BURST using the Web Service.

I tested the bursting of the report from within BIP by manually Scheduling the report to run and it was working fine. I then set about configuring the SOAP request to call the report and let the bursting/delivery be handled by BIP.

Looking at the SOAP request I saw it had a deliveryRequest element. I figured that, as BIP was handling the delivery, I no longer needed the deliveryRequest in my SOAP call.

WRONG!!

I found that I needed the deliveryRequest element in order for the SOAP call to be syntactically correct. Failure to include one meant that the Web Service would fail with an error stating...

oracle.apps.xdo.webservice.exception.InvalidParametersException: PublicReportService::generateScheduledReport Failure: DeliveryRequest is EMPTY

The solution?

Keep the element in your SOAP call. If you have your report configured for Bursting and have Bursting 'Enabled' then this will override whatever Delivery Request you make in your SOAP call to BIP.

What's next?

I will shortly be testing to see if I can use CASE statements in the Bursting query to handle Email and Printer delivery from one report definition.

I will also be trying to get the report to run without the need for passing a user name and password over to BIP via the web service. My report is residing in the GUEST folder but, at the moment, I seem to need to pass a UN & PW for the web service to work.

When time permits, I intend to revisit the PDF merge facility of the BIP APIs.

Summary

So, there you have it! The Java-Agnostic way of getting BIP to do it's thing using Web Services.

BI Publisher: Template Builder Versions

Version chaos!

The good thing about evolving software is that each version brings new functionality and improvements (bug-fixes). The dark side is that compatibility of versions should be watched closely. This also holds for BI Publisher, the Template builder version should be of the same version as the installation that resides on the server or in E-Business Suite.Therefore an overview of the Template Builder versions that I could get a hand on:

• Oracle XML Publisher version 5.6.3 (for R12) - patch 5887917
• Oracle XML Publisher version 5.6.3 (for 11i) - patch 5887917
• Oracle XML Publisher version 5.6.2 - patch 5027437
• Oracle XML Publisher version 5.6.1 - no new builder use patch 4561835
• Oracle XML Publisher version 5.6.0 - no new builder use patch 4561835
• Oracle XML Publisher version 5.5 - patch 4561835
• Oracle XML Publisher version 5.0 - patch 4239263
• Oracle XML Publisher version 4.5 - no template builder :-(

Whenever you start working with another BI Publisher version, you should ideally de-install the current version and install the most suitable Template Builder version.

BI Publisher: An alternative approach to translations

To XLIFF or not to...

Due to internationalization of businesses and the business processes for which BI Publisher is used, more and more templates need translation. Translation for BI Publisher reports is two-fold; we need to translate the XML data (i.e. Reports or the Data Template) and we need to translate the boilerplates (hard-coded texts) on the template. Step 1 is easy, based on the language you want to create the report in, you can select data in a specific language from the "_tl" tables or "_vl" views. Given the fact that you have installed the language you want to translate to. For the hard-coded texts, prompts and the boilerplates the job is a bit more complex.

How to work with XLIFF
BI Publisher supports translation with the eXtensible Localization Interchange File Format (XLIFF for short). XLIFF is an XML-based format that contains translations. The procedure for using this in E-Business Suite is simple: you let BI Publisher extract the hard-coded texts that are to be translated into an XLIFF file. Then you let the translation agency (or yourself if you like) fill in the translations. This XLIFF file is then used as some kind of search and replace resource; during translation single words and even whole sentences are searched and replaced. The good thing about this concept is that it is fully integrated in the BI Publisher engine and the E-Business Suite setup. This makes it easy to setup. However, maintainance is required each time you change your template; each change in the boilerplates requires you to update the XLIFF file. If you lack to do this, there is a big chance the search and replace won't work anymore. XLIFF translation files are template specific, each word or sentence is translated for one template only. This is unfortunate as a change in grammar rules in a language will require you to adapt all XLIFF files for all templates that translate to that language. Also, the maintainer of the translations needs to be someone with knowledge about XML or XLIFF. As XLIFF is not easlily understood by someone with only a functional view on BI Publisher. More on XLIFF: see XLIFF on wikipedia

The alternative - use E-Business Suite Messages
What alternatives to XLIFF are there? I personally do not like to redo the XLIFF file after each (textual) template change and I want the customer to be able to maintain their own translations. The solution I have implemented at several customers now, leaves the terrain of XLIFF files and uses the E-Business Suite's messages to create and maintain translations. This is how it works:

1. Create messages for all hard-coded texts in each language you want to translate to. Give them a general name like XXCUS_TEMPLATE%
   
2. Add query to the data layer to select the messages for the language the report needs to be generated for (select message_name, message_text from fnd_new_messages where language_code = 'NL' and message_name like 'XXCUS_TEMPLATE%').
   
3. Add form fields. In the layout-template replace each hard-coded text for an XPath statement to pick up a message from the XML (./LIST_G_MESSAGE/G_MESSAGE[MESSAGE_NAME='XXKB_CUSTOMER_NAME'] where XXKB_CUSTOMER_NAME is the message name).
   

The good things about this solution is that the customer can easilly maintain the messages from the E-Business Suite screens. There is no re-generation of XLIFF files when the template layout changes. And for the common prompts, messages can be setup for multiple templates at once; simply use these messages on multiple templates. The drawback of this solution is that all the translation texts will be in the XML data. If you are generating a batch of documents in multiple languages then the messages will be in the batch XML multiple times. I have choosen for this solution, because the alternative slows the performance; when the messages are inserted in the XML just under the root instead of under the reporting element. This will require the BI Publisher engine to switch context from a lower level XML group to the root level mutiple times, and this takes additional time. I believe it is better to have a bigger XML file then a slow performing report.

Conclusion - When to use which?
Probably the best answer here is -- it depends. If you wish to create translation for a steady well-developed template, that is not likely to change and you need only 1 translation it is probably save to choose for the XLIFF solution. On the other hand, if you have several translations for muliple templates and both template and translations will be bound to be changed for several times it is best to go for the message solution as it will save you a lot of maintenance overhead.