BOM explosion api in oracle apps

BOM explosion api in oracle apps

In this post , we will be discuss about the BOM explosion api , which helps to extract the multi level boms in oracle apps. This API also help to find out the Item where used in the BOM Item. This API will insert all the BOM explosion components details in the Standard Oracle Temporary table. From that temporary table we will copy all the data rows and insert into our custom table. This BOM explosion api is very useful in finding the  components details using in the BOM items. Please find below the detail explanation of the BOM explosion api in oracle apps.

Step by step to use the BOM explosion api in oracle apps

Step1:- First Create the table BOM EXPLOSION Temporary table to hold the BOX explosion data to extract in the sql query.

create table XX_BOM_EXPLOSION_TEMP as

select * from BOM_SMALL_EXPL_TEMP

Step2:- 

create or replace Procedure XXBOM_EXPLOSION_API

( 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

delete from XX_BOM_EXPLOSION_TEMP;

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_SMALL_EXPL_TEMP temp where temp.group_id = v_group_id;

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

insert into XX_BOM_EXPLOSION_TEMP select * from BOM_SMALL_EXPL_TEMP;

commit;

End;

Step3:- 

We need to create the above procedue in the database. In this procedure we need to pass the Assembly item id for which we want to do this explode data. As below , In the seperate

PLSQL block , we need to pass the Assembly it and it will put the exploded data in the XX_BOM_EXPLOSION_TEMP. Write the SQL query to extract the data from this Temp table 'XX_BOM_EXPLOSION_TEMP'.

Begin

XXBOM_EXPLOSION_API(p_item_id);

End;

Step4:-

select * from XX_BOM_EXPLOSION_TEMP

BOM explosion api in oracle apps