企业官网建设流程全解析

一、销售业务的核心表结构

销售模块涉及报价、订单、出库、开票、收款五个环节。每个环节对应一张主表和若干明细表。

1. 客户主数据

CREATE TABLE bd_customer ( id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_code VARCHAR(20) NOT NULL UNIQUE, customer_name VARCHAR(100) NOT NULL, short_name VARCHAR(50), -- 分类信息 customer_type VARCHAR(20), -- 企业/个人 industry_id BIGINT, level_id BIGINT, -- 客户等级 -- 联系信息 contact_person VARCHAR(50), contact_phone VARCHAR(20), contact_email VARCHAR(100), province VARCHAR(20), city VARCHAR(30), address VARCHAR(200), -- 财务信息 tax_no VARCHAR(30), -- 税号 bank_name VARCHAR(100), bank_account VARCHAR(30), -- 信用控制 credit_limit DECIMAL(18,2) DEFAULT 0, credit_used DECIMAL(18,2) DEFAULT 0, credit_available DECIMAL(18,2) GENERATED ALWAYS AS (credit_limit - credit_used) STORED, payment_terms VARCHAR(20), -- 付款条件 -- 销售信息 salesman_id BIGINT, -- 业务员 department_id BIGINT, -- 部门 price_list_id BIGINT, -- 价格表 status VARCHAR(10) DEFAULT 'ACTIVE', created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_name (customer_name), INDEX idx_salesman (salesman_id) );

credit_limit和credit_used：信用额度控制的核心字段。销售下单时，系统检查credit_available是否足够。开票后credit_used增加，收款后减少。

2. 销售报价单

CREATE TABLE sa_quotation ( id BIGINT PRIMARY KEY AUTO_INCREMENT, quotation_no VARCHAR(30) NOT NULL UNIQUE, quotation_date DATE NOT NULL, customer_id BIGINT NOT NULL, -- 报价信息 currency_code VARCHAR(3) DEFAULT 'CNY', exchange_rate DECIMAL(18,6) DEFAULT 1, tax_rate DECIMAL(5,2) DEFAULT 13.00, -- 金额汇总 total_amount DECIMAL(18,2) DEFAULT 0, total_tax DECIMAL(18,2) DEFAULT 0, total_with_tax DECIMAL(18,2) DEFAULT 0, -- 有效期 valid_from DATE, valid_to DATE, -- 状态 status VARCHAR(10) DEFAULT 'DRAFT', -- DRAFT/SUBMITTED/APPROVED/EXPIRED/CLOSED -- 审核信息 submit_time DATETIME, submit_user_id BIGINT, approve_time DATETIME, approve_user_id BIGINT, memo VARCHAR(500), created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_customer (customer_id), INDEX idx_date (quotation_date) ); CREATE TABLE sa_quotation_line ( id BIGINT PRIMARY KEY AUTO_INCREMENT, quotation_id BIGINT NOT NULL, line_no INT NOT NULL, material_id BIGINT NOT NULL, material_code VARCHAR(30), material_name VARCHAR(100), quantity DECIMAL(18,6) NOT NULL, unit_id BIGINT NOT NULL, unit_name VARCHAR(20), price DECIMAL(18,6) NOT NULL, tax_rate DECIMAL(5,2), discount_rate DECIMAL(5,2) DEFAULT 0, amount DECIMAL(18,2), tax_amount DECIMAL(18,2), total_amount DECIMAL(18,2), delivery_date DATE, memo VARCHAR(200), INDEX idx_quotation (quotation_id) );

报价单的核心价值是历史价格查询。同一个客户的历史报价、同一种物料的历史成交价，都是销售谈判的参考数据。

3. 销售订单

CREATE TABLE sa_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(30) NOT NULL UNIQUE, order_date DATE NOT NULL, order_type VARCHAR(20), -- NORMAL/RETURN/SAMPLE -- 客户信息 customer_id BIGINT NOT NULL, customer_code VARCHAR(20), customer_name VARCHAR(100), -- 业务信息 salesman_id BIGINT, department_id BIGINT, -- 价格信息 currency_code VARCHAR(3) DEFAULT 'CNY', exchange_rate DECIMAL(18,6) DEFAULT 1, price_list_id BIGINT, -- 金额汇总 total_quantity DECIMAL(18,6) DEFAULT 0, total_amount DECIMAL(18,2) DEFAULT 0, total_tax DECIMAL(18,2) DEFAULT 0, total_with_tax DECIMAL(18,2) DEFAULT 0, discount_amount DECIMAL(18,2) DEFAULT 0, -- 出库信息 warehouse_id BIGINT, delivery_date DATE, delivery_address VARCHAR(200), -- 来源追溯 source_type VARCHAR(20), -- QUOTATION/MANUAL source_id BIGINT, -- 状态 status VARCHAR(10) DEFAULT 'DRAFT', -- 审核信息 submit_time DATETIME, approve_time DATETIME, approve_user_id BIGINT, memo VARCHAR(500), created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_customer (customer_id), INDEX idx_date (order_date), INDEX idx_salesman (salesman_id), INDEX idx_status (status) );

订单表的字段设计要考虑“冗余存储”。customer_code、customer_name这些字段在客户表里已经有了，为什么订单表还要存？

因为客户信息可能变化。如果客户改名了，历史订单应该显示当时的客户名称，而不是现在的名称。冗余存储保证了历史数据的准确性。

二、销售出库与库存联动

销售出库是销售模块和库存模块的交界点。出库单既要更新库存，又要生成销售成本。

