create or replace package cux_bom_pub is

PROCEDURE bom_expand_to_temp(
p_organization_id number,
p_item_id VARCHAR2,
p_levels_to_explode NUMBER);
end cux_bom_pub;

create or replace package body cux_bom_pub is
--g_session_id NUMBER;
g_bom_cnt NUMBER := 0;
PROCEDURE bom_expand_to_temp(
p_organization_id number,
p_item_id VARCHAR2,
p_levels_to_explode NUMBER) IS
-- PRAGMA AUTONOMOUS_TRANSACTION;
--v_cnt number;
l_grp_id number ;
l_session_id number ;
-- l_org_id number ;
l_levels_to_explode number;
l_module number default 2 ; -- bom = 2
l_cst_type_id number default -1 ; -- all cost = -1 else 0
l_item_id number ;
l_bom_or_eng number:=1 ;--1 bom 2 eng
l_rev_date varchar2(30):= to_char(sysdate, ‘YYYY/MM/DD HH24:MI:SS‘); --Must be this
l_err_msg varchar2(250) ;
l_error_code number ;
-- l_cnt NUMBER;
begin
-- 首先要有一个group id
select bom_explosion_temp_s.nextval
into l_grp_id
from dual ;

-- 其次要得到一个session id
select bom_explosion_temp_session_s.nextval
into l_session_id
from dual ;

-- Find item_id
l_item_id := p_item_id;
/*select inventory_item_id
into l_item_id
from mtl_system_items_b -- mtl_item_flexfields
where organization_id = p_organization_id and segment1 = p_item_no;
*/
-- determine maximum levels to explode from bom_explosions
IF p_levels_to_explode IS NULL THEN
select maximum_bom_level
into l_levels_to_explode
from bom_parameters
where organization_id = p_organization_id;
ELSE
l_levels_to_explode := p_levels_to_explode;
END IF;

delete bom.bom_explosion_temp;-- where top_item_id =l_item_id;

--delete bom_explosion_temp;
-- commit;

bompexpl.exploder_userexit(
verify_flag =>0, -- DEFAULT 0
org_id =>p_organization_id,
order_by =>1, -- DEFAULT 1
grp_id =>l_grp_id,
session_id =>l_session_id,
levels_to_explode => l_levels_to_explode,
bom_or_eng =>l_bom_or_eng,
impl_flag =>1, -- DEFAULT 1
plan_factor_flag =>2, -- DEFAULT 2
explode_option =>2, -- DEFAULT 2
module =>l_module, -- DEFAULT 2
cst_type_id =>l_cst_type_id,
std_comp_flag =>0,
expl_qty =>1, -- DEFAULT 1
item_id =>l_item_id,
alt_desg =>‘‘,
comp_code =>‘‘,
rev_date =>l_rev_date,
err_msg =>l_err_msg,
error_code =>l_error_code
);

if ( l_error_code <> 0 ) then
-- rollback;
dbms_output.put_line(‘ERROR: ‘ || l_err_msg);
else

