SAP Business One Data Quality: Detect Duplicates and Inconsistencies with SQL

This SAP Business One data quality tutorial starts with a familiar scene. The collections meeting stopped when someone asked why the largest customer of the year appeared three times in the report. Three different codes, three partial balances, three credit limits that added up to triple the exposure the committee had approved six months earlier. Nobody did it on purpose. A salesperson created the code in 2021 under the trade name, administration generated another one when a purchase order arrived with the full legal name, and a third came in from a spreadsheet during the migration that nobody had time to review because the project was already two months late and go-live had to happen on Monday.

That is the trap with SAP Business One data quality: it does not warn you. There is no red screen, no error log, no block. The system runs exactly what you asked for and returns a flawless report built on dirty records. What follows are the four queries you would use this very afternoon to find the duplicates and the empty fields before they reach a board presentation.

SAP Business One data quality: why duplicates drain margin

The most quoted Gartner research on this (2020) put the average annual cost of poor data quality at $12.9 million per organization. That figure is for large corporations, of course, and your company is probably not there. The damage mechanism, however, is identical at any scale. Master data deduplication projects typically find between 5% and 15% redundant records, as the specialist vendors in this field consistently report.

In SAP Business One that percentage translates into very concrete things. A customer split across three codes breaks credit control, because each record carries its own limit and its own balance. It also distorts profitability analysis: the customer you think is mid-sized is actually one of your three largest. It ruins commercial segmentation, inflates the active customer count and forces your team to review every report by hand before showing it. So the cost does not show up as a line in the income statement. It shows up as hours of expensive people reconciling spreadsheets.

SAP Business One data quality in items: the duplicate brings stock with it

With items the effect is worse, because the duplicate carries stock with it. Two codes for the same product mean two inventory balances, two average costs, two reorder points. As a result, MRP plans badly, the buyer over-purchases and the warehouse keeper discovers the truth when there is no room left on the rack. That last part shows up in no report at all.

Prerequisites before auditing your database

Before running any of the queries that follow, make sure you have the following:

• SAP Business One 9.3 or higher, on Microsoft SQL Server or SAP HANA.
• Query Manager authorizationen Administration > System Initialization > Authorizations, the branch General > User Queries must be set to Full Authorization.
• Read-only access to the company database, if you prefer to work from SQL Server Management Studio or HANA Studio.
• A test environment with a recent copy of production. The queries in this tutorial only read, but the corrections that follow do not.
• Basic knowledge of the tables OCRD (business partners), OITM (items), ITM1 (prices by list) and OINM (inventory journal).

Save each query in the Query Manager under its own category, for example Master data audit. That way they stay available to any authorized user without depending on you being logged in.

Step by step: your first master data audit

The full procedure takes under an hour the first time. After that it is fifteen minutes a month.

1. Open the Query Manager. Menu Tools > Queries > Query Manager, or the shortcut key Ctrl+Shift+Q.
2. Create a new category. Press Manage Categories, add Master data audit and confirm.
3. Press New to open the query generator and paste in the first query from the next section.
4. Validate the syntax with the button Execute. If the query returns rows, you already have your first finding.
5. Save the query with an explicit name: QA01 – Duplicate business partners by name. The numeric prefixes keep the alphabetical order aligned with the execution order.
6. Repeat with the three remaining queries and export each result to Excel with the green sheet icon.
7. Build your cleanup plan prioritizing by balance, by stock and by number of associated documents, in that order.

A warning about the Query Manager validator

A warning about step 4, learned the hard way: in release 9.3, if memory serves, the Query Manager validator rejected any query that did not start with the word SELECT. The first two queries in this tutorial use WITH to build a common table expression. If your installation rejects them, replace the CTE with a subquery in the FROM and it works just the same. It is a minor detail that can cost you twenty minutes of confusion.

Query 1: business partners duplicated by normalized name

The first sweep looks for partners whose name, once stripped of spaces, periods, commas, hyphens and ampersands, comes out identical. That is how the classic cases surface: COMERCIAL ANDES LTDA next to Comercial Andes Ltda. and COMERCIAL-ANDES LTDA. The normalization is deliberately aggressive, because at this stage you would rather review ten false positives than miss one real duplicate.

Dónde ejecutar este query. Úsalo en SQL Server Management Studio o en HANA Studio. El Generador de consultas de SAP Business One no siempre digiere las expresiones de tabla común (WITH), así que si necesitas el resultado dentro de B1, guarda la consulta como vista y llama a la vista desde el Generador.

SQL Server

