BOM中，通过Component Item找到Top Item

最新推荐文章于 2022-08-26 12:52:12 发布

转载最新推荐文章于 2022-08-26 12:52:12 发布 · 360 阅读

文章标签：

#数据库

本文介绍了一种在Oracle EBS环境中，通过递归查询组件（ComponentItem）来找到其所属的顶级项（TopItem）的方法。利用PL/SQL过程实现了从给定组件ID出发，逆向追踪至顶层装配项的过程。

BOM中，通过Component Item找到Top Item

BOM我们可以通过标准的API展开（explode）查看其层次结构。

那么当我们反过来，知道其中一个Component的时候，如何找到其最顶层的Item呢？

通过如下代码，我们可以找到用到这个Component Item的所有的Top Item。（递归的用法）

Declare

v_comp number:=0;

Procedure Get_top_item (p_component_item_id number) is

   Cursor Cur_bom is

      select bom.ASSEMBLY_ITEM_ID,

                     msib.segment1 item_number

         from bom_components_b bcb,

                  bom_bill_of_materials bom,

                  mtl_system_items_b msib

         where bcb.bill_sequence_id = bom.BILL_SEQUENCE_ID

               and bom.ORGANIZATION_ID = 122

               and bcb.component_item_id = p_component_item_id

               and bcb.disable_date is null

               and bom.ORGANIZATION_ID = msib.organization_id

               and bom.ASSEMBLY_ITEM_ID = msib.inventory_item_id

               and bcb.component_quantity > 0;

Begin

   For c_bom in Cur_bom Loop

         select count(*) into v_comp from bom_components_b bcb,

                                                                     bom_bill_of_materials bom

            where bcb.bill_sequence_id = bom.BILL_SEQUENCE_ID

               and bom.ORGANIZATION_ID = 122

               and bcb.disable_date is null

               and bcb.component_quantity > 0

               and bcb.component_item_id = c_bom.ASSEMBLY_ITEM_ID

               ;

         If v_comp > 0 then

            Get_top_item (c_bom.ASSEMBLY_ITEM_ID);

         else

               dbms_output.put_line('Top Item_Number:'||c_bom.item_number);

         end if;

   End Loop;

End;
Begin

Get_top_item (42229);--Component Item Id
End;
------------------------------------------------------------------------------------------------

oracle ebs11.0.7

CREATE OR REPLACE Procedure Get_top_item (p_component_item_id number) is
begin
declare
v_comp number:=0;
      Cursor Cur_bom is
          select bom.ASSEMBLY_ITEM_ID assembly_item_id,
                      msib.segment1 item_number
           from BOM_INVENTORY_COMPONENTS bcb,
                     bom_bill_of_materials bom,
                     mtl_system_items msib
            where bcb.bill_sequence_id = bom.common_BILL_SEQUENCE_ID
                 and bom.ORGANIZATION_ID =255
                 and bcb.component_item_id =p_component_item_id
                 and bcb.disable_date is null
                 and bom.ORGANIZATION_ID = msib.organization_id
                 and bom.ASSEMBLY_ITEM_ID = msib.inventory_item_id
                and bcb.component_quantity >0;
   Begin
      For c_bom in Cur_bom Loop
             select count(*)into v_comp
    from BOM_INVENTORY_COMPONENTS bcb,
                  bom_bill_of_materials bom
              where bcb.bill_sequence_id = bom.common_BILL_SEQUENCE_ID
                   and bom.ORGANIZATION_ID =255
                   and bcb.disable_date is null
                   and bcb.component_quantity >0
                   and bcb.component_item_id = c_bom.ASSEMBLY_ITEM_ID;
            If v_comp >0then
                Get_top_item (c_bom.ASSEMBLY_ITEM_ID);
            else
    insert into bom_top_test values (c_bom.assembly_item_id,c_bom.item_number);
    commit;
             end if;
      End Loop;
   close cur_bom;
    exception
       when others then
       null;
   End;
   end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12122734/viewspace-483516/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12122734/viewspace-483516/