Oracle BI Publisher Blog

A few weeks back I wrote about the barcode encoding solution thats now available for use with XML Publisher for advanced barcode encoding calculations prior to applying the bar code font to the output. I found some time to take a look at some of the 2D barcodes and their requirements for encoding and rendering. Once you have the encoding class set up in your environment its pretty simple to add the 2D barcode encoding code.

XML Publisher does not yet license fonts for ... they're coming, although no 2D barcodes. So I have been working with the PDF417 and Data Matrix fonts available from IDAutomation, along with the fonts they also provide neat java classes to do all the encoding you need. Its just a case of making their classes available in your environment and calling them in your encoding class. Then with a neat trick in your RTF template et voila you can have funky 2D barcodes in your output.

Step 1: Get the barcodes and encoding classes

So you'll need to license the fonts from the vendor, in this example I worked with the sample fonts available from the IDAutomation site.
PDF417 Font - http://www.advancemeants.com/downloads/IDAutomation_PDF417FontEncoderDEMO.zip - you'll get a zip file with the font and java class plus a bunch of other useful integration tools. All you need is the PDF417Encoder.class and the IDAutomationPDF417nX.ttf font from the AdditionalFonts.zip where 'X' is the X to Y ratio of the font shape, 3 is the standard.

This is explained better on the web site, http://www.idautomation.com/fonts/pdf417/faq.html#Specifications

Step 2: Update your XMLP encoding class

Now you need to add the encoding code to your encoding class, take a look at our entry from a while back on Advanced Barcode Support.
In the encoder class we created we import the font vendors encoding class and add an entry for the pdf417 font
      import IDautomationPDFE.PDF417Encoder;
      ...
      ...
      ENCODERS.put("pdf417",mUtility.getClass().getMethod("pdf417", clazz));

and create a pdf417 method that just calls the class provided by the font vendor:

  public static final String pdf417( String DataToEncode )
  {
       PDF417Encoder pdfe = new PDF417Encoder();
       return ( pdfe.fontEncode(DataToEncode) );
  }

Compile and deploy to the server as normal.

Step 3: Use the font in your template

Install your font into the <<WINDOWS_HOME>>/fonts directory and fire up MSWord. Create your template and for the data that you wish to use the barcode for:
(a) Use a formfield or raw text
    <?format-barcode:ITEM_DESCRIPTION ;'PDF417';'XMLPBarVendor'?>
where ITEM_DESCRIPTION is the data to be encoded
           PDF417 is the name of the encoding method to be used
           XMLPBarVendor is the name of our encoder
(b) Mark the formfield or text with the PDF417 font
Now save your template and deploy.
Now when you run the template the data will be encoded and the font applied.
I have uploaded a sample encoding class and template to help you out.

The same process can be applied to the Datamatrix barcode to generate that output.  We had a customer test the result of the PDF417 and Datamatrix barcodes and their 2D barcode hardware read the resulting barcodes perfectly.

Finally, an EBS posting!

For those of you using XMLP in your OA Framework pages you may have noticed (as did one of our EBS developers) that the VO method writeXML is great but the dates are pushed out in a non XMLP format i.e. 2006-06-05 07:01:12.0. XMLP needs to have the XSD date format,'YYYY-MM-DDTHH:MM:SS' to allow you to format the date correctly in the layout template.

To get this format Steve Meunch came to the rescue, thanks Steve.

Customizing the getter method of the view object's row class to return a Date with a custom implementation of the
getXMLContentNode() method, using whatever format you like for the date value. Will get you the correct date format.

For example, if you created an EmpView having Empno, Ename, and Hiredate attributes, your EmpViewRowImpl.java class could overide the getter method for the Hiredate attribute like this:

Change:

 public Date getHiredate() {
   return (Date)getAttributeInternal(HIREDATE);
}

To this:

 public Date getHiredate() {
   return new Date((Date)getAttributeInternal(HIREDATE)) {
     public Node getXMLContentNode(Document xmlDoc) {
       SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
       return xmlDoc.createTextNode(sdf.format(timestampValue()));
     }    
   };
}

Doing so would coax writeXML() to change its output from:

<EmpView>
  <EmpViewRow>
     <Empno>7369</Empno>
     <Ename>SMITH2</Ename>
     <Hiredate>2006-07-21 10:50:32.0</Hiredate>
  </EmpViewRow>
</EmpView>

To this:

<EmpView>
  <EmpViewRow>
     <Empno>7369</Empno>
     <Ename>SMITH2</Ename>
     <Hiredate>2006-07-21T10:50:32</Hiredate>
  </EmpViewRow>
</EmpView>

As many of you hopefully know we can secure your PDF outputs from publisher. There was a question on the forum asking how it can be achieved - there are, of course, various ways.

The levels of security supported covered in the following graphic - for more details check the Adobe Reader/Acrobat help files and our of course.

You can see two password fields, the first prevents the document being opened the second prevents someone with the first password and Adobe Professional from changing the document. The changes are controlled by the following attributes in the graphic. You have complete control over the PDF outputs, but how do you achieve it?

E Business Suite Template Manager

In the template manager UI you'll see the configuration tab - this has the options above. They can be set at  template, data definition and site levels. The options will be laid out as above - if you want to secure a PDF output remember that you will need to set the 'Enable PDF Security' value to 'true.'

PeopleSoft and JD Edwards

For these apps you'll need to use the xdo.cfg file. This is an XML file that defines the security options. Check the user guide for details, the file will have the structure:

