Epicor BAQ Recursive BOM Join on PartMtl, Previous Operation Complete and Stale Headers
epicor baq how to recursive join mtlparts
Also searched as
- epicor baq multi level bom partmtl
- epicor baq previous operation complete
- table relations epicor baq
- epicor baq table headers not updating name
Short answer
A BAQ cannot run a recursive query, so a multi-level bill of materials is built by joining PartMtl to itself once per level, or by using a function or the BOM explosion tools for unlimited depth. Previous operation complete uses a subquery against JobOper. Stale column headers usually come from the saved dashboard layout, not the BAQ.
Applies to: Epicor Kinetic and 10.x. Table and field names are from the standard schema; verify in the Data Dictionary Viewer for your release.
How to build a multi-level BOM BAQ
- 1Create a BAQ with PartMtl as the first table and give it an alias such as Level1.
- 2Add PartMtl again with alias Level2 and set the relation: Level2.PartNum = Level1.MtlPartNum, plus Company, and revision and alternate method as your data requires.
- 3Set the relation to left outer so parts without sub-components remain.
- 4Repeat for as many levels as your deepest BOM needs (usually three to six).
- 5Add Part joins to show description and type for each level.
- 6Filter to the top-level part with a parameter and, if needed, only released revisions.
- 7Test against your deepest BOM. If depth is unknown, use a function for the explosion.
Why no true recursion, and the join-per-level pattern
T-SQL has recursive common table expressions, but the BAQ designer builds a fixed query from tables and subqueries, so there is no recursive form. The standard workaround is a fixed number of self joins of PartMtl, each aliased, where level two's parent is level one's component. Each level adds a left join, and query cost rises quickly with depth.
Two caveats. PartMtl is the engineering BOM for a part and revision, so you must match the revision and alternate method or you will see several versions. For manufactured jobs, JobMtl holds the actual job material, which may differ from the engineering BOM.
Previous operation complete
To test whether the prior operation is complete, join JobOper to a subquery that finds the previous OprSeq for the same job and assembly: the highest OprSeq lower than the current one. Then compare the OpComplete flag of that row. A subquery of type top 1 ordered by OprSeq descending does this.
Show operations ready to run as those where the previous operation is complete or does not exist, and the current one is not. Use ISNULL on the subquery result so the first operation, which has no previous one, passes.
SELECT o.JobNum, o.OprSeq, o.OpComplete,
ISNULL((SELECT TOP 1 p.OpComplete FROM Erp.JobOper p
WHERE p.Company = o.Company AND p.JobNum = o.JobNum
AND p.AssemblySeq = o.AssemblySeq AND p.OprSeq < o.OprSeq
ORDER BY p.OprSeq DESC), 1) AS PrevComplete
FROM Erp.JobOper oTable relations and stale headers
Table relations in Query Builder define the join: type (inner or outer), and the field pairs. If row counts look wrong, open each relation and confirm Company is included and the pairs match the real key. An unintended cross join looks like a huge duplicate result.
When a changed field label does not show, check where the header is coming from. A saved dashboard or grid layout keeps the column caption it stored, so the BAQ edit does not appear until the layout is reset or the dashboard is updated. Also confirm you changed the display label, not the field name, saved the BAQ, and refreshed the cache by reopening the dashboard. If the problem persists, remove the column from the dashboard grid and add it back.
Try it here, free
Epicor BAQ Query Template Generator
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.
Common pitfalls
- !Joining PartMtl without revision and alternate method and getting duplicate versions.
- !Building ten levels of joins and slowing the database for every run.
- !Treating the engineering BOM as the as-built job material.
- !Forgetting that the first operation has no predecessor.
- !Editing the BAQ label and expecting a dashboard with a saved layout to change.
How an ERP-grounded AI assistant handles this
Self-join chains and subqueries are easy to get wrong by hand. An assistant that knows the schema can draft the join chain for the number of levels you need and flag a missing revision condition. Your developer checks it against a known BOM before use. On-prem deployment keeps engineering data private.
Frequently asked questions
Can an Epicor BAQ do a recursive BOM explosion?
Not directly. Use a fixed number of PartMtl self joins, or use a function or the standard BOM tools when depth is unknown or large.
How do I check previous operation complete in a BAQ?
Join JobOper to a top 1 subquery for the preceding OprSeq of the same job and assembly, and test its OpComplete flag, treating no previous row as complete.
Why are my BAQ column headers not updating?
The dashboard or grid layout often stores its own captions. Reset the layout or re-add the column after changing the BAQ display label and saving.
What is a table relation in a BAQ?
It is the join between two tables in the query: the join type and the field pairs. Include Company and the full key to avoid duplicate rows.
Related
Epicor BAQ Cookbook: Group By, Union, Distinct, Null Tests, Pivot and Subqueries
Most BAQ patterns map to plain SQL: Group By with aggregates, Combined Queries for union, a Distinct setting, null tests in criteria or expressions, and subqueries for per-row lookups. Pivot and running totals have no single switch, so build them with conditional aggregates and subqueries.
How-toHow to Create a BAQ in Epicor: Build, Run, Search, Export to Excel and Publish
A BAQ (Business Activity Query) is a saved SQL-style query built in Epicor's Query Builder. Open Query Builder, name the query, add tables and join them, choose display fields, add criteria, test it, then run it from the Execute or Analysis tabs, export to Excel, and publish it for use in a dashboard or report.
How-toFixing a Slow Epicor BAQ (Business Activity Query)
A slow BAQ in Epicor is usually caused by unindexed join columns, a subquery or calculated field forcing a table scan, or the BAQ pulling far more rows than the dashboard actually displays before filtering client-side. Fix it by reading the SQL Server execution plan Epicor generates, moving filters into the BAQ criteria instead of the dashboard filter panel, and replacing subqueries with joins where possible.
How-toEpicor MRP Runs but Generates No Suggestions
When Epicor's MRP process completes without a job or purchase suggestion for a part you expect one for, the cause is almost always the part's own configuration (Part Class Type, Make Direct, Non-MRP flag, planning Time Fence) or the demand not being linked in a way MRP recognizes, not a defect in the MRP engine. Work through the part's Planning tab, its safety stock and lead time setup, and the demand source (sales order line status, job material requirement) before assuming the run itself failed.
Error fixFixing Epicor BusinessObjectException: BPM Directive Errors
A BusinessObjectException that says "A Business Process Management (BPM) directive has raised the following error" means a directive on that business object stopped the transaction, either deliberately via a Raise Exception widget or accidentally via an unhandled .NET error in Custom Code. Expand the InnerException on the error dialog to see the real message and the directive name, then open BPM Designer for that object and method to find the widget that fired.
Error fixFixing Epicor REST v2 API 401 Unauthorized Errors
A 401 Unauthorized calling Epicor's REST v2 (api/v2/odata) endpoint almost always comes down to one of three things: a missing or wrong x-api-key header, valid credentials but an API key scoped to a different company than the one in the URL, or REST services simply not enabled for that endpoint. Confirm the API key exists and is active in Application Studio (or the classic REST API help page), matches the company segment in the URL, and that the account used for Basic auth or OAuth has the right security group.
AI for ERPAI for Epicor Kinetic, Beyond What Prism Covers
Add AI to Epicor Kinetic beyond Prism: private LLM over BAQs, BPM data, and REST v2, on-prem or private cloud, with honest guidance on when Prism already covers you.
Stuck on Epicor Kinetic?
Talk to engineers who work inside Epicor Kinetic every week, and who build private AI that answers these questions from your own ERP data.
Keep exploring
Free tools
- Epicor BAQ Query Template GeneratorFill 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.
- 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: 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.
- Fixing a Slow Epicor BAQ (Business Activity Query)A slow Business Activity Query in Epicor is almost always an execution plan problem, not an Epicor problem. Here is how to find the real cause and fix it without rewriting the BAQ from scratch.
- 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.
AI on your ERP
- AI for Epicor Kinetic Job Shops: Quote History, Job Variance and Schedule RiskPrivate AI for Epicor Kinetic job shops: quote-to-job history, JobHead and JobOper variance, subcontract delays and schedule risk, via REST and BAQs.
- 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.
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.
Software
- Epicor KineticEpicor Kinetic explained for manufacturers: modules, REST API, BAQs, deployment, strengths, limits and alternatives to compare before choosing or upgrading.
- Epicor CPQEpicor CPQ is a cloud configure-price-quote product with visual 3D configuration for manufacturers. Modules, ERP integrations and alternatives explained.