Published on March 25, 2021.

As I mentioned in a post earlier this week, I'm currently developing a custom employee portal for one of my NetSuite clients. In that post, I shared a query that I'm using to provide access to an employee's emergency contacts.

One of the things that employees can do in the portal is access their paycheck information. In this post, I'll share and discuss some of the SuiteQL queries that I'm using to provide that information, as well as the various tables that are involved.

Please note: Many of the tables that I'm using in these queries, including the Paycheck table and related tables (PaycheckPayCommissions, PaycheckPayContributions, PaycheckPayDeductions, PaycheckPayEarnings, etc), are only available in NetSuite accounts where SuitePeople Payroll is enabled.

The PayCheck Table

The key to accessing payroll information is the PayCheck table, so let's start by examining it.

In the employee portal, an employee is initially presented with a list of their paychecks. Here's a query that provides the data needed for that function.

SELECT
	PayCheck.ID,
	PayCheck.TranDate,
	PayCheck.TranID AS CheckNumber,
	PayCheck.ChkAmount,
	PayCheck.PeriodEnding,	
	PayCheck.PayFrequency
FROM
	Paycheck	
WHERE
	( Paycheck.Entity = 23011 )
	AND ( PayCheck.TranDate BETWEEN TO_DATE( '2020-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2020-12-31', 'YYYY-MM-DD' ) )
ORDER BY
	PayCheck.TranDate DESC,
	PayCheck.TranID DESC

As you can see, I'm requesting the paycheck's unique ID, the date of the check, the check number, and the check amount. I've also requested the ending date for the pay period, and the pay frequency. I'm filtering for a specific employee ID (represented as the Entity column), and on a date range. For information on you can work with dates and times in SuiteQL, click here.

The PayCheckSummary Table

NetSuite also provides a PayCheckSummary table which rolls up some of the categorized values that make up a paycheck. Here's a query that provides that data for a specified paycheck.

SELECT
	PayCheck.ID,
	PayCheck.TranDate,
	PayCheck.TranID AS CheckNumber,
	PayCheck.ChkAmount,
	BUILTIN.DF( PayCheckSummary.ItemCategory ) AS ItemCategory,
	BUILTIN.DF( PayCheckSummary.PayItem ) AS PayItem,
	BUILTIN.DF( PayCheckSummary.PayItemType ) AS PayItemType,
	PayCheckSummary.Value,
	PayCheckSummary.Amount	
FROM
	Paycheck
	INNER JOIN PayCheckSummary ON
		( PayCheckSummary.Paycheck = Paycheck.ID )		
WHERE
	( Paycheck.Entity = 23011 )
	AND ( PayCheck.ID = 6269663 )
ORDER BY
	PayCheck.TranDate DESC,
	PayCheck.TranID DESC

In this query, I'm making use of the BUILTIN.DF to map the numeric values of several columns to their names. For information on the BUILTIN.DF functions, please refer to this blog post.

Commissions, Contributions, and Deductions

There are three tables that can be used to get the details on a paycheck's commissions, contributions, and deductions. Here are queries that you can use on those tables.

For commissions, you query the PaycheckPayCommissions table.

SELECT
	BUILTIN.DF( PayItem ) AS PayItem,
	Amount	
FROM
	PaycheckPayCommissions
WHERE
	PayCheck = 6269663

For contributions, you query the PaycheckPayContributions table.

	
SELECT
	BUILTIN.DF( PayItem ) AS PayItem,
	Amount	
FROM
	PaycheckPayContributions
WHERE
	PayCheck = 6269663

And for deductions, you query the PaycheckPayDeductions table.

	
SELECT
	BUILTIN.DF( PayItem ) AS PayItem,
	Amount	
FROM
	PaycheckPayDeductions
WHERE
	PayCheck = 6269663

Earnings

The table used to store a paycheck's earnings data is named PaycheckPayEarnings, and here's a query that uses it.

	
SELECT
	BUILTIN.DF( PayItem ) AS PayItem,
	BasedOnQuan,
	Count,
	Rate,
	GrossAmount
FROM
	PaycheckPayEarnings
WHERE
	PayCheck = 6269663

As you can see, I'm returning values that indicate if the earnings is based on a quantity and rate (BasedOnQuan), and if so, what those values are (Count and Rate). For example, an employee's wages might be based on the number of hours worked and an hourly rate.

Payroll Tax Details

The details about a paycheck's taxes can be found in the PayCheckPayTax table. It's in this table that you'll find the unemployment tax, federal withholdings, medicare and social security (for both the employer and employee), and more.

SELECT
	BUILTIN.DF( PayTax ) AS PayTax,
	TaxedWageBase,
	TaxableWageBase,
	ReportableWagebase,
	IsExempt,	
	Amount
FROM
	PayCheckPayTax
WHERE
	PayCheck = 6269663

Paid Time Off

Information about an employee's PTO (Paid Time Off) for the pay period can be found in the PaycheckPayPto table. Here you'll find any PTO hours that were used or accrued during the pay period, and the employee's PTO balance.

	
SELECT 
	BUILTIN.DF( PayItem ) AS PTO,
	HoursBalance,
	HoursUsed,
	HoursAccrued 
FROM
	PaycheckPayPTO
WHERE
	PayCheck = 6269663

Here's an additional PTO-related query that you might find helpful. For a given employee, it returns the PTO data for each pay period.

	
SELECT 
	PayCheck.PeriodEnding,
	BUILTIN.DF( PTO.PayItem ) AS PTO,
	PTO.HoursUsed,
	PTO.HoursAccrued,
	PTO.HoursBalance
FROM
	Paycheck
	INNER JOIN PaycheckPayPto AS PTO ON
		( PTO.Paycheck = Paycheck.ID )
WHERE
	Paycheck.Entity = 23011
ORDER BY
	PayCheck.PeriodEnding DESC

Wrapping Up

NetSuite provides us with a number of tables that we can use to access, report on, and analyze payroll data. In my current project, I'm primarily using that data to provide employees with fast and convenient access to their paychecks. However, you could easily use those tables to provide managers with custom payroll reports.

I hope you find the queries that I've shared in this post to be helpful. If you have any questions about them, or about SuiteQL in general, please feel free to contact me.