Uploading Excel Spreadsheet into Oracle eBusiness Suite. Part 2.

Technical Design
We build a new OAF page for the user to specify the file to upload. Since we are building a generic mechanism, the user also specifies the purpose of the upload. For example, price lists, or project budgets, or manual invoices. The list of purposes is presented as a table with name and description of the purpose for the upload. An advanced implementation can also display a URL for the file template which users can download and populate with data.

The purposes can be defined via a lookup and extracted by a query below

select lookup_code
, meaning
, description
, attribute1 template_url
from fnd_lookup_values_vl
where lookup_type = ‘XXPT_FILE_UPLOAD_PURPOSE’
and enabled_flag = ‘Y’
and sysdate between start_date_active and nvl(end_date_active, sysdate)
and view_application_id = 3
and security_group_id = 0

The data file needs to be uploaded somewhere for processing.

Since we are building an OAF page, we have two places for data processing. We can parse the file inside the page controller, and place the data into the destination tables directly. This approach allows for immediate scan of the incoming data and error reporting. The drawback is that any change to the list of files or to the file structure itself requires changes to Java code.

The only other place where we can place the data is the database. The incoming file can be placed into a customer table into a CLOB field for further processing. For that purpose, we create a table XXPT_FILE_UPLOAD_TMP with the following fields

Since the user can upload binary files such as Excel workbooks, the page controller needs to recognize those and transform into an ASCII file. In out example we will use JExcelAPI library to transform Excel sheets into CSV stream.

We anticipate that most of uploads are for data stored in a tabular format. This assumption makes SQL*Loader our preferred data upload tool. The tool is very generic and can upload any delimited or fixed width flat file into the destination tables. Changes to the data structure can easily be addressed by changing the control file. This change can be carried by most technical consultants even without knowledge of Oracle eBusiness Suite.

However, it makes storing data in a database CLOB very impractical. We need it in a flat file somewhere where SQL*Loader can read. Since SQL*Loader is submitted as a concurrent request, we need to store the flat file in a place from which the concurrent manager can read it, for example, an output of another concurrent request.

To achieve this, we will build a Java concurrent program that is submitted immediately after the file is uploaded into XXPT_FILE_UPLOAD_TMP table. The concurrent program dumps file contents into its output stream. This concurrent program achieves our file transport goal, i.e. data file is transported to a place where it can be processed by Oracle eBusiness Suite.

The output of the concurrent program can now be passed to the SQL*Loader as the input data file. If the data file is an XML message, we can pass it to BI Publisher for processing, or write our own Java concurrent program to parse and process the message. We will use the implementation of the Chain of Command described in previous articles to chain the processing concurrent program.

Stay tuned to learn how to walk each step of the design…