X

An Oracle blog about Oracle Integration Cloud(OIC)

Try Oracle Cloud Platform For Free
  • February 27, 2018

Oracle Integration Cloud (OIC) - File-based Integration Best Practices

Jack Desai

Introduction

 

Oracle Integration Cloud Service (OIC) provides several design and modeling options to receive and process files to support wide variety of use cases and scenarios. The file can be received in OIC from Secure Shell (SSH) File Transfer Protocol (sFTP) server or through REST/SOAP/HTTP interfaces. The goal of this blog is to highlight key design options based on your file size, content format and delivery (outbound). It also highlights common Fusion Applications file-based integration patterns including key orchestration steps in OIC.

 

There are several integration patterns in Oracle Fusion Applications that are file-based. For example:

  • ERP Cloud supports importing bulk data through File Based Data Integration (FBDI) interface
  • HCM Cloud supports importing bulk data through HCM Data Loader (HDL) interface
  • CRM Cloud supports similar patterns
  • ERP Cloud Bulk Extracts (BI Publisher or BI Cloud Connector) where files are delivered to UCM or sFTP server
  • HCM Cloud Extracts where files are delivered to UCM server
  • Uploading attachments into ERP or HCM such as invoices, purchase orders and other supporting documents.

 

For more information, refer ERP FBDI process, HCM HDL process​ and CRM File-based Data import process.

 

All ( ERP FBDI, HCM HDL and CRM) file-based integration mandates to generate a data file based on respective object template in comma separated value (CSV) format. The file could be generated by on-premise system or other cloud applications and uploaded it to sFTP server for OIC consumption. Optionally, file could also be uploaded to Fusion Applications Content Server (UCM).

 

An integration flow in OIC will consume the file and optionally process it with actions such as enrichment and transformation to generate a data file of respective Fusion Applications object. If data file is generated in on-premise system as per respective object template, OIC can pass-through this file to ERP or HCM Cloud to start the import process.

 

Similarly, files could be extracted from Fusion Applications and deliver it to OIC through UCM server. The UCM server resides in Fusion Applications where extracted files are delivered and stored for downstream processing.

 

The following table provides high-level decision model to architect file-based integration flows based on decompressed file size:

Table 1 - File Sizes Supported by Interfaces (based on decompressed file)

Decompressed 

File Size

Inbound

Interfaces

Runtime

Considerations

Outbound

Interfaces
<= 1MB Any Adapters No limitations

Any Adapters (provided outbound file size is <= 10MB)

Supports both BASE64 encoding and file attachment
1MB to 10MB Any Adapters

No limitations for file pass-through

File Processing Limitations:

  • Only XML or CSV format supported
  • Use StageFile-Read File in Segments (Read Entire File not supported)

Any Adapters

Supports both BASE64 encoding and file attachment
>10MB to 500MB FTP, REST and SOAP Adapters

No limitations for file pass-through

File Processing Limitations:

  • Only XML or CSV format supported
  • Use StageFile-Read File in Segments (Read Entire File not supported)

Attachments Only - Any adapters that supports file attachment (FTP, REST, SOAP)

> 500MB

Note: The maximum file size limit depends on concurrency, resources available and I/O timeout considerations (default 300 second)

 FTP, REST and SOAP Adapters

No limitations for file pass-through

File Processing Limitations:

  • Only XML or CSV format supported
  • Use StageFile-Read File in Segments (Read Entire File not supported)

Attachments Only - Any adapters that supports file attachment (FTP, REST, SOAP)

 

 Note: For any incoming or outgoing file, the upload/download automatically times out after 300 seconds

 

 

OIC Design/Modeling Consideration

 

The file size, content format and processing requirements dictates how to model an integration flow in OIC. The following table shows how to design your integration flows based on file size, content and whether it’s pass-through or requires enrichment and transformation actions to generate a file that Fusion Applications supports for the respective object or interface:

 

Table 2 - Decision Model (File Sizes are based on decompressed file)

# Inbound File Interface Inbound Decompressed File Size Key Orchestration Steps Outbound File Size File Delivery Outbound Interfaces
1

FTP - Read File or StageFile - Read Entire File

- Decompressed File Only

- Content Type: XML or CSV

 

Note: Should not be used for binary files (irrespective of file size) - recommendation is to use FTP-Download File.

 <= 1MB

  1. FTP - Read File or StageFile - Read Entire File

  2. Enrichment and Transformation

  3. StageFile - Write (create Target File) Note: Write operations used within a parallel "for each" supports CSV format only!

  4. StageFile - Zip

  5. Outbound: FTP/SOAP/REST - Mapper with encodeReferenceToBase64 function or Attachment

 <= 10MB
  • BASE64
  • Attachment
 Any Interface
