Oracle BI Publisher Blog

Many of you will have seen the Administrator module on the Template manager in the E Business Suite ... the new properties UI is great and much easier to use but you can now also manage those troublesome MICR, Barcode and other fonts through the manager too.

Thanks to Kevin McDermott from Oracle Support for laying out the following very useful instructions:

XML Publisher 5.6 has a new tab: Administration. This replaces the xdo.cfg configuration file. Now fonts can be uploaded and stored in the database instead of stored on the file system.

Under the Administration tab are sub tabs: Configuration, Font Mappings and Font Files and Currencies.

To install a font requires only a few steps.

1. Log in as XML Publisher Administrator.

2. Navigate to Administration->Font Files->Create Font File.

3. Fields are Font Name and File.
       For Font Name choose any descriptive name.
       File will browse your PC to locate the font file.

4. Navigate to Font Mappings->Create Font Mapping Set.

5. Mapping name is the name you will give to a set of fonts.

6. Mapping code is the internal name you will give to this set.

7. Type: 'PDF Form' for PDF templates. 'FO to PDF' for all other template types.

8. Create Font Mapping (this allows you to add multiple fonts to a set).

9. Font Family is the exact same name you see in Word under Fonts.
If you don't use the same name the font will not be picked up at runtime.

10. Style and weight must also match how you use the font in windows.
Normal and Normal are good defaults.

12. Navigate to Configuration General-> FO Processing->Font Mapping Set.
Can also be done at data def and template level under Edit Configuration.
Hierarchy is Site-> Data Def -> Template.

13. Select your new mapping set.

14. Make sure the font is not referenced under File->Properties->Custom in the RTF template file.

15. Upload a template that uses your special font and test using preview.

Its coming ... its even bigger I hear rumors of Oracle taking over a block of Howard Street in San Francisco to "extend" the Moscone Center for the week :o)
This year there will be two XMLP events for EBS and PeopleSoft customers at the Extreme Weekend - lots of hands on stuff to dip your proverbial toes in the XMLP water. Places are limited so book early.
There are also over 20 sessions related to XMLP and its use throughout the all of Oracle including EBS, JDE, PeopleSoft and Enterprise. The sessions are not just from development, we have customer and a partner sessions lined up. Last year they were turning folks away from the doors so get there early too.
Of course the demo pod will be swamped all week but drop by, hang out, have a chat and get a demo or three!

The above sessions are of course subject to some change but you get the idea.

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>

So many questions on the forum about the delivery manager and its integration into the concurrent manager in EBS, so over the next few postings Im going to try and clear the air and show you some of the options you have to get documents delivered from the concurrent manager, forms or OAFramework. 

Introductions
We need an introduction to the delivery manager before we start. Its a rich set of java APIs that can be used to send documents via:

• Email - pretty obvious, supports multiple document attachments, embedded HTML, multiple recipients, etc
  
• Fax - not up to communicating with a multifax board for high volume but definitely adequate
  
• Print - again obvious, uses the IPP for communication to the printer, has tray handling, copies, duplex, orientation, etc
  
• WebDAV - allows you to post documents to a repository such as Oracle Files Online, other 3rd party management solutions
  
• HTTP/HTTPS - posting docs to a web server
  
• FTP/SFTP - posting docs to an FTP
  
• Custom - if you have your own channel or a third party delivery solution you (or they) can create a custom delivery channel interface and XMLP will direct documents down that channel.

It even has the AS2 protocol implemented for your EDI messages. The APIs can rely on a configuration file to define servers, printers, etc or you can hardcode the information directly in your API call. 

Clarifications
A very rich set of APIs that are being expanded with every release ... thats the good news. The bad news is that none of it is directly integrated into the concurrent manager, OA framework or forms ... if you want to use the functionality you are going to have to get into some java coding and customization.

OA Framework
As OAF is completely java anyway its not going to be a tough job to create some UI for folks to deliver docs.

Forms
As EBS11i uses Forms 6i, our options are a little limited to integrate the delivery APIs directly. We could create some plsql wrappers for the APIs ... thats been done by a couple of Apps teams already. Or we could just call a concurrent program from the form. 

Concurrent Manager
This is the most popular method of generating reports, lets just cover what is going on process wise in the concurrent manager today (11i.10.X) with an XMLP report.

1. User selects an XMLP report to execute and enters the parameter values they want, the layout template, output format and language then submits
   
2. Concurrent manager 'looks up' the concurrent program definition, finds the executable and calls it. This could be an XMLP Data Template, Oracle Report, pl/sql or any other extraction routine ... just so long as its getting XML data.
   
3. Once the program has completed the Output Post Processor(OPP) comes into play. This is the new concurrent manager that handles the XMLP requests. It is passed a handle to the data file and calls the XMLP APIs to process the data to then generate the output based on the users choice of template, format, etc. The output document is then generated and the XML data is preserved.
   
4. The user can then either view the document from the SRS forms and print locally or they might have asked that the document be printed directly to a printer. Hopefully the SysAdmin has set up PASTA to handle the document, pre-process it to a printer language and then pass it off to the printer.

Thats the current process in a nutshell. If we want to add delivery other than printing there are a couple of entry points for us to inject our own delivery options at:
Step 2: We can create a wrapper java concurrent program. This would be called by the concurrent manager instead of the current one. It would:

• Call the extraction conurrent program and wait for it to finish
  
• Call the XMLP formatting APIs to generate the output document i.e no OPP involvement
  
• Call the delivery APIs to deliver the document.

Carrying on from where we left off yesterday in ths series of articles on the XMLP delivery manager, today we'll take a look at the APIs themselves and how you can use ... we're not going to touch EBS yet, just get some basics around the APIs. Before we start, arm yourself with the following documents:

XML Publisher Users Guide incl Developers Guide
XML Publisher release 5.6.1 Core Components API (javadoc)

Overview
The basic flow to deliver documents is as follows:

1. Create DeliveryManager instance
   
2. Create DeliveryRequest instance by createRequest() method
   
3. Add request properties such as where to send to the DeliveryRequest. Most of properties require a string value. Take a look at the supported properties of each delivery channel for more detail.
   
4. Set your document to the DeliveryRequest
   
5. Call submit() to submit the delivery request.

One delivery request can handle one document and one destination. This is because it makes simple and easy to track down each delivery status and re-submit the request if it failed to deliver.

The DeliveryRequest allows you to set the documents in two ways, these are;

• Getting OutputStream from the DeliveryReqeust and writing the document to the OutputStream. You don't need to close the OutputStream, but you can just call submit() method right after you finish writing the document to the OutputStream.-->
  
• Setting InputStream of the document to the DeliverRequest. The DeliveryRequest will read the InputStream when you call submit() for the first time. The DeliveryRequest doesn't close the InputStream so you need to take care of closing it.
  
• Setting the file name of the document to the DeliveryRequest. The DeliveryRequest will pick up the document from the file system when you call submit().

Those are the basic steps you need to follow, how about in practice?

EMail
With all of the delivery channels its a case of setting the required properties and then calling the apprpriate API. 

create delivery manager instance
   DeliveryManager dm = new DeliveryManager();
   // create a delivery request
   DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_SMTP_EMAIL);
   // set email subject
   req.addProperty(DeliveryPropertyDefinitions.SMTP_SUBJECT, "Invoice");
   // set SMTP server host
   req.addProperty(
     DeliveryPropertyDefinitions.SMTP_HOST, "mysmtphost");
   // set the sender email address
   req.addProperty(DeliveryPropertyDefinitions.SMTP_FROM, "myname@mydomain.com");
   // set the destination email address
   req.addProperty(
     DeliveryPropertyDefinitions.SMTP_TO_RECIPIENTS, "user1@mydomain.com, user2@mydomain.com" );
   // set the content type of the email body
   req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_TYPE, "application/pdf");
   // set the document file name appeared in the email
   req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_FILENAME, "invoice.pdf");
   // set the document to deliver
   req.setDocument("/document/invoice.pdf");
    // submit the request
   req.submit();
   // close the request
   req.close();

Simple stuff right?

This was just a simple example, you have complete control over the email and its attachments, there are more examples in the developer guide and the javadocs for the APIs.

FAX
The Delivery API supports the delivery of documents to fax modems configured on CUPS using the Internet Printing Protocol. XMLP uses a CUPS instance on Linux or UNIX to act as the delivery server. Its straightforward to set up, check the developers guide for instructions.
You can configure the fax modems on CUPS with efax and FAX4CUPS. By the default setting, CUPS can fax the document in Postscript and PDF document formats.  We have not done much research on Fax over a Windows Server, there are commercial software packages that can use the IPP. Another alternative is to use one of the many Fax over Email solutions out there, we have several customers using it very sucessfully.

Sample FAXing code 

// create delivery manager instance 
DeliveryManager dm = new DeliveryManager();
// create a delivery request 
DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_IPP_FAX);
// set IPP fax host 
req.addProperty(DeliveryPropertyDefinitions.IPP_HOST, "myhost");
// set IPP fax port 
req.addProperty(DeliveryPropertyDefinitions.IPP_PORT, "631");
// set IPP fax name 
req.addProperty(DeliveryPropertyDefinitions.IPP_PRINTER_NAME, "/printers/myfax");
// set the document format 
req.addProperty(DeliveryPropertyDefinitions.IPP_DOCUMENT_FORMAT, "application/postscript");
// set the phone number to send 
req.addProperty(DeliveryPropertyDefinitions.IPP_PHONE_NUMBER, "9999999");
// set the document 
req.setDocument("/document/invoice.pdf");
// submit the request 
req.submit(); 
// close the request 
req.close();

WebDAV
This delivery channel can direct your documents to any repository that supports the WebDAV protocol, whether that be Oracle Files Online, XDB or a third party solution. The document can be pushed into a directory on the repository as an archive or into a personal folder for viewing later by the recipient.
Again, its a simple case of setting properties and then calling the APIs submit method:

   // create delivery manager instance
     DeliveryManager dm = new DeliveryManager();
     // create a delivery request
     DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_WEBDAV);
      // set document content type
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_CONTENT_TYPE, "application/pdf");
     // set the WebDAV server hostname
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_HOST, "mywebdavhost");
     // set the WebDAV server port number
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_PORT, "80");
     // set the target remote directory
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_REMOTE_DIRECTORY, "/content/");
     // set the remote filename
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_REMOTE_FILENAME, "xdotest.pdf");
     // set username and password to access WebDAV server
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_USERNAME, "xdo");
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_PASSWORD, "xdo");
     // set the document
     req.setDocument("/document/test.pdf");
      // submit the request
     req.submit();
     // close the request
     req.close();

FTP
Another protocol that is very widely used to move files to specific locations for a daemon to recognize and have picked up by another process.

     // create delivery manager instance
     DeliveryManager dm = new DeliveryManager();
     // create a delivery request
     DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_FTP);
 
     // set hostname of the FTP server
     req.addProperty(DeliveryPropertyDefinitions.FTP_HOST, "myftphost");
     // set port# of the FTP server
     req.addProperty(DeliveryPropertyDefinitions.FTP_PORT, "21");
     // set username and password to access WebDAV server
     req.addProperty(DeliveryPropertyDefinitions.FTP_USERNAME, "xdo");
     req.addProperty(DeliveryPropertyDefinitions.FTP_PASSWORD, "xdo");
     // set the remote directory that you want to send your document to
     req.addProperty(DeliveryPropertyDefinitions.FTP_REMOTE_DIRECTORY, "pub");
     // set the remote file name 
     req.addProperty(DeliveryPropertyDefinitions.FTP_REMOTE_FILENAME, "test.pdf");
     // set the document
     req.setDocument("/document/test.pdf");
 
     // submit the request
     req.submit();
     // close the request
     req.close();

HTTP
Finally, HTTP ... The Delivery API supports to deliver documents to the HTTP servers. The following sample is sending a document through HTTP POST method. Technically, you can send not only documents, but also anything you want. But the servers should be capable to accept your custom HTTP requests in advance, such as your custom Servlet or CGI program.

 // create delivery manager instance 
 DeliveryManager dm = new DeliveryManager();
 // create a delivery request
 DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_HTTP);
 // set request method
 req.addProperty(DeliveryPropertyDefinitions.HTTP_METHOD, DeliveryPropertyDefinitions.HTTP_METHOD_POST);
 // set document content type
 req.addProperty(DeliveryPropertyDefinitions.HTTP_CONTENT_TYPE, "application/pdf");
 // set the HTTP server hostname
 req.addProperty(DeliveryPropertyDefinitions.HTTP_HOST, "myhost");
 // set the HTTP server port number
 req.addProperty(DeliveryPropertyDefinitions.HTTP_PORT, "80");
 // set the target remote directory
 req.addProperty(DeliveryPropertyDefinitions.HTTP_REMOTE_DIRECTORY, "/servlet/");
 // set the remote filename (servlet class)
 req.addProperty(DeliveryPropertyDefinitions.HTTP_REMOTE_FILENAME, "uploadDocument");
 // set the document
 req.setDocument("/document/test.pdf");
 // submit the request 
 req.submit();
 // close the request
 req.close(); 

Apologies for the break, trying to wrap up on some template standards for the EBS division ...

We now have an idea of the Delivery Manager APIs and what they can do and the limitations within the EBS infrastructure for using them to deliver documents. Now we need to know how we can hook up the delivery APIs to the concurrent manager. As I have mentioned before the delivery manager is not integrated into the concurrent manager. We need to use another method to get the document generated by the concurrent manager to the delivery manager. The concurrent manager expects to direct a document to a printer, the setup of printers has some flexibility and we can take advantage of that, rather than have a printer at the end of the flow we can have a shell script that can then direct the output to another delivery stream. 

Setup XMLP as a virtual printer

Using the System Administrator responsibility we can create a new Printer Driver but rather than calling a print command such as 'lp' we can call our shell script.

The most important field is the 'Arguments' field, this is where we specify the script to call and pass parameters:

$XX_TOP/delivery/xmlpmailer.sh $PROFILES$.CONC_REQUEST_ID $PROFILES$.FILENAME + others

where
$XX_TOP/delivery/xmlpmailer.sh - is the location and name of the script we want to call
$PROFILES$.CONC_REQUEST_ID  - is the request id of the report we are sending
$PROFILES$.FILENAME - is the name of the file we want to deliver
others - we can pass other parameters, either from the PROFILES object or hardcode values here. There is no definitive list of what those PROFILES are in the Oracle documentation but using a little trial and error you can chekc what can and can not be passed. To find the other PROFILES that might be supported have the forms interface open Help > Diagnostics > Examine. Find PROFILES in the popup windows Block field. Heres what I have found is supported so far, not an exhaustive list:

$PROFILES$.CONC_REQUEST_ID
$PROFILES$.PRINTER
$PROFILES$.CONC_COPIES
$PROFILES$.TITLE
$PROFILES$.FILENAME
$PROFILES$.RESP_ID
$PROFILES$.USER_ID

With this lot we can derive quite alot of information and with a trip back to the database to we can get even more information.

To test all this theory you will need to enter the murky world of printers, printer types, styles and drivers ... I still sometimes struggle getting the right combination ... reading the manual, some trial and error and above all patience helps.
I created a very simple shell script to simply write the PROFILE values to a file:

echo $1 $2 $3 $4 $5 $6 $7 $8 $9 $10 > /apps/vis11510appl/xdo/11.5.0/delivery/1.log
exit 0

The exit command is important, without it the concurrent manager will leave your request id as still running. Before you can test the new driver you'll need to bounce the concurrent manager and again for any changes. The following script became my best friend very quickly while putting these articles together:

startmgr sysmgr="apps/pwd" mgrname="std" printer="hqseq1" mailto="jsmith"
 restart="N" logfile="mgrlog" sleep="90" pmon="5" quesiz="10"

So, we can call a script, but what do we want it do ?
Well by now you will hopefully have managed to get the printers on your network working with Apps and a great saying comes to mind here ... if it ain't broke don't fix it. If we leave printing then we have email, fax, ftp, webdav, http or even a custom channel to deliver documents to. All we need do is create a java class that takes the arguments above and then delivers the document ...easy!

Next article we'll tackle emailing documents from the EBS

Trying to write this article for the third time today ... first time I forgot to commit it and my browser crashed ... serves me right for playing with IE7. Second time, I dont know what happened ... anyway trying again.

Apologies for the longer and longer titles ... hopefully the usefullness of the article outweighs the silly titles, got myself into a hole here. This time we're discussing emailing documents from the EBS, we need to pull together the Delivery Manager APIs with the shell scripts techniques we looked at last time.

But you can email already !

Little known fact but you can already allow your users to get emails from the concurrent manager. When submitting a request they can open the 'Options' popup and specify users that should receive a notification upon completion of the request. Thats great but how do they get hold of the output without logging back in and searching through the massive list of requests? If you set the 'Concurrent:Attach URL' to Yes then users will get a mail with a link in it to the output file. However, there are a couple of caveats:
1. The email can only be sent to a user registered in the system (remember to enter their email id)
2. The user has to be connected to the system to retrieve the output file.

Gotta thank Mr Azzopardi for this tidbit!

So what can I do?

To get around these limitations and attach an output file to an email to any user we can use the script print driver and the Delivery Manager APIs. Remember we can pass several parameters to the java via the script:

$PROFILES$.CONC_REQUEST_ID
$PROFILES$.PRINTER
$PROFILES$.CONC_COPIES
$PROFILES$.TITLE
$PROFILES$.FILENAME
$PROFILES$.RESP_ID
$PROFILES$.USER_ID

We could just add the email address we want to send the report to :

$PROFILES$.CONC_REQUEST_ID $PROFILES$.FILENAME tim.dexter@oracle.com

But that would mean either one person is going to get every report output from the system that uses that driver or you'll need to create a personal driver for every user, not a bad idea for smaller user groups. It would be better to derive the email address so we can have one email driver to maintain.
Using a combination of the PROFILES we can make a trip to the database to retrieve pretty much anything we want. With the USER_ID we can fetch the email address for that user; with the RESP_ID we could fetch all the emails for the users that have that responsibility. Using the CONC_REQUEST_ID we can get a hold of the concurrent program and maybe store the emails for the report in the Options field of the program definition form. We could even use the derived program id to look up emails against a custom table where we have stored users and programs ... we now have a nice report subscription model for EBS ... cool!

OK, let's do it ...

For this example this just tackle looking up the email address of the user ... the other options are just a variation on the same theme, its just getting the sql right.
So I have built the following java class:

package oracle.apps.xdo.ebsdelivery;
 
import oracle.apps.xdo.delivery.DeliveryManager;
import oracle.apps.xdo.delivery.DeliveryRequest;
import oracle.apps.xdo.delivery.DeliveryPropertyDefinitions;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
 
 
public class EBSEmailDelivery
{
  public EBSEmailDelivery(String rRequestID, String rFile, String rUser)
  {
        int userid =  Integer.parseInt(rUser);
        int requestid = Integer.parseInt(rRequestID);
    
    try {
        //get the report title
        String repTitle = getReportTitle(requestid);
        // create delivery manager instance
         DeliveryManager delMgr = new DeliveryManager();
        // create a delivery request
         DeliveryRequest delReq = delMgr.createRequest
 (DeliveryManager.TYPE_SMTP_EMAIL);
        // set email subject
         delReq.addProperty(DeliveryPropertyDefinitions.SMTP_SUBJECT,
 "EBS Report:"+repTitle +" for request: "+ rRequestID);
        // set SMTP server host
         delReq.addProperty(
         DeliveryPropertyDefinitions.SMTP_HOST, "mail.yourcompany.com");
        // set the sender email address
         delReq.addProperty(DeliveryPropertyDefinitions.SMTP_FROM,
 "ebsadmin@oracle.com");
        // set the destination email address
         delReq.addProperty(DeliveryPropertyDefinitions.SMTP_TO_RECIPIENTS,
 getEmail(userid) );
        // set the content type of the email body
         delReq.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_TYPE,
 "application/pdf");
        // set the document file name appeared in the email
         delReq.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_FILENAME,
 repTitle+rRequestID+".pdf");
        // set the document to deliver
         delReq.setDocument(rFile);
        // submit the request
         delReq.submit();
        // close the request
         delReq.close();
 
    }
    catch (Exception e) {
    e.printStackTrace();
 
    }
  }
 
  static Connection getConnection() throws SQLException, Exception
  {
        String fDriverName = "oracle.jdbc.driver.OracleDriver";
        String fDbName = "vis11510";
        String fServer = "xdodemo.us.oracle.com";
        String fPort = "1521";
        String fUserName = "apps";
        String fPassword = "apps";
 
        Class.forName(fDriverName).newInstance();
        Connection dbconn = DriverManager.getConnection
             ("jdbc:oracle:thin:@"+fServer+":"+fPort+":"
 +fDbName, fUserName, fPassword);
        return dbconn;
  }
  
  private String getReportTitle(int requestID)
  {
    String reportName = "";
    try
    {
      // Try and geta connection to the db
      Connection conn = getConnection();
      // fetch the report name based on the request id
      PreparedStatement getTitle = conn.prepareStatement(
 "select user_concurrent_program_name \n" + 
 "from fnd_concurrent_requests fcr,\n" + 
 "fnd_concurrent_programs_vl fcpv\n" + 
 "where fcr.concurrent_program_id = fcpv.concurrent_program_id\n" + 
 "and request_id = ?");
 //get the title
      getTitle.setInt(1,requestID);
     
      // get the query result in to a result set and then assign the
      // value to a variable we can pass back to the calling method
      ResultSet titleRslt = getTitle.executeQuery();
      titleRslt.next();
      reportName = titleRslt.getString(1);
      System.out.println(reportName);
      
      //Clean up 
      titleRslt.close();
      getTitle.close();
      conn.close();
      
      
    }
    catch (SQLException eSQL)
    {
      System.err.println("Could not create connection");
      eSQL.printStackTrace();
    }
    catch (Exception e)
    {
     System.err.println("Exception thrown");
     e.printStackTrace(); 
    }
     
    return reportName;
  }
 
 
  private String getEmail(int userID)
  {
    String eMailID = "";
    try
    {
      // Try and geta connection to the db
      Connection conn = getConnection();
      PreparedStatement getEmail = conn.prepareStatement(
 "select email_address from fnd_user where user_id = ?");
      getEmail.setInt(1,userID);
     
      // get the query result in to a result set and then assign the
      // value to a variable we can pass back to the calling method
      ResultSet emailRslt = getEmail.executeQuery();
      emailRslt.next();
      eMailID = emailRslt.getString(1);
      System.out.println(eMailID);
      
      //Clean up 
      emailRslt.close();
      getEmail.close();
      conn.close();
      
      
    }
    catch (SQLException eSQL)
    {
      System.err.println("Could not create connection");
      eSQL.printStackTrace();
    }
    catch (Exception e)
    {
     System.err.println("Exception thrown");
     e.printStackTrace(); 
    }
          
    return eMailID;
  }
  public static final void main(final String[] args) 
  {
    // Arguments passed
    //1.$PROFILES$.CONC_REQUEST_ID
    //2.$PROFILES$.FILENAME
    //3.$PROFILES$.USER_ID
 
    EBSEmailDelivery ebsMail = new EBSEmailDelivery(args[0], args[1], args[2]);
 
  }
 
}

