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
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.
Related Tools
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.
EpicorEpicor 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.
EpicorEpicor 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.
EpicorEpicor 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- Epicor DMT Import Template Worksheet (Part and BOM)
- Epicor MRP Run Checklist
- Epicor Kinetic Upgrade Checklist
- Epicor Safety Stock and Reorder Point Calculator
- Epicor EOQ and Lot Sizing Calculator
- Epicor Resource Group Burden Rate Calculator
- Epicor Shop Floor OEE Calculator
- Epicor Job Cost Variance Worksheet
- Epicor Shop Floor Labor Reporting SOP Template
New to Epicor ERP? Start with the Epicor ERP guide and toolkit, or plan with Training Path Builder, ERP Certification Study Planner, Upgrade Path Planner.
Keep exploring
Free tools
- Epicor Open Jobs BAQ SQL GeneratorGenerates a read-only T-SQL query listing open, released jobs from Erp.JobHead with optional due-date and part filters.
- Epicor BAQ Performance Tuning ChecklistWork through a practical list of checks when a BAQ, dashboard or BPM lookup runs slowly in Epicor Kinetic.
- Epicor BAQ OData REST URL BuilderBuilds Epicor REST v2 OData URLs and curl commands for BAQs and business object services, with API key header and filters.
ERP fixes and how-tos
- How to Create a BAQ in Epicor KineticCreate a BAQ in Epicor Kinetic Query Builder: pick tables, set joins, display fields, criteria, calculated fields, test, and use it in dashboards and reports.
- How to Create a BAQ in Epicor: Build, Run, Search, Export to Excel and PublishStep-by-step: create a BAQ in Epicor Query Builder, add tables and criteria, run and search it, export to Excel, and publish it as a dashboard.
- Epicor Kinetic Dashboard Examples (Shop Floor, Sales, Purchasing) and How to Add FiltersPractical Epicor Kinetic dashboard examples for shop floor, sales and purchasing, with the tables to query and how to add filters and parameters.
AI on your ERP
- Private AI for Epicor Kinetic Cloud, Outside the Multi-Tenant BoundaryRun a private LLM on Epicor Kinetic private cloud or Kinetic Cloud data via BAQs, REST v2 and Data Fabric, keeping sensitive manufacturing data off shared AI.
- Epicor Kinetic AI for Metal Fabrication: Jobs, Heat Numbers and Outside Processing in Plain EnglishPrivate AI on Epicor Kinetic for metal fabricators: ask about jobs, nests, heat numbers and outside processing in plain English, grounded in live data.
Integrations
- How to use Claude with Epicor KineticUse Claude with Epicor Kinetic through an MCP connector, the Anthropic API, AWS Bedrock or Google Vertex, plus the private on-prem alternative for sensitive data.
- How to build an MCP server for Epicor KineticDesign a Model Context Protocol server for Epicor Kinetic: which read tools to expose, BAQ and REST v2 mapping, auth, stdio vs HTTP transport and audit logging.