• Download DLL executable from https://portal.twbs.com to the SQL Server server. The DLL executable is also automatically placed in C:\Program Files\Bizweaver\BWPortal by the Bizweaver installation.

sp_configure 'show advanced options', 1 
GO  
RECONFIGURE 
GO  
sp_configure 'clr enabled', 1
GO  
RECONFIGURE 
GO

• The 'Executable file“ path in the following SQL statement must reflect the actual location of the SQLWSCall.dll file on your system.

-- Create asymmetric key
CREATE ASYMMETRIC KEY [bwws]
AUTHORIZATION [dbo]
FROM EXECUTABLE FILE = 'C:\Program Files\Bizweaver\BWPortal\BWSQLSP\SQLWSCall.dll'
-- Create login
CREATE LOGIN TWBS
FROM ASYMMETRIC KEY [bwws]
-- Modify login
GRANT UNSAFE ASSEMBLY TO TWBS

• Expand the database information for the target (SAP or VersagoData) database.
• Expand the Programmability section.
• Right-click on Assemblies; select New Assembly.
• Browse in the “Path to Assembly” field to find the SQLWSCall.dll file referenced in the “Initialization” section above, and select it.
• Change the Permission Set (just above the assembly path field) to Unrestricted from the drop-down list.
• Click [OK] to add the assembly.

This procedure handles the web service call to Bizweaver. It is used for both POST and GET transactions.

CREATE PROCEDURE [dbo].[spCallBizweaverWS]
@wsMethod NVARCHAR (MAX), @wsUrl NVARCHAR (MAX), @wsPort NVARCHAR (MAX), @wsContent NVARCHAR (MAX), @wsAuthToken NVARCHAR (MAX), @wsTaskId INT, @wsStartAfter INT, @wsParameters NVARCHAR (MAX)
AS EXTERNAL NAME [SQLWSCall].[StoredProcedures].[spCallBizweaverWS]

Contact Third Wave support for assistance with this configuration

A basic database trigger to initiate Bizweaver processing for payments will look like this.

payments_trigger_sql.txt

CREATE TRIGGER [dbo].[twbs_tr_VersagoPayments]
ON [dbo].[Versago_Payments]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--
DECLARE
@vgo_BatchID INT,
@object INT,
@url VARCHAR(5000),
@bw_url nvarchar(100),
@bw_WorkflowID INT,
@bw_port INT,
@pArgVariableName nvarchar(50)
--
/* It may be necessary to change all instances of "vgoCommon" to a different name if you have used a different name */
SELECT
t0.BW_URL, t0.BW_Port, t0.BW_WorkflowID
FROM vgoCommon..twbs_vgo_payment_InfoMapping t0
--
SET @pArgVariableName = 'vgo_BatchID'
SET @vgo_batchid = (SELECT vgo_batchid FROM inserted)
SET @bw_url = (SELECT t0.bw_url FROM vgoCommon..twbs_vgo_payment_InfoMapping t0)
SET @bw_port = (SELECT t0.bw_port FROM vgoCommon..twbs_vgo_payment_InfoMapping t0)
SET @bw_Workflowid = (SELECT t0.BW_WorkflowID FROM vgoCommon..twbs_vgo_payment_InfoMapping t0) 
--
SET @url = @bw_url + '/BWService/api/workflow/InvokeWorkFlow?pTaskID=' + CAST(@bw_WorkflowID AS VARCHAR) + '&pStartAfter=0&pArguments={' + @pArgVariableName + ':' + CAST(@vgo_BatchID AS VARCHAR) + '}'
EXEC sp_OACreate 'MSXML2.XMLHTTP', @object OUT;
EXEC sp_oamethod @object, 'open', NULL, 'get', @url
EXEC sp_oamethod @object, 'send'SELECT @url
--select @responsetext as [Response]
--select @url
EXEC sp_oadestroy @object
END

The following video illustrates this process.

.

Contact Third Wave support for assistance with this configuration

Create a trigger on the table where records are saved by your Versago multi-select report.

Values in <YourValue> are specific to your installation and need to be changed accordingly. The < and > symbols are removed when the desired value is entered.

Trigger.txt

CREATE TRIGGER [dbo].[twbs_tr_VersagoPayments]
ON [dbo].[Versago_Payments]
AFTER INSERT
AS
DECLARE @vgo_Batchid nvarchar(8), @arguments nvarchar(100)
---
SELECT @vgo_BatchID = vgo_BatchID FROM inserted
BEGIN
--
SET @Arguments = '{"vgo_BatchID":' + '"' + @vgo_BatchID + '"' + '}'
--
EXECUTE [dbo].[spCallBizweaverWS]
@wsMethod='POST'
-- add your URL.  Be sure to use http or https as appropriate.
,@wsURL='https:<YourURL>'
,@wsContent='application/json'
-- Add your Auth Token from Bizweaver
,@wsAuthToken='<YourBizweaverToken>'
-- Add your workflow ID from Bizweaver
,@wsTaskId='<YourWorkflowID>'
,@wsStartAfter=''
,@wsParameters= @Arguments
-- Add your port number
,@wsPort='<YourPort>'
END