nikita2206 · February 10, 2026 16:36 · Feb 10, 2026 · Feb 10, 2026
diff --git a/event_detail_investigation.md b/event_detail_investigation.md
@@ -62,7 +62,7 @@ The DV load runs on a cron: Sundays and Wednesdays at 22:20.
 | `Replenish Stock > Shelf Pick Location` | `Replenish Stock > License Plate > Shelf Pick Location` |
 | `Start Replenishment Round` | `Start Replenishment Round > License Plate` |
 
-The change means:
+The change could mean:
 - For `replenish_stock`: `event_raw:"fromLocation"` went from **NULL** to **populated** (with an LP barcode), causing `source_hu_id` to become non-null
 - For `start_replenishment_round`: `event_raw:"load_carrier_barcode"` went from **NULL** to **populated**, causing `target_hu_id` to become non-null
 

diff --git a/event_detail_investigation.md b/event_detail_investigation.md
@@ -0,0 +1,111 @@
+# Investigation: How `event_detail` changed in `ft_wms_events`
+
+## 1. How `event_detail` is built in `ft_wms_events`
+
+**Source → DWH data flow:**
+
+1. **WMS service** emits operational log events (e.g. `replenish_stock`, `start_replenishment_round`) with raw JSON fields like `fromLocation`, `toLocation`, `object`, `load_carrier_barcode`, etc.
+
+2. **`picnic_wms__operational_log_parsed`** (dbt evt model) normalizes these raw events into a unified JSON structure via a large `CASE WHEN event_name = ...` block. For each event type, it maps raw fields into standardized keys:
+   - `replenish_stock`: `source_hu_id` ← `event_raw:"fromLocation"`, `target_hu_id` ← `coalesce(event_raw:"toLocation", event_raw:"object")`
+   - `start_replenishment_round`: `target_hu_id` ← `event_raw:"load_carrier_barcode"`
+
+3. **`wms_events`** (dbt intgr model) reads from the parsed model and:
+   - Computes `h_source_hu_dv_hashkey` = `md5(site_id || '-' || source_hu_id)` and similarly for target
+   - Left joins to `dv.hs_handling_unit` on those hashkeys (filtered to latest version: `r_timestamp_end = '9999-12-31'`)
+   - Builds `event_detail` as:
+     ```sql
+     initcap(
+       replace(
+         event_type
+         || iff(source_hu_id is not null, ' > ' || coalesce(source_hshu.subtype, source_hshu.type_desc, 'Unknown'), '')
+         || iff(target_hu_id is not null, ' > ' || coalesce(target_hshu.subtype, target_hshu.type_desc, 'Unknown'), ''),
+         '_', ' '
+       )
+     )
+     ```
+
+**Formula:** `event_type [> source HU type] [> target HU type]`
+
+Each segment is only appended when the corresponding `source_hu_id` / `target_hu_id` is non-null. The type label comes from `coalesce(subtype, type_desc, 'Unknown')` in `hs_handling_unit`.
+
+---
+
+## 2. How `dv.hs_handling_unit` is built
+
+**Not** from the CSV export (`/admin/handlingunits`, which only exports `PL` and `AREA` types).
+
+Instead, the Data Vault domain `wms_handling_units` uses the **OpsController REST API**:
+
+1. `GET /api/1/ops/handling_units/types` — returns **all** `HandlingUnitType` enum values mapped to their display names (e.g. `LP → "License Plate"`, `PL → "Pick Location"`, `AREA → "Area"`)
+2. For **each** type, `GET /api/1/ops/handling_units?type={type}` — returns all handling units of that type
+
+The ETL code (`wms_handling_units/__init__.py`) iterates over all types and yields a `Datum` per handling unit, setting `type_desc` from the display name. This flows through:
+
+**API response** → **raw table** → **`flow.sql`** (staging) → **Data Vault** (`h_handling_unit` hub + `hs_handling_unit` satellite)
+
+For LP (License Plate) handling units:
+- `type` = `LP`
+- `type_desc` = `License Plate` (from `HandlingUnitType.LP.getDisplayName()`)
+- `subtype` = NULL (LicensePlate class has no subtype override)
+
+So `coalesce(subtype, type_desc, 'Unknown')` → `License Plate` for LP handling units.
+
+The DV load runs on a cron: Sundays and Wednesdays at 22:20.
+
+---
+
+## 3. What changed to produce the new `event_detail` values
+
+| Before (~2025-11-27) | After |
+|---|---|
+| `Replenish Stock > Shelf Pick Location` | `Replenish Stock > License Plate > Shelf Pick Location` |
+| `Start Replenishment Round` | `Start Replenishment Round > License Plate` |
+
+The change means:
+- For `replenish_stock`: `event_raw:"fromLocation"` went from **NULL** to **populated** (with an LP barcode), causing `source_hu_id` to become non-null
+- For `start_replenishment_round`: `event_raw:"load_carrier_barcode"` went from **NULL** to **populated**, causing `target_hu_id` to become non-null
+
+The code responsible is in `picnic-ws-replenishment`:
+- **`StockServiceImpl.replenishStockToPickLocation()`** — `.setFromLocation(stockIdentifier.locationPicnicId())` (commit `36914aea6`, June 2024)
+- **`RoundReplenishmentServiceImpl.startReplenishmentRound()`** — `.setLoadCarrierBarcode(round.assignedMobileLocationPicnicId())` (commit `d162948a8`, April 2024)
+
+---
+
+## 4. How to verify by querying raw events
+
+To confirm the root cause, query the raw operational log events around the change date:
+
+```sql
+select
+    event_raw:"fromLocation"::varchar as from_location,
+    min(event_ts) as first_seen,
+    max(event_ts) as last_seen,
+    count(*) as event_count
+from picnic_wms__operational_log
+where lower(event_raw:"action"::varchar) = 'replenish_stock'
+  and site_id = '<WAREHOUSE_ID>'
+  and event_ts between '2025-11-01' and '2025-12-15'
+group by 1
+order by 1;
+```
+
+If the hypothesis is correct, you should see:
+- `from_location = NULL` rows **before** the change date
+- `from_location = <LP barcode>` rows **after** the change date
+
+Similarly for `start_replenishment_round`:
+
+```sql
+select
+    event_raw:"load_carrier_barcode"::varchar as load_carrier_barcode,
+    min(event_ts) as first_seen,
+    max(event_ts) as last_seen,
+    count(*) as event_count
+from picnic_wms__operational_log
+where lower(event_raw:"action"::varchar) = 'start_replenishment_round'
+  and site_id = '<WAREHOUSE_ID>'
+  and event_ts between '2025-11-01' and '2025-12-15'
+group by 1
+order by 1;
+```
No results found