Oracle Integration Cloud Service (ICS) – How to use Upsert Operation

Oracle launches ICS around April, 2015 and is being accepted by customer well. As customers started using ICS for  Salesforce Integration,  one question was observed and asked by lots of customers, is related to Upsert operation usage. We will dig deep into this in this blog.

Salesforce to ERP Integration common scenarios are –

  1. Account and Contact Sync
  2. Product and Price Sync
  3. Opportunity or Quote to Order (Sales Order)
  4. Order Status Update back to Salesforce.com
  5. Invoice Data Transfer to Salesforce.com

Other Integration flows include, History, Attachments,  Warranty, Cases, etc…

In most of the scenarios where data needs to be inserted or updated into Salesforce.com, recommended operation is Upsert to be used. When Upsert Operation is leveraged, Salesforce SOAP API automatically check based on external Id defined whether incoming record exists or not; if it does exist then record gets updated or else inserted. Click here for more information on upsert operation.

But when you start using Upsert operation with any standard or custom objects from Salesforce.com, by default, external id is not defined in Salesforce.com. So if you have decided to use upsert operation in an integration flow, first thing to do is to define / create external id in the object(s) you are planning to use with upsert operation.

Creation of External ID:

External Id is important parameter for upsert operation. To avoid data duplication external id should be created with utmost care. Best practice suggests to use foreign key (unique key) as an external key. if foreign key is composite key, external key should also be composite. It allows you to create or update a record and relate it to another existing record in a single step instead of querying the parent record ID first.

Let’s take an example here. consider I am working on Account Sync and I am bringing customers master data from ERP to Salesforce.com. So to create external id in Account object, login to Salesforce.com and then navigate to setup -> App Setup -> Customize -> Account -> Fields. To create a new fields, click on new button on this page. Select the data type (for example Text) and click to Next. Provide name for this external id field (appending ext_id in the name is considered best practice) and select external id check box on this page as shown in the below diagram.

external_Id

Save it. external id is created and ready to use.

Build the ICS Flow

Create the Salesforce connection and the other connection required to connect to other system like ERP. Now on Integration canvas of ICS drag and drop the Salesforce adapter on target side and complete the wizard after selecting Account object and Upsert operation.  Drag and drop other adapter on the source side and complete the wizard as per scenario. (In this case it should capture the customer events from ERP). Now click on the mapping and complete the mapping. one important caution that should be taken is to map the primary key of ERP to external id of Salesforce.com Account object. I recommend to map primary id of other system (ERP) because it satisfy the requirements (unique for every different records and same for same record) of external id. but it is not mandated by ICS or Salesforce adapter. You may map external id with any field (or concatenation of multiple fields) by which you can meet the requirements of uniqueness. Once mapping is done, save and activate the flow and run it. it should work as expected.

Advertisements

5 thoughts on “Oracle Integration Cloud Service (ICS) – How to use Upsert Operation

  1. Hi SHALINDRA,

    Could you please tell what are filed need to mapp for upsert operation. Apart adding external ID in salesforce custom field will it will display is mapping??.

    Regards,
    Shiva.

    1. Hi Siva,

      Yes, Once you create custom field in Salesforce.com as external id and import updated Enterprise WSDL on the connection page, you should be able to see new field in all new flows mapper. to see newly created external id field in the existing flows, you need to use regeneration option.
      You should map external field name and then value to the external id element. other than these you need to map as per the requirement or at least mandatory fields.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s