SAP Note này tổng hợp các thông tin liên quan đến việc giải quyết các vấn đề gặp phải trong quá trình sử dụng công cụ Data Transfer Workbench (DTW) để import data vào hệ thống SAP Business One.
Lưu ý quan trọng:
- DTW hỗ trợ thêm mới (add) hoặc cập nhật (update)
- DTW không hỗ trợ hủy (cancel), xóa (remove), đóng (close)
Solution
General Checks
- DTW is a tool based on DIAPI.
- Only adding new or updating existing records corresponding to DIAPI Add() and Update() methods are supported.
- It is not possible to delete/remove/cancel/close records using DTW for main objects.
- Special operations corresponding to DIAPI methods are not supported.(such as UpdateAllocation(),CreateCancellationDocument())
- Always generate templates as described in help.
- A template is related to one object. If an object has sub-objects, every sub-object also has a template.
- For example, BusinessPartners is a main object.ContactEmployees,BPAddresses are subobjects of BusinessPartners.
- Microsoft Excel must be installed on the machine running DTW to create customized templates.
- Always use the template from the current version of DTW. The template format is changed as of version 8.8 and older templates are not compatible.
- The RecordKey field was removed from all templates and the new field ParentKey was added to the child templates as of version 8.8 PL10.
- Templates will differ based on customization and localization of the database. Hence the same template is not interchangeable between databases.
- A template is related to one object. If an object has sub-objects, every sub-object also has a template.
- Fill the data in the templates while keeping below tips and rules in mind:
- First row in the template represents, DIAPI properties. Do not change this first line in the template, as it might cause incorrect mapping, which can result in data not being imported.
- The second row is the property explanation. This line can be changed for a better explanation of the property. But do not use special characters (‘ , / & ” …) or empty spaces in the description.
- We suggest not to change the column order from the order in the standard templates.
- We suggest not to delete any columns from the standard templates. if a value is not required to be updated leave the column blank.
- RecordKey/ParentKey
- This key identifies the header record from other records. To improve the performance, RecordKey/Parentkey values are sorted.
- The same RecordKey for one object must be entered in both templates for the main object and sub-object templates.
- LineNum
- It is only used when updating an existing business object. The value should be an integer which begins with 0. You must keep it empty on add.
- To identify the lineNum, you can use below rules to formulate the query.
- For tables representing main objects (such as OCRD, OITM), the ORDER BY should contain <primary key> of the table.
- For tables representing sub-object objects (such as OCPR, RDR1, CRD1), the ORDER BY should contain <primary key of parent object table + LineNum (if exists) + primary key of the child table>.
- PARTITION BY contains the primary key of parent object table.
- For example, based on above rules, to identify the line number for Business Partners Addresses below query can be formulated.
MSSQL
SELECT ‘LineNum in DTW’ = ROW_NUMBER() OVER (PARTITION BY CardCode ORDER BY CardCode, LineNum, Address, AdresType) -1, *
FROM CRD1 WHERE CardCode = ‘C20000’
HANA
SELECT ROW_NUMBER() OVER (PARTITION BY “CardCode” ORDER BY “CardCode”, “LineNum”, “Address”, “AdresType”) -1 as “LineNum in DTW”, *
FROM CRD1 WHERE “CardCode” = ‘C20000’
-
- ReadOnly Key
- Keep it empty when importing new business objects
- Fill the same value as in the SAP Business One Company database when updating an existing business object. (Example: “DocEntry” is the Documents Objects readOnly Key.)
- ReadWrite Key
- This field is required for both importing new and updating existing business objects. Example: The “CardCode: is the Business Partners ReadWrite Key
- It is possible to delete the contents of an edit text field by using the character / or -1. However, it is not possible to clear an entire row/line using this notation.
- When updating a record in which you wish to set a field to a blank value put the ‘/’ symbol in the field in the template. This function does not delete any line items from an object. It enables you to delete existing values from a specific field only. It’s not possible to update date and numeric data types with a blank value via DTW.
- The correct date format is YYYYMMDD. In UDOs the format is YYYY-MM-DD.
- Do not use <ENTER> Key characters in the import file. Using the <ENTER> key could scramble the source data.
- If the import file contains double-byte characters (common in East Asian country/regions) or special characters, like double quotes (“), single quotes (‘) and leading zeros (00502,0101). Use tab limited format and use text editors instead of MS Excel, since MS Excel could handle special characters differently and can truncate leading zeros if text format is not used.
- DTW does not automatically round values as per the SAP Business One rounding setting. You need to specify the correct rounded values in the Excel.
- ReadOnly Key