What Causes Over Allocation and Excess Qty to Become Negative?

What Causes Over Allocation and Excess Qty to Become Negative?

Release Date: 01/19/2021
Version: 7.x & Up

Q - In Global Setup -> Cop-Func -> Order Line Item Screen -> 12. Allow Qty-To-Ship > Excess Qty (OH-Alloc+BO)? the value is set to "N." See sample screen below:


This should prevent my item's Excess Qty to become negative. So why are some of item's Excess Qtys negative? See sample screen below:

How do I go about finding the cause of the negative Excess Qty?

A - This one is tricky, but here is the principle behind this. The first thing you have to understand is that, by design, Elliott allows over allocation.  The purpose of the Global Setup flag was originally for distributors who do not wish to take back orders. It only works in Elliott's Order Entry and Sales Desk. There are many processes in Elliott that do not pay attention to flag 12 in the Global Setup above. So there are many areas in Elliott that can cause over allocation. The following is an example that can be easily explained:

Example: Inventory Transfer

In Elliott, if you try to transfer inventory from one location to another, you could cause the Excess Qty in certain locations to go negative. The following is an example:


In the above example, you can see the negative 13 Excess Qty is clearly caused by the transfer of 13 pieces from location 10 to CS. So it is likely the Excess Qty was originally zero in location 01. Later on, the user chose to transfer 13 pieces from 01 to the CS location. Since inventory transfer is not subject to the above Global Setup flag 12 control, this can happen.

Other Common Examples

Sales Order Import - In the Sales Order Import ASCII file, the data contains both line item quantity order and quantity to ship. Users can specify that the quantity to ship be the same as the quantity order. Since Sales Order is a batch process, not interactive, we can't stop the order from importing, and that can cause the Excess Qty to become negative.
Web Services - If you use Elliott web services to create sales orders, it will not pay attention to this Global Setup flag. Generally speaking, whether or not to allow over allocation is a decision for the web application to make. If the web application chooses to over allocate and causes the Excess Qty to become negative, web services will not stop it.
Allocation as a Component - If this item is a component of another parent item (Kit or BOMP), then there's no stopping of over allocation when the order is taken for the parent item.

Investigating of Over Allocation Issues

If over allocation is causing you problems and you'd like to address it, the first you need to find out is what is the cause for the over allocation. But since Elliott allows over allocation, how do you find out this?  We suggest that you use the following procedure:
  1. Periodically run the following SQL statement: Select count(*) from IMITMFIL where (item_qty_on_hand - item_qty_alloc + item_qty_bo) < 0;
  2. When running SQL statement of 1, also run the following SQL statement:  select item_no, (item_qty_on_hand - item_qty_alloc + item_qty_bo) as Excess, item_qty_on_hand, item_qty_alloc, item_qty_bo from IMITMFIL where (item_qty_on_hand - item_qty_alloc + item_qty_bo) < 0;
  3. The frequency to run the above two SQL statements is up to you. The key is you need to run it frequently enough so you can narrow this down.
  4. Say you run these two SQL statements once a day. If you run #1 above and the result is 29, and you run #1 above the next day and the result is 30, then you know something in between yesterday and today is causing another item to over allocate.
  5. Compare the two from yesterday and the two from today, and find the new item that joined the list.
Hopefully, the above method will allow you to narrow this down. You need to find out the cause to address the issue. For example, if this is from the web, then you have to figure out whether you did proper checking on the web to prevent the over allocation.

Another factor to consider is Audit Master. Ideally, if you have that installed, it would facilitate the investigation of “5” above.


EMK


    • Related Articles

    • Feature - BOMP Material WO Negative Qty Per Components

      Release date: 3/31/2021 Version 8.5 & Up In BOMP Material Work Order, if there is a negative qty per component, it is treated as a byproduct which will increase the Qty OH after the production of the parent item is complete. But before production is ...

    • Feature - Mass Billing By CSV Auto Select Negative Qty Ordered

      Release Date: 9/5/24 Version: 8.5 and Above A new Global Setup value has been added to COP-Func-> Billing that allows the user to specify if non-serial items with a negative quantity ordered will be automatically selected for billing if not provided ...

    • Feature - Change QTYAVAILBYCOMP “Stock Qty Avail” Calculation Logic

      Release date: 2/4/2021 The current calculation logic for the QTYAVAILBYCOMP attribute field "Stock Qty Avail" is: Qty On Hand - Qty Allocated + Qty Back Ordered This is the same definition as Excess Qty, which means "Short Term Qty Available." It ...

    • What Causes Load Error 198?

      Modified Date: 6/7/2019 Q - One of our computers received an error 198 load error randomly. The message looked like the following sample screen: Load error: file "SYCONSEL" error code: 198, pc=0, call=15, seq=0 or Load error: file "SYACTCHK" error ...

    • Feature - Allow Over Allocation for Certain Locations and Users

      Release Date: 2/27/24 Version: 8.5 and Above A new option is available in Global Setup-> COP-Func-> Order Line Item Screen-> 12. Allow Qty-To-Ship > Excess Qty (OH-Alloc+BO) that allows over allocation to occur for specific locations for specific ...