Sending existing XML Publisher report as embedded HTML email

 

One of the tasks I was doing recently is to take existing XML publisher report, convert it into HTML and send to someone as embedded HTML mail.

At first , it looks difficult. Most of the reports create PDF output and there is no much you can do and specially you cannot convert PDF to HTML. Or should I say “it is possible, but requires too much effort”. Any way it was not an option. So I thought in a different direction. each output in XML publisher created by using some template. In 99% of the cases – it is RTF template. Again , no much to do with an RTF template, but Oracle knows somehow to combine it with XML source. So I did a little research and discovered the following:

1. All templates are stored inside XDO_LOBS table as a BLOB data.

2. When you upload RTF document to EBS it stored the document RAW but it also creates a xsl-fo document for RTF. Bingo!!! If I could get this xsl document I can create any outout I want

Bellow you can see 2 records for the same RTF document. One for RTF and one for XSL

 

So, how to extract this XSL document? One way is to write java program that converts this BLOB field to the file on the disk. But why to invent the wheel twice? Oracle already wrote such a program. It is called XDOLoader

Bellow is an example for XDOLoader usage:

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-LOG_FILE \
-DEBUG

Parameters:

-DB_USERNAME: apps username for the target instance
-DB_PASSWORD: apps password for the target instance
-JDBC_CONNECTION: JDBC connection string (e.g. server:port:sid)
of the target instance
-LOB_TYPE: The XML Publisher object type.
One of 'TEMPLATE', 'XML_SCHEMA', 'XML_SAMPLE' or 'DATA TEMPLATE'
-APPS_SHORT_NAME: Application short name used to register the XML Publisher object
-LOB_CODE: Template code i.e. concurrent program short name
-LANGUAGE: ISO language code(i.e. en) used when registering the object
-TERRITORY: ISO language code(i.e. GB) used when registering the object
-LOG_FILE: Name of the log file. The default filename is xdotmpl.log
-DEBUG: Run in Debug mode. Valid values are 'true' or 'false'

 

After you run this commands you will see 2 files in the same directory. One RTF file (you can delete it, we don’t need it ) and second one is XSL file – exactly what you need.

In order to produce the output you will need the XML source file that contains all the data. If you running this process from Oracle EBS it is not a problem. As I said we are working with existing templates , so they already have data templates defined and probably you can find XML source files under Concurrent Manager output directory ($APPLCSF/$APPLOUT). If XML file is not where – find a way to get it. This is behind the scope of this post.

Ok, now we need to combine XML with XSL and create an HTML output. We can use existing XML Publisher java API to achieve this.

So we will write a little Java

//you need the following import
import oracle.apps.xdo.XDOException;
import oracle.apps.xdo.template.FOProcessor;


//and use the following function
public void convertToHtml(String xmlSourceFile,
                          String xslFoFile,
                          String language)
    {
        FOProcessor processor = new FOProcessor();
        processor.setData(     xmlSourceFile );
        processor.setLocale(new Locale(language));
        processor.setTemplate( xslFoFile     );
        processor.setOutputFormat(FOProcessor.FORMAT_HTML);
        processor.setOutput("/tmp/myOutput.html");
        // Start processing
        try
        {
           processor.generate();
        }
        catch (XDOException e)
        {
           System.err.println("  Cannot convert XSL template to HTML");
           e.printStackTrace();
           System.exit(1);
        }
    }

 

After execution of this code you will have a nice HTML document that contains your report without rewriting your template.

And now the final part. We want to send it by email, but not as attachment. We want to send it as embedded HTML. Embedded html is not a problem, just don’t specify “attachment” content-type. But in html , could be that you have a pictures, this pictures refer to physical files on the disk, so when you send your email , recipient get it properly, but without pictures. Need to find a way to convert this pictures to base64 format and make them part of HTML email body.

   And again, no need to invent the wheel twice. XML publisher delivery API provide this functionality out of the box.

Some more java code….