You can get the source here.
Just a note, to develop and compile your class you are going to need the following libraries:
xdocore.jar - grab it from the Template Builder install
versioninfo.jar - same place
xmlparserv2-904.jar - the Oracle XML parser
mail.jar - this is the java mail library get it from Sun
Activation.jar - a supporting library for the mail library get it from Sun 

if you want to test it on your cleint machine you'll need the Oracle JDBC driver libraries too ... these will be under your EBS JAVA_TOP, jdbc12.zip worked for me. Ar runtime all the libraries will be available for you so all you'll need to do is add the path to your class in the shell script.

Walking through the class we have several methods.

• EBSEmailDelivery - at the top we have the delivery manager APIs for emailing. We have seen these already. You'll need to add your outgoing email server.
  
• getConnection() - this method gets us a connection to the db
  
• getReportTitle - this fetches the report title based on the concurrent request id. Check out the query we're executing.
  
• getEmail - this fetches the email of the user that ran the report based on the user id

The class is not catching every possible situation but you get the idea.

Now we have the class we mount it into the APPL_TOP. You should have a custom area under JAVA_TOP. My class is going to sit under the xdo directory under JAVA_TOP. You should note that the package name in the top of the class i.e. oracle.apps.xdo.ebsdelivery is going to define where the class is going to sit. In this case $JAVA_TOP/oracle/apps/xdo/ebsdelivery.

The shell script now needs to call the class and pass the parameters, I have the following:

CLASSPATH=/apps/vis11510comn/java/oracle/apps/xdo/ebsdelivery:$CLASSPATH
export CLASSPATH

java oracle.apps.xdo.ebsdelivery.EBSEmailDelivery $1 $2 $3
RESULT=$?
if [ $RESULT -ne 0 ]
then
echo "`date` There was an error while delivering the document."
echo "`date` Please inform your System Administrator."
exit $RESULT
fi

# Return success code to Oracle concurrent manager
exit 0

So there you have it, your users can get emails from the EBS with their reports attached. Of course you can get very sophisticated and grab multiple email addresses or even multiple reports if you are using a request set ... just let your imagination run riot.

Next ... faxing documents

The fax article is on the way but since I came up with the email solution there has always been the niggling fact that I was putting the EBS user name/password openly into my java class ... there had to be a better a solution !

There is, and thanks to Ashish from the dev team and a little work I found it. The OA Framework UI uses a DBC file to define the connection and other information to the EBS database and we can use that to get a connection to the database instead of hard coding the connection.

The file resides under the $FND_TOP/secure directory, check it out you'll have one. It will look similar to this:

#DB Settings
#Sun Sep 04 13:02:20 PDT 2005
GUEST_USER_PWD=user/pwd
APPL_SERVER_ID=F936BF325610DB94E03023823E050DC825406294681422881503890572956133
FND_JDBC_BUFFER_DECAY_INTERVAL=300
APPS_JDBC_DRIVER_TYPE=THIN
FND_JDBC_BUFFER_MIN=1
GWYUID=user/pwd
FND_JDBC_BUFFER_MAX=5
APPS_JDBC_URL=jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)
(FAILOVER=YES)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
 (HOST=your server)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=sid)))
FND_JDBC_STMT_CACHE_FREE_MEM=TRUE
FND_JDBC_STMT_CACHE_SIZE=200
TWO_TASK=yourdb
FND_MAX_JDBC_CONNECTIONS=500
FND_JDBC_USABLE_CHECK=false
FNDNAM=user
FND_JDBC_PLSQL_RESET=false
DB_PORT=1521
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5
DB_HOST=your server

there is also an environment variable called $FND_SECURE which will point to the file location.
To take advantage of the file and its contents and make your java class a whole lot more secure you can pass the FND_SECURE value and the dbc file name to the class via the driver i.e. $FND_SECURE/file.dbc

Then in your class add the following:

import oracle.apps.fnd.common.AppsContext;
...

  static Connection getConnection() throws SQLException, Exception
  {
        
        AppsContext aCont = new AppsContext(dbcLocation);
        Connection aConn  = aCont.getJDBCConnection();
        return (aConn);
        
  }

where dbcLocation is a string that holds the new parameter passed from the driver. The rest of the class remains the same.
So now you have a much more secure method to get the connection. Just be sure to close it after you have finished so the connection returns to the connection pool. Much cleaner, neater and faster :o)

Fax integration is probably one of the most popular and tougher delivery destinations from the EBS, you need connections to the fax server from EBS, you need to be able to convert your documents to a tif format, you may need very high volumes of faxes, your users will want to be able to monitor the delivery and have automatic redial if the fax recipient's machine is busy. This has required third parties and Oracle Partners to provide functionality for faxing documents.
The XMLP faxing solution aleviates some of these issues but it is not a replacement for some of the fine solutions out there.

What can it do?

• XMLP can send a fax via the IPP protocol, this requires a delivery server such as CUPS to handle the fax the request.
  
• XMLP can be integrated into the concurrent manager or even a specific product
  
• XMLP can be used for lightweight faxing requirements
  
• You can build a complete faxing solution using the XMLP APIs

• It can not handle huge volumes of faxing, Im talking thousand of faxed an hour
  
• No out of the box redial and monitoring EBS UI. CUPS has its own UI that could be used.

If you're still interested in building a fax delivery channel then you can use the java APIs and the shell script in conjunction with a dummy printer driver. Again, you can extend your implementation as much as you want so you can build feedback into the concurrent manager or even build your own fax monitoring UI interface.

For this demo we'll take a similar approachto that of the email solution. We'll need a CUPS server set up to communicate the fax content to. CUPS is only for UNIX or LINUX implementations, for those of you on a Windows there are commercial solutions available to have a FAX machine connected to your network via IPP. We will also use the database querying to grab the fax number.

Step1: Set up CUPS

The set up of CUPS for faxing is covered in the user guide (pg 11-36) you'll need some extra packages and install them. The good news is that they are all open source solutions and therefore inexpensive, the not so good news is that support is going to be patchy. Follow the instructions and test that the fax machine is sending output when called.

Step 2: Build the java class

We can reuse much of the email code we wrote last time. We of course need to use the fax APIs rather than the Email. We also need to fetch the fax number of course, you can use the techniques we used last. For this example I decided to try something different. Im assuming that the report that is to be delivered actually has the fax number embedded inside it. In this case we can fetch the XML data used to create the report, parse it and retrieve the fax number value.
I have been a little lazy with my class, it parses and sends the document, if you were going to use the parser functionality across multiple delivery classes then you would break it out on its own.
I use the SAXParser rather than the DOM, we're not sure how much data we are going to get and do not want to gobble up memory loading the whole document using the DOM parser.
In the class I have I just have a few methods to:

1. Parse the document - the parser just moves through the document and event are fired that we can subscribe to.
   
2. Look for the specific element that holds the fax number - once found its sets a flag for the next method to
   
3. Fetch the fax number value into a variable and sets another 'found the element' flag for the next function
   
4. Check the closing element that holds the fax number - if the flag is set then the parser stops working through the document.

Heres the code snippet for the parser, the startElement, endElement and characters methods are standard SAXParser methods we need to implement:

  public String parseFAXNum (String xmlFile)
  {
    try {
          
          SAXParser parser = new SAXParser(); 
 //Because Im lazy my class is extending the DefaultHandler for SAX
 // so I can just use 'this' for the following methods



          parser.setContentHandler(this);
          parser.setErrorHandler(this);
          // We parse the XML file fetched from the variables passed
 // from the printer driver
          parser.parse(xmlFile);
          
    }
          catch (Exception ex) {
                System.out.println(ex);
          }
        return(xFaxNum);
  }
  
  public void startElement(String uri, String localName, 
          String rawName, Attributes attributes) 
          // We're checking for the CUST_FAX_NUM element, this method 
 // looks for the opening tag. If found then we set a 
 // "found" flag to true for the "characters" method

       {
             if (localName.equals("CUST_FAX_NUM")) 
             {
                getEleVal = true;
              } 
       } 
  public void endElement (String uri, String localName,
          String rawName) throws SAXException
          {
         // We're checking for the CUST_FAX_NUM element, this method looks
 // for the closing tag.
         // If found then we throw an exception to stop the parser.
            if (localName.equals("CUST_FAX_NUM")) 
            {
               getEleVal = false;
               throw new SAXException("Found element required");
             } 
          }

    public void characters(char[] cbuf, int start, int len) 
      {
         if (getEleVal)
         {
           // We have the value we want so assign it to the fax variable
           xFaxNum = new String(cbuf,start,len);
            
         }
      }

    try
    {
      // fetch the fax number from the XML data that is
      // fetched via the requestid from the driver
      String faxx  = parseFAXNum(getXMLFile(requestid));
      // create delivery manager instance
      DeliveryManager dm = new DeliveryManager();
      // create a delivery request
      DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_IPP_FAX);
      // set IPP fax host - this is our CUPS server 
      req.addProperty(DeliveryPropertyDefinitions.IPP_HOST,
 "xdodev2.us.oracle.com");
      // set IPP fax port - - this is our CUPS server port
      req.addProperty(DeliveryPropertyDefinitions.IPP_PORT, "631");
      // set IPP fax name - this is our CUPS fax name
      req.addProperty(DeliveryPropertyDefinitions.IPP_PRINTER_NAME,
 "/printers/fax1");
      // set the document format
      // we can pass a pdf document and CUPS will convert it to tiff for us
      req.addProperty(DeliveryPropertyDefinitions.IPP_DOCUMENT_FORMAT,
 "application/pdf");
      // set the phone number to send
      req.addProperty(DeliveryPropertyDefinitions.IPP_PHONE_NUMBER, faxx);
      // set the document
      req.setDocument(rFile);
      // submit the request
      req.submit();
      // close the request
      req.close();
    }

You might have noticed that we are  passing a pdf format to the CUPS server, CUPS will convert the pdf to tiff format for us. You can see the complete class definition here.

Now all you need do is create a new fax "printer" driver to call your class and you now have faxing from the EBS with some level of flexibility for your users.
If you are looking at handling a batch of documents, XMLP has a much better solution in its Bursting Engine and I'll cover that once I have completed the delivery articles.

Happy Faxing!

Couple more delivery channels to cover and we'll have exhausted the options for document delivery from EBS. For this article Im turning to document management. Many of you will have a document management system in your company this may be as simple as scanning invoice documents and storing them in some LOBS table in the EBS schema for later retrieval or you may have Oracle Files or maybe a third party solution.
To get the documents to the repository may involve some third party or custom code interfacing into the EBS stack. XML Publisher can help you get the documents to the repository directly from the concurrent manager. Whats more you can do this while you are delivering the document to the end user at the same time and even do some post processing to maybe add a watermark to the archived document with the word "COPY" striped across it.

Same again please ...

With all the work we have done with email and faxing we now know how the whole solution hangs together and how we can call APIs to get the documents delivered. All we need do is add the code to send the document(s) to the correct place in the repository. Just about all the document management solutions offer a webdav channel to push documents into the document repositiory. They may also have the ability to push in some metadata about the document. I have naturally picked on Oracle Files in the example below, but its still generic for all webdav servers.

    /*create delivery manager instance*/
     DeliveryManager dm = new DeliveryManager();
     // create a delivery request
     DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_WEBDAV);
      // set document content type
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_CONTENT_TYPE,
 "application/pdf");
     // set the WebDAV server hostname
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_HOST, "mywebdavhost");
     // set the WebDAV server port number
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_PORT, "80");
     // set the target remote directory
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_REMOTE_DIRECTORY,
 "/content/");
     // set the remote filename
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_REMOTE_FILENAME,
 "xdotest.pdf");
     // set username and password to access WebDAV server
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_USERNAME, "xdo");
     req.addProperty(DeliveryPropertyDefinitions.WEBDAV_PASSWORD, "xdo");
     // set the document
     req.setDocument("/document/test.pdf");
      // submit the request
     req.submit();

     // close the request
     req.close();

I want to watermark ...

To add a watermark to the document its just a simple API call.

 // You already have the document locally so call the PDFDoc merger
 // and create input and output streams for it
 PDFDocMerger docMerger = new PDFDocMerger(inputStreams, outputStream);
 // You can use setTextDefaultWatermark() without these detailed setting
 docMerger.setTextWatermark("COPY", 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();

Pretty straightforward right?
There are a couple of points here:
1.There is a need to get the username/pwd for the repository. There are ways around this, putting documents into a public directory, passing the username/pwd from the EBS instance ... Im not going to cover this in this article thou.
2. Metadata - all of the repositories I have come across have a java interface to push meta data in. You could use the XML parsing techniques in the fax article to grab pertinent information about the document to join it in the repository.

Short but sweet ... next, custom channels

Date formatting in XML Publisher is very powerful, it can provide a variety of formats from simple 03/12/99 to 'Friday, December 31, 1999 6:15 PM GMT' ... notice the timestamp and the timezone components. All very neat but how do you get there?

Get the right format ...

Well the first hurdle is to get your dates in the XSD date-time format:
                  YYYY-MM-DDThh:mm:ss+HH:MM
Looks straightforward and you ought to be able to get the database 'to_char' function to serve it up no problem, but there is a wrinkle in there in the form of that 'T' character its used as a separator between date and time components. Try and use that format mask with the to_char and the database will choke ... the format is not recognised. There is hope, even for Oracle Reports under EBS users :)

1. If you are not interested in the time and zone info then just use the 'YYYY-MM-DD' format mask, no need for the 'T'. Oracle Reports and the db support this mask. XMLP will then format the date appropriately. any timestamp is going to be 12:00:00 AM and the timezone will default to GMT
2. Use the XMLP extraction engine - the engine will extract all dates using the XSD mask - simple
3. If you're using Oracle Reports or your own plsql/sql extraction routine and want the timestamp and zone then only way I have found to construct the mask is to use something like:

    to_char(sysdate,'YYYY-MM-DD')||'T'||to_char(sysdate,'hh:mm:ss+HH:MM')

this will at least get the date into the right format.

I stand corrected on this thanks to an anonymous comment, I was playing with masks in the 
db while writing and could not get the format to work, however our anonymous friend has:
to_char(sysdate, 'YYYY-MM-DD"T"hh:mm:ss+HH:MM') which works perfectly ... apologies!

Date formatting and calculations

Now you have date in the right format you can apply masks in the layout very easily. You can either use the MSWord formats or XMLP provides a format-date function ... there is a complete section devoted to the formatting in the user guide. Page 118 is the start and covers all XMLP flavors.
The other advantage of the mask is that calculations on dates become possible with XSLT 2.0, prior to this all dates were treated as strings not much use when it comes to calculating the number of days between two dates. Thats another article in itself ... there are plenty of resources out there now.

Another series of articles on the way ... this time concerning the relatively new data extraction engine from XML Publisher that is currently available in the Enterprise and E Business Suite flavors.

First a little history ... 

Why build another extraction tool, there are others that can generate XML, Oracle Reports, various solutions from the database folks, the list goes on. To answer why you have to understand the volumes of data we are trying to handle here and the furture of reporting for Oracle Applications. For EBS in particular we had multiple reporting solutions, the majority of which were based on Oracle Reports. The plan going forward is to remove Oracle Reports from the techstack in the fusion timeframe. Therefore we needed an extraction engine that offered all that Oracle Reports offers and was just as fast - Oracle Reports is danged fast at generating XML data. On top of that we are not talking about extracting data for a 2 page invoice, the engine needs to extract millions of rows to build tens of thousands of pages for some reports. 
We looked at what was out there, SQL XML provided fast extraction for large data sets but we needed among others, event triggers and flexfield user exits, plus users would need to learn the wrapper functions used to generate the hierarchies. After a lot of research we decided to build an engine ourselves with all the bells and whistles needed to cover the Oracle Reports replacement requirements.

Features ... features ... features

To dig in a little to the features the engine provides:

Fast, scalable extractions - its fast, faster than Oracle Reports. We worked closely with the Oracle Performance team and had to re-write it a couple of times to get their approval. It needs to be fast too, those of you that use Oracle Reports EBS know that out of the box we generate flat text character based output. With XMLP we're generating highfidelity output ... that requires more processing time so the more time we save on the extraction the more we have for formatting without slowing the report generation.

Oracle Reports Features - if its going to replace OR in EBS then it has to match OR on features:

• Multiple Queries/Joins -  enabling master/detail extractions
  
• Event Triggers - pre- and post- fetch for business rule processing. Currently plsql support but will add java
  
• Flexfield Support - gotta get that natural account description with out the select statement - this is an EBS feature.
  
• Formula/summary columns -  again similar to Oracle Report functionality allowing you to create aggregation values and pl/sql based formula columns in your extraction
  
• Data Structure - this allows you to build a hierarchy into your XML data similar to the grouping abilities in Oracle Reports
  
• Group filters - a la Oracle Reports
  
• etc

on top of that its also has:

• Rich Java API layer - call a data template from your jsp or java app
  
• Distributed Queries - this is neat. You're not tied to a single db nor even to an Oracle db. You can construct a data template that, for example hits an MSSQL instance for customer data and an Oracle db for their invoices. the engine will generate a single result set of hierarchical XML i.e. Customer1
   Invoice1
   Invoice2
  Customer2
   Invoice1
   Invoice2
  
  
• Static XML File support - query across a db and an XML file. Text and XLS support coming.
  
• Pluggable Data Templates - not here yet but theidea here is that you can build a core data template and then allow a plugin DT to be applied over the top to get more data from other sources.

Im interrupting the Data Template flow with a really great question and answer from the forum that I wanted to share on time zone formatting. For those of you that want to include a timezone portion into your output dates XMLP can help but you might not find what you expect!

Let's assume we have the following date element in our data stream:

 2006-07-27T12:48:00.000+02:00

Notice the timezone offset from GMT i.e. Paris, France.
Using 
       <?format-date:DATE_TEST; 'LONG_TIME_TZ'?>
we  get
 Thursday, July 27, 2006 10:48 PM GMT

The GMT appears incorrect we were expecting 'Paris' ... hey thats a bug! Look closer and you'll notice that the time component has been adjusted back by 2 hrs. So it is actually correct for the GMT timezone.
So how do we stop the adjustment and see 'Paris' i.e. Thursday, July 27, 2006 12:48 PM Paris
This can be achieved using:
    <?format-date:DATE_TEST; 'LONG_TIME_TZ';'Europe/Paris'?>
that 'Europe/Paris' is a java time zone specification. The list can be gotten here, http://www.thescripts.com/forum/thread15954.html
This is in the user docs but  a little tough to grasp(;o). If you are in EBS then there is a profile option, more details in the user guide. If you are using APIs the java time zone can be passed into the template as a parameter.  Neat!
 

Apologies for the delay ... I have lots of content and examples for you but I did not have a means for you to test your data templates. I have now rectified that and have taken one of our internal testbeds and packaged it up so you can test to your hearts content. The test bed is a web application that runs inside OC4J, I have not tested it with any other mid tier flavors but if you're game.

Before we start on the content lets install the app and test a sample data template.

1. Download the EAR file here. Well you would be able to download it if I can find somewhere that can host it. for now drop me a mail and I'll get it to you. 
   
   Finally found a home for the file on my website, you can get the file here, http://www.banzel.com/download/DTTestBed.ear
    
   
2. I have tested with OC4J 9.0.4, 10.1.2 and 10.1.3 so once you have the EAR its a simple case of deploying it. If you do not have OC4J installed, go for the latest 10.1.3 release and have a nice UI to help you deploy the app.
   
3. Once installed you just need to point your browser to http://yourserver:port/DTTestBed/DT1.jsp
   4. The first page you'll see is:
   
   
   
   

   I have created a small data template that will run against the scott/tiger schema, so download it from here. Then use the browse button to find it and hit Upload.

   
   
4. You'll then get a page like this:
   
   
   

   
   
   Heres where the fun starts, so in the page you have a parameter section, these are the parameters parsed out of the data template in this case we only have one, pDeptNo. You can fill in a value or leave it blank to get the complete data set.
   Then we have the runtime options:
   

   
   
   • Generate XML Schema  - fairly obvious but very useful if you are going to be doing some PDF template mapping
     
   • Generate Default Layout - this will generate an RTF template for you based on the data structure of the data template, again useful to get you started
     
   • Layout Template - if you have already developed an RTF template (no it does not support PDF templates yet) then you can load it to be applied to the resulting data. If you choose this option then a default template will not be generated.
     
   • Database - we need a connect string to the database, hostname:port:sid
     
   • User Name - obvious
     
   • Password - obvious if you have it
     
   • ORG ID - this is for EBS customers that want to test data templates running against the EBS db - its not required.
   
   
   
   
5. So fill in some values so you have something like this:
   
   
   

   

   
   

   and hit Submit
   7. If all went well then you'll get a page of results similar to this:

   
   

   

   
   

   There will be links to the various objects that XMLP has generated. These are all created in a temp directory under the root of the web app in mycase

   D:Oc4j10.1.3j2eehomeapplicationsDTTestBedDTTestBedtemp

   . Be sure to clean that directory up regularly it will fill your disk all to quickly.
   

