Stock Out BAQ

Does anyone have a BAQ that calculates how many times you have stocked out by PartNum?

I think that a BAQ to do that with data already in the system is going to be really difficult. Technically, you can do with the part tran table. You would have to calculate running totals and that can be hard when trying to look at all of your part numbers.

If if were me, I would make something forward looking in which I would write a log to a UD table when on-hand balance is 0. I would also figure out how to make sure it only records the part numbers that you need it to, since that could grow out of control pretty quickly. Some clever conditions in your BPM, or a UD field on the part master to signal when to record would be helpful.

edit: or maybe add a UD field to the part tran table that records the On Hand balance at the time of the transaction. This might add a lot of overhead though.

1 Like

as @Banderson already said, it is not easily available… but I may have come up with a trick… it wont be 100% accurate, but may be “close enough”.
Create a BAQ that looks at PartTran… In that table there are two fields typically used for COSTING called PartTran.BeginQty and PartTran.AfterQty. These fields are not populated with every transaction… only those transactions that would change the cost (for average costing). this means that when ISSUING, the cost does not change, but when receiving materials, the cost could change… (ok… maybe I gave too much detail here)…
Anyway… you would not be able to find out how many times you “stocked out”… BUT with this data, you CAN find out how many times you REFILLED inventory when you had nothing in stock …
To prove this i made a simple query that has the following filters:

  • PartTran.AfterQty > PartTran.BeginQty //finds all receipts that changed inventory
  • PartTran.TranQty <> 0 //This hides many of the non-inventory transactions
  • PartTran.TranType<>‘ADJ-CST’ //this hides the simple cost adjustment transactions

But again… I am with @Banderson… to truly capture this, you need to log the event… and even then… do you log it when the WAREHOUSE is zero? or the SITE is zero (that would be trickier to find).


Your last reply got me thinking… Could’nt you use the PartBin table add a CnTZero_c ud field and via a data directive, whenever the onHand quantity <= 0, add 1 to the count?
A dashboard could sum up by Part, part/warehouse etc…
I think it would be faster than going into PartTran table…


Part bin records don’t exist when the part reaches 0.

Bummer your are right…it got out of my mind…

man it was a too easy option…:wink:
tks reminding me…

1 Like

You might want to check out PlanningEdge.

They are a demand modeling and forecasting tool that allows you to drill into the distribution data in much more detail. They integrate with Epicor and are now a SAAS platform.

Purchasing Workbench should tell you everything you need to know, or not?

PO Workbench would tell you everything only if all items are purchased… but you can have stockouts on Make and Transfer items as well.

Sorry everyone, been chaotic lately. I’m going to look at all of the suggestions and then I’ll report back what I find. Thank you everyone for your suggestions.

I visited the SMART Software booth at the last Insights. Do you have any experience with that software? Just trying to figure out the differences between SMART and PlanningEdge.

I have only seen their demo’s as well.