Send realtime data from SAP B1 to AllSync

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
Back to blog