Hopefully you have downloaded and installed the Data Template Test Bed I provided in my last post, you may have even loaded and run the sample I provided ... hopefully sucessfully. You may have even gone off and got started on your own. Well we are going to take a step back here and get back to basics and then move up to the more powerful stuff like filters, triggers and flexfields ... we're going to get there quickly so please be patient.

Being the consumate geek and not wanting to break tradition when it comes to starting out with a new technology we're going to build and test a Hello World data template ... now known as 'DTs' for brevity. First lets take a look at the anatomy of a DT, it'll help later.

<dataTemplate name="EMPLOYEES" defaultPackage="" description="Employee Data">
   <properties>
      <property name="include_parameters" value="true"/>
      <property name="include_null_Element" value="true"/>
      <property name="xml_tag_case" value="upper"/>
      <property name="db_fetch_size" value="500"/>
      <property name="scalable_mode" value="off"/>
      <property name="include_rowsettag" value="false"/>
      <property name="debug_mode" value="off"/>
   </properties>
   <parameters>
      <parameter name="pDeptNo" dataType="number" defaultValue=""/>
   </parameters>
   <lexicals/>
   <dataQuery>
  <sqlStatement name="Q1" dataSourceRef=""><![CDATA[SELECT DEPTNO,DNAME,LOC 
 from dept where deptno = nvl(:p_DeptNo,deptno)
 order by deptno]]>
 </sqlStatement>
  <sqlStatement name="Q2" dataSourceRef=""><![CDATA[SELECT  EMPNO,ENAME,JOB
 ,MGR,HIREDATE,SAL,nvl(COMM,0) COMM  
 from EMP 
 WHERE DEPTNO = :DEPTNO]]>
 </sqlStatement>
  </dataQuery>
   <dataStructure>
  <group name="G_DEPT" source="Q1" groupFilter="">
   <element name="DEPT_NUM" value="DEPTNO" function=""/>
   <element name="DEPT_NAME" value="DNAME" function=""/>
   <element name="LOCATION" value="LOC" function=""/>
   <group name="G_EMP" source="Q2" groupFilter="">
    <element name="EMPNO" value="EMP_NUM" function=""/>
    <element name="EMP_NAME" value="ENAME" function=""/>
    <element name="JOB_TITLE" value="JOB" function=""/>
    <element name="MANAGER" value="MGR" function=""/>
    <element name="HIRE_DATE" value="HIREDATE" function=""/>
    <element name="SALARY" value="SAL" function=""/>
    <element name="COMMISSION" value="COMM" function=""/>
   </group>
  </group>
 </dataStructure>
</dataTemplate> 

I have highlighted the main players in a DT:

• dataTemplate - header element where you provide a name and description for your DT. The name will be used as the root name of the element.
  
• properties - these are the runtime switches for the extraction engine:
  
  
  
  • include_parameters - should the parameter values be extracted to the XML or not
    
  • include_null_Element - if a value when extracted is null do you want an empty element in the xml
    
  • xml_tag_case - upper or lower case tag names
    
  • db_fetch_size - maximum number of records to fetch
    
  • scalable_mode - if you know this is going to be a large dataset or an intensive extract then set this to true
    
  • include_rowsettag - for simple XML with no hierarchy do you want ROWSET as the root element
    
  • debug_mode - generate a debug file while processing or not. The test bed I posted will always do this for you.

• parameters - these are the runtime parameters for the DT
  
• lexicals - these are EBS specific and will allow you to fetch flexfield values ... more on these later
  
• dataQuery - heres where we start to define the
  sqlStatement - this is where we define the query(s)
  
• group - this section defines the structure of the data we want to see and contains
  
• element - the reference to the column in a specific query. These also have a name for the resulting XML element and you can include functions here.

So thats a quick overview, lets do the HelloWorld ... its as simple as it gets when it comes to DTs.

<dataTemplate name="HELLOWORLD" defaultPackage="" description="Hello World DT">
   <properties>
      <property name="include_parameters" value="true"/>
      <property name="include_null_Element" value="true"/>
      <property name="xml_tag_case" value="upper"/>
      <property name="db_fetch_size" value="500"/>
      <property name="scalable_mode" value="off"/>
      <property name="include_rowsettag" value="false"/>
      <property name="debug_mode" value="off"/>
   </properties>
   <parameters/>
   <lexicals/>
   <dataQuery>
  <sqlStatement name="Q1" dataSourceRef=""><![CDATA[select 'Hello World!' WELCOME from dual]]></sqlStatement>
 </dataQuery>
   <dataStructure>
  <group name="HELLO" source="Q1">
   <element name="WELCOME" value="WELCOME"/>
  </group>
   </dataStructure>
</dataTemplate>

<?xml version="1.0" encoding="UTF-8"?>
<HELLOWORLD>
 <HELLO>
  <WELCOME>Hello World!</WELCOME>
 </HELLO>
</HELLOWORLD>

I have had a few emails and the forum has been hot lately with questions about sourcing an image from the OA_MEDIA directory. the more popular question is for an RTF template but there are those of you trying to update the purchasing template which is sadly written in XSL-FO and not in RTF format.

RTF Templates

The user guide covers this and its now correct :o! You can either place a dummy image in your template and then in the Web tab of the image properties put in the following:

url:{'${OA_MEDIA}/IMAGE_FILE'}

For example

url:{'${OA_MEDIA}/FNDSSCORP.gif'}

Notice the use of curly braces rather than rounded and the position of quotation marks. Its very important, otherwise you are going to be pulling hair out at a rapid rate. If you have an image name embedded in your XML you can use that too, just need to use the concat command to bind the values together:

url:{concat(${OA_MEDIA},'/',.//IMAGE_NAME'}

the other option is to embed the necessary XSLFO code into a field in the template:

<fo:external-graphic src="url('{'${OA_MEDIA}/FNDSSCORP.gif'}')" />

XSLFO Templates

IF you're updating that PO template and just want your own logo, then you'll find that the PO team have provided a placeholder in their template. Just uncomment it and add in the OA_MEDIA reference

src="url('{'${OA_MEDIA}/fwkhp_folder.gif'}')

Be aware the format is subtly different to the RTF format ... sorry not much we could do there. Just be careful to avoid that hair loss :)

Bonjour mes ami!

I have just got back from a conference in the warmer climes of the French Riviera. Its been quite a shock; after nearly a week of mid 60s temperatures and blue seas coming back to somewhat chilly and mid 30s Colorado has taken some getting used to again. Thankfully the promised thaw is upon us and the piles of snow in my yard are now dwindling, much to my sons' chagrin the snowboard run we had in our back yard is becoming a distant memory. Ahh c'est la vie ... so to another article in the fragmented data template series.

I have spent some time looking at our documentation on data templates, its pretty good so please check it out. Im picking on a series of some of the tougher features to get your head around. for this article its creating and joining multiple queries.

There are two methods to join queries, lets assume we have the following queries:

SELECT DEPTNO,
DNAME,
LOC 
FROM DEPT
ORDER BY DEPTNO

SELECT  DEPTNO,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
NVL(COMM,0) COMM
FROM EMP

we want to create a data template that will use the DEPT query as the master and the EMP as the child. We need to link the two queries.

Method 1: Query Link

We can create a link similar to Oracle Reports, like this:

<link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2" childColumn="DEPTNO"/>

We name the link and then specify the queries and the linking columns, so the <dataQuery> section of our data template will look like:

<dataQuery>
 <sqlStatement name="Q1">
 <![CDATA[ 
    SELECT DEPTNO,DNAME,LOC from scott.dept 
    order by deptno ]]> 
 </sqlStatement>
 <sqlStatement name="Q2">
  <![CDATA[ 
     SELECT  DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
     from scott.EMP ]]> 
 </sqlStatement>
<link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2" childColumn="DEPTNO"/>
</dataQuery>

It works fine but we have to duplicate the DEPTNO column in the child query. Plus on a more real world query its going to be slower than taking ...

Method 2: Bind Variables

We did alot of testing on the joining of queries and found that usng bind variables was consistently faster then query links, so if you can, and there should not be a reason why you should not, use binds. What do I mena by bind variables, well taking our two queries we are able to have a master-detail relationship between the two i.e. the dept based query is the master and the emp the detail. We can join them quite easily using 'dept_id' as our joingin criteria:

<dataQuery>
 <sqlStatement name="Q1">
 <![CDATA[ 
    SELECT DEPTNUM,DNAME,LOC 
 from scott.dept 
    order by deptno ]]> 
 </sqlStatement>
 <sqlStatement name="Q2">
  <![CDATA[ 
     SELECT  DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
     from scott.EMP
 where DEPTNO = :DEPTNUM ]]> 
 </sqlStatement>
</dataQuery>

Notice, I have changed the column names a little just to make the join clearer (dont cut 'n' paste this one.) We use :DEPTNUM from the first query in the where clause of the second, et voila we have two queries joined. Its simpler, less typing and even better its faster!
Salut!

While I have been away I provided two days on in depth techie training to Oracle Consulting in EMEA, they worked me hard (very hard) ranging from inserting barcodes to bursting, all good fun and informative I hope. The second day we spent some time sharing experiences of developing extractions, templates, etc and Im indebted to Serge Vervaet from Oracle Belgium for some great tips. The first of which was on building a data template. The XMLP extraction engine is fast, you can migrate your Oracle Reports to the format relatively easily but there is currently no tool to help you build them so once you have your query sorted out its off to Notepad or an XML editor. For big extracts with lots of columns even I have to admit its very tedious typing out those grouping structures, Serge came up with a way to reduce the tedium and speed up the process by doing away with the grouping section all together - use inline CURSORS in your queries. This creates all the hierarchy you need in your query without the need for the grouping section.

Lets take our old friends the EMP and DEPT queries:

SELECT DEPTNO,
DNAME,
LOC 
FROM DEPT
ORDER BY DEPTNO

SELECT  DEPTNO,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
NVL(COMM,0) COMM
FROM EMP

To get a hierachical XML output we need to add the following data structure.  

<dataStructure>
  <group name="G_DEPT" source="Q1" groupFilter="">
   <element name="DEPT_NUM" value="DEPTNO" function=""/>
   <element name="DEPT_NAME" value="DNAME" function=""/>
   <element name="LOCATION" value="LOC" function=""/>
   <group name="G_EMP" source="Q2" groupFilter="">
    <element name="EMPNO" value="EMP_NUM" function=""/>
    <element name="EMP_NAME" value="ENAME" function=""/>
    <element name="JOB_TITLE" value="JOB" function=""/>
    <element name="MANAGER" value="MGR" function=""/>
    <element name="HIRE_DATE" value="HIREDATE" function=""/>
    <element name="SALARY" value="SAL" function=""/>
    <element name="COMMISSION" value="COMM" function=""/>
   </group>
  </group>
 </dataStructure>

SELECT DEPTNO,
  DNAME,
  LOC, 
  CURSOR(SELECT  EMPNO,
           ENAME,
           JOB,
           MGR,
           HIREDATE,
           SAL,
           nvl(COMM,0) COMM  
      from EMP ) as EMP 
  from dept 
  where deptno = nvl(:p_DeptNo,deptno)

Notice the CURSOR command and the 'as EMP' the latter will create the group G_EMP in the XML data:

<EMPLOYEES>
 <PDEPTNO>10</PDEPTNO>
 <LIST_DEPT>
 <DEPT>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
  <LIST_G_EMP>
   <G_EMP>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>1980-12-17T00:00:00.000-08:00</HIREDATE>
    <SAL>800</SAL>
    <COMM>0</COMM>
   </G_EMP>
    ...
   <LIST_G_EMP>
  </DEPT>
 </LIST_DEPT>
</EMPLOYEES>

So we saved some time and effort and still got structure we needed. Sample DT here. I have to say we have not exhaustively tested the performance but Serge is getting good results and reducing his development time :o)

Many of you will have noticed, maybe even been confused by another product that has hit the Oracle streets namely 'Business Intelligence Publisher', BI Publisher for short and now affectionately know as 'BIP' by the cognoscenti.
Its not a new product, its XML Publisher with a new identity for its place in the BIEE suite of products, where it now generates the 'high fidelity' output from the BI Suite (more on this in a later posting.) Its also the new name for the recently released 10.1.3.2 Enterprise release, BIP Enterprise, again, more on that later too.
For now it remains as 'XML Publisher' under E Business Suite PeopleSoft Enterprise and JD Edwards EnterpriseOne and World products. Have no fear they may have different names but under the covers its the same core engine at work. 

Update on versions ...

Yeah, things are getting complex on our numbering system  so here's the low down. Im mapping ERP releases to the original XMLP release numbers ...

Applications
EBS 11.5.10  - baseline release comes with R4.5
EBS 11.5.10 RUP2 - comes with R5.0
         Of course for EBS 11i you can apply the latest XMLP release from metalink 5.6.2. The 5.6.3 release is coming soon.
EBS R12 - baseline release comes with release 5.6.3
PeopleTools 8.48 - comes with release 5.6
JD Edwards - comes with 5.6

BI EE & Standalone

BIEE Release 10.1.3.2 - contains BI Publisher 5.6.3
BI Publisher Enterprise 10.1.3.2 - based on 5.6.3 release.

Our marketing and I would have you believe that XML Publisher can empower your end users to build their own layouts and for the majority of your reports I still think thats true; creating listing reports with sub totals, re-grouping and page breaks are well within the capability (after a little training) of all but the most ardent technophobe. But when it comes to more complicated documents, usually the customer facing types, invoices, purchase orders, etc they very quickly need to get into some more advanced features which the template builder does not yet support and IT needs to step in. But IT does not need to do it all and revert to the slow report development seen before the advent of XMLP.

Another great tip from Serge in EMEA consulting came out at last weeks training event. Give the user a blank MSWord document and one formfield ... now ask them to design the complete layout of a report use the formfield to position the data and put in some sample data into the fields and even go as far as formatting them i.e. date, string or number. Then have them mark up the document with the look and feel they want, logos, colors, etc. If they think an invoice is going to stretch across multiple pages have them design the second and possible subsequent pages. Once they are happy with it, get them to sign off on it, you can then enable the template putting required data tags and for-each structures. Before handing back to the user for testing with some sample data Serge goes as far as to hide the form fields that contain the logic for the report from the user. This is simple enough, just mark the control fields and then set the font to hidden, you can use the ¶  to show/hide the fields. If the user needs to move data about, they can without getting worried about 'breaking' the report. Word of caution here thou, they could move things around just that little too far.

So even on the tougher templates the end user can still be involved in the design and get what their users want in the output and get that report out and into production much faster <:o) 

Charts charts charts ... yep XMLP/BIP can do em ... in fact it can do about 30 different types but the Insert Chart dialog of the template builder now only exposes about 6 of them, with a few bells and whistles ... if you want to go further then you have to get coding. I've covered some alternatives for the coding in previous posts but Im now posting what I hope is going to be a useful 'cheat sheet' for some of the tougher chart styles. Again, thanks to another Oracle Consulting star, Kan Nishida who put the samples together you can now quickly grasp how to put together the following chart types:

• Multi Bar Chart
  
• Dual Y Bar Chart
  
• Split Dual Bar chart
  
• Vertical Bar Stack
  
• Line Chart
  
• Dual Line Chart
  
• Pie chart
  
• Pie Ring Chart
  
• Multi Pie Charts
  
• Area Chart
  
• Area Vertical Stack

Some easy ones, some not so easy, Kan has provided some useful hints in the template on whats needed for a specific chart type and of course you can always take a look at the web properties for the dummy chart images to get a better idea. RTF template, XML data and PDF output available here.

Update
Got a request for a multi bar/line combo chart ... here it is.

When it comes down to it, XMLP is a great reporting tool inside the E Business Suite but the greatest value is that offered by the products that use it and provide enhanced reporting functionality to their users. 11i10 got the ball rolling with the HRMS, Purchasing, Contracts, Advanced Collections and other teams moving some of their reporting functionality over to XMLP. In the case of Advanced Collections they jumped in a the deep end and based all of their reporting on XMLP including document delivery.

With the release of R12 you are going to see many more products taking advantage of the reporting gains XMLP offers them and ultimately you. The new Payments team are standardizing on a single extract and then providing you EFT formats (EText templates), Check formats (RTF Templates), the Global Finanacials teams have reduced the number of extracts they have and are shipping you layouts. But the most obvious thing to notice is the number of out of the box templates now available, roughly 870 templates across 60 or so teams means that there is a high chance the product as now developed the template you are interested in. So it should be a simple case of getting their template, copying it, customizing it and then deploying it - done.

I have generated a list of templates by product then by concurrent program available here. These are the templates shipping with the baseline R12 release. There is also a project going on to migrate the remainder of the Oracle Reports based concurrent programs to the XMLP platform, slated for release later this year you'll have more than 2000 templates available ... having met many of you and discussed the canned reports you either use none of them or very few and those you have to customize ... hopefully with this many templates available life will become easier for you.

What of your own custom reports, how can you migrate those? Well in R12 there are a set of libraries to help you move your Oracle Reports to a data template (extract) plus a layout template (RTF) ... the libraries are being used internally to convert the canned reports so they have been put through their paces ... more on this in another future post.

Hey, Im an 11i customer what do I get?
Well one of the carrots to come to R12 is the better reporting and the number of templates available. There are tentative plans to build a Template Bank on metalink to allow you to download development delivered templates, hopefully consulting templates and even other customer templates ... this is all still on the drawing board but a distinct possibility. With the 5.6.3 release, coming soon, you also get the migration libraries to help you move your own Oracle Reports. Of course if you can find a friendly R12 customer and do some template negociation and you're off to the races, hey we'll have an gray market in template swapping before you know it.

Had a request today asking how to get started with the BIP API layer. I think the use of the APIs is straightforward enough, there are lots of examples to get you started in the developer sections of the user guide. The tough bit is, what libraries and jars need to be in the class path to be able to work with the APIs without annoying compile errors? So heres the list:

• xdocore.jar  - the core BIP/XMLP library
  
• aolj.jar - this is an Oracle EBS library, we need it whether you're developing in EBS or not
  
• i18nAPI_v3.jar - this is the i18n library used for localization functions
  
• xdoparser.jar  - this is the scalable XML parser and XSLT 2.0 engine
  
• xmlparserv2-904.jar  - the main XML parser/XSLT engine - these will come together again in a coming version.
  
• bipres.jar - charting library
  
• bicmn.jar - charting library
  
• jewt.jar - charting support library
  
• share.jar - charting support library
  
• collections.jar - you only need this if you are working with the delivery APIs or bursting engine.

Now if you're using JDeveloper then the charting and XML Parser libraries will already be available to you. I'd recommend creating a directory with all of the above thou and using them as custom libraries in your project. That way you'll know when it comes to deployment you're not going to get any unexpected surprises at runtime.

Where to get the libraries:
1. EBS - you can point your java project to the JAVA_TOP directory  - not the simplest solution
2. Download the BIP Server and open the EAR file and find the jars
3. Download the Template Builder for Word, install and check the jlib library under the install directory.

So now you know what you need and where to get it.

Need a quick start ... how about applying an RTF template to some XML and generating PDF output ...

package xdotestbed;
 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import oracle.apps.xdo.XDOException;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.RTFProcessor;
 
public class XDOTest 
{
  public XDOTest()
  {
  // Need to use a try-catch block
  try
  { 
    // Set the RTF template
    FileInputStream fiS = new FileInputStream("c:\\temp\\check.rtf");
    //Instntiate the RTFProcessor
    RTFProcessor rtfP = new RTFProcessor(fiS);
    // Set the output XSL
    rtfP.setOutput("c:\\temp\\check.xsl");
    // Process
    rtfP.process();
  }
  catch (FileNotFoundException ex)
  {
    ex.printStackTrace();
   }
  catch (IOException ex)
  {
    ex.printStackTrace();
   }
  catch (XDOException ex)
  {
  ex.printStackTrace();  
   }
   
   //Now process the XSLFO template against the XML data
      // Instantiate the FOprocessor
      FOProcessor processor = new FOProcessor();
      // set XML input file
      processor.setData("c:\\temp\\check_data.xml");
      // set XSL input file
      processor.setTemplate("c:\\temp\\check.xsl");
      // set the output format
      processor.setOutputFormat(FOProcessor.FORMAT_PDF);
      //set output file
      processor.setOutput("c:\\temp\\check.pdf"); 
    // Now we process, have to surround with a try-catch block thou
    try
    {
      // Process !
      processor.generate();
    }
    catch (XDOException e)
    {
      e.printStackTrace();
    }

  }
 
   // Heres the main method to run the class. 
 // You could of course add arguments for the RTF, XML
 // and output PDF and avoid hard coding them in the class.
 // Sorry, Im being lazeeee ... 
 public static void main(String[] args) {
        XDOTest xDOTest = new XDOTest();
    }
}

Not too tough right ? With a little effort you can get the whole thing streaming rather than writing to disc, bolt on a call to the Data Template engine to fetch the data, use the FormProcessor to stick multiple outputs together ... the list goes on.

You have been working feverishly on your EBS instance. You have everything working in your test environment ... you have developed twenty shiny new and sparkly templates with all the bells and whistles your end users could ever wish for ... you're now ready for the big time in your production instance. How do you move all of those files and all that supporting metadata to the production instance? You're not going to type it all in again and manually load files, where's FNDLOAD?, wheres the file loader? Don't panic, its there ... its been there since 5.0 just not documented ... sorry.

There is FNLOAD support for moving meta data about and also support for moving those physical files, sample data, templates, schemas, data templates even translations. All but the last two are documented in the latest docs. Check out the 5.6.2 docs here you want Appendix B starting on page 401. The write up is pretty decent  I think. I have to admit that there are two missing pieces.

1. Data Templates - this is pretty straightforward. There is a new lob type called 'DATA_TEMPLATE' so its treated just like any other XMLP file object.

