本文只是更新了头行销售员，其他字段可自行测试，另外只有未关闭的订单行才能进行更新
DECLARE -- Non-scalar parameters require additional processing p_header_rec oe_order_pub.header_rec_type; p_old_header_rec oe_order_pub.header_rec_type; p_header_val_rec oe_order_pub.header_val_rec_type; p_old_header_val_rec oe_order_pub.header_val_rec_type; p_header_adj_tbl oe_order_pub.header_adj_tbl_type; p_old_header_adj_tbl oe_order_pub.header_adj_tbl_type; p_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type; p_old_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type; p_header_price_att_tbl oe_order_pub.header_price_att_tbl_type; p_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type; p_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type; p_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type; p_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type; p_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type; p_header_scredit_tbl oe_order_pub.header_scredit_tbl_type; p_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type; p_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type; p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type; p_header_payment_tbl oe_order_pub.header_payment_tbl_type; p_old_header_payment_tbl oe_order_pub.header_payment_tbl_type; p_header_payment_val_tbl oe_order_pub.header_payment_val_tbl_type; p_old_header_payment_val_tbl oe_order_pub.header_payment_val_tbl_type; p_line_tbl oe_order_pub.line_tbl_type; p_old_line_tbl oe_order_pub.line_tbl_type; p_line_val_tbl oe_order_pub.line_val_tbl_type; p_old_line_val_tbl oe_order_pub.line_val_tbl_type; p_line_adj_tbl oe_order_pub.line_adj_tbl_type; p_old_line_adj_tbl oe_order_pub.line_adj_tbl_type; p_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type; p_old_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type; p_line_price_att_tbl oe_order_pub.line_price_att_tbl_type; p_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type; p_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type; p_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type; p_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type; p_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type; p_line_scredit_tbl oe_order_pub.line_scredit_tbl_type; p_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type; p_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type; p_old_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type; p_line_payment_tbl oe_order_pub.line_payment_tbl_type; p_old_line_payment_tbl oe_order_pub.line_payment_tbl_type; p_line_payment_val_tbl oe_order_pub.line_payment_val_tbl_type; p_old_line_payment_val_tbl oe_order_pub.line_payment_val_tbl_type; p_lot_serial_tbl oe_order_pub.lot_serial_tbl_type; p_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type; p_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type; p_old_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type; p_action_request_tbl oe_order_pub.request_tbl_type; x_header_rec oe_order_pub.header_rec_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_header_payment_tbl oe_order_pub.header_payment_tbl_type; x_header_payment_val_tbl oe_order_pub.header_payment_val_tbl_type; x_line_tbl oe_order_pub.line_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_line_payment_tbl oe_order_pub.line_payment_tbl_type; x_line_payment_val_tbl oe_order_pub.line_payment_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; p_header_customer_info_tbl oe_order_pub.customer_info_table_type; p_line_customer_info_tbl oe_order_pub.customer_info_table_type; p_org_id NUMBER; p_operating_unit NUMBER; p_api_version_number NUMBER := 1; p_init_msg_list VARCHAR2(2000); p_return_values VARCHAR2(2000); p_action_commit VARCHAR2(2000); x_return_status VARCHAR2(2000); x_msg_count NUMBER; x_msg_data VARCHAR2(2000); l_msg_data VARCHAR2(3000); p_rtrim_data VARCHAR2(2000); p_validate_desc_flex VARCHAR2(2000); l_success_cnt NUMBER := 0; l_failed_cnt NUMBER := 0; CURSOR c_mass_upd IS SELECT t.* ,t.rowid ,h.org_id FROM cux.cux_om_mass_update_temp t ,oe_order_headers_all h WHERE 1 = 1 AND t.header_id = h.header_id --AND t.order_number = '451182452' AND nvl(t.status ,'xx') <> 'S' AND t.salesrep_id_new IS NOT NULL AND t.batch_no = 20250423001 ORDER BY t.header_id ,t.line_id; BEGIN dbms_output.put_line('Starting of script'); fnd_global.apps_initialize(user_id => 0 ,resp_id => 50947 ,resp_appl_id => 20003); mo_global.init('ONT'); dbms_output.put_line('Starting of API'); FOR r IN c_mass_upd LOOP oe_msg_pub.initialize; -- Header Record -- p_header_rec := oe_order_pub.g_miss_header_rec; p_header_rec.operation := oe_globals.g_opr_update; p_header_rec.header_id := r.header_id; -- 先头行销售员一起更新,再将失败记录行销售员更新一下 --p_header_rec.salesrep_id := r.salesrep_id_new; -- Line Record -- p_line_tbl(1) := oe_order_pub.g_miss_line_rec; p_line_tbl(1).operation := oe_globals.g_opr_update; p_line_tbl(1).line_id := r.line_id; p_line_tbl(1).salesrep_id := r.salesrep_id_new; p_org_id := r.org_id; -- Call the procedure oe_order_pub.process_order(p_org_id => p_org_id ,p_operating_unit => p_operating_unit ,p_api_version_number => p_api_version_number ,p_init_msg_list => p_init_msg_list ,p_return_values => p_return_values ,p_action_commit => p_action_commit ,x_return_status => x_return_status ,x_msg_count => x_msg_count ,x_msg_data => x_msg_data ,p_header_rec => p_header_rec ,p_old_header_rec => p_old_header_rec ,p_header_val_rec => p_header_val_rec ,p_old_header_val_rec => p_old_header_val_rec ,p_header_adj_tbl => p_header_adj_tbl ,p_old_header_adj_tbl => p_old_header_adj_tbl ,p_header_adj_val_tbl => p_header_adj_val_tbl ,p_old_header_adj_val_tbl => p_old_header_adj_val_tbl ,p_header_price_att_tbl => p_header_price_att_tbl ,p_old_header_price_att_tbl => p_old_header_price_att_tbl ,p_header_adj_att_tbl => p_header_adj_att_tbl ,p_old_header_adj_att_tbl => p_old_header_adj_att_tbl ,p_header_adj_assoc_tbl => p_header_adj_assoc_tbl ,p_old_header_adj_assoc_tbl => p_old_header_adj_assoc_tbl ,p_header_scredit_tbl => p_header_scredit_tbl ,p_old_header_scredit_tbl => p_old_header_scredit_tbl ,p_header_scredit_val_tbl => p_header_scredit_val_tbl ,p_old_header_scredit_val_tbl => p_old_header_scredit_val_tbl ,p_header_payment_tbl => p_header_payment_tbl ,p_old_header_payment_tbl => p_old_header_payment_tbl ,p_header_payment_val_tbl => p_header_payment_val_tbl ,p_old_header_payment_val_tbl => p_old_header_payment_val_tbl ,p_line_tbl => p_line_tbl ,p_old_line_tbl => p_old_line_tbl ,p_line_val_tbl => p_line_val_tbl ,p_old_line_val_tbl => p_old_line_val_tbl ,p_line_adj_tbl => p_line_adj_tbl ,p_old_line_adj_tbl => p_old_line_adj_tbl ,p_line_adj_val_tbl => p_line_adj_val_tbl ,p_old_line_adj_val_tbl => p_old_line_adj_val_tbl ,p_line_price_att_tbl => p_line_price_att_tbl ,p_old_line_price_att_tbl => p_old_line_price_att_tbl ,p_line_adj_att_tbl => p_line_adj_att_tbl ,p_old_line_adj_att_tbl => p_old_line_adj_att_tbl ,p_line_adj_assoc_tbl => p_line_adj_assoc_tbl ,p_old_line_adj_assoc_tbl => p_old_line_adj_assoc_tbl ,p_line_scredit_tbl => p_line_scredit_tbl ,p_old_line_scredit_tbl => p_old_line_scredit_tbl ,p_line_scredit_val_tbl => p_line_scredit_val_tbl ,p_old_line_scredit_val_tbl => p_old_line_scredit_val_tbl ,p_line_payment_tbl => p_line_payment_tbl ,p_old_line_payment_tbl => p_old_line_payment_tbl ,p_line_payment_val_tbl => p_line_payment_val_tbl ,p_old_line_payment_val_tbl => p_old_line_payment_val_tbl ,p_lot_serial_tbl => p_lot_serial_tbl ,p_old_lot_serial_tbl => p_old_lot_serial_tbl ,p_lot_serial_val_tbl => p_lot_serial_val_tbl ,p_old_lot_serial_val_tbl => p_old_lot_serial_val_tbl ,p_action_request_tbl => p_action_request_tbl ,x_header_rec => x_header_rec ,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_header_payment_tbl => x_header_payment_tbl ,x_header_payment_val_tbl => x_header_payment_val_tbl ,x_line_tbl => x_line_tbl ,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_line_payment_tbl => x_line_payment_tbl ,x_line_payment_val_tbl => x_line_payment_val_tbl ,x_lot_serial_tbl => x_lot_serial_tbl ,x_lot_serial_val_tbl => x_lot_serial_val_tbl ,x_action_request_tbl => x_action_request_tbl ,p_rtrim_data => p_rtrim_data ,p_validate_desc_flex => p_validate_desc_flex ,p_header_customer_info_tbl => p_header_customer_info_tbl ,p_line_customer_info_tbl => p_line_customer_info_tbl); --dbms_output.put_line('Completion of API'); --dbms_output.put_line('x_return_status: ' || x_return_status); IF x_return_status = fnd_api.g_ret_sts_success THEN UPDATE cux.cux_om_mass_update_temp c SET c.status = 'S' ,c.err_msg = NULL WHERE c.rowid = r.rowid; l_success_cnt := l_success_cnt + 1; COMMIT; /*dbms_output.put_line('Order Import Success : ' || x_header_rec.header_id);*/ ELSE /*dbms_output.put_line('Order Import failed v_msg_data:' || x_msg_data); dbms_output.put_line('Order Import failed v_msg_count:' || x_msg_count);*/ FOR i IN 1 .. oe_msg_pub.count_msg LOOP l_msg_data := oe_msg_pub.get(p_msg_index => i ,p_encoded => 'F'); --dbms_output.put_line(i || ') ' || x_msg_data); END LOOP; ROLLBACK; l_failed_cnt := l_failed_cnt + 1; UPDATE cux.cux_om_mass_update_temp c SET c.status = 'E' ,c.err_msg = l_msg_data WHERE c.rowid = r.rowid; COMMIT; END IF; END LOOP; COMMIT; dbms_output.put_line('SUCCESS Count: ' || l_success_cnt); dbms_output.put_line('FAILED Count: ' || l_failed_cnt); dbms_output.put_line('Completion of API'); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLCODE || '.' || SQLERRM); ROLLBACK; END;
