在已存在的OM订单中增加一物料:
PROCEDURE insert_new_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) := ''; l_user_id NUMBER; l_resp_id NUMBER; l_application_id NUMBER; 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 -- /* **日志打印 */ oe_msg_pub.initialize; oe_debug_pub.initialize; x_debug_file := oe_debug_pub.set_debug_mode('FILE'); oe_debug_pub.setdebuglevel(5); dbms_output.put_line('START OF NEW DEBUG'); /* **新增行 */ l_line_tbl_index := 1; --行号 -- Initialize record to missing l_line_tbl(l_line_tbl_index) := oe_order_pub.g_miss_line_rec; -- Line attributes l_line_tbl(l_line_tbl_index).header_id := 98258; --订单头 id --Mandatory fields like qty, inventory item id are to be passed l_line_tbl(l_line_tbl_index).ordered_quantity := 4; l_line_tbl(l_line_tbl_index).inventory_item_id := 149; l_line_tbl(l_line_tbl_index).ship_from_org_id := 207; l_line_tbl(l_line_tbl_index).subinventory := 'FGI'; --Operation set to Create l_line_tbl(l_line_tbl_index).operation := oe_globals.g_opr_create; -- 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;