Published on November 4, 2020.

One of my NetSuite clients is in the process of developing a custom mobile app for their sales team. The developer of the app requested an API that they could use to retrieve sales-related data from NetSuite.

Specifically, they requested that the API provide them with data that they could use to present salesreps and their managers with information about their "Sales Pipeline" (based on sales orders that are scheduled to ship in a specified date range) as well as their "Estimated Commissions" (based on sales orders that actually did ship in a specified date range). They also requested data that can be used to present the "Top Orders of the Day" based on the estimated gross profit of the sales orders.

Developing SuiteQL queries to fulfill this type of request is easy to do, because all of the data that is needed is available in the Transaction table. Let's take a look at the queries that I developed.

Sales Pipeline - Detailed

Let's start with a query that retrieves sales orders that are scheduled to ship in a specified date range.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	Transaction.ShipDate AS ScheduledShipDate,
	Transaction.ActualShipDate,
	Transaction.ForeignTotal,
	Transaction.TotalCostEstimate,
	Transaction.EstGrossProfit,
	ROUND( ( Transaction.EstGrossProfitPercent * 100 ), 2 ) AS GPPct,
	Employee.LastName,
	Employee.FirstName,
	BUILTIN.DF( Transaction.Entity ) AS Customer
FROM
	Transaction
	INNER JOIN Employee ON
		( Employee.ID = Transaction.Employee )		
WHERE
	( Transaction.Type = 'SalesOrd' )
	AND ( Transaction.ShipDate BETWEEN TO_DATE( '2020-10-24', 'YYYY-MM-DD' ) AND TO_DATE( '2020-10-31', 'YYYY-MM-DD' ) )
	AND ( Transaction.Voided = 'F' )

Notice that I'm using two functions in the query: ROUND and BUILTIN.DF. I'm using the ROUND function to round the gross profit percentage to two decimal places. I'm using the BUILTIN.DF function to return the customer's name, instead of their internal NetSuite-assigned ID. This eliminates the need to join to the Entity table.

Also notice that I'm filtering on the transaction type ("SalesOrd" to indicate that we're working with Sales Orders), ShipDate (which is the date that an order is expected / scheduled to ship), and I'm filtering out voided transactions.

As for the columns that the are being returned, I'm including the three date-related values (transaction date, the scheduled ship date, and the actual ship date). I'm also including the "ForeignTotal" (the total sale amount), the TotalCostEstimate (the estimated cost of the order), and the EstGrossProfit (the estimated gross profit for the order) columns. Those columns are extremely helpful, and eliminate the need to sum up the transaction lines to get the totals.

I've joined from the Transaction table to the Employee table to get the record associated with each order's salesperson. Note that I could have used the BUILTIN.DF function to do this, and avoided this additional join. However, I am anticipating that the developer is going to request additional data that is stored on the Employee record - such as custom fields that the company uses to determine each rep's internal sales team, commission plan, and so on.

Sales Pipeline - Summarized By Sales Person

Creating the query that is needed to summarize the sales pipeline is as easy as grouping and summarizing the columns used in the previous query.

SELECT
	Employee.LastName,
	Employee.FirstName,
	SUM( Transaction.ForeignTotal ) AS TotalSales,
	SUM( Transaction.TotalCostEstimate ) AS TotalCostEstimate,	
	SUM( Transaction.EstGrossProfit ) AS TotalGPEstimate
FROM
	Transaction
	INNER JOIN Employee ON
		( Employee.ID = Transaction.Employee )	
WHERE
	( Transaction.Type = 'SalesOrd' )
	AND ( Transaction.ShipDate BETWEEN TO_DATE( '2020-11-01', 'YYYY-MM-DD' ) AND TO_DATE( '2020-11-30', 'YYYY-MM-DD' ) )
	AND ( Transaction.Voided = 'F' )
GROUP BY
	Employee.LastName,
	Employee.FirstName
ORDER BY
	Employee.LastName,
	Employee.FirstName

For the most part, this query is identical to the previous query. The key differences are that I'm summing the ForeignTotal, TotalCostEstimate, and EstGrossProfit columns, and grouping on the employee's name.

Sales Orders Shipped - Detailed

