Indented BOM SQL Query

So to go along with my post a couple of days ago about the Method Master, (Indented Bill of Operations - ERP 10 - Epicor User Help Forum) I’m trying to write my own SQL query for BOM Listing. It will be much cleaner and just provide me with the information I need versus the report.

I have for the most part my multi level query written out and working properly except for one little thing. My where clause is currently around PartRev.Approved = true or null. This works great for probably 95% of the data. My problem is when there isn’t an approved revision. I don’t see all the data I need to see.

So rather than using PartRev.Approved = true or is null. I was thinking of finding and using the MAX(PartRev.EffectiveDate) but I’m not exactly sure of the syntax to use in the where clause to only grab the information from the revision with the max effective date.

Any thoughts?

If you want to do it in the Epicor BAQ, there is a case study walk through that shows you how to do the CTE.

https://epicweb.epicor.com/Education/OnlineHelpFeatureSummary/Epicor%20ERP%2010/10.2.100/Help/enu/Standard/Tools_BAQs/CaseStudy.CTE.html

(This is also in the help files too if you don’t have your epic web password set up)

As for you limiting to the max, it should be pretty much what you have there, but you will need a group by in there as you will always need that for an aggregate function. (unless you are using windowing functions, which is another topic)

4 Likes

Just out of interest what are you trying to achieve? Just asking as we have very complex boms where occasionally a part is included that is not approved. When we create the job the version we are on 10.0.7.4 just tells you a part rev is not approved, unlike Vantage which actually told you which one.

I have created a nested bom report to show the unapproved revisions, basically our nested bom query but using approved=false - we can only ever have one approved rev but I am intrigued if both can be included.

Alex,

This is a solution that we have used in the past. You’ll need to link to a subquery that will only return the Newest Effective Date. Something to the effect of…

SELECT * FROM Part T1 
INNER JOIN (SELECT TOP 1 Max(S1.EffectiveDate) FROM Erp.PartRev S1 WHERE S1.Approved = True) T2
    ON T1.Company = T2.Company AND T1.PartNum = T2.PartNum

James, I have created one of those as well, but it only goes down one level and works out well.

For this I have been asked to categorize our finished goods based on 3 criteria, total number of component (all the way down, not just top level BOM), total number of operations (all the way down, not just top level BOO), Subcontract. So the lowest category would be the simplest, few total parts, fewest operations, no subcontract to the highest category being most total parts, most operations, and subcontract.

What they want to do with this information is beyond me. I’m just trying to provide them with the information.

So the reason I ask is because this report could change on a daily basis dependent on the state of an Engineering Change. In my test, a component is not being displayed because there are currently no approved revisions (we only maintain 1 approved revision at any given time). This causes my count to be off. Which is why I would like to use MAX(Effective Date) rather than Approved = 1.

Brandon, thank you for this information. The actual example is indented BOM. I’m working through it right now.

We have a detailed bom that does all operations/all materials at all levels - if you wish I can share.

I also have a separate query/reports that does something similar to your need - we list the total number of ops, total number of assemblies - largely used a pricing smell test for low cost parts. We have variants that look at specific ops - for example total number of laser ops for all the sub-components on a large assembly - I think this is used for some kind of brute force contribution purposes.

1 Like

Hi James,

If the offer of sharing the detailed bom is still ok, can you please share ?

I am having trouble getting the indented BOM/BOO list based on the CTE example, so some working code code would be beneficial.

Thanks.

Cheers Rod.

Ok will do - I’ll properly comment the queries and get them to you.

We tried CTE and couldn’t get it to work so created SSRS with an SQL direct query - we are on premise Epicor and can live with the risk/performance risk - Jose Gomez and others will be scoffing and rolling their eyes in disdain :crazy_face:

I won’t judge I report sql direct with a readonly user and views/funcs in an alternate db. Be smart about it and have solid backups. Now if you’re using a read/write user we shall shun you

1 Like

SH_BOM.baq (34.8 KB)

This is the BAQ exactly as described int he help file

1 Like

I was able to get the CTE example to work how I needed it to. I’ve modified it since then to match what I need so I don’t know if my BAQ would help or not.

No read/write user

Thanks Steve - much appreciated.

Thanks James

Do you still need me to post something or has Steve sorted it for you?

What if you dont have a epicweb account or forgot password? is there another way to view the case study?

1 Like

Are you on 10.1? If you were on 10.2 they added them to the help files directly, so you don’t need epic web credentials. Do you have a newer test version that you could get the help files from?

1 Like

I am on version 10.1.600.20

did you look in the help files? I’m not sure when they put them in there.