SAP Business One

Data Transfer Workbench

SAP Business One Data Transfer Workbench (DTW) is a migration tool that allows to import data from a CSV file to SAP Business One. It is useful when you need to import large amounts of data, such as items and business partners.

To make the import process easier, the Data Transfer Workbench comes with the templates for all objects, which you can use to fill in your own data and then import to SAP Business One. You can find the default templates in the folder where the Data Transfer Workbench is installed. The folder hierarchy of the templates is the same as the structure of the SAP Business One Main Menu.

A part of the Sana Commerce solution is built on top of and installed inside SAP Business One. Therefore, we added our own user-defined fields to some standard SAP Business One tables. For example, you can see the user-defined fields of the Sana Commerce add-on in the item and business partner master data.

You can customize the default templates to include the user-defined Sana fields and to import the Sana specific data to SAP Business One. The user-defined fields must be added as the extra columns at the end of the template. You must use the technical names of the user-defined fields from the database.

For example, to synchronize items and business partners between Sana and SAP Business One, and to update items and business partners master data, Sana checks the value of the user-defined Sana field "Modified Date & Time". If it is empty, items and business partners will not be imported from SAP Business One to Sana.

Thus, if you are using the Data Transfer Workbench to import or update item master data (OITM) or business partner master data (OCRD), you must add the user-defined Sana field "U_ModifiedDateTime" ("Modified Date & Time") as an extra column to the template. The values of the user-defined Sana field "U_ModifiedDateTime" must be in the following format "yyyy-MM-dd HH:mm:ss", for example 2017-09-28 03:05:47.

This time must be after the last product indexing time in order for Sana to recognize that there are new or updated items and to index the catalog accordingly.

When the appropriate CSV template is filled in with all required data, you can import it to SAP Business One using the Data Transfer Workbench.

Update Modified Date & Time (Option 2)

Alternatively, the values of the "Modified Date & Time" field can be updated by adding the SQL query to the "SBO_SP_TransactionNotification" stored procedure in the SAP Business One SQL database. This stored procedure is triggered whenever operations (adding, updating or deleting) are performed on business objects, like master records and user-defined objects.

If you add the SQL query provided below for the items and business partners to the "SBO_SP_TransactionNotification" stored procedure, each time when items or business partners are imported to SAP Business One or updated using the Data Transfer Workbench, the values of the "Modified Date & Time" field will be automatically updated with the current date and time.

--------------------OCRD (Business Partner Master Data)--------------------
if @object_type = '2' and @transaction_type IN ('A','U','D')
Begin
  update OCRD set U_ModifiedDateTime = CONVERT(varchar, GETUTCDATE(), 120)
  where OCRD.CardCode = @list_of_cols_val_tab_del
    set @error_message ='modified date update failed'
End

--------------------------OITM (Item Master Data)--------------------------
if @object_type = '4' AND @transaction_type IN ('A','U','D')
Begin
  update OITM set U_ModifiedDateTime  = CONVERT(varchar, GETUTCDATE(), 120)
  where OITM.ItemCode = @list_of_cols_val_tab_del
    set @error_message ='modified date update failed'
End

 NOTE

This method can be implemented by your SAP Business One implementation partner. If you upgrade your SAP Business One system, your changes in the "SBO_SP_TransactionNotification" stored procedure will be lost. Thus, you will need to do it again after upgrade.