Published on July 15, 2020.

I recently wrapped up a project that involved implementing PromoStandards-based services for a client in the promotional products industry. The client has been running on NetSuite for more than a decade. As a result, there is a lot of data to work with, and that made working on this project particularly interesting.

I've written in the past about how I've been using SuiteTalk REST Web Services support for SuiteQL to work, and this project is a great example of that. Using SuiteQL, I was able to use SQL queries to gather the data needed to implement the PromoStandards-based services. In this post, I'll discuss how I used SuiteQL for the project, and share some the SQL as well.

PromoStandards

Before I get started with queries, if you aren't familiar with PromoStandards, here's a very brief overview.

PromoStandards is an organization whose goal is to streamline business processes in the promotional products industry by providing standard methods for exchanging data, including inventory information, order statuses, and more. For the past few years, I've been helping distributors integrate PromoStandards into their systems, and helping suppliers to provide PromoStandards-based Web services to their customers. To learn more about PromoStandards, visit the organization's Web site: https://promostandards.org

In this post, I'll discuss two PromoStandards services in particular: a service based on the Inventory specification (and version 1.2.1 in particular), and a service based on the Order Status specification (which is currently at version 1.0.0).

Inventory

The PromoStandards Inventory service is designed to provide current inventory levels ("quantity on hand") for a specific product. There are a number of ways to call the service, but typically a distributor will do so by specifying a product number (productID).

The query that I used to implement this service is really quite basic. Here's a snippet of the SQL.

SELECT 
	ParentItem.ItemID AS productID,	
	ChildItem.ItemID AS partID, 
	ChildItem.Description AS partDescription,
	ChildItem.QuantityAvailable
FROM 
	Item AS ParentItem
	INNER JOIN Item AS ChildItem ON
		( ChildItem.Parent = ParentItem.ID )
WHERE 
	( ParentItem.ItemType = 'InvtPart' )
	AND ( ParentItem.IsInactive = 'F' )
	AND ( ChildItem.IsInactive = 'F' )
	AND ( ParentItem.ItemID = '100102' )
ORDER BY 
	ParentItem.ItemID ASC,
	ChildItem.ItemID ASC

In the query, I'm requesting active inventory items, and joining parent items to their children. The assumption - and it's a safe one in this client's case - is that all items are setup in this parent / child configuration.

The performance of this query is amazing. My client has nearly 6,000 inventory items in their NetSuite instance, and yet the query response is returned in .6 seconds on average.

Order Status

The PromoStandards Order Status service does exactly what you'd expect: It provides current statuses for customer sales orders.

Here's the query that I used to handle requests where a customer has requested the status of an order based on their purchase order number.

SELECT
	Transaction.OtherRefNum AS purchaseOrderNumber,
	Transaction.TranID AS factoryOrderNumber,
	CASE
		Transaction.Status
		WHEN 'A' THEN 10
		WHEN 'B' THEN 20
		WHEN 'C' THEN 99
		WHEN 'D' THEN 75
		WHEN 'E' THEN 75
		WHEN 'F' THEN 75
		WHEN 'G' THEN 80
		WHEN 'H' THEN 80
		ELSE 40
		END AS statusID,
	CASE
		Transaction.Status
		WHEN 'A' THEN 'Order Received'
		WHEN 'B' THEN 'Order Confirmed'
		WHEN 'C' THEN 'Canceled'
		WHEN 'D' THEN 'Partial Shipment'
		WHEN 'E' THEN 'Partial Shipment'
		WHEN 'F' THEN 'Partial Shipment'
		WHEN 'G' THEN 'Complete'
		WHEN 'H' THEN 'Complete'
		ELSE 'General Hold'
		END AS statusName		
FROM
	Transaction
WHERE
	( Transaction.Type = 'SalesOrd' )
	AND ( Transaction.Entity = 66245 )
	AND ( Transaction.OtherRefNum = '202006-001' )

Here's the query, along with a sample SuiteTalk / SuiteQL response, as it appears in the SuiteQL Query Tool that I write about a few months ago.

In this query, I'm filtering on sales order transactions, and specifying the customer's internal NetSuite ID (Entity), as well as their purchase order number (OtherRefNum). Note that it is possible for the query to return multiple records, because a customer's purchase order might be split into multiple sales orders on the supplier side.

The only challenge with this query was mapping NetSuite's statuses to the status codes supported by the Order Status standard. To see a list of the statuses that that the standard supports, refer to the standard's documentation, and specifically the "Possible Values for Status." Each status has both a numeric value (statusID) and a descriptive name (statusName). The PromoStandards statuses don't map one-to-one with NetSuite's statuses, and as a result, you'll likely have to work with a supplier to determine how best to calculate the statuses to return.

To handle the status mappings, I used two CASE expressions. One maps the NetSuite status code to PromoStandards numeric status codes, which the other maps the NetSuite status code to PromoStandards status names.

This query's performance is also impressive. As I mentioned above, my client has been using NetSuite for more than a decade. In that time, they've processed well over 200,000 orders. Regardless of the volume of sales orders in the instance, SuiteTalk is, on average, able to process the query and return a response in .6 seconds.

Wrapping Up

The SQL that I've shared in this post is just one component of the solution that I developed for my client. Those queries are called via a custom PHP-based middleware layer, which receives the SOAP-based PromoStandards requests, authenticates the requests, and processes them. It also implements caching to improve performance and take load off of NetSuite. And the middleware solution runs behind nginx, which handles load balancing, rate limiting, and more.