Have you ever discovered that a customer's credit limit changed with no explanation, or that an already posted invoice had a different price than you remembered? That is the moment you understand why the SAP Business One change log is one of the most underrated tools in the system. Every change is recorded; yet very few companies know how to query it. In this tutorial you will learn, step by step, how to audit who changed what, when and from which user, with SQL queries ready to run.
Why you need to master the SAP Business One change log
An ERP without traceability is a black box. So when a critical piece of data changes (a price, a credit limit, a payment term) and nobody owns up to it, the problem stops being technical and becomes an internal control problem.
There are also very concrete reasons to master this feature. First, external audits demand evidence of who modified master data and documents. Second, internal fraud almost always leaves a trace in the historical records. As a result, knowing how to read the change log makes you the person who answers with data, not with guesses.
For example, imagine a salesperson raising the credit limit of a delinquent customer just to be able to invoice them. Without traceability, that change goes unnoticed until the debt is uncollectible. With the queries in this tutorial, you catch it in minutes.
Prerequisites
Before getting started, make sure you have the following:
- Access to the Query Manager of SAP Business One (Tools > Queries > Query Manager).
- Read permissions on the system tables. You do not need write access: every query here is read-only.
- Know your database platform: SQL Server or SAP HANA. Accordingly, every query in this tutorial includes both versions.
- SAP Business One 9.3 or higher (the history tables exist in earlier versions, but the examples are validated on 9.3 and 10.0).
Step 1: Understand how the change log works in the interface
SAP Business One automatically records the change history of documents and master data. To see it from the interface, open any document or record (an A/R invoice, for example) and go to the menu Tools > Change Log.
There you will see every version of the record: the instance, the user who updated it and the date. Also, if you select two versions and click Show Differences, the system highlights exactly which fields changed between one version and the next.
That view has one important limitation, though: it works record by record. So if you need to audit changes in bulk (every document modified last month, or every customer whose credit limit went up) you need SQL. Here is how.
Step 2: Get to know the history tables (A tables)
Here is the technical secret of this tutorial. For every relevant master or transactional table, SAP Business One keeps a historical mirror table whose name replaces the leading letter O with the letter A:
- ADOC: marketing document history (invoices, orders, deliveries). It is the historical mirror of OINV, ORDR, ODLN and the rest, unified by the ObjType field.
- AITM: item master data history (mirror of OITM).
- ACRD: business partner history (mirror of OCRD).
- AUSR: history of changes to system users.
Three fields are key in all of these tables. First, LogInstanc identifies the version number of the record: version 1 is the oldest. Second, UserSign2 indicates the user who made the last update to that version (join it with OUSR to get the name). Third, UpdateDate records the date of the change.
This way, each row in an A table is a snapshot of the record before its next change. As a result, comparing two consecutive rows shows you exactly what changed.
Step 3: Audit document changes with ADOC
This first query lists every version of the A/R invoices (ObjType 13) modified in the last 30 days, with the user responsible for each version.
SQL Server
-- History of modified A/R invoices (last 30 days)
SELECT T0.DocNum,
T0.LogInstanc AS Version,
T1.U_NAME AS UserName,
T0.UpdateDate AS ChangeDate,
T0.DocStatus,
T0.DocTotal
FROM ADOC T0
INNER JOIN OUSR T1 ON T0.UserSign2 = T1.USERID
WHERE T0.ObjType = '13'
AND T0.UpdateDate >= DATEADD(DAY, -30, GETDATE())
ORDER BY T0.DocNum, T0.LogInstanc
SAP HANA
-- History of modified A/R invoices (last 30 days)
SELECT T0."DocNum",
T0."LogInstanc" AS "Version",
T1."U_NAME" AS "UserName",
T0."UpdateDate" AS "ChangeDate",
T0."DocStatus",
T0."DocTotal"
FROM "ADOC" T0
INNER JOIN "OUSR" T1 ON T0."UserSign2" = T1."USERID"
WHERE T0."ObjType" = '13'
AND T0."UpdateDate" >= ADD_DAYS(CURRENT_DATE, -30)
ORDER BY T0."DocNum", T0."LogInstanc"
As a result, you get one line per saved version of the document. If an invoice shows five instances, it was modified four times after it was created. You can, for example, change the ObjType to audit sales orders (17), deliveries (15) or A/P invoices (18).
Step 4: Track item changes with AITM
Item master data is especially sensitive: a change of name, unit of measure or status can alter entire processes. This query therefore shows the full history of a specific item.
SQL Server
-- Version history of an item
SELECT T0.ItemCode,
T0.LogInstanc AS Version,
T1.U_NAME AS UserName,
T0.UpdateDate AS ChangeDate,
T0.ItemName,
T0.validFor AS Active,
T0.AvgPrice AS AvgCost
FROM AITM T0
INNER JOIN OUSR T1 ON T0.UserSign2 = T1.USERID
WHERE T0.ItemCode = '[%0]'
ORDER BY T0.LogInstanc
SAP HANA
-- Version history of an item
SELECT T0."ItemCode",
T0."LogInstanc" AS "Version",
T1."U_NAME" AS "UserName",
T0."UpdateDate" AS "ChangeDate",
T0."ItemName",
T0."validFor" AS "Active",
T0."AvgPrice" AS "AvgCost"
FROM "AITM" T0
INNER JOIN "OUSR" T1 ON T0."UserSign2" = T1."USERID"
WHERE T0."ItemCode" = '[%0]'
ORDER BY T0."LogInstanc"
The [%0] parameter lets you run it from the Query Manager prompting for the item code. That way any authorized user can check the history without touching the SQL.
Step 5: Detect credit limit changes with ACRD
This is the most powerful query in the tutorial. It does not just list versions: it compares each version with the next one and shows only the business partners whose credit limit changed, with the old value and the new one.
SQL Server
-- Credit limit changes: previous value vs new value
SELECT T0.CardCode,
T0.CardName,
T0.CreditLine AS PreviousLimit,
T2.CreditLine AS NewLimit,
T1.U_NAME AS UserName,
T2.UpdateDate AS ChangeDate
FROM ACRD T0
INNER JOIN ACRD T2 ON T0.CardCode = T2.CardCode
AND T2.LogInstanc = T0.LogInstanc + 1
INNER JOIN OUSR T1 ON T2.UserSign2 = T1.USERID
WHERE ISNULL(T0.CreditLine, 0) <> ISNULL(T2.CreditLine, 0)
ORDER BY T2.UpdateDate DESC
SAP HANA
-- Credit limit changes: previous value vs new value
SELECT T0."CardCode",
T0."CardName",
T0."CreditLine" AS "PreviousLimit",
T2."CreditLine" AS "NewLimit",
T1."U_NAME" AS "UserName",
T2."UpdateDate" AS "ChangeDate"
FROM "ACRD" T0
INNER JOIN "ACRD" T2 ON T0."CardCode" = T2."CardCode"
AND T2."LogInstanc" = T0."LogInstanc" + 1
INNER JOIN "OUSR" T1 ON T2."UserSign2" = T1."USERID"
WHERE IFNULL(T0."CreditLine", 0) <> IFNULL(T2."CreditLine", 0)
ORDER BY T2."UpdateDate" DESC
Note the self-jointechnique: we join the history table with itself, shifting the instance by +1. As a result, every row in the output is a real transition from one value to another. You can also apply exactly the same pattern to any sensitive field: payment terms (GroupNum), price list (ListNum) or assigned salesperson (SlpCode).
Step 6: Ranking of users with the most changes
Finally, this query summarizes change activity by user and document type over the last 30 days. It works well as a monthly internal control report.
SQL Server
-- Top 20 users by number of changes
SELECT TOP 20
T1.U_NAME AS UserName,
T0.ObjType AS DocumentType,
COUNT(*) AS TotalChanges
FROM ADOC T0
INNER JOIN OUSR T1 ON T0.UserSign2 = T1.USERID
WHERE T0.UpdateDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY T1.U_NAME, T0.ObjType
ORDER BY COUNT(*) DESC
SAP HANA
-- Top 20 users by number of changes
SELECT T1."U_NAME" AS "UserName",
T0."ObjType" AS "DocumentType",
COUNT(*) AS "TotalChanges"
FROM "ADOC" T0
INNER JOIN "OUSR" T1 ON T0."UserSign2" = T1."USERID"
WHERE T0."UpdateDate" >= ADD_DAYS(CURRENT_DATE, -30)
GROUP BY T1."U_NAME", T0."ObjType"
ORDER BY COUNT(*) DESC
LIMIT 20
An abnormal volume of changes concentrated in one user and one document type always deserves a second look. It is not about distrusting people; it is about letting the data tell the whole story.
Variations and use cases
The pattern you just learned extends to almost the entire system. These are variations I use often in production environments:
- Price auditing: apply the self-join to AIT1 (price history by price list) to detect unauthorized price changes.
- User changes: query AUSR to find out who modified permissions, branches or other users' data.
- Canceled documents: filter ADOC by DocStatus to reconstruct the history of documents that were canceled after being posted.
- Automatic alerts: combine the credit limit query with a scheduled SAP Business One alert. That way management gets a notification the same day the change happens.
Best practices and common mistakes
First, remember that the A table stores the previous version of the record, not the current one. The live record lives in the O table; the history, in the A table. Confusing this is the most common mistake when interpreting results.
Second, do not use UserSign when you want to know who made a change: that field points to the original creator. For the author of the update, the right field is UserSign2.
Also, keep performance in mind. History tables grow without limit in databases with years of operation. So always filter by date or by a specific record, and avoid running open queries against ADOC during business hours.
Finally, a governance point: access to these queries must be controlled too. Audit information is sensitive; publish it only for internal control, finance or management roles.
Official SAP resources
To go deeper, check these resources:
- Official SAP Business One documentation on the SAP Help Portal
- SAP Community: the SAP Business One forum
- SAP Note on system tables and database structure, available on the SAP Support Portal with your S-user.
Conclusion: turn traceability into real control
The SAP Business One change log is already recording everything in your company, whether you use it or not. The difference between an organization with solid internal control and one that finds out about problems too late is not the tool: it is knowing how to ask the data.
With the four queries in this tutorial you can audit documents, items, business partners and activity by user. This is only the starting point, though. The next step is to fold these queries into automatic alerts and monthly control reports.
If this tutorial was useful to you, share it with your finance or audit team. And in the Circulo Estrategico I will keep publishing practical tutorials like this one, with code ready to run in your SAP Business One. See you in the next one.
