BOM遞迴查詢小執行個體

查詢某個原物料下的子組件：

select level,
       t.*
  from (select items1.segment1 segment1_m,
               items1.inventory_item_id m_id,
               items1.description description_m,
               materials.bill_sequence_id,
               components.item_num,
               components.operation_seq_num,
               components.component_item_id sub_id,
               items2.segment1 segment1_sub,
               items2.description description_sub,
               components.component_quantity,
               components.EFFECTIVITY_DATE,
               components.DISABLE_DATE
          from bom_bill_of_materials    materials,
               mtl_system_items_b       items1,
               bom_inventory_components components,
               mtl_system_items_b       items2
         where materials.assembly_item_id = items1.inventory_item_id
           and materials.bill_sequence_id = components.bill_sequence_id
           and components.component_item_id = items2.inventory_item_id
           and items2.organization_id = 103
           and materials.organization_id = 103
           and items1.organization_id = 103
           and components.EFFECTIVITY_DATE <= sysdate
           and nvl(components.DISABLE_DATE,sysdate+1) > sysdate
           ) t
connect by prior t.segment1_sub = t.segment1_m
 start with t.segment1_m = 'S-7BR1AUX-B1';

 

--connect by prior t.sub_id = t.m_id
-- start with t.m_id = '19044'