Sales Cloud Incentive Compensation application provides API to import sales transactions in bulk. These could be sales transactions exported out of an ERP system. Integration Cloud Service (ICS) offers extensive data transformation and secure file transfer capabilities that could be used to orchestrate, administer and monitor file transfer jobs. In this post, let’s look at an ICS implementation to transform and load sales transactions into Incentive Compensation. Instructions provided in this post are applicable to Sales Cloud Incentive Compensation R11 and ICS R16.4.1 or higher.
Figure 1 provides an overview of the solution described in this post. A text file contains sales transactions, in CSV format, exported out of ERP Cloud. ICS imports the file from a file server using SFTP, transforms the data to a format suitable for Incentive Compensation and submits an import job to Sales Cloud. The data transfer is over encrypted connections end-to-end. ICS is Oracle’s enterprise-grade iPaaS offering, with adapters for Oracle SaaS and other SaaS applications and native adapters that allow connectivity to most cloud and on-premise applications. To learn more about ICS, refer to documentation at this link.
Figure 1 – Overview of the solution
Implementation of the solution requires the following high-level tasks.
For the solution to work, ICS should be able to connect with Sales Cloud and File Server. ICS agents can easily enable connectivity, if one of these systems are located on-premise, behind a firewall.
A File Server is an optional component of the solution. If the source ERP system that produces CSV file allows Secure FTP access, ICS could connect to it directly. Otherwise, a file server could host exported files from ERP system. One way to quickly achieve this is to provision a compute note on Oracle Public Cloud and enable SFTP access to a staging folder with read/write access to ERP system and ICS.
File-based data import service requires that each import job specify a data mapping. This data mapping helps the import service assign the fields in input file content to fields in Incentive Compensation Transaction object. There are two ways to define such mapping.
Here are the steps to complete import mapping:
The new mapping is now ready for use.
Importing sales transaction require a file import web service and another optional web service to collect transactions.
File-based data import and export service could be used to import and data out of all applications on Sales Cloud. For the solution we'll use "submitImportActivity" operation. WSDL is typically accessible at this URL for Sales Cloud R11.
https://<Sales Cloud CRM host name>:<CRM port>/mktImport/ImportPublicService
The next task could be performed by logging into Incentive Compensation application or by invoking a web service. 'Manage Process Submission' service is specific to Incentive Compensation application. The file-based import processes input and loads the records into staging tables. 'submitCollectionJob' operation of 'Manage Process Submission' service initiates the processing of the staged records into Incentive Compensation. This service is typically accessible at this URL. Note that this action can also be performed in Incentive Compensation UI, as described in the final testing section of this post.
https://<IC host name>:<IC port number>/publicIncentiveCompensationManageProcessService/ManageProcessSubmissionService
An ICS orchestration glues the other components together in a flow. ICS orchestrations provide flexible ways to invoke, such as a scheduled triggers or an API interface. Orchestrations can perform variety of tasks and implement complex integration logic. For the solution described in this post, ICS needs to perform the following tasks:
For sake of brevity, only the important parts of the orchestration are addressed in detailhere. Refer to ICS documentation for more information on building orchestrations.
FTP adapters could be used with 'Basic Map my data' or Orchestration patterns. To create a new FTP connection, navigate to "Connections" tab, click on "New Connection" and choose FTP as type of connection.
Under "Configure Connection" page, set "SFTP" drop down to "Yes". FTP adapter allows login through SSL certificate or username and password.
In "Configure Security" page, provide credentials, such as username password or password for a SSL certificate. FTP adapter also supports PGP encryption of content.
Source data from ERP could be in a different format than the format required by target system. ICS provides a sophisticated mapping editor to map fields of source record to target record. Mapping could be as easy as drag & drop of fields from source to target, or could be set using complex logic using XML style sheet language (XSLT). Here is a snapshot of the mapping used for transformation, primarily to convert date string from one format to another.
Mapping for SOURCE_EVENT_DATE requires a transformation, which is done using transformation editor, as shown.
File-based data import service requires the content of a CSV file to be Base64-encoded. This encoding could be done using simple XML schema to be used in the FTP invoke task of the orchestration. Here is the content of the schema.
<schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema"> <element name="opaqueElement" type="base64Binary"/> </schema>
Choose options to select an existing schema.
Pick the schema file containing the schema.
The FTP invoke is ready to get a file via SFTP and return the contents to the orchestration as a Base64-encoded string. Map the content as to a field in SOAP message to be sent to Incentive Compensation.
To test the solution place a CSV formatted file at the stageing folder on file server. Here is sample content from source file.
SOURCE_TRX_NUMBER,SOURCE_EVENT_DATE,CREDIT_DATE,ROLLUP_DATE,TRANSACTION_AMT_SOURCE_CURR,SOURCE_CURRENCY_CODE,TRANSACTION_TYPE,PROCESS_CODE,BUSINESS_UNIT_NAME,SOURCE_BUSINESS_UNIT_NAME,POSTAL_CODE,ATTRIBUTE21_PRODUCT_SOLD,QUANTITY,DISCOUNT_PERCENTAGE,MARGIN_PERCENTAGE,SALES_CHANNEL,COUNTRY TRX-SC1-000001,1/15/2016,1/15/2016,1/15/2016,1625.06,USD,INVOICE,CCREC,US1 Business Unit,US1 Business Unit,90071,SKU1,8,42,14,DIRECT,US TRX-SC1-000002,1/15/2016,1/15/2016,1/15/2016,1451.35,USD,INVOICE,CCREC,US1 Business Unit,US1 Business Unit,90071,SKU2,15,24,13,DIRECT,US TRX-SC1-000003,1/15/2016,1/15/2016,1/15/2016,3033.83,USD,INVOICE,CCREC,US1 Business Unit,US1 Business Unit,90071,SKU3,13,48,2,DIRECT,US
After ICS fetches this file and transforms content, it invokes file-based data import service, with the payload shown below.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/oracle/apps/marketing/commonMarketing/mktImport/model/types/" xmlns:mod="http://xmlns.oracle.com/oracle/apps/marketing/commonMarketing/mktImport/model/"> <soapenv:Header/> <soapenv:Body> <typ:submitImportActivity> <typ:importJobSubmitParam> <mod:JobDescription>Gartner demo import</mod:JobDescription> <mod:HeaderRowIncluded>Y</mod:HeaderRowIncluded> <mod:FileEcodingMode>UTF-8</mod:FileEcodingMode> <mod:MappingNumber>300000130635953</mod:MappingNumber> <mod:ImportMode>CREATE_RECORD</mod:ImportMode> <mod:FileContent>U09VUkNFX1.....JUkVDVCxVUw==</mod:FileContent> <mod:FileFormat>COMMA_DELIMITER</mod:FileFormat> </typ:importJobSubmitParam> </typ:submitImportActivity> </soapenv:Body> </soapenv:Envelope>
At this point, import job has been submitted to Sales Cloud. Status of file import job could be tracked on Sales Cloud, under 'Set and Maintenance'. by opening "Manage File Import Activities". As shown below, there are several Incentive Compensation file imports have been submitted, in status 'Base table upload in progress'.
Here is a more detailed view of one job, opened by clicking on status column of the job. This job has imported records into a staging table.
To complete the job and see transactions in Incentive Compensation, follow one of the these two methods.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/incentiveCompensation/cn/processes/manageProcess/manageProcessSubmissionService/types/"> <soapenv:Header/> <soapenv:Body> <typ:submitCollectionJob> <typ:scenarioName>CN_IMPORT_TRANSACTIONS</typ:scenarioName> <typ:scenarioVersion>001</typ:scenarioVersion> <typ:sourceOrgName>US1 Business Unit</typ:sourceOrgName> <typ:startDate>2016-01-01</typ:startDate> <typ:endDate>2016-01-31</typ:endDate> <typ:transactionType>Invoice</typ:transactionType> </typ:submitCollectionJob> </soapenv:Body> </soapenv:Envelope>
Finally, verify that transactions are visible under Incentive Compensation, by navigating to "Incentive Compensation" -> "Credits and Earnings", from home page and by clicking on "Manage Transactions".
This post explained a solution to import transactions into Incentive Compensation using web services provided by Sales Cloud and Incentive Compensation application. It also explained several features of Integration Cloud Service utilized to orchestrate the import. The solution discussed in this post is suitable for Sales Cloud R11 and ICS R16.4.1. Subsequent releases of these products might offer equivalent or better capabilities out-of-box. Refer to product documentation for later versions before implementing a solution based on this post.