X

The Integration blog covers the latest in product updates, best practices, customer stories, and more.

  • May 13, 2019

Automating Supplier Data Synchronization using Oracle Integration

Narayana Murthy Pedapudi
Technical Director

Co-Author: Kanaka Vijay Kumar Korupolu, Principal Product Manager

Use Case

The Oracle SaaS is a modular suite of applications that includes Financials, HCM, Procurement, Projects, SCM offerings etc.
The Procurement cloud is designed to work as a complete procurement solution or as an extension to existing procurement applications (Cloud/On-Premise).

                                   

Organizations may not be in a position to adopt entire suite and completely replace their existing application portfolio, but organizations can successfully pursue coexistence strategies by selective uptake of cloud offerings that serve as a logical extension to their existing solutions.

Oracle Sourcing is the next generation application for smarter negotiations with the suppliers.  Sourcing cloud is an integral part of the Procurement Cloud and is generally implemented with other Procurement cloud products like Purchasing and Supplier Portal. Sourcing cloud can also be implemented as a standalone offering with other cloud/on-premise Procurement applications, that makes an ideal coexistence scenario.

In such coexistence scenarios, organizations across the world face typical challenges as how to create/synchronize master data and transactional data across different systems within the organization.

This use case covers importing of Suppliers data from an external 3rd party enterprise applications usually from an FTP server in to Oracle Procurement Cloud. Oracle Integration (OIC) is an excellent platform to build such integration use cases seamlessly.

Configure

There are certain pre-requisites that need to be performed in Procurement Cloud, they are detailed below.

1. Add roles required for the Integration

2. Create a Procurement Agent

Let's look at them in detail...

  1. Add the required roles for integration

There are certain roles that are required to access the Procurement application and perform setup and transaction activities.

The Supplier Import process is run by user with below roles:

  • The Supplier Administrator or
  • The Supplier Manager.
  • Integration Specialist

Login as super user who has access to security console

Note: “IT Security Manager” role is required for Security Console access

Navigator > tools > Security Console

Users > Search for Username

Click on Username

Click Edit > Click on Add Role

Add Supplier Administrator (or) Supplier Manager Roles and Integration Specialist Role

Click Save and Close

2. Create a Procurement Agent

Procurement Agent is a mandatory setup that will give an access to create Suppliers and Supplier related entities, hence we will do the agent configuration following the steps below.

Login as a Procurement super user: Calvin.roth / Password

  • Go to Procurement > Purchase Orders
  • Click on Task Bar, go to  Administration > Manage Procurement Agent

  • Click on Management Procurement Agents, then click on Create.

  • Save and Close.

Implement

Supplier data synchronization can be achieved through the file based data import (FBDI) pattern, which can be further simplified using Oracle Integration.

The generic architecture of the flow involves generating FBDI file, upload to Procurement Cloud, receive the callback then process further steps. Typically the flow appears as below...  (Image Credits - Oradocs Solutions)

Description of import-file-based-data-architecture.png follows

