Packages: Pacejet Package Information

Returns information about item fulfillments and related Pacejet packages.

-- Contributor: Tim Dietrich (timdietrich@me.com)
-- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-item-fulfillments-pacejet-packages/
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' ) 
	)

Click here to return to the list of available queries.