Skip to content

Inventory By Status

Table name:

  • referenced in Insights SQL queries: "INVENTORY_BY_STATUS"
  • in data catalog export: marts_mdc__inventory_by_status

Current SKU-lot inventory quantities per warehouse split by inventory status (Sellable, Quarantined, Damaged, Unavailable) and the reason for that status. Statuses follow the public API product inventory definitions: Sellable + Quarantined per SKU and warehouse equals the API on-hand plus quarantined quantity. Sellable units claimed by open orders and not picked yet are reported in a separate Sellable row with the reason "Allocated to orders"; per SKU and warehouse the Sellable rows without reason equal the API available quantity and the allocated rows equal the API allocated quantity. An order claims a SKU in a warehouse, not a lot, so the allocated units are assigned to the lots in expiration order (the picking order), each lot taking at most its sellable quantity. Units already picked for orders but not yet shipped, and units under inventory adjustment research, are not included.

Columns

Column Type Description
warehouse_id number A unique identifier of the warehouse.
warehouse_name varchar Name of the warehouse where the inventory is stored.
account_id number Unique identifier of the account owning the inventory
account_name varchar Name of the account owning the inventory
product_id number A unique identifier of the product.
sku varchar SKU of the product
product_name varchar Name of the product
product_type varchar Type of the product, one of pick_and_pack, insert, packaging
lot_number varchar Lot number of the inventory. Null for inventory without a lot.
lot_expiration_date date Expiration date of the inventory lot. Format: YYYY-MM-DD.
inventory_status varchar Merchant-facing status of the inventory.
Sellable: in an available location and not held; available to orders, or already allocated to orders (see inventory_status_reason)
Quarantined: held by a quarantine, expiration, audit or ASN inventory hold, or stored in a quarantine location
Damaged: stored in a damaged inventory location or container
Unavailable: in a location that does not make inventory available (e.g. put-away staging, returns processing, dimensioning)
inventory_status_reason varchar Reason for the status, null for Sellable units available to orders.
Sellable: "Allocated to orders" for units claimed by open orders and not picked yet, assigned to the lots in expiration order
Quarantined: the hold category ("Quarantine hold", "Expiring inventory", "Audit hold", "ASN inventory hold, ASN ") for units held in place, or "Quarantine location, " for units in a quarantine location
Damaged: "Damaged inventory location"
Unavailable: "Storage category: ", the merchant storage category of the location
quantity number Inventory quantity in the given status per warehouse, SKU, lot and reason
refreshed_at_utc timestamp_tz UTC timestamp when the report data have been refreshed