SAP Business One Import

Data extraction from SAP Business One and import into Bilendo: keys, clearing, orders, and delta runs.

This page describes how data is extracted from SAP Business One, flattened, and handed over to Bilendo. It applies to both methods (pull and push; see the SAP Business One Hub page). The fields themselves are listed in the SAP Business One architecture.

How the data is extracted

Each SAP Business One company is its own database (SAP HANA or Microsoft SQL Server) and is extracted separately. The tables are already relational, so the preferred method is a read-only SQL query against this database for each category (customers, open items, cleared items, and optionally open, undelivered orders). Without database access, the Service Layer (OData: BusinessPartners, JournalEntries, Invoices, Orders) provides the same facts with paging and $filter; the DI API is the fallback for on-premise add-ons. The field names are the B1 table fields; the Service Layer simply spells them differently.

How the data is flattened

Each category is delivered as ONE flat table (CSV or SQL result), with one row per key, column header = field name, and no nested structures. The open amount and clearing date are calculated on the SAP side and delivered as ready-made columns; Bilendo does not recalculate them.

SAP Business One (one company database)
|
+- OCRD + CRD1 + OCPR -------------------------> 1) Customers, one row per CardCode
|     CardType = 'C', billing address, contact
|     Key: CompanyCode + CardCode, once per file
|
+- JDT1 (customer lines only, via OCRD)
|     Key: CompanyCode + TransId + Line_ID, never changes
|     + OJDT          Header text, creation date
|     + OINV / ORIN   Invoice number (TransType 13 / 14)
|     + ITR1 + OITR   Reconciliation (clearing)
|     |
|     |  OpenAmount   = BalDueDeb - BalDueCred
|     |  ClearingDate = latest ReconDate, only if OpenAmount = 0
|     |
|     +-- OpenAmount != 0 ---------------------> 2) Open items, key TransId + Line_ID
|     |
|     +-- OpenAmount = 0 and reconciled -------> 3) Cleared items, same template
|
+- ORDR + RDR1 (optional) ---------------------> 4) Open, undelivered orders, one row per order
      Key: CompanyCode + CardCode + DocNum
      each delivery = ALL open orders, no delta

1) to 4)  ->  flat CSV / SQL result  ->  Bilendo import

