EpicorFree generatorPDF, Excel, Word exportEpicor KineticDiscrete ManufacturingAll Epicor ERP (Kinetic, 10, 9, Vantage) tools

Epicor Job Material Shortage SQL Query Generator

Planners need to know which released jobs are waiting on material. The JobMtl table holds required and issued quantity per material line, so a short query against JobHead and JobMtl answers most shortage questions. This generator fills in your company and plant.

Fill in

Company id

Plant

Leave as your plant id

Look-ahead days for start date

Minimum shortage quantity

Generated code

Free, optional. Reopen it on any device.

Download:

Job material shortage query

-- Open job materials not fully issued, released jobs only
SELECT jh.JobNum,
       jh.PartNum AS JobPart,
       jm.AssemblySeq,
       jm.MtlSeq,
       jm.PartNum AS MtlPart,
       jm.Description,
       jm.RequiredQty,
       jm.IssuedQty,
       (jm.RequiredQty - jm.IssuedQty) AS ShortQty,
       jm.IssuedComplete,
       jm.BuyIt,
       jh.StartDate
FROM Erp.JobHead jh
INNER JOIN Erp.JobMtl jm
        ON jm.Company = jh.Company
       AND jm.JobNum = jh.JobNum
WHERE jh.Company = 'EPIC06'
  AND jh.Plant = 'MfgSys'
  AND jh.JobReleased = 1
  AND jh.JobClosed = 0
  AND jh.JobComplete = 0
  AND jm.IssuedComplete = 0
  AND (jm.RequiredQty - jm.IssuedQty) >= 1
  AND jh.StartDate <= DATEADD(day, 14, GETDATE())
ORDER BY jh.StartDate, jh.JobNum, jm.AssemblySeq, jm.MtlSeq;

Add on hand for each material part

-- Optional: join total on hand by part for the same company
SELECT jm.PartNum AS MtlPart,
       SUM(pb.OnhandQty) AS OnHand
FROM Erp.JobMtl jm
LEFT JOIN Erp.PartBin pb
       ON pb.Company = jm.Company
      AND pb.PartNum = jm.PartNum
WHERE jm.Company = 'EPIC06'
  AND jm.IssuedComplete = 0
GROUP BY jm.PartNum;

Run on a read replica or inside a BAQ, never on a production database during peak hours without a DBA's agreement. Confirm column names against the Data Dictionary Viewer for your release. Plant columns can differ on JobHead depending on version, and a job can have buy-it lines that show a shortage until a purchase receipt arrives.

Shortage reporting review

Optional: we can look at how your planners find shortages today and suggest a read-only assistant.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use the generator

Enter your company id, plant, a look-ahead window for the job start date and the smallest shortage you care about. Copy the query into SQL Server Management Studio on a replica, or recreate the same joins and filters in the BAQ designer. The second block adds total on hand per material part so you can see whether the shortage is a stock-out or just not yet issued.

What the query tells you

A line is short when RequiredQty is greater than IssuedQty and IssuedComplete is not set. Sorting by job start date puts the most urgent shortages first. If on hand is above the shortage, the fix is an issue transaction or a pick, not procurement. If on hand is zero, check purchase order releases due before the job start and whether the line is flagged BuyIt, meaning it is purchased directly for the job.

Short with stock available: issue or pick material
Short with no stock: expedite the purchase order
BuyIt lines: check the PO release against job start

Common mistakes

Forgetting the JobReleased filter pulls in firm planned jobs that are not yet on the floor, creating noise. Ignoring IssuedComplete shows lines the shop has finished with a short issue. Joining PartBin without limiting to the right warehouses overstates availability when stock sits in a non-nettable warehouse. Add a warehouse filter for the warehouses MRP nets in your company.

Frequently Asked Questions

Can I use this inside the BAQ designer?

Yes. Add JobHead and JobMtl as tables, create the join on Company and JobNum, and put the filters in the criteria. The calculated shortage is an expression field equal to RequiredQty minus IssuedQty.

Why is a line shown as short when it is already purchased?

The query reports material not yet issued. A purchased item sitting on an open PO release still counts as short until it is received and issued. Add a PO release lookup if you want to show expected arrival.

Does the query respect Epicor security?

Direct SQL does not. Security groups and company restrictions apply only inside Epicor, such as in a BAQ. Use a BAQ for anything given to end users.

Will column names differ by version?

Core columns like RequiredQty, IssuedQty and IssuedComplete have been stable, but always validate against your Data Dictionary Viewer, especially for plant and status flags.

Netray can turn queries like this into a conversational shortage assistant on your Epicor data, read-only, with the SQL shown for every answer.

Talk to an Expert Explore More Free Tools