//need to following import
import oracle.apps.xdo.delivery.DeliveryException;
import oracle.apps.xdo.delivery.DeliveryManager;
import oracle.apps.xdo.delivery.DeliveryPropertyDefinitions;
import oracle.apps.xdo.delivery.DeliveryRequest;
import oracle.apps.xdo.delivery.InvalidFactoryException;
import oracle.apps.xdo.delivery.UndefinedRequestTypeException;

//and the function that will do all the work
//the logic of converting pictures to base64 and making them part
//of HTML occurs behind the scenes
public void sendEmail()
    {
              //create an instance of delivery manager
        DeliveryManager dm;
        try 
        {
            dm = new DeliveryManager();
            DeliveryRequest req =     dm.createRequest(DeliveryManager.TYPE_SMTP_EMAIL);
            //define mail connection and content 
            req.addProperty(DeliveryPropertyDefinitions.SMTP_SUBJECT, "test");
            req.addProperty(DeliveryPropertyDefinitions.SMTP_HOST,    "localhost");
            req.addProperty(DeliveryPropertyDefinitions.SMTP_PORT,    25);
            req.addProperty(DeliveryPropertyDefinitions.SMTP_FROM,       "dummy@maiul.com);
            req.addProperty(DeliveryPropertyDefinitions.SMTP_TO_RECIPIENTS, "dummy@gmail.com" );
            req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_TYPE, EMAIL_CONTENT_TYPE);
            req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_FILENAME, "/tmp/myOutput.html");
            
            //define html document location. Images being automatically embedded into HTML mail body
            req.setDocument("/tmp/myOutput.html";
                // submit the request
                req.submit();
                // close the request
                req.close();
                System.out.println("Email sent");
            
        } catch (InvalidFactoryException e) 
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
         // create a delivery request
        catch (UndefinedRequestTypeException e) 
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (DeliveryException e) 
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }

Done!! Without changing existing template or developing a new one, you can have now almost any report being converted to HTML and send it to anyone by email (with pictures )

Get connection to APPS schema without knowing APPS password in EBS

 

First of all I need to explain that this post will not explain how to get the APPS password , but will explain how to obtain JDBC connection object.

   Each one who ever developed Oracle Framework page knows that you don’t really need to specify APPS password to work with EBS. But you do need to specify location of dbc file. Why? Because there is an option to get “Connection” object by using dbc file.

   First of all how do you know what dbc file to use?

dbc name can be obtained from the profile called  “Applications Database ID” (APPS_DATABASE_ID). You can get it from EBS  profile directly or by using:

select fnd_profile.VALUE('APPS_DATABASE_ID') from dual

Lets call the value of profile DBC_VALUE.

So , the location of dbc file will be $FND_SECURE/DBC_VALUE.dbc

Now , when you know the location of dbc file we can obtain JDBC connection.

We will use the following code:

 

import java.sql.*;

//this classes located under $OA_JAVA in EBS
import oracle.apps.fnd.common.AppsContext;
import oracle.apps.fnd.common.VersionInfo;

public class GetConnection
{
   public GetConnection(String dbcFileLocation)
   {
      AppsContext ac   = new AppsContext(pDbcFile);
      Connection sConn = ac.getJDBCConnection();
   }
   //do whatever you want after you get Connection
}

The connection object that you get connected to , is APPS user. From this point you can do whatever operation with database that APPS user is privileged to do

Oracle Alerts Distribution List with Custom Application

 

   “Oracle Alerts” has an ability to send emails to predefined list of recipients called “Distribution List”. However if you try to create this list under custom application you will not find this application under the list of values.

The reason is because LOV shows only applications defined in FND_PRODUCT_INSTALLATIONS

select *
from fnd_application_vl
where application_short_name='XXPCK';
select application_name, application_id
from fnd_application_vl fa
where exists (select 'X'
from fnd_product_installations p
where p.application_id = fa.application_id and
p.status in ('I', 'L', 'S'))
order by fa.application_name

