Published on November 23, 2020.

I'm currently working on a NetSuite integration project for a client that uses Pacejet for its shipping and fulfillment operations. The integration requires accessing the package information that Pacejet generates. If you've been following my blog for awhile, it will probably come as no surprise to you that I used SuiteQL to access that data.

In this post, I'll share a few of the queries that I worked up as part of the project. I'll start with a query that shows how to get Item Fulfillment data, then show how to access the Pacejet data, and wrap things up by combining the two queries.

Item Fulfillments

In NetSuite, Item Fulfillments are stored in the Transaction table. The following query returns all Item Fulfillments in a specified date/time range.

SELECT
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	Transaction.Entity AS CustomerID,
	BUILTIN.DF( Transaction.Entity ) AS CustomerName,
	(
		SELECT DISTINCT
			SO.TranID
		FROM
			PreviousTransactionLineLink AS PTLL
			INNER JOIN Transaction AS SO ON
				( SO.ID = PTLL.PreviousDoc )
		WHERE
			( PTLL.NextDoc = Transaction.ID )
			AND ( SO.Type = 'SalesOrd' )
	) AS SalesOrder
FROM
	Transaction
WHERE
	( Transaction .Type = 'ItemShip' )
	AND ( Transaction.TranDate BETWEEN 
		TO_DATE( '2020-11-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss' ) 
		AND TO_DATE( '2020-11-30 23:59:59', 'YYYY-MM-DD hh24:mi:ss' ) 
	)

There isn't too much involved with that query, with two exceptions. First, there's the use of the BUILTIN.DF function, which maps the transaction's entity from a numeric value (the entity's ID) to a text value (the entity's name). Second, I'm using a correlated subquery to get the Sales Order that the Item Fulfillment was generated from. That subquery involves the PreviousTransactionLineLink table, which serves as a join table between a transaction and its previous and/or next transaction type as it moves through its lifecycle. For example, it joins a Sales Order to an Item Fulfillment, an Item Fulfillment to an Invoice, and so on.

Pacejet Package Information

Pacejet stores package information in a custom table named "CustomRecord_Pacejet_Package_Info." Here's a query that returns packages for a specified Item Fulfillment transaction ID.

SELECT
	CustRecord_Pacejet_Package_ID AS Package_ID,
	CustRecord_Pacejet_Package_Tracking AS Tracking_Number,
	CustRecord_Pacejet_Package_Tracking_Link AS Tracking_Link,
	CustRecord_Pacejet_Package_Contents AS Contents,
	CustRecord_Pacejet_Package_Weight AS Weight
FROM 
	CustomRecord_Pacejet_Package_Info
WHERE
	CustRecord_Pacejet_Package_ID LIKE TO_CHAR( 1204394 ) || '%'

This is another simple query, with the exception of the WHERE clause. Pacejet assigns CustRecord_Pacejet_Package_ID values based on the ID of the transaction that the packages are associated with, followed by a dash and sequential value. For example, a package for Item Fulfillment 1204394 would be stored as "1204394-001." Therefore, in the WHERE clause, I'm taking the transaction ID, converting it to a text value, and appending the "%" wildcard to it - and then using that value with a LIKE predicate.

Joining Item Fulfillments With Pacejet Package Info

Now that we have queries that return Item Fulfillments and Pacejet Package Info, let's see what it takes to combine the two. Here's the query.

SELECT
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	Transaction.Entity AS CustomerID,
	BUILTIN.DF( Transaction.Entity ) AS CustomerName,
	(
		SELECT DISTINCT
			SO.TranID
		FROM
			PreviousTransactionLineLink AS PTLL
			INNER JOIN Transaction AS SO ON
				( SO.ID = PTLL.PreviousDoc )
		WHERE
			( PTLL.NextDoc = Transaction.ID )
			AND ( SO.Type = 'SalesOrd' )
	) AS SalesOrder,
	CustRecord_Pacejet_Package_ID AS Package_ID,
	CustRecord_Pacejet_Package_Tracking AS Tracking_Number,
	CustRecord_Pacejet_Package_Tracking_Link AS Tracking_Link,
	CustRecord_Pacejet_Package_Contents AS Contents,
	CustRecord_Pacejet_Package_Weight AS Weight	
FROM
	Transaction
	INNER JOIN MAP_customrecord_pacejet_package_info_custrecord_pacejet_transaction_link AS Map_Links ON
		( Map_Links.MapTwo = Transaction.ID )
	INNER JOIN CustomRecord_Pacejet_Package_Info AS Package_Info ON
		( Package_Info.ID = Map_Links.MapOne )
WHERE
	( Transaction .Type = 'ItemShip' )
	AND ( Transaction.TranDate BETWEEN 
		TO_DATE( '2020-11-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss' ) 
		AND TO_DATE( '2020-11-30 23:59:59', 'YYYY-MM-DD hh24:mi:ss' ) 
	)

Combining the queries requires adding two JOINs. The first one joins the Transaction table to a join table named "MAP_customrecord_pacejet_package_info_custrecord_pacejet_transaction_link." The second joins the MAP_customrecord_pacejet_package_info_custrecord_pacejet_transaction_link table to the CustomRecord_Pacejet_Package_Info table.

The query can be used to get all Item Fulfillments and Pacejet Packages that occurred in a specified date/time range. But you could easily modify the query to return packages for a specified sales order, customer, and so on.

Wrapping Up

This is one of the SuiteQL queries that I'm using for my client's integration project, but it's an important one. It was interesting to have an opportunity to use SuiteQL to query a custom table from a third party developer.

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