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;
Have a Good Day.
superd..
ReplyDeleteNice post. Thank you for the help.
ReplyDeleteTHANK YOU
ReplyDeletehi do u have table script for BOM_SMALL_EXPL_TEMP
ReplyDelete