X
March 7, 2019

Using Oracle Integration with Managed File Transfer

By: Michael Meiner | Engineering Director

Share

Introduction

Oracle Integration (OIC) eliminates barriers between business applications through a combination of machine learning, embedded best-practice guidance, prebuilt integration and process automation. Integration Cloud provides the means for integrating applications, automating processes and building applications visually. Integration Cloud can also consume, produce, and perform transformations, on files.

For use cases where an sftp server is needed, Integration Cloud can be coupled with Oracle Managed File Transfer (MFTCS) Cloud Service to build and end-to-end solution. MFTCS has an embedded sftp server, along with features not available in traditional sftp solutions. For instance, MFTCS can integrate with other endpoints including: SOAP, Storage Service, ODI, B2B and WebCenter.

When provisioning OIC and MFTCS, you’ll want to provision them in the same Oracle datacenter for maximum performance.

 

Use Case

In our use case, we perform a bulk data import of external transactions into ERP cloud.  We introduce the following marketing personas: Mary is responsible for Customer Relationship Management (CRM) in Oracle EBS. We also introduce Bob, responsible for Enterprise Resource Planning (ERP) in Oracle Fusion ERP. Mary and Bob need these systems to sync automatically, without human intervention. EBS will be generating content that needs to be consumed by ERP.

The demonstration uses Oracle’s cloud integration capabilities to automate the bulk data import process. A .csv file of transaction records is transferred to an SFTP server (i.e., MFTCS), where it is picked up and processed by the Integration Cloud. If necessary, the integration flow can add additional data to the inbound transactions prior to passing the data off to ERP cloud.

A file is first generated by Oracle E-business Suite (EBS), which represents the external transactions. This file is uploaded to MFT Cloud Service. Oracle Integration Cloud pulls the files, transforms the data as needed, and then sends the file to ERP as a bulk data import.

See diagram below showing MFTCS, OIC and ERP. OIC pulls the file from MFTCS, then sends the file (bulk data) to ERP. Not shown in the diagram is the EBS system, which generates the bulk data file. This file, in turn, is uploaded to the MFT sftp embedded server.

Detailed Steps

Detailed steps are as follows:

  1. E-biz (EBS) generates a file (gsesample.zip)
  2. EBS uploads the file to MFTCS (sftp server)
  3. An Integration Cloud flow is run (scheduled to run every 10 minutes)
    1. Uses the OIC FTP adapter to read file from MFTCS server (note: FTP adapter also handled sftp traffic)
    2. Mapping performed to map data to ERP format (for ERP ImportBulkData)
    3. Bulk Data is sent to ERP using the OIC ERP Adapter

Note: steps 1,2 above were simulated, by taking an existing gsesample.zip and uploading it (using an sftp client) to the MFTCS embedded server.

Let’s take a look at how to set up OIC with MFTCS.

Figure 1: MFT 2 ERP Integration in OIC

 

This integration is scheduled to run every 10 minutes, whereupon it retrieves a list of all files with gse*.zip in the MFTCS embedded sftp server. For each matching zip file, the integration downloads file file using the ‘Download File’ operation of the FTP Adapter. Then it maps the contents of the zip file to the ERP bulk import format and uses the loadAndImport operation of the ERP Adapter to send the bulk data to the ERP system.

Note that once the file is pulled by the OIC FTP adapter, it is removed from the MFTCS embedded server. This ensures that the same file will not be read multiple times.

Now let’s look at the MFTCS configuration.

The embedded sftp server is configured on port 7522 (sftp). While an ftp server can also be configured we will only use the sftp server due to better security.

 

Figure 2: MFTCS embedded server port configuration

 

In the sftp embedded server configuration, we use authentication type of password. Other options are: public key and both.

Figure 3: MFTCS embedded server sftp configuration

For User Access, we give user: mftuser access to the folder of the same name. Note that user mftuser was already created using the WLS console (as per MFT documented procedures).

Figure 4: MFTCS embedded server user access configuration

Now let’s go back to our Integration (OIC), and configure it to connect to our MFTCS instance. We create an FTP Connection and supply the parameters (FTP server host address and port) of our MFTCS instance.

Figure 5: FTP Connection in OIC

In this same page, we also configure security by giving it the username (mftuser) and password. Note that we do not provide any key information, since MFTCS was configured with authentication type of password.

 

Figure 6: FTP Connection in OIC

We also create a connection using the ERP Adapter, giving it the endpoint URL and associated credentials.

Figure 7: ERP Connection in OIC

Both of these connections (FTP and ERP) are used in the integration shown in Figure 1.

Running the Integration

Now it’s time to run the integration. We copy our file (gsesample.zip) which is saved on a local drive, to the MFTCS embedded server (under directory mftuser). Within 10 minutes, the OIC Integration will run. We can then monitor and view the integration by going to Monitor->Integrations and selecting that run. Here, we see that the integration has detected a file, processed it and sent it to the ERP system.

Figure 8: Completed run in OIC

 

Note that if you select other integration runs where no file was found, you will see the bottom swimlane highlighted.

Summary

We see how OIC and MFTCS can be combined to enable upload to an sftp server, subsequent processing of such files and then integrated with a SaaS system such as ERP.

In my next posting, we’ll look at how this can be extended, by enabling a SOAP endpoint in MFTCS along with an MFTCS transfer.

For More Information

See Oracle Integration documentation for information about the full set of capabilities that Oracle's integration solution provides to customers globally. Oracle Integration solutions also help customers build a B2B content strategy and generate content workflows.

Acknowledgements

I would like to thank Bruce Bailey and Shiladitya Dasgputa for providing me with the OIC flow to integrate with ERP.

 

 

Engineering Director

Michael is a Director of Product Management for Oracle Integration. Specific responsibilities include: Migration to OIC; B2B Cloud Service; MFT Cloiud Service and also representing Oracle in various conferences including Integration Cloud Day.

More about Michael Meiner

Share