SQL Sever

Inventory Control Operations and Transactions

While writing the prior posts about the 10 Second Rule in inventory control, I started thinking about our end-users and the forms they use to operate our software. Imagine that we could gather every inventory control system ever written by DataWorks (and while we are at it let's toss in every AR, AP, GL, Payroll or ERP application ever programed by any software company) into a huge vat of creamy digital goodness. Then using a yet-to-be-invented silicon-spatula, we would pry the user interfaces from these systems into a giant pile of one-dimensional pelts.

Next we would rake these skinned bits into the intake scoop of a yet-to-be-invented silicon-shaking machine. After clicking the OK button, we would adjust the lumbar support of  our Herman Miller Aeron chair (mine's black),  and watch as the mythical machine shakes and sifts the leafy bits though various mesh screens. Eventually all the input will be sorted into five unequal mounds of output. In order of their height and weight the piles would be grouped like this (you may not like the names of the piles, but this is my made-up machine, so these are my made-up names) :

  1. Confirm & Comfort - Little crumbs of programing chaff  that ask yes-no questions,  tell the user that something is happening (Hour Glass or Thermometer Bar), or will happen - once they click the  "OK" button.  These forms generate comfort to the end user - they let you know the software is working away on something, they ask a question to make sure you really want to change all the prices of the beanie babies to 19 cents. They add some flavor to the system, but they don't really do much. They are the cranberry laced croutons of a much bigger salad.
  2. Configure & Forget- rarely used after initial setup. Super simple to program. If you have seen one you have seen them all. (i.e. System Defaults, Colors, Sizes, Units of Measure, Classes, Departments, Margin Plans, Ticket Type, Currencies, Languages, Addresses).
  3. Daily Maintenance - highly specialized forms that handle the heavy daily lifting of inventory. Lots of code.  Speed and flexibility are important.  (i.e Product Input, SKU definition, Cost Updates,  Price Changes.)
  4. Show & Tell - retrieves and organizes data for your viewing pleasure. This group includes screens used to select date ranges for running reports  and forms used for looking up a particular data set (i.e.  Product look up, Sales Audit review, Comparative Sales Reports,  Best - Worst Analysis,  In-Transit report,  Inventory shrink, General Ledger Batch)
  5. Transactional - an elite group or  highly trained, highly specialized forms,  used to record an inventory control action. These forms are the work horses within any inventory control system.  (i.e. Purchase Order, Receipt, Transfer, Markdown, Inventory Adjustment, Return to Vendor).  Lots of Code.  Lots of business logic.

Transactional forms are the core of any inventory control operations system and the focus of this post.   If an action is being recorded that changes an inventory item's on-hand or on-order value (quantity, cost and/or retail) these are the forms that are used.

A characteristic of a transactional form is that it typically has two sections:  a header section and a detail section.  The header is one row of data that captures at the very least the who and the when of the transaction.  Who = The employee who preformed the action. When = The date and time that the transaction occurred. The Detail section contains the "What". What SKUs are being received. What products are being returned to the vendor. What items are being transferred.

We create and maintain a database application that in at it's genetic core  is an accounting system with a very thick veneer of operational epidermis.   Now this may surprise you but here is is what bounces against our head-bones when we are creating or enhancing our inventory control application:

  1. First and foremost is this question: What data do we need?
  2. The next question is:  Is this data related to any existing data?
  3. Thirdly - and this is where business logic, programing, and all the accounting bits come into focus -  if this data changes will it effect any other data?

In a transactional form, the tricky bit about all this accounting and business logic is that there is not just one piece of data moving from a 0 to a 1, (or from a 1 to a 0) but more likely there are hundreds or thousands of data bits queued up that need do their own debit to a credit dance too -- and if one bit changes then all the bits need to change.  It is an all or nothing process.  If you start the transaction the software needs to be able to finish what you have kicked off.  Even - and this is very-very tricky part - if the computer power supply dies in the middle of all the fun, or if the SQL Server connection drops offline, or some other random act of chaos says, "Hello, nice to met you" to our finely crafted inventory control software - the application needs to be able to pick up where it was unceremoniously dropped off and finished the transaction.

So with that said, the receiving transaction generates a lot of exciting data changes (increment the on-hand, and decrement the on-order), and the transaction has to leave some cookie crumbs along the way so if the process dies, Hansel the handy programmer, can code in a method to find a way back to where chaos stepped in and then finish the march to grandma's house.

This is one of those areas that for those who know a little about this industry, but never actually had to write a application (read "Consultant") would rattle off something like, "Hey why are you complaining? Any SQL Server engine worth it's license fee has transactional  roll back, so that if the transaction does not commit you can just let the SQL Server clean up the mess.

Two things that the consultant may not have real-world experience with:

  1. Transactional buffering wins the slow-on-the-go award.  If there are one or two records to update, its not a problem. You got a customer address to update - not a problem.  But take 10,000 inventory items that have just been counted in a physical inventory and they now need to be marched out to the data play ground en masse and updated as one big happy collective body of 0's and 1's -- you got yourself a real programming puzzle.   All the tools that Microsoft, Oracle and Sybase offer us for transactional buffering and saving will have us out playing though recess, and will likely have us out there after school. We know because in version 1 of NeXT®, we used transactional buffering. Time to save a physical with 10,000 updates - more than an hour (sometimes MUCH longer). Now - after rewriting the upload and update routines by adding our own "state-of-the-transaction" bread crumbs and eliminating the use of off-the-shelf  buffering and transactional roll back - less than  3 minutes.
  2. If a transaction can launch another transaction (Receiving product into a Warehouse, generates  a cross dock Transfer Out,  and based on options the Transfer Out automatically creates a Transfer In) you have a an ever bigger and tougher transaction mess to code for and handle. Each transaction launches another transaction. What if the last part of the Transfer-In fails? Well then you got to roll back all the other transactions from a completed state to a pending or in process state.

(By the way, I like consultants - they occasionally introduce our products to clients. And who knows, when I grow up,  I may want to be a consultant someday.)

Another characteristic of the transactional  form is that they  are "The Event" or they are "The Record" of a real event.

If they are "The Record",  then these digital events are tied to actual pieces of paper that are the source documents. They in essence mimic a document that was created by hand or generated by another computer system. Think about going to a merchandise mart in  Atlanta, Dallas, LA or (my favorite - because I use to give Open to Buy lectures there) Miami, and placing a hand written PO with a sales rep. That hand written PO is the source document. When you get back to the hotel or back to the office, you pull those hand written docs out of your attache case and hand them to your assistant - Jill or Jack - and they go up the hill and type them into the system.

If the system's transactional form is  "The Event" then the system creates the source documents. Transfers from a warehouse to store, or transfers from store to store are good examples of that. They produce a document that lists what SKUs and how many have been scanned in

Their main purpose is to update the data that is read-by-end-user, write-by-system-only using the business logic that is embedded in the system's source code. We don't want users to just change an item's inventory on hand value without recording what caused the change. (By the way if you do want to change an on-hand value without any business logic, just use a spreadsheet, there is  no need to buy our database application or any other business application for that matter.)

If it was not for the business logic (and the never ending, feature bloating, endless stream of exceptions to the business logic), creating an inventory control application would be a pretty simple matter;  DataWorks  would not have spent 24 years working on it; I probably would not have this job; and there would be no need for me to be typing this blog.

Thanks for the opportunity.