Dear Colleagues,

In this post I want to participate currency exchange rate updating challenge for SAP Business One. There are already 2 different approaches described on the forum how to reach this goal using DI API and B1IF.

DI API: https://blogs.sap.com/2015/05/29/updating-exchange-rates-automatically-in-sap-business-one/

B1IF: https://blogs.sap.com/2015/06/23/how-to-import-exchange-rates-using-b1if/

In this post we will do the same but using HANA XS as the development and administrating tool and B1 Service Layer as the data interface. This approach is applicable to SAP Business One version for HANA since SQL Server version doesn’t support neither XS nor ServiceLayer. After you have performed all steps you can schedule currency rates updating using standard HANA tools to update your rates regularly. This application updates rates for all currencies in your company using ECB (European Central Bank) web-service. Rates are updated according to company rate direction (Direct/Indirect rate) and using Euro cross-rate (in case the local currency is not Euro). Company details are passed in job settings.

If you don’t want to perform all development steps manually you can just download a delivery unit from here and import it into your environment. 

PREREQUSITES

To test it out you will need an SAP Business One version for HANA instance with HANA XS up and running. To make sure that your HANA XS works well you can go to the following addresses: https://<YourHanaAddress>:43<InstanceNumber> or http://<YourHanaAddress>:80<InstanceNumber>

For example: https://192.168.0.1:4300 (for instance number 00). In case you see the following picture, you can go ahead otherwise you need to make it work first

CREATING THE HANA XS PACKAGE

First of all, we need to create a new package that performs our business-logic. Let’s open HANA Studio and choose “SAP Hana Development” perspective:

Go to “Repositories” tab and create a new working space (skip this if you want to use the default or if you already have one)

I have created a workspace “DONE” for our partner namespace (DatatechOne).

We can create a package now. Right click on your workspace and choose New -> Repository Package… in the context menu:

Specify name “LoadCurrencyRates” for your package and press “Finish”. If you decided to change the name of the package you will need to change the reference in the XSJS file as well since it has dependencies by name when it loads the destination files.

Now we need to create an XS Project in our repository. Right click at our LoadCurrencyRates package and choose New -> XS Project:

Specify same project name “LoadCurrencyRates” and press “Next”. At the second step choose your repository workspace (in my case it’s DONE) and press “Next” again. Make sure that you have “Access objects” ticked at this step:

Then press “Finish” to add all default XS Project files to your package. Now your package must look like that:

In this package we will need to create the following files:

1. Performer.xsjslib – the main file with business-logic implemented in JavaScript
2. RunJob.xsjs – the entry point for the XS JOB Scheduler
3. RunTest.xsjs – the entry point to test this functionality using web-browser
4. EcbService.xshttpdest – destination file with ECB web-service connection details
5. ServiceLayer.xshttpdest – destination file with B1 Service Layer connection details
6. Scheduler.xsjob – file with the schedule settings to run our package regularly.

Please note that it’s better not to rename any files since there are references in the code.

Let’s start with the xsjslib file. Right click on your package and choose: New -> Other -> XS JavaScript Library File -> Next -> File name:  Performer

Paste the following code to this file and save it:

// ========================== SERVICE LAYER METHODS ======================= //

// Logs in ServiceLayer and returns session cookies
function getSlCookies(companyDb, userName, password)
{
	// Create client
	var client = new $.net.http.Client();

	// Where and what to send
	var dest = $.net.http.readDestination("LoadCurrencyRates", "ServiceLayer");
	var request = new $.net.http.Request($.net.http.POST, "Login"); 
	request.setBody(JSON.stringify(
			{
				"CompanyDB": companyDb,
				"UserName": userName,
				"Password": password
			}));
	
	// Send the request and synchronously get the response
	client.request(request, dest);
	var response = client.getResponse();

	// Get all the cookies from the response
	var cookies = [];
	for(var c in response.cookies) 
	{
		cookies.push(response.cookies[c]);
	}

	// Close the connection
	client.close();   
	
	// Throw an error message if B1SESSION cookie isn't got
	if (cookies === undefined 
		|| cookies.length === 0 
		|| cookies.findIndex((e) => { return e.name === 'B1SESSION'; }) === -1)
	{		
		throw new Error(response.body.asString());
	}
	
	// Return cookies if everything is ok
	return cookies;
}

