237 lines
10 KiB
Markdown
237 lines
10 KiB
Markdown
# 库存余额、批次、流水、调整、盘点
|
||
|
||
约定见 [00-conventions.md](./00-conventions.md)。数量桶的业务含义见 [01-er-overview.md](./01-er-overview.md) 与 [05-inventory.md](../05-inventory.md)。Phase 1 锁定，Phase 7、8 写迁移。
|
||
|
||
冻结、锁定不单独建单据表。冻结是可用与 `hold_qty` 的对转；订单锁定是可用与 `reserved_qty` 的对转，锁定对象记在 `wms_outbound_allocation` 和流水的 `biz_no` 上。
|
||
|
||
## wms_batch 批次
|
||
|
||
用途：批次属性。不管理批次的库存使用 `batch_no = ''`，不写本表。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| customer_code | VARCHAR(64) | N | | 货主 |
|
||
| sku_code | VARCHAR(64) | N | | SKU 编码 |
|
||
| batch_no | VARCHAR(64) | N | | 批次号，禁止空字符串 |
|
||
| supplier_code | VARCHAR(64) | Y | | 供应商 |
|
||
| mfg_date | DATE | Y | | 生产日期 |
|
||
| expiry_date | DATE | Y | | 失效日期 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_customer_sku_batch | UNIQUE | (tenant_code, customer_code, sku_code, batch_no) | 定位批次 |
|
||
| idx_tenant_sku_expiry | INDEX | (tenant_code, sku_code, expiry_date) | 临期查询 |
|
||
|
||
关联：`wms_inventory.batch_no`。首次收货写入，之后只允许补空缺的日期，不改已有批次号。
|
||
|
||
并发 / 审计：无软删除。属性变更写 `wms_operation_log`。
|
||
|
||
## wms_inventory 库存余额
|
||
|
||
用途：某一货主、仓库、库位、SKU、批次上的数量结果。余额是结果，不是事实。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| customer_code | VARCHAR(64) | N | | 货主 |
|
||
| warehouse_code | VARCHAR(32) | N | | 仓库编码 |
|
||
| location_code | VARCHAR(64) | N | '' | 库位；在途为空字符串 |
|
||
| sku_code | VARCHAR(64) | N | | SKU 编码 |
|
||
| batch_no | VARCHAR(64) | N | '' | 批次；无批次为空字符串 |
|
||
| available_qty | DECIMAL(16,4) | N | 0 | 可用 |
|
||
| reserved_qty | DECIMAL(16,4) | N | 0 | 预占 |
|
||
| picked_qty | DECIMAL(16,4) | N | 0 | 已拣 |
|
||
| hold_qty | DECIMAL(16,4) | N | 0 | 冻结 |
|
||
| damaged_qty | DECIMAL(16,4) | N | 0 | 残损 |
|
||
| in_transit_qty | DECIMAL(16,4) | N | 0 | 在途 |
|
||
| version | INT UNSIGNED | N | 0 | 乐观锁，随数量更新递增 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_inventory_grain | UNIQUE | (tenant_code, customer_code, warehouse_code, location_code, sku_code, batch_no) | 定位余额行 |
|
||
| idx_tenant_wh_sku | INDEX | (tenant_code, warehouse_code, sku_code) | 仓库内某 SKU 库存 |
|
||
| idx_tenant_customer_sku | INDEX | (tenant_code, customer_code, sku_code) | 货主下某 SKU 库存 |
|
||
|
||
关联：维度对应仓库、库位、SKU、批次。在途行的 `location_code` 不引用 `wms_location`。
|
||
|
||
并发 / 审计：
|
||
|
||
- 禁止先查出数量再在 PHP 里相减后回写。
|
||
- 扣减使用单条条件更新，例如可用转预占：
|
||
|
||
```sql
|
||
UPDATE wms_inventory
|
||
SET available_qty = available_qty - :qty,
|
||
reserved_qty = reserved_qty + :qty,
|
||
version = version + 1
|
||
WHERE tenant_code = :tenant_code
|
||
AND id = :id
|
||
AND available_qty >= :qty;
|
||
```
|
||
|
||
- 受影响行数为 0：库存不足或版本冲突，抛业务异常，整单事务回滚。
|
||
- 同一事务插入对应的 `wms_stock_log`。
|
||
- 各数量桶不得为负。不软删除；数量全为 0 的行保留，避免并发下反复插入同一粒度。
|
||
|
||
## wms_stock_log 库存流水
|
||
|
||
用途：库存变化的事实。任何余额变化都要有流水。只追加。
|
||
|
||
一次业务动作若改变多个数量桶或多行余额，每个桶一行，共用同一个 `movement_no`。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| movement_no | VARCHAR(64) | N | | 同一次动作的组号 |
|
||
| customer_code | VARCHAR(64) | N | | 货主 |
|
||
| warehouse_code | VARCHAR(32) | N | | 仓库 |
|
||
| location_code | VARCHAR(64) | N | '' | 库位 |
|
||
| sku_code | VARCHAR(64) | N | | SKU |
|
||
| batch_no | VARCHAR(64) | N | '' | 批次 |
|
||
| qty_bucket | VARCHAR(32) | N | | `available`、`reserved`、`picked`、`hold`、`damaged`、`in_transit` |
|
||
| qty_before | DECIMAL(16,4) | N | | 该桶变更前 |
|
||
| qty_delta | DECIMAL(16,4) | N | | 有符号变化量 |
|
||
| qty_after | DECIMAL(16,4) | N | | 该桶变更后 |
|
||
| biz_type | VARCHAR(32) | N | | 业务类型 |
|
||
| biz_no | VARCHAR(64) | N | | 业务单号 |
|
||
| biz_line_no | INT UNSIGNED | Y | | 单据行号 |
|
||
| operator_user_code | VARCHAR(64) | Y | | 操作人 |
|
||
| request_id | VARCHAR(64) | Y | | 请求标识 |
|
||
| created_at | TIMESTAMP | Y | | 发生时间 |
|
||
| updated_at | TIMESTAMP | Y | | 行创建后不更新 |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| idx_tenant_sku_created | INDEX | (tenant_code, sku_code, created_at) | 某 SKU 的库存为何变化 |
|
||
| idx_tenant_biz | INDEX | (tenant_code, biz_type, biz_no) | 某单据的流水 |
|
||
| idx_tenant_movement | INDEX | (tenant_code, movement_no) | 同一次动作的各行 |
|
||
|
||
`biz_type`：`inbound_confirm`、`inbound_receive`、`inbound_putaway`、`reserve`、`release`、`pick`、`ship`、`hold`、`unhold`、`damage`、`transfer`、`adjust`、`stocktake`。
|
||
|
||
关联：`biz_no` 指向入库单、出库单、调整单或盘点单。
|
||
|
||
并发 / 审计：禁止更新和删除。`qty_after = qty_before + qty_delta`，且 `qty_after` 等于同一事务中余额桶的新值。
|
||
|
||
## wms_stock_adjust 库存调整单
|
||
|
||
用途：人工调整的单据头。确认时才改余额并写流水。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| adjust_no | VARCHAR(64) | N | | 调整单号 |
|
||
| warehouse_code | VARCHAR(32) | N | | 仓库 |
|
||
| customer_code | VARCHAR(64) | N | | 货主 |
|
||
| status | VARCHAR(32) | N | draft | 状态 |
|
||
| reason | VARCHAR(500) | N | | 原因 |
|
||
| version | INT UNSIGNED | N | 0 | 乐观锁 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_adjust_no | UNIQUE | (tenant_code, adjust_no) | 按单号查找 |
|
||
| idx_tenant_status_created | INDEX | (tenant_code, status, created_at) | 列表 |
|
||
|
||
状态：`draft → confirmed`，`draft → cancelled`。`confirmed`、`cancelled` 为终态。
|
||
|
||
确认时按明细对指定数量桶做原子增减，`biz_type = adjust`。减少时受影响行数为 0 则整单回滚。状态变更写 `wms_status_log`。不软删除。
|
||
|
||
## wms_stock_adjust_line 调整明细
|
||
|
||
用途：调整单的一行，只改一个数量桶。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| adjust_no | VARCHAR(64) | N | | 调整单号 |
|
||
| line_no | INT UNSIGNED | N | | 行号，单内从 1 递增 |
|
||
| location_code | VARCHAR(64) | N | '' | 库位 |
|
||
| sku_code | VARCHAR(64) | N | | SKU |
|
||
| batch_no | VARCHAR(64) | N | '' | 批次 |
|
||
| qty_bucket | VARCHAR(32) | N | | 数量桶 |
|
||
| qty_delta | DECIMAL(16,4) | N | | 有符号数量，禁止 0 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_adjust_line | UNIQUE | (tenant_code, adjust_no, line_no) | 定位明细 |
|
||
|
||
关联：头表 `wms_stock_adjust`。确认后明细不再修改。
|
||
|
||
## wms_stocktake 盘点单
|
||
|
||
用途：盘点单据头。确认时把账面与实盘的差额写成流水，`biz_type = stocktake`。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| stocktake_no | VARCHAR(64) | N | | 盘点单号 |
|
||
| warehouse_code | VARCHAR(32) | N | | 仓库 |
|
||
| status | VARCHAR(32) | N | draft | 状态 |
|
||
| version | INT UNSIGNED | N | 0 | 乐观锁 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_stocktake_no | UNIQUE | (tenant_code, stocktake_no) | 按单号查找 |
|
||
| idx_tenant_status_created | INDEX | (tenant_code, status, created_at) | 列表 |
|
||
|
||
状态：`draft → counting → confirmed`，`draft` 或 `counting` 可到 `cancelled`。`confirmed`、`cancelled` 为终态。
|
||
|
||
盘点期间不锁死全仓。确认时按当时账面重算差额；若并发导致目标桶不足，确认失败并回滚。不软删除。
|
||
|
||
## wms_stocktake_line 盘点明细
|
||
|
||
用途：一个库位、SKU、批次、数量桶上的账面与实盘。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| stocktake_no | VARCHAR(64) | N | | 盘点单号 |
|
||
| line_no | INT UNSIGNED | N | | 行号 |
|
||
| customer_code | VARCHAR(64) | N | | 货主 |
|
||
| location_code | VARCHAR(64) | N | | 库位 |
|
||
| sku_code | VARCHAR(64) | N | | SKU |
|
||
| batch_no | VARCHAR(64) | N | '' | 批次 |
|
||
| qty_bucket | VARCHAR(32) | N | available | 盘点的数量桶 |
|
||
| system_qty | DECIMAL(16,4) | N | | 生成明细时的账面 |
|
||
| counted_qty | DECIMAL(16,4) | Y | | 实盘，未盘为空 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_stocktake_line | UNIQUE | (tenant_code, stocktake_no, line_no) | 定位明细 |
|
||
| idx_tenant_stocktake_loc | INDEX | (tenant_code, stocktake_no, location_code) | 按库位录入 |
|
||
|
||
关联：头表 `wms_stocktake`。确认时差额为 `counted_qty - 当前账面`，不以生成时的 `system_qty` 作为扣减依据。`system_qty` 只说明生成时刻的账面。
|