Skip to content
ERP Migration

Inventory and Warehouse Data Migration: Handling UOM, Batches, and Lot Numbers

A practical guide to migrating complex inventory data, focusing on Unit of Measure (UOM) conversion, batch and lot traceability, and warehouse location mapping to ensure data integrity and operational continuity.

9 min readSysGenPro Editorial
Inventory and Warehouse Data Migration: Handling UOM, Batches, and Lot Numbers

How to Successfully Migrate Inventory and Warehouse Data with UOM, Batches, and Lots

Migrating inventory and warehouse data is one of the most complex aspects of an ERP implementation due to the intricate relationships between items, units of measure (UOM), batch/lot numbers, and physical locations. The recommended approach involves a rigorous data profiling phase to identify inconsistencies, a detailed mapping strategy for UOM conversions, and a phased migration process that prioritizes active stock and critical traceability data. This migration matters because inaccurate inventory data leads to stockouts, overstocking, financial misstatements, and compliance violations. The main risks to control include UOM conversion errors, loss of batch traceability, mismatched warehouse locations, and data duplication. By establishing clear validation rules and reconciliation processes, organizations can ensure data integrity and operational continuity during the transition.

Preparation: Data Profiling and Cleansing

Before any data is moved, a comprehensive data profiling exercise must be conducted on the source system. This involves analyzing the current state of inventory records to identify gaps, duplicates, and inconsistencies. Key areas to profile include item master data, UOM definitions, batch/lot attributes, and warehouse location structures. Data cleansing should focus on standardizing item descriptions, removing obsolete items, and correcting UOM hierarchies. For example, if an item is stored in kilograms but ordered in boxes, the conversion factor must be accurate and consistent. Duplicate records, often resulting from manual entry errors, must be identified and merged. This phase requires collaboration between IT, finance, and operations teams to define business rules for data retention and cleansing. A data mapping matrix should be created to document how each source field maps to the target ERP field, including any transformation logic required.

Identifying Critical Data Elements

Not all inventory data is equally critical. It is essential to distinguish between master data (items, UOMs, locations) and transactional data (stock on hand, batch history). Master data must be migrated first to establish the foundation for transactional data. Critical elements include the base UOM, conversion factors, batch/lot numbers, expiration dates, and warehouse bin locations. Historical transaction data may not need to be migrated in full; instead, a snapshot of current stock on hand, valued at the correct cost, is often sufficient. This decision should be based on business requirements for reporting and traceability. For industries with strict regulatory requirements, such as pharmaceuticals or food and beverage, migrating full batch history may be necessary to maintain compliance.

Handling Unit of Measure (UOM) Conversions

UOM management is a common source of errors in inventory migration. The target ERP must have a clear UOM hierarchy defined, with a base UOM for each item and conversion factors for all other UOMs. During migration, it is crucial to ensure that the base UOM in the target system matches the business requirement. If the source system uses different base UOMs for the same item, a decision must be made on which UOM to standardize on. Conversion factors must be validated to ensure they are mathematically correct and consistent across all records. For example, if 1 box equals 12 units, the conversion factor must be 12.00. Errors in conversion factors can lead to significant discrepancies in stock quantities and valuation. It is recommended to create a UOM conversion table that maps source UOMs to target UOMs and includes the conversion factors. This table should be reviewed and approved by business stakeholders before migration.

Validating UOM Consistency

After migrating UOM data, validation scripts should be run to check for consistency. These scripts can verify that all items have a valid base UOM, that conversion factors are positive numbers, and that UOMs are correctly linked to items. Any discrepancies should be flagged for review and correction. It is also important to test UOM conversions in the target system by creating sample transactions and verifying that quantities are converted correctly. This testing should be performed in a staging environment before the final migration. By thoroughly validating UOM data, organizations can prevent errors that could impact inventory accuracy and financial reporting.

Migrating Batch and Lot Numbers

Batch and lot numbers are critical for traceability and compliance. When migrating this data, it is essential to ensure that batch/lot numbers are unique and correctly linked to items and stock quantities. Batch/lot attributes, such as expiration dates, manufacturing dates, and status (e.g., active, expired, quarantined), must be accurately transferred. If the source system uses different formats for batch/lot numbers, a standardization process may be required. For example, if some batches are stored as 'LOT001' and others as '001', a decision must be made on the standard format. This standardization should be documented and applied consistently during migration. Additionally, batch/lot history, including movements and adjustments, may need to be migrated if required for audit purposes. This data should be validated to ensure that the total quantity of each batch matches the stock on hand.

Managing Batch Expiration and Status

Expired or quarantined batches require special handling during migration. It is important to define business rules for how these batches should be treated in the target system. For example, expired batches may need to be written off or moved to a quarantine location. Quarantined batches should be flagged in the target system to prevent them from being used in production or sales. These rules should be implemented in the migration scripts to ensure that the correct status is applied to each batch. By properly managing batch expiration and status, organizations can maintain compliance and prevent the use of non-conforming materials.

Mapping Warehouse Locations

Warehouse location mapping is another critical aspect of inventory migration. The source system's location structure may differ from the target system's structure. A detailed mapping plan must be created to translate source locations to target locations. This plan should consider the physical layout of the warehouse, the type of storage (e.g., bin, pallet, shelf), and any location-specific attributes (e.g., temperature-controlled, hazardous materials). If the target system uses a different location hierarchy, the mapping must account for this difference. For example, if the source system uses a single-level location structure and the target system uses a multi-level structure (e.g., warehouse, zone, aisle, bin), the mapping must be adjusted accordingly. This mapping should be validated by walking the warehouse and verifying that the mapped locations correspond to the physical locations.

