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 Damaged: "Damaged inventory location" Unavailable: "Storage category: |
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 |