Process for the implementation of the above pattern includes below steps. (Image Credits - Oradocs Solutions

 

Description of import-file-based-data-flow.png follows

 

Here we can completely automate end to end use case right from

a. Preparing the FBDI file including manifest file

i.Generate Supplier Data file using FBDI xslm template

ii. Create Manifest file

b. Building the orchestration flow in Oracle Integration (OIC), that will take care of automating below 3 steps.

i. Uploading Supplier FBDI data file into UCM

ii. Import supplier data into interface tables

iii. Import supplier data from interface to base tables

c. Verifying the Supplier record created in Procurement Cloud

d. Generating a report and sending a callback using Oracle Integration ERP adapter capabilities.

Let's look at the steps in detail below...

a. Preparing the FBDI file including manifest file

i. Generate Supplier Data file using FBDI xlsm template

  • Go to the Oracle Help Center.
  • Click Cloud > Click Applications.
  • Under Enterprise Resource Planning > click Procurement
  • Click Integrate > Under Manage Integrations, click Get started with file-based data import.
  • Click on “+” to expand - File-Based Data Imports
  • Click on Suppliers
  • Make a note of UCM account - prc/supplier/import
  • Click the SupplierImportTemplate.xlsm template link in the File Links table to download

Preparing Data Using the XLSM Template

  • Each interface table is represented as a separate Excel sheet.
  • The interface table tabs contain sample data, that can be used as a guideline to understand how to populate the fields
  • The first row in each sheet contains column headers that represent the interface table columns. The columns are in the order that the control file expects them to be in the data file.

DO NOT:

  • Change the order of the columns in the Excel sheets. Changing the order of the columns will cause the load process to fail.
  • Delete columns not being used. Deleting columns will cause load process to fail.  You can hide columns that you do not intend to use; if needed, during the data creation process, but please reset to unhidden before upload.

Note: Please note the above sample data, specifically Supplier Name, we will use this to verify the supplier records imported into the Procurement Cloud.

  • Open the XLSM template. The first worksheet in file provides instructions for using the template

  • Enter data in the spreadsheet template. Follow the instructions on the Instructions and CSV Generation tab under the section titled Preparing the Table Data.
  • Click the Generate CSV File button.
  • A CSV file is generated that is compressed into a ZIP file.
  • Save the file.

ii. Create Manifest file

Login to Procurement Cloud and navigate as below

Navigator > Setup and Maintenance : Task bar > Search

Task: Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications

Search and select the above task.

Select Import Suppliers and click on EDIT

Make a note of Name and Path details.

  • Name: ImportSuppliers
  • Path:     /oracle/apps/ess/prc/poz/supplierImport/

Check, which parameters are Mandatory - Required and prepare the manifest file as shown below.

Create a zip file with CSV file that we have generated earlier and manifest file. See below for the sample.

Now the supplier data file is ready to feed to the integration flow that we are going to orchestrate in the next steps. Let's look at the orchestration of the flow and creating connections to Procurement cloud and FTP server.

b. Building the orchestration flow in Oracle Integration (OIC)

We will create connections to Procurement Cloud, FTP server and orchestrate an integration flow

i. Create Procurement Cloud Connection

Login to OIC console.

Click on Integrations to launch the Oracle Integration canvas, click on Connections, then click on Create option. Search for Oracle ERP Cloud.

Provide connection name, click on Create. Enter Procurement Cloud connection details

  • Service Catalog WSDL: https://<HostAddress>/fscmService/ServiceCatalogService?wsdl
  • User Name: calvin.roth
  • Password: XXXX

Now test the connection by clicking on Test option, upon successful Test, Save the connection.

ii. Create FTP connection

Now let's create the FTP Server connection. Click on Create option on the connections page.

Provide connection name, click on Create. Enter Procurement Cloud connection details in Configure Connectivity and Configure Security.

  • FTP Server Host Address: XXXXXXX
  • FTP Server Port: XX
  • SFTP Connection: XX
  • User Name: XXX
  • Password: XXX

Now test the connection by clicking on Test option, upon successful Test, Save the connection.

iii. Orchestrate the flow.

Click on Integrations in the Designer canvas. Click Create. Select Create Integration -  Style as a "Scheduled Orchestration"

Provide name for the Integration flow, leave defaults as is. Click on Create.

Note: Please make sure the Supplier FBDI data file is placed in a specific FTP location and mention the location and name of the file in adapter configuration as mentioned below.

In the next step configure FTP adapter to read Supplier FBDI data file that was created earlier, configuration summary has shown below.

Delete additional mapping that got created between a scheduler and the FTP configuration.

As next activity in the flow configure a Procurement Cloud configuration (Oracle ERP Cloud Adapter)

Now configure the data mapping between FTP and Procurement Cloud activities in the flow, by clicking on the map icon, and selecting edit option.

Drag and drop elements from source to target to map the FileReference and Properties-> filename and Properties->directory elements from the FTP endpoint to the Procurement endpoint as below in the mapper.

Click on Validate and Close.

Now configure the business identifiers by configuring Tracking option.

Configure schedule->startTime as a tracking field.

Save and Close the flow, then activate the flow.

Optionally you may choose to enable the trace logging for debugging purposes.

Now the flow is activated, click on the hamburger icon and click on Submit Now for manually submitting the scheduler. Ideally you would be scheduling the flow for the desired frequency expected by the business.

Go to Monitoring --> Tacking to track the instance that is triggered, once the instance is successful we can verify the scheduled job and supplier data eventually in procurement cloud.

Let's see how to do this in the next section.

c. Verifying the Supplier record created in Procurement Cloud

Login to Procurement Cloud, then go to Navigator-->Tools--> Scheduled Processes, then check for the scheduled job that got triggered for supplier data sync.

As the scheduled job is successful let's check supplier data imported in procurement cloud.

Go to Navigator-->Procurement-->Suppliers, from task bar Search -->Advanced - enter "OIC Supplier%"

d. Generating a report and sending a callback using Oracle Integration ERP adapter capabilities (stay tuned, we will discuss in the next blog)

Conclusion

Now you should have fair understanding of how you can use Oracle Integration effectively to automate Supplier Data synchronization from an external 3rd party system to Oracle Procurement Cloud. 

We will be publishing additional blogs to this as a series, that would help understanding how a child process can be orchestrated like Supplier Sites information upon successful Supplier creation.

Join the discussion

Comments ( 3 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.