BI Publisher Blog

BI Publisher Blog News

2012-01-21T06:37:00.000-08:00

Source Code Link Changes
I wanted to give everyone who views this blog a heads-up that some of the links to my source code and home grown apps will be changing in the next couple of months to a new server. My apple mobile me account is being transitioned and I will no longer have server space so I have to do it :-) In the event a link is dead, do me a solid and send me an e-mail or post a comment that it's dead!

BI Publisher Toolbox Updates
In dec 2010, I stated I would be releasing a standalone version of the toolbox for windows. This will be rolled into the link change and I wanted to apologize for the long delay. Also, going forward if you have any suggestions or improvements for the tool please let me know!

BI Publisher Toolbox 4.5

2010-04-23T20:45:00.000-07:00

BI Publisher Toolbox Development Suite Fixes Part III

Last series of updates!!!

A new version of the BI Publisher Toolbox has been released. The following things are fixed in version 4.5

Bursting Designer randomly crashes when bursting

There are several reasons why the Bursting Designer crashes. It mainly has to do with incorrect or blank data. So, I've coded so more validation to catch these problems before a control file is run. You will still be able to enter invalid file paths (just not in the bursting designer). The following error messages will be displayed if you have incorrectly keyed data in your control file:

Document output type cannot be null. See deliveries: [ something]
Document key cannot be null. See deliveries: [ something]
Document Delivery Id must be assigned!
Template type must be populated!
Template Location file-path is invalid

If the Bursting Designer still crashes please let me know and I'll fix it.

Download
The BI Publisher Toolbox can be downloaded here. I'm still working on the standalone version of the application. Should be available for download in the next couple of days. I'll update the blog when it's out there!

BI Publisher Toolbox 4.2.3

2010-04-22T17:16:00.001-07:00

BI Publisher Toolbox Development Suite Fixes Part II

Once, again....
A new version of the BI Publisher Toolbox has been released. The following things are fixed:

BI Publisher Toolbox fail to email reports when running burst control files
Bursting Designer fails to e-mail reports, there are no errors
Delivery Designer does nothing when the run button is pushed for email

The reason the output files were not being emailed is simple. The javamail-1.4.3.zip is required to make this functionality work. If you don't have these jar files you should see the following error message: Unable to locate provider for protocol: smtp. Below is some sample output from the delivery designer....Also, that's how I was able to fix this issue. The Delivery Designer is a great utility for bursting troubleshooting....

Known Issues
There is still an outstanding issue with the Bursting Designer crashing. This is caused by a space or an invalid location to a format template. Be sure to verify the template location is valid. I should have another patch out this week for this issue.

Download
The BI Publisher Toolbox can be downloaded here. I'm still working on the standalone version of the application. Should be available for download in the next couple of days. I'll update the blog when it's out there!

Sample output from the Delivery Designer

[042210_050359941][][STATEMENT] XDO_CFG Location: null
[042210_050359941][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequest][STATEMENT] submit(DeliveryResponseListener): Called
[042210_050359941][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequest][STATEMENT] submit(DeliveryResponseListener): Starting status checking thread.
[042210_050400004][oracle.apps.xdo.delivery.AbstractDeliveryRequest$RequestChecker][STATEMENT] Constructor called. [Check interval : 10000 ms][Timeout : 86400000 ms][Retry : 0][Retry interval : 60000]
[042210_050400020][oracle.apps.xdo.delivery.AbstractDeliveryRequest$RequestChecker][STATEMENT] run(): Called. Start the request checking thread for request id #1
[042210_050400020][oracle.apps.xdo.delivery.AbstractDeliveryRequest$RequestChecker][STATEMENT] run(): Begin retry loop : 0
[042210_050400020][oracle.apps.xdo.delivery.AbstractDeliveryRequest$RequestChecker][STATEMENT] run(): Calling submit()
[042210_050400020][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequest][STATEMENT] submit(): Called
[042210_050400066][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequest][STATEMENT] setDefaultServerProperties(): No properties for the server '' were found.
[042210_050400066][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequest][STATEMENT] setDefaultServerProperties(): properties defined in this request.
----
[PORT:Integer] [25]
[SMTP_FROM:String] [Ike_Wiggins@starkey.com]
[SMTP_CHARSET:String] [UTF-8]
[SMTP_CHARACTER_ENCODING:String] [UTF-8]
[SMTP_CONTENT_TYPE:String] [application/pdf]
[ASYNC_TIMEOUT:Integer] [86400000]
[SMTP_ENCODING:String] [B]
[HOST:String] [mail.starkey.com ]
[RETRY:Integer] [0]
[ASYNC_CHECK_INTERVAL:Integer] [10000]
[SMTP_TO_RECIPIENTS:String] [Ike_Wiggins@starkey.com]
[TEMP_FILE_SUFFIX:String] [.tmp]
[BUFFERING_MODE:Boolean] [true]
[SMTP_ATTACHMENT_FIRST:Boolean] [false]
[SMTP_SUBJECT:String] [This is a converison test!]
[TEMP_FILE_PREFIX:String] [dlvr]
[RETRY_INTERVAL:Integer] [60000]
----

