Case Study

Creating an Auto Allocation Process for Service Orders

  • Dashboard displays orders with item quantity
  • Auto allocate item from dashboard
  • Override system allocation setting 

Projects which touched the General Ledger were not previously considered an option.  It was too risky.

Hearing customers say they were told “it can’t be done,” only provoked the motive to pull it off.  We knew we could do it and do it right.

P21 auto allocations not only save a ton of time but can drastically reduce end user error.  However, many companies find them difficult to streamline. Other companies find manual allocation time consuming, when doing things such as taking a customer off of credit hold.

We created a PHP dashboard where a user can simply press a button called “Allocate Item” and the item will allocate, without anyone entering the order.   

Not long after deploying this solution, we had a customer contact us with a similar problem.  They had a General Ledger issue where the balance was off between branches.  Previously, this would have been a very lengthy and messy fix.  We were able to take what we acquired with the auto allocation stored procedure and provide a solution to them within hours.

Project Details

How Auto Allocations Work in P21

In system settings, you can choose to either manually receive or have material auto allocate.  When ‘Automatically Allocated’ is selected, the system will allocate the oldest existing backorder, first, until all open backorders are filled.  If the setting is set to ‘Receive Only’, the system will not make an allocation upon receipt.  Instead, the end user will receive the material and then allocate each item, one by one.

Challenges with Auto Allocation

Either option is organizationally specific.  With Service Orders, however, when an item comes off of H, there is no automatic allocation.  An item must be manually allocated in ‘Parts, Labor Detail Tab,’ within Service Order Entry. We had companies mention the difficulty in streamlining auto-allocations.  Other customers found it time consuming to take a customer off credit hold, then must go into the order and manually allocate the items. 

Choosing to Create a Solution

As we planned this design, we knew there were some high-risk items we really had to analyze before moving forward.  Mainly because of the number of tables and transactions allocations touch.  To get this right, you must hit the inventory transaction registrar table, perfectly.  In service, allocations also hit the general ledger, via the Inventory WIP Account. 

Auto Allocations Became a Five-Layer Stored


  1. In the Inv_Loc table, we have to reduce/increase the qty allocated B or located – BUT this cannot allow negative backorder or produce more inventory. 
  2. Allocate but 1 available it has fails it has to roll back or it will cause negative backorder. Ten lines of code to make the update. Fifty to roll it back. 
  3. Updates Service Production or Sales Order 
  4. Creates and updates two entries in General Ledger. 
  5. The sub procedures all must exist in a discreet SQL design which catches the error 

For us, it’s these types of projects which really open the door to providing a solution across multiple avenues.  When we continue to perfect the system’s boundaries, we have an opportunity to turn the “It can’t be done,” into “Sure, no problem.”

The ‘yes’ answer is motivating.

This was the project which pushed the bar on what we thought could or could not do with P21