Navigation :

cumsubfallback

cumsubfallback(..), process

The cumsubfallback() 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 cumsubfallback() 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 cumsubfallback() 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: cumsubfallback”: 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 [Id] 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 scan Orders.1
  Orders.OrderIdRank = rank() scan parsenumber(Orders.OrderId)
Orders.OrderIdRank = same(Orders.OrderIdRank) 
                     when (Orders.OrderIdRank != -1) 
                     by Orders.OrderId

//Cumsub fallback
//Variables initialization
Orders.Fallback = Items.Fallback[Orders.Id]
Items.HasFallback = Id != Items.Fallback

///Orders coverage calculation
Orders.StockConsumed = cumsubfallback(Id, \ 
  Items.Stock, Items.Fallback, Orders.OrderedQty, Orders.OrderIdRank)
Orders.FallbackStockConsumed = if Items.HasFallback and Orders.StockConsumed >= 0 then 
                                 Orders.OrderedQty - Orders.StockConsumed
                               else 
                                 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(if Items.HasFallback then max(Orders.StockConsumed, 0) else 0) by Id scan Orders.OrderIdRank
Orders.Stock = (if Items.HasFallback then Items.Stock else 0) - Orders.CumStockConsumed

//In Orders.FallbackStock, we keep track of the stock of all references without a fallback item
Orders.CumFallbackStockConsumed = 
  cumsum(if Items.HasFallback then Orders.FallbackStockConsumed else max(Orders.StockConsumed, 0)) by Items.Fallback scan Orders.OrderIdRank
Orders.FallbackStock = Items.Stock[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"
  Items.Fallback as "Fallback"
  if Items.HasFallback then "Yes" else "No" as "Fallback Item?"
  Items.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"
  if Orders.IsCovered then "Yes" else "No" as "Covered?"
  order by Orders.OrderIdRank
User Contributed Notes
0 notes + add a note

© Lokad 2014 - 2023