web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Incorrect item quantity
Microsoft Dynamics GP (Archived)

Incorrect item quantity

(0) ShareShare
ReportReport
Posted on by Deirdre 105
Hi,
 
I have one item that has the incorrect quantity on hand showing in GP.  The QTY on Hand shows as 0 however when I look at the item transactions I can see that I should have the qty on hand of 1.  (There is less than 10 transactions with this item).   Every report/inquiry I have tried shows the qty on hand as zero (Stock status, Item Stock Inquiry window etc). then I look at the details in my item transaction Smart list, Item Transaction inquiry, Item Stock inquiry they all display the correct transactions but show the qty on hand as zero when I add it up to be one.
 
This item did have a negative qty for a bit but we no longer allow negatives.  This item is not Lot or serial tracked.  This item is not allocated or anything we did a stock count at Oct 31 and the qty on hand should be 0. My fee is overstating inventory as this item has a very high cost in relation to our total inventory value.
 
I have looked at all sorts of tables in SQL, I have ran IV reconcile and while as various check links options including inventory and Purchase Receipts.  We are on GP 10.
 
Have run out of ideas and any help/suggestions would be greatly appreciated.

*This post is locked for comments

I have the same question (0)
  • L Vail Profile Picture
    L Vail 65,271 on at

    Run the Item reconcile utility. If you just want to force it to the correct number, modify the values in the IV00102 table - also bin qtys if you are using multi-bin.

    You have two ways to run item reconcile. Use the utilitity in inventory or use the free Item reconcile tool in the PSTL library.

    Kind regards,

    Leslie

  • Deirdre Profile Picture
    Deirdre 105 on at

    I forgot the mention that in my original post but I have run reconcile and my issue remains.

    Afer reconcile the quanitity that I think I should have according to the item transactions is not the quantity on hand. There is no quanity allocated, returned or damaged either.  

  • L Vail Profile Picture
    L Vail 65,271 on at

    At this point I would change the values in the table.  If you want to continue investigating I would look in the Purchase receipts table.  

    Did you run both reconciles on your inventory? I mean both the one in utilities and the one from the Professional Services Tools Library?

    For what it's worth, the following explains what is involved in the reconcile:

    The Inventory files involved in the reconcile are:

    Purchase Receipts (IV10200)

    Purchase Receipts Detail (IV10201)

    Item Master (IV00101)

    Item Serial Number Master (IV00200)

    Item Quantity Master (IV00102)

    Item Lot Number Master (IV00300)

    Item Vendor Master (IV00103)

    Inventory Transaction Amounts Work (IV10001)

    Inventory Serial and Lot Numbers Work (IV10002)

    Item Site Bin Master (IV00112)

    Inventory Transaction Bin Quantities Work (IV10003)

    Inventory Transaction Bin Quantities History (IV30302)

    Additional files outside of Inventory are involved in the reconcile if modules Purchase Order Processing, Sales Order Processing, or Invoicing are loaded.

    This involves the following files:

    Purchase Order Line (POP10110)

    Purchasing Receipt Line Quantities (POP10500)

    Sales Transaction Amounts Work (SOP10200)

    Sales Serial/Lot Work and History (SOP10201)

    Invoicing Transaction Amounts Work (IVC10101)

    Invoicing Serial and Lot Number Work (IVC10102)

    Here's the process:

    1. The quantities are reconciled first between the Purchase Receipts and the Item Serial Number Master file.

    2. Then the Purchase Receipts and Item Lot Number Master files are reconciled.

    3. At this point, the quantities are reconciled between the Purchase Receipts file and Item Quantity Master file

    4. Reallocating of Inventory Transaction Amounts Work, Inventory Serial and Lot Numbers Work, Sales Transaction Amounts Work, Sales Serial/Lot Work and History, Invoicing Transaction Amounts Work and Invoicing Serial and Lot Number Work tables is done next.

    5. The On Order quantities are determined for the Item Quantity Master and Item Vendor Master tables by comparing them to the Purchase Order Line and Purchasing Receipt Line Quantities tables.

    6. The last step in the reconcile is to reconcile the quantities on each of the items’ individual quantity location records to the total quantity record for that same item.

    Hoping this may help someone.

    Kind regards,

    Leslie

  • Suggested answer
    Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    Leslie is correct but let me suggest something other than modifying the tables directly.

    1.  Reconcile Purchasing.

    2.  Reconcile Sales

    3.  Reconcile Inventory

    IN THAT ORDER.

    Now, I was a bit confused in that you said inventory QOH showed 0 and the physical inventory showed 0 yet you calculate a quantity of 1.  What should the quantitty be?  It sounds correct.

  • Deirdre Profile Picture
    Deirdre 105 on at

    Thanks for your input, I am hesitant to update the tables directly as well, I did try it in a test company but if run reconcile it changes my value back.  I do really appreciate knowing the order that the reconcile functions works with the tables!

    I have just run the IV Reconcile not the ne from PSTL as it is not installed on my instance.

    Richard~ you are correct my QOH shows 0 which is correct I do have 0 in my warehouse. However I am concerned since if I run my Item Transaction Smartlist (or look at the Item Transaction Inquiry window I can see from the detail that I should show 1 on hand.

    My main concern with this is the inconsistency that is showing in GP. This is a high dollar item and therefore has a high potential of being selected for review by auditors.  

    I had not tried reconciling Purchasing and Sales until you suggested this, I have run these reconciles in the order you mentioned and I still have the same scenario.

  • Community Member Profile Picture
    Community Member on at

    Wow this is an old thread, but I had the same problem.  After working with the tables I found that GP does not report item transfers correctly in this report.  It will only show the positive side of the transfer in the detail making it appear that there is more quantity than there actually is.  I discovered this when writing a query on the IV30300 table and my query matched the Item Transaction Inquiry report, not anything else in the system.  

    Below is the query I used that provided accurate transaction inquiry plus you can choose a date and run an inventory as-of report

    Select a.itemnmbr
    ,a.trxqty as 'Quantity'
    ,a.trxqty*a.unitcost as 'Inventory Value'
    ,a.Site
    from(
    SELECT dtl.[DOCDATE]
    ,dtl.[ITEMNMBR]
    ,dtl.[TRXQTY]
    ,dtl.[UNITCOST]
    ,dtl.trxqty * dtl.unitcost as 'value'
    ,dtl.[TRXLOCTN] as 'SITE'
    FROM [FOF].[dbo].[IV30300] dtl

    where dtl.TRXSORCE not like ('IVTFR%')

    union all

    SELECT dtl.[DOCDATE]
    ,dtl.[ITEMNMBR]
    ,-dtl.[TRXQTY] as 'TRXQTY'
    ,dtl.[UNITCOST]
    ,dtl.trxqty * dtl.unitcost as 'value'
    ,dtl.[TRXLOCTN] as 'SITE'
    FROM [FOF].[dbo].[IV30300] dtl

    where dtl.TRXSORCE like ('IVTFR%')

    union all

    SELECT dtl.[DOCDATE]
    ,dtl.[ITEMNMBR]
    ,dtl.[TRXQTY]
    ,dtl.[UNITCOST]
    ,dtl.trxqty * dtl.unitcost as 'value'
    ,dtl.[TRNSTLOC] as 'SITE'
    FROM [FOF].[dbo].[IV30300] dtl

    where dtl.TRXSORCE like ('IVTFR%'))a

    where a.SITE in ('XXX','YYY')
    and a.DOCDATE <= '1/31/2018'
    and a.ITEMNMBR = 'ZZZ'

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans