fifo

fifo(T.onHand: number, U.purchDate: date, U.purchQty : number) 🡒 U.unitByAge : number, process

Returns the number of units that still remain from the historical purchase orders assuming a FIFO (first-in, first-out) consumption.

Example:

table P[id] = with
  [| as Id, as OnHand |]
  [| "hat", 5 |]
  [| "cap", 4 |]

table T = with
  [| as Pid, as PurchDate, as PurchQty |]
  [| "hat", date(2020, 8, 20), 6 |]
  [| "hat", date(2020, 7, 15), 3 |]
  [| "cap", date(2020, 8, 12), 1 |]
  [| "cap", date(2020, 8, 3),  1 |]
  [| "cap", date(2020, 8, 1),  5 |]

where T.Pid in id
  T.Fifo = fifo(P.OnHand, T.PurchDate, T.PurchQty)

  show table "" a1e6 with
    id
    P.OnHand
    T.PurchDate
    T.PurchQty
    T.Fifo
    order by [id, -T.PurchDate]

The fifo function could also be rewritten with a user-defined process:

table P[id] = with
  [| as Id, as OnHand |]
  [| "hat", 5 |]
  [| "cap", 4 |]

table T = with
  [| as Pid, as PurchDate, as PurchQty |]
  [| "hat", date(2020, 8, 20), 6 |]
  [| "hat", date(2020, 7, 15), 3 |]
  [| "cap", date(2020, 8, 12), 1 |]
  [| "cap", date(2020, 8, 3),  1 |]
  [| "cap", date(2020, 8, 1),  5 |]

def process myFifo(qty: number; onHand: number) with
  keep rest = onHand
  sourced = min(rest, qty)
  rest = rest - sourced
  return sourced

where T.Pid in id
  T.MyFifo = myFifo(T.PurchQty; P.OnHand) by id scan (-T.PurchDate)

  show table "" a1e6 with
    id
    P.OnHand
    T.PurchDate
    T.PurchQty
    T.MyFifo
    order by [id, -T.PurchDate]

See also