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

(0) Share

Report

Posted on by Community Member

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!

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

KirkLivermont 5,985 on at

Like (0)

Report

Hi Eugene,

Have you tried running checklinks and then reconciling inventory?

Regards,

Kirk

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Same issue persists.

Was this reply helpful? Yes No
Verified answer

Tim Wappat 5,711 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Tim,

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

Was this reply helpful? Yes No