This page discusses the Bizweaver tools used for integration with SAP Business One (SBO) on SQL Server databases.

The following pages contain additional information that may be helpful with the development of Bizweaver workflows.

• Global Connections
• SQL Command Tool
• Looping Tool

• DI Server refers to an application from SAP Business One that communicates with SAP Business One for the purposes of data manipulation. DI Server is licensed separately from the core SAP Business One application.
• DI API refers to a component of the core SAP Business One application that communicates with SAP Business One for the purposes of data manipulation.

• DI Server and/or DI API global connections must be defined.

The discussions in this document assume that you are using either DI Server or DI API to communicate with SAP Business One in a SQL Server environment. These tools can also be used in a HANA environment but with some limitations. New functionality beginning in SAP Business One v9.2 may not be supported with DI Server or DI API.

For HANA environments Third Wave recommends the use of the SAP Business One Service Layer. This is a web service-based set of APIs that support integration activities. Unfortunately, this approach is only available for HANA environments. The Service Layer is supported in Bizweaver via the Web Service tool. Information on using the Service Layer integration will be provided in a separate document.

There are two Bizweaver tools that are used for integration with SAP Business One: Mapper and SBO Update. These tools are specific to SAP Business One and are not used for any other purpose.

To illustrate the SAP Business One integration process, we will use the following workflow. In this case, the source data is coming from database tables that are associated with a Versago form. (Versago is a Web/Mobile portal application from Third Wave). However, the same principles apply for any data source (e.g. flat files, etc.).

The basic process is:

1. A SQL Command: Reader step gets a list of orders to be processed.
2. A Loop is started to process each order record.
3. A SQL Command: Reader step gets the details for each order to be processed based.
4. The detailed data is mapped to the corresponding fields in SAP Business One.
5. The mapped data is passed through the SAP Business One DI Server to update the designated company database.
6. The Loop End tests for additional unprocessed orders. The next order is processed from step 3.

The Mapper tool is used to connect the incoming data columns to the appropriate value in SAP Business One.

1. Select a DI Connection to an SAP Business One database from the drop-down list.
   1. A previously defined DI connection is used to connect to the desired database.
   2. The connection can be either DI Server or DI API.
2. Select a SQL Command workflow step from the drop-down list.
   1. This step provides the data to be passed to SAP Business One.
3. Click [Next] to move to the mapping page or click on the Mapping tab.

An SAP Business One “object” is the document or transaction to be created. In this example we will create a Sales Order.

The list of objects will be slightly different based on the DI connection being used. When DI API is being used, the object name starts with “o”, where DI Server does not. Using Sales Orders as an example:

• DI Server: OrdersService
• DI API: oOrders

Double-click on the object name to continue.

The “command” is the action you wish to take. The most commonly used options are Add, Update, Close, and Cancel.

Double-click on the command to continue.

The “Object Components” are the elements of the document or transaction that will be used. Most SAP Business One objects have multiple components and you must know which ones are appropriate for your application. In most cases, you will need “Documents” and “Document_Lines” at a minimum. “Documents” is the header information and “Document_Lines” is the information in the rows grid in SAP Business One.

Double-click on the desired object component to select it.

In the following image, “Documents” and “Document_Lines” have been selected.

“Mapping” is the process of defining which SAP Business One values are to be updated from which values are provided as input. It is accomplished by using drag-and-drop to link source data values to the corresponding SAP Business One value.

1. Locate the source value in the left-hand column (as shown below) and the target value in the right-hand column.
2. Click on the source value and drag & drop it on the corresponding field in the column on the right.
   1. A line between the two values indicates that they are now mapped.
3. Continue the process for all values that need to be mapped.

1. Tabs indicating selected object components are shown at the top of the mapping section.
   1. Click on the appropriate tab to map information.
2. Indicate when new records are to be created.
   1. In general, header records will use the “Change of Value” option.
      1. Select the appropriate value from the drop-down list.
   2. Detail records (e.g. order rows) will use the “Every Row” option.
   3. See Mapping Tips for more information.
