Serial Numbers Conundrum

A blog reader has asked me for help about an allegedly strange behavior of items with serial number tracking. They had a customer who had serial number tracking switched on for an item with FIFO costing method. Whenever they posted a sales transaction, they chose the serial number manually. Then they noticed a puzzling behavior.

No matter the specification of the serial number on the sales lines, Microsoft Dynamics NAV seemed to be closing the item entries according to FIFO method. This effectively allowed a serial number to be sold twice (or more). They called for help.

Here is an example to repeat the problem:

  1. Create an item with a serial number tracking setup like this (their setup):

  2. Assign the FIFO costing method for this item
  3. Fill an item-journal like this:

  4. For each of the lines assign a serial number manually (make them SN-01, SN-02 and SN-03)

  5. Post the journal
  6. Create a new journal line, set it up like this:

  7. Track it with serial number SN-03, then post it
  8. Create a new journal line, set it up exactly like the above
  9. Go to Item Tracking Lines, select the Serial No. field, then press F6 (or the Assist button )
  10. See the results:

  11. As you can see, SN-03 which has already been sold is still available. While SN-01 is gone from the list.
  12. Take a look at Item Ledger Entries for this item:

Obviously, SN-03 hasn’t been closed with the sale of the SN-03, and SN-01 was closed instead. Why did this happen?

Explanation: Mere specification of SN Purchase Tracking and SN Sales Tracking did just one thing: make it obligatory to specify the serial number on inbound and outbound sales and purchase transactions. It didn’t do anything with the application method involved. Application method is controlled by the SN Specific Tracking field – and in the setup above, it wasn’t switched on.

Now to the theory behind this.

Whenever decreasing inventory, the system needs to know inventory increase is being decreased with it. Say you bought 30 PCS of ThingoFix (of course, you value ThingoFix by FIFO, how else?):

Posting Date Quantity Amount
Jan 01, 2008 10 PCS 50 US$
Jan 02, 2008 10 PCS 60 US$
Jan 03, 2008 10 PCS 70 US$

Then you sell 6 PCS of ThingoFix on Jan 05, Jan 06 and Jan 07 each. The system needs to know the COGS for these sales. When valued by FIFO, COGS is determined by taking the cost from the earliest open increases, therefore it goes like this:

Posting Date Quantity Cost Amount
Jan 05, 2008 6 PCS -30 US$
Jan 06, 2008 6 PCS -32 US$
Jan 07, 2008 6 PCS -36 US$

If you take a look at the application entries (stored in table 339 Item Application Entry), it will be immediately obvious why the entries were valued the way they were:

Entry No. Item Ledger Entry No. Inbound Item Entry No. Outbound Item Entry No. Quantity Posting Date
296 324 324 0 10 1.1.2008
297 325 325 0 10 2.1.2008
298 326 326 0 10 3.1.2008
299 327 324 327 -6 5.1.2008
300 328 324 328 -4 6.1.2008
301 328 325 328 -2 6.1.2008
302 329 325 329 -6 7.1.2008

The system applied all 6 PCS of the first outbound entry ( 327 ) to the first inbound entry ( 324 ). Then it applied 4 PCS of the second outbound entry ( 328 ) to the remaining 4 PCS of the first inbound entry ( 324 ), and the next 2 PCS to the second inbound entry ( 325 ). Then it applied the 6 PCS of the third outbound entry ( 329 ) to the second inbound entry ( 325 ).

This is FIFO costing method. However, if you specify the SN Specific Tracking in the Item Tracking Code card, then regardless of the FIFO method, the system will apply cost according to the serial number assignment. Let’s say you have another item, ThingoFix 3000. Value it by Specific costing method (how else ;-)), and have a serial number tracking with the following setup assigned to it:

Also, have a number series producing numbers in SN-001..SN-999 range assigned as the Serial Nos. for this item.

Then post the following purchases:

Posting Date Quantity Amount
Jan 01, 2008 10 PCS 50 US$
Jan 02, 2008 10 PCS 60 US$
Jan 03, 2008 10 PCS 70 US$

Assign required serial numbers to each of the lines, using the Assign Serial No. functionality. This will assign serial numbers SN-001..SN-010 to the first line, SN-011..SN-020 to the second line and SN-021..SN-030 to the third line.

Now, go and sell this:

Posting Date Quantity
Jan 05, 2008 6 PCS
Jan 06, 2008 6 PCS
Jan 07, 2008 6 PCS

For each line, choose the item tracking lines. For the first line choose serial numbers SN-023..SN-028, for the second one, choose SN-017..SN-022 and for the third one make it SN-001..SN-006. Post the journal. Adjust the cost.

I am not copy-pasting the Item Ledger Entry or Application Entry – they are huge for these transactions, and I’m not going to do all the legwork of this exercise – do something yourself ;-). However, notice the fixed application in Item Application Entry table, which did not happen based on FIFO method, but based on serial numbers.

Ok, this solves for the first part of the problem. But why did it allow for a serial number to be sold twice? Well, simply because the sales functionality of Microsoft Dynamics NAV allows for something to be sold before it enters the inventory, because it doesn’t have to apply the cost immediately; it can apply a later inbound transaction to such an unapplied outbound transaction. However, with SN Specific Tracking turned on, the system won’t allow posting of a serial number which is not in the inventory, because with specific tracking the system must apply the outbound transaction to the inbound transaction immediately.

Therefore – don’t just rely on SN No. Info Must Exist or any of the SN Purchase/Sale/Positive Adjmt./Negative Adjmt./Manufacturing Tracking. It will merely make sure that nothing enters or exits your inventory without a serial number. It won’t do anything to make any applications based on serial numbers though. If you are happy with this – cool. If not, tick that SN Specific Tracking checkbox.

Advertisements

%d bloggers like this: