~If you want to recursively traverse the BOM you may need a CTE query?~

Kinetic ERP
45

Thanks for taking the time to go through this and provide so much feedback.

I like the idea of replicating the default behavior. For our purpose, we are trying to calculate the future cost of BOMs so that we can set our pricing accordingly. So I’m not terribly opposed to just always using the most recently approved revision - even if the date is in the future. But it’s good to model Epicor’s behavior because it will be consistent and help users to understand what they’re getting. Especially in this case where they aren’t looking at all the details. It’s intended to be a high level exercise.

I will try to play with this today to get it to return one row without using an aggregate function. At the end of the day, that’s what was preventing me from going forward. I wonder if it will work if I limit the subquery to 1 row and sort by effective date descending while my condition says approved = true.

46

We use the following to determine the part revision.

You can adjust as needed.

(Select a.PartNum, a.RevisionNum, a.RevShortDesc
                  From [PartRev] a
                  Inner Join ( -- Use Left Join if the records missing from Table B are still required
                      Select partnum, SysRevID, RevisionNum,
                          ROW_NUMBER() OVER (PARTITION BY partnum ORDER BY  ApprovedDate DESC) As _RowNum
                      From [PartRev]  where Approved = 1 and EffectiveDate <= getdate() 
                              ) b On b.partnum = a.partnum and a.RevisionNum = b.RevisionNum
                  Where 1 = 1 and b._RowNum = 1 
                  ) as RealPartRev
2 Likes
47

Nice Ken, thanks for sharing.

48

I’m just now taking my first look into it, but pretty confident that this post and example BAQ has some significance to the query that we are discussing.

PS: I think between this and the work that @knash has provided, should be able to put something together that works as expected.

I am not certain just yet how to, or even if, the Calculated field used in the example BAQ can be modified to use the where clause as to control the Effective Date ( Approved Date ) as Ken shows. Still researching that part.

rank() over (partition by PartRev.PartNum Order By PartRev.EffectiveDate desc, PartRev.RevisionNum desc)

That’s the part that where the Rank is built. Maybe table criteria takes precedence, maybe there is easy way to edit the Calculation.

1 Like