Integrating Oracle BI Publisher with iProcess case data
Facts - Other software technologies
Monday, 05 July 2010 08:39

This article applies to Oracle BI Publisher version 10.1.3.4 and iProcess version 11.

A customer wanted to create letters using information from iProcess case data. They are working with iProcess already for over a decade. And therefore they have still processes storing all data inside the iProcess case data table. At the moment best practice is to store a reference to information in external databases in the iProcess case data.

Another problem was that they were still using the thick client for opening the work items of these legacy processes. They built scripts into the forms first writing fields to a temporary file on the client. These temporary files were then picked by other windows executables (aword) run from the client pc that combined them with a ms-word template into a letter. The advantage of this approach is that it is possible to modify the data in the form and to print the letter in just one single workitem. The disadvantage is that one can not print letters when the iProcess server is down.

Later it turned out that this disadvantage was one of the main reasons why the customer wanted to use the BI Publisher. Initially this was not clear to me so I assumed that the customer wanted a full replacement for the current document solution, with the same advantages and disadvantages. So I made a solution with the iProcess form writing to the Pack Data table using the script command triggerevent. I created the following datasource in the Oracle BI Publisher:

<dataTemplate name="caseData">
  <parameters>
    <parameter name="casenum" dataType="number" defaultValue="3000"/>
    <parameter name="procname" dataType="character" defaultValue="MYPROC"/>
    <parameter name="stepname" dataType="character" defaultValue="MYFORM"/>
  </parameters>
  <dataQuery>
    <sqlStatement name="field">     
SELECT pd.FIELD_NAME as NAME, pd.FIELD_VALUE as VALUE
FROM PACK_DATA pd, PROC_INDEX pi, OUTSTANDING_ADDR oa WHERE 
pd.CASENUM=:casenum 
AND pd.PROC_ID=pi.PROC_ID
AND oa.REQID=pd.REQID
AND oa.STEPNAME=:stepname
AND pi.proc_name=:procname
    </sqlStatement>
  </dataQuery>
</dataTemplate>

I also defined three parameters with names casenum, procname, and stepname in the Bi Publisher report. In the iProcess form I defined a button that fired a script. The script first issues a triggerevent to store the necessary fields in the PACK_DATA table and then the script opened a General Interface (GI) window using a winrun command. The GI window received the necessary parameters and called a java proxy webservice calling the BI Publisher webservice. The BI Publisher templates had to access each field with an xpath query:

<?field/VALUE[NAME='x']?>

The problem with this approach was that iProcess issued an error message when we closed the workitem:

Error, case data updated elsewhere since item opened.  Please open item and edit it again.

This was resolved by issueing the following command on the iProcess server:

$SWDIR/util/swadm set_attribute 1 ALL 1 IGNORE_PACK_CHANGED 1

Unfortunately there was another problem. Writing fields to the PACK_DATA table with the triggerevent script command made it impossible to release workitems. Instead Tibco recommended to set up an extra database table and replace the triggerevent script command with the command DbWriteFields.

But in this case a complementary solution is better than a replacement. In other words modifying the BI Publisher data source such that it read from the CASE_DATA table made it possible to print letters when iProcess was down.