<config version="1.0.0"  xmlns="http://xmlns.oracle.com/oxp/config/">
    <!-- Properties -->
    <properties>
        <!-- System level properties -->
        <property name="system-temp-dir">c:/Temp</property>

        <!-- PDF security -->
        <property name="pdf-security">true</property>
        <property name="pdf-open-password">welcome</property>
        <property name="pdf-permissions-password">welcomeagain</property>
        <property name="pdf-no-printing">true</property>
        <property name="pdf-no-changing-the-document">true</property>
    </properties>
</config>

As you can see the passwords are going to be clear text - not great but you could argue that almost everyone will not have access to the config file. This file needs to sit under the JRE_TOP/lib directory on your web tier for publisher to find it.

BI Publisher Enterprise

The standalone release has very similar UI to the E Business Suite implementation - the security attributes are available at site and report levels.

RTF Template Method

There is another way to set these attributes via RTF template properties. In MSWord you can set custom properties for the document and we can use those to set the security attributes. Just define new attributes and prefix the property - take the property name and prefix it with 'xdo-' e.g. xdo-pdf-security

From the graphic you can see the custom properties Name and Values defined.

If you look at the  graphic the second property sets the password for the document:

Name: xdo-pdf-open-password
Value: {PASSWORD}

PASSWORD is actually an element value in the XML that can be retrieved at runtime and set as the document password. Again, clear text password in the XML data but you could have the XML swept away as soon as its been used.

Java API Method

Finally, if you are using the API to generate outputs you can set the security via an API call. Just about all of the document processing APIs have a setConfig method. This can be used to set the document security:

    // Set the security property
    Properties prop = new Properties();
    prop.put("pdf-security","true");
    prop.put("pdf-open-password","welcome");
    foProcessor.setConfig(prop);

In this case we have created a Properties object, loaded our security properties into it and then passed it to the processor API.

So, you can secure your PDF output - there are just multiple ways to do it.

I have recently seen a spate of requests for new functions to be built into BIP - we have enhancements coming out of our ears. For those of you that can not wait and have a little java experience or even have access to a java resource read on.

Before I get into the meat of this entry - Oracle will not support you if you choose to use the following methods described. At least I dont think they will - please dont log a TAR if you get stuck :o)

As many of you know, that RTF template you develop is converted to an XSLFO stylesheet and the Oracle XSL transformation engine is then used to apply said template to XML data - so at the heart of the whole engine is an XSL transformer. The Oracle implementation provides a framework that allows you to extend the XSLT engine to create your own functions. We even make use of it in our xdoxslt: functions. I have mounted a cut down version of the XDK dev guide here - it just contains the java section. Useful to understand whats going on in the XSLT engine - check out the 'Programming with Oracle XSLT Extensions' section.

So how do I do it?
From a high level, you create a java class with a method that will process the incoming data and return a result to the transformation engine to be rendered. To then use the new function, your extensions need to be declared in the template as a namespace and the new function name called using the namespace prefix. For example 

bipext:welcomeMsg('Tim')

where 'bipext' is the namespace and welcomeMsg is method to be called. Of course your java class needs to be in the classpath of the publisher engine to be picked up. 

Let's keep it simple to start with, I have created a very simple class that has only a single method:

package oracle.bip.extensions;
 
public class BIPExtensions {
    public BIPExtensions() {
    }
 
  public static final String welcomeMsg(String name)
           {
               return("Hello " + name+" !");
            }     
       }

The welcomeMsg method takes a string input value and returns a string, 'Hello <<name>> !' to be rendered in the output - yes, its very simple - not Hello World thou you have to admit :0)

Couple of other points to notice:

1. The package path is important - we'll use this in the namespace declaration.
   
2. The 'welcomeMsg' method is 'static' - the Oracle XDK can handle both static and non-static methods - they explain further in their documentation here.

We now have our java class and method, it needs to be placed into the classpath for the engine - I'll come back to this, so lets assume its done.

Now we need to declare that we want to use our new extensions in our template. We just declare these as a namespace:

<?namespace:bipext=http://www.oracle.com/XSL/Transform/java/oracle.bip.extensions.BIPExtensions?>

notice that 'bipext' is going to be the prefix we'll use, you can have your own. In the URI we must have :

http://www.oracle.com/XSL/Transform/java/

followed by the package path using period (.) separators and then the class name that holds the extension method(s).

Once, that is done we can call the function in the template.

<?bipext:welcomeMsg('Tim')?>

of course 'Tim' could be replaced with an element name - just so long as its a string or can be interpreted as such.
When the template is applied and the extension is called we get back

'Welcome Tim !'

and its inserted into our output.

Being able to test your functions relies on your class being in that all important classpath - if you are using JDeveloper or similar its simple enough to get your class archive in - just ensure its above the xmlparserv2XXX.jar and xdoparser.jar in the list.  
Using Template Builder for MSWord is a little more tricky and requires what can only be described as 'a bit of hacking' - but with some care you can test them. Template Builder does not support setting a classpath or registering new java functions - yet. The only way around it is to get you class into the xdocore.jar in the /jlib directory under the plugin install. If you are up for it ...

1. Make a copy and back up the original xdocore.jar and rename it
   
2. Using winzip or similar open the jar up
   
3. Now you need to merge the extension class into the jar(zip) file - just make sure you preserve that directory path in the archive. In our case, 'oracle.bip.extensions.BIPExtensions.class'. In the visual zippers this is drag an drop but you may need to work out the command line syntax - sorry, Im lazy and use a UI.
   
