cumsub.fallback

cumsub.fallback(..), process

The cumsub.fallback() function explores all bundles by increasing rank, keeping track of the stock consumed for each item. Every bundle attempts to subtract the corresponding quantity from the remaining stock of the corresponding item. If insufficient, additional stock is taken from the fallback item.

The cumsub.fallback() function takes 5 vectors belonging to the same table G with:

If the bundle succeeds, the function returns for each bundle line the quantity of stock taken from the item’s stock. The quantity taken from the fallback item can be deduced by calculating Grid.Stock - Grid.Quantity. In the specific example where there is no remaining stock on the item to serve the bundle but enough on the fallback item, the function returns 0. Then, the quantity taken from the fallback item is exactly Grid.Quantity. If the bundle fails, the function returns -1 for each bundle line. Note that each fallback item must have an associated line in the grid, otherwise, its fallback stock will not be considered. If none, fake grid lines must be created with missing fallback items.

There are various possible applications of the cumsub.fallback() function, e.g.:

The last use case, namely, Identification of open orders covered by current stock on hand, is being picked up and presented below. It displays a specific example of a customer willing to identify which open orders are currently entirely covered by the stock of his main warehouse (all references inside the order must be covered by the main warehouse stock). Otherwise, those orders would be served by other locations. There is an additional constraint which allows every reference to have a fallback stock in case their own stock would not be sufficient to cover the demand. In this example, two items are considered, A and B. A is the fallback of B. There are five orders ranked by order date that are explored by increasing rank (one by one). If there is enough stock to cover all references of the first order, the whole order is considered covered: the stock of all the associated references is being reduced. If there is still a need, the stock of their fallback references is being decreased as well. The next order is being explored. The same logic applies.

In the table entitled: “Results: cumsub.fallback”: Image

two fields that represent the stock levels of a reference A and B require particular attention. The stock of A needs to be reduced even when the stock of B is required. As a rule of thumb, the stock of a reference with a fallback will be displayed as “Own Stock”, while the stock of a reference without any fallback will be displayed as “Fallback Stock”.

In the selected example, the first order requires 8 units of A, whereas the initial stock contains 10 units. Thus, this order can be easily served. The stock of A will be reduced to 2 units (see “Fallback Stock”). The second order requires 3 units of A, however, only 2 units are left. This order cannot be served. The “Own Consumption” is assigned a -1 consumption, and the stock of A remains the same. The third order requires 5 units of B, and B has 4 units of initial stock, and A has 2 remaining units: this order can be served. We reduce the stock of B which drops to 0 (see “Own Stock”), while the stock of A falls down to 1 unit (see “Fallback Stock”). At last, entirely covered orders are identified as those with either an own stock consumption or a fallback stock consumption strictly positive for every reference of those orders.

The following Envision code covers the selected use case:

read "/sample/Lokad_Items.tsv" as Items with
  Id : text
  Fallback : text
  Stock : number

read "/sample/Lokad_Orders.tsv" as Orders expect [Id, Date] with
  Id : text
  OrderId : text
  Date : date
  OrderedQty : number

Orders.OrderIdRank = -1
where argfirst() by Orders.OrderId sort Orders.1
  Orders.OrderIdRank = rank() sort parsenumber(Orders.OrderId)
Orders.OrderIdRank = same(Orders.OrderIdRank) if(Orders.OrderIdRank != -1) by Orders.OrderId

//Cumsub fallback
//Variables initialization
Orders.Fallback = same(Fallback) by Id at Orders.Id
HasFallback = Id != Fallback

///Orders coverage calculation
Orders.StockConsumed = cumsub.fallback(Id, Stock, Fallback, Orders.OrderedQty, Orders.OrderIdRank)
Orders.FallbackStockConsumed = HasFallback and Orders.StockConsumed >= 0 ? Orders.OrderedQty - Orders.StockConsumed : 0
Orders.IsCovered = all(Orders.StockConsumed > 0 or Orders.FallbackStockConsumed > 0) by Orders.OrderId

//Reporting
//In Orders.Stock, we keep track of the stock of all references with a fallback item
Orders.CumStockConsumed = cumsum(HasFallback ? max(Orders.StockConsumed, 0) : 0) by Id sort Orders.OrderIdRank
Orders.Stock = (HasFallback ? Stock : 0) - Orders.CumStockConsumed

//In Orders.FallbackStock, we keep track of the stock of all references without a fallback item
Orders.CumFallbackStockConsumed = cumsum(HasFallback ? Orders.FallbackStockConsumed : max(Orders.StockConsumed, 0)) by Fallback sort Orders.OrderIdRank
Orders.FallbackStock = same(Stock) by Id at Orders.Fallback
Orders.FallbackStock = Orders.FallbackStock - Orders.CumFallbackStockConsumed

show table "Cumsub fallback" e3h3 with
  Orders.OrderIdRank as "OrderId Rank"
  Orders.OrderId as "OrderId"
  Id as "Ref"
  Fallback as "Fallback"
  HasFallback ? "Yes" : "No" as "Fallback Item?"
  Stock as "Own Initial Stock"
  Orders.OrderedQty as "Ordered Qty"
  Orders.StockConsumed as "Own Consumption"
  Orders.FallbackStockConsumed as "Fallback Consumption"
  Orders.Stock as "Own Ctock"
  Orders.FallbackStock as "Fallback Ctock"
  Orders.IsCovered ? "Yes" : "No" as "Covered?"
  order by Orders.OrderIdRank