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:

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.

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.

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