In this blog, we will use Oracle Managed File Transfer (MFT) and Integration Cloud Service (ICS) to automate batch uploads into Oracle ERP Cloud. We will perform this upload with invoices, a typical use-case for organisations that work with numerous vendors and would like their vendors to bulk invoice them through say an SFTP file drop.

Steps are as follows:

Drop the file into an SFTP folder that is managed by MFT

MFT picks it up and uploads it into ERP Cloud’s WebCenter Content file repository

MFT calls ICS to trigger a multi-step orchestration to load ERP Cloud

Using the ICS ERP Cloud adapter, the ICS orchestration first loads the file into staging tables and import the invoice into final tables

That is because HCM and ERP Cloud (also SCM and SalesCloud) are all Oracle Fusion Applications, modules built on the same platform. Some of you may even be on “Global Shared Instances”, essentially running both your HCM and ERP SaaSes in the same instance.

About MFT

MFT is Oracle’s strategic platform for batch file transfer in all our SaaS services (and more). It provides cool capabilities such as zip/unzip, splitting large files, PGP encryption, retries, embedded FTP/SFTP services that are HA-clustered with user credentials that can be stored in your external Identity Management System. All offered through a dashboard interface.

In short, no more messing around in command-line, OS-level scripting tools to manage your file transfers!

MFT also provides a flexible callout extension framework to trigger downstream activities after a file has been transferred. In this case, we are triggering ICS to perform a series of activities to load ERP Cloud.

ICS provides capabilities to string multiple transactions and transformations into a complex orchestration. A requirement for this ERP loading scenario.

Batch Loading ERP Cloud Manually

Batch Loading ERP Cloud through the browser consists of the following steps:

Create ERP Cloud Payload

Upload the file into WebCenter Content

Load the Interface File into Staging Tables

Import Invoices into Final Tables

Create an ERP Cloud Payload

ERP Cloud provides Excel Macros with sample content to guide payload creation. The output files are Zipped CSVs. If you’re integrating between 2 enterprise systems, you could request the vendor’s system to generate the CSVs directly.

*Note: My integration is for Fusion Release 11, so if you’re reading my blog from Year 2050, best to get a new copy!

Key in the relevant payload and hit [Create CSV File]

Upload File into WebCenter Content

Navigate to [Tools] -> [File Import and Export] and perform the upload. Maximum size of the import through this method Is 2GB.

*Note: Make sure to select the Account matching that task you’re trying to perform. For payables, I’m selecting “fin/payables/import”, otherwise this file cannot be seen by the [Load Interface File for Import] job.

Load Interface File for Import

Next we need to load the file’s data into Staging Tables. Batch processes in ERP Cloud run as Scheduled Process, so we will go to [Tools] -> [Scheduled Process] and then [Schedule New Process] -> [Load Interface File for Import]

Select the [Import Payables Invoices] and the file previously uploaded.

*Note: As you’ve select [Import Payables Invoices], you should only be able to see files uploaded to the relevant account “fin/payables/import”

Import Payables Invoices

Now you’re ready to load the final tables. Select [Tools] -> [Scheduled Process] and then [Schedule New Process] -> [Import Payables Invoices]

Select the [Business Unit] / [ Ledger] you would like to import the invoices to. Select your [Source] depending the field you’ve entered in the payload. In my situation, I’ve used the term [External]. This essentially pulls up the rows in the Staging Table that have source indicator of [External].

Alternatively, can you access the same screen from [Invoices] -> [Import Invoices]

Automating with Web Services

Batch processes in ERP Cloud run as Scheduled Processes and every Scheduled Process can be called by the ERP Integration Web Service.

The end-to-end jobs are a quick and dirty way to load data. However, this inserts the file as a Base64 SOAP payload. For large files and greater requirements such as retries, security and better traceability, we will use MFT as the transfer method for (a) and then perform (b)-(f) through ICS.

Filling in the Web Service Parameters

