OIC and ICS File based integrations for Oracle HCM Cloud using UCM webservices

Introduction

This blog details the solution using Oracle Integration Cloud (OIC) for file based integrations using Universal Content Management (UCM) Webservices for Oracle HCM Cloud integration.

Deprecation of SaaS sFTP servers for Oracle HCM Cloud and other Oracle Cloud applications was announced recently.
Refer to the below MOS Support Doc Note for the announcement and related articles.
    Oracle Applications Cloud Hosted SFTP Server Deprecation Announcement (Doc ID 2312867.1)

Oracle Applications Cloud Hosted SFTP Server Deprecation Announcement (Doc ID 2312867.1)

This means that the SaaS hosted sFTP server will no longer be available for storage and managing files for Oracle HCM Cloud and ERP Cloud systems.

Subsequently, there is a need to rethink file based integrations that were in the past using the SaaS FTP server.
The recommended alternative is to use Oracle WebCenter Content Server (also known as Universal Content Management or just UCM in this blog), which is the standard method of data file transfers for Oracle Cloud Applications.
UCM can be used to store and transfer files such as BI Publisher extracts, Bulk data import and export files, HDL data files and other artifacts.

This blog summarizes the OIC solutions to move from 'sFTP based' to 'UCM based' flows for Oracle HCM Cloud integration.
Though the blog talks about Oracle HCM Cloud specifically, the solution pattern can be employed to integrate with other Oracle Cloud applications like ERP Cloud and also on-premise products like Product Data Hub (PDH) which use UCM (aka WebCenter Content Server) as the document repository.

UCM provides SOAP webservices to checkin documents, retrieve documents and search for documents. These will be used in the OIC flows detailed in next sections

Refer the link below for details of UCMs Core Content Webservices which will be used in the subsequent sections of this blog

Services Reference for Oracle WebCenter Content 

Scenarios

Here are the sample usecases discussed in the next sections

Inbound into HCM: Import data from third party FTP into Oracle HCM Cloud

1. Read HDL dat file from partner FTP server
2. Upload HDL file into UCM using UCM Webservices
3. Call the HCM Dataloader Service

Outbound from HCM: Retreive file from Oracle HCM Cloud and transfer to partner FTP server

1. Search in UCM for files to be exported
2. Retrieve files from UCM using UCM Webservices
3. Write the file into partner FTP server

Now let us look at implementation for these scenarios in detail using ICS.

Note that although the solutions shown below depict ICS screenshots, these apply for OIC as well as ICS.

OIC Note : In OIC, Oracle HCM Cloud Adapter and Oracle ERP Cloud Adapter now provide capability to upload files to Oracle WebCenter Content !

Refer the OIC Whats New for Sep 2018 here

For ICS, the below approach using SOAP adapter will still need to be used.

Inbound into HCM: Import data from third party FTP into Oracle HCM Cloud

Figure below shows the flow of the scheduled orchetration integration used for this inbound flow

[caption id="attachment_48505" align="aligncenter" width="180"] Inbound flow[/caption]

1. Read HDL dat file from partner FTP server

This step can be implemented using FTP Connection and the "download to ICS" operation.
It is common to receieve PGP encrypted zip files from third party sources, so select the options to decrypt and unzip as needed

2. Upload HDL file into UCM using UCM Webservices

The UCM Webservice with IdcService=CHECKIN_UNIVERSAL can be used to upload file contents into UCM repository
Let us configure SOAP Connection Invoke Operation to call the service; The file content downloaded from previous FTP step needs to be passed in UCM payload as an MTOM attachment
So remember to enable MTOM attachment option for the request payload when configuring the invoke activity !
Refer to the figure below which shows SOAP adapter configuration with MTOM enabled for request

Also the mapper for this SOAP adapter invoke can directly map the downloaded FTP fileReference into the SOAP payload's MTOM Attachment field. See figure below

Note the use of 'Field' repeating element when mapping values to UCM webservices payload.Refer to this doc on how repeating elements can be added and mapped using the ICS Mapperhttps://docs.oracle.com/en/cloud/paas/integration-cloud-service/ocmap/repeating-target-element-map-different-sources.html

3. Call the HCM Dataloader Service

Refer here for the HCM DataLoader webservice
This is a SOAP connection Invoke operation to call the importAndLoadData operation
Use the mapper to map the Unique identifier of the file (dDocName) to be imported to the ContentId field of the HCM data Loader request as shown below

