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;
------------------------------------------------------------------------------------------------
那么当我们反过来,知道其中一个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;
/
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/