BOMツリー-子ノードから親ノードへのクエリー
1808 ワード
前編に続き、サブノードから親ノードへのクエリー文を書き直します.
子ノードから親ノードへのクエリー:
子ノードから親ノードへのクエリー:
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;