Dynamics NAV：按指定日期查询未结 Vendor items

结论

不要根据 Vendor Ledger Entry.[Closed at Date] 或 [Closed by Amount] 倒推历史未结金额，也不必单独判断 invoice 是否由 credit note 关闭。

正确的计算方法是：以指定日期为截止日期，按照 [Vendor Ledger Entry No_] 汇总 Detailed Vendor Ledg_ Entry.[Amount (LCY)]。汇总值就是对应 Vendor Ledger Entry 在该日结束时的 remaining amount：

• 汇总金额不为 0：该账项截至当日仍有未结余额。
• 汇总金额为 0：该账项截至当日已经结清。
• 如果只核销了一部分，汇总值就是剩余的未结金额。

这套算法适用于 payment、refund、credit memo 和 invoice 之间的核销，也能处理分次核销及取消核销。

为什么不能依赖 Closing Date

Vendor Ledger Entry 保存主账项，Detailed Vendor Ledg_ Entry 则保存初始金额以及后续的 application、unapplication、汇率调整等明细。

[Closed at Date] 反映的更接近账项最终关闭状态，无法完整表示某个历史日期的余额。例如：

• 一个 invoice 可能经过多次部分付款后才关闭。
• 一个 invoice 可以由 credit memo 核销。
• 一个 credit memo 也可以由 invoice 核销。
• 核销发生后，双方通常都会产生 Detailed Ledger Entry。
• [Closed at Date] 和 [Closed by Amount] 在核销双方的数据中不一定对称。
• 当前已经关闭的账项，在更早的截止日期可能仍处于未结状态。

因此，计算历史余额时，应重新累计截止日期以前的 detailed entries，不能根据当前关闭状态倒推。

推荐查询

下面的查询会返回指定日期仍有余额的 Vendor Ledger Entries：

DECLARE @AsOfDate date = '2016-06-30';

WITH BalanceAtDate AS
(
    SELECT
        DVLE.[Vendor Ledger Entry No_] AS [Entry No_],
        SUM(DVLE.[Amount (LCY)]) AS [Remaining Amount (LCY)]
    FROM [Detailed Vendor Ledg_ Entry] AS DVLE
    WHERE DVLE.[Posting Date] <= @AsOfDate
    GROUP BY
        DVLE.[Vendor Ledger Entry No_]
)
SELECT
    VLE.[Entry No_],
    VLE.[Vendor No_],
    VLE.[Posting Date],
    VLE.[Document Type],
    VLE.[Document No_],
    VLE.[Description],
    B.[Remaining Amount (LCY)]
FROM [Vendor Ledger Entry] AS VLE
INNER JOIN BalanceAtDate AS B
    ON B.[Entry No_] = VLE.[Entry No_]
WHERE
    VLE.[Posting Date] <= @AsOfDate
    AND B.[Remaining Amount (LCY)] <> 0
ORDER BY
    VLE.[Vendor No_],
    VLE.[Posting Date],
    VLE.[Entry No_];

如果字段类型是 datetime 而非 date，建议使用半开区间，以免漏掉截止日当天带有时间部分的记录：

WHERE DVLE.[Posting Date] < DATEADD(day, 1, @AsOfDate)

Invoice 与 Credit Memo 的简单示例

假设 invoice 和 credit memo 在 2016-06-20 相互核销：

以 2016-06-15 为截止日期：

以 2016-06-30 为截止日期：

这里不需要读取任何账项的 closing date。按照 DVLE 的 posting date 汇总后，核销双方都能得到正确结果。

部分核销的处理方式相同。例如，一张金额为 100.00 的 invoice 被金额为 40.00 的 credit memo 部分核销：

Invoice remaining amount = 100.00 - 40.00 = 60.00
Credit memo remaining amount = -40.00 + 40.00 = 0.00

查询结果会保留 invoice，并排除已经全部使用的 credit memo。

如果需要查看汇总过程

排查数据时，可以先单独运行汇总查询：

DECLARE @AsOfDate date = '2016-06-30';

SELECT
    DVLE.[Vendor Ledger Entry No_],
    SUM(DVLE.[Amount (LCY)]) AS [Remaining Amount (LCY)]
FROM [Detailed Vendor Ledg_ Entry] AS DVLE
WHERE DVLE.[Posting Date] <= @AsOfDate
GROUP BY
    DVLE.[Vendor Ledger Entry No_]
HAVING
    SUM(DVLE.[Amount (LCY)]) <> 0
ORDER BY
    DVLE.[Vendor Ledger Entry No_];

确认汇总结果无误后，再连接 Vendor Ledger Entry，取得 vendor、document number、description 等用于展示的字段。

需要注意的地方

不要设置任意起始日期

原查询包含：

DVLE.[Posting Date] BETWEEN '2015-01-04' AND '2016-06-30'

如果 2015-01-04 以前存在尚未关闭的 Vendor Ledger Entry，查询会漏掉这些账项的初始金额，导致结果无法与 Vendor balance 对上。

除非起始日之前的余额已经通过独立的 opening entries 导入，否则应查询截止日期以前的全部 DVLE：

DVLE.[Posting Date] <= @AsOfDate

谨慎过滤 Document Type

原查询使用：

DVLE.[Document Type] >= 2

这个条件可能会排除 payment、refund 或其他影响余额的 detailed entries。特别是不能只保留初始 invoice 和 credit memo，却把用于核销这些账项的 application entries 排除在外。

如果需要与完整的 Vendor balance 对账，通常不应过滤 DVLE 的 document type。即使最终表格只展示 invoice 和 credit memo，也应先用全部 DVLE 计算余额，再在外层按照 VLE.[Document Type] 分类展示。

如果只显示 invoice 和 credit memo，查询结果也未必能与完整的 Vendor balance 一致，因为未应用的 payment、refund 等账项同样可能有未结余额。

使用 Amount (LCY) 对账

如果要与本币 Vendor balance 对账，应汇总：

DVLE.[Amount (LCY)]

不要混用原币 Amount、Remaining Amount 和 Amount (LCY)。存在外币交易时，汇率调整也可能在 DVLE 中产生记录，所以截止日期以前的相关明细都必须纳入计算。

不建议使用浮点容差代替会计精度

NAV 金额字段通常使用定点数值。如果数据库中的字段确实是 decimal 或 numeric，一般可以直接使用：

SUM(DVLE.[Amount (LCY)]) <> 0

只有在数据库经过二次开发、金额被转换为浮点类型时，才需要按照实际会计精度进行舍入，例如：

ROUND(SUM(DVLE.[Amount (LCY)]), 2) <> 0

舍入位数应与公司的金额精度设置一致，不能默认所有环境都使用两位小数。

最后，如果问题中的 “customer balance” 实际指供应商对账，对应的应是 Vendor balance。Customer Ledger Entry 和 Vendor Ledger Entry 属于不同的子账，通常不应直接比较。

备注：内容仅供参考。