调用ORACLE ERP标准过程展开BOM

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; 
/