4. 异常类型一：金额不一致——发票金额与订单/入库单金额不符的排查思路与SQL脚本

金额不一致是发票匹配环节出现频率最高的异常类型，通常表现为发票上的含税/不含税金额与采购订单或入库单的对应金额存在差异。这类差异可能由单价错误、数量四舍五入、税率变更、折扣未同步或人工录入失误引起。本节将系统梳理排查思路，并提供可直接落地的SQL脚本。

4.1 异常现象与影响范围

4.2 排查思路：四层递进法

建议按照以下四个层次逐步缩小排查范围，避免直接跳入SQL细节导致遗漏根因。

1. 第一层：基础字段比对 —— 检查发票与订单的供应商编码、物料编码、数量、单价、税率、币种是否一致。这是最基础的过滤条件。
2. 第二层：金额计算逻辑验证 —— 确认发票的含税金额 = 不含税金额 × (1 + 税率)，以及订单的含税金额 = 订单数量 × 含税单价。如果计算逻辑本身有误，则属于系统配置或数据质量问题。
3. 第三层：时间窗口与状态过滤 —— 检查发票日期是否在订单有效期内，订单是否已全部收货或部分收货，入库单是否已生成且状态为“已审核”。时间错位或状态未更新是常见隐性原因。
4. 第四层：异常归因与分类 —— 将差异金额按值分组（0.01~1元、1~10元、10元以上），并结合差异方向（正/负）判断是四舍五入、折扣未分摊、还是单价变更。

4.3 排查SQL脚本（核心）

以下SQL脚本基于典型的ERP数据模型设计，包含发票主表、发票行表、采购订单行表、入库单行表。实际使用时请根据字段名做适配。

-- 4.3.1 基础比对：发票 vs 采购订单（含税金额差异）
WITH invoice_data AS (
  SELECT
    i.invoice_id,
    i.supplier_code,
    il.line_id,
    il.material_code,
    il.quantity AS inv_qty,
    il.unit_price_tax AS inv_unit_price_tax,
    il.tax_rate,
    il.amount_tax AS inv_amount_tax,
    il.amount_no_tax AS inv_amount_no_tax
  FROM invoice_header i
  JOIN invoice_line il ON i.invoice_id = il.invoice_id
  WHERE i.invoice_date BETWEEN '2025-01-01' AND '2025-01-31'
    AND i.status = '待匹配'
),
order_data AS (
  SELECT
    pol.po_line_id,
    pol.po_header_id,
    pol.material_code,
    pol.quantity AS po_qty,
    pol.unit_price_tax AS po_unit_price_tax,
    pol.tax_rate AS po_tax_rate,
    pol.amount_tax AS po_amount_tax,
    pol.amount_no_tax AS po_amount_no_tax
  FROM po_line pol
  WHERE pol.po_header_id IN (
    SELECT po_header_id FROM po_header
    WHERE po_date BETWEEN '2024-12-01' AND '2025-01-31'
  )
)
SELECT
  i.invoice_id,
  i.supplier_code,
  i.material_code,
  i.inv_qty,
  i.inv_unit_price_tax,
  i.tax_rate,
  i.inv_amount_tax,
  o.po_amount_tax,
  ROUND(i.inv_amount_tax - o.po_amount_tax, 2) AS diff_amount_tax,
  CASE
    WHEN ROUND(i.inv_amount_tax - o.po_amount_tax, 2) = 0 THEN '一致'
    WHEN ABS(ROUND(i.inv_amount_tax - o.po_amount_tax, 2)) <= 0.01 THEN '四舍五入差异'
    ELSE '金额不一致'
  END AS diff_type
FROM invoice_data i
LEFT JOIN order_data o
  ON i.material_code = o.material_code
  AND i.supplier_code = (SELECT supplier_code FROM po_header WHERE po_header_id = o.po_header_id)
WHERE ABS(ROUND(i.inv_amount_tax - o.po_amount_tax, 2)) > 0
ORDER BY diff_amount_tax DESC;

-- 4.3.2 入库单金额比对（含税金额）
SELECT
  i.invoice_id,
  i.supplier_code,
  i.material_code,
  i.inv_amount_tax,
  r.receipt_amount_tax,
  ROUND(i.inv_amount_tax - r.receipt_amount_tax, 2) AS diff_with_receipt
FROM invoice_data i
JOIN (
  SELECT
    rl.po_line_id,
    rl.material_code,
    SUM(rl.quantity * rl.unit_price_tax) AS receipt_amount_tax
  FROM receipt_line rl
  JOIN receipt_header rh ON rl.receipt_header_id = rh.receipt_header_id
  WHERE rh.status = '已审核'
  GROUP BY rl.po_line_id, rl.material_code
) r
  ON i.material_code = r.material_code
WHERE ABS(ROUND(i.inv_amount_tax - r.receipt_amount_tax, 2)) > 0.01
ORDER BY diff_with_receipt DESC;

4.4 排查流程图

4.5 常见原因与处理建议