Microsoft Dynamics GP

Inventory Year End, Remove Sold Receipts and Cost Change History

Tim Wappat

Jan 1, 2017 • 5 min read

FIFO has to be maintained somewhere so goods receipts are added to table IV10200, as the inventory is consumed the QTYSOLD field is incremented against the receipt, until the QTYRECVD = QTYSOLD and at this point the field RCPTSOLD is set from 0 to 1, to indicate that the receipt has been totally consumed. The order of receipts is chosen depending on the cost model, FIFO etc.

As can be seen above, as part of the year end closing, the option is provided to remove any consumed receipts before a specified date. If this option is chosen, then GP will look for receipts with RCPTSOLD=1 and DATERECD<{Date Provided} and remove them from the IV10200 table. It will also remove the records in IV10201 that represent the demand against the receipt. You can see this if you run a SQL trace while it runs.

DELETE FROM IV10201 WHERE ITEMNMBR = 'myitem' AND SRCRCTSEQNM = 1

I was worrying if this would affect the stock levels if I reconciled the inventory after removing the receipts. If you look at my other post Using SQL UNPIVOT operator to reconcile Dynamics GP inventory items then you will note that inventory reconcile calculates the available stock from the IV10200 table using:

SUM(QTYRECVD) - SUM(QTYSOLD)

As we are only removing rows where these are equal (as the whole receipt is sold), then they are rows that were not participating in the stock quantity anyway, hence no effect on stock reconciles.

In the above script SRCRCTSEQNM=1 (Source Receipt Sequence Number) is from the RCTSEQNM (Receipt Sequence Number) of the record removed from IV10200 binding demand and source. – or so it looks at first sight.

… hey wait…

The key for receipts table IV10200 allows the same item to have the same shared RCTSEQNM if the dates received is different. Indeed our data has this a lot of the time, see the table screen shot for an item below, see how the item has multiple RCTSEQNM=1. I don’t think this should happen.

I had a look an our test copy of the live company, ran the above year end routine and indeed it removed all records from IV10201 for the sequence number, even though I think they related to records still remaining in IV10200! I’ve seen some posts regarding people finding missing data in their systems but not being able to track down why and what makes it happen, I think perhaps this may be an answer? It may be that I am interpreting what I am seeing wrong too, I need more information on how records relate.

Further investigation…

I don’t think I’m wrong, I have the feeling that we have some serious corruption in our table! After running the remove sold receipts then reconciling one of the items affected gives us this result:

GP has added a record back into IV10201 to replace the one that was taken by the year end utility, I think this is a good indication that this is not supposed to have happened.

GP creates a new record in IV10201 with ORIGINDOCID field set to “RECON” (reconcile) for the full amount sold on the receipt, as it can’t now find the transaction details, as they were removed by the year end utility.

I’m guessing that the RCTSEQNM should auto increment for each receipt and that there should not duplicate RCTSEQNM for any item. The following script checks for instances of this happening in correctly.

SELECT 
ITEMNMBR,
RCTSEQNM,
COUNT(*) ,MAX(DATERECD)
FROM IV10200 
GROUP BY ITEMNMBR, RCTSEQNM
HAVING COUNT(RCTSEQNM)>1
ORDER BY 4
(19498 row(s) affected)

Oh crickey! - there are 19498 item sequence number combinations where this has occurred. Looking at the first part of the result set we see this is real bad, some of the sequence numbers have been used 735 times, and that is just at first glance, I would rather not dig deeper. With the dates added, I see that this happened between years 1998 and 2009. I guess whatever the cause was went away after that year. So these days it does work how I would expect and the records are tied between the 200 and 201 tables by the RCTSEQNM (when the data is correct).

Where do I go from here?

So I need to clean up this mess, it looks like it may not be possible to recover from this and preserve data. As it happens I want to trim these receipts back to seven years anyway, so if I do that the problem will solve itself, I think. An item reconcile after the trim will rebuild dummy records for sold quantities if any are missing, so we end up in a better place than I am now.

It is not a good idea to delete a receipt and associated detail if consumed recently as it makes processing returns more difficult once a receipt has been removed.

