Mystery Solved: Multiple Lines on NetSuite Item Fulfillments and Receipts

This article is relevant if you are working with NetSuite aggregate or summary searches and you are find that you getting difficult to explain values when you search on Item Fulfillments or Item Receipts.

Background

In recent work for a client who was not well guided by another NetSuite implementation firm relative to use of NetSuite item kits, we assisted them to produce item-kit based profitability reports.  How we solved that concern (it was not easy) is the subject of another article.  However, while doing work there, we encountered a number of concerns while producing saved searches on Item Receipts and Item Fulfillments.  As such, I thought it would be valuable to share my understanding for how NetSuite works with Item Fulfillments and Item Receipts as it pertains to inventory based transactions.

How NetSuite Inventory Balances are Calculated

Many times, when I work with NetSuite customers for the first time, especially with accountants that have many years working with other financial systems, I suggest that NetSuite is different because it is ¬†effectively a transaction system. ¬†Meaning, there is no summary ledger with detail subledgers like we see in older computer architecture which used to mimic paper based accounting systems. ¬†They key is that NetSuite summarizes transaction records to produce a balance as needed. ¬†The most common is balances on the balance sheet. With NetSuite’s architecture, you can’t get out of sync between summary and detail tables. Readers may be interested in articles I have written before on this: ¬†See Philosophies on Batch versus Real-Time Accounting Systems and Produce NetSuite Balance Sheet Amounts via Saved Search among others.

The Inventory balance is no different.  A stock ledger is effectively composed of three transaction types:

  1. Item Receipt: the quantity adds to the stock ledger
  2. Item Fulfillment: the quantity removes from the stock ledger
  3. Item Adjustment: the quantity is deliberately changed as needed.
These are not the only transactions that affect the stock ledger. ¬†An invoice that does not have a related Item Fulfillment record may affect the stock ledger. ¬†The point though is that the inventory balance is comprised of the sum of transaction records. ¬†If you are interested in reproducing NetSuite’s Inventory balance by location, create a saved search that summarizes on GL Inventory Account, Item, Quantity, and Location. ¬† ¬†Let’s though look more closely at what is going on.

Item Fulfillments and Item Receipts Saved Searches Generate Three Lines

NetSuite Administrators quickly learn how to produce Transaction based Saved Searches.  The conventional way to think about these transaction searches is as follows:
  1. Header: The transaction header is the “Mainline” and will be presented as an asterisk in the results set. This is where the transaction body fields live.
  2. Line: The transaction detail lines represent the non-Mainline items.  This is where the transaction column fields live.
Once you get used to thinking this way, it’s easy to assume all transactions work this way. ¬†More importantly, the key to understand NetSuite is to remember that transactions may have ¬†financial impact and thus they need to have related General Ledger (GL) lines. ¬†Most of the time, the pattern above holds true. ¬†For example, on an invoice you will see:
  1. Mainline: Debit Accounts Receivable (the A/R “control” account)
  2. Non-Mainline Lines: Credit Revenue
Yet, when you are working with Item Fulfillments and Item Receipts, more is going on.

Why NetSuite Returns Three Lines on Item Fulfillments and Item Receipts

When it comes to inventory operations, the general ledger routing is controlled by the item definition. ¬†Each item can point to different inventory and cost of goods sold general ledger accounts. ¬†Thus, the assumption that the transaction header points to the “control” account won’t work because each item may have a different general ledger pointer.

Each item on an item fulfillment and or item receipt needs to affect the inventory stock ledger. ¬†The inventory stock ledger is basically a general ledger “Other Asset” account where the “Inventory” inventory is on. ¬†NetSuite wants to summarize, as I shared above, by Item, by GL Account, and by Location. ¬† Since an item fulfillment needs to decrement inventory, the quantity needs to be expressed as negative. ¬†However, when you create an item fulfillment record, you work in positive quantity numbers. ¬†It’s funny to see negative and positive numbers when you perform a saved search.

Thus, the key to understanding the saved search results are as follows:
  1. Operational Line Zero: The first line has no general ledger impact.  Thus it is effectively the line that you are working with while you edit the transaction.  I call this the operation line.   When you commit the transaction, NetSuite produces two more general ledger lines.
  2. GL Line 1: One new line is to the stock ledger inventory account.
  3. GL Line 2: The subsequent line is the offset general ledger account.

Saved Search Challenges with Item Fulfillments and Item Receipts

Without this background understanding, you may find that your aggregate searches are not returning the right information. ¬†Here, it’s important to understand that you can key on the general ledger account as a way to isolate out the line(s) you care about. ¬†Another approach, suggested by NetSuite, is to do divisor math on the line sequence number to determine if you are line zero, 1, or 2. ¬†I find that approach not very intuitive but it is an option.

Key Major Saved Search Challenge when working with Custom Transaction Columns

The real major challenge presents itself if you want to key on your custom transaction columns via your saved searches. ¬†NetSuite will not stamp the general ledger lines with your custom column. ¬†Oh no! ¬†Thus, if you want to perform some type of inventory lookup that leverages your custom field on the transaction, you can’t get to it!

For example, I have created techniques where I want to group items together under my own control with a custom transaction column. ¬†Yet the conventional approach will not work (see image). ¬†I have discovered that you can produce a custom GL segment and this will come over. ¬†But for transactions already written in a closed accounting period, it’s going to take some work to retrofit.

Get Superior Help on your NetSuite Account

They key to getting the most of NetSuite is to demand what you want from professionals that understand the product while also having strong fundamental understanding of business and accounting concepts.  If the success of your ambition depends on NetSuite performing to your requirements, and you are not satisfied with the thinking and care around you, perhaps we should have a conversation?

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: | Category: Accounting, NetSuite, Reporting | 2 Comments

2 Comments

  1. John Cirocco
    Posted August 29, 2016 at 6:37 am | Permalink

    I also have had to work with Item Receipts when Landed Cost is used. You may want to do a “Part 2” to discuss this as it does take a bit of getting used to because the line sequence number goes out the window. Also the fact that the quantity field is not available on all lines makes this a challenge.

    Thanks for a good article.

  2. Posted August 29, 2016 at 8:21 am | Permalink

    I find that using the “Quantity Fulfilled/Received” column, instead of “Quantity”, helps greatly when constructing searches.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>