Published on December 1, 2020.

While working on a recent NetSuite project, my client asked for a SuiteQL query that can be used to generate a summary of purchase order item receipts for a given date range. This was the first time that I had queried for item receipts, so I thought I'd share the query, and review the process of putting it together.

Item Receipts

As you might expect, item receipts are stored in the Transaction and TransactionLine tables. Here's my initial query, which simply joins the two tables, and filters based on the transaction type and date range.

SELECT
	Transaction.TranDate AS DateReceived,	
	BUILTIN.DF( Transaction.Entity ) AS Supplier,	
	Transaction.TranID AS ReceiptID,
	TransactionLine.Quantity AS QuantityReceived
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.IsInventoryAffecting = 'T' )
	INNER JOIN Transaction AS PO ON
		( PO.ID = TransactionLine.CreatedFrom )
WHERE
	( Transaction.Type = 'ItemRcpt' )
	AND ( Transaction.TranDate BETWEEN TO_DATE('2020-11-27', 'YYYY-MM-DD' ) AND TO_DATE('2020-11-30', 'YYYY-MM-DD' ) )	
	AND ( Transaction.Voided = 'F' )
ORDER BY
	DateReceived,
	Supplier

Let's review a few of the query's components.

In the SELECT clause, notice the use of the BUILTIN.DF function. I'm using that to get the supplier's name, and by using the function, I avoid a join.

In the FROM clause, notice the join between the Transaction and TransactionLine tables. Specifically, notice that the join is on the Transaction IDs, and that I've indicated that the join should only be made to transaction lines where "IsInventoryAffecting = 'T'." This specifies that we only want to join to transaction lines that have an impact on the inventory balance.

In the WHERE clause, I'm filtering on the transaction type so that only item receipts ("ItemRcpt") are included. I'm also filtering based on the transaction date, and I'm using the TO_DATE function to cast the date values from strings to date values. I'm also filtering out voided transactions.

Joining to the Item Table

The next step is to join to the Item table, so that the query returns item IDs and descriptions.

SELECT
	Transaction.TranDate AS DateReceived,	
	BUILTIN.DF( Transaction.Entity ) AS Supplier,	
	Transaction.TranID AS ReceiptID,
	Item.ItemID,
	Item.Description AS ItemDescription,
	TransactionLine.Quantity AS QuantityReceived
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.IsInventoryAffecting = 'T' )
	INNER JOIN Transaction AS PO ON
		( PO.ID = TransactionLine.CreatedFrom )
	INNER JOIN Item ON
		( Item.ID = TransactionLine.Item )	
WHERE
	( Transaction.Type = 'ItemRcpt' )
	AND ( Transaction.TranDate BETWEEN TO_DATE('2020-11-27', 'YYYY-MM-DD' ) AND TO_DATE('2020-11-30', 'YYYY-MM-DD' ) )	
	AND ( Transaction.Voided = 'F' )
ORDER BY
	DateReceived,
	Supplier

Joining to the Purchase Order Lineitems

Let's take the query one step further, and join the item receipts to their related purchase order lineitems. This requires the use of the PreviousTransactionLineLink table, which I recently discussed in "NetSuite: Use SuiteQL to Query Item Fulfillments and Pacejet Package Info."

SELECT
	Transaction.TranDate AS DateReceived,	
	BUILTIN.DF( Transaction.Entity ) AS Supplier,	
	PO.TranID AS PONumber,
	POLine.LineSequenceNumber AS POLineNumber,	
	Transaction.TranID AS ReceiptID,
	Item.ItemID,
	Item.Description AS ItemDescription,
	POLine.Quantity AS QuantityOrdered,	
	TransactionLine.Quantity AS QuantityReceived,
	POLine.QuantityShipRecv AS TotalReceived,
	( POLine.Quantity - POLine.QuantityShipRecv) AS QuantityOpen
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.IsInventoryAffecting = 'T' )
	INNER JOIN Transaction AS PO ON
		( PO.ID = TransactionLine.CreatedFrom )
	INNER JOIN Item ON
		( Item.ID = TransactionLine.Item )
	LEFT OUTER JOIN PreviousTransactionLineLink AS PTLL ON
		( PTLL.NextType = 'ItemRcpt' )
		AND ( PTLL.NextDoc = TransactionLine.Transaction )
        AND ( PTLL.NextLine = TransactionLine.ID )
	LEFT OUTER JOIN TransactionLine AS POLine ON
		( POLine.Transaction = PO.ID )
		AND ( POLine.linesequencenumber = PTLL.PreviousLine )
WHERE
	( Transaction.Type = 'ItemRcpt' )
	AND ( Transaction.TranDate BETWEEN TO_DATE('2020-11-27', 'YYYY-MM-DD' ) AND TO_DATE('2020-11-30', 'YYYY-MM-DD' ) )	
	AND ( Transaction.Voided = 'F' )
	AND ( TransactionLine.Quantity <> 0 )
ORDER BY
	DateReceived,
	Supplier,
	PONumber,	
	ItemID

Notice the join to the PreviousTransactionLineLink table, and the subsequent join to the TransactionLine table to get to the source purchase order lineitem. Both joins are LEFT OUTER JOINs, and I'm using those because I have found cases where, for some item receipts, there is no corresponding entry in the PreviousTransactionLineLink. I'm not sure why that is, but I suspect it has to do with how the item was received. In any case, these additional joins allow us to connect from the item receipt lines back to the source PO, based on the lineitem sequence numbers.

Also notice that I've added a few additional columns to the SELECT clause so that we can show the total quantity ordered of each item, and any open balance.

Wrapping Up

With the exception of the join to the PreviousTransactionLineLink table, this was a relatively simple query. As I've demonstrated in previous posts, using the BUILTIN.DF function can often save you from having to make additional joins. Also, the more familiar you are with the Transaction and TransactionLine tables, where all of the NetSuite's transaction-based data is stored, the easier it is to create these types of queries.

As always, I hope these queries prove to be helpful. If you have any questions, please feel free to reach out to me.