ORACLE EBS BOM Expansion (expanded using standard program Bompexpl.exploder_userexit)

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 (+): = To_char (sysdate, ' yyyy/mm/dd HH24:MI:SS '); --must is this
L_err_msg VARCHAR2 (250);
L_error_code number;
--l_cnt number;
Begin
--First to have a group ID
Select Bom_explosion_temp_s.nextval
Into l_grp_id
from dual;

--second to get a 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 =
Commit
G_BOM_CNT: = 0;
END IF;
End If;
COMMIT;
End
End Cux_bom_pub;