2. XLIFF Files - these are the translation files, again an xml format but they have their own loader called, surprisingly enough 'XLIFFLoader'. It's very similar to the XDOLoader, here's how to use it

 UPLOAD usage : % java oracle.apps.xdo.oa.util.XLIFFLoader UPLOAD 
 -DB_USERNAME <db_username> 
 -DB_PASSWORD <db_password> 
 -JDBC_CONNECTION <jdbc_con_string> 
 -APPS_SHORT_NAME <application_short_name> 
 -TEMPLATE_CODE <template_code> 
 -OWNER <owner> 
 -CUSTOM_MODE [FORCE|NOFORCE] 
 -FILE_NAME <file_name> 


 Parameter Name    Description 
 UPLOAD          : (Mandatory) The first parameter 
 DB_USERNAME     : (Mandatory) Database user name (ex: apps) 
 DB_PASSWORD     : (Mandatory) Database user password (ex: manager)
 JDBC_CONNETION  : (Mandatory) JDBC database connection string (e.g.: ap000sun:1521:dev115) 
 APPS_SHORT_NAME : (Mandatory) 3 letter Application short name (ex: XDO)
 TEMPLATE_CODE   : (Optional) XDO Template code, if not given, it should be indicated 
                   in the header section of the xliff.
 OWNER           : (Optional) Owner of the template. Default is "ORACLE"
 CUSTOM_MODE     : (Optional) [FORCE|NOFORCE] Whether force update, default is NOFORCE 
 FILE_NAME       : (Mandatory) Name of the file to be uploaded 

 % java oracle.apps.xdo.oa.util.XLIFFLoader 
 UPLOAD 
 -DB_USERNAME apps 
 -DB_PASSWORD apps 
 -JDBC_CONNECTION ap000sun:1521:apps115 
 -APPS_SHORT_NAME XDO 
 -FILE_NAME patch/115/publisher/templates/JA/XDOTMPL1_ja_JP.xlf
 



DOWNLOAD Usage : % java oracle.apps.xdo.oa.util.XLIFFLoader DOWNLOAD 
 -DB_USERNAME <db_username> 
 -DB_PASSWORD <db_password> 
 -JDBC_CONNECTION <jdbc_con_string> 
 -APPS_SHORT_NAME <application_short_name> 
 -DS_CODE <ds_code> 
 -TEMPLATE_CODE <lob_code> 
 -FILES_DIR <directory> 
 -SUMMARY_FILE <summary_file> 

Parameter Name     Description 
 DOWNLOAD        : (Mandatory) The first parameter
 DB_USERNAME     : (Mandatory) Database user name (ex: apps)
 DB_PASSWORD     : (Mandatory) Database user password (ex: manager)
 JDBC_CONNETION  : (Mandatory) JDBC database connection string (e.g.: ap000sun:1521:dev115)
 APPS_SHORT_NAME : (Optional) 3 letter Application short name (ex: XDO)
 DS_CODE         : (Optional) XDO Data Definition code
 TEMPLATE_CODE   : (Optional) XDO Template code
 FILES_DIR       : (Optional) Files will be downloaded to the given directory.
                    If not provided, files are placed in the current directory.
 SUMMARY_FILE    : (Optional) If provided, will generate an xml file containing list
                    of files downloaded. This should be the full path name of the target file
                   (not affected by FILES_DIR parameter). 

% java oracle.apps.xdo.oa.util.XLIFFLoader 
 DOWNLOAD 
 -DB_USERNAME apps 
 -DB_PASSWORD apps 
 -JDBC_CONNECTION ap000sun:1521:apps115 
 -APPS_SHORT_NAME XDO 
 -TEMPLATE_CODE XDOTMPL1  

Quick one today, trying to work on the bursting series ...
You have developed Data Templates for your EBS instance, but what about setting the org id. In good ol Oracle Reports you just added the following command in your before report trigger:

srw.user_exit('FND SRWINIT')

and it was all done for you. In the world of BIP we go one step further. The executable that runs your data template sets the org id for you so you do not have to do anything.

Now there is a caveat to this, as long as you are using the BIP delivered executable (XDODTEXE) in EBS to call the data engine to run your data templates you're fine ... if you are accessing the data engine via the java APIs or the BIP Enterprise release then its your responsibility to set the org id before the query executes. The simplest way to do it is to call a pl/sql package in the pre-fetch trigger and in there set the org id:

fnd_client_info.setOrgContext(ORG_ID)

then you're set and you'll get the data you are expecting ... well as long as you wrote the query correctly of course ... been there done that, got the T shirt :o)

The bursting articles are still in process ... I did some work a while back for a training course on the 'anatomy of an invoice template' ... there are some useful 'bits' that I thought you might like to see. 

One of the most requested features on the forum for RTF templates is to show a fixed number of rows per page. Maybe you have pre-printed stationary that can only take a certain number of lines, maybe you have a functional requirement for it. For whatever the reason id the functional folks have given it to you to implement.  Up until now there has been a template floating around that I think I let loose that shows how this can be done for invoices. There is little explanation of whats going on and how it's done. I'll try and make ammends to those of you that may have gotten a little lost but plugged it in anyway and it worked so what the heck.

I have started off small and we'll build this template up into a full invoice format that can run against the standard AR Oracle Report in 11i (RAXINV) ... those of you that are not EBSer's or are not interested in the invoice format, dont worry, you can follow along and apply the same principles to any data set. Here's the features out template is going to have.

1. A fixed number of rows per page,
2. 'Filler' space for lines, because that last page may only have 3 lines but you want to maintain the layout
3. Page totals or 'Continued' in place of a total
4. Last page only content
5. Header and page number resetting as we hit each new invoice in the batch.

For all of the explanations we are going to use the attached XML. Its a full invoice batch, thats multiple invoices having multiple lines.

The Data

Lets start with number 1, for the fixed row enumeration we are only interested in the lines section of the XML, so we can focus on this portion Im going to ignore the rest of the XML. We are only interested in the G_LINES group of the XML structure:

<G_LINES>
 <LINE_NUMBER>1</LINE_NUMBER>
 <LINE_CUSTOMER_TRX_ID>1903</LINE_CUSTOMER_TRX_ID>
 <LINE_CUSTOMER_TRX_LINE_ID>1801</LINE_CUSTOMER_TRX_LINE_ID>
 <LINE_CHILD_INDICATOR>0</LINE_CHILD_INDICATOR>
 <LINE_TYPE>LINE>LINE</LINE_TYPE>
 ...
</G_LINES>
<G_LINES>
 <LINE_NUMBER>2</LINE_NUMBER>
 <LINE_CUSTOMER_TRX_ID>1903</LINE_CUSTOMER_TRX_ID>
 <LINE_CUSTOMER_TRX_LINE_ID>1817</LINE_CUSTOMER_TRX_LINE_ID>
 <LINE_CHILD_INDICATOR>1</LINE_CHILD_INDICATOR>
 <LINE_TYPE>LINE>LINE</LINE_TYPE>
 ...
</G_LINES>

There are obviously a lot more elements and a lot more lines but all we are interested in is, the grouping and the line type for now.

The Template

Now take a look at the template; if you just look at the table and nothing else it all looks pretty normal. We have a for-each, some fields followed by an end for-each ... straightforward stuff right. You'll notice the for-each has a little more going on thou and there's that 'if' condition:

<?for-each:$invLines?>
<?if:position()>=$start and position()<$start+$lpp?> 

I'll come back to these, lets look first at the fields above the table:

LinesPerPageVariable - this does what it says on the can and sets up the number of lines we want to see per page and assigns it to a variable 'lpp'. Now remember native XSL 'variables' are not like other language variables, think of them more as constants.
        
        <xsl:variable name="lpp" select="number(15)"/>

LinesTreeVariable - this holds not just a single value but a complete tree of values. Word of caution here, if you are going to do this you need to be aware that the XSLT engine is going to load this tree into memory ... so do not load huge trees. In our completed invoice template we will only load the lines tree for each invoice. In this example we are loading all the lines in the XML regardless of invoice ... thats OK thou, our XML is small, just be aware of what are doing here.
We have a variable 'invLines' and we are loading the members of the G_LINES group where the TYPE is equal to 'LINE'. Notice we use an XPATH expression to do this. We also use the 'incontext' command to ensure we are picking up the lines only for the current position i.e. within the current invoice. For this example remember we have no invoice header so we pick up all lines into the tree.

          <xsl:variable xdofo:ctx="incontext" name="invLines" select=".//G_LINES[LINE_TYPE='LINE']"/>

FEinvLines - this is the first loop we need to to go over the G_LINES group we created earlier. Although there is only a single group we need to iterate over it so we can set up a variable to hold the starting line position.
The if statement is checking if the record position we have reached modulizing with the line per page count equals zero i.e. we have reached the first record the create a variable (constant) called 'start' and initialize it with '0'.

<?for-each:$invLines?>  
 <?if:(position()-1) mod $lpp=0?>  
   <xsl:variable name="start" xdofo:ctx="incontext" select="position()"/>

Now we get into the table, the first field in their is another for-each but this time we are going to loop over the members of the invLines tree. Notice the use of the '$' to reference the variable.

<?for-each:$invLines?>
 <?if:position()>=$start and position()<$start+$lpp?>

The if statement here is checking that the current record pointer 'position()' is either greater than 'start' ie the first record or less the 'lpp' value we set up earlier. If it is then show the record otherwise not. the rest of the table is standard stuff.

Now we wanted a fixed number of rows per page and the logic above will provide that but of course the template needs to signify the need for a page break after the alloted number of rows have been shown. Looking at the 'Page Break' field:

<xsl:if xdofo:ctx="inblock" test="$start+$lpp<=count($group)">
 <xsl:attribute name="break-before">page</xsl:attribute>
</xsl:if>

We have an 'if' statement wrapped around the page break instruction. This is just like the if in the table, if the specified number of rows has been met then insert a page break.

So thats fixed row enumeration, hope things are a little clearer. Next we'll add in the header for th invoices so we can then ensure we have a fixed layout per page of each invoice. Complete template, data and PDF are here.

Continuing on from yesterday, today we'll add in the repeating header and a filler for the invoice lines. Imagine you have pre-printed stationary, yesterday we tacled how to get X number of lines on each page, now assume that on the last page of the document you need a summary at the bottom of the page. If that last page only has 5 rows of data and we were printing 20 rows on the previous pages then once the 5 rows are rendered the summary will then render directly under the five rows i.e. not at the bottom of the last page ... still with me? With 5.6.2 we introduced the concept of a last page only command; we could use that here but I want to continue with the idea of filler rows and we'll tackle the last page only option later.

Taking a batch invoices as our example we can add the header and the filler rows.

Reset your Head

For the header we want to have say an invoice header with page numbering, etc. As we hit a new invoice we want the header information to change and the page numbering to reset. To do this we can use the '@section' option for the for-each command. We create the header layout in the MSWord header.

Notice we can not put formfields in to the MSWord header section so we either need to type in the commands or use a header template and reference it.
The only extra fields added are:a
FE G_INVOICE - this starts the looping over each invoice using the @section to reset the header information as each new invoice is reached.
         <?for-each@section:LIST_G_INVOICE?>
EFE - this closes out the loop above

Running this template (Inv_Header.rtf) gives us two invoices with a repeating header on each page with page numbering per invoice.

Spaced Out

Now lets add the logic for the filler rows, you can see the new fields and empty table just below the lines table in the Inv_Header_Spacer.rtf template. There are two new fields
Filling Out Space - this contains the logic to check if the number of rows rendered above equals the lines per page variable. If not then insert the table row until it does.
<?if:not(count($invLines) mod $lpp=0) and ($start+$lpp>count($invLines))?>
  <?for-each:$invLines?>
     <?if:position()<$lpp - (count($invLines) mod $lpp)?>

then we have the blank table row, then 
End Filling - this just closes out the for-each and the if statements. 

I just added a static string 'End of padded lines' to check that we are actually padding. Complete samples available here.

Next, page totals and last page only ...

Finishing up on this series we will now add a page total and a region that needs to show only on the last page of the document.

Page Totals - as many of you know we have direct support for page totals. You register a page total object in the template and then place it where its required to be rendered. The total is not actually calculated until the rendering engine starts to layout the data on the page, thats the only point at which the engine knows how many 'line' can fit on the page. In our case we know how many lines we are going to fit because we have fixed the number of rows before the rendering engine starts. We also already have those lines loaded into a variable 'invLines' so calculating a total is going to be relatively straightforward.

<?sum($invLines[(position()>=$start) and (position()<($start+$lpp))]/LINE_EXTENDED_AMOUNT)?>

Here's an XPATH expression again inside the '[ ]', this limits the number of rows we want  to sum. it's like a 'where' clause on our data.

If we wanted the word 'Continued ...' to be shown we test the current record position against the total number of lines for the invoice. If they were not equal then we would show the 'Continued ...' string.

Last Page Only - again we now have native support for this when you do not know how much data is going to fit on a page with 5.6.2. In this case thou, we know how much data is going on the page and can ensure that a section on prints on the last page. Take a look a the template, there is a table to hold the summary/special instructions for the invoice at the end of the template. All we have done is wrap an if condition around that table:

<?if:count($invLines) < $start+$lpp?>

This checks to see if we have reached the end of the invoice lines section and if so then render the summary section i.e. on the last page. Then we close out the if condition. To get the table to appear at the bottom of the last page you'll have notived that we created a two celled table and put the line table in the top cell then the summary in the lower one and then drageed the cell border down the page to position the summary at the bottom of the page.

We now have an all singling all dancing invoice template, the features we added were:

1. A fixed number of rows per page,
2. 'Filler' space for lines, because that last page may only have 3 lines but you want to maintain the layout
3. Page totals or 'Continued' in place of a total
4. Last page only content
5. Header and page number resetting as we hit each new invoice in the batch.

The final template, data and output are available here.

We can now take some of those individual features and apply them to pretty much any other document whether than be a batch of purchase orders or a single standard letter showing outstanding invoices. Hope it's been helpful.

The Oracle Information Architecture Services team got in touch with me today to provide some information on their new BIP consulting services. They have a bunch of information on two BIP areas that they are currently providing.

Oracle Reports to BIP Migration

• White Paper
  
• Presentation

General BIP report development and delivery from E Business Suite, the OCS folks offer a complete end to end implementation. They can also implement bursting solutions for you.

• White Paper
  
• Presentation

If you're interested in either of these services or any other Oracle BI Services contact Sameer Singhal (sameer.singhal@oracle.com) or Kanichiro Nishida (kanichiro.nishida@oracle.com) two of the friendliest guys you'll ever meet :-)

You can burst pretty much any xml from the E Business Suite ... the only wrinkle in this statement is that as of today the 5.6.2 release requires that you write some custom code to call the bursting engine to send the documents to their respective recipients. Things will get a lot easier with the 5.6.3 release with a bursting program coming out of the box. 

To save me some typing and you some reading I have put together a set of screen casts to show how the whole process hangs together. For those of you that use English as a second language ... my apologies, I was as clear as I could - you do not want to hear my French, my German is abysmal , my Portuguese terrible, my Russian non existent, my Japanese ... well lets just say, its in English with what I hope is a clear accent.

End User Experience

Your end users need not change their current business practices, just submit the batch report, the bursting process can be submitted automatically for them - check out their potential experience here.

Development Process

From the graphic above I hope you can see the components we need to carry out the burst. For most of the steps below I have recorded its own screencast, just follow the link for more info.

1. Batch Program- this supplies the XML to be burst, this could be an OReport or a data template. This program can then submit the bursting JCP - Walkthrough  
2. Bursting JCP - this is the java concurrent program that will burst the data, format and deliver it to your recipents. Walkthrough.
3. Template - this is the template for the formatting of the burst data - no recording here but 3 articles disecting starting here.
4. Ctrl File - this is the bursting control file telling the bursting engine how to burst. Walkthrough.

I have also zipped (explanation) all of these components up and made them available here.

Between, this blog entry, the recordings and the user guide you should have enough to get out there and do this yourself. As I mentioned above with 5.6.3 the JCP will come delivered so it will just be a case of building and registering that control file not too long to wait I promise.

Right Im now hoarse from talking to much ... I hope this new medium is a little more interesting than reading my eloquent prose (:-) over a cup of java.

Well, its that time of year again ... Collaborate 07 kicks off in Vegas on Sunday. Its funny I started this blog a short time before Collaborate 06 in Nashville last year; 78 articles and supposedly more than 230,000 hits later and here we are again. Sadly, I cant make the conference this year, lets call it a slight technical hitch. However, there are many sessions going on either featuring XMLP/BIP or its an integral part of a solution especially the EBS, PS and JDE sessions.
If you're going and have an interest in XMLP/BIP try and get along to one of the sessions, even if you are a Quest person and do not think theres much for you, get along to a session, many look like they are generic enough that you'll learn a ton. BIP is coming to a system near you. If you can not make a session, bug the folks down on the demo grounds for a demo. More info at the Collaborate website.

With the 5.6.3 (Apps) and 10.1.3.2 (Standalone/BIEE) release of the BIP core engine you now have the ability to get vertical with your report objects. Its a small enhancement but quite useful ... how many of you receive a documents now with a barcode striped down the side, or maybe you have a table of data, column data is narrow but column names are long, now you can just flip em.

There are some caveats - you can only flip the text or data while its inside a table cell and it only works for PDF and RTF outputs. To set the content to show vertically just
1. Select the cell,
2. Right click > Text Direction
3. Choose the direction you want.



There is another option and that is to use the 'text along a line' feature, this has been around for a while. Just:
1. Draw a line object on the template, can be straight, curve or freehand
2. Right click > Format Autoshape
3. Under the web tab enter: <?shape-text-along-path:YOURTEXT?>

When the output renders your text will follow the line.

Samples here.