Not a problem. Go to “Oracle Alerts Responsibility”->”System”->”Installation”

You will get a simple form that asks to enter Application Name and Oracle username

Application name is simpe. It is just the name of your custom application. But what is Oracle Username? If you think that it is Oracle Username that you use to login into EBS, you think wrong.

Go to “System Administrator”->”Security”->”ORACLE ( Don’t ask me why it is in upper case)”-> ”Register”

Inside “Database User Name” and “Password” enter you custom schema credentials. In my case it is XXDM/XXDM

No you can go back to “Define Application Installations” and enter your custom application and Oracle username

Now you can access “Distribution List” form and choose your custom application

Using oracle.xml.parser.v2.XMLDocument to create new XML Document based on the existing one

 

In this post I will demonstrate how to use XMLDocument  class provied by Oracle to parse existing XML document and create a new one.

Source XML

<REPOSITORY>
   <PROJECT>
        <QA_TESTING NAME="Order Management">
              <OWNER>SYSADMIN</OWNER>
        <QA_TESTING>
        <MANAGEMENT NAME="Inventory">
              <END_DATE>SYSADMIN</END_DATE>
        <MANAGEMENT>
   </PROJECT>
</REPOSITORY>

Our goal is to create new XML document for each one of the projects

We want 2 documents.

First:

<REPOSITORY>
   <PROJECT>
        <QA_TESTING NAME="Order Management">
              <OWNER>SYSADMIN</OWNER>
        <QA_TESTING>
   </PROJECT>
</REPOSITORY>

And second

<REPOSITORY>
   <PROJECT>
        <MANAGEMENT NAME="Inventory">
              <END_DATE>SYSADMIN</END_DATE>
        <MANAGEMENT>
   </PROJECT>
</REPOSITORY>

We will start by loading existing XML document

  String fileName = "c:/source.xml";
// Get an instance of the parser
  DOMParser parser = new DOMParser();

// Generate a URL from the filename.
  URL url = createURL(fileName);

// Set various parser options: validation on,
// warnings shown, error stream set to stderr.
  parser.setErrorStream(System.err);
  parser.showWarnings(true);

  // Parse the document.
   parser.parse(url);
 // Obtain the document.
   XMLDocument doc = parser.getDocument();

So we load c:/source.xml and parse it to create XMLDocument instance. DOMParser knows how to work with URL only so we need to convert the file path to URL representation. We will use the function bellow for this

   static URL createURL(String fileName)
   {
      URL url = null;
      try 
      {
         url = new URL(fileName);
      } 
      catch (MalformedURLException ex) 
      {
         File f = new File(fileName);
         try 
         {
            String path = f.getAbsolutePath();
            String fs = System.getProperty("file.separator");
            if (fs.length() == 1)
            {
               char sep = fs.charAt(0);
               if (sep != '/')
                  path = path.replace(sep, '/');
               if (path.charAt(0) != '/')
                  path = '/' + path;
            }
            path = "file://" + path;
            url = new URL(path);
         } 
         catch (MalformedURLException e) 
         {
            System.out.println("Cannot create url for: "+fileName);
            System.exit(0);
         }
      }
      return url;
   }
    

df

Now we need to  create new, empty XML Document

//create new,empty document
XDocumentBuilderFactory factory = (JXDocumentBuilderFactory) 
JXDocumentBuilderFactory.newInstance();
 JXDocumentBuilder documentBuilder = (JXDocumentBuilder) 
 factory.newDocumentBuilder();
 XMLDocument xmlDocument = (XMLDocument) documentBuilder.newDocument();
xmlDocument.setVersion("1.0");
xmlDocument.setEncoding("UTF-8");

Next, extract “repository” element from the source XML. Likely for us, XMLDocument object supports using “XPath” expressions, so we will use it

XMLNode repositoryNode=(XMLNode) doc.selectSingleNode("//REPOSITORY");

Now we will append it as the first (root) element in the new XML document. However we cannot just take the element from existing XML , we need to “import” it first. Only after this, we can use “append” function