3. Listing of values from the data source.
4. Link indicators.
   1. The linking line indicates that the two values are “mapped.”
5. SAP Business One values for the selected object component.
   1. The image above shows the “header” values. The image below shows the “row” values.
   2. The list of values changes based on the selected object component.
6. Source-to-target mapping list.
   1. Lists all source values that have been mapped, and the value to which they are mapped.
   2. This listing is for information only.
7. Use the [Clear Links] button to clear all links for the object component currently being displayed.

The SBO Update tool handles the communication between Bizweaver and the SAP Business One DI. This is the step that updates SAP Business One.

Select a Mapper workflow step from the drop-down list.

To avoid possible DI issues, dates being passed into SAP Business One should use the YYYYMMDD format. The SQL function used to convert a date value to this format is:

CONVERT(VARCHAR,DateFieldName,112) AS [AliasName] 

where FieldName is the column to be converted and AliasName is the name to be used on output.

For example:

 CONVERT(VARCHAR,DocDate,112) AS [DocDate] 

At first glance, it might appear that header data and row data should be two separate data sets. This is not the case. Each incoming record must contain all needed columns for both the header and the row.

This is where the ‘Create new record for …” options come into play. Assume you have ten records for an order, each representing an order row. This means that the source record ID, customer code, etc. are presented ten times, once on each row.

To avoid creating ten orders, one for each row, we use the “Change of value” option. This option ensures that a header record is created only when the specified key record value in the incoming data changes.

At the same time, the rows typically use the “Every record” option. This means that a row record is created for each incoming row; but within the designated header value. Note, however, that in some cases the “Change on value” option for rows may use a specific value. See Appendix D for an example of this type of usage.

When transactions are created in SAP Business One through the DI, certain values are handled just like they are in the SAP Business One application. Examples:

• The document date will default to the current date if a date is not provided.
• Lines will automatically be numbered. You do not have to provide a line number when adding.
• Documents (sales orders, purchase orders, etc.) will default to Items-type unless Service-type is specified
• The default numbering sequence will be used unless an alternate sequence is specified.
• The salesperson associated with the Business Partner will be used on documents that have a salesperson reference.
• Prices (in sales and purchasing documents) will be based on the price list associated with the Business Partner. If you want to use different pricing, the best option is to pass in the LineTotal (extended) value. The DI will then use the line total and quantity to calculate the appropriate discount from the price list price. The DI generally ignores unit price and discount information.
• This is not an exhaustive list of default values. In most cases the DI will follow the same logic as the user application.

“Enumerators” are the values that are passed to SAP Business One to represent a certain value when using an application interface like Data Transfer Workbench (DTW), DI Server and DI API, or Service Layer (HANA). A different value may be passed to the interface depending on the tool being used.

A listing of common enumerators is found here.

The information DI Server uses in the Bizweaver Mapper and SBO Update tools is obtained from SAP Business One schema files. These files contain information about the various data objects that are available, what actions can be performed on each object, and the data elements of each object. Bizweaver DI Server connections interact with SAP Business One to create its own version of these files.

From time to time it may be necessary to update these files. This generally happens automatically when a Mapper step is opened. If the files need to be updated, Bizweaver will appear to “pause” for several seconds. A progress bar is displayed while the update is executing. When the progress bar disappears, you will be able to continue working.

See SBO Schemas for more information.

Bizweaver will create DI Server schemas for SAP Business One User Defined Objects (UDO). For this process to work the UDO name assigned in the registration process cannot contain any spaces. If you have followed the instructions above and still don’t see the UDO, this is the first place to check.

Be sure to follow the manual update process described above after making this change.

The SAP Business One DI handles Business Partner Contacts and Addresses in a rather odd manner. It is not possible to simply add a new contact or address. Nor is it possible to simply update a single existing contact or address record. Instead, you must retrieve ALL appropriate records for the Business Partner. This can be done with a UNION statement in the source SQL.

Note that in both cases the function is an UPDATE, not an Add. This is a bit confusing since you are adding a new record. But you are updating the master record. The DI process takes care of figuring out which child records are additions, and which are updates.

