ERP应收应付模块设计：从发票到对账-开发者社区

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

一、应收管理

应收管理是销售到收款的闭环。发票开了，钱没收到，就是应收。

1. 应收发票

CREATE TABLE ar_invoice ( id BIGINT PRIMARY KEY AUTO_INCREMENT, invoice_no VARCHAR(30) NOT NULL UNIQUE, invoice_date DATE NOT NULL, -- 客户 customer_id BIGINT NOT NULL, customer_name VARCHAR(100), -- 来源 source_type VARCHAR(20), -- SALES_ORDER/MANUAL source_id BIGINT, -- 金额 invoice_amount DECIMAL(18,2) NOT NULL, tax_amount DECIMAL(18,2) DEFAULT 0, total_amount DECIMAL(18,2) NOT NULL, -- 已收/未收 received_amount DECIMAL(18,2) DEFAULT 0, remaining_amount DECIMAL(18,2) GENERATED ALWAYS AS (total_amount - received_amount) STORED, -- 到期日 due_date DATE, overdue_days INT GENERATED ALWAYS AS ( CASE WHEN CURDATE() > due_date THEN DATEDIFF(CURDATE(), due_date) ELSE 0 END ) STORED, -- 状态 status VARCHAR(10) DEFAULT 'OPEN', -- OPEN/PARTIAL/CLOSED/OVERDUE created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_customer (customer_id), INDEX idx_due_date (due_date), INDEX idx_status (status) );

remaining_amount：剩余未收金额。overdue_days：逾期天数，用于账龄分析。

2. 收款单

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, -- 金额 receipt_amount DECIMAL(18,2) NOT NULL, -- 结算方式 payment_method VARCHAR(20), -- BANK_TRANSFER/CHECK/CASH/OTHER bank_account_id BIGINT, -- 状态 status VARCHAR(10) DEFAULT 'DRAFT', created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_customer (customer_id), INDEX idx_date (receipt_date) ); CREATE TABLE ar_receipt_detail ( id BIGINT PRIMARY KEY AUTO_INCREMENT, receipt_id BIGINT NOT NULL, -- 核销的应收发票 invoice_id BIGINT NOT NULL, invoice_no VARCHAR(30), -- 核销金额 apply_amount DECIMAL(18,2) NOT NULL, INDEX idx_receipt (receipt_id) );

3. 收款核销

收款单审核时，更新应收发票的已收金额：

CREATE PROCEDURE sp_ar_receipt_approve(IN p_receipt_id BIGINT) BEGIN -- 更新应收发票已收金额 UPDATE ar_invoice i JOIN ar_receipt_detail d ON i.id = d.invoice_id SET i.received_amount = i.received_amount + d.apply_amount, i.status = CASE WHEN i.received_amount + d.apply_amount >= i.total_amount THEN 'CLOSED' WHEN i.received_amount + d.apply_amount > 0 THEN 'PARTIAL' ELSE i.status END WHERE d.receipt_id = p_receipt_id; -- 更新客户信用占用 UPDATE bd_customer c JOIN ar_receipt r ON c.id = r.customer_id SET c.credit_used = c.credit_used - r.receipt_amount WHERE r.id = p_receipt_id; -- 更新收款单状态 UPDATE ar_receipt SET status = 'APPROVED' WHERE id = p_receipt_id; END;

4. 账龄分析

SELECT c.customer_name, SUM(CASE WHEN i.overdue_days = 0 THEN i.remaining_amount ELSE 0 END) AS not_due, SUM(CASE WHEN i.overdue_days BETWEEN 1 AND 30 THEN i.remaining_amount ELSE 0 END) AS days_1_30, SUM(CASE WHEN i.overdue_days BETWEEN 31 AND 60 THEN i.remaining_amount ELSE 0 END) AS days_31_60, SUM(CASE WHEN i.overdue_days BETWEEN 61 AND 90 THEN i.remaining_amount ELSE 0 END) AS days_61_90, SUM(CASE WHEN i.overdue_days > 90 THEN i.remaining_amount ELSE 0 END) AS days_over_90, SUM(i.remaining_amount) AS total_remaining FROM ar_invoice i JOIN bd_customer c ON i.customer_id = c.id WHERE i.status IN ('OPEN', 'PARTIAL', 'OVERDUE') GROUP BY c.customer_name ORDER BY total_remaining DESC;

二、应付管理

应付是采购到付款的闭环。发票来了，钱没付，就是应付。

1. 应付发票

