oracle展bom逻辑,调用ORACLE ERP标准过程展开BOM

转自：  几番周折终于实现了ORACLE ERP BOM的完美展开

Subject:

Bompexpl.exploder_userexit Does Not Seem To Populate the BOM_EXPLOSION_TEMP Table

:

Note:332133.1

Type:

PROBLEM

Last Revision Date:

24-AUG-2005

Status:

PUBLISHED

if (typeof(top.query) != "undefined")

top.query.document.forms[0].p_text.value=s;

}

In this Document

Applies to:

Oracle Bills of Material - Version: 11.5.9

This problem can occur on any platform.

bompexpl.exploder_userexit

Symptoms

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.