Oracle BOM item where used query

Oracle BOM item where used query

In this post , We will be discuss about Oracle BOM item where used query. I will try to share the sql query to find out the the BOM details in which one specific item used. We can find for some specific item in which BOM this item used. This query is very usefully BOM sql query. Even oracle has given the option from the application itself to find out the Oracle BOM item where used. Directly from SQL query , its quite not possible to find out item where used details for multiple BOM. For this we need to use one API , which will first explode multiple bom details in temp table and then in the temp table we can find for some specific item  that in which boms this item is currently using.

Hoe to use Oracle BOM item where used query

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'.

declare 

cursor c is 

selct assembly_item_id from bom_structures_b where organization_id=85;

Begin

for i in c loop

XXBOM_EXPLOSION_API(i.assembly_item_id);

end lop;

End;

Step4:-

Use this below table to extract Oracle BOM item where used details.

select * from XX_BOM_EXPLOSION_TEMP