SuiteQL: Inventory Detail

Inventory Detail in SuiteQL

In the previous post we discussed the tables Transaction and TransactionLine. Please make sure to read that post before reading this one.

In this post we will discuss the Inventoryassignment table and how we can use it to get the inventory detail.

The inventory assignment table

Suppose we wanted the lot and/or bin information for a transactionline. Obviously we can’t store this in the TransactionLine table as we can have multiple inventory lines for the same transaction line. This is where the Inventoryassignment table comes in.

Here is the join (building on the Transaction table):

FROM transaction
INNER JOIN transactionline on transactionline.transaction = transaction.id
INNER JOIN inventoryassignment on inventoryassignment.transactionline = transactionline.id
and inventoryassignment.transaction = transactionline.transaction

As we can see the primary key of the table is a composite key, containing inventoryassignment.transactionline a reference to the transactionline and inventoryassignment.transaction a refrence to the transaction.

Let us look at some the important fields available to us in the \T Inventoryassignment table:

Column Name	Description	Type
quantity	The quantity of the inventoryassignment line (in base units)	Decimal
inventorynumber	The Lot Number (note it is a reference to the lot number not a string)	Integer (Internal ID)
binnumber	The bin record or null if not using bins	Integer (Internal ID)
inventorystatus	The internal id of the inventory status	Integer (Internal ID)

So suppose we wanted to get the inventory detail of a sales order that has the following lines:

Item	Quantity
Item 1	10
Item 2	20

and has the following inventory detail for item 1:

Lot	Bin	Quantity
Lot 1	Bin 1	5
Lot 2	Bin 2	5

and the following inventory detail for item 2:

Lot	Bin	Quantity
Lot 3	Bin 1	15
Lot 4	Bin 2	5

We can write a query as follows:

SELECT
  BUILTIN.DF(TransactionLine.item) as Item,
  BUILTIN.DF(Inventoryassignment.inventorynumber) as lot,
  BUILTIN.DF(Inventoryassignment.binnumber) as Bin,
  Inventoryassignment.quantity
FROM Transaction
INNER JOIN TransactionLine on TransactionLine.transaction = Transaction.id
INNER JOIN Inventoryassignment on Inventoryassignment.transactionline = TransactionLine.id
AND Inventoryassignment.transaction = TransactionLine.transaction
  WHERE Transaction.type = 'SalesOrd'
  AND Transaction.id = 1234

This will return the following result:

Item	Lot	Bin	Quantity
Item 1	Lot 1	Bin 1	5
Item 1	Lot 2	Bin 2	5
Item 2	Lot 3	Bin 1	15
Item 2	Lot 4	Bin 2	5

Getting inventory as of date

NetSuite - unlike other ERP systems - doesn’t have an single inventory journal table. The three tables above -Inventoryassignment, TransactionLine and Transaction - are the three tables that make up the inventory journal.

For each transaction we can generate a row as follows:

SELECT
Transaction.trandate,
TransactionLine.item,
TransactionLine.location,
Inventoryassignment.inventorynumber,
Inventoryassignment.binnumber,
Inventoryassignment.quantity
FROM transaction
INNER JOIN TransactionLine ON transactionline.transaction = transaction.id
INNER JOIN Inventoryassignment ON inventoryassignment.transactionline = transactionline.id
and Inventoryassignment.transaction = transactionline.transaction

Using this information NetSuite can deduce the inventory snapshot of a given item as of a given date.

We need to include a check for the field TransactionLine.isinventoryaffecting, this will filter out transactions like work orders that don’t affect inventory such as Work Orders. We also will include a check for the field Transaction.posting to check for transactions that are not in a state to affect inventory such as Item Fullfilments in picked status.

Suppose the date that we want to look at is 01/01/2023 and the internal id of the item is 1234:

The following query uses CTEs

WITH Inventoryjournal as (
    SELECT
    Transaction.trandate,
    BUILTIN.DF(TransactionLine.item) AS item,
    BUILTIN.DF(TransactionLine.location) AS location,
    BUILTIN.(Inventoryassignment.inventorynumber) AS lotnumber,
    BUILTIN.DF(Inventoryassignment.binnumber) AS Bin,
    Inventoryassignment.quantity
    FROM Transaction
    INNER JOIN TransactionLine ON TransactionLine.transaction = transaction.id
    INNER JOIN Inventoryassignment ON Inventoryassignment.transactionline = transactionline.id
    and Inventoryassignment.transaction = transactionline.transaction
    WHERE Transaction.trandate <= TO_DATE('01/01/2023', 'MM/DD/YYYY') AND item = 1234
    AND TransactionLine.isinventoryaffecting = 'T'
    AND Transaction.posting = 'T'
)
SELECT
Inventoryjournal.item,
Inventoryjournal.location,
Inventoryjournal.inventorynumber,
Inventoryjournal.binnumber,
SUM(Quantity)
FROM Inventoryjournal
GROUP BY
Inventoryjournal.item,
Inventoryjournal.location,
Inventoryjournal.lotnumber,
Inventoryjournal.bin

This will give you a breakdown of what was in inventory as of 01/01/2023 for item 1234.

This query can be very slow, it is reccomended to test it before running it on all your items

Sample Query | Sales By Location per month per Item (with inventory detail)

This query is the same as the previous post, but with the addition of the inventory detail.

First let’s get the transaction lines we’ll need with the inventory detail:

SELECT
transaction.tranid,
transaction.trandate,
TO_CHAR(transaction.trandate, 'Month YYYY') AS month,
BUILTIN.DF(transactionline.item) as item,
inventoryassignment.quantity
BUILTIN.DF(inventoryassignment.inventorynumber) as lotnumber,
where transaction.type = 'SalesOrd'

Now we pull it into a CTE and do the grouping:

  WITH salestransactions AS (
  SELECT
  transaction.tranid,
  transaction.trandate,
  TO_CHAR(transaction.trandate, 'Month YYYY') AS month,
  BUILTIN.DF(transactionline.item) as item,
  inventoryassignment.quantity
  BUILTIN.DF(inventoryassignment.inventorynumber) as inventorynumber,
  Where transaction.type = 'SalesOrd'
)
SELECT
salestransactions.month,
salestransactions.item,
salestransactions.inventorynumber,
SUM(salestransactions.quantity)
GROUP BY
salestransactions.month,
salestransactions.item,
salestransactions.inventorynumber

Stay tuned for the next post where we will discuss joining the table TransactionAccountingLIne to get the GL impact of transactions.