Oracle EBS INV 库存类别组合数据的导入更新删除API

1.库存类别组合导入

DECLARE l_structure_id NUMBER; l_api_version NUMBER := 1.0; l_init_msg_list VARCHAR2(100) := fnd_api.g_false; l_commit VARCHAR2(100) := fnd_api.g_false; x_return_status VARCHAR2(20); x_errorcode NUMBER; x_msg_count NUMBER; x_msg_data VARCHAR2(4000); x_category_id NUMBER; l_category_rec inv_item_category_pub.category_rec_type; CURSOR c_data IS SELECT t.* ,t.rowid row_id FROM cux.cux_item_category_imp_temp t WHERE 1 = 1 --AND t.segment1 = '047' -- --AND t.segment2 = '18' AND t.batch_no = 20220601001 AND NOT EXISTS (SELECT 1 FROM mtl_categories_kfv m WHERE m.structure_id = 50428 AND m.concatenated_segments = t.concat_segment) AND nvl(t.status ,'xx') <> 'S'; BEGIN fnd_global.apps_initialize(user_id => 7054 ,resp_id => 50947 ,resp_appl_id => 20003); -- Initialize variables -- to get structure_id, use the following query -- select application_id, id_flex_code, id_flex_num, id_flex_structure_code from FND_ID_FLEX_STRUCTURES_VL -- where application_id=401 and id_flex_code='MCAT' and id_flex_structure_code = '&structure_code' ; -- 'PJ_UPPERCASE'; SELECT id_flex_num INTO l_structure_id FROM fnd_id_flex_structures_vl WHERE application_id = 401 AND id_flex_code = 'MCAT' AND id_flex_structure_code = 'HCP_PART_CATEGORY'; FOR r_data IN c_data LOOP l_category_rec.structure_id := l_structure_id; l_category_rec.segment1 := r_data.segment1; l_category_rec.segment2 := r_data.segment2; l_category_rec.segment3 := r_data.segment3; l_category_rec.description := r_data.concat_segment_desc; -- call API to create category --.put_line('============================================='); --dbms_output.put_line('Calling INV_ITEM_CATEGORY_PUB.Create_Category'); inv_item_category_pub.create_category(p_api_version => l_api_version ,p_init_msg_list => l_init_msg_list ,p_commit => l_commit ,x_return_status => x_return_status ,x_errorcode => x_errorcode ,x_msg_count => x_msg_count ,x_msg_data => x_msg_data ,p_category_rec => l_category_rec ,x_category_id => x_category_id); --dbms_output.put_line('==========================================='); --dbms_output.put_line('Return Status: ' || x_return_status); IF x_return_status <> 'S' THEN ROLLBACK; UPDATE cux.cux_item_category_imp_temp c SET c.status = 'E' ,c.err_msg = x_msg_data WHERE c.rowid = r_data.row_id; ELSE UPDATE cux.cux_item_category_imp_temp c SET c.status = 'S' ,c.err_msg = NULL ,c.category_id = x_category_id WHERE c.rowid = r_data.row_id; END IF; COMMIT; END LOOP; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error:' || SQLCODE || '---' || SQLERRM); ROLLBACK; END;

2.库存类别组合描述更新

