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