Microsoft Dynamics GP (Archived)

Clearing Inventory Items balances

(0) Share

Report

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

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

Richard Wheeler 75,848 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

David V 270 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Noel Simela 385 on at

Like (0)

Report

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.

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

Like (0)

Report

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.

Was this reply helpful? Yes No

Community site session details

Clearing Inventory Items balances

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates

Community site session details

Clearing Inventory Items balances

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates