[Home] [Help]
PACKAGE: APPS.INV_MOVE_ORDER_PUB
Source
1 PACKAGE INV_Move_Order_PUB AUTHID CURRENT_USER AS
2 /* $Header: INVPTROS.pls 120.0 2005/05/25 06:16:36 appldev noship $ */
3
4 -------------------------------------------------------------------------------
5 -- Record types for move order header and move order lines
6 -------------------------------------------------------------------------------
7 --------------------------------------------------------------------------------
8 -- Trohdr record type
9 -- Record type to hold a move order header record
10 --------------------------------------------------------------------------------
11
12 g_miss_char varchar2(1) := fnd_api.g_miss_char;
13 g_miss_num number := fnd_api.g_miss_num;
14 g_miss_date date := fnd_api.g_miss_date;
15
16 TYPE Trohdr_Rec_Type IS RECORD
17 ( attribute1 VARCHAR2(150) := G_MISS_CHAR
18 , attribute10 VARCHAR2(150) := G_MISS_CHAR
19 , attribute11 VARCHAR2(150) := G_MISS_CHAR
20 , attribute12 VARCHAR2(150) := G_MISS_CHAR
21 , attribute13 VARCHAR2(150) := G_MISS_CHAR
22 , attribute14 VARCHAR2(150) := G_MISS_CHAR
23 , attribute15 VARCHAR2(150) := G_MISS_CHAR
24 , attribute2 VARCHAR2(150) := G_MISS_CHAR
25 , attribute3 VARCHAR2(150) := G_MISS_CHAR
26 , attribute4 VARCHAR2(150) := G_MISS_CHAR
27 , attribute5 VARCHAR2(150) := G_MISS_CHAR
28 , attribute6 VARCHAR2(150) := G_MISS_CHAR
29 , attribute7 VARCHAR2(150) := G_MISS_CHAR
30 , attribute8 VARCHAR2(150) := G_MISS_CHAR
31 , attribute9 VARCHAR2(150) := G_MISS_CHAR
32 , attribute_category VARCHAR2(30) := G_MISS_CHAR
33 , created_by NUMBER := G_MISS_NUM
34 , creation_date DATE := G_MISS_DATE
35 , date_required DATE := G_MISS_DATE
36 , description VARCHAR2(240) := G_MISS_CHAR
37 , from_subinventory_code VARCHAR2(10) := G_MISS_CHAR
38 , header_id NUMBER := G_MISS_NUM
39 , header_status NUMBER := G_MISS_NUM
40 , last_updated_by NUMBER := G_MISS_NUM
41 , last_update_date DATE := G_MISS_DATE
42 , last_update_login NUMBER := G_MISS_NUM
43 , organization_id NUMBER := G_MISS_NUM
44 , program_application_id NUMBER := G_MISS_NUM
45 , program_id NUMBER := G_MISS_NUM
46 , program_update_date DATE := G_MISS_DATE
47 , request_id NUMBER := G_MISS_NUM
48 , request_number VARCHAR2(30) := G_MISS_CHAR
49 , status_date DATE := G_MISS_DATE
50 , to_account_id NUMBER := G_MISS_NUM
51 , to_subinventory_code VARCHAR2(10) := G_MISS_CHAR
52 , move_order_type NUMBER := G_MISS_NUM
53 , transaction_type_id NUMBER := G_MISS_NUM
54 , grouping_rule_id NUMBER := G_MISS_NUM
55 , ship_to_location_id NUMBER := G_MISS_NUM
56 , return_status VARCHAR2(1) := G_MISS_CHAR
57 , db_flag VARCHAR2(1) := G_MISS_CHAR
58 , operation VARCHAR2(30) := G_MISS_CHAR
59 );
60
61
62 TYPE Trohdr_Tbl_Type IS TABLE OF Trohdr_Rec_Type
63 INDEX BY BINARY_INTEGER;
64
65 -- Trohdr value record type
66
67 TYPE Trohdr_Val_Rec_Type IS RECORD
68 ( from_subinventory VARCHAR2(240) := FND_API.G_MISS_CHAR
69 , header VARCHAR2(240) := FND_API.G_MISS_CHAR
70 , organization VARCHAR2(240) := FND_API.G_MISS_CHAR
71 , to_account VARCHAR2(2000) := FND_API.G_MISS_CHAR
72 , to_subinventory VARCHAR2(240) := FND_API.G_MISS_CHAR
73 , move_order_type VARCHAR2(240) := FND_API.G_MISS_CHAR
74 , transaction_type VARCHAR2(240) := FND_API.G_MISS_CHAR
75 );
76
77 TYPE Trohdr_Val_Tbl_Type IS TABLE OF Trohdr_Val_Rec_Type
78 INDEX BY BINARY_INTEGER;
79
80 -- Trolin record type
81
82 TYPE Trolin_Rec_Type IS RECORD
83 ( attribute1 VARCHAR2(150) := FND_API.G_MISS_CHAR
84 , attribute10 VARCHAR2(150) := FND_API.G_MISS_CHAR
85 , attribute11 VARCHAR2(150) := FND_API.G_MISS_CHAR
86 , attribute12 VARCHAR2(150) := FND_API.G_MISS_CHAR
87 , attribute13 VARCHAR2(150) := FND_API.G_MISS_CHAR
88 , attribute14 VARCHAR2(150) := FND_API.G_MISS_CHAR
89 , attribute15 VARCHAR2(150) := FND_API.G_MISS_CHAR
90 , attribute2 VARCHAR2(150) := FND_API.G_MISS_CHAR
91 , attribute3 VARCHAR2(150) := FND_API.G_MISS_CHAR
92 , attribute4 VARCHAR2(150) := FND_API.G_MISS_CHAR
93 , attribute5 VARCHAR2(150) := FND_API.G_MISS_CHAR
94 , attribute6 VARCHAR2(150) := FND_API.G_MISS_CHAR
95 , attribute7 VARCHAR2(150) := FND_API.G_MISS_CHAR
96 , attribute8 VARCHAR2(150) := FND_API.G_MISS_CHAR
97 , attribute9 VARCHAR2(150) := FND_API.G_MISS_CHAR
98 , attribute_category VARCHAR2(30) := FND_API.G_MISS_CHAR
99 , created_by NUMBER := FND_API.G_MISS_NUM
100 , creation_date DATE := FND_API.G_MISS_DATE
101 , date_required DATE := FND_API.G_MISS_DATE
102 , from_locator_id NUMBER := FND_API.G_MISS_NUM
103 , from_subinventory_code VARCHAR2(10) := FND_API.G_MISS_CHAR
104 , from_subinventory_id NUMBER := FND_API.G_MISS_NUM
105 , header_id NUMBER := FND_API.G_MISS_NUM
106 , inventory_item_id NUMBER := FND_API.G_MISS_NUM
107 , last_updated_by NUMBER := FND_API.G_MISS_NUM
108 , last_update_date DATE := FND_API.G_MISS_DATE
109 , last_update_login NUMBER := FND_API.G_MISS_NUM
110 , line_id NUMBER := FND_API.G_MISS_NUM
111 , line_number NUMBER := FND_API.G_MISS_NUM
112 , line_status NUMBER := FND_API.G_MISS_NUM
113 , lot_number VARCHAR2(80) := FND_API.G_MISS_CHAR
114 , organization_id NUMBER := FND_API.G_MISS_NUM
115 , program_application_id NUMBER := FND_API.G_MISS_NUM
116 , program_id NUMBER := FND_API.G_MISS_NUM
117 , program_update_date DATE := FND_API.G_MISS_DATE
118 , project_id NUMBER := FND_API.G_MISS_NUM
119 , quantity NUMBER := FND_API.G_MISS_NUM
120 , quantity_delivered NUMBER := FND_API.G_MISS_NUM
121 , quantity_detailed NUMBER := FND_API.G_MISS_NUM
122 , reason_id NUMBER := FND_API.G_MISS_NUM
123 , reference VARCHAR2(240) := FND_API.G_MISS_CHAR
124 , reference_id NUMBER := FND_API.G_MISS_NUM
125 , reference_type_code NUMBER := FND_API.G_MISS_NUM
126 , request_id NUMBER := FND_API.G_MISS_NUM
127 , revision VARCHAR2(3) := FND_API.G_MISS_CHAR
128 , serial_number_end VARCHAR2(30) := FND_API.G_MISS_CHAR
129 , serial_number_start VARCHAR2(30) := FND_API.G_MISS_CHAR
130 , status_date DATE := FND_API.G_MISS_DATE
131 , task_id NUMBER := FND_API.G_MISS_NUM
132 , to_account_id NUMBER := FND_API.G_MISS_NUM
133 , to_locator_id NUMBER := FND_API.G_MISS_NUM
134 , to_subinventory_code VARCHAR2(10) := FND_API.G_MISS_CHAR
135 , to_subinventory_id NUMBER := FND_API.G_MISS_NUM
136 , transaction_header_id NUMBER := FND_API.G_MISS_NUM
137 , transaction_type_id NUMBER := FND_API.G_MISS_NUM
138 , txn_source_id NUMBER := FND_API.G_MISS_NUM
139 , txn_source_line_id NUMBER := FND_API.G_MISS_NUM
140 , txn_source_line_detail_id NUMBER := FND_API.G_MISS_NUM
141 , transaction_source_type_id NUMBER := FND_API.G_MISS_NUM
142 , primary_quantity NUMBER := FND_API.G_MISS_NUM
143 , to_organization_id NUMBER := FND_API.G_MISS_NUM
144 , pick_strategy_id NUMBER := FND_API.G_MISS_NUM
145 , put_away_strategy_id NUMBER := FND_API.G_MISS_NUM
146 , uom_code VARCHAR2(3) := FND_API.G_MISS_CHAR
147 , unit_number VARCHAR2(30) := FND_API.G_MISS_CHAR
148 , ship_to_location_id NUMBER := FND_API.G_MISS_NUM
149 , from_cost_group_id NUMBER := FND_API.G_MISS_NUM
150 , to_cost_group_id NUMBER := FND_API.G_MISS_NUM
151 , lpn_id NUMBER := FND_API.G_MISS_NUM
152 , to_lpn_id NUMBER := FND_API.G_MISS_NUM
153 , pick_methodology_id NUMBER := FND_API.G_MISS_NUM
154 , container_item_id NUMBER := FND_API.G_MISS_NUM
155 , carton_grouping_id NUMBER := FND_API.G_MISS_NUM
156 , return_status VARCHAR2(1) := FND_API.G_MISS_CHAR
157 , db_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
158 , operation VARCHAR2(30) := FND_API.G_MISS_CHAR
159 , inspection_status NUMBER :=NULL
160 , wms_process_flag NUMBER :=NULL
161 , pick_slip_number NUMBER :=NULL
162 , pick_slip_date DATE :=NULL
163 , ship_set_id NUMBER :=NULL
164 , ship_model_id NUMBER :=NULL
165 , model_quantity NUMBER :=NULL
166 , required_quantity NUMBER :=NULL
167 --INVCONV BEGIN
168 , secondary_quantity NUMBER := FND_API.G_MISS_NUM
169 , secondary_uom VARCHAR2(3) := FND_API.G_MISS_CHAR
170 , secondary_quantity_detailed NUMBER := FND_API.G_MISS_NUM
171 , secondary_quantity_delivered NUMBER := FND_API.G_MISS_NUM
172 , grade_code VARCHAR2(150) := FND_API.G_MISS_CHAR
173 , secondary_required_quantity NUMBER := NULL
174 --INVCONV END;
175 );
176
177 TYPE Trolin_Tbl_Type IS TABLE OF Trolin_Rec_Type
178 INDEX BY BINARY_INTEGER;
179
180 -- Trolin value record type
181
182 TYPE Trolin_Val_Rec_Type IS RECORD
183 ( from_locator VARCHAR2(2000) := FND_API.G_MISS_CHAR
184 , from_subinventory VARCHAR2(240) := FND_API.G_MISS_CHAR
185 , header VARCHAR2(240) := FND_API.G_MISS_CHAR
186 , inventory_item VARCHAR2(2000) := FND_API.G_MISS_CHAR
187 , line VARCHAR2(240) := FND_API.G_MISS_CHAR
188 , organization VARCHAR2(240) := FND_API.G_MISS_CHAR
189 , project VARCHAR2(240) := FND_API.G_MISS_CHAR
190 , reason VARCHAR2(240) := FND_API.G_MISS_CHAR
191 , reference VARCHAR2(240) := FND_API.G_MISS_CHAR
192 , reference_type VARCHAR2(240) := FND_API.G_MISS_CHAR
193 , task VARCHAR2(240) := FND_API.G_MISS_CHAR
194 , to_account VARCHAR2(2000) := FND_API.G_MISS_CHAR
195 , to_locator VARCHAR2(2000) := FND_API.G_MISS_CHAR
196 , to_subinventory VARCHAR2(240) := FND_API.G_MISS_CHAR
197 , transaction_header VARCHAR2(240) := FND_API.G_MISS_CHAR
198 , uom VARCHAR2(240) := FND_API.G_MISS_CHAR
199 , transaction_type VARCHAR2(240) := FND_API.G_MISS_CHAR
200 , to_organization VARCHAR2(240) := FND_API.G_MISS_CHAR
201 );
202
203 TYPE Trolin_Val_Tbl_Type IS TABLE OF Trolin_Val_Rec_Type
204 INDEX BY BINARY_INTEGER;
205
206 -- For parallel Pick-release
207
208 TYPE Trolin_New_Tbl_Type IS TABLE OF MTL_TXN_REQUEST_LINES%ROWTYPE
209 INDEX BY BINARY_INTEGER;
210
211 TYPE num_tbl_type IS TABLE OF NUMBER
212 INDEX BY BINARY_INTEGER;
213
214
215 -- Variables representing missing records and tables
216
217 G_MISS_TROHDR_REC Trohdr_Rec_Type;
218 G_MISS_TROHDR_VAL_REC Trohdr_Val_Rec_Type;
219 G_MISS_TROHDR_TBL Trohdr_Tbl_Type;
220 G_MISS_TROHDR_VAL_TBL Trohdr_Val_Tbl_Type;
221 G_MISS_TROLIN_REC Trolin_Rec_Type;
222 G_MISS_TROLIN_VAL_REC Trolin_Val_Rec_Type;
223 G_MISS_TROLIN_TBL Trolin_Tbl_Type;
224 G_MISS_TROLIN_VAL_TBL Trolin_Val_Tbl_Type;
225
226
227 --global variables used in parameter p_validation_flag
228 g_validation_yes VARCHAR2(1) := 'Y';
229 g_validation_no VARCHAR2(1) := 'N';
230
231
232 -------------------------------------------------------------------------------
233 -- Procedures and Functions
234 -------------------------------------------------------------------------------
235
236 -- Procedures
237 -- Create_Move_Order_Header
238 -- Input Parameters
239 -- p_api_version_number API version number (current version is 1.0)
240 --
241 -- p_init_msg_list Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
242 -- if set to FND_API.G_TRUE
243 -- initialize error message list
244 -- if set to FND_API.G_FALSE - not initialize error
245 -- message list
246 -- p_return_values valid values: FND_API.G_FALSE or FND_API.G_TRUE
247 -- p_commit whether or not to commit the changes to database
248 -- p_trohdr_rec record contains information to be used to create the
249 -- move order header
250 -- p_trohdr_val_rec contains information values as supposed to internal
251 -- IDs used to create the move order header
252 --
253 -- Output Parameter:
254 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if succeeded
255 -- = FND_API.G_RET_STS_EXC_ERROR, if an expected error occured
256 -- = FND_API.G_RET_STS_UNEXP_ERROR, if an unexpected error occured
257 --
258 -- x_msg_count Number of error message in the error message list
259 --
260 -- x_msg_data If the number of error message in the error message list is one,
261 -- the error message is in the output parameter
262 --
263 -- x_trohdr_rec The information of move order header that got created
264 --
265 -- x_trohdr_val_Rec The information values of move order header that got created
266 --
267 -- Example
268 -- The following code creates a move order header with the following information:
269 -- organization_id 207
270 -- default from_subinventory_code 'FGI'
271 -- default to_subinventory_code 'Stores'
272 -- move_order_type 1 (Move Order Requisition)
273 -- default transaction_type : subinventory_transfer
274 -- header_status preapproved
275 --
276 -- declare
277 -- l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
278 -- l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
279 -- l_msg_count NUMBER;
280 -- l_msg_data VARCHAR2(240);
281 -- l_trohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
282 -- l_commit VARCHAR2(1) := FND_API.G_TRUE;
283 -- begin
284 -- l_trohdr_rec.created_by := 1068;
285 -- l_trohdr_rec.creation_date := sysdate;
286 -- l_trohdr_rec.date_required := sysdate;
287 -- l_trohdr_rec.from_subinventory_code := 'FGI';
288 -- l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
289 -- l_trohdr_rec.last_updated_by := 1068;
290 -- l_trohdr_rec.last_update_date := sysdate;
291 -- l_trohdr_rec.last_update_login := 1068;
292 -- l_trohdr_rec.organization_id := 207;
293 -- l_trohdr_rec.status_date := sysdate;
294 -- l_trohdr_rec.to_subinventory_code := 'Stores';
295 -- l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
296 -- l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REQUISITION;
297 -- l_trohdr_rec.db_flag := FND_API.G_TRUE;
298 -- l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
299 --
300 -- INV_Move_Order_PUB.Create_Move_Order_Header(
301 -- p_api_version_number => 1,
302 -- p_init_msg_list => FND_API.G_FALSE,
303 -- p_return_values => FND_API.G_TRUE,
304 -- p_commit => l_commit,
305 -- x_return_status => l_return_status,
309 -- p_trohdr_val_rec => l_trohdr_val_rec,
306 -- x_msg_count => l_msg_count,
307 -- x_msg_data => msg,
308 -- p_trohdr_rec => l_trohdr_rec,
310 -- x_trohdr_rec => l_trohdr_rec,
311 -- x_trohdr_val_rec => l_trohdr_val_rec
312 -- );
313 --
314 -- IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
315 -- FND_MSG_PUB.Add_Exc_Msg
316 -- ( 'INV_Move_Order_PUB'
317 -- , 'Create_move_orders'
318 -- );
319 -- RAISE FND_API.G_EXC_ERROR;
320 -- ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
321 -- FND_MSG_PUB.Add_Exc_Msg
322 -- ( 'INV_Move_Order_PUB'
323 -- , 'Create_move_orders'
324 -- );
325 -- RAISE FND_API.G_EXC_ERROR;
326 -- END IF;
327 -- dbms_output.put_line(l_return_status);
328 -- EXCEPTION
329 --
330 -- WHEN FND_API.G_EXC_ERROR THEN
331 --
332 -- Raise FND_API.G_EXC_ERROR;
333 --
334 -- WHEN OTHERS THEN
335 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
336 -- THEN
337 -- FND_MSG_PUB.Add_Exc_Msg
338 -- ( 'INV_Move_Order_PUB'
339 -- , 'Create_Move_Orders'
340 -- );
341 -- END IF;
342 -- Raise FND_API.G_EXC_UNEXPECTED_ERROR;
343 -- end;
344 --
345 -- Version Current version = 1.0
346 -- Initial version = 1.0
347 --
348 -- End of Comments
349
350 PROCEDURE Create_Move_Order_Header
351 ( p_api_version_number IN NUMBER
352 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
353 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
354 , p_commit IN VARCHAR2 := FND_API.G_FALSE
355 , x_return_status OUT NOCOPY VARCHAR2
356 , x_msg_count OUT NOCOPY NUMBER
357 , x_msg_data OUT NOCOPY VARCHAR2
358 , p_trohdr_rec IN Trohdr_Rec_Type :=
359 G_MISS_TROHDR_REC
360 , p_trohdr_val_rec IN Trohdr_Val_Rec_Type :=
361 G_MISS_TROHDR_VAL_REC
362 , x_trohdr_rec IN OUT NOCOPY Trohdr_Rec_Type
363 , x_trohdr_val_rec IN OUT NOCOPY Trohdr_Val_Rec_Type
364 , p_validation_flag IN VARCHAR2 DEFAULT g_validation_yes
365 );
366
367 -- Start of Comments
368 -- Procedures
369 -- Create_Move_Order_Lines
370 -- Input Parameters
371 -- p_api_version_number API version number (current version is 1.0)
372 --
373 -- p_init_msg_list Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
374 -- if set to FND_API.G_TRUE
375 -- initialize error message list
376 -- if set to FND_API.G_FALSE - not initialize error
377 -- message list
378 -- p_return_values valid values: FND_API.G_FALSE or FND_API.G_TRUE
379 -- p_commit whether or not to commit the changes to database
380 -- p_trolin_tbl a table of records contains information to be used to
381 -- create the move order lines
382 -- p_trohdr_val_tbl contains information values as supposed to internal
383 -- IDs used to create the move order lines
384 --
385 -- Output Parameter:
386 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if succeeded
387 -- = FND_API.G_RET_STS_EXC_ERROR, if an expected error occured
388 -- = FND_API.G_RET_STS_UNEXP_ERROR, if an unexpected error occured
389 --
390 -- x_msg_count Number of error message in the error message list
391 --
392 -- x_msg_data If the number of error message in the error message list is one,
393 -- the error message is in the output parameter
394 --
395 -- x_trohdr_tbl The information of move order lines that got created
396 --
397 -- x_trohdr_val_tbl The information values of move order lines that got created
398 --
399 -- Example
400 -- The following code creates a move order header with the following information:
401 -- header_id 4125 - this header_id is the header number for the move order
402 -- header that got genereated when the create_move_order_header
403 -- is executed
404 -- organization_id 207
405 -- inventory_item_id 155
406 -- from_subinventory_code 'FGI'
407 -- to_subinventory_code 'Stores'
408 -- line_status preapproved
409 --
410 -- declare
411 -- l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
412 -- l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
413 -- l_msg_count NUMBER;
414 -- l_msg_data VARCHAR2(240);
415 -- l_trohdr_val_rec INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
416 -- l_commit VARCHAR2(1) := FND_API.G_TRUE;
417 -- l_order_count NUMBER := 1; /* total number of lines */
418 -- begin
419 -- l_line_num := l_line_num + 1;
420 -- l_trolin_tbl(l_order_count).header_id := l_trohdr_rec.header_id;
421 -- l_trolin_tbl(l_order_count).created_by := FND_GLOBAL.USER_ID;
422 -- l_trolin_tbl(l_order_count).creation_date := sysdate;
423 -- l_trolin_tbl(l_order_count).date_required := sysdate;
424 -- l_trolin_tbl(l_order_count).from_subinventory_code := 'FGI';
425 -- l_trolin_tbl(l_order_count).inventory_item_id := 155;
426 -- l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
430 -- l_trolin_tbl(l_order_count).line_number := l_line_num;
427 -- l_trolin_tbl(l_order_count).last_update_date := sysdate;
428 -- l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
429 -- l_trolin_tbl(l_order_count).line_id := FND_API.G_MISS_NUM;
431 -- l_trolin_tbl(l_order_count).line_status :=
432 -- INV_Globals.G_TO_STATUS_PREAPPROVED;
433 -- l_trolin_tbl(l_order_count).organization_id := 207;
434 -- l_trolin_tbl(l_order_count).quantity := 100;
435 -- l_trolin_tbl(l_order_count).status_date := sysdate;
436 -- l_trolin_tbl(l_order_count).to_subinventory_code := 'Stores';
437 -- l_trolin_tbl(l_order_count).uom_code := 'Ea';
438 -- l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
439 -- l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
440
441 --/*inv_debug.message('calling inv_transfer_order_pvt.process_transfer_order');*/
442 -- INV_Move_Order_PUB.Create_Move_Order_Lines
443 -- ( p_api_version_number => 1.0 ,
444 -- p_init_msg_list => 'T',
445 -- p_commit => FND_API.G_TRUE,
446 -- x_return_status => l_return_status,
447 -- x_msg_count => l_msg_count,
448 -- x_msg_data => l_msg_data,
449 -- p_trolin_tbl => l_trolin_tbl,
450 -- p_trolin_val_tbl => l_trolin_val_tbl,
451 -- x_trolin_tbl => l_trolin_tbl,
452 -- x_trolin_val_tbl => l_trolin_val_tbl
453 -- );
454 --
455 -- l_trohdr_rec.db_flag := FND_API.G_TRUE;
456 -- l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
457 --
458 -- IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
459 -- FND_MSG_PUB.Add_Exc_Msg
460 -- ( 'INV_Move_Order_PUB'
461 -- , 'Create_move_orders'
462 -- );
463 -- RAISE FND_API.G_EXC_ERROR;
464 -- ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
465 -- FND_MSG_PUB.Add_Exc_Msg
466 -- ( 'INV_Move_Order_PUB'
467 -- , 'Create_move_orders'
468 -- );
469 -- RAISE FND_API.G_EXC_ERROR;
470 -- END IF;
471 -- dbms_output.put_line(l_return_status);
472 -- EXCEPTION
473 --
474 -- WHEN FND_API.G_EXC_ERROR THEN
475 --
476 -- Raise FND_API.G_EXC_ERROR;
477 --
478 -- WHEN OTHERS THEN
479 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
480 -- THEN
481 -- FND_MSG_PUB.Add_Exc_Msg
482 -- ( 'INV_Move_Order_PUB'
483 -- , 'Create_Move_Orders'
484 -- );
485 -- END IF;
486 -- Raise FND_API.G_EXC_UNEXPECTED_ERROR;
487 --end;
488 --
489 -- Version Current version = 1.0
490 -- Initial version = 1.0
491 --
492 -- End of Comments
493
494 PROCEDURE Create_Move_Order_Lines
495 ( p_api_version_number IN NUMBER
496 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
497 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
498 , p_commit IN VARCHAR2 := FND_API.G_FALSE
499 , x_return_status OUT NOCOPY VARCHAR2
500 , x_msg_count OUT NOCOPY NUMBER
501 , x_msg_data OUT NOCOPY VARCHAR2
502 , p_trolin_tbl IN Trolin_Tbl_Type :=
503 G_MISS_TROLIN_TBL
504 , p_trolin_val_tbl IN Trolin_Val_Tbl_Type :=
505 G_MISS_TROLIN_VAL_TBL
506 , x_trolin_tbl IN OUT NOCOPY Trolin_Tbl_Type
507 , x_trolin_val_tbl IN OUT NOCOPY Trolin_Val_Tbl_Type
508 , p_validation_flag IN VARCHAR2 := g_validation_yes
509 );
510
511 -- Procedures
512 -- Process_Move_Order_PUB
513 -- This procedure is use to process move orders (both headers and lines)
514 -- i.e., to create or update move orders.
515 -- Input Parameters
516 -- p_api_version_number API version number (current version is 1.0)
517 --
518 -- p_init_msg_list Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
519 -- if set to FND_API.G_TRUE
520 -- initialize error message list
521 -- if set to FND_API.G_FALSE - not initialize error
522 -- message list
523 -- p_return_values valid values: FND_API.G_FALSE or FND_API.G_TRUE
524 -- p_commit whether or not to commit the changes to database
525 -- p_trohdr_rec record contains information to be used to create the
526 -- move order header
527 -- p_trohdr_val_rec contains information values as supposed to internal
528 -- IDs used to create the move order header
529 -- p_trolin_tbl a table of records contains information to be used to
530 -- create the move order lines
531 -- p_trohdr_val_tbl contains information values as supposed to internal
532 -- IDs used to create the move order lines
533 --
534 -- Output Parameter:
535 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if succeeded
536 -- = FND_API.G_RET_STS_EXC_ERROR, if an expected error occured
537 -- = FND_API.G_RET_STS_UNEXP_ERROR, if an unexpected error occured
538 --
539 -- x_msg_count Number of error message in the error message list
540 --
544 -- x_trohdr_rec The information of move order header that got created
541 -- x_msg_data If the number of error message in the error message list is one,
542 -- the error message is in the output parameter
543 --
545 --
546 -- x_trohdr_val_Rec The information values of move order header that got created
547 --
548 -- x_trohdr_tbl The information of move order lines that got created
549 --
550 -- x_trohdr_val_tbl The information values of move order lines that got created
551 --
552 --
553 -- Example
554 -- The following code creates a move order header with the following information:
555 -- Header Information:
556 -- organization_id 207
557 -- default from_subinventory_code 'FGI'
558 -- default to_subinventory_code 'Stores'
559 -- move_order_type 1 (Move Order Requisition)
560 -- default transaction_type : subinventory_transfer
561 -- header_status preapproved
562 -- Line Information:
563 -- inventory_item_id 155
564 -- from_subinventory_code 'FGI'
565 -- to_subinventory_code 'Stores'
566 -- Quantity 100
567 -- Uom_Code 'Ea'
568 -- Line_status: preapproved
569 -- declare
570 -- l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
571 -- l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
572 -- l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
573 -- l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
574 -- l_msg_count NUMBER;
575 -- l_msg_data VARCHAR2(240);
576 -- msg VARCHAR2(2000);
577 -- l_header_id Number := FND_API.G_MISS_NUM;
578 -- l_line_num Number := 0;
579 -- l_order_count NUMBER := 1;
580 -- l_trohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
581 -- l_commit VARCHAR2(1) := FND_API.G_TRUE;
582 -- p_need_by_date DATE := sysdate;
583 -- p_src_subinv VARCHAR2(30);
584 -- begin
585 -- dbms_output.put_line('In create transfer order');
586 -- l_trohdr_rec.created_by := 1068;
587 -- l_trohdr_rec.creation_date := sysdate;
588 -- l_trohdr_rec.date_required := sysdate;
589 -- l_trohdr_rec.from_subinventory_code := 'FGI';
590 -- l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
591 -- l_trohdr_rec.last_updated_by := 1068;
592 -- l_trohdr_rec.last_update_date := sysdate;
593 -- l_trohdr_rec.last_update_login := 1068;
594 -- l_trohdr_rec.organization_id := 207;
595 -- l_trohdr_rec.status_date := sysdate;
596 -- l_trohdr_rec.to_subinventory_code := 'Stores';
597 -- l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
598 -- l_trohdr_rec.db_flag := FND_API.G_TRUE;
599 -- l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
600 --
601 -- l_line_num := l_line_num + 1;
602 -- l_trolin_tbl(l_order_count).header_id := l_trohdr_rec.header_id;
603 -- l_trolin_tbl(l_order_count).created_by := FND_GLOBAL.USER_ID;
604 -- l_trolin_tbl(l_order_count).creation_date := sysdate;
605 -- l_trolin_tbl(l_order_count).date_required := sysdate;
606 -- l_trolin_tbl(l_order_count).from_subinventory_code := 'FGI';
607 -- l_trolin_tbl(l_order_count).inventory_item_id := 155;
608 -- l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
609 -- l_trolin_tbl(l_order_count).last_update_date := sysdate;
610 -- l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
611 -- l_trolin_tbl(l_order_count).last_update_date := sysdate;
612 -- l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
613 -- l_trolin_tbl(l_order_count).line_id := FND_API.G_MISS_NUM;
614 -- l_trolin_tbl(l_order_count).line_number := l_line_num;
615 -- l_trolin_tbl(l_order_count).line_status :=
616 -- INV_Globals.G_TO_STATUS_PREAPPROVED;
617 -- l_trolin_tbl(l_order_count).organization_id := 207;
618 -- l_trolin_tbl(l_order_count).quantity := 100;
619 -- l_trolin_tbl(l_order_count).status_date := sysdate;
620 -- l_trolin_tbl(l_order_count).to_subinventory_code := 'Stores';
621 -- l_trolin_tbl(l_order_count).uom_code := 'Ea';
622 -- l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
623 -- l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
624 --
625 -- /*inv_debug.message('calling inv_transfer_order_pvt.process_transfer_order');*/
626 -- INV_Move_Order_PUB.Process_Move_Order
627 -- ( p_api_version_number => 1.0 ,
628 -- p_init_msg_list => 'T',
629 -- p_commit => FND_API.G_TRUE,
630 -- x_return_status => l_return_status,
631 -- x_msg_count => l_msg_count,
632 -- x_msg_data => l_msg_data,
633 -- p_trohdr_rec => l_trohdr_rec,
634 -- p_trohdr_val_rec => l_trohdr_val_rec,
635 -- p_trolin_tbl => l_trolin_tbl,
636 -- p_trolin_val_tbl => l_trolin_val_tbl,
637 -- x_trohdr_rec => l_trohdr_rec,
638 -- x_trohdr_val_rec => l_trohdr_val_rec,
639 -- x_trolin_tbl => l_trolin_tbl,
640 -- x_trolin_val_tbl => l_trolin_val_tbl
641 -- );
642 --
643 -- IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
647 -- );
644 -- FND_MSG_PUB.Add_Exc_Msg
645 -- ( 'INV_Move_Order_PUB'
646 -- , 'Process_Move_Order'
648 -- RAISE FND_API.G_EXC_ERROR;
649 -- ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
650 -- FND_MSG_PUB.Add_Exc_Msg
651 -- ( 'INV_Move_Order_PUB'
652 -- , 'Process_Move_Order'
653 -- );
654 -- RAISE FND_API.G_EXC_ERROR;
655 -- END IF;
656 -- dbms_output.put_line(l_return_status);
657 -- EXCEPTION
658 --
659 -- WHEN FND_API.G_EXC_ERROR THEN
660 --
661 -- Raise FND_API.G_EXC_ERROR;
662 --
663 -- WHEN OTHERS THEN
664 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
665 -- THEN
666 -- FND_MSG_PUB.Add_Exc_Msg
667 -- ( 'INV_Move_Order_PUB'
668 -- , 'Process_Move_Order'
669 -- );
670 -- END IF;
671 -- );
672 -- END IF;
673 -- Raise FND_API.G_EXC_UNEXPECTED_ERROR;
674 -- end;
675
676
677 --
678 --
679 -- Version Current version = 1.0
680 -- Initial version = 1.0
681 --
682 -- Notes
683 --
684 -- End of Comments
685
686 PROCEDURE Process_Move_Order
687 ( p_api_version_number IN NUMBER
688 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
689 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
690 , p_commit IN VARCHAR2 := FND_API.G_FALSE
691 , x_return_status OUT NOCOPY VARCHAR2
692 , x_msg_count OUT NOCOPY NUMBER
693 , x_msg_data OUT NOCOPY VARCHAR2
694 , p_trohdr_rec IN Trohdr_Rec_Type :=
695 G_MISS_TROHDR_REC
696 , p_trohdr_val_rec IN Trohdr_Val_Rec_Type :=
697 G_MISS_TROHDR_VAL_REC
698 , p_trolin_tbl IN Trolin_Tbl_Type :=
699 G_MISS_TROLIN_TBL
700 , p_trolin_val_tbl IN Trolin_Val_Tbl_Type :=
701 G_MISS_TROLIN_VAL_TBL
702 , x_trohdr_rec IN OUT NOCOPY Trohdr_Rec_Type
703 , x_trohdr_val_rec IN OUT NOCOPY Trohdr_Val_Rec_Type
704 , x_trolin_tbl IN OUT NOCOPY Trolin_Tbl_Type
705 , x_trolin_val_tbl IN OUT NOCOPY Trolin_Val_Tbl_Type
706 );
707
708 -- Start of Comments
709 -- API name Lock_Move_Order
710 -- Type Public
711 -- Function
712 --
713 -- Pre-reqs
714 --
715 -- Parameters
716 --
717 -- Version Current version = 1.0
718 -- Initial version = 1.0
719 --
720 -- Notes
721 --
722 -- End of Comments
723
724 -- Procedures
725 -- Create_Move_Order_Header
726 -- Input Parameters
727 -- p_api_version_number API version number (current version is 1.0)
728 --
729 -- p_init_msg_list Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
730 -- if set to FND_API.G_TRUE
731 -- initialize error message list
732 -- if set to FND_API.G_FALSE - not initialize error
733 -- message list
734 -- p_return_values valid values: FND_API.G_FALSE or FND_API.G_TRUE
735 -- p_commit whether or not to commit the changes to database
736 -- p_trohdr_rec record contains information to be used to lock the
737 -- move order header
738 -- p_trohdr_val_rec contains information values as supposed to internal
739 -- IDs used to lock the move order header
740 -- p_trolin_tbl a table of records contains information to be used to
741 -- lock the move order lines
742 -- p_trohdr_val_tbl contains information values as supposed to internal
743 -- IDs used to lock the move order lines
744 --
745 -- Output Parameter:
746 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if succeeded
747 -- = FND_API.G_RET_STS_EXC_ERROR, if an expected error occured
748 -- = FND_API.G_RET_STS_UNEXP_ERROR, if an unexpected error occured
749 --
750 -- x_msg_count Number of error message in the error message list
751 --
752 -- x_msg_data If the number of error message in the error message list is one,
753 -- the error message is in the output parameter
754 --
755 -- x_trohdr_rec The information of move order header that got locked
756 --
757 -- x_trohdr_val_Rec The information values of move order header that got locked
758 --
759 -- x_trohdr_tbl The move order lines records that got locked
760 --
761 -- x_trohdr_val_tbl The information values of move order lines record that got locked
762 --
763 PROCEDURE Lock_Move_Order
764 ( p_api_version_number IN NUMBER
765 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
766 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
767 , x_return_status OUT NOCOPY VARCHAR2
768 , x_msg_count OUT NOCOPY NUMBER
769 , x_msg_data OUT NOCOPY VARCHAR2
770 , p_trohdr_rec IN Trohdr_Rec_Type :=
771 G_MISS_TROHDR_REC
772 , p_trohdr_val_rec IN Trohdr_Val_Rec_Type :=
773 G_MISS_TROHDR_VAL_REC
774 , p_trolin_tbl IN Trolin_Tbl_Type :=
775 G_MISS_TROLIN_TBL
779 , x_trohdr_val_rec IN OUT NOCOPY Trohdr_Val_Rec_Type
776 , p_trolin_val_tbl IN Trolin_Val_Tbl_Type :=
777 G_MISS_TROLIN_VAL_TBL
778 , x_trohdr_rec IN OUT NOCOPY Trohdr_Rec_Type
780 , x_trolin_tbl IN OUT NOCOPY Trolin_Tbl_Type
781 , x_trolin_val_tbl IN OUT NOCOPY Trolin_Val_Tbl_Type
782 );
783
784 -- Procedures
785 -- Get_Move_Order
786 -- Input Parameters
787 -- p_api_version_number API version number (current version is 1.0)
788 --
789 -- p_init_msg_list Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
790 -- if set to FND_API.G_TRUE
791 -- initialize error message list
792 -- if set to FND_API.G_FALSE - not initialize error
793 -- message list
794 -- p_return_values valid values: FND_API.G_FALSE or FND_API.G_TRUE
795 -- p_commit whether or not to commit the changes to database
796 -- p_header_id the header_id of the transfer order that you want to get
797 -- p_header the header description of the transfer order that you want to get
798 --
799 -- Output Parameter:
800 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if succeeded
801 -- = FND_API.G_RET_STS_EXC_ERROR, if an expected error occured
802 -- = FND_API.G_RET_STS_UNEXP_ERROR, if an unexpected error occured
803 --
804 -- x_msg_count Number of error message in the error message list
805 --
806 -- x_msg_data If the number of error message in the error message list is one,
807 -- the error message is in the output parameter
808 --
809 -- x_trohdr_rec The information of move order header with the header_id requested
810 --
811 -- x_trohdr_val_Rec The information values of move order header for the requested header_id
812 --
813 -- x_trohdr_tbl The move order lines records for the requested header_id
814 --
815 -- x_trohdr_val_tbl The information values of move order lines record for the requested
816 -- header_id
817 --
818
819 PROCEDURE Get_Move_Order
820 ( p_api_version_number IN NUMBER
821 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
822 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
823 , x_return_status OUT NOCOPY VARCHAR2
824 , x_msg_count OUT NOCOPY NUMBER
825 , x_msg_data OUT NOCOPY VARCHAR2
826 , p_header_id IN NUMBER :=
827 FND_API.G_MISS_NUM
828 , p_header IN VARCHAR2 :=
829 FND_API.G_MISS_CHAR
830 , x_trohdr_rec OUT NOCOPY Trohdr_Rec_Type
831 , x_trohdr_val_rec OUT NOCOPY Trohdr_Val_Rec_Type
832 , x_trolin_tbl OUT NOCOPY Trolin_Tbl_Type
833 , x_trolin_val_tbl OUT NOCOPY Trolin_Val_Tbl_Type
834 );
835
836 PROCEDURE Process_Move_Order_Line
837 (
838 p_api_version_number IN NUMBER
839 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
840 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
841 , p_commit IN VARCHAR2 := FND_API.G_TRUE
842 , x_return_status OUT NOCOPY VARCHAR2
843 , x_msg_count OUT NOCOPY NUMBER
844 , x_msg_data OUT NOCOPY VARCHAR2
845 , p_trolin_tbl IN Trolin_Tbl_Type
846 , p_trolin_old_tbl IN Trolin_Tbl_Type
847 , x_trolin_tbl IN OUT NOCOPY Trolin_Tbl_Type
848 );
849
850 -- For Prallel Pick-Release
851
852 PROCEDURE stamp_cart_id
853 (
854 p_validation_level IN NUMBER
855 , p_carton_grouping_tbl IN inv_move_order_pub.num_tbl_type
856 , p_move_order_line_tbl IN inv_move_order_pub.num_tbl_type
857 );
858
859
860 END INV_Move_Order_PUB;