Skip to Content
Author's profile photo Sergei Travkin

Update SAP B1 rates using HANA XS and ServiceLayer

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

Assigned Tags

      63 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ralph Oliveira
      Ralph Oliveira

      Brilliant work 🙂

      Thanks for sharing!

       

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Hello Sergei

      first thanks for you documentation. I have an issue after making everything you wrote.

       

      I have this error when i call : https://...:4300/LoadCurrencyRates/Perform.xsjs

       

      404 - Not found

      We could not find the resource you're trying to access.
      It might be misspelled or currently unavailable.

      Do you have an idea ?
      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Hi Jeremie,

      Have you performed every step before trying to call this script? I assume the problem can be one of the following:

      1. You renamed either the package or the XSJS file. In this case you need to change the URL with your details.
      2. You haven't activated your package. In this case your application exists only on the client and cannot be called.

      Do you use the trusted connection? Do you see your package in XS Artifacts?

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Hello Sergei

      1. I named every file as you mentionned in your documentation
      2. My package is activated (Right click on the package -> Activate in HANA Studio)
        1. Yes i use a trusted connection :
          : None [Altered]
          : SSL Disabled [Altered]
          : Not Applicable
          : Not Applicable
          : B1 SL
        2. And yes I can see my package in XS Artifacts

      Thank you for your help

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Jeremie,

      Do you have .xsaccess and .xsapp files in your package? You package must look like this

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      No I only have that :

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Sorry, my bad ? I forgot to describe one step. I have updated the post. In the meantime you can create them manually. Please check this thread: https://archive.sap.com/discussions/thread/3802212

      Sorry again, shame on me ?

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Sergei it looks better. It asks me to enter my credentials. I did it and have now a white screen. Is it normal ?

       

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Yup. that is just to test it. Check currency rates in SAP B1 (the system you specified at the end of the JS script).

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Please note that you must have input the HANA user (when you ran this test) that has privileges for the schema you load rates to. I test it with SYSTEM user. Our XS_ADMIN won’t fit as it has no schemas assigned.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Sergei this is what i do. Means i use credentials i used to connect to my schema in HANA Studio. So it should work.

      Any idea of why it doesn't copy anything in the database.

      For information in my project the local currency is PLN.

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Jeremie,

      Please let me know if it works or not when you are done. Also I would appreciate any feedback about complexity of the guide. Maybe you can suggest something to change in the places that were confusing.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Yes Sergei. I still have no result at this moment. I'm actually working on it and will let you know when it's over.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Sergei this is what i do. Means i use credentials i used to connect to my schema in HANA Studio. So it should work.

      Any idea of why it doesn’t copy anything in the database.

      For information in my project the local currency is PLN.

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Jeremie,

      Have you changed this portion with your B1 credentials?

      // Test entry point 
      loadRates({
      		schemaName: 'SBODEMOGB',
      		userName: 'manager',
      		password: 'manager'
      	});
      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Yes I did it. Do i have to let quotes ?

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Well, I have updated the Perform.XSJS code to show the output. Would you please update your code, reactivate the package and try to call it again? It must clarify the reason.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      I have this :

      {"isSuccess":false,"errMessage":"Cannot connect to SL"}
      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Ok, now it's much more clear. Please go to XS Artifact administration again (where you specified your trust store) and specify as on the picture below:

      So when you save it it must look like:

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Unfortunately this is already what i have :

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Sorry for using you as a guinea pig 🙁 I updated the XSJS script to show the original SL error, maybe it might be easier to understand the reason. Would you please update the code, reactivate and try again.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Oups sorry ...

       

      I have this :

      {"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"}
      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      I get this error when setup incorrect user name and password in the credentials portion. Can you please try to login to SL using postman or another REST client if you are used with SL? Also you can check this thread: https://answers.sap.com/questions/188349/fail-to-get-db-credentials-from-sld.html

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Ok I have postman but what is the URL ?

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      POST to https://YourHanaServer:50000/b1s/v1/Login

      with body (set the same parameters as at the end of the script)

      {
      "CompanyDB": companyDb,
      "UserName": userName,
      "Password": password
      }

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      I have this now :

      {"isSuccess":false,"errMessage":"HttpClient.getResponse: Can't get the response from the server: IPCon error: \"Connection to bbhdph1.sboga.local lost while reading response.\""}
      
      For information I change in the file ServiceLayer the port. I put 50000 instead of 50001
      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Did you use 50001 with SSL? Can you please share your destination file?

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      I'm sorry Sergei I don't understand your question.

       

      host = "......";
      port = 50000;
      description = "ServiceLayer TRUSTED!!!!";
      useSSL = false;
      pathPrefix = "/b1s/v1/";
      authType = none;
      useProxy = false;
      proxyHost = "";
      proxyPort = 0;
      timeout = 0;

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Ok, maybe I wasn't clear in the post. You can call ServiceLayer by HTTP or HTTPS. You specify it in the destination file.

      For HTTP you must setup:

      port = 50001; // Or another load balancing member port

      useSSL = false;

      For HTTPS:

      port = 50000;

      useSSL = true;

       

      Now you are trying to call HTTPS without SSL - that is not possible. If you just changed from 50001 to 50000 that means that you tried to call using HTTP (untrusted). Can you change useSSL to true in the destination file and call it again?

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Here is my Authentification details :

      None [Altered]
      SSL Enabled [Altered]
      Client Certificate
      Enabled
      B1 SL
      And general information :
      Not Applicable
      ServiceLayer trusted [Altered]
      bbhdph1.sboga.local [Altered]
      50000 [Altered]
      /b1s/v1/ [Altered]
      0 [Altered]
      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Please change your ServiceLayer.xshttpdest to the following:

      host = “bbhdph1.sboga.local”;

      port = 50000;

      description = “ServiceLayer trusted”;

      useSSL = true;

      pathPrefix = “/b1s/v1/”;

      authType = none;

      useProxy = false;

      proxyHost = “”;

      proxyPort = 0;

      timeout = 0;

      Reactivate the package after the change.

      BTW, have you tried to call SL via Postman?

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      It doesn't work again. Still have the same message :

      {"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"}
      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Here is the response of my REST Client :

      {
      "error" : {
      "code" : 101,
      "message" : {
      "lang" : "en-us",
      "value" : "Bad POST content."
      }
      }
      }

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      My parameters :

       

      {
      “CompanyDB”: SBO_...,
      “UserName”: XS_ADMIN,
      “Password”: ....
      }

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Ahhhh, I got it 🙂 That is SBO credentials, not HANA. Try to use your SAP company user and password.

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      BTW, you need to surround your values with double quotes, for example: {"CompanyDB":"SBODEMOGB","UserName":"manager","Password":"manager"}

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Yup, but at least that error means that SL was reached but couldn't connect to the DB. Otherwise the error was on the connection stage. Are you sure you set the right credentials? Cause this error means that access was denied. If the error via postman will be the same and you are absolutely sure that credentials are correct you should try the solution from HERE

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      This is better with my sbo credentials

       

      I know have :

      {"isSuccess":false,"errMessage":"Cannot get currency details!"}
      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Hey, now we are in! 🙂 SL error has gone, but now your XS user (the one you input when you tried to test this application) has no rights to execute query for this schema. Go to HANA XS ADMIN tool and press logout. Then try to call it again, you will be asked to input credentials. Input your user you are going to use (for example SYSTEM) for the job. Or you can grant privileges for the XS_ADMIN user using this script:

      GRANT SELECT ON SCHEMA SBODEMOGB TO XS_ADMIN;

      Don't forget to change the schema name.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      It works. I have this :

      {"isSuccess":true,"errMessage":""}
      
      But for our case it doesn't work because our local currency is PLN so it doesn't fill the rate for EUR. 
      
      Maybe this is due to the XML source ..
      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Must work, I tested it for SBODEMOGB where local currency is GBP and SBODEMOIT where local currency is EUR. How many currencies do you have? This application takes currency ALPHA-3 codes from the code field. Do you have accurate currency codes?

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      If you have currencies like that this application must fill rates for all currencies that are presented at ECB service if your local currency is Euro or another presented at ECB. But only if the code is valid

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      I have that : 

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Actually this is ok. Please update the XSJS file, I have added the check for the currency code. If your local currency doesn't fit you will be informed.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      It works now. I have my 2 currencies. Thanks a lot Sergei

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      I'm glad it helped. Can you please recap the steps confused you or that you did but they weren't described well enough? I will update the blog according to your experience. Thanks for the feedback.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Yes Sergei I will do that. I just have one more question wich is :

      Do you have an idea of why the scheduler doens't work ?

      When i click on the button "View logs" nothing appears. We should see next events ... You confirm ?

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Hi Jeremie,

      Yes if you go to the view log tab you need to see the following:

      Have you enabled jobs on the Job Dashboard page?

      BTW, please update the XSJS file. It's not critical but with the previous version Jobs will show an error even if everything went smoothly.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Yes it's active :

       

      But this is still inactive ....

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Ok, but have you activated this particular job? It must look like that if you go to the configuration tab

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Hello Sergei

      i have a last issue (I hope) about the scheduler. I added 2 schedules :

       

       

      My issue is that even if I put different parameters between the 2 schedules it always take the one in the function runTest() of the file perform.xsjs

      So i decided to comment this block and now i have no result. Means nothing is insert in the database.

      This is strange it seems like the function need to have the block "runTest" to work. But if it is the case I canno't use this process for my multiple company(DB).

      Thanks

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Hi Jeremie,

      Looks like I screwed up here a bit. Give me a couple of days, I will investigate the reason this weekend and get back with the solution.

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Hi Jeremie!

      I have changed the post in several points after your feedback. The company credentials were transfered from code to the query string and the XSJS file was split for 2 XSJS and 1 XSJS Lib files for easier testing and scheduling. Please remove the old Perform.xsjs file and create new ones. Also during the tests I realised that old unsecured ECB service is not availablle anymore, so I had to change the destination file and update the guide in a trustore section. Also I added a link to the delivery unit, so if you don’t want to make this changes manually you can just import it using this DU.

      I have tested the updated functionality with your use-case (scheduled for different DBs) and it worked fine.

      Please let me know if you still have problems or questions unanswered.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Hello Sergei sorry for the delay of my answer. Anyway it works for my company in Poland if we use the XML for the european central bank but the legal rule in Poland is to use the one from the poland central bank. So I have to work on the perform.xsjs file for reading this new XML. Thanks

       

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Hi Jeremie, yes, then in your case you need to change the destination file to retrieve rates from another service and customise the fillRates function to get rates from the service response.

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Hello Sergei

      how can I contact you in private. I would like to know if it is possible to extract massive datas of items via Service layer with a result in XML File ?

      Author's profile photo Sergei Travkin
      Sergei Travkin
      Blog Post Author

      Hi Jeremie,

      You can find the reference to my linkedin at my accounts page. You can reach me out there. But I’d rather post a question on the forum, if you have any questions regarding SAP. Then the chance to get it answered will be much better + maybe someone else finds it useful.

      PS if your question is about getting response from service layer in xml then I think the answer is no. The response is in JSON. 

      Author's profile photo Christian Birkholtz
      Christian Birkholtz

      Hi,

      I get parsing errors for both "Performer.xsjslib" and "RunTest.xsjs" (see screenshot)

       

      Could you please take a look?

       

      Best Regards

       

      Author's profile photo Christian Birkholtz
      Christian Birkholtz

      Author's profile photo Christian Birkholtz
      Christian Birkholtz

      Error Line in "Performer.xsjslib"

      for(var c in response.cookies)

       

      Error Line in "RunTest"

      for (var counter = 0; counter < $.request.parameters.length; counter++)

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Did you fine any reason for that ?

      Author's profile photo Jérémie SIONNEAU
      Jérémie SIONNEAU

      Hi Sergei

      i configured the package as you describe but i'm always facing this issue :

      {"isSuccess":false,"errMessage":"Cannot get currency details!"}
      
      Do you have an idea why ? 
      
      Regards
      Author's profile photo Jose Luis Murcia
      Jose Luis Murcia

      Hi Sergei,

       

      First of all, thank you for sharing this. I managed to update currencies when the local currency is set to EURO with the standard sap b1 integration framework scenario but it doesn't work out of the box when is not EURO.

      Your solution also works when the local currency is not set to euro and despite it took me a while to figure out some points (not a developer here just and advanced sap b1 user) the test worked and I got updated currencies in one company where the local currency is not euro. Congratulations!

      How can I use the package to update currencies in more than one schema? Can I just add two schema names separated by commas to the scheduler file?Or repet the whole code of the scheduler file changing the schema? Or even creating another file? I don't know what is the correct or best approach.

      Thanks agian for your help!