Tips for “LineNum” of DTW

[haow]

I recently worked on several incidents about errors occurring when importing data through Data Transfer Workbench (DTW), and the most of them are related to the errors about no matching record found, such as the object record does not exist, the record is missing, etc.

Thanks to our partners' cooperation, I could deeply investigate the data and analyze the problem. I finally noticed that the errors are all caused by the wrong LineNum entered in the DTW import template.

The LineNum in DTW import template sometimes may be tricky and confusing. It may cause errors if the data is not entered correctly. Therefore, I would like to hereby share some tips about how to verify LineNum when importing data via DTW.

 

Let’s first take a quick review of SAP Note 1280506 – “DTW Import Data Preparation Steps and Basic Checks”, which explains how to identify the LineNum as this:

• LineNum
  
  
  
  • It is only used when updating an existing business object. The value should be an integer which begins with 0.
  
  
  • To identify the lineNum the following ordering is required:
    
    
    
    • For main tables, the order should be on <primary key> of the table (such as ocrd, oitm)
    
    
    • For a child table, the order should be on <primary key of parent table + LineNum (if exists) + primary key of the table> (such as OCPR, RDR1, CRD1)
    
    
    • You must keep it empty when importing new business objects (Marketing Documents, Items, Business Partners, etc.).
    
    
    
    
  
  
  
  

So, for example, if I have a sales order with 3 different lines (Screenshot 1), and when I query the data using the SQL statement below following the instruction indicated in the SAP Note, I should be able to identify the LineNum in the DTW import template of table RDR1, which are 0, 1 and 2 (Screenshot 1-1):

SELECT DocEntry 'DOC ENTRY', LineNum 'ROW NUMBER', ItemCode 'ITEM', Quantity 'QUANTITY'

FROM RDR1 WHERE DocEntry = 356

ORDER BY DocEntry, LineNum ASC;

(Screenshot 1)

(Screenshot 1-1)

 

Alternatively, we can use another query below for better demonstrating the LineNum which we should use in the DTW import template (Screenshot 1-2):

SELECT ROW_NUMBER() OVER (PARTITION BY DocEntry ORDER BY DocEntry) -1 AS 'DTW LineNum'

, DocEntry 'DOC ENTRY', LineNum 'ROW NUMBER', ItemCode 'ITEM', Quantity 'QUANTITY'

FROM RDR1 WHERE DocEntry = 356;

(Screenshot 1-2)

 

Sometimes, the LineNum of DTW could be easily confused with “Row Number”, because the column of “Row Number” is also named “LineNum” in databases. However, it works differently than the LineNum in DTW import template. For example, if I delete the second row (Row Number #1) from my sales order in Business One Client, and when I query the data using the same SQL statements as before, I will be able to see the Row Number (LineNum) of sales order line 1 and 3 won’t be changed which will remain the same as 0 and 2, but the LineNum of DTW import data will become 0 and 1 for the first line and the second line of the sales order (Screenshot 1-3):

(Screenshot 1-3)

 

As we can see from the above examples, the LineNum to be used in DTW should be always identified as a sequence of natural numbers (non-negative integers) starts from 0. Therefore, we need to make sure that we always enter the correct LineNum in the DTW import template when updating the data using DTW to prevent the errors from happening, and furthermore, we can also check Target Data in the DTW Data Import Wizard window in DTW to verify and ensure the LineNum is entered accurately.

 

Now, let’s have a look at my sales order again. My sales order only has two lines and I have identified the LineNum of the two order lines are 0 and 1. If I want to update the data of the second order line, but I misentered a 2 as the LineNum in the DTW import template (Screenshot 2), then, after DTW loads the data from the template, the Target Data in DTW Data Import Wizard window will display that DTW will try to update the third line, which does not actually exist on this sales order (Screenshot 3).

(Screenshot 2)

(Screenshot 3)

 

As a result, the update will fail because the record cannot be properly found (Screenshot 4).

(Screenshot 4)

 

This error can be simply fixed by changing the LineNum from 2 to 1 in the DTW import template, and as I mentioned previously, we can verify it in the DTW Data Import Wizard window (Screenshot 5).

(Screenshot 5)

 

And when I check the sales order in Business One Client, the update is already showing there (Screenshot 6).

(Screenshot 6)

 

Because I found that reviewing Target Data in DTW Data Import Wizard is very helpful when investigating DTW import issues, and the same logic applies in both SAP Business One and SAP Business One version for SAP HANA*, I would highly suggest that we always check the target data when inserting or updating existing records via DTW, so that we can make sure that the LineNum as well as other data entered in the DTW import template are accurate to prevent the same kind of errors from happening in the future.

 

*All the examples and the screenshots are taken from SAP Business One on SQL Server. If you want to check the logic in a SAP Business One on version HANA environment, the syntax of the SQL statements will have to be changed accordingly, such as below:

SELECT "DocEntry" "DOC ENTRY", "LineNum" "ROW NUMBER", "ItemCode" "ITEM", "Quantity" "QUANTITY"

FROM RDR1 WHERE "DocEntry" = 356

ORDER BY "DocEntry", "LineNum" ASC;



SELECT ROW_NUMBER() OVER (PARTITION BY "DocEntry" ORDER BY "DocEntry") -1 AS "LineNum In DTW"

, "DocEntry" "DOC ENTRY", "LineNum" "ROW NUMBER", "ItemCode" "ITEM", "Quantity" "QUANTITY" 

FROM RDR1 WHERE "DocEntry" = 356;

Furthermore, the DTW import template and the input data used in this article are only for table RDR1. The same logic is applicable for most of the Business Objects, but not for all objects. Therefore, we recommend that you always check existing SAP Notes when you encounter issues with DTW data import and contact SAP SME Support team for any further investigation and assistance.