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:
- Item Receipt: the quantity adds to the stock ledger
- Item Fulfillment: the quantity removes from the stock ledger
- Item Adjustment: the quantity is deliberately changed as needed.
Item Fulfillments and Item Receipts Saved Searches Generate Three Lines
- 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.
- Line: The transaction detail lines represent the non-Mainline items. This is where the transaction column fields live.
- Mainline: Debit Accounts Receivable (the A/R “control” account)
- Non-Mainline Lines: Credit Revenue
Why NetSuite Returns Three Lines on Item Fulfillments and Item Receipts
- 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.
- GL Line 1: One new line is to the stock ledger inventory account.
- GL Line 2: The subsequent line is the offset general ledger account.
Saved Search Challenges with Item Fulfillments and Item Receipts
Key Major Saved Search Challenge when working with Custom Transaction Columns
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.
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.
I find that using the “Quantity Fulfilled/Received” column, instead of “Quantity”, helps greatly when constructing searches.
I have a Saved Summary search looking for Transfer Orders. I am also showing the Fulfilling/Receiving transaction data. For Item Receipts, I am getting a GL impact value, but for the Item Fulfilment records they are all blank. When I drill down my summary search to just an Item fulfilment, it is only showing me the Operational Zero Line. Question: Is there a way to return either the GL line 1, or the GL line 2?
Hello Celeb,
You can use Oracle’s REMAINDER( m, n ) function to help you divide the Line Number element to focus on certain lines in the output.
Marty
Hello Marty,
Just wanted to know how to update line level field ‘Class’ for all lines on the Item Fulfillment. I have created a Saved Search and in the results have created a formula to determine which class is to be set for the lines,but don’t know how to update it now on the IF.It doesn’t seem possible through CSV or Mass Update. Please requesting you to guide me with the approach to be taken.
Ah, the Item Fulfillment record. NetSuite does not provide a tool to update the item fulfillment, only create on shipment. However, we solve this problem using this tool:
https://blog.prolecto.com/2020/04/04/fully-automate-complex-netsuite-data-imports/
Marty
Hey Marty,
Absolutely love your articles.
When I’m dealing with Item Fulfilments, when what I’m interested in is getting just “the normal lines without duplicates”, and when I’m not particularly interested in the account/GL side of things, what I’ve settled on as my simplest “pattern” is querying on the “Applied To Link Type” field.
My go-to criteria now is “Applied To Link Type IN (‘Order Picking/Packing’,’Receipt/Fulfillment’)”
This avoids the duplicates, and also the Kit child-items that show up with “Applied To Link Type=Kit Shipment”.
This works well for me when what I’m after is something that basically shows a subset of the data from the Sales Order, and without duplicates.
And if I *did* want the kit child items instead of parent, I would do either
“Applied To Link Type IN (‘Order Picking/Packing’,’Receipt/Fulfillment’,’Kit Shipment’) AND Item Type NOT IN (‘Kit’)”
or just the simpler
“Account Type in (‘Cost of Goods Sold’)”
I was just wondering what you thought of these approaches, and whether there’s any drawbacks to them.
Cheers.
Andrew,
This is quite helpful. Thank you for sharing and helping all of us! I need to use the Applied to Link Type in more of my queries. You opened my mind!
Marty
Awesome, glad to hear it!
I should mention for interest, it’s probably an obvious point; but if you were starting at a Sales Order and linking to fields from the Item Fulfilment, you can get the same outcomes using the mirror-image field “Applying Link Type”. And using the “Applying Transaction Fields…” to bring out the columns you want from Item Fulfilment.
You can even get a functional “left join” by allowing Applying Link Type to also be “–None–“. Although *other* applying transactions could complicate this, depending on your situation.
And so perhaps
“Quantity Picked = 0 OR Applying Link Type IN (…)”
is better. Or
“Quantity Shipped = 0 OR Applying Link Type IN (…)”.
Ah it seems like I could talk about Item Fulfilments all day 🙂
Thank you Andrew. More gratitude!
Love this blog. Have referred to it a few times in the past and it has helped big time! Thanks Marty.
For this specific issue, I want to thank Andrew for providing the Applying Link Type solution to get rid of the duplicates.
Netsuite sometimes seems very funny, and geniuses like you all help us navigate hurdles along the way.
Thank you!
Thank you Chintan, for the kind words. Let’s keep learning.
Marty
Criteria of Account Type AnyOf None will strip out the GL lines from your search result on Item Fulfillments and you can also display your custom column field results data if populated.
Hi David,
I like your solution for criteria! Thank you.
Marty
I want to go back to @andrew sladens comment. Im starting at the sales order type and am attempting to link order reservation qty’s and fields. Order reservations are relatively new and I know there is not alot of support for this. Any suggestions ? There is not a link type it appears for order reservation records.
Marty Zigman and Andrew Sladen
Thanks for the insights.
I did NOT find the field “Applied To Link Type IN”.
I did find the filed “Applied To Link Type”
Used Applied To Link Type
any of
Order Picking/Packing
and got very few result. (Doubt it did not include all the IF)
I used
Type is Item Fulfillment
COGS Line is False
Main Line is False
Tax Line is False
Shipping Line is False
Formula Numeric NVL({quantity},0) is greater than or equal to 1
and got relatively more reliable results with NOT duplicate lines. I also verified in the Excel.
Could you please convey if I made any mistake here as I am new to NS.
Marty, I’m one of the Mod’s at r/netsuite Reddit group. Thank you for all your excellent posts. I use your website daily and post links to your articles daily on Reddit.
I am working on SQL for Item Fulfillments on Transfer Orders. And this client had 0 in the Transfer Price on the Item record (and forgot to check “Use Item Cost as Transfer Cost”)(a very common mistake) so the Item Fulfillment debited the entire amount to the gain/loss account set on the Item! So this is a third GL-impacting line on the TOIF. And on PO Item Receipts using standard cost on the item the receipt will have a third GL line line too which posts to the Purchase Price Variance account set on the Item (debit inventory @ standard cost, credit accrued purchases @ PO price, db/cr PPV for PO price vs std cost variance)
Hi Nick,
Thank you for sharing that extra detail on those transactions. Those nuances make sense as you have a double whammy on that configuration. Do the lines in the search come up differently with the extra detail? As screenshot would be very valuable.
Marty
>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!
Solved with your inspiration Marty from your running subtotal analytical function trick.
Use FIRST_VALUE
Here’s the formula in my Reddit post for getting the header dept on all the lines:
https://www.reddit.com/r/Netsuite/comments/1k1pmgx/reference_a_value_from_a_different_row_in_a_saved/
This use case was to get the header dept on all rows of the lines, but it would work for your custom field that’s only in the operational line (i.e. first of the window) use case (i.e. first line of the partition window is the operational line and the internalID of the Item would be the same on all 3 lines so you could use that to define the 3 lines of the partition window), (versus mainline of the transaction in Reddit post) (and of course your approach to switch to an official custom segment works, too)
FIRST_VALUE/*comment*/({custcol.linecustomfieldname}) OVER (PARTITION BY {item.internalid} ORDER BY {line})
I would still order by line because that is still sequential ascending so it would insure that all 3 lines of the same item would be together for the partition by to work. This would fail if you put 2 duplicate items right next to each other so all 6 lines would have the same item.internalid (but that’s bad practice to put duplicate items anyways, and if you do append the dupe at the end don’t put them next to each other)
Note: I haven’t actually tested this but I was reading your article today and it triggered this memory for header dept so I want to share back knowledge w/you Marty.
Wow, I need to try this as FIRST_VALUE opens a world where we may have header values and we need those to wrap on the line values. Thank you for the suggestion.
And here’s how to get the ship date in the header of a SalesOrder on all the lines.
https://stackoverflow.com/questions/58702354/how-can-i-include-the-main-line-ship-date-in-line-item-results-for-a-sales-order