Published on February 2, 2024.

A few days ago, I wrote about one of my NetSuite clients - a wholesale distributor of electronic components - that is struggling with Return Authorizations, and I shared a query that I wrote for them to help analyze returns. (See "Analyze Returned Items Using SuiteQL.")

This client, like make businesses these days, is also struggling with supply chain issues. Purchase orders - especially larger, internationally-sourced orders - are often taking significantly longer than normal to ship. So late last year, they started utilizing NetSuite's Inbound Shipment Management capabilities to better track inbound shipments.

To see a list of inbound shipments in NetSuite, navigate to: Transactions > Purchases > Create Inbound Shipment > List. Here's an example of an Inbound Shipment.

Click the image to view a larger version.

I had previously developed a custom Web portal for this client, which their warehouse teams use to perform a variety of tasks without needing direct access to NetSuite. My client asked if I could enhance the portal so that warehouse managers could get visibility into inbound shipments. Specifically, they want managers to be able to see the orders that are in transit, as well as those that have not yet shipped, including expected and actual ship dates, expected delivery dates, and more.

In NetSuite, information about inbound shipments is stored in a table named InboundShipment, and information about the items in a shipment in a table named InboundShipmentItem.

Here's the original query that I developed to handle the new portal function.

SELECT
	InboundShipment.ID AS InboundShipmentID,
	BUILTIN.DF( POTransaction.Entity ) AS Vendor,
	POTransaction.TranID AS PONumber,
	InboundShipment.ShipmentStatus,	
	InboundShipment.ExpectedShippingDate,
	InboundShipment.ActualShippingDate,
	InboundShipment.ExpectedDeliveryDate,
	BUILTIN.DF( InboundShipmentItem.ReceivingLocation ) AS ReceivingLocation,
	InboundShipment.ShipmentMemo,
	BUILTIN.DF( POLine.Item ) AS ItemID,
	InboundShipmentItem.ShipmentItemDescription,	
	InboundShipmentItem.QuantityExpected,
	BUILTIN.DF( InboundShipmentItem.Unit ) AS Units,
	InboundShipmentItem.ExpectedRate,
	InboundShipment.ExternalDocumentNumber,
	InboundShipment.BillOfLading
FROM 
	InboundShipment
	INNER JOIN InboundShipmentItem ON
		( InboundShipmentItem.inboundshipment = inboundshipment.ID )
	INNER JOIN Transaction AS POTransaction ON
		( POTransaction.ID = InboundShipmentItem.purchaseordertransaction )
	INNER JOIN TransactionLine AS POLine ON
		( POLine.uniquekey = InboundShipmentItem.shipmentitemtransaction )
WHERE
	( InboundShipment.ShipmentStatus <> 'received' )
ORDER BY
	InboundShipment.ExpectedDeliveryDate,
	InboundShipment.ID

This is a simple query. I'm using the InboundShipment table as the foundation for the query, and joining to the InboundShipmentItem table. From the InboundShipmentItem, it's easy to join to the related PO transaction and transaction line records.

NetSuite also supports applying landed costs to Inbound Shipments. I'm not using it in this example, but if you need to include landed cost information in your SuiteQL query, take a look at the InboundShipmentLandedCost table.