[042210_050400066][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequest][STATEMENT] submit(): BUFFERING_MODE is ON.
[042210_050400066][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequest][STATEMENT] submit(): TEMP_DIR not found. Turn off the BUFFERING_MODE.
[042210_050400066][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequest][STATEMENT] submit(): Start reading the document file. : C:\SALES.pdf
[042210_050400066][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequest][STATEMENT] submit(): Calling DeliveryRequestHandler.submitRequest()
[042210_050400066][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequestHandler][STATEMENT] submitRequest(): called
[042210_050400113][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequestHandler][STATEMENT] submitRequest(): This request has never been submitted before.
[042210_050400145][oracle.apps.xdo.delivery.smtp.Attachment][STATEMENT] addAttachment(): Adding an attachment ...[filename]=[null], [content-type]=[application/pdf], [index]=[0], [disposition]=[inline]
[042210_050400145][oracle.apps.xdo.delivery.smtp.Attachment][STATEMENT] addAttachment(): Character set for MIME headers : UTF-8
[042210_050400145][oracle.apps.xdo.delivery.smtp.Attachment][STATEMENT] addAttachment(): Character encoding for MIME headers : B
[042210_050400145][oracle.apps.xdo.delivery.smtp.Attachment][STATEMENT] addAttachment(): Exiting addAttachment()
[042210_050400160][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequestHandler][STATEMENT] submitRequest(): Character set for MIME headers : UTF-8
[042210_050400160][oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequestHandler][STATEMENT] submitRequest(): Character encoding for MIME headers : B
[042210_050400176][oracle.apps.xdo.delivery.AbstractDeliveryRequest$RequestChecker][STATEMENT] run(): Calling Thread.sleep() before checking status : 10000
[042210_050410176][oracle.apps.xdo.delivery.AbstractDeliveryRequest$RequestChecker][STATEMENT] run(): Checking request status. (10172 millisec passed since thread started.
[042210_050410176][oracle.apps.xdo.delivery.AbstractDeliveryRequest$RequestChecker][STATEMENT] run(): Got request status. (Status id : 19)
[042210_050410176][oracle.apps.xdo.delivery.AbstractDeliveryRequest$RequestChecker][STATEMENT] run(): Calling callback - DeliveryResponseListener.responseReceived()
[042210_050410176][][STATEMENT] Request done!
[042210_050410176][][STATEMENT] Request status id : 19
[042210_050410176][][STATEMENT] Request status msg: Unable to locate provider for protocol: smtp
[042210_050410176][][STATEMENT] Request body: null

BI Publisher Toolbox 4.2.1

2010-04-20T19:33:00.000-07:00

BI Publisher Toolbox Development Suite

A new version of the BI Publisher Toolbox has been released. The following things are fixed:

Toolbox configuration fails to save or load
Adhoc PL/SQL attempts to execute when the field is blank or null
Parameters dialog doesn't open when you click on the button in cell and error is displayed
Data Template fails to execute and errors out

The BI Publisher Toolbox can be downloaded here. On a side-note, there will be standalone version of the application that will be available for download in the next couple of days. I'll update this post when it's available.

BI Publisher: BI Publisher Toolbox Development Suite

2010-02-22T19:05:00.000-08:00

BI Publisher Toolbox Development Suite

What’s new in the BI Publisher Toolbox
The toolbox is back and packed with more features! Every wanted to convert a report6i report, build a Burst control file or test the Delivery APIs? You now can do that with the Toolbox. Haven’t heard about the toolbox, click here.

Besides, the new tools, the toolbox is also faster, easier to use and has a new look and feel. Below is a list of the all the new features.

New features and updates

Performance
Cancel Long Running Tasks
Parameters
Global Properties
Ad Hoc PL/SQL
File Browser(s)
Connection Screens
XDO Protocol Support
Tools:

*Bursting Designer
*Delivery Designer
*Report

Installation
BI Publisher Toolbox - By clicking on the download link you will be redirected to a different site. The standalone client is no longer supported and the toolbox is now a java webstart application. Webstart applications allow for automatic updates and real-time fixes for developers. Don’t worry though just because it’s a webstart application doesn’t mean the performance will lack luster; the jar files are automatically loaded and saved on your PC. It's recommended to bookmark this page and the download site.

BI Publisher Toolbox 4.2

Performance
All of the performance issues in previous version of the toolbox have now been resolved. As an example, the previous version of the toolbox would perform poorly when clicking the execute button. That issue is now resolved and the performance is significantly better.

Long Running Tasks
Sometimes when you miss a join in a query a data template can run forever. Now, when clicking the execute button there is an option now to kill long running tasks. In the previous version the only option was to kill the program through windows task manager, which is less than ideal.

Parameters
One of the best improvements in the toolbox is the parameters screen. In the past, scrolling was required to enter parameters and it was a bit cumbersome (See Figure 1), now it's very intuitive. With one click of a button a parameters screen will popup (See Figure 2).

Now, if you can't remember what parameters are in your Data Template the toolbox has you covered. Click the look up button, the toolbox will automatically parse the Data Template and provide a list of available parameters to select.

Also, there is no longer a limit on the number of parameters that can be entered!

Figure 1 Old version of the parameters for the toolbox.

Figure 2 New parameters form with Data Template parameter lookup feature

Global Properties
All of the standard and non-standard features of the template manager are now available in the toolbox (See Figure 3). As an example if you wanted to set the system-temp-dir or password protect a pdf you now can!

Figure 3 Global properties tab

Ad Hoc PL/SQL
Developer can stage data, initialize sessions, etc using the “Add Hoc PL/SQL” feature. This feature will execute pl/sql statements before a Data Template executes. The purpose of the ad-hoc pl/sql is to limit hard-coding in your data template. In the E-business suite as example, most views are org secured views. With very little effort a developer can use this new feature to execute apps_initialize before your data template queries are run.

Figure 4 Ad Hoc PL/SQL - Example of apps initialize

File Browser for Data Templates, Format Templates and Burst Control Files
A file browser has now been incorporated into several fields for locating Data Templates, Format Templates and Burst control files. It’s not mandatory to use the new file browser and an absolute path to your files can be manually entered. Also, Data Templates, Format Templates and output files can be in separate directories, which was a restriction in the first release of the toolbox.

XDO Protocol Support
The toolbox also supports the XDO Protocol. To use this new feature a DBC file must be assigned. Go to the Action menu and select “Set DBC File.” The DBC file will be preloaded the next time you open the Toolbox or the Bursting designer.

By setting the DBC file developers can run format templates that have import statements in them and prevents errors from occurring. This is something that the Template Builder in msword cannot currently do. As an example in your format template if your doing and import like this: import:xdo://ONT.ONTBSAERTF.en.00/ that should work in the toolbox.

Not only is the XDO Protocol supported for import in format templates, its also supported in the toolbox itself. There is no longer a requirement to have a format template physically located on a PC. An XDO URL can be entered directly into the format template field. The toolbox will pull the format template from the database using the assigned DBC file. For the URL don’t put getSource=true. ie: xdo://ONT.ONTBSAERTF.en.00/?getSource=true

Connection creation and removal
The previous version of the toolbox was very difficult to update or remove connections and it usually required manually editing of the toolbox config. Going forward, the connection portion of the application has also been completely redone. Creating or removing a connection couldn't be easier (See Figure 5 & 6).

*Connections 10g databases are supported, 9i and earlier versions are not.

Figure 5 New connection setup screen

Figure 6 Edit/Remove connection screen

Tools
One of the new features of the toolbox is external applications integration (See Figure 1). The tools menu has a list of applications that can be run:

1. Bursting
2. Delivery Designer
3. Report Conversion

Before running one of these applications be sure to click the save button or your toolbox changes will be lost.

Figure 7 tools menu

Bursting Designer 3.7 XDO Protocol Supported

About the Bursting Designer

The Bursting Designer allows developers of any skill level to create, run and test Burst control files. The tool provides the following benefits:

Preview a burst control file as it's being built (See Figure 8.)
Test the burst control file (See Figure 9.)
Test XDO Delivery configurations
Onscreen real-time logfile (See Figure 10.)
Review/Analyze your Bursting delivery channels
XDO Protocol Support
XDO Delivery Configurations (xdodelivery.cfg) & XDO_TOP support
Authentication support (email, printer, fax)
Printer Delivery Properties
Data Expressions (implicitly supported)
Template File Browser
File History
Improved UI (Quick Keys & Menus)
Fixed - Crash when opening test file

Figure 8: Preview a control file

Figure 9: Request tree – Hierarchal view of request and their related deliveries. Note: Double click the link to view the file.

Figure 10: Real-time log file

How to build a control file using the Bursting Designer
Below is a list of video tutorials for the Bursting Designer. The tutorials explain how Bursting works and how to create a Burst Control file in the Bursting Designer.

Bursting Designer Lesson 1 - What is Bursting
Bursting Designer Lesson 2 - Creating a Burst Control File

The basic steps to create a control file are:

Step 1: Request setup
Enter an xpath expression for the XML you would like to be burst. Multiple requests can be added and resorted (it’s drag and drop).

Step 2: Create a delivery.

When creating a delivery your using oracle’s document delivery APIs . The following Deliveries are supported:

File
FTP
Webdav
Printer
Email
Fax

General rules:

Multiple deliveries can be created for request
Deliveries do not have to be assigned, but unassigned deliveries will not be used by the bursting engine
Deliveries are not global available across multiple requests, a delivery is request specific.
For file output, make sure the file name is unique. In most instances you would want to use the same value in the KEY field (see document tab).

Step 3: Assign a delivery to a document.
All the fields on this tab are required. One or more deliveries can be assigned to one document row. The Bursting Designer will allow for more than one document to be created, however, Oracles bursting engine does not support this. When choosing a key make sure the value is unique. Real world examples would be invoice number, transaction number, etc.

Step 4: Assign a template.
All the fields on this tab are required except for the filter field. The location to a format template is the absolute path. You may hand-key the path or use the template file browser button. Another option besides local format template is to use the XDO Protocol (see in section below).

One or more templates can be assigned to a document row. However, if there are multiple template rows the filter field is required (you will not be prompted though). Assign multiple templates with caution. If a filter doesn’t get applied, the last template row will be applied to the output. An alternative to filters would be to create multiple requests with predicates for each template. Neither way is a best practice and is purely a personal choice.

Previewing a control file
At any point in the development process a control file can be previewed. To preview the control file go to the Actions menu and choose “View Control File.”

Run a control file
Before running a control file be sure to save it first. To run a control file go to the Actions menu and choose “Run Control File.” You will be prompted to select a data file. Once a data file is selected it will be cached permanently unless you decided otherwise.

XDO Protocol Support
XDO Protocol support allows a control file to be seamless loaded into the Bursting Designer from the E-Business Suite. The syntax to import a format template is as follows: xdo://<appname>.<templatecode>.<language>.<territory>/?getSource=true

Example: xdo://ONT.ONTBSAERTF.en.00/?getSource=true

In order to use this new feature a DBC file must be loaded. To load a DBC file, go to the Actions menu and select “Set DBC File.” The DBC file will be preloaded the next time you open the Bursting designer. When you load a DBC file the designer will test it to see if a connection can be created. If a connection cannot be created then the likely cause is a bad DBC file. The typical reasons for bad DBC file are it’s dated or the JDBC connection needs to be manually edited. To fix the JDBC connection string, remove any slashes (\), save and retry the “Set DBC File” action.

XDO Delivery configuration support
Perhaps you didn't know this but Oracle's Bursting engine can use XDO Delivery configurations (xdodelivery.cfg). Setting the server-alias will utilize the configurations in a xdodelivery.cfg file. The sever-alias field is available on the following deliveries: Email, Print, Fax.

To load a use a XDO Delivery Configuration file, go to the Actions menu and chose set XDO_TOP(see below). XDO_TOP should be the path to the resource directory. The location will be preloaded the next time you open the Bursting designer.

Why use an XDO Delivery configuration file?
Simplified administration is the key benefit of using xdodelivery.cfg. However, there are some other advantages. As an example, what if a printer can only accept postscript output. What’s a programmer to do? BIP & Bursting only support pdf, so I guess were out of options or are we? The filter option in an XDO Delivery configuration would allow a developer to automatically convert a pdf to post script. Something like this should do the trick: filter="pdftops {infile} {outfile}." They’re many more features an XDO Delivery Configuration file can provide. For more information on how to setup one up click here.

Authentication support
The Bursting designer now supports authentication nodes under specific delivery types. To use authentication set the user name and password fields on the following tabs: email, print and fax.

Printer Delivery Properties
Below is a list of the enhanced printer options that are undocumented by Oracle (at least for Bursting). The following attributes are available on the Printer delivery tab:
1. attributes-charset
2. attributes-natural-language
3. document-format
4. job-name
5. media *this is the tray option!
6. orientation-requested
7. requesting-user-name
8. sides

Data Expressions
Data expressions are implicitly supported for e-mail, ftp and webdav. Oracles Bursting Engine uses the Delivery Manager APIs, so by default that functionality works for Bursting. For more information on data expressions see Tim Dexters Blog.

Example:
<xapi:ftp id="ftp" server="localhost" user="hidden" password="hidden" directory="/Users/ike/ " file="ftp_${TRX_NUMBER}_%y.%m.%d.%S" ftp="false">

File History
Another major time saving benefit of the Bursting Designer is file history support.
Every time a control file is opened, file history is created. Under the file menu, the last 5 five control files opened (see Figure 1) will be available.

Also, when a data file (Action Menu --> Run Control File) is selected to run against a control file, the data file will be cached and will pre-populate next time the Bursting Designer is opened.

Figure 11 File History

Crash when opening test file
This was the number one issue that developers had with the Bursting Designer. The reason for the crash was because of an invalid path to a format template. There is a bug in one BIP classes that Oracle is working on. With that said, I'm overriding oracles class with my own in order to fix the issue.

Delivery Designer 1.1

If you ever wanted to test the Document Delivery APIs or a delivery configuration this tool is for you. The Delivery Designer allows a developer to test the all of the Delivery APIs without writing any code.

The Delivery Designer’s use is not strictly related to testing how the Delivery APIs work, it also allows a developer to troubleshoot Bursting delivery problems. As an example, when trying to troubleshoot a Burst control file, it can be difficult to identify a misconfigured printer. In this situation the designer will allow repeated, lightweight troubleshooting of a printer issue without the overhead of running the Bursting Engine.

It’s also never been easier to test an XDO Delivery configuration! The Delivery Designer not only allows XDO Delivery configuration testing, it also can generate a sample XDO delivery configuration.

The Designer supports all of Oracles Documents delivery channels. Below is a list of the Delivery types that are supported:

Email
Printer
Fax
Webdav
Ftp
Sftp
Http
AS2
External Command
File

Setting a Particular Delivery Property
There is an abundance of properties that can be set for a particular delivery channel. The Delivery Designer needs the literal value to be entered for delivery channels. It’s because of this fact the every property in the Delivery Designer has note in the info column. The purpose of the note is not only for informational purposes but what values can be entered. As an example, the IPP_AUTHTYPE (Figure 12) shows the valid values that can be entered are none, basic or digest.

Figure 12 IPP_AUTHTYPE printer delivery attribute and related info

Setting up XDO Delivery Configurations
There is only step to utilizing an XDO Delivery configuration. Navigate to the global property tab, select property called XDO_TOP, add the path to XDO_TOP. Be sure to create a directory called resource and place your xdodelivery.cfg document in that directory.

To use a server defined in an XDO Delivery configuration enter the server name in the property called “SERVER_NAME”. The SERVER_NAME property is available for most delivery types.

XDO Delivery Configuration file generation
An XDO Delivery Configuration can be generated at any point in time by clicking the delivery config button. The Delivery Designer will take the current properties for a delivery and generate the XDO Delivery Configuration. The deliveries that can automatically be generated are IPP, SMTP, Webdav, FTP, SFTP & External Command.

Figure 13 Sample XDO Delivery Configuration file generation

Real-time log file
When a Delivery Request is submitted, instant feedback is provided about the delivery request on the log screen. The log screen is continually updated till the Oracle DeliveryResponseListener returns the final status of the delivery.

The Delivery Designer also supports undocumented properties that manipulate Oracles Delivery Reponse Listener. Changing these properties will determine how and when a delivery times out. Below is a list of properties that are available for every delivery.

ASYNC_TIMEOUT - Sets the timeout period for the delivery
ASYNC_CHECK_INTERVAL- Set this property to change the minimum wait time before refreshing the log screen
RETRY - Set this property to change the number of times to re-attempt sending this delivery
RETRY_INTERVAL- Set this property to change the minimum wait time before re-attempting to send this delivery

Figure 14 Real-time log file

Report Conversion

It's never been easier to convert a reports6i report to BI Publisher using the toolbox. Reports can be converted in mere seconds. To get an idea of how easy it is click here to watch the demonstration video.
Prerequisites

Forms and reports 10g locally must be installed. The report6i files must be converted to an xml format. To convert reports6i report, use the rwconverter.exe. A dos batch or shell script can be written to accomplish a batch conversion task (See Figure 15)

REM WINDOWS CONVERTING REPORT
::report_batch_converter.bat
cls
Echo compiling Report .....
for %%f IN (%1*.rdf) do rwconverter.exe batch=yes source=%%f dest=%%f.xml dtype=xmlfile overwrite=yes logfile=%1reports.log
Echo FINISHED CONVERTING REPORT

Figure 15 Sample batch script for converting a reports6i report to an xml format

Conversion Steps
Step 1: First part of conversion is converting your reports6i report to xml. To do this use rwconverter.exe or create a batch script (see above, Figure 1)

Step 2: The next step is to take the xml files and convert them to bi publisher files. In order to do this we need to run the ReportConversion.exe.

Step 3: Click on the folder icon to select reports6i the xml files. You may optional change the format template and the log file name after the files are loaded.

Step 4: The final step is to click the process button.

Navigate to your conversion directory. The following should be present:
1. Format Template
2. Data Template
3. Related PL/SQL Packages

Other Known Issues

Issue	Resolution/Workaround
Application starts to load but fails	Java 6 is required to run this application.
JNLP file does not execute in browser	Follow these instructions
Application runs in Java 1.5 but none of the cell browsers work.	Java 6 is required to run this application.
: XSL-1031: (Error) Variable not defined: '_XDONFSEPARATORS'.	Import statements in format template will on only work for R12 or 6241631 patchsets
Can’t find DBC File	DBC files can be found under $FND_TOP/secure.
Sftp & AS2 – Fail for Delivery Designer	There is no workaround for this. The delivery designer is coded to execute these classes when there available from oracle
Cannot connect to database for Data Templates	Verify your connection is setup correctly. Tnsping
There is no option to specify a custom xdodelivery.cfg file for bursting.	Future release will allow ad/hoc xdodelivery.cfg files outside of $XDO_TOP
There is no standalone version of the application (executable)	The BI Publisher Toolbox is a webstart application. This allows for developers to run the most current version of the application automatically.
XDO Loader Features are missing	XDO Loader features are being redeveloped.
After keying data in a field, it disappears when I click save or a menu item.	Be sure to always tab out of each field before performing a save action.
The report6i xml file is malformed or does not convert at all.	Identify that 10g version rwconverter.exe is being used to convert the rdf to xml. It’s not uncommon that 6i version of rwconverter.exe gets used instead. Goto My Computer System Properties Environment Variables. Modify the path variable to point at the 10g directory or Manually run the rwconverter.exe

In this post you learned about the new features and updates to the BI Publisher ToolBox Development Suite. The ability to run BI Publisher reporting components has never been simpler. With ToolBox, the majority of your development can now be done locally on your PC.

Bursting Designer 3.7 XDO Protocol Support!

2010-01-15T16:13:00.000-08:00

Thats right folks, there is a new version of the Bursting Designer out. One of the best new features of the Bursting Designer 3.7 is XDO Protocol support*. If you used the previous versions and were unsatisfied, please give the app a second try!

*In order to use the XDO Protocol a DBC file is required. The DBC for the EBS application suite can be found under $FND_TOP/secure.

Also, the previous versions of the application could crash because of invalid path to a format template. That issue has now been resolved and the app is very stable (and faster). If you do have issues, please let me know. I can't fix what I don't know about.

Bursting Designer Download Site
*Desupported 04.22.2010 - please use the BI Publisher Toolbox.
Bursting Designer - By clicking on the download link you will be redirected to a different site. The bursting designer is java webstart application. Java 6 is required to run this application. It's recommended to bookmark this particular blog and the download site. This is because you will need to use these links every time you wish to run the Bursting Designer or find news about updates. It's technically not a requirement to use the webstart application for the java savvy developers but in doing so you will miss out on future updates and releases. It's important to note the Bursting Designer jar files will be loaded on your machine. This allows for a significant increase in performance. If you need to remove this application for some reason, go to add/remove applications.

BI Publisher: Bursting Designer Version 1.2

2009-12-22T07:43:00.001-08:00

Whats new in the Bursting Designer 1.2?

Version 1.2 is here and it includes the following enhancements:

Excel Output
Templates tab fix. If you enter more than one template row it may have potentially caused the application to crash. Also, the templates could potentially be assigned to all the deliveries.

Whats next?

The Bursting Designer 1.3 will include enhanced printer options that are undocumented by Oracle. The following attributes will be added to the Printer delivery tab:

attributes-charset
attributes-natural-language
document-format
job-name
media
orientation-requested
requesting-user-name
sides

The Delivery Designer is also another skunk works project that I'm working on. The tentative release will be sometime in December or late January. This tool will allow a developer to test the delivery APIs without writing a lick of code. There is no need to speculate, whether or not a delivery API is capable of a particular integration point.

Ever been asked, "Can we send a document to an EDI system using as2 Document Delivery APIs?" I know I have and I really didn't have a confident answer of yes or no and I had no way of finding out without writing some code. So, the Delivery Designer will let you kick the tires so speak before making a coding commitment. You'll be able to tell your manager confidently, yes this will work!

More details to follow....

POST A QUESTION
If you have questions about the Bursting Designer or if you find a bug please post a question on this blog. Please no cross posting and make sure the question matches the topic.

BI Publisher: Troubleshooting Bursting

2009-12-19T06:46:00.000-08:00

How to Troubleshoot the Bursting Designer & Bursting

Since the bursting designer has come out I been getting quite a few questions lately. This post tackles some common issues that most developers run into when using the Bursting Designer or using Oracles BI Publisher Bursting Engine. If you haven't used the Bursting Designer yet, go on ahead and start kickin' the tires!

Issue: Bursting Designer Web application crashes when the application is downloading. Something like this might occur, " Java 6 splash screen shows, then "Warning - Security" window blips by, then nothing."
Resolution: There can be a wide variety of reasons why this might occur. The first thing to do is make sure your an administrator or windows power user. Because your installing software you need to make sure you have the appropriate rights.

The second thing you should do is check that java 1.6 is installed.

To find out what version your running go to
start->run
type: cmd, hit return
type: java -version

If java 1.6 is installed that can be eliminated. The next step would be to verify the amount of memory you have on your machine.

By default the Bursting Designer does not support dated hardware. The minimum amount of memory required is 512 megs. If you do not have 512 megs installed on your machine the Bursting Designer will not open. In order to fix this, modify the JNLP file and replace the memory options with this line:

<j2se args="-Xms128m -Xmx256m -Xdebug" version="1.5+"/>

Then manually open the JNLP file through IE or Firefox locally on your PC. Do not try and use the download link because it will use the standard memory options. I don't advice against modifying the memory settings, but I would recommend that you talk to your hardware guy in your organization and either request a new pc or ram upgrade. It's the 21st century and you should have a computer that has 21st century power. What are we in russia? I kid for all the Russians who read my blog :-)

Now, if the Bursting Designer is still crashing. Turn on the java debugger. Go to the control panel in windows and you should see an icon for java. Double click on the app. Select the show console option in Java Control Panel in the Advanced tab. This will let you see an errors that are occurring in the application or general debug info.

See the link below and go to the debug section for the java console:

http://java.sun.com/j2se/1.5.0/docs/guide/deployment/deployment-guide/jcp.html
http://java.sun.com/j2se/1.5.0/docs/guide/deployment/deployment-guide/console.html

If you cannot figure-out what the errors means in the debug console, post a comment here with the error message or find my email address and I'll do my best to help resolve the issue.

Issue: JNLP downloads but doesn't open the Bursting Designer
Resolution: The JNLP file is not associated correctly in windows or it's pointed at the wrong java runtime environment.

I ran into this issue myself when I first deployed and tested the Bursting Designer in windows. That's why I set up the JNLP associations on the download page. I have instructions for most browsers and I have tested the Bursting Designer on Firefox, IE & Safari. http://web.me.com/beth.wiggins/ike/bipublisher/BurstDesignerDist/launch.html

For IE and windows users follow these steps:

To set up the association between *.jnlp files and javaws.exe in Windows which also handles IE:
1. In Windows, not IE, click Start | Settings | Control Panel | Folder Options | File Types.
2. Check to see if there is already an association for *.jnlp files.
3. If there is no entry for *.jnlp files already then click new | advanced | select "jnlp file"
4. If there is an entry, select it and click advanced.
5. Then edit the open application used to perform action field and make sure it is set to "C:\Program Files\java\jre...\bin\javaws.exe ""%1". Uncheck DDE.
6. Click OK.
7. Click Set As Default to make Open the default action. Likely Open will be the only action.
8. Click OK | Close.

Issue: Bursting Designer crashes and the web app log file says it's pointing to this directory: file:/Users/ike/Code/NetBeansProjects/Bursting%20Designer/dist/
Resolution: Contact me, I need to fix the JNLP file. If your in a bind you can do it yourself by changing the code base node to:

codebase="http://web.me.com/beth.wiggins/ike/bipublisher/BurstDesignerDist/"

Issue: Bursting Designer crashes when I click the run button.
Resolution: Follow the steps above to turn on java debug console. If you can not identify the issue or a workaround post the error on my blog or send me an e-mail. There is a known issue on the templates tab. If you enter more than one template row it may potentially cause the application to crash. This will be fixed in Bursting Designer Version 1.2.

Issue: Templates are shown across document deliveries.
Resolution: This will be fixed in Bursting Designer Version 1.2. I had a bug in my code that allowed a user to circumvent document to template associations.

Question: Printer node not found when when bursting. Example: ipp://localhost:631/printers/${CF_PRINTER_NAME}.
Answer:
1. XML is case sensitive, is the printer xml node upper case?
2. Is the printer node a child of the of the parent node selected in the xpath expression?
3. Are you using a document key? It's important, you need this to burst the file correctly. This is new for EBS users, it was not a feature in the original 5.6.3 version of BI Publisher. If you don't have a document key, it will not work.
4. Finally, it could be a pruning issue. See: http://bipublisher.blogspot.com/2007/10/bi-publisher-bursting-how-to-resolve.html

Question: How do I identify a printing issue.
Answer: Use the Bursting Designer and review the log file. In the example below we can see that the issues is related to the printer name.