If you don’t want to run year end for inventory, that updates starting qtys and summaries, you could try writing a SQL script to remove the records. This is something I've ended up doing, archiving the records in case I ever need them again too.

Here is the script I used, beware -this is only here as an example. You NEED to check this with your test environment first and be very careful about using it. I cannot fully guarantee this will be safe to run for your GP installation.

BEGIN TRANSACTION

DECLARE @DateThreshold DATETIME

SELECT @DateThreshold = '20091101'

DECLARE @ToRemove AS TABLE (
    DATERECD DATETIME
    ,ITEMNMBR CHAR(31)
    ,TRXLOCTN CHAR(11)
    ,RCTSEQNM INT
    ,QTYTYPE SMALLINT
    );

INSERT INTO @ToRemove (
    DATERECD
    ,ITEMNMBR
    ,TRXLOCTN
    ,RCTSEQNM
    ,QTYTYPE
    )
SELECT DISTINCT rcpt.DATERECD
    ,rcpt.ITEMNMBR
    ,rcpt.TRXLOCTN
    ,rcpt.RCTSEQNM
    ,rcpt.QTYTYPE
FROM IV10200 rcpt
WHERE rcpt.RCPTSOLD = 1
    AND (rcpt.DATERECD < @DateThreshold)

--AND rcpt.ITEMNMBR='01-214'
DELETE IV10200
OUTPUT DELETED.[ITEMNMBR]
    ,DELETED.[TRXLOCTN]
    ,DELETED.[DATERECD]
    ,DELETED.[RCTSEQNM]
    ,DELETED.[RCPTSOLD]
    ,DELETED.[QTYRECVD]
    ,DELETED.[QTYSOLD]
    ,DELETED.[QTYCOMTD]
    ,DELETED.[QTYRESERVED]
    ,DELETED.[FLRPLNDT]
    ,DELETED.[PCHSRCTY]
    ,DELETED.[RCPTNMBR]
    ,DELETED.[VENDORID]
    ,DELETED.[PORDNMBR]
    ,DELETED.[UNITCOST]
    ,DELETED.[QTYTYPE]
    ,DELETED.[Landed_Cost]
    ,DELETED.[NEGQTYSOPINV]
    ,DELETED.[VCTNMTHD]
    ,DELETED.[ADJUNITCOST]
    ,DELETED.[QTYONHND]
INTO CANFDARCHIVE..IV10200
FROM IV10200
JOIN @ToRemove r ON IV10200.DATERECD = r.DATERECD
    AND IV10200.ITEMNMBR = r.ITEMNMBR
    AND IV10200.RCTSEQNM = r.RCTSEQNM
    AND IV10200.TRXLOCTN = r.TRXLOCTN
    AND IV10200.QTYTYPE = r.QTYTYPE
WHERE RCPTSOLD = 1
    AND (IV10200.DATERECD < @DateThreshold)

--Remove orphan records
DELETE ld
OUTPUT DELETED.[ITEMNMBR]
    ,DELETED.[TRXLOCTN]
    ,DELETED.[QTYTYPE]
    ,DELETED.[DOCDATE]
    ,DELETED.[RCTSEQNM]
    ,DELETED.[ORIGINDOCTYPE]
    ,DELETED.[ORIGINDOCID]
    ,DELETED.[LNSEQNBR]
    ,DELETED.[CMPNTSEQ]
    ,DELETED.[QTYSOLD]
    ,DELETED.[UNITCOST]
    ,DELETED.[IVIVINDX]
    ,DELETED.[IVIVOFIX]
    ,DELETED.[SRCRCTSEQNM]
    ,DELETED.[TRXREFERENCE]
    ,DELETED.[PCHSRCTY]
INTO CANFDArchive.[dbo].[IV10201]
FROM IV10201 ld
LEFT JOIN IV10200 h 
ON h.ITEMNMBR=ld.ITEMNMBR 
AND h.TRXLOCTN=ld.TRXLOCTN 
AND h.RCTSEQNM = ld.SRCRCTSEQNM
AND h.QTYTYPE=ld.QTYTYPE
WHERE h.DATERECD is null

COMMIT TRANSACTION