Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Answered

Item Qty Master vs Item Site Bin Master tables - data not matching

Posted on by Microsoft Employee

hi,

I hope someone can respond with the clue.

We run GP 2016 and multi-bin locations enabled for inventory items.

When I compare bin locations for particular item b/w Item Qty Master (IV00102) and Item Site Bin Master (IV00112) - data does not match, i.e. existing QtyOnHand in shown located in bin XX in IV00102 table and in bin YY in IV00112 table for the same item.

When I look at the data via GP in Inquiry->Inventory->Bin Qty  screen - the data shown seems to be taken from IV00112 table, but I don't understand how and why IV00102 where RCRDTYPE=2 (rows reflecting specific bin locations qtys) does not match data from IV00112.

I have ran Inventory Recon but I did not do anything and I still have the same situation.

I am integrating our GP and pushing a lot of various data from GP into the company db using EONE SmartConnect and I need to know exactly which table I need to rely on as my data source for specific data.

Any suggestion or reference is greatly appreciated!

Thank you!

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Item Qty Master vs Item Site Bin Master tables - data not matching

    Tim,

    thank  you! I have followed your article in the on your blog and its extemely helpful.

  • Verified answer
    Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: Item Qty Master vs Item Site Bin Master tables - data not matching

    Please see this thread on similar issue.

    community.dynamics.com/.../227004

    See if any of the advice in there helps, let us know how you get on.

    Also my post:

    http://www.timwappat.info/post/2016/12/07/Puzzle-of-the-negative-bin-quantities-in-Dynamics-GP

    Has a script that should return no rows, I schedule it to run on a conditional email (only send if it returns results) from a SQL job to look at any corruption as soon as it happens, helps to identify the cause.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: Tim Wappat
    -- Create date: 2016-12-05
    -- Description: Checks the locations have the same qty as the bins in those locations
    -- =============================================
    ALTER PROCEDURE [ourschema].[CA_Admin_EmailBinsLocationsCheck]

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;;
    WITH CTE_BinStock
    AS (
    SELECT ITEMNMBR
    ,LOCNCODE
    ,SUM(QUANTITY) SumQty
    FROM IV00112
    WHERE QTYTYPE = 1
    GROUP BY ITEMNMBR
    ,LOCNCODE
    )
    SELECT iv.ITEMNMBR
    ,iv.LOCNCODE
    ,QTYONHND [Location Qty]
    ,ISNULL(SumQty, 0) AS [Total Bins Qty]
    FROM IV00102 iv
    LEFT JOIN CTE_BinStock ivb ON iv.ITEMNMBR = ivb.ITEMNMBR
    AND iv.LOCNCODE = ivb.LOCNCODE
    WHERE ISNULL(SumQty, 0) != QTYONHND
    AND iv.RCRDTYPE = 2
    END
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Item Qty Master vs Item Site Bin Master tables - data not matching

    Unfortunately this did not help - I ran checklinks and full recon after.

    Same issue persists.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Item Qty Master vs Item Site Bin Master tables - data not matching

    not yet.... My recon takes a while, so I can only do it after hours. Let me try checklinks first and then recon...

    Unfortunately might have to wait another day to see if this help....

    Thank you for the suggestion! Will post the results!

  • Suggested answer
    KirkLivermont Profile Picture
    KirkLivermont 5,985 on at
    RE: Item Qty Master vs Item Site Bin Master tables - data not matching

    Hi Eugene,

    Have you tried running checklinks and then reconciling inventory?

    Regards,

    Kirk

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans