The instructions listed below are relevant only in case you set up the SAP Business One company database manually (i.e. without using the Cloud Apps Installer add-on).
Update the SBO_SP_PostTransactionNotice procedure by adding the following code below the -- ADD YOUR CODE HERE line:
----START: Boyum Solutions BOY_CLOUD (Boyum Cloud) event notifications
IF @error = 0
BEGIN
-- ******************************************************************
-- Procedure version. Increase this every time the procedure changes. (Both MSSQL and SAP HANA)
DECLARE @procedureVersion int
SET @procedureVersion = 15
-- ******************************************************************
DECLARE @trimmedObjectType nvarchar(40)
SET @trimmedObjectType = RIGHT(RTRIM(@object_type),case when LEN(@object_type) < 4 then 0 else LEN(@object_type) - 4 end);
IF ((SELECT COUNT(*) FROM [@BOY_CLOUD_NOTICE]) <= 20000 AND ISNULL((SELECT TOP 1 Code FROM [@BOY_CLOUD_NOTI_TYPE] WHERE Code = @object_type), '') <> '')
BEGIN
DECLARE @identifier uniqueidentifier
DECLARE @whsItemCode nvarchar(50)
SET @identifier = NEWID()
IF (@object_type = '10000062')
BEGIN
--Handle inventory transaction
SELECT TOP 1 @whsItemCode = T0.ItemCode FROM [OIVL] T0 WHERE T0.TransSeq = @list_of_cols_val_tab_del
IF (NOT(@whsItemCode IS NULL))
BEGIN
INSERT INTO [@BOY_CLOUD_WAREHOUSE] (Code, Name, U_SEQUENCE, U_ITEMCODE, U_ORDER) VALUES
(
@identifier,
@identifier,
@list_of_cols_val_tab_del,
@whsItemCode,
(SELECT ISNULL(MAX(U_ORDER), 0) + 1 FROM [@BOY_CLOUD_WAREHOUSE])
);
END
END
ELSE IF (@object_type = '1470000005')
BEGIN
--Handle stock transfers between bin locations within the same warehouse
DECLARE @whsAbsEntry int
SET @whsAbsEntry = CAST( @list_of_cols_val_tab_del AS INT );
SELECT @whsItemCode = OILM."ItemCode" FROM OBTL JOIN OILM ON OILM."MessageID" = OBTL."MessageID" WHERE OBTL."AbsEntry" = @whsAbsEntry AND OILM."ActionType" IN (19, 20);
IF (NOT(@whsItemCode IS NULL))
BEGIN
INSERT INTO [@BOY_CLOUD_WAREHOUSE] (Code, Name, U_SEQUENCE, U_ITEMCODE, U_ORDER) VALUES
(
@identifier,
@identifier,
@list_of_cols_val_tab_del,
@whsItemCode,
(SELECT ISNULL(MAX(U_ORDER), 0) + 1 FROM [@BOY_CLOUD_WAREHOUSE])
);
END
END
ELSE IF (@object_type = '10000045' OR @object_type = '10000044')
BEGIN
DECLARE @itemCode nvarchar(50)
DECLARE @absEntry int
DECLARE @boyumObjectType nvarchar(50)
IF (@transaction_type = 'U')
BEGIN
--Only capture update events for serial & batch number master data, together with item code
SET @absEntry = CAST( @list_of_cols_val_tab_del AS INT );
IF (@object_type = '10000045')
BEGIN
SET @boyumObjectType = 'BOY_SERIAL_NUMBER_UPDATE';
SELECT @itemCode = "ItemCode" FROM OSRN WHERE OSRN."AbsEntry" = @absEntry;
--If empty serial numbers are filled in later (on release only)
IF (NOT(@itemCode IS NULL))
BEGIN
INSERT INTO [@BOY_CLOUD_WAREHOUSE] (Code, Name, U_SEQUENCE, U_ITEMCODE, U_ORDER) VALUES
(
@identifier,
@identifier,
@list_of_cols_val_tab_del,
@itemCode,
(SELECT ISNULL(MAX(U_ORDER), 0) + 1 FROM [@BOY_CLOUD_WAREHOUSE])
);
END
END
ELSE IF (@object_type = '10000044')
BEGIN
SET @boyumObjectType = 'BOY_BATCH_NUMBER_UPDATE';
SELECT @itemCode = "ItemCode" FROM OBTN WHERE OBTN."AbsEntry" = @absEntry;
END
IF (NOT(@boyumObjectType IS NULL))
BEGIN
INSERT INTO "@BOY_CLOUD_NOTICE" ("Code", "Name", "U_OBJECTTYPE", "U_TRANSTYPE", "U_NUMOFCOLS", "U_LISTOFVAL", "U_LISTOFKEY", "U_DATE", "U_ORDER") VALUES
(
@identifier,
@identifier,
@boyumObjectType,
@transaction_type,
2,
@list_of_cols_val_tab_del + CHAR(9) + @itemCode,
@list_of_key_cols_tab_del + CHAR(9) + 'ItemCode',
(SELECT GETDATE()),
(SELECT ISNULL(MAX(U_ORDER), 0) + 1 FROM [@BOY_CLOUD_NOTICE])
);
END
END
ELSE IF (@transaction_type = 'A')
BEGIN
--If batch or serial numbers are created later (on release only)
SET @absEntry = CAST( @list_of_cols_val_tab_del AS INT );
IF (@object_type = '10000045')
BEGIN
SET @boyumObjectType = 'BOY_SERIAL_NUMBER_UPDATE';
SELECT @itemCode = "ItemCode" FROM OSRN WHERE OSRN."AbsEntry" = @absEntry;
END
ELSE IF (@object_type = '10000044')
BEGIN
SET @boyumObjectType = 'BOY_BATCH_NUMBER_UPDATE';
SELECT @itemCode = "ItemCode" FROM OBTN WHERE OBTN."AbsEntry" = @absEntry;
END
IF (NOT(@boyumObjectType IS NULL) AND NOT(@itemCode IS NULL))
BEGIN
INSERT INTO [@BOY_CLOUD_WAREHOUSE] (Code, Name, U_SEQUENCE, U_ITEMCODE, U_ORDER) VALUES
(
@identifier,
@identifier,
@list_of_cols_val_tab_del,
@itemCode,
(SELECT ISNULL(MAX(U_ORDER), 0) + 1 FROM [@BOY_CLOUD_WAREHOUSE])
);
END
END
END
ELSE
BEGIN
--Handle normal event
INSERT INTO [@BOY_CLOUD_NOTICE] (Code, Name, U_OBJECTTYPE, U_TRANSTYPE, U_NUMOFCOLS, U_LISTOFVAL, U_LISTOFKEY, U_DATE, U_ORDER) VALUES
(
@identifier,
@identifier,
@object_type,
@transaction_type,
@num_of_cols_in_key,
@list_of_cols_val_tab_del,
@list_of_key_cols_tab_del,
(SELECT GETDATE()),
(SELECT ISNULL(MAX(U_ORDER), 0) + 1 FROM [@BOY_CLOUD_NOTICE])
)
END
END
--For testing this stored procedure's version through the service layer
--For long object types we only get the first part because SBO_SP_TransactionNotification has a max length for object type.
IF (CHARINDEX('BOY_CLOUD_NOTI_T', @trimmedObjectType) > 0 AND @list_of_cols_val_tab_del = 'BOYCLOUD_TEST_STOREDPROCEDURE' AND (@transaction_type = 'A' OR @transaction_type = 'U'))
BEGIN
DECLARE @existingConfigLineCount int;
SET @existingConfigLineCount = (SELECT COUNT(1) FROM "@BOY_CLOUD_CONFIG" WHERE Code = 'SP_PTN')
IF (@existingConfigLineCount > 0)
BEGIN
UPDATE [@BOY_CLOUD_CONFIG] SET U_PRODVERSION = @procedureVersion WHERE Code = 'SP_PTN'
END
ELSE
BEGIN
INSERT INTO [@BOY_CLOUD_CONFIG] (Code, Name, U_PRODVERSION) VALUES ('SP_PTN', 'SP_PTN', @procedureVersion)
END
DELETE FROM [@BOY_CLOUD_NOTI_TYPE] WHERE Code = 'BOYCLOUD_TEST_STOREDPROCEDURE'
END
--Write that this is an MSSQL system that we are running on
--For long object types we only get the first part because SBO_SP_TransactionNotification has a max length for object type.
IF (CHARINDEX('BOY_CLOUD_NOTI_T', @trimmedObjectType) > 0 AND @transaction_type = 'A')
BEGIN
IF ((SELECT COUNT(1) FROM "@BOY_CLOUD_CONFIG" WHERE Code = 'SP_MSSQL') = 0)
BEGIN
INSERT INTO [@BOY_CLOUD_CONFIG] (Code, Name, U_PRODVERSION) VALUES ('SP_MSSQL', 'SP_MSSQL', 0)
END
END
--Execute ad-hoc SQL queries for objects that are not exposed through the service layer (e.g. stock details, CTNS content, etc.)
IF (@trimmedObjectType = 'BOY_CLOUD_SQLREQ' AND @transaction_type = 'A')
BEGIN
DECLARE @sqlRun nvarchar (4000);
BEGIN TRY
SET @sqlRun = (SELECT U_SQLQUERY FROM [@BOY_CLOUD_SQLREQ] WHERE Code = @list_of_cols_val_tab_del);
IF(@sqlRun <> '')
BEGIN
EXECUTE sp_executesql @sqlRun;
END
END TRY
BEGIN CATCH
DECLARE @guid nvarchar(200)
SET @guid = NEWID()
DECLARE @timestampstr nvarchar(20)
SET @timestampstr = (SELECT FORMAT(GETDATE(), 'yyyyMMddHHmmss'))
INSERT INTO [@BOY_CLOUD_SQLRES] (Code, Name, U_TIMESTAMP, U_SQLQUERYID, U_ERRORCODE, U_ERRORMESSAGE)
VALUES (@guid, @guid, @timestampstr, @list_of_cols_val_tab_del, (SELECT ERROR_NUMBER()), (SELECT ERROR_MESSAGE()));
END CATCH
END
--Clean up ad-hoc SQL queries whose results have already been read
IF (@trimmedObjectType = 'BOY_CLOUD_SQLREQ' AND @transaction_type = 'U')
BEGIN
DECLARE @isProcessed nvarchar(1);
SET @isProcessed = (SELECT U_ISPROC FROM [@BOY_CLOUD_SQLREQ] WHERE Code = @list_of_cols_val_tab_del)
IF (@isProcessed = 'Y')
BEGIN
DELETE FROM [@BOY_CLOUD_SQLRES] WHERE U_SQLQUERYID = @list_of_cols_val_tab_del;
DELETE FROM [@BOY_CLOUD_SQLREQ] WHERE Code = @list_of_cols_val_tab_del;
END
END
END
----END: Boyum Solutions BOY_CLOUD (Boyum Cloud) event notifications
Comments
0 comments
Please sign in to leave a comment.