PAYATHON 2026

Oracle：如何将一笔 Payment 应用于多张 Invoices

支付阿杰

结论

不要把 Payment 直接写入每张 Invoice。正确的做法是按既定顺序分摊付款金额：

  1. 查询该 CUSTOMER 尚未付清的 Invoices；
  2. 按发票日期、到期日或发票编号排序；
  3. 从最早的 Invoice 开始逐张抵扣；
  4. 每张发票的本次核销额为：
LEAST(剩余 Payment, Invoice 未付金额)
  1. 在同一事务中完成整个过程，并锁定相关记录，防止并发付款造成重复核销。

按照题目中的数据，16205.20 应这样分配：

INVOICEAMOUNT本次核销累计 PAID
11000.001000.001000.00
215205.2015205.2015205.20

推荐的数据结构

正式的应收账款系统不应只维护 INVOICE.PAID。一笔 Payment 可以核销多张 Invoices，一张 Invoice 也可能由多笔 Payments 支付，两者是多对多关系。

可以增加一张核销明细表：

CREATE TABLE payment_application (
    payment_id      NUMBER         NOT NULL,
    invoice_id      NUMBER         NOT NULL,
    applied_amount  NUMBER(18, 2)  NOT NULL,
    applied_at      TIMESTAMP      DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT pk_payment_application
        PRIMARY KEY (payment_id, invoice_id),
    CONSTRAINT ck_applied_amount_positive
        CHECK (applied_amount > 0)
);

假设现有表大致如下：

CREATE TABLE invoice (
    customer_id   NUMBER         NOT NULL,
    invoice_id    NUMBER         PRIMARY KEY,
    invoice_date  DATE           NOT NULL,
    amount        NUMBER(18, 2)  NOT NULL,
    paid          NUMBER(18, 2)  DEFAULT 0 NOT NULL
);

CREATE TABLE payment (
    payment_id    NUMBER         PRIMARY KEY,
    customer_id   NUMBER         NOT NULL,
    amount        NUMBER(18, 2)  NOT NULL
);

生产系统通常还需要外键、付款状态、币种和审计字段。这里仅保留分摊逻辑需要的列。

使用 PL/SQL 逐张分摊

下面的过程按照 invoice_date、invoice_id 从早到晚核销发票：

CREATE OR REPLACE PROCEDURE apply_payment (
    p_payment_id IN payment.payment_id%TYPE
) AS
    l_customer_id      payment.customer_id%TYPE;
    l_payment_amount   payment.amount%TYPE;
    l_remaining        payment.amount%TYPE;
    l_applied_amount   payment.amount%TYPE;
BEGIN
    /*
     * 锁定付款记录，防止同一笔付款被两个会话同时处理。
     */
    SELECT customer_id, amount
      INTO l_customer_id, l_payment_amount
      FROM payment
     WHERE payment_id = p_payment_id
       FOR UPDATE;

    /*
     * 扣除这笔付款已经核销过的金额，使过程可以安全地再次执行。
     */
    SELECT l_payment_amount - NVL(SUM(applied_amount), 0)
      INTO l_remaining
      FROM payment_application
     WHERE payment_id = p_payment_id;

    IF l_remaining <= 0 THEN
        RETURN;
    END IF;

    /*
     * 只处理尚未付清的发票，并按明确的业务顺序排列。
     */
    FOR r_invoice IN (
        SELECT invoice_id,
               amount - paid AS outstanding_amount
          FROM invoice
         WHERE customer_id = l_customer_id
           AND paid < amount
         ORDER BY invoice_date, invoice_id
         FOR UPDATE
    ) LOOP
        EXIT WHEN l_remaining <= 0;

        l_applied_amount :=
            LEAST(l_remaining, r_invoice.outstanding_amount);

        INSERT INTO payment_application (
            payment_id,
            invoice_id,
            applied_amount
        ) VALUES (
            p_payment_id,
            r_invoice.invoice_id,
            l_applied_amount
        );

        UPDATE invoice
           SET paid = paid + l_applied_amount
         WHERE invoice_id = r_invoice.invoice_id;

        l_remaining := l_remaining - l_applied_amount;
    END LOOP;

    /*
     * 如果业务不允许出现未分配余额，可以在这里抛出异常。
     */
    IF l_remaining > 0 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Payment has an unapplied balance: ' ||
            TO_CHAR(l_remaining)
        );
    END IF;
END;
/

调用方式：

BEGIN
    apply_payment(p_payment_id => 1001);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

COMMIT 由调用方执行，不写在存储过程内部，这样调用方可以控制完整事务。

查询核销结果

SELECT i.customer_id,
       i.invoice_id,
       i.amount,
       i.paid,
       i.amount - i.paid AS outstanding_amount
  FROM invoice i
 WHERE i.customer_id = 1
 ORDER BY i.invoice_date, i.invoice_id;

核销明细可以单独查询：

SELECT payment_id,
       invoice_id,
       applied_amount,
       applied_at
  FROM payment_application
 WHERE payment_id = 1001
 ORDER BY invoice_id;

如果允许保留未分配余额

付款金额可能大于该客户全部未付发票的余额。例如，客户预付了 20000.00，未付发票总额却只有 16205.20。这时不能把某张 Invoice 的 PAID 更新到超过 AMOUNT。

剩余金额可以记录为客户预收款，也可以在 Payment 上维护 unapplied_amount，等以后产生新 Invoice 时继续核销。

如果业务允许保留未分配余额，应移除过程末尾的 RAISE_APPLICATION_ERROR，并将 l_remaining 保存到付款余额字段或预收款表中。

注意事项

明确定义“之前的发票”

不要只根据 invoice_id 判断发票先后。通常应按照业务规则排序，例如：

ORDER BY due_date, invoice_date, invoice_id

最后加上唯一字段 invoice_id，可以保证排序结果稳定。

不要使用浮点数保存金额

金额列应使用固定精度类型，例如：

NUMBER(18, 2)

SQL 数值字面量使用小数点：

16205.20

16205,20 通常是地区化的显示格式，不能直接作为标准 SQL 数值字面量使用。

处理并发

SELECT ... FOR UPDATE 很重要。如果两个付款处理同时读取同一张未付 Invoice，又没有锁定记录，就可能都按照旧的未付余额核销，最终导致 PAID > AMOUNT。

还可以增加数据库约束作为最后一道保护：

ALTER TABLE invoice ADD CONSTRAINT ck_invoice_paid
CHECK (paid >= 0 AND paid <= amount);

考虑冲销和退款

如果付款后可能发生撤销、退款或重新分配，只保存累计 PAID 会很难追踪历史。保留 payment_application 明细后，可以通过冲销核销记录恢复余额，同时留下完整的审计轨迹。

Oracle Receivables 场景

如果这里的 Oracle 是指 Oracle E-Business Suite Receivables 或 Oracle Fusion Cloud Receivables，而不是自建的 Oracle Database 表，不建议直接更新底层 Invoice 表。应使用产品提供的收款核销界面、AutoCash 规则或受支持的接口/API。具体的接口名称和参数取决于产品及版本，应以对应版本的官方文档为准。

备注：内容仅供参考。