Uploading Excel Spreadsheet into Oracle eBusiness Suite. Part 3.

Building UI
First of all, we need to define business objects and explain their usage. The page we are building contains one field on top of the page where the user specifies the file to upload, and then a table below where user select the purpose for the upload.

The content for the table comes from a view object FileUploadPurposeVO which is sourced by the query from the technical design. We add to it a transient updatable attribute ‘Selected’ to allow for single table selection.

The content of the uploaded file is stored in the database table XXPT_FILE_UPLOAD_TMP. So, we create an entity object XXPTFileUploadTmpEO and corresponding view object XXPTFileUploadTmpVO.

We also need a primary key generator. The first thought, of course, is to define a custom sequence. I do not like this idea since the primary key here does not need to be sequential; it just needs to be unique. So, the sequence will be an extra custom database object without a very good need. We can also define or reuse a document sequence. However, this is a lot of application setups for a primary key which will live only a couple of seconds.

The approach I prefer is to use the GUID generated by the database and convert it into a number. The following query generates unique number each run you execute it.

select okc_p_util.raw_to_number(sys_guid()) id from dual

We build a view object PrimaryKeyGeneratorVO based on this query. This view object completes our data model.

There are many examples available over the internet and in the Developer’s Guide on how to build an OAF page. We will skip those here. The relevant assumption is that the table which displays the upload purposes is a single selection table with an action button ‘submitButton’.

This makes the page controller entry point look like this

/**
* Procedure to handle form submissions for form elements in
* a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
OAApplicationModule am = pageContext.getApplicationModule(webBean);

if (pageContext.getParameter(”submitButton”) != null)
{ processFileUpload(pageContext, webBean, am);
}
}

Method processFileUpload is performing the following tasks

• get hold of the binary file contents
• convert binary file contents into an ASCII stream
• store ASCII in the file in the database
• submit the printing request

The body of this method is

protected void processFileUpload(OAPageContext pageContext, OAWebBean webBean, OAApplicationModule am)
{

// Get hold of the binary file contents
DataObject fileUploadData = (DataObject)pageContext.getNamedDataObject(”fileUploadItem”);
if (fileUploadData == null) return;

String fileName = (String)fileUploadData.selectValue(null, “UPLOAD_FILE_NAME”);
if (fileName == null) return;

String contentType =(String)fileUploadData.selectValue(null, “UPLOAD_FILE_MIME_TYPE”);

BlobDomain uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, fileName);
if (uploadedByteStream == null) return;

// convert binary file contents into an ASCII stream
try {
String inputStream = streamToString(contentType, uploadedByteStream.getInputStream() );

String purposeCode = getSelectedPurpose(am);
if (purposeCode == null)
{
throw new OAException(”XXRFG”,
“XXRFG_SCR0248_MISSING_PURPOSE”,
null,
OAException.ERROR,
null);
}

// Store ASCII in the file in the database
Number primaryKey = storeStream(inputStream , purposeCode, (UploadAMImpl)am);

int orgId = ((OADBTransactionImpl)am.getOADBTransaction()).getOrgId();
// submit the printing request
submitConcurrentProgram(primaryKey, purposeCode, orgId, am.getOADBTransaction().getJdbcConnection());

} catch (IOException ex) {
throw OAException.wrapperException(ex);
} catch (SQLException ex) {
throw OAException.wrapperException(ex);
} catch (RequestSubmissionException ex) {
throw OAException.wrapperException(ex);
}
}

Stay tuned to go into each of the methods…