Friday, November 11, 2011

Reconciling Inventory, what really happens?

I’ve seen this question come up a couple of times lately, even here at the GPUG 2011 Summit. I have some good information about the details of the inventory reconciliation process so I thought it might help to pass it along to everyone.

The Inventory files involved in the reconcile are:
  • Purchase Receipts (IV10200)
  • Purchase Receipts Detail (IV10201)
  • Item Master (IV00101)
  • Item Serial Number Master (IV00200)
  • Item Quantity Master (IV00102)
  • Item Lot Number Master (IV00300)
  • Item Vendor Master (IV00103)
  • Inventory Transaction Amounts Work (IV10001)
  • Inventory Serial and Lot Numbers Work (IV10002)
  • Item Site Bin Master (IV00112)
  • Inventory Transaction Bin Quantities Work (IV10003)
  • Inventory Transaction Bin Quantities History (IV30302)

Additional files outside of Inventory are involved in the reconcile if modules Purchase Order Processing, Sales Order Processing, or Invoicing are loaded.

The files outside of inventory that may be involved are as follows:
  • Purchase Order Line (POP10110)
  • Purchasing Receipt Line Quantities (POP10500)
  • Sales Transaction Amounts Work (SOP10200)
  • Sales Serial/Lot Work and History (SOP10201)
  • Invoicing Transaction Amounts Work (IVC10101)
  • Invoicing Serial and Lot Number Work (IVC10102)
The Reconcile process goes like this:  
  1. The quantities are reconciled first between the Purchase Receipts and the Item Serial Number Master file.
  2. Then the Purchase Receipts and Item Lot Number Master files are reconciled.
  3. At this point, the quantities are reconciled between the Purchase Receipts file and Item Quantity Master file
  4. Reallocating of Inventory Transaction Amounts Work, Inventory Serial and Lot Numbers Work, Sales Transaction Amounts Work, Sales Serial/Lot Work and History, Invoicing Transaction Amounts Work and Invoicing Serial and Lot Number Work tables is done next.
  5. The On Order quantities are determined for the Item Quantity Master and Item Vendor Master tables by comparing them to the Purchase Order Line and Purchasing Receipt Line Quantities tables.
  6. The last step in the reconcile is to reconcile the quantities on each of the items’ individual quantity location records to the total quantity record for that same item.
This information is avaialble in Article ID: 874220.

I hope this is helpful!

Until next post!

Leslie Vail

8 comments:

Beat BUCHER said...

Hi Leslie,
Great topic post... It's a quick and dirty explanation in 6 points in what's happening behind the scene in the IV reconcile. Will definitely use this as reference for my GP users :-)
Have a great day.
Beat

Dynamics Confessor said...

Thanks Beat! I'm glad you can make use of it!

Leslie

0s0-Pa said...

I agree- it really is helpful for an inventory system and simple to follow with all the steps laid out.

Sheilajross said...

Leslie,

I have had more then one customer have problems with running the inventory reconcile.

After reconcile they find that their on order quantities are way out of balance on some items. There are other problems as well but we cannot find a pattern yet.

An example - they knew that they had ordered 200 qty of one item and the on order qty was set to 0 but if you drillback to the detail it will actually show the PO's with the qty's ordered.

We are wondering if it has anything to do with using multi bins in inventory. Have you heard of such a thing?

Rob K. says he has had the same kind of issues at his company.

Thanks,
Sheila

Bill Foreman said...

Hi Leslie,

Have you heard of anyone scheduling this routine to be run automatically after hours on a periodic basis (weekly)?

Thanks...Bill

Dynamics Confessor said...

Hi Bill,

Yes, there are a few ways you can run this unassisted. Probably the easiest is to use a batch file that will log in and call a macro. The macro will run the routine and then log out.

By easiest I mean that no other software is needed.

Leslie

kmmksk said...

Hi Leslie... I have a manufacturing module installed, and we have situations when we get X amount of LotA then Y amount of LotaA and it creates 2 lines in the lot/location master... i in my infinite wisdom combined the lots on to the first entry then deleted the second entry... (this was because our warehouse employees using a barcode scanner would only scan the lot# once... and therefore only ship the amount Y in the system)

all was perfect... then upon a inventory reconcile, these deleted lines were recreated with a generic lot number... what is happening during the reconcile that dynamics is recreating these quantities... for the deleted line, but not changing the added quantities to lines remaining and finding they are also out of balance... so I can change the process to satisfy quantities for the reconcile...

Dynamics Confessor said...

Kmmksk,

How did you go about deleting the second entry? Also, did you run the reconcile using both the PSTL tool and the Inventory routine? Back up first any time you reconcile.

Leslie