We have built a BAQ to create a BOM using CTE. The BAQ uses the max effective date to identify the highest level parts below the parent part entered. However, when we look at part of the BOM we see that the parent part is at the max level but it then shows subordinate parts that were in the original rev but now have been removed:
In this image you will see the parent on line 83 with the proper rev of 3 for mtlseq 30, and then line 84 has the new part of NOT USED for mtlseq 50, which replaced the old mtlseq 50 part. The original seq 50 part is showing on lines 85 and 86. The new part does not have any subordinate material.
I can’t figure out how to change the query to stop showing the removed subordinate material. If I look at the basic query it is pulling all material with all revisions that is subordinate to the top parent I am querying against. A subquery above the CTE and union in the query is trying to filter out by the max effective date, but that clearly isn’t removing the incorrect parts.
How do I specify that I want the child parts for the max rev and not the subordinate material from a previous rev?
I have been thinking that what I may need to do is to use the result set to filter out the records where the parent part is not found in the material part list. I have been trying to do something like an unmatched query where I take my overall query results then try to compare it to the same results to do the unmatched query but nothing ever returns.
Does anyone have an idea how I might do this?
I have been trying to get some sort of rank functionality to perhaps give me the ability to weed out the subordinate records for the older revisions but so far no luck. I would think someone has run into this situation trying to build a BOM.
The way I finally solved this issue was to build the basic BOM using the CTE and Union all queries, then find the max revision (by date) for the parts. After that I looked for the parent part in the child part column. If it was not found it eliminates those “orphaned” parts from the previous revision. Once I pulled that data I then added back in the parts at the first or top highest level since they didn’t (and wouldn’t) have a parent. This is how I matched them up at the end, then added in the top hierarchy
It’s a subtle difference but the first relationship equates the parent part (PartNum) to the subordinate material part (MtlPartNum). Putting Distinct on the top level also is necessary.
