Inventory Inactivity & Obsolescence report

Requirement:

We need a reliable way to measure how long items have been sitting without movement at each location. The default aging report in NetSuite has helped us get part of this visibility, but it does not pull last movement dates or values accurately enough. We’re requesting an Inventory Inactivity & Obsolescence report that shows, by item and location, the last inbound date (receipt, build, transfer-in, adjustment), the last outbound date (invoice, cash sale, or fulfillment if included), the most recent movement date, the number of days since that movement, and an aging bucket (0–30, 31–60, 61–90, 90+). The report should also display on-hand and available quantities, inventory value, and if possible, suggested reserve percentages by item. It needs to be filterable by location, subsidiary, product line, and item type, with the ability to include or exclude zero-on-hand items and specific movement events such as RMAs and fulfillments. We are open to alternative approaches or reporting formats if there’s a better way to achieve an accurate obsolete inventory or aging view.

Export:

var transactionSearchObj = search.create({
   type: "transaction",
   settings:[{"name":"consolidationtype","value":"ACCTTYPE"}],
   filters:
   [
      ["type","anyof","ItemShip","ItemRcpt","CashSale","Build","CustInvc","InvAdjst","InvTrnfr","RtnAuth"], 
      "AND", 
      ["item","noneof","@NONE@"], 
      "AND", 
      ["item.locationquantityonhand","notequalto","0"], 
      "AND", 
      ["item.type","noneof","Description","Discount","Expense","NonInvtPart","OthCharge","Payment","Service","Subtotal"], 
      "AND", 
      ["trandate","within","1/1/2025","31/1/2025"], 
      "AND", 
      ["sum(formulanumeric: CASE            WHEN  {type} = 'Item Fulfillment' AND {status} = 'Shipped' THEN {quantity}       ELSE 0 END)","notlessthanorequalto","0"]
   ],
   columns:
   [
      search.createColumn({
         name: "internalid",
         join: "item",
         summary: "GROUP",
         label: "Item Internal ID"
      }),
      search.createColumn({
         name: "displayname",
         join: "item",
         summary: "GROUP",
         label: "Item"
      }),
      search.createColumn({
         name: "subsidiary",
         summary: "GROUP",
         label: "Subsidiary"
      }),
      search.createColumn({
         name: "inventorylocation",
         join: "item",
         summary: "GROUP",
         label: "Inventory Location"
      }),
      search.createColumn({
         name: "formulanumeric",
         summary: "SUM",
         formula: "CASE      WHEN ROUND({today} - {trandate}) <= 90 AND {type} = 'Item Fulfillment' AND {status} = 'Shipped' THEN {quantity}     ELSE 0 END",
         label: "Quantity Sold (0–90 Days)"
      }),
      search.createColumn({
         name: "formulanumeric",
         summary: "SUM",
         formula: "CASE     WHEN ROUND({today} - {trandate}) > 90          AND ROUND({today} - {trandate}) <= 180          AND {type} = 'Item Fulfillment'          AND {status} = 'Shipped'     THEN {quantity}     ELSE 0 END",
         label: "Quantity Sold (90–180 Days)"
      }),
      search.createColumn({
         name: "formulanumeric",
         summary: "SUM",
         formula: "CASE      WHEN ROUND({today} - {trandate}) > 180 AND {type} = 'Item Fulfillment' AND {status} = 'Shipped' THEN {quantity}     ELSE 0 END",
         label: "Quantity Sold (180+ Days)"
      }),
      search.createColumn({
         name: "formuladate",
         summary: "MAX",
         formula: "CASE WHEN {type} IN ('Item Receipt','Assembly Build','Inventory Transfer','Inventory Adjustment') THEN {trandate} ELSE NULL END",
         label: "Last Inventory Transaction Date"
      }),
      search.createColumn({
         name: "formuladate",
         summary: "MAX",
         formula: "CASE WHEN {type} IN ('Item Fulfillment')   AND {status} = 'Shipped' THEN {trandate} ELSE NULL END",
         label: "Last Sale Transaction Date"
      }),
      search.createColumn({
         name: "formuladate",
         summary: "MAX",
         formula: "{trandate}",
         label: "Last Any Transaction Date"
      }),
      search.createColumn({
         name: "formulanumeric",
         summary: "MAX",
         formula: "ROUND({today}-{trandate})",
         label: "# of Days since last transaction"
      }),
      search.createColumn({
         name: "formulatext",
         summary: "GROUP",
         formula: "CASE    WHEN ROUND({today}-{trandate}) <= 90 THEN '0–90 Days'   WHEN ROUND({today}-{trandate}) <= 180 THEN '90–180 Days'   ELSE '180+ Days' END",
         label: "Aging Group"
      }),
      search.createColumn({
         name: "locationquantityonhand",
         join: "item",
         summary: "GROUP",
         label: "Location On Hand"
      }),
      search.createColumn({
         name: "locationquantityavailable",
         join: "item",
         summary: "GROUP",
         label: "Location Available"
      }),
      search.createColumn({
         name: "locationtotalvalue",
         join: "item",
         summary: "GROUP",
         label: "Location Total Value"
      })
   ]
});

NOTES :

Regarding the quantity sold, we have removed the line where the quantity sold is 0 for all aging groups. The last sale transaction now only considers the Item Fulfillment. Usage from standalone invoices and cash sales has not been included.

The saved search includes a transaction date filter set to January 2025, which affects all columns, including “Last Sale Date” and “Last Any Transaction Date.” These dates will reflect only transactions within the filtered period, even if later transactions exist.

The transaction type is currently included in both the criteria and available filters. Additionally, result formulas are based on the transaction type. If a non-applicable transaction type is selected in the filters, it could significantly impact both the criteria and results, leading to inaccurate search results.