Job Material vs Inventory Dashboard

Can someone point me in the right direction, we are looking for the following:

A dashboard that displays all jobs (oldest to newest) and goes down the list reporting if there is enough material to fill what jobs. For instance:

Job 1 requires 1 qty of part one
job 2 requires 3 qty of part one
job 3 requires 2 qty of part one
job 4 requires 1 qty of part one

We only have 5 part ones in stock so it would read okay on the first two line but not the 2nd two lines. We do use fulfillment workbench but ops is spending a lot of time trying to figure out how much inventory they have to completed x amount of jobs. Below is the exact request from ops. I am going to guess we cannot be the first company to do this, but i cannot find anything online that gets me close to the end goal.

  1. Output with a column indicating “Ok” for all Materials are available vs “Short” for at least 1 Material is missing or not in sufficient supply.

  2. Potentially look at the Fulfillment Workbench table data compared against the JobMtl table to see what materials are allocated/reserved vs what has not been allocated/reserved.

  3. If [#2] is not doable, then look at OnHand, available (Not Non-Nettable) inventory and do a running subtotal of requirements (oldest Job first) and indicate at which Job Number the Material runs out.

  4. Desired output would be a list of items which are not in sufficient supply (Req Qty < Allocated/Reserved Qty), grouped by Job Number. If all Job Materials are available, then a single row with a status of “Ok”.

You could use a running total type column of how much is needed (including the current row) and compare that to how much is on hand and get your OK or Short from that. Syntax would be similar to this:

sum(JobMtl.RequiredQty) over (partition by JobMtl.PartNum order by JobHead.JobNum)

That would give you the running total which you then could compare

1 Like

This thread and links might help.

Time Phase Inquiry BAQ - ERP 10 - Epicor User Help Forum (

1 Like