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.
cumsubfallback() function takes 5 vectors belonging to the same table
G.Item: the item identifier, all lines that share the same value belong to the same item;
G.Stock: the initial stock for the item, all lines that belong to the same item must have the same Grid.Stock value;
G.Fallback: the fallback item with an associated fallback stock in case we run out of the initial stock. This parameter should be equal to Grid.Item in case there is no fallback item;
G.Quantity: the quantity of the item required for the purchase of the grid line;
G.Rank: a bundle identifier, all lines that share the same bundle identifier belong to the same bundle. Again, it is forbidden to have two lines with the same
(Grid.Item, Grid.Rank)pair and all bundles are ordered by increasing rank.
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.:
- Bundle management in purchase and dispatch lists;
- Fallback stock management in dispatch lists;
- Identification of open orders covered by current stock on hand.
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”:
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 = cumsubfallback(Id, Stock, Fallback, Orders.OrderedQty, Orders.OrderIdRank) Orders.FallbackStockConsumed = if 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 HasFallback then max(Orders.StockConsumed, 0) else 0) by Id sort Orders.OrderIdRank Orders.Stock = (if HasFallback then 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 HasFallback then Orders.FallbackStockConsumed else 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" if HasFallback then "Yes" else "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" if Orders.IsCovered then "Yes" else "No" as "Covered?" order by Orders.OrderIdRank