245
We have used GP since GP8.0, and now running GP2010. We have many inventory items that we now would like to track lot #'s. Documentation says that we can change the tracking option as long as the Quantity is 0. Even when I adjust quantities to 0, I still cannot change the tracking option (grayed out). Oddly, there are some (small percentage) of items that the system does allow me to change the tracking option, so I know it is possible.
*This post is locked for comments
Hi Joel
It certainly wouldn't be recommended to delete a table. You may run into all sorts of issues.
Cheers
Heather
Thanks,
So there is no way I can delete a table of the data before restore the data to 2013?
Hi,
would like to this at inverse. We want to deactivate Lot Tracking from GP 2010 to GP 2013 before migrate the data because our new MWS will do it. How can I do this? and, when i migrate the data i will have to do something else?
MGriffiths,
Have you, in fact, never transacted with this item? This is curious. I would search all tables in the database for the item number I was trying to change. If something is out there it'll pop out. Here's the script to search the database:
/DECLARE @value VARCHAR(64)
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
SET @value = 'PUT THE VALUE YOU WANT TO FIND HERE'
CREATE TABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64)
)
DECLARE TABLES CURSOR
FOR
SELECT o.name, c.name
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
ORDER BY o.name, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
--SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) = ''' + @value + ''') '
SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
SET @sql = @sql + @column + ''')'
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @table, @column
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT *
FROM #t
DROP TABLE #t
I downloaded this from a generous soul on the Internet, but I am sorry that I didn't record from where it came.
Let us know if you find anything. I'm with you about modifying the tables, I like to be sure about these things before I get fast and loose with the data.
Kind regards,
Leslie
After further experimentation, I noticed that the items that I was unable to change the Lot tracking on also had a Bill of Material defined (BUT NO ASSEMBLIES PENDING!). When I DELETED the BOM for that item, I was then able to change the lot tracking option! Progress, but still a pain to have to delete BOM's then re-enter after making the change...
Thank you all for your ideas/comments. To further elaborate - not only does GP show that the QOH = 0, but ALL quantities show up as 0. No open PO's or sales orders. In fact, no activity EVER shown for the item! But still unable to change!
I have also tried a different item, where QOH > 0. I did an inventory adjustment to change Quantity to 0, and also still unable to change the lot tracking from None to LOT.
So, I'm still confused about what is preventing this, and am uncomfortable going in to modify item via SQL to force override unless someone can assure me that this is safe.
It makes sense to me that QOH must be 0 before changing, so making Qty adjustment before and after changing this seems appropriate.
Any further suggestions would be appreciated! Thanks!
Sorry Frank, although I have done that, it can cause problems. Make sure, like Dan said that all quantities are zero AND all of the sales orders and purchase orders are cancelled or deleted. If you are using manufacturing or project or field service, these transactions need to be cleaned out as well.
If you still think you have some minor fractional quantities in the inventory tables, look at the Inventory Asset GL accounts. If they have been reconcilled properly and regularly, they should be at zero as well. Then, two steps:
1. Update IV00102 and set quantity on hand = 0
2. Create a journal entry to set the value of inventory in the GL to zero
Change the flags, checklinks, reconcile, then start reloading inventory.
If all else fails, you can go into table IV00101 and change the value in the ITMTRKOPT (I think that's the column name, or something close to that). Make sure you take a backup of the database before making any direct table updates.
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,409
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
