Epicor Job Material Shortage SQL Query Generator

-- 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;

-- 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;