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:
- Performer.xsjslib – the main file with business-logic implemented in JavaScript
- RunJob.xsjs – the entry point for the XS JOB Scheduler
- RunTest.xsjs – the entry point to test this functionality using web-browser
- EcbService.xshttpdest – destination file with ECB web-service connection details
- ServiceLayer.xshttpdest – destination file with B1 Service Layer connection details
- 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
Brilliant work 🙂
Thanks for sharing!
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.
Hi Jeremie,
Have you performed every step before trying to call this script? I assume the problem can be one of the following:
Do you use the trusted connection? Do you see your package in XS Artifacts?
Hello Sergei
Thank you for your help
Jeremie,
Do you have .xsaccess and .xsapp files in your package? You package must look like this
No I only have that :
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
Sergei it looks better. It asks me to enter my credentials. I did it and have now a white screen. Is it normal ?
Yup. that is just to test it. Check currency rates in SAP B1 (the system you specified at the end of the JS script).
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.
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.
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.
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.
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.
Jeremie,
Have you changed this portion with your B1 credentials?
Yes I did it. Do i have to let quotes ?
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.
I have this :
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:
Unfortunately this is already what i have :
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.
Oups sorry ...
I have this :
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
Ok I have postman but what is the URL ?
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
}
I have this now :
Did you use 50001 with SSL? Can you please share your destination file?
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;
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?
Here is my Authentification details :
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?
It doesn't work again. Still have the same message :
Here is the response of my REST Client :
{
"error" : {
"code" : 101,
"message" : {
"lang" : "en-us",
"value" : "Bad POST content."
}
}
}
My parameters :
{
“CompanyDB”: SBO_...,
“UserName”: XS_ADMIN,
“Password”: ....
}
Ahhhh, I got it 🙂 That is SBO credentials, not HANA. Try to use your SAP company user and password.
BTW, you need to surround your values with double quotes, for example: {"CompanyDB":"SBODEMOGB","UserName":"manager","Password":"manager"}
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
This is better with my sbo credentials
I know have :
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:
Don't forget to change the schema name.
It works. I have this :
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?
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
I have that :
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.
It works now. I have my 2 currencies. Thanks a lot Sergei
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.
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 ?
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.
Yes it's active :
But this is still inactive ....
Ok, but have you activated this particular job? It must look like that if you go to the configuration tab
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
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.
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.
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
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.
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 ?
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.
Hi,
I get parsing errors for both "Performer.xsjslib" and "RunTest.xsjs" (see screenshot)
Could you please take a look?
Best Regards
Error Line in "Performer.xsjslib"
for(var c in response.cookies)
Error Line in "RunTest"
for (var counter = 0; counter < $.request.parameters.length; counter++)
Did you fine any reason for that ?
Hi Sergei
i configured the package as you describe but i'm always facing this issue :
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!