Search code examples
transactionsnetsuiteinventorysaved-searches

Is there a way to get a NetSuite Running Inventory Total by Lot via Saved Search or other?

The use case is a running total search of inventory quantity for particular item / lot / location over time to be able to view inventory 'as of' the time of that transaction and each transactions impact on on-hand to reconcile counts and find mistakes and discrepancies. I'm still a bit perplexed that this isn't a native function of an inventory system like NetSuite...if anyone has a simpler way I'm sure many would be eager to hear.

That the system doesn't separate 'change in on hand' as a concept for reporting vs. a generic 'Quantity' without customization is a bit wild, and I'm still somewhat convinced I'm missing something despite how many folks have told me 'it doesn't work that way'. e.g. like a treating a bill or invoice Qty that did not affect your stock count the same as the receipt or item fulfillment that did, from a reporting / data standpoint. Quantity Ordered, Committed, B/O, Invoiced, Billed, Planned, etc. are all distinct concepts from 'Net Change in On Hand', which I can't find an independent treatment of.

Inventory Valuation Detail Report w. Running Balance = True is the closest thing I've found natively, but will not split out quantity by individual lots so that makes it much less useful for companies heavily using lot-tracked inventory like in sensitive manufacturing.

Solution

  • For those interested, here's the search criteria cobbled together to get the running totals:

    Item Lot Running Inventory Total (Transaction Search)

    Here's my sketch of a list of relevant transaction types, if anyone has a comprehensive resource to point to, I made a post specifically on this - thread here.

    • Assembly Build
    • Assembly Unbuild
    • Cash Sale
    • Inventory Adjustment
    • Inventory Count
    • Inventory Distribution
    • Inventory Transfer
    • Inventory Worksheet
    • Item Fulfillment
    • Item Receipt
    • Production Order Completion
    • Production Order Issue
    • Return Authorization
    • Transfer
    • Transfer Order
    • Vendor Return Authorization

    Criteria:

    Filter Set Inventory Detail : Number = not None
    Posting = True
    Type Any of (the bulleted list above)
    Formula (Numeric) =1 case when {inventorydetail.inventorynumber} = {itemnumber.inventorynumber} then 1 else 0 end Optional: To limit initial run results (performance) Location desired default for filter
    Item choose one item for filter
    Item Number : Number choose one existing lot of item selected

    Available Filters: Item, Item Number : Number (Lot), Location

    Results: Critical to sort by Date (ascending) Then Internal ID (ascending)

    This will help show you transactions that happened out of order and may cause negative stocks.

    FIELD FUNCTION FORMULA CUSTOM LABEL SUMMARY LABEL
    Date
    Item : Display Name
    Type
    Location
    Document Number
    Created From
    Formula (Numeric) case when {type}='Item Fulfillment' then {quantity}*-1 else {quantity} end Quantity
    Inventory Detail : Number
    Inventory Detail : Bin Number
    Formula (Numeric) case when {quantity} <0 then {inventorydetail.quantity}-1 when {type} = 'Item Fulfillment' then {inventorydetail.quantity}-1 else {inventorydetail.quantity} end Lot Qty
    Formula (Numeric) sum/* comment /(case when {quantity}<0 then {inventorydetail.quantity}-1 when {type} = 'Item Fulfillment' then {inventorydetail.quantity}*-1 else {inventorydetail.quantity} END) OVER(PARTITION BY {formulatext} ORDER BY {lineuniquekey} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Running Balance