Oracle中如何将一笔款项核销至多张发票

现有以下两张表，需将表2中的款项自动核销至表1的往期发票：

表1：未核销发票 #

表2：到账款项 #

需求：将表2中客户1的16205,20款项自动核销到表1的两张发票，完成后表1的PAID列应分别为1000,00和15205,20，实现全额核销。

方法1：Excel公式手动实现 #

1. 按客户匹配发票与款项，确保仅处理同一客户的数据
2. 按发票顺序优先核销较早的发票：
   • 发票1的PAID单元格输入：=MIN(C2, SUMIF(表2!A:A, A2, 表2!B:B))，取发票金额与客户总到账金额的较小值，得到1000,00
   • 发票2的PAID单元格输入：=MIN(C3, SUMIF(表2!A:A, A3, 表2!B:B)-SUM(D$2:D2))，用总到账金额减去已核销金额，再与当前发票金额取较小值，得到15205,20

方法2：VBA脚本批量自动核销 #

针对大量数据场景，可编写VBA脚本自动完成：

Sub AutoReconcile()
    Dim wsInv As Worksheet, wsPay As Worksheet
    Set wsInv = ThisWorkbook.Sheets("表1")
    Set wsPay = ThisWorkbook.Sheets("表2")
    
    Dim lastInvRow As Long, lastPayRow As Long
    lastInvRow = wsInv.Cells(wsInv.Rows.Count, "A").End(xlUp).Row
    lastPayRow = wsPay.Cells(wsPay.Rows.Count, "A").End(xlUp).Row
    
    Dim custID As String, totalPaid As Double, remainingPaid As Double
    Dim i As Long, j As Long
    
    For j = 2 To lastPayRow
        custID = wsPay.Cells(j, "A").Value
        totalPaid = Replace(wsPay.Cells(j, "B").Value, ",", ".") ' 适配逗号作为小数分隔符
        remainingPaid = totalPaid
        
        For i = 2 To lastInvRow
            If wsInv.Cells(i, "A").Value = custID And remainingPaid > 0 Then
                Dim invAmount As Double
                invAmount = Replace(wsInv.Cells(i, "C").Value, ",", ".")
                Dim payAmount As Double
                
                If invAmount <= remainingPaid Then
                    payAmount = invAmount
                    remainingPaid = remainingPaid - payAmount
                Else
                    payAmount = remainingPaid
                    remainingPaid = 0
                End If
                
                wsInv.Cells(i, "D").Value = Replace(Str(payAmount), ".", ",")
            End If
        Next i
    Next j
End Sub

脚本会遍历表2的每笔款项，匹配对应客户的发票并按顺序核销，自动更新表1的PAID列。

方法3：SQL查询实现（数据库场景） #

若数据存储在数据库中，可通过SQL语句完成核销：

WITH CustomerPayments AS (
    SELECT CUSTOMER, SUM(REPLACE(PAID, ',', '.')::NUMERIC) AS TotalPaid
    FROM 表2
    GROUP BY CUSTOMER
),
InvoiceRanked AS (
    SELECT 
        CUSTOMER, INVOICE, AMOUNT, PAID,
        SUM(REPLACE(AMOUNT, ',', '.')::NUMERIC) OVER (PARTITION BY CUSTOMER ORDER BY INVOICE) AS CumulativeAmount
    FROM 表1
)
UPDATE 表1
SET PAID = CASE
    WHEN ir.CumulativeAmount <= cp.TotalPaid THEN ir.AMOUNT
    WHEN ir.CumulativeAmount - REPLACE(ir.AMOUNT, ',', '.')::NUMERIC < cp.TotalPaid THEN 
        REPLACE(CAST(cp.TotalPaid - (ir.CumulativeAmount - REPLACE(ir.AMOUNT, ',', '.')::NUMERIC) AS TEXT), '.', ',')
    ELSE '0,00'
END
FROM 表1
JOIN InvoiceRanked ir ON 表1.CUSTOMER = ir.CUSTOMER AND 表1.INVOICE = ir.INVOICE
JOIN CustomerPayments cp ON ir.CUSTOMER = cp.CUSTOMER;

先计算每个客户的总到账金额，再按发票顺序累计金额，对比后更新PAID值。

内容的提问来源于stack exchange，提问作者Roberto Best