IPP version: 10
operation id: 02
charset: utf-8
request id: 1
-- operation attrs --
[1]attributes-charset:utf-8
[1]attributes-natural-language:en
[1]printer-uri:ipp://localhost:631/printers/null
[1]document-format:application/pdf
-- printer attrs --
-- job attrs --
[1]copies:1
After adding the document key:

IPP version: 10
operation id: 02
charset: utf-8
request id: 1
-- operation attrs --
[1]attributes-charset:utf-8
[1]attributes-natural-language:en
[1]printer-uri:ipp://localhost:631/printers/HP_TEST_PRINTER
[1]document-format:application/octet-stream
-- printer attrs --
-- job attrs --
[1]copies:1

Question: Printer not found when when bursting.
Answer: You will see a message in the logfile that looks something like this:

IPP version: 10
operation id: 46
charset: utf-8
request id: 20
-- operation attrs --
[1]attributes-charset:utf-8
[1]attributes-natural-language:en
[1]status-message:The printer or class was not found.
-- printer attrs --
-- job attrs --

During upgrades sometimes printers that are setup can get wiped out. I know this true on a Mac. So the first step would be to verify the printer actually exists in the setup. If it doesn't add it. Now it's important to note, if you can't bring up the printer in your browser: http://localhost:631/printers then you may have a CUPS issue depending on how your system is setup.

Question: FTP files fail
Answer: Before starting a ftp control file, I would suggest you initial create file delivery instead. This will allow you to work out the kinks and make sure your control file is first class as well as your format template. Once you know there are no issues, then I would try setting up the FTP delivery. To troubleshoot FTP issues do the following:

1. Check the permissions. Suggest that you open a session in telnet and attempt to manually FTP. If you can't do it in telnet, you can't do it with BIP!
2. Verify the user name and password is correct. Remember it's case sensitive.
3. Verify the files are actually being generated. If there not, than obviously there is nothing to FTP. Also, verify the files aren't being generated with just an extension, in which case there is an issue with the document key.
4. Related to number 3, Verify the document key exists and is correct.

Question: Delivery Fails to show in the logfile or the delivery does not get processed.
Answer: BI Publisher Bursting Engine has some undocumented issues for multiple deliveries, below is a breakdown

1. Multiple deliveries for the same request are supported. If you assign the deliveries to one document you should be okay. See example 1.
2. Multiple distinct deliveries, that are individually assigned to a document will only process the last delivery (LIFO) in the document list. This is a very common mistake new developers make regardless if there using the Bursting Designer or not. Should you design your control file this way? No.

Example 1 is okay, Example 2 is wrong and it needs to be put in another request. The Bursting Designer application will allow you to setup a control file like example 2. Could this be a feature someday? Who knows.....Basically, if oracle allows you to do something and doesn't throw an error the Bursting Designer does the same.

Example 1: One Request - Multiple deliveries assigned to a document.
document key="${TRX_NUMBER}" output-type="pdf" delivery="ftp2, print"
Example 2: One Request - Multiple distinct deliveries assigned to a document.
document key="${TRX_NUMBER}" output-type="pdf" delivery="ftp2"
...some template assigned...
document key="${TRX_NUMBER}" output-type="pdf" delivery="print"
...some template assigned...

Also, putting a filter on each template will not resolve the issue with example 2. You will still have the same issue. Word of advice, when in doubt just create two requests and you use the template filters.

Question: Can there be multiple templates assigned for a delivery?
Answer: Yes. If a filter does not exist, all templates will be applied to the output. When all templates are applied to the output file it will occur in LIFO order.

Example:

xapi:document key="${TRX_NUMBER}" output-type="pdf" delivery="file"
xapi:template type="rtf"location="/Users/temp.rtf" filter=""
xapi:template type="rtf"location="/Users/temp2.rtf" filter=""
xapi:document

Template temp2.rtf will be applied first, and then temp.rtf. Also, there will only be one output file. I do not know if this desired functionality by Oracle or not, so you can use this at your own risk.

Question: Are document keys required for all document elements?
Answer: Absolutely, if a key node is left blank or doesn't exist your requested delivery may process but no results will be generated.

Question: Is there an alternative to creating template filters
Answer: Predicates are allowed for request xpath expressions. Now, don't think that because they are you can start getting fancy. There are very limited.

Is it better or worse than a template filter? No, the two are the same. Personally, I think it's easier to understand by having it in the request itself.

Question: Is CSV supported for bursting?
Answer: Yes, you will need to use etext templates.

Question: Is xdo:// uri syntax supported for Bursting?
Answer: Yes.

Question: Is xdo:// uri syntax supported for Bursting Designer?
Answer: No.
Jan-15-2010: XDO Protocol is supported in the Bursting Designer

Question: I want to use bursting as a way to apply one or multiple format templates indiscriminately to the output file and I only want a single file(s) to be generated, I actually don't want it bursted.
Answer: Seems like an oxymoron, but it's really not. You may very well run into this situation, I know I have. In your request, just use slash "/" and for the key field generate a something in the data template that is below the root node and use that. As an example, lets say user requested that they need have 3 separate csv files. You could create 3 etext and burst the xml file. Make sense?

Question: I want to use excel as an output format in the Bursting Designer
Answer: That will be available in version 1.1 of the application.

Question: Are Data Expressions supported in Bursting?
Answer: Absolutely. It's supported for e-mail, ftp and webdav. Bursting uses the delivery manager APIs so by default that functionality works for bursting. For more info data expressions see Tim D. blog

http://blogs.oracle.com/xmlpublisher/2009/12/dynamic_delivery_file_naming.html

Example:

xapi:ftp id="ftp" server="localhost" user="hidden"
password="hidden"
remote-directory="/Users/ike/Desktop/Mallika/"
remote-file="ftp_${TRX_NUMBER}_%y.%m.%d.%S" secure-ftp="false"
xapi:ftp

[ASYNC_TIMEOUT:Integer] [86400000]
[HOST:String] [localhost]
[RETRY:Integer] [0]
[ASYNC_CHECK_INTERVAL:Integer] [60000]
[TEMP_FILE_SUFFIX:String] [.tmp]
[USERNAME:String] [hidden]
[PASSWORD:String] [*******]
[TEMP_FILE_PREFIX:String] [dlvr]
[FTP_REMOTE_DIRECTORY:String] [/Users/ike/Desktop/Mallika/]
[FTP_REMOTE_FILENAME:String] [ftp_1537394_%y.%m.%d.%S]
[RETRY_INTERVAL:Integer] [60000]

[121909_020451288][oracle.apps.xdo.delivery.ftp.FTPDeliveryRequestHandler][STATEMENT] submitRequest(): Opening connection : [localhost][21][hidden][****][/Users/ike/Desktop/Mallika/][ftp_1537384_2009.12.19.51][true]

Question: Is xdodelivery.cfg supported for Bursting?
Answer: Although, I have never tried it, I suspect it is. If you do try it let me know.
Jan-15-2010: xdodelivery.cfg is supported

Question: Is xdodelivery.cfg supported for the Bursting Designer?
Answer: No. If I ever decide to support it, I will create a property on the properties tab.
Jan-15-2010: xdodelivery.cfg is supported in the Bursting Designer

Question: Does Bursting support custom delivery channels?
Answer: No. Would it be possible for oracle to do this? Absolutely, but not many organizations need this functionality.

POST A QUESTION
If you have questions about the Bursting Designer or if you find a bug please post a question on this blog. Please no cross posting and make sure the question matches the topic.

BI Publisher: Bursting Designer Version 1.0

2009-12-15T21:01:00.000-08:00

Whats new in the Bursting Designer?

The Bursting Designer has moved out of the beta phase! Version 1.0 is here and includes the following enhancements:

FTP and Secure FTP (depends on the patch level)
Webdav
Key support
Latest Version of BIP Libraries
Memory Adjustment

So if you have written a Bursting Listener to FTP files you no longer have to do that anymore. You now can FTP reports in seconds with Oracle's Bursting Engine and the Bursting Designer! A lot of developers are not aware of the fact that BI Publisher Bursting supports FTP. I can't confirm it but I think FTP is an undocumented feature for burst control files. Will have to ask Leslie Studdard from oracle if that's the case or not, she's got people and the answers.

What about Webdav? Thats another option in the Bursting Designer. Webadv is really not a utilized feature but a powerful one. A developer could use a sharepoint portal to create a report repository or pseudo reporting dashboard. I've never had the request for it but when I do I know the Bursting Designer should do the trick!

Key support is available, finally! The beta version was working off of the 5.6.2 version. Version 1.0 works off of BIP 5.6.3 (patch 6241631) . Developers don't have to rollback to old version when creating control files. Also, developers are working with the latest APIs for BI Publisher (11.5.10.2). Should be close enough for R12 developers too!

Run time memory options have also changed for the Bursting Designer. Most developers didn't experience issues with the with web start application but some did :-( The developers that had problems had out-dated hardware. If you don't have less than 256 megs of ram that is so 1999 and you need to get with the program (literally) and get a modern machine. I've changed the app to use -Xms256m -Xmx512m instead of -Xms512m -Xmx1024m. Funny story, back in 1999 I begged my father to give me some coin so I could pick up some ram to make my machine have 128 megs of ram. Back then, 128 megs was the shizzle and I was ridding high on the hog.

Future Functionality
There are some features that are still being developed below is a list:

XDO uri support
Request Removal *Manually edit the burst control file to remove a request
File Browsing for File Delivery and Template Fields

If you would like to request an enhancement please feel free to post a question on this blog. Also, If you would like to participate in the development of the Bursting Designer contact me via e-mail. To date I have had no developers volunteer. If you do help out, you'll be elite like a level 3 black sage*

*I went to go get a hair-cut at a salon once (it was proximity thing). The stylist told me that they had there "level 2" people on and it would cost 40.00. I'm a dude, dudes don't need a $40.00 hair cut. Anyways, I responded with "Can you ask your level 2 people to see if they can cut me a deal. I'm a level 3 black sage and I can guarantee safe passage through the mushroom forest." Needless to say the stylist did not responsed to my request. It's easy to understand why, if I were in her shoes looking a level 3 black sage I would be spooked or maybe I would just blew a funny fuse.

Bursting Designer Download Site
*Desupported 04.22.2010 - please use the BI Publisher Toolbox.
Bursting Designer - By clicking on the download link you will be redirected to a different site. The bursting designer is java webstart application. Java 6 is required to run this application. It's recommended to bookmark this particular blog and the download site. This is because you will need to use these links every time you wish to run the Bursting Designer or find news about updates. It's technically not a requirement to use the webstart application for the java savvy developers but in doing so you will miss out on future updates and releases. It's important to note the Bursting Designer jar files will be loaded on your machine. This allows for a significant increase in performance. If you need to remove this application for some reason, go to add/remove applications.

IMPORTANT: If you have questions about the Bursting Designer or if you find a bug please post a question on this blog. If you do not post your comment on this post, I will reject your comment. I've had some complaints in recent months that it's hard to find answers on my blog because of cross post Q&A. If you e-mail me a question about the Bursting Designer be forewarned, I may ask you to post your question on this post.

Also, The source code is available for the Bursting Designer upon request. Note: You cannot sell, copyright, distribute or reproduce the software without the express written consent of Ike Wiggins.

BI Publisher ToolBox

2009-10-30T09:20:00.000-07:00

Ever wished that using the Oracle BI Publisher APIs could be easier to use? Ever wanted a single-click template load application for EBS Template Builder? Ever wanted to be able to test your reports with a single click? Look no further! These features and many more are provided in the BI Publisher ToolBox, a free configuration management tool I created for Oracle BI Publisher .

The BI Publisher ToolBox is the predecessor to the BIPublisherIDE. It's another application, I've written to help supplement BI Publisher Development. To learn more about how this free utility can make running Oracle BI Publisher reporting components incredibly easy click the link below. OTN Tech Article: Getting Started with BI Publisher ToolBox

Like the other applications I've written, the Toolbox is a 100% java. If you don't have JRE 1.6 installed you need to do so (click here).

Oracle Open World

2009-10-13T08:00:00.000-07:00

Open World is a blast!
It has been great putting names with faces! Met up with fellow bippers yesterday: Cory Keogh (from Gallup and fellow presenter), Pradeep Sharma, Noelle Bartlam, Klaus Fabian, Leslie, Osama Elkady and Mike Donahue. Fun crowd!

A little known fact about the infamous Klaus. Ever heard of a pink elephant gift? Klaus has taken this to a whole new level with the pink iPhone. Rumor has it Klaus got a very good deal on this phone, from a disgruntled husband who got his wife’s pink phone. Classic example of checking your pride at the door or a possible lack of shame; regardless Klaus mantra, it’s all about the technology, pink it’s the color of passion! Correction: It's a pink razor.

Also, met up briefly with Gareth Roberts. He's working the press scene here at Open World and answering BIP questions. If you get a chance, say hi but I warn you everything is on the record :-)

Wondering what’s new with Oracle Business Intelligence Publisher for Oracle Applications? If you get a chance check out Noelle Bartlam session, she’ll give you the low down on how to use BIP across platforms. Check it out! The reporting platform for Applications: Oracle Business Intelligence Publisher (S309970). The session covers implementations of Oracle Business Intelligence Publisher in Oracle E-Business Suite; Oracle’s Siebel Customer Relationship Management, PeopleSoft, and JD Edwards products; and other Oracle Applications.

What’s hot? How about the HTTP Analyzer! Don’t know about this hidden gem in JDeveloper11g? It’s a great tool that allows developers to easily create and test existing web services. If you ever wanted to test out the web service or build one from scratch this is the ticket! Went to the "Hands-on Lab: Web Services Development and Consumption with Oracle JDeveloper" and did three different exercises in 45 min. I walked away with a really good understanding of the tool. If you’re at open world, I would highly recommend this session or similar sessions. If you not at open world or would like to take a quick tutorial click here

BI Publisher: Bursting Designer

2009-09-20T07:00:00.000-07:00

What is the Bursting Designer?

The bursting designer is a new application that allows developers to build, run and test burst control files (see Figure 1). Developers of all skill levels can now implement bursting easily!

Why use the Bursting Designer?

The Bursting Designer will enhance your productivity significantly and make your bursting development easier. Below is a high-level list of benefits the tool provides:

Minimal knowledge is needed to build a configuration
Build a burst control file in minutes
Preview a burst control file as it's being built (See Figure 2)
Test the burst control file (See Figure 3)
Review/Analyze your delivery channels
View errors or troubleshoot misconfiguration
It's free!

Getting Started

Below is a list of video tutorials. It's recommend that developers of all skill levels review each video before trying to create a control file. This is especially true for rookie's. The video's are fast paced so you may need to pause or replay them to get the full value. This is especially true for the Bursting Designer Features.

Video Tutorials - Quicktime Required
Bursting Designer Features
Bursting Designer Lesson 1 - What is Bursting
Bursting Designer Lesson 2 - Creating a Burst Control File

Bursting Designer Download Site
Bursting Designer - By clicking on the download link you will be redirected to a different site. The bursting designer is java webstart application. Java 6 is required to run this application. It's recommended to bookmark this particular blog and the download site. This is because you will need to use these links every time you wish to run the Bursting Designer or find news about updates. It's technically not a requirement to use the webstart application for the java savvy developers but in doing so you will miss out on future updates and releases. It's important to note the Bursting Designer jar files will be loaded on your machine. This allows for a significant increase in performance. If you need to remove this application for some reason, go to add/remove applications.

This is a beta release of the Bursting Designer. There are some features that are still being developed below is a list:

XDO uri support
Request Removal *Manually edit the burst control file to remove a request
File Browsing for File Delivery and Template Fields

Request Removal is now fixed. Before removing a request make sure it's the selected one, the selected one gets removed.

Figure 1:

Figure 2:

Figure 3:

IMPORTANT: If you have questions about the Bursting Designer or if you find a bug please post a question on this blog. If you do not post your comment on this post, I will reject your comment. I've had some complaints in recent months that it's hard to find answers on my blog because of cross post Q&A. If you e-mail me a question about the Bursting Designer be forewarned, I may ask you to post your question on this post.

