Hello Richard,
When we get support cases on the Item Stock Inquiry, we provide how this window functions. Please see how the window works below.
Item Stock Inquiry
The window pulls from 2 main tables.
The totals and balances are pulling from different tables. Above the Total Stock quantity and all the lower fields in the bottom part of the window are stored in IV00102. If the balances in IV00102 are wrong, the window will calculate the balance at the top incorrectly.
See the On hand value = 5. If you look in table IV00102 this matches the ONHAND field in the table.
The IV30300 is where the detail (inflows and outflows ie. 000000000000112, STDINV2237, 0000000000000113) is pulling from. The system uses the quantities in these tables in its calculation to get to the balance (4).
It starts with the balance in the IV00102 and works its way backwards (starting at the bottom of the window) through transactions in the IV30300. Once it gets to the last transaction in the table (based on date) the system puts that balance at the top of the page for the “balance”. See screenshot above, 4 is the balance.
Example below:
They have 5 on hand.
5 – 3 = 2. The balance is 2 at the top, but they think it should be 4 because the Inflow record was 4.
Balance at the top is just a calculation.
The issue in this case is there is a missing record in the IV30300 for a sale of 2. It uses the IV00102 to get the onhand balance, but then uses the IV30300 to “back out” the quantities from the transactions. Since the Sales transaction for a transaction quantity of 2 (STDINV2267) was deleted out of the table, the balance won’t match the first record coming in causing confusion.
Reasons why the balance can be off based on cases.
- Not maintaining IV history.
- Removed data via SQL from the IV30300.
- Posting interruption where data didn’t make it into the IV history tables.
- Changed U of M equivalent. 1 each is the base, 6 each= 1 case. Customer changes the equivalent and now 8 each = 1 case. The system allows you to change the U of M equivalent, but there is no warning that this will be the ramifications of this act (working as designed). A better solution for customers is to create a whole new UofM.
- Serial/lot quantity overrides. (See below). Brought in 3, sold 5 and overwrote quantity. (See screenshot below)
We also have the KB article below that has additional information pertaining to the Item Stock Inquiry window.
https://support.microsoft.com/en-us/help/2647425/the-balance-in-item-stock-inquiry-is-incorrect-in-microsoft-dynamics-g
When I capture a SQL Trace of the window loading, I only see one Stored Proc. This is ivFillStockInquiryScrollingWindowTempTable.
I hope this helps!
Thank you!
Microsoft Support Engineer | Brandon Jarratt
