Declare
v_comp number:= 0;
cursor cur_1 is
select inventory_item_id
from mtl_system_items_b
where organization_id = <ORGANIZATION_ID>
and segment1 = <ITEM>
r1 cur_1%rowtype;
Procedure Get_top_item (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 = <ORGANIZATION_ID>
and bcb.component_item_id = 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 = <ORGANIZATION_ID>
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:' ||c_bom .item_number);
insert into tmp_ccc (a) values ( c_bom.item_number ) ;
end if;
End Loop;
End;
Begin
for r1 in cur_1 loop
Get_top_item (r1.inventory_item_id );--Component Item Id
end loop;
End;
-----------------------------------------------------------------------------------------------------------
--逆展BOM，可以自訂階層。
declare
IN_ORGANIZATION_ID NUMBER ;
IN_ITEM_ID NUMBER ;
ln_SEQUENCE_ID number;
char_date varchar2(30);
err_msg varchar2(200);
err_code varchar2(200);
begin
in_organization_id:=84;
select inventory_item_id into in_item_id
from mtl_system_items_b where organization_id=84 and segment1= <ITEM> ;
delete BOM_SMALL_IMPL_TEMP; --刪除TEMP資料
select bom_implosion_temp_s.nextval -- 取 SEQUENCE
into ln_SEQUENCE_ID
from dual;
-- 執行日期
SELECT to_char(sysdate, 'YYYY/MM/DD HH24:MI')
INTO char_date
FROM dual;
-- 逆展API
BOMPIINQ.IMPLODER_USEREXIT (
sequence_id => ln_SEQUENCE_ID,
eng_mfg_flag => 1, -- BOM
org_id => IN_ORGANIZATION_ID,
impl_flag => 1, --IMPLEMENTED_ONLY,
display_option => 1, --All
levels_to_implode => 1, --Level
item_id => IN_ITEM_ID,
impl_date => X_char_date,
unit_number_from => null,
unit_number_to => null,
err_msg => err_msg,
err_code => err_code,
organization_option => 1,
organization_hierarchy => null,
serial_number_from => null,
serial_number_to => null
);
end;
--執行OK就可以應用BOM_SMALL_IMPL_TEMP的資料了。上一階ITEM抓parent_item_id，目前的ITEM抓CURRENT_ITEM_ID
