[Home] [Help]
PACKAGE BODY: APPS.WMS_POST_ALLOCATION
Source
1 PACKAGE BODY wms_post_allocation AS
2 /* $Header: WMSPRPAB.pls 120.9 2010/12/07 00:45:19 sfulzele noship $ */
3
4 g_pkg_body_ver CONSTANT VARCHAR2(100) := '$Header $';
5 g_newline CONSTANT VARCHAR2(10) := fnd_global.newline;
6 g_single_threaded CONSTANT BOOLEAN := FALSE;
7 g_bulk_fetch_limit CONSTANT NUMBER := 1000;
8 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'),0);
9 g_num_workers NUMBER := NVL(fnd_profile.value('WSH_PR_NUM_WORKERS'),1);
10
11 g_assign_op_plans BOOLEAN := TRUE;
12 g_call_cartonization BOOLEAN := TRUE;
13 g_consolidate_tasks BOOLEAN := TRUE;
14 g_assign_task_types BOOLEAN := TRUE;
15 g_process_device_reqs BOOLEAN := TRUE;
16 g_assign_pick_slips BOOLEAN := TRUE;
17 g_plan_tasks BOOLEAN := FALSE;
18 g_print_labels BOOLEAN := TRUE;
19
20
21 TYPE t_num_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
22
23
24 PROCEDURE print_debug
25 ( p_msg IN VARCHAR2
26 , p_api_name IN VARCHAR2
27 ) IS
28 BEGIN
29 inv_log_util.trace
30 ( p_message => p_msg
31 , p_module => g_pkg_name || '.' || p_api_name
32 , p_level => 9
33 );
34 END print_debug;
35
36
37
38 PROCEDURE print_version_info IS
39 BEGIN
40 print_debug ('Spec:: ' || g_pkg_spec_ver, 'print_version_info');
41 print_debug ('Body:: ' || g_pkg_body_ver, 'print_version_info');
42 END print_version_info;
43
44
45
46 --
47 -- This API divides MMTT into sub-batches and inserts records into
48 -- WMS_PR_WORKERS, one for each sub-batch.
49 --
50 PROCEDURE create_sub_batches
51 ( p_organization_id IN NUMBER
52 , p_mo_header_id IN NUMBER
53 , p_batch_id IN NUMBER
54 , p_mode IN VARCHAR2
55 , x_return_status OUT NOCOPY VARCHAR2
56 , x_num_sub_batches OUT NOCOPY NUMBER
57 ) IS
58 l_api_name VARCHAR2(30) := 'create_sub_batches';
59 l_msg_count NUMBER;
60 l_msg_data VARCHAR2(2000);
61
62 l_sub_batch_id NUMBER;
63 l_num_sub_batches NUMBER := 0;
64 l_detail_count NUMBER := 0;
65
66 -- Tables used for bulk processing
67 l_counts t_num_tab;
68 l_itm_ids t_num_tab;
69 l_sub_ids t_num_tab;
70 l_crt_ids t_num_tab;
71 l_tmp_ids t_num_tab;
72
73 -- Cursor to fetch cartonization IDs for label printing
74 CURSOR c_wpr_carton_labels (p_mo_hdr_id NUMBER) IS
75 SELECT COUNT(*) l_num_rows
76 , mmtt.cartonization_id
77 FROM mtl_material_transactions_temp mmtt
78 WHERE mmtt.move_order_header_id = p_mo_hdr_id
79 AND mmtt.cartonization_id IS NOT NULL
80 GROUP BY mmtt.cartonization_id;
81
82 -- Cursor to fetch temp IDs for case pick labels
83 CURSOR c_wpr_casepick_labels (p_mo_hdr_id NUMBER) IS
84 -- Non-bulk tasks
85 SELECT mmtt.transaction_temp_id
86 FROM mtl_material_transactions_temp mmtt
87 WHERE mmtt.move_order_header_id = p_mo_hdr_id
88 AND mmtt.parent_line_id IS NULL
89 AND EXISTS
90 ( SELECT 'x'
91 FROM wms_user_task_type_attributes wutta
92 WHERE wutta.organization_id = mmtt.organization_id
93 AND wutta.user_task_type_id = mmtt.standard_operation_id
94 AND wutta.honor_case_pick_flag = 'Y'
95 )
96 UNION ALL
97 -- Bulk pick parent tasks
98 SELECT mmtt.transaction_temp_id
99 FROM mtl_material_transactions_temp mmtt
100 WHERE mmtt.transaction_temp_id IN
101 ( SELECT DISTINCT mmtt2.parent_line_id
102 FROM mtl_material_transactions_temp mmtt2
103 WHERE mmtt2.move_order_header_id = p_mo_hdr_id
104 AND mmtt2.parent_line_id IS NOT NULL
105 AND mmtt2.transaction_temp_id <> mmtt2.parent_line_id
106 )
107 AND EXISTS
108 ( SELECT 'x'
109 FROM wms_user_task_type_attributes wutta
110 WHERE wutta.organization_id = mmtt.organization_id
111 AND wutta.user_task_type_id = mmtt.standard_operation_id
112 AND wutta.honor_case_pick_flag = 'Y'
113 );
114
115 -- Op plan assignment: fetch unique item IDs across all records
116 CURSOR c_wpr_opa (p_mo_hdr_id NUMBER) IS
117 SELECT COUNT(*) l_num_rows
118 , mmtt.inventory_item_id
119 FROM mtl_material_transactions_temp mmtt
120 WHERE mmtt.move_order_header_id = p_mo_hdr_id
121 GROUP BY mmtt.inventory_item_id;
122
123 -- Task type assignment: fetch unique item IDs from
124 -- bulk pick parent tasks and non-bulk tasks
125 CURSOR c_wpr_tta (p_mo_hdr_id NUMBER) IS
126 SELECT COUNT(*) l_num_rows
127 , mmtt.inventory_item_id
128 FROM mtl_material_transactions_temp mmtt
129 WHERE ( mmtt.move_order_header_id = p_mo_hdr_id
130 AND mmtt.parent_line_id IS NULL)
131 OR ( mmtt.transaction_temp_id IN
132 ( SELECT DISTINCT mmtt2.parent_line_id
133 FROM mtl_material_transactions_temp mmtt2
134 WHERE mmtt2.move_order_header_id = p_mo_hdr_id
135 AND mmtt2.parent_line_id IS NOT NULL
136 AND mmtt2.transaction_temp_id <> mmtt2.parent_line_id
137 )
138 )
139 GROUP BY mmtt.inventory_item_id;
140
141 BEGIN
142 x_return_status := fnd_api.g_ret_sts_success;
143
144 SAVEPOINT cr_sub_batch_sp;
145
146 IF g_debug = 1 THEN
147 print_debug( 'Entered with parameters: ' || g_newline ||
148 'p_organization_id => ' || TO_CHAR(p_organization_id) || g_newline ||
149 'p_mo_header_id => ' || TO_CHAR(p_mo_header_id) || g_newline ||
150 'p_batch_id => ' || TO_CHAR(p_batch_id) || g_newline ||
151 'p_mode => ' || p_mode
152 , l_api_name);
153 END IF;
154
155 -- If type is LABEL, create sub-batches to print carton labels
156 -- and case pick labels
157 IF p_mode = 'LABEL' THEN --{
158 OPEN c_wpr_carton_labels(p_mo_header_id);
159
160 LOOP --{
161 FETCH c_wpr_carton_labels BULK COLLECT
162 INTO l_counts, l_crt_ids LIMIT g_bulk_fetch_limit;
163
164 EXIT WHEN l_crt_ids.COUNT = 0;
165
166 FORALL ii IN l_crt_ids.FIRST..l_crt_ids.LAST
167 INSERT INTO wms_pr_workers( batch_id
168 , worker_mode
169 , processed_flag
170 , organization_id
171 , mo_header_id
172 , cartonization_id
173 , detailed_count
174 )
175 VALUES ( p_batch_id
176 , 'CRTN_LBL' -- Carton label
177 , 'N'
178 , p_organization_id
179 , p_mo_header_id
180 , l_crt_ids(ii)
181 , l_counts(ii)
182 );
183
184 l_num_sub_batches := l_num_sub_batches + l_crt_ids.COUNT;
185
186 END LOOP; --}
187
188 IF c_wpr_carton_labels%ISOPEN THEN
189 CLOSE c_wpr_carton_labels;
190 END IF;
191
192 IF g_debug = 1 THEN
193 print_debug( 'Inserted ' || l_num_sub_batches ||
194 ' worker records for cartonization label printing.'
195 , l_api_name);
196 END IF;
197
198 OPEN c_wpr_casepick_labels(p_mo_header_id);
199
200 LOOP --{
201 FETCH c_wpr_casepick_labels BULK COLLECT
202 INTO l_tmp_ids LIMIT 500;
203
204 EXIT WHEN l_tmp_ids.COUNT = 0;
205
206 l_detail_count := l_tmp_ids.COUNT;
207
208 INSERT INTO wms_pr_workers( batch_id
209 , worker_mode
210 , processed_flag
211 , organization_id
212 , mo_header_id
213 , transaction_batch_id
214 , detailed_count
215 )
216 VALUES ( p_batch_id
217 , 'CSPK_LBL' -- Case pick label
218 , 'N'
219 , p_organization_id
220 , p_mo_header_id
221 , mtl_material_transactions_s.nextval
222 , l_detail_count
223 )
224 RETURNING transaction_batch_id INTO l_sub_batch_id;
225
226 l_num_sub_batches := l_num_sub_batches + 1;
227
228 FORALL ii IN l_tmp_ids.FIRST..l_tmp_ids.LAST
229 UPDATE mtl_material_transactions_temp mmtt
230 SET mmtt.transaction_batch_id = l_sub_batch_id
231 WHERE mmtt.transaction_temp_id = l_tmp_ids(ii);
232
233 END LOOP; --}
234
235 IF c_wpr_casepick_labels%ISOPEN THEN
236 CLOSE c_wpr_casepick_labels;
237 END IF;
238
239 IF g_debug = 1 THEN
240 print_debug('Total number of worker records for label printing: '
241 || l_num_sub_batches, l_api_name);
242 END IF;
243 --}
244 ELSIF p_mode IN ('OPA','TTA') THEN --{
245 IF p_mode = 'OPA' THEN
246 OPEN c_wpr_opa(p_mo_header_id);
247 ELSE
248 OPEN c_wpr_tta(p_mo_header_id);
249 END IF;
250
251 LOOP --{
252 IF p_mode = 'OPA' THEN
253 FETCH c_wpr_opa BULK COLLECT
254 INTO l_counts, l_itm_ids LIMIT g_bulk_fetch_limit;
255 ELSE
256 FETCH c_wpr_tta BULK COLLECT
257 INTO l_counts, l_itm_ids LIMIT g_bulk_fetch_limit;
258 END IF;
259
260 EXIT WHEN l_itm_ids.COUNT = 0;
261
262 FORALL ii IN l_itm_ids.FIRST..l_itm_ids.LAST
263 INSERT INTO wms_pr_workers( batch_id
264 , worker_mode
265 , processed_flag
266 , organization_id
267 , mo_header_id
268 , transaction_batch_id
269 , detailed_count
270 )
271 VALUES ( p_batch_id
272 , p_mode
273 , 'N'
274 , p_organization_id
275 , p_mo_header_id
276 , mtl_material_transactions_s.nextval
277 , l_counts(ii)
278 )
279 RETURNING transaction_batch_id BULK COLLECT INTO l_sub_ids;
280
281 l_num_sub_batches := l_num_sub_batches + l_itm_ids.COUNT;
282
283 FORALL jj IN l_sub_ids.FIRST..l_sub_ids.LAST
284 UPDATE mtl_material_transactions_temp mmtt
285 SET mmtt.transaction_batch_id = l_sub_ids(jj)
286 WHERE mmtt.inventory_item_id = l_itm_ids(jj)
287 AND ( ( mmtt.move_order_header_id = p_mo_header_id
288 AND mmtt.parent_line_id IS NULL)
289 OR ( p_mode = 'TTA'
290 AND mmtt.transaction_temp_id IN
291 ( SELECT DISTINCT mmtt2.parent_line_id
292 FROM mtl_material_transactions_temp mmtt2
293 WHERE mmtt2.move_order_header_id = p_mo_header_id
294 AND mmtt2.parent_line_id IS NOT NULL
295 AND mmtt2.transaction_temp_id <> mmtt2.parent_line_id
296 )
297 )
298 );
299 END LOOP; --}
300
301 IF p_mode = 'OPA' AND c_wpr_opa%ISOPEN THEN
302 CLOSE c_wpr_opa;
303 ELSIF c_wpr_tta%ISOPEN THEN
304 CLOSE c_wpr_tta;
305 END IF;
306
307 IF g_debug = 1 THEN
308 print_debug('Done inserting worker records for ' || p_mode, l_api_name);
309 END IF;
310 --}
311 ELSE --{
312 IF g_debug = 1 THEN
313 print_debug('Invalid worker type: ' || p_mode, l_api_name);
314 END IF;
315 RAISE fnd_api.g_exc_unexpected_error;
316 END IF; --}
317
318 IF g_debug = 1 THEN
319 print_debug('Number of sub-batches: ' || l_num_sub_batches, l_api_name);
320 END IF;
321 x_num_sub_batches := l_num_sub_batches;
322
323 COMMIT;
324
325 EXCEPTION
326 WHEN fnd_api.g_exc_error THEN
327 ROLLBACK TO cr_sub_batch_sp;
328 x_return_status := fnd_api.g_ret_sts_error;
329 fnd_msg_pub.count_and_get
330 ( p_count => l_msg_count
331 , p_data => l_msg_data
332 , p_encoded => fnd_api.g_false
333 );
334 IF g_debug = 1 THEN
335 print_debug (l_msg_data, l_api_name);
336 END IF;
337 IF c_wpr_carton_labels%ISOPEN THEN
338 CLOSE c_wpr_carton_labels;
339 END IF;
340 IF c_wpr_casepick_labels%ISOPEN THEN
341 CLOSE c_wpr_casepick_labels;
342 END IF;
343 IF c_wpr_opa%ISOPEN THEN
344 CLOSE c_wpr_opa;
345 END IF;
346 IF c_wpr_tta%ISOPEN THEN
347 CLOSE c_wpr_tta;
348 END IF;
349
350 WHEN OTHERS THEN
351 ROLLBACK TO cr_sub_batch_sp;
352 x_return_status := fnd_api.g_ret_sts_unexp_error;
353 IF g_debug = 1 THEN
354 print_debug ('Other error: ' || SQLERRM, l_api_name);
355 END IF;
356 IF c_wpr_carton_labels%ISOPEN THEN
357 CLOSE c_wpr_carton_labels;
358 END IF;
359 IF c_wpr_casepick_labels%ISOPEN THEN
360 CLOSE c_wpr_casepick_labels;
361 END IF;
362 IF c_wpr_opa%ISOPEN THEN
363 CLOSE c_wpr_opa;
364 END IF;
365 IF c_wpr_tta%ISOPEN THEN
366 CLOSE c_wpr_tta;
367 END IF;
368 END create_sub_batches;
369
370
371
372 --
373 -- This API submits concurrent requests for operation plan assignment,
374 -- task type assignment and label printing.
375 --
376 PROCEDURE spawn_workers
377 ( p_batch_id IN NUMBER
378 , p_mode IN VARCHAR2
379 , p_num_workers IN NUMBER
380 , p_wsh_status IN VARCHAR2
381 , p_auto_pick_confirm IN VARCHAR2
382 , x_return_status OUT NOCOPY VARCHAR2
383 ) IS
384 l_api_name VARCHAR2(30) := 'spawn_workers';
385 l_msg_count NUMBER;
386 l_msg_data VARCHAR2(2000);
387
388 l_sub_request BOOLEAN := TRUE;
389 l_request_id NUMBER;
390
391 BEGIN
392 x_return_status := fnd_api.g_ret_sts_success;
393
394 -- Need to pause the parent request if doing operation plan assignment,
395 -- task type assignment, or label printing with auto-pick-confirm = Y
396 IF (p_mode = 'LABEL' AND p_auto_pick_confirm = 'Y')
397 OR (p_mode <> 'LABEL') THEN
398 l_sub_request := TRUE;
399 IF g_debug = 1 THEN
400 print_debug ('Sub request is TRUE.', l_api_name);
401 END IF;
402 ELSE
403 l_sub_request := FALSE;
404 IF g_debug = 1 THEN
405 print_debug ('Sub request is FALSE.', l_api_name);
406 END IF;
407 END IF;
408
409 FOR ii IN 1..p_num_workers LOOP -- {
410 IF g_debug = 1 THEN
411 print_debug ('Submitting worker #: ' || ii, l_api_name);
412 END IF;
413 l_request_id :=
414 FND_REQUEST.Submit_Request( application => 'WMS'
415 , program => 'WMSPALOC_SUB'
416 , description => ''
417 , start_time => ''
418 , sub_request => l_sub_request
419 , argument1 => p_batch_id
420 , argument2 => p_mode
421 , argument3 => ii -- Worker ID
422 );
423 IF l_request_id = 0 THEN
424 IF g_debug = 1 THEN
425 print_debug( 'Request submission failed for worker ' || ii
426 , l_api_name);
427 END IF;
428 RAISE fnd_api.g_exc_unexpected_error;
429 ELSE
430 IF g_debug = 1 THEN
431 print_debug( 'Request ' || l_request_id ||
432 ' submitted successfully' , l_api_name);
433 END IF;
434 END IF;
435 END LOOP; --}
436
437 IF l_sub_request THEN
438
439 IF g_debug = 1 THEN
440 print_debug ('Setting Parent Request to pause' , l_api_name);
441 END IF;
442
443 FND_CONC_GLOBAL.Set_Req_Globals( Conc_Status => 'PAUSED'
444 , Request_Data => p_batch_id ||':'||
445 p_wsh_status ||':'||
446 p_mode);
447 END IF;
448
449 EXCEPTION
450 WHEN fnd_api.g_exc_error THEN
451 x_return_status := fnd_api.g_ret_sts_error;
452 fnd_msg_pub.count_and_get
453 ( p_count => l_msg_count
454 , p_data => l_msg_data
455 , p_encoded => fnd_api.g_false
456 );
457 IF g_debug = 1 THEN
458 print_debug (l_msg_data, l_api_name);
459 END IF;
460
461 WHEN OTHERS THEN
462 x_return_status := fnd_api.g_ret_sts_unexp_error;
463 IF g_debug = 1 THEN
464 print_debug ('Other error: ' || SQLERRM, l_api_name);
465 END IF;
466 END spawn_workers;
467
468
469
470 PROCEDURE assign_operation_plans
471 ( p_organization_id IN NUMBER
472 , p_mo_header_id IN NUMBER
473 , p_batch_id IN NUMBER
474 , p_num_workers IN NUMBER
475 , p_create_sub_batches IN VARCHAR2
476 , p_wsh_status IN VARCHAR2
477 , x_return_status OUT NOCOPY VARCHAR2
478 , x_sub_requests OUT NOCOPY VARCHAR2 --BUG10325405
479 ) IS
480 l_api_name VARCHAR2(30) := 'assign_operation_plans';
481 l_msg_count NUMBER;
482 l_msg_data VARCHAR2(2000);
483
484 l_dummy VARCHAR2(1);
485 l_rule_exists BOOLEAN := FALSE;
486 l_api_return_status VARCHAR2(1);
487 l_op_plan_id NUMBER;
488 l_num_sub_batches NUMBER;
489
490 CURSOR c_opa_rule_exists (p_org_id NUMBER) IS
491 SELECT 'x' FROM dual
492 WHERE EXISTS
493 ( SELECT 'x'
494 FROM wms_rules rules
495 , wms_op_plans_b wop
496 WHERE rules.organization_id IN (p_org_id,-1)
497 AND rules.type_code = 7
498 AND rules.enabled_flag = 'Y'
499 AND rules.type_hdr_id = wop.operation_plan_id
500 AND wop.activity_type_id = 2 -- Outbound
501 AND wop.enabled_flag = 'Y'
502 );
503
504 BEGIN
505 x_return_status := fnd_api.g_ret_sts_success;
506 x_sub_requests := 'N'; --BUG10325405
507
508 SAVEPOINT op_plan_assign_sp;
509
510 IF g_debug = 1 THEN
511 print_debug( 'Entered with parameters: ' || g_newline ||
512 'p_organization_id => ' || TO_CHAR(p_organization_id) || g_newline ||
513 'p_mo_header_id => ' || TO_CHAR(p_mo_header_id) || g_newline ||
514 'p_batch_id => ' || TO_CHAR(p_batch_id) || g_newline ||
515 'p_num_workers => ' || TO_CHAR(p_num_workers) || g_newline ||
516 'p_create_sub_batches => ' || p_create_sub_batches || g_newline ||
517 'p_wsh_status => ' || p_wsh_status
518 , l_api_name);
519 END IF;
520
521 OPEN c_opa_rule_exists (p_organization_id);
522 FETCH c_opa_rule_exists INTO l_dummy;
523 IF c_opa_rule_exists%FOUND THEN
524 l_rule_exists := TRUE;
525 END IF;
526 CLOSE c_opa_rule_exists;
527
528 IF l_rule_exists THEN --{
529 IF g_debug = 1 THEN
530 print_debug('OP plan rules exist, spawning sub-requests', l_api_name);
531 END IF;
532
533 x_sub_requests := 'Y'; --BUG10325405
534
535 IF g_debug = 1 THEN
536 print_debug('BUG10325405 in assign operations plan after setting the sub_requests value to Y', l_api_name);
537 END IF;
538
539 IF p_create_sub_batches = 'Y' THEN --{
540 l_api_return_status := fnd_api.g_ret_sts_success;
541 create_sub_batches
542 ( p_organization_id => p_organization_id
543 , p_mo_header_id => p_mo_header_id
544 , p_batch_id => p_batch_id
545 , p_mode => 'OPA'
546 , x_return_status => l_api_return_status
547 , x_num_sub_batches => l_num_sub_batches
548 );
549
550 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
551 IF g_debug = 1 THEN
552 print_debug('Error status from create_sub_batches: '
553 || l_api_return_status, l_api_name);
554 END IF;
555 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
556 RAISE fnd_api.g_exc_error;
557 ELSE
558 RAISE fnd_api.g_exc_unexpected_error;
559 END IF;
560 END IF;
561 END IF; --}
562
563 l_api_return_status := fnd_api.g_ret_sts_success;
564 spawn_workers
565 ( p_batch_id => p_batch_id
566 , p_mode => 'OPA'
567 , p_num_workers => LEAST(l_num_sub_batches,g_num_workers)
568 , p_wsh_status => p_wsh_status
569 , p_auto_pick_confirm => 'N'
570 , x_return_status => l_api_return_status
571 );
572
573 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
574 IF g_debug = 1 THEN
575 print_debug('Error status from spawn_workers: '
576 || l_api_return_status, l_api_name);
577 END IF;
578 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
579 RAISE fnd_api.g_exc_error;
580 ELSE
581 RAISE fnd_api.g_exc_unexpected_error;
582 END IF;
583 END IF;
584 --}
585 ELSE
586 -- If there is no operation plan selection rule enabled,
587 -- stamp the org default outbound operation plan
588 IF g_debug = 1 THEN
589 print_debug('No OP plan rules exist, checking for default org plan', l_api_name);
590 END IF;
591
592 x_sub_requests := 'N'; --BUG10325405
593 IF g_debug = 1 THEN
594 print_debug('BUG10325405 in assign operations plan after setting the sub_requests value to N', l_api_name);
595 END IF;
596
597 IF (inv_cache.set_org_rec(p_organization_id) ) THEN
598 l_op_plan_id := NVL(inv_cache.org_rec.default_pick_op_plan_id,1);
599 ELSE
600 IF g_debug = 1 THEN
601 print_debug ( 'Error setting cache for organization', l_api_name );
602 END IF;
603 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
604 END IF;
605
606 IF g_debug = 1 THEN
607 print_debug ('l_op_plan_id value: ' || l_op_plan_id, l_api_name);
608 END IF;
609
610 UPDATE mtl_material_transactions_temp
611 SET operation_plan_id = l_op_plan_id
612 WHERE move_order_header_id = p_mo_header_id;
613 END IF;
614
615 COMMIT;
616
617 EXCEPTION
618 WHEN fnd_api.g_exc_error THEN
619 ROLLBACK TO op_plan_assign_sp;
620 x_return_status := fnd_api.g_ret_sts_error;
621 fnd_msg_pub.count_and_get
622 ( p_count => l_msg_count
623 , p_data => l_msg_data
624 , p_encoded => fnd_api.g_false
625 );
626 IF g_debug = 1 THEN
627 print_debug (l_msg_data, l_api_name);
628 END IF;
629
630 IF c_opa_rule_exists%ISOPEN THEN
631 CLOSE c_opa_rule_exists;
632 END IF;
633
634 WHEN OTHERS THEN
635 ROLLBACK TO op_plan_assign_sp;
636 x_return_status := fnd_api.g_ret_sts_unexp_error;
637 IF g_debug = 1 THEN
638 print_debug ('Other error: ' || SQLERRM, l_api_name);
639 END IF;
640
641 IF c_opa_rule_exists%ISOPEN THEN
642 CLOSE c_opa_rule_exists;
643 END IF;
644
645 END assign_operation_plans;
646
647
648
649 PROCEDURE assign_task_types
650 ( p_organization_id IN NUMBER
651 , p_mo_header_id IN NUMBER
652 , p_batch_id IN NUMBER
653 , p_num_workers IN NUMBER
654 , p_create_sub_batches IN VARCHAR2
655 , p_wsh_status IN VARCHAR2
656 , x_return_status OUT NOCOPY VARCHAR2
657 , x_sub_requests OUT NOCOPY VARCHAR2 --BUG10325405
658 ) IS
659 l_api_name VARCHAR2(30) := 'assign_task_types';
660 l_msg_count NUMBER;
661 l_msg_data VARCHAR2(2000);
662
663 l_dummy VARCHAR2(1);
664 l_rule_exists BOOLEAN := FALSE;
665 l_api_return_status VARCHAR2(1);
666 l_ttype_id NUMBER;
667 l_num_sub_batches NUMBER;
668
669 CURSOR c_tta_rule_exists (p_org_id NUMBER) IS
670 SELECT 'x' FROM dual
671 WHERE EXISTS
672 ( SELECT 'x'
673 FROM wms_rules rules
674 WHERE rules.organization_id IN (p_org_id,-1)
675 AND rules.type_code = 3
676 AND rules.enabled_flag = 'Y'
677 );
678
679 BEGIN
680 x_return_status := fnd_api.g_ret_sts_success;
681 x_sub_requests := 'N'; --BUG10325405
682 SAVEPOINT ttype_assign_sp;
683
684 IF g_debug = 1 THEN
685 print_debug( 'Entered with parameters: ' || g_newline ||
686 'p_organization_id => ' || TO_CHAR(p_organization_id) || g_newline ||
687 'p_mo_header_id => ' || TO_CHAR(p_mo_header_id) || g_newline ||
688 'p_batch_id => ' || TO_CHAR(p_batch_id) || g_newline ||
689 'p_num_workers => ' || TO_CHAR(p_num_workers) || g_newline ||
690 'p_create_sub_batches => ' || p_create_sub_batches || g_newline ||
691 'p_wsh_status => ' || p_wsh_status
692 , l_api_name);
693 END IF;
694
695 OPEN c_tta_rule_exists (p_organization_id);
696 FETCH c_tta_rule_exists INTO l_dummy;
697 IF c_tta_rule_exists%FOUND THEN
698 l_rule_exists := TRUE;
699 END IF;
700 CLOSE c_tta_rule_exists;
701
702 IF l_rule_exists THEN --{
703 IF g_debug = 1 THEN
704 print_debug('Task type rules exist, spawning sub-requests', l_api_name);
705 END IF;
706 x_sub_requests := 'Y'; --BUG10325405
707 IF g_debug = 1 THEN
708 print_debug('BUG10325405 in assign task type after setting the sub_requests value to Y', l_api_name);
709 END IF;
710
711 l_num_sub_batches := g_num_workers;
712 IF p_create_sub_batches = 'Y' THEN --{
713 l_api_return_status := fnd_api.g_ret_sts_success;
714 create_sub_batches
715 ( p_organization_id => p_organization_id
716 , p_mo_header_id => p_mo_header_id
717 , p_batch_id => p_batch_id
718 , p_mode => 'TTA'
719 , x_return_status => l_api_return_status
720 , x_num_sub_batches => l_num_sub_batches
721 );
722
723 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
724 IF g_debug = 1 THEN
725 print_debug('Error status from create_sub_batches: '
726 || l_api_return_status, l_api_name);
727 END IF;
728 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
729 RAISE fnd_api.g_exc_error;
730 ELSE
731 RAISE fnd_api.g_exc_unexpected_error;
732 END IF;
733 END IF;
734 END IF; --}
735
736 l_api_return_status := fnd_api.g_ret_sts_success;
737 spawn_workers
738 ( p_batch_id => p_batch_id
739 , p_mode => 'TTA'
740 , p_num_workers => LEAST(l_num_sub_batches,g_num_workers)
741 , p_wsh_status => p_wsh_status
742 , p_auto_pick_confirm => 'N'
743 , x_return_status => l_api_return_status
744 );
745
746 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
747 IF g_debug = 1 THEN
748 print_debug('Error status from spawn_workers: '
749 || l_api_return_status, l_api_name);
750 END IF;
751 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
752 RAISE fnd_api.g_exc_error;
753 ELSE
754 RAISE fnd_api.g_exc_unexpected_error;
755 END IF;
756 END IF;
757 --}
758 ELSE
759 -- If there is no task type assignment rule enabled,
760 -- stamp the org default picking task type
761 IF g_debug = 1 THEN
762 print_debug('No task type rules exist, checking for default org task type', l_api_name);
763 END IF;
764 x_sub_requests := 'N'; --BUG10325405
765 IF g_debug = 1 THEN
766 print_debug('BUG10325405 in assign task type after setting the sub_requests value to N', l_api_name);
767 END IF;
768
769 IF (inv_cache.set_org_rec(p_organization_id) ) THEN
770 l_ttype_id := inv_cache.org_rec.default_pick_task_type_id;
771 ELSE
772 IF g_debug = 1 THEN
773 print_debug ( 'Error setting cache for organization', l_api_name );
774 END IF;
775 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
776 END IF;
777
778 IF g_debug = 1 THEN
779 print_debug ('l_ttype_id value: ' || l_ttype_id, l_api_name);
780 END IF;
781
782 UPDATE mtl_material_transactions_temp
783 SET standard_operation_id = l_ttype_id
784 WHERE move_order_header_id = p_mo_header_id;
785 END IF;
786
787 COMMIT;
788
789 EXCEPTION
790 WHEN fnd_api.g_exc_error THEN
791 ROLLBACK TO ttype_assign_sp;
792 x_return_status := fnd_api.g_ret_sts_error;
793 fnd_msg_pub.count_and_get
794 ( p_count => l_msg_count
795 , p_data => l_msg_data
796 , p_encoded => fnd_api.g_false
797 );
798 IF g_debug = 1 THEN
799 print_debug (l_msg_data, l_api_name);
800 END IF;
801
802 IF c_tta_rule_exists%ISOPEN THEN
803 CLOSE c_tta_rule_exists;
804 END IF;
805
806 WHEN OTHERS THEN
807 ROLLBACK TO ttype_assign_sp;
808 x_return_status := fnd_api.g_ret_sts_unexp_error;
809 IF g_debug = 1 THEN
810 print_debug ('Other error: ' || SQLERRM, l_api_name);
811 END IF;
812
813 IF c_tta_rule_exists%ISOPEN THEN
814 CLOSE c_tta_rule_exists;
815 END IF;
816
817 END assign_task_types;
818
819
820
821 PROCEDURE print_labels
822 ( p_organization_id IN NUMBER
823 , p_mo_header_id IN NUMBER
824 , p_batch_id IN NUMBER
825 , p_num_workers IN NUMBER
826 , p_auto_pick_confirm IN VARCHAR2
827 , p_create_sub_batches IN VARCHAR2
828 , p_wsh_status IN VARCHAR2
829 , x_return_status OUT NOCOPY VARCHAR2
830 , x_sub_requests OUT NOCOPY VARCHAR2 --BUG10325405
831 ) IS
832 l_api_name VARCHAR2(30) := 'print_labels';
833 l_msg_count NUMBER;
834 l_msg_data VARCHAR2(2000);
835
836 l_dummy VARCHAR2(1);
837 l_call_lbl_prnt BOOLEAN := FALSE;
838 l_api_return_status VARCHAR2(1);
839 l_num_sub_batches NUMBER;
840
841 CURSOR c_lbl_data_exists IS
842 SELECT 'x' FROM dual
843 WHERE EXISTS
844 ( SELECT 'x'
845 FROM mtl_material_transactions_temp mmtt1
846 WHERE mmtt1.move_order_header_id = p_mo_header_id
847 AND mmtt1.cartonization_id IS NOT NULL
848 )
849 OR EXISTS
850 ( SELECT 'x'
851 FROM mtl_material_transactions_temp mmtt2
852 WHERE mmtt2.move_order_header_id = p_mo_header_id
853 AND EXISTS
854 ( SELECT 'x'
855 FROM wms_user_task_type_attributes wutta1
856 WHERE wutta1.organization_id = mmtt2.organization_id
857 AND wutta1.user_task_type_id = mmtt2.standard_operation_id
858 AND wutta1.honor_case_pick_flag = 'Y'
859 )
860 )
861 OR EXISTS
862 ( SELECT 'x'
863 FROM mtl_material_transactions_temp mmtt3
864 WHERE mmtt3.transaction_temp_id IN
865 ( SELECT DISTINCT mmtt4.parent_line_id
866 FROM mtl_material_transactions_temp mmtt4
867 WHERE mmtt4.move_order_header_id = p_mo_header_id
868 AND mmtt4.parent_line_id IS NOT NULL
869 AND mmtt4.transaction_temp_id <> mmtt4.parent_line_id
870 )
871 AND EXISTS
872 ( SELECT 'x'
873 FROM wms_user_task_type_attributes wutta2
874 WHERE wutta2.organization_id = mmtt3.organization_id
875 AND wutta2.user_task_type_id = mmtt3.standard_operation_id
876 AND wutta2.honor_case_pick_flag = 'Y'
877 )
878 );
879
880 BEGIN
881 x_return_status := fnd_api.g_ret_sts_success;
882 x_sub_requests := 'N'; --BUG10325405
883 SAVEPOINT label_print_sp;
884
885 IF g_debug = 1 THEN
886 print_debug( 'Entered with parameters: ' || g_newline ||
887 'p_organization_id => ' || TO_CHAR(p_organization_id) || g_newline ||
888 'p_mo_header_id => ' || TO_CHAR(p_mo_header_id) || g_newline ||
889 'p_batch_id => ' || TO_CHAR(p_batch_id) || g_newline ||
890 'p_num_workers => ' || TO_CHAR(p_num_workers) || g_newline ||
891 'p_create_sub_batches => ' || p_create_sub_batches || g_newline ||
892 'p_wsh_status => ' || p_wsh_status
893 , l_api_name);
894 END IF;
895
896 OPEN c_lbl_data_exists;
897 FETCH c_lbl_data_exists INTO l_dummy;
898 IF c_lbl_data_exists%FOUND THEN
899 l_call_lbl_prnt := TRUE;
900 END IF;
901 CLOSE c_lbl_data_exists;
902
903 IF l_call_lbl_prnt THEN --{
904 IF g_debug = 1 THEN
905 print_debug('Label data exists, spawning sub-requests', l_api_name);
906 END IF;
907 x_sub_requests := 'Y'; --BUG10325405
908
909 IF g_debug = 1 THEN
910 print_debug('BUG10325405 in print labels after setting the sub_requests value to Y', l_api_name);
911 END IF;
912 l_num_sub_batches := g_num_workers;
913 IF p_create_sub_batches = 'Y' THEN --{
914 l_api_return_status := fnd_api.g_ret_sts_success;
915 create_sub_batches
916 ( p_organization_id => p_organization_id
917 , p_mo_header_id => p_mo_header_id
918 , p_batch_id => p_batch_id
919 , p_mode => 'LABEL'
920 , x_return_status => l_api_return_status
921 , x_num_sub_batches => l_num_sub_batches
922 );
923
924 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
925 IF g_debug = 1 THEN
926 print_debug('Error status from create_sub_batches: '
927 || l_api_return_status, l_api_name);
928 END IF;
929 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
930 RAISE fnd_api.g_exc_error;
931 ELSE
932 RAISE fnd_api.g_exc_unexpected_error;
933 END IF;
934 END IF;
935 END IF; --}
936
937 l_api_return_status := fnd_api.g_ret_sts_success;
938 spawn_workers
939 ( p_batch_id => p_batch_id
940 , p_mode => 'LABEL'
941 , p_num_workers => LEAST(l_num_sub_batches,g_num_workers)
942 , p_wsh_status => p_wsh_status
943 , p_auto_pick_confirm => p_auto_pick_confirm
944 , x_return_status => l_api_return_status
945 );
946
947 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
948 IF g_debug = 1 THEN
949 print_debug('Error status from spawn_workers: '
950 || l_api_return_status, l_api_name);
951 END IF;
952 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
953 RAISE fnd_api.g_exc_error;
954 ELSE
955 RAISE fnd_api.g_exc_unexpected_error;
956 END IF;
957 END IF;
958 --}
959 ELSE
960 IF g_debug = 1 THEN
961 print_debug('No label printing data exists', l_api_name);
962 END IF;
963 x_sub_requests := 'N'; --BUG10325405
964 IF g_debug = 1 THEN
965 print_debug('BUG10325405 in print labels after setting the sub_requests value to N', l_api_name);
966 END IF;
967
968 END IF;
969
970 COMMIT;
971
972 EXCEPTION
973 WHEN fnd_api.g_exc_error THEN
974 ROLLBACK TO label_print_sp;
975 x_return_status := fnd_api.g_ret_sts_error;
976 fnd_msg_pub.count_and_get
977 ( p_count => l_msg_count
978 , p_data => l_msg_data
979 , p_encoded => fnd_api.g_false
980 );
981 IF g_debug = 1 THEN
982 print_debug (l_msg_data, l_api_name);
983 END IF;
984
985 IF c_lbl_data_exists%ISOPEN THEN
986 CLOSE c_lbl_data_exists;
987 END IF;
988
989 WHEN OTHERS THEN
990 ROLLBACK TO label_print_sp;
991 x_return_status := fnd_api.g_ret_sts_unexp_error;
992 IF g_debug = 1 THEN
993 print_debug ('Other error: ' || SQLERRM, l_api_name);
994 END IF;
995
996 IF c_lbl_data_exists%ISOPEN THEN
997 CLOSE c_lbl_data_exists;
998 END IF;
999
1000 END print_labels;
1001
1002
1003
1004 -- Invoked by the post-allocation child concurrent request (WMSPALOC_SUB)
1005 PROCEDURE process_sub_request
1006 ( errbuf OUT NOCOPY VARCHAR2
1007 , retcode OUT NOCOPY NUMBER
1008 , p_batch_id IN NUMBER
1009 , p_mode IN VARCHAR2
1010 , p_worker_id IN NUMBER
1011 ) IS
1012 l_api_name VARCHAR2(30) := 'process_sub_request';
1013 l_msg_count NUMBER;
1014 l_msg_data VARCHAR2(2000);
1015
1016 l_api_return_status VARCHAR2(1);
1017 l_conc_ret_status BOOLEAN;
1018 l_error_message VARCHAR2(2000);
1019
1020 BEGIN
1021 retcode := 0;
1022
1023 IF g_debug = 1 THEN
1024 print_debug( 'Entered with parameters: ' || g_newline ||
1025 'p_batch_id => ' || TO_CHAR(p_batch_id) || g_newline ||
1026 'p_mode => ' || p_mode || g_newline ||
1027 'p_worker_id => ' || TO_CHAR(p_worker_id)
1028 , l_api_name);
1029 END IF;
1030
1031 l_api_return_status := fnd_api.g_ret_sts_success;
1032 IF p_mode = 'OPA' THEN
1033 WMS_POSTALLOC_PVT.assign_operation_plans
1034 ( p_batch_id => p_batch_id
1035 , x_return_status => l_api_return_status
1036 );
1037 ELSIF p_mode = 'TTA' THEN
1038 WMS_POSTALLOC_PVT.assign_task_types
1039 ( p_batch_id => p_batch_id
1040 , x_return_status => l_api_return_status
1041 );
1042 ELSIF p_mode = 'LABEL' THEN
1043 WMS_POSTALLOC_PVT.print_labels
1044 ( p_batch_id => p_batch_id
1045 , x_return_status => l_api_return_status
1046 );
1047 ELSE
1048 IF g_debug = 1 THEN
1049 print_debug('Invalid worker type: ' || p_mode, l_api_name);
1050 END IF;
1051 RAISE fnd_api.g_exc_unexpected_error;
1052 END IF;
1053
1054 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1055 IF g_debug = 1 THEN
1056 print_debug('Error status from WMS_POSTALLOC_PVT API: '
1057 || l_api_return_status, l_api_name);
1058 END IF;
1059 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1060 RAISE fnd_api.g_exc_error;
1061 ELSE
1062 RAISE fnd_api.g_exc_unexpected_error;
1063 END IF;
1064 END IF;
1065
1066 l_conc_ret_status := fnd_concurrent.set_completion_status('NORMAL','');
1067 IF NOT l_conc_ret_status THEN
1068 IF g_debug = 1 THEN
1069 print_debug('Error setting concurrent return status to NORMAL', l_api_name);
1070 END IF;
1071 RAISE fnd_api.g_exc_unexpected_error;
1072 END IF;
1073
1074 EXCEPTION
1075 WHEN fnd_api.g_exc_error THEN
1076 fnd_msg_pub.count_and_get
1077 ( p_count => l_msg_count
1078 , p_data => l_msg_data
1079 , p_encoded => fnd_api.g_false
1080 );
1081 IF g_debug = 1 THEN
1082 print_debug (l_msg_data, l_api_name);
1083 END IF;
1084
1085 l_conc_ret_status := fnd_concurrent.set_completion_status('ERROR', l_msg_data);
1086 retcode := 2;
1087 errbuf := l_msg_data;
1088
1089 WHEN OTHERS THEN
1090 l_error_message := SQLERRM;
1091 IF g_debug = 1 THEN
1092 print_debug ('Other error: ' || l_error_message, l_api_name);
1093 END IF;
1094
1095 l_conc_ret_status := fnd_concurrent.set_completion_status('ERROR', l_error_message);
1096 retcode := 2;
1097 errbuf := l_error_message;
1098 END process_sub_request;
1099
1100
1101
1102 PROCEDURE cleanup_sub_batches
1103 ( p_org_id IN NUMBER
1104 , p_mo_header_id IN NUMBER
1105 , p_batch_id IN NUMBER
1106 ) IS
1107 l_api_name VARCHAR2(30) := 'cleanup_sub_batches';
1108
1109 BEGIN
1110 IF g_debug = 1 THEN
1111 print_debug( 'Entered with parameters: ' || g_newline ||
1112 'p_org_id => ' || TO_CHAR(p_org_id) || g_newline ||
1113 'p_mo_header_id => ' || TO_CHAR(p_mo_header_id)
1114 , l_api_name);
1115 END IF;
1116
1117 UPDATE mtl_material_transactions_temp
1118 SET transaction_batch_id = NULL
1119 , lock_flag = NULL -- newly added
1120 WHERE move_order_header_id = p_mo_header_id;
1121
1122 IF g_consolidate_tasks THEN
1123 UPDATE mtl_material_transactions_temp
1124 SET transaction_batch_id = NULL,
1125 lock_flag = NULL -- bug 9130704
1126 WHERE transaction_temp_id IN
1127 ( SELECT DISTINCT mmtt2.parent_line_id
1128 FROM mtl_material_transactions_temp mmtt2
1129 WHERE move_order_header_id = p_mo_header_id
1130 AND parent_line_id IS NOT NULL
1131 );
1132 END IF;
1133
1134 DELETE wms_pr_workers
1135 WHERE batch_id = p_batch_id
1136 AND organization_id = p_org_id;
1137
1138 COMMIT;
1139
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 IF g_debug = 1 THEN
1143 print_debug ('Other error: ' || SQLERRM, l_api_name);
1144 END IF;
1145 END cleanup_sub_batches;
1146
1147
1148
1149 PROCEDURE release_tasks
1150 ( p_mo_header_id IN NUMBER
1151 , x_return_status OUT NOCOPY VARCHAR2
1152 ) IS
1153 l_api_name VARCHAR2(30) := 'release_tasks';
1154 l_msg_count NUMBER;
1155 l_msg_data VARCHAR2(2000);
1156
1157 BEGIN
1158 x_return_status := fnd_api.g_ret_sts_success;
1159
1160 SAVEPOINT task_release_sp;
1161
1162 IF g_debug = 1 THEN
1163 print_debug( 'Entered with parameters: ' || g_newline ||
1164 'p_mo_header_id => ' || TO_CHAR(p_mo_header_id)
1165 , l_api_name);
1166 END IF;
1167
1168 UPDATE mtl_material_transactions_temp
1169 SET wms_task_status = 1
1170 WHERE move_order_header_id = p_mo_header_id;
1171
1172 IF g_consolidate_tasks THEN
1173 UPDATE mtl_material_transactions_temp
1174 SET wms_task_status = 1
1175 WHERE transaction_temp_id IN
1176 ( SELECT DISTINCT mmtt2.parent_line_id
1177 FROM mtl_material_transactions_temp mmtt2
1178 WHERE move_order_header_id = p_mo_header_id
1179 AND parent_line_id IS NOT NULL
1180 );
1181 END IF;
1182
1183 COMMIT;
1184
1185 EXCEPTION
1186 WHEN fnd_api.g_exc_error THEN
1187 x_return_status := fnd_api.g_ret_sts_error;
1188 ROLLBACK TO task_release_sp;
1189 fnd_msg_pub.count_and_get
1190 ( p_count => l_msg_count
1191 , p_data => l_msg_data
1192 , p_encoded => fnd_api.g_false
1193 );
1194 IF g_debug = 1 THEN
1195 print_debug (l_msg_data, l_api_name);
1196 END IF;
1197
1198 WHEN OTHERS THEN
1199 x_return_status := fnd_api.g_ret_sts_unexp_error;
1200 ROLLBACK TO task_release_sp;
1201 IF g_debug = 1 THEN
1202 print_debug ('Other error: ' || SQLERRM, l_api_name);
1203 END IF;
1204
1205 END release_tasks;
1206
1207
1208
1209 -- Entry point for post-allocation processing APIs
1210 PROCEDURE launch
1211 ( p_organization_id IN NUMBER
1212 , p_mo_header_id IN NUMBER
1213 , p_batch_id IN NUMBER
1214 , p_num_workers IN NUMBER
1215 , p_auto_pick_confirm IN VARCHAR2
1216 , p_wsh_status IN VARCHAR2
1217 , p_wsh_mode IN VARCHAR2
1218 , p_grouping_rule_id IN NUMBER
1219 , p_allow_partial_pick IN VARCHAR2
1220 , p_plan_tasks IN VARCHAR2
1221 , x_return_status OUT NOCOPY VARCHAR2
1222 , x_org_complete OUT NOCOPY VARCHAR2
1223 ) IS
1224 l_api_name VARCHAR2(30) := 'launch';
1225 l_msg_count NUMBER;
1226 l_msg_data VARCHAR2(2000);
1227
1228 l_api_return_status VARCHAR2(1);
1229 l_create_sub_batches VARCHAR2(1);
1230 l_do_post_alloc NUMBER := NVL(FND_PROFILE.VALUE('WMS_ASSIGN_TASK_TYPE'),1);
1231 l_sub_requests VARCHAR2(1) := 'N' ; --BUG10325405
1232
1233
1234 BEGIN
1235 x_return_status := fnd_api.g_ret_sts_success;
1236 x_org_complete := 'N';
1237
1238 IF g_debug = 1 THEN
1239 print_debug( 'Entered with parameters: ' || g_newline ||
1240 'p_organization_id => ' || TO_CHAR(p_organization_id) || g_newline ||
1241 'p_mo_header_id => ' || TO_CHAR(p_mo_header_id) || g_newline ||
1242 'p_batch_id => ' || TO_CHAR(p_batch_id) || g_newline ||
1243 'p_num_workers => ' || TO_CHAR(p_num_workers) || g_newline ||
1244 'p_auto_pick_confirm => ' || p_auto_pick_confirm || g_newline ||
1245 'p_wsh_status => ' || p_wsh_status || g_newline ||
1246 'p_wsh_mode => ' || p_wsh_mode || g_newline ||
1247 'p_grouping_rule_id => ' || TO_CHAR(p_grouping_rule_id) || g_newline ||
1248 'p_allow_partial_pick => ' || p_allow_partial_pick || g_newline ||
1249 'p_plan_tasks => ' || p_plan_tasks
1250 , l_api_name);
1251 END IF;
1252
1253 -- Validations
1254 IF p_organization_id IS NULL OR
1255 p_mo_header_id IS NULL OR
1256 p_batch_id IS NULL
1257 THEN
1258 IF g_debug = 1 THEN
1259 print_debug('Required input is missing.', l_api_name);
1260 END IF;
1261 RAISE fnd_api.g_exc_unexpected_error;
1262 END IF;
1263
1264 IF p_plan_tasks = 'Y' THEN
1265 g_plan_tasks := TRUE;
1266 END IF;
1267
1268 --
1269 -- Run single-threaded post-allocation if needed.
1270 -- Device Integration as well as Label Printing for Pick Release
1271 -- business flow will not work if doing parallel pick release and
1272 -- single-threaded post-allocation. This problem has existed
1273 -- since base R12.
1274 --
1275 IF g_single_threaded OR
1276 p_wsh_mode IS NULL OR
1277 p_num_workers < 2
1278 THEN --{
1279 l_api_return_status := fnd_api.g_ret_sts_success;
1280 inv_pick_release_pub.call_cartonization
1281 ( p_api_version => 1.0
1282 , p_init_msg_list => FND_API.G_FALSE
1283 , p_commit => FND_API.G_FALSE
1284 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1285 , p_out_bound => 'Y'
1286 , p_org_id => p_organization_id
1287 , p_move_order_header_id => p_mo_header_id
1288 , p_grouping_rule_id => p_grouping_rule_id
1289 , p_allow_partial_pick => FND_API.G_TRUE
1290 , x_return_status => l_api_return_status
1291 , x_msg_count => l_msg_count
1292 , x_msg_data => l_msg_data
1293 );
1294 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1295 IF g_debug = 1 THEN
1296 print_debug('Error status from call_cartonization: '
1297 || l_api_return_status, l_api_name);
1298 END IF;
1299 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1300 RAISE fnd_api.g_exc_error;
1301 ELSE
1302 RAISE fnd_api.g_exc_unexpected_error;
1303 END IF;
1304 END IF;
1305
1306 IF NOT g_plan_tasks THEN
1307 l_api_return_status := fnd_api.g_ret_sts_success;
1308 release_tasks
1309 ( p_mo_header_id => p_mo_header_id
1310 , x_return_status => l_api_return_status
1311 );
1312 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1313 IF g_debug = 1 THEN
1314 print_debug('Error status from release_tasks: '
1315 || l_api_return_status, l_api_name);
1316 END IF;
1317 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1318 RAISE fnd_api.g_exc_error;
1319 ELSE
1320 RAISE fnd_api.g_exc_unexpected_error;
1321 END IF;
1322 END IF;
1323 END IF;
1324
1325 x_org_complete := 'Y';
1326 GOTO END_POST_ALLOC;
1327 END IF; --}
1328
1329 -- If doing auto-pick-confirm or if the profile "WMS: Assign Task Types"
1330 -- is set to NO, turn off everything except pick slip numbering
1331 IF p_auto_pick_confirm = 'Y' OR l_do_post_alloc <> 1 THEN
1332 g_assign_op_plans := FALSE;
1333 g_call_cartonization := FALSE;
1334 g_consolidate_tasks := FALSE;
1335 g_assign_task_types := FALSE;
1336 g_process_device_reqs := FALSE;
1337 g_assign_pick_slips := TRUE;
1338 g_plan_tasks := FALSE; -- Over-rides p_plan_tasks
1339 g_print_labels := FALSE;
1340 END IF;
1341
1342 -- Multi threaded execution: Operation Plan assignment (OPA)
1343 IF g_assign_op_plans AND p_wsh_mode IN ('PICK-SS','PICK') THEN --{
1344 l_api_return_status := fnd_api.g_ret_sts_success;
1345
1346 assign_operation_plans
1347 ( p_organization_id => p_organization_id
1348 , p_mo_header_id => p_mo_header_id
1349 , p_batch_id => p_batch_id
1350 , p_num_workers => p_num_workers
1351 , p_create_sub_batches => 'Y'
1352 , p_wsh_status => p_wsh_status
1353 , x_return_status => l_api_return_status
1354 , x_sub_requests => l_sub_requests --BUG10325405
1355 );
1356 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1357 IF g_debug = 1 THEN
1358 print_debug('Error status from assign_operation_plans: '
1359 || l_api_return_status, l_api_name);
1360 END IF;
1361 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1362 RAISE fnd_api.g_exc_error;
1363 ELSE
1364 RAISE fnd_api.g_exc_unexpected_error;
1365 END IF;
1366 END IF;
1367 --
1368 -- Op plan assignment is parallelized, so skip to the end.
1369 -- Conc. req. is PAUSED until the child requests finish.
1370 -- IF..END IF for BUG10325405
1371 IF l_sub_requests = 'Y' THEN
1372 IF g_debug = 1 THEN
1373 print_debug('BUG10325405 before the GOTO after assign_operation_plans call', l_api_name);
1374 END IF;
1375 GOTO END_POST_ALLOC;
1376 END IF;
1377 IF g_debug = 1 THEN
1378 print_debug('BUG10325405 DID NOT GOTO end of post alloc after assign_operation_plans call', l_api_name);
1379 END IF;
1380 END IF; --}
1381
1382 -- Cartonization: single threaded, starts after OPA child requests finish
1383 IF g_call_cartonization AND p_wsh_mode IN ('PICK-SS','PICK','OPA') THEN --{
1384 l_api_return_status := fnd_api.g_ret_sts_success;
1385 wms_postalloc_pvt.cartonize
1386 ( p_org_id => p_organization_id
1387 , p_move_order_header_id => p_mo_header_id
1388 , x_return_status => l_api_return_status
1389 );
1390 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1391 IF g_debug = 1 THEN
1392 print_debug('Error status from wms_postalloc_pvt.cartonize: '
1393 || l_api_return_status, l_api_name);
1394 END IF;
1395 -- Continue processing even if cartonization fails
1396 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1397 -- RAISE fnd_api.g_exc_error;
1398 NULL;
1399 ELSE
1400 -- RAISE fnd_api.g_exc_unexpected_error;
1401 NULL;
1402 END IF;
1403 END IF;
1404 END IF; --}
1405
1406 -- Task consolidation: single threaded
1407 IF g_consolidate_tasks AND p_wsh_mode IN ('PICK-SS','PICK','OPA') THEN --{
1408 l_api_return_status := fnd_api.g_ret_sts_success;
1409 wms_postalloc_pvt.consolidate_tasks
1410 ( p_mo_header_id => p_mo_header_id
1411 , x_return_status => l_api_return_status
1412 );
1413 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1414 IF g_debug = 1 THEN
1415 print_debug('Error status from wms_postalloc_pvt.consolidate_tasks: '
1416 || l_api_return_status, l_api_name);
1417 END IF;
1418 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1419 RAISE fnd_api.g_exc_error;
1420 ELSE
1421 RAISE fnd_api.g_exc_unexpected_error;
1422 END IF;
1423 END IF;
1424 END IF; --}
1425
1426 -- Task Type assignment (TTA): multi-threaded
1427 IF g_assign_task_types AND p_wsh_mode IN ('PICK-SS','PICK','OPA') THEN --{
1428 IF p_wsh_mode = 'OPA' THEN
1429 l_create_sub_batches := 'N';
1430 ELSE
1431 l_create_sub_batches := 'Y';
1432 END IF;
1433 l_api_return_status := fnd_api.g_ret_sts_success;
1434
1435 assign_task_types
1436 ( p_organization_id => p_organization_id
1437 , p_mo_header_id => p_mo_header_id
1438 , p_batch_id => p_batch_id
1439 , p_num_workers => p_num_workers
1440 , p_create_sub_batches => l_create_sub_batches
1441 , p_wsh_status => p_wsh_status
1442 , x_return_status => l_api_return_status
1443 , x_sub_requests => l_sub_requests --BUG10325405
1444 );
1445 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1446 IF g_debug = 1 THEN
1447 print_debug('Error status from assign_task_types: '
1448 || l_api_return_status, l_api_name);
1449 END IF;
1450 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1451 RAISE fnd_api.g_exc_error;
1452 ELSE
1453 RAISE fnd_api.g_exc_unexpected_error;
1454 END IF;
1455 END IF;
1456 -- Skip to end, and PAUSE.
1457 -- IF..END IF for BUG10325405
1458 IF l_sub_requests = 'Y' THEN
1459 IF g_debug = 1 THEN
1460 print_debug('BUG10325405 Before the GOTO call in assign task types', l_api_name);
1461 END IF;
1462 GOTO END_POST_ALLOC;
1463 END IF;
1464 IF g_debug = 1 THEN
1465 print_debug('BUG10325405 DID NOT GOTO end of post allocation after the call in assign task types', l_api_name);
1466 END IF;
1467 END IF; --}
1468
1469 --
1470 -- Resume from here after TTA child requests are complete
1471 --
1472
1473 -- Device integration: single threaded
1474 IF g_process_device_reqs AND p_wsh_mode IN ('PICK-SS','PICK','OPA','TTA') THEN --{
1475 l_api_return_status := fnd_api.g_ret_sts_success;
1476 wms_postalloc_pvt.insert_device_requests
1477 ( p_organization_id => p_organization_id
1478 , p_mo_header_id => p_mo_header_id
1479 , x_return_status => l_api_return_status
1480 );
1481 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1482 IF g_debug = 1 THEN
1483 print_debug('Error status from wms_postalloc_pvt.insert_device_requests: '
1484 || l_api_return_status, l_api_name);
1485 END IF;
1486 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1487 RAISE fnd_api.g_exc_error;
1488 ELSE
1489 RAISE fnd_api.g_exc_unexpected_error;
1490 END IF;
1491 END IF;
1492 END IF; --}
1493
1494 -- Pick slip number assignment: single threaded
1495 IF g_assign_pick_slips AND p_wsh_mode IN ('PICK-SS','PICK','OPA','TTA') THEN --{
1496 l_api_return_status := fnd_api.g_ret_sts_success;
1497 wms_postalloc_pvt.assign_pick_slip_numbers
1498 ( p_organization_id => p_organization_id
1499 , p_mo_header_id => p_mo_header_id
1500 , p_grouping_rule_id => p_grouping_rule_id
1501 , x_return_status => l_api_return_status
1502 );
1503 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1504 IF g_debug = 1 THEN
1505 print_debug('Error status from wms_postalloc_pvt.assign_pick_slip_numbers: '
1506 || l_api_return_status, l_api_name);
1507 END IF;
1508 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1509 RAISE fnd_api.g_exc_error;
1510 ELSE
1511 RAISE fnd_api.g_exc_unexpected_error;
1512 END IF;
1513 END IF;
1514 END IF; --}
1515
1516 -- Clear transaction_batch_id column on MMTT,
1517 -- delete WPR records
1518 cleanup_sub_batches
1519 ( p_org_id => p_organization_id
1520 , p_mo_header_id => p_mo_header_id
1521 , p_batch_id => p_batch_id
1522 );
1523
1524 -- Release tasks: single threaded
1525 IF NOT g_plan_tasks AND p_wsh_mode IN ('PICK-SS','PICK','OPA','TTA') THEN --{
1526 l_api_return_status := fnd_api.g_ret_sts_success;
1527 release_tasks
1528 ( p_mo_header_id => p_mo_header_id
1529 , x_return_status => l_api_return_status
1530 );
1531 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1532 IF g_debug = 1 THEN
1533 print_debug('Error status from release_tasks: '
1534 || l_api_return_status, l_api_name);
1535 END IF;
1536 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1537 RAISE fnd_api.g_exc_error;
1538 ELSE
1539 RAISE fnd_api.g_exc_unexpected_error;
1540 END IF;
1541 END IF;
1542 END IF; --}
1543
1544 -- Label printing: multi-threaded. Parent process is paused
1545 -- only if we are doing auto-pick-confirm
1546 IF g_print_labels AND p_wsh_mode IN ('PICK-SS','PICK','OPA','TTA') THEN --{
1547 l_api_return_status := fnd_api.g_ret_sts_success;
1548
1549 print_labels
1550 ( p_organization_id => p_organization_id
1551 , p_mo_header_id => p_mo_header_id
1552 , p_batch_id => p_batch_id
1553 , p_num_workers => p_num_workers
1554 , p_auto_pick_confirm => p_auto_pick_confirm
1555 , p_create_sub_batches => 'Y'
1556 , p_wsh_status => p_wsh_status
1557 , x_return_status => l_api_return_status
1558 , x_sub_requests => l_sub_requests --BUG10325405
1559 );
1560 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1561 IF g_debug = 1 THEN
1562 print_debug('Error status from print_labels: '
1563 || l_api_return_status, l_api_name);
1564 END IF;
1565 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1566 RAISE fnd_api.g_exc_error;
1567 ELSE
1568 RAISE fnd_api.g_exc_unexpected_error;
1569 END IF;
1570 END IF;
1571 IF g_debug = 1 THEN
1572 print_debug('BUG10325405 Before the GOTO call in label printing', l_api_name);
1573 END IF;
1574 --l_sub_requests for BUG10325405
1575 IF p_auto_pick_confirm = 'Y' and l_sub_requests = 'Y' THEN
1576 GOTO END_POST_ALLOC;
1577 END IF;
1578 IF g_debug = 1 THEN
1579 print_debug('BUG10325405 DID NOT GOTO the end of post allocation in label printing', l_api_name);
1580 END IF;
1581 END IF; --}
1582
1583 --
1584 -- Set org completion flag to 'Y' after all processing completes
1585 -- to let Shipping know that the next org can be processed.
1586 -- Whenever we spawn child requests for OPA, TTA, etc., org_complete
1587 -- remains as 'N' and we skip to the part after END_POST_ALLOC
1588 --
1589 x_org_complete := 'Y';
1590
1591 <<END_POST_ALLOC>>
1592 COMMIT;
1593
1594 EXCEPTION
1595 WHEN fnd_api.g_exc_error THEN
1596 x_return_status := fnd_api.g_ret_sts_error;
1597 fnd_msg_pub.count_and_get
1598 ( p_count => l_msg_count
1599 , p_data => l_msg_data
1600 , p_encoded => fnd_api.g_false
1601 );
1602 IF g_debug = 1 THEN
1603 print_debug (l_msg_data, l_api_name);
1604 END IF;
1605
1606 cleanup_sub_batches
1607 ( p_org_id => p_organization_id
1608 , p_mo_header_id => p_mo_header_id
1609 , p_batch_id => p_batch_id
1610 );
1611
1612 WHEN OTHERS THEN
1613 x_return_status := fnd_api.g_ret_sts_unexp_error;
1614 IF g_debug = 1 THEN
1615 print_debug ('Other error: ' || SQLERRM, l_api_name);
1616 END IF;
1617
1618 cleanup_sub_batches
1619 ( p_org_id => p_organization_id
1620 , p_mo_header_id => p_mo_header_id
1621 , p_batch_id => p_batch_id
1622 );
1623
1624 END launch;
1625
1626
1627
1628 -- Concurrent processing wrapper for post-allocation processing APIs
1629 -- when running post-allocation standalone (outside of pick release)
1630 PROCEDURE process_post_allocation
1631 ( errbuf OUT NOCOPY VARCHAR2
1632 , retcode OUT NOCOPY NUMBER
1633 , p_pickrel_batch IN VARCHAR2
1634 , p_organization_id IN NUMBER
1635 , p_assign_op_plans IN NUMBER
1636 , p_call_cartonization IN NUMBER
1637 , p_consolidate_tasks IN NUMBER
1638 , p_assign_task_types IN NUMBER
1639 , p_process_device_reqs IN NUMBER
1640 , p_assign_pick_slips IN NUMBER
1641 , p_plan_tasks IN NUMBER
1642 , p_print_labels IN NUMBER
1643 , p_wave_simulation_mode IN VARCHAR2
1644 ) IS
1645 l_api_name VARCHAR2(30) := 'process_post_allocation';
1646 l_msg_count NUMBER;
1647 l_msg_data VARCHAR2(2000);
1648
1649 l_api_return_status VARCHAR2(1);
1650 l_conc_ret_status BOOLEAN;
1651 l_error_message VARCHAR2(2000);
1652 l_batch_id NUMBER;
1653 l_mo_header_id NUMBER;
1654 l_auto_pick_confirm VARCHAR2(1);
1655 l_grouping_rule_id NUMBER;
1656 l_request_data VARCHAR2(30);
1657 l_mode VARCHAR2(30);
1658 l_wsh_status VARCHAR2(10);
1659 l_plan_tasks VARCHAR2(1) := 'N';
1660 l_org_complete VARCHAR2(1);
1661 l_dummy VARCHAR2(1);
1662 l_bulk_tasks_exist BOOLEAN;
1663
1664 l_child_task_id t_num_tab;
1665 l_parent_task_id t_num_tab;
1666 l_lot_control_code t_num_tab;
1667 l_serial_control_code t_num_tab;
1668
1669 plan_wave_error BOOLEAN := FALSE;
1670
1671 CURSOR c_existing_bulk_tasks (p_mo_header_id NUMBER) IS
1672 SELECT mmtt.transaction_temp_id
1673 , mmtt.parent_line_id
1674 , msi.lot_control_code
1675 , msi.serial_number_control_code
1676 FROM mtl_material_transactions_temp mmtt
1677 , mtl_system_items msi
1678 WHERE mmtt.move_order_header_id = p_mo_header_id
1679 AND mmtt.parent_line_id IS NOT NULL
1680 AND msi.inventory_item_id = mmtt.inventory_item_id
1681 AND msi.organization_id = mmtt.organization_id;
1682
1683 BEGIN
1684 retcode := 0;
1685
1686 IF g_debug = 1 THEN
1687 print_debug( 'Entered with parameters: ' || g_newline ||
1688 'p_pickrel_batch => ' || p_pickrel_batch || g_newline ||
1689 'p_organization_id => ' || TO_CHAR(p_organization_id) || g_newline ||
1690 'p_assign_op_plans => ' || TO_CHAR(p_assign_op_plans) || g_newline ||
1691 'p_call_cartonization => ' || TO_CHAR(p_call_cartonization) || g_newline ||
1692 'p_consolidate_tasks => ' || TO_CHAR(p_consolidate_tasks) || g_newline ||
1693 'p_assign_task_types => ' || TO_CHAR(p_assign_task_types) || g_newline ||
1694 'p_process_device_reqs => ' || TO_CHAR(p_process_device_reqs) || g_newline ||
1695 'p_assign_pick_slips => ' || TO_CHAR(p_assign_pick_slips) || g_newline ||
1696 'p_plan_tasks => ' || TO_CHAR(p_plan_tasks) || g_newline ||
1697 'p_print_labels => ' || TO_CHAR(p_print_labels) || g_newline ||
1698 'p_wave_simulation_mode => '|| p_wave_simulation_mode
1699 , l_api_name);
1700 END IF;
1701
1702 -- Do not allow standalone post-allocation processing in single-threaded mode
1703 IF g_num_workers < 2 THEN
1704 IF p_wave_simulation_mode = 'Y' THEN
1705 g_num_workers := 3;
1706 ELSE
1707 IF g_debug = 1 THEN
1708 print_debug('Invalid number of workers: ' || g_num_workers, l_api_name);
1709 END IF;
1710 fnd_message.set_name('WMS', 'WMS_PALOC_PARALLEL_ONLY'); -- TBD
1711 fnd_msg_pub.ADD;
1712 RAISE fnd_api.g_exc_error;
1713 END IF;
1714 END IF;
1715
1716 IF p_assign_op_plans = 2 THEN
1717 g_assign_op_plans := FALSE;
1718 END IF;
1719
1720 IF p_call_cartonization = 2 THEN
1721 g_call_cartonization := FALSE;
1722 END IF;
1723
1724 IF p_consolidate_tasks = 2 THEN
1725 g_consolidate_tasks := FALSE;
1726 END IF;
1727
1728 IF p_assign_task_types = 2 THEN
1729 g_assign_task_types := FALSE;
1730 END IF;
1731
1732 IF p_process_device_reqs = 2 THEN
1733 g_process_device_reqs := FALSE;
1734 END IF;
1735
1736 IF p_assign_pick_slips = 2 THEN
1737 g_assign_pick_slips := FALSE;
1738 END IF;
1739
1740 IF p_plan_tasks = 1 THEN
1741 g_plan_tasks := TRUE;
1742 l_plan_tasks := 'Y';
1743 END IF;
1744
1745 IF p_print_labels = 2 THEN
1746 g_print_labels := FALSE;
1747 END IF;
1748
1749 -- Shipping's format of Request_Data is 'Batch_id:Request_Status:Mode'
1750 l_request_data := FND_CONC_GLOBAL.Request_Data;
1751 l_wsh_status := SUBSTR( l_request_data
1752 , INSTR(l_request_data,':',1,1) + 1
1753 , 1);
1754 l_mode := SUBSTR( l_request_data
1755 , INSTR(l_request_data,':',1,2) + 1
1756 , LENGTH(l_request_data));
1757
1758 IF g_debug = 1 THEN
1759 print_debug('l_request_data: ' || l_request_data || g_newline ||
1760 'l_mode: ' || l_mode || g_newline ||
1761 'l_wsh_status: ' || l_wsh_status
1762 , l_api_name);
1763 END IF;
1764
1765 BEGIN
1766 SELECT mtrh.header_id
1767 , mtrh.grouping_rule_id
1768 , DECODE(mp.mo_pick_confirm_required,1,'N','Y')
1769 INTO l_mo_header_id
1770 , l_grouping_rule_id
1771 , l_auto_pick_confirm
1772 FROM mtl_txn_request_headers mtrh
1773 , mtl_parameters mp
1774 WHERE mtrh.request_number = p_pickrel_batch
1775 AND mtrh.organization_id = p_organization_id
1776 AND mtrh.move_order_type = inv_globals.g_move_order_pick_wave
1777 AND mp.organization_id = p_organization_id;
1778 EXCEPTION
1779 WHEN OTHERS THEN
1780 IF g_debug = 1 THEN
1781 print_debug ('Error fetching MO header and Org attributes: '
1782 || l_error_message, l_api_name);
1783 END IF;
1784 RAISE fnd_api.g_exc_unexpected_error;
1785 END;
1786
1787 l_batch_id := TO_NUMBER(p_pickrel_batch);
1788 IF g_debug = 1 THEN
1789 print_debug('l_mo_header_id: ' || TO_CHAR(l_mo_header_id) || g_newline ||
1790 'l_batch_id: ' || TO_CHAR(l_batch_id) || g_newline ||
1791 'l_grouping_rule_id: ' || TO_CHAR(l_grouping_rule_id) || g_newline ||
1792 'l_auto_pick_confirm: ' || l_auto_pick_confirm
1793 , l_api_name);
1794 END IF;
1795
1796 IF l_mode IS NULL AND g_debug = 1 THEN
1797 print_version_info;
1798 END IF;
1799
1800 /* -- Check for conflicts with other running programs
1801 IF l_mode IS NULL THEN --{
1802 BEGIN
1803 SELECT 'x' INTO l_dummy
1804 FROM dual
1805 WHERE EXISTS
1806 ( SELECT 'x'
1807 FROM wms_pr_workers
1808 WHERE organization_id = p_organization_id
1809 AND (batch_id = l_batch_id
1810 OR worker_mode = 'WMSBLKPR')
1811 );
1812 IF g_debug = 1 THEN
1813 print_debug('Other running program(s) found, so return error', l_api_name);
1814 END IF;
1815 fnd_message.set_name('WMS', 'WMS_PALOC_OTHER_PROG'); -- TBD
1816 fnd_msg_pub.ADD;
1817 RAISE fnd_api.g_exc_error;
1818 EXCEPTION
1819 WHEN NO_DATA_FOUND THEN
1820 IF g_debug = 1 THEN
1821 print_debug('No conflicting programs found, so proceeding', l_api_name);
1822 END IF;
1823 WHEN fnd_api.g_exc_error THEN
1824 RAISE fnd_api.g_exc_error;
1825 WHEN OTHERS THEN
1826 IF g_debug = 1 THEN
1827 print_debug( 'Error checking for conc program conflicts: ' || SQLERRM
1828 , l_api_name);
1829 END IF;
1830 RAISE fnd_api.g_exc_unexpected_error;
1831 END;
1832 END IF; --}
1833 */
1834 IF l_mode IS NULL THEN --{
1835 -- Verify that unreleased tasks exist
1836 BEGIN
1837 SELECT 'x' INTO l_dummy
1838 FROM dual
1839 WHERE EXISTS
1840 ( SELECT 'x'
1841 FROM mtl_material_transactions_temp
1842 WHERE move_order_header_id = l_mo_header_id
1843 AND wms_task_status = 8 -- Unreleased
1844 );
1845 IF g_debug = 1 THEN
1846 print_debug('Unreleased tasks exist, so proceed', l_api_name);
1847 END IF;
1848 EXCEPTION
1849 WHEN NO_DATA_FOUND THEN
1850 IF g_debug = 1 THEN
1851 print_debug('No unreleased tasks exist', l_api_name);
1852 END IF;
1853 IF p_wave_simulation_mode = 'Y' THEN
1854 IF g_debug = 1 THEN
1855 print_debug('Running for Wave Planning. Concurrent request completion status will be normal only', l_api_name);
1856 END IF;
1857 plan_wave_error := TRUE;
1858 END IF;
1859 fnd_message.set_name('WMS', 'WMS_PALOC_NO_UNREL_TASKS'); -- TBD
1860 fnd_msg_pub.ADD;
1861 RAISE fnd_api.g_exc_error;
1862 WHEN OTHERS THEN
1863 IF g_debug = 1 THEN
1864 print_debug( 'Error when checking for unreleased tasks: ' || SQLERRM
1865 , l_api_name);
1866 END IF;
1867 RAISE fnd_api.g_exc_unexpected_error;
1868 END;
1869
1870 -- Verify there are no released tasks
1871 BEGIN
1872 SELECT 'x' INTO l_dummy
1873 FROM dual
1874 WHERE EXISTS
1875 ( SELECT 'x'
1876 FROM mtl_material_transactions_temp
1877 WHERE move_order_header_id = l_mo_header_id
1878 AND wms_task_status = 1 -- Released
1879 );
1880 IF g_debug = 1 THEN
1881 print_debug('Released tasks exist, so return error', l_api_name);
1882 END IF;
1883 fnd_message.set_name('WMS', 'WMS_PALOC_RLSD_TASK'); -- TBD
1884 fnd_msg_pub.ADD;
1885 RAISE fnd_api.g_exc_error;
1886 EXCEPTION
1887 WHEN NO_DATA_FOUND THEN
1888 IF g_debug = 1 THEN
1889 print_debug('No released tasks, proceed', l_api_name);
1890 END IF;
1891 WHEN fnd_api.g_exc_error THEN
1892 RAISE fnd_api.g_exc_error;
1893 WHEN OTHERS THEN
1894 IF g_debug = 1 THEN
1895 print_debug( 'Error when checking for released tasks: ' || SQLERRM
1896 , l_api_name);
1897 END IF;
1898 RAISE fnd_api.g_exc_unexpected_error;
1899 END;
1900 END IF; --}
1901
1902 --
1903 -- If first time, and bulk picking is turned on,
1904 -- delete pre-existing bulk pick suggestions
1905 --
1906 IF l_mode IS NULL AND g_consolidate_tasks THEN --{
1907 l_bulk_tasks_exist := FALSE;
1908 BEGIN
1909 SELECT 'x' INTO l_dummy FROM dual
1910 WHERE EXISTS
1911 ( SELECT 'x' FROM mtl_material_transactions_temp
1912 WHERE move_order_header_id = l_mo_header_id
1913 AND parent_line_id IS NOT NULL
1914 );
1915 l_bulk_tasks_exist := TRUE;
1916 IF g_debug = 1 THEN
1917 print_debug('Found existing bulk pick suggestions, deleting..', l_api_name);
1918 END IF;
1919 EXCEPTION
1920 WHEN NO_DATA_FOUND THEN NULL;
1921 WHEN OTHERS THEN
1922 IF (g_debug = 1) THEN
1923 print_debug( 'Other error when checking if bulk tasks already exist: '
1924 || sqlerrm, l_api_name );
1925 END IF;
1926 RAISE fnd_api.g_exc_unexpected_error;
1927 END;
1928
1929 IF l_bulk_tasks_exist THEN --{
1930 OPEN c_existing_bulk_tasks(l_mo_header_id);
1931 LOOP --{
1932 FETCH c_existing_bulk_tasks BULK COLLECT INTO
1933 l_child_task_id
1934 , l_parent_task_id
1935 , l_lot_control_code
1936 , l_serial_control_code LIMIT g_bulk_fetch_limit;
1937
1938 EXIT WHEN l_child_task_id.COUNT = 0;
1939
1940 FORALL ll IN l_child_task_id.FIRST .. l_child_task_id.LAST
1941 UPDATE mtl_material_transactions_temp
1942 SET parent_line_id = NULL
1943 WHERE transaction_temp_id = l_child_task_id(ll);
1944
1945 FOR ii IN l_child_task_id.FIRST .. l_child_task_id.LAST LOOP --{
1946 l_api_return_status := fnd_api.g_ret_sts_success;
1947 inv_trx_util_pub.update_parent_mmtt
1948 ( x_return_status => l_api_return_status
1949 , p_parent_line_id => l_parent_task_id(ii)
1950 , p_child_line_id => l_child_task_id(ii)
1951 , p_lot_control_code => l_lot_control_code(ii)
1952 , p_serial_control_code => l_serial_control_code(ii)
1953 );
1954 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1955 IF g_debug = 1 THEN
1956 print_debug('Error status from inv_trx_util_pub.update_parent_mmtt: '
1957 || l_api_return_status, l_api_name);
1958 END IF;
1959 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1960 RAISE fnd_api.g_exc_error;
1961 ELSE
1962 RAISE fnd_api.g_exc_unexpected_error;
1963 END IF;
1964 END IF;
1965 END LOOP; --}
1966 END LOOP; --}
1967 l_lot_control_code.DELETE;
1968 l_serial_control_code.DELETE;
1969 END IF; --}
1970
1971 COMMIT;
1972 END IF; --}
1973
1974 --
1975 -- If first time, and cartonization is turned on,
1976 -- delete pre-existing cartonization suggestions
1977 --
1978 IF l_mode IS NULL AND g_call_cartonization THEN --{
1979 UPDATE mtl_material_transactions_temp
1980 SET cartonization_id = NULL
1981 , container_item_id = NULL
1982 WHERE move_order_header_id = l_mo_header_id
1983 AND cartonization_id IS NOT NULL;
1984
1985 IF g_debug = 1 and SQL%ROWCOUNT > 0 THEN
1986 print_debug('Cleared existing cartonization suggestions', l_api_name);
1987 END IF;
1988
1989 -- This is required because of the unique index WMS_PACKAGING_HIST_U1
1990 -- (header_id + sequence_id + packaging_mode)
1991 DELETE wms_packaging_hist
1992 WHERE header_id = l_mo_header_id
1993 AND packaging_mode = wms_cartnzn_pub.pr_pkg_mode;
1994
1995 IF g_debug = 1 and SQL%ROWCOUNT > 0 THEN
1996 print_debug('Cleared WMS_PACKAGING_HIST records', l_api_name);
1997 END IF;
1998
1999 COMMIT;
2000 END IF; --}
2001
2002 l_api_return_status := fnd_api.g_ret_sts_success;
2003 launch
2004 ( p_organization_id => p_organization_id
2005 , p_mo_header_id => l_mo_header_id
2006 , p_batch_id => l_batch_id
2007 , p_num_workers => g_num_workers
2008 , p_auto_pick_confirm => l_auto_pick_confirm
2009 , p_wsh_status => NVL(l_wsh_status,'0')
2010 , p_wsh_mode => NVL(l_mode,'PICK')
2011 , p_grouping_rule_id => l_grouping_rule_id
2012 , p_allow_partial_pick => fnd_api.g_true
2013 , p_plan_tasks => l_plan_tasks
2014 , x_return_status => l_api_return_status
2015 , x_org_complete => l_org_complete
2016 );
2017 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2018 IF g_debug = 1 THEN
2019 print_debug('Error status from launch: '
2020 || l_api_return_status, l_api_name);
2021 END IF;
2022 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
2023 RAISE fnd_api.g_exc_error;
2024 ELSE
2025 RAISE fnd_api.g_exc_unexpected_error;
2026 END IF;
2027 END IF;
2028
2029 EXCEPTION
2030 WHEN fnd_api.g_exc_error THEN
2031 fnd_msg_pub.count_and_get
2032 ( p_count => l_msg_count
2033 , p_data => l_msg_data
2034 , p_encoded => fnd_api.g_false
2035 );
2036 IF g_debug = 1 THEN
2037 print_debug (l_msg_data, l_api_name);
2038 END IF;
2039
2040 IF plan_wave_error = FALSE THEN
2041 l_conc_ret_status := fnd_concurrent.set_completion_status('ERROR', l_msg_data);
2042 retcode := 2;
2043 errbuf := l_msg_data;
2044 end if;
2045
2046 WHEN OTHERS THEN
2047 l_error_message := SQLERRM;
2048 IF g_debug = 1 THEN
2049 print_debug ('Other error: ' || l_error_message, l_api_name);
2050 END IF;
2051
2052 l_conc_ret_status := fnd_concurrent.set_completion_status('ERROR', l_error_message);
2053 retcode := 2;
2054 errbuf := l_error_message;
2055 END process_post_allocation;
2056
2057 END wms_post_allocation;