当前位置:Gxlcms > 数据库问题 > Oracle EBS OPM 生产批创建事务处理

Oracle EBS OPM 生产批创建事务处理

时间:2021-07-01 10:21:17 帮助过:3人阅读

  1. --生产批创建事物处理
  2. --created by jenrry
  3. DECLARE
  4. p_mmti_rec mtl_transactions_interface%ROWTYPE;
  5. p_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
  6. x_return_status VARCHAR2 (2000);
  7. p_validation_level NUMBER;
  8. p_init_msg_list VARCHAR2 (2000);
  9. p_commit VARCHAR2 (2000);
  10. x_message_count NUMBER;
  11. x_message_list VARCHAR2 (2000);
  12. l_msg_count NUMBER;
  13. l_msg_data VARCHAR2 (2000);
  14. x_mmt_rec mtl_material_transactions%ROWTYPE;
  15. x_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
  16. l_txn_count NUMBER;
  17. l_count_t NUMBER;
  18. l_count_i NUMBER;
  19. setup_failure EXCEPTION;
  20. p_org_code VARCHAR2 (3) := ‘PR1‘;
  21. g_debug VARCHAR2 (5) := fnd_profile.VALUE (‘AFLOG_LEVEL‘);
  22. l_user_name VARCHAR2 (80);
  23. l_user_id NUMBER;
  24. CURSOR get_user_id (v_user_name IN VARCHAR2)
  25. IS
  26. SELECT user_id
  27. FROM fnd_user
  28. WHERE user_name = v_user_name;
  29. PROCEDURE display_messages (p_msg_count IN NUMBER)
  30. IS
  31. MESSAGE VARCHAR2 (2000);
  32. dummy NUMBER;
  33. l_api_name CONSTANT VARCHAR2 (30) := ‘DISPLAY_MESSAGES‘;
  34. BEGIN
  35. FOR i IN 1 .. p_msg_count
  36. LOOP
  37. fnd_msg_pub.get (p_msg_index => i,
  38. p_data => MESSAGE,
  39. p_encoded => ‘F‘,
  40. p_msg_index_out => dummy
  41. );
  42. DBMS_OUTPUT.put_line (‘Message ‘ || TO_CHAR (i) || ‘ ‘ || MESSAGE);
  43. END LOOP;
  44. EXCEPTION
  45. WHEN OTHERS
  46. THEN
  47. fnd_msg_pub.add_exc_msg (‘wrapper for Create_Material_txn‘, l_api_name);
  48. END display_messages;
  49. BEGIN
  50. DBMS_OUTPUT.ENABLE (20000);
  51. l_user_name := ‘SYSADMIN‘;
  52. OPEN get_user_id (l_user_name);
  53. FETCH get_user_id
  54. INTO l_user_id;
  55. IF get_user_id%NOTFOUND
  56. THEN
  57. DBMS_OUTPUT.put_line (‘Invalid User ‘ || l_user_name);
  58. CLOSE get_user_id;
  59. RAISE NO_DATA_FOUND;
  60. END IF;
  61. CLOSE get_user_id;
  62. fnd_profile.initialize (l_user_id);
  63. fnd_global.apps_initialize (user_id => l_user_id,
  64. resp_id => NULL,
  65. resp_appl_id => NULL
  66. );
  67. x_return_status := fnd_api.g_ret_sts_success;
  68. fnd_msg_pub.initialize;
  69. gme_common_pvt.g_error_count := 0;
  70. gme_common_pvt.set_timestamp;
  71. gme_common_pvt.g_move_to_temp := fnd_api.g_false;
  72. p_mmti_rec.organization_id := 1199;
  73. p_mmti_rec.source_code := ‘OPM‘;
  74. p_mmti_rec.source_header_id := 147;
  75. p_mmti_rec.transaction_source_id := 414962;
  76. p_mmti_rec.trx_source_line_id := 576266;
  77. --p_mmti_rec.last_updated_by := gme_common_pvt.g_user_ident;
  78. --p_mmti_rec.last_update_login := gme_common_pvt.g_user_ident ;
  79. --p_mmti_rec.last_update_date := gme_common_pvt.g_timestamp ;
  80. --p_mmti_rec.creation_date := gme_common_pvt.g_timestamp ;
  81. --p_mmti_rec.created_by := gme_common_pvt.g_user_ident;
  82. p_mmti_rec.inventory_item_id := 234657;
  83. p_mmti_rec.revision := NULL;
  84. p_mmti_rec.organization_id := 1199;
  85. p_mmti_rec.transaction_date := SYSDATE;
  86. p_mmti_rec.transaction_type_id := 44/*gme_common_pvt.g_ing_issue*/;
  87. p_mmti_rec.transaction_action_id := gme_common_pvt.g_ing_issue_txn_action;
  88. p_mmti_rec.transaction_quantity := 20;
  89. --p_mmti_rec.primary_quantity :=
  90. p_mmti_rec.transaction_uom := ‘袋‘;
  91. p_mmti_rec.subinventory_code := ‘CWK‘;
  92. p_mmti_rec.locator_id := 983;
  93. p_mmti_rec.transaction_source_type_id := 5;
  94. --p_mmti_rec.transaction_source_name :=
  95. p_mmti_rec.wip_entity_type := 10;
  96. --p_mmti_rec.reason_id :=
  97. p_mmli_tbl(1).last_update_date := gme_common_pvt.g_timestamp ;
  98. p_mmli_tbl(1).last_updated_by := gme_common_pvt.g_user_ident ;
  99. p_mmli_tbl(1).creation_date := gme_common_pvt.g_timestamp ;
  100. p_mmli_tbl(1).created_by := gme_common_pvt.g_user_ident ;
  101. p_mmli_tbl(1).lot_number := ‘L20170814‘ ;
  102. --p_mmli_tbl(1).parent_lot_number := ‘GRNEWPARENTAPI‘ ;
  103. p_mmli_tbl(1).transaction_quantity := 20 ;
  104. gme_api_pub.create_material_txn
  105. (p_api_version => 2.0,
  106. p_validation_level => gme_common_pvt.g_max_errors,
  107. p_init_msg_list => fnd_api.g_false,
  108. p_commit => fnd_api.g_true,
  109. x_message_count => x_message_count,
  110. x_message_list => x_message_list,
  111. x_return_status => x_return_status,
  112. p_org_code => ‘F06‘,
  113. p_mmti_rec => p_mmti_rec,
  114. p_mmli_tbl => p_mmli_tbl,
  115. p_batch_no => NULL,
  116. p_line_no => NULL,
  117. p_line_type => NULL,
  118. p_create_lot => fnd_api.g_true,
  119. p_generate_lot => NULL,
  120. p_generate_parent_lot => NULL,
  121. x_mmt_rec => x_mmt_rec,
  122. x_mmln_tbl => x_mmln_tbl
  123. );
  124. DBMS_OUTPUT.put_line ( ‘msg_count from process trxns=‘
  125. || TO_CHAR (l_msg_count)
  126. );
  127. IF l_msg_count > 1
  128. THEN
  129. display_messages (l_msg_count);
  130. END IF;
  131. DBMS_OUTPUT.put_line ( ‘after process transactions x_return_status=‘
  132. || TO_CHAR (x_return_status)
  133. );
  134. -- Output the results
  135. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_id = ‘
  136. || TO_CHAR (x_mmt_rec.transaction_id),
  137. 1,
  138. 255
  139. )
  140. );
  141. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_type_id = ‘
  142. || TO_CHAR (x_mmt_rec.transaction_type_id),
  143. 1,
  144. 255
  145. )
  146. );
  147. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_action_id = ‘
  148. || TO_CHAR (x_mmt_rec.transaction_action_id),
  149. 1,
  150. 255
  151. )
  152. );
  153. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_source_id = ‘
  154. || TO_CHAR (x_mmt_rec.transaction_source_id),
  155. 1,
  156. 255
  157. )
  158. );
  159. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.trx_source_line_id = ‘
  160. || TO_CHAR (x_mmt_rec.trx_source_line_id),
  161. 1,
  162. 255
  163. )
  164. );
  165. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.source_line_id = ‘
  166. || TO_CHAR (x_mmt_rec.source_line_id),
  167. 1,
  168. 255
  169. )
  170. );
  171. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_quantity = ‘
  172. || TO_CHAR (x_mmt_rec.transaction_quantity),
  173. 1,
  174. 255
  175. )
  176. );
  177. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_uom = ‘
  178. || TO_CHAR (x_mmt_rec.transaction_uom),
  179. 1,
  180. 255
  181. )
  182. );
  183. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_date = ‘
  184. || TO_CHAR (x_mmt_rec.transaction_date),
  185. 1,
  186. 255
  187. )
  188. );
  189. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmln_tbl.count = ‘
  190. || TO_CHAR (x_mmln_tbl.COUNT),
  191. 1,
  192. 255
  193. )
  194. );
  195. IF (x_mmln_tbl.COUNT > 0)
  196. THEN
  197. FOR i IN 1 .. x_mmln_tbl.COUNT
  198. LOOP
  199. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmln_tbl((i).lot_number = ‘
  200. || TO_CHAR (x_mmln_tbl.COUNT),
  201. 1,
  202. 255
  203. )
  204. );
  205. END LOOP;
  206. END IF;
  207. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.LAST_UPDATE_DATE = ‘
  208. || TO_CHAR (x_mmt_rec.last_update_date),
  209. 1,
  210. 255
  211. )
  212. );
  213. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.LAST_UPDATED_BY = ‘
  214. || TO_CHAR (x_mmt_rec.last_updated_by),
  215. 1,
  216. 255
  217. )
  218. );
  219. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.CREATION_DATE = ‘
  220. || TO_CHAR (x_mmt_rec.creation_date),
  221. 1,
  222. 255
  223. )
  224. );
  225. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.CREATED_BY = ‘
  226. || TO_CHAR (x_mmt_rec.created_by),
  227. 1,
  228. 255
  229. )
  230. );
  231. DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.LAST_UPDATE_LOGIN = ‘
  232. || TO_CHAR (x_mmt_rec.last_update_login),
  233. 1,
  234. 255
  235. )
  236. );
  237. DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,
  238. 1,
  239. 255
  240. )
  241. );
  242. DBMS_OUTPUT.put_line (‘x_message_count = ‘ || TO_CHAR (x_message_count));
  243. DBMS_OUTPUT.put_line (SUBSTR (‘x_message_list = ‘ || x_message_list, 1,
  244. 255)
  245. );
  246. gme_common_pvt.count_and_get (x_count => x_message_count,
  247. p_encoded => fnd_api.g_false,
  248. x_data => x_message_list
  249. );
  250. IF x_message_count > 1
  251. THEN
  252. display_messages (x_message_count);
  253. END IF;
  254. EXCEPTION
  255. WHEN setup_failure
  256. THEN
  257. DBMS_OUTPUT.put_line (‘ in setup failure‘);
  258. WHEN OTHERS
  259. THEN
  260. DBMS_OUTPUT.put_line (SUBSTR ( ‘Error ‘
  261. || TO_CHAR (SQLCODE)
  262. || ‘: ‘
  263. || SQLERRM,
  264. 1,
  265. 255
  266. )
  267. );
  268. RAISE;
  269. END;
  270. /

  

Oracle EBS OPM 生产批创建事务处理

标签:ant   wip   end   creat   return   const   time   org   fail   

人气教程排行