Also, The source code is available for the Bursting Designer upon request. Note: You cannot sell, copyright, distribute or reproduce the software without the express written consent of Ike Wiggins.

BI Publisher: Advanced Line Wrapping

2009-08-09T16:52:00.000-07:00

Over the years, programmatically controlling line wrapping has been a very popular topic! There have been many requests in the BI Publisher forum, this blog, or via e-mail for a solution. Unfortunately, I wasn't able to provide a relatively simple solution until now.

If your new to this issue let me run down some technical scenarios on why line wrapping causes so many headaches!

Examples
My company's bill of ladding only has seven lines for comments. The comment field is stored as one text field in the database. The text field in the database allows for 70 character but the cell on the BOL can only have 50 characters. I need to only print what will fit on 7 lines maximum.

or

I expanded my cell to support 35 characters of text without wrapping but in some rare instances it wraps with 35 character because of the font I'm using.

or

My Swiss Declarations report can only print two lines on page 1 and there are several lines thereafter on the next page I can use. The text when added needs to create a new row (Cell) in the table and simply not add a carriage return in the one cell.

It's difficult to solve any of these examples in a format template. This is because BI Publisher does not have a string tokenizer api or something like a character array. However we do have ways of creating similar frameworks.

In order to solve the line wrapping problem were going to need to be able to do the following:

1. Have a controlled for-loop
2. Know how many times to loop to break-up a string (iterations) into a specified length
3. Storing the string that's going to get broken up

Controlled for-loop

The for loop down below cannot be directly ported to xsl syntax or BI Publisher.

for-loop java

for(int i=1; i<11; i++){
System.out.println("Count is: " + i);
}

We do have some workarounds though for controlled looping. There are some quietly documented features in the development guide and some other examples that can be found on Tim Dexter blog.

foreach_number can be used to control how many times a for-each statement can loop. It's may seem counter intuitive but for-loops in BI Publisher do not require an xml nodes to loop. The following statement is valid in BI Publisher:

for-each:xdoxslt:foreach_number($_XDOCTX,1,3,1)
Note: This will loop 3 times

Iterations

The math for figuring-out how many iterations are needed to break-up a string is pretty easy. Take the string below, if I have a cell length of 20, I can deduce the number of likely characters to fit in that cell by taking the length of the string, divided by the length of the cell. In the text below the length is 107. When we take 107 (text) divided by 20 (cell) equals 5 rounded iterations.

"Ike likes to walk the walk and talk the talk all of the time. End result is very long sentence of text."

Storage

Although it's not commonly used in the BIP world, xsl variables can be created in BI Publisher. XSL variables can be used globally in a template and or they can be used locally in an xsl template. Variables can also be re-assigned. If you were assuming that assignment can happen multiple times, all of the time, your mistaken. Here's the syntax break-down for creating an xsl variable:

Example 1: Create a variable with one value that's reset in a loop
creating a variable: variable@incontext:first_line_text; substring(//LONG_STRING,1,20)
getting the value: $first_line_text (should be "Ike likes to walk")

Misc Example 2: Create a variable with one value that is the length of a field.
creating a variable: variable:field_length; round(string-length(string(//LONG_STRING)))
getting the value: $field_length (should be 170)

Misc Example 3: Create two variables and summing both values
creating a variable: variable: bill_only; //ORDER_LINES[QTY > 10 and LINE_TYPE ='Bill Only' ]/QTY)
creating a variable: variable: ship_only; //ORDER_LINES[QTY > 10 and LINE_TYPE ='Ship Only' ]/QTY)
getting the value: sum($bill_only | $bill_only)

Solution

We now have the basic elements to create a parser utility. The xml text that were going to breaking up is below.
XML Doc


<WRAPPING>
<LONG_STRING>Ike likes to walk the walk and talk the talk all of the time.
End result is very long sentence of text.</LONG_STRING>
<LONG_STRING>This is some other text that will have to get parsed.<LONG_STRING>
<WRAPPING>

Here's the template that will break it up. Below is the code from the format template. This template will be breaking up the text in lengths of 20.

BIP Code:


<?param@begin:cell_length;20?>

<?for-each:WRAPPING/LONG_STRING?>
 <?call-template: string_variables?>
 <?variable@incontext:iterations; ceiling(string-length(string(.)) div $cell_length)?>
 <?$iterations?>
 <?for-each:xdoxslt:foreach_number($_XDOCTX,1,$iterations,1)?>
   <?call-inline-template:string_parser?>
 <?end for-each?>
<?end for-each?>


<?template:string_variables?>
<?xdoxslt:set_variable($_XDOCTX, 'text_string',.)?>
<?end template?>

<?template:string_parser?>
<?variable@incontext:start; ((position()-1)*20)+1?>
<?variable@incontext:first_line_text; substring(xdoxslt:get_variable($_XDOCTX,  'text_string'),1, $cell_length)?>
<?variable@incontext:line_text; substring(xdoxslt:get_variable($_XDOCTX, 'text_string'),$start, $cell_length)?>
<?variable@incontext:last_line_text; substring(xdoxslt:get_variable($_XDOCTX,  'text_string'), $start, $cell_length)?>
<?choose:?>
<? When: position() = 1?>
<?$first_line_text?>(FIRST LINE)
<?end when?>
<? When: position() = last()?>
<?$last_line_text?>(LAST  LINE)
<?end when?>
<?otherwise:?>
<?$line_text?>(REGULAR  LINE)
<?end otherwise?>
<?end choose:?>
<?end template?>

This is the best solution that I could come up. It doesn't account for the fact that specific characters may be bigger or smaller for a particular font (basically characters are not uniform). When implementing this solution be very conservative about how many characters can fit in a cell. I would suggest using the largest character in caps to figure-out the maximum length.

I'll throw this out there too, if anyone has a better or different way of doing it, please send me an e-mail with your solution on I'll post it on my blog with your name.

Also, it's important to note that a data template with a pipelined function can be used to solve line wrapping issues.....

Is there topic you would like me to cover? Post a comment and I'll see what I can do!

BI Publisher: Font Setup & Troubleshooting

2009-07-29T08:50:00.000-07:00

Recently, Tim Dexter at oracle has been writing up a storm about barcode fonts implementations. This is an overview on how to do a relatively simple installation of fonts for BI Publisher on a PC. There are really only three steps to setting up a font for BI Publisher. The first step is to install it in windows, the second step is configuring the font for BI Publisher (java) and the third step is troubleshooting the setup font setup.

The following posts from Tim Dexter are useful as well for font setup or config:
How to install a font using XML Publisher Administrator?
I want a default font!
PS Font Support?
Barcoding 101

Installing the new fonts in word
1. Close any open Windows applications, such as Microsoft Word or Microsoft Outlook®.
2. In Control Panel, click Appearance and Themes.
Note: If you are using Control Panel Classic view or Microsoft Windows 2000, double-click Fonts, and then go to step 4.
3. In the task pane, under See Also, click Fonts.
Note: If a folder tree appears instead of the task pane, click Folders on the toolbar, and then under See Also, click Fonts.
4. On the File menu, click Install New Font.
5. In the Drives list, click the drive you want.
6. In the Folders list, double-click the folder that contains the fonts you want to add.
7. Under List of fonts, click the font you want to add, and then click OK.

Installing the new fonts in BI Publisher
1. Locate the JRE for BI Publisher (JAVA_HOME/lib). This can be done by going to Tools -> Options, then click the preview tab.

xdo.cfg


<?xml version="1.0" encoding="UTF-8"?>
<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/"><!-- Properties -->
<properties>
<!-- System level properties    -->
<property name="system-temp-dir">c:\tmp</property>
<!-- PDF compression
<property name="pdf-compression">true</property>     -->
<!-- PDF Security
<property name="pdf-security">true</property>
<property name="pdf-open-password">user</property>
<property name="pdf-permissions-password">owner</property>
<property name="pdf-no-printing">true</property>
<property name="pdf-no-changing-the-document">true</property>     -->
</properties>
<!-- Font setting -->
<fonts>
<!-- Font setting (for FO to PDF etc...) -->
<font family="Arial" style="normal" weight="normal">
<truetype path="/fonts/Arial.ttf"/>
</font>
<font family="Default" style="normal" weight="normal">
<truetype path="/fonts/ALBANWTJ.ttf"/>
</font>

<font family="BC 3of9" style="normal" weight="normal">
<truetype path="C:\Program Files\Java\jre6\lib\fonts\B39R00.ttf"/>
</font>

<!--Font substitute setting (for PDFForm filling etc...) -->
<font-substitute name="MSGothic">
<truetype path="/fonts/msgothic.ttc" ttcno="0"/>
</font-substitute>
</fonts>
</config>

3. Configure the xdodebug.cfg. This is the standard debug configuration file for BI Publisher. xdodebug.cfg allows you to set the BI Publisher debugging level and to specify the directory where the log file should be written.

This is an example of what should be in the xdodebug.cfg
LogLevel=STATEMENT
LogDir=c:\tmp

If you would like to know more about xdodebug.cfg file it’s available in the Oracle BI Publisher documentation or see the following metalink Note:364547.1. Also, the following posts are good:

BI Publisher: xdodebug.cfg troubleshooting
Get Logging

Troubleshooting the font setup
Step 1: When you run your template and you export to rtf, the specified font should show up correctly. If the font does not show up correctly it's because of the following issues:

A. You may need to do barcode implementation
B. The font is not configured *Most likely the issue
Step 2: Run the template again, but select the output format for PDF. If the barcode font does not show up it could be caused by the followings issues:

A. Verify the font is the fonts directory under the JAVA_HOME/lib/fonts
B. Verify the path is correct. I always use the absolute path to the font.
Example: C:\Program Files\Java\jre6\lib\fonts\B39R00.ttf
C. Verify the font is being found by BI Publisher. In the example above for the xdodebug.cfg we set the log directory to C:\tmp. Navigate to the tmp directory and open the xdo.log file. Locate any errors that might be present.

Example error:
[072909_090808562][oracle.apps.xdo.common.font.FontFactory$FontDef][ERROR] Failed to open TrueType font: /fonts/B39R00.ttf(0)

Example fix:
[072909_091211224][oracle.apps.xdo.common.font.FontFactory$FontDef][STATEMENT] TrueType font created: C:\Program Files\Java\jre6\lib\fonts\B39R00.ttf(0)

BI Publisher: Conditionally Limiting Rows on a Page

2009-06-19T18:13:00.000-07:00

Recently, I've been getting quite a few questions about row manipulation. Besides the solution posted here there are two really good examples for developers to look at that Tim Dexter at oracle created.

Tim D. Examples

Across not Down!
Multi Column Row Woes

Question

Hello Iceman,

I came across your posts on internet. I have trouble with a BI publisher report presentation and was hoping to get a quick answer from you.

I have a query result (4 simple columns) that could result records anywhere between 10 to 70. There is no grouping in this query and it is flat. I have a requirement to show 15 records in each page. Since, I have used syntax, it is showing about 22 records per page in the pdf report.

Is there a parameter in for-each element to limit the number of rows per page in the report?

Thanks in advance,
Srinath Sura

The question that Sirnath posed is quite common. It's not atypical for a business to specify the amount of lines they want to be displayed on a customer facing document (invoice, packing slip, etc). Fortunately, Srinath is not being asked to account for the number of printed lines. That gets tricky because we would have to account for line wrapping. The solution here does not account for line wrapping. If their is interest in seeing how one would go about doing that, post a comment.

In order to tackle Srinath problem were going to have to use two bip functions: position() and mod.

For those of you new to xpath or xsl, position() is the current position in a for-each loop. For each iteration the position is incremented by one automatically. Typically, position does not need to be used in most Xpath operations.

Modulus is a basic mathematical function that divides a number and returns the reminder. It's usefullness is not strictly limited to mathematical applications. As an example, if you have ever written program that creates a Gregorian calendar you have used modulus. Anyways, it's also a valuable function in bip as we will see shortly.

Pseudo Code Solution

If position() mod 15 = 0 then
  Use bip section break
End if

BIP Solution


<?for-each@section:DEPT?> <?//NAME?>
<?../DEPT_NAME?>
<?for-each:EMPLOYEE?>
  Name:  <?ENAME?>   Salary: <?SAL?>
  <?if: position() mod 3 = 0?>
      PAGE BREAK APPLIED
      <?split-by-page-break:?>
  <?end if?>
<?end for-each?>
<?end for-each?>

Pretty simple solution huh! All we have to do is test if the position is divisible by 15, if it's not then we do nothing, otherwise, we do a page-break.

Download the limiting 3 rows per page example

Up and coming posts: Webservices Mayhem

BI Publisher: Reports6i to BIP Conversion Utilities

2009-05-10T19:53:00.000-07:00

Currently, many organizations and developers would really like to make the leap to BI Publisher. One potential option to ease into the BIP tool set is to convert some relatively simple reports6i reports.

There are two ways to do the conversion. Manually do it (see links below) or use the BIP Conversion Utilities.

Oracle OBIEE Documentation
RDFtoBIP.zipped

Doing it manually without a GUI is a good option but it's more time consuming because there is less automation and it doesn't really tell you where your at in the conversion process. Using the BIP conversion utilities is really the way to go though because of it's simplicity. It reminds me of quote by Walter Chrysler

"Whenever there is a hard job to be done, I assign it to a lazy man; he is sure to find an easy way of doing it."

It's refreshingly easy to do the conversion using the BIP Conversion Utilities and extremely fast. Here are the main advantages of using them:

BIP technical expertise is not required for the conversion
Complete automation from start to finish

Download the tutorial video (Quicktime)

Download the code

*By downloading the software you agree that you will not sell or license the BIP conversion Utilities. It's free software for everyone to use capiche.

Instructions
Step 1 : Extract the zip
Step 2: Navigate to directory to the bin directory (ReportConversion->bin)
Step 3: Copy the batch the dos batch file to the directory where you will be converting your reports6i binary to xml. Double click to run.
Step 4: Run the ReportConversion.exe (ReportsConversion->bin, do not move the executable)
Step 5. Click the folder icon
Step 6. Navigate to your conversion directory. Select the files named .rdf.xml
Step 7. Click process button.

Report Conversion FAQ

Q. I need to have reports9i installed on the apps server in order to do the conversion.
A. This is the number one misconception about the conversion process. I have heard a lot of developers say they don't have 10g setup in there apps server environment, so they will have to wait until release 12. In reality all a developer needs to do is install forms and reports 10g locally on there PC. Also, it's possible to have multiple versions oracle forms and reports installed on a PC, just be sure to create a new home for each installation.

Q. The conversion has to take place in unix; it must be done from the command line.
A. Because BIP is written in java it allows the conversion APIs to run anywhere there is a JVM. To resolve this issue you need to grab the BIP jar files. The BIP jar files are included with the conversion utility so there is no need to worry.

Q. The reports6i Binary to XML conversion is a manual process and I have to manually key everything into rwconverter.exe.
A. A dos batch or shell script can be written to accomplish this task. A dos batch script is included with the code.

Q. The conversion is time consuming.
A. It's remarkable fast. The conversion utility is running so fast (30 seconds) that it was very difficult to add an voice over for each clip step in the process. As a matter of fact, the video was extended by nearly a minute to allow for a voice over.

Q. The conversion is flawless.
A. Definitely not. The conversion has some known issues: format triggers, bind variables, etc that don't port (there is a log that can be reviewed after the conversion)! You can find more about those issues on this blog, Tim Dexter blog at oracle or if your feeling lucky you can convert your reports and see for yourself. The utility does a really good job but there is no magic bullet.

BI Publisher: Charts tips and tricks

2009-03-09T06:05:00.000-07:00

The purpose of this post is a tips and tricks guide that explains effective and efficient ways of designing custom charts with BIP and BI Beans. When possible it's always preferable to use Oracle Charting Wizard in the Word plug-in. However if their is a requirement to do conditional logic or dynamic logic, the charting wizard was not designed for this. In this case you would have to manually manipulate the chart xml.

Before getting started on manually manipulating a chart xml file your going to needs some tools and resources.

Tools and Resources:

1. Use an XML Editor. Do not use Microsoft word to edit the chart xml. When you edit it in MSword your much more likely to make syntax errors, and there is no validation that your xml is syntactically correct. I highly recommend XML Pad as editor: http://www.wmhelp.com/

2. Get the chart DTD from oracle. While an XSD is eaiser to use than DTD, it's still better than the alternative which is nothing. The charting DTD is an excellent resource that gets passed over by most developers. Below are two links which should be helpful in your charting endeavors. The first one is Oracle Reports Graphing Faq and the second is the DTD. Note: You will need to copy the dtd and paste it in a file to use it with XML Pad.

I highly recommend reading the Oracle Reports Technical FAQ for graphs. This document is a hidden gem for setting and creating particular properties of a graph. As an example, I was able to find an obscure information like getting a reference line in a graph. I wouldn't have even been able to guess that this was an option by looking at the graph.dtd

http://www.oracle.com/technology/products/reports/htdocs/faq/Graph_FAQ_with_style.html
http://www.oracle.com/technology/products/reports/htdocs/getstart/whitepapers/graphdtd/graph_dtd_technote_2.html

When developers look at the graphing DTD, it seems like it's an impossible document to read and understand. In reality their is no need to understand the DTD. The real purpose of DTD is to assign it to an XML document. When it's assigned your xml editor will validate to make sure your creating a valid structure. The editor will also highlight available attributes for the applicable xml elements. This is one of the most important features of assigning the DTD because the heavy lifting is done automatically for you ie: the editor determines the required elements and attributes.

To assign a DTD in XML Pad go to the menu: XML->Assign Schema/DTD. See that wasn't to bad now was it! Now you should be good to go. Below is a screen shot (sorry for the blurriness, blame blogger, click it to view the whole image) . Notice to the left side of the screen there is a list of properties available. This is the benefit of assigning a DTD. When I clicked on a series line I was able to see all of the available attributes. It gets better though, when I clicked on the markerShape field, a drop down was provided with all the available shapes. Nice...

3. Use Oracle’s template viewer. This tool is another hidden gem and it’s extremely useful for debugging your format template charting issues. The tool can be found in the bi publisher directory in your program files. When running the template viewer be sure to change the “log (debug) level” on the Settings Tab to “STATEMENT” before running the template. As an example, I was able to figure-out a string comparison was being done to a number and the xml element could not be found because it was being treated as a literal string.

Caused by: java.lang.NumberFormatException: For input string: ".//P_ACTIVE_ENGAGEMENT_RATE"

Also, did you know that the template viewer is capable of running in any environment that has the jre? Below is an example the template viewer on my mac. All one would need to do is use the Mac OS X jar bundler and you can have this baby up and running in no time....perhaps a topic for another post :-)

Questions and Answers

This section contains some of the most common questions I have been asked. I tried to put in actual examples so it makes more sense. If you would like to ask a charting question please leave a comment and I'll see what I can do.

Q. Can I do if statements in charts.
A. Yes, remember this is xsl and you have to use that syntax over bip syntax.

Q. Can I do choose statements in charts.
A. Yes, remember this is xsl and you have to use that syntax over bip syntax. (See Example 1)

Q. Can I use xsl variables.
A. Yes (See Example 1)

Q. Do I have to use for-each-group for row data.
A. No . This is a common mistake that people think they have to group the data always. The only reason to group the data would be if you need to aggregate it. If your data is aggregated or you can use xpath with the sum function then the answer is always no. (See Example 1)

Example 1: Get the current month sales, using an xsl variable and default it to zero if there is no data for the month.

Q. If statements are not evaluating correctly or just plain not working.
A. It's likely the if statement may be evaluating the value as string and not number. To solve this use number() (See Example 2)

Q. If statements fail when I use in less than or greater than signs.
A. Use & lt; or & gt; (See Example 2)

Q. My chart keeps failing because the xml field is being recognized as literal.
A. Use the squiggles { } to avoid this issue. (See Example 2)

Example 2: If statement, number check, avoiding literal interpretations

Q. How do I mix static text with dynamic text without using append.
A. This is actually surprisingly simple. Create a “some text like this for {$YOUR_VAR or YOUR_XML_NODE} " (See Example 3)

Example 3: Dynamic substitution text

Q. Can I use reports1og to create a chart.
A. Yes you can use repors10g to setup your graph. There is one caveat; it’s not runnable in BIP. However, it’s a great starting point for complex charting were the bip wizard can be sometimes lacking. When you’re done creating your mockup, save the report as xml file. You can rip out the nodes to figure-out what you need to do in chart in BIP.

This all I got for now. Like I said before if you have a question don't hesitate to leave a comment!

Ike

BI Publisher: Data Templates vs. Reports6i

2009-01-20T09:37:00.000-08:00

Top 5 Reasons to use Data Templates over Reports6i

To start off there are more than 5 reasons!

1. Displaying a reports in real-time
2. Advanced PL/SQL
3. Easy of use
4. Multiple Integration Points
5. No Binary Conversions
6. Used across multiple platforms
7. Integration with multiple data sources
8. Excellent debugging facilities
9. Eliminates Ambiguous Crashing (who doesn't love terminated by signal 11)

I forgot to mention a couple of other benefit about data templates vs reports6i that we haven't ever discussed. Data templates are the only way a developer can generate the xml in real-time in the eBusiness suite or locally on there pc. In comparison, a developer always has to submit a concurrent request in order to generate an xml file with reports6i.

Other benefits of using data templates would be that the logic has to be put into a pl/sql package, whice essentially makes the business logic globally available to all other objects in the database. This provides an amazing code reduction. Developers can create other data templates (that are similar but different) and reuse existing business logic.

Ike

BI Publisher: Data Templates - Simplified Error Handling

2008-12-01T07:25:00.000-08:00

Debugging data templates and "calculated formulas"/"report triggers" can be tricky. This is especially true if a developer needs to test there queries in sql*plus or concurrent manager (what output utility should be used). Or what about user requests for putting error messages directly into format templates dynamically. Well there is a solution to all of these problems and yes you can have your cake and eat it too.

Now in the past I have talked up a storm about pipelined functions and how useful they are for reporting and not just etl. If you haven't read-up on pipelined functions I suggest you do so, you don't know what your missing out on! (see first article, second article)

Below is the outlined technical approach:

1. Pass in a debug parameter that specifies the debug output ie: DBMS, CONCURRENT, XML
2. In the beforeReportTrigger set the debugging method
3. Call a debugging procedure which is able to determine the right logging mechanism.

Now, I know what your thinking, this is way too simple, it can't be that easy. DBMS and CONCURRENT manager output mechanisms are straight forward but how would XML work. This where the pipelined function comes in. Click on the pic below. Notice what happens when the debug procedure finds that g_debug is set to xml. The debug procedure instead populates a pl/sql table, I wonder what were going to with that!

All one would need to do now is create a pipelined function to get the values out the table. Below is another code snippet.

From there it's a simple one line query to get everything out of the pl/sql table:
SELECT seq, message FROM table(sample_pkg.get_debug_log) Click here to download the sample package pl/sql source code.

A couple of things to keep in mind. Because the debugging is parametrized an ebs developer can configure there concurrent program setup ie: set the default for the p_debug parameter to be "CONCURRENT". This is a really nice/flexible feature. As an example a developer could be running this same code locally in toad (or the BIPublisherIDE) and could change the output to DBMS or what have you with no worries because it's parametrized.....

For the reports6i developers you have to admit this sure beats the socks off of srw.message! So, why fight the feeling, make the switch :-)

BI Publisher: Troublshooting the BIPublisherIDE

2008-11-10T08:17:00.000-08:00

For sometime now, I have been receiving request for a troubleshooting guide. I must apologize for not getting one at sooner since it seems like I'm getting monthly help requests. On the right side of the page there is a new link called BIPublisherIDE Troubleshooting. If you think something is missing or you have a suggestion, please add a comment to this blog and I'll see what I can do.

Also, if you have already downloaded the BIPublisherIDE in the past and could it to work, I would suggest you download it again (I fixed a couple of things) and use the troubleshooting manual.

BI Publisher: 500 Plus Downloads

2008-10-25T07:41:00.000-07:00

The BIPublisherIDE standard and OAF edition have been downloaded over 500+ times from this site!

If you are interested an integrated bi publisher developer environment then BIPublisherIDE is the right fit for you. To find more out about the BIPublisherIDE I would suggest you read the following posts:

http://bipublisher.blogspot.com/2008/03/bi-publisher-bipublisheride.html

If your an experienced developer looking to do cutting edge reporting inside the eBusiness suite using the OA Framework I would suggest the following posts:

Part I: http://bipublisher.blogspot.com/2008/03/bi-publisher-bip-in-oa-framework-part-1.html
Part II: http://bipublisher.blogspot.com/2008/03/bi-publisher-bip-in-oa-framework-part.html

BI Publisher: xdodebug.cfg troubleshooting

2008-10-08T08:01:00.000-07:00

Using the xdodebug.cfg file is a great way to debug the BI Publisher API's for custom code that is calling the BI Publisher API's. Below is the metalink note (Note:364547.1) on how to do this but beware.....

Direct report publishing via the XML Publisher Core APIs (without concurrent processing)
1. Connect to the Apache Server as ‘applmgr’. Run the APPSORA.env to set the APPS environment (Windows: envshell.cmd).
2. Create an $XDO_TOP/temp directory.
3. Go to the $OA_JRE_TOP/jre/lib directory.
4. Create an xdodebug.cfg file with the following 2 lines:
LogLevel=STATEMENT
LogDir=[full XDO_TOP]/temp
5. Restart the Apache server.
6. Reproduce the problem.

Unfortunately, Oracle's metalink note is missing one key setup that has to be done. In order for debugging to work you must have a temp directory setup via the template manager. Unbelievably, if you do not have the temp directory setup, debugging actually causes the BI Publisher API's to crash.

The situation is really a quagmire since the reason one would turn on the debugging is to troubleshoot a template issue and in doing so they have exacerbated the issue. Below is the error you can expect to see.

java.io.FileNotFoundException: xdo-1225988068296.tmp (Permission denied)

I retrived this from JSERV log file, see Stack Trace below. Note: We had turned on debugging for the BIPublisher_OAF (bi publisher report pop-ups from forms6i) code to troubleshoot an issue. If your interested in learning how to develop pop bip reports for forms6i, see http://bipublisher.blogspot.com/2008/03/bi-publisher-bip-in-oa-framework-part.html

So, what's happening here is that the bip api's are trying to create a temporary file in $XDO_TOP code tree which only has r-x permissions, hence a file not being able to be created.

The resolution was to setup the temp directory in the template manager (see below) and to do a graceful bounce of apache. Ironically afterwards, there was no issue except turning on the debugging so beware about this configuration in production environments if you have not setup temp directory!

java.io.FileNotFoundException: xdo-1225988068296.tmp (Permission denied)
at java.io.RandomAccessFile.open(Native Method)
at java.io.RandomAccessFile.(RandomAccessFile.java:204)
at java.io.RandomAccessFile.(RandomAccessFile.java:94)
at oracle.apps.xdo.dataengine.ScalableStringList.initSessionIfNecessary(ScalableStringList.java:53)
at oracle.apps.xdo.dataengine.StringList.(StringList.java:51)
at oracle.apps.xdo.dataengine.ScalableStringList.(ScalableStringList.java:38)
at oracle.apps.xdo.dataengine.StringList.createStringList(StringList.java:43)
at oracle.apps.xdo.dataengine.DataGroup.(DataGroup.java:76)
at oracle.apps.xdo.dataengine.DataTemplateParser.groupParser(DataTemplateParser.java:779)
at oracle.apps.xdo.dataengine.DataTemplateParser.dataStructureParser(DataTemplateParser.java:745)
at oracle.apps.xdo.dataengine.DataTemplateParser.templateParser(DataTemplateParser.java:324)
at oracle.apps.xdo.dataengine.XMLPGEN.setDataTemplate(XMLPGEN.java:599)
at oracle.apps.xdo.dataengine.DataProcessor.setDataTemplate(DataProcessor.java:193)
at oracle.apps.xdo.oa.util.DataTemplate.(DataTemplate.java:136)
at oracle.apps.xbol.bipublisher.api.XMLPublisherApi.oaDataEngine(XMLPublisherApi.java:193)
at oracle.apps.xbol.pfp.execsumm.webui.EXECSummGenerator.(EXECSummGenerator.java:92)
at oracle.apps.xbol.pfp.execsumm.webui.EXECSummCO.processRequest(EXECSummCO.java:35)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:587)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1136)
at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(OAFormBean.java:385)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)
at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2335)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1734)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:508)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:429)
at _oa__html._OA._jspService(_OA.java:85)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._RF._jspService(_RF.java:102)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)

