This procedure is needed whenever you import a database. The reason is that the sequences are reinitialized by the import process, so the next insert would try to insert key 1 again, leading to primary key issues.

In these cases the procedure below needs to be created, then executed, in order to rebuild the Produmex sequences from the actual data found in the tables.

Note: in most recent Produmex versions, the procedure is automatically created by the Produmex database upgrade process. In this case, you only need to execute the already existing procedure.

CREATE PROCEDURE "PMX_SP_RebuildSequences" ()
LANGUAGE SQLSCRIPT AS
	v_seqName NVARCHAR(256);
	v_resetByQuery NCLOB;
	v_restartWith BIGINT;
BEGIN
	DECLARE CURSOR cur FOR
		SELECT SEQUENCE_NAME, RESET_BY_QUERY FROM sys.sequences WHERE SCHEMA_NAME = current_schema AND SEQUENCE_NAME LIKE 'PMX_%' AND LENGTH(RESET_BY_QUERY) > 0;
	OPEN cur;
	FETCH cur INTO v_seqName, v_resetByQuery;
	WHILE v_seqName IS NOT NULL DO
		DELETE FROM TMP_TN_CharListTable;
		EXEC 'INSERT INTO TMP_TN_CharListTable ("idx") ' || v_resetByQuery;
		SELECT TOP 1 "idx" INTO v_restartWith FROM TMP_TN_CharListTable;
 
		EXEC 'ALTER SEQUENCE "' || current_schema || '"."' || v_seqName || '" RESTART WITH ' || v_restartWith || ' RESET BY ' || v_resetByQuery;
 
		FETCH cur INTO v_seqName, v_resetByQuery;
	END WHILE;
END;

After having created it, or if it was already existing, you have to execute the procedure the following way:

CALL "PMX_SP_RebuildSequences" ()

It can happen that a procedure needs to be recompiled after some changes. When that happens, you'll see an error message like the following:

Could not execute 'CREATE PROCEDURE SBO_SP_TransactionNotification ( in object_type nvarchar(20), – SBO Object Type …' in 14 ms 515 µs . SAP DBTech JDBC: [430] (at 961): invalidated procedure: PMX_SP_TransactionNotification: line 32 col 7 (at pos 961)

In this case, you have to recompile the invalidated procedure first. This can be done as follows:

ALTER PROCEDURE "<yourSchema>"."PMX_SP_TransactionNotification" RECOMPILE

Sometimes it can also happen that the SBO_SP_TRANSACTIONNOTIFICATION procedure needs to be recompiled.

When this situation occurs, it's impossible to book transactions or modify master data in SAP B1: strange red error messages come everywhere.

In this case, you have to recompile the stored procedure, which can be done as follows:

ALTER PROCEDURE "<yourSchema>"."SBO_SP_TRANSACTIONNOTIFICATION" RECOMPILE

If this doesn't help, you can also try to re-create completely the stored procedure, with the DROP PROCEDURE and CREATE PROCEDURE commands.

For Crystal reports, you can use the “ODBC (RDO)” connection type, with the following connection string:

DRIVER={HDBODBC32};SERVERNODE=yourserver:30015;DATABASE=yourdatabase