# Epicor BAQ Query Template Generator for Open Jobs and Operations

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

## Short answer

Fill in company, days back and status filters to get a starter SQL for an open jobs BAQ, plus a design checklist for the Query Builder.

## Category

epicor

## Erp

Epicor Kinetic

## Industry

Discrete Manufacturing

## Audience

- Epicor BAQ developer
- Epicor administrator
- Production planner

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Company ID

**Key:** company

**Type:** text

**Default:** EPIC06

#### Released flag

**Key:** released

**Type:** select

**Options:**

- 1
- 0

**Default:** 1

#### Closed flag

**Key:** closed

**Type:** select

**Options:**

- 0
- 1

**Default:** 0

#### Due date window (days past)

**Key:** days_back

**Type:** number

**Default:** 30

#### Part number filter (use % for all)

**Key:** part

**Type:** text

**Default:** %

#### BAQ ID

**Key:** baq_id

**Type:** text

**Default:** OpenJobsByOp

**Blocks:**

#### Starter query (JobHead joined to JobOper)

**Body:** -- BAQ: {{baq_id}}
-- Tables: Erp.JobHead, Erp.JobOper
SELECT
  jh.Company,
  jh.JobNum,
  jh.PartNum,
  jh.ProdQty,
  jh.StartDate,
  jh.DueDate,
  jo.AssemblySeq,
  jo.OprSeq,
  jo.OpCode,
  jo.RunQty,
  jo.OpComplete
FROM Erp.JobHead jh
INNER JOIN Erp.JobOper jo
  ON jo.Company = jh.Company
 AND jo.JobNum = jh.JobNum
WHERE jh.Company = '{{company}}'
  AND jh.JobReleased = {{released}}
  AND jh.JobClosed = {{closed}}
  AND jh.PartNum LIKE '{{part}}'
  AND jh.DueDate >= DATEADD(day, -{{days_back}}, GETDATE())
ORDER BY jh.DueDate, jh.JobNum, jo.AssemblySeq, jo.OprSeq;

#### Calculated field idea (late flag)

**Body:** -- Add as a Calculated Field in the Query Builder Calculated Fields tab
CASE WHEN JobHead.DueDate < GETDATE() AND JobHead.JobComplete = 0 THEN 1 ELSE 0 END

#### Design notes for {{baq_id}}

**Body:** -- 1. Build in Query Builder: Tables tab, add JobHead then JobOper, join on Company and JobNum.
-- 2. Display Fields: pick only columns you need. Fewer columns run faster.
-- 3. Criteria: use Table Criteria for fixed filters and Query Parameters for user prompts.
-- 4. Test in the Analyze tab, check row counts and the generated SQL.
-- 5. Save, assign a security group, and set the Shared flag only when appropriate.

**Notes:** This is starter SQL to guide your build. Epicor BAQs are saved as definitions in Query Builder, and Query Builder generates its own SQL, so recreate the joins and criteria there. Confirm table and column names in Data Dictionary Viewer for your Kinetic version.

## Content

**Intro:** A good BAQ starts as a clear query before it is clicked together in Query Builder. This generator gives you a starter SQL for the most requested BAQ, open jobs by operation, and a checklist of how to recreate it correctly.

**Sections:**

#### How to use the generator

**Body:** Enter your company ID, choose the released and closed flags, set a due date window and optionally filter by part number. Copy the SQL into a text editor to review the logic, then rebuild it in Query Builder under Tables, Display Fields and Criteria. Epicor stores the BAQ as a definition and generates its own SQL, so the pasted code is a blueprint and not something you import.

**Bullets:**

- Always join JobHead to JobOper on Company and JobNum
- Keep Company in every join, or multi-company data will multiply
- Use query parameters for anything a user should choose
- Name the BAQ clearly so it is findable in the menu

#### Why joins on Company matter

**Body:** Epicor tables carry Company as the first column of the key. Leaving it out of a join can pair a job with operations from another company in a multi-company install, and the error is silent. The result looks plausible but the counts are wrong. Check the row count of a simple JobHead query against the joined result as a quick test.

#### Calculated fields and performance

**Body:** Calculated fields are evaluated in the generated SQL, so keep them simple. A late flag based on due date and the complete flag is cheap. Subqueries across large tables such as LaborDtl are not, and they belong in a dedicated BAQ with narrow criteria. Always limit by date or company before sorting.

#### Common mistakes

**Body:** Filtering only on Closed shows complete jobs as open. Forgetting the shared flag stops other users from running the query. Granting a BAQ to a security group that lacks menu access means the BAQ exists but no one can find it. Test as a normal user, not as manager.

**Cta:** If you want users to ask for this data in plain English without waiting for a BAQ build, we can set up a read-only layer over your Epicor database.

**Faq:**

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

**Answer:** No. Query Builder stores a BAQ as a definition made of tables, fields, joins and criteria, and generates the SQL itself. Use the generated SQL as the blueprint and recreate the same logic in the designer. You can then compare the Analyze tab SQL with your template.

#### What tables hold open job data in Epicor?

**Answer:** JobHead holds the job header and status flags, JobOper holds operations, JobMtl holds material requirements and JobAsmbl holds assemblies. All are keyed by Company and JobNum, which is why every join should include both columns.

#### How do I add a prompt for the user to choose a date?

**Answer:** Create a Query Parameter in Query Builder and reference it in the criteria of the table or in a calculated field. When the BAQ runs from a dashboard or the menu, Epicor prompts the user for the value.

#### Can I use a BAQ from the REST API?

**Answer:** Yes. Saved BAQs can be called through the REST API using the BAQ service, with query parameters passed on the URL. Our REST URL builder in this tool set shows the exact pattern to use.

## Search terms

epicor baq query, epicor baq template, epicor business activity query example, epicor open jobs baq, epicor jobhead joboper sql, epicor query builder, epicor baq calculated field, epicor kinetic baq tutorial

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