Zero inventory quantity with inventory value in Navision

Solution

This section contains the info from Microsoft. See the Additional Comments for utilities and the steps to correct.

From Microsoft:

Needless to say, there are no silver bullet corrections for these situations. Typically, it will take some investigation to determine the cause for inventory value remaining on zero quantity items.

 One key factor that may cause zero quantity with remaining value is where miss-applied inventory postings have occurred due to a Location Code, Variant Code, Lot Number, etc. mismatch. Basically, if there is a difference between an Outbound Entry (such as a Sales Shipment, Negative Adjustment, Consumption, Transfer ILE) and an Inbound Entry (Positive Adjustment, Purchase, Output, Transfer In, ILE), the application of the outbound entries to the inbound entries can’t be completed in Table 339, which will result in the Adjust Cost Item Entries not being able to fully adjust cost. You would need to confirm the application of quantity as part of your review.  A quick review of the Open Flag in the Item Ledger Entries to confirm that Open = FALSE and also Remaining Quantity = 0 on all Item ledger Entries are factors to review for the Items that reflect zero quantity with value.

 In certain cases, if an application has been made but cost has not flowed through correctly, an edit of Table 32 – Item Ledger Entries can be made to set the Applied Entry to Adjust flag to YES (TRUE) on the Inbound Entries (Purchase, Positive Adjustment, Output, etc).  If in 4.00 Sp1 or later, also make sure that the Item Card is set to Cost is Adjusted = FALSE. Once these steps have been completed, you will run the Adjust Cost Item Entries to try to push the adjustment through for residual cost value.

 If this is an upgraded database from very early Versions of Navision, such as 2.60 or prior, the remaining value may be related to adjustments for rounding or other value that have not been fully pushed through because there weren’t any built-in mechanisms to manage rounding as there are in the 3.X or later versions.  The remaining value may be related to the upgraded data and old history, which makes it much more difficult to manage. Similar steps to the previous paragraph must be taken to try to push the adjustment through.

 The other thing to confirm is – if you run with a Date filter as of the latest date of the Value Entries that exist for the item, does it show 0.00. There are cases where there are timing differences and future transactions have been posted. The Inventory Valuation as of a backdated reporting shows a balance, but that is strictly because the Value Entry Date of some of the adjustment Value Entries did not occur until a later date.

 Additional info from 12/18

One of your findings is that the customer that have poorer inventory practices have a greater occurrence of this issue. Although, even some of your customers who follow best practices have a few of these situations.

 You also confirmed that some of the customers have Bill of Materials Items (Manufacturing and Assembly), which also adds to the complexity of the analysis.  However, from a practical standpoint, to force an adjustment, the selection of the Applied Entry to Adjust Flag to push an adjustment through applies to all inbound entries, whether the entry involves output, positive adjustments (Positive Qty), purchases, etc.   If you need to prevent an adjustment through the cost override process, you will need to deselect the Applied Entry to Adjust flag prior to the Adjust Cost Item Entries being run.

 We focused primarily on the relationship between Table 32 – Item Ledger Entries and Table 339 – Item Application Entries.  These are the key tables for managing quantities – particularly from a flow through and application of quantity perspective. 

 We reviewed a couple of Item Ledger Entries that were posted with different quantities and in unique ways – such as a Sales Entry with a Positive Quantity and a Positive Adjustment with a Negative Quantity. In all of these cases, the Table 339 records were created with a focus on the quantity and not the Item Ledger Entry Type.  The Positive Adjustment with negative quantity was treated as a negative entry for purposes of creating the Item Application Entries and applying the negative quantity to the correct remaining quantity Inbound ILE (Positive adjustment with + quantity, purchase etc) based on First In First Out (FIFO) rules. The positive quantity sale was actually linked to an Outbound entry (the original sale), which indicated that this was either a Sales Return posted with Exact Cost Reversing rules and the Applied From Item Entry number populated on the Sales Return/Credit Memo lines, or an Undo Shipment against a shipped Sales Order, where the Inbound quantity was applied back to the original outbound quantity.  This is important to get correct costing and valuation of the incoming return quantity.

 We also discussed the correction steps for overriding the FIFO cost to clear these values. The key factor being that once you post the combination of a positive adjustment and negative adjustment and force the cost, you must deselect the Applied Entry To Adjust Flag on the Item Ledger Entry for the Positive Adjustment to prevent the cost adjustment routine (Adjust Cost Item Entries) from adjusting the negative adjustment value to match, which we didn’t want to happened. We want to force an override of cost to flush out the remaining value against the zero quantity.

Additional Comments

Report 67302 Set Cost is Adj. to False will set the Cost is Adjusted field on the item card.

Navision seems to treat an entry as incoming if the quantity is positive, regardless of the Entry Type. And it seems to treat an entry as outgoing if the quantity is negative. So an entry type of Positive Adjustment with a negative quantity is treated as an outgoing entry.

Additional Workaround from Microsoft: On FIFO items, do a positive entry for quantity 1 and zero cost. Do a negative entry for quantity 1 and cost to be corrected. (Or other way around on a negative amount). Before running the Adj Cost-Item Entry periodic activity, set the Applied Entry to Adjust flag in table 32 to False.

On Standard items, you need to play with the revaluation journal using the manual entry method (which adjusts outgoing entries as well as incoming entries). Use table 339 to find the correct entries to adjust

BJ suggests the following order:

1. Review the item to make sure all ledger entries are closed and remainder is zero. We should not be adjusting if an entry is still open. If the open entries are very old, we need to do more investigation about why they are not being cleared. We may be able to clear them by creating entries and selecting the open entries in the Applies-To field.
2. If all ledger entries are closed, try to recalculate all positive entries by setting the Cost is Adjusted field on the item card to false and setting the Applied Entry to Adjust in table 32 to Yes.
3. Finally, if the value is still not correct, use the workaround that posts a positive and negative entry and removes the Applied Entry to Adjust flag.