Overview

The OneEnterprise SAP Business One connector retrieves data from Business One on a timer basis. For most business objects, the connector uses the standard getList APIs of the SAP Business One Service Layer. Some objects — for example, the inventory transaction log (OIVL/OINM) or warehouse stock levels (OITW) — aren't exposed as Service Layer entities, so you can't fetch them through standard getList calls.

For these objects, the connector executes a registered SQL query through the Service Layer SQLQueries API. You register the query once in the Business One system, and the OneEnterprise timer process executes it repeatedly.

This guide describes the one-time setup steps that your IT administration team and consulting partner perform on the SAP Business One system before the connector can use this mechanism. It covers the generic procedure for any custom data object, along with a complete worked example for retrieving stock movements.

This mechanism uses only official Service Layer APIs. It reads data using SELECT statements; it never writes directly to the Business One database.

Before you begin

Prerequisites

• SAP Business One 10.0, Feature Package 2011 or later (the release that introduced the SQLQueries API), on SAP HANA or Microsoft SQL Server.
• The Service Layer is installed and reachable from the OneEnterprise integration server (default port 50000).
• A Business One user account for the OneEnterprise connector with authorization to create and execute SQL queries.
• Administrative access to the server that hosts the Service Layer, including its configuration directory.
• The query definition provided in your OneEnterprise project documentation includes the query code, query name, and the exact SQL statement.
• A maintenance window — extending the table allowlist requires a Service Layer restart.

Who does what

The Service Layer doesn't accept ad hoc SQL. Instead, you register a query once as a named object (similar to user-defined queries in the Business One client), and the system executes it afterward using its code. OneEnterprise uses this pattern as follows:

1. The OneEnterprise timer triggers the inbound method for the data object.
2. The connector executes the registered query and passes the current cursor value (also called a watermark) as a parameter. The cursor identifies the last record that the connector already processed.
3. The Service Layer returns the next block of records. OneEnterprise controls the block size through the page-size request header.
4. The connector processes the records and advances the stored cursor. The next timer run continues exactly where the previous run stopped.

Because the cursor is based on a monotonically increasing column (or a timestamp), the connector processes every record exactly once, and interrupted runs resume without data loss.

The Service Layer accepts SELECT statements only and always paginates SQL query results. Both are server-side protections and cannot be disabled.

Setup steps

Step 1: Allow the source tables in the Service Layer

Performed by: customer IT administrator

The Service Layer executes SQL queries only against tables explicitly allowed by its configuration. You must add tables that aren't part of the default allowlist — such as OINM — once.

1. On the server that hosts the Service Layer, open the file b1s_sqltable.conf in the conf Directory of the Service Layer installation.
2. Add the table name(s) listed in your OneEnterprise project documentation, following the format of the existing entries in the file.
3. Save the file.
4. Restart the Service Layer so the change takes effect.

Restarting the Service Layer briefly interrupts all Service Layer-based integrations and add-ons. Perform this step in a maintenance window.

Version note: As of SAP Business One 9.3, OINM is a database view; the physical data resides in the underlying inventory log tables (for example, OIVL). Depending on your Business One version, the allowlist entry must name the view, the underlying table, or both. Your OneEnterprise project documentation states the exact entries for your version.

Step 2: Prepare the SQL statement

Performed by: consulting partner

Use the SQL statement exactly as provided in your OneEnterprise project documentation. If you adapt it (for example, to add columns), follow these rules:

• Use SELECT only. The Service Layer rejects any other statement type.
• Make parameter placeholders match column names. Write :TransNum, not :par1 — the Service Layer resolves the parameter type through the column.
• Enclose column names in double quotes ("TransNum"). SAP HANA requires this (case-sensitive identifiers), and Microsoft SQL Server accepts it as well, so a single statement serves both database types.
• Include an ORDER BY on the cursor column. Without deterministic ordering, block-wise reading skips records.
• Compare the cursor column with >=. OneEnterprise advances the cursor accordingly, so the connector never loses a record or processes one twice.
• Don't use LIMIT or TOP. These clauses are database-specific. OneEnterprise controls the block size via the page-size request header, which works the same way on HANA and SQL Server.
• Select explicit columns rather than SELECT *. This keeps the field mapping stable when the table structure changes.

Generic statement pattern:

SELECT "<CursorColumn>", <further columns>
FROM   <Table>
WHERE  "<CursorColumn>" >= :<CursorColumn>
ORDER BY "<CursorColumn>"

Note: The Service Layer validates the statement with its own SQL parser, which supports only a subset of the SQL language. If a statement that follows all the rules above is still rejected with Invalid SQL syntax … mismatched input, move the statement into a database view. See Fallback: database views for complex SQL.

Step 3: Register the query

Performed by: consulting partner

Register the query once through the Service Layer API. Use any REST client, for example, Postman or curl.

1. Log in to obtain a session:

   POST https://<b1-server>:50000/b1s/v1/Login
   Content-Type: application/json
   
   {
     "CompanyDB": "<company database>",
     "UserName":  "<connector user>",
     "Password":  "<password>"
   }
   

   The response sets the B1SESSION cookie, which is used to authenticate subsequent calls.
