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;
/