ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 调用ORACLE ERP标准过程展开BOM

调用ORACLE ERP标准过程展开BOM

Linux操作系统 作者:oly9109 时间:2016-03-09 15:45:37 0 删除 编辑

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

Subject: Bompexpl.exploder_userexit Does Not Seem To Populate the BOM_EXPLOSION_TEMP Table
  Doc ID: Note:332133.1 Type: PROBLEM
  Last Revision Date: 24-AUG-2005 Status: PUBLISHED
<SCRIPT. language=JavaScript. function setmlsrch (s) { if (typeof(top.query) != "undefined") top.query.document.forms[0].p_text.value=s; } </SCRIPT.

In this Document
  Symptoms
  Cause
  Solution


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:

  1. Start SQL*PLUS
  2. Create a new table to hold the BOM_EXPLOSION_TEMP data

    SQL> create table BOM_EXPLOSION_TEMP_SAVE as select * from BOM_EXPLOSION_TEMP;
  3. Run the exploder in this same session

    SQL> @<<Name_Of_Script_That_Calls_Exploder>>
  4. 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;
  5. 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.
  6. Your explosion data should now reside in the BOM_EXPLOSION_TEMP_SAVE table.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23233349/viewspace-2051898/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-01-23

  • 博文量
    17
  • 访问量
    47592