Sales Cloud - batch Insert using REST Custom Actions

September 30, 2020 | 5 minute read
Tim Bennett
CX Solution Architect
Text Size 100%:

Introduction

The REST API for CX Sales and B2B Service supports Batch Actions - multiple operations can be combined into a single HTTP request, with the request body being a JSON object containing an array of objects to be processed. Each object in the array contains the payload for the individual action being taken:

  • A unique ID
  • A relative path to the resource
  • An operation
  • A payload

See the documentation here for further details

A Batch Action is treated as a single transaction, so if 1 record within the batch fails, the whole batch fails. Also, the response contains the full records that have been created (unless header Accept: application/vnd.api+json;q=0 is used in which case there will be a 204 No Content response).

The above works fine in most situations, but there are cases where it is not sufficient. For example:

  • Individual records are allowed to fail, with the indexes of the failed rows being returned
  • The Ids of successful or failed parts are required, not the full payload

Any use case that requires row level commit, or a "shaped" response requires an alternative approach.

This article describes how a Custom Action can be used to implement both row level transactions and basic response shaping.

 

Solution Overview

Custom Actions are implemented in Application Composer as Object functions with Visibility set to "Callable by External Systems" and are called using REST POST messages. A Custom Action accepts an array of String parameters and has a single String response.

The high level steps to configure and run a custom batch action are:

  1. Create a Custom Object called GlobalFunctions with no custom fields and no pages
  2. Add 1 record to the new object (use REST), noting its Id
  3. Create an Object Function called batchCreate with external visibility
  4. Execute the function by sending a POST request to the custom object REST endpoint using the appropriate payload

In this example, the object function will accept the name of an object and a delimited file containing new records for the object, it will insert the records into the object, and it will return the keys of the records inserted and the row numbers of those that fail. The approach can be adapted to suit different scenarios, using different input parameters and data formats as required.

The example object function has 3 parameters:

objName          The API name of the target object

key                   The name of the object field that will be returned in the response 

data                  The rows to be inserted supplied as a single string with delimiters (see later)

 

Detailed Steps

1. Create a Custom Object called GlobalFunctions with no fields and no pages

While it is possible to add the Object Function to an existing object, it is recommended that a new object is created so that the code is separated from core business logic, especially if the code is generic and is not written for any specific object.

 

2. Add 1 record to the new custom object and note its Id

Object Functions execute within the context of a row, not simply the object they belong to, so when calling the REST endpoint, the record Id must be known. If using VB Business Objects (this article works for VB as long as Business Rules are enabled) then the Id of the only row that is added is likely to be 1, in Fusion it is going to be a 15 digit id.

 

3. Create an Object Function called batchCreate with external visibility

In Application Composer, select the new object, create a new Object function called batchCreate, check the Callable by External Systems flag, and add 3 String parameters as shown below:

Next, add the code below:

def msg = ""
def r = null
//split the data parameter into rows
def rows = data.split("!-!")

//declare the object that the data will be imported into
def bo = newView(objName)

//the first row contains attribute names, save them to a list
def boAttributes = rows[0].split("\\|").toList()

//get rid of the header row, remaining rows are data
def boData = rows.drop(1) 

//iterate over the input rows, inserting new records
boData.eachWithIndex{it,index->  
  try {
         r = bo.createRow()
         //setAttributeValues takes 2 lists - the attribute names, and the attribute values
         r.setAttributeValues(boAttributes, it.split("\\|").toList())  
         r.validate()
         bo.insertRow(r)
         //append the key of the new record to the response
         msg += r.getAttribute(key) + ","
      } catch (Exception e) {
         //append the row number and error to the response
         msg += "Error row ${index} : ${e.getMessage()} ,"
         if (r?.getPrimaryRowState()?.isNew()) {
             r?.remove()
          } else {
             r?.revert()
          }
      }
}
//The response can contain more or less anything as long as it is a String 
return (msg)

Notes:

The code above will generate lots of validation warnings because the types of many of the variables are unknown.

At the time of writing (Sept 2020) the error handling is complicated by a bug that results in invalid rows not being rolled back correctly and the real error being "lost", causing the function to exit the function completely, hence the catch block looks at the status of a failed row and removes it if necessary. Unfortunately the real reason for a failed row is not returned to the catch block - this may make this technique unusable in some circumstances.  

 

4. Running the code using REST

This example uses a delimited file for the data to be imported. The first row contains the API names of the columns, and to avoid issues with embedded quotes and new lines etc, it uses the following delimiters:

Column separator      |

Row separator          !-!

This can be changed to suit different requirements. One of the benefits of this format is that it is compact compared with the out of box batch JSON payload.

The example REST call will insert 5 simple Service Requests with 3 fields: Title, Problem Code, and Severity. One record has an invalid severity code.

Example data parameter:

Title|ProblemDescription|SeverityCd!-!New SR 1|Description 1|ORA_SVC_SEV3!-!New SR 2|Description 2|ORA_SVC_SEV3!-!New SR 3|Description 3|ORA_SVC_SEV3!-!New SR 4|Description 4|ORA_SVC_SEV3!-!New SR 5|Description 5|ORA_SVC_SEVxx!-!

Note the use of API names in the first "row" and the invalid severity in the last row.

 

The cURL command to run this is:

curl --location --request POST 'https://xxxx.yy.xx.oraclecloud.com/crmRestApi/resources/11.13.18.05/GlobalFunctions_c/300000005521757' \
--header 'Content-Type: application/vnd.oracle.adf.action+json' \
--header 'Authorization: ' \
--data-raw '{
    "name": "batchCreate",
    "parameters": [
        {"objName": "ServiceRequestVO"},
        {"key": "SrId"},
        {"data": "Title|ProblemDescription|SeverityCd!-!New SR 1|Description 1|ORA_SVC_SEV3!-!New SR 2|Description 2|ORA_SVC_SEV2!-!New SR 3|Description 3|ORA_SVC_SEV3!-!New SR 4|Description 4|ORA_SVC_SEV3!-!New SR 5|Description 5|ORA_SVC_SEVxx!-!"}
    ]}'

 

Note, the POST is to a specific row within the GlobalFunctions object, the Content-Type is application/vnd.oracle.adf.action+json, and the body contains the name of the Object Function and the parameters array.

The response to the above call is:

{
"result": "300000005671502,300000005671503,300000005671504,300000005671505,Error row 5 : JBO-27024: Failed to validate a row with key oracle.jbo.Key[300000005671506 ] in ServiceRequestEO ,"
}

 

The first 4 result entries contain the SrIds of the 4 SRs that have been created, are the error states that row 5 cannot be validated.

 

Conclusion

Custom Actions are useful in cases where non-standard processing of the payload or response is required. As shown above, both the input payload and response are flexible, and the input data can be processed to suit the requirements.

The above used a simple example, but clearly the payload could include multiple object types in a single payload and the Groovy can interact with multiple objects within the same function.

 

Tim Bennett

CX Solution Architect

Solution Architect specialising in Oracle Sales Cloud configuration and integration, particularly security and scripting.


Previous Post

Connecting a Cloud-Native Kubernetes App on OKE to Autonomous Transaction Processing Dedicated in a Different VCN

Stefan Koser | 6 min read

Next Post


Connecting Oracle Data Integrator Studio to Autonomous Database on Dedicated Infrastructure

Dayne Carley | 5 min read