Inventory Reconciliation - Purchase Receipt Work and Details

One of the most important steps performed during Inventory Control Reconciliation is reconciling the purchases receipt details [IV10201] against purchase receipt work [IV10200]. This is practically performed by linking all the "details layers" to the associated "work layer".
Dynamics GP joins both tables through a receipt sequence number that is unique on an item-location base.

• Purchase Receipt Work   | IV10200 - [RCTSEQNM]
• Purchase Receipt Details | IV10201 - [SRCRCTSEQNM]

The relationship between these two tables is one-to-many, which means that several documents in the [IV10201] withdraw from a specific cost layer in [IV10200 ]. It is important to remember that Receipt sequence number is unique on an item-location level.

The following shows an example of reconciliation process effects;

Regular Case  | Due to posting interruption, a record is missing from Purchase Receipt Details

Before Reconciliation
IV10200 |Inventory Purchase Receipts Work 

 

 

IV10201 |Inventory Purchase Receipts Work Detail

 

After Reconciliation
The following Recon is added into purchase receipt details in order to match the quantities between the inventory tables

Once the inventory reconciliation process is finished, the report will show the quantity adjustments made above on an item level as follows;

Reconcile Report

Item Number: 100XLG
Item Description: Green Phone
Transaction Location: Main
Added Quantity Sold detail record quantity of 3 for receipt Transfer1111 

--SQL Script - Corrupted Cost Layers
--The following script retrieves the corrupted purchase receipt details against purchase receipt work layers.

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

 

Best Regards,
Mahmoud M. AlSaadi