Hello Jim
As derived from the complexity of lot number tracking process in Dynamics GP, and the degree to which it is involved in several distribution modules such as purchasing, sales, inventory, manufacturing ..etc. The test has been limited only to POP, SOP and IV lots. The lot number tracking primary tables, on which all the modules depend for open cost layers is (IV00300), this tables retrieves primarily form (IV10200) for the items with lot number tracking option enabled, and available quantity on hand.
Other modules, stores historical lots and details since the open lot table (IV00300) get cleared out when the lot is consumed. I have followed the following testing criteria and here are my results:
- Manual Insertion, for the primary Inventory table. Through this scenario, I posted all the pending transactions related to inventory, cleared all the lot related tables (POP10330, SOP10201 and IV00300, IV00301). Historical lot tables are not touched, since it should not affect open transactions, no harm to keep them. The following script was built to re-create the lot in IV00300
USE TWO
INSERT INTO dbo.IV00300
( ITEMNMBR ,
LOCNCODE ,
DATERECD ,
DTSEQNUM ,
LOTNUMBR ,
QTYRECVD ,
QTYSOLD ,
ATYALLOC ,
UNITCOST ,
RCTSEQNM ,
VNDRNMBR ,
LTNUMSLD ,
QTYTYPE ,
BIN ,
MFGDATE ,
EXPNDATE
)
SELECT A.ITEMNMBR ,
A.TRXLOCTN AS LOCNCODE ,
A.DATERECD ,
A.DEX_ROW_ID AS DTSEQNUM ,
A.DEX_ROW_ID AS LOTNUMBR ,
A.QTYRECVD ,
A.QTYSOLD ,
0 AS ATYALLOC ,
A.UNITCOST ,
A.RCTSEQNM ,
A.VENDORID ,
0 AS LTNUMSLD ,
A.QTYTYPE ,
0 AS BIN ,
'1900-01-01 00:00:00.000' AS MFGDATE ,
'1900-01-01 00:00:00.000' AS EXPNDATE
FROM dbo.IV10200 AS A
LEFT OUTER JOIN dbo.IV00101 AS B
ON A.ITEMNMBR = B.ITEMNMBR
WHERE B.ITMTRKOP =3 AND A.QTYRECVD-A.QTYSOLD <> 0 AND A.RCPTSOLD <> 1
Furthermore, proceed with the usual reconciliation and check links as a must in such a case.
- The second testing scenario, was more simple and straight forward. I followed the same steps by posting all the pending transactions, clearing the lot tables and then instead of inserting lot numbers manually (fabricated lots), I have proceeded with the reconciliation immediately. And here is the interesting part, new RECON lots have been created in all the lot associated tables according to the open cost layers. I believe, this is the ultimate solution instead of fabricating lots.
Finally, as I previously mentioned, I do not recommend this solution as a work around, but it would be application in case further testing is applied. For the mean time, you may proceed with creating a testing environment , and apply the testing scenario.
Keep me posted on your findings,
Looking forward to hearing what's hiding beneath your database records, since your it has gone through a lot and we never know how things would go with you :)