oracle.apps.fnd.framework.OAException: EXECSummGenerator - Failed to generate xml file
at oracle.apps.xbol.pfp.execsumm.webui.EXECSummGenerator.(EXECSummGenerator.java:97)
at oracle.apps.xbol.pfp.execsumm.webui.EXECSummCO.processRequest(EXECSummCO.java:35)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:587)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1136)
at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(OAFormBean.java:385)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:959)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:926)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:646)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:247)
at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)
at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2335)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1734)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:508)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:429)
at _oa__html._OA._jspService(_OA.java:85)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._RF._jspService(_RF.java:102)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)

BI Publisher: Let Go My Logo

2008-07-29T08:12:00.000-07:00

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

2008-07-28T02:28:00.000-07:00

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

2008-07-17T02:27:00.000-07:00

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

2008-07-15T13:08:00.000-07:00

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:

Create messages for all hard-coded texts in each language you want to translate to. Give them a general name like XXCUS_TEMPLATE%
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%').
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.

BI Publisher: BIP in the OA Framework part II

2008-06-02T19:00:00.000-07:00

In the last article BI Publisher: BIP in the OA Framework part I we covered how the PO communicate process works. Going forward I will be explaining a project that I wrote that will allow developers to see how all the pieces of the puzzle come together. This project leverages the BIPubliserIDE code. It's been modified to be put into a 11.5.10 cu2 environment, there is a seperate download for this code see BIPublisherIDE_OAF (it's meant to run in OA JDeveloper9i, there is sax issue I'm working around). The classes for this project were refactored but there are very little changes except for coding for AppsContect and OAPageContext. There are some real advantages of doing all your development with BIPublisherIDE. Note: You will probably want to refactor the classes for your company.

As always you are allowed to download this code, modify this code, etc. but you cannot sell it or copyright it for commercial purposes. It's free for everyone to use.

Also, I might generate an avi video that shows the program running and explain a little more about what's going on so you can get this code up and running. That will likely take place in another post...sorry

The Environment: JDeveloper