2

FTP

- Compressed or Decompressed

- Content Type: XML or CSV

NOTE: Binary files cannot be read using StageFile operations.

 <= 10MB

  1. FTP - List File

  2. FTP - Download File

  3. StageFile - Read File in Segments

  4. Enrichment and Transformation

  5. StageFile - Write (create Target File) Note: Write operations used within a parallel "for each" supports CSV format only

  6. StageFile - Zip

  7.  Outbound: FTP/SOAP/REST - Mapper with encodeReferenceToBase64 function or Attachment

 <= 10MB
  • BASE64
  • Attachment
 Any Interface
3

FTP (Same as #2, but file size is greater than 10MB)

 

NOTE: Binary files cannot be read using StageFile operations.

 
 Greater than 10MB

  1. FTP - List File

  2. FTP - Download File

  3. StageFile - Read File in Segments

  4. Enrichment and Transformation

  5. StageFile - Write (create Target File) Note: Write operations used within a parallel "for each" supports CSV format only!

  6. StageFile - Zip

  7. Outbound: FTP/SOAP/REST - Mapper - Attachment Only

 Greater than 10MB Attachment Only FTP/SOAP/REST
4

REST - OIC Interface of integration flow - Base64

- Compressed or Decompressed

- XML/CSV Format Only 

NOTE: Binary files cannot be read using StageFile operations.

 <= 10MB

  1. File Reference in Mapper (use decodeReferenceFromBase64 or Attachment)

  2. StageFile - Unzip

  3. StageFile - Read File in Segments

  4. Enrichment and Transformation

  5. StageFile - Write (create Target File) Note: Write operations used within a parallel "for each" supports CSV format only

  6. StageFile - Zip

  7. Outbound: FTP/SOAP/REST - Mapper with encodeReferenceToBase64 function or Attachment

 <= 10MB
  • BASE64
  • Attachment
 Any Interface
5

REST - OIC Interface of integration flow - Attachment

- Compressed or Decompressed

- XML/CSV Format Only 

NOTE: Binary files cannot be read using StageFile operations.

 Greater than10MB

  1. FileReference in Mapper

  2. StageFile - Unzip

  3. StageFile - Read File in Segments

  4. Enrichment and Transformation

  5. StageFile - Write (create Target File) Note: Write operations used within a parallel "for each" supports CSV format only!

  6. StageFile - Zip

  7. Outbound: FTP/SOAP/REST -Mapper - Attachment Only

 Greater than 10MB Attachment Only FTP/SOAP/REST
6

File in intermediate SOAP/REST Response Payload

- Compressed or Decompressed

- XML/CSV Format Only 

NOTE: Binary files cannot be read using StageFile operations.

 <= 10MB

  1. File Reference in Mapper (use decodeReferenceFromBase64 or Attachment)

  2. StageFile - Unzip

  3. StageFile - Read File in Segments

  4. Enrichment and Transformation

  5. StageFile - Write (create Target File) Note: Write operations used within a parallel "for each" supports CSV format only!

  6. StageFile - Zip

  7. Outbound: FTP/SOAP/REST -Mapper with encodeReferenceToBase64 function or Attachment

 <= 10MB
  • BASE64
  • Attachment
 Any Interface
7

Same as #6, but file size is greater than 10MB

 

NOTE: Binary files cannot be read using StageFile operations.

 Greater than 10MB

  1. FileReference in Mapper

  2. StageFile - Unzip

  3. StageFile - Read File in Segments

  4. Enrichment and Transformation

  5. StageFile - Write (create Target File) Note: Write operations used within a parallel "for each" supports CSV format only

  6. StageFile - Zip

  7. Outbound: FTP/SOAP/REST -Mapper - Attachment Only

 Greater than 10MB Attachment Only FTP/SOAP/REST

 

Fusion Applications - SaaS Common Use Cases with file-based integrations

Table 3 - Fusion Application Use Cases

# 

 Use Cases OIC Key Orchestration Steps
1

Get data file from sFTP server and send compressed data file to ERP/HCM/UCM SOAP Integration Interface as Pass-through

  • Compressed File Size: <= 10MB
  • Content Type: XML or CSV
  • (Binary only for pass-through)

 

Example:

  • FBDI or HDL bulk imports where data file is generated accordingly with respective template and do not need any changes or processing prior to sending it to ERP or HCM Cloud to start import process.
  • Consider using model #2 or #3 from Table 2 without enrichment and transformation steps as data file is pass-through 
  • FTP - List File
  • FTP - Download File
  • Mapper -  encodeReferenceToBase64 or Attachment
  • Outbound - BASE64 or Attachment
2

Same as #1, but file size is greater than 10MB

Example:

  • FBDI or HDL bulk imports where data file is generated accordingly with respective template and do not need any changes or processing prior to sending it to ERP or HCM Cloud to start import process.
  • Consider using model #3  from Table 2 without enrichment and transformation steps as data file is pass-through

  • FTP - List File

  • FTP - Download File

  • Mapper - Attachment Only

  • Outbound - Attachment Only
3

Get data file from sFTP server - process/enhance data file and send revised file to ERP/HCM SOAP Integration Interface

  • Decompressed File Size  <= 10MB
  • Content Type: XML/CSV

 

Example:

  • FBDI or HDL bulk imports where FBDI or HDL files need to be generated based on source file and enrichment/transformation to respective target file
  • Consider using model #2 or #3  from Table 2

  • FTP - List File

  • FTP - Download File

  • Mapper -  encodeReferenceToBase64 or Attachment

  • StageFile - Read File in Segments

  • Enrichment and Transformation

  • StageFile - Write (create Target File) Note: Write operations used within a parallel "for each" supports CSV format only

  • StageFile - Zip

  • Outbound: FTP/SOAP/REST - Mapper with encodeReferenceToBase64 function or Attachment
4

Same as #3, but decompressed file size greater than 10MB

 

Example:

  • FBDI or HDL bulk imports where FBDI or HDL files need to be generated based on source file and enrichment/transformation to respective target file
  • Consider using model #3  from Table 2
  • FTP - List File
  • FTP - Download File
  • Mapper -  encodeReferenceToBase64 or Attachment

  • StageFile - Read File in Segments

  • Enrichment and Transformation

  • StageFile - Write (create Target File) Note: Write operations used within a parallel "for each" supports CSV format only

  • StageFile - Zip

  • Outbound: Mapper with Attachment Only
5

Get file from Fusion Application UCM Server

  • Decompressed File size <= 10MB
  • Content Type: XML or CSV

 

Example:

  • Download file from FA UCM generated by ERP/BIP or HCM Extracts or source file uploaded to UCM instead of sFTP server
  • Consider using model #6 or #7 from Table 2

  • Invoke UCM SOAP Service

  • Select Attachment Option in SOAP Adapter

  • Mapper -  encodeReferenceToBase64 or Attachment

  • StageFile - Unzip

  • StageFile - Read File in Segments
6

Same as #5, but file size is greater than 10MB

 

Example:

  • Download file from FA UCM generated by ERP/BIP or HCM Extracts or source file uploaded to UCM instead of sFTP server
  • Consider using model #7 from Table 2

  • Invoke UCM SOAP Service

  • Select Attachment Option in SOAP Adapter

  • Mapper - Attachment Only

  • StageFile - Unzip

  • StageFile - Read File in Segments
7

Receive (Response) File from any SOAP/REST/HTTP interfaces with media type: application/octet-stream

 

Example:

  • Download file attached in SOAP/REST/HTTP responses
  • Consider using model #7 from Table 2

  • Invoke SOAP/REST interface to get a file in response payload

  • Select Attachment option (SOAP/REST - API must support attachments)

  • Mapper - Attachment Only

  • StageFile - Read File in Segments
8

OIC/ICS REST Interface - Receive (Request) file when OIC integration flow is invoked through REST interface - Multipart

  • File size: > 10MB
  • Content Type: XML or CSV

 

Example:

Invoke OIC integration flow from on-premise or other cloud systems

  • Receive file in OIC (invoked from on-premise or non-OIC system)

  • Mapper - Attachment Only

  • StageFile - Read File in Segments

 

 

Conclusion

This blog assists in modeling the integration flow in OIC based on above best practices and common file-based integration use cases in Fusion Applications or any other applications. It provides detailed information on how to handle and process files in OIC based on file size, content format and delivery.

Join the discussion

Comments ( 2 )
  • Krishna Friday, May 3, 2019
    Hi,

    I am using Rest API to get an attachment in response.but am facing 500-internal server error,


    Note:- am using "process attachments in response" in Rest API Response.


    Request you to please help me on the same.


    Thanks and Regards
    Krishna
  • Atul Thursday, August 8, 2019
    Hi Jack,

    The post is very informative. Although I have a query on the same. Hope you can help on this.

    Query- Can OIC will be able to place a 450 MB File on OUCM. And the steps needed to achieve this.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content