SBO_SP_TransactionNotification: SQL Server and HANA examples

Below is how the procedure is wired, the same rules written for SQL Server and for HANA, the object type codes you actually need, and the mistakes that show up most often in existing procedures.

How does SBO_SP_TransactionNotification work?

Every company database ships with the procedure already created and an empty “ADD YOUR CODE HERE” block. SAP B1 passes five inputs:

At the end the procedure returns one row: the error number and the message. 0 means “let it through”. Any other number blocks the transaction.

Two things follow from “inside the same transaction”. First, when your code runs, the new document is already in ORDR, OINV and the line tables, so you can query it like any committed row. Second, everything you put in the procedure adds time to every save in the company, for every object type. A slow query in the sales order branch slows down nothing else only if the branch is filtered by object_type first.

What do the object type codes mean?

object_type is the same value you see in the ObjType column of each header table. The ones that cover most rules:

For a UDO, object_type is the UDO code you registered, not a number. Field-level details for the main tables are in our SAP B1 table reference.

How do you block a sales order without a customer PO number?

A typical first rule: the sales order must carry the customer’s reference (NumAtCard, shown as “Customer Ref. No.” in the client).

SQL Server (T-SQL):

-- inside the "ADD YOUR CODE HERE" block
IF @object_type = '17' AND @transaction_type IN ('A', 'U')
BEGIN
    IF EXISTS (SELECT 1
               FROM ORDR
               WHERE DocEntry = CAST(@list_of_cols_val_tab_del AS INT)
                 AND ISNULL(NumAtCard, '') = '')
    BEGIN
        SET @error = 17001
        SET @error_message = N'Sales order: enter the Customer Ref. No.'
    END
END

SAP HANA (SQLScript):

-- declare at the top of the procedure, next to error and error_message:
-- cnt INT;

IF :object_type = '17' AND (:transaction_type = 'A' OR :transaction_type = 'U') THEN
    SELECT COUNT(*) INTO cnt
    FROM "ORDR"
    WHERE "DocEntry" = CAST(:list_of_cols_val_tab_del AS INT)
      AND IFNULL("NumAtCard", '') = '';

    IF :cnt > 0 THEN
        error := 17001;
        error_message := N'Sales order: enter the Customer Ref. No.';
    END IF;
END IF;

The HANA version is not a find-and-replace of the T-SQL one. Four differences matter:

1. Declarations go at the top. SQLScript wants cnt INT; in the declaration section before the first statement, where error and error_message are already declared. Declaring it in the middle of the body fails at compile time. If you prefer, wrap the rule in its own BEGIN ... END block with its own DECLARE.
2. No IF EXISTS (SELECT ...). Count into a variable and test the variable.
3. Read parameters with a colon (:object_type), assign with :=.
4. Quote mixed-case names. "DocEntry" and "NumAtCard" must be quoted because HANA treats unquoted names as upper case.

The other syntax swaps (ISNULL to IFNULL, GETDATE to CURRENT_DATE, + to ||) are the same ones you make in saved queries. Our SQL to HANA converter handles those for a quick first pass, and the HANA migration guide covers the full procedure rewrite.

How do you validate document lines?

Header checks are easy because the key is the header. For lines, join the line table on DocEntry. Example: block an A/R invoice that has any line priced at zero for an item that is sold, not free.

SQL Server:

IF @object_type = '13' AND @transaction_type = 'A'
BEGIN
    IF EXISTS (SELECT 1
               FROM INV1 T1
               INNER JOIN OITM T2 ON T2.ItemCode = T1.ItemCode
               WHERE T1.DocEntry = CAST(@list_of_cols_val_tab_del AS INT)
                 AND T1.Price = 0
                 AND T2.SellItem = 'Y')
    BEGIN
        SET @error = 13001
        SET @error_message = N'A/R invoice: a line has price 0. Check the price list.'
    END
END

SAP HANA:

IF :object_type = '13' AND :transaction_type = 'A' THEN
    SELECT COUNT(*) INTO cnt
    FROM "INV1" T1
    INNER JOIN "OITM" T2 ON T2."ItemCode" = T1."ItemCode"
    WHERE T1."DocEntry" = CAST(:list_of_cols_val_tab_del AS INT)
      AND T1."Price" = 0
      AND T2."SellItem" = 'Y';

    IF :cnt > 0 THEN
        error := 13001;
        error_message := N'A/R invoice: a line has price 0. Check the price list.';
    END IF;
