如何使用 SuiteQL 查询 Invoice 及对应的 Payment Transaction

2026-10-24支付小周

结论

可以使用 SuiteQL 查询 Invoice 对应的 Payment Transaction，但 Payment ID 不在 Transaction 或 TransactionLine 表中。要查找 Invoice 与 Customer Payment 之间的核销关系，需要查询交易链接表。通常可以使用 NextTransactionLineLink，再连接 Transaction 获取付款交易。

为什么 Invoice 中没有 Payment ID

Invoice 和 Payment 是两笔独立的交易：

Invoice 的类型通常是 CustInvc
Customer Payment 的类型通常是 CustPymt
Vendor Bill 的类型通常是 VendBill
Vendor Payment 的类型通常是 VendPymt

一张 Invoice 可以分多次付款，一笔 Payment 也可以同时核销多张 Invoice。因此，NetSuite 不会在 Invoice 记录中保存一个固定的 paymentID。Invoice 和 Payment 是多对多关系，具体的核销信息记录在交易链接表中。

示例 SQL 还缺少 TransactionLine 与 Transaction 之间的显式连接。FROM 子句中只有 TransactionLine 时，不能直接使用 Transaction.TranID。

查询 Invoice 对应的 Customer Payment

从 Invoice 出发，通过 NextTransactionLineLink 可以找到后续的付款交易：

SELECT DISTINCT
    Invoice.ID AS InvoiceID,
    Invoice.TranID AS InvoiceNumber,
    Invoice.TranDate AS InvoiceDate,
    Invoice.Status AS InvoiceStatus,

    Payment.ID AS PaymentID,
    Payment.TranID AS PaymentNumber,
    Payment.TranDate AS PaymentDate,
    Payment.Status AS PaymentStatus,

    Link.LinkType,
    Link.ForeignAmount AS AppliedAmount

FROM Transaction AS Invoice

INNER JOIN NextTransactionLineLink AS Link
    ON Link.PreviousDoc = Invoice.ID

INNER JOIN Transaction AS Payment
    ON Payment.ID = Link.NextDoc

WHERE Invoice.Type = 'CustInvc'
    AND Invoice.TranID = ?
    AND Payment.Type = 'CustPymt'

ORDER BY Payment.TranDate, Payment.ID

在 SuiteScript 2.x 中，可以使用参数化查询：

define(['N/query'], function (query) {
    function onRequest(context) {
        var sql = `
            SELECT DISTINCT
                Invoice.ID AS InvoiceID,
                Invoice.TranID AS InvoiceNumber,
                Invoice.TranDate AS InvoiceDate,
                Payment.ID AS PaymentID,
                Payment.TranID AS PaymentNumber,
                Payment.TranDate AS PaymentDate,
                Link.ForeignAmount AS AppliedAmount
            FROM Transaction AS Invoice
            INNER JOIN NextTransactionLineLink AS Link
                ON Link.PreviousDoc = Invoice.ID
            INNER JOIN Transaction AS Payment
                ON Payment.ID = Link.NextDoc
            WHERE Invoice.Type = 'CustInvc'
                AND Invoice.TranID = ?
                AND Payment.Type = 'CustPymt'
            ORDER BY Payment.TranDate, Payment.ID
        `;

        var resultSet = query.runSuiteQL({
            query: sql,
            params: ['2535']
        });

        var results = resultSet.asMappedResults();

        context.response.setHeader({
            name: 'Content-Type',
            value: 'application/json; charset=utf-8'
        });

        context.response.write(JSON.stringify(results));
    }

    return {
        onRequest: onRequest
    };
});

asMappedResults() 返回的属性名通常会转为小写。例如，InvoiceID 在 JSON 中可能显示为 invoiceid。

查询不到 Payment 时如何排查

链接方向可能因交易类型、功能配置或核销路径而不同。此时可以检查 PreviousTransactionLineLink，确认 Invoice 在链接的哪一端：

SELECT DISTINCT
    Invoice.ID AS InvoiceID,
    Invoice.TranID AS InvoiceNumber,
    Payment.ID AS PaymentID,
    Payment.TranID AS PaymentNumber,
    Link.LinkType,
    Link.ForeignAmount AS AppliedAmount