The main requirement for to run this BIPublisherIDE_OAF locally is that you need to grab all of the classes for $JAVA_TOP/oracle/* or you can use the apps.zip. I prefer to use the classes over the apps.zip, the apps.zip seems to really slow down JDeveloper9i. If you don't have these classes you won't be able to compile the project.

There is a gotcha too in the aol.jar file in the JDeveloper9i. You will need to use this i18nAPI_v3.jar. For more info on this issue, you can read up on it in this post in OAF forum. It's very likely you will also need to remove these classes from the aol.jar file oracle.apps.fnd.common.* (only in JDeveloper9i)

The following jar files are not included in the project and you will have to download them. The following files are needed in order to run the project (see BIP library):

1. j5472959_xdo.zip
2. collections.jar
3. versioninfo.jar
4. i18nAPI_v3.jar
5. xdoparser.jar
6. xmlparserv2-904.jar

*Files 2-6 can be found under $JAVA_TOP/oracle, Item 1. you will have to download the patch

The Environment: (linux, unix or windows)

A lot of companies get "fussy" and there is a generally misunderstanding that you have to put java classes in the oracle's code tree. This is simply not true.

This is not a mandatory step or requirement to understanding the OA Framwork or Java Concurrent Programs, but generally speaking this is a best practice. If your doing java development in the EBS you might not have your environment setup correctly or at all. Here's what you should have setup:

Under YOUR custom top create the following directory structure for your customizations: $XX_TOP/java/oracle/apps/[your_top_here]

Note this is not to be confused with $JAVA_TOP. This is the code tree for your companies customizations. You will also need to add this as the VERY FIRST classpath in the jserv.properties file in apache and do a hardbounce. This will not only allow your classes to be loaded but it will also allow you to override oracles classes without removing/overwriting them in $JAVA_TOP.

How It Works

The design for the BIPublisherIDE_OAF is to allow developer to have a standardized way of interacting with the BIP Api's seemsly despite what environment there in. There are two ways of running data templates and format templates using the BIP Api's in the EBS:

1. XML Publisher Repository (EBS environment)
2. Flat-files (Non-EBS environments or Development)

The solution a developer should use for the OAF is pulling the data template and format template from the XML Publisher Repository. The two oracle bip classes to help you achieve this are:

1. Data Templates: DataTemplate.class
2. Format Templates: TemplateHelper.class

The BIPublisherIDE_OAF code has done all of the wrapping around these classes. A developer doesn't really have to worry about much except for a basic understanding of the OA Framework. In order to get a report to display in a browser the developer has to code the following:

1. have the application short name for the format template
2. have the code (name) for the format template
3. have the application short name for the data template
4. have the code (name) for the data template

5. set the parameters. note the code is designed for the OAF piece to map the session parameters to BIP parameters for the data template. This is unnecessary for java concurrent programs, because the parameter are in hashTable already.

6. Output type (PDF, EXCEL, HTML, RTF) (this is done in XMPublisherAPI constructor)

7. generate the xml

8. apply the template

9. display the document in the OAF.

Here's the sample code. Pretty basic. You will need to extend the XMLPublisherAPI, call the superclass and everything is good to go. All you have to do is call the api's for format and data templates.

You'll also notice in the snippet below that an assumption is not made that the data template code and format template code match.

package oracle.apps.xbol.ipt.webui;

import oracle.apps.fnd.common.AppsContext;
import com.sun.java.util.collections.Hashtable;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.common.EnvironmentStore;
import java.util.Enumeration;
import java.io.File;
//import java.util.Random;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.xbol.bipublisher.api.XMLPublisherApi;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;

public class IPTInvoiceGenerator extends XMLPublisherApi
{
static oracle.apps.xdo.common.log.Logger logger;
public IPTInvoiceGenerator (OAPageContext pageContext, AppsContext appsContext, OAWebBean webBean)
{
//output type is mandatory!!!!!
super(pageContext, appsContext, webBean, pageContext.getParameter("output"), getOALookup());
StringBuffer sb = new StringBuffer();
String xdoTempCode ="";
String xdoTempShort ="";
String xdoDataCode ="";
String xdoDataShort ="";
try
{
setStatus("SUCCESS");
//int i = System.getProperty("os.name").indexOf("Linux");
//set the org if were in linux....
if (System.getProperty("os.name").indexOf("Windows") == -1 )
{
String orgId = pageContext.getParameter("orgId");
setOrgContext(orgId);
}

logger.getLog().setLevel(logger.STATEMENT);
setOutputDirectory(getFileDirectory(pageContext));
setOutputFile("COSTPLUS_"+getRequestId());

//setup data template code
if (System.getProperty("os.name").indexOf("Windows") == -1 )
{
String orgId = pageContext.getParameter("orgId");
setOrgContext(orgId);
xdoTempCode = pageContext.getParameter("template");
xdoTempShort = pageContext.getParameter("tempShort");
xdoDataCode = pageContext.getParameter("dataTemplate");
xdoDataShort = pageContext.getParameter("dataShort");
} else
{
//hard-coding for windows...See screen shots of BIP config to make sense of
//whats going on
xdoTempCode = "XXPO_INT_INVOICE";
xdoTempShort = "XBOL";
xdoDataCode = "XXPO_INT_INVOICE";
xdoDataShort = "XBOL";
}
if (xdoTempCode == null)
{
throw new OAException("IPTInvoiceGenerator- Template code not provided", OAException.ERROR);
}
if (xdoTempShort == null)
{
throw new OAException("IPTInvoiceGenerator- Template short name not provided", OAException.ERROR);
}
if (xdoDataCode == null)
{
throw new OAException("IPTInvoiceGenerator- Data Template code not provided", OAException.ERROR);
}
if (xdoDataShort == null)
{
throw new OAException("IPTInvoiceGenerator- Data Template short name not provided", OAException.ERROR);
}

//set data template code, no link is required to the format template
setXdoDataDefinitionCode(xdoDataCode);
setXdoDataDefinitionApplicationShortName(xdoDataShort);

//setup format template code, no link is required to the data template
setXdoTemplateCode(xdoTempCode);
setXdoTemplateApplicationShortName(xdoTempShort);

//if it's windows than use hard-coded hashtable, otherwise parameters are set in super class
if (System.getProperty("os.name").indexOf("Windows") >= 0 )
{
setParameters(getHashtable());
}

//apply the format template
if ("SUCCESS".equalsIgnoreCase(getStatus()))
{
oaDataEngine();
}

if ("FAILURE".equalsIgnoreCase(getStatus()))
{
throw new OAException("IPTInvoiceGenerator- Failed to generate xml file", OAException.ERROR);
}

//apply the format template
if ("SUCCESS".equalsIgnoreCase(getStatus()))
{
applyTemplate();
}

displayOAFDocument(); //pop the document up on the browser

if ("FAILURE".equalsIgnoreCase(getStatus()))
{
throw new OAException("IPTInvoiceGenerator- Failed to apply format template", OAException.ERROR);
}

//dumpProperties(pageContext,appsContext);
}
catch (Exception e)
{
e.printStackTrace();
pageContext.writeDiagnostics(this, "IPTInvoiceGenerator::constructor - error - " + e.getMessage(), 1);
} finally
{
appsContext.releaseJDBCConnection();
}
}

public final static Hashtable getOALookup()
{
Hashtable parameters = new Hashtable();
/**@Mapping:
*=================== OA Parameter | @BIP Parameter (see form function call =============
bi publisher ide will search oa parameters, get there values and map them to the data template parms.
*/
parameters.put("P_MLOGO_ID" , "P_MLOGO_ID");
parameters.put("P_CONC_REQUEST_ID" , "P_CONC_REQUEST_ID");
parameters.put("P_DIR_PROJECT_ID" , "P_DIR_PROJECT_ID");
parameters.put("P_INDIR_PROJECT_ID" , "P_INDIR_PROJECT_ID");
parameters.put("P_INVOICE_NUMBER" , "P_INVOICE_NUMBER");
parameters.put("P_COST_PLUS_BILLING_ID" , "P_COST_PLUS_BILLING_ID");
parameters.put("P_TO_DATE" , "P_TO_DATE");
parameters.put("P_FROM_DATE" , "P_FROM_DATE");
parameters.put("P_ORDER_BY" , "P_ORDER_BY");
parameters.put("P_ORDER_BY_BILL_CODE" , "P_ORDER_BY_BILL_CODE");
parameters.put("orgId" , "P_ORG_ID");
parameters.put("P_DEBUG" , "P_DEBUG");
return parameters;
}

//this is for testing in windows...
public static Hashtable getHashtable()
{
Hashtable parameters = new Hashtable();
parameters.put("P_MLOGO_ID" ,"1");
parameters.put("P_CONC_REQUEST_ID" ,"0");
parameters.put("P_DIR_PROJECT_ID" ,"798");
parameters.put("P_INDIR_PROJECT_ID" ,"");
parameters.put("P_INVOICE_NUMBER" ,"24636");
parameters.put("P_COST_PLUS_BILLING_ID" , "");
parameters.put("P_TO_DATE" ,"");
parameters.put("P_FROM_DATE" ,"");
parameters.put("P_ORDER_BY" ,"D");
parameters.put("P_ORDER_BY_BILL_CODE" , "YES");
parameters.put("P_ORG_ID" , "84");
parameters.put("P_DEBUG" ,"XML");
return parameters;
}

public String nvl(String str, String replacementValue)
{
return str == null || str.equalsIgnoreCase("")? replacementValue: str;
}

public String getFileDirectory(OAPageContext pageContext)
{
StringBuffer path = new StringBuffer();
try
{
String osName = System.getProperty("os.name");
pageContext.writeDiagnostics(this, "IPTInvoiceGenerator::getFileDirectory() resolved operating system - " + osName.toString(), 1);
if (osName != null && osName.indexOf("Windows") >= 0)
{
//files go right to the c: drive
path.append("C:");
path.append(File.separator);
}
else
{
//files go to the tmp directory in unix.
path.append(File.separator);
path.append("tmp");
path.append(File.separator);
}
}
catch (Exception e)
{
pageContext.writeDiagnostics(this, "IPTInvoiceGenerator::getFileDirectory() - " + e.getMessage(), 1);
}
pageContext.writeDiagnostics(this, "IPTInvoiceGenerator::getFileDirectory() resolved file dir - " + path.toString(), 1);
return path.toString();
}

public static final String RCS_ID = "$Header: COSTPlusGenerator.java 115.1 2008/03/27 08:18:27 iwiggins noship $";
public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion("$Header: COSTPlusGenerator.java 115.1 2008/03/27 08:18:27 iwiggins noship $", "oracle.apps.xbol.costplus.webui");
}

The design to associate a format template with and data template is intentional. This will allow a developer to link up any format template with any data template and this is the way it should work. Going forward oracle fusion publisher will probably work the same way which is great!

See Tim Dexter's blog: http://blogs.oracle.com/xmlpublisher/2008/06/02#a986

So now that we have the code, how do we call this from a form? It's really simple...We have to call an OAF page and the controller class has to run something similar to the snippet above. Now your probably wondering how do I call an oaf page from forms? We need to leverage fnd_function.execute and call a menu function for the OA page and the rest is history.

Here's a snippet of how in your form:

--NOTICE THESE MAP OUT IN THE JAVA SNIPPET ABOVE (see getOALookup())
--For the sake of consistency I named the parameters the same for oaf and the data template.
--you can't see it but this form actually supports 3 similar reports!!!!

fnd_function.execute(
function_name => 'XX_VIEW_IPT_DOCUMENT',
other_params=>'P_DIR_PROJECT_ID='||:BILL_ITEMS.PROJECT_ID||
--'&P_INVOICE_NUMBER='||:BILL_ITEMS.INVOICE_NUMBER||
'&orgId='||:BILL_ITEMS.ORG_ID||
'&output='||:CTRL_BLOCK.XDO_OUTPUT_TYPE||
'&template='||:CTRL_BLOCK.TEMPLATE_CODE||
'&tempShort='||:CTRL_BLOCK.TEMPLATE_APP_SHORT_NAME||
'&dataTemplate='||:CTRL_BLOCK.DATA_SOURCE_CODE||
'&dataShort='||:CTRL_BLOCK.DATA_TEMPLATE_APP_SHORT_NAME||
'&P_DEBUG=XML'||
'&P_COST_PLUS_BILLING_ID='||:BILL_ITEMS.COST_PLUS_BILLING_ID||
'&P_ODRDER_BY_BILL_CODE=YES'||
'&P_ORDER_BY=D'
);

Now to fill in some of the gaps, below is a highlevel diagram, the form and bip config. You may need to click on the image to see the whole picture.

Notice that BIP code, data template and format templates can now be leveraged with a concurrent request or in forms6i!!!!

Have to make it sswa!

Wow, are there two oaf pages here, does the other page pop up another report? Yep it sure does. It works really slick, users never have to key a single parameter and they can also run a series of reports right from the form!

Notice the data template! If you still creating xml from reports6i, you should really stop doing this, your coding yourself in a corner!

BI Publisher: xdo.cfg & BIPublisherIDE

2008-06-02T15:32:00.001-07:00

For those of you out there that have downloaded the BIPublisherIDE a question may be plaguing you: "How do I use the xdo.cfg or xdodebug.cfg file, where do I put those suckers?"

After some digging today I helped out a soon to be poster on this blog, Brett Baldwin. Here's how you dig into this problem.

Step 1: Run the BIPublisherIDE
Step 2: Pause it in JDeveloper and look at the log for the following text:

Note: Directory structures will differ based on your versions of jre or drive letters

[060208_051345725][][STATEMENT] XDO version = Oracle XML Publisher 5.6.3
[060208_051345725][][STATEMENT] java.home = C:\Program Files\Java\jre1.6.0_06
[060208_051345725][][STATEMENT] XDO_TOP = null
[060208_051345725][][STATEMENT] Config Path = null
[060208_051345725][][STATEMENT] Debug Cfg Path= null
[060208_051345740][][STATEMENT] Font dir = C:\Program Files\Java\jre1.6.0_06\lib\fonts\
[060208_051345740][][STATEMENT] Locale = en-US
[060208_051345740][][STATEMENT] Fallback font = type1.Helvetica

Step 3: Find the java home, see above and blue and create two files in the lib directory for our example it would the following:

C:\Program Files\Java\jre1.6.0_06\lib\xdo.cfg
C:\Program Files\Java\jre1.6.0_06\lib\xdodebug.cfg

