Oracle Metalink Note:332133.1
The BOM exploder userexit is run (bompexpl.exploder_userexit) and seems to complete successfully.
The exploder is supposed to explode a bill into the BOM_EXPLOSION_TEMP table. However, the BOM_EXPLOSION_TEMP table is empty.
Code used to call the exploder:
--
-- BOM Exploder Userexit Example
-- Calls the bompexpl.exploder_userexit to explode a single item in a single org
-- Change v_item and v_org to suitable values
--
set serveroutput on
declare
v_item varchar2(240) := 'AS54888'; -- item to explode
v_org varchar2(3) := 'M1'; -- org in which item is exploded
v_cnt NUMBER := 0;
v_err_msg varchar2(240);
v_err_code NUMBER := 0;
v_verify_flag NUMBER := 0; -- DEFAULT 0
v_online_flag NUMBER := 2; -- DEFAULT 0
v_item_id NUMBER := 0; -- set to inventory_item_id of item to explode
v_org_id NUMBER := 0; -- set to organization_id of item to explode
v_alternate VARCHAR2(240) := NULL; -- DEFAULT null
v_list_id NUMBER := 0; -- for reports (default 0)
v_order_by NUMBER := 1; -- DEFAULT 1
v_grp_id NUMBER := 0; --
v_session_id NUMBER := 0; -- DEFAULT 0
v_req_id NUMBER := 0; -- DEFAULT 0
v_prgm_appl_id NUMBER := -1; -- DEFAULT -1
v_prgm_id NUMBER := -1; -- DEFAULT -1
v_levels_to_explode NUMBER := 1; -- DEFAULT 1
v_bom_or_eng NUMBER := 1; -- DEFAULT 1
v_impl_flag NUMBER := 1; -- DEFAULT 1
v_plan_factor_flag NUMBER := 2; -- DEFAULT 2
v_incl_lt_flag NUMBER := 2; -- DEFAULT 2
v_explode_option NUMBER := 2; -- DEFAULT 2
v_module NUMBER := 2; -- DEFAULT 2
v_cst_type_id NUMBER := 0; -- DEFAULT 0
v_std_comp_flag NUMBER := 0; -- DEFAULT 0
v_rev_date VARCHAR2(240); --
v_comp_code VARCHAR2(240) := NULL; --
v_expl_qty NUMBER := 1; -- DEFAULT 1
begin
-- item revision will be based on this explode date.
-- In this example, we use current date/time
v_rev_date := to_char(SYSDATE);
-- Find org_id
select mp.organization_id into v_org_id
from MTL_PARAMETERS mp
where mp.organization_code = v_org;
-- Find item_id
select inventory_item_id into v_item_id
from MTL_ITEM_FLEXFIELDS
where organization_id = v_org_id and item_number = v_item;
-- v_grp_id is a unique identifier for this run of the exploder
select bom_explosion_temp_s.nextval into v_grp_id from dual;
-- determine maximum levels to explode from bom_explosions
select maximum_bom_level into v_levels_to_explode
from bom_parameters where organization_id = v_org_id;
apps.bompexpl.exploder_userexit (
v_verify_flag,
v_org_id,
v_order_by,
v_grp_id,
v_session_id,
v_levels_to_explode,
v_bom_or_eng,
v_impl_flag,
v_plan_factor_flag,
v_explode_option,
v_module,
v_cst_type_id,
v_std_comp_flag,
v_expl_qty,
v_item_id,
v_alternate,
v_comp_code,
v_rev_date,
v_err_msg,
v_err_code);
if ( v_err_code <> 0 ) then
rollback;
dbms_output.put_line('ERROR: '
v_err_msg);
else
select count(*) into v_cnt from bom_explosion_temp where group_id=v_grp_id;
dbms_output.put_line('Count='
v_cnt);
commit;
dbms_output.put_line('.');
dbms_output.put_line('Group Id='
v_grp_id);
dbms_output.put_line('Org ='
v_org);
dbms_output.put_line('Item ='
v_item);
dbms_output.put_line('Ord Id ='
v_org_id);
dbms_output.put_line('Item Id='
v_item_id);
dbms_output.put_line('Levels ='
v_levels_to_explode);
end if;
end;
/
Cause
The BOM_EXPLOSION_TEMP table is a global temporary table. Data written to the table is automatically
removed after the SQL*PLUS session terminates. In addition, the data written in one SQL*PLUS session is
not available to any other SQL*PLUS session.
Solution
In the same SQL*PLUS session in which the exploder userexit is run, copy the data from the BOM_EXPLOSION_TEMP
table to another user created temporary table since the data in the BOM_EXPLOSION_TEMP table is only available to
the session in which you run the exploder and is deleted afterward. For example:
Start SQL*PLUS
Create a new table to hold the BOM_EXPLOSION_TEMP data
SQL> create table BOM_EXPLOSION_TEMP_SAVE as select * from BOM_EXPLOSION_TEMP;
Run the exploder in this same session
SQL> @<
Copy the explosion data created by the exploder userexit into the new table you created
SQL> insert into BOM_EXPLOSION_TEMP_SAVE select * from BOM_EXPLOSION_TEMP;
SQL> commit;
Exit SQL*PLUS
When you exit, all data created for this session is automatically deleted from BOM_EXPLOSION_TEMP
because it is a global temporary table.
Your explosion data should now reside in the BOM_EXPLOSION_TEMP_SAVE table.
發表文章
