如何展開BOM表

如何使用Oracle 官方的 API 展開 BOM 表

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.