如何通过NetSuite SuiteQL查询销售订单/行项目是否已开票及开票金额？

NetSuite中销售订单（类型码SalesOrd）和发票（类型码CustInvc）的关联关系存储在TransactionLink表中，而非直接在Transaction或TransactionLine的字段里。以下是两种场景的查询方案：

一、订单级查询（整体开票状态+累计开票金额） #

该查询返回每个销售订单的基本信息、是否已开票以及累计已开票的总金额：

SELECT
    so.tranid AS 销售订单编号,
    so.trandate AS 订单日期,
    so.tranamount AS 订单总金额,
    CASE WHEN COUNT(inv.id) > 0 THEN '是' ELSE '否' END AS 是否已开票,
    COALESCE(SUM(inv.tranamount), 0) AS 累计已开票金额
FROM
    Transaction so
LEFT JOIN
    TransactionLink tl ON so.id = tl.transaction
    AND tl.linktype = '发票' -- 中文环境用该值，英文环境替换为'Invoice'
LEFT JOIN
    Transaction inv ON tl.linkedtransaction = inv.id
    AND inv.type = 'CustInvc' -- 筛选发票类型
WHERE
    so.type = 'SalesOrd' -- 筛选销售订单类型
GROUP BY
    so.id, so.tranid, so.trandate, so.tranamount
ORDER BY
    so.trandate DESC;

关键说明： #

• TransactionLink.linktype需匹配NetSuite系统语言，根据环境切换值
• COALESCE函数确保未开票订单的金额返回0，避免NULL值
• 如需排除已取消/作废订单，可添加so.status NOT IN ('Cancelled', 'Voided')条件

二、行项目级查询（明细开票状态） #

如果需要精准到销售订单行项目的开票情况（比如部分行已开票、部分未开），需关联TransactionLine和发票行项目：

SELECT
    so.tranid AS 销售订单编号,
    sol.line AS 订单行号,
    sol.item AS 物料ID,
    sol.description AS 物料描述,
    sol.quantity AS 订单数量,
    CASE WHEN SUM(invl.quantity) IS NOT NULL THEN '是' ELSE '否' END AS 该行是否已开票,
    COALESCE(SUM(invl.quantity), 0) AS 该行已开票数量,
    COALESCE(SUM(invl.amount), 0) AS 该行已开票金额
FROM
    Transaction so
JOIN
    TransactionLine sol ON so.id = sol.transaction
LEFT JOIN
    TransactionLink tl ON so.id = tl.transaction
    AND tl.linktype = '发票'
LEFT JOIN
    Transaction inv ON tl.linkedtransaction = inv.id
    AND inv.type = 'CustInvc'
LEFT JOIN
    TransactionLine invl ON inv.id = invl.transaction
    AND invl.origintransactionline = sol.id -- 通过源订单行ID精准关联发票行
WHERE
    so.type = 'SalesOrd'
GROUP BY
    so.id, so.tranid, sol.line, sol.item, sol.description, sol.quantity
ORDER BY
    so.tranid, sol.line;

关键说明： #

• invl.origintransactionline = sol.id是订单行和发票行最精准的关联方式，适用于一对一开票场景
• 若存在发票合并多行订单的业务，可调整关联逻辑为invl.item = sol.item并结合其他维度匹配

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