Step 4: Add whatever additions you need to these files, for xdo.cfg this is particularly useful for fonts (barcodes), currencies, etc (see oracle's bip manual). For xdodebug, you can use the xdodebug.cfg (really not necessary client side, however very useful server-side and for oaf customizations.

Step 5: Re-run or click debug, You should see the following the configs are now being picked up!

[060208_052916553][][STATEMENT] [ PDF GENERATOR ]---------------------------------------------
[060208_052916553][][STATEMENT] XDO version = Oracle XML Publisher 5.6.3
[060208_052916553][][STATEMENT] java.home = C:\Program Files\Java\jre1.6.0_06
[060208_052916553][][STATEMENT] XDO_TOP = null
[060208_052916553][][STATEMENT] Config Path = C:\Program Files\Java\jre1.6.0_06\lib\xdo.cfg
[060208_052916553][][STATEMENT] Debug Cfg Path= C:\Program Files\Java\jre1.6.0_06\lib\xdodebug.cfg
[060208_052916553][][STATEMENT] Font dir = C:\Program Files\Java\jre1.6.0_06\lib\fonts\
[060208_052916553][][STATEMENT] Locale = en-US
[060208_052916553][][STATEMENT] Fallback font = type1.Helvetica

Hope this was helpful, Keep on Bipin!

BI Publisher: Totals got ya down

2008-05-07T16:24:00.000-07:00

Alright, if you have ever converted a report6i to a data template in bi publisher using the api's or by hand you will know one thing, removal of the integration of summary columns and formula columns or vice versa in lower groups is a total "beach."

I said it, "beach." There is a sneaky workaround, not pleasant but doable. When your report gets converted you might see something like this:

Functional flow in reports6i:
[First Group (CF_TOTAL_AMOUNT combines summaries columns)]-->[First Group has summary columns (:cs_detail_amount, :cs_detail_state, :cs_detail_federal)]

SQL Example:
select
cf_total_amount(:cs_detail_amount, :cs_detail_state, :cs_detail_federal
...
...
from ap_invoices
where blah, blah

The basic probably we have here is that cs fields are in the same group, but when the query runs the summed amounts haven't taken place, nor will they, a classic paradox. You will get nothing for the cf_total_amount. Quite the conundrum....or is it.

I guess, it;s time to pull this calculated formula out and give up. The business your working for will totally understand that there are limitations to technology. Alright, back to reality, let's make this puppy work.

Solution remove the functions and create something like this for a sqlQuery and new group :

select
cf_total_amount(:cs_detail_amount, :cs_detail_state, :cs_detail_federal
...
...
from dual

Organize your data template to do the summing below all of the other current group(s). Follow up with the new group with the calculated formula's select from dual with the summary columns. Here's the logic breakdown:

Order of execution:
1. [First Group (get all the fields that will be summed)]
2. [Summary Elements (summary columns here)]
3. [Second Group (select calculated formula(s) from dual with summary column values)]

Why:
Fields that are going to be summed must be higher than the summary columns. It's simple, we select the calculated formula's from dual with the summary bind variables.

This approach should work for those of you that are aspiring new bip developers that are converting reports6i to data templates. You will find that people believe or not will have functions calling summary columns in the same group. This example should help with other "minor" limitations with bip with summary columns and calculated formulas.

BI Publisher: BIP in the OA Framework part I

2008-03-17T07:00:00.000-07:00

Have you ever wondered how the PO report pops up without a submitting a concurrent request? Pretty sweet huh! Well your going to know how do it after you reading this blog.

When this type of functionality came out in 11.5.10 myself and others wondered how is this possible, how can you generate a document and display it in "real-time." After digging into in 2005, I figured out that it was using the OA Framework. The problem for me at the time though was that I didn't have a clue about the OA Framework. I was literally baffeled and frustrated (see screaming baby above). I kept searching for the poViewDocumentPG and for the life of me I couldn't freakin' find it.

Well it's a two years later and I finally spent some time into figuring it out. A couple of key factors was reading the documentation, plus, I've started doing some OA Framework development. When we look at bare-bones how a pdf can be displayed on a screen in using the OA Framework it's really straightforward. Throughout this document I'm going to refer to this type of development as the "Real-Time" model.

How the Oracle PO Works

Step 1. find the form function PO_VIEW_DOCUMENT,

HTML CALL: OA.jsp?page=/oracle/apps/po/communicate/webui/poViewDocumentPG

Step 2. Create an OA framework page, very simple. This is ridicuosly easily. You need to have a controller and application module. Note, the application module doesn't have to have anything it but it must exist.

FYI: For our example, the PO_VIEW_DOCUMENT function has a page called poViewDocumentPG, however when we go out to the code tree (/oracle/apps/po/communicate/webui/) it doesn't exist. This because the xml files are loaded in the database. We can use this utility to download it:

begin

JDR_UTILS.printDocument('/oracle/apps/po/communicate/webui/poViewDocumentPG');

end;

We can see it's a bare bones page with one region and zero fields.

Step 3: write the logic in the controller class to get the document and return it. Note: this is a weird way to create an OAF page, usually you call the Application Module to generate the data and set the where clauses for the view objects in the AM, which bind to the OAF page. However the controller in this example generates the data/document and use HttpServletResponse to create the page, the application module doesn't really do anything. The application module could if you wanted it to, the blanket sales agreement is an example where the AM generates the xml.

sample code snippet:

public class poViewDocumentCO extends oracle.apps.fnd.framework.webui.OAControllerImpl
{
public void processRequest(oracle.apps.fnd.framework.webui.OAPageContext oapagecontext, oracle.apps.fnd.framework.webui.beans.OAWebBean oawebbean)

int j = java.lang.Integer.parseInt(oapagecontext.getParameter("DocumentId"));
....
....
....
....
httpservletresponse.setContentType("application/pdf");
httpservletresponse.setContentLength(abyte0.length);
httpservletresponse.setHeader("Content-Disposition", "attachment;Filename = " + pogeneratedocument.getfileName());
httpservletresponse.getOutputStream().write(abyte0, 0, abyte0.length);
httpservletresponse.getOutputStream().flush();
httpservletresponse.getOutputStream().close();
return;

Step 4: Call the function from your form to create basically a pop up and generate the document. Exluded in step 3's code above is the snippet to grab all the parameters that are appended together below from the function call in the form. I left just one so you would get the point.

fnd_function.execute( function_name => 'PO_VIEW_DOCUMENT',
other_params=>'DocumentId='l_document_id'
&RevisionNum=':HEADERS_FOLDER.REVISION_NUM'
&LanguageCode='userenv('LANG''
&DocumentType='l_document_type'
&DocumentSubtype='l_document_subtype'
&OrgId=' :po_startup_values.org_id'
&AuthorizationStatus=':headers_folder.authorization_status'
&UserSecurity='userSecurity '
&StoreFlag=N'
&ViewOrCommunicate=View');

DONE DEAL

Yes that's it, there really is no OA Framework code to write for the application module or controller. Note, as a best practice if you decide to do this development, your controller class should call another class to generate the final output, the purpose of the controller in this case is purely to push the document upto the browser. This is a better practice because it allows the developer to create new and additional constructors for one class that generates the document, versus copying and pasting the logic in each implementation (OAF, JavaConcurrentProgram, etc).

Things to Keep in Mind

If your still doing report development using reports6i you really need to stop that. While reports6i is a great tool, it has one big downside (besides being dated and replaced with BIP) and that is in-order to generate xml file, you have to submit a concurrent request (there are workarounds for this, none that are pleasant). This doesn't work in the "real-time" model.

In the above example the PO will actually generate the xml using pl/sql package. While this was acceptable in the past, you really shouldn't be doing this either.

Data templates should be used for generating the xml. Besides ease of use and it being less intense than using pure pl/sql, in this model it works because it can be run in "real-time."

There are a lot of possibilities with form personalization's to plug reports in line with standard oracle forms. Simply create a menu and call a built-in function which submits a function call to the OA Framework page. This allows the developer to have even more flexibility in customizing the application and reporting, while saving users time for keying and submitting concurrent requests.

A caveat here is don't do the real-time model with slow reports. Slow reports should be concurrent requests so they can be managed and maintained more effectively. From a high-level it doesn't make any sense at all to have a slow running report in real-time, it's a distraction for end-users and it may make it harder for them to manage there report(s), hence concurrent manager.

Part II: The next article will have a zip of an OA Framework project that will push a pdf up on the screen. Booyah!

Part III: Understand BIP Integration points

BI Publisher: BIPublisherIDE

2008-03-12T12:17:00.000-07:00

BI Publisher Integrated Development Environment

If you ever wondered how the BI Publisher API's worked then look no further. This is the ultimate blog on how the API's and there is a free working code base you can download and use for development.

There are four caveats:

1. This code is free but cannot be copyrighted, unless your oracle (but you need to discuss terms first with me)

2. I don't think I can distribute xdocore.jar file, you will need to download either in the latest patchset (ebs) or standalone version.

For 11i environment, I would suggest downloading patch: 5472959. If you put the j5472959_xdo.zip file in c:\JDeveloper\jdev\mywork\BIPublisherIDE\BIPublisherIDE\lib
the project should work.

3. If you improve the project you should share the information here in this blogpost.

4. If you download the project please post a "positive comment." Kidding...but it would nice!

History

Long ago, I was doing a tremedous amount of BI Publisher development inside the EBS and Oracle Portal. One of the problems I was having is that BI Publisher development can be time consuming when you continually have to reload your format template, data template into the database and re-iteratively submit concurrent requests.

I was also faced with other issues as well, what if I wanted to test eText and Bursting locally? There was no option to do this any product. The only way to test eText is start->programs- your bip installation-> run template viewer

How It Works: Architecture

The BIPublisherIDE is setup for a developer by a developer (it's just like the hair club for men, "I'm not only a member but I'm also a client.")

There is a config class, api class and utility class.

config->api->utility

1. The config class contains methods and creates connections to databases.

2. The api's class extends the config class and inherits the methods and has the ability to override the methods. This class contains all of the bi publisher core api's.

3. The utility class extends the api class, put simply the utility class is a wrapper. It sets the foundation for which api's to call based on what is set in the config file (xml).

The design is intentional and sets a precedence for doing report development. As developers we iteratively run unit tests till we receive the desired result. One of the most cumbersome problems we face is that there is a lot of "non-value" added steps in report development process. This is why report development to a certain degree needs to be configurable and run locally.

Other benefits of the design is that fact that it's not only a development tool, it can be deployed in other environments. For instance you can deploy the BIPublisherIDE jar file in a concurrent program or other web based environment like portal, websphere, etc (this might be helpful for apex too).

How to run the IDE (BIPublisherIDE.pdf)

Download the IDE (BIPublisherIDE.zip)

Next Article to Come: How to display a BIP Report using the OA Framework (i.e: like the PO)

BI Publisher: XDO Loader

2008-01-22T19:42:00.000-08:00

How many times has a developer approached you and said, "I made the changes to my template and put it out on the code tree, but my change isn't being recogonized in 11i." Your response could be two things.

1. You need to upload it into the database using the XML Publisher Administrator responsiblity.
2. Use xdo loader, an example is on ike's blog...

Number 2, is really the best option (he he )....if the template config is already done. When you start browsing and uploading files from your PC or emailing them to your administrator, your opening the door for mistakes, mistakes suck when they happen in production. One of the biggest advantages of using XDO loader is the fact that what's out on the code tree is what will get loaded into the database. The only other workaround for loading templates from unix is samba...

Implementing a solution using XDO Loader in your shop is really easy task. These sample calls are designed to be called from a shell script. They are for existing templates that have been configured in 11i. Essentially, calling this script will automatically, convert your RTF to XSL-FO and upload it in the database.

Static Example:
/opt/java1.5/bin/java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION sand:1535:NADV \
-LOB_TYPE TEMPLATE_SOURCE \
-APPS_SHORT_NAME VCA \
-LOB_CODE VGMDQCARD \
-LANGUAGE en \
-TERRITORY US \
-XDO_FILE_TYPE RTF \
-FILE_CONTENT_TYPE 'application/rtf' \
-FILE_NAME $VCA_TOP/xml_pub/template/VGMDQCARD.rtf \
-NLS_LANG $NLS_LANG \
-CUSTOM_MODE FORCE \

Dynamic Example:
/opt/java1.5/bin/java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION $1 \
-LOB_TYPE TEMPLATE_SOURCE \
-APPS_SHORT_NAME VCA \
-LOB_CODE $2 \ -LANGUAGE en \
-TERRITORY US \
-XDO_FILE_TYPE RTF \
-FILE_CONTENT_TYPE 'application/rtf' \
-FILE_NAME $VCA_TOP/xml_pub/template/$3 \
-NLS_LANG $NLS_LANG \
-CUSTOM_MODE FORCE

$1 = JDBC Connection string this could be simplified by setting up an environment variable.
$2 = this is the name of the template code (select LOB_CODE from xdo_lobs)
$3 = this is the RTF flatfile

-CUSTOM_MODE FORCE = FORCE the update if a lob already exists, otherwise it will error out....

BI Publisher: Blog Tag

2008-01-08T20:36:00.000-08:00

Tim Dexter has decided to blog tag me. Being a good sport I will accept his challenge. Basically, I have to reveal 8 things that people wouldn’t know about me unless I told you.

1. Being 50% Irish, 25% German, 15%, Scottish, 5% English. I have a fond love for beer, it’s genetic :-). I have brewing beer successfully at home for over 2 years and yes it’s very drinkable I might add. It’s because of this I would consider myself a “beer snob.” If you know who Michael Jackson is, then you’re probably a beer snob too and I’m not talking about the singer….

2. I lack any form of shame. Two small examples: There is a childhood picture of me in my senior yearbook riding a horse naked or the fact that in my previous driver license photo I don’t have my shirt on.

3. I’m notoriously cheap. If you ask my wife she’ll say this is a typical conversation: Does it cost money? Then it’s been decided were not doing it.

4. I still use my college student ID to get discounts at movie theaters. I’m 28 years old….On a side-note, I convinced my wife to do the same thing :-)

5. A typical vacation for me is usually a tropical destination. I love to dive. Diving is one of the most relaxing sports around. I’m jaded though because it’s quite an expensive hobby (see #3).

6. I recently got into Olympic lifting, it’s awesome sport.

7. PBS Junky

8. I believe global warming is a real phenomenon...

BI Publisher: Common Use for Bursting API

2007-11-16T07:16:00.000-08:00

This is an excerpt of a post requesting to do three things in one shot (grab an xml, apply a template, send it to the printer). This exactly what bursting was designed for. Because of the obscurity of how to use the java api's for BI Publisher, people overlook the fact that you can call a majority of the api's through the command line. Nearly every BI Publisher api that is documented has a main method.

Question:

I have xml documents coming in on a linux directory. I would like to take the raw xml data and apply a template and send it to a printer.

Questions are:

1. Since there is no way to tell when the xml documents will appear for processing, is there any way I can make the BIP poll the linux directory to check for incoming files?

2. How can I apply a template and make the raw xml data beautiful?

3. Finally, after template application, I need to take the output and send it to a printer. I believe I will have to use delivery manager api's to do it.

Can someone help with the three questions?

Response:

I don't know if the xml gateway will solve your problem as "punit" suggested. don't worry though, this problem can get solved pretty easily, Chin up cowboy.

I would setup an autosys job that calls a shell script that loops through the files in a directory and executes a command line argument for the busting api's.

It's going to expect the following arguments:

java oracle.apps.xdo.batch.DocumentProcessor [-debug] -tempdir -datafile

you may need to resolve the whole path. echo $JAVA_TOP. That it's, there's really not much to the api.

BI Publisher: More for-each tags

2007-11-12T16:34:00.000-08:00

Okay, you may be wondering what is up with the iFrames. Totally, annoying I know. However, blogspot has a tendency to replace the < ? ? > all of the time. This site is not straight HTML it's XHTML. Which is cool but it has it's own issues. I'm only doing the iFrames so you can cut and copy and paste.

Like other disclaimers, nothing here is guaranteed to work it's an overview or tutorial. If you feel like something is wacked out, send me a shout, I will fix it and give you mad props.

Please excuse the iFrame for the tutorial. See below.
&amp;amp;amp;amp;amp;amp;lt;br&amp;amp;amp;amp;amp;amp;gt;

BI Publisher: Understanding the for-each tag

2007-11-06T19:47:00.000-08:00

This should be viewed as a supplement guide to oracle standard documentation! For-each in BI Publisher (and xml in general or basic programming) causes a lot of issues for new developers or business analysts. The key to understanding xml and bi publisher is “Context.”

Vocabulary
Context: Context is used to describe where you currently at in a set of relationships. As an example: Right now your sitting down in a chair , in some room or cube, which is in building, which is in some city, some state, some country, well you get the picture.
Hierarchal Relationships: Things that exist between one more objects. An example would be a parent an child. Child is a product of the parent.
Root: is the start or the beginning of something. An example lets get to the root of the problem.
Node/Element: Basically an item of some sort. Your keys are elements of your key chain.

Let’s Get Started
The for-each tag is used to loop through and xml document. An xml document always has a root node or starting node (see bold green). All xml documents have heirachal relationships. In the example below we can see the following: an Employee is a child of dept and dept is a child of corporation. Every employee, dept and corporation have elements and these elements are typically unique to them (hence the coloring scheme).

Cont...Please scroll through
&amp;amp;amp;amp;amp;amp;lt;br&amp;amp;amp;amp;amp;amp;gt;

BI Publisher: Bursting - How to resolve pruning issues

2007-10-22T16:13:00.000-07:00

Andrew Papastefanou for South Africa wrote:

Hi,

When bursting according to a specific location in my data, I find that I
cannot access fields outside the scope of the 'select' in the control file.

I have the following xml file:

My select request in my control
file tries to burst according to branches as follows:

DATA
_CONTROL_AREAS
__CONTROL_AREA
___CNAME
___BRANCHES
____BRANCH
_____DIVISIONS
______DIVISION
_______AGENTS
________AGENT

My report template however, needs to be able to display "CNAME" which is a node in the /DATA/CONTROL_AREAS/CONTROL_AREA section of data. Opening the generated report shows a blank space where the field 'CNAME' should be.

Is this information available when I use a 'select'
to somewhere inside the tree?
Or do I need to create a new template each
time to display the outer data?

I tried many XPATH combinations but was
unable to get that to work either.

Thanks in advance!

The two selects Andy was doing were as follows and in this order:
request select="/DATA/CONTROL_AREAS/CONTROL_AREA"
request select="/DATA/CONTROL_AREAS/CONTROL_AREA/BRANCHES/BRANCH"

The reason CNAME was not showing up in the xml was because it was pruned (removed) from the previous red select...There were a couple of responses on how to solve the issue. CJ (Chris Brown in the BI Publisher Forum) Suggested to add the CONTROL_AREA deeper in the xml at the same level with BRANCH. Not a bad idea, but there is a an easier way to solve this issue.

This alternative solution I came up with was to just switch the order like so:

request select="/DATA/CONTROL_AREAS/CONTROL_AREA/BRANCHES/BRANCH"
request select="/DATA/CONTROL_AREAS/CONTROL_AREA"

Now the only thing that will have gotten pruned will be everything from BRANCH. There is another solution for this issue as well.

Create two burst files and call the DocumentProcessor API in a java concurrent program. However, this solution could be considered excessive compared to previous mentioned ones.
Sample Call: DocumentProcessor dp = new DocumentProcessor(getBurstDirectory() + getBurstFileName(), getOutputDirectory() + getOutputFile() + ".xml", getOutputDirectory());

Now when you run into this issue, you can keep your cool and remeber that prunes are for old people.

Ike

BI Publisher: Merge PDF's - Why would I ever want to do that?

2007-10-18T08:48:00.000-07:00

When you first read that BI Publisher has the ability to merge PDF documents it's neat but you think, "I’ll never use that." Well not necessarily. Remember merging files together is new functionality in the EBS suite. Nobody knows that developers have this kind of power at there finger tips now. But it still gets a back to why would you ever want to do this. There are really two main reasons to merge PDF documents:

1. Automation
2. Desperate Content (documents in different systems)

Let’s just say you’re dealing with PDF attachments in EBS Suite. The business has requested that when there is an attachment they want it to be apart of some other standard document. Well, you can’t do that in reports6i with PDF’s but in BI Publisher this is a no brainer. You grab the blob, write out to file and then call the PDFDocMerger Utility.

Here’s a more long winded scenario. Your company works with hazardous materials. Some chemicals are so volatile that if there ever was a spill, having a MSDS (Material Safety Data Sheet) with the shipper/driver might be extremely helpful to authorities to resolve the situation quickly and safely. However you shipping personnel on the floor are just absolutely swamped and there is potential to miss that step. Well, the document merger might be nice tool solve that issue. Now the shipping personnel don’t have to worry about finding an MSDS on the system to give to the driver. This happens automatically based on some business logic coded in a concurrent program that generates a BOL and then finds a MSDS and merges the two documents before printing (Automation and Desperate Content). The shipping personal go right to the printer and pick up one document!

Normally, I would paste some sort of code snippet here. But the BI Publisher documentation for the PDFDocMerger API is really good and implementing it is very simple.

Good Luck!

Ike

BI Publisher: What the Lexical! Understanding why, when and how to use lexicals and bind variables correctly

2007-10-15T20:28:00.000-07:00

Lexicals can be confusing for new developers. Lexicals & Bind variables are used in the following technology sets:

1. BI Publisher
2. Reports6i
3. SQL Scripts
4. PL/SQL

A bind variable is used for the assignment of value, a lexical is the literal value. The syntax is as follows for a bind variable :P_SEGMENT and the sytax for lexical is &L_WHERE_CLAUSE. Here’s how you would use the two of these in a query:

1. Select * from mtl_system_items where segment1 = :p_segment
2. Select * from mtl_system_items where &l_where_clause

Here are some neat tricks with lexicals and how they can be combined with bind variables. Below we can see that a lexical can contain a bind variable. This may seem confusing but it has major implications for the performance of your query.

Good:
l_where_clause := 'segment1 = :p_segment';

Bad:
l_where_clause := 'segment1 = '''p_segment'';

Appending segment1i s a bad idea because it causes a performance issue. Every time this query is parsed it needs to generate a new explain plan. So nothing gets cached. Where as the lexical with a bind variable is much better, because the statement gets cached.

The performance issue doesn’t really rear its ugly head until it gets in a production environment or this query is being executed in some sort of batch process.

That’s it, that’s all there is to lexical and bind variables.

Good Luck!

BI Publisher: Taking it to the next level part II (PL/SQL Tables)

2007-10-10T10:00:00.000-07:00

You may find this useful if you read my previous article (PL/SQL Taking it to the next level). This is an excerpt of what I posted as a solution to a user in the BI Publisher forum who was going to create staging tables for a BI Publisher Report. There is no need to create staging tables anymore, your just creating more work for yourself and possible performance issues.

Hi All,I am creating a report, which first calls a procedure to populate the custom table and then it uses the custom table to create the report.My Question to you all is, is there a way to call a procedure
first and then call the table from BIP.Thanks,Madhavan G V

Here's my response:

whoa, whoa,whoa.

You should use a pipelined function. Then you can cast a pl/sql table as and actual table. It will save you a read and write to disk, plus the table objects, and there is no need to worry about commits, rollbacks and clean up. This is truly a godsend for reporting, however, because of the anonymity of pipelined functions nobody ever uses it, which is such a shame as it is one of the most powerful tools in pl/sql (it's not just for ETL). Below is some sample
code. What you can see is the ability to join on the table. Basically you could do counts, max, min, etc all in one shot versus creating a function, plus, it can be reused...
good luck,

Ike

Here's an example:

==================================
==Sample Query
==================================
SELECT
GMD_SPEC_TESTS.SPEC_ID
,GMD_SPEC_TESTS.TEST_ID
,GMD_SPEC_TESTS.TEST_METHOD_ID
FROM
GMD_QC_TESTS
,GMD_SPEC_TESTS
,table(vca_rm_spec_pkg.get_valid_tests_table(:p_report_type, GMD_QC_TESTS.TEST_CODE, GMD_QC_TESTS.TEST_DESC)) test_tab
WHERE
GMD_QC_TESTS.TEST_ID = GMD_SPEC_TESTS.TEST_ID
AND NVL(GMD_QC_TESTS.DELETE_MARK, 0 ) != 1
and GMD_SPEC_TESTS.SPEC_ID=:HEADER_SPEC_ID
and test_tab.test_code = GMD_QC_TESTS.TEST_CODE
and GMD_QC_TESTS.TEST_CODE <> 'QAPRV'
ORDER BY TEST_CODE

==================================
==package body
==================================
function get_valid_tests_table (p_report_type in varchar2, p_test_code in varchar2 , p_test_desc in varchar2)
RETURN test_tbl_undef PIPELINED
is
l_test_rec vca_rm_spec_pkg.test_rec;
begin
G_PROC_NAME :='get_valid_tests_table';
if p_report_type = 'SEARCH' then
if g_test_tbl.count <> 0 then
for i in g_test_tbl.first..g_test_tbl.last loop
l_test_rec := g_test_tbl(i);
pipe row(l_test_rec);
end loop;
end if;
else
--this is the summary, what comes goes out.
l_test_rec.test_code := p_test_code;
l_test_rec.test_desc := p_test_desc;
pipe row(l_test_rec);
end if;
return;
end get_valid_tests_table;

procedure set_valid_tests_table (p_test_code in varchar2, p_test_desc in varchar2)
is
begin
G_PROC_NAME :='set_valid_tests_table';
if g_test_tbl.count is null or g_test_tbl.count = 0 then
g_test_tbl(1).test_code := p_test_code;
g_test_tbl(1).test_desc := p_test_desc;
else
g_test_tbl(g_test_tbl.count+1).test_code := p_test_code;
g_test_tbl(g_test_tbl.count).test_desc := p_test_desc;
end if;
return;
end set_valid_tests_table;

==============================
==package spec
==============================
type test_rec is record (test_code varchar2(30),
test_desc varchar2(150)
);
type test_tbl_undef is table of test_rec;
type test_tbl is table of test_rec INDEX BY BINARY_INTEGER;
g_test_tbl test_tbl;

--returns a pl/sql table pipelined, query casts as table
function get_valid_tests_table (p_report_type in varchar2, p_test_code in varchar2, p_test_desc in varchar2)
RETURN test_tbl_undef PIPELINED;

--returns a pl/sql table pipelined, query casts as table
procedure set_valid_tests_table (p_test_code in varchar2, p_test_desc in varchar2); --return varchar2;

BI Publisher: Converting Report6i reports - Pitfalls and Suggestions

2007-09-27T09:24:00.000-07:00

Yes there are pitfalls! I have seen some questions in the bi publisher forum about converting all of a companies "existing report6i code" to BI Publisher. Don’t get me wrong the tool is a great, but like any tool it has some issues.

Here are some common elements as far as time is concerned for report development in general. These estimates are based my experience writing reports in the 11i e-business suite.

New Reports – From dev -->test -->production typically development time is about 40 hours.
Fixing an Existing report – If you’re lucky, 2 minutes but on average 5 about hours.
Converting/Copying an existing report 10-15 hours
Converting reports6i to BI Publisher
Small report 10 hours
Large report 20-40 hours, it can get ugly!

You may be asking yourself, what in the duce? How is that possible that converting a large report could take upto 40 hours? There are some bugs in the conversion tool. As example:

Reports6i:
Function get_item_no return char
Is
l_item_no varchar2(30);
Begin
Select item_no
Into l_item_no
From ic_item_mst
Where item_id = :item_id;

Return l_item_no;
End;

Conversion to PL/SQL:
Function get_item_no (item_id in number) return char
Is
l_item_no varchar2(30);
Begin
Select item_no
Into l_item_no
From ic_item_mst
Where item_id = item_id;

Return l_item_no;
End;

Data Template Issues
In the converted PL/SQL example you can see the problem in grey. Sometimes during the conversion the parameters end up having the same name to a joining column. Ouch, get_item_no will actually compile too (note: it really shouldn’t in "theory" since item_id is “technically” ambiguous). Now were left with a situation where that function will blow up 100% no matter where it’s called from. Some reports can have over 50+ functions. When this issue occurs you have to fix each query and narrow it down in the data template.

Format Template Issues
Another kind of nasty issue is caused by sloppy development in reports6i with srw.set_field(….). This "was" a way of changing the data on a field in the report format trigger. It’s a big no, no. Well, guess what, set_field never gets converted to the format template so we have to fix the issue manually. Something like this is really easy to slip through the cracks. Also, did I mention that all the logic in your format triggers to suppress fields or groups, change colors, fonts, etc doesn’t get converted as well? Now that will take some dev time!

Conclusion
The one thing to come away from this article is this: Don’t fix things that aren’t broken (it’s easy to say, hard to practice). It’s a lot of work to convert a large report and troubleshooting the conversion issues will cost you time and money. Also, expect to feel a majority of the pain in the format template conversion. Formatting is the number one challenge for any report!

When the conversion tool becomes a more mature product we can expect the conversion to be better and it “might” make sense to convert more of your organizations reports. The only justification for this would be either lower cost of support or a disappearing reports6i skill set in your organization/development community. With that in mind, if you don’t have a choice and you have to convert a report, the tool does a pretty good job and it’s better than nothing.

PL/SQL: Taking it to the next level.

2007-09-25T17:42:00.000-07:00

Disclaimer: The PL/SQL Examples presented here are theory and will not compile. However there are plenty of resources from oracle on this subject.

Ever wonder how you could query a pl/sql table? Well there is a way to do this now, since 9i. You must define a function to be “pipelined.” This is one of the PL/SQL’s more powerful abilities, ironically, it’s rarely used, it’s relatively unknown in the PL/SQL Development community. If you talked to your top PL/SQL guru and said, "Hey man these pipelined functions could be really awesome for reporting." The response from your guru would be this, "How dare you feel my head with such vile and lonesome propaganda!"

Essentially, for report writers this opens up some doors that we haven’t ever thought about or wish we had when writing some really challenging reports. This really comes in handy when your working with tough data models. With pipelined functions there is no need for staging tables, long concatenated lexical’s or multiple functions.

While a function will only return one value, there is an exception to this rule which is a pipelined function which can returns n-values. The pseudo syntax for defining a function of that nature is below.

function get_valid_employee_table (p_dept in varchar2) RETURN emp_tbl_type PIPELINED
is
l_emp_rec emp_pkg.emp_rec;
begin
loop through some pl/sql table that you pre-populated in your before report trigger or the calling program
l_emp_rec := some_pl_sql_table;
if ..
--do something
pipe row(l_emp_rec);
elsif ..
--do something
pipe row(l_emp_rec);
else
--do something
pipe row(l_emp_rec);
end if;
end loop;
return;
end get_valid_employee_table;

Sample Call:

select *
from TABLE(emp_pkg.get_valid_employee_table(‘finance’) managers
where managers.employee_id in (select employee_id from employees)

The inside skinny: When, Where and Why

When:
1. You want to avoid a huge concatenated lexical variable in your where clause that will cause performance issues.

2. There is a need for staging table. Typically, with staging tables your already be storing the data in memory so why make the report transaction based. Note: Staging tables in reports are caused by a bad data model or unruly requirements….it can happen :-)

Where:
1. Code this in your pl/sql package.
2. Use it either for reporting (see when) or an ETL Process.

Why:
1. There is no need to create half a dozen functions, you can create a record and call one function and cast it as a table. Boom, you saved yourself some redundancy in your query and in PL/SQL
2. Writes to disk are eliminated, performance plus
3. No need to manage transactions in a report (ie commit/rollbacks into staging tables). Less complex report…
4. Complex calculations can be preformed in SQL natively
a. ad-hoc counts
b. group by
c. joins/outer joins to sql tables or even other parallel enable functions.
5. Technical this can be viewed as polymorphism. Since its code that is logical being represented as a table or view (yes can you put in a view) it can be queried n- possible ways with your limitation only being that of SQL and or PL/SQL. Think about it...

Send me a shout! Is there a mistake or would you like to comment about this article?

About Me

2007-01-25T19:29:00.000-08:00

This blog is a treasure trove of BI Publisher and all it's capabilities! Unique solutions and idea are presented in this blog about Format Templates, Data Templates, Bursting, BI Publisher Java API's, etc that you won't anywhere else but here.

About Me

Ike Wiggins has over 5+ years development experience with BI Publisher (since it's inception). I have developed over 40 BI Publisher reports and have extensive experience with the BI Publisher Java API's.

The reports that I developed have run in an International Environment (multilingual documents), North American environment, Oracle Portal and the Oracle Applications Framework (OAF).

I'm also the inventor and developer of the BIPublisherIDE, BIPublisherIDE OAF edition, and soon to come the BI Publisher ToolBox. These are integrated development tools for BI Publisher Developers. The tools and support are free and the source code is always available for custom development.

Downloads

2005-06-26T10:01:00.000-07:00

*New 04-22-2010
BI Publisher Toolbox 4.5
BI Publisher Toolbox - By clicking on the download link you will be redirected to a different site. The standalone client is no longer supported and the toolbox is now a java webstart application. Webstart applications allow for automatic updates and real-time fixes for developers. Don’t worry though just because it’s a webstart application doesn’t mean the performance will be lack luster; the jar files are automatically loaded and saved on your PC. It's recommended to bookmark this page and the download site.

*New 01-10-2010
*Desupported 04.22.2010 - please use the BI Publisher Toolbox.
Delivery Designer 1.1
Delivery Designer 1.1 - By clicking on the download link you will be redirected to a different site. The Delivery Designer 1.1 is java webstart application. Java 6 is required to run this application.

Bursting Designer Links
Video Tutorials - Quicktime Required
Bursting Designer Features
Bursting Designer Lesson 1 - What is Bursting
Bursting Designer Lesson 2 - Creating a Burst Control File

*New 01-10-2010
*Desupported 04.22.2010 - please use the BI Publisher Toolbox.
Bursting Designer Download Version 3.5 - XDO Protocol Supported
Bursting Designer 3.5 - By clicking on the download link you will be redirected to a different site. The bursting designer 3.5 is java webstart application. Java 6 is required to run this application.

Bursting Designer Download Version 1.2
Bursting Designer 1.2 - By clicking on the download link you will be redirected to a different site. The bursting designer 1.0 is java webstart application. Java 6 is required to run this application.

*Desupported 04.22.2010 - please use the BI Publisher Toolbox.
BIP Conversion Utilities
Video & Code (click on images)

BI Publisher Integrated Developer Environment
Downloads
BIPublisherIDE_OAF
BIPublisherIDE

Manuals
BIPublisherIDE Manual
BIPublisherIDE Troubleshooting
BIPublisherIDE Blog