日志

BOM 子物料查找所有父物料及它的顶级产品

已有 657 次阅读2008/10/17 08:56

在网上没有找到这样的应用,今天因需要写了下面的程序,主要是查找物料的上级BOM,及它所要应用到的所有产品,网上一般都是从上面找下去,而没有通过物料去查找它的顶级产品,通过这段PL/SQL,可以方便地让你找到物料应用到了哪些产品中!望各位支持一下!

--新建一个表,将结果插入到表里面
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 = 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_BOM

BEGIN

CUX_BOM;

END;

--查询即可运行得到的结果

SELECT * FROM CUX_BOM_ID;