In part 1, I gave you an overview of a solution to extract supplier data from a Sourcing event in SAP Ariba Sourcing, and save it in a mailing list in SAP Qualtrics XM for Suppliers.
In part 2, I detailed 2 ways in which we can create SAP HANA Cloud database tables to store the information extracted from SAP Ariba Sourcing.
In this post, I will detail how to consume both classic and CDS HANA Cloud databases from SAP Cloud Integration.
Process
First off, we need to fetch the RFx information from SAP Ariba Sourcing.Then we will store it in the SAP HANA Cloud database. In a separate iFlow, we will read the information from the SAP HANA Cloud database and then we will post the information to a mailing list in SAP Qualtrics XM for Suppliers.
Fetch RFx information from SAP Ariba Sourcing
We use the SAP Ariba Operational Repoting API (Synchronous). This API uses OAuth Client Credentials authentication flow, so first we need to get an access token, and then pass this token to the API call as a Header parameter.
All of this is handled automatically by the HTTP adapter:
You just need to define the credential information in a Security Material:
Also, make sure to add “apiKey” as an allowed header parameter, otherwise you might get error 403 when invoking the API.
Send RFx to SAP HANA Cloud (Classic)
You can use JDBC to access a HANA Cloud database created with the classic schema, since there is no service related to the artifacts.
The JDBC accepts XML SQL statements, so you have to generate such XML inside the iFlow. I used Groovy script to do this.
For example, this is an XML SQL to INSERT a record in table RFX:
<InsertStatement>
<rfx @action='INSERT'>
<table>RFX<table/>
<access>
<ID>rfx.InternalId</ID>
<TITLE>rfx.Title</TITLE>
<CREATED_AT>rfx.TimeCreated</CREATED_AT>
<UPDATED_AT>rfx.TimeUpdated</UPDATED_AT>
<EVENT_TYPE>rfx.EventType</EVENT_TYPE>
<EVENT_STATE>rfx.EventState</EVENT_STATE>
<STATUS>rfx.Status</STATUS>
</access>
</rfx>
</InsertStatement>And this is part of the script to generate it:
import com.sap.gateway.ip.core.customdev.util.Message
import java.util.HashMap
import groovy.json.JsonSlurper
import groovy.xml.*
def Message processData(Message message) {
def body = message.getBody()
def jsonSlurper = new JsonSlurper()
def apiResult = jsonSlurper.parse(body)
// Stores API response for later use
message.setProperty('ApiResponse', apiResult.toString())
// Create SQL statement
def writer = new StringWriter()
def sqlStatement = new MarkupBuilder(writer)
sqlStatement.root{
for (rfx in apiResult.Records) {
String eventType
switch(rfx.EventType) {
case '0':
eventType = 'FullProject'
break
case '1':
eventType = 'RFI'
break
case '2':
eventType = 'RFP'
break
case '3':
eventType = 'RFQ'
break
case '4':
eventType = 'Auction'
break
case '5':
eventType = 'Survey'
break
case '6':
eventType = 'ForwardAuction'
break
case '7':
eventType = 'SourcingRequest'
break
}
sqlStatement.InsertStatement{
sqlStatement.rfx(action: 'INSERT') {
sqlStatement.table('RFX')
sqlStatement.access{
sqlStatement.ID(rfx.InternalId)
sqlStatement.TITLE(rfx.Title)
sqlStatement.CREATED_AT(rfx.TimeCreated)
sqlStatement.UPDATED_AT(rfx.TimeUpdated)
sqlStatement.EVENT_TYPE(eventType)
sqlStatement.EVENT_STATE(rfx.EventState)
sqlStatement.STATUS(rfx.Status)
}
}
}
}
}
message.setBody(writer.toString())
return message
}In this script I create the XML SQL statements to INSERT records in both RFX and RFX_INVITED_USERS tables.
Then, we post all records to the SAP HANA Cloud database using JDBC:
Be sure to check the “Batch mode” option, so you can post many records at once.
You can also maintain the JDBC connection details in a JDBC material:
Finally, I use a general splitter to call the Events Management API, to get the details of the invited suppliers. This information I will be stored in the RFX_ORGANIZATIONS table.
Send RFx to SAP HANA Cloud (CDS)
To read and write information in a SAP HANA Cloud table created with CDS, we use OData services. This way is simpler and more universally accessible.
In this scenario, we will use the HTTP adapter in the iFlow to do a POST of the information in JSON format.
This is part of the script to generate the payload:
Fetch RFx information from SAP HANA Cloud (Classic)
To read a table in the classic schema, we will also use JDBC, only in this case we won’t need XML SQL, we can just send the SQL statement directly in the body; for example:
SELECT * FROM "ARIBAXM_HDI_DB_1"."RFX_INVITED_USERS"We can define this statement in a Content Modifier.
Fetch RFx information from SAP HANA Cloud (CDS)
In this case, we will use again the OData service but now with a GET method. This will return a JSON with all the information in the table. In this case we don’t need any particular script because we want to get all records of the table
Post Contact to SAP Qualtrics XM via Mailing List API
Finally, now that we have the information from the SAP HANA Cloud, we need to post it to SAP Qualtrics XM. For this, we use the Create Directory Contact API, which expects a JSON. You can check the documentation here.
This is part of the script to generate the payload as the API needs it:
import com.sap.gateway.ip.core.customdev.util.Message
import java.util.HashMap
import groovy.json.JsonBuilder
import groovy.xml.*
def Message processData(Message message) {
def rfxOrganizationsResult = new XmlSlurper().parseText(message.getBody(java.lang.String))
def rfxUsersResult = new XmlSlurper().parseText(message.getProperty('rfx_invited_users'))
// Create payload to Qualtrics
def writer = new StringWriter()
def qualtrics = new MarkupBuilder(writer)
qualtrics.root {
for (user in rfxUsersResult.select_response.row) {
// Only post awarded users
if (user.AWARDED == 1) {
qualtrics.contact {
id(user.ID)
unique_name(user.UNIQUE_NAME)
payload(getPayload(user, rfxOrganizationsResult.select_response.row))
}
}
}
}
message.setBody(writer.toString())
return message
}
// Set information of the User's Organization
def getPayload(user, organizations) {
def org = organizations.find { it.CONTACT_ID == user.UNIQUE_NAME }
def payload = new JsonBuilder()
String phone = 'N/A'
if (user.PHONE && !user.PHONE.isEmpty() && user.PHONE != '') phone = user.PHONE
payload ([
firstName: "$user.FIRST_NAME",
lastName: "$user.LAST_NAME",
email: "$user.EMAIL",
phone: "$phone",
extRef: "$user.ID",
language: "en",
unsubscribed: false,
embeddedData: [
OrganizationID: "$org.ORG_ID",
Name: "$org.NAME",
Address: "$org.ADDRESS, $org.CITY, $org.STATE. $org.POSTAL_CODE. $org.COUNTRY"
]
])
return payload.toString()
}This payload contains the details of all RFx’s users stored in the SAP HANA Cloud, so we need to use a general splitter to send one RFx event at a time to SAP Qualtrics XM. Then, we use the HTTP adapter to call the API
Final Thoughts
Now you know how to create integrations with both versions of the SAP HANA Cloud artifacts: Classic via JDBC, and CDS via OData services.
This is the final part of this series. I hope you find it useful!
Follow my profile to be notified of the next blog posts, and please, let me know your thoughts or questions you have in the comments section below.
