Recalculating Inventory in a Counterpoint Multi-site Environment
If you are using Counterpoint in a multi-site environment, care must be taken when it comes to running the Inventory Recalculate Quantities procedure. This procedure checks several of the quantity fields in the inventory tables, against the data in the rest of the tables. Fields such as quantity-committed, quantity-on-po, and quantity-on-transfers, among others, are checked and corrected if necessary. Note, that it will not adjust quantity-on-hand. That must be done via inventory adjustments.
The issue in a multi-site environment relates to how replication works. When a replication session starts with a remote site, it does not take a snapshot of the database. Rather, it goes through the list of tables and makes the appropriate updates as it processes the various tables. What this means as to the inventory quantity recalculations, is that after finishing replication, all of the data is not consistent as to the point in time that it represents, due to ongoing user activity.
To illustrate, let us look at the purchasing process and inventory quantities. The purchasing tables are processed by replication in the first half of the list of tables, while the inventory tables themselves are processed nearly at the end. This process leads to the possibility that a replication session will start, and get through the purchasing tables. Then while replication processes other tables, a user at the remote site posts a new purchase order. A bit later, the replication processes the inventory tables. In this case, after the replication is finished, the hub site has no records of the purchase order that was posted at the remote site. However, it does have updated inventory records showing a quantity-on-po. In the Inventory Recalculate Quantities is run at the hub at that point, the result would be to set the quantity-on-po to zero for all items on the purchase order that was posted (assuming that they are not on any other purchase orders). When replication runs again to the remote site, the purchase order will then be transferred to the hub. Also, the quantity-on-po that was set to zero at the hub will be sent to the remote. Now both the remote and the hub have an open purchase order, while the quantity-on-po for all of the items on that purchase order will be zero.
Inventory quantities should only be recalculated after replicating with the remote sites when no activity is occurring at those sites for the entire duration of the replication, avoiding this timing problem. Typically, this means that the recalculation must be run well after the remote site closes, or before they begin processing in the morning. Remember, it is not enough that the remote site has closed, and everyone has gone home. A replication must occur after they have closed and gone home. Only then will the recalculation routine have data that truly reflects the state of the data at the remote site. Most of the time, this means that the best time to run the recalculation is in the morning before users at the remote site start doing anything with Counterpoint.
Dave.