Dispatcher of transaction notifications for B1

-------------------- Project has moved --------------------

The version described in this post is obsolete. The lastest
version is available at Gitlab:

      https://gitlab.com/gazintech/b1-trandispatcher

---------------------- The old post -----------------------

I have seen several installations of Business One where the
standard SBO_SP_TransactionNotification procedure has grown
into an unmanageable mass of spaghetti code. Sometimes
implementers amend the situation by wrapping individual
checks into separate stored procedures, but this approach
has a serious drawback: in order to add such a check, one
has to make changes in two places:

  a. add a new stored procedure, and
  b. modify SBO_SP_TransactionNotification to invoke it.

which leaves SBO_SP_TransactionNotification cluttered with
many similar calls:

CALL Check_1( ... , errCode, errText );
CALL Check_2( ... , errCode, errText );
/*  ...  */
CALL Check_N( ... , errCode, errText );

SELECT :error, :errText FROM DUMMY;

Notice that this is also inefficient because if some check
fails the execusion of the rest will be redundant. It is
clumsy enough to solve in T-SQL, but clumsier still in
SQLSript:

/* Using WHILE-BREAK to emulate GOTO: */
WHILE ( 1 = 1 ) DO
   CALL Check_1( ... , errCode, errText );
   IF :errCode <> 0 THEN BREAK; END IF;

   CALL Check_2( ... , errCode, errText );
   IF :errCode <> 0 THEN BREAK; END IF;

   /*  ...  */

   CALL Check_N( ... , errCode, errText );
   IF :errCode <> 0 THEN BREAK; END IF;
END WHILE;

SELECT :errCode, :errText FROM DUMMY;

In order to keep SBO_SP_TransactionNotification clean and to
simlify the maintenance of transaction notifications, I have
written a transaction-notification dispatcher -- both for MS
SQL and HANA, which I offer to everybody at no cost and
without any warranty, on the terms of the ICS license.

The dispatcher allows one to remove spaghetti code from the
standard SBO_SP_TransactionNotification procedure by
implementing each check or handler in a separate, easily
manageable stored procedure with a single responsibility.
These individual procedures need not be called explicitly,
because the dispatcher will find and invoke them
automatically. The dispatcher does not break compatibility
and will work alongside existing checks. In order to
install it onto a company database,

  1. run dispatcher.sql to create the dispatching procedure
     and

  2. add its invocation to SBO_SP_TransactionNotification,
     as shown in install.sql, supplying a prefix of the
     checking procedures, e.g. 'GT_TN'.

Now, in order to add a check you have only to create a
correctly named stored prorcedure, e.g.:

CREATE PROCEDURE "GT_TN_BP_PreventLongNames"
(  IN  objType  NVARCHAR ( 20),
   IN  objKey   NVARCHAR (255),
   IN  action   NVARCHAR (  1),
   OUT error    NVARCHAR (200)
)
AS cardName  NVARCHAR(64);
   maxLen    INT;
BEGIN SEQUENTIAL EXECUTION
   maxLen := 5;
   IF :objType <> '2' THEN RETURN; END IF;

   IF :action <> 'A' AND :action <> 'U' THEN RETURN; END IF;

   SELECT "CardName" INTO cardName FROM OCRD
   WHERE "CardCode" = :objKey;

   IF LENGTH( :cardName ) <= :maxLen THEN RETURN; END IF;

   error := N'Business partner name shall not be longer than ' ||
            CAST(:maxLen AS VARCHAR(4)) || ' characters.';
END;

The dispatcher will automatically invoke it when necessary
It will terminate at the first check that fails to save CPU
time. Note that the name should start with the chosen
prefix followed by an underscore. The common prefix
facilitates the viewing of the checks in an alphabetical
list such as IDE tools (HANA Studio and SQL Management
Studio) provide.

When a check blocks an action, the dispatcher prints the
name of the blocking procedure after the error message,
e.g.:

  (1) Business partner name shall not be longer than 5
  characters. [GT_TN_BP_PreventLongNames].

Unhandled exceptions are reported in a similar manner so
that one always knows where to look:

 (304) division by zero undefined: search table error:
 "SBODEMORU"."DO": line 4 col 2 (at pos 73): [304]
 (range 3) division by zero undefined exception: division
 by zero undefi... [GT_TN_BP_PreventLongNames]

In the message above, the phrase "division by zero
undefined" is repeated three times. It is shame that HANA
error message should be so confusing and repetitive. The
text is truncated because the of the 200-charachter limit
for the error message parameter in the standard B1 procedure
SBO_SP_TransactionNotification.