续前一篇,重写一下由子节点向父节点查询语句。
子节点向父节点查询:
select level bom_level, --等级
bom_trees.parent_item_id, --父节点
bom_trees.parent_item_code, --父节点物料编码
bom_trees.parent_item_name, --父节点物料说明
bom_trees.sub_item_id, --子节点
bom_trees.sub_item_code, --子节点物料编码
bom_trees.sub_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.parent_item_id = bom_trees.sub_item_id
start with bom_trees.sub_item_code = 'YBC.DA020-04' --子节点物料编码
order by bom_level;