Hey, Google, let me talk to HANA

Some time -and some more coding- have passed after this initial teaser from Craig. Using his code calling Amazon Alexa as a foundation, I could trigger some more business into my HANA instances from my Google Home.

There are many components involved in this interaction. I will document the details of most of them, but recommend you take a look at the available documentation on each of the pieces for further understanding:

Creating API.AI intents and Entities

I first created my API.AI entity to recognize the type of purchase order (weekly, daily, monthly..etc):

Then created the Intent that will use the entity:

When I entered the “Create a monthly purchase order” in the “User says” field, it automatically detected my entity and added it in the definition. This will allow me to use the same phrase once and pass the order type as a parameter. I could also do this with the material number and the supplier name or ID, for example.

Create your Google Cloud Function

Having created all the entities and intents that I need, before I can define the integration, I need to define the other end (i.e, what will be called when voice the intent). This is a Google Cloud Function:

You can create on of these from the console, copying the code from a local editor or using a Cloud Source Repository (created separately). The important pieces here are that the name of the function to execute is the right one from your code and you may also want to try the local emulator for your initial test cycles:

The important pieces of code here are:

exports.askHANA = function askHANA(req, res) {

    var assistant = new ApiAiAssistant({request: req, response: res});
    console.log('Request headers: ' + JSON.stringify(req.headers));

  function responseHandler (assistant) {
    const intent = assistant.getIntent();

    switch (intent) {
        
        case 'purchase-order':
           //Get the Entity PurchaseOrderTypes defined in API.AI

            let poTypes = assistant.getArgument('PurchaseOrderTypes');
            if (poTypes===null){poTypes = 'weekly'};
            poTypes = poTypes.toLowerCase();
          
          //Call async function to create Purchase Orders. It will set the value of "data" with the speech for the assistant. 
            createPurchaseOrder(poTypes, function(data){
                    speechOutput = "This is the default speech text for create PO";   
                    speechOutput = data;
                    assistant.tell(speechOutput); 
                    console.log("CREATE PO: assistant tell data" + data);
                    
            } );
            break; //Purchase Order

        default:
                speechOutput = "I'm sorry, I could not interpret that. You can ask to create a purchase order, start the production run or set the default vendor";   
                assistant.tell(speechOutput); 
                console.log("DEFAULT OUTPUT: assistant tell data " + data);     

        }; //switch intent

        
    } //function responseHandler

//Finally, set the actionMap. If this is not set, you will get a timeout error in the console

    let actionMap = new Map();

//The first value refers to the action as defined in the intent in API.AI
    actionMap.set('purchase-order', responseHandler);
//  actionMap.set('other-intent-name', responseHandler);

    assistant.handleRequest(actionMap);


};

Note how the name of the intent needs to be the same as the one you defined as the action in API.AI:

The createPurchaseOrder function is defined as follows:

var createPurchaseOrder = function(itemName, callback){
    var request = new https.request({
        hostname: host, 
        path: "/.../flow.xsodata/newPO",
        method: "POST",
        headers: {
            'Authorization': authStrIoT,
            'Content-Type': 'application/json; charset=utf-8',
            'Accept': '*/*'
        }
    });
/** dummy**/    

    request.end( definePurchaseOrder(itemName) );

    request.on('response', function (response) {
        if( response.statusCode === 201 ){
            lv_vendor = '100';
            lv_QTY1 = 2;
            lv_QTY2 = 5;

            speechOutput = "I have successfully created the Purchase Order,,";

            /** begin remote po update **/
            var request2 = new http.request({
                hostname: hxe_host,
                port: 8000,
                path: "/.../SmartSupplier/newPO.xsjs?MATNR1=" + cMATNR1 + "&QTY1=" + lv_QTY1 + "&MATNR2=" + cMATNR2 + "&QTY2=" + lv_QTY2 + "&SUPPLIER=" + lv_vendor,
                method: "POST",
                headers: {
                    'Authorization': authStrHXE,
                    'Content-Type': 'application/json; charset=utf-8',
                    'Accept': '*/*'
                }
            });
/** dummy **/

            request2.end( defineStockSupplier(itemName) );
            request2.on('response', function (response) {
                console.log('STATUS response 2: ' + response.statusCode);
                if( response.statusCode === 200 ){
                    speechOutput = speechOutput + "successfuly received confirmation from the supplier";

                    /** begin stock update **/
                    var request3 = new https.request({
                        hostname: host,
                        path: "/.../flow.xsodata/newStock",
                        method: "POST",
                        headers: {
                            'Authorization': authStrIoT,
                            'Content-Type': 'application/json; charset=utf-8',
                            'Accept': '*/*'
                        }
                    } );
/** dummy**/
                    request3.end( defineStock(itemName) );
                    request3.on('response', function (response) {
                        console.log('STATUS update STOCK: ' + response.statusCode);
                        if( response.statusCode === 201 ){
                            speechOutput = speechOutput + ",, received the goods and updated stock";
                            callback(speechOutput);

                        }
                    });
                    /** end stock update **/
                } else {
                    speechOutput = speechOutput + "but there was an error contacting the supplier";
                    console.log("error en la request al supplier");
                    callback(speechOutput);
                }
                    
            });

            /** end remote po update **/
        } 
    });
};