INSERT INTO cux_explosion_temp(
top_bill_sequence_id ,
bill_sequence_id ,
organization_id ,
component_sequence_id ,
component_item_id ,
plan_level ,
extended_quantity ,
sort_order ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
group_id ,
session_id ,
select_flag ,
select_quantity ,
extend_cost_flag ,
top_alternate_designator ,
top_item_id ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
header_id ,
line_id ,
list_price ,
selling_price ,
component_yield_factor ,
item_cost ,
include_in_rollup_flag ,
based_on_rollup_flag ,
actual_cost_type_id ,
component_quantity ,
shrinkage_rate ,
so_basis ,
optional ,
mutually_exclusive_options ,
check_atp ,
shipping_allowed ,
required_to_ship ,
required_for_revenue ,
include_on_ship_docs ,
include_on_bill_docs ,
low_quantity ,
high_quantity ,
pick_components ,
primary_uom_code ,
primary_unit_of_measure ,
base_item_id ,
atp_components_flag ,
atp_flag ,
bom_item_type ,
pick_components_flag ,
replenish_to_order_flag ,
shippable_item_flag ,
customer_order_flag ,
internal_order_flag ,
customer_order_enabled_flag ,
internal_order_enabled_flag ,
so_transactions_flag ,
mtl_transactions_enabled_flag,
stock_enabled_flag ,
description ,
assembly_item_id ,
alternate_bom_designator ,
configurator_flag ,
price_list_id ,
rounding_factor ,
pricing_context ,
pricing_attribute1 ,
pricing_attribute2 ,
pricing_attribute3 ,
pricing_attribute4 ,
pricing_attribute5 ,
pricing_attribute6 ,
pricing_attribute7 ,
pricing_attribute8 ,
pricing_attribute9 ,
pricing_attribute10 ,
pricing_attribute11 ,
pricing_attribute12 ,
pricing_attribute13 ,
pricing_attribute14 ,
pricing_attribute15 ,
component_code ,
loop_flag ,
inventory_asset_flag ,
planning_factor ,
operation_seq_num ,
parent_bom_item_type ,
wip_supply_type ,
item_num ,
effectivity_date ,
disable_date ,
implementation_date ,
supply_subinventory ,
supply_locator_id ,
component_remarks ,
change_notice ,
operation_lead_time_percent ,
rexplode_flag ,
common_bill_sequence_id ,
common_organization_id ,
primary_path_flag ,
auto_request_material ,
explosion_type ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
comp_bill_seq_id ,
comp_common_bill_seq_id ,
num_col1 ,
num_col2 ,
num_col3 ,
date_col1 ,
date_col2 ,
date_col3 ,
char_col1 ,
char_col2 ,
char_col3 ,
include_in_cost_rollup ,
parent_sort_order ,
structure_type_id ,
obj_name ,
pk1_value ,
pk2_value ,
pk3_value ,
pk4_value ,
pk5_value ,
hgrid_flag ,
operation_offset ,
current_revision ,
locator ,
suggested_vendor_name ,
unit_price ,
vendor_id ,
from_end_item_unit_number ,
to_end_item_unit_number ,
from_end_item_rev_id ,
from_end_item_minor_rev_id ,
to_end_item_rev_id ,
to_end_item_minor_rev_id ,
new_component_code ,
from_bill_revision_id ,
to_bill_revision_id ,
revision_id ,
revision_label ,
effectivity_control ,
access_flag ,
eng_item_flag ,
assembly_type ,
from_object_revision_id ,
from_minor_revision_id ,
to_object_revision_id ,
to_minor_revision_id ,
object_revision_id ,
minor_revision_id ,
minor_revision_code ,
component_item_revision_id ,
component_minor_revision_id ,
bom_implementation_date ,
gtin_number ,
gtin_description ,
trade_item_descriptor ,
top_gtin_number ,
top_gtin_description ,
top_trade_item_descriptor ,
parent_gtin_number ,
parent_gtin_description ,
parent_trade_item_descriptor,
trade_item_descriptor_desc ,
gtin_publication_status ,
quantity_of_children ,
total_qty_at_next_level ,
change_id ,
acd_type ,
quantity_related ,
change_policy_value ,
exploded_date ,
exploded_unit_number ,
exploded_end_item_rev ,
exploded_option ,
basis_type)
SELECT top_bill_sequence_id,
bill_sequence_id ,
organization_id ,
component_sequence_id ,
component_item_id ,
plan_level ,
extended_quantity ,
sort_order ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
group_id ,
session_id ,
select_flag ,
select_quantity ,
extend_cost_flag ,
top_alternate_designator ,
top_item_id ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
header_id ,
line_id ,
list_price ,
selling_price ,
component_yield_factor ,
item_cost ,
include_in_rollup_flag ,
based_on_rollup_flag ,
actual_cost_type_id ,
component_quantity ,
shrinkage_rate ,
so_basis ,
optional ,
mutually_exclusive_options ,
check_atp ,
shipping_allowed ,
required_to_ship ,
required_for_revenue ,
include_on_ship_docs ,
include_on_bill_docs ,
low_quantity ,
high_quantity ,
pick_components ,
primary_uom_code ,
primary_unit_of_measure ,
base_item_id ,
atp_components_flag ,
atp_flag ,
bom_item_type ,
pick_components_flag ,
replenish_to_order_flag ,
shippable_item_flag ,
customer_order_flag ,
internal_order_flag ,
customer_order_enabled_flag ,
internal_order_enabled_flag ,
so_transactions_flag ,
mtl_transactions_enabled_flag,
stock_enabled_flag ,
description ,
assembly_item_id ,
alternate_bom_designator ,
configurator_flag ,
price_list_id ,
rounding_factor ,
pricing_context ,
pricing_attribute1 ,
pricing_attribute2 ,
pricing_attribute3 ,
pricing_attribute4 ,
pricing_attribute5 ,
pricing_attribute6 ,
pricing_attribute7 ,
pricing_attribute8 ,
pricing_attribute9 ,
pricing_attribute10 ,
pricing_attribute11 ,
pricing_attribute12 ,
pricing_attribute13 ,
pricing_attribute14 ,
pricing_attribute15 ,
component_code ,
loop_flag ,
inventory_asset_flag ,
planning_factor ,
operation_seq_num ,
parent_bom_item_type ,
wip_supply_type ,
item_num ,
effectivity_date ,
disable_date ,
implementation_date ,
supply_subinventory ,
supply_locator_id ,
component_remarks ,
change_notice ,
operation_lead_time_percent ,
rexplode_flag ,
common_bill_sequence_id ,
common_organization_id ,
primary_path_flag ,
auto_request_material ,
explosion_type ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
comp_bill_seq_id ,
comp_common_bill_seq_id ,
num_col1 ,
num_col2 ,
num_col3 ,
date_col1 ,
date_col2 ,
date_col3 ,
char_col1 ,
char_col2 ,
char_col3 ,
include_in_cost_rollup ,
parent_sort_order ,
structure_type_id ,
obj_name ,
pk1_value ,
pk2_value ,
pk3_value ,
pk4_value ,
pk5_value ,
hgrid_flag ,
operation_offset ,
current_revision ,
locator ,
suggested_vendor_name ,
unit_price ,
vendor_id ,
from_end_item_unit_number ,
to_end_item_unit_number ,
from_end_item_rev_id ,
from_end_item_minor_rev_id ,
to_end_item_rev_id ,
to_end_item_minor_rev_id ,
new_component_code ,
from_bill_revision_id ,
to_bill_revision_id ,
revision_id ,
revision_label ,
effectivity_control ,
access_flag ,
eng_item_flag ,
assembly_type ,
from_object_revision_id ,
from_minor_revision_id ,
to_object_revision_id ,
to_minor_revision_id ,
object_revision_id ,
minor_revision_id ,
minor_revision_code ,
component_item_revision_id ,
component_minor_revision_id ,
bom_implementation_date ,
gtin_number ,
gtin_description ,
trade_item_descriptor ,
top_gtin_number ,
top_gtin_description ,
top_trade_item_descriptor ,
parent_gtin_number ,
parent_gtin_description ,
parent_trade_item_descriptor,
trade_item_descriptor_desc ,
gtin_publication_status ,
quantity_of_children ,
total_qty_at_next_level ,
change_id ,
acd_type ,
quantity_related ,
change_policy_value ,
exploded_date ,
exploded_unit_number ,
exploded_end_item_rev ,
exploded_option ,
basis_type
FROM bom_explosion_temp t;-- WHERE t.session_id = l_session_id;
g_bom_cnt := g_bom_cnt + 1;
IF g_bom_cnt = 1000 THEN
commit;
g_bom_cnt := 0;
END IF;
end if;
COMMIT;
end;
end cux_bom_pub;