To provide the data needed for commissions, I took the original query - which was based on the scheduled ship date - and used the actual ship date (ActualShipDate) instead.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	Transaction.ShipDate AS ScheduledShipDate,
	Transaction.ActualShipDate,
	Transaction.ForeignTotal,
	Transaction.TotalCostEstimate,
	Transaction.EstGrossProfit,
	ROUND( ( Transaction.EstGrossProfitPercent * 100 ), 2 ) AS GPPct,
	Employee.LastName,
	Employee.FirstName,
	BUILTIN.DF( Transaction.Entity ) AS Customer
FROM
	Transaction
	INNER JOIN Employee ON
		( Employee.ID = Transaction.Employee )		
WHERE
	( Transaction.Type = 'SalesOrd' )
	AND ( Transaction.ActualShipDate BETWEEN TO_DATE( '2020-10-24', 'YYYY-MM-DD' ) AND TO_DATE( '2020-10-31', 'YYYY-MM-DD' ) )
	AND ( Transaction.Voided = 'F' )

Keep in mind that this is a very basic way to provide the data needed for commission purposes. It is only providing estimated gross profits, based on the estimated cost of the orders. For some businesses, with simple commission plans, this might might be all that they need. But again, in most cases this would only serve as an estimate.

Regardless, the data returned is still interesting, and helpful to a sales rep that wants to get a sense of their orders that have shipped, and the potential gross profit of those orders.

Sales Orders Shipped - Summarized By Sales Person

Creating a summarized version of the "Sales Orders Shipped" query is a simple matter of adjusting the query used for the detailed version. I'm grouping by employee, and summarizing the sales, cost, and gross profit values.

SELECT
	Employee.LastName,
	Employee.FirstName,
	SUM( Transaction.ForeignTotal ) AS TotalSales,
	SUM( Transaction.TotalCostEstimate ) AS TotalCostEstimate,	
	SUM( Transaction.EstGrossProfit ) AS TotalGPEstimate
FROM
	Transaction
	INNER JOIN Employee ON
		( Employee.ID = Transaction.Employee )	
WHERE
	( Transaction.Type = 'SalesOrd' )
	AND ( Transaction.ActualShipDate BETWEEN TO_DATE( '2020-11-01', 'YYYY-MM-DD' ) AND TO_DATE( '2020-11-30', 'YYYY-MM-DD' ) )
	AND ( Transaction.Voided = 'F' )
GROUP BY
	Employee.LastName,
	Employee.FirstName
ORDER BY
	Employee.LastName,
	Employee.FirstName

Top Orders of the Day

The final request was to provide data used to display the top orders of the day, based on the estimated gross profit of the orders. The query that I created does that and a little more. It can actually be used to retrieve the top orders based on whatever data range is desired. So it could be used to present the top orders of the day, week, month, and so on.

SELECT * FROM
(
	SELECT
		Transaction.TranID,
		Transaction.TranDate,
		Transaction.ShipDate AS ScheduledShipDate,
		Transaction.ActualShipDate,
		Transaction.ForeignTotal,
		Transaction.TotalCostEstimate,
		Transaction.EstGrossProfit,
		ROUND( ( Transaction.EstGrossProfitPercent * 100 ), 2 ) AS GPPct,
		Employee.LastName,
		Employee.FirstName,
		BUILTIN.DF( Transaction.Entity ) AS Customer
	FROM
		Transaction
		INNER JOIN Employee ON
			( Employee.ID = Transaction.Employee )		
	WHERE
		( Transaction.Type = 'SalesOrd' )
		AND ( Transaction.TranDate BETWEEN TO_DATE( '2020-11-01', 'YYYY-MM-DD' ) AND TO_DATE( '2020-11-30', 'YYYY-MM-DD' ) )
		AND ( Transaction.Voided = 'F' )
	ORDER BY
		Transaction.EstGrossProfit DESC
)
WHERE
	ROWNUM <= 5

Of note with this query is the use of a non-correlated subquery. The inner query returns a recordset consisting of the sales orders that were placed in a specified date range, and the rows are returned in descending order by the estimate gross profit. The outer query takes that result and returns only the first 5 rows.

The reason that I'm using this approach is that SuiteQL does not support Offset-Fetch clauses. While the use of a non-correlated subquery works, it has one important limitation: It doesn't support ties. So in the query above, only the first 5 records, regardless of whether the 6th record "tied" the 5th record's EstGrossProfit value.

Need Help With SuiteQL?

If you have any questions about the SuiteQL queries that I've shared in this post, or need help developing other queries, please feel free to contact me.