如何使用SuiteQL查询NetSuite的Currency Revaluation (Unrealized Gain/Loss)表

完全可以通过SuiteQL查询获取“Currency Revaluation (Unrealized Gain/Loss)”对应的数据。

核心逻辑说明 #

你提到的货币重估（未实现汇兑损益）记录本质是NetSuite中类型为CurrencyRevaluation的交易，无需调用独立的特殊表，直接通过SuiteQL内置的Transaction表筛选对应交易类型，再关联交易行、会计期间、汇率、子公司等维度表即可获取全量字段。
你提供的参考查询已经包含了关联前后交易、汇率匹配的核心逻辑，只需要补充对应交易类型筛选条件即可定向拉取重估数据。

参考查询示例 #

基础版：直接拉取所有已过账货币重估记录 #

SELECT
  tr.id AS revaluation_id,
  tr.tranid AS revaluation_number,
  tr.trandate AS revaluation_date,
  ap.periodname AS posting_period,
  sb.name AS subsidiary_name,
  tl.account AS account_id,
  a.displayname AS account_name,
  tl.foreignamount AS foreign_currency_amount,
  tl.amount AS functional_currency_amount,
  tl.unrealizedgainloss AS unrealized_gain_loss, -- 系统预置未实现损益字段，无需手动计算
  c.symbol AS transaction_currency
FROM
  Transaction tr
  INNER JOIN TransactionLine tl ON tl.transaction = tr.id
  INNER JOIN Subsidiary sb ON sb.id = tr.subsidiary
  INNER JOIN AccountingPeriod ap ON ap.id = tr.postingperiod
  INNER JOIN Account a ON a.id = tl.account
  INNER JOIN Currency c ON c.id = tr.currency
WHERE
  tr.type = 'CurrencyRevaluation' -- 核心筛选，仅取货币重估交易
  AND tr.posting = 'T'
  AND ap.isposting = 'T'

适配版：在你原有查询基础上增加重估交易筛选 #

你原有用于关联源交易和后续关联交易的逻辑无需改动，仅需要在WHERE条件中增加NT.type = 'CurrencyRevaluation'即可定向拉取对应重估记录：

SELECT
NT .*
FROM
  NextTransactionLineLink AS NTLL
  INNER JOIN Transaction AS NT ON (NT.ID = NTLL.NextDoc)
  INNER JOIN Transaction ON (Transaction.ID = NTLL.PreviousDoc)
  inner join transactionline tl ON (tl.TRANSACTION = Transaction.id)
  inner join subsidiary sb ON sb.id = tl.subsidiary
  inner join accountingperiod ap ON (
    (ap.id = Transaction.postingperiod)
    AND ap.isposting = 'T'
  )
  inner join accountingperiod pap ON (
    (pap.id = NT.postingperiod)
    AND pap.isposting = 'T'
  )
  inner join currencyrate cr1 ON (
    cr1.basecurrency = sb.currency
    AND cr1.transactioncurrency = Transaction.currency
    AND (
      cr1.effectivedate = To_date(
        ap.startdate,
        'MM/DD/YYYY'
      )
    )
  )
  inner join consolidatedexchangerate cexr ON (
    cexr.postingperiod = Transaction.postingperiod
    AND cexr.fromsubsidiary = tl.subsidiary
    AND cexr.tosubsidiary = 1
  )
WHERE
 (NTLL.NextDoc = 212328) 
 AND NT.type = 'CurrencyRevaluation' -- 新增筛选条件：仅取货币重估类后续交易

参考图示 #

注意事项 #

• 若需要折算到不同的上级子公司，修改consolidatedexchangerate关联条件中的tosubsidiary值为对应目标子公司ID即可
• 优先使用系统预置的unrealizedgainloss字段取值，避免手动计算汇率差出现的误差
• 可以根据需求补充筛选子公司、会计期间、科目等维度的条件缩小查询范围

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