如何按业务规则从客户发票中处理已退款发票

需求说明 #

针对客户发票数据，需按规则完成退款发票与采购发票的核销/移除操作，最终得到已处理后的有效采购发票数据。

核心规则 #

• 单个商品采购后退款：删除该商品对应的采购发票行和退款发票行
• 同一发票含多商品，其中单个商品退款：从对应采购发票行扣减退款的Qty（数量）与Revenue（营收），并移除退款发票行

关键字段说明 #

• IsReturn=1：该记录为退款发票行
• IsReturn=0：该记录为采购发票行
• ItemCode：产品编码（唯一标识商品）
• ColorCode：产品颜色（商品维度之一）
• ItemDim1Code：产品尺寸（商品维度之一）
• Qty：采购/退款数量（注：若退款记录的Qty为负数表示扣减量，需调整计算逻辑）

处理逻辑实现（SQL示例） #

以下以SQL为例实现该逻辑，假设数据表名为customer_invoices，按CustomerID（客户ID）、InvoiceNo（发票号）分组处理：

步骤1：匹配采购与退款记录 #

通过商品维度（ItemCode+ColorCode+ItemDim1Code）关联采购行与对应退款行，同时统计发票内的商品行数：

WITH return_matches AS (
    SELECT
        p.CustomerID,
        p.InvoiceNo,
        p.ItemCode,
        p.ColorCode,
        p.ItemDim1Code,
        p.Qty AS purchase_qty,
        p.Revenue AS purchase_rev,
        r.Qty AS return_qty,
        r.Revenue AS return_rev,
        -- 统计当前发票下的商品种类数
        COUNT(DISTINCT CONCAT(p.ItemCode, p.ColorCode, p.ItemDim1Code)) OVER (PARTITION BY p.CustomerID, p.InvoiceNo) AS item_count_in_invoice
    FROM customer_invoices p
    LEFT JOIN customer_invoices r
        ON p.CustomerID = r.CustomerID
        AND p.ItemCode = r.ItemCode
        AND p.ColorCode = r.ColorCode
        AND p.ItemDim1Code = r.ItemDim1Code
        AND p.IsReturn = 0
        AND r.IsReturn = 1
)

步骤2：按规则生成处理后数据 #

根据发票内商品数量，分别处理两种场景：

SELECT
    CustomerID,
    InvoiceNo,
    ItemCode,
    ColorCode,
    ItemDim1Code,
    -- 多商品发票：扣减退款数量；单商品发票直接过滤
    CASE WHEN item_count_in_invoice > 1 THEN purchase_qty - COALESCE(return_qty, 0) END AS Qty,
    CASE WHEN item_count_in_invoice > 1 THEN purchase_rev - COALESCE(return_rev, 0) END AS Revenue,
    0 AS IsReturn -- 处理后仅保留采购行
FROM return_matches
WHERE
    -- 过滤掉单商品且有退款的记录
    NOT (item_count_in_invoice = 1 AND return_qty IS NOT NULL)
    -- 保留无退款的采购行
    OR return_qty IS NULL

补充说明 #

• 若退款记录的Qty为负数（直接表示扣减量），将计算逻辑改为purchase_qty + COALESCE(return_qty, 0)即可
• 若用Python（Pandas）实现，可通过merge关联采购与退款数据，用groupby统计发票内商品数量，再按条件过滤或计算新的Qty/Revenue
• 处理后的数据自动移除所有退款行（IsReturn=1），仅保留有效采购行

内容的提问来源于stack exchange，提问作者Bugra Varol