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

《BOM树型查询含查找上级及顶级产品.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、--新建一个表,将结果插入到表里面 createtableCUX_BOM_ID(KINDIDnumber(20),kindnamevarchar(200),FATHERIDnumber(20),ITEMDnumber(20))--新建一个存储过程,并将执行结果放入到CUX_BOM_ID里面CREATEORREPLACEprocedureCUX_BOMis cursoremp_cursorisselectsegment1 from(selectmtl.segment1,mtl.description         frombom_bill_of_materialsa,MTL_SY

2、STEM_ITEMS_Bmtl        wherea.ASSEMBLY_ITEM_ID=mtl.inventory_item_id          anda.ORGANIZATION_ID=mtl.organization_id          andmtl.organization_id=101         )a wherenotexists (select*         from(selectmtl2.segment1,mtl2.description                 frombom_inventory_componentsbom,MTL_S

3、YSTEM_ITEMS_Bmtl2                wherebom.COMPONENT_ITEM_ID=mtl2.inventory_item_id                  andmtl2.organization_id=101                  )bwhere a.segment1=b.segment1)andrownum<15;begin FORemp_recordinEMP_CURSORloop  insertintocux_bom_idselectkindid,      lpad('

4、---',(level-1)*4,'')

5、

6、

7、lpad('『',2)

8、

9、kindname

10、

11、      rpad('』',2)kindname,fatherid,emp_record.segment1id   from (selectmtl.segment1kindid,mtl.descriptionkindname,mtl2.segment1fatherid frombom_bill_of_materialsa,      bom_inventory_componentsb,       MTL_SYSTEM_ITEMS_Bmtl,       MTL_SYSTEM_ITEMS_Bmtl2 wherea.bill_sequ

12、ence_id=b.BILL_SEQUENCE_ID  andmtl.inventory_item_id=b.COMPONENT_ITEM_ID  anda.ASSEMBLY_ITEM_ID=mtl2.inventory_item_id  andmtl.organization_id=101  andmtl2.organization_id=101  anda.ORGANIZATION_ID=101  andmtl.invoice_enabled_flag='Y'  andmtl.organization_id=a.ORGANIZATION_ID   )  CONNECTBYPR

13、IORkindid=fatherid STARTWITHfatherid=emp_record.segment1   orderbykindiddesc;     commit; endloop; end;--执行存储过程CUX_BOMBEGINCUX_BOM;END;--查询即可运行得到的结果SELECT*FROMCUX_BOM_ID;