Whoa! Too much? What’s happening here is that during the demo we were actually calling two different hosts, one was representing our main Manufacturing system on SAP Cloud Platform and the others were HXE hosts representing our suppliers. Those hosts were actually HANA Express instances in each of our different hosting partners (MS Azure, Google Cloud Platform, Amazon Web Services or an Intel NUC).

We had a different command to change the supplier as well as a definition of what the different types of purchase orders were (the MRP would define that in real life instead of hard-coding, but you get the idea).

Gluing API.AI and Google Functions

Now that we have the first two pieces of the integration together, we can go ahead and define it in the fulfillment in API.AI:

You get this value from the “Trigger” tab in your function definition:

The HANA piece: Calling stored procedures from oData and XSJS scripts

We are calling two types of services to update or insert new records into our tables, an xsjs script and an xsodata that calls a procedure.There is plenty of documentation on how to do this, so I will not go deep into details.

The store procedure is defined similar to this:

PROCEDURE "SMART"."smartfactory.procedures::createProdOrder" ( 
    in row "smartfactory.data::smart_manufacturing.ty_ProductionOrders",
    OUT error "smartfactory.data::smart_manufacturing.ty_error" )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   DEFAULT SCHEMA SMART
   AS
BEGIN

        declare  lv_ORDER_TXT string;
        declare lv_ORDER_DATE  string;
        declare  lv_MATNR string;


        select "smartfactory.data::ProdOrders".NEXTVAL into lv_ordnr from dummy; 

        select    ORDER_TXT, ORDER_DATE,  MATNR  
            into lv_ORDER_TXT,  lv_ORDER_DATE, lv_MATNR 
            from :row;
            
            
            
    /*Get required materials from BOM and check availability

Sparing this part 
...

 */

        
/*Some error handling*/        
    if lv_count = 0 then

            error = select 404 as http_status_code,
        	        'BOM not found' as error_message,
        		    'There is no Bill of Material defined for the material in the Prod Order' as detail from dummy;
    end if;
    
/*Insert new pruchase order with current timestamp   */
    INSERT INTO "smartfactory.data::smart_manufacturing.ProductionOrders" VALUES(
    	lv_ordnr,
    	lv_ORDER_TXT,  
    	now(), 
    	'NEW',
    	lv_MATNR 
    );


END

The call in the oData file was defined as follows:

service namespace "smartfactory.services" {
	        
	    "smartfactory.data::smart_manufacturing.tt_PurchaseOrders" as "newPO"
	        create using "smartfactory.procedures::createPO"; 
}

And the xsjs file in my HANA Express instances has something like:

var matnr1 = $.request.parameters.get("MATNR1");
var qty1 = $.request.parameters.get("QTY1");
var matnr2 = $.request.parameters.get("MATNR2");
var qty2 = $.request.parameters.get("QTY2");
var SUPPLIER = $.request.parameters.get("SUPPLIER");

try {
	var conn = $.hdb.getConnection();
	
	if ( matnr1 > 0){
		var query = "SELECT QUANTITY FROM \"SmartSupplier.data::SMART.STOCK\" where \"MATNR\" = " + matnr1 + " and \"STATUS\" = \'AVAILABLE\' and \"SUPPLIER\" = " + SUPPLIER ;
	
		var rs = conn.executeQuery(query);
		var newQty = rs[0]["QUANTITY"] - qty1;
/**Validate quantities are not negative**/
		var query = "UPDATE \"SmartSupplier.data::SMART.STOCK\" SET \"QUANTITY\"="+ newQty +" WHERE \"MATNR\" = " + matnr1 + " and \"STATUS\" = \'AVAILABLE\' and \"SUPPLIER\" = "  + SUPPLIER;
		conn.executeUpdate(query);
		conn.commit();
	}

/*Adapted from original code, using both materials in the parameter URL*/	

  conn.close();
  
}catch (err) {
	var body = "[ERROR]"+ err;
	$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
	$.response.contentType = "application/json";
	$.response.setBody(body);
}


var body = "Updated Materials" + newQty;
$.response.status = $.net.http.OK;
$.response.contentType = "application/json";
$.response.setBody(JSON.stringify(body));

The code snippets above are adapted samples we used in a demo. The steps to create an anonymous connection are in this tutorial  or in the last part of this blog which also explains how to create a project using HANA Studio in case you are just getting started with SAP HANA.

If you are planning on migrating into XS Advanced, then you should follow these tutorials instead as some definitions above will not work:

• Creating an OData Service with Create Operation and XSJS Exit

• SAP HANA XS Advanced, create a user-provided anonymous service for SAP HANA database Schemas

Happy talking to your HANA instances and see you on Twitter or on LinkedIn !