-- QA01: Potentially duplicated business partners by name
WITH BP_Norm AS (
    SELECT
        T0.CardCode,
        T0.CardName,
        T0.CardType,
        T0.LicTradNum,
        T0.Balance,
        T0.validFor,
        UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            LTRIM(RTRIM(T0.CardName)),
            ' ', ''), '.', ''), ',', ''), '-', ''), '&', '')) AS ClaveNombre
    FROM OCRD T0
)
SELECT
    B.ClaveNombre                                   AS NameKey,
    COUNT(*)                                        AS Occurrences,
    SUM(B.Balance)                                  AS TotalBalance,
    SUM(CASE WHEN B.validFor = 'Y' THEN 1 ELSE 0 END) AS Active,
    STUFF((SELECT ', ' + X.CardCode
             FROM BP_Norm X
            WHERE X.ClaveNombre = B.ClaveNombre
            FOR XML PATH('')), 1, 2, '')             AS Codes
FROM BP_Norm B
GROUP BY B.ClaveNombre
HAVING COUNT(*) > 1
ORDER BY SUM(B.Balance) DESC, COUNT(*) DESC;

SAP HANA

-- QA01: Socios de negocio potencialmente duplicados por nombre
SELECT
    B."ClaveNombre"                                 AS "Clave",
    COUNT(*)                                        AS "Repeticiones",
    SUM(B."Balance")                                AS "SaldoAcumulado",
    SUM(CASE WHEN B."validFor" = 'Y' THEN 1 ELSE 0 END) AS "Activos",
    STRING_AGG(B."CardCode", ', ')                  AS "Codigos"
FROM (
    SELECT
        T0."CardCode",
        T0."CardName",
        T0."CardType",
        T0."LicTradNum",
        T0."Balance",
        T0."validFor",
        UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            TRIM(T0."CardName"),
            ' ', ''), '.', ''), ',', ''), '-', ''), '&', '')) AS "ClaveNombre"
    FROM OCRD T0
) BP_NORM B
GROUP BY B."ClaveNombre"
HAVING COUNT(*) > 1
ORDER BY SUM(B."Balance") DESC, COUNT(*) DESC;

Sort by accumulated balance and not by number of repetitions. The reason is practical: a duplicate with a zero balance you can deactivate tomorrow, while one carrying twenty million in receivables needs coordination with collections before you touch anything. The Active column tells you how many of those codes are still enabled, which is the real work list.

Query 2: duplicates by tax ID, the most reliable signal

Names get typed wrong, tax IDs do not. That makes the tax identifier the best duplicate detector you have, provided people actually entered it. One important clarification here: the same identifier on a customer record and on a vendor record is perfectly legitimate, because you buy from and sell to the same company. The real problem is two records of the same type with the same number, which is why the query also groups by CardType.

SQL Server

-- QA02: Exact duplicates by tax ID
SELECT
    T0.LicTradNum                                   AS TaxId,
    CASE T0.CardType WHEN 'C' THEN 'Customer'
                     WHEN 'S' THEN 'Vendor'
                     ELSE 'Lead' END                AS Type,
    COUNT(*)                                        AS Records,
    SUM(T0.Balance)                                 AS TotalBalance,
    MIN(T0.CardCode)                                AS LowestCode,
    MAX(T0.CardCode)                                AS HighestCode,
    MAX(T0.CardName)                                AS ReferenceName
FROM OCRD T0
WHERE ISNULL(T0.LicTradNum, '') <> ''
GROUP BY T0.LicTradNum, T0.CardType
HAVING COUNT(*) > 1
ORDER BY SUM(T0.Balance) DESC;

SAP HANA

-- QA02: Exact duplicates by tax ID
SELECT
    T0."LicTradNum"                                 AS "TaxID",
    CASE T0."CardType" WHEN 'C' THEN 'Customer'
                       WHEN 'S' THEN 'Vendor'
                       ELSE 'Lead' END              AS "Type",
    COUNT(*)                                        AS "Records",
    SUM(T0."Balance")                               AS "TotalBalance",
    STRING_AGG(T0."CardCode", ' | ')                AS "Codes",
    MAX(T0."CardName")                              AS "ReferenceName"
FROM OCRD T0
WHERE IFNULL(T0."LicTradNum", '') <> ''
GROUP BY T0."LicTradNum", T0."CardType"
HAVING COUNT(*) > 1
ORDER BY SUM(T0."Balance") DESC;

Here comes the gripe I carry about this part of the product: SAP Business One lets you save two business partners with the same tax ID without so much as a warning. The field License Trade Number has no unique index and no native validation. In practice that means the control depends entirely on you, and further on we will see how to really close it.

Query 3: duplicate items and the stock they drag along

