How to query past revisions of the parent in BOM to get revision shown for a child item?
I want to query out all the revision of a parent and output child revisions of each parent revision exactly as displayed on UI. The documentation says
- The rev shown for a child item of any past revision of the parent item is the latest revision as of the time just preceding the next parent revision.
Svevis,
I’m not certain I can help or not, but to even start I have some questions. What is the input to this query? Are you starting with an item as the parent and providing a specific rev number? Then you want to see all its components at the time and what their rev values were? Or are you wanting to just provide the parent number, and show ALL revs in one query result? Is this an exploded BOM you’re looking for or just top level under the parent item?
Steve
Hoo boy, you aren’t asking for much, are you?? I would venture that it cannot be done as “one query result”. As a PL/SQL procedure, certainly. But it would most likely not be a single procedure but a group of procedures and functions.
Anyway, you would have to go through all of the ECO revisions of the assembly, and for each one, go out and find the component parts that are in the BOM for that specific assembly revision. And then for each component part in the assembly revision, you would have to get the component revision that was released most-recently previous to it (or that is how I *think* it always works – the reason I am here is because I need to find out if there is a configuration setting for that).
Given that the query for each assembly revision in the BOM looks like “select item_number, find_number, quantity from BOM where item=XYZ and change_in IN (0,<list of change IDs for all processed assembly revisions>) and (change_out = 0 OR change_out NOT IN (<list of change IDs for all processed assembly revisions>) )”, you have to *build* the query for each assembly revision.
No matter how you try it, not an easy task.