The following command imports “repository” element and appends it as a first child

 xmlDocument.appendChild( xmlDocument.importNode(repositoryNode,false)) ;

Note “false” parameter. If you don’t specify it “false” , all children of “repository” elements will be appended. Since “repository” is root element it will be equal to appending the whole document. This is not an option for us, since we want to split the document

By the same approach we will add also “projects” element

 xmlDocument.getFirstChild().appendChild(xmlDocument.importNode(doc.selectSingleNode("//REPOSITORY/PROJECT"),false));

Now we need to take all first children of “project” element only. So we will use more advanced XPath expression. We want to take the first child of “project” (QA_TESTING) and all his siblings (MANAGEMENT)

XMLNodeList appletNodes=  (XMLNodeList) doc.selectNodes("/REPOSITORY/PROJECT/*[1]/following-sibling::* | /REPOSITORY/PROJECT/*[1]");

Note the difference between “selectNodes” and “selectSingleNode”. We want to get ALL first children of “project” element, so we need to bring a set. This is why we use “selectNodes” function that returns a set of nodes.

Next piece of code  will walkthrough the set we extracted. Remember that we already have some target XML with “repository” as root and “project” as “repository” child. So all we need to do it append “project” child from source XML to “project” in the target XML , write it to the file and remove immediatelly, because we want that target XML will have always only one child for “project”. Note “NAME” attribute. We will use it as a name of our target file

//all firtst children
for (int i=0; i<appletNodes.getLength(); i++ )
{
   //get first child
   XMLNode toRemove = (XMLNode) xmlDocument.importNode(appletNodes.item(i),true)  ;

   String targetFileName = "";
   //output file name will be as the name of NAME attribute
   for (int j=0; j<toRemove.getAttributes().getLength(); j++)
   {
       if (   toRemove.getAttributes().item(j).getLocalName()!=null &&   toRemove.getAttributes().item(j).getLocalName().equals("NAME")    )
       {
           targetFileName =  toRemove.getAttributes().item(j).getTextContent();
              break;
       }
           
   }
   //remove all blanks
   targetFileName = targetFileName.replaceAll(" ", "_");
 
   //append to target XML
   xmlDocument.getFirstChild().getFirstChild().appendChild(toRemove );
      
   //write to file           
   OutputStream output = new FileOutputStream(new File(            "S:/"+targetFileName+".xml"));
   XMLPrintDriver xmlPrintDriver = new XMLPrintDriver(new PrintWriter(output));
   xmlPrintDriver.printDocument(xmlDocument);
   xmlPrintDriver.flush();
   xmlPrintDriver.close();
    
  //delete it from the target XML
    
  xmlDocument.getFirstChild().getFirstChild().removeChild(toRemove);
     
}//end for

 

What's it.

Jdeveloper 9i problem with look and feel under Windows7

 

There are still some people that have to use Jdeveloper 9i and I am one of them. Don’t ask me why. it is just a fact. But After I installed Windows 7 I found that it is very hard to work with Jdeveloper since ALL scrollers, folder , checkboxes and a lot of other GUI elements disappeared. So it looks like

 

I don’t know what could be a reason. Probably Oracle don’t want to invest any more into this old version. So I will just tell you how to fix it. Very easy

Go to “Tools”->”Preferences”->”Look and feel” and choose something else, but not Windows

Restart Jdeveloper and you will get back all Graphic elements

Changing command line prompt in UNIX

 

When you connect to UNIX command line you get some default prompt. For example it can display your current directory. But what if you want to change it. Simple – just set PS1 variable.

Display current directory+ “>” sign:

export PS1=$PWD>

Display  hostname + “>” sign

export PS1=`hostname`\>

 

Solving java.lang.InstantiationException exception when running Java Concurrent Program

 

Assume that you want to create new Java Concurrent Program , but you also want to be able to execute this class as a standalone class with “main” method. Here is an example of such a class

public class SampleJavaConcProg implements JavaConcurrentProgram{

