1 PACKAGE BODY WMS_ITEM_LOAD AS
2 /* $Header: WMSTKILB.pls 120.13.12020000.2 2012/07/04 06:37:43 abasheer ship $ */
3
4
5 -- Global constant holding the package name
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_ITEM_LOAD';
7
8
9 PROCEDURE print_debug(p_debug_msg IN VARCHAR2)
10 IS
11 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
12 BEGIN
13 IF (l_debug = 1) THEN
14 inv_mobile_helper_functions.tracelog
15 (p_err_msg => p_debug_msg,
16 p_module => 'WMS_ITEM_LOAD',
17 p_level => 4);
18 END IF;
19
20 END;
21
22
23 PROCEDURE get_available_qty
24 (p_organization_id IN NUMBER ,
25 p_lpn_id IN NUMBER ,
26 p_inventory_item_id IN NUMBER ,
27 p_revision IN VARCHAR2 ,
28 p_prim_uom_code IN VARCHAR2 ,
29 p_uom_code IN VARCHAR2 ,
30 x_return_status OUT NOCOPY VARCHAR2 ,
31 x_msg_count OUT NOCOPY NUMBER ,
32 x_msg_data OUT NOCOPY VARCHAR2 ,
33 x_available_qty OUT NOCOPY NUMBER ,
34 x_total_qty OUT NOCOPY NUMBER)--Added for bug 5002690
35 IS
36 l_api_name CONSTANT VARCHAR2(30) := 'get_available_qty';
37 l_progress VARCHAR2(10);
38 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
39
40 -- Variables used to call validate_operation API
41 l_error_code NUMBER;
42 l_inspection_flag NUMBER;
43 l_load_flag NUMBER;
44 l_drop_flag NUMBER;
45 l_load_prim_quantity NUMBER;
46 l_drop_prim_quantity NUMBER;
47 l_inspect_prim_quantity NUMBER;
48
49 BEGIN
50 IF (l_debug = 1) THEN
51 print_debug('***Calling get_available_qty with the following parameters***');
52 print_debug('p_organization_id: ===> ' || p_organization_id);
53 print_debug('p_lpn_id: ============> ' || p_lpn_id);
54 print_debug('p_inventory_item_id: => ' || p_inventory_item_id);
55 print_debug('p_revision: ==========> ' || p_revision);
56 print_debug('p_prim_uom_code: =====> ' || p_prim_uom_code);
57 print_debug('p_uom_code: ==========> ' || p_uom_code);
58 END IF;
59
60 -- Set the savepoint
61 SAVEPOINT get_available_qty_sp;
62 l_progress := '10';
63
64 -- Initialize message list to clear any existing messages
65 fnd_msg_pub.initialize;
66 l_progress := '20';
67
68 -- Initialize the output variables
69 x_return_status := fnd_api.g_ret_sts_success;
70 x_available_qty := 0;
71 l_progress := '30';
72
73 -- Bug# 3401739
74 -- Call the ATF runtime API validate_operation to get the
75 -- available quantity of item to load for the given
76 -- org/LPN/item/revision.
77 IF (l_debug = 1) THEN
78 print_debug('Call validate_operation API for the given LPN/item/revision');
79 END IF;
80 wms_atf_runtime_pub_apis.validate_operation
81 (x_return_status => x_return_status,
82 x_msg_data => x_msg_data,
83 x_msg_count => x_msg_count,
84 x_error_code => l_error_code,
85 x_inspection_flag => l_inspection_flag,
86 x_load_flag => l_load_flag,
87 x_drop_flag => l_drop_flag,
88 x_load_prim_quantity => l_load_prim_quantity,
89 x_drop_prim_quantity => l_drop_prim_quantity,
90 x_inspect_prim_quantity => l_inspect_prim_quantity,
91 p_source_task_id => NULL,
92 p_move_order_line_id => NULL,
93 p_inventory_item_id => p_inventory_item_id,
94 p_lpn_id => p_lpn_id,
95 p_activity_type_id => WMS_GLOBALS.G_OP_ACTIVITY_INBOUND,
96 p_organization_id => p_organization_id,
97 p_lot_number => NULL,
98 p_revision => p_revision);
99
100 IF (l_debug = 1) THEN
101 print_debug('Finished calling the validate_operation API');
102 END IF;
103 l_progress := '40';
104
105 -- Check to see if the validate_operation API returned successfully
106 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
107 IF (l_debug = 1) THEN
108 print_debug('Success returned from validate_operation API');
109 END IF;
110 ELSE
111 IF (l_debug = 1) THEN
112 print_debug('Failure returned from validate_operation API');
113 print_debug('Error code: ' || l_error_code);
114 END IF;
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117
118 -- Now check to see if this move order line is allowed to be
119 -- loaded and if so, how much quantity from the line is available
120 IF (l_debug = 1) THEN
121 print_debug('Values returned from call to validate_operation');
122 print_debug('x_inspection_flag: =======> ' || l_inspection_flag);
123 print_debug('x_load_flag: =============> ' || l_load_flag);
124 print_debug('x_drop_flag: =============> ' || l_drop_flag);
125 print_debug('x_load_prim_quantity: ====> ' || l_load_prim_quantity);
126 print_debug('x_drop_prim_quantity: ====> ' || l_drop_prim_quantity);
127 print_debug('x_inspect_prim_quantity: => ' || l_inspect_prim_quantity);
128 END IF;
129
130 -- Convert the available load quantity from the primary UOM
131 -- to the given user inputted UOM if different
132 IF (l_debug = 1) THEN
133 print_debug('Call inv_um_convert to convert the load quantity');
134 END IF;
135 IF (p_uom_code <> p_prim_uom_code) THEN
136 x_available_qty := inv_convert.inv_um_convert (p_inventory_item_id,
137 5,
138 l_load_prim_quantity,
139 p_prim_uom_code,
140 p_uom_code,
141 NULL,
142 NULL
143 );
144 ELSE
145 x_available_qty := l_load_prim_quantity;
146 END IF;
147
148 --bug 5002690 BEGIN
149 BEGIN
150 SELECT SUM(Decode(uom_code,
151 p_uom_code,
152 quantity,
153 inv_convert.inv_um_convert (p_inventory_item_id,
154 5,
155 quantity,
156 uom_code,
157 p_uom_code,
158 NULL,
159 NULL
160 )
161 ))
162 INTO x_total_qty
163 FROM wms_lpn_contents
164 WHERE inventory_item_id = p_inventory_item_id
165 AND organization_id = p_organization_id
166 AND parent_lpn_id = p_lpn_id
167 AND Nvl(revision,'#$%') = Nvl(p_revision,'#$%');
168 EXCEPTION
169 WHEN OTHERS THEN
170 IF (l_debug = 1) THEN
171 print_debug('Exception querying total qty');
172 END IF;
173 x_total_qty := -1;
174 RAISE FND_API.G_EXC_ERROR;
175 END;
176 --bug 5002690 END
177
178 IF (l_debug = 1) THEN
179 print_debug('Total qty:'||x_total_qty);
180 print_debug('Available quantity in inputted UOM is: ' || x_available_qty);
181 END IF;
182 l_progress := '50';
183
184 IF (l_debug = 1) THEN
185 print_debug('***End of get_available_qty***');
186 END IF;
187
188 EXCEPTION
189 WHEN FND_API.G_EXC_ERROR THEN
190 ROLLBACK TO get_available_qty_sp;
191 x_return_status := fnd_api.g_ret_sts_error;
192 fnd_msg_pub.count_and_get(p_count => x_msg_count,
193 p_data => x_msg_data);
194 IF (l_debug = 1) THEN
195 print_debug('Exiting get_available_qty - Execution error: ' ||
196 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
197 END IF;
198
199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
200 ROLLBACK TO get_available_qty_sp;
201 x_return_status := fnd_api.g_ret_sts_unexp_error;
202 fnd_msg_pub.count_and_get(p_count => x_msg_count,
203 p_data => x_msg_data);
204 IF (l_debug = 1) THEN
205 print_debug('Exiting get_available_qty - Unexpected error: ' ||
206 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
207 END IF;
208
209 WHEN OTHERS THEN
210 ROLLBACK TO get_available_qty_sp;
211 x_return_status := fnd_api.g_ret_sts_unexp_error;
212 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
213 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
214 END IF;
215 fnd_msg_pub.count_and_get(p_count => x_msg_count,
216 p_data => x_msg_data);
217 IF (l_debug = 1) THEN
218 print_debug('Exiting get_available_qty - Others exception: ' ||
219 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
220 END IF;
221
222 END get_available_qty;
223
224
225 PROCEDURE get_available_lot_qty
226 (p_organization_id IN NUMBER ,
227 p_lpn_id IN NUMBER ,
228 p_inventory_item_id IN NUMBER ,
229 p_revision IN VARCHAR2 ,
230 p_lot_number IN VARCHAR2 ,
231 p_prim_uom_code IN VARCHAR2 ,
232 p_uom_code IN VARCHAR2 ,
233 x_return_status OUT NOCOPY VARCHAR2 ,
234 x_msg_count OUT NOCOPY NUMBER ,
235 x_msg_data OUT NOCOPY VARCHAR2 ,
236 x_available_lot_qty OUT NOCOPY NUMBER)
237 IS
238 l_api_name CONSTANT VARCHAR2(30) := 'get_available_lot_qty';
239 l_progress VARCHAR2(10);
240 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
241
242 -- Variables used to call validate_operation API
243 l_error_code NUMBER;
244 l_inspection_flag NUMBER;
245 l_load_flag NUMBER;
246 l_drop_flag NUMBER;
247 l_load_prim_quantity NUMBER;
248 l_drop_prim_quantity NUMBER;
249 l_inspect_prim_quantity NUMBER;
250
251 BEGIN
252 IF (l_debug = 1) THEN
253 print_debug('***Calling get_available_lot_qty with the following parameters***');
254 print_debug('p_organization_id: ===> ' || p_organization_id);
255 print_debug('p_lpn_id: ============> ' || p_lpn_id);
256 print_debug('p_inventory_item_id: => ' || p_inventory_item_id);
257 print_debug('p_revision: ==========> ' || p_revision);
258 print_debug('p_lot_number: ========> ' || p_lot_number);
259 print_debug('p_prim_uom_code: =====> ' || p_prim_uom_code);
260 print_debug('p_uom_code: ==========> ' || p_uom_code);
261 END IF;
262
263 -- Set the savepoint
264 SAVEPOINT get_available_lot_qty_sp;
265 l_progress := '10';
266
267 -- Initialize message list to clear any existing messages
268 fnd_msg_pub.initialize;
269 l_progress := '20';
270
271 -- Initialize the output variables
272 x_return_status := fnd_api.g_ret_sts_success;
273 x_available_lot_qty := 0;
274 l_progress := '30';
275
276 -- Bug# 3401739
277 -- Call the ATF runtime API validate_operation to get the
278 -- available quantity of item to load for the given
279 -- org/LPN/item/revision/lot.
280 IF (l_debug = 1) THEN
281 print_debug('Call validate_operation API for the given LPN/item/revision/lot');
282 END IF;
283 wms_atf_runtime_pub_apis.validate_operation
284 (x_return_status => x_return_status,
285 x_msg_data => x_msg_data,
286 x_msg_count => x_msg_count,
287 x_error_code => l_error_code,
288 x_inspection_flag => l_inspection_flag,
289 x_load_flag => l_load_flag,
290 x_drop_flag => l_drop_flag,
291 x_load_prim_quantity => l_load_prim_quantity,
292 x_drop_prim_quantity => l_drop_prim_quantity,
293 x_inspect_prim_quantity => l_inspect_prim_quantity,
294 p_source_task_id => NULL,
295 p_move_order_line_id => NULL,
296 p_inventory_item_id => p_inventory_item_id,
297 p_lpn_id => p_lpn_id,
298 p_activity_type_id => WMS_GLOBALS.G_OP_ACTIVITY_INBOUND,
299 p_organization_id => p_organization_id,
300 p_lot_number => p_lot_number,
301 p_revision => p_revision);
302
303 IF (l_debug = 1) THEN
304 print_debug('Finished calling the validate_operation API');
305 END IF;
306 l_progress := '40';
307
308 -- Check to see if the validate_operation API returned successfully
309 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
310 IF (l_debug = 1) THEN
311 print_debug('Success returned from validate_operation API');
312 END IF;
313 ELSE
314 IF (l_debug = 1) THEN
315 print_debug('Failure returned from validate_operation API');
316 print_debug('Error code: ' || l_error_code);
317 END IF;
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320
321 -- Now check to see if this move order line is allowed to be
322 -- loaded and if so, how much quantity from the line is available
323 IF (l_debug = 1) THEN
324 print_debug('Values returned from call to validate_operation');
325 print_debug('x_inspection_flag: =======> ' || l_inspection_flag);
326 print_debug('x_load_flag: =============> ' || l_load_flag);
327 print_debug('x_drop_flag: =============> ' || l_drop_flag);
328 print_debug('x_load_prim_quantity: ====> ' || l_load_prim_quantity);
329 print_debug('x_drop_prim_quantity: ====> ' || l_drop_prim_quantity);
330 print_debug('x_inspect_prim_quantity: => ' || l_inspect_prim_quantity);
331 END IF;
332
333 -- Convert the available load quantity from the primary UOM
334 -- to the given user inputted UOM if different
335 IF (l_debug = 1) THEN
336 print_debug('Call inv_um_convert to convert the load quantity');
337 END IF;
338 IF (p_uom_code <> p_prim_uom_code) THEN
339 x_available_lot_qty := inv_convert.inv_um_convert (p_inventory_item_id,
340 5,
341 l_load_prim_quantity,
342 p_prim_uom_code,
343 p_uom_code,
344 NULL,
345 NULL
346 );
347 ELSE
348 x_available_lot_qty := l_load_prim_quantity;
349 END IF;
350 IF (l_debug = 1) THEN
351 print_debug('Available lot quantity in inputted UOM is: ' || x_available_lot_qty);
352 END IF;
353 l_progress := '50';
354
355 IF (l_debug = 1) THEN
356 print_debug('***End of get_available_lot_qty***');
357 END IF;
358
359 EXCEPTION
360 WHEN FND_API.G_EXC_ERROR THEN
361 ROLLBACK TO get_available_lot_qty_sp;
362 x_return_status := fnd_api.g_ret_sts_error;
363 fnd_msg_pub.count_and_get(p_count => x_msg_count,
364 p_data => x_msg_data);
365 IF (l_debug = 1) THEN
366 print_debug('Exiting get_available_lot_qty - Execution error: ' ||
367 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
368 END IF;
369
370 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
371 ROLLBACK TO get_available_lot_qty_sp;
372 x_return_status := fnd_api.g_ret_sts_unexp_error;
373 fnd_msg_pub.count_and_get(p_count => x_msg_count,
374 p_data => x_msg_data);
375 IF (l_debug = 1) THEN
376 print_debug('Exiting get_available_lot_qty - Unexpected error: ' ||
377 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
378 END IF;
379
380 WHEN OTHERS THEN
381 ROLLBACK TO get_available_lot_qty_sp;
382 x_return_status := fnd_api.g_ret_sts_unexp_error;
383 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
384 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
385 END IF;
386 fnd_msg_pub.count_and_get(p_count => x_msg_count,
387 p_data => x_msg_data);
388 IF (l_debug = 1) THEN
389 print_debug('Exiting get_available_lot_qty - Others exception: ' ||
390 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
391 END IF;
392
393 END get_available_lot_qty;
394
395
396
397 PROCEDURE pre_process_load
398 (p_organization_id IN NUMBER ,
399 p_lpn_id IN NUMBER ,
400 p_inventory_item_id IN NUMBER ,
401 p_revision IN VARCHAR2 ,
402 p_lot_number IN VARCHAR2 ,
403 p_quantity IN NUMBER ,
404 p_uom_code IN VARCHAR2 ,
405 --laks
406 p_sec_quantity IN NUMBER ,
407 p_sec_uom_code IN VARCHAR2 ,
408 p_user_id IN NUMBER ,
409 p_into_lpn_id IN NUMBER ,
410 p_serial_txn_temp_id IN NUMBER ,
411 p_txn_header_id IN OUT NOCOPY NUMBER ,
412 x_return_status OUT NOCOPY VARCHAR2 ,
413 x_msg_count OUT NOCOPY NUMBER ,
414 x_msg_data OUT NOCOPY VARCHAR2)
415 IS
416 l_api_name CONSTANT VARCHAR2(30) := 'pre_process_load';
417 l_progress VARCHAR2(10);
418 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
419 l_lpn_context NUMBER;
420 -- Variables used to lock the WLC (WMS_LPN_CONTENTS) records.
421 -- WLC records should always exist for the given LPN if an item
422 -- load on the java side has been done.
423 CURSOR lock_wlc_cursor IS
424 SELECT lpn_content_id
425 FROM wms_lpn_contents
426 WHERE parent_lpn_id = p_lpn_id
427 AND inventory_item_id = p_inventory_item_id
428 AND organization_id = p_organization_id
429 AND NVL(revision, '###') = NVL(p_revision, '###')
430 AND NVL(lot_number, '###') = NVL(p_lot_number, '###')
431 FOR UPDATE NOWAIT;
432 record_locked EXCEPTION;
433 PRAGMA EXCEPTION_INIT(record_locked, -54);
434 -- Primary load quantity variables used to convert
435 -- the load transaction UOM values into primary UOM
436 l_primary_load_qty NUMBER;
437 l_primary_uom_code VARCHAR2(3);
438 -- laks
439 l_sec_load_qty NUMBER;
440 l_sec_uom_code VARCHAR2(3);
441 -- Move order line variables with quantity
442 -- in the move order line's transaction UOM
443 l_mo_line_id NUMBER;
444 l_mo_qty_avail NUMBER;
445 -- laks
446 l_mo_sec_qty_avail NUMBER;
447 l_mo_uom_code VARCHAR2(3);
448 -- Variables used to check if move order lines exist
449 -- and to call create_mo if necessary
450 l_mo_line_count NUMBER;
451 l_mo_header_id NUMBER;
452 -- Primary UOM quantity variables used when
453 -- matching move order line(s)
454 l_primary_qty_avail NUMBER;
455 l_primary_qty_used NUMBER;
456 l_primary_qty_left NUMBER;
457 -- laks
458 l_sec_qty_avail NUMBER;
459 l_sec_qty_used NUMBER;
460 l_sec_qty_left NUMBER;
461 -- Move order line table to store the move order line(s)
462 -- used to match against the load entry values
463 l_mo_lines_tb inv_rcv_integration_apis.mo_in_tb_tp;
464 l_tmp_mo_lines_tb inv_rcv_integration_apis.mo_in_tb_tp;
465 l_index NUMBER;
466 -- Cursor to get move order lines with available quantity
467 -- which match the item load entry parameters
468 CURSOR mo_lines_cursor IS
469 SELECT mtrl.line_id,
470 mtrl.quantity - NVL(mtrl.quantity_delivered, 0),
471 mtrl.uom_code,
472 --laks
473 mtrl.secondary_quantity - NVL(mtrl.secondary_quantity_delivered, 0),
474 mtrl.secondary_uom_code
475 FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
476 WHERE mtrl.organization_id = p_organization_id
477 AND mtrl.lpn_id = p_lpn_id
478 AND mtrl.inventory_item_id = p_inventory_item_id
479 AND NVL(mtrl.revision, '###') = NVL(p_revision, '###')
480 AND NVL(mtrl.lot_number, '###') = NVL(p_lot_number, '###')
481 AND mtrl.quantity <> NVL(mtrl.quantity_delivered, 0)
482 AND mtrl.line_status <> inv_globals.g_to_status_closed
483 AND mtrl.header_id = mtrh.header_id
484 AND mtrh.move_order_type = inv_globals.g_move_order_put_away
485 ORDER BY 2 DESC;
486 -- Variables used for matching move order lines
487 -- when the item is serial controlled
488 l_current_serial VARCHAR2(30);
489 l_serial_quantity NUMBER;
490 l_is_new_entry BOOLEAN;
491 l_table_index NUMBER;
492
493 -- Cursor to get the move order lines for INV/RCV serials
494 CURSOR mol_ser_csr_for_inv_rcv IS
495 SELECT mtrl.line_id
496 , Nvl(mtrl.inspection_status,0) inspection_status
497 , quantity-Nvl(quantity_delivered,0) avail_qty
498 FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
499 WHERE mtrl.organization_id = p_organization_id
500 AND mtrl.lpn_id = p_lpn_id
501 AND mtrl.inventory_item_id = p_inventory_item_id
502 AND NVL(mtrl.revision, '###') = NVL(p_revision, '###')
503 AND NVL(mtrl.lot_number, '###') = NVL(p_lot_number, '###')
504 AND mtrl.quantity <> NVL(mtrl.quantity_delivered, 0)
505 AND ((l_lpn_context = 3) OR
506 (l_lpn_context = 1 AND l_mo_line_count = 0))
507 AND mtrl.line_status <> inv_globals.g_to_status_closed
508 AND mtrl.header_id = mtrh.header_id
509 AND mtrh.move_order_type = inv_globals.g_move_order_put_away
510 ORDER BY mtrl.inspection_status;
511
512 TYPE mol_ser_tb IS TABLE OF mol_ser_csr_for_inv_rcv%ROWTYPE;
513 l_mol_ser_tb mol_ser_tb;
514
515 -- Cursor to get the move order lines for WIP serials
516 CURSOR mol_ser_csr_for_wip IS
517 SELECT mtrl.line_id
518 FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
519 WHERE mtrl.organization_id = p_organization_id
520 AND mtrl.lpn_id = p_lpn_id
521 AND mtrl.inventory_item_id = p_inventory_item_id
522 AND NVL(mtrl.revision, '###') = NVL(p_revision, '###')
523 AND NVL(mtrl.lot_number, '###') = NVL(p_lot_number, '###')
524 AND mtrl.quantity <> NVL(mtrl.quantity_delivered, 0)
525 AND l_lpn_context = 2
526 AND mtrl.reference_id IN (SELECT header_id
527 FROM wip_lpn_completions_serials
528 WHERE l_current_serial BETWEEN fm_serial_number AND
529 to_serial_number
530 AND NVL(lot_number, '###') = NVL(p_lot_number, '###'))
531 AND mtrl.line_status <> inv_globals.g_to_status_closed
532 AND mtrl.header_id = mtrh.header_id
533 AND mtrh.move_order_type = inv_globals.g_move_order_put_away;
534
535 -- Cursor to get the marked serials
536 CURSOR marked_serials_cursor IS
537 SELECT serial_number
538 , Nvl(inspection_status,0) inspection_status
539 FROM mtl_serial_numbers
540 WHERE inventory_item_id = p_inventory_item_id
541 AND current_organization_id = p_organization_id
542 AND NVL(revision, '###') = NVL(p_revision, '###')
543 AND NVL(lot_number, '###') = NVL(p_lot_number, '###')
544 AND lpn_id = p_lpn_id
545 AND group_mark_id = p_serial_txn_temp_id
546 AND EXISTS (SELECT 1
547 FROM mtl_serial_numbers_temp
548 WHERE transaction_temp_id = p_serial_txn_temp_id
549 AND serial_number BETWEEN fm_serial_number AND
550 to_serial_number)
551 ORDER BY Nvl(inspection_status,0),LPAD(serial_number, 20);
552
553 TYPE marked_serials_tb IS TABLE OF marked_serials_cursor%ROWTYPE;
554 l_marked_serials_tb marked_serials_tb;
555
556 -- Variables used to call validate_operation API
557 l_inspection_flag NUMBER;
558 l_load_flag NUMBER;
559 l_drop_flag NUMBER;
560 l_load_prim_quantity NUMBER;
561 l_drop_prim_quantity NUMBER;
562 l_inspect_prim_quantity NUMBER;
563 -- laks
564 l_load_sec_quantity NUMBER;
565 l_drop_sec_quantity NUMBER;
566 l_inspect_sec_quantity NUMBER;
567 l_error_code NUMBER;
568 -- Variables used to call split_mo API
569 l_mo_split_tb inv_rcv_integration_apis.mo_in_tb_tp;
570 -- Variables used to call insert MMTT API
571 l_return NUMBER;
572 l_txn_temp_id NUMBER;
573 l_subinv_code VARCHAR2(10);
574 l_tosubinv_code VARCHAR2(10);
575 l_locator_id NUMBER;
576 l_tolocator_id NUMBER;
577 l_cost_group_id NUMBER;
578 l_txn_src_id NUMBER;
579 l_project_id NUMBER;
580 l_task_id NUMBER;
581 l_trx_src_type_id NUMBER;
582 l_trx_type_id NUMBER;
583 l_trx_action_id NUMBER;
584 l_xfr_org_id NUMBER := p_organization_id;
585 l_trx_qty NUMBER;
586 -- Variables used to call insert MTLT API
587 l_ser_trx_id NUMBER;
588
589 -- Cursor used to get the marked serials tied to a specific MOL.
590 -- Note that in the WIP case, we are making the assumption that
591 -- the fm_serial_number is always the same as the to_serial_number.
592 -- It seems that for WIP LPN completions with serials, each record in
593 -- WIP_LPN_COMPLETIONS_SERIALS contains one and only one serial.
594 CURSOR matched_wip_serials_cursor IS
595 SELECT wlcs.fm_serial_number
596 FROM wip_lpn_completions_serials wlcs, mtl_serial_numbers msn
597 WHERE l_lpn_context = 2
598 AND NVL(wlcs.lot_number, '###') = NVL(p_lot_number, '###')
599 AND wlcs.header_id IN (SELECT reference_id
600 FROM mtl_txn_request_lines
601 WHERE line_id = l_mo_line_id
602 AND organization_id = p_organization_id)
603 AND wlcs.fm_serial_number = msn.serial_number
604 AND msn.inventory_item_id = p_inventory_item_id
605 AND msn.current_organization_id = p_organization_id
606 AND NVL(msn.revision, '###') = NVL(p_revision, '###')
607 AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
608 AND msn.lpn_id = p_lpn_id
609 AND msn.group_mark_id = p_serial_txn_temp_id
610 AND EXISTS (SELECT 1
611 FROM mtl_serial_numbers_temp msnt
612 WHERE msnt.transaction_temp_id = p_serial_txn_temp_id
613 AND msn.serial_number BETWEEN msnt.fm_serial_number AND
614 msnt.to_serial_number);
615
616 l_dummy NUMBER;
617 l_serial_matched NUMBER;
618 l_tmp NUMBER;
619
620 --BUG 5194761
621 l_cdock_flag NUMBER;
622 l_ret_crossdock NUMBER;
623 l_backorder_delivery_detail_id NUMBER;
624 l_to_sub_code VARCHAR2(30);
625 l_to_loc_id NUMBER;
626 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
627 l_msg_count NUMBER;
628 l_msg_data VARCHAR2(2400);
629 l_tmp_index NUMBER;
630 --END BUG 5194761
631
632 TYPE serial_tb IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
633 TYPE mol_serial_tb IS TABLE OF serial_tb INDEX BY BINARY_INTEGER;
634 l_mol_serial_tb_b4_splt mol_serial_tb;
635 l_mol_serial_tb_af_splt mol_serial_tb;
636
637 BEGIN
638 IF (l_debug = 1) THEN
639 print_debug('***Calling pre_process_load with the following parameters***');
640 print_debug('p_organization_id: ====> ' || p_organization_id);
641 print_debug('p_lpn_id: =============> ' || p_lpn_id);
642 print_debug('p_inventory_item_id: ==> ' || p_inventory_item_id);
643 print_debug('p_revision: ===========> ' || p_revision);
644 print_debug('p_lot_number: =========> ' || p_lot_number);
645 print_debug('p_quantity: ===========> ' || p_quantity);
646 print_debug('p_uom_code: ===========> ' || p_uom_code);
647 -- laks
648 print_debug('p_sec_quantity: ===========> ' || p_sec_quantity);
649 print_debug('p_uom_code: ===========> ' || p_sec_uom_code);
650 print_debug('p_user_id: ============> ' || p_user_id);
651 print_debug('p_into_lpn_id: ========> ' || p_into_lpn_id);
652 print_debug('p_serial_txn_temp_id: => ' || p_serial_txn_temp_id);
653 print_debug('p_txn_header_id: ======> ' || p_txn_header_id);
654 END IF;
655
656 -- Set the savepoint
657 SAVEPOINT pre_process_load_sp;
658 l_progress := '10';
659
660 -- Initialize message list to clear any existing messages
661 fnd_msg_pub.initialize;
662 l_progress := '15';
663
664 -- Set the return status to success
665 x_return_status := fnd_api.g_ret_sts_success;
666 l_progress := '20';
667
668 -- Lock the LPN contents record(s) to make sure
669 -- nobody else is processing them
670 IF (l_debug = 1) THEN
671 print_debug('Lock the WLC records for the item/rev/lot/LPN combination');
672 END IF;
673 BEGIN
674 OPEN lock_wlc_cursor;
675 CLOSE lock_wlc_cursor;
676 EXCEPTION
677 WHEN NO_DATA_FOUND THEN
678 IF (l_debug = 1) THEN
679 print_debug('No WLC records found for given item/rev/lot/LPN combination!');
680 END IF;
681 FND_MESSAGE.SET_NAME('INV', 'INV_NO_RESULT_FOUND');
682 FND_MSG_PUB.ADD;
683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
684 WHEN RECORD_LOCKED THEN
685 IF (l_debug = 1) THEN
686 print_debug('WLC record not available because it is locked by someone');
687 END IF;
688 FND_MESSAGE.SET_NAME('WMS', 'WMS_LPN_UNAVAIL');
689 FND_MSG_PUB.ADD;
690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
691 END;
692 IF (l_debug = 1) THEN
693 print_debug('Successfully locked the WLC records');
694 END IF;
695 l_progress := '25';
696
697 -- Get a new transaction header id from the sequence
698 -- if a value is not passed in
699 IF (p_txn_header_id = -999) THEN
700 SELECT mtl_material_transactions_s.NEXTVAL INTO p_txn_header_id FROM dual;
701 END IF;
702 IF (l_debug = 1) THEN
703 print_debug('Transaction header ID: ' || p_txn_header_id);
704 END IF;
705 l_progress := '30';
706
707 -- Get the sub, loc and context of the source LPN ID
708 SELECT lpn_context, NVL(subinventory_code, '###'), NVL(locator_id, -999)
709 INTO l_lpn_context, l_subinv_code, l_locator_id
710 FROM wms_license_plate_numbers
711 WHERE lpn_id = p_lpn_id
712 AND organization_id = p_organization_id;
713 IF (l_debug = 1) THEN
714 print_debug('Source LPN Context: => ' || l_lpn_context);
715 print_debug('Source LPN Sub: =====> ' || l_subinv_code);
716 print_debug('Source LPN Loc: =====> ' || l_locator_id);
717 END IF;
718 l_progress := '40';
719
720 -- Get the sub and loc of the destination LPN ID
721 SELECT NVL(subinventory_code, '###'), NVL(locator_id, -999)
722 INTO l_tosubinv_code, l_tolocator_id
723 FROM wms_license_plate_numbers
724 WHERE organization_id = p_organization_id
725 AND lpn_id = p_into_lpn_id;
726 IF (l_debug = 1) THEN
727 print_debug('Into LPN Sub: =======> ' || l_tosubinv_code);
728 print_debug('Into LPN Loc: =======> ' || l_tolocator_id);
729 END IF;
730 l_progress := '50';
731
732 -- Reset the values to null if necessary
733 IF (l_subinv_code = '###') THEN
734 l_subinv_code := NULL;
735 END IF;
736 IF (l_locator_id = -999) THEN
737 l_locator_id := NULL;
738 END IF;
739 IF (l_tosubinv_code = '###') THEN
740 l_tosubinv_code := NULL;
741 END IF;
742 IF (l_tolocator_id = -999) THEN
743 l_tolocator_id := NULL;
744 END IF;
745 l_progress := '60';
746
747 -- Get the item's primary uom code
748 SELECT primary_uom_code
749 INTO l_primary_uom_code
750 FROM mtl_system_items
751 WHERE inventory_item_id = p_inventory_item_id
752 AND organization_id = p_organization_id;
753 IF (l_debug = 1) THEN
754 print_debug('Item primary UOM code: ' || l_primary_uom_code);
755 END IF;
756 l_progress := '70';
757
758 -- Convert the item load quantity into the primary quantity if necessary
759 IF (p_uom_code <> l_primary_uom_code) THEN
760 l_primary_load_qty :=
761 inv_convert.inv_um_convert (p_inventory_item_id,
762 5,
763 p_quantity,
764 p_uom_code,
765 l_primary_uom_code,
766 NULL,
767 NULL);
768 ELSE
769 l_primary_load_qty := p_quantity;
770 END IF;
771
772 --laks
773 l_sec_load_qty := p_sec_quantity;
774 IF (l_debug = 1) THEN
775 print_debug('Load quantity in primary UOM: ' || l_primary_load_qty);
776 -- laks
777 print_debug('Load quantity in secondary UOM: ' || l_sec_load_qty);
778 END IF;
779 l_progress := '80';
780
781 -- Check if any valid move order lines exist for the item load entry
782 SELECT COUNT(*)
783 INTO l_mo_line_count
784 FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
785 WHERE mtrl.organization_id = p_organization_id
786 AND mtrl.lpn_id = p_lpn_id
787 AND mtrl.inventory_item_id = p_inventory_item_id
788 AND NVL(mtrl.revision, '###') = NVL(p_revision, '###')
789 AND NVL(mtrl.lot_number, '###') = NVL(p_lot_number, '###')
790 AND mtrl.quantity <> NVL(mtrl.quantity_delivered, 0)
791 AND mtrl.line_status <> inv_globals.g_to_status_closed
792 AND mtrl.header_id = mtrh.header_id
793 AND mtrh.move_order_type = inv_globals.g_move_order_put_away;
794 IF (l_debug = 1) THEN
795 print_debug('The number of MOLs found is: ' || l_mo_line_count);
796 END IF;
797 l_progress := '90';
798
799 IF (l_mo_line_count = 0) THEN
800 -- No move order lines were found so check the LPN context
801 IF (l_lpn_context <> 1) THEN
802 -- No valid move order lines for a non-Inventory case
803 IF (l_debug = 1) THEN
804 print_debug('No valid move order lines found for non-INV LPN');
805 END IF;
806 l_progress := '100';
807 -- RCV and WIP LPN's should have valid move order lines
808 FND_MESSAGE.SET_NAME('WMS', 'WMS_MO_NOT_FOUND');
809 FND_MSG_PUB.ADD;
810 RAISE FND_API.G_EXC_ERROR;
811 ELSE
812 -- LPN is an Inventory LPN with no move order lines.
813 -- This should be the non-ATF flow since if an ATF plan was in
814 -- effect, there would have been move order lines for the
815 -- material within the INV LPN.
816 IF (l_debug = 1) THEN
817 print_debug('INV LPN with no valid move order lines');
818 END IF;
819
820 -- Get the project and task for the source LPN
821 SELECT NVL(project_id, -999), NVL(task_id, -999)
822 INTO l_project_id, l_task_id
823 FROM mtl_item_locations
824 WHERE inventory_location_id = l_locator_id
825 AND organization_id = p_organization_id
826 AND subinventory_code = l_subinv_code;
827 l_progress := '110';
828
829 -- Get the cost group ID for the item load material.
830 -- Bug# 3368741
831 -- Added the ROWNUM = 1 line and a DISTINCT keyword in case there are
832 -- multiple WLC records for the same LPN/item/rev/lot which is possible
833 -- if from different sources.
834 SELECT DISTINCT NVL(cost_group_id, -999)
835 INTO l_cost_group_id
836 FROM wms_lpn_contents
837 WHERE parent_lpn_id = p_lpn_id
838 AND inventory_item_id = p_inventory_item_id
839 AND NVL(revision, '###') = NVL(p_revision, '###')
840 AND NVL(lot_number, '###') = NVL(p_lot_number, '###')
841 AND ROWNUM = 1;
842 l_progress := '120';
843
844 -- Reset the values to NULL if necessary
845 IF (l_project_id = -999) THEN
846 l_project_id := NULL;
847 END IF;
848 IF (l_task_id = -999) THEN
849 l_task_id := NULL;
850 END IF;
851 IF (l_cost_group_id = -999) THEN
852 l_cost_group_id := NULL;
853 END IF;
854 IF (l_debug = 1) THEN
855 print_debug('Required variable for calling create_mo');
856 print_debug('Project ID: ====> ' || l_project_id);
857 print_debug('Task ID: =======> ' || l_task_id);
858 print_debug('Cost Group ID: => ' || l_cost_group_id);
859 END IF;
860 l_progress := '130';
861
862 -- Call create_mo
863 IF (l_debug = 1) THEN
864 print_debug('Call the create_mo API for the given item load entry');
865 END IF;
866 wms_task_dispatch_put_away.create_mo
867 (p_org_id => p_organization_id,
868 p_inventory_item_id => p_inventory_item_id,
869 p_qty => p_quantity,
870 p_uom => p_uom_code,
871 -- laks
872 p_sec_qty => p_sec_quantity,
873 p_sec_uom => p_sec_uom_code,
874 p_lpn => p_lpn_id,
875 p_project_id => l_project_id,
876 p_task_id => l_task_id,
877 p_reference => NULL,
878 p_reference_type_code => NULL,
879 p_reference_id => NULL,
880 p_lot_number => p_lot_number,
881 p_revision => p_revision,
882 p_header_id => l_mo_header_id,
883 p_sub => l_subinv_code,
884 p_loc => l_locator_id,
885 x_line_id => l_mo_line_id,
886 p_inspection_status => NULL,
887 p_transaction_type_id => 64,
888 p_transaction_source_type_id => 4,
889 p_wms_process_flag => NULL,
890 x_return_status => x_return_status,
891 x_msg_count => x_msg_count,
892 x_msg_data => x_msg_data,
893 p_from_cost_group_id => l_cost_group_id,
894 p_transfer_org_id => p_organization_id);
895
896 IF (l_debug = 1) THEN
897 print_debug('Finished calling the create_mo API');
898 END IF;
899 l_progress := '140';
900
901 -- Check to see if the create_mo API returned successfully
902 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
903 IF (l_debug = 1) THEN
904 print_debug('Success returned from create_mo API');
905 END IF;
906 ELSE
907 IF (l_debug = 1) THEN
908 print_debug('Failure returned from create_mo API');
909 END IF;
910 FND_MESSAGE.SET_NAME('WMS', 'WMS_TD_MO_ERROR');
911 FND_MSG_PUB.ADD;
912 RAISE FND_API.G_EXC_ERROR;
913 END IF;
914 l_progress := '150';
915
916 -- This newly created move order line is the only one
917 -- needed to match the item load entry
918 IF (l_debug = 1) THEN
919 print_debug('Move order line ID: ===> ' || l_mo_line_id);
920 print_debug('Move order header ID: => ' || l_mo_header_id);
921 print_debug('Primary Load Qty: =====> ' || l_primary_load_qty);
922 print_debug('Secondary Load Qty: =====> ' || l_sec_load_qty);
923 END IF;
924 l_progress := '160';
925 END IF;
926 END IF;
927 -- End of no move order lines found logic
928
929 -- If item is not serialized, then use mo_lines_cursor,
930 -- otherwise use mo_lines_serial_cursor for matching MOLs.
931 -- A value should be passed for the serial txn temp ID if the item
932 -- is serial controlled. This validation is checked on the java page
933 -- prior to calling this procedure.
934 IF (p_serial_txn_temp_id IS NULL) THEN
935 -- Non-serial controlled item
936 IF (l_debug = 1 ) THEN
937 print_debug('Non-serial controlled item');
938 END IF;
939 -- Open the mo_lines cursor
940 l_primary_qty_left := l_primary_load_qty;
941 l_sec_qty_left := l_sec_load_qty;
942 l_index := 1;
943 IF (l_debug = 1) THEN
944 print_debug('Open the move order lines cursor');
945 END IF;
946 OPEN mo_lines_cursor;
947 LOOP
948 FETCH mo_lines_cursor INTO l_mo_line_id, l_mo_qty_avail, l_mo_uom_code, l_mo_sec_qty_avail, l_sec_uom_code; --laks
949 EXIT WHEN mo_lines_cursor%NOTFOUND;
950 IF (l_debug = 1) THEN
951 print_debug('Found a matching move order line');
952 print_debug('Move order line ID: =========> ' || l_mo_line_id);
953 print_debug('Move order qty available: ===> ' || l_mo_qty_avail);
954 print_debug('Move order UOM code: ========> ' || l_mo_uom_code);
955 --laks
956 print_debug('Move order sec qty available: ===> ' || l_mo_sec_qty_avail);
957 print_debug('Move order Sec UOM code: ========> ' || l_sec_uom_code);
958 END IF;
959 l_progress := '170';
960
961 -- Convert the MO qty available to the primary UOM if necessary
962 IF (l_mo_uom_code <> l_primary_uom_code) THEN
963 l_primary_qty_avail :=
964 inv_convert.inv_um_convert (p_inventory_item_id,
965 5,
966 l_mo_qty_avail,
967 l_mo_uom_code,
968 l_primary_uom_code,
969 NULL,
970 NULL);
971 ELSE
972 l_primary_qty_avail := l_mo_qty_avail;
973 END IF;
974 --laks
975 l_sec_qty_avail := l_mo_sec_qty_avail;
976 IF (l_debug = 1) THEN
977 print_debug('Primary quantity available: => ' || l_primary_qty_avail);
978 --laks
979 print_debug('Secondary quantity available: => ' || l_sec_qty_avail);
980 END IF;
981 l_progress := '180';
982
983 -- Call the ATF validate_operation API to see if this move order
984 -- line can be loaded and if so, how much quantity is available
985 -- to be loaded
986 IF (l_debug = 1) THEN
987 print_debug('Call validate_operation API for the given MOL');
988 END IF;
989 wms_atf_runtime_pub_apis.validate_operation
990 (x_return_status => x_return_status,
991 x_msg_data => x_msg_data,
992 x_msg_count => x_msg_count,
993 x_error_code => l_error_code,
994 x_inspection_flag => l_inspection_flag,
995 x_load_flag => l_load_flag,
996 x_drop_flag => l_drop_flag,
997 x_load_prim_quantity => l_load_prim_quantity,
998 x_drop_prim_quantity => l_drop_prim_quantity,
999 x_inspect_prim_quantity => l_inspect_prim_quantity,
1000 p_source_task_id => NULL,
1001 p_move_order_line_id => l_mo_line_id,
1002 p_inventory_item_id => p_inventory_item_id,
1003 p_lpn_id => p_lpn_id,
1004 p_activity_type_id => WMS_GLOBALS.G_OP_ACTIVITY_INBOUND,
1005 p_organization_id => p_organization_id);
1006
1007 IF (l_debug = 1) THEN
1008 print_debug('Finished calling the validate_operation API');
1009 END IF;
1010 l_progress := '190';
1011
1012 -- Check to see if the validate_operation API returned successfully
1013 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
1014 IF (l_debug = 1) THEN
1015 print_debug('Success returned from validate_operation API');
1016 END IF;
1017 ELSE
1018 IF (l_debug = 1) THEN
1019 print_debug('Failure returned from validate_operation API');
1020 print_debug('Error code: ' || l_error_code);
1021 END IF;
1022 RAISE FND_API.G_EXC_ERROR;
1023 END IF;
1024
1025 -- Now check to see if this move order line is allowed to be
1026 -- loaded and if so, how much quantity from the line is available
1027 IF (l_debug = 1) THEN
1028 print_debug('Values returned from call to validate_operation');
1029 print_debug('x_inspection_flag: =======> ' || l_inspection_flag);
1030 print_debug('x_load_flag: =============> ' || l_load_flag);
1031 print_debug('x_drop_flag: =============> ' || l_drop_flag);
1032 print_debug('x_load_prim_quantity: ====> ' || l_load_prim_quantity);
1033 print_debug('x_drop_prim_quantity: ====> ' || l_drop_prim_quantity);
1034 print_debug('x_inspect_prim_quantity: => ' || l_inspect_prim_quantity);
1035 END IF;
1036 l_primary_qty_avail := l_load_prim_quantity;
1037
1038 --laks
1039 IF(p_sec_uom_code IS NOT NULL) THEN
1040 l_sec_qty_avail := inv_convert.inv_um_convert (
1041 p_inventory_item_id,
1042 p_lot_number,
1043 p_organization_id,
1044 5,
1045 l_load_prim_quantity,
1046 l_primary_uom_code,
1047 p_sec_uom_code,
1048 NULL,
1049 NULL);
1050 END IF;
1051
1052
1053 -- Consume loaded quantity from this MOL only if the qty available
1054 -- is greater than 0
1055 IF (l_primary_qty_avail > 0) THEN
1056 -- Compare the primary quantity available from this
1057 -- move order line with the primary quantity left to match
1058 IF (l_primary_qty_avail > l_primary_qty_left) THEN
1059 -- We do not need to consume all of the quantity
1060 -- on this current move order line
1061 l_primary_qty_used := l_primary_qty_left;
1062 --laks
1063 l_sec_qty_used := l_sec_qty_left;
1064 ELSE
1065 -- Consume the entire quantity for this move order line
1066 l_primary_qty_used := l_primary_qty_avail;
1067 --laks
1068 l_sec_qty_used := l_sec_qty_avail;
1069 END IF;
1070 l_primary_qty_left := l_primary_qty_left - l_primary_qty_used;
1071 --laks
1072 l_sec_qty_left := l_sec_qty_left - l_sec_qty_used;
1073 IF (l_debug = 1) THEN
1074 print_debug('Primary quantity used: ======> ' || l_primary_qty_used);
1075 print_debug('Primary quantity left: ======> ' || l_primary_qty_left);
1076 --laks
1077 print_debug('Sec quantity used: ======> ' || l_sec_qty_used);
1078 print_debug('Sec quantity left: ======> ' || l_sec_qty_left);
1079 END IF;
1080 l_progress := '200';
1081
1082 -- Store this move order line info in the mo line table
1083 l_mo_lines_tb(l_index).prim_qty := l_primary_qty_used;
1084 l_mo_lines_tb(l_index).sec_qty := l_sec_qty_used;
1085 l_mo_lines_tb(l_index).line_id := l_mo_line_id;
1086 IF (l_debug = 1) THEN
1087 print_debug('Stored the move order line entry in the table: ' || l_index);
1088 END IF;
1089 l_progress := '210';
1090 END IF;
1091
1092 -- Check if we have finished matching the full load quantity
1093 IF (l_primary_qty_left = 0) THEN
1094 EXIT;
1095 END IF;
1096 -- Increment the table index value
1097 l_index := l_index + 1;
1098 END LOOP;
1099 CLOSE mo_lines_cursor;
1100 IF (l_debug = 1) THEN
1101 print_debug('Closed the move order lines cursor');
1102 END IF;
1103 l_progress := '220';
1104
1105 -- Check that we were able to fully match the load quantity
1106 IF (l_primary_qty_left <> 0) THEN
1107 IF (l_debug = 1) THEN
1108 print_debug('Unable to fully match the load quantity');
1109 END IF;
1110 l_progress := '230';
1111 -- This case should technically not occur if the move order
1112 -- lines are maintained properly.
1113 FND_MESSAGE.SET_NAME('WMS', 'WMS_MO_NOT_FOUND');
1114 FND_MSG_PUB.ADD;
1115 RAISE FND_API.G_EXC_ERROR;
1116 ELSE
1117 -- Full quantity has been matched
1118 IF (l_debug = 1) THEN
1119 print_debug('Able to fully match the load quantity');
1120 END IF;
1121 END IF;
1122 END IF;
1123 -- End of non-serial controlled item part for matching MOL
1124
1125 -- Beginning of serial controlled item part
1126 IF (p_serial_txn_temp_id IS NOT NULL) THEN
1127
1128 IF (l_debug = 1 ) THEN
1129 print_debug('Serial controlled item');
1130 END IF;
1131
1132 IF (l_lpn_context = 2) THEN
1133
1134 IF (l_debug = 1 ) THEN
1135 print_debug('WIP Serial');
1136 END IF;
1137
1138 -- Serial controlled item
1139 l_serial_quantity := 0;
1140 l_index := 1;
1141 OPEN marked_serials_cursor;
1142 LOOP
1143 FETCH marked_serials_cursor INTO l_current_serial,l_dummy;
1144 EXIT WHEN marked_serials_cursor%NOTFOUND;
1145 IF (l_debug = 1) THEN
1146 print_debug('Current serial: ' || l_current_serial);
1147 END IF;
1148 l_progress := '240';
1149
1150 -- For the current serial, match it against the appropriate move order line.
1151 -- There should only be one move order line that matches to a
1152 -- specific serial.
1153 OPEN mol_ser_csr_for_wip;
1154 FETCH mol_ser_csr_for_wip INTO l_mo_line_id;
1155 IF (mol_ser_csr_for_wip%FOUND) THEN
1156 IF (l_debug = 1) THEN
1157 print_debug('Found a matching move order line');
1158 print_debug('Move order line ID: => ' || l_mo_line_id);
1159 END IF;
1160 l_progress := '250';
1161
1162 -- Increment the matched serial quantity variable
1163 l_serial_quantity := l_serial_quantity + 1;
1164
1165 l_is_new_entry := TRUE;
1166 -- Check if this move order line has already been stored in
1167 -- the mo line table
1168 IF (l_mo_lines_tb.COUNT <> 0) THEN
1169 -- Entries have already been stored in the MOL table
1170 l_table_index := l_mo_lines_tb.FIRST;
1171 LOOP
1172 IF (l_mo_lines_tb(l_table_index).line_id = l_mo_line_id) THEN
1173 -- Increment the quantity for the same MOL entry
1174 l_mo_lines_tb(l_table_index).prim_qty :=
1175 l_mo_lines_tb(l_table_index).prim_qty + 1;
1176 l_mo_lines_tb(l_table_index).line_id := l_mo_line_id;
1177 l_is_new_entry := FALSE;
1178 IF (l_debug = 1) THEN
1179 print_debug('Updated the move order line entry in the table: ' ||
1180 l_table_index);
1181 END IF;
1182 l_progress := '260';
1183 EXIT;
1184 END IF;
1185 EXIT WHEN l_table_index = l_mo_lines_tb.LAST;
1186 l_table_index := l_mo_lines_tb.NEXT(l_table_index);
1187 END LOOP;
1188
1189 -- Check if this MOL is a new entry in the table
1190 IF (l_is_new_entry) THEN
1191 l_mo_lines_tb(l_index).prim_qty := 1;
1192 l_mo_lines_tb(l_index).line_id := l_mo_line_id;
1193 IF (l_debug = 1) THEN
1194 print_debug('Stored the move order line entry in the table: ' || l_index);
1195 END IF;
1196 l_progress := '270';
1197 -- Update the index whenever we insert a new entry
1198 -- into the MOL table
1199 l_index := l_index + 1;
1200 END IF;
1201 ELSE
1202 -- No entries entered in MOL table yet
1203 -- Store this move order line info in the mo line table
1204 l_mo_lines_tb(l_index).prim_qty := 1;
1205 l_mo_lines_tb(l_index).line_id := l_mo_line_id;
1206 IF (l_debug = 1) THEN
1207 print_debug('Stored the move order line entry in the table: ' || l_index);
1208 END IF;
1209 l_progress := '280';
1210 -- Update the index whenever we insert a new entry
1211 -- into the MOL table
1212 l_index := l_index + 1;
1213 END IF;
1214 -- End of check if entries exist in l_mo_lines_tb
1215 ELSE
1216 -- No move order line found with the matching serial
1217 IF (l_debug = 1) THEN
1218 print_debug('Could not find a matching move order line: ' ||
1219 l_current_serial);
1220 END IF;
1221 FND_MESSAGE.SET_NAME('WMS', 'WMS_MO_NOT_FOUND');
1222 FND_MSG_PUB.ADD;
1223 RAISE FND_API.G_EXC_ERROR;
1224 END IF;
1225
1226 -- Close the MOL serial cursor
1227 CLOSE mol_ser_csr_for_wip;
1228 l_progress := '290';
1229
1230 END LOOP;
1231 CLOSE marked_serials_cursor;
1232 IF (l_debug = 1) THEN
1233 print_debug('Closed the marked_serials_cursor');
1234 print_debug('Serial quantity matched: => ' || l_serial_quantity);
1235 print_debug('Serial quantity loaded: ==> ' || l_primary_load_qty);
1236 END IF;
1237 l_progress := '300';
1238
1239 -- Check that the inputted l_primary_load_qty equals the amount
1240 -- of serials that were marked for the given item/rev/lot combination
1241 IF (l_primary_load_qty <> l_serial_quantity) THEN
1242 IF (l_debug = 1) THEN
1243 print_debug('Unable to fully match the load quantity');
1244 END IF;
1245 FND_MESSAGE.SET_NAME ('WMS', 'WMS_CONT_INVALID_X_QTY');
1246 FND_MSG_PUB.ADD;
1247 RAISE FND_API.G_EXC_ERROR;
1248 ELSE
1249 -- Full quantity has been matched
1250 IF (l_debug = 1) THEN
1251 print_debug('Able to fully match the load quantity');
1252 END IF;
1253 END IF;
1254 l_progress := '310';
1255 ELSE -- IF l_lpn_context in (1,3) THEN
1256 IF (l_debug = 1) THEN
1257 print_debug('INV/RCV Serial');
1258 END IF;
1259
1260 l_index := 1;
1261
1262 OPEN marked_serials_cursor;
1263 FETCH marked_serials_cursor bulk collect INTO l_marked_serials_tb;
1264 CLOSE marked_serials_cursor;
1265
1266 IF (l_debug = 1) THEN
1267 print_debug('# marked serials: '||l_marked_serials_tb.COUNT);
1268 END IF;
1269
1270 IF (l_primary_load_qty <> l_marked_serials_tb.COUNT) THEN
1271 IF (l_debug = 1) THEN
1272 print_debug('Unable to fully match the load quantity');
1273 END IF;
1274 FND_MESSAGE.SET_NAME ('WMS', 'WMS_CONT_INVALID_X_QTY');
1275 FND_MSG_PUB.ADD;
1276 RAISE FND_API.G_EXC_ERROR;
1277 ELSE
1278 -- Full quantity has been matched
1279 IF (l_debug = 1) THEN
1280 print_debug('Serials match the load quantity');
1281 END IF;
1282 END IF;
1283
1284 l_progress := '310.1';
1285
1286 OPEN mol_ser_csr_for_inv_rcv;
1287 FETCH mol_ser_csr_for_inv_rcv bulk collect INTO l_mol_ser_tb;
1288 CLOSE mol_ser_csr_for_inv_rcv;
1289
1290 IF (l_debug = 1) THEN
1291 print_debug('# of MOL matched: '||l_mol_ser_tb.COUNT);
1292 END IF;
1293
1294 l_progress := '310.2';
1295
1296 FOR i IN 1..l_marked_serials_tb.COUNT LOOP
1297 IF (l_debug = 1) THEN
1298 print_debug('l_marked_serials_tb('||i||').serial_number:'||
1299 l_marked_serials_tb(i).serial_number||' inspection_status:'
1300 ||l_marked_serials_tb(i).inspection_status);
1301 END IF;
1302
1303 l_progress := '310.3';
1304
1305 FOR j IN 1..l_mol_ser_tb.COUNT LOOP
1306 IF (l_debug = 1) THEN
1307 print_debug('l_mol_ser_tb('||j||').line_id:'||
1308 l_mol_ser_tb(j).line_id||' avail_qty:'
1309 ||l_mol_ser_tb(j).avail_qty||' inspection_status:'
1310 ||l_mol_ser_tb(j).inspection_status);
1311 END IF;
1312
1313 l_progress := '310.4';
1314
1315 IF (l_marked_serials_tb(i).inspection_status = l_mol_ser_tb(j).inspection_status) THEN
1316 l_is_new_entry := TRUE;
1317
1318 -- Check if this move order line has already been stored in
1319 -- the mo line table
1320 IF (l_mo_lines_tb.COUNT <> 0) THEN
1321 -- Entries have already been stored in the MOL table
1322 l_table_index := l_mo_lines_tb.FIRST;
1323 LOOP
1324 IF (l_mo_lines_tb(l_table_index).line_id = l_mol_ser_tb(j).line_id) THEN
1325 -- Increment the quantity for the same MOL entry
1326 l_mo_lines_tb(l_table_index).prim_qty := l_mo_lines_tb(l_table_index).prim_qty + 1;
1327 l_is_new_entry := FALSE;
1328 IF (l_debug = 1) THEN
1329 print_debug('Updated the move order line entry in the table: ' ||l_table_index);
1330 END IF;
1331 l_progress := '310.5';
1332 EXIT;
1333 END IF;
1334 EXIT WHEN l_table_index = l_mo_lines_tb.LAST;
1335 l_table_index := l_mo_lines_tb.NEXT(l_table_index);
1336 END LOOP;
1337
1338 -- Check if this MOL is a new entry in the table
1339 IF (l_is_new_entry) THEN
1340 l_mo_lines_tb(l_index).prim_qty := 1;
1341 l_mo_lines_tb(l_index).line_id := l_mol_ser_tb(j).line_id;
1342 IF (l_debug = 1) THEN
1343 print_debug('Stored the move order line entry in the table: ' || l_index);
1344 END IF;
1345 l_progress := '310.6';
1346 -- Update the index whenever we insert a new entry
1347 -- into the MOL table
1348 l_index := l_index + 1;
1349 END IF;
1350 ELSE
1351 -- No entries entered in MOL table yet
1352 -- Store this move order line info in the mo line table
1353 l_mo_lines_tb(l_index).prim_qty := 1;
1354 l_mo_lines_tb(l_index).line_id := l_mol_ser_tb(j).line_id;
1355 IF (l_debug = 1) THEN
1356 print_debug('Stored the move order line entry in the table: ' || l_index);
1357 END IF;
1358 l_progress := '310.7';
1359 -- Update the index whenever we insert a new entry
1360 -- into the MOL table
1361 l_index := l_index + 1;
1362 END IF;--END IF (l_mo_lines_tb.COUNT <> 0) THEN
1363
1364 IF l_mol_serial_tb_b4_splt.exists(l_mol_ser_tb(j).line_id) THEN
1365 l_tmp := l_mol_serial_tb_b4_splt(l_mol_ser_tb(j).line_id).COUNT + 1;
1366 ELSE
1367 l_tmp := 1;
1368 END IF;
1369
1370 l_mol_serial_tb_b4_splt(l_mol_ser_tb(j).line_id)(l_tmp) := l_marked_serials_tb(i).serial_number;
1371
1372 l_mol_ser_tb(j).avail_qty := l_mol_ser_tb(j).avail_qty - 1;
1373
1374 IF l_mol_ser_tb(j).avail_qty <= 0 THEN
1375 l_mol_ser_tb.DELETE(j);
1376 END IF;
1377
1378 l_serial_matched := 1;
1379
1380 EXIT;
1381
1382 END IF;--END IF (l_marked_serials_tb(i).inspection_status = l_mol_ser_tb(j).inspection_status) THEN
1383 END LOOP;--END FOR j IN 1..l_mol_ser_tb.COUNT LOOP
1384
1385 IF (l_serial_matched <> 1) THEN
1386 -- No move order line found with the matching serial
1387 IF (l_debug = 1) THEN
1388 print_debug('Could not find a matching move order line: ' ||
1389 l_marked_serials_tb(i).serial_number);
1390 END IF;
1391 FND_MESSAGE.SET_NAME('WMS', 'WMS_MO_NOT_FOUND');
1392 FND_MSG_PUB.ADD;
1393 RAISE FND_API.G_EXC_ERROR;
1394 END IF;
1395 END LOOP;--FOR i IN 1..l_marked_serials_tb.COUNT LOOP
1396 END IF;--END IF (l_lpn_context = 2) THEN
1397
1398 -- Call the ATF validate_operation API to see if the move order
1399 -- lines matched for all serials can be loaded and if so, how much
1400 -- quantity is available to be loaded
1401 IF (l_debug = 1) THEN
1402 print_debug('Call validate_operation API for the matched MOLs');
1403 END IF;
1404 l_table_index := l_mo_lines_tb.FIRST;
1405 LOOP
1406 IF (l_mo_lines_tb(l_table_index).line_id = l_mo_line_id) THEN
1407 print_debug('Current MOL: ' || l_mo_lines_tb(l_table_index).line_id);
1408 print_debug('MOL quantity: ' || l_mo_lines_tb(l_table_index).prim_qty);
1409 END IF;
1410 l_progress := '320';
1411
1412 wms_atf_runtime_pub_apis.validate_operation
1413 (x_return_status => x_return_status,
1414 x_msg_data => x_msg_data,
1415 x_msg_count => x_msg_count,
1416 x_error_code => l_error_code,
1417 x_inspection_flag => l_inspection_flag,
1418 x_load_flag => l_load_flag,
1419 x_drop_flag => l_drop_flag,
1420 x_load_prim_quantity => l_load_prim_quantity,
1421 x_drop_prim_quantity => l_drop_prim_quantity,
1422 x_inspect_prim_quantity => l_inspect_prim_quantity,
1423 p_source_task_id => NULL,
1424 p_move_order_line_id => l_mo_lines_tb(l_table_index).line_id,
1425 p_inventory_item_id => p_inventory_item_id,
1426 p_lpn_id => p_lpn_id,
1427 p_activity_type_id => WMS_GLOBALS.G_OP_ACTIVITY_INBOUND,
1428 p_organization_id => p_organization_id);
1429
1430 IF (l_debug = 1) THEN
1431 print_debug('Finished calling the validate_operation API');
1432 END IF;
1433 l_progress := '330';
1434
1435 -- Check to see if the validate_operation API returned successfully
1436 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
1437 IF (l_debug = 1) THEN
1438 print_debug('Success returned from validate_operation API');
1439 END IF;
1440 ELSE
1441 IF (l_debug = 1) THEN
1442 print_debug('Failure returned from validate_operation API');
1443 print_debug('Error code: ' || l_error_code);
1444 END IF;
1445 RAISE FND_API.G_EXC_ERROR;
1446 END IF;
1447 l_progress := '340';
1448
1449 -- Now check to see if this move order line is allowed to be
1450 -- loaded and if so, how much quantity from the line is available
1451 IF (l_debug = 1) THEN
1452 print_debug('Values returned from call to validate_operation');
1453 print_debug('x_inspection_flag: =======> ' || l_inspection_flag);
1454 print_debug('x_load_flag: =============> ' || l_load_flag);
1455 print_debug('x_drop_flag: =============> ' || l_drop_flag);
1456 print_debug('x_load_prim_quantity: ====> ' || l_load_prim_quantity);
1457 print_debug('x_drop_prim_quantity: ====> ' || l_drop_prim_quantity);
1458 print_debug('x_inspect_prim_quantity: => ' || l_inspect_prim_quantity);
1459 END IF;
1460
1461 -- Check that the full serial quantity matched to
1462 -- that move order line can be loaded
1463 IF (l_load_prim_quantity < l_mo_lines_tb(l_table_index).prim_qty) THEN
1464 -- The MOL matched is not available for loading all the serials
1465 IF (l_debug = 1) THEN
1466 print_debug('Matching MOL does not have enough valid qty for load');
1467 END IF;
1468 FND_MESSAGE.SET_NAME('WMS', 'WMS_MO_NOT_FOUND');
1469 FND_MSG_PUB.ADD;
1470 RAISE FND_API.G_EXC_ERROR;
1471 ELSE
1472 -- The matched MOL has enough valid quantity to load all the serials
1473 IF (l_debug = 1) THEN
1474 print_debug('Able to fully load the serials for the matched MOL');
1475 END IF;
1476 END IF;
1477 l_progress := '350';
1478
1479 EXIT WHEN l_table_index = l_mo_lines_tb.LAST;
1480 l_table_index := l_mo_lines_tb.NEXT(l_table_index);
1481 END LOOP;
1482
1483 END IF;
1484 -- End of serial controlled item part for matching MOL
1485
1486 -- Now that we have matched the item load entry with the
1487 -- appropriate move order lines, call split_mo to split the
1488 -- move order lines and create new ones for the item load entry
1489 IF (l_debug = 1) THEN
1490 print_debug('For each move order line, call the split_mo API');
1491 END IF;
1492 -- Initialize loop variables
1493 l_mo_split_tb.DELETE;
1494 l_index := l_mo_lines_tb.FIRST;
1495 l_progress := '360';
1496 LOOP
1497 IF (l_debug = 1) THEN
1498 print_debug('Current MO line: => ' || l_mo_lines_tb(l_index).line_id);
1499 print_debug('Current MO qty: ==> ' || l_mo_lines_tb(l_index).prim_qty);
1500 END IF;
1501 -- Set up the qty value to split in the input MO split table
1502 l_mo_split_tb(1).prim_qty := l_mo_lines_tb(l_index).prim_qty;
1503 --laks
1504 l_mo_split_tb(1).sec_qty := l_mo_lines_tb(l_index).sec_qty;
1505 l_mo_split_tb(1).line_id := l_mo_lines_tb(l_index).line_id;
1506
1507 IF (l_debug = 1) THEN
1508 print_debug('Calling split_mo API');
1509 END IF;
1510 inv_rcv_integration_apis.split_mo
1511 (p_orig_mol_id => l_mo_lines_tb(l_index).line_id,
1512 p_mo_splt_tb => l_mo_split_tb,
1513 p_operation_type => 'LOAD',
1514 x_return_status => x_return_status,
1515 x_msg_count => x_msg_count,
1516 x_msg_data => x_msg_data
1517 );
1518 IF (l_debug = 1) THEN
1519 print_debug('Finished calling split_mo API');
1520 END IF;
1521 l_progress := '370';
1522
1523 -- Check that the call to split_mo returned successfully
1524 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
1525 IF (l_debug = 1) THEN
1526 print_debug('Success returned from split_mo API');
1527 END IF;
1528 ELSE
1529 IF (l_debug = 1) THEN
1530 print_debug('Failure returned from split_mo API');
1531 END IF;
1532 RAISE FND_API.G_EXC_ERROR;
1533 END IF;
1534
1535 -- Update the MO lines table entry with the newly created split MO line
1536 IF (l_debug = 1) THEN
1537 print_debug('New Split MO line: => ' || l_mo_split_tb(1).line_id);
1538 END IF;
1539
1540 l_progress := '380';
1541
1542 IF (p_serial_txn_temp_id IS NOT NULL AND l_lpn_context IN (1,3)) THEN
1543 l_mol_serial_tb_af_splt(l_mo_split_tb(1).line_id) := l_mol_serial_tb_b4_splt(l_mo_lines_tb(l_index).line_id);
1544 END IF;
1545
1546 l_mo_lines_tb(l_index).line_id := l_mo_split_tb(1).line_id;
1547
1548 -- Exit if all move order lines have been split
1549 EXIT WHEN l_index = l_mo_lines_tb.LAST;
1550 l_index := l_mo_lines_tb.NEXT(l_index);
1551 END LOOP;
1552 IF (l_debug = 1) THEN
1553 print_debug('Finished calling split_mo for all MO lines');
1554 END IF;
1555 l_progress := '390';
1556
1557
1558 --BUG 5194761: Call crossdock API here to make sure that all the MO
1559 --splitting is done before inserting into dummy MMTT
1560 BEGIN
1561 SELECT NVL(crossdock_flag, 2) cdock
1562 INTO l_cdock_flag
1563 FROM mtl_parameters
1564 WHERE organization_id = p_organization_id;
1565 EXCEPTION
1566 WHEN OTHERS THEN
1567 IF (l_debug = 1) THEN
1568 print_debug('Error getting org crossdock flag. SQLERRM:'||SQLERRM);
1569 END IF;
1570 RAISE fnd_api.g_exc_error;
1571 END;
1572
1573 IF (l_debug = 1) THEN
1574 print_debug('l_cdock_flag:'||l_cdock_flag);
1575 END IF;
1576
1577 --Make a copy of l_mo_lines_tb
1578 l_tmp_mo_lines_tb := l_mo_lines_tb;
1579
1580 l_index := l_tmp_mo_lines_tb.FIRST;
1581 LOOP
1582
1583 BEGIN
1584 SELECT backorder_delivery_detail_id
1585 , to_subinventory_code
1586 , to_locator_id
1587 INTO l_backorder_delivery_detail_id
1588 , l_to_sub_code
1589 , l_to_loc_id
1590 FROM mtl_txn_request_lines
1591 WHERE line_id = l_tmp_mo_lines_tb(l_index).line_id;
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 IF (l_debug = 1) THEN
1595 print_debug('Error querying MTRL. SQLERRM:'||SQLERRM);
1596 END IF;
1597 RAISE fnd_api.g_exc_error;
1598 END;
1599
1600 IF (l_debug = 1) THEN
1601 print_debug('l_backorder_delivery_detail_id:'||l_backorder_delivery_detail_id);
1602 print_debug('l_to_sub_code:'||l_to_sub_code);
1603 print_debug('l_to_loc_id:'||l_to_loc_id );
1604 END IF;
1605
1606 IF ((l_cdock_flag = 1 -- WIP, op-xdock enabled, and x-dock not happened
1607 AND l_lpn_context = 2
1608 AND l_backorder_delivery_detail_id IS NULL)
1609 OR
1610 (l_lpn_context = 3 -- RCV, xdock happened, but staging lane suggestion not successful
1611 AND l_backorder_delivery_detail_id IS NOT NULL
1612 AND (l_to_sub_code IS NULL OR l_to_loc_id IS NULL))
1613 ) THEN
1614
1615 IF (l_debug = 1) THEN
1616 print_debug('Calling crossdock API');
1617 END IF;
1618
1619 -- Call the cross dock API
1620 wms_cross_dock_pvt.crossdock(
1621 p_org_id => p_organization_id
1622 , p_lpn => p_lpn_id
1623 , x_ret => l_ret_crossdock
1624 , x_return_status => l_return_status
1625 , x_msg_count => l_msg_count
1626 , x_msg_data => l_msg_data
1627 , p_move_order_line_id => l_tmp_mo_lines_tb(l_index).line_id
1628 ); -- added for ATF_J
1629
1630 IF (l_debug = 1) THEN
1631 print_debug('Finisehd calling crossdock API');
1632 END IF;
1633
1634 FOR l_splitted_rec IN (SELECT line_id
1635 , primary_quantity
1636 FROM mtl_txn_request_lines
1637 WHERE reference_detail_id = l_tmp_mo_lines_tb(l_index).line_id) LOOP
1638
1639 IF (l_debug = 1) THEN
1640 print_debug('Splitted Line:'||l_splitted_rec.line_id);
1641 print_debug('Splitted Qty:'||l_splitted_rec.primary_quantity);
1642 END IF;
1643
1644 l_tmp_index := l_mo_lines_tb.LAST + 1;
1645 l_mo_lines_tb(l_tmp_index).line_id := l_splitted_rec.line_id;
1646 l_mo_lines_tb(l_tmp_index).prim_qty := l_splitted_rec.primary_quantity;
1647 l_mo_lines_tb(l_index).prim_qty := l_mo_lines_tb(l_index).prim_qty - l_splitted_rec.primary_quantity;
1648
1649 BEGIN
1650 UPDATE mtl_txn_request_lines
1651 SET reference_detail_id = NULL
1652 WHERE line_id = l_splitted_rec.line_id;
1653 EXCEPTION
1654 WHEN OTHERS THEN
1655 IF (l_debug = 1) THEN
1656 print_debug('Error nulling out mtrl.ref_detail_id. SQLERRM:'||SQLERRM);
1657 END IF;
1658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1659 END;
1660
1661 END LOOP;
1662 END IF;
1663
1664 EXIT WHEN l_index = l_tmp_mo_lines_tb.LAST;
1665 l_index := l_tmp_mo_lines_tb.NEXT(l_index);
1666 END LOOP;
1667 --END BUG 5194761
1668
1669 -- Now that we have split the MOLs and created new lines,
1670 -- we can insert dummy MMTT records tied to the newly split MOLs
1671 IF (l_debug = 1) THEN
1672 print_debug('l_tmp_mo_lines_tb.COUNT:'||l_tmp_mo_lines_tb.COUNT);
1673 print_debug('l_mo_lines_tb.COUNT:'||l_mo_lines_tb.COUNT);
1674 print_debug('For each move order line, insert a dummy MMTT record');
1675 print_debug('For each move order line, insert a dummy MMTT record');
1676 END IF;
1677 l_index := l_mo_lines_tb.FIRST;
1678 LOOP
1679 IF (l_debug = 1) THEN
1680 print_debug('Move order line ID: => ' || l_mo_lines_tb(l_index).line_id);
1681 print_debug('Primary qty used: ===> ' || l_mo_lines_tb(l_index).prim_qty);
1682 END IF;
1683 l_progress := '400';
1684
1685 -- Update the wms_process_flag for each MOL used so other processes
1686 -- will not pick up this MOL to use. This should only be done for
1687 -- receiving LPNs
1688 IF (l_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_RCV) THEN
1689 IF (l_debug = 1) THEN
1690 print_debug('Receiving LPN so update wms_process_flag on the MOL');
1691 END IF;
1692 BEGIN
1693 UPDATE mtl_txn_request_lines
1694 SET wms_process_flag = 2
1695 WHERE line_id = l_mo_lines_tb(l_index).line_id
1696 AND organization_id = p_organization_id;
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 IF (l_debug = 1) THEN
1700 print_debug('Exception while setting wms_process_flag for MOLs');
1701 END IF;
1702 END;
1703 IF (l_debug = 1) THEN
1704 print_debug('Successfully updated wms_process_flag for MOL');
1705 END IF;
1706 l_progress := '405';
1707 END IF;
1708
1709 -- If a 'From' value is null, set it to the 'To' value and vice versa
1710 IF ((l_subinv_code IS NULL) AND (l_tosubinv_code IS NOT NULL)) THEN
1711 l_subinv_code := l_tosubinv_code;
1712 END IF;
1713 IF ((l_locator_id IS NULL) AND (l_tolocator_id IS NOT NULL)) THEN
1714 l_locator_id := l_tolocator_id;
1715 END IF;
1716 IF ((l_tosubinv_code IS NULL) AND (l_subinv_code IS NOT NULL)) THEN
1717 l_tosubinv_code := l_subinv_code;
1718 END IF;
1719 IF ((l_tolocator_id IS NULL) AND (l_locator_id IS NOT NULL)) THEN
1720 l_tolocator_id := l_locator_id;
1721 END IF;
1722 IF (l_debug = 1) THEN
1723 print_debug('From and To sub/loc info based on LPNs used for MMTT records:');
1724 print_debug('From Sub: => ' || l_subinv_code);
1725 print_debug('From Loc: => ' || l_locator_id);
1726 print_debug('To Sub: ===> ' || l_tosubinv_code);
1727 print_debug('To Loc: ===> ' || l_tolocator_id);
1728 END IF;
1729 l_progress := '410';
1730
1731 -- Set the transaction source type, type and action based on the from and to
1732 -- locations. If there is a change in sub/loc, then this should be a
1733 -- sub xfer transaction, otherwise it will be a container split.
1734 IF ((l_subinv_code <> l_tosubinv_code) OR (l_locator_id <> l_tolocator_id)) THEN
1735 -- Sub Xfer transaction
1736 IF (l_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_RCV) THEN
1737 -- For receiving, the dummy MMTT records should always
1738 -- have the container split txn action type. The call to
1739 -- pack_unpack_split and suggestions_pub expects dummy
1740 -- non suggestions MMTT records to be of this type for receiving.
1741 l_trx_src_type_id := 13;
1742 l_trx_type_id := 89;
1743 l_trx_action_id := 52;
1744 ELSE
1745 -- For non receiving scenarios, this should be an inventory
1746 -- sub transfer transaction. For the inventory case, we will
1747 -- use the dummy MMTTs and call the TM to transact them.
1748 -- The TM will error out if there is a change in sub/loc but
1749 -- the transaction type is not an inventory sub xfer.
1750 l_trx_src_type_id := 13;
1751 l_trx_type_id := 2;
1752 l_trx_action_id := 2;
1753 END IF;
1754 ELSE
1755 -- Container Split transaction
1756 l_trx_src_type_id := 13;
1757 l_trx_type_id := 89;
1758 l_trx_action_id := 52;
1759 -- Null out the xfr sub/loc/org variables since there is no change
1760 -- in location. Do this only for non receiving cases since for
1761 -- receiving, the pack_unpack_split API called in process_load
1762 -- expects these values to be populated in the dummy MMTT records.
1763 -- If these values are not nulled out in the inventory case, the
1764 -- TM will error out.
1765 IF (l_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_RCV) THEN
1766 IF (l_debug = 1) THEN
1767 print_debug('Receiving LPN so no need to null out xfer variables');
1768 END IF;
1769 ELSE
1770 IF (l_debug = 1) THEN
1771 print_debug('Non receiving LPN so null out the xfer variables');
1772 END IF;
1773 l_tosubinv_code := NULL;
1774 l_tolocator_id := NULL;
1775 l_xfr_org_id := NULL;
1776 END IF;
1777 END IF;
1778 IF (l_debug = 1) THEN
1779 print_debug('Transaction type info');
1780 print_debug('Txn Source Type: => ' || l_trx_src_type_id);
1781 print_debug('Txn Type: ========> ' || l_trx_type_id);
1782 print_debug('Txn Action: ======> ' || l_trx_action_id);
1783 print_debug('Transfer transaction info');
1784 print_debug('Xfr Sub: =========> ' || l_tosubinv_code);
1785 print_debug('Xfr Loc: =========> ' || l_tolocator_id);
1786 print_debug('Xfr Org: =========> ' || l_xfr_org_id);
1787 END IF;
1788 l_progress := '415';
1789
1790 -- Now get some additional info from the move order line
1791 SELECT NVL(txn_source_id, -999), NVL(project_id, -999), NVL(task_id, -999)
1792 INTO l_txn_src_id, l_project_id, l_task_id
1793 FROM mtl_txn_request_lines
1794 WHERE organization_id = p_organization_id
1795 AND line_id = l_mo_lines_tb(l_index).line_id;
1796 l_progress := '420';
1797
1798 -- Reset the values to null if necessary
1799 IF (l_txn_src_id = -999) THEN
1800 l_txn_src_id := NULL;
1801 END IF;
1802 IF (l_project_id = -999) THEN
1803 l_project_id := NULL;
1804 END IF;
1805 IF (l_task_id = -999) THEN
1806 l_task_id := NULL;
1807 END IF;
1808
1809 IF l_primary_uom_code <> p_uom_code THEN
1810 l_trx_qty := inv_rcv_cache.convert_qty(p_inventory_item_id => p_inventory_item_id
1811 ,p_from_qty => l_mo_lines_tb(l_index).prim_qty
1812 ,p_from_uom_code => l_primary_uom_code
1813 ,p_to_uom_code => p_uom_code);
1814 ELSE
1815 l_trx_qty := l_mo_lines_tb(l_index).prim_qty;
1816 END IF;
1817
1818 IF (l_debug = 1) THEN
1819 print_debug('Additional move order line info');
1820 print_debug('Txn Source ID: => ' || l_txn_src_id);
1821 print_debug('Project ID: ====> ' || l_project_id);
1822 print_debug('Task ID: =======> ' || l_task_id);
1823 END IF;
1824 l_progress := '430';
1825
1826 -- Insert a record into MMTT
1827 l_return := inv_trx_util_pub.insert_line_trx
1828 (p_trx_hdr_id => p_txn_header_id,
1829 p_item_id => p_inventory_item_id,
1830 p_revision => p_revision,
1831 p_org_id => p_organization_id,
1832 p_trx_action_id => l_trx_action_id,
1833 p_subinv_code => l_subinv_code,
1834 p_tosubinv_code => l_tosubinv_code,
1835 p_locator_id => l_locator_id,
1836 p_tolocator_id => l_tolocator_id,
1837 p_xfr_org_id => l_xfr_org_id,
1838 p_trx_type_id => l_trx_type_id,
1839 p_trx_src_type_id => l_trx_src_type_id,
1840 p_trx_qty => l_trx_qty,
1841 p_pri_qty => l_mo_lines_tb(l_index).prim_qty,
1842 p_uom => p_uom_code,
1843 --laks
1844 p_secondary_trx_qty => l_mo_lines_tb(l_index).sec_qty,
1845 p_secondary_uom => l_sec_uom_code,
1846 p_date => SYSDATE,
1847 p_user_id => p_user_id,
1848 p_cost_group => NULL,
1849 p_from_lpn_id => p_lpn_id,
1850 p_cnt_lpn_id => NULL,
1851 p_xfr_lpn_id => p_into_lpn_id,
1852 p_trx_src_id => l_txn_src_id,
1853 x_trx_tmp_id => l_txn_temp_id,
1854 x_proc_msg => x_msg_data,
1855 p_xfr_cost_group => NULL,
1856 p_project_id => l_project_id,
1857 p_task_id => l_task_id,
1858 p_move_order_line_id => l_mo_lines_tb(l_index).line_id,
1859 p_posting_flag => 'N');
1860
1861 IF (l_debug = 1) THEN
1862 print_debug('Successfully inserted MMTT record: ' || l_txn_temp_id);
1863 END IF;
1864 l_progress := '440';
1865
1866 -- Check if the API call was successful or not
1867 IF (l_return <> 0) THEN
1868 IF (l_debug = 1) THEN
1869 print_debug('Error occurred while calling inv_trx_util_pub.insert_line_trx');
1870 END IF;
1871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1872 END IF;
1873 l_progress := '450';
1874
1875 -- Bug# 3251237
1876 -- Dummy MMTT records should have the wms_task_type set to 2 for
1877 -- putaway transactions. This is needed by the inventory TM so
1878 -- it can bypass the call to finalize_pick_confirm. We should call
1879 -- the ATF apis instead to update the move order lines.
1880 UPDATE mtl_material_transactions_temp
1881 SET wms_task_type = 2
1882 WHERE transaction_temp_id = l_txn_temp_id
1883 AND organization_id = p_organization_id;
1884 IF (l_debug = 1) THEN
1885 print_debug('Successfully updated wms_task_type for dummy MMTT record');
1886 END IF;
1887 l_progress := '455';
1888
1889 -- If lot controlled, insert a record into MTLT
1890 IF (p_lot_number IS NOT NULL) THEN
1891 IF (l_debug = 1) THEN
1892 print_debug('Insert a record into MTLT for lot: ' || p_lot_number);
1893 END IF;
1894 l_return := inv_trx_util_pub.insert_lot_trx
1895 (p_trx_tmp_id => l_txn_temp_id,
1896 p_user_id => p_user_id,
1897 p_lot_number => p_lot_number,
1898 p_trx_qty => l_trx_qty,
1899 p_pri_qty => l_mo_lines_tb(l_index).prim_qty,
1900 --laks
1901 p_secondary_qty => l_mo_lines_tb(l_index).sec_qty,
1902 p_secondary_uom => l_sec_uom_code,
1903 x_ser_trx_id => l_ser_trx_id,
1904 x_proc_msg => x_msg_data);
1905 IF (l_debug = 1) THEN
1906 print_debug('Successfully inserted MTLT record');
1907 END IF;
1908 l_progress := '460';
1909
1910 IF (l_return <> 0) THEN
1911 IF (l_debug = 1) THEN
1912 print_debug('Error occurred while calling inv_trx_util_pub.insert_lot_trx');
1913 END IF;
1914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1915 END IF;
1916 l_progress := '470';
1917
1918 -- If not serial controlled, don't need the serial txn temp ID
1919 IF (p_serial_txn_temp_id IS NULL) THEN
1920 -- Update the MTLT record to clear out the serial_transaction_temp_id column
1921 -- since insert_lot_trx by default will insert a value for it.
1922 UPDATE mtl_transaction_lots_temp
1923 SET serial_transaction_temp_id = NULL
1924 WHERE transaction_temp_id = l_txn_temp_id;
1925 IF (l_debug = 1) THEN
1926 print_debug('Cleared out the serial txn temp ID column in MTLT record');
1927 END IF;
1928 l_progress := '480';
1929 END IF;
1930 END IF;
1931
1932 -- If serial controlled, insert record(s) into MSNT
1933 IF (p_serial_txn_temp_id IS NOT NULL) THEN
1934 -- If item is serial but not lot controlled, use the MMTT record's
1935 -- transaction temp ID, otherwise use the value returned for the
1936 -- serial transaction temp ID when inserting MTLT records
1937 IF (p_lot_number IS NULL) THEN
1938 l_ser_trx_id := l_txn_temp_id;
1939 END IF;
1940 IF (l_debug = 1) THEN
1941 print_debug('Serial transaction temp ID: ' || l_ser_trx_id);
1942 END IF;
1943 l_progress := '490';
1944
1945 l_mo_line_id := l_mo_lines_tb(l_index).line_id;
1946
1947 IF (l_lpn_context = 2) THEN
1948 IF (l_debug = 1) THEN
1949 print_debug('Insert MSNT records for WIP');
1950 END IF;
1951
1952
1953 OPEN matched_wip_serials_cursor;
1954 LOOP
1955 FETCH matched_wip_serials_cursor INTO l_current_serial;
1956 EXIT WHEN matched_wip_serials_cursor%NOTFOUND;
1957 IF (l_debug = 1) THEN
1958 print_debug('Insert a record into MSNT for serial: ' || l_current_serial);
1959 END IF;
1960 l_progress := '500';
1961
1962 l_return := inv_trx_util_pub.insert_ser_trx
1963 (p_trx_tmp_id => l_ser_trx_id,
1964 p_user_id => p_user_id,
1965 p_fm_ser_num => l_current_serial,
1966 p_to_ser_num => l_current_serial,
1967 p_quantity => 1,
1968 x_proc_msg => x_msg_data);
1969 IF (l_debug = 1) THEN
1970 print_debug('Successfully inserted MSNT record');
1971 END IF;
1972 l_progress := '510';
1973
1974 IF (l_return <> 0) THEN
1975 IF (l_debug = 1) THEN
1976 print_debug('Error occurred while calling inv_trx_util_pub.insert_ser_trx');
1977 END IF;
1978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1979 END IF;
1980 l_progress := '520';
1981
1982 END LOOP;
1983 CLOSE matched_wip_serials_cursor;
1984 IF (l_debug = 1) THEN
1985 print_debug('Closed the marked_serials_cursor');
1986 END IF;
1987 l_progress := '530';
1988 ELSE --IF l_lpn_context in (3,1)
1989 IF (l_debug = 1) THEN
1990 print_debug('Insert MSNT records for RCV/INV');
1991 END IF;
1992 -- Set the move order line variable to be used in the
1993 -- matched_mo_serials_cursor to get the marked serials tied to
1994 -- this specific MOL
1995 FOR j IN 1..l_mol_serial_tb_af_splt(l_mo_line_id).COUNT LOOP
1996
1997 l_current_serial := l_mol_serial_tb_af_splt(l_mo_line_id)(j);
1998
1999 IF (l_debug = 1) THEN
2000 print_debug('Insert a record into MSNT for serial: ' || l_current_serial);
2001 END IF;
2002 l_progress := '540';
2003
2004 l_return := inv_trx_util_pub.insert_ser_trx
2005 (p_trx_tmp_id => l_ser_trx_id,
2006 p_user_id => p_user_id,
2007 p_fm_ser_num => l_current_serial,
2008 p_to_ser_num => l_current_serial,
2009 p_quantity => 1,
2010 x_proc_msg => x_msg_data);
2011 IF (l_debug = 1) THEN
2012 print_debug('Successfully inserted MSNT record');
2013 END IF;
2014 l_progress := '550';
2015
2016 IF (l_return <> 0) THEN
2017 IF (l_debug = 1) THEN
2018 print_debug('Error occurred while calling inv_trx_util_pub.insert_ser_trx');
2019 END IF;
2020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2021 END IF;
2022 l_progress := '560';
2023
2024 END LOOP;
2025 END IF;--IF l_lpn_context = 2 THEN
2026 END IF;--IF (p_serial_txn_temp_id IS NOT NULL) THEN
2027
2028 -- Finished inserting all MMTT, MTLT, and MSNT records
2029 EXIT WHEN l_index = l_mo_lines_tb.LAST;
2030 l_index := l_mo_lines_tb.NEXT(l_index);
2031 l_progress := '580';
2032
2033 END LOOP;
2034 IF (l_debug = 1) THEN
2035 print_debug('Finished inserting dummy MMTT records for all MOLs');
2036 END IF;
2037 l_progress := '590';
2038
2039 IF (l_debug = 1) THEN
2040 print_debug('***End of pre_process_load***');
2041 END IF;
2042
2043 EXCEPTION
2044 WHEN FND_API.G_EXC_ERROR THEN
2045 ROLLBACK TO pre_process_load_sp;
2046 x_return_status := fnd_api.g_ret_sts_error;
2047 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2048 p_data => x_msg_data);
2049 IF (l_debug = 1) THEN
2050 print_debug('Exiting pre_process_load - Execution error: ' ||
2051 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
2052 END IF;
2053
2054 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2055 ROLLBACK TO pre_process_load_sp;
2056 x_return_status := fnd_api.g_ret_sts_unexp_error;
2057 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2058 p_data => x_msg_data);
2059 IF (l_debug = 1) THEN
2060 print_debug('Exiting pre_process_load - Unexpected error: ' ||
2061 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
2062 END IF;
2063
2064 WHEN OTHERS THEN
2065 ROLLBACK TO pre_process_load_sp;
2066 x_return_status := fnd_api.g_ret_sts_unexp_error;
2067 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2068 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2069 END IF;
2070 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2071 p_data => x_msg_data);
2072 IF (l_debug = 1) THEN
2073 print_debug('Exiting pre_process_load - Others exception: ' ||
2074 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
2075 END IF;
2076
2077 END pre_process_load;
2078
2079
2080
2081 PROCEDURE process_load
2082 (p_txn_header_id IN NUMBER ,
2083 p_serial_txn_temp_id IN NUMBER ,
2084 p_lpn_context IN NUMBER ,
2085 p_lpn_id IN NUMBER ,
2086 p_into_lpn_id IN NUMBER ,
2087 p_organization_id IN NUMBER ,
2088 p_user_id IN NUMBER ,
2089 p_eqp_ins IN VARCHAR2 ,
2090 x_return_status OUT NOCOPY VARCHAR2 ,
2091 x_msg_count OUT NOCOPY NUMBER ,
2092 x_msg_data OUT NOCOPY VARCHAR2)
2093 IS
2094 l_api_name CONSTANT VARCHAR2(30) := 'process_load';
2095 l_progress VARCHAR2(10);
2096 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2097 -- Variables used to call Suggestions_PUB and activate_operation_instance
2098 l_number_of_rows NUMBER;
2099 l_crossdock VARCHAR2(3);
2100 l_mo_line_id NUMBER;
2101 l_error_code NUMBER;
2102 l_task_execute_rec WMS_DISPATCHED_TASKS%ROWTYPE;
2103 l_emp_id NUMBER;
2104 -- Variables used to determine the drop type when calling Suggestions_PUB
2105 l_drop_type VARCHAR2(3);
2106 l_txn_source_type_id NUMBER;
2107 l_txn_type_id NUMBER;
2108 -- Cursor to get MMTT suggestions created by call to Suggestions_PUB.
2109 -- Make sure we don't pick up dummy pack/unpack MMTT records inserted
2110 -- by other transactions. Putaway MMTT suggestions should not be
2111 -- of type Container Split or Inventory sub transfer. Putaway
2112 -- suggestions if in inventory should be of type Move order sub transfers.
2113 CURSOR mmtt_suggestions_cursor IS
2114 SELECT transaction_temp_id
2115 FROM mtl_material_transactions_temp
2116 WHERE organization_id = p_organization_id
2117 AND lpn_id = p_lpn_id
2118 AND transaction_header_id <> p_txn_header_id
2119 AND NOT (transaction_source_type_id = 13 AND
2120 transaction_type_id IN (89, 2) AND
2121 transaction_action_id IN (52, 2))
2122 AND move_order_line_id = l_mo_line_id;
2123 l_mmtt_temp_id NUMBER;
2124 -- Cursor to get move order lines tied to the txn header id
2125 -- for the dummy pack/unpack MMTT records that were inserted.
2126 -- These records will be of type container split or inventory
2127 -- sub transfer.
2128 CURSOR mo_lines_cursor IS
2129 SELECT DISTINCT move_order_line_id
2130 FROM mtl_material_transactions_temp
2131 WHERE organization_id = p_organization_id
2132 AND transaction_header_id = p_txn_header_id
2133 AND (transaction_source_type_id = 13 AND
2134 transaction_type_id IN (89, 2) AND
2135 transaction_action_id IN (52, 2))
2136 AND move_order_line_id IS NOT NULL
2137 ORDER BY move_order_line_id ASC;
2138 -- Variables used to call validate_operation API
2139 l_inspection_flag NUMBER;
2140 l_load_flag NUMBER;
2141 l_drop_flag NUMBER;
2142 l_load_prim_quantity NUMBER;
2143 l_drop_prim_quantity NUMBER;
2144 l_inspect_prim_quantity NUMBER;
2145 l_suggestions_created BOOLEAN;
2146 -- laks
2147 l_load_sec_quantity NUMBER;
2148 l_drop_sec_quantity NUMBER;
2149 l_inspect_sec_quantity NUMBER;
2150 -- Variable used for calling pack_unpack_split
2151 l_mo_lines_tb inv_rcv_integration_apis.mo_in_tb_tp;
2152 l_index NUMBER;
2153 -- Variable used to store the current receiving txn mode
2154 l_txn_mode_code VARCHAR2(25);
2155 -- Variables used to call the Inventory TM
2156 l_txn_return_status NUMBER := 0;
2157 -- Cursor used to get pack/unpack info for WIP LPNs.
2158 -- Bug# 3220020
2159 -- Since the transfer sub/loc fields in the dummy MMTT's are not
2160 -- populated anymore if there is no change in sub/loc, use an NVL
2161 -- and pick up the sub/loc in case the xfr sub/loc fields are empty.
2162 CURSOR wip_pup_cursor IS
2163 SELECT mmtt.transaction_temp_id,
2164 mmtt.inventory_item_id,
2165 mmtt.revision,
2166 mmtt.subinventory_code,
2167 mmtt.locator_id,
2168 mmtt.transaction_quantity,
2169 mmtt.transaction_uom,
2170 -- laks
2171 mmtt.secondary_transaction_quantity,
2172 mmtt.secondary_uom_code,
2173 NVL(mmtt.transfer_subinventory, mmtt.subinventory_code),
2174 NVL(mmtt.transfer_to_location, mmtt.locator_id),
2175 mmtt.cost_group_id,
2176 mmtt.lpn_id,
2177 mmtt.transfer_lpn_id,
2178 mtlt.lot_number,
2179 mtlt.serial_transaction_temp_id
2180 FROM mtl_material_transactions_temp mmtt,
2181 mtl_transaction_lots_temp mtlt
2182 WHERE mmtt.organization_id = p_organization_id
2183 AND mmtt.transaction_header_id = p_txn_header_id
2184 AND (mmtt.transaction_source_type_id = 13 AND
2185 mmtt.transaction_type_id IN (89, 2) AND
2186 mmtt.transaction_action_id IN (52, 2))
2187 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+);
2188 -- Variables used to retrieve info from wip_pup_cursor
2189 l_inventory_item_id NUMBER;
2190 l_revision VARCHAR2(3);
2191 l_subinv_code VARCHAR2(10);
2192 l_locator_id NUMBER;
2193 l_txn_quantity NUMBER;
2194 l_txn_uom VARCHAR2(3);
2195 -- laks
2196 l_sec_txn_quantity NUMBER;
2197 l_sec_uom VARCHAR2(3);
2198 l_to_subinv_code VARCHAR2(10);
2199 l_to_locator_id NUMBER;
2200 l_cost_group_id NUMBER;
2201 l_from_lpn_id NUMBER;
2202 l_to_lpn_id NUMBER;
2203 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2204 l_lot_number VARCHAR2(80);
2205 l_ser_trx_id NUMBER;
2206 -- Cursor used to get serial pack/unpack info for WIP LPNs
2207 CURSOR wip_serial_cursor IS
2208 SELECT fm_serial_number, to_serial_number
2209 FROM mtl_serial_numbers_temp
2210 WHERE transaction_temp_id = l_ser_trx_id;
2211 -- Variables used to retrieve info from wip_serial_cursor
2212 l_serial_number_ctrl_code NUMBER;
2213 l_lot_control_code NUMBER;
2214 l_from_serial_number VARCHAR2(30);
2215 l_to_serial_number VARCHAR2(30);
2216 l_prefix VARCHAR2(30);
2217 l_quantity NUMBER;
2218 l_from_number NUMBER;
2219 l_to_number NUMBER;
2220 l_errorcode NUMBER;
2221 -- Variables used to call cleanup_ATF
2222 l_return_status VARCHAR2(1);
2223 l_msg_count NUMBER;
2224 l_msg_data VARCHAR2(2500);
2225 -- Bug# 3446419
2226 -- Boolean variable indicating if the RCV TM was called yet or not
2227 l_rcv_tm_called BOOLEAN := FALSE;
2228 l_consolidation_method_id NUMBER;
2229 l_drop_lpn_option NUMBER;
2230
2231 --Bug 4566517
2232 l_into_sub VARCHAR2(10);
2233 l_into_loc NUMBER;
2234
2235 BEGIN
2236 IF (l_debug = 1) THEN
2237 print_debug('***Calling process_load with the following parameters***');
2238 print_debug('p_txn_header_id: ======> ' || p_txn_header_id);
2239 print_debug('p_serial_txn_temp_id: => ' || p_serial_txn_temp_id);
2240 print_debug('p_lpn_context: ========> ' || p_lpn_context);
2241 print_debug('p_lpn_id: =============> ' || p_lpn_id);
2242 print_debug('p_into_lpn_id: ========> ' || p_into_lpn_id);
2243 print_debug('p_organization_id: ====> ' || p_organization_id);
2244 print_debug('p_user_id: ============> ' || p_user_id);
2245 print_debug('p_eqp_ins: ============> ' || p_eqp_ins);
2246 END IF;
2247
2248 -- Set the savepoint
2249 SAVEPOINT process_load_sp;
2250 l_progress := '10';
2251
2252 -- Initialize message list to clear any existing messages
2253 fnd_msg_pub.initialize;
2254 l_progress := '15';
2255
2256 -- Set the return status to success
2257 x_return_status := fnd_api.g_ret_sts_success;
2258 l_progress := '20';
2259
2260 -- Get the employee ID so we can populate the l_task_execute_rec
2261 -- properly when calling activate_operation_instance
2262 IF (l_debug = 1) THEN
2263 print_debug('Retrieve the employee ID from the user ID');
2264 END IF;
2265 BEGIN
2266 SELECT employee_id
2267 INTO l_emp_id
2268 FROM fnd_user
2269 WHERE user_id = p_user_id;
2270 EXCEPTION
2271 WHEN NO_DATA_FOUND THEN
2272 IF (l_debug = 1) THEN
2273 print_debug('There is no employee tied to the user');
2274 END IF;
2275 l_emp_id := NULL;
2276 END;
2277 IF (l_debug = 1) THEN
2278 print_debug('Employee ID: ' || l_emp_id);
2279 END IF;
2280 l_progress := '30';
2281
2282 -- Set up the WDT record type properly with the necessary values
2283 l_task_execute_rec.organization_id := p_organization_id;
2284 l_task_execute_rec.person_id := l_emp_id;
2285 l_task_execute_rec.equipment_instance := p_eqp_ins;
2286 l_task_execute_rec.user_task_type := -1;
2287 l_task_execute_rec.task_type := 2;
2288 IF (l_debug = 1) THEN
2289 print_debug('Assigned the necessary values to WDT record type');
2290 END IF;
2291 l_progress := '40';
2292
2293 -- Call Suggestions_PUB for each move order line that is
2294 -- tied to the transaction header ID passed
2295 IF (l_debug = 1) THEN
2296 print_debug('Open the move order lines cursor');
2297 END IF;
2298 l_index := 1;
2299 OPEN mo_lines_cursor;
2300 LOOP
2301 FETCH mo_lines_cursor INTO l_mo_line_id;
2302 EXIT WHEN mo_lines_cursor%NOTFOUND;
2303 IF (l_debug = 1) THEN
2304 print_debug('Current MO line ID: ' || l_mo_line_id);
2305 END IF;
2306 l_progress := '50';
2307
2308 -- Store the move order line found in a table to be used later
2309 -- on for INV and WIP case when we want to be able to update the
2310 -- MOL for the LPN ID once we are done processing the dummy MMTT
2311 -- pack/unpack transaction records.
2312 l_mo_lines_tb(l_index).line_id := l_mo_line_id;
2313
2314 -- Set the drop type if this is an Inventory Item Load and the move
2315 -- order line was created by pre_process_load. This will be the
2316 -- case if the transaction source type and transaction type is
2317 -- Move Order and Subinventory Transfer respectively.
2318 -- Otherwise just pass in a null value for p_drop_type
2319 IF (p_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_INV) THEN
2320 SELECT NVL(transaction_source_type_id, -999),
2321 NVL(transaction_type_id, -999)
2322 INTO l_txn_source_type_id, l_txn_type_id
2323 FROM mtl_txn_request_lines
2324 WHERE line_id = l_mo_line_id;
2325 IF (l_debug = 1) THEN
2326 print_debug('Move Order Line values');
2327 print_debug('Txn Source Type ID: => ' || l_txn_source_type_id);
2328 print_debug('Txn Type ID: ========> ' || l_txn_type_id);
2329 END IF;
2330 l_progress := '55';
2331
2332 IF (l_txn_source_type_id = 4 AND l_txn_type_id = 64) THEN
2333 l_drop_type := 'IIL';
2334 END IF;
2335 END IF;
2336 IF (l_debug = 1) THEN
2337 print_debug('Drop Type: ' || l_drop_type);
2338 END IF;
2339
2340 -- Call the Suggestions_PUB API for the current MO line
2341 IF (l_debug = 1) THEN
2342 print_debug('Call the suggestions_pub API');
2343 END IF;
2344 WMS_Task_Dispatch_put_away.suggestions_pub
2345 (p_lpn_id => p_lpn_id,
2346 p_org_id => p_organization_id,
2347 p_user_id => p_user_id,
2348 p_eqp_ins => p_eqp_ins,
2349 x_number_of_rows => l_number_of_rows,
2350 x_return_status => x_return_status,
2351 x_msg_count => x_msg_count,
2352 x_msg_data => x_msg_data,
2353 x_crossdock => l_crossdock,
2354 p_status => 4,
2355 p_check_for_crossdock => 'Y',
2356 p_move_order_line_id => l_mo_line_id,
2357 p_commit => 'N',
2358 p_drop_type => l_drop_type);
2359
2360 IF (l_debug = 1) THEN
2361 print_debug('Finished calling the suggestions_pub API');
2362 END IF;
2363 l_progress := '60';
2364
2365 -- Check to see if the suggestions_pub API returned successfully
2366 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
2367 IF (l_debug = 1) THEN
2368 print_debug('Success returned from suggestions_pub API');
2369 END IF;
2370 ELSE
2371 IF (l_debug = 1) THEN
2372 print_debug('Failure returned from suggestions_pub API');
2373 END IF;
2374 RAISE FND_API.G_EXC_ERROR;
2375 END IF;
2376
2377 -- For each suggestion generated, call the ATF runtime API
2378 -- to activate the operation instance. This will generate the
2379 -- WDT records for the given MMTT suggestion/task
2380 IF (l_debug = 1) THEN
2381 print_debug('Open the MMTT suggestions cursor');
2382 END IF;
2383 -- Initialize the suggestion_created boolean variable
2384 l_suggestions_created := FALSE;
2385 OPEN mmtt_suggestions_cursor;
2386 LOOP
2387 FETCH mmtt_suggestions_cursor INTO l_mmtt_temp_id;
2388 EXIT WHEN mmtt_suggestions_cursor%NOTFOUND;
2389 IF (l_debug = 1) THEN
2390 print_debug('Current MO line ID: =========> ' || l_mo_line_id);
2391 print_debug('Current MMTT suggestion ID: => ' || l_mmtt_temp_id);
2392 END IF;
2393 l_progress := '70';
2394
2395 -- Call the validate_operation API to see if this MMTT suggestion
2396 -- that is tied to the move order line is valid
2397 IF (l_debug = 1) THEN
2398 print_debug('Call validate_operation API for the given MMTT');
2399 END IF;
2400 wms_atf_runtime_pub_apis.validate_operation
2401 (x_return_status => x_return_status,
2402 x_msg_data => x_msg_data,
2403 x_msg_count => x_msg_count,
2404 x_error_code => l_error_code,
2405 x_inspection_flag => l_inspection_flag,
2406 x_load_flag => l_load_flag,
2407 x_drop_flag => l_drop_flag,
2408 x_load_prim_quantity => l_load_prim_quantity,
2409 x_drop_prim_quantity => l_drop_prim_quantity,
2410 x_inspect_prim_quantity => l_inspect_prim_quantity,
2411 p_source_task_id => l_mmtt_temp_id,
2412 p_move_order_line_id => l_mo_line_id,
2413 p_inventory_item_id => NULL,
2414 p_lpn_id => p_lpn_id,
2415 p_activity_type_id => WMS_GLOBALS.G_OP_ACTIVITY_INBOUND,
2416 p_organization_id => p_organization_id);
2417
2418 IF (l_debug = 1) THEN
2419 print_debug('Finished calling the validate_operation API');
2420 END IF;
2421 l_progress := '75';
2422
2423 -- Check to see if the validate_operation API returned successfully
2424 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
2425 IF (l_debug = 1) THEN
2426 print_debug('Success returned from validate_operation API');
2427 END IF;
2428 ELSE
2429 IF (l_debug = 1) THEN
2430 print_debug('Failure returned from validate_operation API');
2431 print_debug('Error code: ' || l_error_code);
2432 END IF;
2433 RAISE FND_API.G_EXC_ERROR;
2434 END IF;
2435
2436 -- Now check to see if this MMTT suggestion is allowed to be loaded
2437 IF (l_debug = 1) THEN
2438 print_debug('Values returned from call to validate_operation');
2439 print_debug('x_inspection_flag: =======> ' || l_inspection_flag);
2440 print_debug('x_load_flag: =============> ' || l_load_flag);
2441 print_debug('x_drop_flag: =============> ' || l_drop_flag);
2442 print_debug('x_load_prim_quantity: ====> ' || l_load_prim_quantity);
2443 print_debug('x_drop_prim_quantity: ====> ' || l_drop_prim_quantity);
2444 print_debug('x_inspect_prim_quantity: => ' || l_inspect_prim_quantity);
2445 END IF;
2446
2447 -- If the MMTT line is valid, then call activate_operation_instance.
2448 -- Since we passed in an MMTT source task ID into validate_operation,
2449 -- the procedure should either return No Load or Full Load for
2450 -- the MMTT suggestion line.
2451 IF (l_load_flag = WMS_ATF_RUNTIME_PUB_APIS.G_FULL_LOAD) THEN
2452 IF (l_debug = 1) THEN
2453 print_debug('Call the activate_operation_instance API');
2454 END IF;
2455
2456 -- Found a valid MMTT suggestion so set the boolean variable
2457 l_suggestions_created := TRUE;
2458
2459 -- Set the current move order line ID in the WDT task record
2460 l_task_execute_rec.move_order_line_id := l_mo_line_id;
2461
2462 wms_atf_runtime_pub_apis.activate_operation_instance
2463 (x_return_status => x_return_status,
2464 x_msg_data => x_msg_data,
2465 x_msg_count => x_msg_count,
2466 x_error_code => l_error_code,
2467 p_source_task_id => l_mmtt_temp_id,
2468 p_activity_id => WMS_GLOBALS.G_OP_ACTIVITY_INBOUND,
2469 p_operation_type_id => WMS_GLOBALS.G_OP_TYPE_LOAD,
2470 p_task_execute_rec => l_task_execute_rec,
2471 x_consolidation_method_id => l_consolidation_method_id,
2472 x_drop_lpn_option => l_drop_lpn_option
2473 );
2474
2475 IF (l_debug = 1) THEN
2476 print_debug('Finished calling the activate_operation_instance API');
2477 END IF;
2478 l_progress := '80';
2479
2480 -- Check to see if the activate_operation_instance API returned successfully
2481 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
2482 IF (l_debug = 1) THEN
2483 print_debug('Success returned from activate_operation_instance API');
2484 END IF;
2485 ELSE
2486 IF (l_debug = 1) THEN
2487 print_debug('Failure returned from activate_operation_instance API');
2488 print_debug('Error code: ' || l_error_code);
2489 END IF;
2490 RAISE FND_API.G_EXC_ERROR;
2491 END IF;
2492
2493 END IF;
2494 END LOOP;
2495 CLOSE mmtt_suggestions_cursor;
2496 IF (l_debug = 1) THEN
2497 print_debug('Closed the MMTT suggestions cursor');
2498 END IF;
2499 l_progress := '90';
2500
2501 -- Check that suggestions were created for the MOL
2502 IF (l_suggestions_created) THEN
2503 IF (l_debug = 1) THEN
2504 print_debug('Suggestions were successfully created for MOL');
2505 END IF;
2506 ELSE
2507 -- Unable to load item if no valid suggestions were created
2508 IF (l_debug = 1) THEN
2509 print_debug('No valid suggestions created for MOL!');
2510 END IF;
2511 FND_MESSAGE.SET_NAME ('WMS', 'WMS_ALLOCATE_FAIL');
2512 FND_MSG_PUB.ADD;
2513 RAISE FND_API.G_EXC_ERROR;
2514 END IF;
2515
2516 -- Increment the table index value
2517 l_index := l_index + 1;
2518 END LOOP;
2519 CLOSE mo_lines_cursor;
2520 IF (l_debug = 1) THEN
2521 print_debug('Closed the move order lines cursor');
2522 END IF;
2523 l_progress := '100';
2524
2525 -- Receiving LPN item load txn
2526 IF (p_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_RCV) THEN
2527 -- Initialize l_mo_lines_tb. This is used for INV and WIP cases to
2528 -- store the move order lines used but is not necessary for RCV.
2529 l_mo_lines_tb.DELETE;
2530
2531 -- Call the pack_unpack_split API to create RTI records
2532 -- needed for processing the item load receiving transfer
2533 IF (l_debug = 1) THEN
2534 print_debug('Processing item load for a RCV LPN');
2535 print_debug('Call the pack_unpack_split API');
2536 END IF;
2537
2538 wms_rcv_pup_pvt.pack_unpack_split
2539 (p_header_id => p_txn_header_id ,
2540 p_call_rcv_tm => fnd_api.g_false ,
2541 x_return_status => x_return_status ,
2542 x_msg_count => x_msg_count ,
2543 x_msg_data => x_msg_data ,
2544 x_mo_lines_tb => l_mo_lines_tb
2545 );
2546 IF (l_debug = 1) THEN
2547 print_debug('Finished calling the pack_unpack_split API');
2548 END IF;
2549 l_progress := '110';
2550
2551 -- Check to see if the call to pack_unpack_split returned successfully
2552 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
2553 IF (l_debug = 1) THEN
2554 print_debug('Success returned from pack_unpack_split API');
2555 END IF;
2556 ELSE
2557 IF (l_debug = 1) THEN
2558 print_debug('Failure returned from pack_unpack_split API');
2559 END IF;
2560 RAISE FND_API.G_EXC_ERROR;
2561 END IF;
2562 l_progress := '120';
2563
2564 -- Finally call the Receiving TM
2565 IF (l_debug = 1) THEN
2566 print_debug('Call the Receiving TM');
2567 END IF;
2568
2569 -- Store the original receiving txn mode in a local variable
2570 l_txn_mode_code := inv_rcv_common_apis.g_po_startup_value.transaction_mode;
2571
2572 -- Set the receiving txn mode to online
2573 inv_rcv_common_apis.g_po_startup_value.transaction_mode := 'ONLINE';
2574 IF (l_debug = 1) THEN
2575 print_debug('Temporarily set the RCV transaction mode to ONLINE');
2576 END IF;
2577 l_progress := '130';
2578 --Bug 6944334
2579 Begin
2580 print_debug('updating proceesing mode in rti for group id'||inv_rcv_common_apis.g_rcv_global_var.interface_group_id);
2581 UPDATE RCV_TRANSACTIONS_INTERFACE
2582 SET PROCESSING_MODE_CODE = 'ONLINE'
2583 WHERE GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
2584 EXCEPTION
2585 WHEN OTHERS THEN
2586 print_debug('no record found in rti to ');
2587 END;
2588 --End of Bug 6944334
2589
2590
2591 -- Call the receiving TM
2592 inv_rcv_mobile_process_txn.rcv_process_receive_txn
2593 (x_return_status => x_return_status,
2594 x_msg_data => x_msg_data
2595 );
2596
2597 -- Bug# 3446419
2598 -- Mark the variable as TRUE to indicate that the RCV TM was called
2599 -- implying that a commit was done.
2600 l_rcv_tm_called := TRUE;
2601
2602 -- Revert the receiving txn mode
2603 inv_rcv_common_apis.g_po_startup_value.transaction_mode := l_txn_mode_code;
2604 IF (l_debug = 1) THEN
2605 print_debug('Reverted the RCV transaction mode to previous value');
2606 END IF;
2607 l_progress := '140';
2608
2609 -- Call this to clean up the RCV globals
2610 -- Bug# 3251237: Always call this whether or not the TM call is
2611 -- successful. Previously this would not be called in case the
2612 -- Rcv TM errored out since an exception would be thrown.
2613 inv_rcv_common_apis.rcv_clear_global;
2614 IF (l_debug = 1) THEN
2615 print_debug('Finished calling rcv_clear_global API');
2616 END IF;
2617 l_progress := '150';
2618
2619 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2620 IF (l_debug = 1) THEN
2621 print_debug('Error calling the Receiving TM!');
2622 END IF;
2623 FND_MESSAGE.SET_NAME ('WMS', 'WMS_TD_TXNMGR_ERROR');
2624 FND_MSG_PUB.ADD;
2625 RAISE FND_API.G_EXC_ERROR;
2626 ELSE
2627 IF (l_debug = 1) THEN
2628 print_debug('Successfully called the Receiving TM');
2629 END IF;
2630 END IF;
2631
2632 END IF;
2633
2634 -- INV LPN item load txn
2635 IF (p_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_INV) THEN
2636 -- Call the INV TM to process the dummy pack/unpack MMTT records
2637 IF (l_debug = 1) THEN
2638 print_debug('Processing item load for an INV LPN');
2639 print_debug('Calling the INV TM online: ' || p_txn_header_id);
2640 END IF;
2641 -- Call the Inventory TM in online mode (1), passing in the
2642 -- Inventory business flow code (30)
2643 l_txn_return_status := inv_lpn_trx_pub.process_lpn_trx
2644 (p_trx_hdr_id => p_txn_header_id,
2645 p_commit => fnd_api.g_false,
2646 x_proc_msg => x_msg_data,
2647 p_proc_mode => 1,
2648 p_atomic => fnd_api.g_true,
2649 p_business_flow_code => 30);
2650
2651 -- Check if the Transaction Manager was successful or not
2652 IF (l_debug = 1) THEN
2653 print_debug ('Txn return status: ' || l_txn_return_status);
2654 END IF;
2655 l_progress := '160';
2656
2657 IF (l_txn_return_status <> 0) THEN
2658 IF (l_debug = 1) THEN
2659 print_debug('Error calling the Inventory TM');
2660 END IF;
2661 FND_MESSAGE.SET_NAME ('WMS', 'WMS_TD_TXNMGR_ERROR');
2662 FND_MSG_PUB.ADD;
2663 RAISE FND_API.G_EXC_ERROR;
2664 ELSE
2665 IF (l_debug = 1) THEN
2666 print_debug('Success calling the Inventory TM');
2667 END IF;
2668 END IF;
2669 END IF;
2670
2671 -- WIP LPN item load txn
2672 IF (p_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_WIP) THEN
2673 IF (l_debug = 1) THEN
2674 print_debug('Processing item load for a WIP LPN');
2675 print_debug('Call the PackUnpack_Container API directly');
2676 END IF;
2677
2678 -- Open up the wip_pup_cursor to get pack/unpack info
2679 OPEN wip_pup_cursor;
2680 LOOP
2681 FETCH wip_pup_cursor INTO l_mmtt_temp_id, l_inventory_item_id,
2682 l_revision, l_subinv_code, l_locator_id, l_txn_quantity,
2683 l_txn_uom, l_sec_txn_quantity, l_sec_uom, l_to_subinv_code, l_to_locator_id, l_cost_group_id,
2684 l_from_lpn_id, l_to_lpn_id, l_lot_number, l_ser_trx_id;
2685 EXIT WHEN wip_pup_cursor%NOTFOUND;
2686 IF (l_debug = 1) THEN
2687 print_debug('Current item info for WIP pack/unpack operation');
2688 print_debug('Transaction Temp ID: => ' || l_mmtt_temp_id);
2689 print_debug('Inventory Item ID: ===> ' || l_inventory_item_id);
2690 print_debug('Revision: ============> ' || l_revision);
2691 print_debug('Subinventory Code: ===> ' || l_subinv_code);
2692 print_debug('Locator ID: ==========> ' || l_locator_id);
2693 print_debug('Transaction Qty: =====> ' || l_txn_quantity);
2694 print_debug('Transaction UOM: =====> ' || l_txn_uom);
2695 --laks
2696 print_debug('Sec Trnsn Qty: =====> ' || l_sec_txn_quantity);
2697 print_debug('Sec UOM: =============> ' || l_sec_uom);
2698 print_debug('To Sub Code: =========> ' || l_to_subinv_code);
2699 print_debug('To Locator ID: =======> ' || l_to_locator_id);
2700 print_debug('Cost Group ID: =======> ' || l_cost_group_id);
2701 print_debug('From LPN ID: =========> ' || l_from_lpn_id);
2702 print_debug('To LPN ID: ===========> ' || l_to_lpn_id);
2703 print_debug('Lot Number: ==========> ' || l_lot_number);
2704 print_debug('Serial Txn Temp ID: ==> ' || l_ser_trx_id);
2705 END IF;
2706 l_progress := '170';
2707
2708 -- Get the lot and serial number control code for the item
2709 SELECT serial_number_control_code, lot_control_code
2710 INTO l_serial_number_ctrl_code, l_lot_control_code
2711 FROM mtl_system_items
2712 WHERE inventory_item_id = l_inventory_item_id
2713 AND organization_id = p_organization_id;
2714 IF (l_debug = 1) THEN
2715 print_debug('Serial Number Code: ' || l_serial_number_ctrl_code);
2716 print_debug('Lot Control Code: ' || l_lot_control_code);
2717 END IF;
2718 l_progress := '180';
2719
2720 IF (l_serial_number_ctrl_code IN (1, 6)) THEN
2721 -- Not serial controlled
2722
2723 -- Call the PackUnpack_Container API directly to first
2724 -- unpack the item load material from the source LPN
2725 IF (l_debug = 1) THEN
2726 print_debug('Unpack the material from the source LPN');
2727 END IF;
2728 wms_container_pub.PackUnpack_Container
2729 (p_api_version => 1.0,
2730 p_validation_level => fnd_api.g_valid_level_none,
2731 x_return_status => x_return_status,
2732 x_msg_count => x_msg_count,
2733 x_msg_data => x_msg_data,
2734 p_lpn_id => l_from_lpn_id,
2735 p_content_item_id => l_inventory_item_id,
2736 p_revision => l_revision,
2737 p_lot_number => l_lot_number,
2738 p_from_serial_number => NULL,
2739 p_to_serial_number => NULL,
2740 p_quantity => l_txn_quantity,
2741 p_uom => l_txn_uom,
2742 -- laks
2743 p_sec_quantity => l_sec_txn_quantity,
2744 p_sec_uom => l_sec_uom,
2745 p_organization_id => p_organization_id,
2746 p_subinventory => l_subinv_code,
2747 p_locator_id => l_locator_id,
2748 p_operation => 2,
2749 p_cost_group_id => l_cost_group_id);
2750 IF (l_debug = 1) THEN
2751 print_debug('Finished calling PackUnpack_Container for Unpack operation');
2752 END IF;
2753 l_progress := '190';
2754
2755 -- Check to see if the call to PackUnpack_Container returned successfully
2756 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
2757 IF (l_debug = 1) THEN
2758 print_debug('Success returned from PackUnpack_Container API');
2759 END IF;
2760 ELSE
2761 IF (l_debug = 1) THEN
2762 print_debug('Failure returned from PackUnpack_Container API');
2763 END IF;
2764 RAISE FND_API.G_EXC_ERROR;
2765 END IF;
2766 l_progress := '200';
2767
2768 -- Now call the PackUnpack_Container API to pack the loose
2769 -- item load material into the destination LPN.
2770 -- Since this is in WIP, it should be okay to just pass the
2771 -- sub/loc of the source LPN when doing the pack into the
2772 -- destination LPN.
2773 IF (l_debug = 1) THEN
2774 print_debug('Pack the material into the destination LPN');
2775 END IF;
2776 wms_container_pub.PackUnpack_Container
2777 (p_api_version => 1.0,
2778 p_validation_level => fnd_api.g_valid_level_none,
2779 x_return_status => x_return_status,
2780 x_msg_count => x_msg_count,
2781 x_msg_data => x_msg_data,
2782 p_lpn_id => l_to_lpn_id,
2783 p_content_item_id => l_inventory_item_id,
2784 p_revision => l_revision,
2785 p_lot_number => l_lot_number,
2786 p_from_serial_number => NULL,
2787 p_to_serial_number => NULL,
2788 p_quantity => l_txn_quantity,
2789 p_uom => l_txn_uom,
2790 -- laks
2791 p_sec_quantity => l_sec_txn_quantity,
2792 p_sec_uom => l_sec_uom,
2793 p_organization_id => p_organization_id,
2794 p_subinventory => l_subinv_code,
2795 p_locator_id => l_locator_id,
2796 p_operation => 1,
2797 p_cost_group_id => l_cost_group_id);
2798 IF (l_debug = 1) THEN
2799 print_debug('Finished calling PackUnpack_Container for Pack operation');
2800 END IF;
2801 l_progress := '210';
2802
2803 -- Check to see if the call to PackUnpack_Container returned successfully
2804 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
2805 IF (l_debug = 1) THEN
2806 print_debug('Success returned from PackUnpack_Container API');
2807 END IF;
2808 ELSE
2809 IF (l_debug = 1) THEN
2810 print_debug('Failure returned from PackUnpack_Container API');
2811 END IF;
2812 FND_MESSAGE.SET_NAME ('WMS', 'WMS_CONT_INVALID_SER');
2813 FND_MSG_PUB.ADD;
2814 RAISE FND_API.G_EXC_ERROR;
2815 END IF;
2816 l_progress := '220';
2817
2818 ELSE
2819 -- Serial controlled
2820
2821 -- Set the value for the serial txn temp ID
2822 IF (l_ser_trx_id IS NULL) THEN
2823 l_ser_trx_id := l_mmtt_temp_id;
2824 END IF;
2825
2826 OPEN wip_serial_cursor;
2827 LOOP
2828 FETCH wip_serial_cursor INTO l_from_serial_number, l_to_serial_number;
2829 EXIT WHEN wip_serial_cursor%NOTFOUND;
2830 IF (l_debug = 1) THEN
2831 print_debug('Current serials: ' || l_from_serial_number
2832 || ' - ' || l_to_serial_number);
2833 END IF;
2834 l_progress := '230';
2835
2836 -- Call this API to parse the serial numbers into prefixes and numbers.
2837 -- We need to call this API to get the number of serials in
2838 -- the range in case the from and to serial are different.
2839 IF (l_debug = 1) THEN
2840 print_debug('Call inv_serial_info API to parse the serials');
2841 END IF;
2842 IF (NOT MTL_Serial_Check.inv_serial_info
2843 (p_from_serial_number => l_from_serial_number,
2844 p_to_serial_number => l_to_serial_number,
2845 x_prefix => l_prefix,
2846 x_quantity => l_quantity,
2847 x_from_number => l_from_number,
2848 x_to_number => l_to_number,
2849 x_errorcode => l_errorcode)) THEN
2850 IF (l_debug = 1) THEN
2851 print_debug('Could not successfully parse the serials!');
2852 END IF;
2853 FND_MESSAGE.SET_NAME ('WMS', 'WMS_CONT_INVALID_SER');
2854 FND_MSG_PUB.ADD;
2855 RAISE FND_API.G_EXC_ERROR;
2856 END IF;
2857 IF (l_debug = 1) THEN
2858 print_debug('Successfully parsed the serials');
2859 print_debug('Prefix: ======> ' || l_prefix);
2860 print_debug('Quantity: ====> ' || l_quantity);
2861 print_debug('From Number: => ' || l_from_number);
2862 print_debug('To Number: ===> ' || l_to_number);
2863 print_debug('Error Code: ==> ' || l_errorcode);
2864 END IF;
2865 l_progress := '240';
2866
2867 -- Call the PackUnpack_Container API directly to first
2868 -- unpack the item load serials from the source LPN.
2869 -- The dummy MMTT records are always inserted with the
2870 -- item's primary UOM code for the transaction UOM.
2871 IF (l_debug = 1) THEN
2872 print_debug('Unpack the serials from the source LPN');
2873 END IF;
2874 wms_container_pub.PackUnpack_Container
2875 (p_api_version => 1.0,
2876 p_validation_level => fnd_api.g_valid_level_none,
2877 x_return_status => x_return_status,
2878 x_msg_count => x_msg_count,
2879 x_msg_data => x_msg_data,
2880 p_lpn_id => l_from_lpn_id,
2881 p_content_item_id => l_inventory_item_id,
2882 p_revision => l_revision,
2883 p_lot_number => l_lot_number,
2884 p_from_serial_number => l_from_serial_number,
2885 p_to_serial_number => l_to_serial_number,
2886 p_quantity => l_quantity,
2887 p_uom => l_txn_uom,
2888 p_organization_id => p_organization_id,
2889 p_subinventory => l_subinv_code,
2890 p_locator_id => l_locator_id,
2891 p_operation => 2,
2892 p_cost_group_id => l_cost_group_id);
2893 IF (l_debug = 1) THEN
2894 print_debug('Finished calling PackUnpack_Container for Unpack operation');
2895 END IF;
2896 l_progress := '250';
2897
2898 -- Check to see if the call to PackUnpack_Container returned successfully
2899 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
2900 IF (l_debug = 1) THEN
2901 print_debug('Success returned from PackUnpack_Container API');
2902 END IF;
2903 ELSE
2904 IF (l_debug = 1) THEN
2905 print_debug('Failure returned from PackUnpack_Container API');
2906 END IF;
2907 RAISE FND_API.G_EXC_ERROR;
2908 END IF;
2909 l_progress := '260';
2910
2911 -- Now call the PackUnpack_Container API to pack the loose
2912 -- item load serials into the destination LPN
2913 IF (l_debug = 1) THEN
2914 print_debug('Pack the serials into the destination LPN');
2915 END IF;
2916 wms_container_pub.PackUnpack_Container
2917 (p_api_version => 1.0,
2918 p_validation_level => fnd_api.g_valid_level_none,
2919 x_return_status => x_return_status,
2920 x_msg_count => x_msg_count,
2921 x_msg_data => x_msg_data,
2922 p_lpn_id => l_to_lpn_id,
2923 p_content_item_id => l_inventory_item_id,
2924 p_revision => l_revision,
2925 p_lot_number => l_lot_number,
2926 p_from_serial_number => l_from_serial_number,
2927 p_to_serial_number => l_to_serial_number,
2928 p_quantity => l_quantity,
2929 p_uom => l_txn_uom,
2930 p_organization_id => p_organization_id,
2931 p_subinventory => l_subinv_code,
2932 p_locator_id => l_locator_id,
2933 p_operation => 1,
2934 p_cost_group_id => l_cost_group_id);
2935 IF (l_debug = 1) THEN
2936 print_debug('Finished calling PackUnpack_Container for Pack operation');
2937 END IF;
2938 l_progress := '270';
2939
2940 -- Check to see if the call to PackUnpack_Container returned successfully
2941 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
2942 IF (l_debug = 1) THEN
2943 print_debug('Success returned from PackUnpack_Container API');
2944 END IF;
2945 ELSE
2946 IF (l_debug = 1) THEN
2947 print_debug('Failure returned from PackUnpack_Container API');
2948 END IF;
2949 RAISE FND_API.G_EXC_ERROR;
2950 END IF;
2951 l_progress := '280';
2952
2953 END LOOP;
2954 CLOSE wip_serial_cursor;
2955 IF (l_debug = 1) THEN
2956 print_debug('Closed the WIP serial cursor');
2957 END IF;
2958 l_progress := '290';
2959 END IF;
2960 -- End of serial controlled part
2961
2962 -- Delete the dummy MMTT/MTLT/MSNT records once we have processed them
2963 IF (l_ser_trx_id IS NOT NULL) THEN
2964 IF (l_debug = 1) THEN
2965 print_debug('Delete the dummy MSNT records: ' || l_ser_trx_id);
2966 END IF;
2967 DELETE FROM mtl_serial_numbers_temp
2968 WHERE transaction_temp_id = l_ser_trx_id;
2969 END IF;
2970
2971 IF (l_lot_control_code = 2) THEN
2972 IF (l_debug = 1) THEN
2973 print_debug('Delete the dummy MTLT records: ' || l_mmtt_temp_id);
2974 END IF;
2975 DELETE FROM mtl_transaction_lots_temp
2976 WHERE transaction_temp_id = l_mmtt_temp_id;
2977 END IF;
2978
2979 IF (l_debug = 1) THEN
2980 print_debug('Delete the dummy MMTT records: ' || l_mmtt_temp_id);
2981 END IF;
2982 DELETE FROM mtl_material_transactions_temp
2983 WHERE transaction_temp_id = l_mmtt_temp_id;
2984
2985 IF (l_debug = 1) THEN
2986 print_debug('Finished deleting all dummy pack/unpack records');
2987 END IF;
2988 l_progress := '295';
2989
2990 END LOOP;
2991 CLOSE wip_pup_cursor;
2992 -- Finished processing all MMTT records for transaction header ID
2993 IF (l_debug = 1) THEN
2994 print_debug('Closed the WIP PUP cursor');
2995 END IF;
2996 l_progress := '300';
2997
2998 -- If the Into LPN was dynamically generated, we need to update the
2999 -- LPN context from 5 (Defined but not used) to 2 (WIP context).
3000 -- This is done in the INV and RCV TM but for WIP, we do not call a TM
3001 -- to process the load. We can always do this update since we will
3002 -- need to do another query to check the LPN context of the Into LPN.
3003 -- If the Into LPN was not dynamically generated, it should already
3004 -- have an LPN context of 2.
3005 IF (l_debug = 1) THEN
3006 print_debug('Update the LPN context for Into LPN');
3007 END IF;
3008 BEGIN
3009 UPDATE wms_license_plate_numbers
3010 SET lpn_context = 2
3011 WHERE lpn_id = p_into_lpn_id
3012 AND organization_id = p_organization_id;
3013 EXCEPTION
3014 WHEN OTHERS THEN
3015 IF (l_debug = 1) THEN
3016 print_debug('Exception while updating LPN context for Into LPN');
3017 END IF;
3018 END;
3019 IF (l_debug = 1) THEN
3020 print_debug('Successfully updated LPN context for Into LPN');
3021 END IF;
3022 l_progress := '305';
3023
3024 END IF;
3025
3026 -- For INV and WIP LPN item loads, we need to call complete_operation_instance
3027 -- for each MMTT suggestion record. Additionally, we need to update the
3028 -- LPN ID for the MOL and MMTT sugestion records since they still point
3029 -- to the source LPN ID instead of the destination Into LPN ID.
3030 -- Finally, we should clear the group mark ID for the serials used if necessary.
3031 IF ((p_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_WIP) OR
3032 (p_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_INV)) THEN
3033
3034 -- For each move order line that is tied to the transaction header
3035 -- ID, we need to update the LPN ID column to the Into LPN ID.
3036 IF (l_debug = 1) THEN
3037 print_debug('WIP or INV LPN case');
3038 print_debug('Need to update the LPN ID and complete the operations');
3039 print_debug('Query the move order lines table to update the LPN ID');
3040 END IF;
3041
3042 -- First make sure that MOLs were stored in the table
3043 IF (l_mo_lines_tb.COUNT = 0) THEN
3044 IF (l_debug = 1) THEN
3045 print_debug('No Move order lines were stored in the table!');
3046 END IF;
3047 FND_MESSAGE.SET_NAME('WMS', 'WMS_MO_NOT_FOUND');
3048 FND_MSG_PUB.ADD;
3049 RAISE FND_API.G_EXC_ERROR;
3050 END IF;
3051
3052 --Bug 4566517. Get the sub/loc of into lpn
3053 IF (p_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_INV) THEN
3054 BEGIN
3055 SELECT subinventory_code
3056 , locator_id
3057 INTO l_into_sub
3058 , l_into_loc
3059 FROM wms_license_plate_numbers
3060 WHERE lpn_id = p_into_lpn_id;
3061 EXCEPTION
3062 WHEN OTHERS THEN
3063 NULL;
3064 END;
3065 END IF;
3066 --Bug 4566517 end.
3067
3068 l_index := l_mo_lines_tb.FIRST;
3069 LOOP
3070 l_mo_line_id := l_mo_lines_tb(l_index).line_id;
3071 IF (l_debug = 1) THEN
3072 print_debug('Current MO line ID: ' || l_mo_line_id);
3073 END IF;
3074 l_progress := '320';
3075
3076 -- Update the LPN ID in the MOL record
3077 IF (l_debug = 1) THEN
3078 print_debug('Update the LPN ID/SUB/LOC on MOL record');
3079 END IF;
3080 BEGIN
3081 UPDATE mtl_txn_request_lines
3082 SET lpn_id = p_into_lpn_id
3083 , from_subinventory_code = Decode(p_lpn_context,
3084 WMS_CONTAINER_PUB.lpn_context_inv,
3085 l_into_sub,
3086 from_subinventory_code)
3087 , from_locator_id = Decode(p_lpn_context,
3088 WMS_CONTAINER_PUB.lpn_context_inv,
3089 l_into_loc,
3090 from_locator_id)
3091 WHERE line_id = l_mo_line_id
3092 AND organization_id = p_organization_id;
3093 EXCEPTION
3094 WHEN OTHERS THEN
3095 IF (l_debug = 1) THEN
3096 print_debug('Exception while updating LPN ID/SUB/LOC for MOL');
3097 END IF;
3098 END;
3099 IF (l_debug = 1) THEN
3100 print_debug('Successfully updated LPN ID/SUB/LOC on MOL record');
3101 END IF;
3102 l_progress := '330';
3103
3104 -- For each MMTT record tied to the current MOL, we need to call
3105 -- the ATF runtime API complete_operation_instance. This is not
3106 -- done in the INV TM for a pack/unpack transaction. Since we are
3107 -- always processing dummy pack/unpack MMTTs for the Inventory
3108 -- LPN case, we will need to call complete_operation_instance here
3109 -- after the TM is called. This is also not done for WIP LPNs
3110 -- since we call the PackUnpack_Container API directly.
3111 IF (l_debug = 1) THEN
3112 print_debug('Open the MMTT suggestions cursor');
3113 END IF;
3114 OPEN mmtt_suggestions_cursor;
3115 LOOP
3116 FETCH mmtt_suggestions_cursor INTO l_mmtt_temp_id;
3117 EXIT WHEN mmtt_suggestions_cursor%NOTFOUND;
3118 IF (l_debug = 1) THEN
3119 print_debug('Call complete_operation_instance for MMTT: ' || l_mmtt_temp_id);
3120 END IF;
3121 l_progress := '340';
3122
3123 wms_atf_runtime_pub_apis.complete_operation_instance
3124 (x_return_status => x_return_status,
3125 x_msg_data => x_msg_data,
3126 x_msg_count => x_msg_count,
3127 x_error_code => l_error_code,
3128 p_source_task_id => l_mmtt_temp_id,
3129 p_activity_id => WMS_GLOBALS.G_OP_ACTIVITY_INBOUND,
3130 p_operation_type_id => WMS_GLOBALS.G_OP_TYPE_LOAD);
3131
3132 IF (l_debug = 1) THEN
3133 print_debug('Finished calling the complete_operation_instance API');
3134 END IF;
3135 l_progress := '350';
3136
3137 -- Check to see if the complete_operation_instance API returned successfully
3138 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
3139 IF (l_debug = 1) THEN
3140 print_debug('Success returned from complete_operation_instance API');
3141 END IF;
3142 ELSE
3143 IF (l_debug = 1) THEN
3144 print_debug('Failure returned from complete_operation_instance API');
3145 print_debug('Error code: ' || l_error_code);
3146 END IF;
3147 RAISE FND_API.G_EXC_ERROR;
3148 END IF;
3149
3150 -- Update the LPN ID and Allocated LPN ID in the MMTT record
3151 IF (l_debug = 1) THEN
3152 print_debug('Update the LPN ID/sub/loc on MMTT record');
3153 END IF;
3154 BEGIN
3155 UPDATE mtl_material_transactions_temp
3156 SET lpn_id = p_into_lpn_id
3157 , allocated_lpn_id = p_into_lpn_id
3158 , subinventory_code = Decode(p_lpn_context,
3159 WMS_CONTAINER_PUB.lpn_context_inv,
3160 l_into_sub,
3161 subinventory_code)
3162 , locator_id = Decode(p_lpn_context,
3163 WMS_CONTAINER_PUB.lpn_context_inv,
3164 l_into_loc,
3165 locator_id)
3166 WHERE transaction_temp_id = l_mmtt_temp_id
3167 AND organization_id = p_organization_id;
3168 EXCEPTION
3169 WHEN OTHERS THEN
3170 IF (l_debug = 1) THEN
3171 print_debug('Exception while updating LPN ID/sub/loc for MMTT');
3172 END IF;
3173 END;
3174 IF (l_debug = 1) THEN
3175 print_debug('Successfully updated LPN ID/sub/loc on MMTT record');
3176 END IF;
3177 l_progress := '360';
3178
3179 END LOOP;
3180 CLOSE mmtt_suggestions_cursor;
3181 IF (l_debug = 1) THEN
3182 print_debug('Closed the MMTT suggestions cursor');
3183 END IF;
3184 l_progress := '370';
3185
3186 -- Exit if all move order lines in the table have been used
3187 EXIT WHEN l_index = l_mo_lines_tb.LAST;
3188 l_index := l_mo_lines_tb.NEXT(l_index);
3189
3190 END LOOP;
3191 IF (l_debug = 1) THEN
3192 print_debug('Finished processing the move order lines table');
3193 END IF;
3194 l_progress := '380';
3195
3196 -- Clear the serials that were marked during load if necessary
3197 IF (p_serial_txn_temp_id IS NOT NULL) THEN
3198 IF (l_debug = 1) THEN
3199 print_debug('Clear the group mark ID for the marked serials');
3200 END IF;
3201 BEGIN
3202 UPDATE mtl_serial_numbers
3203 SET group_mark_id = NULL
3204 WHERE current_organization_id = p_organization_id
3205 AND group_mark_id = p_serial_txn_temp_id
3206 AND EXISTS (SELECT 1
3207 FROM mtl_serial_numbers_temp
3208 WHERE transaction_temp_id = p_serial_txn_temp_id
3209 AND serial_number BETWEEN fm_serial_number AND
3210 to_serial_number);
3211 EXCEPTION
3212 WHEN OTHERS THEN
3213 IF (l_debug = 1) THEN
3214 print_debug('Exception while clearing group mark ID');
3215 END IF;
3216 END;
3217 l_progress := '310';
3218 END IF;
3219
3220 END IF;
3221 -- Done with INV/WIP case for updating of MOL and MMTT records for the
3222 -- LPN ID columns, calling complete_operation_instance for MMTT suggestions
3223 -- and clearing the group mark ID for serials used if necessary.
3224
3225 -- If a serial txn temp ID is passed, delete the temporary MSNT records
3226 -- used when marking serials from the java side. This should be done
3227 -- for all cases, i.e. RCV, INV, and WIP.
3228 IF (p_serial_txn_temp_id IS NOT NULL) THEN
3229 IF (l_debug = 1) THEN
3230 print_debug('Delete the temporary MSNT records');
3231 END IF;
3232 BEGIN
3233 DELETE FROM mtl_serial_numbers_temp
3234 WHERE transaction_temp_id = p_serial_txn_temp_id;
3235 EXCEPTION
3236 WHEN OTHERS THEN
3237 IF (l_debug = 1) THEN
3238 print_debug('Exception while deleting temporary MSNT records');
3239 END IF;
3240 END;
3241 l_progress := '390';
3242 END IF;
3243
3244 IF (l_debug = 1) THEN
3245 print_debug('***End of process_load***');
3246 END IF;
3247
3248 EXCEPTION
3249 WHEN FND_API.G_EXC_ERROR THEN
3250 -- Call the cleanup_ATF procedure since the call to
3251 -- activate_operation_instance in process_load will insert records
3252 -- into WDT and commit them autonomously. A rollback will not
3253 -- remove those records.
3254 -- Bug# 3446419
3255 -- Also pass in a parameter indicating if the RCV TM was called or not.
3256 cleanup_ATF
3257 (p_txn_header_id => p_txn_header_id,
3258 p_lpn_context => p_lpn_context,
3259 p_lpn_id => p_lpn_id,
3260 p_organization_id => p_organization_id,
3261 p_rcv_tm_called => l_rcv_tm_called,
3262 x_return_status => l_return_status,
3263 x_msg_count => l_msg_count,
3264 x_msg_data => l_msg_data);
3265
3266 -- Check to see if the cleanup_ATF API returned successfully
3267 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
3268 IF (l_debug = 1) THEN
3269 print_debug('Success returned from cleanup_ATF API');
3270 END IF;
3271 ELSE
3272 IF (l_debug = 1) THEN
3273 print_debug('Failure returned from cleanup_ATF API');
3274 END IF;
3275 -- Nothing we can do if the cleanup API's fail.
3276 END IF;
3277
3278 BEGIN
3279 ROLLBACK TO process_load_sp;
3280 EXCEPTION
3281 WHEN OTHERS THEN
3282 -- This implies that a commit was done which should only happen
3283 -- if the RCV TM was called and it errored out
3284 IF (l_debug = 1) THEN
3285 print_debug('Exception while rolling back to save point');
3286 END IF;
3287 END;
3288 x_return_status := fnd_api.g_ret_sts_error;
3289 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3290 p_data => x_msg_data);
3291 IF (l_debug = 1) THEN
3292 print_debug('Exiting process_load - Execution error: ' ||
3293 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
3294 END IF;
3295
3296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3297 -- Call the cleanup_ATF procedure since the call to
3298 -- activate_operation_instance in process_load will insert records
3299 -- into WDT and commit them autonomously. A rollback will not
3300 -- remove those records.
3301 -- Bug# 3446419
3302 -- Also pass in a parameter indicating if the RCV TM was called or not.
3303 cleanup_ATF
3304 (p_txn_header_id => p_txn_header_id,
3305 p_lpn_context => p_lpn_context,
3306 p_lpn_id => p_lpn_id,
3307 p_organization_id => p_organization_id,
3308 p_rcv_tm_called => l_rcv_tm_called,
3309 x_return_status => l_return_status,
3310 x_msg_count => l_msg_count,
3311 x_msg_data => l_msg_data);
3312
3313 -- Check to see if the cleanup_ATF API returned successfully
3314 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
3315 IF (l_debug = 1) THEN
3316 print_debug('Success returned from cleanup_ATF API');
3317 END IF;
3318 ELSE
3319 IF (l_debug = 1) THEN
3320 print_debug('Failure returned from cleanup_ATF API');
3321 END IF;
3322 -- Nothing we can do if the cleanup API's fail.
3323 END IF;
3324
3325 BEGIN
3326 ROLLBACK TO process_load_sp;
3327 EXCEPTION
3328 WHEN OTHERS THEN
3329 -- This implies that a commit was done which should only happen
3330 -- if the RCV TM was called and it errored out
3331 IF (l_debug = 1) THEN
3332 print_debug('Exception while rolling back to save point');
3333 END IF;
3334 END;
3335 x_return_status := fnd_api.g_ret_sts_unexp_error;
3336 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3337 p_data => x_msg_data);
3338 IF (l_debug = 1) THEN
3339 print_debug('Exiting process_load - Unexpected error: ' ||
3340 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
3341 END IF;
3342
3343 WHEN OTHERS THEN
3344 -- Call the cleanup_ATF procedure since the call to
3345 -- activate_operation_instance in process_load will insert records
3346 -- into WDT and commit them autonomously. A rollback will not
3347 -- remove those records.
3348 -- Bug# 3446419
3349 -- Also pass in a parameter indicating if the RCV TM was called or not.
3350 cleanup_ATF
3351 (p_txn_header_id => p_txn_header_id,
3352 p_lpn_context => p_lpn_context,
3353 p_lpn_id => p_lpn_id,
3354 p_organization_id => p_organization_id,
3355 p_rcv_tm_called => l_rcv_tm_called,
3356 x_return_status => l_return_status,
3357 x_msg_count => l_msg_count,
3358 x_msg_data => l_msg_data);
3359
3360 -- Check to see if the cleanup_ATF API returned successfully
3361 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
3362 IF (l_debug = 1) THEN
3363 print_debug('Success returned from cleanup_ATF API');
3364 END IF;
3365 ELSE
3366 IF (l_debug = 1) THEN
3367 print_debug('Failure returned from cleanup_ATF API');
3368 END IF;
3369 -- Nothing we can do if the cleanup API's fail.
3370 END IF;
3371
3372 BEGIN
3373 ROLLBACK TO process_load_sp;
3374 EXCEPTION
3375 WHEN OTHERS THEN
3376 -- This implies that a commit was done which should only happen
3377 -- if the RCV TM was called and it errored out
3378 IF (l_debug = 1) THEN
3379 print_debug('Exception while rolling back to save point');
3380 END IF;
3381 END;
3382 x_return_status := fnd_api.g_ret_sts_unexp_error;
3383 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3384 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3385 END IF;
3386 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3387 p_data => x_msg_data);
3388 IF (l_debug = 1) THEN
3389 print_debug('Exiting process_load - Others exception: ' ||
3390 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
3391 END IF;
3392
3393 END process_load;
3394
3395
3396 PROCEDURE unmark_serials
3397 (p_serial_txn_temp_id IN NUMBER ,
3398 p_organization_id IN NUMBER ,
3399 p_inventory_item_id IN NUMBER ,
3400 x_return_status OUT NOCOPY VARCHAR2 ,
3401 x_msg_count OUT NOCOPY NUMBER ,
3402 x_msg_data OUT NOCOPY VARCHAR2)
3403 IS
3404 l_api_name CONSTANT VARCHAR2(30) := 'unmark_serials';
3405 l_progress VARCHAR2(10);
3406 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3407
3408 BEGIN
3409 IF (l_debug = 1) THEN
3410 print_debug('***Calling unmark_serials with the following parameters***');
3411 print_debug('p_serial_txn_temp_id: => ' || p_serial_txn_temp_id);
3412 print_debug('p_organization_id: ====> ' || p_organization_id);
3413 print_debug('p_inventory_item_id: ==> ' || p_inventory_item_id);
3414 END IF;
3415
3416 -- Set the savepoint
3417 SAVEPOINT unmark_serials_sp;
3418 l_progress := '10';
3419
3420 -- Initialize message list to clear any existing messages
3421 fnd_msg_pub.initialize;
3422 l_progress := '20';
3423
3424 -- Set the return status to success
3425 x_return_status := fnd_api.g_ret_sts_success;
3426 l_progress := '30';
3427
3428 -- Reset the group mark ID for all marked serials
3429 IF (l_debug = 1) THEN
3430 print_debug('Clear the group mark ID for the marked serials');
3431 END IF;
3432 BEGIN
3433 UPDATE mtl_serial_numbers
3434 SET group_mark_id = NULL
3435 WHERE inventory_item_id = p_inventory_item_id
3436 AND current_organization_id = p_organization_id
3437 AND group_mark_id = p_serial_txn_temp_id
3438 AND EXISTS (SELECT 1
3439 FROM mtl_serial_numbers_temp
3440 WHERE transaction_temp_id = p_serial_txn_temp_id
3441 AND serial_number BETWEEN fm_serial_number AND
3442 to_serial_number);
3443 EXCEPTION
3444 WHEN OTHERS THEN
3445 IF (l_debug = 1) THEN
3446 print_debug('Exception while clearing group mark ID');
3447 END IF;
3448 END;
3449 l_progress := '40';
3450
3451 IF (l_debug = 1) THEN
3452 print_debug('Delete the temporary MSNT records');
3453 END IF;
3454 BEGIN
3455 DELETE FROM mtl_serial_numbers_temp
3456 WHERE transaction_temp_id = p_serial_txn_temp_id;
3457 EXCEPTION
3458 WHEN OTHERS THEN
3459 IF (l_debug = 1) THEN
3460 print_debug('Exception while deleting temporary MSNT records');
3461 END IF;
3462 END;
3463 l_progress := '50';
3464
3465 IF (l_debug = 1) THEN
3466 print_debug('***End of unmark_serials***');
3467 END IF;
3468
3469 EXCEPTION
3470 WHEN FND_API.G_EXC_ERROR THEN
3471 ROLLBACK TO unmark_serials_sp;
3472 x_return_status := fnd_api.g_ret_sts_error;
3473 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3474 p_data => x_msg_data);
3475 IF (l_debug = 1) THEN
3476 print_debug('Exiting unmark_serials - Execution error: ' ||
3477 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
3478 END IF;
3479
3480 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3481 ROLLBACK TO unmark_serials_sp;
3482 x_return_status := fnd_api.g_ret_sts_unexp_error;
3483 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3484 p_data => x_msg_data);
3485 IF (l_debug = 1) THEN
3486 print_debug('Exiting unmark_serials - Unexpected error: ' ||
3487 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
3488 END IF;
3489
3490 WHEN OTHERS THEN
3491 ROLLBACK TO unmark_serials_sp;
3492 x_return_status := fnd_api.g_ret_sts_unexp_error;
3493 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3494 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3495 END IF;
3496 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3497 p_data => x_msg_data);
3498 IF (l_debug = 1) THEN
3499 print_debug('Exiting unmark_serials - Others exception: ' ||
3500 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
3501 END IF;
3502
3503 END unmark_serials;
3504
3505
3506 PROCEDURE cleanup_ATF
3507 (p_txn_header_id IN NUMBER ,
3508 p_lpn_context IN NUMBER ,
3509 p_lpn_id IN NUMBER ,
3510 p_organization_id IN NUMBER ,
3511 p_rcv_tm_called IN BOOLEAN ,
3512 x_return_status OUT NOCOPY VARCHAR2 ,
3513 x_msg_count OUT NOCOPY NUMBER ,
3514 x_msg_data OUT NOCOPY VARCHAR2)
3515 IS
3516 l_api_name CONSTANT VARCHAR2(30) := 'cleanup_ATF';
3517 l_progress VARCHAR2(10);
3518 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3519 l_mmtt_temp_id NUMBER;
3520 l_error_code NUMBER;
3521 -- This cursor will get all of the MMTT suggestionss associated with the lpn_id passed.
3522 -- Cleanup should not be called if there are no WDT records.
3523 CURSOR mmtt_suggestions_cursor IS
3524 SELECT mmtt.transaction_temp_id
3525 FROM mtl_material_transactions_temp mmtt,
3526 mtl_txn_request_lines mtrl,
3527 wms_dispatched_tasks wdt
3528 WHERE mmtt.organization_id = p_organization_id
3529 AND mmtt.transaction_temp_id = wdt.transaction_temp_id
3530 AND wdt.task_type = 2
3531 AND wdt.organization_id = p_organization_id
3532 AND wdt.move_order_line_id = mtrl.line_id
3533 AND mtrl.line_id = mmtt.move_order_line_id
3534 AND mtrl.line_status = 7
3535 AND mtrl.organization_id = p_organization_id
3536 AND mtrl.lpn_id = p_lpn_id;
3537
3538 BEGIN
3539 IF (l_debug = 1) THEN
3540 print_debug('***Calling cleanup_ATF with the following parameters***');
3541 print_debug('p_txn_header_id: ===> ' || p_txn_header_id);
3542 print_debug('p_lpn_context: =====> ' || p_lpn_context);
3543 print_debug('p_lpn_id: ==========> ' || p_lpn_id);
3544 print_debug('p_organization_id: => ' || p_organization_id);
3545 END IF;
3546
3547 -- Set the savepoint
3548 SAVEPOINT cleanup_ATF_sp;
3549 l_progress := '10';
3550
3551 -- Initialize message list to clear any existing messages
3552 fnd_msg_pub.initialize;
3553 l_progress := '20';
3554
3555 -- Set the return status to success
3556 x_return_status := fnd_api.g_ret_sts_success;
3557 l_progress := '30';
3558
3559 -- Get all of the MMTT suggestions created so we can
3560 -- cleanup the operation instance for it. Note that for the
3561 -- non-receiving case, the only thing that is not cleaned up
3562 -- properly is the insertion of WDT records. Everything else is
3563 -- rolled back for the non-receiving case since no commit is done.
3564 -- However the autonomous commit done when calling
3565 -- activate_operation_instance prevents us from rolling that change back.
3566 -- In the receiving case, we will call the ATF runtime API
3567 -- cleanup_operation_instance to cleanup the data since we are unable
3568 -- to perform a rollback.
3569 OPEN mmtt_suggestions_cursor;
3570 LOOP
3571 FETCH mmtt_suggestions_cursor INTO l_mmtt_temp_id;
3572 EXIT WHEN mmtt_suggestions_cursor%NOTFOUND;
3573
3574 IF (p_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_RCV) THEN
3575 -- Receiving case so call cleanup_operation_instance
3576 IF (l_debug = 1) THEN
3577 print_debug('Call cleanup_operation_instance for MMTT: ' || l_mmtt_temp_id);
3578 END IF;
3579 l_progress := '40';
3580
3581 wms_atf_runtime_pub_apis.cleanup_operation_instance
3582 (p_source_task_id => l_mmtt_temp_id,
3583 p_activity_type_id => WMS_GLOBALS.G_OP_ACTIVITY_INBOUND,
3584 x_return_status => x_return_status,
3585 x_msg_data => x_msg_data,
3586 x_msg_count => x_msg_count,
3587 x_error_code => l_error_code);
3588
3589 IF (l_debug = 1) THEN
3590 print_debug('Finished calling the cleanup_operation_instance API');
3591 END IF;
3592 l_progress := '50';
3593
3594 -- Check to see if the cleanup_operation_instance API returned successfully
3595 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
3596 IF (l_debug = 1) THEN
3597 print_debug('Success returned from cleanup_operation_instance API');
3598 END IF;
3599 ELSE
3600 IF (l_debug = 1) THEN
3601 print_debug('Failure returned from cleanup_operation_instance API');
3602 print_debug('Error code: ' || l_error_code);
3603 END IF;
3604 RAISE FND_API.G_EXC_ERROR;
3605 END IF;
3606 ELSE
3607 -- Non-receiving case so call delete_dispatched_task
3608 IF (l_debug = 1) THEN
3609 print_debug('Call delete_dispatched_task for MMTT: ' || l_mmtt_temp_id);
3610 END IF;
3611 l_progress := '60';
3612
3613 wms_op_runtime_pvt_apis.delete_dispatched_task
3614 (p_source_task_id => l_mmtt_temp_id,
3615 p_wms_task_type => WMS_GLOBALS.G_WMS_TASK_TYPE_PUTAWAY,
3616 x_return_status => x_return_status,
3617 x_msg_count => x_msg_count,
3618 x_msg_data => x_msg_data);
3619
3620 IF (l_debug = 1) THEN
3621 print_debug('Finished calling the delete_dispatched_task API');
3622 END IF;
3623 l_progress := '70';
3624
3625 -- Check to see if the delete_dispatched_task API returned successfully
3626 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
3627 IF (l_debug = 1) THEN
3628 print_debug('Success returned from delete_dispatched_task API');
3629 END IF;
3630 ELSE
3631 IF (l_debug = 1) THEN
3632 print_debug('Failure returned from delete_dispatched_task API');
3633 END IF;
3634 RAISE FND_API.G_EXC_ERROR;
3635 END IF;
3636 END IF;
3637
3638 END LOOP;
3639 CLOSE mmtt_suggestions_cursor;
3640 IF (l_debug = 1) THEN
3641 print_debug('Closed the MMTT suggestions cursor');
3642 END IF;
3643 l_progress := '80';
3644
3645 -- For the receiving case, we need to commit the changes done in the
3646 -- call to cleanup_operation_instance. Since we do not rely on a
3647 -- rollback to clean up data for receiving, a commit should be okay.
3648 -- A commit is done already prior to calling the receiving TM. Thus we
3649 -- cannot rely on rollbacks since all savepoints have been deleted.
3650 -- Bug# 3446419
3651 -- Perform the commit only if the RCV TM was called and it errored out
3652 -- there. In that case, we can assume that a commit was already done.
3653 -- We will need to commit here for the cleanup changes to be saved.
3654 -- Otherwise if there was an error earlier, like in the call to
3655 -- wms_rcv_pup_pvt.pack_unpack_split, we can still rely on the rollback
3656 -- to clean up the data. There would be no need to commit here.
3657 IF (p_lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_RCV AND p_rcv_tm_called) THEN
3658 COMMIT;
3659 END IF;
3660 l_progress := '90';
3661
3662 IF (l_debug = 1) THEN
3663 print_debug('***End of cleanup_ATF***');
3664 END IF;
3665
3666 EXCEPTION
3667 WHEN FND_API.G_EXC_ERROR THEN
3668 ROLLBACK TO cleanup_ATF_sp;
3669 x_return_status := fnd_api.g_ret_sts_error;
3670 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3671 p_data => x_msg_data);
3672 IF (l_debug = 1) THEN
3673 print_debug('Exiting cleanup_ATF - Execution error: ' ||
3674 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
3675 END IF;
3676
3677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3678 ROLLBACK TO cleanup_ATF_sp;
3679 x_return_status := fnd_api.g_ret_sts_unexp_error;
3680 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3681 p_data => x_msg_data);
3682 IF (l_debug = 1) THEN
3683 print_debug('Exiting cleanup_ATF - Unexpected error: ' ||
3684 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
3685 END IF;
3686
3687 WHEN OTHERS THEN
3688 ROLLBACK TO cleanup_ATF_sp;
3689 x_return_status := fnd_api.g_ret_sts_unexp_error;
3690 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3691 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3692 END IF;
3693 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3694 p_data => x_msg_data);
3695 IF (l_debug = 1) THEN
3696 print_debug('Exiting cleanup_ATF - Others exception: ' ||
3697 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
3698 END IF;
3699
3700 END cleanup_ATF;
3701
3702
3703 END wms_item_load;
3704
[Home] [Help]