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

创建销售订单API主要注意几点:

  • 初始化环境变量:fnd_global.apps_initialize(); mo_global.init('ONT'); mo_global.set_policy_context('S', OU_ID);
  • 订单状态:订单状态在给值的时候给:booked_flag:='N'; flow_status_code:='ENTERED'; 

                 然后在行上要记得控制订单状态:
                 --登記銷售訂單
                 l_action_request_tbl(l_line_tbl_index).entity_code := oe_globals.g_entity_header;
                 l_action_request_tbl(l_line_tbl_index).request_type := oe_globals.g_book_order;

  • 提交API时参数要正确:输入的参数和输出的参数不能用同一个变量名,即便是类型一样,也要取两个变量名

 

创建销售订单(SO)-API 示例代码如下:

  1. PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) IS
  2. /********************銷售訂單參數********************/
  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_true;
  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. --参数
  87. v_so_msg VARCHAR2(2000) := NULL;
  88. v_return_code VARCHAR2(10) := 'S';
  89. v_return_msg VARCHAR2(2000) := NULL;
  90. BEGIN
  91. dbms_output.put_line('***************創建銷售訂單开始***************');
  92. --初始化SO环境变量
  93. BEGIN
  94. -- Get the user_id
  95. SELECT user_id
  96. INTO l_user_id
  97. FROM fnd_user
  98. WHERE user_name = l_user_name;
  99. -- Get the application_id and responsibility_id
  100. SELECT application_id, responsibility_id
  101. INTO l_application_id, l_resp_id
  102. FROM fnd_responsibility_vl
  103. WHERE responsibility_name = l_resp_name;
  104. --oe_msg_pub.initialize;
  105. fnd_global.apps_initialize(user_id => /*fnd_global.user_id,*/ l_user_id,
  106. resp_id => /*fnd_global.resp_id,*/ l_resp_id,
  107. resp_appl_id => /*fnd_global.resp_appl_id*/ l_application_id);
  108. mo_global.init('ONT');
  109. mo_global.set_policy_context('S', 349); --OU_ID
  110. END;
  111. --Create Header record
  112. --Initialize header record to missing
  113. l_header_rec := oe_order_pub.g_miss_header_rec;
  114. l_header_rec.org_id := v_org_id; --業務實體OU_ID
  115. l_header_rec.ship_to_customer_id := v_account_id; --客戶Account_ID
  116. l_header_rec.ship_to_org_id := v_so_head.ship_to_site_id; --收貨地點 --437137
  117. l_header_rec.invoice_to_org_id := v_so_head.bill_to_site_id; --收單地點 --437135
  118. l_header_rec.sold_to_org_id := v_account_id; --ACCOUNT_ID
  119. l_header_rec.order_type_id := v_order_type_id; --訂單類型ID
  120. l_header_rec.ordered_date := SYSDATE; --訂購日期
  121. l_header_rec.price_list_id := 453581; --價目表(可以由訂單類型出,可以不赋值)
  122. l_header_rec.salesrep_id := v_salesrep_id; --銷售人員ID --100073147
  123. l_header_rec.booked_flag := 'N';
  124. l_header_rec.flow_status_code := 'ENTERED'; --狀態 说明:销售订单在创建的时候,API都默认给订单只能为ENTERED的状态,如赋值BOOKED也不行,系统也会是ENDTERED,这个状态只能在下面行赋值后控制
  125. l_header_rec.booked_date := SYSDATE;
  126. l_header_rec.transactional_curr_code := v_so_head.currency_code; --幣種
  127. l_header_rec.payment_term_id := v_so_head.payment_term_id; --付款條件ID
  128. l_header_rec.packing_instructions := v_so_head.customer_item_name; --包裝說明(主品名)
  129. l_header_rec.sales_document_name := v_so_head.sales_document_name; --客戶料號
  130. l_header_rec.cust_po_number := v_so_head.purchase_order_num; --客戶採購單
  131. l_header_rec.request_date := SYSDATE; --交貨日期
  132. l_header_rec.ship_from_org_id := 122; --库存组织ID
  133. l_header_rec.order_source_id := v_order_source_id; --訂單來源
  134. l_header_rec.orig_sys_document_ref := 'CPQ'; --訂單來源參考
  135. l_header_rec.CONTEXT := 'TWGV_INV'; --上下文
  136. l_header_rec.attribute10 := v_so_head.cpq_transaction_id; --CPQ_transaction_id
  137. l_header_rec.attribute9 := v_so_head.is_f; --是否為F
  138. l_header_rec.attribute4 := v_so_head.is_partial_shipment; --可分批出貨
  139. l_header_rec.shipping_instructions := v_so_head.shipping_instructure; --發貨說明
  140. l_header_rec.attribute7 := v_so_head.attribute3; --發票開立方式
  141. l_header_rec.attribute5 := v_taxes_code; --稅籍編號
  142. l_header_rec.order_category_code := 'ORDER';
  143. l_header_rec.operation := oe_globals.g_opr_create; --关键字:创建订单
  144.  
  145. /*
  146. **说明:如果要创建多行,则可以用游标循环 l_line_tbl_index 行号,循环行就可以
  147. */
  148. --CREAETE LINE RECORD
  149. l_line_tbl.DELETE;
  150. l_line_tbl_index := 1;
  151. -- Initialize record to missing
  152. l_line_tbl(l_line_tbl_index) := oe_order_pub.g_miss_line_rec;
  153. -- Line attributes
  154. l_line_tbl(l_line_tbl_index).inventory_item_id := v_inventory_item_id; --物料id
  155. l_line_tbl(l_line_tbl_index).ordered_quantity := v_so_line.order_qty; --订购数量
  156. l_line_tbl(l_line_tbl_index).order_quantity_uom := v_so_line.inventory_item_uom; --订购单位
  157. l_line_tbl(l_line_tbl_index).calculate_price_flag := 'N'; --控制是否從 价目表 price-list 帶出价格,N:不带出,自行赋值; Y:销售单价由价目表带出,不用赋值
  158. l_line_tbl(l_line_tbl_index).unit_selling_price := 12; --銷售單價
  159. l_line_tbl(l_line_tbl_index).request_date := l_header_rec.request_date; --请求日期
  160. l_line_tbl(l_line_tbl_index).schedule_ship_date := l_header_rec.request_date; --workflow SO Line Request date 跳過去(若不是假日)
  161. l_line_tbl(l_line_tbl_index).line_type_id := v_line_type_id; --行類型
  162. l_line_tbl(l_line_tbl_index).tax_code := v_tax_rate_code; --税码
  163. l_line_tbl(l_line_tbl_index).cust_po_number := v_so_line.purchase_order_num; --客戶採購單單號
  164. l_line_tbl(l_line_tbl_index).customer_line_number := v_so_line.requisition_num; --客戶請購單號
  165. l_line_tbl(l_line_tbl_index).subinventory := 'BTC'; --库存编码
  166. l_line_tbl(l_line_tbl_index).ship_from_org_id := 385; --子庫存385(库存组织id)
  167. l_line_tbl(l_line_tbl_index).CONTEXT := v_org_id; --行信息上下文(OU_ID)
  168. l_line_tbl(l_line_tbl_index).attribute17 := v_so_line.cpq_transaction_lineid; --CPQ_TXN_Line_ID
  169. l_line_tbl(l_line_tbl_index).booked_flag := 'N'; --这个应该也可以不用赋值,应该会跟着头走
  170. l_line_tbl(l_line_tbl_index).schedule_arrival_date := SYSDATE;
  171. l_line_tbl(l_line_tbl_index).orig_sys_line_ref := v_so_line.cpq_transaction_lineno; --客制标识
  172. --l_line_tbl(l_line_tbl_index).flow_status_code := 'SUPPLY_ELIGIBLE'; --不用赋值行状态
  173.  
  174. /*
  175. **说明:如果前面的calculate_price_flag为N,就是不从价目表带出价格,则需要手动赋值行的unit_list_price,如果前面为Y,则不需要
  176. */
  177. --賦值:unit_list_price
  178. IF l_line_tbl(l_line_tbl_index).price_list_id IS NOT NULL AND l_line_tbl(l_line_tbl_index).calculate_price_flag = 'N' AND l_line_tbl(l_line_tbl_index).unit_selling_price IS NOT NULL THEN
  179. l_line_tbl(l_line_tbl_index).unit_list_price := 0;
  180. END IF;
  181. l_line_tbl(l_line_tbl_index).operation := oe_globals.g_opr_create;
  182. /*
  183. **说明:控制订单是否登记:BOOKED
  184. */
  185. --登記銷售訂單
  186. l_action_request_tbl(l_line_tbl_index).entity_code := oe_globals.g_entity_header;
  187. l_action_request_tbl(l_line_tbl_index).request_type := oe_globals.g_book_order; --控制登記動作
  188.  
  189. /*
  190. **说明:如果需要生成多行,则可以再循环里递增这个行号
  191. **l_line_tbl_index := l_line_tbl_index + 1;
  192. */
  193.  
  194. -- CALL API
  195. oe_order_pub.process_order(p_api_version_number => p_api_version_number,
  196. p_init_msg_list => p_init_msg_list, --是否每次初始化返回信息的listfnd_api.g_true (TRUE则每调用一次清空,false则不清空)
  197. p_return_values => p_return_values, --fnd_api.g_false
  198. p_action_commit => p_action_commit, --是否自动提交:fnd_api.g_false
  199. x_return_status => l_return_status, --API 返回是否成功状态
  200. x_msg_count => l_msg_count, --API 返回错误信息的条数
  201. x_msg_data => l_msg_data, --API 返回错误信息的数据
  202. p_header_rec => l_header_rec, --输入:头信息
  203. p_line_tbl => l_line_tbl, --输入:行信息
  204. p_action_request_tbl => l_action_request_tbl, --输入:控制状态信息
  205. -- OUT PARAMETERS
  206. x_header_rec => l_header_rec_out, --输出:头信息;注意:不能和输入的头信息参数一样,必须用新变量
  207. x_header_val_rec => x_header_val_rec,
  208. x_header_adj_tbl => x_header_adj_tbl,
  209. x_header_adj_val_tbl => x_header_adj_val_tbl,
  210. x_header_price_att_tbl => x_header_price_att_tbl,
  211. x_header_adj_att_tbl => x_header_adj_att_tbl,
  212. x_header_adj_assoc_tbl => x_header_adj_assoc_tbl,
  213. x_header_scredit_tbl => x_header_scredit_tbl,
  214. x_header_scredit_val_tbl => x_header_scredit_val_tbl,
  215. x_line_tbl => l_line_tbl_out, --输出:行信息;注意:不能和输入的行信息参数一样,必须用新变量
  216. x_line_val_tbl => x_line_val_tbl,
  217. x_line_adj_tbl => x_line_adj_tbl,
  218. x_line_adj_val_tbl => x_line_adj_val_tbl,
  219. x_line_price_att_tbl => x_line_price_att_tbl,
  220. x_line_adj_att_tbl => x_line_adj_att_tbl,
  221. x_line_adj_assoc_tbl => x_line_adj_assoc_tbl,
  222. x_line_scredit_tbl => x_line_scredit_tbl,
  223. x_line_scredit_val_tbl => x_line_scredit_val_tbl,
  224. x_lot_serial_tbl => x_lot_serial_tbl,
  225. x_lot_serial_val_tbl => x_lot_serial_val_tbl,
  226. x_action_request_tbl => x_action_request_tbl);
  227. --检查返回状态
  228. IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
    v_return_msg := NULL; --初始化信息
  229. -- Retrieve messages
  230. FOR i IN 1 .. l_msg_count LOOP
  231. l_msg_data := oe_msg_pub.get(p_msg_index => i, p_encoded => 'F');
  232. v_so_msg := v_so_msg || l_msg_data;
  233. END LOOP;
  234. v_return_code := 'E';
  235. v_return_msg := v_so_msg;
  236. /*
  237. **说明:按需要回滚,如果和物料一起在一个session ,则可以回滚物料,如果单独的销售订单创建则可以不需要
  238. **v_so_count_e := v_so_count_e + 1; --按需要可记录失败的条数
  239. **ROLLBACK;
  240. */
  241. dbms_output.put_line('創建銷售訂單失敗:' || v_return_code || ':' || v_return_msg);
  242. ELSE
  243. v_return_code := 'S';
  244. v_return_msg := '訂單編號(' || l_header_rec_out.order_number || '); 销售订单ID(' || l_header_rec_out.header_id || ')';
  245. /*
  246. **v_so_count_s := v_so_count_s + 1; --按需要可记录成功的条数
  247. */
  248. COMMIT;
  249. dbms_output.put_line('***************創建銷售訂單成功:銷售訂單編號(' || l_header_rec_out.order_number || ')');
  250. /*
  251. **说明:可以获取头行信息
  252. --头信息
  253. dbms_output.put_line('销售订单头ID:' || l_header_rec_out.header_id);
  254. dbms_output.put_line('销售订单头编号:' || l_header_rec_out.order_number);
  255. dbms_output.put_line('销售订单头状态:' || l_header_rec_out.flow_status_code);
  256. --循環獲取行
  257. FOR v_line_count IN 1 .. l_line_tbl_out.COUNT LOOP
  258. dbms_output.put_line('销售订单行ID:' || l_line_tbl_out(v_line_count).line_id);
  259. dbms_output.put_line('销售订单行状态:' || l_line_tbl_out(v_line_count).flow_status_code);
  260. END LOOP;
  261. */
  262. END IF;
  263. EXCEPTION
  264. WHEN OTHERS THEN
  265. v_return_code := 'E';
  266. v_return_msg := '程序出現異常(MAIN),請聯繫管理員:' || SQLCODE || ':' || SQLERRM;
  267. retcode := 2;
  268. dbms_output.put_line(v_return_code || ':' || v_return_msg);
  269. /*
  270. **v_so_count_s := v_so_count_s + 1; --按需要可记录成功的条数
  271. **ROLLBACK;
  272. */
  273. 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号