1 PACKAGE BODY WMS_PUTAWAY_SUGGESTIONS AS
2 /* $Header: WMSPRGEB.pls 120.16.12020000.3 2012/08/10 10:41:12 pyerrams ship $ */
3
4 -- Global constant holding the package name
5 g_pkg_name constant varchar2(50) := 'WMS_PUTAWAY_SUGGESTIONS';
6
7 /*===========================================================================+
8 | Procedure: |
9 | conc_pre_generate |
10 | |
11 | Description: |
12 | This is a wrapper API that calls WMS_PUTAWAY_SUGGESTIONS.PRE_GENERATE |
13 | API. It has the necessary parameters required for being a concurrent |
14 | program. |
15 | |
16 | Input Parameters: |
17 | p_organization_id |
18 | Mandatory parameter. Organization where putaway suggestions have |
19 | to be pre-generated. |
20 | p_lpn_id |
21 | Optional parameter. LPN for which suggestions have to be created. |
22 | |
23 | Output Parameters: |
24 | x_errorbuf |
25 | Standard Concurrent program parameter - Holds error message. |
26 | x_retcode |
27 | Standard Concurrent program parameter - Normal, Warning, Error. |
28 | |
29 | API Used: |
30 | PRE_GENERATE API to generate the putaway suggestions. |
31 +===========================================================================*/
32
33 PROCEDURE print_debug(p_err_msg VARCHAR2,
34 p_level NUMBER := 4)
35 IS
36 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
37 BEGIN
38 inv_mobile_helper_functions.tracelog
39 (p_err_msg => p_err_msg,
40 p_module => 'wms_putaway_suggestions',
41 p_level => p_level);
42
43 END;
44
45 PROCEDURE conc_pre_generate(
46 x_errorbuf OUT NOCOPY VARCHAR2,
47 x_retcode OUT NOCOPY VARCHAR2,
48 p_organization_id IN NUMBER,
49 p_lpn_id IN NUMBER,
50 p_is_srs_call IN VARCHAR2 DEFAULT NULL
51 )
52 IS
53
54 --Variables
55
56 l_return_status VARCHAR2(1);
57 l_msg_count NUMBER;
58 l_msg_data VARCHAR2(2000);
59 l_msg VARCHAR2(200);
60 l_conc_status BOOLEAN;
61 l_lpn_line_error_tbl lpn_line_error_tbl;
62
63 l_partial_success VARCHAR2(1);
64 --partial_success is a flag used to denote whether suggestions have been
65 --generated successfully for all LPNs.
66 --l_partial_success = 'N', if all LPN have been succesfully processed.
67 -- 'Y', if one or more LPN have errored out.
68
69
70 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
71 BEGIN
72 IF (l_debug = 1) THEN
73 print_debug('Entered WMS_PUTAWAY_SUGGESTIONS.conc_pre_generate');
74 print_debug('Org:'||p_organization_id);
75 print_debug('LPN:'||p_lpn_id);
76 print_debug('p_is_srs_call: '||p_is_srs_call);
77 END IF;
78
79
80 WMS_PUTAWAY_SUGGESTIONS.pre_generate
81 (x_return_status => l_return_status,
82 x_msg_count => l_msg_count,
83 x_msg_data => l_msg_data,
84 x_partial_success => l_partial_success,
85 x_lpn_line_error_tbl => l_lpn_line_error_tbl,
86 p_from_conc_pgm => 'Y',
87 p_commit => 'Y',
88 p_organization_id => p_organization_id,
89 p_lpn_id => p_lpn_id,
90 p_is_srs_call => Nvl(p_is_srs_call, 'Y')
91 );
92
93
94 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
95 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
96 RAISE fnd_api.g_exc_error;
97 ELSE
98 RAISE fnd_api.g_exc_unexpected_error;
99 END IF;
100 ELSE
101 IF (l_partial_success = 'Y') THEN
102 fnd_message.set_name('WMS', 'WMS_LPN_PROC_WARN');
103 fnd_msg_pub.add;
104 print_message();
105 l_msg := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
106 l_conc_status := fnd_concurrent.set_completion_status('WARNING',l_msg);
107 x_retcode := RETCODE_WARNING;
108 x_errorbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
109 ELSE
110 print_message();
111 l_conc_status := fnd_concurrent.set_completion_status('NORMAL','NORMAL');
112 x_retcode := RETCODE_SUCCESS;
113 x_errorbuf := NULL;
114 END IF;
115 END IF;
116
117
118 EXCEPTION
119 WHEN fnd_api.g_exc_error THEN
120
121 print_message();
122 l_conc_status := fnd_concurrent.set_completion_status('ERROR','ERROR');
123 x_retcode := RETCODE_ERROR;
124 x_errorbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
125
126 WHEN fnd_api.g_exc_unexpected_error THEN
127
128 print_message();
129 l_conc_status := fnd_concurrent.set_completion_status('ERROR','ERROR');
130 x_retcode := RETCODE_ERROR;
131 x_errorbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
132
133 WHEN OTHERS THEN
134
135 print_message();
136 l_conc_status := fnd_concurrent.set_completion_status('ERROR','ERROR');
137 x_retcode := RETCODE_ERROR;
138 x_errorbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
139
140 END conc_pre_generate;
141
142
143 -- Procedure called by receiving. The procedure submits a request to start
144 --the concurrent program
145 PROCEDURE start_pregenerate_program
146 (p_org_id IN NUMBER,
147 p_lpn_id IN NUMBER,
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2
151 )
152 IS
153
154 l_req_id NUMBER;
155 l_wms_install BOOLEAN := FALSE;
156 l_crossdock_flag NUMBER;
157 l_pregen_putaway_tasks_flag NUMBER;
158 l_plan_xdock_flag VARCHAR2(1);
159
160 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
161 BEGIN
162
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164 IF (l_debug = 1) THEN
165 print_debug('Entered start_pregenerate_program');
166 print_debug('Org:'||p_org_id);
167 print_debug('LPN:'||p_lpn_id);
168 END IF;
169
170 /*
171 l_wms_install := WMS_INSTALL.check_install
172 (x_return_status => x_return_status,
173 x_msg_count => x_msg_count,
174 x_msg_data => x_msg_data,
175 p_organization_id => p_org_id
176 );
177
178 IF (NOT l_wms_install) THEN
179 fnd_message.set_name('WMS', 'WMS_NOT_INSTALLED');
180 fnd_msg_pub.add;
181 RAISE fnd_api.g_exc_error;
182 END IF;
183 */
184
185 IF (p_org_id is null) THEN
186 fnd_message.set_name('INV', 'INV_ORG_REQUIRED');
187 fnd_msg_pub.add;
188 RAISE fnd_api.g_exc_error;
189 END IF;
190
191 --Added for Bug 13789232
192 BEGIN
193
194 SELECT crossdock_flag , pregen_putaway_tasks_flag
195 INTO l_crossdock_flag , l_pregen_putaway_tasks_flag
196 FROM mtl_parameters
197 WHERE organization_id = p_org_id;
198
199 IF (l_crossdock_flag = 2 AND l_pregen_putaway_tasks_flag = 2) THEN -- 2 disabled
200 SELECT 'Y' INTO l_plan_xdock_flag
201 FROM mtl_reservations
202 WHERE lpn_id=p_lpn_id
203 AND organization_id=p_org_id
204 AND external_source_code='XDOCK'
205 AND EXISTS (SELECT 1 FROM mtl_txn_request_lines
206 WHERE lpn_id=p_lpn_id
207 AND organization_id=p_org_id
208 AND line_status=7
209 AND ROWNUM=1
210 )
211 AND ROWNUM=1;
212 END IF ;
213
214 EXCEPTION
215 WHEN NO_DATA_FOUND THEN
216 l_plan_xdock_flag:='N';
217 END ;
218
219
220 IF (l_crossdock_flag <> 2 OR l_pregen_putaway_tasks_flag <> 2) OR l_plan_xdock_flag='Y' THEN
221 --End of bug 13789232
222
223
224 l_req_id := fnd_request.submit_request
225 (application => 'WMS',
226 program => 'WMSPRPUT',
227 description => 'Pregenerate putaway suggestions',
228 argument1 => TO_CHAR(p_org_id),
229 argument2 => TO_CHAR(p_lpn_id),
230 argument3 => 'N');
231
232 IF (l_debug = 1) THEN
233 print_debug('After calling the suggestions:'||l_req_id);
234 END IF;
235 IF l_req_id = 0 THEN
236 IF (l_debug = 1) THEN
237 print_debug('error calling the conc. request');
238 END IF;
239 RAISE fnd_api.g_exc_error;
240 END IF;
241 END IF; -- Added for bug 13789232
242
243 x_return_status := FND_API.G_RET_STS_SUCCESS;
244
245 EXCEPTION
246 WHEN FND_API.G_EXC_ERROR THEN
247 x_return_status := FND_API.G_RET_STS_ERROR;
248 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
249
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
252 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
253
254 WHEN OTHERS THEN
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
256 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
257
258 END start_pregenerate_program;
259
260
261
262
263 /*===========================================================================+
264 | Procedure: |
265 | pre_generate |
266 | |
267 | Description: |
268 | This API polls receipts table for receipts yet to be put away and |
269 | create suggestions for their put away. |
270 | |
271 | Input Parameters: |
272 | p_from_conc_pgm |
273 | Mandatory parameter. Default 'Y'. Indicates if the caller is |
274 | concurrent program or otherwise. This is needed to know if |
275 | messages have to be logged in a file. |
276 | p_commit |
277 | Mandatory parameter. Default 'Y'. Indicates if commit has to |
278 | happen. |
279 | p_organization_id |
280 | Mandatory parameter. Organization where putaway suggestions have |
281 | to be pre-generated. |
282 | p_lpn_id |
283 | Optional parameter. LPN for which suggestions have to be created. |
284 | |
285 | Output Parameters: |
286 | x_return_status |
287 | Standard API return status - Success, Error, Unexpected Error. |
288 | x_msg_count |
289 | Number of messages in the message queue |
290 | x_msg_data |
291 | If the number of messages in the message queue is one, |
292 | x_msg_data has the message text. |
293 | x_partial_success |
294 | Indicates if one or more lpns errored out. |
295 | x_lpn_line_error_tbl |
296 | Plsql table to hold the errored out lpn_ids and line_ids. |
297 | |
298 | Tables Used: |
299 | 1. mtl_txn_request_headers |
300 | 2. mtl_txn_request_lines |
301 +===========================================================================*/
302 --
303 PROCEDURE pre_generate(
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2,
307 x_partial_success OUT NOCOPY VARCHAR2,
308 x_lpn_line_error_tbl OUT NOCOPY lpn_line_error_tbl,
309 p_from_conc_pgm IN VARCHAR2,
310 p_commit IN VARCHAR2,
311 p_organization_id IN NUMBER,
312 p_lpn_id IN NUMBER,
313 p_is_srs_call IN VARCHAR2 DEFAULT NULL
314 )
315 IS
316
317 --Variables
318 l_wms_install BOOLEAN := FALSE;
319 l_error BOOLEAN := FALSE;
320 l_entry_loop BOOLEAN := FALSE;
321 l_from_conc_pgm VARCHAR2(1) := p_from_conc_pgm;
322 l_partial_success VARCHAR2(1) := 'N';
323 l_tbl_index NUMBER := 1;
324 l_cnt_success NUMBER := 0;
325 l_cnt_failed NUMBER := 0;
326
327 l_mtl_reservation inv_reservation_global.mtl_reservation_tbl_type;
328 l_return_status VARCHAR2(1);
329 l_mol_line_id NUMBER;
330 l_mol_line_no NUMBER;
331 l_lpn_id NUMBER;
332 l_lpn_no VARCHAR2(30);
333 l_msg_count NUMBER;
334 l_msg_data VARCHAR2(2000);
335 l_label_status VARCHAR2(300);
336 l_temp_id NUMBER;
337 -- Following 3 variables were added for Bug# 4178478
338 -- l_lpn_table l_lpn_table_type; -- Commented out as part of fix for bug # 4964866
339 l_temp_lpn_id NUMBER;
340 l_print_lpn_label VARCHAR2(5);
341 l_organization_id NUMBER; -- Added for bug # 4964866
342 l_subinventory_code VARCHAR2(10); -- Added for bug # 4964866
343 l_locator_id NUMBER; -- Added for bug # 4964866
344 l_mmtt_table mmtt_table_type; -- Added for bug # 4964866
345
346 l_discrepancy NUMBER; -- Bug 7460112
347
348
349 -- Following variables added in ATF_J
350
351 l_backorder_delivery_detail_id NUMBER;
352 l_crossdock_type NUMBER;
353 l_wip_supply_type NUMBER;
354 l_lpn_context NUMBER;
355 l_process_flag_count NUMBER := 0;
356 l_inspect_req_rej_count NUMBER := 0;
357 l_cross_dock_flag NUMBER;
358 l_ret_crossdock NUMBER := 1;
359 l_ATF_error_code NUMBER;
360 l_operation_plan_id NUMBER;
361 l_mmtt_record_count NUMBER;
362 l_pregen_putaway_tasks_flag NUMBER;
363 l_txn_source_id NUMBER;
364 l_insp_status NUMBER; --Added for Bug 8373292
365 label_mmtt_record_count NUMBER;
366 record_locked EXCEPTION;
367 PRAGMA EXCEPTION_INIT (record_locked, -54);
368
369 -- End variables added in ATF_J
370
371 --Cursors
372 /*
373 ATF_J:
374 comment out following definition since its not referred in the code
375
376 CURSOR mol_cursor IS
377 SELECT mol.line_id, mol.line_number
378 FROM mtl_txn_request_headers moh,
379 mtl_txn_request_lines mol
380 WHERE mol.header_id = moh.header_id
381 AND moh.move_order_type = INV_GLOBALS.g_move_order_put_away
382 AND mol.organization_id = p_organization_id
383 AND mol.lpn_id is not null
384 AND mol.lpn_id = NVL(p_lpn_id, mol.lpn_id)
385 AND (NVL(mol.quantity_delivered,0) + NVL(mol.quantity_detailed,0)) <
386 NVL(mol.quantity,0)
387 FOR UPDATE;
388 */
389
390 /* for bug 6918744 */
391 CURSOR mol_lpn_cursor IS
392 SELECT
393 distinct mol.lpn_id,
394 lpn.license_plate_number,
395 lpn.lpn_context, --- for bug 5175569
396 mol.line_id,
397 mol.txn_source_id --- for bug 7190056
398 FROM
399 mtl_txn_request_headers moh,
400 mtl_txn_request_lines mol,
401 wms_license_plate_numbers lpn
402 WHERE mol.header_id = moh.header_id
403 AND moh.move_order_type = INV_GLOBALS.g_move_order_put_away
404 AND mol.organization_id = p_organization_id
405 AND mol.lpn_id is not null
406 AND mol.lpn_id = nvl(p_lpn_id, mol.lpn_id) --BUG3497572 p_lpn_id is an optional argument for concurrent request
407 AND mol.lpn_id = lpn.lpn_id
408 AND (NVL(mol.quantity_delivered,0) + NVL(mol.quantity_detailed,0)) <
409 NVL(mol.quantity,0)
410 ORDER BY mol.txn_source_id ASC; --bug 6189438,6160359,6716184,7190056
411 /* for bug 6918744 */
412
413
414
415 --ATF_J
416 -- select operation_plan_ID from MMTT
417 -- This makes this file depending on patchset I and above.
418 -- We need to at least break the dual maintanance between H and I,
419 -- but can keep the dual maintanance between I and J
420
421 CURSOR mmtt_cursor (l_line_id IN NUMBER) IS
422 SELECT
423 nvl(mmtt.LPN_ID, mmtt.CONTENT_LPN_ID),
424 mmtt.organization_id, -- Added for bug # 4964866
425 mmtt.subinventory_code, -- Added for bug # 4964866
426 mmtt.locator_id, -- Added for bug # 4964866
427 mmtt.transaction_temp_id,
428 mmtt.operation_plan_id, -- added for ATF_J
429 mol.backorder_delivery_detail_id, -- added for ATF_J
430 mol.crossdock_type -- added for ATF_J
431 FROM
432 mtl_material_transactions_temp mmtt,
433 mtl_txn_request_lines mol
434 WHERE mmtt.move_order_line_id = l_line_id
435 AND mol.line_id = l_line_id;
436
437
438 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
439 BEGIN
440 x_return_status := fnd_api.g_ret_sts_success;
441
442 IF (l_debug = 1) THEN
443 print_debug('pre_generate: Starting pre_generate API');
444 print_debug('p_from_conc_pgm => ' || p_from_conc_pgm);
445 print_debug('p_commit => ' || p_commit);
446 print_debug('p_organization_id => '|| p_organization_id);
447 print_debug('p_lpn_id => '||p_lpn_id);
448 print_debug('p_is_srs_call => '||p_is_srs_call);
449 END IF;
450
451
452
453 IF (p_organization_id is null) THEN
454 fnd_message.set_name('INV', 'INV_ORG_REQUIRED');
455 fnd_msg_pub.add;
456 IF (l_debug = 1) THEN
457 print_debug('pre_generate: p_organization_ID is null');
458 END IF;
459
460 RAISE fnd_api.g_exc_error;
461 END IF;
462
463 --This savepoint is used to rollback if p_commit = 'N' and an error
464 --ocurrs.
465 SAVEPOINT suggestions_start_sp;
466
467 -- ATF_J:
468 -- Following validation is added along with ATF project in patchset J. But it
469 -- is general validation that should be performed for every patchset. It does
470 -- not pertain to ATF functionality.
471
472
473 -- 1. Do not pre-generate if LPN context is not WIP or Receiving
474 -- 2. Do not pre-generate if LPN is locked (either by a different pre-generate process, or putaway load page).
475 IF p_lpn_id IS NOT NULL THEN --BUG3497572 p_lpn_id is an optional when pre_generate called from concurrent request
476 BEGIN
477 SELECT lpn_context
478 INTO l_lpn_context
479 FROM wms_license_plate_numbers
480 WHERE lpn_id = p_lpn_id
481 AND organization_id = p_organization_id
482 FOR UPDATE nowait;
483
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN
486 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
487 fnd_msg_pub.add;
488
489 IF (l_debug = 1) THEN
490 print_debug('pre_generate: p_lpn_id '||p_lpn_id||' does not exist. ' );
491 END IF;
492
493 RAISE fnd_api.g_exc_error;
494
495 WHEN record_locked THEN
496 fnd_message.set_name('WMS', 'WMS_LPN_UNAVAIL');
497 fnd_msg_pub.ADD;
498
499 IF (l_debug = 1) THEN
500 print_debug('pre_generate: LPN not available. It is locked by someone else, either by different pre-generate process, or putaway load page');
501 END IF;
502
503 RAISE fnd_api.g_exc_error;
504
505 END;
506
507 IF (l_debug = 1) THEN
508 print_debug('pre_generate: l_lpn_context = '||l_lpn_context);
509 END IF;
510
511 IF l_lpn_context NOT IN (2, 3) THEN
512 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN_CONTEXT');
513 fnd_msg_pub.add;
514
515 IF (l_debug = 1) THEN
516 print_debug('pre_generate: l_lpn_context is not WIP or Receiving, do not pre-generate.' );
517 END IF;
518
519 RAISE fnd_api.g_exc_error;
520 END IF;
521
522 -- Do not pre-generate if any move order line in this LPN with wms_process_flag = 2
523
524
525 BEGIN
526 SELECT 1
527 INTO l_process_flag_count
528 FROM DUAL WHERE exists
529 (SELECT 1
530 FROM mtl_txn_request_lines
531 WHERE lpn_id = p_lpn_id
532 AND line_status <> inv_globals.g_to_status_closed /* 3867448 */
533 AND Nvl(wms_process_flag, 1) = 2);
534 EXCEPTION
535 WHEN no_data_found THEN
536 l_process_flag_count := 0;
537
538 END ;
539
540 IF l_process_flag_count > 0 THEN
541 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
542 fnd_msg_pub.add;
543
544 IF (l_debug = 1) THEN
545 print_debug('pre_generate: move order wms_process_flag indicates allocation is not yet allowed.');
546 END IF;
547
548 RAISE fnd_api.g_exc_error;
549 END IF;
550
551 -- End of validation added in ATF_J
552 END IF; --BUG3497572 p_lpn_id is an optional when pre_generate called from concurrent request
553
554
555 IF (l_debug = 1) THEN
556 print_debug('Before calling WMS_Cross_Dock_Pvt.crossdock');
557 print_debug('p_organization_id = '||p_organization_id);
558 print_debug('p_lpn_id = '||p_lpn_id);
559 print_debug('p_move_order_line_id = '||l_mol_line_id);
560 END IF;
561
562 --{{
563 -- Test cases related to pre_generate
564 -- 1. pre_generate should always be called after receipt, but not necessarily generate task.
565 -- 2. If check crossdock indicates crossdock occurs, or org level pregeneration flag
566 -- is set, or the call is from SRS, then need to pregenerate task.
567 -- 3. In otherwords, if no crossdock occurs, and call is from PL/SQL api, and org level
568 -- pregenerate flag is off, do not pregenerate task.
569 --}}
570
571 WMS_Cross_Dock_Pvt.crossdock
572 (p_org_id => p_organization_id,
573 p_lpn => p_lpn_id,
574 p_move_order_line_id => l_mol_line_id,
575 x_ret => l_ret_crossdock,
576 x_return_status => l_return_status,
577 x_msg_count => l_msg_count,
578 x_msg_data => l_msg_data);
579
580 IF (l_debug = 1) THEN
581 print_debug('After calling WMS_Cross_Dock_Pvt.crossdock');
582 print_debug('l_ret_crossdock = '||l_ret_crossdock);
583 print_debug('l_return_status = '||l_return_status);
584 print_debug('l_msg_count = '||l_msg_count);
585 print_debug('l_msg_data = '||l_msg_data);
586 END IF;
587
588 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
589 IF (l_debug = 1) THEN
590 print_debug('pre_generate: Success returned from WMS_Cross_Dock_Pvt.crossdock API');
591 END IF;
592 ELSE
593 IF (l_debug = 1) THEN
594 print_debug('pre_generate: Failure returned from WMS_Cross_Dock_Pvt.crossdock API');
595 END IF;
596 RAISE fnd_api.g_exc_error;
597
598 END IF; -- (l_return_status = fnd_api.g_ret_sts_success)
599
600 IF l_ret_crossdock <> 0 THEN
601 IF (l_debug = 1) THEN
602 print_debug('pre_generate:Crossdock did not happen, get org level flag');
603 END IF;
604
605 SELECT pregen_putaway_tasks_flag
606 INTO l_pregen_putaway_tasks_flag
607 FROM mtl_parameters
608 WHERE organization_id = p_organization_id;
609
610 IF (l_debug = 1) THEN
611 print_debug('l_pregen_putaway_tasks_flag = '||l_pregen_putaway_tasks_flag);
612 END IF;
613
614 IF l_pregen_putaway_tasks_flag <> 1 AND Nvl(p_is_srs_call, 'Y') = 'N' THEN
615 IF (l_debug = 1) THEN
616 print_debug('Return and do not pregenerate since not called from SRS, AND org level flag not set, AND no crossdock.');
617 END IF;
618 RETURN;
619 END IF;
620 END IF; -- (l_ret_crossdock=1)
621
622
623
624 --Each line of all the pending lpns are processed int the foll. logic.
625 --If a line of one of the lpns errors out then the entire lpn is not
626 --processed. A savepoint is defined at the start of each lpn. Commit
627 --is made after every lpn is processed.
628 OPEN mol_lpn_cursor;
629 label_mmtt_record_count := 0;
630 LOOP
631 FETCH mol_lpn_cursor
632 INTO
633 l_lpn_id,
634 l_lpn_no,
635 l_lpn_context, -- for bug 5175569
636 l_mol_line_id,
637 l_txn_source_id; -- for bug 7190056
638 EXIT WHEN mol_lpn_cursor%notfound;
639
640 IF (l_debug = 1) THEN
641 print_debug('l_lpn_id: ' || l_lpn_id);
642 END IF;
643
644 SAVEPOINT suggestions_lpn_sp;
645 l_entry_loop := TRUE;
646
647 l_error := FALSE;
648
649 IF (l_debug = 1) THEN
650 print_debug('l_mol_line_id: ' || l_mol_line_id);
651 END IF;
652
653
654 --Calling Rules Engine.
655 INV_PPEngine_PVT.create_suggestions
656 (p_api_version => 1.0,
657 p_init_msg_list => FND_API.G_TRUE,
658 p_commit => FND_API.G_FALSE,
659 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
660 x_return_status => l_return_status,
661 x_msg_count => l_msg_count,
662 x_msg_data => l_msg_data,
663 p_transaction_temp_id => l_mol_line_id,
664 p_reservations => l_mtl_reservation,
665 p_suggest_serial => 'N'
666 );
667
668 IF (l_debug = 1) THEN
669 print_debug('l_return_status => ' || l_return_status);
670 END IF;
671
672 -- if rules engine fails to create suggestion, the idea is to rollback all changes to
673 -- the MOL that might have been set by crossdock. However,
674 -- l_return_status is always success even though the rules engine cannot create suggestion
675 -- thus, this if statement will never be true until the rules engine is fixed
676 -- for now, a work around is to check the mmtt cursor record count. if it is 0, then
677 -- we raise the fnd_api.g_exc_error
678 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
679 l_error := TRUE;
680 l_partial_success := 'Y';
681
682 --Log Errored lpn_id and line_id with the error msg in msg file.
683 fnd_message.set_name('WMS', 'WMS_LPN_LINE_ERROR');
684 fnd_message.set_token('lpn_no', l_lpn_no);
685 fnd_message.set_token('lpn_id', to_char(l_lpn_id));
686 --fnd_message.set_token('line_no',to_char(l_mol_line_no));
687 fnd_message.set_token('line_id',to_char(l_mol_line_id));
688 fnd_msg_pub.add;
689 x_lpn_line_error_tbl(l_tbl_index).lpn_id := l_lpn_id;
690 x_lpn_line_error_tbl(l_tbl_index).line_id := l_mol_line_id;
691 l_tbl_index := l_tbl_index+1;
692
693 IF (l_from_conc_pgm = 'Y') THEN
694 print_message();
695 END IF;
696 --RAISE fnd_api.g_exc_error;
697 --11685037 move handling of problematic lpn to lpn_errored, also print stuff for concurrent program there instead
698
699 goto lpn_errored;
700 ELSE
701
702 -- Log Success in msg file.
703 fnd_message.set_name('WMS', 'WMS_PUTAWAY_SUCCESS');
704 fnd_message.set_token('lpn_no', l_lpn_no);
705 fnd_message.set_token('lpn_id', to_char(l_lpn_id));
706 --fnd_message.set_token('line_no',to_char(l_mol_line_no));
707 fnd_message.set_token('line_id',to_char(l_mol_line_id));
708 fnd_msg_pub.add;
709
710 IF (l_from_conc_pgm = 'Y') THEN
711 print_message();
712 END IF;
713
714 END IF;
715
716 UPDATE mtl_txn_request_lines
717 SET
718 last_update_date = Sysdate,
719 quantity_detailed = (SELECT SUM(transaction_quantity)
720 FROM mtl_material_transactions_temp
721 WHERE move_order_line_id = l_mol_line_id),
722 secondary_quantity_detailed = (SELECT SUM(secondary_transaction_quantity) --bug 8217008
723 FROM mtl_material_transactions_temp
724 WHERE move_order_line_id = l_mol_line_id)
725 WHERE line_id = l_mol_line_id;
726
727 -- a workaround to problem described above (rules engine always return success status)
728 l_mmtt_record_count := 0;
729
730 OPEN mmtt_cursor(l_mol_line_id);
731 LOOP
732 FETCH mmtt_cursor INTO
733 l_temp_lpn_id,
734 l_organization_id, -- Added for bug # 4964866
735 l_subinventory_code, -- Added for bug # 4964866
736 l_locator_id, -- Added for bug # 4964866
737 l_temp_id,
738 l_operation_plan_id,
739 l_backorder_delivery_detail_id,
740 l_crossdock_type;
741 EXIT WHEN
742 mmtt_cursor%notfound;
743
744 l_mmtt_record_count := l_mmtt_record_count + 1; --bug8775458
745
746 l_print_lpn_label := 'Y';
747
748 FOR i IN 1..l_mmtt_table.count() LOOP
749 -- the following IF condition has been modified for the bug # 4964866
750 IF (l_mmtt_table(i).lpn_id = l_temp_lpn_id
751 AND l_mmtt_table(i).organization_id = l_organization_id
752 AND l_mmtt_table(i).subinventory_code = l_subinventory_code
753 AND l_mmtt_table(i).locator_id = l_locator_id)
754 AND l_backorder_delivery_detail_id IS NOT NULL AND l_mmtt_table(i).backorder_delivery_detail_id=l_backorder_delivery_detail_id THEN
755 l_print_lpn_label := 'N';
756 EXIT;
757 END IF;
758 END LOOP;
759
760 /*Bug 8373292 If Inspection is done only then print label*/
761 SELECT inspection_status
762 INTO l_insp_status
763 FROM mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
764 WHERE mtrl.line_id=mmtt.move_order_line_id
765 AND mmtt.transaction_temp_id=l_temp_id;
766
767 IF (l_debug = 1) THEN
768 print_debug('pre_generate: l_insp_status='||l_insp_status);
769 END IF;
770
771 IF (Nvl(l_insp_status, 2) = 1) THEN
772 l_print_lpn_label := 'N';
773
774 IF (l_debug = 1) THEN
775 print_debug('pre_generate: Do not print label for receive transaction if inspection is required');
776 END IF;
777 END IF;
778 /*Bug 8373292 If Inspection is done only then print label*/
779
780
781 IF(l_print_lpn_label = 'Y') THEN
782 inv_label.print_label_wrap(x_return_status => l_return_status
783 , x_msg_count => l_msg_count
784 , x_msg_data => l_msg_data
785 , x_label_status => l_label_status
786 , p_business_flow_code => 27
787 , p_transaction_id => l_temp_id);
788
789 /* Start of fix for bug # 4964866*/
790 --l_lpn_table(l_mmtt_record_count) := l_temp_lpn_id;
791
792 label_mmtt_record_count := label_mmtt_record_count + 1;
793 l_mmtt_table(label_mmtt_record_count).lpn_id := l_temp_lpn_id;
794 l_mmtt_table(label_mmtt_record_count).organization_id := l_organization_id;
795 l_mmtt_table(label_mmtt_record_count).subinventory_code := l_subinventory_code;
796 l_mmtt_table(label_mmtt_record_count).locator_id := l_locator_id;
797 l_mmtt_table(label_mmtt_record_count).backorder_delivery_detail_id := l_backorder_delivery_detail_id;
798
799 /* End of fix for bug # 4964866*/
800
801 END IF;
802
803 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
804
805 IF (l_debug = 1) THEN
806 print_debug('pre_generate: Label printing failed. Continue');
807 END IF;
808 END IF;
809
810 -- ATF_J:
811 -- Following two apis,
812 -- operation_plan_assignment and init_op_plan_instance,
813 -- are only called if customer is at patchset J or above.
814 -- Also, only need to call these APIs if current MMTT does not yet have
815 -- operation_plan_id stamped.
816
817
818 IF (l_debug = 1) THEN
819 print_debug('pre_generate: crdk_wip_info_table.count = '||wms_task_dispatch_put_away.crdk_wip_info_table.count );
820 END IF;
821
822 IF (l_debug = 1) THEN
823 print_debug('pre_generate: Current release is above J.');
824 END IF;
825
826
827 IF l_operation_plan_id IS NULL
828 AND l_lpn_context = 3 THEN -- LPN context resides in receiving
829 IF (l_debug = 1) THEN
830 print_debug(' operation_plan_id is null on MMTT and this is a receiving LPN, assign operation plan and initialize operation plan instance.');
831
832 --Following API assigns operation plan to an MMTT
833 print_debug('pre_generate: Before calling wms_rule_pvt.assign_operation_plan with following parameters: ');
834 print_debug('p_task_id = ' || l_temp_id);
835 print_debug('p_activity_type_id = ' || 1);
836 print_debug('p_organization_id = ' || p_organization_id);
837 END IF;
838 WMS_ATF_Util_APIs.assign_operation_plan
839 (
840 p_api_version => 1.0,
841 x_return_status => l_return_status,
842 x_msg_count => l_msg_count,
843 x_msg_data => l_msg_data,
844 p_task_id => l_temp_id,
845 p_activity_type_id => 1, -- Inbound
846 p_organization_id => p_organization_id
847 );
848
849 IF (l_debug = 1) THEN
850 print_debug('pre_generate: After calling wms_rule_pvt.assign_operation_plan');
851 print_debug('l_return_status = ' || l_return_status);
852 END IF;
853
854 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
855
856 IF (l_debug = 1) THEN
857 print_debug('pre_generate: wms_rule_pvt.assign_operation_plan failed.');
858 END IF;
859
860 --11685037 do not error out, just throw warning when we fail for a particular LPN, so subsequent LPN generation still occurs
861 --instead of throwing an error and stopping everything, just exit this inner loop mmtt_cursor, mark partial success, and continue with next iteration of outer mol_lpn_cursor
862 --set these variables so we automatically rollback to the savepoint properly
863 l_error := TRUE;
864 l_partial_success := 'Y';
865 exit;
866 --RAISE fnd_api.g_exc_error;
867
868 END IF; -- (l_return_status <> fnd_api.g_ret_sts_success)
869
870 IF (l_debug = 1) THEN
871 --Following API initializes the operation plan instance
872 print_debug('pre_generate: Before calling wms_op_runtime_pub_apis.init_op_plan_instance with following parameters: ');
873 print_debug('p_source_task_id = ' || l_temp_id);
874 print_debug('p_activity_id = ' || 1);
875 END IF;
876
877
878 wms_atf_runtime_pub_apis.init_op_plan_instance
879 (
880 x_return_status => l_return_status,
881 x_msg_data => l_msg_data,
882 x_msg_count => l_msg_count,
883 x_error_code => l_ATF_error_code,
884 p_source_task_id => l_temp_id,
885 p_activity_id => 1 -- Inbound
886 );
887
888 IF (l_debug = 1) THEN
889 print_debug('pre_generate: After calling wms_op_runtime_pub_apis.init_plan_instance');
890 print_debug('l_return_status = ' || l_return_status);
891 END IF;
892
893 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
894
895 IF (l_debug = 1) THEN
896 print_debug('pre_generate: wms_op_runtime_pub_apis.init_plan_instance failed.');
897 END IF;
898
899 --11685037 do not error out, just throw warning when we fail for a particular LPN, so subsequent LPN generation still occurs
900 --instead of throwing an error and stopping everything, just exit this inner loop mmtt_cursor, mark partial success, and continue with next iteration of outer mol_lpn_cursor
901 --set these variables so we automatically rollback to the savepoint properly
902 l_error := TRUE;
903 l_partial_success := 'Y';
904 exit;
905 --RAISE fnd_api.g_exc_error;
906
907 END IF; -- (l_return_status <> fnd_api.g_ret_sts_success)
908
909 END IF; -- (l_operation_plan_id IS NULL)
910
911 -- End new API calls added for ATF_J
912
913 END LOOP;
914 CLOSE mmtt_cursor;
915
916
917 IF (l_debug = 1) THEN
918 print_debug('l_mmtt_record_count = ' || l_mmtt_record_count);
919 END IF;
920
921 -- workaround to rules engine problem (always returning success status)
922 IF (l_mmtt_record_count = 0) THEN
923 l_error := TRUE;
924 END IF;
925
926 --11685037
927 <<lpn_errored>>
928 --Log Errored lpn_id and line_id with the error msg in msg file.
929 fnd_message.set_name('WMS', 'WMS_LPN_LINE_ERROR');
930 fnd_message.set_token('lpn_no', l_lpn_no);
931 fnd_message.set_token('lpn_id', to_char(l_lpn_id));
932 --fnd_message.set_token('line_no',to_char(l_mol_line_no));
933 fnd_message.set_token('line_id',to_char(l_mol_line_id));
934 fnd_msg_pub.add;
935 x_lpn_line_error_tbl(l_tbl_index).lpn_id := l_lpn_id;
936 x_lpn_line_error_tbl(l_tbl_index).line_id := l_mol_line_id;
937 l_tbl_index := l_tbl_index+1;
938
939 IF (l_from_conc_pgm = 'Y') THEN
940 print_message();
941 END IF;
942 IF (l_error) THEN
943 ROLLBACK TO SAVEPOINT suggestions_lpn_sp;
944 --Counts the no. of lpns that failed to process.
945 l_cnt_failed := l_cnt_failed + 1;
946 ELSE
947 IF (p_commit = 'Y' and p_lpn_id IS NULL) THEN -- 7460112
948 COMMIT;
949 END IF;
950 -- Counts the no. of lpns processed successfully.
951 l_cnt_success := l_cnt_success + 1;
952 END IF;
953
954 END LOOP;
955 CLOSE mol_lpn_cursor;
956
957 -- start of fix for -- 7460112
958
959
960 IF p_lpn_id IS NOT NULL THEN
961
962 BEGIN
963 SELECT /*+ ORDERED INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */ 1
964 INTO l_discrepancy
965 FROM
966 (SELECT wlpn.lpn_id
967 FROM wms_license_plate_numbers wlpn
968 START WITH wlpn.lpn_id = p_lpn_id
969 CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id) wlpn,
970 mtl_txn_request_lines mtrl -- Bug 14372071
971 WHERE mtrl.lpn_id = wlpn.lpn_id
972 AND mtrl.line_status = 7
973 AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) <> Nvl(mtrl.quantity_detailed,0)
974 AND mtrl.organization_id = p_organization_id;
975 EXCEPTION
976 -- removed other exceptions as they are not required 12942776
977 WHEN OTHERS THEN
978 l_discrepancy := 0;
979 END;
980
981 IF (l_debug = 1) THEN
982 print_debug('l_discrepancy = ' || l_discrepancy);
983 END IF;
984
985 IF l_discrepancy = 1 THEN
986
987 print_debug(' Suggested locator capacity should be reverted as putaway fails');
988 print_debug(' Before calling the revert_loc_suggested_capacity');
989 wms_task_dispatch_put_away.revert_loc_suggested_capacity(
990 x_return_status => l_return_status
991 , x_msg_count => l_msg_count
992 , x_msg_data => l_msg_data
993 , p_organization_id => p_organization_id
994 , p_lpn_id => p_lpn_id
995 );
996 print_debug(' After calling the revert_loc_suggested_capacity');
997 ROLLBACK;
998
999 RAISE fnd_api.g_exc_error;
1000 else
1001 if (p_commit = 'Y') then
1002 print_debug(' Before LPN Commit');
1003 commit;
1004 end if;
1005 END IF;
1006
1007 END IF;
1008
1009
1010 -- End of fix for bug 7460112
1011
1012
1013
1014 fnd_message.set_name('WMS', 'WMS_COUNT_LPN_SUCCESS');
1015 fnd_message.set_token('CNT_SUCCESS', to_char(l_cnt_success));
1016 fnd_msg_pub.add;
1017
1018 fnd_message.set_name('WMS', 'WMS_COUNT_LPN_FAILED');
1019 fnd_message.set_token('CNT_FAILED', to_char(l_cnt_failed));
1020 fnd_msg_pub.add;
1021
1022 --If all the lpns have failed then we raise ERROR.
1023 IF (l_cnt_success = 0) AND (l_cnt_failed > 0) THEN
1024 RAISE fnd_api.g_exc_error;
1025 END IF;
1026
1027 IF (l_from_conc_pgm = 'Y') THEN
1028 print_message();
1029 END IF;
1030
1031 x_partial_success := l_partial_success;
1032 IF (l_debug = 1) THEN
1033 print_debug('pre_generate: End of pre_generate API');
1034 END IF;
1035
1036 EXCEPTION
1037 WHEN fnd_api.g_exc_error THEN
1038 x_return_status := fnd_api.g_ret_sts_error;
1039
1040 IF (l_from_conc_pgm = 'Y') THEN
1041 print_message();
1042 END IF;
1043
1044 IF (l_debug = 1) THEN
1045 print_debug('pre_generate: fnd_api.g_exc_error');
1046 END IF;
1047
1048 IF ((p_commit = 'N') OR (NOT(l_entry_loop))) THEN
1049 ROLLBACK TO SAVEPOINT suggestions_start_sp;
1050 ELSE
1051 ROLLBACK TO SAVEPOINT suggestions_lpn_sp;
1052 END IF;
1053
1054 IF mol_lpn_cursor%isopen THEN
1055 CLOSE mol_lpn_cursor;
1056 END IF;
1057
1058 IF mmtt_cursor%isopen THEN
1059 CLOSE mmtt_cursor;
1060 END IF;
1061
1062 WHEN fnd_api.g_exc_unexpected_error THEN
1063 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1064
1065 IF (l_from_conc_pgm = 'Y') THEN
1066 print_message();
1067 END IF;
1068
1069 IF (l_debug = 1) THEN
1070 print_debug('pre_generate: fnd_api.g_exc_unexpected_error');
1071 END IF;
1072
1073 IF ((p_commit = 'N') OR (NOT(l_entry_loop))) THEN
1074 ROLLBACK TO SAVEPOINT suggestions_start_sp;
1075 ELSE
1076 ROLLBACK TO SAVEPOINT suggestions_lpn_sp;
1077 END IF;
1078
1079 IF mol_lpn_cursor%isopen THEN
1080 CLOSE mol_lpn_cursor;
1081 END IF;
1082
1083 IF mmtt_cursor%isopen THEN
1084 CLOSE mmtt_cursor;
1085 END IF;
1086
1087 WHEN OTHERS THEN
1088 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1089
1090 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1091 fnd_msg_pub.add_exc_msg(g_pkg_name, 'PRE_GENERATE');
1092 END IF;
1093
1094 IF (l_from_conc_pgm = 'Y') THEN
1095 print_message();
1096 END IF;
1097
1098 IF (l_debug = 1) THEN
1099 print_debug('pre_generate: other EXCEPTION');
1100 END IF;
1101
1102 IF ((p_commit = 'N') OR (NOT(l_entry_loop))) THEN
1103 ROLLBACK TO SAVEPOINT suggestions_start_sp;
1104 ELSE
1105 ROLLBACK TO SAVEPOINT suggestions_lpn_sp;
1106 END IF;
1107
1108 IF mol_lpn_cursor%isopen THEN
1109 CLOSE mol_lpn_cursor;
1110 END IF;
1111
1112 IF mmtt_cursor%isopen THEN
1113 CLOSE mmtt_cursor;
1114 END IF;
1115
1116
1117
1118 END pre_generate;
1119
1120
1121 PROCEDURE cleanup_suggestions
1122 (p_org_id IN NUMBER,
1123 p_lpn_id IN NUMBER,
1124 x_return_status OUT NOCOPY VARCHAR2,
1125 x_msg_count OUT NOCOPY NUMBER,
1126 x_msg_data OUT NOCOPY VARCHAR2,
1127 p_move_order_line_id IN NUMBER DEFAULT NULL -- added for ATF_J2
1128 )
1129 IS
1130
1131 Cursor sugg_info is
1132 SELECT transaction_temp_id,
1133 locator_id,
1134 inventory_item_id,
1135 primary_quantity,
1136 operation_plan_id -- added for ATF_J2
1137 FROM mtl_material_transactions_temp
1138 WHERE lpn_id = p_lpn_id
1139 AND move_order_line_id = Nvl(p_move_order_line_id, move_order_line_id) -- added for ATF_J2
1140 AND organization_id = p_org_id;
1141
1142 l_locator_id NUMBER;
1143 l_inventory_item_id NUMBER;
1144 l_primary_quantity NUMBER;
1145
1146 -- Following variables added in ATF_J2
1147
1148 l_ATF_error_code NUMBER;
1149 l_return_status VARCHAR2(1);
1150 l_msg_count NUMBER;
1151 l_msg_data VARCHAR2(2000);
1152
1153 -- End variables added in ATF_J2
1154
1155 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1156
1157 BEGIN
1158
1159 IF (l_debug = 1) THEN
1160 print_debug('clean_up suggestions: Start of cleanup_suggestions API');
1161 print_debug('p_lpn_id = '|| p_lpn_id);
1162 print_debug('p_org_id = '|| p_org_id);
1163 print_debug('p_move_order_line_id = '|| p_move_order_line_id);
1164 END IF;
1165
1166 SAVEPOINT sp_cleanup_suggs;
1167
1168 BEGIN
1169 DELETE
1170 FROM mtl_transaction_lots_temp
1171 WHERE transaction_temp_id IN
1172 (SELECT transaction_temp_id
1173 FROM mtl_material_transactions_temp
1174 WHERE lpn_id = p_lpn_id
1175 AND move_order_line_id = Nvl(p_move_order_line_id, move_order_line_id) -- added for ATF_J2
1176 AND organization_id = p_org_id);
1177
1178 EXCEPTION
1179 WHEN NO_DATA_FOUND THEN NULL;
1180 END;
1181
1182 FOR current_suggestion in sugg_info
1183 LOOP
1184 l_locator_id := current_suggestion.locator_id;
1185 l_inventory_item_id := current_suggestion.inventory_item_id;
1186 l_primary_quantity := current_suggestion.primary_quantity;
1187
1188 IF (l_debug = 1) THEN
1189 print_debug('clean_up suggestions: inside sugg_info cursor');
1190 print_debug('clean_up suggestions: locator id =' || l_locator_id) ;
1191 print_debug('clean_up suggestions: item='|| l_inventory_item_id);
1192 print_debug('clean_up suggestions: primary qty='|| l_primary_quantity);
1193 print_debug('clean_up suggestions: transaction_temp_id = ' || current_suggestion.transaction_temp_id);
1194 END IF;
1195
1196 -- added following if statement for bug fix 3401817
1197 -- Abort_Operation_Plan calls revert_loc_sugg_capacity_nauto
1198 IF current_suggestion.operation_plan_id IS NULL THEN
1199
1200 inv_loc_wms_utils.revert_loc_suggested_capacity
1201 (
1202 x_return_status => x_return_status
1203 , x_msg_count => x_msg_count
1204 , x_msg_data => x_msg_data
1205 , p_organization_id => p_org_id
1206 , p_inventory_location_id => l_locator_id
1207 , p_inventory_item_id => l_inventory_item_id
1208 , p_primary_uom_flag => 'Y'
1209 , p_transaction_uom_code => NULL
1210 , p_quantity => l_primary_quantity
1211 );
1212 IF (l_debug = 1) THEN
1213 print_debug('cleanup_suggestions: After calling inv_loc_wms_utils.revert_loc_suggested_capacity');
1214 print_debug(' x_return_status = ' || x_return_status);
1215 END IF;
1216
1217 IF x_return_status = fnd_api.g_ret_sts_error THEN
1218 -- Bug 5393727: do not raise an exception if locator API returns an error
1219 -- RAISE fnd_api.g_exc_error;
1220 NULL;
1221 END IF ;
1222
1223 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1224 -- Bug 5393727: do not raise an exception if locator API returns an error
1225 -- RAISE fnd_api.g_exc_unexpected_error;
1226 NULL;
1227 END IF;
1228
1229 END IF;
1230
1231
1232 -- ATF_J
1233 -- Need to call abort_operation_plan if J or above,
1234 -- which will abort and archive the operation plan and operation instances.
1235 -- Cancel_operation_plan also takes care of MMTT, MOL, etc.
1236 -- which is not appropriate being called here.
1237
1238 IF current_suggestion.operation_plan_id IS NOT NULL THEN
1239 IF (l_debug = 1) THEN
1240 print_debug('cleanup_suggestions: Current release is above J, therefore need to call abort_operation_plan.');
1241 print_debug('cleanup_suggestions: Before calling wms_op_runtime_pub_apis.Abort_Operation_Plan with following parameters: ');
1242 print_debug('p_source_task_id = ' || current_suggestion.transaction_temp_id);
1243 print_debug('p_activity_type_id = ' || 1);
1244 END IF;
1245
1246 wms_atf_runtime_pub_apis.Abort_Operation_Plan
1247 (
1248 x_return_status => l_return_status,
1249 x_msg_data => l_msg_data,
1250 x_msg_count => l_msg_count,
1251 x_error_code => l_ATF_error_code,
1252 p_source_task_id => current_suggestion.transaction_temp_id,
1253 p_activity_type_id => 1, -- Inbound,
1254 p_for_manual_drop => TRUE -- added for bug fix 3866880
1255 -- Pass TRUE in this argument so that abort_operation_plan
1256 -- will call the revert_locator_capacity API with autonomous
1257 -- commit. By doing this, we can decommission the cleanup_suggestions
1258 -- api with autonomous commit, which is conflicting with
1259 -- MMTT and MOL update from inbound UI for item load.
1260
1261 );
1262
1263 IF (l_debug = 1) THEN
1264 print_debug('cleanup_suggestions: After calling wms_op_runtime_pub_apis.Abort_Operation_Plan');
1265 print_debug(' l_return_status = ' || l_return_status);
1266 END IF;
1267
1268 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1269
1270 IF (l_debug = 1) THEN
1271 print_debug('cleanup_suggestions: wms_op_runtime_pub_apis.Abort_Operation_Plan failed.');
1272 END IF;
1273
1274 RAISE fnd_api.g_exc_error;
1275
1276 END IF; -- (l_return_status <> fnd_api.g_ret_sts_success)
1277
1278
1279 END IF; -- current_suggestion.operation_plan_id IS NOT
1280 -- End ATF_J
1281
1282 END LOOP;
1283
1284
1285 BEGIN
1286 DELETE
1287 FROM mtl_material_transactions_temp
1288 WHERE lpn_id = p_lpn_id
1289 AND move_order_line_id = Nvl(p_move_order_line_id, move_order_line_id) -- added for ATF_J2
1290 AND organization_id = p_org_id;
1291
1292 EXCEPTION
1293 WHEN NO_DATA_FOUND THEN NULL;
1294 END;
1295
1296 BEGIN
1297 UPDATE mtl_txn_request_lines
1298 SET quantity_detailed = Nvl(quantity_delivered, 0),secondary_quantity_detailed = Nvl(secondary_quantity_delivered,0) --10332833
1299 WHERE lpn_id = p_lpn_id
1300 AND line_id = Nvl(p_move_order_line_id, line_id) -- added for ATF_J2
1301 AND organization_id = p_org_id;
1302 -- Removed the backorder detail id from the where clause since the lines have TO clean up ALL lines FROM mmtt AND correspondingly UPDATE MTRL vipartha
1303
1304 EXCEPTION
1305 WHEN NO_DATA_FOUND THEN NULL;
1306 END;
1307
1308 x_return_status := FND_API.G_RET_STS_SUCCESS;
1309 IF (l_debug = 1) THEN
1310 print_debug('clean_up suggestions: End of cleanup_suggestions API');
1311 END IF;
1312
1313
1314 EXCEPTION
1315 WHEN FND_API.G_EXC_ERROR THEN
1316 x_return_status := FND_API.G_RET_STS_ERROR;
1317 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1318 ROLLBACK TO sp_cleanup_suggs;
1319
1320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1322 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1323 ROLLBACK TO sp_cleanup_suggs;
1324
1325 WHEN OTHERS THEN
1326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1327 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1328 ROLLBACK TO sp_cleanup_suggs;
1329
1330 END cleanup_suggestions;
1331
1332
1333
1334
1335 /*===========================================================================+
1336 | Procedure: |
1337 | print_message |
1338 | |
1339 | Description: |
1340 | Writes message text in log files. |
1341 | |
1342 | Input Parameters: |
1343 | None |
1344 | |
1345 | Output Parameters: |
1346 | None |
1347 | |
1348 | Tables Used: |
1349 | None |
1350 +===========================================================================*/
1351
1352 PROCEDURE print_message(dummy IN VARCHAR2 DEFAULT NULL) IS
1353
1354 --Variables
1355 l_msg_count NUMBER;
1356 l_msg_data VARCHAR2(2000);
1357
1358 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1359 BEGIN
1360
1361 fnd_msg_pub.count_and_get(
1362 p_count => l_msg_count,
1363 p_data => l_msg_data,
1364 p_encoded => 'F'
1365 );
1366
1367 FOR i IN 1..l_msg_count LOOP
1368 l_msg_data := fnd_msg_pub.get(i, 'F');
1369 fnd_file.put_line(fnd_file.log, l_msg_data);
1370 END LOOP;
1371 fnd_file.put_line(fnd_file.log, ' ');
1372
1373 fnd_msg_pub.initialize;
1374
1375 EXCEPTION
1376 WHEN OTHERS THEN
1377 fnd_file.put_line(fnd_file.log, sqlerrm);
1378
1379 END print_message;
1380
1381 END wms_putaway_suggestions;
[Home] [Help]