• Adding and Updating Business Partner Addresses
• Adding and Updating Business Partner Contacts

• Inventory Transfers with Bins

The Bizweaver File Writer tool can be used to create a file to be used for transaction processing tracking. A common use of this type of file is to capture error information when a transaction in SAP Business One is being created.

The Custom Output File Writer option must be used to create this type of file. In this example, we are using all three sections of the custom output. The Body section of the file contains the response from the SAP Business One DI.

The values that are used in the Body section are shown below. Press F4 to get a list of all available variables.

SBO Key: WorkFlow(DI_ServiceCall).Get({RetKey})
PassFail: WorkFlow(DI_ServiceCall).Get({VALUE})
Type: WorkFlow(DI_ServiceCall).Get({RetType})
Message: WorkFlow(DI_ServiceCall).Get({MESSAGE})

• RetKey is the key value returned from SAP Business One when a document is posted successfully.
• Value indicates whether the SAP Business One posting was successful. Values returned will be either “Y” for success or “N” for fail. In most cases this variable will be used in a decision step.
• RetType is the SAP Business One object ID for the document that was created.
• Message is the response returned from the SAP Business One ID if the posting was not successful. This will general provide some information as to the problem. No message information is returned if the posting was successful.

Following is the output to the file based on the setup described here. The mapping was deliberately done incorrectly to illustrate the output from SAP Business One. Note that {RetKey} is blank, {Value} is “N” and {Type} is blank since the posting failed. {Message} describes the issue as it came from the SAP Business One DI.

This is the Header section

Service Call for: C20000
File written at 2016-03-01 09:56:06.220


This is the Body section
=====

RetKey:
Value: N
Type:
Message: Error Code: -5002, Error Message: Subject is not specified in service call [OSCL.subject]
===========
This is the Footer section

An additional workflow step could also be added to email this file to someone as an alert that a problem occurred.

A Versago form can be configured to allow the user to upload files as part of the transaction. It is possible, using Bizweaver, to use these files to create attachments in SAP Business One. The following section describes this process for two scenarios.

Note: the SAP Business One object (Business Partner, Document, etc.) must exist before attachments can be applied.

Uploaded files are stored in a table named TWBS_VGO_FormAttachments. This table is automatically created by the Versago process in whatever database is referenced by the form. The name of the table is fixed, and cannot be changed.

There are three columns in the table that are key to retrieving the file data and passing it to SAP Business One. These columns are: FormId, RecordId, FileName, and FileContent.

FileName is the name of the file when it was uploaded. No references to the source folder are captured since they are not relevant.

FileContent is the actual data from the file, stored as a binary record. The information in this field cannot be used without some process to convert it back to its original format.

FileName and FileContent are used in the mapping into SAP Business One.

FormId is the identifying number of the Versago form. You can find this information on the “Form Creation”tab on the from maintenance function is Versago.

RecordId is the primary key value (record ID) of the record in Versago that was created when the file was uploaded. There may be multiple records in TWBS_VGO_FormAttachments for each RecordId.

The Versago record referenced by RecordId must contain some information that will allow you to reference the object in SAP Business One where the attachment will be added. In the following Example 1, the value is a Business Partner code. In Example 2, the value is the unique ID assigned to an SAP Business One Marketing document (Purchase Order, in this case) when the order is created. This link is required, but the method of obtaining the value can be accomplished in different ways. The discussion of how this is done in Versago is outside the scope of this document.

The folder that holds attachments for SAP Business One is defined in Administration > System Initialization > General Settings >

In this example, a Versago form has a field that captures a Business Partner code in its record. It also allows a user to upload associated files.

1. A list of unique file upload records (column “Id”) is obtained. In this example, the Versago form ID (ObjectId) is 11.
   

   SELECT 
   t0.Id 
   FROM TWBS_VGO_FormAttachments t0 
   WHERE t0.ObjectID = 11 