Handling Location-Specific Attributes

Some warehouse locations have specific attributes that must be preserved during migration. For example, temperature-controlled locations may have specific temperature ranges, and hazardous material locations may have specific safety requirements. These attributes must be accurately transferred to the target system to ensure that the correct storage conditions are maintained. If the target system does not support certain attributes, a workaround may be needed, such as using a custom field or a separate system. By carefully handling location-specific attributes, organizations can ensure that the target system accurately reflects the physical warehouse environment.

Technical Execution: Migration Scripts and Tools

The technical execution of the migration involves developing and testing migration scripts that extract data from the source system, transform it according to the mapping rules, and load it into the target system. These scripts should be written in a robust programming language and include error handling and logging capabilities. Migration tools can be used to automate the process and provide a user-friendly interface for monitoring and managing the migration. It is important to test the migration scripts in a staging environment using a representative sample of data. This testing should include unit tests, integration tests, and end-to-end business process tests. Any defects identified during testing should be fixed and retested before the final migration. By thoroughly testing the migration scripts, organizations can reduce the risk of errors and ensure a smooth migration process.

Validation and Reconciliation

After the migration, a comprehensive validation and reconciliation process must be performed to ensure data integrity. This process involves comparing the source and target data to identify any discrepancies. Key metrics to validate include total stock on hand, stock value, batch/lot quantities, and UOM conversions. Reconciliation reports should be generated to highlight any differences and provide a detailed breakdown of the discrepancies. These reports should be reviewed by business stakeholders to determine the root cause of the discrepancies and take corrective action. It is important to document the reconciliation process and the results to provide an audit trail. By thoroughly validating and reconciling the migrated data, organizations can ensure that the target system contains accurate and reliable inventory data.

Cutover and Deployment

The cutover phase involves the final migration of data and the switch from the source system to the target system. A detailed cutover plan must be developed, including a timeline, roles and responsibilities, and go/no-go criteria. The cutover should be performed during a period of low business activity to minimize disruption. A data freeze should be implemented in the source system to prevent any changes during the migration. The final data load should be performed, and the target system should be validated to ensure that all data has been migrated correctly. Once the validation is complete, the target system can be made available to users. A rollback plan should be in place in case of any critical issues. By carefully planning and executing the cutover, organizations can ensure a smooth transition to the new ERP system.

Post-Migration Stabilization

After the cutover, a post-migration stabilization phase is essential to address any issues that arise and to ensure that the system is operating correctly. This phase involves monitoring the system for errors, performance issues, and user feedback. Any issues identified should be logged and prioritized for resolution. User training and support should be provided to help users adapt to the new system. Regular reconciliation checks should be performed to ensure that the inventory data remains accurate. By actively managing the post-migration stabilization phase, organizations can ensure that the new ERP system delivers the expected benefits and that the inventory data remains reliable.

PhaseKey ActivitiesResponsible RolesKey Deliverables
PreparationData profiling, cleansing, mappingIT, Finance, OperationsData mapping matrix, cleansing rules
Technical ExecutionScript development, testingIT, ERP ConsultantsMigration scripts, test results
ValidationReconciliation, discrepancy resolutionIT, Finance, OperationsReconciliation reports, audit trail
CutoverFinal data load, system switchIT, Project ManagerCutover plan, go/no-go decision
StabilizationMonitoring, issue resolution, trainingIT, Support TeamIssue log, user feedback
  • Ensure UOM conversion factors are mathematically correct and consistent.
  • Standardize batch/lot number formats and validate uniqueness.
  • Map warehouse locations accurately, including location-specific attributes.
  • Perform thorough testing in a staging environment before final migration.
  • Implement a robust reconciliation process to validate data integrity.
Written by
SysGenPro Editorial

SysGenPro Editorial

Platform & solutions team

Practical guidance on ERP, AI automation, cloud deployment and enterprise transformation from the SysGenPro team.

Share this article

Frequently asked questions

Common questions on this topic. Talk to our team for anything specific to your business.

01How do I handle UOM mismatches during ERP migration?

Identify all UOM mismatches during data profiling, define a standard base UOM for each item, and create a UOM conversion table with accurate conversion factors. Validate these conversions in a staging environment before final migration.

02What is the best way to migrate batch and lot history?

Migrate batch/lot numbers, attributes, and current stock quantities. Historical transaction data may be migrated if required for compliance or audit purposes. Validate that batch quantities match stock on hand.

03How do I map old warehouse locations to the new ERP?

Create a detailed mapping plan that translates source locations to target locations, considering the physical layout and location-specific attributes. Validate the mapping by walking the warehouse.

04What are the common risks in inventory data migration?

Common risks include UOM conversion errors, loss of batch traceability, mismatched warehouse locations, and data duplication. Mitigate these risks through thorough data profiling, cleansing, and validation.

05How do I validate inventory data after migration?

Perform a comprehensive reconciliation process, comparing source and target data for total stock on hand, stock value, batch/lot quantities, and UOM conversions. Generate reconciliation reports and resolve any discrepancies.

06Should I migrate historical inventory transactions?

Migrate historical transactions only if required for compliance, audit, or reporting purposes. Otherwise, a snapshot of current stock on hand is usually sufficient. This decision should be based on business requirements.

Continue learning
View all articles
Explore the platform

Where to go next

Browse the knowledge hub

See how SysGenPro fits your business

Discuss your workflows, modules and deployment options with our team.

Back to resources