whse_inventory

source

Live stock positions, by lot.

Definition

One row per lot with SKU, site, denormalized client owner, lot code, on-hand/allocated quantities, storage zone, bin location, received date, lot expiry (null for non-perishable), inventory status, and snapshot unit cost. Feeds int_whse__inventory_valued → mart_inventory_health. `client_id` is denormalized from the product owner so the row policy has a direct column.

13 of 13

Column

Description

Tests · PII

lot_id

UUID5 — primary key on the source side. Stable across re-syncs.

2 tests

sku

The SKU stocked in this lot. String on the wire; coerced and validated against `whse_products` in staging.

site_id

The site holding this lot. String on the wire; coerced and validated against `whse_sites` in staging.

client_id

Denormalized owning client (from the product's owner), carried for the row policy. An INC-22 integrity test asserts equality to the product owner.

client_contact +1 PII

lot_code

Human lot/batch code. Free-text.

qty_on_hand

Units on hand. Coerced to Int32 in staging.

qty_allocated

Units allocated (≤ qty_on_hand). Coerced to Int32 in staging.

zone

Bin zone — `ambient`, `chilled`, `frozen`, `hazmat`, `bulk` (correlates with product temp/hazard class).

bin_location

Bin location, e.g. `A-12-04`. Free-text.

received_date

ISO-8601 date the lot was received. Coerced to Date in staging. Source of `inv_month` downstream.

lot_expiry

ISO-8601 lot expiry date. Coerced to Nullable(Date); null for non-perishable SKUs.

inventory_status

Inventory status — `available`, `allocated`, `quarantine`, `damaged`, `expired`.

unit_cost_eur

Snapshot unit cost in EUR (≈ product unit_value_eur). Coerced to Decimal(14,2) in staging.