// Sets currency rates for the specified currencies using specified SL session 
function postRates(slCookies, currencyRates, date)
{	
	try
	{
		// Create client and destination
		var client = new $.net.http.Client();
		var dest = $.net.http.readDestination("LoadCurrencyRates", "ServiceLayer");
		
		// Create request
		var request = new $.net.http.Request($.net.http.POST, "SBOBobService_SetCurrencyRate");
		for (var cookiesCounter = 0; cookiesCounter < slCookies.length; cookiesCounter++)
		{
			var cookie = slCookies[cookiesCounter];
			request.cookies.set(cookie.name, cookie.value);
		}
		
		// Send a request for each currency.
		for (var counter = 0; counter < currencyRates.length; counter++)
		{
			
			var currencyDetails = currencyRates[counter];
			if (!currencyDetails.rate) continue; // Continue if rate isn't specified
	
			// Set body to the request
			request.setBody(JSON.stringify(
					{
						"Currency": currencyDetails.currencyCode,
						"Rate": currencyDetails.rate,
						"RateDate": date
					}));
			
			// Send
			client.request(request, dest);	
			client.getResponse();
		}
		
		// Close the connection
		client.close();
	}
	catch (exc) {
		var test = exc;
	}
}

// Logs out ServiceLayer
function closeSlSession(slCookies)
{
	try
	{
		// Create client
		var client = new $.net.http.Client();

		// Where and what to send
		var dest = $.net.http.readDestination("LoadCurrencyRates", "ServiceLayer");
		var request = new $.net.http.Request($.net.http.POST, "Logout");
		for (var counter = 0; counter < slCookies.length; counter++)
		{
			var cookie = slCookies[counter];
			request.cookies.set(cookie.name, cookie.value);
		}
				
		// Send the request. This request returns nothing.
		client.request(request, dest);	
		client.getResponse();
		// Close the connection
		client.close();
	}
	catch (exc) {
		var test = exc;
	}
}

// ========================== GET CURRENCIES INFO METHODS ========================= //

// Gets info about company currencies settings
function getCompanyCurrenciesInfo(schemaName)
{
	// Result object with all necessary data
	var result = {
			localCurrency: '',
			isDirectRate: false,
			isLocalEuro: false,		
			currencies: []
		};
	
	// Query to get currencies info
	var qryText = `
			SELECT "OCRN"."CurrCode" AS "CurrencyCode" 
				, "OADM"."MainCurncy" AS "LocalCurrency" 
				, "OADM"."DirectRate" AS "DirectRate" 
			FROM "<SchemaName>"."OCRN" 
			CROSS JOIN "<SchemaName>"."OADM" 
			WHERE "<SchemaName>"."OCRN"."CurrCode" <> "<SchemaName>"."OADM"."MainCurncy"
			`;
	
	// Open connection to HANA
	var con = $.hdb.getConnection();
	try
	{
		// Execute for the schema provided
		qryText = qryText.replace(/<SchemaName>/g, schemaName);
		var rs = con.executeQuery(qryText);
		
		// Fill result object with the data from the first row
		result.localCurrency = rs[0].LocalCurrency.trim();
		result.isLocalEuro = result.localCurrency === 'EUR';
		result.isDirectRate = rs[0].DirectRate === 'Y';
		
		// Fill currencies array
		var iterator = rs.getIterator();
		while (iterator.next())
		{
			
			result.currencies.push({
				currencyCode: iterator.value().CurrencyCode, 
				rate: 0
			});
		}
	}
	finally
	{
		// Close connection
		con.close();
		return result;
	}
}

function getCurrencyRateFromXml(currencyCode, xmlString)
{
	// Response is in XML so we need to parse it as string 
	// since there are no XML built-in functions in HANA 1.0
	try
	{
		var pattern = "[Ss]*(?:s|<CurrencyCode>' rate=')([0-9.]*)['][Ss]*";
		pattern = pattern.replace('<CurrencyCode>', currencyCode.trim());
		
		var regex = new RegExp(pattern);
		var match = regex.exec(xmlString);
		
		if (match !== null)
		{
			// Value is presented
			return parseFloat(match[1]);
		}
		else
		{
			// Value is not presented
			return undefined;
		}
	}
	catch (exc)
	{
		return undefined;
	}
}

function calculateCurrencyRate(rateAgainstEuro, localAgainstEuro, isDirectRate)
{
	var rate = isDirectRate ? localAgainstEuro / rateAgainstEuro : rateAgainstEuro / localAgainstEuro;
	return Math.round(rate * 10000) / 10000;
}