2. Register the query:

   POST https://<b1-server>:50000/b1s/v1/SQLQueries
   Content-Type: application/json
   
   {
     "SqlCode": "<query code from project documentation>",
     "SqlName": "<query name from project documentation>",
     "SqlText": "<SQL statement from Step 2>"
   }
   

   The Service Layer responds with HTTP 201 Created. Inside the JSON body of SqlText, escape every double quote as \".

To update a registered statement later, send PATCH /b1s/v1/SQLQueries('<query code>') with the new SqlText.

Naming convention: OneEnterprise query codes always carry the prefix OE_ (for example OE_STOCKDELTA). This prevents collisions with queries that the customer or other add-ons register. Don't change the code — the OneEnterprise configuration references it.

Step 4: Verify the query

Performed by: consulting partner

Confirm that the query exists and executes correctly before you hand over to the OneEnterprise configuration.

1. Check the registration:

   GET https://<b1-server>:50000/b1s/v1/SQLQueries('<query code>')

2. Execute a test run with a parameter value and a small block size:

   POST https://<b1-server>:50000/b1s/v1/SQLQueries('<query code>')/List
   
   Prefer: odata.maxpagesize=20
   Content-Type: application/json
   
   { "ParamList": "<CursorColumn>=<value>" }
   

• Pass string and date values in single quotes (CreateDate='2026-07-01'); pass numeric values without quotes (TransNum=0).
• Separate multiple parameters with & inside the one ParamList string.
• The response contains the records in the value array. If more records match than the page size allows, the response also contains @odata.nextLink — this is expected; OneEnterprise reads block by block and doesn't follow the link within one run.

If both calls succeed and the test run returns plausible data, the Business One side of the setup is complete.

Step 5: Configure the OneEnterprise inbound process

Performed by: consulting partner

Configure the timer-triggered inbound method in OneEnterprise as described in the connector configuration guide. The configuration references:

• The query code you registered in Step 3,
• The cursor column and its initial value,
• The block size per timer run.

Choose the initial cursor value deliberately: a value of 0 (or an early date) performs a full initial load of all existing records; the current maximum value starts the synchronization from now on and skips history.

Worked example: available warehouse stock (OIVL + OITW)

A wholesale customer synchronizes the available stock of one warehouse from SAP Business One to their online shop. Stock changes with every delivery, goods receipt, and stock transfer — but neither the inventory transaction log (OIVL) nor the warehouse stock table (OITW) is available as a Service Layer entity.

The registered query combines both tables: OIVL identifies the items whose stock moved in the warehouse since the last processed transaction sequence, and for exactly those items, OITW supplies the current available quantity (OnHand minus IsCommitted). The transaction sequence number TransSeq Serves as the cursor: it increases with every movement, so each timer run picks up exactly the items that changed since the previous run.

Tip: The query returns absolute stock values, not movements. Processing an item twice simply rewrites the same quantity — every run is idempotent, and interrupted runs recover without special handling.

Example — Step 1: Allowlist

The IT administrator adds OIVL and OITW to b1s_sqltable.conf and restarts the Service Layer.

Example — Step 2: SQL statement

SELECT T1."ItemCode",
       (T1."OnHand" - T1."IsCommited") AS "Available",
       MAX(T0."TransSeq") AS "LastTransSeq"
FROM OIVL T0
  INNER JOIN OITW T1
    ON  T1."ItemCode" = T0."ItemCode"
    AND T1."WhsCode"  = T0."LocCode"
WHERE T0."LocCode"  = :LocCode
  AND T0."TransSeq" >= :TransSeq
GROUP BY T1."ItemCode", T1."OnHand", T1."IsCommited"
ORDER BY "LastTransSeq"

Note that both parameters are named after columns of OIVL (LocCode, TransSeq) — see Step 2, rule 2. The warehouse is a parameter rather than a literal, so the same registered query serves any warehouse.

Warning: Some Service Layer versions reject the arithmetic expression (T1."OnHand" - T1."IsCommited") in this statement with Invalid SQL syntax … mismatched input '.' expecting FROM. In that case, either select OnHand  IsCommited as separate columns and let OneEnterprise calculate the difference, or move the statement into a database view. See Fallback: database views for complex SQL.

Example — Step 3: Registration

POST https://b1srv.example.com:50000/b1s/v1/SQLQueries
Content-Type: application/json

{
  "SqlCode": "OE_STOCKDELTA",
  "SqlName": "OE Available stock delta",
  "SqlText": "SELECT T1.\"ItemCode\", (T1.\"OnHand\" - T1.\"IsCommited\") AS \"Available\", MAX(T0.\"TransSeq\") AS \"LastTransSeq\" FROM OIVL T0 INNER JOIN OITW T1 ON T1.\"ItemCode\" = T0.\"ItemCode\" AND T1.\"WhsCode\" = T0.\"LocCode\" WHERE T0.\"LocCode\" = :LocCode AND T0.\"TransSeq\" >= :TransSeq GROUP BY T1.\"ItemCode\", T1.\"OnHand\", T1.\"IsCommited\" ORDER BY \"LastTransSeq\""
}

