Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Clearing Inventory Ite...
Microsoft Dynamics GP (Archived)

Clearing Inventory Items balances

(0) ShareShare
ReportReport
Posted on by Noel Simela 385

Hi All,

I imported Inventory Module settings and Items from an old company to a new company through DTS.

The client then complained that they don't want items in the new companies to have any of the balances inherited from the old company

I then ran the following script to clear out Inventory Items Balances:

UPDATE IV00102
SET QTYONHND =0,QTYSOLD =0,BGNGQTY =0,LSORDQTY =0,LRCPTQTY =0

But this did'nt resolve the issue.Am I missing anything?

Would appreciate if someone can point me in the right direction.

 

*This post is locked for comments

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    Re: Clearing Inventory Items balances

    That would make sense. Just zeroing the item quantity master table(IV00102) would not be sufficient because the inventory transactions would exist in the purchase receipts table(IV10200). So as soon as you run reconcile, the quantities would come back. Get rid of the transactions, run reconcile and there you go! Glad everything worked out.

  • Noel Simela Profile Picture
    Noel Simela 385 on at
    Re: Clearing Inventory Items balances

    Thank you gentlemen for the insight.I combined your responses into one solution and it seems to work.

    Ran the updated update statement authored by David and followed Richard's suggestion and reconciled.

  • Verified answer
    David V Profile Picture
    David V 270 on at
    Re: Clearing Inventory Items balances

    Richard is right, as usual.  If you brought over balances via DTS, Noel, you might be missing the reconcile step Richard mentions last.

    Although I would have updated more fields in my update, after the copy.  The update statement I would have run looks more like this:

    UPDATE iv00102
    SET    [QTYRQSTN] = 0,
           [QTYONORD] = 0,
           [QTYBKORD] = 0,
           [QTY_Drop_Shipped] = 0,
           [QTYINUSE] = 0,
           [QTYINSVC] = 0,
           [QTYRTRND] = 0,
           [QTYDMGED] = 0,
           [QTYONHND] = 0,
           [ATYALLOC] = 0,
           [QTYCOMTD] = 0,
           [QTYSOLD] = 0,
           [LSORDQTY] = 0,
           [LRCPTQTY] = 0 

     

    I've noticed that using the Copy Company Automated Solution or copying setups as described in KB 872709 would have you copying over the item quantity master (IV00102).  I typically run the above update and then reconcile.

    D. Valade

    http://gpworking.com

  • Verified answer
    Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    Re: Clearing Inventory Items balances

    Note: Make sure you have a backup of your data before proceeding.

    1. Make sure all batches have been deleted out of Inventory Batch Entry(Transactions - Inventory - Batches) and Invoice Batch Entry (Transactions - Sales - Invoicing Batches) . NOTE: If using Sales Order Processing, Invoicing, or Purchase Order Processing make sure that unposted transactions do not exist.

    2. If running on C-tree or Pervasive, delete the following tables from the Inventory directory: IV00102.* (Item Quantity Master) IV00200.* (Item Serial Number Master) IV00300.* (Item Lot Number Master) IV10200.* (Purchase Receipts File) All files from IV30100.* through IV30600.* (Transaction History Files)

    If running on SQL server, you may run a delete statement within the Company database on the tables noted above.

    3. Perform the Reconcile Inventory Quantities procedure (Utilities - Inventory - Reconcile) for all inventory items.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans