API-Oracle EBS INV 创建物料搬运单

摘自：Oracle EBS INV 创建物料搬运单 (bbsmax.com)

Oracle EBS INV 创建物料搬运单

Jenrry 2018-11-26 原文

1. Create or Replace PROCEDURE ProcessMoveOrder
2. AS
3. -- Common Declarations
4. l_api_version NUMBER := 1.0;
5. l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
6. l_return_values VARCHAR2(2) := FND_API.G_FALSE;
7. l_commit VARCHAR2(2) := FND_API.G_FALSE;
8. x_return_status VARCHAR2(2);
9. x_msg_count NUMBER := 0;
10. x_msg_data VARCHAR2(255);
12. -- WHO columns
13. l_user_id NUMBER := -1;
14. l_resp_id NUMBER := -1;
15. l_application_id NUMBER := -1;
16. l_row_cnt NUMBER := 1;
17. l_user_name VARCHAR2(30) := 'MFG';
18. l_resp_name VARCHAR2(50) := 'Manufacturing and Distribution Manager';
20. -- API specific declarations
21. l_header_id NUMBER := 0;
22. l_trohdr_rec INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;
23. l_trohdr_val_rec INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;
24. l_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;
25. l_trolin_val_tbl INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE;
26. x_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;
27. x_trolin_val_tbl INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE;
28. x_trohdr_rec INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;
29. x_trohdr_val_rec INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;
31. BEGIN
33. -- Get the user_id
34. SELECT user_id
35. INTO l_user_id
36. FROM fnd_user
37. WHERE user_name = l_user_name;
39. -- Get the application_id and responsibility_id
40. SELECT application_id, responsibility_id
41. INTO l_application_id, l_resp_id
42. FROM fnd_responsibility_vl
43. WHERE responsibility_name = l_resp_name;
45. FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); -- Suhasini / Mfg Mgr / INV
46. dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
48. -- Initialize the move order header
49. l_trohdr_rec.date_required := sysdate+2;
50. l_trohdr_rec.organization_id := 207;
51. l_trohdr_rec.from_subinventory_code := 'Stores';
52. l_trohdr_rec.to_subinventory_code := 'FGI';
53. l_trohdr_rec.status_date := sysdate;
54. l_trohdr_rec.request_number := 'TEST_TRO2';
55. l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED; -- preApproved
56. l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR; -- INV_GLOBALS.G_TYPE_TRANSFER_ORDER_STGXFR;
57. l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REQUISITION; -- G_MOVE_ORDER_PICK_WAVE;
58. l_trohdr_rec.db_flag := FND_API.G_TRUE;
59. l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
61. -- Who columns
62. l_trohdr_rec.created_by := l_user_id;
63. l_trohdr_rec.creation_date := sysdate;
64. l_trohdr_rec.last_updated_by := l_user_id;
65. l_trohdr_rec.last_update_date := sysdate;
67. -- create line for the header created above
68. l_trolin_tbl(l_row_cnt).date_required := sysdate;
69. l_trolin_tbl(l_row_cnt).organization_id := 207;
70. l_trolin_tbl(l_row_cnt).inventory_item_id := 513963;
71. l_trolin_tbl(l_row_cnt).from_subinventory_code:= 'Stores';
72. l_trolin_tbl(l_row_cnt).to_subinventory_code := 'FGI';
73. l_trolin_tbl(l_row_cnt).quantity := 2;
74. l_trolin_tbl(l_row_cnt).status_date := sysdate;
75. l_trolin_tbl(l_row_cnt).uom_code := 'Ea';
76. l_trolin_tbl(l_row_cnt).line_number := l_row_cnt;
77. l_trolin_tbl(l_row_cnt).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
78. l_trolin_tbl(l_row_cnt).db_flag := FND_API.G_TRUE;
79. l_trolin_tbl(l_row_cnt).operation := INV_GLOBALS.G_OPR_CREATE;
81. -- Who columns
82. l_trolin_tbl(l_row_cnt).created_by := l_user_id;
83. l_trolin_tbl(l_row_cnt).creation_date := sysdate;
84. l_trolin_tbl(l_row_cnt).last_updated_by := l_user_id;
85. l_trolin_tbl(l_row_cnt).last_update_date := sysdate;
86. l_trolin_tbl(l_row_cnt).last_update_login := FND_GLOBAL.login_id;
88. -- call API to create move order header
89. DBMS_OUTPUT.PUT_LINE('=======================================================');
90. DBMS_OUTPUT.PUT_LINE('Calling INV_MOVE_ORDER_PUB.Process_Move_Order API');
92. INV_MOVE_ORDER_PUB.Process_Move_Order(
93. P_API_VERSION_NUMBER => l_api_version
94. , P_INIT_MSG_LIST => l_init_msg_list
95. , P_RETURN_VALUES => l_return_values
96. , P_COMMIT => l_commit
97. , X_RETURN_STATUS => x_return_status
98. , X_MSG_COUNT => x_msg_count
99. , X_MSG_DATA => x_msg_data
100. , P_TROHDR_REC => l_trohdr_rec
101. , P_TROHDR_VAL_REC => l_trohdr_val_rec
102. , P_TROLIN_TBL => l_trolin_tbl
103. , P_TROLIN_VAL_TBL => l_trolin_val_tbl
104. , X_TROHDR_REC => x_trohdr_rec
105. , X_TROHDR_VAL_REC => x_trohdr_val_rec
106. , X_TROLIN_TBL => x_trolin_tbl
107. , X_TROLIN_VAL_TBL => x_trolin_val_tbl
108. );
110. DBMS_OUTPUT.PUT_LINE('=======================================================');
111. DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);
113. IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
114. DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
115. END IF;
117. IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
118. DBMS_OUTPUT.PUT_LINE('Move Order Created Successfully for '||x_trolin_tbl(l_row_cnt).header_id);
119. END IF;
121. DBMS_OUTPUT.PUT_LINE('=======================================================');
123. EXCEPTION
124. WHEN OTHERS THEN
125. DBMS_OUTPUT.PUT_LINE('Exception Occured :');
126. DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
127. DBMS_OUTPUT.PUT_LINE('=======================================================');
128. END ProcessMoveOrder;