Oracle EBS BOM 通过ECO批量新增或者更新资源

通过ECO来新建或者更新BOM Routing对基础数据的要求很高而且总会出些莫名其妙的问题，本人就发现同一批数据没有做任何操作通过多次执行的方式就跑过去了，如果实在执行不过去可以通过控制数据量来执行程序，小批量多次的方式，本身数量量大了之后这个API执行的时间会非常久，如果执行很久后失败就很蛋疼

新增、更新、失效通过字段acd_type来控制（Add:1 Change:2 Disable:3）

以下代码供参考

--ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; DECLARE l_eco_rec eng_eco_pub.eco_rec_type := eng_eco_pub.g_miss_eco_rec; l_eco_revision_tbl eng_eco_pub.eco_revision_tbl_type := eng_eco_pub.g_miss_eco_revision_tbl; l_revised_item_tbl eng_eco_pub.revised_item_tbl_type := eng_eco_pub.g_miss_revised_item_tbl; l_rev_component_tbl bom_bo_pub.rev_component_tbl_type := eng_eco_pub.g_miss_rev_component_tbl; l_sub_component_tbl bom_bo_pub.sub_component_tbl_type := eng_eco_pub.g_miss_sub_component_tbl; l_ref_designator_tbl bom_bo_pub.ref_designator_tbl_type := eng_eco_pub.g_miss_ref_designator_tbl; l_rev_operation_tbl bom_rtg_pub.rev_operation_tbl_type := eng_eco_pub.g_miss_rev_operation_tbl; l_rev_op_resource_tbl bom_rtg_pub.rev_op_resource_tbl_type := eng_eco_pub.g_miss_rev_op_resource_tbl; l_rev_sub_resource_tbl bom_rtg_pub.rev_sub_resource_tbl_type := eng_eco_pub.g_miss_rev_sub_resource_tbl; -- API output variables x_eco_rec eng_eco_pub.eco_rec_type := eng_eco_pub.g_miss_eco_rec; x_eco_revision_tbl eng_eco_pub.eco_revision_tbl_type := eng_eco_pub.g_miss_eco_revision_tbl; x_revised_item_tbl eng_eco_pub.revised_item_tbl_type := eng_eco_pub.g_miss_revised_item_tbl; x_rev_component_tbl bom_bo_pub.rev_component_tbl_type := eng_eco_pub.g_miss_rev_component_tbl; x_sub_component_tbl bom_bo_pub.sub_component_tbl_type := eng_eco_pub.g_miss_sub_component_tbl; x_ref_designator_tbl bom_bo_pub.ref_designator_tbl_type := eng_eco_pub.g_miss_ref_designator_tbl; x_rev_operation_tbl bom_rtg_pub.rev_operation_tbl_type := eng_eco_pub.g_miss_rev_operation_tbl; x_rev_op_resource_tbl bom_rtg_pub.rev_op_resource_tbl_type := eng_eco_pub.g_miss_rev_op_resource_tbl; x_rev_sub_resource_tbl bom_rtg_pub.rev_sub_resource_tbl_type := eng_eco_pub.g_miss_rev_sub_resource_tbl; l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); l_error_table error_handler.error_tbl_type; l_message_text VARCHAR2(2000); i NUMBER; l_user_id NUMBER; l_application_id NUMBER; l_resp_id NUMBER; l_user_name VARCHAR2(30) := 'SYSADMIN'; -- YOUR USER l_resp_name VARCHAR2(30) := 'HCP_MDM_ITEM_MST_ADMIN'; -- YOUR RESP NAME v_eco_name VARCHAR2(50); org_code VARCHAR2(5) := 'MXH'; -- Organization Code l_organization_id NUMBER; l_cnt NUMBER; l_op_cnt NUMBER; l_op_res_cnt NUMBER; l_batch_no NUMBER := 20251215001; /*l_seq_from NUMBER; l_seq_to NUMBER;*/ x_return_status VARCHAR2(10); CURSOR c_routings(p_batch_no IN NUMBER) IS SELECT c.item_number ,c.alternate_routing_designator ,c.organization_id ,ood.organization_code FROM cux.cux_bom_routings_upd c ,org_organization_definitions ood WHERE c.organization_id = ood.organization_id AND c.batch_no = p_batch_no --AND c.seq_num > 50 --AND c.seq_num <= 52 AND c.routing_sequence_id IS NOT NULL AND c.operation_sequence_id IS NOT NULL AND c.resource_seq_num IS NOT NULL AND c.item_number NOT IN ('1038797140069' ,'2099307140089') GROUP BY c.item_number ,c.alternate_routing_designator ,c.organization_id ,ood.organization_code; CURSOR c_routings_op(p_batch_no IN NUMBER ,p_item IN VARCHAR2 ,p_organization_id IN VARCHAR2 ,p_alternate_routing_designator IN VARCHAR2) IS SELECT DISTINCT c.item_number ,c.alternate_routing_designator ,ood.organization_code ,c.organization_id ,c.operation_seq_num ,c.department_code ,bos.effectivity_date ,bos.option_dependent_flag FROM cux.cux_bom_routings_upd c ,org_organization_definitions ood ,bom_operation_sequences bos WHERE c.organization_id = ood.organization_id AND c.item_number = p_item AND nvl(c.alternate_routing_designator ,'-xx') = nvl(p_alternate_routing_designator ,'-xx') AND c.organization_id = p_organization_id AND c.batch_no = p_batch_no --AND c.seq_num > 50 --AND c.seq_num <= 52 AND c.routing_sequence_id IS NOT NULL AND c.operation_sequence_id IS NOT NULL AND c.resource_seq_num IS NOT NULL AND c.operation_sequence_id = bos.operation_sequence_id; CURSOR c_routings_op_res(p_batch_no IN NUMBER ,p_item IN VARCHAR2 ,p_organization_id IN VARCHAR2 ,p_alternate_routing_designator IN VARCHAR2 ,p_op_seq_no IN NUMBER) IS SELECT DISTINCT c.item_number ,c.alternate_routing_designator ,ood.organization_code ,c.operation_seq_num ,bos.effectivity_date ,c.resource_seq_num -- ,c.basis_type ,c.resource_code ,c.usage_rate_or_amount FROM cux.cux_bom_routings_upd c ,org_organization_definitions ood ,bom_operation_sequences bos WHERE c.organization_id = ood.organization_id AND c.item_number = p_item AND c.operation_seq_num = p_op_seq_no AND nvl(c.alternate_routing_designator ,'-xx') = nvl(p_alternate_routing_designator ,'-xx') AND c.organization_id = p_organization_id AND c.batch_no = p_batch_no --AND c.seq_num > 50 --AND c.seq_num <= 52 AND c.routing_sequence_id IS NOT NULL AND c.operation_sequence_id IS NOT NULL AND c.resource_seq_num IS NOT NULL AND c.operation_sequence_id = bos.operation_sequence_id; BEGIN -- Get the user_id SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = l_user_name; -- Get the application_id and responsibility_id SELECT application_id ,responsibility_id INTO l_application_id ,l_resp_id FROM fnd_responsibility WHERE responsibility_key = l_resp_name; fnd_global.apps_initialize(user_id => l_user_id ,resp_id => l_resp_id ,resp_appl_id => l_application_id); error_handler.initialize; SELECT ood.organization_id INTO l_organization_id FROM org_organization_definitions ood WHERE ood.organization_code = org_code; bompcoan.bom_eco_autonumber(p_user_id => l_user_id ,p_organization_id => l_organization_id ,p_mode => 1 ,p_prefix => v_eco_name ,x_return_status => x_return_status); dbms_output.put_line('ECO Name:' || v_eco_name); -- change order l_eco_rec.eco_name := v_eco_name; l_eco_rec.organization_code := org_code; l_eco_rec.change_type_code := 'Manufacturing'; l_eco_rec.description := 'Mexico Standard Cost update 2026'; l_eco_rec.approval_list_name := NULL; l_eco_rec.approval_status_name := 'Approved'; l_eco_rec.plm_or_erp_change := 'ERP'; l_eco_rec.status_name := 'Open'; l_eco_rec.transaction_type := 'CREATE'; l_eco_rec.return_status := NULL; l_cnt := 1; l_op_cnt := 1; l_op_res_cnt := 1; FOR r_routings IN c_routings(l_batch_no) LOOP -- revised items l_revised_item_tbl(l_cnt).eco_name := l_eco_rec.eco_name; l_revised_item_tbl(l_cnt).organization_code := l_eco_rec.organization_code; l_revised_item_tbl(l_cnt).revised_item_name := r_routings.item_number; l_revised_item_tbl(l_cnt).alternate_bom_code := nvl(r_routings.alternate_routing_designator ,fnd_api.g_null_char); l_revised_item_tbl(l_cnt).start_effective_date := SYSDATE; l_revised_item_tbl(l_cnt).status_type := 1; l_revised_item_tbl(l_cnt).transaction_type := 'CREATE'; -- routings operation FOR r_routings_op IN c_routings_op(l_batch_no ,r_routings.item_number ,r_routings.organization_id ,r_routings.alternate_routing_designator) LOOP l_rev_operation_tbl(l_op_cnt).eco_name := l_eco_rec.eco_name; l_rev_operation_tbl(l_op_cnt).organization_code := l_eco_rec.organization_code; l_rev_operation_tbl(l_op_cnt).revised_item_name := r_routings.item_number; l_rev_operation_tbl(l_op_cnt).alternate_routing_code := nvl(r_routings.alternate_routing_designator ,fnd_api.g_null_char); l_rev_operation_tbl(l_op_cnt).old_start_effective_date := r_routings_op.effectivity_date; l_rev_operation_tbl(l_op_cnt).start_effective_date := SYSDATE; --l_rev_operation_tbl(l_op_cnt).old_operation_sequence_number := 10; l_rev_operation_tbl(l_op_cnt).operation_sequence_number := r_routings_op.operation_seq_num; l_rev_operation_tbl(l_op_cnt).option_dependent_flag := r_routings_op.option_dependent_flag; --l_rev_operation_tbl(l_op_cnt).reference_flag := 2; --l_rev_operation_tbl(l_op_cnt).Standard_Operation_Code := 'T600'; --l_rev_operation_tbl(l_op_cnt).department_code := r_routings_op.depcode; -- Add:1 Change:2 Disable:3 l_rev_operation_tbl(l_op_cnt).acd_type := 2; l_rev_operation_tbl(l_op_cnt).transaction_type := 'CREATE'; -- transaction type : CREATE / UPDATE -- routings operation resource FOR r_routings_op_res IN c_routings_op_res(l_batch_no ,r_routings.item_number ,r_routings.organization_id ,r_routings.alternate_routing_designator ,r_routings_op.operation_seq_num) LOOP l_rev_op_resource_tbl(l_op_res_cnt).eco_name := l_eco_rec.eco_name; l_rev_op_resource_tbl(l_op_res_cnt).organization_code := l_eco_rec.organization_code; l_rev_op_resource_tbl(l_op_res_cnt).revised_item_name := r_routings.item_number; l_rev_op_resource_tbl(l_op_res_cnt).alternate_routing_code := nvl(r_routings.alternate_routing_designator ,fnd_api.g_null_char); l_rev_op_resource_tbl(l_op_res_cnt).operation_sequence_number := r_routings_op.operation_seq_num; l_rev_op_resource_tbl(l_op_res_cnt).resource_sequence_number := r_routings_op_res.resource_seq_num; l_rev_op_resource_tbl(l_op_res_cnt).op_start_effective_date := SYSDATE; --l_rev_op_resource_tbl(l_op_res_cnt).Basis_Type := r_routings_op_res.basis_type; l_rev_op_resource_tbl(l_op_res_cnt).resource_code := r_routings_op_res.resource_code; l_rev_op_resource_tbl(l_op_res_cnt).usage_rate_or_amount := r_routings_op_res.usage_rate_or_amount; --l_rev_op_resource_tbl(l_op_res_cnt).assigned_units := r_routings_op_res.变更后assignedunits; -- Add:1 Change:2 Disable:3 l_rev_op_resource_tbl(l_op_res_cnt).acd_type := 1; l_rev_op_resource_tbl(l_op_res_cnt).transaction_type := 'CREATE'; l_op_res_cnt := l_op_res_cnt + 1; END LOOP; l_op_cnt := l_op_cnt + 1; END LOOP; l_cnt := l_cnt + 1; END LOOP; dbms_output.put_line('Comes before process_eco call'); -- Call the private API eng_eco_pub.process_eco(p_api_version_number => 1.0 ,p_init_msg_list => TRUE ,x_return_status => l_return_status ,x_msg_count => l_msg_count ,p_bo_identifier => 'ECO' ,p_eco_rec => l_eco_rec ,p_eco_revision_tbl => l_eco_revision_tbl ,p_revised_item_tbl => l_revised_item_tbl ,p_rev_component_tbl => l_rev_component_tbl ,p_ref_designator_tbl => l_ref_designator_tbl ,p_sub_component_tbl => l_sub_component_tbl ,p_rev_operation_tbl => l_rev_operation_tbl ,p_rev_op_resource_tbl => l_rev_op_resource_tbl ,p_rev_sub_resource_tbl => l_rev_sub_resource_tbl ,x_eco_rec => x_eco_rec ,x_eco_revision_tbl => x_eco_revision_tbl ,x_revised_item_tbl => x_revised_item_tbl ,x_rev_component_tbl => x_rev_component_tbl ,x_ref_designator_tbl => x_ref_designator_tbl ,x_sub_component_tbl => x_sub_component_tbl ,x_rev_operation_tbl => x_rev_operation_tbl ,x_rev_op_resource_tbl => x_rev_op_resource_tbl ,x_rev_sub_resource_tbl => x_rev_sub_resource_tbl ,p_debug => 'N' ,p_output_dir => '/usr/tmp' ,p_debug_filename => 'ECO_BO_ROUTINGS_Debug.log'); -- error_handler.get_message_list(x_message_list => l_error_table); FOR i IN 1 .. l_error_table.count LOOP dbms_output.put_line('Entity Id:' || l_error_table(i).entity_id); dbms_output.put_line('Index:' || l_error_table(i).entity_index); dbms_output.put_line('Mesg:' || l_error_table(i).message_text); dbms_output.put_line('---------------------------------------'); END LOOP; dbms_output.put_line('Total Messages:' || to_char(i)); l_msg_count := error_handler.get_message_count; dbms_output.put_line('Message Count Function:' || to_char(l_msg_count)); error_handler.dump_message_list; error_handler.get_entity_message(p_entity_id => 'ECO' ,x_message_list => l_error_table); error_handler.get_entity_message(p_entity_id => 'REV' ,x_message_list => l_error_table); error_handler.get_entity_message(p_entity_id => 'RI' ,x_message_list => l_error_table); error_handler.get_entity_message(p_entity_id => 'RC' ,x_message_list => l_error_table); error_handler.get_entity_message(p_entity_id => 'SC' ,x_message_list => l_error_table); error_handler.get_entity_message(p_entity_id => 'RD' ,x_message_list => l_error_table); IF l_return_status = 'S' THEN --COMMIT; dbms_output.put_line('SUCCESS'); ELSE --ROLLBACK; dbms_output.put_line('ERROR'); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLCODE || '.' || SQLERRM); END;