Recently we discovered that an inventory item showed a quantity on hand of 21 but a review of the transactions in the system showed that the quantity should have been 0. I was able to narrow down the problem to 2 invoices which came from the same order number and summed to a quantity of 21. The receipts for the quantity sold on the invoices were present in the IV10200 table, but the QTYSOLD was 0 for both and the RCPTSOLD was 0 as well. Reconcile and Check Links did nothing to fix this.
Using some advice I found elsewhere on these forums, I set the QTYSOLD to equal the QTYRECVD and set the RCPTSOLD to 1. Then I ran reconcile, which inserted records into the IV10201 table and recalculated the Quantity on Hand for the item. I did this in the test database.
This seems to have corrected the issue; however,before I run the update in the live data, I would like to determine what other tables may have failed to have been updated by the invoice posting process; and also I would like to figure out a way to query the database to find any other items where this mismatch may have occurred. Essentially I need a way to compare the "ins and outs" for an item with the resulting quantity on hand. It is worth noting that for these 2 invoices, no records existed in the IV10201 table, but they did show up in the Item Transaction Inquiry screen.
Any help or insight that anyone can provide would be very much appreciated.
John
*This post is locked for comments
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,134 Super User 2024 Season 2
Martin Dráb 229,928 Most Valuable Professional
nmaenpaa 101,156