Fusion SaaS: Perform efficient SQL validation for incoming files by using Custom Object CLOB fields, OIC and BIP

General

Importing external files into Oracle Fusion SaaS is a common requirement. Oracle provides HDL and FBDI that perform efficient pre-validations on input files before loading them to the system.
This post will show a generic approach of performing efficient SQL validation of custom incoming file by utilizing OIC, Application Composer Custom Object CLOB fields and BIP SQL queries.

Use Case

Allow user to perform custom mass upload of Sales Orders into Oracle Fusion SCM.

High Level Implementation Details

1. Create an utility Custom Object with Long text field (utilizing CLOB data type).
2. Read the incoming file by OIC and convert the incoming format to XML.
3. Create a record in the utility Custom Object above and upload the full content of the XML representation containing the input file content to the record created into CLOB column.
4. Invoke BIP Report with SQL data set in the model.
   The BIP report will utilize XMLTable feature of Oracle database, can query the file content as if it was a regular table and perform all validations and defaulting on SQL level.
   Such a query can access the file content along with Fusion SaaS database tables.
5. Get the results of the SQL and perform updates to Fusion by FBDI or REST API.

Detailed Implementation Details

1. Create Custom Object, for example File Upload Request with the following fields, File Content defined as Long Text will be stored as CLOB
   
2. In OIC – Read any input file, for example CSV and convert it to XML. In our case – the file is CSV and we are converting it to XML by a simple mapping in OIC while writing into XML stage file:
   
3. Create a record inside the Custom Object above and upload the file content.
   As the record contains the CLOB field it can not be uploaded directly when creating the record in custom object.
   The process is done in 2 steps:
   • Create a custom object record and receive the created record id (POST operation).
     Example URL: /crmRestApi/resources/latest/FileUploadRequest_c
   • Upload the file content by using FileReference of the stage file created in step 2 and additional PUT operation.
     Example URL: /crmRestApi/resources/latest/FileUploadRequest_c/{id}/enclosure/FileContent_c, map the stream reference from stage file to the PUT operation.
4. Extract the XML version of the uploaded file by using the code similar to the query below which later can be joined to any validation against Fusion tables:
   
   The result can contain the full JSON body ready for the POST/PATCH operation:
   
5. Perform standard REST operation or generate and submit FBDI/HDL DAT files.