4. Close the archive

Hacking done - you're now ready to test the function using the Template Builder. Now remember, you get into a mess, just back up to step 1 again - remember support do not want a ticket asking about how to unhack Template Builder and dont tell em Tim told you to do it :o)

Once you're happy with the template, deploy it and your class to the server and you're good to go.

Now, here we tackled the simple stuff - you, Im sure will get very creative - got common functions all your templates need and can not be satisfied in a sub template? build an extension - the possibilities are endless and I can hear the cogs whirring from here. If you come up with a hot extension and want to share it, send it in and I'll post it here.

Those of you that use the Shipping module under 11i and want to use publisher to format them, will maybe know that its not as simple as flipping the Oracle Reports to XML, creating and uploading a layout template for the reports in the set and running them. You'll get a nasty surprise - yes, you'll get XML data rather than shiny new PDF output.

Dont panic, the Shipping team have apatch out for you to solve this - 5688014 - 'Shipping Document Sets does not generate the report in PDF format even if report is defined with the Output Format as 'XML' and has a Template attached' - a long but very descriptive patch header. The patch is not going to let your users select a template for each document at runtime but you will not have to customize any reports or plsql - always a plus.

The problem with the document set submission and its not alone, I can think of a few other 'problematic' reports, is that they are submittedly programatically via plsql using the FND_REQUEST package. When you submit programmatically the concurrent manager will not automatically pick up that you are submitting an XML output report and look for a template to apply - you need to tell it prior to sending that FND_REQUEST.SUBMIT_REQUEST call.

If you have your own code and want the concurrent manager to apply the template you need to add the following command to your code prior to the submit command.

FND_REQUEST.ADD_LAYOUT(
 template_appl_name => 'XXX',
 template_code => 'XXXX',
 template_language => 'XX',
 template_territory => 'XX',
 output_format => 'XXX');

FND_REQUEST.SUBMIT_REQUEST(
'XXX',
'YYYY',
'SSSSSS',
NULL,
FALSE,
P_XXXX,
NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL);

A treat for me today, Brent Lowe from Oracle Partner STR Software sent me a blog entry to share. STR Software provide a really nice, tight delivery server integration for EBS customers. They have developed a user interface to allow you to deliver and monitor documents via the forms interface. If you are not wanting to get into the delivery manager APIs and need a 'delivery' solution that installs in a snap and works with Publisher check out STR's offereing.

Brent's post deals with a delivery channel I did not cover in last year's delivery series - that is the 'custom' channel. He uses the extensibility of the Publisher delivery manager to provide the tight integration that you see in the STR solution today. This was just what we were looking for from a third party when we decided to provide the custom channel option. If you are wondering how you could take advantage of the channel, read on ... if not read on anyway its a good article.

So with out further ado ...

Document Delivery From EBS : Part IV

<channel name="aventx">com.strsoftware.axtko.delivery.aventx.AventXDeliveryRequestFactory</channel>

DeliveryManager.addRequestFactory("aventx",
 "com.strsoftware.axtko.delivery.aventx.AventXDeliveryRequestFactory");

import com.strsoftware.axtko.delivery.aventx.AventXPropertyDefinitions;

         // Create an 'aventx' delivery channel

         DeliveryManager.addRequestFactory("aventx",
 "com.strsoftware.axtko.delivery.aventx.AventXDeliveryRequestFactory");
         DeliveryManager deliverymanager = new DeliveryManager();
 DeliveryRequest deliveryrequest = deliverymanager.createRequest("aventx");
         // Add specific properties about this document.
         deliveryrequest.addProperty(AventXPropertyDefinitions.
                          AVENTX_SUBJECT, "Purchase Order #" + sDocId);
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_TOCOMPANY, sToCompany);
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_TONAME, sToName); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_FROMNAME, sUserName);  
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_FROMEMAIL, sUserEmail); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_CONFIRMEMAIL1, sUserEmail); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_CONFIRMLEVEL1, "All"); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_CONFIRMEMAIL2, sBuyerEmail); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_CONFIRMLEVEL2, "Success"); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_CONFIRMEMAIL3, "purchasing@yourcompany.com"); 
        deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_CONFIRMLEVEL3, "Fatal");
 deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_ERPTRACKNO, sRequestId); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_ERPLOGIN, sUser); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_DOCID, sDocId); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.
AVENTX_ORGID, sOrgId); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.AVENTX_SECONDARYATTACHMENTFILE1,
 "\tmp\Instructions.doc"); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.AVENTX_SECONDARYATTACHMENTNAME1,
 "Instructions_For_Vendor.doc"); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.AVENTX_SECONDARYATTACHMENTFILE2,
 "\tmp\T&C.doc");
         deliveryrequest.addProperty(AventXPropertyDefinitions.AVENTX_SECONDARYATTACHMENTNAME2,
 "Terms_and_Conditions.doc"); 
         deliveryrequest.addProperty(AventXPropertyDefinitions.AVENTX_PRIMARYATTACHMENTNAME, 
 "Purchase_Order_#" + sDocId + ".pdf");       

         // Create the remarks for the coverpage.
          sRemarks =  "Dear Sir or Madam, \nPlease find the attached Purchase Order #" + sDocId + ".\n" ;
          sRemarks += "It has been a pleasure working with you!\n\n";
          sRemarks += "Sincerely,\n";
          sRemarks += "[SIG=RBL1]\n\n\n\n";
          sRemarks += "Brent Lowe";
 
 deliveryrequest.addProperty(AventXPropertyDefinitions.AVENTX_REMARKS, sRemarks);
          deliveryrequest.addProperty(AventXPropertyDefinitions.AVENTX_ACKFILE,
 "$FCHOME/tmp/grimlock.VIS1.^FAXID.%X.ACK");
          deliveryrequest.addProperty(AventXPropertyDefinitions.AVENTX_DESTINATION, sDestination);

         // Set the actual document to deliver.
          deliveryrequest.setDocument(sFileName);

         // Submit the document.
          deliveryrequest.submit();
          deliveryrequest.close();
          ...

   CLASSPATH=$FCHOME/axtko:$FCHOME/axtko/com/strsoftware/axtko/samples:$CLASSPATH
   export CLASSPATH
   java AventXDeliverySample $*
   RESULT=$?

   if [ $RESULT 墨e 0 ]; then
       echo 典here was an error while delivering the document.?
       exit $RESULT
   fi
   exit 0