The response payload contains the result of the load operation within importAndLoadDataResponse/result element.

Outbound from HCM: Retreive file from Oracle HCM Cloud and transfer to partner FTP server

Figure below shows the flow of the scheduled orchestration integration used for the outbound flow

1. Search in UCM for files to be exported

We use the UCM Webservice with IdcService=GET_SEARCH_RESULTS to search for files within UCM. Search criteria can be specified as Querytext for the search operation
There is no need to enable MTOM attachments on request or response payloads since this operation does not return the content of files.
Figure below shows sample mappings used for the search operation

The result of GET_SEARCH_RESULTS is a list of document metadata which includes the document ID (dID) of files matching the search criteria.
The dID will be needed in the next step to retrieve the file contents from UCM using another SOAP connection invoke.
Check the appendix for some example GET_SEARCH_RESULTS payloads for reference

The orchestration flow shown above depicts the iteration over the resultList to retreive list of dIDs from the search operation using "ForEach"
For each file dID, the subsequent step is performed to retrieve the file from UCM

2. Retrieve the file from UCM using UCM Webservices

The UCM Webservice with IdcService=GET_FILE is used to download the file contents from UCM repository as an attachment.
Figure below shows mapper with sample payload mapping for GET_FILE operation

This is accomplished again using SOAP Connection invoke operation.  Make sure to enable MTOM attachment processing on the response payload as shown below

Use Mapper to directly map the MTOM attachment to a FileReference of a file based activity which here is an FTP write operation onto a third party FTP server.
See response mapper in figure below

Note- If you are using ICS, note that some of these features are available only starting 18.1.3 of ICS (Feb 2018).
Specifically MTOM attachment feature is available only with "Early Uptake Enabled" on 18.1.3 ICS. Contact Oracle Support to have it enabled on your ICS instance

MTOM attachment feature is available only with "Early Uptake Enabled" on 18.1.3 ICS

Alternate implementation using base64 encoding instead of MTOM attachments

ICS 18.1.3 also brings 2 new ICS Mapper functions that can encode/decode base64 directly from File Contents. The File Contents can refer to VFS FileReferences from File based activities like FTP, Stage activities and Attachments. Below are the new functions. Refer the 18.1.3 documentation for details of these new functions.

encodeReferenceToBase64 - Accepts the VFS’s file reference as input and returns the base64–encoded content of the file as the return value

decodeBase64ToReference - Accepts the base64–encoded content as input, decodes it, stores the base64–decoded value in a file in the VFS, and returns the reference to this file

The new functions are available within Mappers under advanced functions as shown below

These functions provide an alternate way to set content into UCM webservice requests. This could be handy if SOAP Connection MTOM feature is not enabled on the ICS instance !
A FileReference from FTP download operation can be base64encoded and set as inline content in the UCM request payload for UNIVERSAL_CHECKIN operation
Similarly the Content within GET_FILE operation's response payload can be base64decoded and mapped to the next File based activity in the flow !

Below screenshots show how the GET_FILE can use base64 decode function instead of MTOM option

[caption id="attachment_48601" align="aligncenter" width="600"] Map UCM GET_FILE response payload's inline file content to FTP Write filereference[/caption]

[caption id="attachment_48600" align="aligncenter" width="600"] Decode the base64encoded UCM content using the new function and store directly in VFS file[/caption]

Similarly CHECKIN_UNIVERSAL UCM operation can use the encodeReferenceToBase64 operation to encode and map VFS file content to UCM Webservice's payload !

Conclusion

In the above sections we have seen how file based integrations can be implemented using OIC by employing UCM webservices and hence eliminating the need for Oracle cloud hosted sFTP server.

Appendix

Sample request responses for GET_FILE and CHECKIN_UNIVERSAL UCM services - here

Example search criteria using QueryText for GET_SEARCH_RESULTS UCM service - here

WebCenter Content (UCM) Services Reference Guide - here

Document Services - CHECKIN_UNIVERSAL and GET_FILE

Webcenter Content (UCM) SOAP services are usually found in the below Fusion SaaS URL

https://<FusionSaaSHostName>:443/idcws/GenericSoapPort?wsdl

The UCM Application is accessible at below Fusion SaaS URL

https://<FusionSaaSHostName>/cs