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

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

Oracle EBS INV 创建物料搬运单行

Jenrry 2018-11-26 原文

1. CREATE OR REPLACE
2. PROCEDURE CreateMoveOrderLines 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_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;
22. l_trolin_val_tbl INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE;
23. x_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;
24. x_trolin_val_tbl INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE;
25. l_validation_flag VARCHAR2(2) := INV_MOVE_ORDER_PUB.G_VALIDATION_YES;
27. BEGIN
29. -- Get the user_id
30. SELECT user_id
31. INTO l_user_id
32. FROM fnd_user
33. WHERE user_name = l_user_name;
35. -- Get the application_id and responsibility_id
36. SELECT application_id, responsibility_id
37. INTO l_application_id, l_resp_id
38. FROM fnd_responsibility_vl
39. WHERE responsibility_name = l_resp_name;
41. FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);
42. dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
44. -- creates lines for every header created before, and provides the line_id
45. -- Initialize the variables
46. l_trolin_tbl(l_row_cnt).header_id := 4073038;
47. l_trolin_tbl(l_row_cnt).date_required := sysdate;
48. l_trolin_tbl(l_row_cnt).organization_id := 207;
49. l_trolin_tbl(l_row_cnt).inventory_item_id := 513963;
50. l_trolin_tbl(l_row_cnt).from_subinventory_code := 'Stores';
51. l_trolin_tbl(l_row_cnt).to_subinventory_code := 'FGI';
52. l_trolin_tbl(l_row_cnt).quantity := 2;
53. l_trolin_tbl(l_row_cnt).status_date := sysdate;
54. l_trolin_tbl(l_row_cnt).uom_code := 'Ea';
55. l_trolin_tbl(l_row_cnt).line_number := l_row_cnt;
56. l_trolin_tbl(l_row_cnt).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
57. l_trolin_tbl(l_row_cnt).db_flag := FND_API.G_TRUE;
58. l_trolin_tbl(l_row_cnt).operation := INV_GLOBALS.G_OPR_CREATE;
60. -- Who columns
61. l_trolin_tbl(l_row_cnt).created_by := l_user_id;
62. l_trolin_tbl(l_row_cnt).creation_date := sysdate;
63. l_trolin_tbl(l_row_cnt).last_updated_by := l_user_id;
64. l_trolin_tbl(l_row_cnt).last_update_date := sysdate;
65. l_trolin_tbl(l_row_cnt).last_update_login := FND_GLOBAL.login_id;
67. -- call API to create move order lines
68. DBMS_OUTPUT.PUT_LINE('==========================================================');
69. DBMS_OUTPUT.PUT_LINE('Calling INV_MOVE_ORDER_PUB.Create_Move_Order_Lines API');
71. INV_MOVE_ORDER_PUB.Create_Move_Order_Lines(
72. P_API_VERSION_NUMBER => l_api_version
73. , P_INIT_MSG_LIST => l_init_msg_list
74. , P_RETURN_VALUES => l_return_values
75. , P_COMMIT => l_commit
76. , X_RETURN_STATUS => x_return_status
77. , X_MSG_COUNT => x_msg_count
78. , X_MSG_DATA => x_msg_data
79. , P_TROLIN_TBL => l_trolin_tbl
80. , P_TROLIN_VAL_TBL => l_trolin_val_tbl
81. , X_TROLIN_TBL => x_trolin_tbl
82. , X_TROLIN_VAL_TBL => x_trolin_val_tbl
83. , P_VALIDATION_FLAG => l_validation_flag
84. );
86. DBMS_OUTPUT.PUT_LINE('==========================================================');
87. DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);
89. IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
90. DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
91. END IF;
93. IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
94. DBMS_OUTPUT.PUT_LINE('Move Order Lines Created Successfully for '||x_trolin_tbl(l_row_cnt).header_id);
95. END IF;
97. DBMS_OUTPUT.PUT_LINE('==========================================================');
99. EXCEPTION
100. WHEN OTHERS THEN
101. DBMS_OUTPUT.PUT_LINE('Exception Occured :');
102. DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
103. DBMS_OUTPUT.PUT_LINE('=======================================================');
104. END CreateMoveOrderLines;