EpicorFree generatorPDF, Excel, Word exportEpicor KineticDiscrete Manufacturing

Epicor BAQ Query Template Generator for Open Jobs and Operations

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.

Fill in

Generated code
Download:

Starter query (JobHead joined to JobOper)

-- BAQ: OpenJobsByOp
-- 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 = 'EPIC06'
  AND jh.JobReleased = 1
  AND jh.JobClosed = 0
  AND jh.PartNum LIKE '%'
  AND jh.DueDate >= DATEADD(day, -30, GETDATE())
ORDER BY jh.DueDate, jh.JobNum, jo.AssemblySeq, jo.OprSeq;

Calculated field idea (late flag)

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

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

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.

BAQ backlog review

Share your list of requested BAQs and we will point out which ones a read-only AI layer could answer instead.

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

How to use the generator

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.

  • 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

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

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

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.

Frequently Asked Questions

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

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?

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?

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?

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.

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.

Talk to an ExpertExplore More Free Tools

Related Tools

Epicor

Epicor Kinetic Job Status Reference

A searchable lookup of the job lifecycle flags and related fields in Epicor Kinetic job entry, so you can build BAQs and dashboards with the right filter.

Epicor

Epicor REST API v2 URL Builder

Fill in server, instance, company and service to get a ready OData URL, a curl command and the required headers for the Epicor REST API v2.

Epicor

Epicor Security Group Design Worksheet

An editable worksheet to plan security groups by role and score each for exposure, so over-broad access is visible before audit.

Epicor

Epicor BPM Directive Design Template

A fill-in design document for BPM method and data directives covering trigger, conditions, actions, testing and rollback.

More Epicor tools

See all
Back to All Tools