SAP Business One supports the "Transaction Notification" to capture realtime data once it is successful posted to database.
We can use MS SQL to call AllSync API, this is a trick.
--Enable Ole Automation Procedures in SQL: sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO
-
Create generate store procedure to call AllSync API
Get AllSync Webhook URL from Integration Flow setting.
SBO_SP_TransactionNotification:
if @object_type=4 --product begin declare @json nvarchar(4000) set @json=( select ItemCode product_id, ItemName description from OITM where ItemCode=@list_of_cols_val_tab_del FOR JSON PATH, WITHOUT_ARRAY_WRAPPER ) exec _allsync_generateQueue @json,'product', @error out, @error_message out set @error_message=@json end
create proc [dbo].[_allsync_generateQueue] @body nvarchar(4000), @table nvarchar(100), @error int out, @error_message nvarchar(200) out as declare @serviceUrl nvarchar(500) set @serviceUrl = 'https://api.allsync.io/QueueIncoming/Hook?tenantID=1&applicationID=SAP&type=product&token=1' Declare @Object as Int; Declare @ResponseText as Varchar(8000); Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'post',@serviceUrl, 'false' Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json' Exec sp_OAMethod @Object, 'send', null, @body Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT Exec sp_OADestroy @Object if isnull((select value from OPENJSON(@ResponseText) where [key] = 'success'),'')='false' begin set @error=-1 set @error_message=isnull((select value from OPENJSON(@ResponseText) where [key] = 'message'),'error from allsync store') end select @error, @error_message