Scenario: Review Negative Inventory by Bin

NetSuite currently provides reports based on ‘negative stock by location’ and it doesn’t include bin even though bin is enabled in company settings as shown below.

Currently NetSuite report or view is as shown below, and it doesn’t include bin.

As explained above, the objective here is to provide negative inventory by bin.

To achieve this, we need to first evaluate NetSuite inventory-based transactions and find if any of the items linked to these transactions end up with negative stock.

Steps:

Create transaction saved search as shown below and name it with ID as ‘customsearch_tra_summary_inv_by_bin’

Criteria,

1. Date is between 12/31/2017 till today
2. Quantity is not empty
3. Formula numeric; case when {location.id}={item.inventorylocation.id} then 1 else 0  end
4. Formula numeric; case when {account.id} = {item.assetaccount.id} then 1 else ((case when {type} in (‘Bin Transfer’) then 1 else 0 end) ) end

Note: 12/31/2017 : we want to consider all transactions from beginning but this can be controlled through script.

Summary Criteria,

1. Type is “summary” and field is ”transaction bin quantity” and description is “less than 0”

Results:

1. Item with summary type GROUP
2. Transaction bin Quantity with summary type SUM
3. Location with summary type GROUP
4. Transaction binNumber with summary type GROUP
5. Formula numeric: round({amount} / NULLIF({quantity},0),5)       — FifoCost

Available filters:

1. Item
2. Location
3. Date
4. Transaction bin Number

Save the above saved search and run it.

The next step is to build another saved search that finds out all INVENTORY based transactions that were modified AS OF TODAY. Let the ID of this saved search be ‘customsearch_tra_find_last_modified’. It should output the item, transaction date.

Next step is to build custom record with fields like below,

Item, Transaction Date, Transaction Bin, Location, Qty, Last Processed, Comments

Let the Id of the custom record be customrecord_item_negative_as_of_date

Let us build a scheduled script now.

Steps:

1. Check customsearch_tra_find_last_modified and loop through the saved search results and build list of items that have last inventory transaction date.
2.  For each item from step 1, call customsearch_tra_summary_inv_by_bin by passing the transaction date. 
3. Check if step 2 produces any negative stock and if so, append to customrecord_item_negative_as_of_date with all necessary details
4. Repeat until all items from step 1 are processed within step 2 to step 3.

At the end of the above steps, we should have any items with negative stock appended to customrecord_item_negative_as_of_date.

Note: it is possible to add stock (to offset the negative stock of an item) directly within the script but most companies would like to see why negative stock appeared and there may be some missing transactions that could fix the negative stock.

The last step in the process it to create saved search view based on customrecord_item_negative_as_of_date. 

Let the criteria indicate all rows sorted by Item, lastProcessed date (desc), Transaction date, comments, qty. 

It is possible that same item for same transaction date could appear more than once in the above saved search and in such cases, you can create occurrence count.

Hope this blog is useful and outlines how to achieve item negative stock by bin. Get to know more about MIBAR and contact us with any questions.

Need more help? MIBAR offers expert NetSuite support. Contact us today to learn more.