Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 


What if you have a lot of users working in different departments and you need to notify them with custom messages? Knowing your departments and users are saved in a database and you still have to build your message and send it to them.

Then you search and use notification/message services, or you create a solution your own!

That's exact what we are going to do in this blog, we will build our own End-to-End Notification Flow.

This by developing a UI5 App to write our message content and send it to all users from a specific department. These departments and users are saved in a HANA MDC Database.

The notification content and selected department in the UI5 App will be send to a CPI-Flow endpoint. This flow will get all the users from the HANA DB and send a personal mail to all of them.

In this blog, I chose to send every employee an individual email. This way I can demonstrate the use of the Iteration Splitter. You might consider to send one email and place all the employees of a department into the 'to' of your email. But that's not what I will demonstrate today.


Architecture & Steps

Here is a high-level architecture of the whole End-to-End scenario:

These steps will be taken throughout the whole flow:

  1. In the UI5 App we can select a department and we write the text for our notification/mail.

  2. The CPI-flow will be triggered by the UI5 app.

  3. The CPI-flow will fetch all the employees of the department (passed by the UI5 app) from the HANA DB.

  4. The CPI-flow will send an email via the mail adapter to all the users.



Building the Database, Flow & App

Let us go over all the required technical steps, starting with the database which will be consumed by our UI5 app and CPI-flow. Next we will model our CPI-Flow and finish with the UI5 App.

Let's start!

1.  Create, Populate & Expose (OData) the HANA DB

In the SAP CP you create a HANA MDC Database and assign the required roles to the user you want to use. 

To create a new database user and assign the correct roles and privileges you can refer to this detailed Wiki documentation by SAP Mentors.

Create Developer User and assign Roles and Privileges

An overview of all the files that will be created in our package called 'CpiNotificationApp':


1.1 The .xsaccess file

The application-access (.xsaccess) file allows you to specify whether to expose package content or not, which authentication method is used to grant access, and what content is visible.

In here we place the following JSON-Strucutre:
"exposed": true,
"prevent_xsrf": true

The exposed key is set to true so we can expose our content via OData.

The prevent_xsrf key is set to true to protect applications from cross-site request-forgery (XSRF) attacks.

For more information about this .xsaccess file you can refer to the following SAP Documentation:


1.2 The .xsapp file

The application descriptor is the core file that you use to describe an application's framework within SAP HANA XS.

In this case we can leave it with an empty JSON-Object:

For more information about this .xsapp file you can refer to the following SAP Documentation:


1.3 The .User.hdbdd file
namespace CpiNotificationApp;