FROM Transaction AS Invoice

INNER JOIN PreviousTransactionLineLink AS Link
    ON Link.NextDoc = Invoice.ID

INNER JOIN Transaction AS Payment
    ON Payment.ID = Link.PreviousDoc

WHERE Invoice.Type = 'CustInvc'
    AND Invoice.TranID = ?
    AND Payment.Type = 'CustPymt'

可以先运行一个不限制付款类型的诊断查询，查看 Invoice 实际关联了哪些交易：

SELECT DISTINCT
    LinkedTransaction.ID,
    LinkedTransaction.TranID,
    LinkedTransaction.Type,
    LinkedTransaction.TranDate,
    Link.LinkType,
    Link.ForeignAmount
FROM Transaction AS Invoice
INNER JOIN NextTransactionLineLink AS Link
    ON Link.PreviousDoc = Invoice.ID
INNER JOIN Transaction AS LinkedTransaction
    ON LinkedTransaction.ID = Link.NextDoc
WHERE Invoice.Type = 'CustInvc'
    AND Invoice.TranID = ?
ORDER BY LinkedTransaction.TranDate

如果这个方向没有结果，再用 PreviousTransactionLineLink 做同样的检查。确认 Type 和 LinkType 的实际值后，再添加过滤条件。

查询 Vendor Bill 对应的 Vendor Payment

查询 Vendor Bill 的方法相同，只需调整交易类型：

SELECT DISTINCT
    Bill.ID AS BillID,
    Bill.TranID AS BillNumber,
    Bill.TranDate AS BillDate,

    Payment.ID AS PaymentID,
    Payment.TranID AS PaymentNumber,
    Payment.TranDate AS PaymentDate,

    Link.LinkType,
    Link.ForeignAmount AS AppliedAmount

FROM Transaction AS Bill

INNER JOIN NextTransactionLineLink AS Link
    ON Link.PreviousDoc = Bill.ID

INNER JOIN Transaction AS Payment
    ON Payment.ID = Link.NextDoc

WHERE Bill.Type = 'VendBill'
    AND Bill.TranID = ?
    AND Payment.Type = 'VendPymt'

ORDER BY Payment.TranDate, Payment.ID

如果查询没有结果，同样需要检查反向的 PreviousTransactionLineLink。

从 TransactionLine 正确查询 Invoice

如果报表还需要 Invoice 的行项目，应显式连接交易头和交易行：

SELECT
    T.ID AS TransactionID,
    T.TranID,
    T.TranDate,
    T.Type,
    TL.ID AS TransactionLineID,
    TL.Line,
    TL.Item,
    TL.Memo,
    TL.NetAmount
FROM Transaction AS T
INNER JOIN TransactionLine AS TL
    ON TL.Transaction = T.ID
WHERE T.TranID = ?
    AND T.Type = 'CustInvc'
ORDER BY TL.Line

付款关联是交易级链接。不要把所有行项目和所有付款直接放进同一个查询，否则会产生”行项目数量 × 付款数量”的重复记录。更稳妥的处理方式是：

查询 Invoice 头信息。
查询 Invoice 行项目。
通过交易链接表查询 Payment 和核销金额。
在 Suitelet 中按 Invoice ID 汇总结果。

注意事项

优先使用 Invoice 的 internal ID 查询。受交易类型、子公司或编号规则影响，TranID 不一定全局唯一。
不要使用 SELECT *。SuiteQL 不保证所有字段都能查询，大量无关字段也会增加响应体积。
Paid In Full 只表示当前未付余额为零，并不表示存在唯一的一笔 Payment。Invoice 可能由多笔付款、Credit Memo、Deposit 或 Journal Entry 共同结清。
ForeignAmount 是链接记录上的金额。它的币种和正负符号需要结合交易币种、交易方向及 LinkType 判断。
DISTINCT 可以消除相同行链接产生的重复记录，但不能代替正确的汇总。多笔真实核销记录应该保留，并根据业务需要使用 SUM。
可查询的字段和交易链接的表现可能受 NetSuite 账户功能以及 Records Catalog 暴露情况影响。请在目标账户的 Records Catalog 中确认 NextTransactionLineLink、PreviousTransactionLineLink 和相关字段能否用于 SuiteAnalytics。