Expected response: HTTP 201 Created.

Example — Step 4: Verification

POST https://b1srv.example.com:50000/b1s/v1/SQLQueries('OE_STOCKDELTA')/List
Prefer: odata.maxpagesize=20
Content-Type: application/json

{ "ParamList": "LocCode='CD02'&TransSeq=0" }

The response returns one row per changed item — item code, current available quantity, and the item's highest transaction sequence — ordered by LastTransSeq. OneEnterprise stores the highest LastTransSeq of the processed rows plus 1 as the new cursor, so the >= comparison never returns an unchanged item twice.

Tip: The query returns absolute stock values, not movements. Processing an item twice simply rewrites the same quantity — every run is idempotent, and interrupted runs recover without special handling.

Example — Step 5: OneEnterprise configuration

The partner configures the inbound method with query code OE_STOCKDELTA, cursor column TransSeq, the warehouse code as the LocCode parameter value, initial cursor value 0 for a full initial load, and the block size agreed for the project.

Fallback: database views for complex SQL

The Service Layer doesn't pass a registered statement to the database unchecked — it validates the statement with its own SQL parser, which supports only a subset of the SQL language (a limited set of functions; the parser rejects arithmetic expressions in the SELECT list, among other restrictions). A statement that the database itself executes without complaint can therefore fail to register or execute with an error such as:

Invalid SQL syntax:, line 1, character position 38,
mismatched input '.' expecting FROM

Try to simplify the statement first — often it's enough to select the raw columns (for example , OnHand and IsCommited) and let OneEnterprise calculate derived values. If you can't simplify the statement, move it into a database view: inside a view, the database's full SQL dialect is available, and the Service Layer only reads the view's result.

Two things change compared to a registered SQL query: you create the view in the database rather than through the Service Layer API, and query parameters (ParamList) aren't available — OneEnterprise filters the view with OData query options ($filter, $orderby) instead. Every value that was a parameter before — such as the warehouse and the cursor — must therefore be an output column of the view.

Microsoft SQL Server: SQL views

Performed by: customer IT administrator or consulting partner with database access

1. Create the view in SQL Server Management Studio. The Service Layer only recognizes views in the schema dbo whose name ends with B1SLQuery. For the worked example:

   CREATE VIEW [dbo].[OE_StockAvailableB1SLQuery] AS
   SELECT T0."LocCode",
          T1."ItemCode",
          (T1."OnHand" - T1."IsCommited") AS "Available",
          MAX(T0."TransSeq") AS "LastTransSeq"
   FROM OIVL T0
     INNER JOIN OITW T1
       ON  T1."ItemCode" = T0."ItemCode"
       AND T1."WhsCode"  = T0."LocCode"
   GROUP BY T0."LocCode", T1."ItemCode", T1."OnHand", T1."IsCommited"

2. Expose the view to the Service Layer (the Unexpose action reverses this). The Service Layer responds with HTTP 204 No Content:

   POST https://<b1-server>:50000/b1s/v1/SQLViews('OE_StockAvailableB1SLQuery')/Expose

3. Grant the connector user access: in the Business One client, open Administration > System Initialization > Authorizations > General Authorizations and assign Full Authorization for the view under Service Layer SQL View.
4. Read the view through the view service. The warehouse and the cursor move from ParamList into $filter:

   GET https://<b1-server>:50000/b1s/v2/view.svc/OE_StockAvailableB1SLQuery
       ?$filter=LocCode eq 'CD02' and LastTransSeq ge 12345
       &$orderby=LastTransSeq

Note: The result and the cursor handling are identical to the registered query: an item has moved since the last run exactly when its LastTransSeq reaches the cursor value. You control pagination through the page-size request header, as before.

SAP HANA: calculation views

On SAP HANA, you can't expose plain SQL views to the Service Layer. The equivalent is a semantic layer calculation view, which requires the SAP Business One Analytics Service:

1. Install the SAP Business One Analytics Service and initialize the company database on the Analytics home page (Company tab > Initialize)— this is a one-time step.
2. Create the calculation view in SAP HANA Studio. You can expose only calculation views whose name ends with Query (for example OE_StockAvailableQuery). All Business One tables are available.
3. Package the view and deploy the package through the SAP HANA Model Management window in the Business One client.
4. Expose the view: in SAP HANA Model Management, select Views, check Service Layer Expose, choose Update, and restart the Service Layer.
5. Grant Full Authorization for the view in the General Authorizations window.

Read the view through the semantic layer service with the same OData query options:

GET https://<b1-server>:50000/b1s/v2/sml.svc/OE_StockAvailableQuery
    ?$filter=LocCode eq 'CD02' and LastTransSeq ge 12345

Warning: The calculation view route requires considerably more setup than a SQL view on Microsoft SQL Server (Analytics Service, SAP HANA Studio, packaging, Service Layer restart). On SAP HANA installations, always check first whether you can simplify the statement so the SQLQueries parser accepts it.

Troubleshooting

Setup checklist