EBS Version: 11.5.8
Requirement: User need to disable bom component by uploading a csv file which containing the TOP level assemblies to be disable by adding disable_date to all the components/subassemblies.
Constraints:
1. The disabling should be done level by level, which means, after disabling upper level, the program can go to lower level to do further disabling.
2. I hate submitting a lot of 'import bom' request, it's realy ugly.
Thus I decide to use BOM_BO_PUB.PROCESS_BOM. Following is my code
FOR c IN my_cur LOOP
I := I + 1;
l_bom_header_rec.Assembly_item_name := c.assy_item_number;
l_bom_header_rec.Organization_code := d.organization_code;
l_bom_header_rec.Assembly_type := 1;
l_bom_header_rec.attribute_category := gc_header_att_cat;
l_bom_header_rec.attribute2 := d.file_name;
l_bom_header_rec.Transaction_Type := 'UPDATE';
l_bom_component_tbl(i) := bom_bo_pub.g_miss_bom_component_rec;
l_bom_component_tbl(i).transaction_type := 'UPDATE';
l_bom_component_tbl(i).organization_code := d.organization_code;
l_bom_component_tbl(i).assembly_item_name := c.assy_item_number;
l_bom_component_tbl(i).component_item_name := c.comp_item_number;
l_bom_component_tbl(i).operation_sequence_number := c.operation_seq_num;
l_bom_component_tbl(i).start_effective_date := c.eff_date;
l_bom_component_tbl(i).disable_date := d.disable_date;
l_bom_component_tbl(i).item_sequence_number := c.item_num;
l_bom_component_tbl(i).so_basis := c.so_basis;
l_bom_component_tbl(i).wip_supply_type := c.wip_supply_type;
l_bom_component_tbl(i).quantity_per_assembly := c.component_quantity;
l_bom_component_tbl(i).projected_yield := c.component_yield_factor;
l_bom_component_tbl(i).planning_percent := c.planning_factor;
l_bom_component_tbl(i).quantity_related := c.quantity_related;
l_bom_component_tbl(i).include_in_cost_rollup := c.include_in_cost_rollup;
l_bom_component_tbl(i).check_atp := c.check_atp;
l_bom_component_tbl(i).optional := c.optional;
l_bom_component_tbl(i).mutually_exclusive := c.mutually_exclusive_options;
l_bom_component_tbl(i).shipping_allowed := c.shipping_allowed;
l_bom_component_tbl(i).required_to_ship := c.required_to_ship;
l_bom_component_tbl(i).required_for_revenue := c.required_for_revenue;
l_bom_component_tbl(i).include_on_ship_docs := c.include_on_ship_docs;
l_bom_component_tbl(i).supply_subinventory := c.supply_subinventory;
l_bom_component_tbl(i).maximum_allowed_quantity := c.high_quantity;
l_bom_component_tbl(i).minimum_allowed_quantity := c.low_quantity;
l_bom_component_tbl(i).location_name := NULL;
END LOOP;
IF l_bom_component_tbl.COUNT > 0 THEN
--bom_interface(d.organization_id);
error_handler.initialize;
bom_bo_pub.process_bom
(p_bo_identifier => 'BOM'
,p_api_version_number => 1.0
,p_init_msg_list => TRUE
,p_bom_header_rec => l_bom_header_rec
,p_bom_revision_tbl => l_bom_revision_tbl
,p_bom_component_tbl => l_bom_component_tbl
,p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
,p_bom_sub_component_tbl => l_bom_sub_component_tbl
,x_bom_header_rec => l_x_bom_header_rec
,x_bom_revision_tbl => l_x_bom_revision_tbl
,x_bom_component_tbl => l_x_bom_component_tbl
,x_bom_ref_designator_tbl => l_x_bom_ref_designator_tbl
,x_bom_sub_component_tbl => l_x_bom_sub_component_tbl
,x_return_status => l_x_return_status
,x_msg_count => l_x_msg_count
,p_debug => 'N'
,p_output_dir => ''
,p_debug_filename => ''
);
END IF;
I encountered tow nasty issues:
1. process_bom tries to validate the existance of the component by organization_code, assembly_item_name, component_item_name, start_effectivity_date and operation_seq_number and item_sequence_number. Since I do disabling level by level, I explode the BOM and loop against bom_explosion_temp level by level. Initially I tried to use effectivity_date in this table, but unfortunately, the explosion program stupidly change second of effectivity_date to 00, thus it always complains the component doesn't exist in the BOM like following:
MESSAGE TEXT You cannot update or delete component C1 since it does not exist for item FG1.
MESSAGE TYPE E
MESSAGE TEXT You cannot update or delete component SA1 since it does not exist for item FG1.
MESSAGE TYPE E
MESSAGE TEXT You cannot update or delete component SA4 since it does not exist for item FG1.
MESSAGE TYPE E
MESSAGE TEXT An error occured while processing business object FG1 in organization CT1. Please check the data in this business object and re-run the import process
My solution is to join the bom_inventory_components with bom_explosion_temp using component_sequence_id and get original effectivity_date.
And later,
MESSAGE TEXT You cannot null out the item sequence number for component C1.
MESSAGE TYPE E
MESSAGE TEXT You cannot null out the quantity per assembly for component C1.
MESSAGE TYPE E
MESSAGE TEXT You cannot null out the projected yield for component C1.
MESSAGE TYPE E
MESSAGE TEXT You cannot null out the planning percent for component C1.
MESSAGE TYPE E
MESSAGE TEXT You cannot null out Quantity Related for component C1.
MESSAGE TYPE E
MESSAGE TEXT BOM_INCL_IN_CST_ROLLUP_MISSING (REVISED_COMPONENT_NAME=C1)
MESSAGE TYPE E
MESSAGE TEXT You cannot null out Check ATP for component C1.
MESSAGE TYPE E
MESSAGE TEXT The Item Sequence Number you entered for component C1 is invalid. You must enter a value between 0 and 9999.
MESSAGE TYPE E
MESSAGE TEXT The Projected Yield value for component C1 must be between zero and one.
MESSAGE TYPE E
MESSAGE TEXT Include In Cost Rollup must have a value of 1 or 2 for component C1.
MESSAGE TYPE E
MESSAGE TEXT Optional must have a value of 1 or 2 for component C1.
MESSAGE TYPE E
MESSAGE TEXT Mutually Exclusive must have a value of 1 or 2 for component C1.
MESSAGE TYPE E
MESSAGE TEXT Check ATP must have a value of 1 or 2 for component C1.
MESSAGE TYPE E
MESSAGE TEXT Shipping Allowed must have a vlaue of 1 or 2 for comopnent C1.
MESSAGE TYPE E
MESSAGE TEXT Required To Ship must have a value of 1 or 2 for comopnent C1.
MESSAGE TYPE E
MESSAGE TEXT Required For Revenue must have a value of 1 or 2 for component C1.
MESSAGE TYPE E
MESSAGE TEXT Include On Ship Docs must have a value of 1 or 2 for component C1.
MESSAGE TYPE E
MESSAGE TEXT Quantity Related must have a value of 1 or 2 for component C1.
My sollution is to populate all the attributes from the joined query which gets attributes from bom_inventory_components.