Does anyone know what the links are to build a full BOM report. I have tried Part and PartMtl and linking the PartMtl again to go 10 levels deep but when there are multiple Revs it gets muddy. I have tried for years to build a query to recreate an exploded BOM but never successful. Any help appreciated.
I have a SQL view that I use… Not sure if this will be helpful to you.
WITH pm AS (SELECT x.Company, x.PartNum, x.RevisionNum, x.QtyPer, x.FixedQty, x.MtlPartNum AS ChildPartNum, x.ViewAsAsm
FROM Erp.PartMtl AS x INNER JOIN
Erp.PartRev AS pr ON pr.Company = x.Company AND pr.PartNum = x.PartNum AND pr.RevisionNum = x.RevisionNum AND pr.AltMethod = x.AltMethod AND pr.AltMethod = ’ ’ AND pr.Approved = 1 AND
pr.EffectiveDate <= GETDATE() AND pr.EffectiveDate =
(SELECT MAX(EffectiveDate) AS Expr1
FROM Erp.PartRev AS pr2
WHERE (Company = pr.Company) AND (PartNum = pr.PartNum) AND (Approved = 1) AND (EffectiveDate <= GETDATE()))), bm AS
(SELECT 1 AS BOMLevel, PartNum AS TopLevelPartNum, Company, PartNum, RevisionNum, QtyPer, FixedQty, ChildPartNum, CAST(QtyPer AS decimal(30, 10)) AS PartQty, ViewAsAsm
FROM pm AS root
UNION ALL
SELECT bm.BOMLevel + 1 AS BOMLevel, bm.TopLevelPartNum, node.Company, node.PartNum, node.RevisionNum, node.QtyPer, node.FixedQty, node.ChildPartNum, CAST(node.QtyPer * bm.PartQty AS decimal(30,
10)) AS PartQty, node.ViewAsAsm
FROM pm AS node INNER JOIN
bm ON bm.Company = node.Company AND bm.ChildPartNum = node.PartNum
WHERE (bm.ViewAsAsm = ‘1’))
SELECT BOMLevel, TopLevelPartNum, Company, PartNum, RevisionNum, QtyPer, FixedQty, ChildPartNum, PartQty, ViewAsAsm
FROM bm
2 Likes
Are you trying to do it via SQL or as a BAQ?
This CAN be done… but it takes a RECURSIVE (CTE) BAQ. I have demoed this in the past at user group meetings.
Note that this is NOT something for the faint of heart… your brain gets twisted while trying to get your head around a CTE query.
BOM_Explode.baq (72.8 KB)
Recursive BAQs.pdf (983.2 KB)
6 Likes
Take a look at this thread.
Thanks All,
I will get to work to test each suggestion out and let you know the results.
I tried the baq called BOM Explode and it timed out.
Yea, you probably need to filter it to only one parent part number.
It seemed to prompt for a parent part number.
@tthayer did you eventually find a happy place where the BAQ would complete in a reasonable amount of time?
I really wanted to find the parent where the Effective Date was not greater than the getdate(), and I was able to speed up the query by taking the PartPlant and Part tables and criteria out of the AllPartRevs subquery.
But I am not quite certain as to what ramifications that may have exactly. Although it actually seems to be placing expected parts through a method that I am looking at on a sample part.
I did get it to run faster but it was not by taking out tables. Even though the query was running in a specific company I found if I put a filter in on each table for company = ???. That made a huge difference. What I am thinking is by putting in the filters that criteria is then passed on to sql. Where if I did not do that sql returned everything and then epicor filtered it on the return.
That was my similar train of thought, that the Company and Plant if needed, are on PartRev anyways. So I moved criteria there and then poofed the PartPlant and Part tables, giving up also the Source and InActive parameters, which in my specific business use case appears to cause no issue. But, I can see where that would be useful, so maybe I’ll create myself another iteration and apply the criteria as you mention and see where I end up.
Originally I did boost QueryTimeout to 60 seconds and observed the timeout, decided it was better to optimize the query.
Thank you for your reply.
Firstly thank you to you all for the sample query and feedback. I am trying this out in our environment (10.2.700.23). The query imported and had no issues. No results for a top level part. I then removed the Company/Plant filters as we are single company/plant and thought it would speed the BAQ up. It did not and still got no results even when I moved it over to a Dashboard where it ran a little longer but nothing came up.
Also tried removing the PartPlant and Part tables from the AllPartRevs subquery with still no results.
Any ideas as to how I can get this BAQ to work and return results?
I downloaded the BAQ as well but I get this error Incorrect syntax near ‘(’.
I traced it to the calculated field RANK. When I use this expression in a different BAQ it works fine. If I remove it I get other errors related to the field RANK is not valid
rank() over (partition by PartRev.PartNum Order By PartRev.EffectiveDate desc, PartRev.RevisionNum desc)
Has anyone else seen this or gotten the BAQ to work for them?
Are you really running 10.1.4… what version of SQL?
Reason I ask, some BAQ examples will only work using the same or later versions they were built in.
Also, have you tried searching Indented"? I think you’ll find a lot of examples for BOM’s.
e.g.
10.2.200.5 But the syntax works in another BAQ. I’ll see if I can find other examples
Thanks
Note in addition to the indenting of your BOM’s levels,
you’ll (probably) need to make sure revisions are handled correctly too.
2 Likes
and REVISIONS is what makes this one of the tougher queries to build because you don’t want ALL the revisions of the children’s parts. you only want to see the CURRENT.
How do you know that the Rank() is the part that is giving you problems?
Because when I keep that calculated field and remove the expression I replace it with 0. The syntax error goes away then. As soon as I add the expression back for the RANK calculated field the syntax error comes back