Published on November 2, 2020.

Recently, one of my NetSuite clients had an interesting request. They're about to go through an audit, and in preparation, they're gathering data from their instance that the auditors have requested.

My client's request was to provide a list of the employees that have access to their NetSuite instance, including each employee's supervisor, and the roles that each employee has been assigned. In addition, for each role, they needed the permission that was granted, and the permission level.

Using SuiteSQL, I created a single query that was used to pull this information. Let's walk through the query and show how it evolved.

Employees

Let's start with a query that pulls from the Employee table.

SELECT
	Employee.ID AS EmployeeID,	
	Employee.LastName,
	Employee.FirstName,
	Employee.Title,
	Employee.Email
FROM
	Employee
WHERE
	( Employee.IsInactive = 'F' )
	AND ( Employee.GiveAccess = 'T' )
ORDER BY
	Employee.LastName,
	Employee.FirstName

This is a simple, straightforward query. Notice that I'm only including employees that are active and that have been given access to NetSuite. However, to provide data for auditing purposes, you might want to relax these conditions, and include all employees.

Supervisors

Data regarding supervisors is also stored in the Employees table. Therefore, to include it in the query, I used a self join.

SELECT
	Employee.ID AS EmployeeID,	
	Employee.LastName,
	Employee.FirstName,
	Employee.Title,
	Employee.Email,
	Supervisor.LastName AS SupervisorLastName,
	Supervisor.FirstName AS SupervisorFirstName,
	Supervisor.Title AS SupervisorTitle,
	Supervisor.Email AS SupervisorEmail
FROM
	Employee
	INNER JOIN Employee AS Supervisor ON
		( Supervisor.ID = Employee.Supervisor )
WHERE
	( Employee.IsInactive = 'F' )
	AND ( Employee.GiveAccess = 'T' )
ORDER BY
	Employee.LastName,
	Employee.FirstName

As you can see, the join is being made from the Employee table to itself, with the second reference aliased as "Supervisor." To avoid confusion when working with the results, I've also aliased the columns being returned from that second instance.

Roles

To include the roles that each employee is assigned to, I joined to two additional tables.

I first joined to the EmployeeRolesForSearch table. The EmployeeRolesForSearch table maps employees to the roles that they are assigned to. Keep in mind that an employee can be assigned to multiple roles.

By joining to the EmployeeRolesForSearch, I had only the IDs of the roles that an employee is assigned to. To get the role names, I also had to join to the Role table.

SELECT
	Employee.ID AS EmployeeID,	
	Employee.LastName,
	Employee.FirstName,
	Employee.Title,
	Employee.Email,
	Supervisor.LastName AS SupervisorLastName,
	Supervisor.FirstName AS SupervisorFirstName,
	Supervisor.Title AS SupervisorTitle,
	Supervisor.Email AS SupervisorEmail,
	Role.Name AS RoleName
FROM
	Employee
	INNER JOIN Employee AS Supervisor ON
		( Supervisor.ID = Employee.Supervisor )
	INNER JOIN EmployeeRolesForSearch ON
		( EmployeeRolesForSearch.Entity = Employee.ID )
	INNER JOIN Role ON
		( Role.ID = EmployeeRolesForSearch.Role )
		AND ( Role.IsInactive = 'F' )
WHERE
	( Employee.IsInactive = 'F' )
	AND ( Employee.GiveAccess = 'T' )
ORDER BY
	Employee.LastName,
	Employee.FirstName

Notice that in the join to the Role table, I'm indicating that I only want roles returned that are active.

Permissions

The final step in creating the SuiteQL query was to add the permissions that each employee has, as a result of the roles that they are assigned to. Roles can, and usually do, have multiple permissions associated with them. To add the permissions, I joined from the Role table to the RolePermissions table.

SELECT
	Employee.ID AS EmployeeID,	
	Employee.LastName,
	Employee.FirstName,
	Employee.Title,
	Employee.Email,
	Supervisor.LastName AS SupervisorLastName,
	Supervisor.FirstName AS SupervisorFirstName,
	Supervisor.Title AS SupervisorTitle,
	Supervisor.Email AS SupervisorEmail,
	Role.Name AS RoleName,
	RolePermissions.Name AS PermissionName,
	BUILTIN.DF( RolePermissions.PermLevel ) AS PermissionLevel
FROM
	Employee
	INNER JOIN Employee AS Supervisor ON
		( Supervisor.ID = Employee.Supervisor )
	INNER JOIN EmployeeRolesForSearch ON
		( EmployeeRolesForSearch.Entity = Employee.ID )
	INNER JOIN Role ON
		( Role.ID = EmployeeRolesForSearch.Role )
		AND ( Role.IsInactive = 'F' )
	INNER JOIN RolePermissions ON
		( RolePermissions.Role = Role.ID )	
WHERE
	( Employee.IsInactive = 'F' )
	AND ( Employee.GiveAccess = 'T' )
ORDER BY
	Employee.LastName,
	Employee.FirstName

That final join is fairly straightforward. I do want to point out that I'm using the BUILTIN.DF function to map the numeric ID of the permission level (View, Create, Edit, Full) that has been granted.

Wrapping Up

This was a interesting request to work on. In the process, I discovered three tables - EmployeeRolesForSearch, Role, and RolePermissions - that I had not previously worked with.

As always, I hope you find the queries in this post to be helpful. If you have any questions, feel free to contact me.