BOM树-由父节点向子节点查询

      接触EBS刚刚不久，对于我这种菜鸟，看着超过百行的代码，头都有些发晕。前几天导师给我一个十级BOM的语句，语句中各个表中关系比较清晰，但是语句过于繁琐，一直想进行优化，今天正好有时间，重写了一下BOM树结构，便于自己以后应用。

 

      由父节点向子节点查询：

 

select level bom_level, --等级
       bom_trees.sub_item_id, --子节点
       bom_trees.sub_item_code, --子节点物料编码
       bom_trees.sub_item_name, --子节点物料说明
       bom_trees.parent_item_id, --父节点
       bom_trees.parent_item_code, --父节点物料编码
       bom_trees.parent_item_name --父节点物料说明

  from (select msi_sub.inventory_item_id sub_item_id,
               msi_sub.segment1          sub_item_code,
               msi_sub.description       sub_item_name,
               msi.inventory_item_id     parent_item_id,
               msi.segment1              parent_item_code,
               msi.description           parent_item_name,
               msi.organization_id       organization_id
        
          from bom_inventory_components bic,
               bom_bill_of_materials    bom,
               mtl_system_items_b       msi,
               mtl_system_items_b       msi_sub
         where msi.inventory_item_id = bom.assembly_item_id
           and msi.organization_id = bom.organization_id
           and msi.organization_id = 103
              
           and bom.bill_sequence_id = bic.bill_sequence_id
           and bom.alternate_bom_designator is null
           and bom.organization_id = 103
              
           and bic.disable_date is null
           and bic.component_item_id = msi_sub.inventory_item_id
              
           and msi_sub.organization_id = 103
        
        ) bom_trees --BOM树结构规则
connect by prior bom_trees.sub_item_id = bom_trees.parent_item_id
 start with bom_trees.parent_item_code =
            'C.HP.MZL1.BSW.250-040YB-KJ01-28065' --父节点物料编码
 order by bom_level;