A few weeks back I documented how to build extensions to publisher if you wanted some functionality that we did not support. Frank Menne from HSM consulting in Germany has jumped on it and built a sql extension. the extension allows you to go back to the database from the layout template layer. Its simple but it works, you are able to pass select staement values and fetch data back - if you are an Oracle Reports developer, think of Formula columns.

Now a word to wise, we do not condone doing this, its bad, so very very bad :0) - if you are going to make a trip back for every line of a report its going to hurt just like formula columns can hurt in an Oracle Report. But its a valid requirement in some cases; I have to admit I did not push Frank to see if he could have gotten the data up front, I was just pleased to see he was trying to build an extension. If you can get that data in your extract please do, its going to be worth the pain of redesigning your query(s) - your report is going to run that much faster.

So, please build extensions and if you are going to and it either, does not work as expected or you get performance problems - DO NOT LOG AN SR! Oracle Support, will quite rightly, not be interested - this really is a customization and you will be out of your support contract.

OK, warnings out of the way, lets share what Frank has been doing on his baaad trips to the database. He created the following class:

package oracle.bip.extensions;
 
import java.util.Hashtable;
import java.lang.reflect.Method;
import oracle.apps.xdo.template.rtf.util.XDOBarcodeEncoder;
import oracle.apps.xdo.common.log.Logger;
import oracle.apps.fnd.common.*;
import java.util.*;
import java.sql.*;
import oracle.jdbc.*;
import java.lang.reflect.Constructor;
 

public class sql {
   public sql() {}
   public static  String ConnectString="";
 
 public static final String SetConnectString (String name)
{
   ConnectString = name;
   return "";
}
 

 public static final String ExecSql (String s1, String s2, String s3 )
{
   String resultstring;
   String completesql=s1+s2+s3;
   Properties p = new Properties(System.getProperties());
    try
  {
  Connection aConn;
  if (ConnectString != "")
  {
     Class.forName("oracle.jdbc.driver.OracleDriver");
     aConn = DriverManager.getConnection(ConnectString);
  }
  else
  {AppsContext aCont = new AppsContext(System.getProperty("DBCFILE"));
   aConn  = aCont.getJDBCConnection();
  }
  OraclePreparedStatement ops;
  ops = (OraclePreparedStatement)aConn.prepareStatement(s1+s2+s3);
  ops.defineColumnType(1, 2);
  ResultSet rs = ops.executeQuery();
  rs.next();
  resultstring=rs.getString(1);
  }
  catch(Exception exception) { resultstring="exception"+exception.toString()+", SQL Command: "+completesql; }
 
  return(resultstring);
 
}
}

Pretty straightforward stuff but you can see the potential. Those of you wondering why Frank has a SetConnectString method which seems superfluous in the ExecSQL method - if you are running inside the template builder you can not, without a lot of effort, if at all grab an Apps session hence the method to set the db connection manually.

With the class in the classpath he could then register the extension in the template

<?namespace:bipext=http://www.oracle.com/XSL/Transform/java/oracle.bip.extensions.sql?>

 Remember the 'oracle.bip.extensions' is from the package in class and the 'sql' is the class. Then use it.

<?sql: ExecSql('select ', ' @PICK_SLIP_NUMBER ' ,'from dual ') ?>

There was an issue that Frank hit, he wanted to use the above SQL as a test but in its current format there was a problem, the '@PICK_SLIP_NUMBER' is not evaluated by the XSL engine, its just passed as a string to the extension so the extension assembles the select statement as:

select  @PICK_SLIP_NUMBER from dual

to get the value resolved we need

<?sql: ExecSql('select ', @PICK_SLIP_NUMBER  ,'from dual ') ?>

