We have approximately 2000 items in our inventory that we need to change the class ID to 1CObsolete 1D. Is there any way that we can run a process in SQL server that will change all item 19s class ID at once?

(0) Share

Report

Posted on by Community Member

We have approximately 2000 items in our inventory that we need to change the class ID to “Obsolete”. Is there any way that we can run a process in SQL server that will change all item’s class ID at once?

Or does GP have a Utility to do this?

Thanks,

Doreen

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Mohamed El-Tohamy 305 on at

Like (0)

Report

Re: We have approximately 2000 items in our inventory that we need to change the class ID to 1CObsolete 1D. Is there any way that we can run a process in SQL server that will change all item 19s class ID at once?

If your going to update the 2000 items you can use the below code but before that you must create 'OBSOLETE' as a inventory class first then run the code at the SQL

UPDATE IV00101

SET ITMCLSCD = 'OBSOLETE'

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Re: We have approximately 2000 items in our inventory that we need to change the class ID to 1CObsolete 1D. Is there any way that we can run a process in SQL server that will change all item 19s class ID at once?

Hi Doreen,

A question not asked yet, what are you wanting to accomplish by changing the class ID to obsolete? Are you merely using it for a grouping of some kind, or do you want to change any of the settings in the item card of the 'obsolete' items?

Kind regards,

Leslie

Was this reply helpful? Yes No
Suggested answer

Richard Whaley 25,195 on at

Like (0)

Report

Re: We have approximately 2000 items in our inventory that we need to change the class ID to 1CObsolete 1D. Is there any way that we can run a process in SQL server that will change all item 19s class ID at once?

In any of the methods suggested above, accounts and amounts in those accounts are NOT addressed. NOW, if your firm only uses one account for Inventory Assets, no problem. But if you have an item whose IA account is (for example) 1001 and the obsolete inventory IA is 1100, changing the class ID won't change the account number. If you say, no problem, I will simply change the account index as well, you are potentially leaving dollars in the original account that are not moved to the new account.

Better scheme: Have an Obsolete Inventory SITE, do a transfer transaction to move items from the warehouse (whatever you call it) to the Obsolete site. This will move the items as well as the dollars and quantity on hand. AND, the transaction can be imported from a spreadsheet and executed easily.

BEST Scheme: Leave the items in the same site but change their Item Type to Discontunued. This puts a flag on the item so that they potentiall cannot be sold or purchased (there are a couple of setup options to consider). This can be accomplished via a SQL script easily as there are no accounting changes!

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Re: We have approximately 2000 items in our inventory that we need to change the class ID to 1CObsolete 1D. Is there any way that we can run a process in SQL server that will change all item 19s class ID at once?

Please explain the reluctance do perform a SQL update on the class ID.

update IV00101. ITMCLSCD='OBSOLETE' where itemnmbr in (select itemnmbr from listofitems).

All you are doing is changing the class ID. Whatever GL accounts were assigned before will still be there after the update. You will need to create the OBSOLETE class first and assign GL accounts to that class. You make them the same or some dummy account. If you set them to a dummy account, after you do the update, go to the class and set all the GL accounts to what you want them to be and then choose to have these changes rolled down to all items in this new class. Now you will have the 2000 items all assigned to the OBSOLETE class with the correct GL accounts.

Was this reply helpful? Yes No
Suggested answer

Mohamed El-Tohamy 305 on at

Like (0)

Report

Re: We have approximately 2000 items in our inventory that we need to change the class ID to 1CObsolete 1D. Is there any way that we can run a process in SQL server that will change all item 19s class ID at once?

Hello Doreen,

You can update from the SQL direct in table IV00101 in field ITMCLSCD, but I don't recommend that because the script will not update the accounts, but you can do it using the Integration Manager by selecting in the Destination Edit Mode: Update Only. at the template you will just need to add the Item ID and the new Class ID.

Wish that this is useful for you.

BR,

Mohammed Tohamy

http://mtohamy.blogspot.com/

Was this reply helpful? Yes No
Andy Sather on at

Like (0)

Report

Re: We have approximately 2000 items in our inventory that we need to change the class ID to 1CObsolete 1D. Is there any way that we can run a process in SQL server that will change all item 19s class ID at once?

Hello Doreen - We do not have a Utility to do this within GP and updating through SQL would not be supported. I will open this up to the Community to provide some guidance.

Was this reply helpful? Yes No