// Fills rates from ECB web-service
function fillRates(currencyInfo)
{
	// Create client
	var client = new $.net.http.Client();

	// Where and what to send
	var dest = $.net.http.readDestination("LoadCurrencyRates", "EcbService");
	var request = new $.net.http.Request($.net.http.GET, ""); 
	
	// Send the request and synchronously get the response
	client.request(request, dest);
	var response = client.getResponse();

	// Get the body
	var bodyXml = response.body.asString();

	// Close the connection
	client.close();        

	// All rates are presented against euro so we need to get the cross-rate 
	// in case if local currency is not Euro
	var localAgainstEuro = 1.0;
	if (!currencyInfo.isLocalEuro)
	{
		localAgainstEuro = getCurrencyRateFromXml(currencyInfo.localCurrency, bodyXml);
		if (localAgainstEuro === undefined) throw new Error('Cannot find the local currency with code ' + currencyInfo.localCurrency + ' in the ECB web-service'); // Stop processing if local currency isn't presented
	}
	
	var currencyAgainstEuroRate = 0.0;
	for (var counter = 0; counter < currencyInfo.currencies.length; counter ++)
	{
		var currency = currencyInfo.currencies[counter];
		
		if (!currencyInfo.isLocalEuro && currency.currencyCode === 'EUR')
		{
			// Calculate Euro rate according to company rate direction
			currency.rate = calculateCurrencyRate(1, localAgainstEuro, currencyInfo.isDirectRate);
		}
		else // Calculate other currencies using Euro cross-rate 
		{
			// Get currency exchange rate against euro
			currencyAgainstEuroRate = getCurrencyRateFromXml(currency.currencyCode, bodyXml);
			
			// Handle next if this currency isn't presented in the XML
			if (currencyAgainstEuroRate === undefined) continue;
			
			// Calculate rate with cross-rate and company rate direction
			currency.rate = calculateCurrencyRate(currencyAgainstEuroRate, localAgainstEuro, currencyInfo.isDirectRate);
		}		
	}
}

function GetCurrentDateAsString()
{
	// Prepare todays date in necessary format
	var todayDate = new Date();
	var stringYear = String(todayDate.getFullYear());
	var stringMonth = todayDate.getMonth() + 1 < 10 // Zero based
				? '0' + String(todayDate.getMonth() + 1)
				: String(todayDate.getMonth() + 1);
	var stringDate = todayDate.getDate() < 10
				? '0' + String(todayDate.getDate())
				: String(todayDate.getDate());
	return stringYear + stringMonth + stringDate;
}

// =============================== ENTRY POINT ============================ //

// Main function that performs the business logic 
function loadRates(schemaName, userName, password)
{
	var cookies; // SL session cookies
	try // Handle exceptions in test mode only
	{
		// Try to login to Service Layer
		cookies = getSlCookies(schemaName, userName, password);
		
		// Get company currencies to work with
		var curInfo = getCompanyCurrenciesInfo(schemaName);
		
		// Check if currencies are got successfully and there are currencies except local currency
		if (curInfo === undefined 
				|| curInfo.localCurrency === undefined 
				|| curInfo.localCurrency === ''
				|| curInfo.currencies.length === 0
			) throw new Error('Cannot get currency details!');
		
		// Get currency rates for all necessary currencies
		fillRates(curInfo);
		
		// Set currency rates for the company
		postRates(cookies, curInfo.currencies, GetCurrentDateAsString());
	}
	finally
	{
		// Logout if connected
		if (cookies !== undefined) closeSlSession(cookies);
	}	
}

The business logic is created and now we need to create 2 XSJS files. One to test our functionality manually and one to schedule it using the XS JOB Scheduler.

Same with XSJSLIB file (the file type is not the same), right click on your package and choose: New -> Other -> XS JavaScript File -> Next -> File name:  RunTest

Insert the following code:

// Gets query string parameters
// Parameters are the following: schemaName, userName, password
function getParams()
{
	var params = {};
	
	// Extract parameters from the query string
	for (var counter = 0; counter < $.request.parameters.length; counter++)
	{
		var param = $.request.parameters[counter];		
		if (param.name.toUpperCase() === 'SCHEMANAME') params.schemaName = param.value;
		else if (param.name.toUpperCase() === 'USERNAME') params.userName = param.value;
		else if (param.name.toUpperCase() === 'PASSWORD') params.password = param.value;
	}
	
	// Validate parameters
	var mandatoryParams = [];
	if (!params.hasOwnProperty('schemaName')) mandatoryParams.push('SchemaName');
	if (!params.hasOwnProperty('userName')) mandatoryParams.push('UserName');
	if (!params.hasOwnProperty('password')) mandatoryParams.push('Password');

	// Throw an error in case not all parameters are got
	if (mandatoryParams.length > 0) throw new Error('The following mandatory parameters are not provided: ' + mandatoryParams.join(', '));
	
	return params;
}

