Published on February 20, 2022.

I've recently been working on a custom application for a NetSuite client to help them with production planning. It's been a very interesting project, requiring the use of work orders, assembly items, and more.

In this post, I'm going to share some of the queries that I developed for the application. Specifically, I'll share queries involving work orders, show how you can get the sales orders that they were created for, and more.

Work Order Transactions

Let's start with a simple query that returns all of the work orders that are released. Work orders are stored in the Transaction table, and their transaction type is "WorkOrd."

Here's the query.

SELECT
	WorkOrder.ID,
	WorkOrder.TranID,
	BUILTIN.DF( WorkOrder.Status ) AS Status,
	BUILTIN.DF( WorkOrder.Entity ) AS Customer
FROM
	Transaction AS WorkOrder		
WHERE
	( WorkOrder.Type = 'WorkOrd' )
	AND ( BUILTIN.CF( WorkOrder.Status ) = 'WorkOrd:B' )

Notice the use of the BUILTIN.DF function, which I'm using to get the names of the work order statuses and the names of the customers associated with the work orders.

Also notice that I'm using the BUILTIN.CF function in the WHERE clause to indicate that I only want work orders that have been released. I wrote about the BUILTIN.CF function in a post titled SuiteQL-Based Sales Reports, Transaction Statuses, and the WITH Clause back in December. Think of BUILTIN.CF as the WHERE clause-equivalent of the SELECT clause's BUILTIN.DF function.

Work Order Statuses

If you're interested in seeing the other work order statuses, run this query. It returns a complete list of the various statuses that work orders are currently in.

SELECT DISTINCT
	Status,
	BUILTIN.DF( Status ) AS DFStatus,
	BUILTIN.CF( Status ) AS CFStatus
FROM
	Transaction
WHERE
	( Transaction.Type = 'WorkOrd' )
ORDER BY
	Status

If you're interested in seeing the number of work orders in each status, you can use this query:

SELECT
	Status,
	BUILTIN.DF( Status ) AS DFStatus,
	BUILTIN.CF( Status ) AS CFStatus,
	COUNT(*)
FROM
	Transaction
WHERE
	( Transaction.Type = 'WorkOrd' )
GROUP BY
	Status,
	BUILTIN.DF( Status ),
	BUILTIN.CF( Status )	
ORDER BY
	Status

Related Sales Orders

Suppose that in the results we also want to see the sales order associated with each of the work orders. There are two things to keep in mind.

First, the ID of the sales order that a work order was created for is not stored at the header level of the work order. In other words, there's no column on the Transaction table that contains the sales order ID. Instead, that value is stored at the lineitem level.

Second, we also need to keep in mind that it is entirely possible for a work order to exist without it having been originated from a sales order. So we need to be careful with regards to the types of joins that we're using.

Let's look at the query and then review it.

SELECT
	WO.ID AS WOID,
	WO.TranID AS WONumber,
	BUILTIN.DF( WO.Status ) AS WOStatus,
	BUILTIN.DF( WO.Entity ) AS Customer,
	SO.ID AS SOID,
	SO.TranID AS SONumber
FROM
	Transaction AS WO	
	INNER JOIN TransactionLine AS WOLine ON
		( WOLine.Transaction = WO.ID )
		AND ( WOLine.MainLine = 'T' )	
	LEFT OUTER JOIN Transaction AS SO ON
		( SO.ID = WOLine.CreatedFrom )
WHERE
	( WO.Type = 'WorkOrd' )
	AND ( BUILTIN.CF( WO.Status ) = 'WorkOrd:B' )

Notice that I'm now joining from the Transaction table to the TransactionLine table. I'm joining on the Transaction ID value, and specifying that I only want the "mainline." The mainline filter prevents a single work order from being returned multiple times.

To get to the Sales Order, I'm using the TransactionLine's "CreatedFrom" value, and joining to the Transaction table a second time. By using a table alias, I can refer to the same table in multiple ways. This is one of the reasons that table aliases are so important and helpful.

And finally, notice that in the join from the lineitem to the sales order transaction, I'm using a LEFT OUTER JOIN. Had I used an INNER JOIN, I would have inadvertently excluded work orders that did not originate from sales orders.

What's Being Produced?

So far we've looked at queries that give you work orders at the header level. But what if we're interested in the lineitem level information? For example, suppose we want to know the total quantities of the assembly items on the released work orders.

Here's a query that returns that information.

SELECT
	BUILTIN.DF( WOLine.Item ) AS ItemID,
	Item.ItemType,
	SUM( WOLine.Quantity )
FROM
	Transaction AS WO	
	INNER JOIN TransactionLine AS WOLine ON
		( WOLine.Transaction = WO.ID )
	INNER JOIN Item ON
		( Item.ID = WOLine.Item )
