SERVICE LAYER SQL QUERY


The following are the steps to Create a Custom Service Layer Query.


Open Postman, logon to the SBO Company

The following will appear after successful logon. 


Sample code:

POST https://localhost:50000/b1s/v2/Login
{    "CompanyDB": "SBODemoAU",    "Password": "XXXX",    "UserName": "XXXX"}


Create New Query


Select POST and enter the desired Custom Query    

Sample code:

POST https://localhost:50000/b1s/v2/SQLQueries 

{"SqlCode": "CustomItemListCode", "SqlName": "CustomItemListName", "SqlText": "SELECT ItemCode, ItemName FROM OITM"}



Click Sent button, below message will appear.


Query Output


Select GET and enter the following


Click Send button, below message will appear. That is listing of the item list according to the custom query.


Sample code:

POST https://localhost:50000/b1s/v2/SQLQueries('CustomItemListCode')/List



View Query Definition


Select GET and enter the following


Click Send button, below message will appear. The Query definition is display in SqlText


Sample code:

GET https://localhost:50000/b1s/v2/SQLQueries('CustomItemListCode')



Modify Query


Select PATCH and enter the following, for example adding a new column.


Click Send button, below message will appear with no error.  


Sample code:

PATCH https://localhost:50000/b1s/v2/SQLQueries('CustomItemListCode') 


{"SqlCode": "CustomItemListCode", "SqlName": "CustomItemListName",
 "SqlText": "SELECT ItemCode, ItemName, OnHand FROM OITM"}



Delete Query


Select DELETE and enter the following, 


Click Send button, below message will appear with no error. 


Sample code:

DELETE https://localhost:50000/b1s/v2/SQLQueries('CustomItemListCode') 



SAP User Authorization


Once the Service Layer Query is created, SAP authorization is required to be setup for user access.


User-defined Table


For query to SAP User-defined table, the name of the user-defined table required to be added to the white list. 


Open below file with Notepad Run as Administrator. 


C:\Program Files\SAP\SAP Business One ServerTools\ServiceLayer\Conf\b1s_sqltable.conf


Append the user-defined table name in the list. 




SERVICE LAYER SQL VIEWS


Please note SAP Warranty specifically stated that custom views is not allowed to be created in SAP database. There is a risk that custom views will be removed without warning in future upgrade. Please proceed below with your own risk.


The following are the steps to Create a Custom Service Layer Views


Open SSMS and create a custom view. 

The name of the view required to be end with 'B1SLQuery' in order to be exposed in Service Layer.



Make sure the setup user is Super User. Only Super User is allowed to expose view in Service Layer. 



Open Postman, logon to the SBO Company


Expose View


Enter the following to expose the view


Click Send button, below message will appear with no error.


Sample code:

POST https://localhost:50000/b1s/v2/SQLViews('vCustomer_B1SLQuery')/Expose 



Display View


Select GET and enter the following


Click Send button, below message will appear. That is listing of customer according to the view definition.


The following will appear if the view is not exposed. 



Sample code:

GET https://localhost:50000/b1s/v2/view.svc/vCustomer_B1SLQuery



Unexpose View


Enter the following to unexpose the view


Click Send button, below message will appear with no error. 


Sample code:

POST https://localhost:50000/b1s/v2/SQLViews('vCustomer_B1SLQuery')/Unexpose



SAP User Authorization


Once the Service Layer View is exposed, SAP authorization is required to be setup for user access. 


Reference:
Managing SQL Queries Using Service Layer API

Postman Download