My first try at this was to filter out all but the minimum level in a certain set of departments. This minimum level could be any number depending on where it fell in the BOM structure. I could not figure that out. This, in my mind, is the simplest solution if it can be done. If this can be done, I don’t need a counter column like we’ve been discussing.
Can step back and explain what you are trying to do? Remember that we don’t know any of your companies processes so make sure if it’s not base epicor that you explain what you mean. We don’t know what “Level” means in your context here.
Windowing functions can be used to filter for the minimum by using rank or row numbers, then filtering on the next level up. But you can also group and use aggregate functions to get that, and then bring the subquery into join to whatever other information you need.
1 Like
Basically, This is a BOM recursion baq that only needs to pull purchased parts in the assembly depts (137, 197, 195) and the first mfg part from the machining depts. so no raw material (bar stock) and no intermediate part numbers that are requires to make the machined part (aka the minimum level in a dept not in 137, 197, or 195).
So I will assume the recursion is working the way that you want to get the base set of data. Correct?
Now you want to exclude a some of the results based on a certain set of criteria?
The criteria is this.
If department is (137 or 197 or 195 ) and Purchased then include.
If departments is not (137 or 197 or 195) then include only the first manufacturing part number (based on level I’m assuming?)
So first step I would do is make a calculated bit field for AssemblyDepts
case when character01 = 137
or character01 = 197
or character01 = 195 then
1
else 0
end
Then you can use that field in your other stuff.
Row_Number() over (partition by MainPart, AssemblyDepts order by level)
(edit: if you want to restart at each dept then add in Dept in the partitions)
After you get those fields, bring that subquery into the top level then make your criteria on the table filters something this.
(AssemblyDepts = true
and Purchased = true)
or (Purchased = false
and MyRowNumber = 1)
From what you’ve told me, I think that should work… but I don’t have all the information, (like your BAQ) so I’m kinda debugging through a straw here.
1 Like
Also, Morbid curiosity here, but what are you using this info for? When I see “Part to do adjust on Hand” something smells fishy.
It’s a customization that’s not working dealing with phantoms and inventory counts so the intermediate fix is to do a baq to adjust off and on the appropriate parts.