When you implement SAP B1 and wants to migrate transactions data also for batter analysis or its statutory requirement to have data of specified time period in your accounting software. Here i will discuss methodology and challenges during migration
Templates with sample data applicable with DTW can be found on following path on same machine where DTW is installed C:\Program Files (x86)\sap\Data Transfer Workbench\Templates
The Methodology used during migration is EFI (Export, Filtration, Import) i will discuss it step by step and assume your legacy system's database is also in SQL Server and for example you want to import Open Purchase Orders
1- Export (E): First you export Open Purchase Orders from your legacy system to excel while exporting develop your query according to Data Transfer Work Bench (DTW) templates i.e 1 query for header and one query for rows with reference of header record number and line number for each row.
Fields which should be mandatory in your header templates are Document Number, Vendor Code, Posting Date, Due Date, Document Date, Document Discount, Document Total and Vendor reference Number or any remarks any other fields according to your business process.
Further regarding required fields you can post some transactions in your test company with you consultant to know which fields you need to import
Fields mandatory for rows are Document Number, Line number, Item Code, Item Description, ,Quantity (Open Only), Warehouse, Unit of Measure, Unit Price, Discount , Line total and Line remarks if any or any other fields according to your business process
2- Filtration (F): Now you have your data in excel you need to do filtration and some updates according to SAP B1 like if there is change in item codes, vendor codes or UOM Codes, Whs Code,Update codes and descriptions according to new system, adjust charterer size of remarks fields as may be SAP B1 have short field lengths as compare to legacy system in this case you need to revise remarks or add UDF in SAP B1 according to character size of legacy system.
In Header File add additional column of Old document number From legacy system in your template and duplicate document number as its in legacy system.
Deploy a UDF for Old Ref number so in future user can easily track documents between legacy and new system.
3- Import (1): Now you have your data ready to import you just need to link column with relevant fields while importing i.e CardCode with vendor code and Cardname with vendor Name and so on
1- If you did not put discount there is some difference between multiplication of unit price and quantity system will automatically consider it as discount same for header total and total of all rows
2- If you did not put reference number from legacy system in separate filed user will never be able to track your documents
3- Reference between header and rows should be 100% correct otherwise header from one document will be imported with rows from other documents
4- Use Base Entry from SAP B1 if there are base documents like if you are importing Goods Receipts POs. To have accurate results use base entry of Po number from SAP B1.
Always perform such activity in test environment first after successful reconciliations go for live environment