2. A loop is used to process each record from the uploaded files table individually.
3. The detailed information needed to create the attachment in SAP Business One is obtained from each record identified in step 1. This is normal Bizweaver loop processing. Note that the SAP Business One value to be updated (CardCode), the file name (FileName) and the binary data for the file (FileContent) are retrieved.
   

    SELECT
   t0.recordid, t1.CardCode, t0.Filename, t0.FileContent
   FROM TWBS_VGO_FormAttachments t0
   INNER JOIN Attachments_BP t1 ON t0.RecordID = t1.vgoRecNum
   WHERE t0.Id = WorkFlow(SQLCommand_getProcessList).Get({FIELD_Id})

4. The needed fields are mapped for SAP Business One.
   1. In this case the SAP Business One object receiving the attachment is a Business Partner.
   2. The transaction for attachment will always be an UPDATE.
   3. The FileName and FileContent values are both mapped to the “AttachmentEntry” (AtcEntry) value in SAP Business One.
      
5. The mapped information is passed into the SAP Business One update tool for processing.

In this example, a Versago form is used to capture information to create a Purchase Order in SAP Business One. The order is created in SAP Business One, and then the attachments are associated with the new order.

1. A list of records created from the Versago form is created, and an SAP Business One purchase document is created for each one.
2. Once an order has been created we retrieve the primary key value (DocEntry) needed for update.
   

    SELECT DocEntry, CardCode FROM OPOR WHERE DocEntry = 'WorkFlow(SBOUpdate_PO).Get({RetKey})'

3. A list of attachment records associated with the form record are retrieved.
   

   SELECT
   t0.Id
   FROM TWBS_VGO_FormAttachments t0
   WHERE t0.ObjectID = 12 AND RecordId = WorkFlow(SQLCommand_getOrderList).Get({FIELD_vgoRecNum}) 

4. A loop is used to process each record from the uploaded files table individually.
5. The detailed information needed to create the attachment in SAP Business One is obtained from each record identified in step 3. This is normal Bizweaver loop processing. Note that the SAP Business One value to be updated (CardCode), the file name (FileName) and the binary data for the file (FileContent) are retrieved.
   

   SELECT
   t0.Id, t0.Filename, t0.FileContent, WorkFlow(SQLCommand_getDocEntry).Get({FIELD_DocEntry}) AS [DocEntry], 'Test' AS [NumAtCard]
   FROM TWBS_VGO_FormAttachments t0
   WHERE t0.Id = WorkFlow(SQLCommand_getAttachList).Get({FIELD_ID}) 

6. The needed fields are mapped for SAP Business One.
   1. In this case the SAP Business One object receiving the attachment is the purchase order created previously in the process and defined by the “DocEntry” value from step 2.
   2. The transaction for attachment will always be an UPDATE.
   3. The FileName and FileContent values are both mapped to the “AttachmentEntry” (AtcEntry) value in SAP Business One.
      
7. The mapped information is passed into the SAP Business One update tool for processing.

Files on disk (as opposed to files uploaded into Versago as described in Appendix F) can also be added as SAP Business One attachments. The process consists of identifying the file to be “attached,” mapping it into SAP Business One, and posting the attachment.

The file to be attached must be identified as a Bizweaver Variable in the workflow. This will typically be done using a File Operations-Get step. The results of the “get” are then identified in a subsequent Variable step.

1. File Operations – Get step to get file information.
   
   In this example we are getting one file (TestFile.txt) from C:\Shared\Infiles.
2. Set variable to the value from the “get” step.
   
   Note that the “FileName” value is used from the “get” step. This value includes the folder path, file name, and file extension.
3. Map the needed information in a Mapper step.
   
   In this example we are adding an attachment to a Business Partner. The only values needed are the BP Code (CardCode) and the variable set earlier with the file information.
4. A subsequent SBOUpdate tool step is used to post the information to SAP Business One.
   Any object in SAP Business One that supports attachments can be updated using the above process. You only need to provide the key value for the object and the file information. The two most common objects for attachments are Business Partners where the key value is CardCode, and Marketing documents (e.g. Orders, Deliveries/Receipts, Invoices) where the key value is DocEntry.

Sample of Calling Bizweaver from SQL Server