I wrote yesterday about all of the great content at Collab07 there will be around XMLP/BIP ... I also mentioned that we would not be there :-(

There has been a reprieve, XMLP will be represented by yours truly. We, thats me and my laptop chock full of demos and 'intersting slides' (I kid you not) will be in South Seas F on Wednesday @4:30 ... looks like there will be plenty of room so join me for an hour of information, demos and news.

Details below, please excuse the meglomaniacal title and over the top session blurb ... we wanted to jump out at readers and pique their interest to find out who the heck is Tim.

Title: 'The Greatest of the Latest Oracle XML Publisher from Tim'

Abstract: Oracle XML Publisher, has fast become a key component of the EBS, Peoplesoft Enterprise and JD Edwards E1 reporting architectures reducing the high costs associated with the development, customization, and maintenance of business documents, while increasing the efficiency of reports management. Oracle XML Publisher is now being implemented in many other of the Oracle Applications family of products such as Siebel and Oracle Retail allowing a new group of Oracle customers to take advantage of this innovative approach to reporting.
This session explores the current and future features and benefits of XML Publisher within each of these applications showing how customers can take advantage of this innovative approach to reporting, packed full of demos, information and news you can use.

Time and Place: Wednesday April 18th @4.30pm in South Seas F

See you there.

I think most folks see the advantages of BIP/XMLP no matter what the flavor. It's still relatively new, but XMLP has actually been around in the E Business Suite for coming up 3 years in May. It's still evolving yes, but the reporting engine itself I think is now proven. We have customers generating more than 10,000 invoices an hour, others generating huge 10,000 page documents and its not just listing reports and invoices, its certificates, shipping labels, marketing campaigns even books.

The common thing across all of these examples is that the layout templates, for the most part have had to be custom developed by IT departments or consultants. The real value add for BIP/XMLP is the templates and those provided out of the box for you the customer by the development teams. In EBS 11.5.10 there are about 200, in PeopleSoft 9 there are a smattering of templates available. For EBS that is about to change ...

With the R12 there are more than 850 templates available across more than 50 products ... I have put together a document (using XMLP of course) listing all the available templates, you can get that here. 'Hang on', I hear you cry what about the other near 2000 reports that you guys ship to us? Good news here too, we currently have a project going on in development to migrate the rest of the Oracle Reports based concurrent programs that you will be able to take advantage of later this year ... so thats going to be nigh on 2500 templates to customize to your hearts content.

What about my reports?

Well with R12 we have released migration utilities to help you migrate your own Oracle Reports to the XMLP platform in EBS. They are documented but here's the document itself. We take your RDF binary, use Oracle Reports to convert it to an XML format, parse that XML and generate a Data Template (extraction), supporting plsql packages and an RTF Template (layout). We are not guaranteeing 100% conversion, you are likely going to need to tweak the layout a little and deploy the plsql but we'll get you a long way along the process of conversion.

What about us 11i folks?

Right now, no plans to backport the templates to the 11i code line ... we need some carrots to get you to upgrade. But the migration scripts will be released with or very soon after the 5.6.3 release is made available. They come with a caveat, you folks use Oracle Reports 6i, we need 9i or higher available to carry out the conversion ... its not a biggy and I'll document the 'how' once 5.6.3 hits the streets.

What about us non-Apps folks?

Manish, fair comment. For those of you that are not in the EBS bucket theres hope for you too. The scripts are planned for release with the 10.1.3.3 release later this year. We needed to do some more work for you to generate you a complete XDO file. Still going to need at least the 9i reports executable somewhere on the disk but the principle will be the same. 

Looking to the future a little, there are plans on the table to provide a Template Bank. This will be a hosted version of the Template Manager with some bells and whistles. The plan is to make all of the development delivered templates available via this application, then hopefully consulting and of course you folks out there building templates. Should be a great big template sharing community!

Hot off the presses ... 5.6.3 for EBS has hit metalink in the last half hour. Get in there and get it downloaded:

5472959 - XDO/ORACLE XML PUBLISHER OA ROLLUP PATCH 5.6.3

Just a few of the new features:

XML Publisher's Template Builder for Microsoft Word is now called Oracle Business Intelligence Publisher Template Builder for Microsoft Word. This release includes a cross tabulation (cross tab) builder, XML schema support, and an enhanced chart builder. New chart types added are: Area, Ring, and 3D Bar. The Template Builder also now supports the application of styles to define the look and feel of your chart. Styles include: April, Autumn, Black and White, Comet, Confetti, Earth, Executive, Financial, Glass, Nautical, Projection, Regatta, Southwest.

XML Publisher provides a utility to facilitate the conversion of Oracle Reports (version 9i and later) to XML Publisher Reports. The conversion is a two-step process requiring a data model conversion and a layout conversion using the following new APIs:

DataTemplateGenerator API converts the Oracle Reports data model to an XML Publisher data template.

RTFTemplateGenerator API converts the Oracle Reports layout to an XML Publisher RTF template.

XML Publisher now supports the Text Direction feature of Microsoft Word that enables you to change the orientation of text in a table cell. You can rotate the text 90, 180, or 270 degrees. This is supported for RTF and PDF output.

Now in addition to the xsl:if statment, you can also use the "It...Then...Else" construct in RTF Templates to define conditional formatting.

The XML output data structure can be defined for a data template now when using REF CURSOR.

The following attributes have been added to handle XML data structures and scalability - include_parameters, include_null_element, xml_tag_case, db_fetch_size, scalable_mode, include_rowsettag, debug_mode

Plus many ( well not that many cos our folks write such good code) bug fixes ... more information can be gotten from the About Document Note 422508.1 on metalink.

I'll dig in a bit more next week ...

I talk about java concurrent programs quite a lot on this blog, if you want have publisher interact with the EBS concurrent manager you typically need to write a java concurrent program (JCP) ... I have been scouring the user docs for EBS and can not find a reference to them ... that leaves you folks stuck. Rather sneakily and maybe lazily I have lifted the 'How-to' doc from the ATG pages ... thanks to Venkat from the Concurrent Processing team. Without further ado ...

Overview

Concurrent Processing provides an interface 遷avaConcurrentProgram?  with abstract method runProgram() which passes the concurrent processing context 舛pContext?. The concurrent program developer will implement all of their business logic for a concurrent program in runProgram(). The main() method, implemented by AOL, will call runProgram() after performing all of the required initialization for the concurrent program, including establishing a database connection, initializing the required contexts, and setting up the log and output files. CpContext will have the request specific log and output file input methods. The class name with the main method will be registered as the java executable file name in the register executable form or by using pl/sql API.

Developer has to register the full package path for the class in register executable form. For example, if wip product creates a class in $WIP_TOP/java/shopfloor/server/BackgroundProgram.class, this is then registered with the package location 双racle/apps/wip/shopfloor/server/BackgroundProgram.class?. Developer will register 腺ackgroundProgram? as the executable file name and 双racle.apps.wip.shopfloor.server? as the package path in the register executable form. Developer has to follow the java notation to register the package path location, ?.? to denote the directory instead of ?/? in UNIX.

Java Concurrent Program parameters must register with token names in the parameter screen of the register concurrent programs form.  Developer can get the value of the parameter for a given token name in the java concurrent program. These token names will be used in passing the parameter values as parameter string  to the concurrent program from the command line for testing the java concurrent program.

Developer should set the status and completion text for the java program by using the setCompletion() method provided by AOL. The runProgram() method should call setCompletion() method to report the program completion status to the Concurrent Manager. The program may set its completion status to Normal, Warning or Error. Completion text is optional.

Steps in writing Java Concurrent Program

Template Program:

Copy the template Java Concurrent Program from $FND_TOP/java/cp/request/Template.java to your directory and start coding according to your requirement. Change file name and class name to your required name as Java Concurrent Program name.

================= Template.java=========================== 
package oracle.apps.fnd.cp.request; 
// Change the package name to the required one. 
// import all the other required classes/packages. 
import oracle.apps.fnd.util.*; 
import oracle.apps.fnd.cp.request.*; 
// Change the name of the class from Template to your 
// concurrent program class name 
public class Template implements JavaConcurrentProgram 
{ 
   /** Optionally provide class constructor without any arguments. 
    *  If you provide any arguments to the class constructor then while 
    *  running the program will fail. 
    */ 
   public void runProgram(CpContext pCpContext) 
   { 
        ReqCompletion lRC = pCpContext.getReqCompletion(); 
        String CompletionText = ""; 
        /* Code your program logic here. 
         * Use getJDBCConnection method to get the connection 
         * object for any JDBC operations. 
         * Use CpContext provided commit,rollback methods to
         * commit/rollback data base transactions. 
         * Don't forget to release the connection before returning
         * from this method. 
         */ 
  
        /* Call setCompletion method to set the request completion
         * status and completion text. 
         * Status values are ReqCompletion.NORMAL,ReqCompletion.WARNING, 
         * ReqCompletion.ERROR. 
         * Use Completion text message of length 240 characters.
         * If it is more than 240 then full string will appear in 
         * log file and truncated 240 characters will be used as 
         * request completion text. 
         */ 
         lRC.setCompletion(ReqCompletion.NORMAL, CompletionText); 
    } 
} 
==================End of Template.java===========================

program Logic

Implement the runProgram with your Java Concurrent Program business logic. runProgram() gets the CpContext .  CpContext is a subclass of AOL/J AppsContext which provides the request specific member classes LogFile to write to request log file, OutFile to write to request output file and ReqCompletion to set the completion status of the request.

Program Parameters
CpContext uses the AOL/J utility Parameter List to pass the parameters to the Java Concurrent Program.
Please refer AOL/J Parameter list to get the Parameter List name, value pairs. You will be referring parameter list name as the parameter name and corresponding value as the parameter value in the Java Concurrent Program. You have to register the parameter names as token name in the register concurrent program form Parameter screen.

Database Operations
Use getJDBCConnection method to get the connection object for any JDBC operations within program and release the connection to the AppsContext connection pool. Use CpContext's commit(), rollback() methods to commit or rollback the transactions in the database. These methods will set the program proffered rollback segments after commit or rollback segments for the rest of the database transaction in the Java Concurrent Program.

Setting request Completion Status
Call setCompletion() method of the ReqCompletion object which is a member of CpContext before returning from your Java Concurrent Program to set the completion status for the program and optional completion text.
 

Register executable

Register your Java Concurrent Program class name as execution_file_name and package name in execution_file_path in register concurrent executables form.

Register Concurrent Program

Register you Java Concurrent Program as concurrent program in the register concurrent program form. Register all the parameters that you want to pass to the program in the parameter screen of this form. Register the Parameter List names referred in the program as the token names in the parameter screen.

Test Program from OS Prompt

You can test your Java Concurrent Program from OS prompt by using the following syntax:

jre -Ddbcfile=<dbc filename with full path>
            [ -Drequest.logfile=<logfile name> ]
            [ -Drequest.requestid=<request id> ]
            [ -Drequest.outfile=<output file name> ]
            [ -Drequest.userid=<user id>  ]
            [ -Drequest.respapplid=<resp appl id> ]
            [ -Drequest.respid=<resp id> ]
            [ -Drequest.secgrpid=<sec grp id> ]
            [ -Drequest.enabletrace=<Y/N> ]
            oracle.apps.fnd.cp.request.Run <program/class name>
            [<parameters>]

     Example:
     jre -Ddbcfile=/d2/fnd/secure/appsnode_appdb.dbc
          -Dreqeust.requestid=23453 -Drequest.logfile=./myreq.log
           oracle.apps.fnd.cp.request.Run oracle.apps.wip.program.MyProg
           "TOKEN1=value1:TOKEN2=value2"

If you don't specify the 'request.logfile' with -D option then the all the log file information will be printed to the standard output. Specify the 'request.requestid' to rerun already completed request. You can specify the all the application user specific context values with -D to get the specific user context to the program when run from the OS prompt. Pass all the parameters if any by using the AOL/J Parameter list syntax.

Sample Program

package oracle.apps.fnd.cp.request; 
import java.io.*; 
import java.sql.*; 
import oracle.apps.fnd.util.*; 
public class AvailableProg implements JavaConcurrentProgram 
{ 
   String applName; 
   public AvailableProg() 
   { 
        // if no parameter value is specified for 
 // APPLNAME then use FND as default value 
        applName = "FND"; 
   } 
   public void runProgram(CpContext pCpContext) 
   { 
        // get the JDBC connection object 
        Connection mJConn = pCpContext.getJDBCConnection(); 
        // get parameter list object from CpContext 
        ParameterList lPara = pCpContext.getParameterList(); 
        // get ReqCompletion object from CpContext 
        ReqCompletion lRC = pCpContext.getReqCompletion(); 
        String lQuery = 
                " select substr(user_concurrent_program_name,1,70) , " + 
                " decode(enabled_flag,'Y','Enabled','N','Disabled') " + 
                " from fnd_concurrent_programs_vl cp, fnd_application_vl a " + 
                " where cp.application_id        = a.application_id " + 
                " and a.application_short_name = ? " + 
                " order by 1 " ; 
        // check for the APPLNAME parameter token name and if it there get 
        // value and use it as the application short name in the query 
        while (lPara.hasMoreElements()) 
        { 
                NameValueType aNVT = lPara.nextParameter(); 
                if ( aNVT.getName().equals("APPLNAME") ) 
                        applName = aNVT.getValue(); 
        } 
        try 
        { 
           PreparedStatement lStmt = mJConn.prepareStatement(lQuery); 
           lStmt.setString(1, applName ); 
           ResultSet lRs = lStmt.executeQuery(); 
           // get OutFile object from CpContext 
           OutFile lOF = pCpContext.getOutFile(); 
           // get LogFile object from CpContext 
           LogFile lLF = pCpContext.getLogFile(); 
  
           lLF.writeln("Generating Programs for Application : "  + applName, 
                      LogFile.STATEMENT); 
           lOF.writeln( 
           "                Available Concurrent Programs for Application " + 
                         applName ); 
           lOF.writeln( 
           "Concurrent Program Name                             Enabled"); 
           lOF.writeln( 
           "----------------------------------------------" ); 
           while( lRs.next() ) 
           { 
                lOF.writeln(lRs.getString(1) + "   " + lRs.getString(2) ); 
           } 
           lLF.writeln("Generated Programs for Application : " + applName, 
                        LogFile.STATEMENT); 
           lStmt.close(); 
           lRC.setCompletion(ReqCompletion.NORMAL, "Request Completed Normal"); 
        } 
        catch (SQLException e) 
        { 
              lRC.setCompletion(ReqCompletion.ERROR, e.toString()); 
        } 
        finally 
        { 
              pCpContext.releaseJDBCConnection(); 
        } 
    } 
}

NOTE: If you're creating a BC4J Application Module in a concurrent program and are passing CpContext object to it, you should NOT call releaseJDBCConnection() on CpContext while it is still in use by the BC4J AM. You should clean up after the AM properly by calling am.remove().

Some Q & A

Q. How can I run my Java Concurrent Program from OS prompt?
A. To run a Java Concurrent Program from the OS prompt the syntax is as follows:
Please note that there is no newline between different -D options and arguments to jre.

jre -Ddbcfile=<dbc filename with full path>
       [ -Drequest.logfile=<logfile name> ]
       [ -Drequest.requestid=<request id> ]
       [ -Drequest.outfile=<output file name> ]
       [ -Drequest.userid=<user id>  ]
       [ -Drequest.respappid=<resp appl id> ]
       [ -Drequest.respid=<resp id> ]
       [ -Drequest.secgrpid=<sec grp id> ]
       [ -Drequest.enabletrace=<Y/N> ]
       oracle.apps.fnd.cp.request.Run <program/class name>
       [<parameters>]

Example:
jre -Ddbcfile=/d2/fnd/secure/appsnode_appdb.dbc
     -Dreqeust.requestid=23453 -Drequest.logfile=./myreq.log
      oracle.apps.fnd.cp.request.Run oracle.apps.wip.program.MyProg
      "TOKEN1=value1:TOKEN2=value2"

Q. Can I use Constructor for my Java Concurrent Program class?
A. You can use the constructor without any arguments to it.
Example:
class MyProg implements JavaConcurrentProgram
{
  // DO NOT USE MyProg(String arg1, int arg2...)
   public void MyProg()
   {
       ...
    }
   public void runProgram(CpContext pCpContext)
   {
     ....
    }
}

Q. Can my JCP class Constructor accept arguments?
A. NO, do not use any class constructor with arguments. Refer above Example.

Q. How to pass parameters to my Java Concurrent Program?
A. Java Concurrent Program uses AOL/J Parameter List utility APIs for parameter management. To pass parameters to Java Concurrent Program you have to  register those parameters with token name by using the "Register Concurrent Program". In the JCP you can access the registered token name in the AOL/J ParameterList API and get corresponding value to get the value of the parameter. Refer AOL/J ParameterList API for more information.

Q. Can I use different output file name than the standard name?
A. Yes, you can use different output file name by calling the setOutFile method of OutFile class.

Q. What will happen if I don't call setCompletion method of ReqCompletion?
A. Your request run by using this program will complete with error.

Hope its useful ... I have a copy of the javadoc posted here.

I have posted about the new server side versions of BIP (10.1.3.2.x) and EBS (5.6.3) being available now ... what I neglected to mention was the availability of a new version of the Template Builder for Word. There are some new enhancements for both BIP and EBS user alike:

• Crosstab Wizard




• Multiple Levels


• Level and report Totals


• Enhanced Chart Dialog




• Multiple Line Charts


• 3D Charts with gradient fills


• Real time preview


• Table wizard


• Translation testing support


• Template Validation Checker

Those of you using BIP Server you get a few more bells and whistles. Rather than download sample data to your desktop and manually load it to the template builder, you can now connect to the BIP server, find a report, create a new template or modify an existing one, test and then upload to the server from the builder ... makes the whole development process much smoother. 

You can get the new version from the OTN download page, http://www.oracle.com/technology/software/products/publishing/index.html

Get Templating !

 

I covered what I thought were the tougher 2D barcodes a while back , we documented the simpler codes (and the 2D's for that matter ... same principle) but I think because we decided to break the doc into two sections, one for the template designer and another for the developer I think we confused things a little and caused a disconnect for most folks. So to make ammends until we revamp the docs. Today, I'll cover the simple stuff ...

Simple Barcodes

If you are using something like a code 39 barcode it may not require any pre-processing on the data to be 'coded'. This is the simplest form of barcoding. Firstly you need to build and test on your desktop. Here are the steps:

1. Install the barcode font in your WINDOWS_HOME/fonts directory
   
2. In MSWord highlight the field/data you want to have the barcode applied to
   
3. To test the output you need to let the publisher engine know where the font is on the desktop. Under the cleint install directory you'll find a config directory and under there an 'xdo example.cfg' file. Open it up and you'll see a sample entry for a font:
   
   <font family="GnuMICR" style="normal" weight="normal">
    <truetype path="C:\WINNT\fonts\GnuMICR.ttf" />
   </font>
   
   
4. Just change the entry to point to your font and for the family attribute pput in the name you see in Word when selecting the font.
   
5. Rename the file to xdo.cfg
   
6. Test your template, you should see the barcode font being used to replace the data in the output 

Now you need to deploy to the server. The template is simple enough, but we need the server publisher engine to know the location of the font. For both EBS and standalone there is an interface to load the font to the server, the steps are now pretty straightforward. In EBS just use the Font Manager, I covered how to load fonts in this article. For standalone:

1. On the operating system copy the font to the JDK/fonts directory
   
2. Login as an administrator and go to Admin tab > Font Settings
   
3. Now fill the details out about your font and select the font file from the drop list.
   
   
4. The font is now available to all templates in the system.

We have had several requests on how to write log messages while publisher is processing a job. Its not a published API ... it ought to be but for now ...

We have a class you can use in your java code called Logger, just use the following import:

import oracle.apps.xdo.common.Logger;

General Usage

// Log a message 
Logger.log("Copying string from buffer xyz to buffer zyx.", Logger.STATEMENT); 
// Log a message with the current object 
Logger.log(this, "Copying string from buffer xyz to buffer zyx.", Logger.STATEMENT); 
// Log an exception Logger.log(exception); 
// Log an exception with level. 
Logger.log(exception, Logger.EXCEPTION); 
// Log an exception with level and the current object 
Logger.log(this, exception, Logger.EXCEPTION); 

Logging level

• UNEXPECTED
  
• ERROR
  
• EXCEPTION
  
• EVENT
  
• PROCEDURE
  
• STATEMENT
  
• OFF

// Set the system logging level to EXCEPTION * 
Logger.setLevel(Logger.EXCEPTION); 
// This log message will not be logged because the message 
// logging level is lower than EXCEPTION 
Logger.log(this, "Copying string from buffer xyz to buffer zyx.", Logger.STATEMENT); 
// This log message will be logged because the message 
// logging level is the same or higher than EXCEPTION
Logger.log(this, "Copying string from buffer xyz to buffer zyx." , Logger.EXCEPTION); 

How do I get to the log?

Standalone - In standalone versions (10.1.3.2) you can turn logging on for the install via the Admin > Server Configuration page. Here you can turn the logging on or off.

EBS - this is a little more involved to get the logging running ...

1. Create a file called xdodebug.cfg
2. The contents of the file should be:

  LogLevel=STATEMENT 
  LogDir=c:temp 
 
  LogLevel specifies the level of logging, this should always be set to STATEMENT to maximize the information provided in the log.
  LogDir specifies where the debug files should be written to, this needs to be a writable directory for XMLP.

3. This file should then be saved to the $JRE_TOP/lib directory.

Once you have the logging turned on you can then run the process thats causing issues and your messages will be written to the log. Just remember to turn it off afterwards.

Now get loggin'

As regular readers will have noticed we have a new look and feel ... playing with CSS and templates has been 'interesting' ... still, may prove useful elsewhere. I have to thank Martin from the ATG Doc Tech team for the banner image ... I have somewhat mangled the original into a montone format that really does not do the original justice, an awesome picture of Oracle HQ. Check out his other pictures on Flickr, some great photos. 

The useful part of this post, Martin's image aside, is the fact that I have added an email feed for the blog. You no longer need come here every day to check for an update, you can get a mail whenever we have something new. Just fill out the 'Get a BIPScription' box on the right hand side bar with your email, confirm the return email and you're all set, BIP updates will be coming to an inbox near you.

Charts ... the bane of my templating life ... our chart dialog is getting better and better but you're still going to need to tweak things that we are just not going to get to anytime soon. Case in point (thanks for the question Neida), I have a pie chart that looks like this:

but I want this

See the percentage values have been rounded up, you'll laso notice that the actual chart is bigger in its frame (yes the image is larger but the chart itself fills the plot area better), its not a trick, because the labels take less room then the actual chart can fill the space. We're concerned with the rounding for this post. Its not the XML data that we need to round but we need to instruct the charting engine to do the rounding for us on the calculated pie slice values. I had to dig and dig, I have written in the past about the graph.dtd and trying to decode it using an XML editor and fooling JDeveloper into helping us visually but for this little gem I thankfully found the answer on the Oracle Reports graph FAQ.

http://www.oracle.com/technology/products/reports/htdocs/faq/Graph_FAQ_with_style.html

The solution for this particular issue is to override the default slice labels thus: 

<SliceLabel>
<ViewFormat decimalDigit="0" decimalDigitUsed="true" decimalSeparatorUsed="true" />
</SliceLabel>

Please, if you have charting questions take a look see here, there are some 40 questions and answers and you can use most of them in your charts. If you can not find the answer drop us a message on the forum and we can help. 

Ever since we first released BIP in 2004 or what was back then, for the old schoolers amongst us, XMLP I have been asked, 'Where's the training?'
We were a little stymied by our VP telling folks that they did not need training, XMLP was/is that easy. For the most part, its true, but many people like some hand holding, beginners docs, etc - Im in that camp. So began an uphill battle (maybe too strong a word) to get an XMLP training course from Oracle University. Its finally arrived and new locations are being added constantly, check out the OU site for more details:

Oracle XML Publisher Fundamentals  - Locations

The development team have had a lot of input and Bill Sawyer, one of our best curriculum writers has put together a great course which is informative and best of all useful. A high level agenda:

I got an update from Leta on a more updated agenda

Introduction 

• Understand the course agenda


• Describe the Oracle XML technology stack


• Describe the uses of Oracle BI Publisher


• Explain the limitations of classic tools


• Describe the advantages of Oracle BI Publisher


• Describe the basic structure of Oracle BI Publisher

Oracle BI Publisher ArchitectureDescribe the components of Oracle BI Publisher

• Describe the architecture of Oracle BI Publisher


• Describe the technology of Oracle BI Publisher

Introduction to XML Standards

• Describe XML, XSL, and other X Standards objects


• List the components of an XML document


• Create a well-formed XML document


• Describe XML namespaces


• Describe a DTD


• Describe XML Schema


• Describe XML Path Language (XPath)


• Describe XSL and XSL Transformations (XSLT)

Installing Oracle BI Publisher Desktop

• Install Oracle BI Publisher Desktop

Introduction to Oracle BI Publisher Desktop

• Create and use a basic RTF template for Oracle BI Publisher using Oracle BI Publisher Desktop

RTF Templates

• Explain the capabilities of an RTF template in Oracle BI Publisher


• Describe the two methods to create Oracle BI Publisher RTF templates

Advanced RTF Template Techniques

• Describe the advanced capabilities of  RTF templates

Other Templates

• Describe the basics of a PDF template for Oracle BI Publisher


• Describe the advanced capabilities of PDF templates for Oracle BI Publisher


• Describe the basics of an eText template in Oracle BI Publisher

Installing Oracle BI Publisher Enterprise

• Install Oracle BI Publisher Enterprise 5.6.2


• Install Oracle BI Publisher 5.6.2 patches for an E-Business Suite instance

Oracle BI Publisher Enterprise Administation

• Administer the Oracle BI Publisher Enterprise

BI Publisher Enterprise

• Access the Oracle BI Publisher Enterprise


• View and schedule reports


• Create a new report

Translating Reports

• Describe the options for translating reports using BI Publisher.


• Create the translation files using BI Publisher Desktop.


• Create the translation files using BI Publisher Enterprise.

Building a Data Template

• Understand the XML Publisher data template


• Create a data template


• Associate that data template to a report

The E-Business Suite Development Process

• Understand the process of creating an BI Publisher solution


• Use an existing Oracle Report as an XML source for XML Publisher


• Create a data definition registration


• Create a template registration


• Upload a template


• Run an BI Publisher report

There is something there for EBS and BIP Enterprise users alike. Those of you looking for JDE and PEopleSoft training, its in the works and wiil be found on their respective training sites.

I have to say, because of the long wait for the course many of you have gone out and got stuck in and learnt it yourselves. Its called XML Publisher Fundamentals - if you know how to add a barocde, or know what this command will do in a template, <?for-each:INVOICE_LINES[TYPE!='TAX'] or you know what the OPP in EBS or you already understand how to add a BIP report to a BIEE dshboard then I think you are beyond this course. Its an excellent introduction to the technology. Its now partially my responsibility to get more advanced content up here on this blog and onto OTN ... any requests?

For those of you using EBS either 11i or R12 there is now patch available for the Template Builder for MS Word.

5887917      ORACLE XML PUBLISHER DESKTOP PATCH 5.6.3

You can download it from metalking for the Windows 32 bit platform. I covered some of the new functionality in a previous post.

For those of you using Peoplsoft or JD Ewards you should be OK using it, there is nothing in the builder that would not work once deployed to the server. If you folks do not have access to metalink you can get the builder from the OTN download page.

I have seen several threads lately on the forum asking along the lines of -  Where the heck are the checkboxes on my report that I included in my template? Well, they are there they are just hidden, sort of. When you add a check box field to a template the Publisher engine interprets them correctly i.e. should they be checked or not, but the problem is, that none of the standard PDF fonts or the fonts publisher ships have a decent checkbox glylph. Therefore, if you're lucky you get a filled/unfilled diamond shape (if the publisher fonts are accessible) or you get nothing at all.

There is a solution to all this and its down to configuration, if you let the engine know where the checkbox font is and which glyphs represent 'checked' and 'unchecked' then it will pick them up and use them in your output.  

The method to use to configure the font and glyphs is going to depend on your platform. I tested with the Wingdings ttf font and used characters 253 (unchecked) and 254 (checked).

To get the right glyph number:
1. Open MSWord and use Insert->Symbol.
2. Now find the font you want
3. then the glyphs you want, the glyph characgter code will be in the bottom right hand corner of the dialog.

Dont forget to ensure you are licensed to use the font you choose for the check box glyphs.

Config File - EBS (pre-5.6), JDE, PS and if your using the APIs

Your documentation should tell you where the config file should located and what to put in it. For the check boxes you need:

1. The font location, we need to tell the publisher engine where the Wingdings font is located:

<font family="Wingdings" style="normal" weight="normal">
 <truetype path="c:\windows\fonts\wingding.ttf"/>
</font>

2. The glyphs to be used. Here we specify the font family name ie 'Wingdings' and the 253/254 glyphs

<property name="rtf-checkbox-glyph">Wingdings;253;254</property>

Config Manager - EBS (5.6 and above)

1. Load the font to the manager - thats been covered here
2. Update the 'Characters used for checkbox' field with the values you want ie Wingdings;253;254

Now when you run outputs to PDF you will see your checkboxes as expected and no longer be boxless.

I found a set of articles on XMLP in EBS from Barry Goodsell on the Oracle Contractors Network blog 

Using XML Publisher in eBusiness Suite (Part 1)

Using XML Publisher in eBusiness Suite (Part 2)

Using XML Publisher in eBusiness Suite (Part 3)

A good series, I have a few comments but definitely worth the read.

Refreshed from 3 daze spent in our yard with one of the biggest landscaping projects I have ever undertaken ... we are building a 16 foot round pond (read swimming pool for the local dog population) with adjoining stream and waterfalls. I jumped into our internal mailing list with some relief, everything aches you see from wielding a shovel for 8 hours straight yesterday.

A good question came up over the weekend that one of our fantastic support reps, Pieter had jumped on. The jist of the question was that someone had developed a concurrent program with a specific name, 'Posting Single Ledger' using a data template and a layout template. Upon opening the PDF output from the concurrent program screen and trying to save Acrobat offered to save the file as 'FNDWRR.exe' rather than 'Posting_Single_Ledger290507' i.e. the concurrent program name with the run date appended.

Pieter rightly pointed out that, the problem with Publisher related requests is, that these are for PDF output files and are opened inline through the browser. Even though the FNDWRR.exe passes the correct filename to the browser, it is the Adobe Acrobat Reader that decides to make FNDWRR.exe the file name and this is beyond our control. From an Apps point of view, we're sending the following HTTP header along with the PDF document:

HTTP/1.1 200 OK
Date: Tue, 29 May 2007 12:45:18 GMT
Server: Oracle HTTP Server Powered by Apache/1.3.19
Content-disposition: inline; filename ="Concurrent_Program_Details_Rep_290507.pdf"
FNDFS-Error: 0
Content-length: 104838
Keep-Alive: timeout=15
Connection: Keep-Alive
Content-Type: application/pdf

In the latest template builder release there is a very neat feature that we have been trying to implement for quite a while. Some of you may know that with later versions of MSWord when you click File > New, you get a task bar on the right hand side that allows you to search for 'Templates on Office Online'. This allows you to search for templates from the MS website and then download them to Word ... et voila you have a layout ready to go.

Now all you need to do is 'markup' the template with some data fields. Of course the data needs to match the functional purpose of the layout. Single header fields are simple enough, just use the Insert Field dialog to drop fields into the template.

The key feature is being able to drop the detail or 'line' fields and then wrap the grouping tags for you. You can now drop the line fields into the table row, then highlight the row and use Insert > Repeating Group.

You can then select the grouping level you want to wrap around the fields. Of course you will need to know the grouping level you wish to wrap buts its a good step forward and opens up the tool to those folks less knowledgeable about the data i.e. your funkies. They can now download a template, mark it up, test and deploy very quickly.

Another question on the forum that deserves a bit more than a 5 line answer. Neeraj has been trying to merge PDF documents using the Publisher APIs. From the question I think he has merged the docs but he wants to add a security password on top of the merged docs too.

I am trying to merge multiple pdf document and want to secure the output using pdf security attributes.
Is there a way to achieve this while pdf merge ?

(I am able to do so while generating the pdf using FO Processor but not while merging existing pdf files.)

There are several ways to merge PDFs using APIs, the simplest is the PDFDocMerger API. Its a pretty simple API, just get the documents to be merged into an array, specify the output file and then call the process method.

package pdftester;


 
import java.io.File;
import java.util.Properties; 
import oracle.apps.xdo.XDOException; 
import oracle.apps.xdo.common.pdf.util.PDFDocMerger;


 public class PDFMerger {
    public PDFMerger() {
    
    // Output destination of the merged PDF document.
    File outFile = new File("c:\\temp\\mergePDF.pdf");     
 // PDF documents to be merged.
    File[] inpFiles = new File[2];
    inpFiles[0] = new File("c:\\temp\\1.pdf");
    inpFiles[1] = new File("c:\\temp\\2.pdf");
     // Create PDFDocMerger instance.
    PDFDocMerger pdfMerger = new PDFDocMerger(inpFiles, outFile);
     // Set the security property
    Properties prop = new Properties();
    prop.put("pdf-security","true");
    prop.put("pdf-open-password","welcome");
    pdfMerger.setConfig(prop);
       
    // Run the merging process.
    try {
         pdfMerger.process();
    
        } catch (XDOException e) {
            e.printStackTrace();
        }
    }
     public static void main(String[] args) {
        PDFMerger pDFMerger = new PDFMerger();
    }
}

Neeraj wanted to add a password to the resulting document. We can us the setConfig method to set the appropriate properties to set the password.

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

Of course you can fetch that password from some repository rather than hard coding it.
If you want to have a go, you can get the PDFs and the java from here.

Nope, it's not a new feature of publisher, sorry, we can't help with your persona but we can help with your template images.

Most of you know about the 'dummy' image approach to putting images/pictures into your templates. You insert a dummy image into the template and then in the properties dialog for the image navigate to the Web tab and use the following command:

url:{檀ttp://www.oracle.com/images/ora_logo.gif筑

This works great as long as the dummy image dimensions match or are of the same aspect. If not then you are going to get some interesting images at best. Not the best way to introduce your company to your clients with a whacky image on your invoices that looks only vaguely like your logo.  

You have another option, that is to add a field in the template that will call the image in directly. Its a piece of XSL rather than a friendlier publisher expression but when needs must. Its fairly straight forward but must be embedded inside a formfield.

<fo:external-graphic src="http://oracleimg.com/admin/images/ocom/hp/oralogo_small.gif"/>

There is a width and height attribute that you can set if you wish but leaving them out will result in the image being brought in as is.

This will pull the graphic in via a URI, in this case a URL. Remember if you're pulling via a URL then ensure you take care of any proxy settings required to get out of your intranet.

Got this great question today ... 

Is it possible to check whether a document generated by BI publisher fits 1 page?
If it's more than one page then we want to reduce the font until it fits on a single page.

Its a nice question to which I can quickly answer 'yes - potentially' - but before I could really say 'yes' thou, I needed to put my money where my mouth was and prove it. Finally, I have put together 10mins here and there to get something that works. Its a simple approach and will probably need some more work for production but the solution shows off some of the APIs we have and how they can be used to solve this problem.

From 10,000 feet

Getting the answer to the first part can be done quite simply but only after the final document has been created. Sadly, you can not use a nice 'if' statement in the template to check if you have more than one page and if so then reduce the font size until it fits. Page numbers can not be determined until the rendering engine has done its stuff and laid the data on the page. So it has to be a post generation check.

We need a flow such as

1. Set font size to X
        |
2. Generate Output
        |
3. Test page numbers
        |
4. If page number > 1 
        |
5. Set font size  X=X - 2 or some other number
        |
6. Goto 2

7. else End

Its a nighmarish BASIC program from my distant youth ... arrrrgggghhhh!

From about 12 inches

Lets get step 3 out of the way first cos its the easiest - there is an API we can use to count the number of pages in a PDF document.

Under the FormProcessor API there is a method getPageNumber(). We are going to use it on a completed document, we need to use the setTemplate method and pass it our completed document.

    int numPages;
    FormProcessor fp = new FormProcessor();
    fp.setTemplate("c:\\temp\\1.pdf");
    try {
            numPages = fp.getPageNumber();
            System.out.println("Number of pages: "+ numPages  );
       
        } catch (Exception e) {
            e.printStackTrace();
        }

Straightforward stuff really but as I said thats the easy piece. The other part to this is to change the font size in the template until it fits on a page. I have been playing with a sample template but have put it aside for now. If we had written an XSLFO template by hand we could easily use a variable in the template for the font size and pass that each time. But we are using RTF templates, so we need some logic to update/override the font-size attribute, remember it will have been set when you create the layout in the template. Im not even sure a template can be written that updates itself during processing ... if there are any real XSLT experts out there let me know and I'll post the solution. It's going to be simpler ...

The other steps ...

For now I have dodged the issue and use a parser to look for the relevant font-size attribute and update it to its current value -2 in the generated XSLFO template file from the RTF template.

So our java logic for the whole process will be :
1. RTF -> XSL
2. XSL+XML data - > PDF
3. Count pages
4. If > 1 page then use a parser to find instances of 'font-size' and 'height'. Assign initial value found to a variable, then reduce this by 2 points for all values.
5. With the new XSL+XML -> PDF
6. Retest page numbers and repeat as necessary.

The java class I have written is not perfect but I think you can easily use it as a start for a full solution. Once the intial XSL has been generated and the resulting PDF document tested for page numbers it then parses the XSL template using a DOM parser. This looks for the 'font-size' and 'height' attributes under the 'inline' elements and knocks them down by 2 pts.

There are surely going to be templates where reducing the font-size and height are not going to be enough but if you keep things simple you can do it. You could even get into scaling images as well so everything remains in proportion. My template is simple and heres where you may need to modify the class to handle some of the other 'height' and 'font-size' attributes if present. Here's the code with some annotation:

package xdotestbed;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
 
import java.io.OutputStream;
 
import oracle.apps.xdo.XDOException;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.FormProcessor;
import oracle.apps.xdo.template.RTFProcessor;
 
import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.parser.v2.XMLElement;
 

import org.w3c.dom.Document;
 
import org.w3c.dom.NodeList;
 
import org.xml.sax.SAXException;
 
public class FitSinglePage {
    //declare class variables
    String rtfTemplate;
    String xslFile;
    String pdfFile;
    String xmlData;
    XMLDocument newDoc;
    
    public FitSinglePage(String rtfF,String xslF,String xmlF,String pdfF) {
        //Assign program parameters
        rtfTemplate = rtfF;
        xslFile = xslF;
        xmlData = xmlF;
        pdfFile = pdfF;
        
        //Initial RTF -> XSL conversion
        processTemplate(rtfTemplate);
        // Initial PDF generation using XSL above
        generateOutput(xslFile,xmlData,pdfFile);
        
        // Wrapping a loop to stop the process running away
        //if it can never fit on a single page
        
        for (int x=0; x<11; x++){
        //Test the number of pages of the resulting PDF,
        //keep going until it fits 1 page
        
        while (countPages(pdfFile) != 1) {
            // Assign the updated xslFile to an XMLDocument instance
            newDoc = parseTemplate(xslFile);
            // Write out the template to the same file name
            writeTemplate(newDoc);
            // regenerate the PDF document with the adjusted
            //font size and height settings
            generateOutput(xslFile,xmlData,pdfFile);
           }
        System.out.println("Success!");
        break;
    }
    
} 
    
public void generateOutput(String xslFileLoc,String xmlData,String pdfFile){
    // This method will generate the PDF output each time
    
    FOProcessor fop = new FOProcessor();
    fop.setData(xmlData);
    fop.setTemplate(xslFileLoc);
    fop.setOutput(pdfFile);
    fop.setOutputFormat(FOProcessor.FORMAT_PDF);
    try {
        fop.generate();
    } catch (XDOException e) {
        e.printStackTrace();
    }
 
}
public void processTemplate(String rtfFile) {
    // Only called once to create the initial XSLFO template
    // from the RTF template
        try {
            RTFProcessor rtfP = new RTFProcessor(rtfFile);
            rtfP.setOutput(xslFile);
            // this prevents the processor generating attribute sets
            // You could allow it but it would require changes to the
            //the parser code
            rtfP.setExtractAttributeSet(RTFProcessor.EXTRACT_DISABLE);
            rtfP.process();
        } 
        
        catch (XDOException e) {
            e.printStackTrace();
        }
        catch (IOException ioe){
            ioe.printStackTrace();
        }
    
    }
 
    public void writeTemplate(XMLDocument newTemplate){
    
    //Write the updated XMLDocument to the XSLFO template file
    
    OutputStream os;
    try {
        os = new FileOutputStream(xslFile);
        newTemplate.print(os);
        os.close();
         } 
    catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    catch (IOException ioe){
        ioe.printStackTrace();
    }
       
}
 
public static void main(String[] args) {
        
FitSinglePage fitSinglePage = new FitSinglePage(args[0],args[1]
                                                        ,args[2],args[3]);
    }
 
public XMLDocument parseTemplate (String templFile){
       // Parse the XSLFO template method
        DOMParser dp = new DOMParser();
        try {
            InputStream inp = new FileInputStream(templFile);
            dp.parse(inp);
            inp.close();
        } 
        catch (SAXException e) {
            e.printStackTrace();
        }
        catch (IOException e) {
             e.printStackTrace();
        }
        XMLDocument tDoc = dp.getDocument();
        //Grab all instances of the 'inline' element and their children
        NodeList ns = tDoc.getDocumentElement().getElementsByTagNameNS
                             ("http://www.w3.org/1999/XSL/Format","inline");
        XMLElement attrVal;
       //Loop thru the inline elements
        for (int i = 0; i < ns.getLength(); i++)
    {
        attrVal = (XMLElement)ns.item(i);
      //Change the font Sizes
       if (attrVal.getAttribute("font-size").indexOf("pt") != -1)
       { 
        //System.out.print("Number: "+i +"::" 
                         //+ attrVal.getAttribute("font-size")+"\n");
        //Get the font size value e.g. 12.0pt
 String fontSize = attrVal.getAttribute("font-size");
        
 //Strip out the 'pt' part to leave a number e.g. 12.0
 String fontVal = fontSize.substring(0,fontSize.indexOf("pt"));
        
 //Set the new value and add the 'pt' back in e.g. 10.0pt
 attrVal.setAttribute("font-size",
 (Double.parseDouble(fontVal)-2) +"pt");
        
 //System.out.print("Number: "+i +"::" 
 + attrVal.getAttribute("font-size")+"\n");
        } 
        // Change the row heights
        if (attrVal.getAttribute("height").indexOf("pt") != -1)
        { 
         //System.out.print("Number: "+i +"::" + attrVal.getAttribute("font-size")+"\n");
         String heightSize = attrVal.getAttribute("height");
         String heightVal = heightSize.substring(0,heightSize.indexOf("pt"));
         attrVal.setAttribute("height",(Double.parseDouble(heightVal)-2) +"pt");
         //System.out.print("Number: "+i +"::" + attrVal.getAttribute("height")+"\n");
         } 
        
    }    
 
    return(tDoc);
}
 
public int countPages (String outDoc) {
   //Count the number of pages in the generated PDF document
    int numPages = 0;
    FormProcessor fp = new FormProcessor();
    fp.setTemplate(outDoc);
    try {
            numPages = fp.getPageNumber();
            System.out.println("Number of pages: "+ numPages  );
        
        } catch (Exception e) {
            e.printStackTrace();
        }
    return(numPages);    
 }
}

You can also get the class here, both compiled and not along with the template and XML data. You can run the class from the command line passing in 4 parameters :

java xdotestbed.FitSinglePage rtfFileName xslFileName xmlFileName pdfFileName

you'll need to set your classpath accordingly, substitute your values for the parameters above e.g.

java xdotestbed.FitSinglePage 1.rtf 1.xsl 1.xml 1.pdf

 and you'll need the following libraries:

aolj.jar - EBS lib, required even for standalone
bicmn.jar - BIBEans for charting
bijdbc14.jar - BIBEans for charting
bipres.jar - BIBEans for charting
collections.jar - Needed in mailing
i18nAPI_v3.jar - internationalization lib
share.jar - general library
versioninfo.jar - anothe EBS lib
xdochartstyles.jar - for charting on the publisher side
xdocore.jar - core library
xdoparser.jar - publisher parser
xmlparserv2-904.jar - XML  lib
xmlpserver.jar - XML  lib

You may not need all of them depending on whats in your template but they are all easily grabbed either from the standalone server install or the Template Builder for MSWord install directory.

Summing Up

It seems quite a niche requirement but if you need this type of functionality then the APIs and an XML parser can help. I could even see the need to manipulate the template repeatedly for other requirements. I chose a DOM paerser because templates are not that big. It would not be a huge task to move this over to SAX.
Overall, I at least had some fun and frustration building the solution  and if nothing else you got to see a few more APIs. 

Hey, do you have users that want RTF output and they are not happy because fonts are not the right size when they open the document? If 'yes', read on - if 'no' check out another blog article, yesterday's took me ages and its pretty cool.

If you're still here. What I mean by the wrong size font is, say you created a template that has some blank space for someone to add more information into an RTF doc. They open the doc, enter the data but then find themselves having to change fonts, etc. It's not too bad for a single doc but say you are updating dunning letters all day, its going to get tedious very quickly. Well there is a property that was introduced in 5.6.2 (all flavors) that allows you to set a default font for the document.

This means that any empty cell in a table or position within the document where the user might want to add more information will have a default font set, the default is 'Arial 12 pt.' The property takes the format:

FontFamilyName:Size e.g. Arial:12

Now remember, if you choose some esoteric font that only you have on your machine then your users are not going to get the same font unless they have it in their fonts directory.

If you're using MSWord plugin or the Template Viewer use the config file. 
For Template Builder you'll need to update the configuration file under the install directory

<property name="rtf-output-default-font">Arial:20</property>

For Template Viewer you can put it in the second tab of properties.

Name - rtf-output-default-font  Value Arial:20

Today, I have to thank a couple of folks on the forum for a great question and an even better answer. Dave Williams a.ka. 'DaveInFl' and 'CC' - sorry CC I dont have your real name ' Christopher Columbus' or 'Christina' of course ... anyhoo. Dave put up the question.

Should the report parameters of an RDF report be available in the XML output ?

The short but rather mean answer would be 'Yes- they should'. In fact they really should be but they are not available in Oracle Report XML output. CC came back with:

The report parameters will not be available in the XML output unless you are passing the parameter value to a formula col/placeholder col. You can however display the parameter values in the template after you run the conc request in the apps and put the below code in your template...

Enter <?param@begin:P_START_DATE?> into a Text Form Field at the top of the RTF and then create another Text Form Field to display <?$P_START_DATE?>. Update the template in the Apps and then run the concurrent request for the report.

That is what I did to display the parameter P_START_DATE...Hope this helps

Now I have to thank CC for finding this and the concurrent processing team for implementing it. We logged the enhancement request against them a long time ago and I have to admit I had not chased them up on it. Im very pleased to see that they have implemented it. So, you can now get your concurrent parameter values very easily. As CC has outlined, first create a reference to the parameter value in your template:

<?param@begin:PARAMTER_NAME?> where parameter name refers to the internal name on the conc program form

to reference the value in the template either to display it or perform calculations on it use

<?$PARAMETER_NAME?> - note the '$' sign.

At runtime the conc manager will pass these values to the template. Remember they will not magically appear on the template you must create the reference to them (param@begin) and then use them in the template explicitly. 

Almost forgot, you need to be on 11.5.10.2 or higher to get this functionality, the publisher level does not matter.

Thanks Dave and CC!

Those of you working with the E Business Suite will probably have come across the Financial Statement Generator (FSG) from the General Ledger application. Of course publisher can now be used to format XML output from the FSG engine. The Lance Reedy from Solution Beacon website has written up a good article on how to use FSG with publisher or vice versa of course. Its a great introduction to the solution.

http://solutionbeacon.blogspot.com/2007/07/simple-tutorial-for-publishing-fsg.html 

 Im sure you're going to want to take things a bit further with the output, just be aware that the FSG XML format is not the most friendly in the world. If you get stuck check the forum first, there have been several threads on the vagaries of the XML and how to manipulate it. Thanks for the article Lance. 

Last time we looked at building and pre-filling the PDF data entry form. We now need to look at how we can allow the user to 'submit' the data they have entered back to the server and to then let them know that their data has been received.

There are a several of ways that Adobe will allow you to post data from a form.



Im using Adobe 6 you may be on another version, things should be similar thou. The properties dialog, for a button reveals the folloiwn submission methods:

• FDF - this is Adobe's 'Form Data Format' - you can get more info on this here. It's a proprietory format for sending (and receiving) form data and comments. Im not going to deal with this format.
  
• HTML - need I say more except Im not going to deal with this either
  
• XFDF - XML Forms Data Format. Yep, you guessed it, it's Adobe's proprietory XML format for the forms data - now this we can and will use. Not the friendliest format but guess what? We have an API for that.
  
• PDF - you can just submit the complete PDF form. This is useful if you wanted to store a copy of a form created by a user. Even better if they signed the form with a digital signature - as good as a paper signature. Of course we need to scrape the data out of the form too - yep, we have an API for that too and we're going to try that too.

Now I guess you could use the HTML format and Adobe provides an SDK to parse the FDF format but why bother, Publisher has all you need for for the last two formats and they have enough information.

Using the properties dialog we can choose XFDF or PDF. Initially I chose XFDF, I need to POST the XFDF to some CGI process on a web server. I elected to use a servlet. Looking at the graphic above you'll see I have entered a URL. Therein lies an issue, I have hardcoded the URL on the button - you got it. We have an API for that, now I have to admit this one is hidden and again I lent on Incheol for information.

We have an undocumented class FieldEditor, as the name suggests you can edit fields with it - that includes adding fields too. So, we can  create the button in its entirity and enter the submission destination and format and then put it on the page.

We first need to instantiate a PDFParser object, this takes the file we want to edit as an input. We then create a FieldEditor object and add the button and then re-generate the form. The addButton method takes a series of parameters.

            addButton(btnName(String),
                      btnLabel(String),
                      btnfontSize(float),
                      btnCode(String),
                      btnCoords(float[],
                      btnPage(int),
                      btnType(int))

Most I hope are self explanatory except maybe:
btnCode - you can create a submit or javascript type button and here is where you pass either the

            //Add the button
            float[] btnCoords = {350f,350f,470f,380f};
            float fontSize = 16f;
            String btnName = "SUBMIT_BTN";
            String btnLabel = "Submit";
            String btnDest = "http://www.oracle.com";
            int btnPg = 0;
            //Create a PDF Parser instance
            PDFParser pdfP = new PDFParser(pdfForm);
            //Create a FieldEditor instance
            FieldEditor fldEd = new FieldEditor(pdfP);
            //Add the button
            fldEd.addButton(btnName,btnLabel,fontSize,btnDest,btnCoords
               ,btnPg,fldEd.BUTTON_ACTIONTYPE_SUBMIT_FORM);
            //Regenrate the form
            fldEd.generatePDF(pdfForm);

Now the only drawback is that the API does not let you influence the look and feel of the button, so you get a flat button ... not exactly a nice beveled 'clickable' affair but it gets the job done. Before you ask, no there is not a method to update a button. We can look into that if enough of you scream loud enough. I also have to admit that we do not give you the control over the posting format of the button e.g. XFDF, PDF, etc. It defaults to HTML ... dang - Im logging an enhancement for this today.

Apologies for this tangent we got on to with buttons that led down a rather disappointing cul-de-sac. Next time, we get into receiving the contents of PDF via a servlet and letting the user know we got their information. 

I couldnt really say 'Crosstabs on Steroids' now could I, not with 755 being hit over the weekend and all the controversy surrounding that particular batter, what happended to 'innocent until proven guilty' - I guess the modern day press and media happened. I used to attend Giants games resonably regularly even before the 'Balcogate' mess and the excitement that Barry Bonds generated in the park when he stepped up to bat was infectious. Even if he did 'enhance' his game physically, I do not think there is a drug out there that can improve you ability to see and react to a ball being thrown at near 100 miles per hour, in my humble opinion its still an astounding feat. 

Anyhoo, moving on to an even more exciting and thought provoking subject, good question from Chrissy on the forum this weekend that I think deserves an investigation and an answer:

With the Analyzer I can create a report for my data with two column fields.
I would like to rebuild this in my RTFTemplate, but with the cross table assistant it's not possible to do this and I haven't found anything about this in the documentation.
Can anybody give me some hints & tips how to do it?
When creating a pivot table in excel you can add 2 data items. Can this also be realized in BI Publisher?

The Template Builder for Word does provide a dialog to help you build a cross tab but that will only help with a single 'measure' or 'data' value. Assuming we have the following data:

<?xml version='1.0' encoding='UTF-8'?>
<SALES>
 <SALE>
 <YEAR>2000</YEAR>
 <REGION>Americas</REGION>
 <SOFTWARE>1200</SOFTWARE>
 <HARDWARE>850</HARDWARE>
 <SERVICES>2000</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2000</YEAR>
 <REGION>EMEA</REGION>
 <SOFTWARE>1000</SOFTWARE>
 <HARDWARE>800</HARDWARE>
 <SERVICES>1100</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2000</YEAR>
 <REGION>APAC</REGION>
 <SOFTWARE>900</SOFTWARE>
 <HARDWARE>1200</HARDWARE>
 <SERVICES>1500</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2001</YEAR>
 <REGION>Americas</REGION>
 <SOFTWARE>2200</SOFTWARE>
 <HARDWARE>950</HARDWARE>
 <SERVICES>2100</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2001</YEAR>
 <REGION>EMEA</REGION>
 <SOFTWARE>1100</SOFTWARE>
 <HARDWARE>900</HARDWARE>
 <SERVICES>1300</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2001</YEAR>
 <REGION>APAC</REGION>
 <SOFTWARE>1000</SOFTWARE>
 <HARDWARE>1400</HARDWARE>
 <SERVICES>1200</SERVICES>
 </SALE>
 </SALES>

Line of business (LOB) sales data across multiple regions and years.

With the dialog we can build a crosstab with Years as our column headers and Regions as our row headers.

We can generate something like

Now, as Chrissy asks what if we wanted something like this:

The dialog can not help you here, we are going to have to dive into code. Let me say here that we are looking to address this in a future release of the template builder but for now, we're coding. 

To get this to work I had to beef up the RTF layout to accomodate the 3 column sets (HARDWARE,SOFTWARE and SERVICES).

The graphic above shows the original 'wizard' generated cross tab at the top and the new and improved 'ripped' crosstab below. Things of note:

1. The embedded LOB table with the LOB titles, these need to be in a cell that will get repeated with the YEAR component.
2. The extra columns to handle the extra two LOB values

The other changes are hidden in the fields.

1. On the 999.00 - fields on the REGION row, notice the G and E wrap the three columns now. Each field contains:
    
     <?sum ($G1[(./YEAR=current()/YEAR)]/<<LOB_NAME>>)?>

2. Similarly on the 'Total' row we have G and E wrapping the 3 columns, each total value containing:

     <?sum(current-group()/<<LOB_NAME>>)?>

3. In the 'Total' column we need some XPATH to ensure we get the right numbers summed. For the regular totals:

     <?sum ($G1/*[name()="HARDWARE"or name()="SOFTWARE" or name()="SERVICES"])?>
   
    The XPATH is ensuring that the HARDWARE, SOFTWARE and SERVICES values are summed.

    Same for the bottom right cell:

    <?sum ($T/*[name()="HARDWARE"or name()="SOFTWARE" or name()="SERVICES"])?>

Thats it, not too much effort, files here. Yes, I admit you need some XPATH experience and a little XSL but other than that you too can achieve 'ripped abs', sorry 'crosstabs'.

If you are writing reports that will show currency data across multiple currencies then templates can get complex all to quickly. With the introduction of the Euro-currency things have gotten simpler. I rememeber writing Oracle Reports for my old team EMEA Financials and having to handle multiple currencies, even revaluation reports to get amounts to a single currency. We had some nice user exits to format currencies for us to make life simlper. You'll be pleased to hear that publisher can help you there too - no matter what flavor you are running. Life is admittedly easier for EBS users, and will shortly be as easy for standalone with PS and JDE following along later.

The xdo.cfg file can contain all the formatting information required any currency you happen to be reporting on, the entries for the currencies are very straight forward.

    <currency-formats>
      <currency code="USD" mask="999D99"/>
      <currency code="JPY" mask="999D99XX"/>
    </currency-formats>

Once you have defined the formats you can then reference them in your templates thus:

<?format-currency:CURRENT_BALANCE;'USD'?>

When the formatting engine executes it will pick up your formats and apply them to your currency values. If you apply a mask that does not exist then you'll get good ol 2 d.p. output ... not good if you want Yen!

I mentioned that life was easier for EBS users, you get a nice user interface to create the formats in the configuration manager tab, its strightforward stuff.

1. 
   

   Navigate to the Currencies sub-tab under the Administration tab. Click on Create Currency Format Sets.

   
   

   Enter a name and a code for this set. The code is a unique identifier and cannot be changed later.

   
   
2. 
   

   The View Currency Formats page shows the currencies formats available in this set. You can also update the currency set (changing its name), or update/delete any currency formats already created. Click on Add Currency Format.

   
   
   
   
3. 
   

   Select a currency from the LOV. This list is generated from the FND currency table so you can add more currencies if needed from System Adminstrator responsibility (but all iso currencies should be covered).

   
   

   The format mask should be in Oracle number format. The format should be MLS compliant so 'D' and 'G' should be used instead of explicitly specifying the separators. In fact use only '9', '0', 'D', and 'G' to compose the number format. (Some information regarding Oracle number formats can be found at this page: http://www.ss64.com/orasyntax/numfmt.html)

   
   

   There is currently no validation of the format mask. This may be included in a future release with APIs from the i18n library.

   
   
4. 
   

   Once the currency format set is created, you can assign it to the Currency Format Set property in the configuration page (under category FO Processing).

Standalone, JDE and PS users, you need to wait a while and keep plugging away at that config file for now. 

Dates, dates, dates ... no not the sweet dried fruit with a pit, we British only ever seem to eat at Christmas, I mean DATES, birsthdates, invoice dates, shipping dates ... they are tough nut to crack in XSL especially calculations. Date calculations are tough right now with publisher .... they will get better but there was a nice workaround provided by Darshan on the forum yesterday, definitely worthy of a wider audience. It was in repsonse to a question on how to calculate the difference between two dates from Valerie.

Valerie was expecting <?xdofx:$C_REPORT_START_DATE-C_DUE_DATE?> to provide a date result, sadly she got an error result. Until we can pick up the xsl2.0 date support in the Oracle XDK engine we are a little stuck on these calculations. Happily Darshan provided two workarounds, one forcing tghe calculation back into the data layer, the other in the layou template.

1) Create one Hidden parameter for that concurrent program and assign default value as ( select :$FLEX$.C_REPORT_START_DATE - :$FLEX$.C_DUE_DATE from dual) and refer this parameter in your layout template. You have to tweak above query to extract correct difference between dates. You could also do the caluculation in the main extraction query.

2) If you still want to do it in template then i would like to suggest you first that convert your dates into number by julius format_code      

  to_number(TO_CHAR(SYSDATE,'JSSSSS'))

<?xdofx:tO_NUMBER(TO_CHAR(to_date('2007-08-15', 'YYYY-MM-DD'),'JSSSSS'))
 - TO_ NUMBER(TO_CHAR(to_date('2007-08-14', 'YYYY-MM-DD'),'JSSSSS'))?>

You may have read last month about the great BIP Mind Share project, the plan was to provide the ability for development, consultants and customers across all flavors of publisher to share their templates - one big happy family sharing our toys.  

Well, I have had a great meeting recently with the folks that are implementing this wee beasty, one of those folks is Jake of AppsLab and Web2.0 evangelist. Its coming together very quickly and it looks like it is going to be really useful. Once loaded with its meta data to describe said layout or data template you are going to be able to search for that elusive template for that obscure concurrent program that ships with European Localizations. Just as a tease I even have grainy screenshots stolen from the locked cube (yes Oracle's cubes are lockable:0) where developers have been feverishly bashing this out.

Initial release rollout will support EBS and PeopleSoft, but JD Edwards, Siebel, <<put you app name here>> and even standalone publisher templates will be supported in furture releases.

There are a few operational things to iron out such as having a librarian or some means of ensuring the quality of the content, where its going to get hosted, behind Metalink somewhere is currently on the cards. Nothing major to sort out but here's where you come in.

This is only going to be as good as the content that is posted into it. You can expect all of the templates from development teams in EBS and PeopleSoft to be in there. But we need your templates in there too - I know theres a whole question of intellectual property, you have worked hard to get your templates where they are today and why should you share them? But its a give and take process, Im sure that there is going to be a template in there that you need, so if you put one in when taking one out the whole concept is going to be great - if I or any of the development team have helped you with a template I expect to see it in there, got it :0)

You're going to get to see the application at OpenWorld in November and we are looking for volunteer templates to be loaded to our demo system - we need layout templates and a little meta data - what report name and application it's associated and we'll do the rest. We can not guarantee you'll get into the session but we'll try. If you're up for it, mail me your templates, zip em and change the extension to .zipped otherwise our mail server will spit it back at you.

I came across my XML Publisher Pilot web page today, it has a bunch of early examples we built way back then - OK it was only 3-4 years ago - we're young still but we have some history ...

Back in '98 I had just finished a degree (finally) and started with Oracle UK in the Financials for EMEA team under the EBS product. We had nearly 200 reports and umpteen pieces of regional functionality to build and maintain. I can still remember fixing my first bug on an Oracle Report - 10.6 character was still around,  and R11 had just come out - I was amazed at just how bad the reports were. Now, Im not talking content here, all the data was there of course and it could be argued that for many of the 'listing' reports 10 point Courier with no text decoration was fine. I was not on that side of the fence - we had a good solid tool in Oracle Reports 2.5, yes it blew up too regularly and core dumped when you least expected it to - I learnt very quickly to save at every step - boy did it need an autosave feature.

But, we were using it in character mode and generating what I considered to be crappy outputs. There were reasons of course - to move all of the Apps reports to Bitmap mode would have been a huge task with ~2000 reoports, there were other concerns around i18n to consider too. Almost a year later I moved to our sister team in California and discovered RXi for those of you using EBS you will no doubt have come across RXi - it addressed one of the big issues customers had with Oracle Reports - customization! It was painful, we never shipped quite the right report to you folks, there were fields you wanted and had to be added or removed, layouts needed changing - as soon as you touched that report - it was yours to maintain. RXi allowed you to create as many layouts as you wished on top of a basic extract, on top of that you could plug your own data into the extract - still character based but so much better.

In 2003 I got a new project - Financials Reporting Strategy - a grand title for a tough project. How could we get the best of RXi and Oracle Reports and produce a flexible reporting solution? We worked with the Oracle Reports folks, we even came up with a prototype that allowed you to create multiple bitmapped layouts on top of an Oracle Report and choose which one you wanted at runtime - sound familiar?

Then I got involved with a broader Applications Reporting Strategy - I was shown a reporting based on MSWord and XML, it had folks' names and their telephone numbers in output - I was blown away, this was awesome. I told my team to 'down pencils' - we had found the answer to the problem. I dragged my now development director in front of our VP and got him to demo it - it did not go too well - after the meeting I was gushing with enthusiam - the rest of the attendees were less than overwhelmed - 'give me a month I said.'

In that month, we worked danged hard to get a real world example working - the first report was an invoice listing.

The template parser back then was a little different, we now have '<? ?>' and XSL like commands - back then we swung from '[ ]' to '{ }' and back again and used 'Group:' rather than 'for-each:'

Three years later and the tool has come a long way ... we still have a long way to go of course. But you'll find it in EBS, PeopleSoft, JDE, BIEE as a standalone product, integrated with APEX and maybe even bundled with the database one day - its a fun ride, exhausting but fun.

It seems our friends in Redmond have done it again, another security patch has screwed up the Template Builder for Word. We apologise, its tough to know what any given patch is likely to do, this one -KB936021 at least mentions the MS XML Services which we do use so we maybe should have picked it up.

If you are experiencing the following error when trying to load an XML sample file when building a template or trying to connect to the BIP server to create a template:

       Compile error in hidden module: Module_starter

We have the following work around - which seems to work reliably
and does not require you to uninstall the security patch:

1. Go to the Start Menu:
               All Programs -> Oracle BI Publisher Desktop -> Template Builder for Word Language
2. Select your language
3. Click OK.
Now use the builder as normal.

We apologise for this interruption of service, normal Templating experience will resume shortly ... barring any more surprises from Washington.   

Finally 10.1.3.3 folks get to catch up with those of you with EBS 11i and R12  - makes a change I know. Im talking about converting your Oracle Reports to the publisher format.

The conversion libraries are pretty good, we have a team internally converting all of the EBS Oracle Reports and they have been very successful converting reports. There are of course corner cases, Reports lets you do the same thing in so many ways and the libraries need to cover them.

Now, rather than regurgitate the documentation heres a link - http://download.oracle.com/docs/cd/E10383_01/doc/bip.1013/e10416/convertrpts.htm#BABGJHAB 

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.

Many of you know that publisher requires some 'extras' to get your document to a printer in a legible format. There are now printers that can handle the PDF format natively but the majority of printers need to have the PDf converted to either Postscript (PS) or Printer Control Language(PCL). We have recommended a couple of converters to help you out with this.

Ghostscript, offers both PS and PCL converters the other product we recommend is xpdf from foolabs and we have many of you using both products very successfully. In spite of their robustness many of you have expressed concern - 'it's another moving part in the whole document generation process' - 'and it's not owned by Oracle' -  both fair comments and I can understand the concern - however we have faith in these solutions and will help you to use them.

For BIP Standlaone users there is some good news, we have now built our own Postscript libraries to convert from PDF to PS. Very easy to use when setting up your printers in the user inteface.

As you can see its simple to use and if you want to continue to use another converter thats simple too. There are some caveats and limitations:

This release of the PDF to PostScript converter has the following limitations:

• 
  

  Only PDF version 1.4 or earlier is supported. This is also the currently supported version of the Oracle BI Publisher PDF output.

  
  
• 
  

  CID fonts, which are used mainly to support languages with large sets of glyphs like Chinese, Japanese, and Korean, are only supported when embedded in the PDF.

  
  
• 
  

  Transparent and half-tone renderings are not supported.

  
  
• 
  

  DeviceN color space is not supported.

  
  
• 
  

  Shading patterns are not fully supported.

  
  
• 
  

  Vertical writing is not supported.

Not forgetting you EBS and other apps users out there - this functionality will be coming to an application near you later this year (for EBS) and next year for others.

those of you pulling hair out over getting your barcodes into your bursting outputs under EBS - stop pulling, you're ot going crazy. You're going to have to go old school to get them showing up - the bursting engine will not check the Font Manager for fonts - its an bug or enhancement depending on how you look at it - we'll get it sorted either. In the mean time you can still get those barcodes or other fonts into hte output.

Two methods are available:

1. RTF Properties - I have covered these previously, you create a custom property in the RTF.

Name: xdo-font.<<font family name>>.normal.normal


Type: Text 


Value: truetype.font <<directory+name>>

Its quick, the oly problem being the template might not be very portable unless the font directory is mounted across instances.

2. Config File - thats covered somewhere too. A file, xdo.cfg is put into the JRE_TOP/lib directory. Needs the following to map the font location to a name in the template. 

<config version="1.0.0"  xmlns="http://xmlns.oracle.com/oxp/config/">
   <!-- Font setting -->
    <fonts>
      <font family="3 of 9 Barcode" style="normal" weight="normal">
       <truetype path="C:\WINNT\fonts\3of9.ttf" /> 
      </font>
    </fonts>
</config>

Need to bounce those servers to take effect. Good Luck!

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

Ever been building a dynamic URL in your RTF template and when it renders you get some interesting prefixes to the URL you were expecting?

MSWord is good at that, its a 'nanny' application and knows what is best for you, now take your medicine like a good little 'templater'.  Most popular is the 'C:Documents and Settingstdexter' prefix - nice, just what you need. We discovered another one today, the FSG folks are looking into providing a means to drill down on FSG numbers back into the appropriate GL ledger - its going to be a very neat solution that will add a whole new level of interactivity to FSG reports with Publisher. We had to learn how to ignore Nanny first thou ...

To create the URLs to the correct account they need to build it up using some of the data elements around the number element:

<fsg:RptLine RptCnt="p1001" RowCnt="r100002" LinCnt="l100122">
<fsg:RptCell ColCnt="c1000" RealNum="">Totaled 01-01              </fsg:RptCell>
<fsg:RptCell ColCnt="c1001" RealNum="0.000000">0.00</fsg:RptCell>
<fsg:RptCell ColCnt="c1002" RealNum="2685057669.910000">2,685,057,669.91</fsg:RptCell>
<fsg:RptCell ColCnt="c1003" RealNum="0.000000">0.00</fsg:RptCell>

To build the URL we need RptCnt, RowCnt, LinCnt, etc ... straightforward right. Well it would be if Word did not want to help you so much. The values we are interested in are attributes and therefore we reference those with an '@' sign e.g. @RptCnt. See, even my blog software thinks it knows best, putting a mailto link on the '@' - there it goes again!

When we built reportthe URL in MSWord we got a great 'mailto:' prefix on our URLs - no matter how many auto complete switches we turned off- Nanny Word was there to correct us like a 'dogged' school teacher determined to make you spell 'colour' as 'color' cos we're in America, or maybe 'favourite' as 'favorite' and the best transatlantic transgression - 'sulfur' instead of the correct British 'sulphur'. 'Sulfur' seems so '1st grade' - flame me if you will, its a pet peeve. Another reason for hating Word's nannying tendencies - it's America-English dictionary - yes, I could switch it to British-English but seeing as the majority of my internal docs are headed US based eyes I have to live with it. Right, back from rant land - we create URLs in our template like:

http://{servername}/fsg/?callfunct=XXXX&code={../@RptCnt}{../@RowCnt}{@ColCnt}

and because of those @'s we get the following in the output:

mailto:http://tdexter-us.us.orac.com/fsg/?callFunct=XXXX&code=1223|434343|34322|232

That 'mailto:' really screws things up arrrrgggghhh ... its done it again! Here's the solution, just put the URL in and let Nanny help you.Now when you have the document infront of you press Alt-F9 - this will toggle the field codes in the document and you can remove the offending mailto from the HYPERLINK field.

{HYPERLINK"mailto:%7b/MasterReport/fsg:HostName%7d?DRILLDOWN_ACTION=0&DRILL_DOCUMENT_ID=%7b/MasterReport/fsg:ReqId%7d&DRILL_DOCUMENT_NODE=%7b../@RptCnt%7d%7b../@RowCnt%7d%7b../@LinCnt%7d%7b./@ColCnt%7d"}

press Alt-F9 again and your URLs will be evaluated correctly at runtime. Be sure not to get caught by 'Nanny' or you'll get detention!

With OOW07 just around the corner, superstar Nadia from the applications technology strategy team has put together some useful documents on the Apps Tech track, whats on and where - these are almost cast in stone, lets say the concrete is 'going off' but you can still stick your hand print in it. Those of you that came last year will remember the lines(queues) to get into to some of these sessions so please book a seat ahead of the conference to avoid disappointment.

There is a new type of session this year 'meet the experts' or MTE - you get an hour and a half to grill experts with tough gruelling questions with multiple followups getting deeper and deeper into the product until we have completely lost the rest of the audience - be gentle please.

• Focus On EBS AppsTech - The E-Business Suite: Tools and Technology track sessions
  
• EBS Techology MTE  - this lists those MTE sessions specifically for EBS technology. You will not find us here - we now run across multiple apps.
  
• All Apps MTE - as the name suggests, this lists all the sessions for Apps. We are in this section, we'll have representatives from the PeopleSoft and JD Edwards 'publisher' teams join us so it's not an EBS only affair. Plus as many developers that I can so you get to talk directly to some of these folks rather than me boring your socks off.

Yours truly will be there and as I have mentioned before there are a host of sessions going on that are either exclusively about publisher or about technology that uses publisher. There are multiple demo pods featuring publisher staffed by experts of course so along with hte MTE session you have no excuses for not coming to talk to us 

By the end of the week, you'll be all 'published' up!

There was a question on our internal mailing list today on our translation methods - the answers are in the user guide but the interesting thing was a mail from another BIPper. Microsoft have released a pretty decent XML editor at last. Last time I tried it, it was not a good experience, there still seem to be some niggly issues but overall its pretty useful.

Its not a style sheet editor but if you need to do some work with XML - I seem to spend my life doing this, debugging templates, building demo XML, etc. I get to use the great but expensive, XMLSpy but considering the features I use, the MS solution is pretty good. Get it here:

http://msdn2.microsoft.com/en-us/xml/default.aspx

Here's another 'Open XML Editor' - pretty good, not quite as sophisticated as the MS offering. And another from Peter, better than the Open XML and more on a par with MS.

Of course, if you have jdeveloper installed you get an XML editor included - but if you do not have it anyway, its a bit of an overhead if all you want to do is work with XML files.

I received an interesting question from Mark Nelson, one of our SCs last week from a customer he is working with. It concerned fonts - as you know we use MSWord on a Windows platform to create templates. Now before you unix/linux fans ask - no we have not certified on OpenOffice or similar. We have built a few templates by hand using OO - some things work, some dont. Right ow the demand is not high enough to warrant certified support of OO or similar and the porting of the template builder to OO would be a mamouth task.

Moving swiftly on, fonts on Linux - so you build your template on Windows and lets assume you use a few external fonts, say Wingdings for some checkboxes. It all works and tests fine on your desktop - now you need to move to production on the server and you use Linux.

Where do you stand on using the MS Windows wingdings font on a linux server? Will it work? What licensing should I be aware of?

Well, we do not ship those fonts to you nor license them - I must give all credit to Mark for digging the following nugget of information up.   

Microsoft released their TTF fonts for use on other systems with no license charge, provided they were distributed in their original format.  They are no longer available on Microsoft痴 web site, to the best of our knowledge, but they are available here,

An easy way to install Microsoft's TrueType core fonts on linux   

The source forge site provides instructions on downloading and installing the fonts. I have to admit to not having tried the instructions but if any one out there can confirm or deny them I'll share experiences. 

Another shameless OOW session plug - I wrote about the Reports Repository last week - Joe Gum and Elise Mattei form the Finapps strategy will be presenting RR in all its glory. I have sat through a dry run and its not gonna be a 'death by powerpoint' session. I have known Joe for some time, he's a great guy and a lot of fun - its going to be an awesome session, informative but fun - no chance of nodding off in the back. 

The title is a little cryptic for those of you looking for Publisher related sessions but its all about sharing publisher templates rather than music, software and photos - get it? The room is filling up quick so get a reservation in - if you can not make it but have booked for the EBS Publisher session on Tuesday afternoon you'll get to see RR - I have cajoled Joe into joining us for a demo.

Next week - its regular OOW pictures and short commentary coming at you. I have Flickr set up to push images and editorial out to the blog via my cell phone. So I can blog from the demogrounds, sessions, some bar of an evening that Im sure to end up in - OK maybe not the bar. Hopefully, those of you that can not make it will get some 'publisher' flavor of OOW.
I'll not be 'twittering' thou - Jake mentions the OOW twitter stream created by Eddie on the AppsLab blog - I still can not see the value. If I can get images and editorial out to the blog in the same time that I can 'twitter' a message then why not blog it to the masses - easier to consume than a stream of messages I think - maybe I'll see the light and have my 'road to Damacus' moment but I doubt it. We have all but forgotten how to talk to one another - I even caught myself saying this week 'I'll drop them an email.' I caught myself and 2 minutes later it was sorted out on the phone 'voice to voice' - I followed up with a mail thou. Just talk people!

Update: Just went and looked at the OOW twitter feed and Eddie has hooked up Oracle blog related headlines to the feed any way - so if twitter is your preferred medium you'll at least get links to publisher blog entries. 

Final update for OOW for the week - earlier this week we were filling the XMLP for EBS session room with pre-booked folks. By yesterday we had nearly 50 people wait listed to get it. The great logistics folks have gotten us a bigger room.

Couple of doors down from the old room and bigger - get a reservation quick to guarantee a seat or a square foot of floor space.

Whats going on in the session?

Well, all I have is about 30 slides and thats it!
We have demos galore from bursting to the reports repository, I have folks talking about their implementations, I have some Oracle Partners talking about their solutions and you got my dulcet tones to keep you interested and to make a change I have questions for you all - should be fun!

Get to the Schedule Builder here and get a spot.

Quick Update  

Pradeep from out PM team has pulled out a list of 5 sessions that cover BIP/XMLP from a customers perstepctive - try and get to some of them and get the real skinny on BIP/XMLP

Down on the demo grounds this morning ... a slow start but boy did it get going. We are right next to the concourse - not buried at the back like last year so there were lots of folks coming by. Im very impressed by what some of you are getting up to with publisher - you have moved on from getting that invoice listing out and are now attacking the bursting engine with a vengeance. I heard today of a customer, sorry I can not remember your name, doing 'double bursting' - burst once and then take the results and burst those again - I did not catch the business problem we were solving but solving it we were and thats what matters. So bursting is hot - just about every one I spoke to wanted to know how to burst-  other than to drink 3 gallons of water and see if you can hang on!

For the initiated, bursting is a term used to describe the taking of a set of batch data, splitting them in to individual documents and then delivering them to recipients. A good example was quoted at the SIG yesterday - a company generates commission statements for all of its sales force every month. Rather than run 100s of individual reports, just run one for all of the sales force and then burst each report to the appropriate employee - simple n'est-ce pas? Thanks Jean Pierre - my 20 year old French is more than a little rusty :)

Publisher offers a little more, what if each employee had a delivery preference, or some wanted HTML while others PDF? what if some wanted it formatted using Layout A and others Layout B? Publisher can do that - as long as those values around layout, format and delivery option are available at run time then the bursting engine will dynamically apply templates, format layouts and deliver according to preferences.

We go a stage further thou, for real low level control, at each stage of the burst e.g. data split, format, generate, etc we have created a 'listener' - you can subscribe to any listener in your own code so that as a stage is reached in the burst you can interrupt the flow, step in, execute some logic and then let the burst continue. Let's say you wanted a copy of each document sent to your content management system - in the case of partner documents such as invoice, you can not just dump the documents into the repository, you need to add some attributes to it so that its searchable later on. Use the listener functionality - subscribe to the 'generate' event - once the document has been generated, get a copy of it and its XML data - now parse out what you need from the XML data and create your meta data file - now push this into your content manager et voila. The customer gets their invoice and you get an indexed copy of it for retrieval and record if needed later.

There are versions of bursting for EBS, PS and Standalone and I believe JDE will deliver a solution next year.

Go forth and burst! 

What an hour, I had some fun and hopefully those of you that squeezed in did too. I think we had nearly 330 in the room with some folks not managing to get in - apologies - need a bigger room next time. Dang, it was hot once they shut the doors thou. I just wanted to thank Joe for coming in and demoing the reports repository - and remember its coming 'soon'. Special thanks to Rosa from Agencourt for being a good sport about her email address and Pat from Emulex, who I made stand up and say 'Hi' and the rest of you for participating in the session.

I did not want it to be 'death by powerpoint' - I think I achieved that. Hopefully thou, you'll actually remember something of the content and if not, I have mounted the presentation here. To see the 'funky template dance' you'll need Powerpoint 2003 or its 'player'. I have some viewlets of the bursting demo and some around XBRLPublisher, DGF and Excel templates to mount - I never had time to get to the last 3 demos - they will go up in the next few days.

Not to hark on about the session yesterday and upset those of you not here in Crazytown, USA but ... I weaved some time in for a few of the Oracle Partners that have been working feverishly to integrate with Publisher to get some 'talk time' with the folks in the room. As I mentioned in the session, historically reporting in EBS has been crappy to say the least. There was a yawning gap that was filled with many vendors - then Publisher came along and started to fill the void. Suddenly you as a customer did not need the third party - the writing was on the wall, a nice niche was closing up - time to 'adapt or die'. It was not meant as a marketing pitch and a few folks left the room which was a shame - these guys have embraced Publisher, invested time and money, adapted their own products and have moved with the times. They all have something to offer you that we do not yet cover. There was a strict 2 minute time slot - after that a big hook came out and dragged them off the stage a la Bong Show ... Im kidding, Im kidding. The solutions these folks have come up with really do tightly integrate with the publisher engine or its outputs and where Publisher has a gap they are well worth looking at.

Once such 'gap' is hardware secured check printing. I have blogged and white papered 'soft' methods for securing check signatures and MICR fonts but there are even more secure, hardware solutions out there. The images and fonts are held on a memory chip of some description that may plug into the printer or sit between printer and server. As of today - we can not 'talk' to the hardware to get the printer to pull the images/fonts from the memory chip - we're looking into it.

In the mean time you might check out Evergreen's solution - some of you may already have it - its a small box that sits between server and printer and has a removable SIMM card that holds the signatures and MICR font.

The folks at Evergreen now have a solution that will allow you to use Publisher to generate your check print run and then have the printer call the hardware and insert the appropriate signatures. This involves injecting printer escape sequences into a resulting PCL file generated from the Publisher PDF check document.   

Its a neat solution that I think could be adapted to work with any SIMM module not just Evergreen's hardware. I have posted a paper Evergreen have written for more information here.

Scouring the forum this morning I almost had an 'instant message' session with Darice, we were pinging the forum back and forth so quickly - a great way to communicate and really shows the value of the forum. There were problems getting counting working when re-grouping. We ended up exchanging emails and I think things are going to be working now.
Another thread on counting came up today too and is worthy of mention here. It came from Vadim (nagornyi):

Here is sample XML:

<ROWSET>
 <ROW>
 <DEPT>A1</DEPT>
 <DATA>2112</DATA>
 </ROW>
 <ROW>
 <DEPT>A1</DEPT>
 <DATA>7985</DATA>
 </ROW>
 <ROW>
 <DEPT>B1</DEPT>
 <DATA>8452</DATA>
 </ROW>
 <ROW>
 <DEPT>C2</DEPT>
 <DATA>2159</DATA>
 </ROW>
</ROWSET> 

Using Table Wizard, I am getting this report:

A1 2112
 7985 
B1 8452 
C2 2159 

Everything OK, but at the report header I need to count the number of DEPT, i.e. 3. How to get it? Thanks.

There are ways to do this programmatically in XSL but its nasty, very nasty. Thats why we have a function that makes life tres simple - 'distinct_values'. Its a Publisher extension that does all the heavy lifting for you in the background, it takes the format:

xdoxslt:distinct_values(Node name)

and returns a numeric value.

In Vadim's case

<?count(xdoxslt:distinct_values(/ROWSET/ROW/DEPT))?>

A word of caution, its expensive, just like DISTINCT in SQL its going to require a lot of processing to read the node tree in and process it eliminating duplicates. There are other slightly less expensive ways to achieve the same using XPATH but for now, just use it wisely.

An update - Nilanshu came up with the XPATH solution I was aluding to above ... remember the data must be ordered by the data you want to count and remove the duplicates from.

If your data is ordered, then you may simply count all ROWS that have a different DEPT than the previous one, keeping into account the first row of course. This might give you a better performance for large XML files.

<?count(ROW[position()=1 or ./preceding-sibling::ROW[1]/DEPT!=DEPT])?>

XPATH 2.0 does provide something similar, but AFAIK XPATH 2.0 does not work in Oracle XMLP yet.

<?count(distinct-values('DEPT')?>

Happy Thanksgiving to those readers in the US - for the rest of you Happy Thursday. Before I head off to help eat some monstrous great bird with a group of friends and family I have a great customer tip to share.

You may remember the article I posted last week on a check security solution from Evergreen - they provide the removable hardware that holds the check singatures and micr font, it sits between server and printer. They also now provide the software solution that integrates with Publisher and enables the signatures to be read and pulled into the final document.   

I received a mail from Dustin Frysinger and Keith Preston from the National Lime & Stone Company - they have been working on their own check security solution wanting to come up with a lower cost alternative. They had not invested in a SIMM printer module to hold the check signature images but they still wanted a hardware solution. I have mentioned mounting a USB or memory card as an accessible drive in the past; Dustin and Keith have actually implemented it. They have the check signatures on a memory card that can be kept locked up until a check run needs to be executed. When they are ready, the memory card is inserted into a card reader on the server and its mounted as a drive - I have played with mounting the card on a web server and gotten that working in the past. They took a simpler route and just mounted it as a drive - simpler is always better in my book - I have to kick myself sometime for being too fancy in my solutions.

In their template they have logic to pull in the signature images as required i.e.

url:{'file:///media/USB_DISK/sig1-5.gif'}

Rather than a regular URL they are using the 'file' URI to pull the image in. Of course that drive needs to be accessible from where ever Publisher is running. If the check print is run without the card in place then signatures are of course not pulled in and there is a blank area left. Still a little vulnerable may be - my bank never seems to check my signature until after tha mony has been drawn - maybe things are stricter for a company check. However its not hard to add the logic to put in a 'VOID' image if the signature image is not present - I blogged this a while back ... actually it was back in April 06 that's more than 200 articles ago! If you implement that logic alongside the card method then I think the checks can be as secure as they can be.

Its a nice solution to a tough problem - Im working with a few customers that do already have a printer SIMM module that they need to communicate with via escape sequences. Basically, we need to inject PCL codes into the document prior to sending to the printer to ensure images are pulled in as necessary from the printer module. Its a tough nut to crack because we do not generate PCL ourselves so we need some injection method - Im hoping to be able to publish that soon.

Dustin and Keith have been up to a host of other stuff but I'll save that for another day. If you want to know more about their approach, let me know via comments or mail and I'll hook you up - Im sure they'll be cool with that.

Now, Im off to stuff myself with stuffing, veg out on veggies and maybe eat some turkey - its a tough time of year for the poor old turkey.  

Easing back into work mode after the Turkey and Black Friday - yes I was up at 3am in 20F (-7C) temperatures looking for a bargain ... madness!

I spoke to a few folks at OOW about check printing in 11i and how the AP team had released a patch to provide single step check formatting. Those of you in the know will know what I mean, for those of you that dont. Until this patch hit the streets and you wanted to use Publisher for formatting you had to run the format program via the AP user interface to get the XML data. Then go to the SRS forms to then submit the publishing program separately - not nice. There were ways around it - it meant some custom coding but it could be done. Thankfully there is now a standard patch available from metalink to solve this.

It does not wuite go as far as your users might like, they will not be able to pick a layout (template) at runtime but Im sure there is little need to change a format at runtime. Its all in the setup, once the program you are using to currently format checks is converted to generate XML, you then create a data defintion in Publisher Administrator and then upload your check template and your done. At runtime the AP process will apply the template to the check data for you and generate a PDF check output.

Here are the patch details available from metalink - the patch title is a little subtle so apologies if you have spent time looking for it.

4303528 11.5.10.2: XML Payment format does not use default template

Get it applied and check the readme for instructions and you're on your way!

Check printing seems to be the hot topic right now ... I have seen far too many requests for a check print template out in the publisher-sphere. Its a little complex with the cheque or check (depending on your version of English) at the top. It then repeats the invoices to be paid twice allowing 10 rows (this is adjustable) for each. If it spills to a second page then the check portion is not printed.

It also has some conditional formatting around the check signature images - this is a soft image solution but could easily be used with the 'hard' solution I blogged last week from Dustin and Keith from the National Lime & Stone Company.

In the zip file that contains the template, data, PDF output and signature images there is also an annotated version of the template that attempts to explain how the thing actually works under the covers.

Its delivered as is - its actually based on an Oracle Report XML output from EBS so apologies to non EBS folks but it should be enough to get you up and running at least.

And before you try, not that you would, but you might, thats a random routing and account number :0) 

If you have been struggling to build templates for Purchase Orders in EBS with their XSLFO restriction I have relief. Maybe you have been building RTF templates and using the export facility to get the XSLFO template from it. May be, if you're mad you have learnt the XSLFO standard and have been building XSLFO templates from the ground up?

You no longer need pull your hair out, the Purchasing team have released a patch to allow you to use RTF templates. Its actually been out for quite some time and I apologise for not telling you sooner. They sneaked it out and I have had what can only be described as 'fun' trying to find the patch in our bug database and metalink. Anyhoo, the patch number is 4670662 - you need to be on 11i10 as I understand it and if you are on later versions of 11i10 you probably already have it. If you are not sure just check that you have the following version or higher of

java/communicate/PoGenerateDocument.java 115.23.11510.17

A great colleague and friend of mine, David Haimes has recently started blogging about Financials and more specifically on the Intercompany product - his pride and joy.

'Yeah' so what? I hear you cry - its not about reporting or BIP'

Well, today it is, its a bit of a teaser for an upcoming article I hope Dave is going to get into some detail on soon and I can then followup on with a more generic 'how-to'.

Dave and I go waaay back, well in Oracle terms, to 1998 when I joined Oracle in the UK. We both worked in the Financials for EMEA development team. Building and maintaining localizations for the financials product across mainly Europe. It was a really tight team run by Paul Snelling, a curry eating, beer swilling monster of a man who I thought was fantastic, very scary but fantastic. The things we got up to would make your Grandma blush, they made me blush - but enough of Paul's 'party tricks'.
We had a sister team based in sunny California and in '99 I got the chance to come out and attend the Forms6i roll out training - remember, this was just before EBS11i was released so Forms6i was hot, well for us Apps bods it was - Forms9i was probably already out by then but you know how up to date Apps stayed back then.

Anyhoo, the week I was in California it poured with rain every day, I got lost on the 101, a feat in itself. The number of times I drove by my hotel! I could see it from the freeway but could not for the life of me work out how to get there. In my defense, the jet lag meant that I woke at some ungodly hour in the morning and thought, I might as well get to the office - in the dark. I left the office in the evening, in the dark, jazzed up on gallons of coffee to make it through the afternoon - in my addled state my brain could not get itself around those cloverleaf interchanges, so it was no wonder I couldnt get to the hotel, well thats my excuse.

After coming back to good ol'Blighty and disseminating what I could remember of Forms6i to the team, it was back to the wonders of Interest Invoice - a mean plsql, forms and reports solution that I had been tasked with, 'getting to work.' In the spring of '99 I then got the chance to come to America with my family to live. So with 'rose tinted spectacles', expecting white picket fences, acres of cheap land and a mansion to live in we arrived in California - oh, what a shock! California is sunny, warm and full of great folks but boy is it expensive!

Suffice to say, we settled in a tiny apartment and tried to work our way up, I skateboarded to work in shorts and t-shirt - life was good. About 10 months later Dave arrived from the UK - slept in our garage for a month because of the housing shortage in San Fran and the rest, they say is history. May be not history, I could go on and on ... and on, but I wont.

Back to Dave's blog - as I mentioned above, the latest entry is about reporting and how the intercompany team used the Publisher common region to host interactive reports within their OA Framework pages providing drill down from intercompany transactions to the sub ledgers. As I mentioned, I will follow up with a how to on using the region in your pages - just as soon as I can get the danged OAF plugin to work with JDeveloper.

Those of you using XMLP under EBS will no doubt have had your users asking for a default output other than PDF to save them some time when running a report. We're still working on a fix for that :o( but for now check out and implement Gareth's awesome workaround.

  http://garethroberts.blogspot.com/2008/01/changing-default-layout-format-from-pdf.html

It requires very little effort and it will make you a star to all your users - they will shower you with flowers and chocolates ... or maybe a pat on the back.