出库单表结构

CREATE TABLE sa_delivery ( id BIGINT PRIMARY KEY AUTO_INCREMENT, delivery_no VARCHAR(30) NOT NULL UNIQUE, delivery_date DATE NOT NULL, delivery_type VARCHAR(20) DEFAULT 'SALES', -- SALES/RETURN/TRANSFER -- 订单关联 order_id BIGINT, order_no VARCHAR(30), -- 客户信息 customer_id BIGINT NOT NULL, -- 仓库信息 warehouse_id BIGINT NOT NULL, -- 金额汇总 total_quantity DECIMAL(18,6) DEFAULT 0, total_cost DECIMAL(18,2) DEFAULT 0, -- 销售成本 total_amount DECIMAL(18,2) DEFAULT 0, -- 销售金额 -- 状态 status VARCHAR(10) DEFAULT 'DRAFT', -- 审核信息 approve_time DATETIME, approve_user_id BIGINT, created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_order (order_id), INDEX idx_customer (customer_id), INDEX idx_date (delivery_date) ); CREATE TABLE sa_delivery_line ( id BIGINT PRIMARY KEY AUTO_INCREMENT, delivery_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, -- 成本信息 cost DECIMAL(18,6), -- 单位成本 total_cost DECIMAL(18,2), -- 成本金额 -- 销售信息 price DECIMAL(18,6), -- 单价 total_amount DECIMAL(18,2), -- 销售金额 -- 库存位置 warehouse_id BIGINT NOT NULL, location_id BIGINT, INDEX idx_delivery (delivery_id) );

出库审核时的库存扣减

CREATE PROCEDURE sp_sales_delivery_approve(IN p_delivery_id BIGINT) BEGIN DECLARE v_line_id BIGINT; DECLARE v_material_id BIGINT; DECLARE v_warehouse_id BIGINT; DECLARE v_batch_no VARCHAR(50); DECLARE v_quantity DECIMAL(18,6); DECLARE v_cost DECIMAL(18,6); DECLARE done INT DEFAULT FALSE; -- 游标遍历出库明细 DECLARE cur_line CURSOR FOR SELECT id, material_id, warehouse_id, batch_no, quantity, cost FROM sa_delivery_line WHERE delivery_id = p_delivery_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; START TRANSACTION; OPEN cur_line; read_loop: LOOP FETCH cur_line INTO v_line_id, v_material_id, v_warehouse_id, v_batch_no, v_quantity, v_cost; IF done THEN LEAVE read_loop; END IF; -- 扣减库存 UPDATE inv_stock SET quantity = quantity - v_quantity WHERE warehouse_id = v_warehouse_id AND material_id = v_material_id AND batch_no = v_batch_no; -- 记录库存流水 INSERT INTO inv_transaction (transaction_type, warehouse_id, material_id, batch_no, quantity, cost) VALUES ('OUT_SALES', v_warehouse_id, v_material_id, v_batch_no, -v_quantity, v_cost); END LOOP; CLOSE cur_line; -- 更新出库单状态 UPDATE sa_delivery SET status = 'APPROVED', approve_time = NOW() WHERE id = p_delivery_id; COMMIT; END;

三、应收账款管理

销售开票后生成应收账款，收款后核销应收。

应收账款表

CREATE TABLE ar_invoice ( id BIGINT PRIMARY KEY AUTO_INCREMENT, invoice_no VARCHAR(30) NOT NULL UNIQUE, invoice_type VARCHAR(20), -- SALES/CREDIT_NOTE invoice_date DATE NOT NULL, -- 客户信息 customer_id BIGINT NOT NULL, -- 金额信息 total_amount DECIMAL(18,2) NOT NULL, tax_amount DECIMAL(18,2), received_amount DECIMAL(18,2) DEFAULT 0, remaining_amount DECIMAL(18,2) GENERATED ALWAYS AS (total_amount - received_amount) STORED, -- 来源 source_type VARCHAR(20), -- DELIVERY/ORDER source_id BIGINT, -- 账龄 due_date DATE, overdue_days INT GENERATED ALWAYS AS (DATEDIFF(CURDATE(), due_date)) STORED, status VARCHAR(10) DEFAULT 'OPEN', -- OPEN/PARTIAL/CLOSED created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_customer (customer_id), INDEX idx_due_date (due_date), INDEX idx_status (status) );

overdue_days：账龄分析的核心字段。正数表示逾期天数，负数表示未到期。

收款核销

CREATE TABLE ar_receipt ( id BIGINT PRIMARY KEY AUTO_INCREMENT, receipt_no VARCHAR(30) NOT NULL UNIQUE, receipt_date DATE NOT NULL, customer_id BIGINT NOT NULL, -- 收款信息 total_amount DECIMAL(18,2) NOT NULL, bank_account VARCHAR(30), receipt_type VARCHAR(20), -- CASH/BANK/NOTE status VARCHAR(10) DEFAULT 'DRAFT', created_time DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE ar_receipt_apply ( id BIGINT PRIMARY KEY AUTO_INCREMENT, receipt_id BIGINT NOT NULL, invoice_id BIGINT NOT NULL, apply_amount DECIMAL(18,2) NOT NULL, INDEX idx_receipt (receipt_id), INDEX idx_invoice (invoice_id) );

核销逻辑：一笔收款可以对应多张发票，一张发票也可以分多次收款。ar_receipt_apply表记录这种多对多关系。

成都云策数链科技有限公司 | 用友四川授权服务中心 | 专注企业数字化转型