    private Connection _conn;
    
    public SampleJavaConcProg(Connection conn)
    {
        _conn = conn;
    }
    public void runProgram(CpContext cp)
    {
        _conn = cp.getJDBCConnection();
        
    }
    public static void main(String [] param) throws SQLException
    {
        DriverManager.registerDriver(new OracleDriver());
        String connectionString = param[0];
        Connection connection = DriverManager.getConnection(connectionString,"SCOTT","TIGER");
        
        SampleJavaConcProg prog = new SampleJavaConcProg(connection);
    }
}

You can see that this class can initialize Connection object by using CpContex object that is provided to you by Java Concurrent Program in EBS or directly by “main” method. But!!! If you try to run this class as a concurrent you will get the following error.

java.lang.InstantiationException: xxx.xxx.xxx.xxx.xxxx.SampleJavaConcProg at java.lang.Class.newInstance0(Class.java:335) at java.lang.Class.newInstance(Class.java:303) at oracle.apps.fnd.cp.request.Run.main(Run.java:152)

It doesn’t work.  What is the reson? Don’t know, but I do know how to solve it. Just create a default constructor in your class.

Modified code will look like

public class SampleJavaConcProg implements JavaConcurrentProgram{

    private Connection _conn;
    
    //default constractor
    public SampleJavaConcProg()
    {
        
    }
    public SampleJavaConcProg(Connection conn)
    {
        _conn = conn;
    }
    public void runProgram(CpContext cp)
    {
        _conn = cp.getJDBCConnection();
        
    }
    public static void main(String [] param) throws SQLException
    {
        DriverManager.registerDriver(new OracleDriver());
        String connectionString = param[0];
        Connection connection = DriverManager.getConnection(connectionString,"SCOTT","TIGER");
        
        SampleJavaConcProg prog = new SampleJavaConcProg(connection);
    }
}

No more error messages….. 

How to get rid of “Query has exceeded XXX rows” error message in OAF page

 

If you have inside your framework page ViewObject that brings o lot of data you probably will get at some point “Query has exceeded XXX rows” message”

In my case it was a dropdown list that displayed a list of EBS users

After digging into View Object definition for about an hour and finding nothing I finally found that limit value controlled by EBS profile called “FND: View Object Max Fetch Size”. I put the value of 6000 and the message disappeared

XML Publisher report with barcode

 

   Basically this post refers to any font that you want to embed into you output but I was working specifically on demand to insert the barcode.

  So start with something simple. Obtain the font file. In my case I posses “WASP 39 ELC” font. Font file name is w39elc.ttf. Insall the font into Windows by double clicking on it and choosing “install”

Open your RTF template and place the barcode in the location you want

 

So far so good and if you run this template locally on your workstation you will see the barcode. But not in EBS.

  This is what you need to do in order to make it work in EBS

Open “XML Publisher Administrator” responsibility

Go to “Administration”-> “Font Files”

 

Click on “Create Font File”

In “Font Name” put the name of the font as it appears in Word. Choose the font file and click “Apply”

Go to “Administrator”-> “Font Mapping”

Click on “Create Font Mapping Set”

Put into “Mapping Name” – Bar Code Fonts, into “Mapping Code” – BAR_CODE_FONTS, into “Type” –FO To PDF and click “Apply”

Click on the “Bar Code Fonts” link

Click on “Create Font Mapping”

Define Font Mapping as appears in the print screen bellow and click “Continue”

Ok, now you need  template to “know” the barcode font we just created.

Open template definition and go to “Edit Configuration”

Expand “FO Processing” and put into “Font Mapping Set” – Bar Code Fonts and click “Apply”.

Now you can run your template and it will display the barcode properly

 

Adding Client Side code to Oracle Framework Page

I had to add very simple logic into existing Framework page. One this page 2 checkboxes exists. Lets call one of them “first” and second one will be “second”. Desired result – whenever user clicking on “first” it should disable “second” checkbox. Well first of all I tried to do it on the server side. To do it , I had to submit the page when user clicking on “first” checkbox. This is not a problem. Just choose proper options in the GUI element as you can see in the picture bellow