context User {
@Catalog.tableType: #COLUMN

entity User {
key id: Integer not null;
name: String(25);
groupId: Integer not null;
email: String(50);

entity UserGroup {
key id: Integer not null;
name: String(25);
users: Association[*] to User on users.groupId = id;

In this database file we perform the following steps:

  1. Define the namespace.

  2. Add a Schema.

  3. Create your context.

  4. Define the tabletype.

  5. Create the User entity, with its id (pk), name, groupid (fk) and email.

  6. Create the UserGroup entity, with its id (pk), name and users association (1 usergroup can have multiple users. association based on the pk (usergroup) and fk (user)).


1.4 The .CpiNotification.xsodata file

The OData service definition is a configuration file you use to specify which data (for example, views or tables) is exposed as an OData collection for analysis and display by client applications.

For more information about this .xsodata file you can refer to the following SAP Documentation:
service { 
"CPI_NOTIFICATION_APP"."CpiNotificationApp::User.UserGroup" as "UserGroup" navigates ("UserGroup_users" as "UserGroup_users");
"CPI_NOTIFICATION_APP"."CpiNotificationApp::User.User" as "User";

association "UserGroup_users" principal "UserGroup"("id") multiplicity "1" dependent "User"("groupId") multiplicity "*";

In this service definition we define the following logic:

  1. Expose the usergroups as 'UserGroup' with a navigation property to all its corresponding users, called 'UserGroup_users'.

  2. Expose the users as 'User'.

  3. Create the association between the tables in the OData on the navigation property. This on the usergroup its 'id' and the user its 'groupid'. The primary key of usergroup table and the foreign-key of user table. One usergroup can have multiple users.


1.5 Add data to the usergroup and user tables

First add the desired departments:

Next add the users in the user table and place in the userGroup field the id for which department you want them to work.

These are all fictive chosen names. If they would match real existing names it would be a coincidence.


Nice, we finished all the steps that need to be taken in the HANA Editor and Catalog.


1.6 Test the OData Service

You can test your OData service in the browser by pressing the green run button:

Next a new tab with your service will be opened. Here the usergroup and user collection are displayed.

The URLs to call your data via the OData Service:

Root url that will show all the Collections:

Followed by one of the following urls:

OData URL OData Content
/UserGroup Show all Usergroups
/User Show all Users
/UserGroup(1000) Show specific usergroup with id 1000
/UserGroup(1000)/UserGroup_users Show all users from usergroup with id 1000
/UserGroup(1000)?$expand=UserGroup_users Show users of department 1000 and department data



2.  Model, Design & Deploy the CPI-Flow

The next step is to create a CPI-Flow that will be triggered by the UI5 App.

In this blog we will use a Gmail-account to send our mails to the users.


Therefor we will perform the following steps:


2.1 Add the username and password from you sending email address to the Security Material

In the Operations view select the Security Material tile under the Manage Security section:

Select 'Add' and choose User Credentials:

Fill in the required fields and password of course. Next press the 'deploy' button.

Confirm your deployed credentials are deployed. First it will display in the status that it is Stored. After refreshing the page it will display Deployed.


2.2 Retrieve the Gmail Certificates through the CPI Connectivity Test


To use the Mail Adapter later in our CPI-Flow, we need to add the certificates to the key-store in our CPI environment.

This certificates can be retrieved by using the CPI Connectivity Test. Which is another tile under the Operations view.

In the Connectivity test, choose SMTP, and add the following values:

(when successful, download the certificates)


2.3 Add the Certificates to the CPI Key-store

Now we can add these certificates to the key-store. To do so, go to the operations overview and select the Key-store tile.

Now unzip the certificates from the zip-file.

Next choose Add > Certificate.

Provide an Alias for the certificate, select the certificate and deploy it.

As last check if your certificate is deployed successfully.


2.4 Build the CPI-Flow

Create you Package and add an Artifact. In this case we choose an Integration-Flow.

We will build the following Integration-Flow:


Let's go over all the steps in this flow:


2.4.1 HTTP Session Reuse

To set the  HTTP Session Reuse, select outside the flow (with background) and go to the 'Runtime Configuration' tab.

Select the value: 'On Integration Flow' for the HTTP Session Reuse.

This will make sure that the session will be reused for all message exchanges for the Integration Flow. Only used for stateless services.

Stateless means there is no record of previous interactions and each interaction request has to be handled based entirely on information that comes with it.


2.4.2 Configure the Inbound HTTPS Connection

The inbound HTTPS Connection can be configured by drawing a connection line from the Sender to the start message. When selecting this connection select the Connection tab.

In here we perform the following 4 configurations:

Field Purpose
Address The endpoint which you will post your request to from the UI5 App. Prefixed by a /. We choose '/sendMail'.
Authorization The way you want to authorize, we select the User Role option.
User Role The role you need to call the endpoint. 'ESBMessaging'
CSRF Protected Set to true/enabled to protect against Cross-Site Request Forgery (CSRF) attacks .

When the start message control is hit we continue with a router. Here we decide based on conditions, if we want to continue the flow or drop and navigate to a dead end in the flow.

Because CSRF Protected is enabled we need to pass a X-CSRF-Token when we post something to the endpoint that triggers our flow. If we do not add the X-CSRF-Token with our POST call, we will receive a 403-Forbidden response code.

So the condition in this case is very easy. We drop everything thats is NOT a POST Request. This way we ensure that we only continue in our flow when it is a POST request. Since we enabled the CSRF Protect, it also checks if a token was passed when posting to the endpoint. So good to go!

The condition in the router:

So when not POST, we drop. This achieved by the following condition:

${header.CamelHttpMethod} != 'POST'

At this point we finished the X-CSRF Security steps.

For more information on how to protect your Integration Flow against Cross-Site Request Forgery (CSRF) attacks, you can refer to this amazing blog by vadim.klimov:

Inbound HTTPS with CSRF Protection in CPI Integration Flows


2.4.5 Log the Payload and Set Flow Properties

When we met our condition en are allowed to continue our flow, we use a Groovy-Script to log our payload and set some properties which we will use later on in our flow.

The script:
import java.util.HashMap;
import groovy.json.*;
def Message processData(Message message) {

def body = message.getBody(java.lang.String) as String;
// Log the incoming payload message
def messageLog = messageLogFactory.getMessageLog(message);
if(messageLog != null){
messageLog.setStringProperty("Logging#1", "Printing Payload As Attachment")
messageLog.addAttachmentAsString("ResponsePayload:", body, "text/plain");

// Create json slurper and pare the payload into object variable
def jsonSlurper = new JsonSlurper();
def object = jsonSlurper.parseText(body);

// Set variables equal to object property
def to =;
def subject = object.subject;
def mailBody = object.body;

// Set the variable into a flow property
message.setProperty("to", to);
message.setProperty("subject", subject);
message.setProperty("body", mailBody);

return message;


Groovy Playground


2.4.6 Add your SAP HANA Username and Password to the Security Material in the CPI-Tenant

Like we did in step 2.1, we now add our SAP HANA username and password to the Security Material. This so we can later on call our OData Service.


2.4.7 Log the Payload and Set Flow Properties

After we logged our Payload and have set our properties for our flow, we will call all our users for the respective department. That was passed in our POST Call to the CPI-endpoint.

When you drew and select the HTTP-connection to the receiver you provide the following configuration:

Field Value
Query $expand=UserGroup_users&$format=json
Proxy Type Internet
Method GET
Send Body False/disabled
Authentication Basic
Credential Name The name you provided to your credential in step 2.4.6.

With this address and expand property we request all the users from the department provided by the UI5 App.

We pass the usergroup id in the address. We can call it with the variable ${}.

We have set this variable in step 2.4.5 in our Groovy-Script.

As last Query parameter we pass the format JSON parameter. This is just because I like to work with JSON. This is not necessary. If you do not pass this parameter here you will have to make some adjustments yourself in the rest of the flow which is yet to come.


2.4.8 Transform the result from the OData Call

When our OData Call was successful we transform the result so we can continue with our desired result. This transformation takes place in a Groovy-Script:

The Script:
import java.util.HashMap;
import groovy.json.*;
import groovy.xml.*
def Message processData(Message message) {

// Get the body from the incoming message
def body = message.getBody(java.lang.String) as String;

// Create a JSON jsonSlurper and parse the body
def jsonSlurper = new JsonSlurper()
def object = jsonSlurper.parseText(body);

//List of items represented as a map
def users = [];

// Add all the users with an email address to the users array
object.d.UserGroup_users.results.each {
if( != null && != ""){
users.push([email:, name:])

// Create a StringWriter and build your final XML result
def writer = new StringWriter()
def xml = new MarkupBuilder(writer)

// Add all the users as a single xml record to your final XML result
users.each { user->

// Write your XML as a String and set it to the message body -> passed to the next control in the flow
def result = writer.toString();

// Log the result xml message
def messageLog = messageLogFactory.getMessageLog(message);
if(messageLog != null){
messageLog.setStringProperty("Logging#1", "Printing Payload As Attachment")
messageLog.addAttachmentAsString("ResponsePayload:", "Xml to loop: " + writer, "text/plain");

return message;


2.4.9 Loop over the resulting emails of the users and send an email

After we transformed our result of the OData call and built our own XML result, we will loop over it.

But before we start looping we place a Sequential Multicast control, when we finished looping over the emails we can go the the second step of the multicast and send a custom response to the caller of the CPI-Endpoint.

Since we built our own xml result we choose for the Expression Type XPath and we provide the XPath Expression (Path) to all the users to loop over.

For every user, so every time we are in the loop, we perform the following:

We set the email-address of the user and we send an email.

The email-address and username is set in the Content Modifier under the tab Exchange Property:

We create a new name and emailAddress variable based on the value of the final XML we created our own. This by navigating into the XPath.

Then the last step is sending the email. Therefor we set our Mail Adapter with an outgoing request from the end message to the Receiver.

When selecting the arrow to the receiver, select Mail Adapter and provide the following values:

In the Credential Name, provide the name of the credential you created in step 2.1.

In the 'from' field you pass the email-address you want to send emails from. So obviously the email-address you provided in step 2.1.

The 'to' property and subject and body and name accessed the same way we did in step 2.4.7 to access our usergroup id.


2.4.10 Send a custom response message when finished looping over email-addresses

When we finished looping over all the users we want to notify, we set a custom message as response. This by setting the second branch in our Sequential Multicast. In here we create a Groovy-Script and set the response message:

The script:
import java.util.HashMap;
def Message processData(Message message) {
def body = message.getBody();
message.setBody("Mail sent successfully!");
return message;

Awesome we finished building our CPI-Flow!

Time to deploy it!


2.5 Deploy the CPI-Flow

You can deploy the integration flow by pressing the Deploy button.

Next go to the Operations view and select the All tile under the Manage Integration Content.

Here you will see the status of your flow and the endpoint on how to trigger your CPI-Flow.


2.6 Add the Role ESB-Messaging.send to your SAP CP User

Go to the SAP CP subaccount of your integration tenant and assign the role ESB-Messaging.send to your user. This can be done under the Security/Authorizations tab. Provide your user and assign the ESBMessaging.send Role to your user.

If you do not assign this role you will not be able to trigger the CPI-Flow by its endpoint!

Awesome our CPI-Flow is ready!

Time to build our UI5 App!



3.  Develop the UI5 Application

In our UI5 Application we want to use a Rich Text Editor to write the body for our notification. Together with a combobox that provides all the departments (from the HANA DB). Also we provide an input field to fill in the subject.


3.1 Destinations in the SAP CP

Before we start developing our application we will configure our 2 necessary destinations in the SAP CP.

These 2 destinations are:

  1. SAP HANA OData Service Destination

  2. Cloud Platform Integration Flow Endpoint


3.1.1 SAP HANA OData Service Destination

To be able to use the data from our HANA DB in our UI5 app we need to configure the destination for this.

Values for the destination:

Field Value
Description A description
URL (This is the root path of your OData Service URL)
Proxy Type Internet
Authentication BasicAuthentication
User The User Name that you use to login in the SAP HANA Editor or the OData Service
Password Password of this user

Additional Properties Value
WebIDEEnabled True
WebIDEUsage odata_xs, odata_gen
Use default JDK truststore True/Enabled


3.1.1 SAP Cloud Platform Integration Flow Endpoint Destination

To trigger the CPI-Flow to send our notification we need to set the destination.

Field Value
Name cpi
Description A description
URL (root url of the flow that you see when you deployed it. See step 2.5)
Proxy Type Internet
Authentication BasicAuthentication
User The user which you use to logon on the CPI subaccount
Password The password for this user

Allright, the destinations for our app are configured, time for the development!


3.2 Write the code

3.2.1 Neo-app.json

In the neo-app.json file we add the following json-objects to the routes-array:
"path": "/sendMail",
"target": {
"type": "destination",
"name": "cpi",
"entryPath": "/http/sendMail"
"description": "cpi mail"
}, {
"path": "/HANAMDC",
"target": {
"type": "destination",
"name": "HANAMDC"
"description": "HANAMDC"

The first one is to call our CPI-Flow endpoint. We refer to the destination we created earlier by providing the destination name. Next we provide the entryPath to tell the call where to go in our destination. The path keyword will be used in the app to call the CPI-Flow.

The second one is the HANA DB destination, this does not need an entryPath. We will use this OData Service as a model and so configure this in the manifest.json file.


3.2.2 Manifest.json

In the manifest.json file or App Descriptor file we add the following json-object in the
"dataSources": {
"mainService": {
"uri": "/HANAMDC/CpiNotificationApp/CpiNotification.xsodata/",
"type": "OData",
"settings": {
"odataVersion": "2.0",
"localUri": "localService/metadata.xml"

This datasource will be used in our model. We called it mainService and the URI here is built by starting with the value of the path key-word in the neo-app.json file for the HANA DB destination. Next you provide the rest of the path for the OData service.

As last in the manifest.json file we add a model to the models in the sap.ui5 json-object:
"": {
"dataSource": "mainService",
"preload": true,
"settings": {
"defaultBindingMode": "TwoWay"

For this model we configure our dataSource as the mainService we created.


3.2.3 Controller.js

Here the skeleton of our controller.js file. We define the jsonmodel, messagetoast and messagebox in here so we can use it later in our controller.
], function (Controller, JSONModel, MessageToast, MessageBox) {
"use strict";

return Controller.extend("be.MailCPIConnect.MailCPIConnect.controller.View1", {


In the controller.js file we will have 4 functions:

  1. onInit

  2. http

  3. onGroupSelection

  4. sendMail The onInit function:

In here we create a new jsonmodel and set this model to the view. We name it 'mailModel'.
onInit: function () {
var oMdel = new JSONModel();
this.getView().setModel(oMdel, "mailModel");
}, The http function:

This function is based on the following MDN Web Docs documentation Promise XMLHttpRequest()

Basically this function provides us the possibility to perform http calls via ajax/xmlhttprequest via promises. It will build our path/url to call the OData service.
http: function (url) {
var core = {
ajax: function (method, url, headers, args, mimetype) {
var promise = new Promise(function (resolve, reject) {
var client = new XMLHttpRequest();
var uri = url;
if (args && method === 'GET') {
uri += '?';
var argcount = 0;
for (var key in args) {
if (args.hasOwnProperty(key)) {
if (argcount++) {
uri += '&';
uri += encodeURIComponent(key) + '=' + encodeURIComponent(args[key]);
if (args && (method === 'POST' || method === 'PUT')) {
var data = {};
for (var keyp in args) {
if (args.hasOwnProperty(keyp)) {
data[keyp] = args[keyp];
}, uri);
if (method === 'POST' || method === 'PUT') {
client.setRequestHeader("accept", "application/json");
client.setRequestHeader("content-type", "application/json");
for (var keyh in headers) {
if (headers.hasOwnProperty(keyh)) {
client.setRequestHeader(keyh, headers[keyh]);
if (data) {
} else {
client.onload = function () {
if (this.status == 200 || this.status == 201) {
var oResult = {
response: this.response,
responseHeaders: client.getResponseHeader("x-csrf-token")
} else {
client.onerror = function () {
return promise;

return {
'get': function (headers, args) {
return core.ajax('GET', url, headers, args);
'post': function (headers, args) {
return core.ajax('POST', url, headers, args);
'put': function (headers, args) {
return core.ajax('PUT', url, headers, args);
'delete': function (headers, args) {
return core.ajax('DELETE', url, headers, args);

If you take a look at the client.onload function you will see some adjustments. When you create a record in the db you can receive a status of 201 = created. In this case we also want to resolve our promise. We will not get this status code in the app, but just for the record.

Next we create a oResult json-object where we will store the response of the call and where we will save the x-csrf-token we receive by performing the GET call. We need this token to later on post our notification to the cpi-flow.
client.onload = function () {
if (this.status == 200 || this.status == 201) {
var oResult = {
response: this.response,
responseHeaders: client.getResponseHeader("x-csrf-token")
} else {
}; The onGroupSelection function:

When we select a group/department from the combobox we will save the key of this group in a property called '/to' in our mailModel. This because we will later need it when we send our mail.
onGroupSelection: function (oEvent) {
var oMailModel = this.getView().getModel("mailModel");
oMailModel.setProperty("/to", oEvent.getSource().getSelectedKey());
} The sendMail function:

In this function we will send the mail obviously.

We start with showing a busy indicator. Followed by getting our mailModel from the view.

Next we created a oMail object, here we store the to, subject and body values. These values are bound to our mailModel and can be accessed by the getProperty function.

A oHeaders object is created that will be passed to our first call to get the x-csrf-token. This with the value 'fetch' to get the token via the GET call.

When we received the x-csrf-token we set the oHeaders object equal to this token, accessed via the responseHeaders.

So in the POST call we pass the x-csrf-token via the oHeaders object and all our mail content via the oMail object.

When success we hide the busy indicator and show a success message. The opposite for an error.
sendMail: function () {;

var oMailModel = this.getView().getModel("mailModel");

var oMail = {
to: oMailModel.getProperty("/to"),
subject: oMailModel.getProperty("/subject"),
body: oMailModel.getProperty("/body")

var oHeaders = {
"X-CSRF-TOKEN": "fetch"

this.http("/sendMail").get(oHeaders).then(function (result) {
oHeaders = {
"X-CSRF-TOKEN": result.responseHeaders
this.http("/sendMail").post(oHeaders, oMail).then(function () {
var oBundle = this.getView().getModel("i18n").getResourceBundle();
var sMsg = oBundle.getText("NotificationSentSuccess");
}.bind(this)).catch(function () {
var oBundle = this.getView().getModel("i18n").getResourceBundle();

Controller file done!


3.2.4 View.xml

At the top of the xml view file import the 'sap.ui.richtexteditor.RichTextEditor' class. This with the following line: (Now we are able to use the RichTextEditor control)

In the view.xml file we place a combobox to display all our departments from the HANA DB. By providing the path to it. This is being read from our OData model. Next the onGroupSelection is added.

The Input control its value is bound to the mailModel subject property, this way we can access it in our controller like we did.

Finally time to use the RichTextEditor, this control created by first providing the classname we chose 'rte' followed by the control name 'RichTextEditor'. Again the value bound to the mail model.

The last step is providing a send button the send the mail.
<mvc:View controllerName="be.MailCPIConnect.MailCPIConnect.controller.View1" xmlns:mvc="sap.ui.core.mvc" xmlns:f="sap.ui.layout.form"
xmlns:rte="sap.ui.richtexteditor" xmlns:core="sap.ui.core" displayBlock="true" xmlns="sap.m">
<Shell id="shell">
<App id="app">
<Page id="page" title="{i18n>title}">
<Panel width="auto" class="sapUiResponsiveMargin" accessibleRole="Region">
<Label text="{i18n>GroupToNotify}"/>
<ComboBox items="{ path: '/UserGroup', sorter: { path: 'name' } }" selectionChange=".onGroupSelection">
<core:Item key="{id}" text="{name}"/>
<Label text="{i18n>subject}"/>
<Input value="{mailModel>/subject}" class="sapUiTinyMarginBottom"/>
<Label text="{i18n>body}"/>
<rte:RichTextEditor id="RTextEditor" width="100%" height="350px" customToolbar="true" showGroupFont="true" showGroupLink="true"
showGroupInsert="true" editorType="TinyMCE4" value="{mailModel>/body}"/>
<FlexBox class="sapUiSmallMarginTop" alignItems="End" justifyContent="End">
<Button text="{i18n>sendNotification}" press=".sendMail" type="Emphasized"/>

Awesome we finished the development of our UI5 App!

Time to test it!


4.  Test the application

When we open the app we select a department, provide a subject and some content.

Since I work for the development department (because we placed it so in our HANA DB) I will select this department in the combobox.

And let's check our mailbox......


The sending email-address that we used in the CPI-Flow sent us an email with a personal greeting and the subject and content. Perfect!



5.  Time for some recap

So we performed a lot of steps and development. Let us make a summary of what we did:

  1. Create your own HANA MDC Database with tables and content. Exposed via an OData Service.

  2. Model and build your own CPI-Flow to send the email. Secured against x-csrf-attacks. With thanks to Vadim!

  3. Create a UI5 App with the rich text editor to create a nice notification.

As you can see I created this blog to go cover a few SAP services in one demo and show the awesome possibilities they offer. This was the purpose of the whole blog from the beginning.

NOTE!!! : In real cases you can encounter some problems with the amount of mails you want so end via the SMTP protocol. Because you are sending too many emails in a small time period it looks like spam. You can consider using the Microsoft Graph api in this case. This will allow you to send a lot of emails in a small period.


Thanks for reading my blog about 'How to Send Notifications from UI5 App over CPI Flow with Data from HANA DB'. I hope you found it interesting and it helps you out with starting or using these services in the future.

Kind regards,


Labels in this area