As the ERP Integration Web Service is a generic service, you will need point it to the necessary job to perform the activity. You will need:

Get Job Parameters

Now for the parameters, first run a batch load manually, then go to [Tools] -> [Scheduled Processes] and select the job you have triggered manually. The parameters are listed at the bottom of the job. Substitute necessary changes per your data, e.g. LedgerID and DocID

Create MFT Transfer to Perform the WebCenter Upload

Now let’s create the transfer process. This allows us to drop a file into MFT’s SFTP server that will be automatically uploaded to ERP’s WebCenter. Follow the steps from my previous blog for HCM and substitute with the ERP Cloud’s information instead.

As HCM and ERP are just modules deployed on the same platform, the WebCenter end-point is the same: https://[ERP_Base_URL]/cs/idcplg. In fact, if you are running a “Global Shared Instance”, you should see both your HCM and ERP files being drop in the same place!

You’ve probably noticed in the manual process prior, I’ve explained that Document Account must be “fin/payables/import”. Otherwise this file cannot be seen by the Load Interface File for Import job. Enter that information in [Advanced Properties] of the target:

Create an ICS SOAP Trigger

First create a new SOAP connector in ICS. As a shortcut, I am using the HCM WSDL as the SOAP definition for the ICS callout. If you’re using my demo orchestration code, you can grab the WSDL from here.

Create an ERP Cloud Connection

Create an ERP Cloud Connection, your connection WSDL should be similar to: https://{ERP_BASE_URL}/fndAppCoreServices/ServiceCatalogService?WSDL

If you also wish to listen to events created by ERP Cloud (not covered in this blog), you can enter https://{ERP_BASE_URL}/soa-infra

Create an ICS Orchestration

Now create an ICS orchestration that is triggered by the SOAP Trigger.

Load Interface File

To call the Load Interface File job, drag the connector onto the orchestration and select [Browse by Service] -> [ERPIntegrationService] -> [submitESSJobRequest]. This will essentially perform the Web Service call we described manually.

Perform the mapping to pass in the relevant payload. The MFT transfer will return a DocID from the WebCenter transfer. This is the reference to the file uploaded in the system. We have saved this value as ContentId in the Java Callout which will then be passed through ICS into the parameters. Note that the other values have come from our exploration of the [Scheduled Processes] run manually.

Import Payables Invoice

We will call the same Web Service for Import Payables Invoice with different parameters. In this situation, we have hardcoded the LedgerID. In the real-world, we would pass this in through ICS parameters.

Get Status of Load or Import

To check the status of a job, drag the connector onto the orchestration and select [Browse by Service] -> [ERPIntegrationService] -> [getESSJobStatus].

Perform the mapping to pass in the relevant payload. SubmitESSJobRequest returns a JobID, which we will keep querying till it returns SUCCEEDED or FAILED.

Perform the Transfer and View Success

Now grab your payload created earlier and drop the file into MFT’s SFTP embedded server.

Navigate to MFT and you can see the upload in progress.

Navigate to ICS and you can the orchestration has kicked off.

Navigate to [Scheduled Process] and you can see the jobs that have been called.

Click on [Import Payables Invoices Reports] -> [Succeeded] and you will see a BIPublisher (PDF) report and the items that have been inserted / failed. You can also retrieve this information through the Web Service call – downloadESSJobExecutionDetails.

Now finally if [Submit Invoice Import] has succeeded, navigate to [Invoices] and you can see that the new Invoices has been created under the [Recent] tab!

Blogroll

SolutionsANZ blog and contributors. All Rights Reserved. The views expressed in this blog are our own and do not necessarily reflect the views of Oracle Corporation. All content is provided on an 'as is' basis, without warranties or conditions of any kind, either express or implied, including, without limitation, any warranties or conditions of title, non-infringement, merchantability, or fitness for a particular purpose. You are solely responsible for determining the appropriateness of using or redistributing and assume any risks.

Follow Blog via Email

Enter your email address to follow this blog and receive notifications of new posts by email.