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. 'CMP_TN'

Now, in order to add a check you have only to create a
correctly named stored prorcedure, e.g.:
CREATE PROCEDURE "CMP_TN_BP_PreventLongNames"
(	IN  objType  NVARCHAR (20),
	IN  objKey   NVARCHAR (255),
	IN  action   NVARCHAR (1)
)
AS  error     NVARCHAR(200);
	cardName  NVARCHAR(64);
	maxLen    INT;
BEGIN SEQUENTIAL EXECUTION
	maxLen := 5;
	
	WHILE 1 = 1 DO	
		IF :objType <> '2' THEN BREAK; END IF;
		
		IF :action <> 'A' AND :action <> 'U' THEN BREAK; END IF;
			
		SELECT "CardName" INTO cardName FROM OCRD
		WHERE "CardCode" = :objKey;
		
		IF LENGTH( :cardName ) <= :maxLen THEN BREAK; END IF;
		
		error := N'Business partner name shall not be longer than ' ||
		           CAST(:maxLen AS VARCHAR(4)) || ' characters.';

		-- Report error code and error text:
		CALL GT_DISPATCH_ERROR_SET( 1, :error );
	END WHILE;
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
faciliates 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. (CMP_TN_BP_PreventLongNames).

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

  (-1) CMP_TN_BP_PreventLongNames: line 13: Divide by zero
  error encountered.
To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Anton Shepelev Post author

    When publishing, the moderators changed the formatting, making the post unreadable. I have now changed it back. Please, let me know if anything is wrong so I can fix it myself.

     

    (0) 

Leave a Reply