function execute()
{
	var result = {
			isSuccess: true,
			errMessage: ''
	};

	try
	{
		// Get parameters from the query string
		var requestParams = getParams();
		
		// Execute function
		var rateLoader = $.import('LoadCurrencyRates', 'Performer');
		rateLoader.loadRates(requestParams.schemaName, requestParams.userName, requestParams.password);
	}
	catch (exc)
	{
		result.isSuccess = false;
		result.errMessage = !exc.message ? exc : exc.message;
	}
	
	//Build the response for test purposes
	$.response.contentType = "application/json";			
    $.response.status = $.net.http.OK;
    $.response.setBody(JSON.stringify(result));
}

execute();

One more time for the RunJob file, right click on your package and choose: New -> Other -> XS JavaScript File -> Next -> File name:  RunJob

Paste the following:

// The main entry point for an XS Job
function execute(inputParameter)
{
	try
	{
		var rateLoader = $.import('LoadCurrencyRates', 'Performer');
		rateLoader.loadRates(inputParameter.schemaName, inputParameter.userName, inputParameter.password);
	}
	catch (exc) {
		throw !exc.message ? exc : exc.message;
	}
}

XSJS files are created, now we can create the destination files.

Destination for ECB service: right click on your package and choose: New -> Other -> XS HTTP Destination Configuration -> Next -> File name:  EcbService

Place the following in your destination file (change the proxy part in case you use proxy):

host = "www.ecb.europa.eu";
port = 443;  
description = "Daily currency exchange rates posted by ECB";
useSSL = true;
pathPrefix = "/stats/eurofxref/eurofxref-daily.xml";
authType = none;
useProxy = false;
proxyHost = "";
proxyPort = 0;
timeout = 0;

Destination for ServiceLayer: right click on your package and choose: New -> Other -> XS HTTP Destination Configuration -> Next -> File name:  ServiceLayer

Here we have 2 options: we can either use HTTP calling one of load balancing ports (such as 50001) or HTTPS. This will be internal traffic so you can decide if it’s important to use the secure connection. In case you have decided to use the untrusted connection, you can skip trust store configuring (for ServiceLayer, you will need to set it up for ECB anyway since there is no untrust connection available anymore).

Place the following in your destination file for HTTP (change the proxy part in case you use proxy):

host = "<HanaAddress>";
port = 50001;  
description = "ServiceLayer UNTRUSTED!!!!";
useSSL = false;
pathPrefix = "/b1s/v1/";
authType = none;
useProxy = false;
proxyHost = "";
proxyPort = 0;
timeout = 0;

Or place the following in your destination file for HTTPS (change the proxy part in case you use proxy):

host = "<HanaAddress>";
port = 50000;  
description = "ServiceLayer trusted";
useSSL = true;
pathPrefix = "/b1s/v1/";
authType = none;
useProxy = false;
proxyHost = "";
proxyPort = 0;
timeout = 0;

Don’t forget to change <HanaAddress> to your IP or domain name. Destination files are done now we need to create the xsjob file to configure our schedule.

Right click on your package and choose: New -> Other -> XS Job Scheduler File -> Next -> File name:  Scheduler.

In this file input the following code and save it:

{
    "description": "Loads currency exchange rates from ECB web-service using ServiceLayer",
    "action": "LoadCurrencyRates:RunJob.xsjs::execute",
    "schedules": [
       {
          "description": "The default schedule",
          "xscron": "* * * * 23 00 0",
          "parameter": {"schemaName": "SBODEMOGB",
					"userName": "manager",
					"password": "manager"
				}
       }
    ]
}

That’s it. The package is ready and we can activate it. Right click at your package and choose Activate in the context menu.

If you see no errors we can move on and setup our package.

CREATING AN ADMIN USER

To administrate (setup schedule and trustore) our package we will need to create a user with necessary privileges. Let’s create a user called “XS_ADMIN” and grant necessary roles to him. Execute the following script in HANA Studion:

CREATE USER XS_ADMIN PASSWORD "Password1!";
CALL _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.admin.roles::TrustStoreAdministrator','XS_ADMIN');
CALL _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.admin.roles::JobAdministrator','XS_ADMIN');
CALL _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.admin.roles::JobSchedulerAdministrator','XS_ADMIN');
CALL _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.admin.roles::HTTPDestAdministrator','XS_ADMIN');