<?sql: ExecSql(concat(concat('select ?, ?&apos;?,@PICK_SLIP_NUMBER,?&apos;?, from dual ') ?>

its a little messy because we have to escape the single quotes as &apos;
But that way the quotes are inserted into the sql string and the attribute gets evaluated correctly. This also breaks the current extension but has an advantage in simplifying it, you only need a single parameter passed to the extension - the select statement is built in the template layer.

Another word to the wiser, if you are going to implement this - please think about the terrors of sql injection. Here, we are expecting a 'select' statment but you could easily abuse this and use inserts and updates. The extension is simple but needs some parsing to ensure its not being used for nefarious means.

So, there you have it, our first customer extension - if we get enough interest I'll post this and others to a section on the blog ... get extending people and share them!

I have to thank Dave Matlock on the forum for the following. I have not covered the web services we offer with BIP in 10.1.3.3.1 - its not going to be a 10 line java example ... sadly.

However, it appears that in PHP, it can be a 10 line code example, Im not greatly familiar with PHP so have not tried this out. If it really is this straightforward bring on PHP please.

I wanted to share this code with the community. I needed a way to integrate a PHP5 application with BIP Enterprise without sending the user to the BIP application. The answer was, of course, web services. Thankfully the BIP folks provided this interface. There is no error handling in the example below.

$wsdl = "http://hostname:8088/xmlpserver/services/ReportService?wsdl";
$client = new SoapClient($wsdl);

$params[] = array('name'=>'v_item', 'value'=>'NIW-TEST1', 'hasMultiValues'=>0);
$params[] = array('name'=>'v_item1', 'value'=>'NIW-TEST2', 'hasMultiValues'=>0);

$report = $client->getReportData('/Guest/Status/Status.xdo',
$params,
'uname', // 'Guest' can be used with a null pwd 
'pwd', 
'pdf',
'/Guest/Status/Status.rtf');

header('Content-type: application/pdf');
header('Pragma: no-cache');
header('Content-disposition: attachment; filename=Status.pdf');

echo $report; 

Thanks Dave, I need to find the time to get more familiar with PHP.

So static text is all very well for a document but lets say we need something more dynamic, maybe the text is in an XML element in the incoming XML data for the report or maybe we need some conditional logic to show a specific value based on some flag value in the incoming XML.

Let's deal with the simple situation first - pulling a value from the incoming XML data. This is pretty straightforward, use the same method to add the watermark in Word but rather than enter a fixed string, just reference the XML element that is holding the watermark text.

Just be careful here, you might need to provide the full path to the element or use a .// prefix to get the text to render correctly.

You can also assign an element value to an XSL variable, so if you want to concatenate a few strings in your XML or static test you can build up watermark text quite easily.

For the more complex case above where we need some logic, I needed to scratch my balding pate a little. Roc has posed a question on the forum wanting some conditional logic around the watermark.

Hi XMLP Gurus,

Is it possible to code watermark in XMLP by inserting a field and coding the condition in that field. Depending on the condition I can print the watermark text on the output. Is this achievable?

Thanks in advance...

I took a little while to get an answer - I was getting a little desperate looking to build some XSLFO template to handle te logic - I stumbled upon a very useful inline 'if' statement that was hidden away - yes, another hidden feature that even I did not know about. It takes the format:

xdoxslt:ifelse(boolean expression ,true result, false result)

in use we get something like

xdoxslt:ifelse(.//WM_FLAG='C?,'Canceled','Approved')

So if the WM_FLAG element contains a 'C' then the 'if' statement returns 'Canceled' otherwise 'Approved' - now you can actually nest the 'ifelse' statements so we can get some serious logic into the inline 'if'. In this case, for the watermark we used the following:

<xsl:variable name="wMark" select="xdoxslt:ifelse(.//WM_FLAG='C?,'Canceled','Approved')"/>

this assigns the result of the 'if' to a variable wMark - we then use

<?$wMark?>

as the value in the watermark text. This then gets resolved at runtime correctly based on the 'if' statement, neat huh?

I have to mention that we have an issue at the moment if you are using the @section command - we're looking into that.

Finally, there is another method to add a watermark - via a java API that we provide. Its a post document generation step and of course you need to have some means of calling the java API. Either a java concurrent program or maybe someting funky in the after report trigger if you are using OReports to generate the XML ... hmmm thats needs some thought.

Using the API is pretty straightforward, its actually documented here for all versions but I'll give you the basics.

Use the SetTextDefaultWatermark( ) method to set a text watermark with the following attributes:

? Text angle (in degrees): 55
? Color: light gray (0.9, 0.9, 0.9)
? Font: Helvetica
? Font Size: 100
? The start position is calculated based on the length of the text

Alternatively, use the SetTextWatermark( ) method to set each attribute separately. Use the SetTextWatermark() method as follows:

? SetTextWatermark ("Watermark Text", x, y) - declare the watermark text, and set the x and y coordinates of the start position. In the following example, the watermark text is "Draft" and the coordinates are 200f, 200f.

? setTextWatermarkAngle (n) - sets the angle of the watermark text. If this method is not called, 0 will be used.

? setTextWatermarkColor (R, G, B) - sets the RGB color. If this method is not called, light gray (0.9, 0.9, 0.9) will be used.

? setTextWatermarkFont ("font name", font size) - sets the font and size. If you do not call this method, Helvetica, 100 will be used.

The following example shows how to set these properties and then call the PDFDocMerger.

Input:

? PDF Documents (InputStream)

Output:

? PDF Document (OutputStream)

For example

import java.io.*;
import oracle.apps.xdo.common.pdf.util.PDFDocMerger;


...


public boolean mergeDocs(InputStream inputStreams, OutputStream outputStream)


{
  try
  {
    // Initialize PDFDocMerger
        PDFDocMerger docMerger = new PDFDocMerger(inputStreams, outputStream);
    // You can use setTextDefaultWatermark() without these detailed setting
        docMerger.setTextWatermark("DRAFT", 200f, 200f); //set text and place
        docMerger.setTextWatermarkAngle(80); //set angle 
        docMerger.setTextWatermarkColor(1.0f, 0.3f, 0.5f); // set RGB Color
    // Merge PDF Documents and generates new PDF Document
        docMerger.mergePDFDocs();
        docMerger = null;
        return true;
      }
        catch(Exception exc)
      {
        exc.printStackTrace();
        return false;
       }
     }

Not too tough, right!

Well I made it back to the wintery wastes of Colorado on Friday getting home at some ungodly hour of the night after fun and games getting my poor old Ford started after a week shivering in an open parking lot. Im being disingenuous to Colorado - I still love it. You can actually get up to 70 miles per hour on the freeways here and find parking in Denver - from the airport to the office took nearly half an hour last week - the great 101 and California traffic. I spent yesterday with the kids, sliding down mountains in 'white out' snow conditions, carving through light and fluffy powder  - it's good to be back!

I got to sit on lots of desks last week talking to team members about what they are up to for coming releases - 11g is on the horizon but there is at least one release before that, 10.1.3.4 and its packed with new features, as we get closer to the release I'll work them in more detail.

• Server Side Preferences
  
• Document cache Enhancements
  
• Cascading parameters
  
• Complex type support in Web Services
  
• Workspace Integration
  
• Smartspace Integration
  
• Web Services
  
• Enh for Scheduling Web Services
  
• Scheduler HTTP / WebService Notification
  
• OLAP Data Source
  
• Template Builder Enhancements
  
• SQR Integration
  
• Adding Context Sensitive Help to BI Publisher Server UI

Bear in mind, they are all not set in stone and in good Oracle tradition, I dont have a definite release. There are some exciting features coming, from an integration point of view, the Scheduler web services and the HTTP / WebService Notification stick out.
The former is going to allow you to interact with the scheduling engine and provide complete control over it with abilities to stop, start and modify schedules, you'll also have access to historical report outputs and snap shots of data.
The rather crptically named 'HTTP / WebService Notification' is the ability to call what is an 'after report' trigger. You can now hook up post processes to a BIP report using this feature. Maybe you want to take the report output from BIP and put it into a BPEL approval flow prior to delivery with an ability to stripe DRAFT through the document until its finally approved. Maybe you want to update all the records that have been reported in the output. The trigger and the web services are going to give you that ability and really open up the BIP server to minute control.

Before we get that far, I want to spend some time getting through the existing web services and showing you what you can do with them ... 

Im up in Denver, spending the day with the JDE folks so its a quick post today. There are a couple of interesting documents just posted to the BIP home page that you might find useful.

The first is a guide on how to migrate from Crystal to BIP, authored by Pradeep and Mike from the PM team - Converting Crystal Reports to BI Publisher Its a manual process but at least shows that it can be done with a little effort. The steps are laid out very nicely and should be easy enough to follow for those of you migrating from Crystal. A more automated approach is under discussion.

The second is Integrating BI Publisher with Oracle Forms - I have mentioned this paper before by Jurgen and Rainer in our German office but the paper has been updated since. Its a good intro the web services we support and then how those can be called from Oracle Forms. Well worth checking out - if you're looking for Forms integration of course.

We had a customer issue a few weeks back ... 'really Tim, I never would have guessed' :0) Yeah, we get some issues sometimes. This was a JD Edwards customer that had developed an encoding class for a barcode. Lost? Check out the Advanced Barcode Support article. Basically, some barcodes need the data encoded, calculating stop bits and such like, prior to applying the barcode font. To support this, we allow you to create a java class that implements a Publisher interface to encode the data values. This can then be called from the RTF template layer using commands - check the user guide people!

The encoding class needs to be in the classpath on the server be it JDE, EBS or standalone - this is straightforward enough but how about being able to test on the Template Builder for Word interface?

It can be done, no support directly in the tool but with a little jiggery pokery we can get the class tested. The list of jar files that the builder will load at runtime is limited but the main one we can highjack is the xdocore.jar. You'll find it under the jlib directory under the Template Builder install directory.

You can open the jar file using something like WinZip, with it you can see the structure of the contents,

The class you developed will no doubt have a package declaration in it e.g. oracle.apps.xx.xmlp.barcode - this needs to map to the path in the jar file. To get it into the jar in the right place just get the class into the directory path on your local drive.

Then, in the case of Winzip, open the jar, then drag and drop the class and ensure the 'Use Full Path Info' option is set to get the class into the correct path that will map to your package specification.

Now, you can test to your hearts content in the comfort and relative speed of MSWord.

A big plug today for one of our active community members and his blog - Ike from the land of a 1000 lakes and 'food ona stick', Minnesota. The 'food ona stick' is uncorroborated from a lady I met in London who came from MN claiming that 'food ona stick' was the state dish. We did not get into the nature of the food but it was not just the obvious hot dog or candy floss. Anything that will stay on a stick long enough for you to bite it off was fair game. Kinda reminds me of CMOT Dibbler of Discworld fame, not going to tell you what he sold on a stick and Im sure its not on offer at the MN state fair!

Getting back to Ike, he writes the 'BI Publisher Blog' - note we distinguish this blog with the mandatory 'Oracle' prefix. You'll find him hanging out on the BIP forums answering question. His latest post provides information about an IDE he has come up with for developers that want/need to use the BIP APIs. Its actually a very neat packaging of the BIP jar libraries with bells and whistles that allows you to test your BIP objects without ever having to touch EBS,its UI or its concurrent managers, all in the comfort of the JDev IDE.
In his own words:

The BI Publisher IDE is a tool that was developed that allows developers to write and run reports locally from there PC without having to login into 11i or Oracle Portal.
The tool increases the speed of development because a developer can now connect to any environment (dev, test, production) or any system wercs, optiva, etc. This functionality also comes with the added benefit of not having to create or key any config, copy files, wait for concurrent manager requests or copying files/deploying them in oracle portal. The tool also fills some gaps of other reporting tools such as reports6i/9i or disco (discoverer). For instance, you can do batch testing for a single report or whole report set. Ad-hoc pl/sql is an option as well, which allows a developer to setup an environment, stage data, etc. The ability to do pl/sql on the fly is a major enhancement for any reporting tool, no homegrown or oracle reporting tool can do this. This allows a developer to not have to worry about hard coding in their report, because everything resides in the config and not the code.

Very neat - if you are just getting to grips with BIP/XMLP coding or want to speed your development times up, check out the post and get it installed.

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

Now where did I leave my fried egg ... ona stick of course!

Check out Tyler's post on using BIP web services to integrated BIP and Application Express: Call BI Publisher Web Services from APEX

We have a little known API that is really very powerful - the fact that its little known is my fault. The documentation is light to say the least and I promised Leslie that I would have a white paper out a long time ago to walk folks through some examples. Its not a paper yet cos I can be lazee and irreverent here on the blog and get information out much more quickly. It will be a paper eventually I promise, mybe even get into the documentation so you are not trawling all over the place for information.

So, what is it? How many of you are involved in pulling together multiple documents or maybe coming up with a programmatic way of pulling them together? Maybe, quarter end financial numbers for a management briefing book that come from multiple systems. How about shipping documents? You have an invoice, delivery instructions, picking slip, customer letters - 'shipping documents' covers a multitude of potential documents depending on what your shipping - but again they may come from different systems or at least separate reports from the same system.

From the graphic you can see that Publisher can take disperate PDF outputs and merge them together into a single document. No great shakes on the surface, plenty of applications can merge reports. We have the simpler APIs to do simpler merging but the Bookbinder API takes things to a whole new level. 

Imagine being able to take those disperate documents and apply another template over the top of them during the merging process. We allow you to add common layout objects, page numbering (chapter and master), chapter start and end pages, table of contents, cross references, you can even split a big result document into a series of volumes for easier loading through the Adobe interface.

In  the next few articles I'll cover working from a simple merge all the way through to an all singing all dancing book binding spectacular! If you have made a start with the APIs and want to get stuck in to the examples you can get them here.

Trying to get back on track here ... too many demos to get working for various events. Back to bookbinding - hope fully the introduction I provided gave you an idea of what the bookbinding API is and what it can be used for. Before we get to the nitty gritty its worth knowing ehats going on under the covers.

We have our disperate PDFs to the left coming from various sources - the BookBinding APIs sits amid the Publisher engine. It takes a control file in the form of XML as a set of instructions on how to build the bound document - the control file will reference layout templates that can be overlaid atop the the final combined document.

The control file is the most important piece - there is no builder for this and it can get complicated but if we start with the basics and work our way up to the more complex you'll understand the structure.

For the examples I have taken a bunch of Oracle product 'brochures' that we can then bind. They reside in the 'oracle_books' directory in the zip file available here. Unpack the zip and check out the first example.

Example1 - Simple Merge plus front page

In this example we will merge the three Oracle overview documents and add a front page to the document. So we will have a document structure as follows:
1. Front Page
2. Database Overview
3. Application Server Overview
4. E Business Suite Overview

The XML control file for this is pretty straightforward:

<?xml version="1.0" encoding="utf-8"?>
<outline xmlns="http://xmlns.oracle.com/oxp/book/">
  <!-- 
   The 'title-page' element should point to a PDF title page inserted 
   at the beginning of the book. (OPTIONAL)
  -->
  <!-- So here is the title page reference, in this case it is going to use an RTF template -->
  <title-page type="rtf">templates/title_page.rtf</title-page>
  <!-- nex follow the three documents we want to combine -->
  <item>
    <content>../oracle_books/database_overview.pdf</content>
  </item>
  <item>
    <content>../oracle_books/appserver_overview.pdf</content>
  </item>
  <item>
    <content>../oracle_books/ebs_brochure.pdf</content>
  </item>
</outline>

I have provided an html page and annotated control file that hopefully provides you with enough information to work out what is going on. The first example takes a template, title_page.rtf from the templates directory and then prefixes it to the following three pdf documents. Cant get much simpler than this.

To run the example you'll find a cmd file that can be run directly in Windows other OS' you need to set environment variables accordingly . Its a straightforward java call to the command line interface of the bookbinder API.

set CLASSPATH=..libxdo.zip;..libxdoparser.jar;..libxmlparserv2.jar;..libi18nAPI_v3.jar;..libaolj.jar
set JAVA_CLASS=oracle.apps.xdo.template.pdf.book.PDFBookBinder
set JAVA_OPTS=-mx150m
set JAVA_CMD=%JAVA_HOME% -classpath %CLASSPATH% %JAVA_OPTS% %JAVA_CLASS%
echo %JAVA_CMD% -debug true -tmp ..tmp -xml Example1.xml -pdf Example1.pdf
%JAVA_CMD% -debug true -tmp ..tmp -xml Example1.xml -pdf Example1.pdf

The command line arguments take the form

-debug true|false -tmp <temporary directory location> -xml <control file location> -pdf <output file location>

Give it a try - you might have to do a little jiggery pokery with the cmd file to get it working. Notice I have JAVA_HOME set to pick up the java executable to run the API.

Enough for today, try out the first few examples and we'll pick up some of the more complex examples next time.

Got a good one today that had me a little stumped until I RTFM! To be more precise RTFJD - the JD is the javadoc. The data engine we have does not only consume data templates but you can also throw SQL at it and get XML in return. Neat! One of our customers has been struggling with this, they want to be able to pass parameters to the sql at runtime - we support that but they were having pain getting it to work. Our trusty support guys have been nagging me for some help and I finally got to it last night.

The data engine has a setSql method to allow you to pass the sql and you can then pass parameters as a Hashtable or ArrayList thus:

DataProcessor dataProcessor = new DataProcessor();
 dataProcessor.setConnection(jdbcConnection);
 //Set the query
 dataProcessor.setSql("select * from departments where department_id = :p_DeptNo");
 //Create hash to hold param val
 Hashtable parameters = new Hashtable();
  parameters.put("p_DeptNo","10");
 dataProcessor.setParameters(parameters);
 dataProcessor.setOutput("c:\\temp\\EmpDetails.xml");
 dataProcessor.processData();

Looks straight forward enough but it would not compile or run. I scratched my head for a while. I know we dont docunment the setSql method that well. I ended up in the javadoc and found my problem. I was using the java.util.Hashtable. Hey, I know it, I use it regularly so why not. However the data engine actually needs the Collections version of the Hashtable - com.sun.java.util.collections.Hashtable .. dang! A quick update on the import statements et voila, I have XML coming out based on parameter values. A good lesson for me to read all the doc and apologies to support and the customer for taking so danged long about it!

If you're interested here's the complete code for the method:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.sun.java.util.collections.Hashtable;
import oracle.apps.xdo.dataengine.DataProcessor;
import oracle.jdbc.driver.OracleDriver;



public class DTSql {
    public DTSql() {
    
        try {
         //get connection
        Connection jdbcConnection = getConnection();
         //Initilization//
         DataProcessor dataProcessor = new DataProcessor();
        dataProcessor.setConnection(jdbcConnection);
          //Set the SQL
          dataProcessor.setSql("select * from departments where department_id = :p_DeptNo");
          //Create hash for parameters
          Hashtable parameters = new Hashtable();
          parameters.put("p_DeptNo","10");
          dataProcessor.setParameters(parameters);
          dataProcessor.setOutput("c:\\temp\\EmpDetails.xml");
          dataProcessor.processData();
        } 
        catch (Exception e)
         {
         e.printStackTrace();
         }
  
    }
        public static Connection getConnection() throws SQLException {
            String username = "hr";
            String password = "hr";
            String thinConn = "jdbc:oracle:thin:@IPG.US.ORACLE.COM:1525:ora10g";
            DriverManager.registerDriver(new OracleDriver());
            Connection conn =
                DriverManager.getConnection(thinConn, username, password);
            conn.setAutoCommit(false);
            return conn;
        } 


    public static void main(String[] args) {
        DTSql dTSql = new DTSql();
    }
}

A mad few daze getting presentations and demos ready for internal meetings on BIP for Fusion Apps. Yep, it's coming together and its going to be good from a user and a developer point of view. I want to say its going to be 'sweeeeet', my son's favorite answer for all things good at the moment, but I won't.

Back to data engines, I said I would get into the more complex case that I stated at the end of the last entry.

How can we get away from this 'pull' model where a report is scheduled to pick up the data, what if we wanted to control the timing of the report, say those XML files were being dropped into a directory periodically and each time the new file was dropped in we wanted to get BIP to run the report. While we are at it, maybe we have retail branch sales data coming into a central server, all of those XML files have the same data structure. We do not want to define a report for each branch, we only want a single report definition that picks up the appropriate filename at runtime, processes it and sends an email to the branch manager with their results and a copy to corporate.

This is a real use case scenario I worked on with a customer recently andI believe they are well on their way to implementing it. Branches would periodically drop their data files into a central directory and need a report sent back tout suite. The first hurdle to over come is how to invoke Publisher when a new file hits the directory? This customer has a centralized scheduler called Control-M - fairly widely used I think. This product has the ability to act as a directory daemon looking for files as they are dropped into a directory and then invoking some other process - theres our hook to get BIP to execute a report. Its not that tough to create your own in Perl or similar language. Here's the architecture I came up with:

Whats going on?

The Control-M product is constantly polling a specific directory looking for new or updated files. These are XML data files from the branches, when a new file appears it invokes the shell script, this is passed the filename as a parameter. It in turn invokes the java web service client class that then calls the BIP server to run a given report. The web services we provide allow you to have tight control over the report, run it now?, run it later?, which template to use?, what output to generate?, where to deliver it? All of this information can be passed to the WS client code from the shell script or you could have the WS client class to parse the XML to find out this type of information e.g. the branch's email address.

On the BIP server we have a single report defined that uses an HTTP data source with the filename as a parameter. This calls a servlet that is looking over the directory and based on the incoming filename parameter. I guess you could just have a servlet acting as the daemon to recognise new/updated files and make a call back to BIP. In this case Control-M is orchestrating the whole process so the servlet is just returning the XML file to BIP for processing.

Thats it really, the only point of note is the return codes that get passed back to the WS class and thence to the script and ultimately back to the Control-M application. Fairly simple architecture, that allows another application to control the BIP server. If you have such a requirement, I'd love to hear from you!