BOM展开

SELECT DISTINCT *
  FROM (SELECT rownum seq_num,
               lpad(to_char(LEVEL), decode(LEVEL, 1, 1, LEVEL + 1), '.') bom_level,
               msi.segment1 assembly_item,
               msit.description assembly_description,
               msic.segment1 component_item,
               msitc.description c_item_description
          FROM bom_bill_of_materials        bbm,
               bom_inventory_components     bic,
               mtl_system_items_b           msi,
               mtl_system_items_b           msic,
               mtl_system_items_tl          msit,
               mtl_system_items_tl          msitc,
               org_organization_definitions ood
         WHERE 1 = 1
           AND bbm.assembly_item_id = msi.inventory_item_id
           AND bbm.organization_id = msi.organization_id
           AND bic.component_item_id = msic.inventory_item_id
           AND bic.pk2_value = msic.organization_id
           AND bbm.bill_sequence_id = bic.bill_sequence_id
           AND (bic.disable_date IS NULL OR bic.disable_date >= SYSDATE)
           AND bic.effectivity_date <= SYSDATE
           AND ood.organization_id = msi.organization_id
           AND msi.organization_id = msit.organization_id
           AND msi.inventory_item_id = msit.inventory_item_id
           AND msit.language = userenv('LANG')
           AND msic.organization_id = msitc.organization_id
           AND msic.inventory_item_id = msitc.inventory_item_id
           AND msitc.language = userenv('LANG')
        CONNECT BY bbm.assembly_item_id = PRIOR bic.component_item_id
         START WITH msi.segment1 IN ('11011005472')
                AND ood.organization_code = 'SN') a
 WHERE bom_level IN ('1', '..2')