Now we have the user “XS_ADMIN” with the necessary privileges and the password “Password1!”. You will be asked to change the password when you log into XS Admin Tool.

TRUST STORE SETTING UP

Here we need to create trust stores and assign them to our package for ECB and B1 ServiceLayer certificates in case you decided to call ServiceLayer via HTTPS. Let’s start from ECB service. As the first step you need to download the certificate. You can do that if you open this web-site in a browser and press the lock in the address bar. You can find more information of how to export a certificate via browser in this video.

Once we have our certificate downloaded we can setup the trustore for it and assign it to our application.

Go to HANA XS admin tool by path https://<YourHanaAddress>:43<InstanceNumber>/sap/hana/xs/admin and input our XS_ADMIN user credentials. Open the popup menu (press the burger button on the left-hand side) and choose the Trust Manager option.

Press “Add” on the bottom tool bar (on the left-hand side) and input “ECB” to the Trust Store name field. Press OK. Trust store is created. Now we need to import our certificate we have downloaded in the previous step. Choose “ECB” trust store, go to the “Certificate list” tab, press “Import Certificate” button in the bottom tool-bar, choose the saved certificate (*.crt/*.cer or another certificate file depending on the way you saved that) file and press “Import Certificate”. Trust store is ready to be used.

After trust store is created we need to assign this trust store to our package. Go to “XS Artifact Administration” and press the arrow (it might be changed in the future XS Admin Tools versions UI) next to our package.

Choose our EcbService.xshttpdest file to assign the trust store to. Go to “Authentication details” tab and press the Edit button from the bottom tool-bar. Tick “SSL Enabled” checkbox, change “SSL Authentication type” to “Client certificate” and choose our “ECB” trust store and hit the save button. The result must look like on the picture below:

The trust store is assigned and now we need to do the same in case we decided to use trusted connection with ServiceLayer. Firsly we need to download the certificate. You can do that from this address: https://<YourHanaServer>:50000 same with ECB certificate or you can download it from your SLES system by path: <ServiceLayer installation folder>/conf/server.crt.

By default it’s /usr/sap/SAPBusinessOne/ServiceLayer/conf/server.crt

When you have your certificate downloaded you need to create the trust store for it (let’s call it B1 SL) and assign it to the package for the ServiceLayer.xshttpdest file. The result must be the following:

The trust stores are assigned and our package is ready to be tested.

TESTING THE PACKAGE

To test our package we just need to call it via HTTP (simply input https://<YourHanaAddress>:43<InstanceNumber>/LoadCurrencyRates/RunTest.xsjs?SchemaName=SBODEMOGB&UserName=manager&Password=manager in the address bar of any browser). You can specify your DB credentials in the query string. In case everything went smooth you will see the following result:

{"isSuccess":true,"errMessage":""}

Otherwise you will see the error message, for example:

{"isSuccess":false,"errMessage":"{\n   \"error\" : {\n      \"code\" : -304,\n      \"message\" : {\n         \"lang\" : \"en-us\",\n         \"value\" : \"Fail to get DB Credentials from SLD\"\n      }\n   }\n}\n"}

NOTE: To test this application you must login with a user with privileges to select data from your schema (for example SYSTEM). Same user must be assign to the JOB. Otherwise you will have the authorisation error, such as: Cannot get currency details!

SCHEDULING RATES LOADING

In the meantime, all steps are completed and now we just need to setup the schedule. Go back to the HANA XS Admin Tool (same where we managed our trust store) and choose the “XS Job Dashboard” menu point. Enable the scheduler by a tumbler “Scheduler enabled” on the top, right-hand side corner. Drill down into our package by clicking on the row corresponding to our package.

Here we need to activate our job. Go to the “Configuration” tab and tick the “Active” checkbox. Input credentials for this job. Press the “Save” button. Job is activated.

Now we need to setup the schedule. We already have one (the one we have created in our Schedule.xsjob file) but since ECB updates their rates at 4 pm ETC we want to run it twice a day: at 1 am to setup it for the business day and at 4:10 pm to keep it updated. Press “Add schedule” button and input settings like on the picture below:

NOTE: Cron settings are in UTC, so I need to setup it 2 hours before since I have CEST timezone.

Press Ok. The schedules are setup and now we can check if the loader is planned. If you press “View Logs” button over the grid you must see the following:

That’s it. Functionality is done and scheduled using native HANA tools. Hope you enjoyed this post. Feel free to share your feedback.

Cheers,

Sergei Travkin,

Datatech One