So, user clicking on the checkbox and submitting the page to the server. It is important to give some name in the event field.

Second step is to catch this event inside the controller. User can catch this event in processFormRequest method

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)

{

   super.processFormRequest(pageContext, webBean);    

   if ("submitByfirst".equals(pageContext.getParameter(EVENT_PARAM)))

   {

     //do your logic here

   }

}

Well, I thought that I am one step of achieving the goal. The last think that I should do is to get a reference to “second” checkbox and disable it in the following way

OAMessageCheckBoxBean oaCheckBoxBean = (OAMessageCheckBoxBean) webBean.findIndexedChildRecursive("second");

oaCheckBoxBean.setDisabled(true);

And so I did. And it didn’t work at all!!!  Instead I got an error message saying "blah.. blah.. blah.. You can not do it in processFormRequest method. Nice. The only other place I could do this logic is inside processRequest method. But I can catch my event only in processFormRequest methos. Why Oracle did it in this way?? Who knows.

  Any way if I wanted to proceed this this approach I had to resubmit the page by using pageContext.forwardImmediately() to the same page and then I could proceed my code in processRequest method. I am thinking that it is too much. So I decided to write client code and attach it to the “first” checkbox.

First of all it need to written in the “processRequest” method. Only several lines of code

public void processRequest(OAPageContext pageContext, OAWebBean webBean)

  {

    super.processRequest(pageContext, webBean);

      String onClick = "if (this.checked) { document.getElementById('second').disabled =true;}";

      OAMessageCheckBoxBean chkEnableOmpact = (OAMessageCheckBoxBean) webBean.findIndexedChildRecursive("first");

      chkEnableOmpact.setOnClick(onClick);

   }

And this is all!!!  Just write your script and attach it to the relevant event.

Building and configuring PHP upload file page

Hi. Recently I had to build some quick way to upload files to the server.  I found a lot of examples on the WEB and decided to choose PHP.

Here are the files I used for my application

upload.php

<table width="500" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form action="upload_ac.php" method="post" enctype="multipart/form-data" name="form1" id="form1">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td><strong>Single File Upload </strong></td>
</tr>
<tr>
<td>Select file 
<input name="ufile" type="file" id="ufile" size="50" /></td>
</tr>
<tr>
<td align="center"><input type="submit" name="Submit" value="Upload" /></td>
</tr>
</table>
</td>
</form>
</tr>
</table>

upload_ac.php

<?php
//set where you want to store files
//in this example we keep file in folder upload 
//$HTTP_POST_FILES['ufile']['name']; = upload file name
//for example upload file name cartoon.gif . $path will be upload/cartoon.gif
$path= "upload/".$HTTP_POST_FILES['ufile']['name'];
if($ufile !=none)
{
if(copy($HTTP_POST_FILES['ufile']['tmp_name'], $path))
{
echo "Successful<BR/>"; 
//$HTTP_POST_FILES['ufile']['name'] = file name
//$HTTP_POST_FILES['ufile']['size'] = file size
//$HTTP_POST_FILES['ufile']['type'] = type of file
echo "File Name :".$HTTP_POST_FILES['ufile']['name']."<BR/>"; 
echo "File Size :".$HTTP_POST_FILES['ufile']['size']."<BR/>"; 
echo "File Type :".$HTTP_POST_FILES['ufile']['type']."<BR/>"; 
echo "<img src=\"$path\" width=\"150\" height=\"150\">";
}
else
{
echo "Error";
}
}
?> 

Everything is pretty straightforward, but in all examples I found no one told, that by default I cannot upload more then 2M.

So here is the solution. Detect php.ini file on your server. In my case it was located under /etc/php.ini.

Make sure you have the following line: file_uploads = On

To set/change upload  limit modify upload_max_filesize line. In my case, I put there upload_max_filesize = 100M