ERP生产模块设计：从BOM到完工

一、基础数据BOM与工艺路线生产模块的核心是BOM物料清单和工艺路线。这两个搞不清楚生产计划无从谈起。1. BOM表结构CREATE TABLE bd_bom ( id BIGINT PRIMARY KEY AUTO_INCREMENT, bom_no VARCHAR(30) NOT NULL UNIQUE, material_id BIGINT NOT NULL, -- 成品 version VARCHAR(10) DEFAULT 1.0, -- 基本信息 bom_type VARCHAR(20), -- STANDARD/ENGINEERING effective_date DATE, expire_date DATE, -- 用量汇总 total_material_cost DECIMAL(18,2), status VARCHAR(10) DEFAULT ACTIVE, created_time DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_material_version (material_id, version), INDEX idx_material (material_id) ); CREATE TABLE bd_bom_component ( id BIGINT PRIMARY KEY AUTO_INCREMENT, bom_id BIGINT NOT NULL, line_no INT NOT NULL, -- 子件 material_id BIGINT NOT NULL, -- 用量 quantity DECIMAL(18,6) NOT NULL, -- 基础用量 base_quantity DECIMAL(18,6) DEFAULT 1,-- 成品基数 usage_rate DECIMAL(18,6) GENERATED ALWAYS AS (quantity / base_quantity) STORED, -- 损耗 scrap_rate DECIMAL(5,2) DEFAULT 0, actual_usage DECIMAL(18,6) GENERATED ALWAYS AS (quantity / base_quantity * (1 scrap_rate / 100)) STORED, -- 替代件 substitute_group VARCHAR(20), substitute_priority INT, -- 工序 operation_seq INT, INDEX idx_bom (bom_id), INDEX idx_material (material_id) );usage_rate单位用量。如果生产100个成品需要50个零件quantity50base_quantity100usage_rate0.5。scrap_rate损耗率。考虑损耗后的实际需求量 标准用量 × (1 损耗率)。2. 工艺路线CREATE TABLE bd_routing ( id BIGINT PRIMARY KEY AUTO_INCREMENT, routing_no VARCHAR(30) NOT NULL UNIQUE, material_id BIGINT NOT NULL, version VARCHAR(10) DEFAULT 1.0, status VARCHAR(10) DEFAULT ACTIVE, created_time DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE bd_routing_operation ( id BIGINT PRIMARY KEY AUTO_INCREMENT, routing_id BIGINT NOT NULL, operation_seq INT NOT NULL, -- 工序号 operation_code VARCHAR(20) NOT NULL, -- 工序代码 operation_name VARCHAR(50) NOT NULL, -- 工作中心 work_center_id BIGINT NOT NULL, -- 时间标准 setup_time DECIMAL(10,2), -- 准备时间小时 run_time DECIMAL(10,2), -- 单件加工时间小时 wait_time DECIMAL(10,2), -- 等待时间小时 move_time DECIMAL(10,2), -- 移动时间小时 -- 人员 crew_size INT DEFAULT 1, -- 成本 labor_cost DECIMAL(18,6), machine_cost DECIMAL(18,6), overhead_cost DECIMAL(18,6), INDEX idx_routing (routing_id) );二、生产订单与领料1. 生产订单CREATE TABLE mo_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, mo_no VARCHAR(30) NOT NULL UNIQUE, mo_date DATE NOT NULL, mo_type VARCHAR(20), -- NORMAL/REWORK/TEAR_DOWN -- 产品 material_id BIGINT NOT NULL, bom_id BIGINT, routing_id BIGINT, -- 数量 quantity DECIMAL(18,6) NOT NULL, completed_quantity DECIMAL(18,6) DEFAULT 0, scrapped_quantity DECIMAL(18,6) DEFAULT 0, -- 日期 plan_start_date DATE, plan_end_date DATE, actual_start_date DATE, actual_end_date DATE, -- 状态 status VARCHAR(10) DEFAULT DRAFT, -- DRAFT/RELEASED/STARTED/COMPLETED/CLOSED created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_material (material_id), INDEX idx_status (status) );2. 领料单CREATE TABLE mo_material_requisition ( id BIGINT PRIMARY KEY AUTO_INCREMENT, requisition_no VARCHAR(30) NOT NULL UNIQUE, requisition_date DATE NOT NULL, mo_id BIGINT NOT NULL, warehouse_id BIGINT NOT NULL, status VARCHAR(10) DEFAULT DRAFT, created_time DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE mo_material_requisition_line ( id BIGINT PRIMARY KEY AUTO_INCREMENT, requisition_id BIGINT NOT NULL, material_id BIGINT NOT NULL, batch_no VARCHAR(50), required_quantity DECIMAL(18,6), -- 需求数量 actual_quantity DECIMAL(18,6), -- 实领数量 warehouse_id BIGINT NOT NULL, location_id BIGINT, INDEX idx_requisition (requisition_id) );3. 领料审核扣减库存CREATE PROCEDURE sp_mo_requisition_approve(IN p_requisition_id BIGINT) BEGIN -- 扣减库存 UPDATE inv_stock s JOIN mo_material_requisition_line l ON s.material_id l.material_id AND s.warehouse_id l.warehouse_id SET s.quantity s.quantity - l.actual_quantity WHERE l.requisition_id p_requisition_id; -- 记录流水 INSERT INTO inv_transaction (transaction_type, warehouse_id, material_id, quantity) SELECT OUT_PRODUCTION, warehouse_id, material_id, -actual_quantity FROM mo_material_requisition_line WHERE requisition_id p_requisition_id; UPDATE mo_material_requisition SET status APPROVED WHERE id p_requisition_id; END;三、完工入库与成本核算1. 完工报告CREATE TABLE mo_completion ( id BIGINT PRIMARY KEY AUTO_INCREMENT, completion_no VARCHAR(30) NOT NULL UNIQUE, completion_date DATE NOT NULL, mo_id BIGINT NOT NULL, -- 完工数量 completed_quantity DECIMAL(18,6) NOT NULL, scrapped_quantity DECIMAL(18,6) DEFAULT 0, -- 入库仓库 warehouse_id BIGINT NOT NULL, -- 成本 material_cost DECIMAL(18,2), labor_cost DECIMAL(18,2), overhead_cost DECIMAL(18,2), total_cost DECIMAL(18,2), status VARCHAR(10) DEFAULT DRAFT, created_time DATETIME DEFAULT CURRENT_TIMESTAMP );2. 完工审核增加库存CREATE PROCEDURE sp_mo_completion_approve(IN p_completion_id BIGINT) BEGIN DECLARE v_mo_id BIGINT; DECLARE v_material_id BIGINT; DECLARE v_quantity DECIMAL(18,6); DECLARE v_cost DECIMAL(18,6); DECLARE v_warehouse_id BIGINT; -- 获取完工信息 SELECT mo_id, completed_quantity, total_cost, warehouse_id INTO v_mo_id, v_quantity, v_cost, v_warehouse_id FROM mo_completion WHERE id p_completion_id; -- 获取产品ID SELECT material_id INTO v_material_id FROM mo_order WHERE id v_mo_id; -- 计算单位成本 SET v_cost v_cost / v_quantity; -- 增加库存 INSERT INTO inv_stock (warehouse_id, material_id, quantity, cost) VALUES (v_warehouse_id, v_material_id, v_quantity, v_cost) ON DUPLICATE KEY UPDATE quantity quantity v_quantity, cost (cost * quantity v_cost * v_quantity) / (quantity v_quantity); -- 记录流水 INSERT INTO inv_transaction (transaction_type, warehouse_id, material_id, quantity, cost) VALUES (IN_PRODUCTION, v_warehouse_id, v_material_id, v_quantity, v_cost); -- 更新生产订单完工数量 UPDATE mo_order SET completed_quantity completed_quantity v_quantity WHERE id v_mo_id; UPDATE mo_completion SET status APPROVED WHERE id p_completion_id; END;四、生产成本核算逻辑1. 材料成本领料时按实际领用量 × 领料单价计算。如果采用加权平均法领料单价取当前库存平均成本。2. 人工成本按工序报工汇总人工工时 × 工作中心人工费率。3. 制造费用按工时分摊或按产量分摊或按机器工时分摊。不同企业分摊方法不同。4. 成本差异标准成本 vs 实际成本。差异分析是成本控制的基础。成都云策数链科技有限公司 | 用友四川授权服务中心 | 专注企业数字化转型