skcah
Writing / Netsuite

SuiteQL Accounting Period Queries: Fiscal Years and Posting Periods

Query NetSuite accounting periods with SuiteQL and avoid confusing transaction dates, posting periods and fiscal calendars.

Ryan Sheehy2 minute read

A transaction’s calendar date and its accounting period answer different questions. When a report is defined by posting period, query the period relationship instead of deriving a month from transaction.trandate.

Return the transaction posting period

SELECT
  transaction.id,
  transaction.tranid,
  transaction.trandate,
  accountingperiod.id AS period_id,
  accountingperiod.periodname,
  accountingperiod.startdate,
  accountingperiod.enddate,
  accountingperiod.closed
FROM transaction
LEFT JOIN accountingperiod
  ON accountingperiod.id = transaction.postingperiod
WHERE transaction.posting = 'T'

The period ID is the stable value to use in application logic. The period name is a display label and may follow account-specific naming conventions.

Filter by period ID

If the calling script already knows the period:

const rows = query.runSuiteQL({
  query: `
    SELECT id, tranid, trandate, postingperiod
    FROM transaction
    WHERE posting = 'T'
      AND postingperiod = ?
  `,
  params: [periodId],
}).asMappedResults();

This avoids guessing the fiscal period from the transaction date.

Find periods containing a selected date

SELECT
  id,
  periodname,
  startdate,
  enddate,
  closed
FROM accountingperiod
WHERE startdate <= TO_DATE(?, 'YYYY-MM-DD')
  AND enddate >= TO_DATE(?, 'YYYY-MM-DD')
ORDER BY startdate

An account can contain summary periods as well as posting periods, so inspect the period-type fields exposed in your account’s Records Catalog and filter to the level your report requires.

Calendar reports versus fiscal reports

Use EXTRACT or TO_CHAR on trandate when the requirement explicitly concerns calendar dates:

EXTRACT(YEAR FROM transaction.trandate)

Use transaction.postingperiod and accountingperiod when the requirement concerns:

  • period-close reporting;
  • fiscal months, quarters or years;
  • transactions posted into a period different from their calendar month;
  • open versus closed accounting periods.

Common accounting-period mistakes

  • Assuming January is period 1 of the fiscal year.
  • Matching a period by its display name instead of internal ID.
  • Grouping by trandate when the report specification says posting period.
  • Including summary and posting periods in the same result unintentionally.
  • Assuming every transaction is posting.
  • Treating a calendar quarter from TO_CHAR(date, 'Q') as a fiscal quarter.