Six things about Manhattan's schema that aren't in the documentation — and what to do about the LPN vs. HU naming conflict.
Manhattan Associates Active WMS is one of the more commonly encountered systems in mid-to-large distribution center environments, particularly in retail and CPG supply chains. Its data extraction layer is functional — but underdocumented in ways that cause real problems for integration projects. These notes are based on hands-on work with Active WMS deployments across several distribution environments and are intended to save the next data engineer a few weeks of discovery time.
Active WMS uses the term LPN (License Plate Number) extensively in its UI and user documentation. The term refers to a container identifier — a pallet, tote, carton, or any scannable unit with an associated ID. When you look at the underlying tables, LPN and HU (Handling Unit) are used inconsistently across different module versions and upgrade paths.
Specifically: older schema versions use the LPN_HDR and LPN_DTL tables for license plate header and detail records. Newer deployments that went through a 2022+ upgrade path may have a parallel set of HU_HDR and HU_DTL tables with overlapping but not identical data. In some configurations, both table families exist simultaneously with referential links between them; in others, one set is deprecated-but-populated.
When integrating downstream to SAP (which uses HU natively throughout EWM and ERP), the naming conflict becomes a join problem: your SAP records reference LENUM (the SAP HU ID) and your Active WMS records may be in LPN_HDR with a different ID format. You need a lookup bridge table — typically built from LPN_HU_XREF if it exists in your instance, or constructed from matching on weight, quantity, and time proximity if it doesn't.
Check for the existence of LPN_HU_XREF before building the integration. If it doesn't exist, escalate to the Manhattan system admin — there's a configuration flag that should generate it.
The PICK_DETAIL table tracks pick tasks in the warehouse. It contains both QTY_TO_PICK (the allocated quantity at task creation) and QTY_PICKED (the confirmed quantity at task completion). These fields are almost always identical in a normal pick operation — but they diverge in short-pick scenarios, where a picker confirms a smaller quantity due to inventory discrepancy.
The underdocumented issue: during a pick task that is partially completed and then reassigned (canceled by one picker, resumed by another), the STATUS field may show PENDING while QTY_PICKED is non-zero from the partial completion. This means filtering on WHERE status = 'COMPLETE' to get finished picks will miss these partially-completed-then-reassigned records. For inventory reconciliation purposes, you need to check for non-zero QTY_PICKED regardless of status, and then join back to the reassigned task to get the full picture.
Active WMS contains a table called INVENTORY_SNAPSHOT that sounds like exactly the right thing to query for point-in-time inventory balances. It is not a snapshot in the traditional data warehousing sense.
In Manhattan's schema, INVENTORY_SNAPSHOT is a working table used by the system's cycle count and physical inventory modules. It holds the expected quantities at the time a cycle count was initiated, used to compare against actual counts. It is not populated on a schedule, not updated continuously, and does not represent current on-hand quantities.
The actual current on-hand inventory in Active WMS is derived by querying INVENTORY_SUMMARY joined with LOCATION_INVENTORY, filtered to active locations and excluding reserved/allocated quantities if you want available-to-pick inventory. This is documented, but the misleading table name causes enough confusion that it's worth calling out explicitly.
Many Active WMS deployments run in a multi-client configuration — a single WMS instance serving multiple warehouse clients, each with logically separated inventory. The schema isolation is implemented through a COMPANY_CODE column present on most key tables. What's underdocumented is that the company code structure is not uniform across table families.
Specifically: location tables (LOCATION_MASTER, ZONE_MASTER) are often shared across clients with no company code filtering. Item master tables (ITEM_MASTER, ITEM_FACILITY) use company code. Transaction tables use company code. If you join between a location table and a transaction table without understanding which tables are client-scoped and which are facility-scoped, you get incorrect row multiplication.
The safe approach: always validate company code presence and filter consistency before assuming a join is clean. In 3PL configurations serving 8+ clients from one instance, incorrect company code handling produces data that looks correct (row counts are plausible) but is actually contaminated with cross-client records.
Active WMS uses both SHIPMENT and LOAD as first-class entities, and the distinction matters for how you structure extraction queries for TMS integration.
A SHIPMENT in Manhattan represents an order shipment — a set of inventory allocated to fulfill a specific order for a specific customer destination. A LOAD represents a physical carrier load event — potentially grouping multiple shipments for efficiency. One carrier trailer may contain shipments for multiple customers at multiple stops.
The SHIPMENT_DETAIL table joins to LOAD_DETAIL via LOAD_NBR. If you're building a TMS integration that tracks freight by load (as most carrier EDI feeds do), you need to aggregate shipment-level data up to the load level. If you're tracking by order for customer service purposes, you stay at the shipment level. The mistake is conflating the two — using load-level tables to answer shipment-level questions or vice versa — which produces counts that don't match what the warehouse operations team sees in their WMS screens.
Most data engineers building incremental loads from Active WMS rely on MODIFIED_DATE (or LAST_UPDATED_DTTM depending on table version) as the watermark field. There is a known behavior in Active WMS where batch processes that update records in bulk — inventory adjustments, cycle count posting, load confirmation — do not consistently update MODIFIED_DATE on all affected rows.
The specific pattern: when a load confirmation posts, the SHIPMENT_DETAIL rows are updated correctly. The related INVENTORY_SUMMARY rows may not have their MODIFIED_DATE updated if the inventory adjustment was processed by a background batch process that bypasses the normal update triggers. This means incremental loads on INVENTORY_SUMMARY watermarked by MODIFIED_DATE can miss records that were actually changed by load confirmation.
As discussed in our article on watermark-based incremental loads in WMS pipelines, this is a broader pattern across WMS systems — but Active WMS has some specific batch processes where it's particularly pronounced. For INVENTORY_SUMMARY, a full-refresh on a configurable schedule (every 2-4 hours during operational hours) is often more reliable than watermark-based incrementals.
Manhattan Associates provides an API layer (Web Services) for some integration scenarios. For data pipeline work, the choice between API access and direct database access has significant implications. The API layer provides well-documented, version-stable access to a subset of data — primarily focused on outbound transactions for order and shipment confirmations.
For analytical data pipelines that need historical records, custom aggregations, or access to data not exposed via the API (cycle count history, task performance records, audit trails), direct database access is typically necessary. Manhattan's SQL Server or Oracle database is well-structured and reasonably queryable once you understand the schema conventions described above.
The practical tradeoff: API access is more portable across WMS upgrades (the API contract is maintained between versions) but has data coverage gaps. Direct database access has comprehensive coverage but requires schema knowledge maintenance when upgrades occur — which brings us back to the drift monitoring practices described in our article on WMS schema drift.
Manhattan Associates Active WMS is a capable, enterprise-grade system with a well-designed data model for operational use. For data extraction and pipeline integration, it rewards careful study of the actual schema rather than assumptions based on documentation or naming conventions. The six areas covered here — LPN/HU mapping, pick task state handling, the inventory snapshot misnomer, company code filtering, shipment/load distinction, and MODIFIED_DATE reliability — represent the recurring friction points in Active WMS integrations.
Addressing these early in an integration project typically prevents the category of data quality issues that take months to surface and days to diagnose.
MLPipeLab's Manhattan Associates connector handles LPN/HU mapping, company code filtering, and the MODIFIED_DATE gap automatically. Request a demo to see it running against your Active WMS configuration.