Wednesday, November 24, 2021

#893 OIC November Release New Features - Oracle Hospitality Adapter


Another compelling example of the power of OHIP and Oracle Integration (OIC). 

The OIC November release includes the Oracle hospitality adapter, which makes creating integrations, such as Profile / Contact sync very easy. 

In this simple use case I will subscribe to Opera Business Events and pick up new guest profiles. The profile data will be used to create contacts in SFDC.

Let's look at the configuration of the Oracle Hospitality Trigger - 

Now to the integration - 

Here is the Trigger configuration - 

So what are these fields here?
Hotel Id - refers to a specific hotel. External System Id refers to a group of hotels, e.g a specific chain. One can subscribe to hotel specific or group business events.

Here is the sample JSON, describing the Opera business event format - 

  "businessEventData" : [ {
    "businessEvent" : {
      "header" : {
        "moduleName" : "Profile",
        "actionType" : "NEW PROFILE",
        "actionId" : "561139",
        "primaryKey" : "42212",
        "publisherId" : "18371",
        "createdDateTime" : "2019-11-24 21:30:52.0",
        "hotelId" : "HOTEL1"
      "details" : [ {
        "dataElement" : "FIRST NAME"
      }, {
        "dataElement" : "NAME",
        "oldValue" : "Safe Travel",
        "newValue" : "Safe Travel One"
      }, {
        "dataElement" : "IATA NUMBER",
        "newValue" : "355453"
      }, {
        "dataElement" : "EMAIL",
        "oldValue" : "",
        "newValue" : ""
      }, {
        "dataElement" : "WEBPAGE"
      } ]
    "businessEventId" : {
      "id" : "1971132"
  }, {
    "businessEvent" : {
      "header" : {
        "moduleName" : 14,
        "actionType" : "NEW PROFILE",
        "actionId" : "561141",
        "primaryKey" : "494139",
        "publisherId" : "27234",
        "createdDateTime" : "2019-11-24 21:43:59.0",
        "hotelId" : "HOTEL1"
      "details" : [ {
        "dataElement" : "FIRST NAME",
        "newValue" : "William"
      }, {
        "dataElement" : "EMAIL YN",
        "newValue" : "N"
      }, {
        "dataElement" : "WEBPAGE"
      } ]
    "businessEventId" : {
      "id" : "1971133"
  } ]

Here is the SWITCH configuration - 

Now to the structure of the Hospitality Business Event in a bit more detail - starting with the Header - 

moduleName will tell us which business object is related to this event e.g. "moduleName":"Profile"

actionType is the event type e.g. "actionType":"CREATE PROFILE"

primaryKey is the unique id of the object in Opera. 

now to the recurring details - 

e.g.  {"dataElement": "FIRST NAME", "oldValue": "Niall", "newValue": "Niall" }, {"dataElement": "NAME", "oldValue": "Commiskey", "newValue": "MacCumascaigh" },

you get the idea!

Back in the integration - I assign the primaryKey to a variable -

Then I use the Hospitality connection again, this time to retrieve the profile - 


Now to the SFDC CreateContact - 

Final step - do the mappings - and then we're ready to test it out!

I use Postman to create a new Profile in Opera - 

I check out the flow in OIC - 

Here is the Activity Stream with the result from SFDC - 

I validate in SFDC -

I now map a couple of more fields to SFDC. Essentially, I want the mailing address and email of the guest in my CRM. I change the getProfile invoke to add 2 fetchInstructions for Address and Communication.

I then re-test by creating the following profile via Postman - 

I validate in SFDC - 

Friday, November 19, 2021

#892 OIC November 21 Release New Features

Check out the What's New doc here

My highlights - New Adapters -

  • Oracle Hospitality Adapter
  • eBay Marketplace Adapter
  • SugarCRM adapter
Adapter Enhancements - 
  • Jira adapter - Authorization Code Credentials security policy support
  • SFDC adapter - CDC events support
  • Zendesk adapter - new operations supported
  • SAP S4 Hana adapter- simplified connection definition
Connectivity Agent JDK 11 certified.

File Server - max concurrent connections upped to 20.

You can expect more in-depth posts from me on the above.
Have a great weekend!

Thursday, November 18, 2021

#891 OIC -> DB adapter - overcoming 10 MB limit

This is an issue with many customers - I execute a SELECT against my on-premise Oracle DB from OIC and a humungous amount of data is returned. This exceeds the 10 MB limit and causes an error.

Here is a simple example of avoiding such.

Here is my test table - 


I can use SQL with OFFSET - 

SELECT orderNr, customer, value FROM orders ORDER BY ordernr OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY; 

I wrap this SQL in a stored procedure - 

Now to my integration, which will invoke the Stored Procedure. This is a scheduled orchestration -

First step is to do a SELECT COUNT(1) FROM ORDERS - this returns 16 in my case.

Now for my simple example I will process these in batches of 5. You can extrapolate from this e.g. 200 rows is well within my 10MB limit.

I then divide my count by the batch size - using the ceiling function.

ceiling(16 / 5) = 4 iterations.



Next step is to leverage the DB adapter to invoke the PL/SQL stored procedure - 

The DB invoke Mapping is as follows - 

The order data is returned in the following format, because we are using a refCursor -

So I need to remember the sequence here - column 1 is ORDERNR, column 2 is CUSTOMER and column 3 is VALUE.

As you can see above, the only processing I have is logging the order details - here is the configuration of the LOG Action - 

concat("Order Details - OrderNr / Customer / Value : ",$currentOrderRow/nsmpr3:Row/nsmpr3:Column[1], " / ",$currentOrderRow/nsmpr3:Row/nsmpr3:Column[2], " / ",$currentOrderRow/nsmpr3:Row/nsmpr3:Column[3]) 

 Note also, the last action is to update the variable I am using for the offset value.

I run the integration and check out the activity stream - 

4 iterations are executed - Iteration 1 has the following orders - 

Iteration 2 has the following orders - 

Iteration 3 orders - 

Iteration 4 orders - 

Wednesday, November 3, 2021

#890 OIC and OCI AI Language Service Part II

 Let's look at some of the other apis offered - I start off with language detection - 

Above we see the structure returned by the api - in my example - 2 languages with over 90% probability that the text is in English. The invoke of the api is configured as follows - 

I add logic to parse all language entries returned - 

I then include the language in the response - 

Let's now look at text classification - 

Here is the configuration of the invoke - 

I activate and test with the text = This is my second order for this product, it is a really cool bike