WHERE
	( WO.Type = 'WorkOrd' )
	AND ( BUILTIN.CF( WO.Status ) = 'WorkOrd:B' )
	AND ( Item.ItemType = 'Assembly' )
GROUP BY
	BUILTIN.DF( WOLine.Item ),
	Item.ItemType
ORDER BY
	ItemID

If you're interested in seeing all of the items that will need to produced and the components that will be needed, you can remove the "AND ( Item.ItemType = 'Assembly' )" statement from the WHERE clause.

Basic Production Planning

Now that we have the work orders and their related sales orders, we can use values from each to do some very basic analysis and production planning.

On the work order we have the Production End Date, which NetSuite defines as "the date you expect to complete assembly production." On the sales order, we have the Customer Ship Date, which is the shipping date for the order. Using those two dates and a basic date arithmetic function, we can determine the number of days between the two dates.

Here's a revised query.

SELECT
	WO.ID AS WOID,
	WO.TranID AS WONumber,
	BUILTIN.DF( WO.Status ) AS WOStatus,
	BUILTIN.DF( WO.Entity ) AS Customer,
	SO.ID AS SOID,
	SO.TranID AS SONumber,
	SO.ShipDate,
	WO.EndDate,	
	( TRUNC ( SO.ShipDate ) - TRUNC  ( WO.EndDate ) ) AS DeltaDays
FROM
	Transaction AS WO	
	INNER JOIN TransactionLine AS WOLine ON
		( WOLine.Transaction = WO.ID )
		AND ( WOLine.MainLine = 'T' )	
	LEFT OUTER JOIN Transaction AS SO ON
		( SO.ID = WOLine.CreatedFrom )
WHERE
	( WO.Type = 'WorkOrd' )
	AND ( BUILTIN.CF( WO.Status ) = 'WorkOrd:B' )

As you can see, I've included the two date columns to the results, and I'm subtracting the work order's "production end date" from the sales order's "ship date." (I'm using the TRUNC function on the dates so that the calculation is based only date portions of the two columns, which are actually timestamps.) That value represents the number of days between the date that we're expecting the work order to be completed, and the date that the customer is expecting their order to ship. If that value is negative, then we're likely going to be shipping the order late. If the delta is low, then we don't have much "wiggle room" so to speak. So if we're doing production planning, we might want to prioritize these work orders.

Again, this is a very, very basic approach to production planning. In reality, there's usually much more to this process. But it does give you some idea as to what's possible.

What's In The Pipeline?

Let's look at one more query that might be helpful to you. Suppose that we want to know what's in the pipeline that we might want to plan for. In other words, what sales orders are queued up, pending approval, that are likely going to result in the creation of work orders?

Here's a query that shows that information, including the customers, the assembly items, the quantities involved, and the ship dates.

SELECT
	SO.ID,
	SO.TranID,
	BUILTIN.DF( SO.Status ) AS SOStatus,
	BUILTIN.DF( SO.Entity ) AS Customer,
	Item.ItemID,
	SOLine.Quantity,
	SO.ShipDate
FROM
	Transaction AS SO	
	INNER JOIN TransactionLine AS SOLine ON
		( SOLine.Transaction = SO.ID )	
	INNER JOIN Item ON
		( Item.ID = SOLine.Item )
WHERE
	( SO.Type = 'SalesOrd' )
	AND ( BUILTIN.CF( SO.Status ) = 'SalesOrd:A' )
	AND ( Item.ItemType = 'Assembly' )

We can also look at quotes / estimates that are in the pipeline. To do that, we'd query the Transaction tables for Estimates that have a status of Open.

Here's that query.

	
SELECT
	Estimate.ID,
	Estimate.TranID,
	BUILTIN.DF( Estimate.Status ) AS EstimateStatus,
	BUILTIN.DF( Estimate.Entity ) AS Customer,
	Item.ItemID,
	Item.ItemType,
	EstimateLine.Quantity,
	Estimate.ShipDate,
	Estimate.Probability,
	Estimate.ExpectedCloseDate
FROM
	Transaction AS Estimate	
	INNER JOIN TransactionLine AS EstimateLine ON
		( EstimateLine.Transaction = Estimate.ID )	
	INNER JOIN Item ON
		( Item.ID = EstimateLine.Item )
WHERE
	( Estimate.Type = 'Estimate' )
	AND ( BUILTIN.CF( Estimate.Status ) = 'Estimate:A' )
	AND ( Item.ItemType = 'Assembly' )

I've added a few potentially helpful columns to the results: Probability (the probability that an estimate will be converted to a sale) and ExpectedCloseDate (the date the estimate is expected to close).

Wrapping Up

In this post, I've discussed Work Orders and provided a few examples of queries that I think you'll find helpful - especially if you're trying to report on and/or analyze your production process. I've only scratched the surface of what's possible.

In a future post, I'll discuss assembly items, and show how you can query for their components.