本文介绍了一个更新销售订单的API示例代码，包括如何设置更新标识、循环更新行信息等关键步骤，并提供了完整的PL/SQL过程代码。
更新销售订单和创建销售订单差不多，调用的API相同，只是传入的时候标识不一样：operation := oe_globals.g_opr_update
示例代码如下：
PROCEDURE update_so_api(p_return_code OUT VARCHAR2, p_return_msg OUT VARCHAR2) IS l_header_rec oe_order_pub.header_rec_type; l_line_tbl oe_order_pub.line_tbl_type; l_action_request_tbl oe_order_pub.request_tbl_type; l_header_adj_tbl oe_order_pub.header_adj_tbl_type; l_line_adj_tbl oe_order_pub.line_adj_tbl_type; l_header_scr_tbl oe_order_pub.header_scredit_tbl_type; l_line_scredit_tbl oe_order_pub.line_scredit_tbl_type; l_request_rec oe_order_pub.request_rec_type; l_return_status VARCHAR2(1000); l_msg_count NUMBER; l_msg_data VARCHAR2(1000); p_api_version_number NUMBER := 1.0; p_init_msg_list VARCHAR2(10) := fnd_api.g_false; p_return_values VARCHAR2(10) := fnd_api.g_false; p_action_commit VARCHAR2(10) := fnd_api.g_false; x_return_status VARCHAR2(1); x_msg_count NUMBER; x_msg_data VARCHAR2(100); p_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec; p_old_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec; p_header_val_rec oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec; p_old_header_val_rec oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec; p_header_adj_tbl oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl; p_old_header_adj_tbl oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl; p_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl; p_old_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl; p_header_price_att_tbl oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl; p_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl; p_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl; p_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl; p_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl; p_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl; p_header_scredit_tbl oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl; p_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl; p_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl; p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl; p_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl; p_old_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl; p_line_val_tbl oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl; p_old_line_val_tbl oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl; p_line_adj_tbl oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl; p_old_line_adj_tbl oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl; p_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl; p_old_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl; p_line_price_att_tbl oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl; p_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl; p_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl; p_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl; p_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl; p_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl; p_line_scredit_tbl oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl; p_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl; p_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl; p_old_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl; p_lot_serial_tbl oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl; p_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl; p_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl; p_old_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl; p_action_request_tbl oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl; l_header_rec_out oe_order_pub.header_rec_type; l_line_tbl_out oe_order_pub.line_tbl_type; x_header_val_rec oe_order_pub.header_val_rec_type; x_header_adj_tbl oe_order_pub.header_adj_tbl_type; x_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type; x_header_price_att_tbl oe_order_pub.header_price_att_tbl_type; x_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type; x_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type; x_header_scredit_tbl oe_order_pub.header_scredit_tbl_type; x_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type; x_line_val_tbl oe_order_pub.line_val_tbl_type; x_line_adj_tbl oe_order_pub.line_adj_tbl_type; x_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type; x_line_price_att_tbl oe_order_pub.line_price_att_tbl_type; x_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type; x_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type; x_line_scredit_tbl oe_order_pub.line_scredit_tbl_type; x_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type; x_lot_serial_tbl oe_order_pub.lot_serial_tbl_type; x_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type; x_action_request_tbl oe_order_pub.request_tbl_type; x_debug_file VARCHAR2(100); l_line_tbl_index NUMBER; l_msg_index_out NUMBER(10); v_return_code VARCHAR2(10) := ''; v_return_msg VARCHAR2(2000) := ''; v_c NUMBER := 1; l_user_id NUMBER; l_resp_id NUMBER; l_application_id NUMBER; CURSOR c_so_line(c_header_id NUMBER) IS SELECT line_id, attribute5 FROM oe_order_lines_all ooll WHERE ooll.header_id = c_header_id; BEGIN --初始化SO環境變量 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_vl WHERE responsibility_name = l_resp_name; --oe_msg_pub.initialize; fnd_global.apps_initialize(user_id => /*fnd_global.user_id,*/ l_user_id, resp_id => /*fnd_global.resp_id,*/ l_resp_id, resp_appl_id => /*fnd_global.resp_appl_id*/ l_application_id); mo_global.init('ONT'); mo_global.set_policy_context('S', 349); --OU_ID END; --初始化環境變量 END -- /* **说明：更新头信息 */ l_header_rec := oe_order_pub.g_miss_header_rec; l_header_rec.header_id := p_so_header_id; --需要更新的头 ID l_header_rec.flow_status_code := 'BOOKED'; --需要更新的头字段 l_header_rec.operation := oe_globals.g_opr_update; --更新标识 /* **说明：可以循环更新行，循环行号：v_c */ FOR v_so_line IN c_so_line(p_so_header_id) LOOP l_line_tbl(v_c) := oe_order_pub.g_miss_line_rec; --Initialize record to missing l_line_tbl(v_c).line_id := v_so_line.line_id; --需要更新的行ID l_line_tbl(v_c).attribute6 := NULL; --需要更新的字段 l_line_tbl(v_c).operation := oe_globals.g_opr_update; --更新标识 v_c := v_c + 1; --在更新多条时：计数行号 END LOOP; -- CALL TO PROCESS orDER oe_order_pub.process_order(p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_true, --是否每次初始化返回信息的list：fnd_api.g_true (TRUE则每调用一次清空，false则不清空) p_return_values => fnd_api.g_false, p_action_commit => fnd_api.g_false, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_header_rec => l_header_rec, --输入：头信息 p_line_tbl => l_line_tbl, --输入：行信息 p_action_request_tbl => l_action_request_tbl, -- OUT PARAMETERS x_header_rec => l_header_rec_out, --输出：头信息；注意：不能和输入的头信息参数一样，必须用新变量 x_header_val_rec => x_header_val_rec, x_header_adj_tbl => x_header_adj_tbl, x_header_adj_val_tbl => x_header_adj_val_tbl, x_header_price_att_tbl => x_header_price_att_tbl, x_header_adj_att_tbl => x_header_adj_att_tbl, x_header_adj_assoc_tbl => x_header_adj_assoc_tbl, x_header_scredit_tbl => x_header_scredit_tbl, x_header_scredit_val_tbl => x_header_scredit_val_tbl, x_line_tbl => l_line_tbl_out, --输出：行信息；注意：不能和输入的行信息参数一样，必须用新变量 x_line_val_tbl => x_line_val_tbl, x_line_adj_tbl => x_line_adj_tbl, x_line_adj_val_tbl => x_line_adj_val_tbl, x_line_price_att_tbl => x_line_price_att_tbl, x_line_adj_att_tbl => x_line_adj_att_tbl, x_line_adj_assoc_tbl => x_line_adj_assoc_tbl, x_line_scredit_tbl => x_line_scredit_tbl, x_line_scredit_val_tbl => x_line_scredit_val_tbl, x_lot_serial_tbl => x_lot_serial_tbl, x_lot_serial_val_tbl => x_lot_serial_val_tbl, x_action_request_tbl => l_action_request_tbl); -- Check the return status IF l_return_status <> fnd_api.g_ret_sts_success THEN v_return_code := l_return_status;
v_return_msg := NULL; --初始化信息 -- Retrieve messages FOR i IN 1 .. l_msg_count LOOP oe_msg_pub.get(p_msg_index => i, p_encoded => fnd_api.g_false, p_data => l_msg_data, p_msg_index_out => l_msg_index_out); dbms_output.put_line('message is: ' || l_msg_data); v_return_msg := v_return_msg || l_msg_data; END LOOP; p_return_code := v_return_code; p_return_msg := v_return_msg; ELSE p_return_code := l_return_status; p_return_msg := '更新销售订单成功！'; END IF; EXCEPTION WHEN OTHERS THEN v_return_code := 'E'; v_return_msg := '更新銷售訂單行出現異常：' || SQLCODE || ':' || SQLERRM; p_return_code := v_return_code; p_return_msg := v_return_msg; dbms_output.put_line(v_return_code || v_return_msg); END;
扫一扫
5294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
