Dynamics GP Bulk Delete

I'm running Dynamics GP 10.0 and have approx. 40,000 inventory items that have never been used. Is there a recommended procedure for completely deleting these from the system?

I don't want to have to go into every inventory card and manually delete as this would take forever.

I suppose a last resort would be SQL script to delete from the tables but that's a last resort, unless someone knows all the tables the script would need to touch?

Thanks for any suggestions you may have
BrianFordAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thekneeCommented:
Hi Brian,

There is not tool in GP out of the box to do this in batch.

If your are sure that your items was never use in any transaction there is no probleme to delete the record by the back door.

I will recommend to First do a Good reliable backup
Double check in each table if the item is used or not with this peace of code you should be able to find out
declare @CMD char(2000)
declare @DB varchar(100)
declare @itemnmbr varchar(31)

Set @itemnmbr = '100xlg'

set nocount on

declare db cursor for 
Select object_name(id) from sys.syscolumns where name = 'itemnmbr'
order by object_name(id)

open db


fetch next from db into @db


WHILE @@FETCH_STATUS = 0
BEGIN


  print 'Record Counts for database ' + @DB


  set @CMD = 'SELECT count(*) from '+ @db + ' where itemnmbr = ''' + @itemnmbr + ''''  


  exec (@CMD)


  fetch next from db into @db
end


close db
deallocate db

Open in new window




Once your clean up is done, Run check link and reconcile over the inventory and your should be good with this

Don't forget the backup.


0
BrianFordAuthor Commented:
thanks for your help, I'll check this out :)
0
Abdulmalek_HamshoCommented:
Do you have Manufacturing, Project Accounting, or Field Service modules installed and used?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

BrianFordAuthor Commented:
We have all 3 installed, but we are only using the Manufacturing module
0
thekneeCommented:
not in our system,  But I have access to some setup. what do you need to know
0
Abdulmalek_HamshoCommented:
Unfortunately, the above script won't be sufficient to ensure that the item has been used or not, since the Manufacturing module uses other column labels for items e.g. CPN_I and PPN_I in BM010115. Moreover, you may delete an item that is a BOM or a component of an existing BOM, the thing which may result in orphans.

May I know the approximate number of the items that are existing in your DB and have not been used?
0
BrianFordAuthor Commented:
approx 40,000
0
Abdulmalek_HamshoCommented:
Oops, I thought the overall total is 40,000.

Let me ask you these questions to know how the final script will look like:

1- What would you do with an item that is used in a BOM but has never been used in any transaction?

2- What would you do with an item that is a BOM, but has never been used?
0
BrianFordAuthor Commented:
All our finished goods items have a BOM, but if we never used the item, we've never used the BOM

If we have used BOM, then we've most likely used it in a transaction

My goal is to completely delete any inv item that we have never used, we have around 43K items and only ever used 3K of them
0
Abdulmalek_HamshoCommented:
Well, can we say this:

Delete all items that:

1- Have not been purchased;
2- used in any BOM; nor
3- are themselves BOMs.

?
0
Abdulmalek_HamshoCommented:
Sorry, rephrase:

Well, can we say this:

Delete all items that:

1- Have not been purchased;
2- aren't used in any BOM; nor
3- are not themselves BOMs.

?
0
BrianFordAuthor Commented:
yes, we can say that :)
0
Abdulmalek_HamshoCommented:
Do the following to delete all of the items that have not been Purchased, Sold, Used in BOM, Returned, had an Adjustment, used in Kit, nor had a Work Orders:

1- Make a Full Backup for your DB.

2- Run the following script:

=============================================================
DELETE * FROM IV00101 WHERE ITEMNMBR
NOT IN
(SELECT PPN_I FROM BM010115) AND ITEMNMBR
NOT IN
(SELECT CPN_I FROM BM010115) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM WO010032) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM POP30310) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM POP10310) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM SOP10200) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM IV00104) AND ITEMNMBR
NOT IN
(SELECT CMPTITNM FROM IV00104) AND ITEMNMBR
NOT IN
(SELECT CMPTITNM FROM IV10200) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM IV00102 WHERE RCRDTYPE = 1 AND
      (QTYONORD <> 0 OR QTYBKORD <> 0 OR QTYINUSE <> 0 OR QTYINSVC <> 0 OR QTYRTRND <> 0 OR QTYDMGED <> 0 OR QTYONHND <> 0 OR ATYALLOC <> 0 OR QTYSOLD <> 0 OR QTY_Drop_Shipped <> 0))
      
GO
===============================================
3- Repeat the previous script for the following tables: IV00102, IV00103, IV00105, IV00106, IV00107, IV00108. For example:
===============================================
DELETE * FROM IV00102 WHERE ITEMNMBR
NOT IN
(SELECT PPN_I FROM BM010115) AND ITEMNMBR
NOT IN
(SELECT CPN_I FROM BM010115) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM WO010032) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM POP30310) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM POP10310) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM SOP10200) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM IV00104) AND ITEMNMBR
NOT IN
(SELECT CMPTITNM FROM IV00104) AND ITEMNMBR
NOT IN
(SELECT CMPTITNM FROM IV10200) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM IV00102 WHERE RCRDTYPE = 1 AND
      (QTYONORD <> 0 OR QTYBKORD <> 0 OR QTYINUSE <> 0 OR QTYINSVC <> 0 OR QTYRTRND <> 0 OR QTYDMGED <> 0 OR QTYONHND <> 0 OR ATYALLOC <> 0 OR QTYSOLD <> 0 OR QTY_Drop_Shipped <> 0))
      
GO
======================================
4- Run Check Links and Reconcile Inventory, Purchasing, and Sales. Please post the reconciliation results here to ensure the safety of the process.
0
BrianFordAuthor Commented:
This is great, thank you very much for help.

I'll be running this in our DEV environment first which is current down, so will post results over the week end

thanks again
0
Abdulmalek_HamshoCommented:
You can execute the following script to see what items are going to be deleted with the above script:

============================================
SELECT * FROM IV00101 WHERE ITEMNMBR
NOT IN
(SELECT PPN_I FROM BM010115) AND ITEMNMBR
NOT IN
(SELECT CPN_I FROM BM010115) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM WO010032) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM POP30310) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM POP10310) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM SOP10200) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM IV00104) AND ITEMNMBR
NOT IN
(SELECT CMPTITNM FROM IV00104) AND ITEMNMBR
NOT IN
(SELECT CMPTITNM FROM IV10200) AND ITEMNMBR
NOT IN
(SELECT ITEMNMBR FROM IV00102 WHERE RCRDTYPE = 1 AND
      (QTYONORD <> 0 OR QTYBKORD <> 0 OR QTYINUSE <> 0 OR QTYINSVC <> 0 OR QTYRTRND <> 0 OR QTYDMGED <> 0 OR QTYONHND <> 0 OR ATYALLOC <> 0 OR QTYSOLD <> 0 OR QTY_Drop_Shipped <> 0))
=================================================

Go through them and verify that there are no important items are included in the list.
0
BrianFordAuthor Commented:
great again... thanks
0
BrianFordAuthor Commented:
I just ran the SELECT script to see what 's going to get deleted and it only returned 5506 rows nothing near the 40,000 I was hoping for :(

an ideas?

also your code has 2 selects that are returning col CMPTITNM is this correct because it does not exist in IV10200
thanks
0
Abdulmalek_HamshoCommented:
Sorry, it's a mistake. Replace CMPTITNM with ITEMNMBR when it related to IV10200 table. Replace it in the DELETE statement also.

It should like this:

NOT IN
(SELECT ITEMNMBR FROM IV10200) AND ITEMNMBR
0
Abdulmalek_HamshoCommented:
Regarding the number of items the SELECT statement returned, the SELECT Statement as mentioned before, returns the list of items that:

1- Have never been Purchased.
2- Have never been Sold
3- Have never been Returned by a customer
4- Have no quantities in the stock
5- Have never been Manufactured
6- Are not BOM or are used in a BOM
7- Have never had an Inventory Transaction (Adjustment or Transfer)
8- Are not Kit or are used in a Kit

Do you want to exclude any of the above conditions?
0
BrianFordAuthor Commented:
I believe your criteria is correct, however, can I clarify one thing...

EVERY Inventory Item we have, has an associated BOM, if we have never sold the item,then we have NEVER used the BOM and therefore I suppose that needs deleting also, is that covered in your script?

My original figure of 40k came from subtracting the 3.5k that the script below returns, from the number of items in IV00101 (approx 43k)

This was provided to me several years ago and is suposed to return all items that have been sold, perhaps the script or my logic is flawed ? :(

select ITEMNMBR from SOP10200
inner join SOP10100 on SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
where voidstts <> '1' and ITEMNMBR <> ''
group by ITEMNMBR
union all
select ITEMNMBR from SOP30300
inner join SOP30200 on SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where voidstts <> '1' and ITEMNMBR <> ''
group by ITEMNMBR

thanks again
0
Abdulmalek_HamshoCommented:
Brian, my script doesn't delete the item if it's a BOM or part of a BOM, if you want to remove this condition, you would be having orphaned BOMs, for example:

Suppose you have the following BOM:

X1
  A
  B
  Y2
    C
    D

Suppose item C has been added recently to this BOM and has never been used in any transaction, if we deleted this item, the BOM X1 will be referring to an orphan item. The thing which you don't want to happen.

If you want to delete an item that has never been used in transactions and is a BOM or part of a BOM, and you're sure that at the end the existing used BOMs won't refer to orphan items, you need to remove this part from the above script:

NOT IN
(SELECT PPN_I FROM BM010115) AND ITEMNMBR
NOT IN
(SELECT CPN_I FROM BM010115) AND ITEMNMBR
0
BrianFordAuthor Commented:
THanks again for your help
0
Abdulmalek_HamshoCommented:
With pleasure dear.
0
BrianFordAuthor Commented:
Does the script I posted earlier look correect for listing any item that HAS been sold?
0
Abdulmalek_HamshoCommented:
It's correct, but it shows the quoted items as well. You can use this one to see the ever Sold, Returned, or Backordered items:

SELECT ITEMNMBR FROM IV00102 WHERE RCRDTYPE = 1 AND (QTYSOLD <> 0 OR QTYBKORD <> 0 OR QTYRTRND <> 0)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrianFordAuthor Commented:
thank so much for help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Tax / Financial Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.