--Bom API會將結果放進BOM_SMALL_EXPL_TEMP，料號就找component_item_id，用量就找component_quantity
--上一階（父階）就找 assembly_item_id
declare
cursor cur1 is
select inventory_item_id,segment1 from mtl_system_items_b where segment1 =<ITEM>
and inventory_item_status_code='Active'
and item_type=<ITEM_TYPE>
and organization_id= <ORGANIZATION_ID> ;
r1 cur1%rowtype;
v_Group_Id number;
v_Error_Message varchar2(1000);
v_Error_Code varchar2(1000);
begin
delete from BOM_SMALL_EXPL_TEMP;
open cur1;
LOOP
FETCH cur1 INTO r1;
EXIT WHEN cur1%NOTFOUND;
BEGIN
--取下一個Group_id
Select Bom_Explosion_Temp_S.NextVal into v_Group_Id From dual;
Bompxinq.Exploder_Userexit(
Verify_Flag => 0,
Org_Id => <ORGANIZATION_ID>,
Order_By => 1,
Grp_Id => v_Group_Id,
Session_Id => 0,
Levels_To_Explode => 15,
Bom_Or_Eng => 1, --Bom_Or_Eng
Impl_Flag => 1,
Plan_Factor_Flag => 2,
Explode_Option => 3, -- :B_Bill_Of_Matls.Bom_Inquiry_Display_Type
Module => 2, --:B_Bill_Of_Matls.Costs,
Cst_Type_Id => 0, --:B_Bill_Of_Matls.Cost_Type_Id,
Std_Comp_Flag => 2,
Expl_Qty => 1, --:B_Bill_Of_Matls.Explosion_Quantity,
Item_Id => r1.inventory_item_id, -- :B_Bill_Of_Matls.Assembly_Item_Id,
Alt_Desg => null, --:B_Bill_Of_Matls.Alternate_Bom_Designator,
Comp_Code => null,
Unit_Number_From => null, --NVL(:B_Bill_Of_Matls.Unit_Number_From, :CONTEXT.UNIT_NUMBER_FROM),
Unit_Number_To => null, --NVL(:B_Bill_Of_Matls.Unit_Number_To, :CONTEXT.UNIT_NUMBER_TO),
Rev_Date => sysdate, -- :B_Bill_Of_Matls.Disp_Date,
Show_Rev => 1, -- yes
Material_Ctrl => 2, -- :B_Bill_Of_Matls.Material_Control,
Lead_Time => 2, --:B_Bill_Of_Matls.Lead_Time,
Err_Msg => v_Error_Message,
Error_Code => v_Error_Code);
END;
END LOOP;
CLOSE cur1;
