BOM树型查询含查找上级及顶级产品.doc

--新建一个表,将结果插入到表里面?create table CUX_BOM_ID (KINDID number(20),kindname varchar(200),FATHERID number(20),ITEMD number(20)) --新建一个存储过程,并将执行结果放入到CUX_BOM_ID里面 CREATE OR REPLACE procedure CUX_BOM is? cursor emp_cursor is select segment1? from (select mtl.segment1, 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 = anization_id?????????? and anization_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 anization_id = 101?????????????????? ) b where? a.segment1 = b.segment1) and rownum15;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 anization_id=101?? and anization_id=101?? and a.ORGANIZATION_ID=101?? and mtl.invoice_enabled_flag=Y?? and anization_id=a.ORGANIZATION_ID??? )?? CONNECT BY PRIOR kindid = fatherid? START WITH fatherid = emp_record.segment1??? order by kindid desc;????? commit;? end loop;? end; --执行存储过程CUX_BOM BEGIN CUX_BOM; END; --查询即可运行得到的结果 SELECT * FROM CUX_BOM_ID;