Duplicate items need more context than business partners, because to decide which code survives you have to see stock, recent activity and status. This query delivers all three in the same grid.

SQL Server

-- QA03: Duplicate items by normalized description
WITH Art AS (
    SELECT
        T0.ItemCode,
        T0.ItemName,
        T0.ItmsGrpCod,
        T0.OnHand,
        T0.validFor,
        UPPER(REPLACE(REPLACE(REPLACE(
            LTRIM(RTRIM(T0.ItemName)),
            ' ', ''), '-', ''), '.', ''))           AS NameKey
    FROM OITM T0
    WHERE T0.InvntItem = 'Y'
)
SELECT
    A.NameKey                                      AS ItemKey,
    A.ItemCode                                      AS Code,
    A.ItemName                                      AS Description,
    A.OnHand                                        AS CurrentStock,
    A.validFor                                      AS Active,
    D.Occurrences,
    (SELECT MAX(L.DocDate) FROM OINM L
      WHERE L.ItemCode = A.ItemCode)                AS LastMovement
FROM Art A
INNER JOIN (
    SELECT NameKey, COUNT(*) AS Occurrences
      FROM Art
     GROUP BY NameKey
    HAVING COUNT(*) > 1
) D ON D.NameKey = A.NameKey
ORDER BY A.NameKey, A.OnHand DESC;

SAP HANA

-- QA03: Duplicate items by normalized description
WITH ART AS (
    SELECT
        T0."ItemCode",
        T0."ItemName",
        T0."ItmsGrpCod",
        T0."OnHand",
        T0."validFor",
        UPPER(REPLACE(REPLACE(REPLACE(
            TRIM(T0."ItemName"),
            ' ', ''), '-', ''), '.', ''))           AS "NameKey"
    FROM OITM T0
    WHERE T0."InvntItem" = 'Y'
)
SELECT
    A."NameKey"                                    AS "ItemKey",
    A."ItemCode"                                    AS "Code",
    A."ItemName"                                    AS "Description",
    A."OnHand"                                      AS "CurrentStock",
    A."validFor"                                    AS "Active",
    D."Occurrences",
    (SELECT MAX(L."DocDate") FROM OINM L
      WHERE L."ItemCode" = A."ItemCode")            AS "LastMovement"
FROM ART A
INNER JOIN (
    SELECT "NameKey", COUNT(*) AS "Occurrences"
      FROM ART
     GROUP BY "NameKey"
    HAVING COUNT(*) > 1
) D ON D."NameKey" = A."NameKey"
ORDER BY A."NameKey", A."OnHand" DESC;

The rule I apply to pick the survivor is simple and orderly: the code with the most recent movement wins; if they tie, the one with stock wins; if they tie again, the one linked to more price lists wins. The loser is deactivated, never deleted. We will come back to that point in the best practices, because that is where almost everyone gets it wrong.

Query 4: the SAP Business One data quality traffic light

The three previous queries are for working. This one is for reporting. It returns one row per finding type with its count, so you can paste it into a monthly email or mount it on a cockpit indicator with no extra work. It is, in practice, the minimum health dashboard for your master data.

SQL Server

-- QA04: Master data completeness traffic light
SELECT 'Active business partners without tax ID' AS Finding, COUNT(*) AS Records
  FROM OCRD
 WHERE validFor = 'Y' AND ISNULL(LicTradNum, '') = ''
UNION ALL
SELECT 'Active business partners without payment terms', COUNT(*)
  FROM OCRD
 WHERE validFor = 'Y' AND ISNULL(GroupNum, -1) = -1
UNION ALL
SELECT 'Active business partners without email', COUNT(*)
  FROM OCRD
 WHERE validFor = 'Y' AND CardType IN ('C','S') AND ISNULL(E_Mail, '') = ''
UNION ALL
SELECT 'Inventory items without unit of measure', COUNT(*)
  FROM OITM
 WHERE InvntItem = 'Y' AND validFor = 'Y' AND ISNULL(InvntryUom, '') = ''
UNION ALL
SELECT 'Active items without price in the base list', COUNT(*)
  FROM OITM T0
 INNER JOIN ITM1 T1 ON T1.ItemCode = T0.ItemCode AND T1.PriceList = 1
 WHERE T0.validFor = 'Y' AND ISNULL(T1.Price, 0) = 0
UNION ALL
SELECT 'Duplicate barcodes', COUNT(*)
  FROM (SELECT CodeBars
          FROM OITM
         WHERE ISNULL(CodeBars, '') <> ''
         GROUP BY CodeBars
        HAVING COUNT(*) > 1) D;

