经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库运维 » Oracle » 查看文章
销售订单(SO)-API-更新销售订单
来源:cnblogs  作者:wellhuang  时间:2018/9/25 20:04:06  对本文有异议

更新销售订单和创建销售订单差不多,调用的API相同,只是传入的时候标识不一样:operation := oe_globals.g_opr_update

 

示例代码如下:

  1. PROCEDURE update_so_api(p_return_code OUT VARCHAR2,
  2. p_return_msg OUT VARCHAR2) IS
  3. l_header_rec oe_order_pub.header_rec_type;
  4. l_line_tbl oe_order_pub.line_tbl_type;
  5. l_action_request_tbl oe_order_pub.request_tbl_type;
  6. l_header_adj_tbl oe_order_pub.header_adj_tbl_type;
  7. l_line_adj_tbl oe_order_pub.line_adj_tbl_type;
  8. l_header_scr_tbl oe_order_pub.header_scredit_tbl_type;
  9. l_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
  10. l_request_rec oe_order_pub.request_rec_type;
  11. l_return_status VARCHAR2(1000);
  12. l_msg_count NUMBER;
  13. l_msg_data VARCHAR2(1000);
  14. p_api_version_number NUMBER := 1.0;
  15. p_init_msg_list VARCHAR2(10) := fnd_api.g_false;
  16. p_return_values VARCHAR2(10) := fnd_api.g_false;
  17. p_action_commit VARCHAR2(10) := fnd_api.g_false;
  18. x_return_status VARCHAR2(1);
  19. x_msg_count NUMBER;
  20. x_msg_data VARCHAR2(100);
  21. p_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
  22. p_old_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
  23. p_header_val_rec oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;
  24. p_old_header_val_rec oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;
  25. p_header_adj_tbl oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;
  26. p_old_header_adj_tbl oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;
  27. p_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;
  28. p_old_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;
  29. p_header_price_att_tbl oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;
  30. p_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;
  31. p_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;
  32. p_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;
  33. p_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;
  34. p_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;
  35. p_header_scredit_tbl oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;
  36. p_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;
  37. p_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;
  38. p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;
  39. p_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
  40. p_old_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
  41. p_line_val_tbl oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;
  42. p_old_line_val_tbl oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;
  43. p_line_adj_tbl oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;
  44. p_old_line_adj_tbl oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;
  45. p_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;
  46. p_old_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;
  47. p_line_price_att_tbl oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;
  48. p_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;
  49. p_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;
  50. p_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;
  51. p_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;
  52. p_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;
  53. p_line_scredit_tbl oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;
  54. p_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;
  55. p_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;
  56. p_old_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;
  57. p_lot_serial_tbl oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;
  58. p_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;
  59. p_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;
  60. p_old_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;
  61. p_action_request_tbl oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl;
  62. l_header_rec_out oe_order_pub.header_rec_type;
  63. l_line_tbl_out oe_order_pub.line_tbl_type;
  64. x_header_val_rec oe_order_pub.header_val_rec_type;
  65. x_header_adj_tbl oe_order_pub.header_adj_tbl_type;
  66. x_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
  67. x_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
  68. x_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
  69. x_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
  70. x_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
  71. x_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type;
  72. x_line_val_tbl oe_order_pub.line_val_tbl_type;
  73. x_line_adj_tbl oe_order_pub.line_adj_tbl_type;
  74. x_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type;
  75. x_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
  76. x_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
  77. x_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
  78. x_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
  79. x_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type;
  80. x_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
  81. x_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type;
  82. x_action_request_tbl oe_order_pub.request_tbl_type;
  83. x_debug_file VARCHAR2(100);
  84. l_line_tbl_index NUMBER;
  85. l_msg_index_out NUMBER(10);
  86. v_return_code VARCHAR2(10) := '';
  87. v_return_msg VARCHAR2(2000) := '';
  88. v_c NUMBER := 1;
  89. l_user_id NUMBER;
  90. l_resp_id NUMBER;
  91. l_application_id NUMBER;
  92. CURSOR c_so_line(c_header_id NUMBER) IS
  93. SELECT line_id, attribute5
  94. FROM oe_order_lines_all ooll
  95. WHERE ooll.header_id = c_header_id;
  96. BEGIN
  97.  
  98. --初始化SO環境變量
  99. BEGIN
  100. -- Get the user_id
  101. SELECT user_id
  102. INTO l_user_id
  103. FROM fnd_user
  104. WHERE user_name = l_user_name;
  105. -- Get the application_id and responsibility_id
  106. SELECT application_id, responsibility_id
  107. INTO l_application_id, l_resp_id
  108. FROM fnd_responsibility_vl
  109. WHERE responsibility_name = l_resp_name;
  110. --oe_msg_pub.initialize;
  111. fnd_global.apps_initialize(user_id => /*fnd_global.user_id,*/ l_user_id,
  112. resp_id => /*fnd_global.resp_id,*/ l_resp_id,
  113. resp_appl_id => /*fnd_global.resp_appl_id*/ l_application_id);
  114. mo_global.init('ONT');
  115. mo_global.set_policy_context('S', 349); --OU_ID
  116. END;
  117. --初始化環境變量 END --
  118.  
  119. /*
  120. **说明:更新头信息
  121. */
  122. l_header_rec := oe_order_pub.g_miss_header_rec;
  123. l_header_rec.header_id := p_so_header_id; --需要更新的头 ID
  124. l_header_rec.flow_status_code := 'BOOKED'; --需要更新的头字段
  125. l_header_rec.operation := oe_globals.g_opr_update; --更新标识
  126.  
  127. /*
  128. **说明:可以循环更新行,循环行号:v_c
  129. */
  130. FOR v_so_line IN c_so_line(p_so_header_id) LOOP
  131. l_line_tbl(v_c) := oe_order_pub.g_miss_line_rec; --Initialize record to missing
  132. l_line_tbl(v_c).line_id := v_so_line.line_id; --需要更新的行ID
  133. l_line_tbl(v_c).attribute6 := NULL; --需要更新的字段
  134. l_line_tbl(v_c).operation := oe_globals.g_opr_update; --更新标识
  135. v_c := v_c + 1; --在更新多条时:计数行号
  136. END LOOP;
  137. -- CALL TO PROCESS orDER
  138. oe_order_pub.process_order(p_api_version_number => 1.0,
  139. p_init_msg_list => fnd_api.g_true, --是否每次初始化返回信息的listfnd_api.g_true (TRUE则每调用一次清空,false则不清空)
  140. p_return_values => fnd_api.g_false,
  141. p_action_commit => fnd_api.g_false,
  142. x_return_status => l_return_status,
  143. x_msg_count => l_msg_count,
  144. x_msg_data => l_msg_data,
  145. p_header_rec => l_header_rec, --输入:头信息
  146. p_line_tbl => l_line_tbl, --输入:行信息
  147. p_action_request_tbl => l_action_request_tbl,
  148. -- OUT PARAMETERS
  149. x_header_rec => l_header_rec_out, --输出:头信息;注意:不能和输入的头信息参数一样,必须用新变量
  150. x_header_val_rec => x_header_val_rec,
  151. x_header_adj_tbl => x_header_adj_tbl,
  152. x_header_adj_val_tbl => x_header_adj_val_tbl,
  153. x_header_price_att_tbl => x_header_price_att_tbl,
  154. x_header_adj_att_tbl => x_header_adj_att_tbl,
  155. x_header_adj_assoc_tbl => x_header_adj_assoc_tbl,
  156. x_header_scredit_tbl => x_header_scredit_tbl,
  157. x_header_scredit_val_tbl => x_header_scredit_val_tbl,
  158. x_line_tbl => l_line_tbl_out, --输出:行信息;注意:不能和输入的行信息参数一样,必须用新变量
  159. x_line_val_tbl => x_line_val_tbl,
  160. x_line_adj_tbl => x_line_adj_tbl,
  161. x_line_adj_val_tbl => x_line_adj_val_tbl,
  162. x_line_price_att_tbl => x_line_price_att_tbl,
  163. x_line_adj_att_tbl => x_line_adj_att_tbl,
  164. x_line_adj_assoc_tbl => x_line_adj_assoc_tbl,
  165. x_line_scredit_tbl => x_line_scredit_tbl,
  166. x_line_scredit_val_tbl => x_line_scredit_val_tbl,
  167. x_lot_serial_tbl => x_lot_serial_tbl,
  168. x_lot_serial_val_tbl => x_lot_serial_val_tbl,
  169. x_action_request_tbl => l_action_request_tbl);
  170. -- Check the return status
  171. IF l_return_status <> fnd_api.g_ret_sts_success THEN
  172. v_return_code := l_return_status;
    v_return_msg := NULL; --初始化信息
  173. -- Retrieve messages
  174. FOR i IN 1 .. l_msg_count LOOP
  175. oe_msg_pub.get(p_msg_index => i,
  176. p_encoded => fnd_api.g_false,
  177. p_data => l_msg_data,
  178. p_msg_index_out => l_msg_index_out);
  179. dbms_output.put_line('message is: ' || l_msg_data);
  180. v_return_msg := v_return_msg || l_msg_data;
  181. END LOOP;
  182. p_return_code := v_return_code;
  183. p_return_msg := v_return_msg;
  184. ELSE
  185. p_return_code := l_return_status;
  186. p_return_msg := '更新销售订单成功!';
  187. END IF;
  188. EXCEPTION
  189. WHEN OTHERS THEN
  190. v_return_code := 'E';
  191. v_return_msg := '更新銷售訂單行出現異常:' || SQLCODE || ':' || SQLERRM;
  192. p_return_code := v_return_code;
  193. p_return_msg := v_return_msg;
  194. dbms_output.put_line(v_return_code || v_return_msg);
  195. END;

 

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号