Oracle BI Publisher Blog

I have been nagged by many folks for a how-to on data entry via PDF forms. A certain competitor provides the ability to create a PDF form and then allow users to enter data via Adobe Acrobat or Reader and push it into the application tables. Well now you can do it with E Business Suite, I should add its not out of the box, there are a few applications teams looking at this and a few have already started to provide this type of functionality. I indebted to Incheol on our development staff for help on some of the finer points ... Publisher is getting so big it's tough to keep up with all its capabilities. 

Some folks will be asking 'whats the point?, there are forms and pages provided by the application for data entry, why not use those?'

It's a fair question but when you think about entering orders or invoices into a system your users may want a faster medium than Oracle Forms to get the data in ... the more obvious choice would be Excel - I'll cover that in another post. How about wanting to provide your customers or suppliers the opportunity to enter data into your system but you do not want them to use the professional apps. Or maybe you want to collect  I-9 information from your new hires and rather than develop a user interface, why not use the govenrment provided I-9 form.  

Here's where a PDF form can be very handy, its a reasonably familiar format for users now, Adobe Reader is almost ubiquitous on the desktop so minimal install for them and you have the power to make the form as simple as you like.

Im going to split this article into a few sections, 1 to save you reading and 2 to save me writing massive amounts of editorial. Im going to start with creating the PDF form itself. Sounds simple enough and ought not to warrant an article to itself I hear you say. But Publisher a few tricks up its sleeve that can prove very handy.

Let's assume you want to collect employee information using a PDF form.

Building the form is going to require either Adobe Acrobat 5 or higher. If you are building a form from scratch rather than using a 3rd party form e.g. I-9 you typically do not start with a blank page. Use Word, scan in a form or similar to build a layout and then add the data entry fields to the form. 

There may well be some information you already know, in our case, the list of job descriptions, manager and department names. You could hard code these but things change so if you can make it dynamic so much the better. Fortunately publisher can help you here, it has the ability to populate fields and combo boxes for you.

The table we want to populate is the EMPLOYEES table in the demo HR schema.

Name              Null     Type
----------------- -------- ------------
EMPLOYEE_ID       NOT NULL NUMBER(6) 
FIRST_NAME                 VARCHAR2(20)
LAST_NAME         NOT NULL VARCHAR2(25)
EMAIL             NOT NULL VARCHAR2(25)
PHONE_NUMBER               VARCHAR2(20)
HIRE_DATE         NOT NULL DATE
JOB_ID            NOT NULL VARCHAR2(10)
SALARY                     NUMBER(8,2) 
COMMISSION_PCT             NUMBER(2,2)
MANAGER_ID                 NUMBER(6)
DEPARTMENT_ID              NUMBER(4)

Notice that the table does not store job, manager and department names but rather the relevant IDs. Therefore when we populate the combo boxes we need to extract the value (for the user to select) and the 'id' so that the table can be updated with the 'id' rather than the name.

We can use the FormProcessor API to populate the combo boxes:

    String pdfTemplate = "D:\\Work\\DataEntry\\EmpForm.pdf";
    String pdfData = "D:\\Work\\DataEntry\\Data.xml";
    String pdfForm = "D:\\Work\\DataEntry\\EmpFormEntry.pdf";
    
        try {
         System.out.print("Starting to fill form ...");
           
           FormProcessor fp = new FormProcessor();
           fp.setTemplate(pdfTemplate);
           fp.setOutput(pdfForm);
           fp.setData(pdfData);





           Properties prop = new Properties();
           prop.setProperty("all-field-readonly","false");
           fp.setConfig(prop);            
           fp.setLOV("JOB_ID",getJobHash());
           fp.setLOV("MANAGER_ID",getMgerHash());
           fp.setLOV("DEPT_ID",getDeptHash());

           fp.process();
        } 
        
        catch (Exception ex){
            ex.printStackTrace();
        }

Its pretty straightforward, we need to use some dummy data with the PDF template (form). If you had fields that you could pre-populate you would put this data into the XML. In our case we did not have any. 

The two main things to notice here are:
1. We need to set the "all-field-readonly" property to false. Without this the fields in the resulting form will be set to read only.
2. We are using the setLOV method to populate the combo boxes in the PDF form. It has a couple of signatures but Im using the following:

setLOV(java.lang.String fieldName, java.util.Hashtable values)

I have fields named JOB_ID, MANAGER_ID and DEPT_ID. To populate the hashtables for each I connect to the database and select the appropriate data. For the MANAGER_ID hash I have:

private Hashtable getMgerHash()    {
      int mgerID;
      String mgerName = "";
      //Hsh table for manager information
      Hashtable mgerHash = new Hashtable();
      System.out.println("Loading Managers \n");
      try
      {
        // Try and geta connection to the db
        Connection conn = getConnection();
       // Fetch the emp id and name for those emps that are managers
        PreparedStatement getHash 
                = conn.prepareStatement("select distinct e.employee_id ID," +
                                      "e.first_name||' '||e.last_name NAME \n" + 
                                      "from employees e, employees f\n" +
                                      "where f.manager_id = e.employee_id");
       
        ResultSet locRslt = getHash.executeQuery();
        // loop thru the dataset and load values to the hashtable
        while (locRslt.next()){
        
            mgerID = locRslt.getInt(1);
            mgerName = locRslt.getString(2);
            mgerHash.put(String.valueOf(mgerID),mgerName);
           }
        //Clean up 
        locRslt.close();
        getHash.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(mgerHash);
    }    

This passes a filled hashtable back to the main code:

           fp.setLOV("MANAGER_ID",getMgerHash());

Once the code completes we have pre filled the form with the latest combo box values for the user to select appropriate values. They will select a value from the list but the id will be stored.
Publisher can even help you inject javascript into the form as well. I have not included a sample here but check out the the JavascriptEditor class in the javadocs. You can inject/replace javascript at document and field level. 
You can get the starting PDFForm, the resulting pre-populated form and the java class here.

You'll have noticed the Submit button ... more on that next.