Automate File Based Data Import using ErpIntegrationService

A lot of customers are past the point of adapting Oracle Cloud Applications and are moving to the next step of the lifecycle, being integrating Oracle Cloud Applications within their enterprise architecture. Oracle is currently providing a plethora of CRUD web services, REST and SOAP for integration.
But in some cases the only ability to interface data is still through File Based Data Imports (FBDI).

Using FBDI you generate an input file for import using predelivered Excel sheets by Oracle. These input files need to be uploaded to UCM. After which you start the Load Interface File for Import process to load the data into staging tables. After this you start the appropriate import process to load the data into the actual transaction tables. These are all manual steps.

With the ErpIntegrationService service you can now automate the process of

• uploading the input file to UCM
• initiate the Load Interface File for Import process 
• initiate the import process

Where to start the full automation?
In order to automate this process from any backend system or middleware, you first need to extract the data and mimic the format that FBDI generates and in some cases zip the extract file.

Suppose we want to automate the import of Suppliers.
The FBDI template looks like this:

And generates following csv file:

In order to upload the extract (input file) to Oracle Cloud using the ErpIntegrationService you need to base64 encode the zip file that contains the csv input file.

Before you can invoke the importBulkData operation, you need to lookup the import process Path, Jobname and Parameters. These can be found via task Manage Custom Enterprise Scheduler Jobs for [module]. It is also a good practice to run the import process manually to see the actual parameter values.

Request
Using follow request you can now initiate the

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/" xmlns:erp="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:importBulkData>
         <typ:document>
            <erp:Content>UEsDBBQAAAAIADGhcUlqdPKdYAAAAHkAAAATAAAAUG96U3VwcGxpZXJzSW50LmNzdnMOcnUMcdVxDQrwzCtJTS9KLMnMz9PLzC3ILypxKs3JdkksSdTR0VHQcc4vAoqBpXWCSwsKcjJTi3R0ggNc/VziHUNDPPyDPKNcXXRAav2AynUgWCESzIJAGABq4eUCAFBLAQIUABQAAAAIADGhcUlqdPKdYAAAAHkAAAATAAAAAAAAAAEAAAAAAAAAAABQb3pTdXBwbGllcnNJbnQuY3N2UEsFBgAAAAABAAEAQQAAAJEAAAAAAA==</erp:Content>
            <erp:FileName>PozSuppliersInt.zip</erp:FileName>
            <erp:ContentType>zip</erp:ContentType>
         </typ:document>
         <typ:jobDetails>
            <erp:JobName>/oracle/apps/ess/prc/poz/supplierImport,ImportSuppliers</erp:JobName>
            <erp:ParameterList>NEW,N</erp:ParameterList>
         </typ:jobDetails>
         <typ:notificationCode>00</typ:notificationCode>
         <typ:callbackURL>#NULL</typ:callbackURL>
      </typ:importBulkData>
   </soapenv:Body>
</soapenv:Envelope>

Response

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
   <env:Header>
      <wsa:Action>http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService//ErpIntegrationService/importBulkDataResponse</wsa:Action>
      <wsa:MessageID>urn:uuid:ed27f0b1-2feb-432a-ad09-2b5c0f6c90db</wsa:MessageID>
   </env:Header>
   <env:Body>
      <ns0:importBulkDataResponse xmlns:ns0="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
         <result xmlns="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">1124309</result>
      </ns0:importBulkDataResponse>
   </env:Body>

</env:Envelope>


Initiated processes

Imported Supplier

A few pointers
The element notificationcode can have the following values:

First digit	0	No notification
	1	Email
	2	Bell
	3	Email & Bell
Second digit	0	Send in any case
	1	Send on success
	2	Send on error

The element callbackURL can be used to provide a (local) endpoint to a web service that will be invoked upon completion of the load and import sequence. 

For more details on the implementation of this callback web service, please see the blog post of Jack Desai