3

I'm wondering if anyone knows how SAP B1 (SAP Business One) generates the unique primary keys it uses in various tables. Examples of what I am talking about would include OCRD.DocEntry and OCPR.CntctCode. These integer columns that get "automatically" incremented.

Typical approaches for doing this include identity columns (e.g., SQL Server), sequences (e.g., Oracle), or manual sequence tables holding a Nextval which is programmatically incremented. As best I can tell, B1 is not using any of these techniques for these columns. So how is it handling them?

The particular instance I'm looking at is using an SQL Server database.

Yes, I'm well aware of the fact that there is no "need" for me to know know about the inner workings, shouldn't be mucking around in the DB, etc. It's just bothering me that I don't know how they are doing it! If anyone can explain, I'd be grateful.

Boghyon Hoffmann
  • 17,103
  • 12
  • 72
  • 170
Rob3C
  • 456
  • 7
  • 16

2 Answers2

4

SAPB1 generates new unique numbers using the ONNM table. When a document is added the following takes place.

  • SQL Transaction begins
  • The next number is queried from the ONNM table with an update lock
  • The ONNM table is updated with the new number (+1).
  • The document is added
  • The SQL transaction is committed.

Running an SQL SELECT statement with an update lock returns the current row while simultaneously locking that row until the end of the transaction. You are guaranteed that no other user can change that row between when you select it and when the transaction ends.

You can use SQL Profiler to watch the statements executed when you perform actions in SAP B1. Here is the line that gets the next number to use in a Quotation. Quotations are ObjectType 23.

SELECT T0.* FROM [dbo].[ONNM] T0 WITH (UPDLOCK) WHERE T0.[ObjectCode] = '23'

Jared Kells
  • 6,771
  • 4
  • 38
  • 43
2

SAP B1 use ONNM for sequence number generation.

it maintains an auto key for every object that was registered in it. and based on the auto key sequence number will be generated. for every add event this auto key will be incremented by +1

Rajmohan
  • 46
  • 2