SAP HANA

-- QA04: Master data completeness traffic light
SELECT 'Active business partners without tax ID' AS "Finding", COUNT(*) AS "Records"
  FROM OCRD
 WHERE "validFor" = 'Y' AND IFNULL("LicTradNum", '') = ''
UNION ALL
SELECT 'Active business partners without payment terms', COUNT(*)
  FROM OCRD
 WHERE "validFor" = 'Y' AND IFNULL("GroupNum", -1) = -1
UNION ALL
SELECT 'Active business partners without email', COUNT(*)
  FROM OCRD
 WHERE "validFor" = 'Y' AND "CardType" IN ('C','S') AND IFNULL("E_Mail", '') = ''
UNION ALL
SELECT 'Inventory items without unit of measure', COUNT(*)
  FROM OITM
 WHERE "InvntItem" = 'Y' AND "validFor" = 'Y' AND IFNULL("InvntryUom", '') = ''
UNION ALL
SELECT 'Active items without price in the base list', COUNT(*)
  FROM OITM T0
 INNER JOIN ITM1 T1 ON T1."ItemCode" = T0."ItemCode" AND T1."PriceList" = 1
 WHERE T0."validFor" = 'Y' AND IFNULL(T1."Price", 0) = 0
UNION ALL
SELECT 'Duplicate barcodes', COUNT(*)
  FROM (SELECT "CodeBars"
          FROM OITM
         WHERE IFNULL("CodeBars", '') <> ''
         GROUP BY "CodeBars"
        HAVING COUNT(*) > 1) D;

Run this query on the first business day of every month and save the result in a spreadsheet with the date. After six months you will have something far more valuable than a snapshot: a trend. If the numbers go up, the problem is not in the data but in the process that creates it, and that is where you have to step in.

Variations and use cases

The four base queries allow extensions that are worth it depending on your reality:

• Duplicates by phone or email. Change CardName by Phone1 o E_Mail in Query 1. It is particularly effective at companies with many individual customers.
• Phonetic matching. In SQL Server you can use SOUNDEX(CardName) to catch misspelled variants. HANA does not offer that function natively, so there the path is to compare the first eight normalized characters with LEFT().
• Automatic alert. Set up Query 2 as a scheduled alert in Management > Alerts so you get a notice when a repeated tax ID shows up.
• Preventive formatted search. Link a query to the tax ID field on the business partner form, so the user sees on screen whether the number already exists before saving.
• Unused items. Add a filter by LastMovement on Query 3 to separate the live duplicates from the ones that only take up space in the list.

Best practices and common mistakes in SAP Business One data quality

Never run a direct UPDATE or DELETE on the database. Do not do it. SAP withdraws support for any database modified outside the application or the Service Layer, and the inconsistency you create will show up months later somewhere you will never connect to this. Every correction goes through the interface, through DTW or through the Service Layer.

Deactivate, do not delete. SAP Business One prevents deleting a business partner or an item with associated documents, which in this case is a sensible restriction. The correct procedure is to flag the record as inactive on the relevant tab and prefix its description with ZZ_ That way the duplicate drops to the bottom of any alphabetical search and stops showing up among the first options for the user in a hurry.

Careful with over-normalizing. This one has cost me dearly. Stripping that many characters can merge two related but distinct companies, such as a parent and its transport subsidiary, and if someone merges without reviewing you end up with the accounting of two legal entities under a single code. Always review the list before acting on it.

Prevention: close the front door

Cleaning without preventing is wasted work. It leaves you paying for the same effort every six months, with the same people and the same result. The three controls that really work are formatted searches that validate at creation time, mandatory fields defined in the form settings, and a validation in SP_TransactionNotification that blocks creating a business partner with an existing tax ID.

Assign an owner. As long as anyone can create a business partner, someone will create it wrong. Restrict master data creation to two or three trained people and let the rest request the record. It is less convenient and it works much better.

Official SAP Resources

• SAP Business One SDK 10.0: OCRD table reference, with the detail of every field in the business partner master.
• SAP Help Portal: SAP Business One documentation, for authorization and form settings.
• SAP Community, where specific questions about queries and tables usually get resolved.

Conclusion: SAP Business One data quality as a monthly routine

The four queries in this tutorial do not solve the problem on their own. They make it visible, which is the step almost nobody takes. A dirty master data set survives precisely because it is never measured, and the moment someone has to explain to a committee why there are one hundred and twenty duplicate items, the data governance conversation stops being a chore you can postpone.

Start this week with Query 2. It is the fastest to run, the one that produces the fewest false positives and probably the one that will give you the most useful scare of the month.