Hello,
I’m trying to build a CTE BAQ to build a BOM that will show us all the sub assembly parts and then raw materials that are layered in our parts.
I want the BAQ to pull this data for only the parts on the Jobs listed in Project entry. I set up the parameters for the BAQ to look at ProjectID but it errors out.
1 Like
I know this is bit impersonal, but we really need more information to even attempt to help you here. Here’s a template of the things to think about.
To help the community provide the best answer, could you include as much of the information below as you can? Your question appears to be lacking some much needed context
- Epicor Version
- Deployment Type (Cloud, On Prem, 3rd Party Cloud etc)
- The business problem you’re trying to solve
- What you’ve already tried
- Is this a Kinetic UX (Web) or Epicor Classic issue?
- Is it related to a Method Directive, Data Directive, Function, BAQ, UBAQ, Configurator, etc.?
- Any screenshots, error messages, or logs.
- Any code you’ve written (or borrowed , make sure it is property formatted)
```
code here
``` - Steps to reproduce the issue
For tips on how to ask questions effectively, check out this guide:
Tips for Asking Questions on the Forum
5 Likes
We are on Epicor Version 10.2.700, On Prem.
I have attached the BAQ that I have started.
Daher-BOM-PerAC.baq (58.3 KB)
Hello,
Recursive BOM’s are a lot of fun…
I would consider applying the parameter to the top level part number on the job and then filtering by the ProjectID in a subquery. It would be beneficial to see some screenshots of how you set up the Recursive BOM with the querys.
Just curious did you follow the steps on the creating CTE recursive BOM’s that epicor provides?
1 Like
I did follow the steps for creating a recursive BAQ.
The issue we have with our company structure is that we do not have a top level part number. We build airplanes so as you can imagine our BOM per aircraft is very complicated and varies from airplane to airplane based on configuration. We build out that configuration in Project Entry with jobs for each stage of our final production line.
We put upper lever Jobs with part numbers that represent a portion of the plane into Project Entry. The parent parts on those 15(ish) jobs represents all the parts that it takes to build that specific aircraft. We also build all the sub-assembly parts from raw materials that eventually go into those upper level job parts. The only way to link what parts are build to what aircraft is through Project Entry.
What I would like to write is a BAQ that looks at all the jobs per project ID and use those as the parent part. Then recursively look at all the mtl parts and see what it takes to build those. This can technically go 7 layers deep.
That sounds perfectly doable. I didn’t import the BAQ so I haven’t looked at it, but what error are you getting? Timeout? Bad SQL? does it pass syntax check?
Also, if it’s on jobs, you don’t need a CTE. The Job Asm and Job Mtl table has all of the levels in the table already.
1 Like
We have never been able to figure out how to see all level of parts by jobs themselves, especially because most of them are build to stock and not associated with the ProjectID jobs.
Ok, curiosity is killing my cat here… so I’m gonna pull in your BAQ, but you’re going to have to probably start how your jobs are set up then in order for use to help you with this. I may be able to look at your BAQ, but I don’t have your data.
If they are all separate jobs for each level, you’ll have to figure out how one is linked to another. If they are make to stock, you don’t have a true link here. You did mention they all have the same project? Can you just grab all of the jobs and job materials that have the same project?
So first thing I see wrong with your query, you created a parameter, but you never actually applied that to anything, so even if everything else is working correctly (which I don’t know for sure yet) that would cause a timeout.
1 Like
Our final assembly line is separated into about 9 different stages; floors, aft, wings, stages 1-6. Every aircraft serial number is given a special part number for the completed aircraft in that stage; i.e. PSN-100-0345-500. (this example would represent a “part” that is a Kodiak model 100, serial number 0345, complete stage 5) Specific jobs are created for each stage part and those are added to project entry. The jobs then have sub-assemblies with the specifications of parts by configuration and rev changes for that airplane.
Because these stage jobs were made to stock jobs they technically have to go back to stockroom and be issued to the following stage job. We are trying to make the stage jobs a job to job relation but there’s a lot of internal issues with when and how to do that.
This structure was setup by engineers many years ago and it’s a mess to untangle.
I have the parameter’s linked to Erp.Project again and get the same error. I exported the BAQ before I realized that I need to add those back in. I keep deleting and retrying options to get it to work.
But the jobs that are related all have the same project right? Just filter the job header by project ID and return everything. No CTE needed.
1 Like
The jobs that are associated with a project are jobs with part numbers that represent a portion of work that has been completed by that stage.
I need to recursively look into all the Subassembly material parts that are required to build the major parts that complete each stage.
Example:
Project ID 100-0345 has job #123456 to build PSN-100-0345-600 (this job/part number represents airplane SN 0345 as a completed plane)
That job has materials issued:
A100
A200
A300
A400
PSN-100-0345-500
This job also has 92 Subassemblies with their own materials:
ASM:1
ASM:2
ASM:3
Mtl: B100, B200, B300
ASM:4
Mtl: C100, C200, C300
I need to know what parts were required to build each of the parts in those Subassemblies.
Example:
Mtl: B100 has a job with issued materials and 15 Subassemblies with their own materials.
This model continues over and over for 7 layers of Subassembly Materiels.
And those lower level jobs don’t have a project assigned to them? (can they? That would be a good way to sort this out)
Do you have lower level jobs that go into multiple upper level jobs?
Also, you’re gonna have to limit the job tables somehow, because they will hold historical information. So if you ever make that part again, it will show up for every job. You could maybe filter by jobs that aren’t closed or completed? But that’s not going to be the best as I would think that you would be completing the lower level jobs as they are being made. You’re probably timing out because it’s getting to much stuff.
We cannot assign the lower level jobs to Project because those are job for parts that we stock and make long ahead of the aircraft assembly line.
Then this won’t work. You don’t have definitive links from one job to the next. The best you can do is a BOM CTE based on the master tables (partMtl). You can get the starting part number from the parent job, but you’ll have to walk a generic BOM, not job BOM’s.
Take a step back and tell us what information you actually want out of this at the end of the day. Are you just looking for BOM information? Or do you want job information?