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.
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
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.
In this package we will need to create the following files:
1. Perform.xsjs – the main file with business-logic implemented in JavaScript
2. EcbService.xshttpdest – destination file with ECB web-service connection details
3. ServiceLayer.xshttpdest – destination file with B1 Service Layer connection details
4. 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 xsjs file. Right click on your package and choose: New -> Other -> XS JavaScript File -> Next -> File name: Perform
Paste the following code to this file (change the test connection details for the test stage; If you don’t want to hardcode credentials you will not be able to test it via browser) and save it:
// ========================== SERVICE LAYER METHODS ======================= //
// Logs in ServiceLayer and returns session cookies
function getSlCookies(companyDb, userName, password)
{
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, "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();
return cookies;
}
catch (exc)
{
return undefined;
}
}
// 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;
result.isLocalEuro = rs[0].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);
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) return; // 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 for the job
function loadRates(inputParameter)
{
// Try to login to Service Layer
var cookies = getSlCookies(inputParameter.schemaName, inputParameter.userName, inputParameter.password);
// If couldn't open session with SL - stop processing
if (cookies === undefined || cookies.length === 0) return;
try
{
// Get company currencies to work with
var curInfo = getCompanyCurrenciesInfo(inputParameter.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
) return;
// Get currency rates for all necessary currencies
fillRates(curInfo);
// Set currency rates for the company
postRates(cookies, curInfo.currencies, GetCurrentDateAsString());
}
finally
{
// Logout anyway
closeSlSession(cookies);
}
}
// Test entry point
loadRates({
schemaName: 'SBODEMOGB',
userName: 'manager',
password: 'manager'
});
XSJS file is created, now we can create 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 = 80;
description = "Daily currency exchange rates posted by ECB";
useSSL = false;
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.
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:Perform.xsjs::loadRates",
"schedules": [
{
"description": "Loads currency exchange rates from ECB web-service using ServiceLayer",
"xscron": "* * * * 23 00 0",
"inputParameter": {
"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
If you decided to call ServiceLayer via HTTPS you will need to setup the trustore with ServiceLayer certificate and assign it to your package. As the first step you need to download the certificate. You can do that if you open https://<YourHanaServer>:50000 in a browser and press the lock in the address bar. You can find more information of how to do that via browser in this video: https://www.youtube.com/watch?v=dJ_5SJLWMGY
Also 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
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 “B1 SL” 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 “B1 SL” trust store, press “Import Certificate” button in the bottom tool-bar, choose the saved *.crt 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 ServiceLayer.xshttpdest file to assign the trust store to. Go to “Authentication details” tab and press the Edit button from the bottom tool-bar. Choose our “B1 SL” trust store and hit the save button. The trust store is 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/Perform.xsjs in the address bar of any browser). Check if your rates were loaded to the company specified at the end of your XSJS file. These credentials are used for this test only.
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 ETC 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.
No comments:
Post a Comment