一、采购业务流程采购模块的典型流程请购 → 审批 → 询价 → 下单 → 到货 → 入库 → 开票 → 付款。每个环节都要留痕迹否则出了问题追溯不到。1. 请购单CREATE TABLE po_requisition ( id BIGINT PRIMARY KEY AUTO_INCREMENT, requisition_no VARCHAR(30) NOT NULL UNIQUE, requisition_date DATE NOT NULL, -- 需求部门 department_id BIGINT NOT NULL, requester_id BIGINT NOT NULL, -- 用途 purpose VARCHAR(200), -- 状态 status VARCHAR(10) DEFAULT DRAFT, -- DRAFT/SUBMITTED/APPROVED/REJECTED/CLOSED approve_time DATETIME, approve_user_id BIGINT, created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_department (department_id), INDEX idx_date (requisition_date) ); CREATE TABLE po_requisition_line ( id BIGINT PRIMARY KEY AUTO_INCREMENT, requisition_id BIGINT NOT NULL, line_no INT NOT NULL, material_id BIGINT NOT NULL, quantity DECIMAL(18,6) NOT NULL, unit_id BIGINT NOT NULL, need_date DATE, -- 需求日期 suggested_supplier_id BIGINT, -- 建议供应商 -- 后续关联 ordered_quantity DECIMAL(18,6) DEFAULT 0, remaining_quantity DECIMAL(18,6) GENERATED ALWAYS AS (quantity - ordered_quantity) STORED, memo VARCHAR(200), INDEX idx_requisition (requisition_id) );请购单的核心价值是”需求追溯”。生产部门提了采购需求采购部门下了单货什么时候到、到没到齐都要能查到。2. 采购订单CREATE TABLE po_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(30) NOT NULL UNIQUE, order_date DATE NOT NULL, -- 供应商 supplier_id BIGINT NOT NULL, supplier_code VARCHAR(20), supplier_name VARCHAR(100), -- 业务信息 buyer_id BIGINT, -- 采购员 department_id BIGINT, -- 价格条款 currency_code VARCHAR(3) DEFAULT CNY, exchange_rate DECIMAL(18,6) DEFAULT 1, payment_terms VARCHAR(20), -- 金额汇总 total_amount DECIMAL(18,2) DEFAULT 0, total_tax DECIMAL(18,2) DEFAULT 0, total_with_tax DECIMAL(18,2) DEFAULT 0, -- 状态 status VARCHAR(10) DEFAULT DRAFT, created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_supplier (supplier_id), INDEX idx_date (order_date) ); CREATE TABLE po_order_line ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT NOT NULL, line_no INT NOT NULL, -- 请购关联 requisition_line_id BIGINT, -- 物料信息 material_id BIGINT NOT NULL, material_code VARCHAR(30), material_name VARCHAR(100), -- 数量价格 quantity DECIMAL(18,6) NOT NULL, unit_id BIGINT NOT NULL, price DECIMAL(18,6) NOT NULL, tax_rate DECIMAL(5,2) DEFAULT 13.00, -- 金额 amount DECIMAL(18,2), tax_amount DECIMAL(18,2), total_amount DECIMAL(18,2), -- 交货 delivery_date DATE, warehouse_id BIGINT, -- 入库进度 received_quantity DECIMAL(18,6) DEFAULT 0, remaining_quantity DECIMAL(18,6) GENERATED ALWAYS AS (quantity - received_quantity) STORED, INDEX idx_order (order_id), INDEX idx_material (material_id) );received_quantity已入库数量。采购订单和入库单是多对多关系一张采购订单可以分多次入库一次入库也可以对应多张采购订单。二、采购入库与质检1. 到货登记CREATE TABLE po_arrival ( id BIGINT PRIMARY KEY AUTO_INCREMENT, arrival_no VARCHAR(30) NOT NULL UNIQUE, arrival_date DATE NOT NULL, supplier_id BIGINT NOT NULL, -- 状态 status VARCHAR(10) DEFAULT ARRIVED, -- ARRIVED/INSPECTING/QUALIFIED/REJECTED created_time DATETIME DEFAULT CURRENT_TIMESTAMP );到货登记是采购入库的前置环节。货到了先登记质检合格后再入库。2. 入库单CREATE TABLE po_receipt ( id BIGINT PRIMARY KEY AUTO_INCREMENT, receipt_no VARCHAR(30) NOT NULL UNIQUE, receipt_date DATE NOT NULL, -- 订单关联 order_id BIGINT, order_no VARCHAR(30), -- 供应商 supplier_id BIGINT NOT NULL, -- 仓库 warehouse_id BIGINT NOT NULL, -- 金额 total_quantity DECIMAL(18,6) DEFAULT 0, total_amount DECIMAL(18,2) DEFAULT 0, -- 状态 status VARCHAR(10) DEFAULT DRAFT, created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_order (order_id), INDEX idx_supplier (supplier_id) ); CREATE TABLE po_receipt_line ( id BIGINT PRIMARY KEY AUTO_INCREMENT, receipt_id BIGINT NOT NULL, line_no INT NOT NULL, -- 订单明细关联 order_line_id BIGINT, -- 物料 material_id BIGINT NOT NULL, batch_no VARCHAR(50), -- 数量 quantity DECIMAL(18,6) NOT NULL, unit_id BIGINT NOT NULL, -- 价格 price DECIMAL(18,6), total_amount DECIMAL(18,2), -- 库位 warehouse_id BIGINT NOT NULL, location_id BIGINT, INDEX idx_receipt (receipt_id) );3. 入库审核时的库存增加CREATE PROCEDURE sp_po_receipt_approve(IN p_receipt_id BIGINT) BEGIN -- 更新库存 UPDATE inv_stock s JOIN po_receipt_line l ON s.material_id l.material_id AND s.warehouse_id l.warehouse_id SET s.quantity s.quantity l.quantity WHERE l.receipt_id p_receipt_id; -- 记录流水 INSERT INTO inv_transaction (transaction_type, warehouse_id, material_id, quantity) SELECT IN_PURCHASE, warehouse_id, material_id, quantity FROM po_receipt_line WHERE receipt_id p_receipt_id; -- 更新订单明细的已入库数量 UPDATE po_order_line ol JOIN po_receipt_line rl ON ol.id rl.order_line_id SET ol.received_quantity ol.received_quantity rl.quantity WHERE rl.receipt_id p_receipt_id; -- 更新入库单状态 UPDATE po_receipt SET status APPROVED WHERE id p_receipt_id; END;三、采购价格管理1. 供应商价格表CREATE TABLE po_supplier_price ( id BIGINT PRIMARY KEY AUTO_INCREMENT, supplier_id BIGINT NOT NULL, material_id BIGINT NOT NULL, -- 价格信息 price DECIMAL(18,6) NOT NULL, currency_code VARCHAR(3) DEFAULT CNY, -- 有效期 valid_from DATE, valid_to DATE, -- 价格条件 min_quantity DECIMAL(18,6) DEFAULT 0, -- 起订量 lead_days INT, -- 交货周期 is_preferred BIT DEFAULT 0, -- 首选供应商 created_time DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_supplier_material (supplier_id, material_id, valid_from), INDEX idx_material (material_id) );2. 价格查询逻辑下单时自动取有效价格SELECT sp.*, s.supplier_name FROM po_supplier_price sp JOIN bd_supplier s ON sp.supplier_id s.id WHERE sp.material_id ? AND sp.valid_from CURDATE() AND (sp.valid_to IS NULL OR sp.valid_to CURDATE()) AND sp.min_quantity ? ORDER BY sp.is_preferred DESC, sp.price ASC LIMIT 1;首选供应商优先同条件下取最低价。成都云策数链科技有限公司 | 用友四川授权服务中心 | 专注企业数字化转型