CREATE TABLE ap_invoice ( id BIGINT PRIMARY KEY AUTO_INCREMENT, invoice_no VARCHAR(30) NOT NULL UNIQUE, invoice_date DATE NOT NULL, -- 供应商 supplier_id BIGINT NOT NULL, supplier_name VARCHAR(100), -- 来源 source_type VARCHAR(20), -- PURCHASE_ORDER/MANUAL source_id BIGINT, -- 金额 invoice_amount DECIMAL(18,2) NOT NULL, tax_amount DECIMAL(18,2) DEFAULT 0, total_amount DECIMAL(18,2) NOT NULL, -- 已付/未付 paid_amount DECIMAL(18,2) DEFAULT 0, remaining_amount DECIMAL(18,2) GENERATED ALWAYS AS (total_amount - paid_amount) STORED, -- 到期日 due_date DATE, -- 状态 status VARCHAR(10) DEFAULT 'OPEN', created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_supplier (supplier_id), INDEX idx_due_date (due_date), INDEX idx_status (status) );

2. 付款单

CREATE TABLE ap_payment ( id BIGINT PRIMARY KEY AUTO_INCREMENT, payment_no VARCHAR(30) NOT NULL UNIQUE, payment_date DATE NOT NULL, -- 供应商 supplier_id BIGINT NOT NULL, -- 金额 payment_amount DECIMAL(18,2) NOT NULL, -- 结算方式 payment_method VARCHAR(20), bank_account_id BIGINT, -- 状态 status VARCHAR(10) DEFAULT 'DRAFT', created_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_supplier (supplier_id), INDEX idx_date (payment_date) ); CREATE TABLE ap_payment_detail ( id BIGINT PRIMARY KEY AUTO_INCREMENT, payment_id BIGINT NOT NULL, invoice_id BIGINT NOT NULL, invoice_no VARCHAR(30), apply_amount DECIMAL(18,2) NOT NULL, INDEX idx_payment (payment_id) );

三、往来对账

1. 客户对账单

SELECT '期初余额' AS item_type, NULL AS doc_no, NULL AS doc_date, NULL AS debit, NULL AS credit, opening_balance AS balance FROM ar_customer_balance WHERE customer_id = ? AND period = ? UNION ALL SELECT CASE WHEN i.total_amount > 0 THEN '应收' ELSE '收款' END, i.invoice_no, i.invoice_date, CASE WHEN i.total_amount > 0 THEN i.total_amount ELSE 0 END, CASE WHEN i.total_amount < 0 THEN ABS(i.total_amount) ELSE 0 END, @running := @running + i.total_amount FROM ( SELECT invoice_no, invoice_date, total_amount FROM ar_invoice WHERE customer_id = ? AND invoice_date BETWEEN ? AND ? UNION ALL SELECT receipt_no, receipt_date, -receipt_amount FROM ar_receipt WHERE customer_id = ? AND receipt_date BETWEEN ? AND ? ) i, (SELECT @running := 0) r ORDER BY doc_date, item_type;

2. 供应商对账

逻辑和客户对账一致，方向相反：应付为贷方，付款为借方。

四、期末处理

1. 汇兑损益

外币应收应付，月末按新汇率重新折算，差额计入汇兑损益。

CREATE PROCEDURE sp_revaluation(IN p_period VARCHAR(7)) BEGIN -- 应收汇兑损益 INSERT INTO gl_journal (journal_type, journal_date, memo) SELECT 'REVALUATION', LAST_DAY(STR_TO_DATE(CONCAT(p_period, '-01'), '%Y-%m-%d')), CONCAT(p_period, '应收汇兑损益') FROM DUAL; -- 计算每笔外币应收的汇兑差异 -- remaining_amount(本币) - remaining_amount(外币) * 新汇率 -- 正数为汇兑收益，负数为汇兑损失 END;

2. 坏账准备

按账龄计提坏账准备：

账龄	计提比例
1-6个月	5%
6-12个月	10%
1-2年	30%
2-3年	50%
3年以上	100%

SELECT SUM(CASE WHEN overdue_days BETWEEN 0 AND 180 THEN remaining_amount * 0.05 ELSE 0 END) + SUM(CASE WHEN overdue_days BETWEEN 181 AND 365 THEN remaining_amount * 0.10 ELSE 0 END) + SUM(CASE WHEN overdue_days BETWEEN 366 AND 730 THEN remaining_amount * 0.30 ELSE 0 END) + SUM(CASE WHEN overdue_days BETWEEN 731 AND 1095 THEN remaining_amount * 0.50 ELSE 0 END) + SUM(CASE WHEN overdue_days > 1095 THEN remaining_amount ELSE 0 END) AS provision_amount FROM ar_invoice WHERE status IN ('OPEN', 'PARTIAL', 'OVERDUE');

五、设计要点

核销规则

收款核销是应收管理的核心。核销方式有：