-- =============================================
|
-- 仓储物流模块数据库初始化脚本
|
-- 包含：仓库审批配置表、单据BPM字段扩展、BPM分类
|
-- =============================================
|
|
-- 1. 创建仓库审批配置表
|
CREATE TABLE IF NOT EXISTS wms_warehouse_approval_config (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
|
warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
|
biz_type VARCHAR(32) NOT NULL COMMENT '业务类型(INBOUND-入库/OUTBOUND-出库/TRANSFER-调拨/CHECK-盘点)',
|
approval_enabled TINYINT(1) DEFAULT 0 COMMENT '是否启用审批(0-否 1-是)',
|
process_definition_key VARCHAR(64) DEFAULT NULL COMMENT '流程定义Key(为空则使用分类默认)',
|
category_code VARCHAR(64) DEFAULT 'wms_approve' COMMENT 'BPM分类编码',
|
creator VARCHAR(64) DEFAULT '' COMMENT '创建者',
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updater VARCHAR(64) DEFAULT '' COMMENT '更新者',
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted BIT DEFAULT 0 COMMENT '是否删除',
|
INDEX idx_warehouse_biz (warehouse_id, biz_type)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='仓库审批配置表';
|
|
-- 2. 为出入库单据增加BPM审批字段
|
|
-- 采购入库单
|
ALTER TABLE mes_wm_item_receipt
|
ADD COLUMN IF NOT EXISTS process_instance_id VARCHAR(64) DEFAULT NULL COMMENT '流程实例ID' AFTER remark,
|
ADD COLUMN IF NOT EXISTS process_definition_key VARCHAR(64) DEFAULT NULL COMMENT '流程定义Key' AFTER process_instance_id;
|
|
-- 销售出库单
|
ALTER TABLE mes_wm_product_sales
|
ADD COLUMN IF NOT EXISTS process_instance_id VARCHAR(64) DEFAULT NULL COMMENT '流程实例ID' AFTER remark,
|
ADD COLUMN IF NOT EXISTS process_definition_key VARCHAR(64) DEFAULT NULL COMMENT '流程定义Key' AFTER process_instance_id;
|
|
-- 生产领料单
|
ALTER TABLE mes_wm_product_issue
|
ADD COLUMN IF NOT EXISTS process_instance_id VARCHAR(64) DEFAULT NULL COMMENT '流程实例ID' AFTER remark,
|
ADD COLUMN IF NOT EXISTS process_definition_key VARCHAR(64) DEFAULT NULL COMMENT '流程定义Key' AFTER process_instance_id;
|
|
-- 生产入库单（产品收货）
|
ALTER TABLE mes_wm_product_receipt
|
ADD COLUMN IF NOT EXISTS process_instance_id VARCHAR(64) DEFAULT NULL COMMENT '流程实例ID' AFTER remark,
|
ADD COLUMN IF NOT EXISTS process_definition_key VARCHAR(64) DEFAULT NULL COMMENT '流程定义Key' AFTER process_instance_id;
|
|
-- 转移调拨单
|
ALTER TABLE mes_wm_transfer
|
ADD COLUMN IF NOT EXISTS process_instance_id VARCHAR(64) DEFAULT NULL COMMENT '流程实例ID' AFTER remark,
|
ADD COLUMN IF NOT EXISTS process_definition_key VARCHAR(64) DEFAULT NULL COMMENT '流程定义Key' AFTER process_instance_id;
|
|
-- 盘点方案
|
ALTER TABLE mes_wm_stock_taking_plan
|
ADD COLUMN IF NOT EXISTS process_instance_id VARCHAR(64) DEFAULT NULL COMMENT '流程实例ID' AFTER remark,
|
ADD COLUMN IF NOT EXISTS process_definition_key VARCHAR(64) DEFAULT NULL COMMENT '流程定义Key' AFTER process_instance_id;
|
|
-- 杂项入库单
|
ALTER TABLE mes_wm_misc_receipt
|
ADD COLUMN IF NOT EXISTS process_instance_id VARCHAR(64) DEFAULT NULL COMMENT '流程实例ID' AFTER remark,
|
ADD COLUMN IF NOT EXISTS process_definition_key VARCHAR(64) DEFAULT NULL COMMENT '流程定义Key' AFTER process_instance_id;
|
|
-- 杂项出库单
|
ALTER TABLE mes_wm_misc_issue
|
ADD COLUMN IF NOT EXISTS process_instance_id VARCHAR(64) DEFAULT NULL COMMENT '流程实例ID' AFTER remark,
|
ADD COLUMN IF NOT EXISTS process_definition_key VARCHAR(64) DEFAULT NULL COMMENT '流程定义Key' AFTER process_instance_id;
|
|
-- 3. 创建BPM审批分类
|
INSERT INTO bpm_category (id, name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT 120, '仓库审核', 'wms_approve', '仓库管理审批流程分类', 0, 30, '1', NOW(), '1', NOW(), 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'wms_approve');
|
|
-- =============================================
|
-- 业务类型说明：
|
-- INBOUND - 入库类业务（采购入库、生产入库、销售退货等）
|
-- OUTBOUND - 出库类业务（销售出库、生产领料、采购退货等）
|
-- TRANSFER - 调拨业务（内部调拨、外部调拨）
|
-- CHECK - 盘点业务（静态盘点、动态盘点）
|
-- =============================================
|
