2025年整理BOM树型查询含查找上级及顶级产品

BOM 树型查询含查找上级及顶级产品--新建一个表,将结果插入到表里面 create table CUX_BOM_ID (KINDID number(20),kindname varchar(200),FATHERID number(20),ITEMD number(20))--新建一个存储过程,并将执行结果放入到CUX_BOM_ID 里面ent1, mtl.description from bom_bill_of_materials a, MTL_SYSTEM_ITEMS_B mtl where a.ASSEMBLY_ITEM_ID = mtl.inventory_item_id and a.ORGANIZATION_ID = mtl.organization_id and mtl.organization_id = 101 ) a where not exists (select * from (select mtl2.segment1, mtl2.description from bom_inventory_components bom, MTL_SYSTEM_ITEMS_B mtl2 where bom.COMPONENT_ITEM_ID = mtl2.inventory_item_id and mtl2.organization_id = 101 ) b where a.segment1 = b.segment1) and rownum<15;begin FOR emp_record in EMP_CURSOR loop insert into cux_bom_id select kindid, lpad('|---', (level - 1) * 4, ' ') || lpad('『', 2) || kindname || rpad('』', 2) kindname,fatherid,emp_record.segment1 id from (select mtl.segment1 kindid,mtl.description kindname,mtl2.segment1 fatherid from bom_bill_of_materials a, bom_inventory_components b, MTL_SYSTEM_ITEMS_B mtl, MTL_SYSTEM_ITEMS_B mtl2 where a.bill_sequence_id = b.BILL_SEQUENCE_ID and mtl.inventory_item_id=b.COMPONENT_ITEM_ID and a.ASSEMBLY_ITEM_ID=mtl2.inventory_item_id and mtl.organization_id=101 and mtl2.organization_id=101 and a.ORGANIZATION_ID=101 and mtl.invoice_enabled_flag='Y' and mtl.organization_id=a.ORGANIZATION_ID ) CONNECT BY PRIOR kindid = fatherid START WITH fatherid = emp_record.segment1 order by kindid desc; commit; end loop; end;--执行存储过程 CUX_BOMBEGINCUX_BOM;END;--查询即可运行得到的结果SELECT * FROM CUX_BOM_ID;