Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Deleting old sites fro...
Microsoft Dynamics GP (Archived)

Deleting old sites from items

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee
We are cleaning up legacy data (15+ years of different teams changing processes and not cleaning up as they go). 
One of the tasks is to delete old sites from all non-discontinued items.  We had thought to do this task manually, however, running a Smartlist we came up with 395,840 records item/site combinations.  We have determined that this will take 1,003 hours to manually remove the old sites from the items so am looking for an automated solution.  We can't just delete the sites because they're assigned to items - gotta do that cleanup first. 
Is this task SQL scriptable?  If so, has anyone done this and have a script handy that they'd share?  We're running GP 2010.
Thanks!!
Jeanne

*This post is locked for comments

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: Deleting old sites from items

    Mariano makes a good point, as does Dan and some others. I think you first need to figure out how the machine can determine which item/sites need to be deleted. Once you have that list, you can use it as a source for a mail-merge macro that can run against the selected company. You can use a combination of the Remove Transaction History utility for the target item/site and SQL for the Purchase Receipts and price change history that needs to be deleted. If you can run a year-end close on inventory, you can remove Discontinued items that have no current quantities. You said these items weren't discontinued, so nix that. You could use a macro to run through and discontinue them and then run the Year End close and delete them. I think this also will delete the item transaction history. I think you can 'automate' this, but I think you'll need several macros you could string together to accomplish it. POs can be moved to history and then the history can be deleted with IV Utilities. It's best to do all you can through the user interface. Inventory has a lot of strings tied to it, as you are discovering. I think that doing anything to nearly 400,000 records requires a lot of careful planning.

    Kind regards,

    Leslie

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: Deleting old sites from items

    Your script assume no quantities on hand or recent activity for the item. However, that's not the problem. What do you do with item/site combinations with historical records? You can't just delete these records as you would create orphan records.

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: Deleting old sites from items

    What do you call an "old site" or in other words, what constitutes a deletable item/site combination, also since the items are not discontinued, what are you planning to do with the history associated to the item/site combination.

  • Rosemary Profile Picture
    Rosemary 4 on at
    RE: Deleting old sites from items

    Hi,

    The same issue came up me me when I tried the same script. I saw it was because of purchase orders and receipts that were still on the system.

    The historical transactions to the previous financial year could not be removed as yet.

    Regards

    Rosemary

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Deleting old sites from items

    We attempted this process and successfully deleted the site.  However a reconcile brought back the deleted site along with all of the site-item assignments for that site.  There are a number of references to this SQL process that is also outlined in "Confessions of a Dynamics GP Consultant", however there is no reference to cleaning up the IV10201 table, which is the detail table for IV10200.

    Is there any reason one would not purge the IV10201 table as well? (ie: Delete from IV10201 where TRXLOCTN='site id')

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Deleting old sites from items

    This might be useful. Make sure to post all transactions and batches involving inventory before running below script. Any comments is very much welcome.

    DELETE FROM IV00102 WHERE

    LOCNCODE = 'site id' AND

    (QTYONHND <> 0 OR

    QTYONORD <> 0 OR

    QTYINUSE <> 0 OR

    ATYALLOC <> 0 OR

    QTYBKORD <>0 OR

    QTYRTRND <> 0 OR

    QTYINSVC <> 0 OR

    QTYDMGED <> 0 )

    DELETE FROM IV10200 WHERE TRXLOCTN='site id'

    UPDATE IV40700 SET

    PORECEIPTBIN='',

    PORETRNBIN='',

    SOFULFILLMENTBIN='',

    SORETURNBIN='',

    BOMRCPTBIN='',

    MATERIALISSUEBIN='',

    MORECEIPTBIN='',

    REPAIRISSUESBIN=''

    WHERE LOCNCODE='site id'

  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: Deleting old sites from items

    Assuming no ISV's, you could run a script in SQL.   I would do a backup and also test prior to doing in a live environment.   It would be removing records from the IV00102 or the Quantity Master.   And as Max suggested, I would make sure no qty's exist at any item/site combo.

    Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com

  • DinB Profile Picture
    DinB 3,812 on at
    Re: Deleting old sites from items

    I think you can try to use GP's macro function to delete the Item/Site records. Since this involves relatively simple steps, you should be able to record a macro and then create a long macro by repeated copy and paste in the notepad. Before running the macro, You will still need to make sure there are no Quantities for the Item/Site combination. This method will be safer than using SQL Script, since there is less risk of damaging referential integrity of the data.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,387 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans