Dynamics Community Forum Thread Details

Inventory reconcile report show repeated data

After running inventory reconcile report for a particular item it show blank report. Now I make out certain transaction ( issue the item with our software) then if I run again the report, it always show data in it.

Adjusted purchase receipt quantities by 0.040720 for receipt RCT130000494.

After reading blogs on the same site and through some analysis, I came to know that

issue occured due to mismatching in PCHSRCTY in IV10200, IV10201 and SEE30303.

I deliberately adjusted it to 5 i.e receipt instead of 1(inv. adjustment) just if to check if it gets fixed in any case.

But the issue is not getting fixed neither I am finding any reason to produce it.

Any help would be appreciated.

That's a common statement in reconciliation report. Your research is quite effective so far, inventory reconciliation encompasses few tables which are (IV10200, IV10201, IV00102 ... etc)

Although, I may go through a different way around this. When reconciling inventory, the two priority things for me purchase receipt work versus details (IV10200 and IV10201) and Item quantity master (IV00102).

Therefore, i may advice you to run the following script which will check your purchase receipt work versus details;

--- The following script will retrieve all the corrupted cost layers between IV10200 and IV10201

SELECT A.[ITEMNMBR],
        A.[TRXLOCTN],
        A.[RCTSEQNM],
        A.[QTYRECVD_200],
        A.[QTYSOLD_200],
        A.[RCPTSOLD_Index],
        B.[ITEMNMBR],
        B.[TRXLOCTN],
        B.[SRCRCTSEQNM],
        B.[QTYSOLD_201],
        A.[QTYSOLD_200] - B.[QTYSOLD_201] AS VARIANCE
        FROM
(
    SELECT [ITEMNMBR],
            [TRXLOCTN],
            [RCTSEQNM],
            SUM([QTYRECVD]) AS QTYRECVD_200,
            SUM([QTYSOLD]) AS QTYSOLD_200 ,
            RCPTSOLD_Index = CASE
            WHEN SUM([QTYRECVD])-SUM([QTYSOLD]) = 0
            THEN 1
            ELSE 0
            END
    FROM [IV10200]
    GROUP BY [ITEMNMBR], [TRXLOCTN], [RCTSEQNM]
) AS A
FULL OUTER JOIN
(
    SELECT    [ITEMNMBR],
            [TRXLOCTN],
            [SRCRCTSEQNM],
            SUM([QTYSOLD]) AS QTYSOLD_201
            FROM [IV10201]
            GROUP BY [ITEMNMBR], [TRXLOCTN], [SRCRCTSEQNM]
) AS B
        ON    A.[ITEMNMBR] = B.[ITEMNMBR] AND
            A.[TRXLOCTN] = B.[TRXLOCTN] AND
            A.[RCTSEQNM] = B.[SRCRCTSEQNM]
            WHERE
            ISNULL(A.[QTYSOLD_200],0) - ISNULL(B.[QTYSOLD_201],0)
            <> 0