Customers (master data): one row per OCRD.CardCode with CardType = 'C' and frozenFor <> 'Y', plus the constant column CompanyCode. Join CRD1 via CardCode with AdresType = 'B' and Address = OCRD.BillToDef (the default billing address; if none exists, the first billing address), and OCPR via CardCode and Name = OCRD.CntctPrsn (the default contact person). Use LEFT JOINs so that a customer without an address or contact stays in the list, and condense each join to ONE row.
Open items (transaction data): one row per posting line in JDT1 (key CompanyCode + TransId + Line_ID). An INNER JOIN on OCRD via ShortName = CardCode keeps only customer lines and omits G/L account lines. LEFT JOIN OJDT via TransId for the header text and creation date, and OINV (or ORIN) via CreatedBy = DocEntry AND TransType 13 (invoice) or 14 (credit memo) for the invoice number, payment terms, cancellation flag, and branch. Clearing: LEFT JOIN ITR1 via TransId and TransRowId = Line_ID, then OITR via ReconNum (only Canceled = 'N'); a line with multiple reconciliations has multiple entries there and is condensed to one (the latest ReconDate, along with its ReconNum). Both are delivered only where the open amount is 0 (see Cleared items).
Amounts: document amount = Debit - Credit. The open amount is delivered pre-calculated as the column OpenAmount = BalDueDeb - BalDueCred (local currency, receivable positive); it is 0 as soon as the line is cleared. The foreign currency is in FCCurrency (empty = the company's local currency). Credit memos and incoming payments come with the opposite sign and stay that way.
Orders (optional): the open, undelivered orders, one row per order at header level; the open amount is summed up from the RDR1 lines (see Open, undelivered orders).

Keys and duplicates

Bilendo builds its keys from the delivered columns. A duplicate in the receivables sub-ledger leads to wrong balances, so the following rules are strict. Within ONE batch (one delivery object), Bilendo recognizes a repeated key as a duplicate and does not accept it. Across separate deliveries, the newer one overwrites the values of the older one under the same key (customers and postings; orders work differently, see below).

Customer key = CompanyCode + CardCode. In one batch it may appear only ONCE. CompanyCode is a constant column (the company, as a code agreed with the customer and never changed), because SAP Business One has no such column: every company is its own database.
Posting key = CompanyCode + TransId + Line_ID. It never changes across deliveries, not even when the item is cleared: an invoice of 1,000 EUR keeps the same key for as long as it exists, and every change to it is delivered again WITH THE SAME key, so that the newer values replace the older ones. Within one batch the key is unique; open and cleared items of one batch never share a key.
Order key = CompanyCode + CardCode + DocNum. Unlike customers and postings, an order is not updated under its key: see Open, undelivered orders.
Cleared = same key: when an item is fully cleared (OpenAmount = 0), it is delivered again with the SAME key, now with OpenAmount = 0 and a ClearingDate. That is an update of the existing posting, never a new posting.
Order of deliveries: separate deliveries are applied as they arrive, and the newer one overwrites the older one. Send them in chronological order; an older extract sent after a newer one would put the old values back.
Join traps: DocEntry is unique only per document type, so OINV / ORIN are joined on DocEntry AND TransType. Every one-to-many join (ITR1, CRD1, OCPR, RDR1) is reduced to one row before delivery. The extraction checks before each batch that the number of rows equals the number of distinct keys.
Cancellations: a cancelled document creates a reversal posting with its own TransId, i.e. a new key; the original posting keeps its key.

Cleared items

Cleared items use the SAME template as the open items: the same fields, the same flat table, and the same key (CompanyCode + TransId + Line_ID); only the selection differs, so they are delivered as a second extract (or in the same file, as long as the key is unique). A cleared item is the later state of an open item: it arrives with the key it already has.

Which lines: the customer lines of JDT1 (same joins as the open items) with OpenAmount = 0 (BalDueDeb = 0 and BalDueCred = 0) that carry a reconciliation (ITR1 / OITR with Canceled = 'N'). Debit and Credit keep the original amount, so a cleared item still shows what was booked; only the open amount is 0.
How a line comes to be cleared: in B1, a reconciliation (OITR.ReconNum) ties debit and credit lines of one customer together, e.g. an invoice (TransType 13) and the incoming payment (24) or credit memo (14) that settles it. Both sides carry the same ReconNum and ReconDate; BOTH sides belong in the extract, which is how Bilendo links an invoice with its payment.
Clearing fields: ClearingDate (the latest OITR.ReconDate of the line) and ReconNum are filled ONLY where OpenAmount = 0. A partly cleared line stays an open item with a balance above 0 and has NO clearing date; for everything else both stay empty.
Window: the first run takes the cleared lines of the agreed period by ClearingDate (usually the last 24 months); later runs take only the lines cleared since the last run.

Open, undelivered orders

Orders (optional) are a snapshot, not a stream of changes. Bilendo builds the order key from CompanyCode + CardCode + DocNum, and nothing is overwritten: with every delivery, Bilendo archives all order records it received before and creates the records of the new delivery as new ones.

Which orders: only open, undelivered orders: ORDR.DocStatus = 'O', not cancelled, with at least one RDR1 line with LineStatus = 'O' and OpenQty > 0. A partly delivered order stays in, with the open amount of the remainder.
Always complete: every delivery carries ALL open, undelivered orders of the company, never only the changed ones. An order missing from a delivery is archived by it, so a partial extract would archive orders that are still open.
Delivered or closed = gone: an order that is fully delivered, closed, or cancelled is simply no longer in the next delivery and is archived there. No status change and no delete row are needed.
One row per order: header level; the open amount is summed up from the lines (OpenOrderAmount in local currency, OpenOrderAmountFC in document currency). The key is unique within one batch, as in every category.
Order of deliveries: the newest delivery counts. Send in chronological order; an older snapshot sent after a newer one would archive the newer orders and bring the old ones back.

Delta runs

Customers (master data): The first run delivers all customers. Later runs deliver new customers (OCRD.CreateDate) and changed customers (OCRD.UpdateDate), looking back one day, each under the same key (CompanyCode + CardCode), so that the newer values replace the older ones. A change to the billing address or contact person belongs to the customer: confirm on the customer's instance that such a change moves OCRD.UpdateDate; if it doesn't, every run delivers all customers, which the key rules make safe. Recommended: send a run's customers before its postings, so that a posting never points to a customer that Bilendo doesn't yet know.

Postings (transaction data): New postings are found via OJDT.CreateDate (re-read one day back); a change to an existing posting (OJDT.UpdateDate) arrives again with the same key. An open amount also changes when a line is reconciled, without a new posting: re-read every line with an open balance, plus the lines reconciled since the last run (OITR.ReconDate). A canceled reconciliation (Canceled = 'Y') sets the line back to open: it returns under the same key when open items are re-read, with OpenAmount above 0 and no reconciliation date, and drops out of the reconciled items.

Orders: no delta. Every run delivers the complete snapshot of open, undelivered orders.

Please verify on your system
The table and field names follow the SAP Business One standard schema. Customized installations (user-defined fields, add-ons, a different B1 version) may differ: before the first extraction, have your SAP partner confirm joins and field names on your own instance. The look-back period for reconciled items is agreed upon for each customer. Dunning level and dunning block have no standard field in B1 and therefore are not part of this page; if you keep them in a user-defined field, we will include it as a custom field. Two cases would change a key and must be clarified with the customer before the first extraction: manual postings (TransType 30) can be changed afterward in SAP Business One, and if lines are inserted or removed, Line_ID can shift; and a renamed business partner code is a new customer key.