Revisiting Inventory Status

I'm in the process of revisiting both the inventory and the financial sub-systems in PubAssist. I'm moving these sub-systems to the new Version 5 architecture. This move should make these subsystems available for automation from other applications through COM. While doing this, there are a number of minor but nagging issues that are worth revisiting as long as I am making the change. One of those issues is how to derive the inventory status as of a given item.

The inventory system is a historical accounting system, very much like the general ledger. Inventory status is derived by summing a selected set of historical transactions. Each inventory transaction is associated with a specific ordered item. Each item has it's own unique item number. There may be multiple inventory transactions for any given item, but there is only one item associated with any given inventory transaction.

There are a few situations where understanding the inventory status as of a given item is important. A couple of examples are:

We have expended a great deal of energy to ensure that users can modify any transaction in Publishers' Assistant.

That means that a user can go back and change an invoice from a year ago. So, it's possible that an item from an invoice will be deleted. When that happens, the inventory transactions and the financial transactions that are associated with that item are deleted as well. The user may re-enter the item, or enter a new item for the same invoice a year after the fact. The new item that is entered will have a new item number which is quite a bit out of sequence with the other items in the order.

So, now it's time to re-ship the invoice. Is there enough inventory at the time of that shipment to accommodate that new item? Moreover, what impact will the new item have on other items that were shipped on the same date? In order to determine if there is sufficient inventory, we'll want an inventory status as of the item being shipped. Put another way, we want to capture all of the inventory transactions for items that were shipped or received prior to the item we are shipping.

In a perfect world, this would be a simple matter of grabbing all inventory transactions for the title with an item number less than the item in question. But, of course this is not a perfect world. We made it less so when we allowed users to modify invoices long after they were originally logged. So, while it may be rare, there are likely to be at least a few items that are out of their original sequence.

So to get an inventory status, the question becomes, "What constitutes an earlier item?" Well, we can still grab all inventory transactions for the title with item numbers less than our item in question. We'll also want to grab inventory transactions with higher item numbers that are logged for dates that are prior to the shipment date.

What we may still miss are items that were modified (and therefore have a higher item number) that were shipped on the same date as our item in question. This is a very small set. It is now a business question whether to consider these items as previously shipped or not. For this implemention, I've made the decision that items with a higher item number that are shipped on the same date are NOT considered when determining the status as of a given item.

For those of you who wonder how royalty accounts may change, or how your inventory can wind up dipping below zero at some point in time, this discussion should give you a clue.