# Epicor Open Jobs Query Generator (Erp.JobHead SQL for BAQ and SSMS)

> Source: https://netray.co/tools/epicor-open-jobs-baq-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generates a read-only T-SQL query listing open, released jobs from Erp.JobHead with optional due-date and part filters.

## Category

epicor

## Erp

Epicor Kinetic

## Audience

- Epicor developer
- Epicor BAQ author
- Production planner

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Company ID

**Key:** company

**Type:** text

**Default:** EPIC06

#### Plant

**Key:** plant

**Type:** text

**Default:** MfgSys

#### Due on or before

**Key:** due_before

**Type:** date

**Default:** 2026-12-31

#### Part number pattern

**Key:** part_like

**Type:** text

**Default:** BRK%

**Help:** SQL LIKE pattern. Use % for all parts.

#### Job firm filter

**Key:** firm

**Type:** select

**Options:**

- Any
- Firm only

**Default:** Any

**Blocks:**

#### Open jobs query

**Body:** -- Open released jobs for company {{company}}, plant {{plant}}
-- Read-only. Run in SSMS against a non-production copy first.
SELECT
    jh.Company,
    jh.Plant,
    jh.JobNum,
    jh.PartNum,
    jh.PartDescription,
    jh.ProdQty,
    jh.StartDate,
    jh.DueDate,
    jh.JobFirm,
    jh.JobReleased,
    jh.JobComplete,
    jh.JobClosed
FROM Erp.JobHead AS jh
WHERE jh.Company = '{{company}}'
  AND jh.Plant = '{{plant}}'
  AND jh.JobClosed = 0
  AND jh.JobComplete = 0
  AND jh.JobReleased = 1
  AND jh.DueDate <= '{{due_before}}'
  AND jh.PartNum LIKE '{{part_like}}'
ORDER BY jh.DueDate, jh.JobNum;

#### Firm-job note

**Body:** -- Firm filter selected: {{firm}}
-- If you chose Firm only, add this line to the WHERE clause:
--   AND jh.JobFirm = 1

#### Operations per open job (optional join)

**Body:** SELECT jo.JobNum, jo.AssemblySeq, jo.OprSeq, jo.OpCode, jo.OpComplete
FROM Erp.JobOper AS jo
JOIN Erp.JobHead AS jh
  ON jh.Company = jo.Company AND jh.JobNum = jo.JobNum
WHERE jh.Company = '{{company}}'
  AND jh.JobClosed = 0
  AND jo.OpComplete = 0
ORDER BY jo.JobNum, jo.AssemblySeq, jo.OprSeq;

**Notes:** Table and column names are standard Erp schema names, but verify them in the Data Dictionary Viewer for your Kinetic version. In a BAQ, build the same joins visually in Query Builder and add the criteria in the Criteria tab rather than pasting raw SQL. Always filter on Company.

## Content

**Intro:** Open-job lists are the most requested Epicor report: what is released, not complete and due soon. This generator builds a clean T-SQL query against Erp.JobHead (and optionally Erp.JobOper) that you can paste into SSMS for a quick check or use as the blueprint for a BAQ.

**Sections:**

#### How to use the generator

**Body:** Enter your company ID, plant, a due-date cut-off and a part pattern, then copy the code blocks. The first block lists open released jobs, the second shows how to restrict to firm jobs, and the third joins Erp.JobOper to list incomplete operations. Run it against a test or reporting copy first. If you build a BAQ instead, recreate the joins in Query Builder, set the criteria as parameters, and publish the BAQ so it can be used in dashboards and Excel.

#### What the job flags mean

**Body:** A job is open when JobClosed is false. JobComplete means production is finished but the job may still need closing. JobReleased means it is on the shop floor and can collect labor. JobFirm marks the job as firm so MRP will not reschedule or delete it. The query uses all three so planners see work that is genuinely active. See the Epicor job lifecycle flags reference for the full list of related fields.

**Bullets:**

- JobClosed = 0: the job is not closed
- JobComplete = 0: production not yet marked complete
- JobReleased = 1: released to the floor
- JobFirm = 1: protected from MRP rescheduling

#### Common mistakes

**Body:** The most frequent error is forgetting the Company filter, which returns jobs from every company and slows the query. Another is using DueDate when the planner cares about StartDate, or comparing dates to text in the wrong format. Do not write back to Erp tables with SQL; use business objects or DMT so BPMs, security and audit fire. Large joins to LaborDtl can be heavy, so add date limits.

**Cta:** If you want planners to ask for open jobs in plain English instead of maintaining BAQs, Netray can put a read-only natural-language layer over your Epicor data, on-prem if required.

**Faq:**

#### Can I paste this SQL straight into a BAQ?

**Answer:** Not directly. BAQs are built in Query Builder from tables, joins and criteria, not raw SQL. Use the generated query as a guide: add Erp.JobHead, set the same filters in the criteria, and add the columns to the Display Fields tab.

#### Is it safe to run on production?

**Answer:** The query is read-only, but a broad query on a busy database can still cost resources. Run it against a reporting copy if you have one, filter on Company and dates, and avoid joining large transaction tables without limits.

#### Why are closed jobs still showing?

**Answer:** Check that JobClosed = 0 is in the WHERE clause and that you are not filtering only on JobComplete. A complete job is not necessarily closed. Also confirm the plant filter matches the plant on the job header.

#### Does it work on Epicor 10 and Kinetic?

**Answer:** The Erp.JobHead and Erp.JobOper tables exist across Epicor 10 and Kinetic with these core columns. Custom UD columns and any version-specific additions should be confirmed in your Data Dictionary Viewer before use.

## Search terms

epicor open jobs query, epicor jobhead sql, epicor baq open jobs, erp.jobhead table fields, epicor open job report sql, epicor kinetic sql query jobs, epicor jobhead jobclosed jobcomplete, epicor jobhead duedate query

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json