Monday, December 1, 2008

BI Publisher: Data Templates - Simplified Error Handling

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 :-)

Monday, November 10, 2008

BI Publisher: Troublshooting the BIPublisherIDE

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.

Saturday, October 25, 2008

BI Publisher: 500 Plus Downloads

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

Wednesday, October 8, 2008

BI Publisher: xdodebug.cfg troubleshooting

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)

Tuesday, July 29, 2008

BI Publisher: Let Go My Logo

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

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

Sample Select:

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

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


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

<fo:instream-foreign-object content-type="image/jpg">

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

Monday, July 28, 2008

Bursting with Web Services

Background

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

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

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

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

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

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

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

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

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

Getting the report to BURST using the Web Service.

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

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

WRONG!!

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

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

The solution?

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

What's next?

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

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

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

Summary

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

Thursday, July 17, 2008

BI Publisher: Template Builder Versions

Version chaos!

The good thing about evolving software is that each version brings new functionality and improvements (bug-fixes). The dark side is that compatibility of versions should be watched closely. This also holds for BI Publisher, the Template builder version should be of the same version as the installation that resides on the server or in E-Business Suite.Therefore an overview of the Template Builder versions that I could get a hand on:
  • Oracle XML Publisher version 5.6.3 (for R12) - patch 5887917
  • Oracle XML Publisher version 5.6.3 (for 11i) - patch 5887917
  • Oracle XML Publisher version 5.6.2 - patch 5027437
  • Oracle XML Publisher version 5.6.1 - no new builder use patch 4561835
  • Oracle XML Publisher version 5.6.0 - no new builder use patch 4561835
  • Oracle XML Publisher version 5.5 - patch 4561835
  • Oracle XML Publisher version 5.0 - patch 4239263
  • Oracle XML Publisher version 4.5 - no template builder :-(
Whenever you start working with another BI Publisher version, you should ideally de-install the current version and install the most suitable Template Builder version.

Tuesday, July 15, 2008

BI Publisher: An alternative approach to translations

To XLIFF or not to...

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

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

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


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

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

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

Monday, June 2, 2008

BI Publisher: BIP in the OA Framework part II

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.

Free Image Hosting

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

Free Image Hosting

Have to make it sswa!


Free Image Hosting

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!

Free Image Hosting

Free Image Hosting

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

Free Image Hosting


BI Publisher: xdo.cfg & BIPublisherIDE

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!

Wednesday, May 7, 2008

BI Publisher: Totals got ya down

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.

Monday, March 17, 2008

BI Publisher: BIP in the OA Framework part I



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

Wednesday, March 12, 2008

BI Publisher: BIPublisherIDE

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)

Tuesday, January 22, 2008

BI Publisher: XDO Loader

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....

Tuesday, January 8, 2008

BI Publisher: Blog Tag

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...