Published on February 3, 2024.

Back in August of 2022, I wrote about how you can use SuiteQL to get a Vendor Bill's related Purchase Order. (See "Use SuiteQL To Get A Vendor Bill's Related Purchase Order.")

This week I was working on a project for a NetSuite client, and it involved developing a similar query - except this time I needed to go in the opposite direction. I needed a query that would return any Vendor Bills that have been received and entered against a specific Purchase Order. This was a small but important part of what was actually a much larger project.

The primary goal of this first query is to be able to see the status of a purchase order in terms of its original amount compared with the anything that has been billed against it. This is to determine if the order's allocated amount has been exhausted, and if not, how much of what had been allocated is still available. The query also needs to take into account any prepayments that have been made, and the client wanted to see related transactions such as item receipts, returns, credits, and so on.

For this example, here's the Purchase Order that I started with.

Click the image to view a larger version.

A Prepayment was made for the order. Here's what that looked like.

Click the image to view a larger version.

Items were received against the PO. Here's the Item Receipt.

Click the image to view a larger version.

And a Vendor Bill was received a few days later. Here it is.

Click the image to view a larger version.

Here's the initial query that I developed.

SELECT
	PO.TranID AS PONumber,
	PO.TranDate AS DateOrdered,
	BUILTIN.DF( PO.Entity ) AS Supplier,
	BUILTIN.DF( PO.Status ) AS POStatus,
	( PO.ForeignTotal * -1 ) AS POAmount,
	NextTransaction.TranDate,
	BUILTIN.DF( NTL.NextDoc ) AS RelatedTransaction,
	BUILTIN.DF( NextTransaction.Status ) AS TransactionStatus,
	BUILTIN.DF( NextTransaction.ApprovalStatus ) AS ApprovalStatus,
	( NextTransaction.ForeignTotal * -1 ) AS TotalAmount,
	NextTransaction.ForeignAmountPaid AS AmountPaid,
	NextTransaction.ForeignAmountUnpaid AS BalanceDue,
	NextTransaction.Memo	
FROM
	Transaction AS PO
	INNER JOIN NextTransactionLink AS NTL ON
		( NTL.PreviousDoc = PO.ID )
	INNER JOIN Transaction AS NextTransaction ON
		( NextTransaction.ID = NTL.NextDoc )
WHERE
	PO.ID = 33108
ORDER BY
	NextTransaction.TranDate,
	NextTransaction.ID

Unlike the query that I shared in that previous post, in this query I'm using the NextTransactionLink table. I'm starting with the PO transaction, joining to the NextTransactionLink table, and from there I'm joining to the next transaction.

Here's what the results of the query look like.

Click the image to view a larger version.

The first 5 columns provide information about the PO (the PO Number, date ordered, supplier name, current status of the PO, and the total amount). The remaining columns apply to the related transaction.

The first row represents the Vendor Prepayment. The second row represents the Item Receipt (which was a partial shipment). And the third row represents the Vendor Bill for that partial shipment. Note that the Vendor Bill included a charge for inbound freight, and that the Vendor Prepayment was applied to it. However, the prepayment didn't fully cover the bill, so it has an open balance of $1925.

This was the first version of the query that I developed to support this project. There was a lot more to it, but this was a good start. I hope it helps you as well.

Also, while this query was used to present transactions related to a purchase order, it can be easily modified to work with other transaction types.