Published on September 13, 2022.

Over the past several months, I've been working on a custom Business Intelligence project for one of my NetSuite clients. The project has involved developing all sorts of interesting SuiteQL queries.

For one of the project's KPIs, I needed a way to identify all of the Accounting Periods that are applicable based on the current date, including the current fiscal year, quarter, and month.

Current Accounting Periods

It turns out that this is very easy to do, and simply requires a self-join on the AccountingPeriod table. Here's what that query looks like.

SELECT
	AccountingPeriod.ID,
	AccountingPeriod.PeriodName, 
	ParentPeriod.ID AS ParentPeriodID,
	ParentPeriod.PeriodName AS ParentPeriodName,
	AccountingPeriod.StartDate,
	AccountingPeriod.EndDate,
	AccountingPeriod.IsYear,
	AccountingPeriod.IsQuarter,
	AccountingPeriod.IsPosting, 
	AccountingPeriod.IsAdjust,
	AccountingPeriod.AllLocked,
	AccountingPeriod.ARLocked,
	AccountingPeriod.APLocked,
	AccountingPeriod.AllowNonGLChanges,
	AccountingPeriod.LastModifiedDate,
	AccountingPeriod.Closed,
	AccountingPeriod.ClosedOnDate
FROM
	AccountingPeriod 
	LEFT OUTER JOIN AccountingPeriod AS ParentPeriod ON
		( ParentPeriod.ID = AccountingPeriod.Parent )
WHERE
	( AccountingPeriod.IsInactive = 'F' )
	AND ( SYSDATE BETWEEN AccountingPeriod.StartDate AND AccountingPeriod.EndDate )
ORDER BY 
	AccountingPeriod.IsYear DESC,
	AccountingPeriod.IsQuarter DESC

And here are the results.

Click the image to view a larger version.

As you can see, AccountingPeriod records that represent the fiscal year, the current quarter, and the current month are returned.

By using a LEFT OUTER JOIN, the record representing the Fiscal Year is included in the results. Note that it does not have a parent period.

The ORDER BY clause is somewhat interesting. By sorting in this way, the applicable Accounting Periods are returned in order based on the current fiscal year first, followed by the quarter, and then the month.

If you'd like to limit the results to only the active posting Accounting Period (i.e. the record representing the current month), you would adjust the WHERE clause. For example:

SELECT
	AccountingPeriod.ID,
	AccountingPeriod.PeriodName, 
	ParentPeriod.ID AS ParentPeriodID,
	ParentPeriod.PeriodName AS ParentPeriodName,
	AccountingPeriod.StartDate,
	AccountingPeriod.EndDate,
	AccountingPeriod.IsYear,
	AccountingPeriod.IsQuarter,
	AccountingPeriod.IsPosting, 
	AccountingPeriod.IsAdjust,
	AccountingPeriod.AllLocked,
	AccountingPeriod.ARLocked,
	AccountingPeriod.APLocked,
	AccountingPeriod.AllowNonGLChanges,
	AccountingPeriod.LastModifiedDate,
	AccountingPeriod.Closed,
	AccountingPeriod.ClosedOnDate
FROM
	AccountingPeriod 
	LEFT OUTER JOIN AccountingPeriod AS ParentPeriod ON
		( ParentPeriod.ID = AccountingPeriod.Parent )
WHERE
	( AccountingPeriod.IsInactive = 'F' )
	AND ( SYSDATE BETWEEN AccountingPeriod.StartDate AND AccountingPeriod.EndDate )
	AND ( AccountingPeriod.IsPosting = 'T' )

Similarly, you could adjust the WHERE clause to determine the Accounting Period record that represents the active fiscal year ( AccountingPeriod.IsYear = 'T' ) or the active quarter ( AccountingPeriod.IsQuarter = 'T' ).

Accounting Periods for a Specific Date

And finally, if you'd like to get the Accounting Period records for a date other than the current date, you would simply adjust the date-related filters in the WHERE clause. For example, to get the Account Period records for May 23, 2022, the query would look like this:

SELECT
	AccountingPeriod.ID,
	AccountingPeriod.PeriodName, 
	ParentPeriod.ID AS ParentPeriodID,
	ParentPeriod.PeriodName AS ParentPeriodName,
	AccountingPeriod.StartDate,
	AccountingPeriod.EndDate,
	AccountingPeriod.IsYear,
	AccountingPeriod.IsQuarter,
	AccountingPeriod.IsPosting, 
	AccountingPeriod.IsAdjust,
	AccountingPeriod.AllLocked,
	AccountingPeriod.ARLocked,
	AccountingPeriod.APLocked,
	AccountingPeriod.AllowNonGLChanges,
	AccountingPeriod.LastModifiedDate,
	AccountingPeriod.Closed,
	AccountingPeriod.ClosedOnDate
FROM
	AccountingPeriod 
	LEFT OUTER JOIN AccountingPeriod AS ParentPeriod ON
		( ParentPeriod.ID = AccountingPeriod.Parent )
WHERE
	( AccountingPeriod.IsInactive = 'F' )
	AND ( TO_DATE( '2022-05-23', 'YYYY-MM-DD' ) BETWEEN AccountingPeriod.StartDate AND AccountingPeriod.EndDate )
ORDER BY 
	AccountingPeriod.IsYear DESC,
	AccountingPeriod.IsQuarter DESC

The results would look like this:

Click the image to view a larger version.

Wrapping Up

It's not often that I need data from the AccountingPeriod table, but as I mentioned earlier, the project that I've been working on is making extensive use of it. If you need data from accounting period data, then I hope the queries that I've shared in this post help get you started.