title: Need Oracle query for a BOM IMPLOSION or BOM "WHERE-USED" on Oracle 10g
body: <p>Working with Oracle DB 10g.</p>
  
  <p>I am tasked with writing an Oracle query for a BOM IMPLOSION commonly known as BOM "WHERE-USED."  Essentially, given an item or part, I need to provide a list of parent items that contain that item or part, if any.</p>
  
  <p>I have recently coded a BOM EXPLOSION using the following SQL which utilizes the START WITH and CONNECT BY syntax to create a heirarchy downward from a parent item.  I found inspiration for the BOM EXPLOSION query at <a href="http://www.confluentminds.com/Trainings/SCM/Topic1.1_Ch1_Part5.html" rel="nofollow">http://www.confluentminds.com/Trainings/SCM/Topic1.1_Ch1_Part5.html</a></p>
  
  <p>Current BOM EXPLOSION code:</p>
  
  <pre><code>/* BOM EXPLOSION */
  select distinct
         level,
         sys_connect_by_path(msib.segment1, ' / ') as "PATH",
         msib2.segment1 as "CHILD ITEM AT LEVEL/PATH"
         /*bic.component_item_id,*/
         /*msib.inventory_item_id,*/
         /*msib2.inventory_item_id*/
  from   bom.bom_components_b bic,
         bom.bom_structures_b bom,
         inv.mtl_system_items_b msib,
         inv.mtl_system_items_b msib2
  where  1=1
         and bic.bill_sequence_id = bom.bill_sequence_id
         and bic.disable_date is null
         and bom.assembly_item_id = msib.inventory_item_id
         and bom.organization_id = msib.organization_id
         and bic.component_item_id = msib2.inventory_item_id
         and bom.organization_id = msib2.organization_id
         and bom.organization_id = #### /* organization id here */
         and bic.effectivity_date &lt; sysdate
         and bom.alternate_bom_designator is null
  start with msib.segment1 = '$$$$$$$$$$' /* top parent item here */
  connect by nocycle prior bic.component_item_id = msib.inventory_item_id
  order by level
  </code></pre>
  
  <p>Now, I need go from any child item and list all parent items that contain that child item.</p>
  
  <p>I have searched for "oracle bom implosion" and "oracle bom where used" but nothing is obvious to me.  The BOM IMPLOSION seems far less straightforward than the BOM EXPLOSION.</p>
  
  <p>Any help or advice is greatly appreciated.</p>
  
  <p>Clint Van Zee</p>
  
  <p>EDIT 02-NOV-2011:</p>
  
  <p>Yes, I want to traverse up a BOM heirarchy and list those items or components where a specified component is used in the bill.</p>
  
  <p>Based on your answer, I looked into the "connect by" relationship and "start with." I think I have a way to do this. I may have been close to the answer without knowing it.</p>
  
  <p>Craig, here is your mock-up with modifications added to prove this out. I also modified the "connect by" and "start with." It {should!} start with the child component and go "upwards" to list those models or components that "use" the specified starting component. To do this, I also removed the "prior" keyword.</p>
  
  <pre><code>with data
  as
  (
      select 'topmodel1' id, 'component1' child_id from dual union all
      select 'topmodel1' id, 'component3' child_id from dual union all
      select 'component2' id, 'component5' child_id from dual union all
      select 'component3' id, 'component4' child_id from dual union all
      select 'component4' id, 'component5' child_id from dual union all
      select 'component5' id, null child_id from dual union all
      select 'topmodel2' id, 'component1' child_id from dual union all
      select 'topmodel2' id, 'component5' child_id from dual union all
      select 'component5' id, null child_id from dual
  
  )
  select distinct
         sys_connect_by_path(id, '/') path, child_id, level
  from data
  start with child_id = 'component5'
  connect by id = child_id
  order by level
  </code></pre>
  
  <p>This produces the following result:</p>
  
  <pre><code>PATH        CHILD_ID    LEVEL
  ----------- ----------  -----
  /component2 component5  1
  /component4 component5  1
  /topmodel2  component5  1
  </code></pre>
  
  <p>Looking at the mock data, component 5 is "used by" component2, component4, and topmodel2. So, this change seems to do what I intend. I had to add the dreaded "distinct" as it was traversing the same paths more than once.</p>
  
  <p>I think this is getting me closer.</p>
  
  <p>Here are the minimal changes to my BOM EXPLOSION code to do this in reverse:</p>
  
  <pre><code>START WITH msib.segment1 = '$$$$$$$$$$' /* child item here */
  CONNECT BY nocycle msib.inventory_item_id = bic.component_item_id
  ORDER BY level
  </code></pre>
  
  <p>When I apply this change to my SQL script and try it against the actual Oracle data, the CBO is reporting that the cost of searching just one component is out into the stratosphere. So, this method needs tuning.</p>