58 lines
2.6 KiB
SQL
58 lines
2.6 KiB
SQL
-- =============================================
|
||
-- 功能：领料单库存快照功能
|
||
-- 版本：v1.6.037
|
||
-- 日期：2026-02-04
|
||
-- 作者：周启威
|
||
-- 说明：为领料单明细表添加库存快照相关字段，用于记录领料时的库存状态
|
||
-- =============================================
|
||
|
||
-- 1. 为领料单明细表添加库存快照相关字段
|
||
ALTER TABLE wm_production_pick_entry
|
||
ADD COLUMN inventory_snapshot DECIMAL(18,6) DEFAULT NULL COMMENT '领料时库存快照',
|
||
ADD COLUMN snapshot_time DATETIME DEFAULT NULL COMMENT '快照记录时间',
|
||
ADD COLUMN snapshot_warehouse_id BIGINT DEFAULT NULL COMMENT '快照来源仓库ID',
|
||
ADD COLUMN snapshot_batch_number VARCHAR(100) DEFAULT NULL COMMENT '快照来源批次号';
|
||
|
||
-- 2. 添加索引以优化查询性能
|
||
-- 用于快速查询指定物料的最新库存快照
|
||
CREATE INDEX idx_material_snapshot_time
|
||
ON wm_production_pick_entry(material_id, snapshot_time DESC);
|
||
|
||
-- 3. 添加字段说明注释
|
||
ALTER TABLE wm_production_pick_entry
|
||
MODIFY COLUMN inventory_snapshot DECIMAL(18,6) DEFAULT NULL COMMENT '领料时库存快照（记录领料时该物料的实时库存总量）',
|
||
MODIFY COLUMN snapshot_time DATETIME DEFAULT NULL COMMENT '快照记录时间（领料单创建时间）',
|
||
MODIFY COLUMN snapshot_warehouse_id BIGINT DEFAULT NULL COMMENT '快照来源仓库ID（领料单关联的仓库）',
|
||
MODIFY COLUMN snapshot_batch_number VARCHAR(100) DEFAULT NULL COMMENT '快照来源批次号（如有多个批次，记录第一个）';
|
||
|
||
-- 4. 验证字段是否添加成功
|
||
SELECT
|
||
COLUMN_NAME AS '字段名',
|
||
COLUMN_TYPE AS '字段类型',
|
||
IS_NULLABLE AS '是否可空',
|
||
COLUMN_DEFAULT AS '默认值',
|
||
COLUMN_COMMENT AS '字段说明'
|
||
FROM
|
||
INFORMATION_SCHEMA.COLUMNS
|
||
WHERE
|
||
TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'wm_production_pick_entry'
|
||
AND COLUMN_NAME IN ('inventory_snapshot', 'snapshot_time', 'snapshot_warehouse_id', 'snapshot_batch_number')
|
||
ORDER BY
|
||
ORDINAL_POSITION;
|
||
|
||
-- 5. 验证索引是否创建成功
|
||
SHOW INDEX FROM wm_production_pick_entry WHERE Key_name = 'idx_material_snapshot_time';
|
||
|
||
-- =============================================
|
||
-- 回滚脚本（如需回滚，请执行以下语句）
|
||
-- =============================================
|
||
-- ALTER TABLE wm_production_pick_entry
|
||
-- DROP COLUMN inventory_snapshot,
|
||
-- DROP COLUMN snapshot_time,
|
||
-- DROP COLUMN snapshot_warehouse_id,
|
||
-- DROP COLUMN snapshot_batch_number;
|
||
--
|
||
-- DROP INDEX idx_material_snapshot_time ON wms_pro_pick_entry;
|
||
-- =============================================
|