Exploding BOM - bompxinq.exploder_userexit

Exploding BOM - bompxinq.exploder_userexit

The main purpose of exploding BOM is to get the details from the temporary table that oracle uses to hold data. Its session based. So once you are out of that session the data is gone. BOM_SMALL_EXPL_TEMP is the table used to hold the data in a session.

Data cannot be retrived be querying the View from FORM --> HELP-->Record History.
Hence explode the BOM for the Item you needed data.

There are many ways  of holding the data.You can write to a file, print report so on..Its upto you.

In this post i used a table BOM_SMALL_EXPL_TEMP1 to hold the data. Remember its not a temporary table, its a copy of BOM_SMALL_EXPL_TEMP with all the columns.

I too found it difficult to find the values to explode BOM. So i have explored the standard oracle form to find out what columns its uses to explode. All the comments after assignments are the standard oracle Block.FieldName (Block Name -> B_BILL_OF_MATLS). So you can pass the values of your Appliction if needed.

Version : 12.0.6

create or replace Procedure EAM_BOM_EXPLODE
( p_item_id in number ) /* The BOM assembly which you want to explode */
is

v_group_id NUMBER;
x_error_message VARCHAR2 (2000);
x_error_code NUMBER;
sess_id number;
l_rec_count number;

begin

fnd_global.apps_initialize(<>,<>,<>);

delete from BOM_SMALL_EXPL_TEMP1;

commit;

SELECT bom_explosion_temp_s.NEXTVAL
INTO v_group_id
FROM DUAL;

SELECT bom_explosion_temp_session_s.NEXTVAL
INTO sess_id
FROM Dual ;

bompxinq.exploder_userexit(
Verify_Flag => 0,
Org_Id => **ORG_ID**,
Order_By => 1,--:B_Bill_Of_Matls.Bom_Bill_Sort_Order_Type,
Grp_Id => v_group_id,
Session_Id => 0,
Levels_To_Explode => 20, --:B_Bill_Of_Matls.Levels_To_Explode,
Bom_Or_Eng => 1, -- :Parameter.Bom_Or_Eng,
Impl_Flag => 1, --:B_Bill_Of_Matls.Impl_Only,
Plan_Factor_Flag => 2, --:B_Bill_Of_Matls.Planning_Percent,
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 => p_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 => 0, --NVL(:B_Bill_Of_Matls.Unit_Number_From, :CONTEXT.UNIT_NUMBER_FROM),
Unit_Number_To => 'ZZZZZZZZZZZZZZZZZ', --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 => x_error_message, --err_msg
error_code => x_error_code); --error_code

select count(*) into l_rec_count from
--BOM_EXPLOSION_TEMP temp
BOM_SMALL_EXPL_TEMP temp where temp.group_id = v_group_id;

DBMS_OUTPUT.PUT_LINE('l_rec_count = '||l_rec_count);

insert into BOM_SMALL_EXPL_TEMP1 select * from BOM_SMALL_EXPL_TEMP;

commit;

DBMS_OUTPUT.PUT_LINE(x_error_message);
DBMS_OUTPUT.PUT_LINE(x_error_code);
DBMS_OUTPUT.PUT_LINE('grp_id = '||v_group_id);
DBMS_OUTPUT.PUT_LINE('sess_id = '||sess_id);

End;

Once you are done compiling execute the following command to insert the data into the table you have created in the above case its BOM_SMALL_EXPL_TEMP1.

Begin
EAM_BOM_EXPLODE(p_item_id);
End;
/
select * from BOM_SMALL_EXPL_TEMP1;