NetSuite SQL查询指定会计期间内所有开放发票的解决方案问询

问题根源 #

原有查询仅通过t.postingperiod = 目标期间过滤数据，该逻辑只能匹配过账到目标期间的交易，完全未校验交易的开放状态时间段是否覆盖目标会计期间，因此会漏掉过账在之前期间、但目标期间仍处于开放状态、后续才关闭的交易。

核心调整逻辑 #

要满足「只要发票在目标期间处于开放状态就命中」的规则，需同时满足两个判断：

• 交易的交易日期 ≤ 目标会计期间的最后一天
• 交易的关闭日期 ≥ 目标会计期间的第一天 OR 交易至今未关闭（关闭日期为空）

修正后的查询代码 #

-- 子查询获取目标会计期间的参数，可自行调整periodname匹配目标期间
WITH target_period AS (
    SELECT 
        id AS target_ap_id,
        TO_DATE(startdate, 'MM/DD/YYYY') AS target_start,
        TO_DATE(enddate, 'MM/DD/YYYY') AS target_end
    FROM AccountingPeriod
    WHERE periodname = 'Mar 2021'
      AND isposting = 'T'
)
select
  t.*
from
  target_period tp,
  Transaction t
  INNER JOIN TransactionLine tl ON (tl.Transaction = t.ID)
  INNER JOIN Subsidiary sb ON sb.id = tl.subsidiary
  INNER JOIN AccountingPeriod ap ON (
    (ap.id = t.postingperiod)
    AND ap.isposting = 'T'
  )
  INNER JOIN CurrencyRate cr1 ON (
    cr1.BaseCurrency = sb.currency
    AND cr1.TransactionCurrency = t.Currency
    AND (
      cr1.EffectiveDate = TO_DATE(ap.startdate, 'MM/DD/YYYY')
    )
  )
  INNER JOIN CurrencyRate cr2 ON (
    cr2.BaseCurrency = sb.currency
    AND cr2.TransactionCurrency = t.Currency
    AND (
      cr2.EffectiveDate = TO_DATE(ap.enddate, 'MM/DD/YYYY')
    )
  )
  LEFT JOIN CurrencyRate cr3 ON (
    cr3.BaseCurrency = 1
    AND cr3.TransactionCurrency = sb.currency
    AND (
      cr3.EffectiveDate = TO_DATE(t.TranDate, 'MM/DD/YYYY')
    )
  )
  INNER JOIN consolidatedexchangerate cexr ON (
    cexr.postingperiod = t.PostingPeriod
    AND cexr.fromsubsidiary = tl.subsidiary
    AND cexr.tosubsidiary = 1
  )
Where
  t.AbbrevType in ('BILL', 'INV')
  AND tl.MainLine = 'T'
  AND tl.subsidiary IN (1)
  -- 核心替换原过账期间过滤逻辑
  AND TO_DATE(t.TranDate, 'MM/DD/YYYY') <= tp.target_end
  AND (
    TO_DATE(t.ClosedDate, 'MM/DD/YYYY') >= tp.target_start
    OR t.ClosedDate IS NULL
  )

注意事项 #

• 若你的NetSuite实例日期格式不是MM/DD/YYYY，请自行调整TO_DATE函数的格式参数
• 保留了原查询所有汇率、子公司关联逻辑，不会影响原有字段的返回结果
• 若需批量查询多个会计期间的开放交易，仅需调整target_period子查询的过滤条件即可

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