- 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;