Detective
It was a chillingly cold morning in November when a manufacturing firm contacted our Data Detective indicating that their Inventory reports seemed irregular. Some of the assemblies they have on hand did not appear in their numbers, and related parts were also out-of-balance. Upon arrival our sleuth ran the QuickBooks Verify utility which failed, just as he expected; when the QBWin.log was examined, numerous LVL_ERRORs like the one below were observed:
LVL_ERROR--Error: Verify Item History: Build txn target bad pending state. Item = 1242 - Target recnum = 747288 - Target NoPost state - 0.
ItemHistUpdateEngine.cpp (3117) : MESSAGE: Mon Nov 12 09:02:20 LVL_ERROR--Error: Verify item history Target quantity on hand mismatch error:
Build Assembly transaction 4441 (internal target 675900 - 0)
date: 10/26/2013
item: 20045
qty: -3
memo:
expected value: 3,243.5, found value: 3,336.5
The Data Detective knows that these errors have become more common and more difficult to fix. There are a variety of similar ‘mismatch errors’ that can occur (quantity on hand, average cost, etc.) but the causative mechanism is similar. The problem typically results when 'assembly builds' are associated with negative items on hand, or items whose average cost change. While investigating the specifics the company’s bookkeeper asks how this error can occur.
Our sleuth explains that when an item has been received without a bill and the item receipt is entered with a date prior to the build, the receipt increases the quantity on hand, thus permitting the build to be performed. However, subsequent to the build, the Vendor's bill for the item comes into the bookkeepers office and when it is posted into QuickBooks the receiving date is 'changed' the to the billing date; this means that the inventory quantity is now 'negative' prior to the build, and not increased until after the build. When QuickBooks looks at the transaction associated record it computes that the inventory quantity should have been a positive number, but the item record shows it is a negative number, therefore there is a 'mismatch for the item' and the error is generated during verify. In addition because QuickBooks computes that the build cannot possibly be correct, because the quantity on hand was insufficient at the time of the build to ‘make the build’, the pending state of the build must be wrong, and reports concerning the quantity of builds on hand will be incorrect.
This same situation can occur when assembly builds are based upon quantities at specific sites if a QuickBooks user has subscribed to the Advanced Inventory Multi-site feature. If site related inventory goes negative either for the same type of ‘receiving related’ reason specified above, or because inventory is transferred ‘subsequent to the build’, but using a retroactive date, a site-specific version of this same error can occur. In cases such as these, corrective efforts must be based upon site-specific quantity fixes using the same principles as outlined below.
The Data Detective knows that running a rebuild may not resolve the problem, or if it appears to do so, it may be only a temporary fix with the problem reoccurring, perhaps as soon as either the next verification, or the next time the affected item is used. In either case, the following steps should be applied:
1. Export the Item list using the iiF utility and look for the "Item" (internal) reference number that corresponds to the number reported in the Error message.
2. Run an "Inventory Valuation Detail" report for the particular item identified in step 1. Look for the point at which the item’s "quantity on hand" becomes a negative number.
3. If the item went negative because it had been received increasing quantity on hand to a positive value before the build, but then the receiving voucher date was changed later to the billing date, resulting in a negative inventory at the time of the build, change the bill date back to the original receiving date.
4. If you can’t determine the cause of the irregularity, delete the "build assembly" involved in the negative on hand.
5. As an alternative to steps 3 or 4, consider an Inventory Adjustment to increase the quantity of the item which is negative for the build, date the adjustment prior to the date of the build.
6. Re-sort the Item list, and
7. Run the Rebuild utility.
8. Run the Verify utility to insure that the above steps cleared the error(s). After this, the QBWin.log should no longer report the Error message, the Verify and Back-up utilities should complete normally, and Inventory-related Reports should appears normal and in balance.