Tuesday Sep 16, 2008

Focus On Publisher

[Read More]

Friday Jul 25, 2008

File Attachments

[Read More]

Wednesday Jul 09, 2008

Multiple DataSources II

[Read More]

Tuesday Aug 28, 2007

BIP & Disco Do The Hustle

[Read More]

Monday Aug 27, 2007

Come in from cold ...

[Read More]

Thursday Aug 09, 2007

Template Building 10.1.3.3 Style

[Read More]

Wednesday Aug 08, 2007

AppsLabbing It

[Read More]

Tuesday Aug 07, 2007

No Database Trippin'

[Read More]

Friday Aug 03, 2007

PDF Data Entry - Part IV

 

Gums still aching here in stormy Colorado but Im finally bringing things together into a single servlet to receive, process and insert the data. Last time I mentioned that we needed to get an employee id from the appropriate database sequence prior to executing the 'insert' command.


The following code will retrieve the next ID from the employees sequence:


 

    private int getEmpID()
    {
      int empID = 0;
      try
      {
        // Try and geta connection to the db
        Connection conn = getConnection();
        // fetch the report name based on the request id
        PreparedStatement getID = conn.prepareStatement("select employees_seq.nextval from dual");
        
        // 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 idRslt = getID.executeQuery();
        idRslt.next();
        empID = idRslt.getInt(1);
        
        //Clean up 
        idRslt.close();
        getID.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 empID;
    } 

 


This assumes we have a method called getConnection() to get a connection to the database. Now we have the employee ID we can correctly construct the sql statement to insert a new employee. Now we can complete the insert statement string by adding the employee id and its value


 

        //Add the emp id column and value
         sqlColsBuffer.append(",EMPLOYEE_ID");
         sqlValsBuffer.append(","+Integer.toString(getEmpID()));
        
        sqlStmtBuffer.append(sqlColsBuffer.append(")").toString());
        sqlStmtBuffer.append(sqlValsBuffer.append(")").toString());
This gets us the followning statement

insert into employees(DEPT_ID,SALARY,EMAIL,LAST_NAME,PHONE_NUMBER,
                                   JOB_ID,HIRE_DATE,FIRST_NAME,EMPLOYEE_ID) 
values ('50','44444','tim.dexter@oracle.com','Dexter','343434343'
                                 ,'ST_MAN','01jan2003','Tim',214)

 


If you look closely you'll see that the hire date is in the wrong format - we need to force the user to enter it in the correct format otherwise our insert will fail. Something else you may have noticed is that DEPT_ID is not the column name in the EMPLOYEES table it should be DEPARTMENT_ID. Watch out! Either match the field names in the PDF form to the column names or do the conversion in your code to the right names. 


Now we can insert the record:


 

    private boolean insertRecord(String sqlStmt){
        boolean insFlg = true;
        Connection conn = getConnection();
        try
        {
        Statement stmt = conn.createStatement();
        stmt.execute(sqlStmt);
        conn.commit();
        stmt.close();
        conn.close();
        
        }
        catch (SQLException sqle){
            sqle.printStackTrace();
            insFlg = false;
        }
    return insFlg;   
    }

 


We pass the SQL statement in as a parameter when we call the insert method:


 

        if (!insertRecord(sqlStmtBuffer.toString())){
            
            out.println("Error: 45500: The employee registry could not be" +
            "updated with your information.<BR>" +
            "Please contact as administrator on x66633 for help.");
        }

 


I have the method returning a boolean so that I can inform the user if the insertion fails and provide a message to them to contact an administrator.
To prove it worked, here I am in the table as a new employee ...


EmpDataForm4:


A nice surprise the date got converted as the record was inserted ... there is a trigger on the table.


Hope you found this series useful or at least interesting, Publisher has a lot to offer to help with data entry via PDF. With some java coding you can build a system quite quickly, I built this demo in about a day. I went for simple db connections and servlets just to allow me to focus on the publisher aspects, you of course can use other methods and be a lot tighter on your server side validation.
The XFDF XML format does have a great advantage in that its a fixed schema so you could conceivably build multiple forms that feed into a single servlet on the server and then it can redirect to table handlers based on some hidden field value.
I have to thank Incheol Kang from the development team for some help along the way and we got 2 enhancement requests out of this exercise so it was useful for us. If you are interested in the source code its all in this zip file.
Happy Data Entering!

 

Thursday Jul 26, 2007

PDF Data Entry - Part I

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.


EmpDataForm1:


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.

EmpDataForm2:


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.

About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today