多级BOM

最新推荐文章于 2025-04-07 11:30:00 发布

最新推荐文章于 2025-04-07 11:30:00 发布 · 127 阅读

本文介绍了一种通过复杂SQL查询实现多级物料清单(BOM)的检索方法。该查询利用了Oracle数据库特性，如连接操作、子查询及递归查询等功能，实现了从顶级组件到所有下级组件的完整追溯。此技术适用于制造业等需要精细化管理物料清单的场景。

--多级BOM
SELECT chlid_items.level_num,
fmsib.segment1 parent_item,
msib.segment1 component_item,
chlid_items.bill_sequence_id
FROM mtl_system_items_b msib,
(SELECT LEVEL level_num,
bic.component_item_id,
bic.bill_sequence_id
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id IS NOT NULL
AND nvl(bic.disable_date, SYSDATE + 1) > SYSDATE
START WITH bic.bill_sequence_id = (SELECT bsb.bill_sequence_id
FROM mtl_system_items_b msib,
bom_structures_b bsb
WHERE bsb.assembly_item_id = msib.inventory_item_id
AND bsb.organization_id = msib.organization_id
AND bsb.organization_id = 108
AND bsb.assembly_item_id = 310)
CONNECT BY bic.bill_sequence_id = PRIOR
(SELECT bsb.bill_sequence_id
FROM bom_structures_b bsb
WHERE bic.component_item_id = bsb.assembly_item_id)) chlid_items,
bom_structures_b bsb,
mtl_system_items_b fmsib
WHERE msib.inventory_item_id = chlid_items.component_item_id
AND bsb.bill_sequence_id = chlid_items.bill_sequence_id
AND bsb.assembly_item_id = fmsib.inventory_item_id
AND fmsib.organization_id = bsb.organization_id
AND msib.organization_id = bsb.organization_id;