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 THEN
BEGIN
DECLARE existingCount int;
DECLARE dateMarker date;
DECLARE identifier nvarchar (50);
DECLARE collect nvarchar (255);
DECLARE orderCount int;
DECLARE whsItemCode nvarchar (255);
DECLARE whsTransactionId int;
DECLARE procedureVersion int;
DECLARE recordCount int;
-- ******************************************************************
-- Procedure version. Increase this every time the procedure changes. (Both MSSQL and SAP HANA)
procedureVersion := 15;
-- ******************************************************************
SELECT CURRENT_DATE INTO dateMarker FROM DUMMY;
SELECT COUNT(*) INTO existingCount FROM "@BOY_CLOUD_NOTICE";
SELECT SYSUUID INTO identifier FROM DUMMY;
SELECT TOP 1 "Code" INTO collect FROM (SELECT "Code" FROM "@BOY_CLOUD_NOTI_TYPE" WHERE "Code" = :object_type UNION SELECT '' FROM DUMMY);
IF (existingCount <= 20000 AND collect <> '')
THEN
IF (object_type = '10000062')
THEN
--Handle inventory transaction
whsTransactionId := CAST( list_of_cols_val_tab_del AS INT );
SELECT COUNT(1) INTO recordCount FROM "OIVL" T0 WHERE T0."TransSeq" = :whsTransactionId;
IF (recordCount > 0)
THEN
SELECT TOP 1 T0."ItemCode" INTO whsItemCode FROM "OIVL" T0 WHERE T0."TransSeq" = :whsTransactionId;
SELECT IFNULL("ORDER", 0) + 1 INTO orderCount FROM (SELECT MAX("U_ORDER") AS "ORDER" FROM "@BOY_CLOUD_WAREHOUSE");
IF (NOT(whsItemCode IS NULL))
THEN
INSERT INTO "@BOY_CLOUD_WAREHOUSE" ("Code", "Name", "U_SEQUENCE", "U_ITEMCODE", "U_ORDER") VALUES
(
:identifier,
:identifier,
:whsTransactionId,
:whsItemCode,
:orderCount
);
END IF;
END IF;
ELSEIF (object_type = '1470000005')
THEN
--Handle stock transfers between bin locations within the same warehouse
DECLARE boyumObjectType nvarchar(50);
whsTransactionId := CAST( list_of_cols_val_tab_del AS INT );
SELECT COUNT(1) INTO recordCount FROM OBTL JOIN OILM ON OILM."MessageID" = OBTL."MessageID" WHERE OBTL."AbsEntry" = :whsTransactionId AND OILM."ActionType" IN (19, 20);
IF (recordCount > 0)
THEN
SELECT OILM."ItemCode" INTO whsItemCode FROM OBTL JOIN OILM ON OILM."MessageID" = OBTL."MessageID" WHERE OBTL."AbsEntry" = :whsTransactionId AND OILM."ActionType" IN (19, 20);
SELECT IFNULL("ORDER", 0) + 1 INTO orderCount FROM (SELECT MAX("U_ORDER") AS "ORDER" FROM "@BOY_CLOUD_WAREHOUSE");
IF (NOT(whsItemCode IS NULL))
THEN
INSERT INTO "@BOY_CLOUD_WAREHOUSE" ("Code", "Name", "U_SEQUENCE", "U_ITEMCODE", "U_ORDER") VALUES
(
:identifier,
:identifier,
:whsTransactionId,
:whsItemCode,
:orderCount
);
END IF;
END IF;
ELSEIF (object_type = '10000045' OR object_type = '10000044')
THEN
IF (transaction_type = 'U')
THEN
--Only capture update events for serial & batch number master data, together with item code
DECLARE itemCode nvarchar(50);
DECLARE absEntry int;
DECLARE boyumObjectType nvarchar(50);
absEntry := CAST( list_of_cols_val_tab_del AS INT );
IF (object_type = '10000045')
THEN
boyumObjectType := 'BOY_SERIAL_NUMBER_UPDATE';
SELECT "ItemCode" INTO itemCode FROM OSRN WHERE OSRN."AbsEntry" = :absEntry;
--If empty serial numbers are filled in later (on release only)
SELECT IFNULL("ORDER", 0) + 1 INTO orderCount FROM (SELECT MAX("U_ORDER") AS "ORDER" FROM "@BOY_CLOUD_WAREHOUSE");
IF (NOT(itemCode IS NULL))
THEN
INSERT INTO "@BOY_CLOUD_WAREHOUSE" ("Code", "Name", "U_SEQUENCE", "U_ITEMCODE", "U_ORDER") VALUES
(
:identifier,
:identifier,
:list_of_cols_val_tab_del,
:itemCode,
:orderCount
);
END IF;
ELSEIF (object_type = '10000044')
THEN
boyumObjectType := 'BOY_BATCH_NUMBER_UPDATE';
SELECT "ItemCode" INTO itemCode FROM OBTN WHERE OBTN."AbsEntry" = :absEntry;
END IF;
IF (NOT(boyumObjectType IS NULL))
THEN
SELECT IFNULL("ORDER", 0) + 1 INTO orderCount FROM (SELECT MAX("U_ORDER") AS "ORDER" FROM "@BOY_CLOUD_NOTICE");
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',
:dateMarker,
:orderCount
);
END IF;
ELSEIF (transaction_type = 'A')
THEN
--If batch or serial numbers are created later (on release only)
DECLARE itemCode nvarchar(50);
DECLARE absEntry int;
DECLARE boyumObjectType nvarchar(50);
absEntry := CAST( list_of_cols_val_tab_del AS INT );
IF (object_type = '10000045')
THEN
boyumObjectType := 'BOY_SERIAL_NUMBER_UPDATE';
SELECT "ItemCode" INTO itemCode FROM OSRN WHERE OSRN."AbsEntry" = :absEntry;
ELSEIF (object_type = '10000044')
THEN
boyumObjectType := 'BOY_BATCH_NUMBER_UPDATE';
SELECT "ItemCode" INTO itemCode FROM OBTN WHERE OBTN."AbsEntry" = :absEntry;
END IF;
IF (NOT(boyumObjectType IS NULL) AND NOT(itemCode IS NULL))
THEN
SELECT IFNULL("ORDER", 0) + 1 INTO orderCount FROM (SELECT MAX("U_ORDER") AS "ORDER" FROM "@BOY_CLOUD_WAREHOUSE");
INSERT INTO "@BOY_CLOUD_WAREHOUSE" ("Code", "Name", "U_SEQUENCE", "U_ITEMCODE", "U_ORDER") VALUES
(
:identifier,
:identifier,
:list_of_cols_val_tab_del,
:itemCode,
:orderCount
);
END IF;
END IF;
ELSE
--Handle normal event
SELECT IFNULL("ORDER", 0) + 1 INTO orderCount FROM (SELECT MAX("U_ORDER") AS "ORDER" FROM "@BOY_CLOUD_NOTICE");
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,
:dateMarker,
:orderCount
);
END IF;
END IF;
--For testing this stored procedure's version through the service layer
IF (substring(object_type,4) = '@BOY_CLOUD_NOTI_TYPE' AND list_of_cols_val_tab_del = 'BOYCLOUD_TEST_STOREDPROCEDURE' AND (transaction_type = 'A' OR transaction_type = 'U'))
THEN
DECLARE existingConfigLineCount int;
SELECT COUNT(1) INTO existingConfigLineCount FROM "@BOY_CLOUD_CONFIG" WHERE "Code" = 'SP_PTN';
IF (existingConfigLineCount > 0)
THEN
UPDATE "@BOY_CLOUD_CONFIG" SET "U_PRODVERSION" = :procedureVersion WHERE "Code" = 'SP_PTN';
ELSE
INSERT INTO "@BOY_CLOUD_CONFIG" ("Code", "Name", "U_PRODVERSION") VALUES ('SP_PTN', 'SP_PTN', :procedureVersion);
END IF;
DELETE FROM "@BOY_CLOUD_NOTI_TYPE" WHERE "Code" = 'BOYCLOUD_TEST_STOREDPROCEDURE';
END IF;
--Execute ad-hoc SQL queries for objects that are not exposed through the service layer (e.g. stock details, CTNS content, etc.)
BEGIN
IF (substring(object_type,4) = '@BOY_CLOUD_SQLREQ' AND transaction_type = 'A')
THEN
DECLARE sqlRun nvarchar (4000);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
declare guid nvarchar(50);
declare timestampstr nvarchar(20);
SELECT SYSUUID INTO guid FROM DUMMY;
SELECT TO_NVARCHAR(current_timestamp, 'YYYYMMDDHH24MISS') INTO timestampstr FROM DUMMY;
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, ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE);
END;
SELECT TOP 1 "U_SQLQUERY" INTO sqlRun FROM (SELECT "U_SQLQUERY" FROM "@BOY_CLOUD_SQLREQ" WHERE "Code" = :list_of_cols_val_tab_del);
IF(sqlRun <> '')
THEN
EXECUTE IMMEDIATE (' ' || sqlRun || ' ');
END IF;
END IF;
END;
--Clean up ad-hoc SQL queries whose results have already been read
BEGIN
IF (substring(object_type,4) = '@BOY_CLOUD_SQLREQ' AND transaction_type = 'U')
THEN
DECLARE isProcessed nvarchar(1);
SELECT "U_ISPROC" INTO isProcessed FROM "@BOY_CLOUD_SQLREQ" WHERE "Code" = :list_of_cols_val_tab_del;
IF (isProcessed = 'Y')
THEN
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 IF;
END IF;
END;
END;
END IF;
----END: Boyum Solutions BOY_CLOUD (Boyum Cloud) event notifications
Comments
0 comments
Please sign in to leave a comment.