DECLARE l_structure_id NUMBER; l_api_version NUMBER := 1.0; l_init_msg_list VARCHAR2(100) := fnd_api.g_false; l_commit VARCHAR2(100) := fnd_api.g_false; x_return_status VARCHAR2(20); x_errorcode NUMBER; x_msg_count NUMBER; x_msg_data VARCHAR2(4000); x_category_id NUMBER; l_category_rec inv_item_category_pub.category_rec_type; CURSOR c_data IS SELECT t.* ,t.rowid row_id FROM cux.cux_item_category_upd_temp t WHERE 1 = 1 --AND t.segment1 = '047' -- --AND t.segment2 = '18' --AND t.concat_segment = '133.06.10' AND t.category_id IS NOT NULL AND nvl(t.status ,'xx') <> 'S'; BEGIN fnd_global.apps_initialize(user_id => 7054 ,resp_id => 50947 ,resp_appl_id => 20003); -- Initialize variables -- to get structure_id, use the following query -- select application_id, id_flex_code, id_flex_num, id_flex_structure_code from FND_ID_FLEX_STRUCTURES_VL -- where application_id=401 and id_flex_code='MCAT' and id_flex_structure_code = '&structure_code' ; -- 'PJ_UPPERCASE'; /*SELECT id_flex_num INTO l_structure_id FROM fnd_id_flex_structures_vl WHERE application_id = 401 AND id_flex_code = 'MCAT' AND id_flex_structure_code = 'HCP_PART_CATEGORY';*/ FOR r_data IN c_data LOOP /*l_category_rec.structure_id := l_structure_id; l_category_rec.segment1 := r_data.segment1; l_category_rec.segment2 := r_data.segment2; l_category_rec.segment3 := r_data.segment3; l_category_rec.description := r_data.concat_segment_desc;*/ -- call API to create category --.put_line('============================================='); --dbms_output.put_line('Calling INV_ITEM_CATEGORY_PUB.Create_Category'); /*inv_item_category_pub.create_category(p_api_version => l_api_version ,p_init_msg_list => l_init_msg_list ,p_commit => l_commit ,x_return_status => x_return_status ,x_errorcode => x_errorcode ,x_msg_count => x_msg_count ,x_msg_data => x_msg_data ,p_category_rec => l_category_rec ,x_category_id => x_category_id);*/ inv_item_category_pub.update_category_description(p_api_version => l_api_version ,p_init_msg_list => l_init_msg_list ,p_commit => l_commit ,x_return_status => x_return_status ,x_errorcode => x_errorcode ,x_msg_count => x_msg_count ,x_msg_data => x_msg_data ,p_category_id => r_data.category_id ,p_description => r_data.concat_segment_desc); --dbms_output.put_line('==========================================='); --dbms_output.put_line('Return Status: ' || x_return_status); IF x_return_status <> 'S' THEN ROLLBACK; UPDATE cux.cux_item_category_upd_temp c SET c.status = 'E' ,c.err_msg = x_msg_data WHERE c.rowid = r_data.row_id; ELSE UPDATE cux.cux_item_category_upd_temp c SET c.status = 'S' ,c.err_msg = NULL --,c.category_id = x_category_id WHERE c.rowid = r_data.row_id; END IF; COMMIT; END LOOP; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error:' || SQLCODE || '---' || SQLERRM); ROLLBACK; END;

3.库存类别组合删除

DECLARE l_structure_id NUMBER; l_api_version NUMBER := 1.0; l_init_msg_list VARCHAR2(100) := fnd_api.g_false; l_commit VARCHAR2(100) := fnd_api.g_false; x_return_status VARCHAR2(20); x_errorcode NUMBER; x_msg_count NUMBER; x_msg_data VARCHAR2(4000); x_category_id NUMBER; l_category_rec inv_item_category_pub.category_rec_type; CURSOR c_data IS SELECT t.* ,t.rowid row_id FROM cux.cux_item_category_del_temp t WHERE 1 = 1 --AND t.segment1 = '047' -- --AND t.segment2 = '18' --AND t.category_id = 294678 AND t.batch_no = 20220601001 AND t.category_id IS NOT NULL AND nvl(t.status ,'xx') <> 'S'; BEGIN fnd_global.apps_initialize(user_id => 7054 ,resp_id => 50947 ,resp_appl_id => 20003); -- Initialize variables -- to get structure_id, use the following query -- select application_id, id_flex_code, id_flex_num, id_flex_structure_code from FND_ID_FLEX_STRUCTURES_VL -- where application_id=401 and id_flex_code='MCAT' and id_flex_structure_code = '&structure_code' ; -- 'PJ_UPPERCASE'; FOR r_data IN c_data LOOP -- call API to create category --.put_line('============================================='); --dbms_output.put_line('Calling INV_ITEM_CATEGORY_PUB.Create_Category'); inv_item_category_pub.delete_category(p_api_version => l_api_version ,p_init_msg_list => l_init_msg_list ,p_commit => l_commit ,x_return_status => x_return_status ,x_errorcode => x_errorcode ,x_msg_count => x_msg_count ,x_msg_data => x_msg_data ,p_category_id => r_data.category_id); --dbms_output.put_line('==========================================='); --dbms_output.put_line('Return Status: ' || x_return_status); IF x_return_status <> 'S' THEN ROLLBACK; UPDATE cux.cux_item_category_del_temp c SET c.status = 'E' ,c.err_msg = x_msg_data WHERE c.rowid = r_data.row_id; ELSE UPDATE cux.cux_item_category_del_temp c SET c.status = 'S' ,c.err_msg = NULL --,c.category_id = x_category_id WHERE c.rowid = r_data.row_id; END IF; COMMIT; END LOOP; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error:' || SQLCODE || '---' || SQLERRM); ROLLBACK; END;