Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Saturday, July 13, 2013

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 

IV10200

 

 

IV10201 |Inventory Purchase Receipts Work Detail

IV10201

 

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

After Reconciliation

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

7 comments:

  1. Thanks for the post Mahmoud. I ran the above scrip and 3 records pulled up. Whats next?

    ReplyDelete
    Replies
    1. Those three records are corrupted cost layers. The first step to get them corrected is to run the Inventory Reconciliation for each of the items above solely. Let me know what's the result

      Delete
    2. Hi Mahmood I run the scrit, few records pulled up. Then i run Inventory Reconciliation, executed your script again to see no changes at all. few recors that Pulled up is as below:
      ITEMNMBR TRXLOCTN QTYRECVD_200 QTYSOLD_200 ITEMNMBR TRXLOCTN SRCRCTSEQNM QTYSOLD_201 VARIANCE
      0033 6022 92.53000 92.53000 NULL NULL NULL NULL NULL
      0066 6022 5478.00000 5478.00000 NULL NULL NULL NULL NULL
      0259 6022 4225.00000 4225.00000 NULL NULL NULL NULL NULL
      208001 2004 2357.91000 2357.91000 208001 2004 1 2075.67000 282.24000
      0121 6022 1521.00000 1521.00000 NULL NULL NULL NULL NULL

      Delete
  2. Thanks for the script, I believe I can use it to help find corruption issues, but my next step is automating inventory reconcile (so it can run during off peak hours).

    The only solution I have come up with is SQL writing to a text file (which is in the form of an Inventory Reconcile macro); then adding a task to launch GP with the macro during off peak hours. Do you have any other solutions regarding possible Inventory Reconcile automation? Thanks

    ReplyDelete
    Replies
    1. The script above returns mainly the corrupted purchase receipt layers, further correction needs consideration for each of the records retrieved to determine what's the best method for resolution.
      In this essence, I would not go with the macro automation suggestion as inventory corruption should not be a usual issue. If you frequently have issues with inventory, then you might need to find the root causes of the problem.

      Delete
  3. Thanks for the details and script. Please need an advise. I run a reconciliation process about a week ago and it takes almost 3 days to finish. So, i came up with series of testing. I deleted some data on IV10200 and IV10202 (retain 3 months on current) and the reconciliation process only takes 3 hours. But i having issue on Historical stock status report major item are dis balance.
    Please help me.

    ReplyDelete
    Replies
    1. Deleting records from IV10200 is not something to consider at all, it will have negative ramifications on several associated modules and functions

      Delete