END IF;

Put the line number in the message when you can ('Line ' + CAST(T1.LineNum + 1 AS NVARCHAR) on SQL Server, 'Line ' || TO_NVARCHAR(T1."LineNum" + 1) on HANA). LineNum starts at 0, the client shows rows from 1, and users will otherwise hunt through a 60-line invoice.

How do you make the tax ID mandatory for e-invoicing?

In ZATCA, SRI, FEL and DTE projects, a missing or malformed buyer tax ID is a common reason an invoice is rejected after it has already been posted in SAP B1. Stopping it at the business partner is cheaper than fixing a rejected invoice later.

-- SQL Server: customers must have a tax ID (LicTradNum)
IF @object_type = '2' AND @transaction_type IN ('A', 'U')
BEGIN
    IF EXISTS (SELECT 1 FROM OCRD
               WHERE CardCode = @list_of_cols_val_tab_del
                 AND CardType = 'C'
                 AND ISNULL(LicTradNum, '') = '')
    BEGIN
        SET @error = 2001
        SET @error_message = N'Customer: Federal Tax ID is required for e-invoicing.'
    END
END

Note that the business partner key is CardCode, a string, so there is no cast here. In HANA the same block uses "CardCode" = :list_of_cols_val_tab_del and the count pattern from above.

Format rules (check digits, length by ID type) are better done in an add-on or a validator before data entry, because they change per country. To find the customers that would fail today, run the business partners missing tax ID query first, clean them up, then switch the rule on. Otherwise the first person to edit an old customer record gets blocked for something they did not cause.

What are the common mistakes?

Rules that skip drafts. If an approval procedure turns a sales order into a draft, SAP B1 saves it as object type 112 in ODRF/DRF1, not as 17. Your 17 rule fires only when the draft is converted into a real document. That is often fine, but if the rule is meant to stop bad data before an approver sees it, add a 112 branch filtered on ODRF.ObjType = '17'.

Returning extra result sets. On SQL Server, a stray SELECT or PRINT used for debugging can produce result sets before the final SELECT @error, @error_message, and SAP B1 may read the wrong one. Keep debug output out of the procedure; log to a table if you must.

Slow branches. Every save in the company waits for the procedure. Filter by object_type and transaction_type in the outer IF, use the key in the WHERE clause, and avoid cursors. A rule that scans OINV without the DocEntry filter will feel fine in a test company and painful in a database with years of invoices.

Error codes nobody can trace. Use a numbering scheme, for example the object type times 1000 plus a counter (17001, 13001, 2001). When a user sends a screenshot, you know which branch fired without reading the whole procedure.

Cancel and close are separate events. C and L arrive as their own transaction types. A rule written for A and U will not stop someone from closing a sales order. If you need to block a close (for example, open deliveries still linked), handle L explicitly.

Blocking things that SAP B1 updates in the background. Some objects are updated as a side effect of another transaction (an item’s stock fields when a document is posted, a business partner’s balance). A strict rule on U for object 4 or 2 can block a sales document because of a side-effect update. Keep update rules narrow and test them by posting documents, not only by editing master data.

Losing the code on a rebuild. The procedure lives in the company database. Keep the full script in source control and compare it after upgrades, company copies and restores, so a rule never disappears quietly.

What about SBO_SP_PostTransactionNotice?

It has the same parameters, but SAP B1 calls it after the transaction is committed. It cannot block anything. Use it for side jobs: writing to a log table or putting the DocEntry in a queue for an integration (an e-invoicing sender, a WhatsApp notification, a sync to another system). Keep it light too, and never call external services from inside it. A queue table plus a separate service that reads it is the safer design.

How do add-ons and Service Layer see the error?

The rule runs at the database level, so it applies no matter where the document comes from. A DI API Add() returns a non-zero result and your message comes back through GetLastError. A Service Layer POST fails with an error that carries the message text. Data Transfer Workbench imports fail row by row. This is why the procedure is a good place for rules that must hold everywhere, and why your messages should make sense to someone reading an integration log, not only to a user in the client. For the DI API side of error handling, see our DI API error codes guide.

Need rules like these written and tested on both SQL Server and HANA? That is part of our SAP Business One development work.

Sources

• SAP Community: Implementing SBO_SP_TransactionNotification of SAP Business One, version for SAP HANA (2013)
• SAP Help Portal: SAP Business One

Related services

• SAP Business One development