Updating Inventory Transaction Accounts

When you enter inventory transactions (Transactions-Inventory), distributions are not created until you post.  This is because, based on your valuation method, the costs involved may not be known until you post (as other activity may have been recorded that impacted the cost to be used).

So, because of this, the inventory transaction amounts work table (IV10001) simply contains the account indexes (rather than there being a separate distributions table for work transactions).  The IVIVINDX contains the account index for the inventory account, and IVIVOFIX is the account index for the inventory offset account used for inventory adjustments.

The good news in all of this is that if you need to make a change to these, it is relatively easy.  Why would you need to do this?  Well, imagine a scenario where you import your inventory balances (for go live, for example) and only after importing (and tying it all out of course) you notice that you have some items with the wrong account numbers.  So, you go and change the accounts on the item card.  And.  Bummer.  It does not update the existing unposted transactions.  But all is not lost!

You could...

--Update one of the index fields with a constant (if all transactions should have the same account number)

UPDATE IV10001 SET IVIVOFIX=57

--Update one of the index fields based on the newly assigned account for the item

UPDATE IV10001 SET IV10001.IVIVOFIX=IV00101.IVIVOFIX FROM IV10001 INNER JOIN IV00101 ON IV10001.ITEMNMBR=IV00101.ITEMNMBR

A couple of notes/warnings!

Normally, I would not be a fan of updating accounts...but these are work transactions, and the distributions themselves have not yet been created.

And, of course, please make sure you test this on a test database first and review the results.  And always make a backup prior to running against a live company.

 
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.