[Home] [Help]
PACKAGE BODY: APPS.WMS_BULK_PICK
Source
1 PACKAGE BODY wms_bulk_pick AS
2 /* $Header: WMSBKPIB.pls 120.6.12020000.4 2013/02/07 19:36:11 raminoch ship $*/
3
4 --
5 -- File : WMSBKPIS.pls
6 -- Content : WMS_bulk_pick package specification
7 -- Description : WMS bulk picking API for mobile application
8 -- Notes :
9 -- Modified : 07/30/2003 jali created
10 g_pkg_name CONSTANT VARCHAR2(30) := 'wms_bulk_pick';
11 g_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
12 g_newline CONSTANT VARCHAR2(10) := fnd_global.newline;
13
14 -- Bug# 4185621: added global variable to be used later
15 l_g_task_loaded CONSTANT NUMBER := 4;
16
17
18 PROCEDURE mydebug(p_msg IN VARCHAR2, p_api_name IN VARCHAR2) IS
19 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
20 BEGIN
21 -- dbms_output.put_line(p_msg);
22 IF g_trace_on = 1 THEN
23 inv_mobile_helper_functions.tracelog( p_err_msg => p_msg
24 , p_module => 'WMS_BULK_PICK.' || p_api_name
25 , p_level => 4
26 );
27 END IF;
28 END;
29
30
31 --
32 PROCEDURE wms_concurrent_bulk_process(
33 errbuf OUT NOCOPY VARCHAR2
34 ,retcode OUT NOCOPY NUMBER
35 ,p_organization_id IN NUMBER
36 ,p_start_mo_request_number IN VARCHAR2 :=null
37 ,p_end_mo_request_number IN VARCHAR2 :=null
38 ,p_start_release_date IN VARCHAR2 :=null
39 ,p_end_release_date IN VARCHAR2 :=null
40 ,p_subinventory_code IN VARCHAR2 :=null
41 ,p_item_id IN NUMBER := null
42 ,p_delivery_id IN NUMBER := null
43 ,p_trip_id IN NUMBER := null
44 ,p_only_sub_item IN NUMBER := null
45 ) IS
46
47 l_bulk_input wms_bulk_pick.bulk_input_rec;
48 l_return_status varchar2(1);
49 l_msg_count NUMBER;
50 l_msg_data VARCHAR2(2000);
51 l_api_name CONSTANT VARCHAR2(30) := 'wms_concurrent_bulk_process';
52 l_api_version CONSTANT NUMBER := 1.0;
53 ret BOOLEAN;
54 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
55
56 BEGIN
57
58 -- Initialize API return status to success
59 l_return_status := fnd_api.g_ret_sts_success;
60 -- Start API body
61
62 IF l_debug=1 THEN
63 mydebug('Input parameters:' ||g_newline||
67 'p_start_release_date '||p_start_release_date ||g_newline||
64 'p_organizaton_id '||p_organization_id ||g_newline||
65 'p_start_mo_request_number '||p_start_mo_request_number ||g_newline||
66 'p_end_mo_request_number ' ||p_end_mo_request_number ||g_newline||
68 'p_end_release_date '||p_end_release_date ||g_newline||
69 'p_subinventory_code ' || p_subinventory_code ||g_newline||
70 'p_item_id ' || p_item_id ||g_newline||
71 'p_delivery_id ' || p_delivery_id ||g_newline||
72 'p_trip_id ' || p_trip_id ||g_newline||
73 'p_only_sub_item' || p_only_sub_item
74 ,l_api_name);
75 END IF;
76 l_bulk_input.organization_id := p_organization_id;
77 l_bulk_input.start_mo_request_number := p_start_mo_request_number;
78 l_bulk_input.end_mo_request_number := p_end_mo_request_number;
79 l_bulk_input.start_release_date :=FND_DATE.canonical_to_date(p_start_release_date);
80 l_bulk_input.end_release_date :=FND_DATE.canonical_to_date(p_end_release_date);
81 l_bulk_input.subinventory_code :=p_subinventory_code;
82 l_bulk_input.item_id := p_item_id;
83 l_bulk_input.delivery_id := p_delivery_id;
84 l_bulk_input.trip_id := p_trip_id;
85 l_bulk_input.only_sub_item := p_only_sub_item;
86
87 SAVEPOINT concurrent_bulk_process;
88 -- calling cartonize API to do the bulking
89 wms_cartnzn_pub.cartonize(p_api_version => 1.0
90 ,x_return_status => l_return_status
91 ,x_msg_count => l_msg_count
92 ,x_msg_data => l_msg_data
93 ,p_out_bound => 'Y'
94 ,p_org_id => p_organization_id
95 ,p_move_order_header_id => -1 -- -1 to indicate this is come from bulk concurrent program
96 ,p_disable_cartonization => 'Y'
97 ,p_transaction_header_id => 0 -- default
98 ,p_input_for_bulk => l_bulk_input);
99 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
100 ret := fnd_concurrent.set_completion_status('NORMAL', l_msg_data);
101 retcode := 0;
102 ELSE
103 ret := fnd_concurrent.set_completion_status('ERROR', l_msg_data);
104 retcode := 2;
105 errbuf := l_msg_data;
106 END IF;
107
108 -- Standard call to get message count and if count is 1,
109 -- get message info.
110 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
111 EXCEPTION
112 WHEN fnd_api.g_exc_error THEN
113 ROLLBACK TO concurrent_bulk_process;
114 l_return_status := fnd_api.g_ret_sts_error;
115 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
116 WHEN fnd_api.g_exc_unexpected_error THEN
117 ROLLBACK TO concurrent_bulk_process;
118 l_return_status := fnd_api.g_ret_sts_unexp_error;
119 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
120 WHEN OTHERS THEN
121 ROLLBACK TO concurrent_bulk_process;
122 l_return_status := fnd_api.g_ret_sts_unexp_error;
123
124 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
125 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
126 END IF;
127
128 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
129
130 END wms_concurrent_bulk_process;
131
132 PROCEDURE split_child_mmtt(p_child_temp_id NUMBER,
133 p_new_child_temp_id NUMBER,
134 p_split_pri_qty NUMBER,
135 p_split_sec_qty NUMBER) --bug 8197506
136 IS
137 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
138 -- starts bug 8197506
139 l_api_name CONSTANT VARCHAR2(30) := 'split_child_mmtt';
140 BEGIN
141 -- 8197506
142
143 IF l_debug=1 THEN
144 mydebug('Input parameters:' ||g_newline||
145 'p_child_temp_id '||p_child_temp_id ||g_newline||
146 'primary_quantity '||p_split_pri_qty ||g_newline||
147 'p_new_child_temp_id'||p_new_child_temp_id ||g_newline||
148 'p_split_sec_qty'||p_split_sec_qty ,l_api_name);
149 END IF;
150 -- 8197506
151 INSERT INTO mtl_material_transactions_temp
152 (
153 transaction_header_id
154 , transaction_temp_id
155 , source_code
156 , source_line_id
157 , transaction_mode
158 , lock_flag
159 , last_update_date
160 , last_updated_by
161 , creation_date
162 , created_by
163 , last_update_login
164 , request_id
165 , program_application_id
166 , program_id
167 , program_update_date
168 , inventory_item_id
169 , revision
170 , organization_id
171 , subinventory_code
172 , locator_id
173 , transaction_quantity
174 , primary_quantity
175 , secondary_transaction_quantity -- BUG 8197506
176 , secondary_uom_code -- bug 8197506
180 , transaction_action_id
177 , transaction_uom
178 , transaction_cost
179 , transaction_type_id
181 , transaction_source_type_id
182 , transaction_source_id
183 , transaction_source_name
184 , transaction_date
185 , acct_period_id
186 , distribution_account_id
187 , transaction_reference
188 , requisition_line_id
189 , requisition_distribution_id
190 , reason_id
191 , lot_number
192 , lot_expiration_date
193 , serial_number
194 , receiving_document
195 , demand_id
196 , rcv_transaction_id
197 , move_transaction_id
198 , completion_transaction_id
199 , wip_entity_type
200 , schedule_id
201 , repetitive_line_id
202 , employee_code
203 , primary_switch
204 , schedule_update_code
205 , setup_teardown_code
206 , item_ordering
207 , negative_req_flag
208 , operation_seq_num
209 , picking_line_id
210 , trx_source_line_id
211 , trx_source_delivery_id
212 , physical_adjustment_id
213 , cycle_count_id
214 , rma_line_id
215 , customer_ship_id
216 , currency_code
217 , currency_conversion_rate
218 , currency_conversion_type
219 , currency_conversion_date
220 , ussgl_transaction_code
221 , vendor_lot_number
222 , encumbrance_account
223 , encumbrance_amount
224 , ship_to_location
225 , shipment_number
226 , transfer_cost
227 , transportation_cost
228 , transportation_account
229 , freight_code
230 , containers
231 , waybill_airbill
232 , expected_arrival_date
233 , transfer_subinventory
234 , transfer_organization
235 , transfer_to_location
236 , new_average_cost
237 , value_change
238 , percentage_change
239 , material_allocation_temp_id
240 , demand_source_header_id
241 , demand_source_line
242 , demand_source_delivery
243 , item_segments
244 , item_description
245 , item_trx_enabled_flag
246 , item_location_control_code
247 , item_restrict_subinv_code
248 , item_restrict_locators_code
249 , item_revision_qty_control_code
250 , item_primary_uom_code
251 , item_uom_class
252 , item_shelf_life_code
253 , item_shelf_life_days
254 , item_lot_control_code
255 , item_serial_control_code
256 , item_inventory_asset_flag
257 , allowed_units_lookup_code
258 , department_id
259 , department_code
260 , wip_supply_type
261 , supply_subinventory
262 , supply_locator_id
263 , valid_subinventory_flag
264 , valid_locator_flag
265 , locator_segments
266 , current_locator_control_code
267 , number_of_lots_entered
268 , wip_commit_flag
269 , next_lot_number
270 , lot_alpha_prefix
271 , next_serial_number
272 , serial_alpha_prefix
273 , shippable_flag
274 , posting_flag
275 , required_flag
276 , process_flag
277 , ERROR_CODE
278 , error_explanation
279 , attribute_category
280 , attribute1
281 , attribute2
282 , attribute3
283 , attribute4
284 , attribute5
285 , attribute6
286 , attribute7
287 , attribute8
288 , attribute9
289 , attribute10
290 , attribute11
291 , attribute12
292 , attribute13
293 , attribute14
294 , attribute15
295 , movement_id
296 , reservation_quantity
297 , shipped_quantity
298 , transaction_line_number
299 , task_id
300 , to_task_id
301 , source_task_id
305 , to_project_id
302 , project_id
303 , source_project_id
304 , pa_expenditure_org_id
306 , expenditure_type
307 , final_completion_flag
308 , transfer_percentage
309 , transaction_sequence_id
310 , material_account
311 , material_overhead_account
312 , resource_account
313 , outside_processing_account
314 , overhead_account
315 , flow_schedule
316 , cost_group_id
317 , demand_class
318 , qa_collection_id
319 , kanban_card_id
320 , overcompletion_transaction_id
321 , overcompletion_primary_qty
322 , overcompletion_transaction_qty
323 , end_item_unit_number
324 , scheduled_payback_date
325 , line_type_code
326 , parent_transaction_temp_id
327 , put_away_strategy_id
328 , put_away_rule_id
329 , pick_strategy_id
330 , pick_rule_id
331 , common_bom_seq_id
332 , common_routing_seq_id
333 , cost_type_id
334 , org_cost_group_id
335 , move_order_line_id
336 , task_group_id
337 , pick_slip_number
338 , reservation_id
339 , transaction_status
340 , transfer_cost_group_id
341 , lpn_id
342 , transfer_lpn_id
343 , content_lpn_id
344 , cartonization_id
345 , standard_operation_id
346 , wms_task_type
347 , task_priority
348 , container_item_id
349 , operation_plan_id
350 , parent_line_id
351 , serial_allocated_flag
352 , move_order_header_id
353 , wms_task_status -- Bug# 4185621
354 )
355 (SELECT transaction_header_id
356 , p_new_child_temp_id
357 , source_code
358 , source_line_id
359 , transaction_mode
360 , lock_flag
361 , SYSDATE
362 , last_updated_by
363 , SYSDATE
364 , created_by
365 , last_update_login
366 , request_id
367 , program_application_id
368 , program_id
369 , program_update_date
370 , inventory_item_id
371 , revision
372 , organization_id
373 , subinventory_code
374 , locator_id
375 , p_split_pri_qty
376 , p_split_pri_qty -- only the primary UOM is used
377 , p_split_sec_qty -- BUG 8197506
378 , secondary_uom_code -- BUG 8197506
379 , item_primary_uom_code -- transaction_uom
380 , transaction_cost
381 , transaction_type_id
382 , transaction_action_id
383 , transaction_source_type_id
384 , transaction_source_id
385 , transaction_source_name
386 , transaction_date
387 , acct_period_id
388 , distribution_account_id
389 , transaction_reference
390 , requisition_line_id
391 , requisition_distribution_id
392 , reason_id
393 , lot_number
394 , lot_expiration_date
395 , serial_number
396 , receiving_document
397 , demand_id
398 , rcv_transaction_id
399 , move_transaction_id
400 , completion_transaction_id
401 , wip_entity_type
402 , schedule_id
403 , repetitive_line_id
404 , employee_code
405 , primary_switch
406 , schedule_update_code
407 , setup_teardown_code
408 , item_ordering
409 , negative_req_flag
410 , operation_seq_num
411 , picking_line_id
412 , trx_source_line_id
413 , trx_source_delivery_id
414 , physical_adjustment_id
415 , cycle_count_id
416 , rma_line_id
417 , customer_ship_id
418 , currency_code
419 , currency_conversion_rate
420 , currency_conversion_type
421 , currency_conversion_date
422 , ussgl_transaction_code
423 , vendor_lot_number
424 , encumbrance_account
425 , encumbrance_amount
426 , ship_to_location
427 , shipment_number
428 , transfer_cost
432 , containers
429 , transportation_cost
430 , transportation_account
431 , freight_code
433 , waybill_airbill
434 , expected_arrival_date
435 , transfer_subinventory
436 , transfer_organization
437 , transfer_to_location
438 , new_average_cost
439 , value_change
440 , percentage_change
441 , material_allocation_temp_id
442 , demand_source_header_id
443 , demand_source_line
444 , demand_source_delivery
445 , item_segments
446 , item_description
447 , item_trx_enabled_flag
448 , item_location_control_code
449 , item_restrict_subinv_code
450 , item_restrict_locators_code
451 , item_revision_qty_control_code
452 , item_primary_uom_code
453 , item_uom_class
454 , item_shelf_life_code
455 , item_shelf_life_days
456 , item_lot_control_code
457 , item_serial_control_code
458 , item_inventory_asset_flag
459 , allowed_units_lookup_code
460 , department_id
461 , department_code
462 , wip_supply_type
463 , supply_subinventory
464 , supply_locator_id
465 , valid_subinventory_flag
466 , valid_locator_flag
467 , locator_segments
468 , current_locator_control_code
469 , number_of_lots_entered
470 , wip_commit_flag
471 , next_lot_number
472 , lot_alpha_prefix
473 , next_serial_number
474 , serial_alpha_prefix
475 , shippable_flag
476 , 'Y' --posting_flag Bug#4185621: make sure new child mmtt is posting
477 , required_flag
478 , process_flag
479 , ERROR_CODE
480 , error_explanation
481 , attribute_category
482 , attribute1
483 , attribute2
484 , attribute3
485 , attribute4
486 , attribute5
487 , attribute6
488 , attribute7
489 , attribute8
490 , attribute9
491 , attribute10
492 , attribute11
493 , attribute12
494 , attribute13
495 , attribute14
496 , attribute15
497 , movement_id
498 , reservation_quantity
499 , shipped_quantity
500 , transaction_line_number
501 , task_id
502 , to_task_id
503 , source_task_id
504 , project_id
505 , source_project_id
506 , pa_expenditure_org_id
507 , to_project_id
508 , expenditure_type
509 , final_completion_flag
510 , transfer_percentage
511 , transaction_sequence_id
512 , material_account
513 , material_overhead_account
514 , resource_account
515 , outside_processing_account
516 , overhead_account
517 , flow_schedule
518 , cost_group_id
519 , demand_class
520 , qa_collection_id
521 , kanban_card_id
522 , overcompletion_transaction_id
523 , overcompletion_primary_qty
524 , overcompletion_transaction_qty
525 , end_item_unit_number
526 , scheduled_payback_date
527 , line_type_code
528 , parent_transaction_temp_id
529 , put_away_strategy_id
530 , put_away_rule_id
531 , pick_strategy_id
532 , pick_rule_id
533 , common_bom_seq_id
534 , common_routing_seq_id
535 , cost_type_id
536 , org_cost_group_id
537 , move_order_line_id
538 , task_group_id
539 , pick_slip_number
540 , reservation_id
541 , transaction_status
542 , transfer_cost_group_id
543 , lpn_id
544 , transfer_lpn_id
545 , content_lpn_id
546 , cartonization_id
547 , standard_operation_id
548 , wms_task_type
549 , task_priority
550 , container_item_id
551 , operation_plan_id
552 , parent_line_id
553 , serial_allocated_flag
554 , move_order_line_id
555 , l_g_task_loaded -- Bug# 4185621: loaded status
556 FROM mtl_material_transactions_temp
557 WHERE transaction_temp_id = p_child_temp_id);
558
559 -- update the old line with remaining qty
560 update mtl_material_transactions_temp
564 where transaction_temp_id = p_child_temp_id;
561 set primary_quantity = primary_quantity-p_split_pri_qty, -- UOM
562 secondary_transaction_quantity = decode(secondary_transaction_quantity,NULL,NULL,secondary_transaction_quantity-p_split_sec_qty), --bug 8197506
563 transaction_quantity = transaction_quantity-p_split_pri_qty
565
566
567
568 END split_child_mmtt;
569
570 PROCEDURE split_child_mtlt(p_child_temp_id NUMBER,
571 p_new_child_temp_id NUMBER,
572 p_new_serial_temp_id NUMBER,
573 p_split_pri_qty NUMBER,
574 p_child_lot_number VARCHAR2, --v1 Bug 3902766
575 p_split_sec_qty NUMBER) --bug 8197506
576 IS
577 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
578 -- 8197506
579 l_api_name CONSTANT VARCHAR2(30) := 'split_child_mtlt';
580 BEGIN
581 -- 8197506
582 IF l_debug=1 THEN
583 mydebug('MTLT Input parameters:' ||g_newline||
584 'p_child_temp_id '||p_child_temp_id ||g_newline||
585 'primary_quantity '||p_split_pri_qty ||g_newline||
586 'p_new_child_temp_id'||p_new_child_temp_id ||g_newline||
587 'p_split_sec_qty'||p_split_sec_qty ,l_api_name);
588 END IF;
589 -- 8197506
590 -- insert into mtlt and update the qty for old line
591 INSERT INTO mtl_transaction_lots_temp
592 (
593 transaction_temp_id
594 , last_update_date
595 , last_updated_by
596 , creation_date
597 , created_by
598 , last_update_login
599 , request_id
600 , program_application_id
601 , program_id
602 , program_update_date
603 , transaction_quantity
604 , primary_quantity
605 , secondary_quantity -- BUG 8197506
606 , secondary_unit_of_measure -- bug 8197506
607 , lot_number
608 , lot_expiration_date
609 , ERROR_CODE
610 , serial_transaction_temp_id
611 , group_header_id
612 , put_away_rule_id
613 , pick_rule_id
614 , description
615 , vendor_id
616 , supplier_lot_number
617 , territory_code
618 , --country_of_origin,
619 origination_date
620 , date_code
621 , grade_code
622 , change_date
623 , maturity_date
624 , status_id
625 , retest_date
626 , age
627 , item_size
628 , color
629 , volume
630 , volume_uom
631 , place_of_origin
632 , --kill_date,
633 best_by_date
634 , LENGTH
635 , length_uom
636 , recycled_content
637 , thickness
638 , thickness_uom
639 , width
640 , width_uom
641 , curl_wrinkle_fold
642 , lot_attribute_category
643 , c_attribute1
644 , c_attribute2
645 , c_attribute3
646 , c_attribute4
647 , c_attribute5
648 , c_attribute6
649 , c_attribute7
650 , c_attribute8
651 , c_attribute9
652 , c_attribute10
653 , c_attribute11
654 , c_attribute12
655 , c_attribute13
656 , c_attribute14
657 , c_attribute15
658 , c_attribute16
659 , c_attribute17
660 , c_attribute18
661 , c_attribute19
662 , c_attribute20
663 , d_attribute1
664 , d_attribute2
665 , d_attribute3
666 , d_attribute4
667 , d_attribute5
668 , d_attribute6
669 , d_attribute7
670 , d_attribute8
671 , d_attribute9
672 , d_attribute10
673 , n_attribute1
674 , n_attribute2
675 , n_attribute3
676 , n_attribute4
677 , n_attribute5
678 , n_attribute6
679 , n_attribute7
680 , n_attribute8
681 , n_attribute9
682 , n_attribute10
683 , vendor_name
684 )
685 (SELECT
686 p_new_child_temp_id
687 , last_update_date
688 , last_updated_by
689 , creation_date
690 , created_by
691 , last_update_login
692 , request_id
693 , program_application_id
694 , program_id
695 , program_update_date
696 , p_split_pri_qty
697 , p_split_pri_qty
698 , p_split_sec_qty -- BUG 8197506
699 , secondary_unit_of_measure -- BUG 8197506
700 , lot_number
701 , lot_expiration_date
702 , ERROR_CODE
703 , p_new_serial_temp_id
704 , group_header_id
708 , vendor_id
705 , put_away_rule_id
706 , pick_rule_id
707 , description
709 , supplier_lot_number
710 , territory_code
711 , --country_of_origin,
712 origination_date
713 , date_code
714 , grade_code
715 , change_date
716 , maturity_date
717 , status_id
718 , retest_date
719 , age
720 , item_size
721 , color
722 , volume
723 , volume_uom
724 , place_of_origin
725 , --kill_date,
726 best_by_date
727 , LENGTH
728 , length_uom
729 , recycled_content
730 , thickness
731 , thickness_uom
732 , width
733 , width_uom
734 , curl_wrinkle_fold
735 , lot_attribute_category
736 , c_attribute1
737 , c_attribute2
738 , c_attribute3
739 , c_attribute4
740 , c_attribute5
741 , c_attribute6
742 , c_attribute7
743 , c_attribute8
744 , c_attribute9
745 , c_attribute10
746 , c_attribute11
747 , c_attribute12
748 , c_attribute13
749 , c_attribute14
750 , c_attribute15
751 , c_attribute16
752 , c_attribute17
753 , c_attribute18
754 , c_attribute19
755 , c_attribute20
756 , d_attribute1
757 , d_attribute2
758 , d_attribute3
759 , d_attribute4
760 , d_attribute5
761 , d_attribute6
762 , d_attribute7
763 , d_attribute8
764 , d_attribute9
765 , d_attribute10
766 , n_attribute1
767 , n_attribute2
768 , n_attribute3
769 , n_attribute4
770 , n_attribute5
771 , n_attribute6
772 , n_attribute7
773 , n_attribute8
774 , n_attribute9
775 , n_attribute10
776 , vendor_name
777 FROM mtl_transaction_lots_temp
778 WHERE transaction_temp_id = p_child_temp_id
779 AND lot_number = p_child_lot_number); --v1 Bug 3902766
780
781 -- update the old line with remaining qty
782 update mtl_transaction_lots_temp
783 set primary_quantity = primary_quantity-p_split_pri_qty, -- UOM
784 secondary_quantity = decode(secondary_quantity,NULL,NULL,secondary_quantity-p_split_sec_qty), --bug 8197506
785 transaction_quantity = transaction_quantity-p_split_pri_qty -- UOM
786 where transaction_temp_id = p_child_temp_id
787 and lot_number = p_child_lot_number; --v1 Bug 3902766
788
789 END split_child_mtlt;
790
791 PROCEDURE update_child(p_child_temp_id NUMBER,
792 p_parent_temp_id NUMBER,
793 p_new_txn_hdr_id NUMBER) IS
794 l_parent_uom VARCHAR2(10);
795 l_child_uom VARCHAR2(10);
796
797 l_parent_sub_code VARCHAR2(30);
798 l_parent_loc_id NUMBER;
799 l_lpn_id NUMBER;
800 l_transfer_lpn_id NUMBER;
801 l_api_name VARCHAR2(32):= 'update_child';
802 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
803
804 BEGIN
805 IF l_debug=1 THEN
806 mydebug('get parent line info, parent temp id:'||p_parent_temp_id,l_api_name);
807 END IF;
808
809 -- get the information in parent
810 select transfer_lpn_id,nvl(lpn_id,content_lpn_id),subinventory_code,locator_id, transaction_uom
811 into l_transfer_lpn_id,l_lpn_id,l_parent_sub_code,l_parent_loc_id,l_parent_uom
812 from mtl_material_transactions_temp
813 where transaction_temp_id = p_parent_temp_id;
814
815 IF p_child_temp_id is not null THEN
816 IF l_debug=1 THEN
817 mydebug('update child line '||p_child_temp_id||' with parent info and new header id '||p_new_txn_hdr_id,l_api_name);
818 END IF;
819
820 -- update child line with the correct parent info
821 UPDATE mtl_material_transactions_temp mmtt
822 SET mmtt.transaction_header_id = p_new_txn_hdr_id
823 , mmtt.transfer_lpn_id = l_transfer_lpn_id
824 , mmtt.lpn_id = l_lpn_id
825 , mmtt.parent_line_id = p_parent_temp_id
826 , mmtt.subinventory_code = l_parent_sub_code
827 , mmtt.locator_id = l_parent_loc_id
828 , mmtt.transaction_uom = mmtt.item_primary_uom_code
829 , mmtt.transaction_quantity = mmtt.primary_quantity
830 , mmtt.last_update_date = SYSDATE
831 , mmtt.last_updated_by = FND_GLOBAL.USER_ID
832 , mmtt.posting_flag = 'Y' -- Bug# 4185621: make sure child line mmtt is now posting
833 , mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line mmtt task status is loaded
834 WHERE mmtt.transaction_temp_id = p_child_temp_id;
835 ELSE
836 -- update all child lines with the correct parent info
837 IF l_debug=1 THEN
838 mydebug('update all child lines with parent info and new header id '||p_new_txn_hdr_id,l_api_name);
839 END IF;
840 UPDATE mtl_material_transactions_temp mmtt
841 SET mmtt.transaction_header_id = p_new_txn_hdr_id
845 , mmtt.subinventory_code = l_parent_sub_code
842 , mmtt.transfer_lpn_id = l_transfer_lpn_id
843 , mmtt.lpn_id = l_lpn_id
844 , mmtt.parent_line_id = p_parent_temp_id
846 , mmtt.locator_id = l_parent_loc_id
847 , mmtt.transaction_uom = mmtt.item_primary_uom_code
848 , mmtt.transaction_quantity = mmtt.primary_quantity
849 , mmtt.last_update_date = SYSDATE
850 , mmtt.last_updated_by = FND_GLOBAL.USER_ID
851 , mmtt.posting_flag = 'Y' -- Bug# 4185621: make sure child line mmtt is now posting
852 , mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line mmtt task status is loaded
853 WHERE mmtt.transaction_temp_id <> p_parent_temp_id
854 and mmtt.parent_line_id = p_parent_temp_id;
855 END IF;
856
857 END update_child;
858
859 PROCEDURE create_sub_transfer(p_from_temp_id NUMBER,
860 p_pri_qty NUMBER,
861 p_txn_qty NUMBER,
862 p_sec_qty NUMBER, --12747184
863 p_lot_controlled VARCHAR2
864 ) IS
865 l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
866 l_mtlt_rec mtl_transaction_lots_temp%ROWTYPE;
867 l_new_temp_id NUMBER;
868 l_api_name VARCHAR2(30) := 'create_sub_transfer';
869 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
870
871 CURSOR over_picked_lots(p_temp_id NUMBER) IS
872 select sum(transaction_quantity) transaction_quantity,
873 sum(primary_quantity) primary_quantity,
874 sum(secondary_quantity) secondary_quantity, --12747184
875 lot_number
876 from mtl_allocations_gtmp
877 where transaction_temp_id = p_temp_id
878 group by lot_number;
879
880 BEGIN
881 -- need to copy the child line since the child line contains the neccessary info
882 -- which the sub transfer needs
883 select *
884 into l_mmtt_rec
885 from mtl_material_transactions_temp
886 where transaction_temp_id <> p_from_temp_id
887 and parent_line_id = p_from_temp_id
888 and rownum <2;
889
890 IF l_debug=1 THEN
891 mydebug('BULK_PICK:create_sub_transfer:get the rec for the child:'||l_mmtt_rec.transaction_temp_id,
892 l_api_name);
893 END IF;
894
895 select mtl_material_transactions_s.NEXTVAL
896 into l_new_temp_id
897 from dual;
898
899 l_mmtt_rec.primary_quantity := p_pri_qty;
900 l_mmtt_rec.transaction_quantity := p_txn_qty;
901 l_mmtt_rec.secondary_transaction_quantity := p_sec_qty ; --12747184
902 l_mmtt_rec.transaction_temp_id := l_new_temp_id;
903 l_mmtt_rec.move_order_line_id := null;
904 l_mmtt_rec.operation_plan_id := null;
905 l_mmtt_rec.standard_operation_id := null;
906 l_mmtt_rec.cartonization_id := null;
907 l_mmtt_rec.trx_source_line_id := null;
908 l_mmtt_rec.transaction_source_id := null;
909 l_mmtt_rec.demand_source_line := null;
910 l_mmtt_rec.pick_rule_id := null;
911 l_mmtt_rec.reservation_id := null;
912 l_mmtt_rec.wms_task_type := null;
913 l_mmtt_rec.wms_task_status := l_g_task_loaded; -- Bug 4185621: new child mmtt line for overpicked quantity should have status loaded as well
914 l_mmtt_rec.transfer_subinventory := null;
915 l_mmtt_rec.transfer_to_location := null;
916
917 l_mmtt_rec.posting_flag := 'Y'; -- will be shown in the qty tree
918 l_mmtt_rec.transaction_source_type_id := 13;
919 l_mmtt_rec.transaction_action_id := 2;
920 l_mmtt_rec.transaction_type_id := 2; -- inventory sub transfer
921 -- the sub transfer transaction will have the same parent_line_id as the
922 -- child line so that it can be used in unload
923
924
925
926 wms_task_dispatch_engine.insert_mmtt(l_mmtt_rec);
927
928 -- create lot record
929 IF (p_lot_controlled = 'Y') THEN -- lot controlled
930 FOR lot_line in over_picked_lots(p_from_temp_id) LOOP
931 select *
932 into l_mtlt_rec
933 from mtl_transaction_lots_temp
934 where transaction_temp_id = p_from_temp_id
935 and lot_number = lot_line.lot_number;
936
937 l_mtlt_rec.transaction_temp_id := l_new_temp_id;
938 l_mtlt_rec.primary_quantity := lot_line.primary_quantity;
939 l_mtlt_rec.transaction_quantity := lot_line.primary_quantity; -- UOM will be using the primary uom
940 l_mtlt_rec.secondary_quantity := lot_line.secondary_quantity ; --12747184
941
942 l_mtlt_rec.serial_transaction_temp_id := null; -- serial numbers never be allocated, so we can't create
943 -- serial records for the sub transfer
944
945 -- insert lot rec
946 inv_rcv_common_apis.insert_mtlt(l_mtlt_rec);
947
948 END LOOP; -- end loop through each lot
949 END IF; -- end lot process
950
951 END create_sub_transfer;
952
953 -- Added for 14699845 (Flexible Lot Allocation)
954 PROCEDURE insert_mtlt (
955 p_new_temp_id IN NUMBER
956 , p_serial_temp_id IN NUMBER := NULL
957 , p_pri_att_qty IN NUMBER
958 , p_trx_att_qty IN NUMBER
959 , p_secondary_trx_qty IN NUMBER
960 , p_lot_number IN VARCHAR2
961 , p_item_id IN NUMBER
962 , p_organization_id IN NUMBER
963 , x_return_status OUT NOCOPY VARCHAR2) IS
964
965 l_api_name VARCHAR2(30) := 'bulk_pick';
966 BEGIN
967 x_return_status := fnd_api.g_ret_sts_success;
968 mydebug('FlexibleLotAlloc: Inside insert mtlt',l_api_name );
972 , last_update_date
969 INSERT INTO mtl_transaction_lots_temp
970 (
971 transaction_temp_id
973 , last_updated_by
974 , creation_date
975 , created_by
976 , transaction_quantity
977 , primary_quantity
978 , lot_number
979 , lot_expiration_date
980 , serial_transaction_temp_id
981 , description
982 , vendor_name
983 , supplier_lot_number
984 , origination_date
985 , date_code
986 , grade_code
987 , change_date
988 , maturity_date
989 , retest_date
990 , age
991 , item_size
992 , color
993 , volume
994 , volume_uom
995 , place_of_origin
996 , best_by_date
997 , LENGTH
998 , length_uom
999 , recycled_content
1000 , thickness
1001 , thickness_uom
1002 , width
1003 , width_uom
1004 , curl_wrinkle_fold
1005 , lot_attribute_category
1006 , c_attribute1
1007 , c_attribute2
1008 , c_attribute3
1009 , c_attribute4
1010 , c_attribute5
1011 , c_attribute6
1012 , c_attribute7
1013 , c_attribute8
1014 , c_attribute9
1015 , c_attribute10
1016 , c_attribute11
1017 , c_attribute12
1018 , c_attribute13
1019 , c_attribute14
1020 , c_attribute15
1021 , c_attribute16
1022 , c_attribute17
1023 , c_attribute18
1024 , c_attribute19
1025 , c_attribute20
1026 , d_attribute1
1027 , d_attribute2
1028 , d_attribute3
1029 , d_attribute4
1030 , d_attribute5
1031 , d_attribute6
1032 , d_attribute7
1033 , d_attribute8
1034 , d_attribute9
1035 , d_attribute10
1036 , n_attribute1
1037 , n_attribute2
1038 , n_attribute3
1039 , n_attribute4
1040 , n_attribute5
1041 , n_attribute6
1042 , n_attribute7
1043 , n_attribute8
1044 , n_attribute9
1045 , n_attribute10
1046 , vendor_id
1047 , territory_code
1048 , secondary_quantity
1049 )
1050 (SELECT p_new_temp_id
1051 , sysdate
1052 , -9999
1053 , sysdate
1054 , -9999
1055 , p_trx_att_qty
1056 , p_pri_att_qty
1057 , p_lot_number
1058 , mln.expiration_date
1059 , p_serial_temp_id
1060 , mln.description
1061 , mln.vendor_name
1062 , mln.supplier_lot_number
1063 , mln.origination_date
1064 , mln.date_code
1065 , mln.grade_code
1066 , mln.change_date
1067 , mln.maturity_date
1068 , mln.retest_date
1069 , mln.age
1070 , mln.item_size
1071 , mln.color
1072 , mln.volume
1073 , mln.volume_uom
1074 , mln.place_of_origin
1075 , mln.best_by_date
1076 , mln.LENGTH
1077 , mln.length_uom
1078 , mln.recycled_content
1079 , mln.thickness
1080 , mln.thickness_uom
1081 , mln.width
1082 , mln.width_uom
1083 , mln.curl_wrinkle_fold
1084 , mln.lot_attribute_category
1085 , mln.c_attribute1
1086 , mln.c_attribute2
1087 , mln.c_attribute3
1088 , mln.c_attribute4
1089 , mln.c_attribute5
1090 , mln.c_attribute6
1091 , mln.c_attribute7
1092 , mln.c_attribute8
1093 , mln.c_attribute9
1094 , mln.c_attribute10
1095 , mln.c_attribute11
1096 , mln.c_attribute12
1097 , mln.c_attribute13
1098 , mln.c_attribute14
1099 , mln.c_attribute15
1100 , mln.c_attribute16
1101 , mln.c_attribute17
1102 , mln.c_attribute18
1103 , mln.c_attribute19
1104 , mln.c_attribute20
1105 , mln.d_attribute1
1106 , mln.d_attribute2
1107 , mln.d_attribute3
1108 , mln.d_attribute4
1109 , mln.d_attribute5
1110 , mln.d_attribute6
1111 , mln.d_attribute7
1112 , mln.d_attribute8
1113 , mln.d_attribute9
1114 , mln.d_attribute10
1115 , mln.n_attribute1
1116 , mln.n_attribute2
1117 , mln.n_attribute3
1118 , mln.n_attribute4
1119 , mln.n_attribute5
1120 , mln.n_attribute6
1121 , mln.n_attribute7
1125 , mln.vendor_id
1122 , mln.n_attribute8
1123 , mln.n_attribute9
1124 , mln.n_attribute10
1126 , mln.territory_code
1127 , p_secondary_trx_qty
1128 FROM mtl_lot_numbers mln
1129 WHERE mln.lot_number = p_lot_number
1130 AND mln.inventory_item_id = p_item_id
1131 AND mln.organization_id = p_organization_id);
1132
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 x_return_status := fnd_api.g_ret_sts_error;
1136 mydebug(' Insert mtlt returns exception' ,l_api_name);
1137 mydebug ('Others exception while updating From LPN context: ' || SQLCODE,l_api_name);
1138 END insert_mtlt;
1139 --End Added for 14699845 (Flexible Lot Allocation)
1140
1141 /*
1142 -- This procedure will be used for distributing the quantity
1143 -- picked (in one or more parent MMTT lines) to the original
1144 -- child MMTT lines (again one or more)
1145 */
1146
1147
1148 PROCEDURE bulk_pick(p_temp_id IN NUMBER,
1149 p_txn_hdr_id IN NUMBER,
1150 p_org_id IN NUMBER,
1151 p_multiple_pick IN VARCHAR2, -- to indicate if this is multiple pick or not
1152 p_exception IN VARCHAR2, -- to indicate if this is over picking or short pick
1153 p_lot_controlled IN VARCHAR2,
1154 p_user_id IN NUMBER,
1155 p_employee_id IN NUMBER,
1156 p_reason_id IN NUMBER,
1157 x_new_txn_hdr_id OUT NOCOPY NUMBER,
1158 x_return_status OUT NOCOPY VARCHAR2,
1159 x_msg_count OUT NOCOPY NUMBER,
1160 x_msg_data OUT NOCOPY VARCHAR2)
1161 IS
1162
1163
1164 CURSOR c_parent_mmtt_lines IS
1165 SELECT mmtt.transaction_temp_id
1166 , mmtt.inventory_item_id
1167 , mmtt.subinventory_code
1168 , mmtt.locator_id
1169 , NVL(mmtt.content_lpn_id, mmtt.lpn_id)
1170 , mmtt.transfer_lpn_id
1171 , mmtt.transaction_uom
1172 , mmtt.transaction_quantity
1173 , mmtt.primary_quantity
1174 , mmtt.secondary_transaction_quantity --bug 8197506
1175 FROM mtl_material_transactions_temp mmtt
1176 WHERE mmtt.transaction_header_id = p_txn_hdr_id
1177 AND mmtt.organization_id = p_org_id
1178 AND mmtt.transaction_quantity > 0
1179 AND mmtt.parent_line_id = mmtt.transaction_temp_id -- make sure it is only parent lines
1180 ORDER BY mmtt.transaction_quantity DESC;
1181
1182 CURSOR c_parent_mmtt_shortpick IS
1183 SELECT mmtt.transaction_temp_id
1184 , mmtt.inventory_item_id
1185 , mmtt.subinventory_code
1186 , mmtt.locator_id
1187 , NVL(mmtt.content_lpn_id, mmtt.lpn_id)
1188 , mmtt.transfer_lpn_id
1189 , mmtt.transaction_uom
1190 , mmtt.transaction_quantity
1191 , mmtt.primary_quantity
1192 , mmtt.secondary_transaction_quantity --bug 8197506
1193 FROM mtl_material_transactions_temp mmtt
1194 WHERE mmtt.transaction_header_id = p_txn_hdr_id
1195 AND mmtt.organization_id = p_org_id
1196 AND mmtt.transaction_quantity > 0
1197 AND mmtt.parent_line_id <> p_temp_id
1198 AND mmtt.parent_line_id = mmtt.transaction_temp_id -- make sure it is only parent lines
1199 ORDER BY mmtt.transaction_quantity DESC;
1200
1201
1202
1203
1204 CURSOR c_parent_mmtt_lines_for_lots IS
1205 SELECT mmtt.transaction_temp_id
1206 , mmtt.transfer_lpn_id
1207 , mtlt.lot_number
1208 , mtlt.transaction_quantity lot_trx_qty
1209 , mtlt.primary_quantity lot_primary_qty
1210 , mtlt.secondary_quantity lot_sec_qty --bug 8197506
1211 FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
1212 WHERE mmtt.transaction_header_id = p_txn_hdr_id
1213 AND mmtt.organization_id = p_org_id
1214 AND mmtt.transaction_quantity > 0
1215 AND mmtt.parent_line_id = mmtt.transaction_temp_id
1216 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
1217 ORDER BY mmtt.transaction_quantity DESC;
1218
1219 CURSOR c_parent_mmtt_shorpick_lots IS
1220 SELECT mmtt.transaction_temp_id
1221 , mmtt.transfer_lpn_id
1222 , mtlt.lot_number
1223 , mtlt.transaction_quantity lot_trx_qty
1224 , mtlt.primary_quantity lot_primary_qty
1225 , mtlt.secondary_quantity lot_sec_qty --bug 8197506
1226 FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
1227 WHERE mmtt.transaction_header_id = p_txn_hdr_id
1228 AND mmtt.organization_id = p_org_id
1229 AND mmtt.transaction_quantity > 0
1230 AND mmtt.parent_line_id <> p_temp_id
1231 AND mmtt.parent_line_id = mmtt.transaction_temp_id
1232 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
1233 ORDER BY mmtt.transaction_quantity DESC;
1234
1235
1236 CURSOR c_child_mmtt_lines IS
1237 SELECT mmtt.transaction_temp_id
1238 , mmtt.transaction_uom
1239 , mmtt.transaction_quantity
1240 , mmtt.primary_quantity
1244 AND mmtt.parent_line_id <> mmtt.transaction_temp_id -- exclude the parent line
1241 , mmtt.secondary_transaction_quantity --bug 8197506
1242 FROM mtl_material_transactions_temp mmtt
1243 WHERE mmtt.parent_line_id = p_temp_id
1245 AND mmtt.organization_id = p_org_id
1246 ORDER BY mmtt.transaction_quantity DESC;
1247
1248 CURSOR c_child_mmtt_lines_so IS
1249 SELECT mmtt.transaction_temp_id
1250 , mmtt.transaction_uom
1251 , mmtt.transaction_quantity
1252 , mmtt.primary_quantity
1253 , mmtt.secondary_transaction_quantity --bug 8197506
1254 FROM mtl_material_transactions_temp mmtt,mtl_txn_request_lines mol,
1255 wsh_delivery_details wdd,wsh_delivery_assignments_v wda
1256 WHERE mmtt.parent_line_id = p_temp_id
1257 AND mmtt.parent_line_id <> mmtt.transaction_temp_id -- exclude the parent line
1258 AND mmtt.organization_id = p_org_id
1259 AND mol.line_id = mmtt.move_order_line_id
1260 AND mol.line_id = wdd.move_order_line_id
1261 AND wdd.released_status = 'S' --Bug#6848907
1262 AND wda.delivery_detail_id = wdd.delivery_detail_id
1263 ORDER BY nvl(wda.delivery_id,mol.carton_grouping_id), mmtt.transaction_quantity DESC;
1264
1265 CURSOR c_child_mmtt_lines_short IS
1266 SELECT mmtt.transaction_temp_id
1267 , mmtt.transaction_uom
1268 , mmtt.transaction_quantity
1269 , mmtt.primary_quantity
1270 , mmtt.secondary_transaction_quantity --bug 8197506
1271 FROM mtl_material_transactions_temp mmtt,mtl_txn_request_lines mol,
1272 wsh_delivery_details wdd,wsh_delivery_assignments_v wda,
1273 oe_order_lines_all ol,mtl_txn_request_headers moh
1274 WHERE mmtt.parent_line_id = p_temp_id
1275 AND mmtt.parent_line_id <> mmtt.transaction_temp_id -- exclude the parent line
1276 AND mmtt.organization_id = p_org_id
1277 AND mol.line_id = mmtt.move_order_line_id
1278 AND mol.line_id = wdd.move_order_line_id
1279 AND wdd.released_status = 'S' --Bug#6848907
1280 AND wda.delivery_detail_id = wdd.delivery_detail_id
1281 AND ol.line_id = wdd.source_line_id
1282 AND mol.header_id = moh.header_id
1283 ORDER BY ol. SCHEDULE_SHIP_DATE,
1284 nvl(wda.delivery_id,mol.carton_grouping_id),
1285 moh.creation_date, -- mmtt creation date is changed for splitting, so moh date will be better
1286 mmtt.transaction_quantity DESC;
1287
1288 c_mmtt_line c_child_mmtt_lines%ROWTYPE;
1289
1290 CURSOR c_child_lots(p_child_transaction_temp_id NUMBER) IS
1291 SELECT
1292 mtlt.lot_number
1293 , mtlt.transaction_quantity
1294 , mtlt.primary_quantity
1295 , mtlt.secondary_quantity -- bug 8197506
1296 FROM mtl_transaction_lots_temp mtlt
1297 WHERE
1298 mtlt.transaction_temp_id = p_child_transaction_temp_id;
1299
1300
1301 CURSOR c_lot_parents(p_lot_number VARCHAR2) IS
1302 SELECT mag.transaction_temp_id,
1303 mag.primary_quantity,
1304 mag.transaction_quantity,
1305 mag.secondary_quantity --bug 8197506
1306 FROM mtl_allocations_gtmp mag
1307 WHERE lot_number = p_lot_number
1308 ORDER BY search_sequence;
1309
1310
1311
1312 CURSOR over_picked_lines IS
1313 SELECT sum(transaction_quantity) transaction_quantity,
1314 sum(primary_quantity) primary_quantity,
1315 sum(secondary_quantity) secondary_quantity , --12747184
1316 transaction_temp_id
1317 from mtl_allocations_gtmp
1318 group by transaction_temp_id;
1319
1320 -- Start changes for 14699845 (Flexible Lot Allocation)
1321 CURSOR c_child_mmtts(p_tran_id NUMBER)
1322 IS
1323 SELECT mmtt.transaction_temp_id,
1324 mmtt.transaction_quantity,
1325 mmtt.primary_quantity,
1326 mmtt.parent_line_id,
1327 mmtt.inventory_item_id,
1328 mmtt.secondary_transaction_quantity
1329 FROM mtl_material_transactions_temp mmtt
1330 WHERE mmtt.transaction_temp_id <> mmtt.parent_line_id
1331 AND mmtt.parent_line_id = p_temp_id
1332 AND mmtt.transfer_lpn_id IS NULL
1333 ORDER BY mmtt.transaction_quantity;
1334
1335 CURSOR c_parent_mmtt_lines_no_alloc
1336 IS
1337 SELECT mmtt.transaction_temp_id,
1338 mag.transaction_quantity,
1339 mag.primary_quantity,
1340 mmtt.lpn_id,
1341 mmtt.transfer_lpn_id,
1342 mmtt.content_lpn_id,
1343 mmtt.parent_line_id,
1344 mag.lot_number,
1345 mag.secondary_quantity,
1346 mmtt.inventory_item_id
1347 FROM mtl_material_transactions_temp mmtt , mtl_allocations_gtmp mag
1348 WHERE mmtt.transaction_temp_id = mag.transaction_temp_id
1349 ORDER BY mag.transaction_quantity, mag.primary_quantity desc;
1350
1351 CURSOR c_child_lot_allocation_exists
1352 IS
1353 SELECT 'Y'
1354 FROM mtl_transaction_lots_temp
1355 WHERE transaction_temp_id IN (SELECT mmtt.transaction_temp_id
1356 FROM mtl_material_transactions_temp mmtt
1357 WHERE mmtt.parent_line_id = p_temp_id
1358 AND mmtt.parent_line_id <> mmtt.transaction_temp_id);
1359 -- End changes for 14699845 (Flexible Lot Allocation)
1360
1361 l_parent_txn_qty NUMBER;
1362 l_child_txn_qty NUMBER:= 0;
1363 l_parent_pri_qty NUMBER;
1364 l_child_pri_qty NUMBER;
1365 l_parent_uom VARCHAR2(10);
1366 l_child_uom VARCHAR2(10);
1367 l_primary_uom VARCHAR2(10);
1368 l_parent_txn_temp_id NUMBER;
1369 l_child_txn_temp_id NUMBER;
1370 l_item_id NUMBER;
1371 l_parent_sub_code VARCHAR2(30);
1372 l_parent_loc_id NUMBER;
1373 l_lpn_id NUMBER;
1374 l_transfer_lpn_id NUMBER;
1375 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1376 l_new_temp_id NUMBER;
1377 l_serial_temp_id NUMBER;
1378 l_api_name VARCHAR2(30) := 'bulk_pick';
1379 l_mmtt_qty NUMBER;
1380 l_new_serial_temp_id NUMBER;
1381 L_CHILD_LOT_TXN_QTY NUMBER;
1382 l_link_mtlt_needed VARCHAR2(1) := 'N';
1383 l_process_qty NUMBER;
1384 l_move_order_type NUMBER;
1385 g_not_lot_controlled constant NUMBER := 1;
1386 g_lot_controlled constant NUMBER := 2;
1387 l_unpicked_serial_rec_exists VARCHAR2(1);
1388 l_transaction_action_id NUMBER;
1389 l_search_sequence NUMBER;
1390 l_temp_id NUMBER;
1391 --BUG 8197506
1392 l_parent_sec_qty NUMBER;
1393 l_child_sec_qty NUMBER;
1394 l_child_lot_sec_txn_qty NUMBER;
1395 l_process_sec_qty NUMBER;
1396 l_ret_sts VARCHAR2(3); -- Added for 14699845 (Flexible Lot Allocation)
1397 l_progress NUMBER; -- Added for 14699845 (Flexible Lot Allocation)
1398 l_lot_alloc_exist VARCHAR2(1) := 'N'; -- Added for 14699845 (Flexible Lot Allocation)
1399 l_primary_qty NUMBER;
1400
1401 BEGIN
1402 x_return_status := fnd_api.g_ret_sts_success;
1403
1404 IF l_debug = 1 THEN
1405 mydebug('Dispatching Bulk Pick Tasks for TxnHdrID = ' || p_txn_hdr_id || ' : TxnTempID = ' || p_temp_id,l_api_name);
1406 mydebug('p_multiple_pick:'||p_multiple_pick,l_api_name);
1407 mydebug('p_exception:'||p_exception,l_api_name);
1408 mydebug('Lot control code : '|| p_lot_controlled, l_api_name);
1409 END IF;
1410
1411 -- processed child lines will get a new header id
1412
1413 SELECT mtl_material_transactions_s.NEXTVAL
1414 INTO x_new_txn_hdr_id
1415 FROM DUAL;
1416
1417 IF (l_debug = 1) THEN
1418 mydebug('New header id the child lines will have :'||x_new_txn_hdr_id,l_api_name);
1419 END IF;
1420
1421 l_temp_id := p_temp_id;
1422 -- get the transaction action to be used later on
1423 BEGIN
1424 SELECT transaction_action_id
1425 INTO l_transaction_action_id
1426 FROM mtl_material_transactions_temp mmtt
1427 WHERE
1428 mmtt.transaction_temp_id = p_temp_id;
1429 EXCEPTION
1430 WHEN NO_DATA_FOUND THEN -- when the line merged from APL and old temp id is not there any more
1431 SELECT transaction_action_id,transaction_temp_id
1432 INTO l_transaction_action_id,l_temp_id
1433 FROM mtl_material_transactions_temp mmtt
1434 WHERE transaction_header_id = p_txn_hdr_id
1435 and rownum<2;
1436 END;
1437
1438 IF (l_debug = 1) THEN
1439 mydebug('transaction action id :'||l_transaction_action_id,l_api_name);
1440 END IF;
1441
1442 -- Start changes for 14699845 (Flexible Lot Allocation)
1443 OPEN c_child_lot_allocation_exists ;
1444 FETCH c_child_lot_allocation_exists INTO l_lot_alloc_exist ;
1445 CLOSE c_child_lot_allocation_exists;
1446
1447 IF (l_debug = 1) THEN
1448 mydebug(' Allocation Exists :'||l_lot_alloc_exist,l_api_name);
1449 END IF;
1450 -- End changes for 14699845 (Flexible Lot Allocation)
1451
1452 -- if it is complete single pick, nothing need to be done except to update the child line with
1453 -- the parent line info
1454 IF (p_exception is null and p_multiple_pick='N' AND l_lot_alloc_exist = 'Y' ) THEN -- Modified for 14699845 (Flexible Lot Allocation)
1455 --Even If it is a Complete LPN Pick, we need to distribute the childs amongst the Parents. Hence
1456 --bypassing this.... l_lot_alloc_exist IS NOT 'Y' --14699845
1457
1458 IF (l_debug = 1) THEN mydebug('This is a complete single pick!',l_api_name);
1459 END IF;
1460 -- update all child line with the correct parent info
1461 update_child(null,l_temp_id,x_new_txn_hdr_id);
1462
1463 --Bug# 4185621: update parent line posting flag back to 'N'
1464 UPDATE mtl_material_transactions_temp
1465 SET posting_flag = 'N'
1466 WHERE transaction_temp_id = l_temp_id
1467 AND parent_line_id = transaction_temp_id;
1468 -- Bug# 4185621: end change
1469
1470 return;
1471 END IF;
1472
1473 -- make sure the global temp table is emplty
1474 IF (l_debug = 1) THEN mydebug('deleting the global temp table ',l_api_name); END IF;
1475 delete mtl_allocations_gtmp;
1476
1477 -- populate the temp table mtl_allocations_gtmp
1478 if (p_lot_controlled = 'Y' ) THEN -- lot controlled
1479 IF (l_debug = 1) THEN
1480 mydebug('inserting the records to the tmp table',l_api_name);
1481 END IF;
1482 l_search_sequence := 0;
1483 IF (p_exception = 'SHORT') THEN
1484 IF (l_debug = 1) THEN
1485 mydebug('Inserting from c_parent_mmtt_shorpick_lots v1',l_api_name);
1486 END IF;
1487 FOR c_parent_lines_rec IN c_parent_mmtt_shorpick_lots
1488 LOOP
1489 l_search_sequence := l_search_sequence +1;
1490 INSERT
1491 INTO mtl_allocations_gtmp
1492 (
1493 TRANSACTION_TEMP_ID ,
1494 TRANSFER_LPN_ID ,
1495 LOT_NUMBER ,
1499 SECONDARY_QUANTITY --bug 8197506
1496 TRANSACTION_QUANTITY ,
1497 PRIMARY_QUANTITY ,
1498 SEARCH_SEQUENCE ,
1500 )
1501 VALUES
1502 (
1503 c_parent_lines_rec.transaction_temp_id ,
1504 c_parent_lines_rec.transfer_lpn_id ,
1505 c_parent_lines_rec.lot_number ,
1506 c_parent_lines_rec.lot_trx_qty ,
1507 c_parent_lines_rec.lot_primary_qty ,
1508 l_search_sequence ,
1509 c_parent_lines_rec.lot_sec_qty
1510 ); --bug 8197506
1511 --Bug# 4185621: update parent line posting flag back to 'N'
1512 UPDATE mtl_material_transactions_temp
1513 SET posting_flag = 'N'
1514 WHERE transaction_temp_id = c_parent_lines_rec.transaction_temp_id
1515 AND parent_line_id = transaction_temp_id;
1516
1517 -- Bug# 4185621: end change
1518 END LOOP;
1519 ELSE
1520 FOR c_parent_lines_rec IN c_parent_mmtt_lines_for_lots
1521 LOOP
1522 l_search_sequence := l_search_sequence +1;
1523 INSERT
1524 INTO mtl_allocations_gtmp
1525 (
1526 TRANSACTION_TEMP_ID ,
1527 TRANSFER_LPN_ID ,
1528 LOT_NUMBER ,
1529 TRANSACTION_QUANTITY ,
1530 PRIMARY_QUANTITY ,
1531 SEARCH_SEQUENCE ,
1532 SECONDARY_QUANTITY --bug 8197506
1533 )
1534 VALUES
1535 (
1536 c_parent_lines_rec.transaction_temp_id ,
1537 c_parent_lines_rec.transfer_lpn_id ,
1538 c_parent_lines_rec.lot_number ,
1539 c_parent_lines_rec.lot_trx_qty ,
1540 c_parent_lines_rec.lot_primary_qty ,
1541 l_search_sequence ,
1542 c_parent_lines_rec.lot_sec_qty --bug 8197506
1543 );
1544
1545 --Bug# 4185621: update parent line posting flag back to 'N'
1546 UPDATE mtl_material_transactions_temp
1547 SET posting_flag = 'N'
1548 WHERE transaction_temp_id = c_parent_lines_rec.transaction_temp_id
1549 AND parent_line_id = transaction_temp_id;
1550
1551 -- Bug# 4185621: end change
1552 END LOOP;
1553 END IF;
1554 IF (l_debug = 1) THEN
1555 mydebug( l_search_sequence
1556 ||' parent lines are inserted',l_api_name);
1557 END IF;
1558 IF (l_transaction_action_id = 28 AND p_exception= 'SHORT') THEN
1559 IF (l_debug = 1) THEN
1560 mydebug('opening c_child_mmtt_lines_short...',l_api_name);
1561 END IF;
1562 OPEN c_child_mmtt_lines_short;
1563 ELSIF l_transaction_action_id = 28 THEN
1564 IF (l_debug = 1) THEN
1565 mydebug('opening c_child_mmtt_lines_so ...',l_api_name);
1566 END IF;
1567 OPEN c_child_mmtt_lines_so;
1568 ELSE
1569 OPEN c_child_mmtt_lines;
1570 END IF;
1571 LOOP -- loop through each child mmtt line
1572 IF c_child_mmtt_lines%ISOPEN THEN
1573 FETCH c_child_mmtt_lines
1574 INTO c_mmtt_line;
1575
1576 EXIT
1577 WHEN c_child_mmtt_lines%NOTFOUND;
1578 ELSIF c_child_mmtt_lines_so%ISOPEN THEN
1579 FETCH c_child_mmtt_lines_so
1580 INTO c_mmtt_line;
1581
1582 EXIT
1583 WHEN c_child_mmtt_lines_so%NOTFOUND;
1584 ELSE
1585 FETCH c_child_mmtt_lines_short
1586 INTO c_mmtt_line;
1587
1588 EXIT
1589 WHEN c_child_mmtt_lines_short%NOTFOUND;
1590 END IF;
1591 IF (l_debug = 1) THEN
1592 mydebug('child transaction temp id'
1593 ||c_mmtt_line.transaction_temp_id,l_api_name);
1594 END IF;
1595 l_child_txn_temp_id := c_mmtt_line.transaction_temp_id;
1596 FOR c_child_lots_rec IN c_child_lots(c_mmtt_line.transaction_temp_id)
1597 LOOP -- loop through each child mtlt line
1598 l_child_lot_txn_qty := c_child_lots_rec.primary_quantity; -- get the lot qty
1599 l_child_lot_sec_txn_qty := c_child_lots_rec.secondary_quantity; --bug 8197506
1600 IF (l_debug = 1) THEN
1601 mydebug('lot number '
1602 ||c_child_lots_rec.lot_number
1603 ||' lot qty:'
1604 || c_child_lots_rec.primary_quantity
1605 ||'sec lot qty:'
1606 || c_child_lots_rec.secondary_quantity,l_api_name); --bug 8197506
1607 END IF;
1608 FOR c_lot_parents_rec IN c_lot_parents(c_child_lots_rec.lot_number)
1612 mydebug('parent transaction temp id:'
1609 LOOP -- loop though parent lines to find the
1610 -- the proper parent lines for the lot
1611 IF (l_debug = 1) THEN
1613 ||c_lot_parents_rec.transaction_temp_id
1614 ||' parent lot qty:'
1615 || c_lot_parents_rec.primary_quantity
1616 ||' parent sec lot qty:'
1617 || c_lot_parents_rec.secondary_quantity,l_api_name);--bug 8197506
1618 END IF;
1619 -- find what's the lot qty can be processed
1620 IF c_lot_parents_rec.primary_quantity >= l_child_lot_txn_qty THEN
1621 l_process_qty := l_child_lot_txn_qty;
1622 l_process_sec_qty :=l_child_lot_sec_txn_qty; --bug 8197506
1623 ELSE
1624 l_process_qty := c_lot_parents_rec.primary_quantity;
1625 l_process_sec_qty := c_lot_parents_rec.secondary_quantity; --bug 8197506
1626 END IF;
1627 IF (l_debug = 1) THEN
1628 mydebug('processed lot qty :'
1629 ||l_process_qty,l_api_name);
1630 mydebug('processed sec lot qty :'
1631 ||l_process_sec_qty,l_api_name);--bug 8197506
1632 END IF;
1633 -- initialize the local variable
1634 l_link_mtlt_needed := 'N';
1635 -- first find out if the mmtt line for this parent line has been created or not, for multiple lots
1636 BEGIN
1637 SELECT child_transaction_temp_id
1638 INTO l_child_txn_temp_id
1639 FROM mtl_allocations_gtmp
1640 WHERE transaction_temp_id = c_lot_parents_rec.transaction_temp_id
1641 AND child_transaction_temp_id IS NOT NULL
1642 AND rownum = 1;
1643
1644 l_link_mtlt_needed := 'Y'; -- remember it since the mtlt may be needed splitting
1645 IF (l_debug = 1) THEN
1646 mydebug(' child line created before id:'
1647 || l_child_txn_temp_id,l_api_name);
1648 END IF;
1649 -- update the qty with the new process qty
1650 UPDATE mtl_material_transactions_temp
1651 SET primary_quantity = primary_quantity +l_process_qty ,
1652 secondary_transaction_quantity = DECODE(secondary_transaction_quantity,NULL,NULL,secondary_transaction_quantity + l_process_sec_qty), --bug 8197506
1653 transaction_quantity = transaction_quantity+l_process_qty ,
1654 posting_flag = 'Y' , -- Bug# 4185621: change child line posting flag back to 'Y'
1655 wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line task status is loaded
1656 WHERE transaction_temp_id = l_child_txn_temp_id;
1657
1658 /*Bug8460179-We need to deduct the qty form original child MMTTs*/
1659 UPDATE mtl_material_transactions_temp
1660 SET primary_quantity = primary_quantity - l_process_qty ,
1661 secondary_transaction_quantity = DECODE(secondary_transaction_quantity,NULL,NULL,secondary_transaction_quantity - l_process_sec_qty),
1662 transaction_quantity = transaction_quantity - l_process_qty
1663 WHERE transaction_temp_id = c_mmtt_line.transaction_temp_id;
1664
1665 IF (l_debug = 1) THEN
1666 mydebug('updated the chld line with the new processed qty',l_api_name);
1667 END IF;
1668 EXCEPTION
1669 WHEN NO_DATA_FOUND THEN
1670 IF (l_debug = 1) THEN
1671 mydebug('child line was not created',l_api_name);
1672 END IF;
1673 -- child line is not created,split the mmtt line if needed
1674 IF c_mmtt_line.primary_quantity = l_process_qty THEN -- no need to split
1675 IF (l_debug = 1) THEN
1676 mydebug('no need to split....',l_api_name);
1677 END IF;
1678 l_child_txn_temp_id := c_mmtt_line.transaction_temp_id;
1679 l_link_mtlt_needed := 'N';
1680 ELSE -- qty in mmtt > qty processed ,split anyway, will be cleaned up later on
1681 SELECT mtl_material_transactions_s.NEXTVAL
1682 INTO l_new_temp_id
1683 FROM dual;
1684
1685 IF (l_debug = 1) THEN
1686 mydebug('split the child line with the new temp id '
1687 ||l_new_temp_id,l_api_name);
1691 l_child_txn_temp_id := l_new_temp_id;
1688 END IF;
1689 split_child_mmtt(c_mmtt_line.transaction_temp_id, l_new_temp_id, l_process_qty, l_process_sec_qty --bug 8197506
1690 );
1692 IF (l_debug = 1) THEN
1693 mydebug('update the processed qty for the new child line',l_api_name);
1694 END IF;
1695 UPDATE mtl_allocations_gtmp
1696 SET child_transaction_temp_id = l_new_temp_id
1697 WHERE transaction_temp_id = c_lot_parents_rec.transaction_temp_id;
1698
1699 l_link_mtlt_needed := 'Y';
1700 END IF;
1701 END;
1702 -- split the lot line if needed
1703 IF (l_process_qty = c_child_lots_rec.primary_quantity ) THEN -- no need to split
1704 IF (l_debug = 1) THEN
1705 mydebug('No need to split the mtlt....',l_api_name);
1706 END IF;
1707 IF l_link_mtlt_needed = 'Y' THEN
1708 IF (l_debug = 1) THEN
1709 mydebug('update the lot qty for temp id:'
1710 ||c_mmtt_line.transaction_temp_id, l_api_name);
1711 END IF;
1712 UPDATE mtl_transaction_lots_temp
1713 SET transaction_Temp_id = l_child_txn_temp_id
1714 WHERE transaction_temp_id = c_mmtt_line.transaction_temp_id
1715 AND lot_number = c_child_lots_rec.lot_number;
1716
1717 END IF;
1718 ELSE
1719 -- split lot is needed, l_process_qty <the lot qty
1720 IF (l_debug = 1) THEN
1721 mydebug('splitting the mtlt line with new temp id '
1722 || l_child_txn_temp_id,l_api_name);
1723 END IF;
1724 split_child_mtlt(c_mmtt_line.transaction_temp_id, l_child_txn_temp_id, -- new temp id
1725 NULL, -- new serial temp id
1726 l_process_qty, c_child_lots_rec.lot_number, --v1
1727 l_process_sec_qty --bug 8197506
1728 );
1729 END IF;
1730 -- update child line with the correct parent info
1731 IF (l_debug = 1) THEN
1732 mydebug('updating the child line with the correct parent....',l_api_name);
1733 END IF;
1734 update_child(l_child_txn_temp_id,c_lot_parents_rec.transaction_temp_id, x_new_txn_hdr_id);
1735 -- update the processed lot quantity
1736 l_child_lot_txn_qty := l_child_lot_txn_qty -l_process_qty;
1737 l_child_lot_sec_txn_qty := l_child_lot_sec_txn_qty - l_process_sec_qty; --bug 8197506
1738 IF (l_debug = 1) THEN
1739 mydebug('left lot qty to be processed '
1740 ||l_child_lot_txn_qty,l_api_name);
1741 END IF;
1742 mydebug('left sec lot qty to be processed '
1743 ||l_child_lot_sec_txn_qty,l_api_name); --bug 8197506
1744 -- update the quantity for the parent line in the gtmp
1745 UPDATE mtl_allocations_gtmp
1746 SET primary_quantity = primary_quantity -l_process_qty, -- only primary UOM will be used
1747 secondary_quantity = DECODE(secondary_quantity,NULL,NULL,secondary_quantity-l_process_sec_qty) --bug 8197506
1748 WHERE transaction_temp_id = c_lot_parents_rec.transaction_temp_id
1749 AND lot_number = c_child_lots_rec.lot_number;
1750
1751 IF (l_debug = 1) THEN
1752 mydebug('after updating the parent qty in the temp table.',l_api_name);
1753 END IF;
1754 -- delete the record if the qty has became 0
1755 DELETE mtl_allocations_gtmp
1756 WHERE transaction_temp_id = c_lot_parents_rec.transaction_temp_id
1757 AND primary_quantity = 0;
1758
1759 IF (l_debug = 1 AND SQL%ROWCOUNT>0) THEN
1760 mydebug('records deteted for qty 0 in the temp table '
1761 ||SQL%ROWCOUNT,l_api_name);
1762 mydebug('child lot txn qty to be processed :'
1763 ||l_child_lot_txn_qty,l_api_name);
1764 END IF;
1765 EXIT
1766 WHEN l_child_lot_txn_qty = 0;
1767 END LOOP; -- for parent lines, c_lot_parents_rec
1768 END LOOP; -- for mtlt,c_child_lots_rec
1769 -- delete the qty 0 mmtt due to the split
1770 DELETE
1774
1771 FROM mtl_material_transactions_temp mmtt
1772 WHERE transaction_temp_id = c_mmtt_line.transaction_temp_id
1773 AND primary_quantity = 0;
1775 -- before process another mmtt line, the child transaction temp id should be nullified
1776 UPDATE mtl_allocations_gtmp
1777 SET child_transaction_temp_id = NULL;
1778
1779 END LOOP; -- for mmtt
1780 IF c_child_mmtt_lines%ISOPEN THEN
1781 CLOSE c_child_mmtt_lines;
1782 ELSIF c_child_mmtt_lines_so%ISOPEN THEN
1783 CLOSE c_child_mmtt_lines_so ;
1784 ELSE
1785 CLOSE c_child_mmtt_lines_short;
1786 END IF;
1787
1788
1789 -- Start changes for 14699845 (Flexible Lot Allocation)
1790 FOR rec_parent_mmtt_line IN c_parent_mmtt_lines_no_alloc
1791 LOOP
1792 l_progress := 1.1;
1793 IF l_debug = 1 THEN
1794 mydebug('l_progress: ' ||l_progress, l_api_name);
1795 mydebug('Got Parent: ' ||rec_parent_mmtt_line.transaction_temp_id, l_api_name);
1796 END IF;
1797 l_process_qty := rec_parent_mmtt_line.transaction_quantity;
1798 l_process_sec_qty := rec_parent_mmtt_line.secondary_quantity;
1799 l_primary_qty := rec_parent_mmtt_line.primary_quantity;
1800 FOR rec_child_mmtts IN c_child_mmtts(rec_parent_mmtt_line.transaction_temp_id)
1801 LOOP
1802 IF l_debug = 1 THEN
1803 mydebug('Found Parent transaction_temp_id: '|| rec_parent_mmtt_line.transaction_temp_id,l_api_name);
1804 mydebug('Found Parent parent_line_id: '|| rec_parent_mmtt_line.parent_line_id,l_api_name);
1805 mydebug('Found Parent transaction_quantity: '|| rec_parent_mmtt_line.transaction_quantity,l_api_name);
1806 mydebug('Found Parent primary_quantity: '|| rec_parent_mmtt_line.primary_quantity,l_api_name);
1807 mydebug('Found Parent secondary_quantity: '|| rec_parent_mmtt_line.secondary_quantity,l_api_name);
1808 mydebug('Found l_process_qty: '|| l_process_qty,l_api_name);
1809 mydebug('Found Parent lot_number: '|| rec_parent_mmtt_line.lot_number,l_api_name);
1810 mydebug(':----------------------------------:' , l_api_name);
1811 mydebug('Found Child transaction_temp_id: '|| rec_child_mmtts.transaction_temp_id,l_api_name);
1812 mydebug('Found Child parent_line_id: '|| rec_child_mmtts.parent_line_id,l_api_name);
1813 mydebug('Found Child transaction_quantity: '|| rec_child_mmtts.transaction_quantity,l_api_name);
1814 mydebug('Found Child primary_quantity: '|| rec_child_mmtts.primary_quantity,l_api_name);
1815 mydebug('Found Child secondary_quantity: '|| rec_child_mmtts.secondary_transaction_quantity,l_api_name);
1816 --mydebug('Found Child lot_number: '|| rec_child_mmtts.lot_number,l_api_name);
1817 mydebug('l_progress: ' ||l_progress, l_api_name);
1818 END IF;
1819 BEGIN
1820 IF l_process_qty = rec_child_mmtts.transaction_quantity
1821 THEN
1822 -- Got the exact Match, consume the full child and delete the record from temp table
1823 -- Insert the mtlt and update mmtt for child
1824 IF l_debug = 1 THEN
1825 mydebug('mmtt qty = child ' , l_api_name);
1826 mydebug('l_progress: ' ||l_progress, l_api_name);
1827 END IF;
1828
1829 insert_mtlt (
1830 p_new_temp_id => rec_child_mmtts.transaction_temp_id
1831 , p_serial_temp_id => NULL
1832 , p_pri_att_qty => rec_child_mmtts.primary_quantity
1833 , p_trx_att_qty => rec_child_mmtts.transaction_quantity
1834 , p_secondary_trx_qty => rec_child_mmtts.secondary_transaction_quantity
1835 , p_lot_number => rec_parent_mmtt_line.lot_number
1836 , p_item_id => rec_parent_mmtt_line.inventory_item_id
1837 , p_organization_id => p_org_id
1838 , x_return_status => l_ret_sts) ;
1839 mydebug('Inserting into MTLT - l_ret_status-' || l_ret_sts,l_api_name);
1840 l_progress := 1.2;
1841
1842 IF l_debug = 1 THEN
1843 mydebug('Update the Child MMTT ' , l_api_name);
1844 mydebug('Inserted the Child MTLT ' , l_api_name);
1845 mydebug('Update the Child MMTT with LPN_ID ' ||rec_parent_mmtt_line.lpn_id, l_api_name);
1846 mydebug('Update the Child MMTT with Xfer LPN_ID ' ||rec_parent_mmtt_line.transfer_lpn_id, l_api_name);
1847 mydebug('Update the Child MMTT with content LPN_ID' ||rec_parent_mmtt_line.content_lpn_id, l_api_name);
1848 mydebug('Update the Child MMTT having tran_temp_id' ||rec_child_mmtts.transaction_temp_id, l_api_name);
1849 mydebug('l_progress: ' ||l_progress, l_api_name);
1850 END IF;
1851
1852 update_child(rec_child_mmtts.transaction_temp_id,
1853 rec_parent_mmtt_line.transaction_temp_id,
1854 x_new_txn_hdr_id
1855 );
1856
1857 l_progress := 1.3;
1858
1859 IF l_debug = 1 THEN
1860 mydebug('Since the full Child is consumed, delete from the GTEMP ' , l_api_name);
1861 END IF;
1862 -- Delete from mag
1863 DELETE FROM mtl_allocations_gtmp
1864 WHERE transaction_temp_id = rec_parent_mmtt_line.transaction_temp_id
1865 AND lot_number = rec_parent_mmtt_line.lot_number;
1866
1867 EXIT;
1868
1869 ELSIF l_process_qty > rec_child_mmtts.transaction_quantity
1870 THEN
1874 IF l_debug = 1 THEN
1871 -- Got a subset of the Parent, consume this child and get the next one . Insert the mtlt and update mmtt for child
1872 l_progress := 2.1;
1873
1875 mydebug('mmtt qty > child ' , l_api_name);
1876 mydebug('Insert the Child MTLT ' , l_api_name);
1877 END IF;
1878
1879 insert_mtlt (
1880 p_new_temp_id => rec_child_mmtts.transaction_temp_id
1881 , p_serial_temp_id => NULL
1882 , p_pri_att_qty => rec_child_mmtts.primary_quantity
1883 , p_trx_att_qty => rec_child_mmtts.transaction_quantity
1884 , p_secondary_trx_qty => rec_child_mmtts.secondary_transaction_quantity
1885 , p_lot_number => rec_parent_mmtt_line.lot_number
1886 , p_item_id => rec_parent_mmtt_line.inventory_item_id
1887 , p_organization_id => p_org_id
1888 , x_return_status => l_ret_sts) ;
1889 mydebug('Inserting into MTLT - l_ret_status-' || l_ret_sts,l_api_name);
1890
1891 l_progress := 2.2;
1892 IF l_debug = 1 THEN
1893 mydebug('Inserted the Child MMTT ' , l_api_name);
1894 END IF;
1895
1896 update_child(rec_child_mmtts.transaction_temp_id,
1897 rec_parent_mmtt_line.transaction_temp_id,
1898 x_new_txn_hdr_id
1899 );
1900
1901 l_progress := 2.3;
1902
1903 IF l_debug = 1 THEN
1904 mydebug('Update the GTMP TABLE, decrement with qty ' ||rec_child_mmtts.transaction_quantity, l_api_name);
1905 END IF;
1906
1907 UPDATE mtl_allocations_gtmp
1908 SET transaction_quantity = transaction_quantity - rec_child_mmtts.transaction_quantity,
1909 primary_quantity = primary_quantity - rec_child_mmtts.primary_quantity,
1910 secondary_quantity = secondary_quantity - rec_child_mmtts.secondary_transaction_quantity
1911 WHERE transaction_temp_id = rec_parent_mmtt_line.transaction_temp_id
1912 AND lot_number = rec_parent_mmtt_line.lot_number;
1913
1914 l_progress := 2.3;
1915
1916 IF l_debug = 1 THEN
1917 mydebug('l_process_qty ' ||l_process_qty , l_api_name);
1918 mydebug('l_primary_qty ' ||l_primary_qty , l_api_name);
1919 mydebug('l_process_sec_qty ' ||l_process_sec_qty , l_api_name);
1920 mydebug('Decrementing l_process_qty for next iteration', l_api_name);
1921 END IF;
1922
1923 l_process_qty := l_process_qty - rec_child_mmtts.transaction_quantity;
1924 l_primary_qty := l_primary_qty - rec_child_mmtts.primary_quantity;
1925 l_process_sec_qty := l_process_sec_qty - rec_child_mmtts.secondary_transaction_quantity;
1926
1927 IF l_debug = 1 THEN
1928 mydebug('l_process_qty ' ||l_process_qty, l_api_name);
1929 mydebug('l_primary_qty ' ||l_primary_qty, l_api_name);
1930 mydebug('l_process_sec_qty ' ||l_process_sec_qty, l_api_name);
1931 END IF;
1932
1933 ELSIF l_process_qty < rec_child_mmtts.transaction_quantity
1934 THEN
1935 l_progress := 3.1;
1936 IF l_debug = 1 THEN
1937 mydebug('mmtt qty < child ' , l_api_name);
1938 mydebug('l_process_qty ' ||l_process_qty, l_api_name);
1939 END IF;
1940 -- Split the Child mmtt and insert mtlt.
1941 SELECT mtl_material_transactions_s.NEXTVAL
1942 INTO l_new_temp_id
1943 FROM dual;
1944
1945 l_progress := 3.2;
1946 IF l_debug = 1 THEN
1947 mydebug('Split the Child MMTT ' , l_api_name);
1948 mydebug('l_new_temp_id ' ||l_new_temp_id , l_api_name);
1949 mydebug('Parent Quantity ' ||l_process_qty , l_api_name);
1950 mydebug('Child Quantity ' ||rec_child_mmtts.transaction_quantity , l_api_name);
1951 mydebug('Parent Lot Number ' ||rec_parent_mmtt_line.lot_number , l_api_name);
1952 --mydebug('Child Lot Number ' ||rec_child_mmtts.lot_number , l_api_name);
1953 END IF;
1954
1955 split_child_mmtt(rec_child_mmtts.transaction_temp_id,
1956 l_new_temp_id,
1957 rec_child_mmtts.transaction_quantity - l_process_qty,
1958 rec_child_mmtts.secondary_transaction_quantity - l_process_sec_qty
1959 );
1960 l_progress := 3.3;
1961 IF (l_debug = 1)
1962 THEN
1963 mydebug('Done Splitting MMTT with new temp id '|| l_new_temp_id,l_api_name);
1964 mydebug('l_progress '|| l_progress,l_api_name);
1965 mydebug('Inserting the mtlt line with mmtts temp id '|| rec_child_mmtts.transaction_temp_id,l_api_name);
1966 END IF;
1967
1968 insert_mtlt (
1969 p_new_temp_id => rec_child_mmtts.transaction_temp_id
1970 , p_serial_temp_id => NULL
1971 , p_pri_att_qty => l_primary_qty
1972 , p_trx_att_qty => l_process_qty
1973 , p_secondary_trx_qty => l_process_sec_qty
1974 , p_lot_number => rec_parent_mmtt_line.lot_number
1975 , p_item_id => rec_parent_mmtt_line.inventory_item_id
1976 , p_organization_id => p_org_id
1977 , x_return_status => l_ret_sts) ;
1978 mydebug('Inserting into MTLT - l_ret_status-' || l_ret_sts,l_api_name);
1979
1983 mydebug('l_progress '|| l_progress,l_api_name);
1980 l_progress := 3.4;
1981 IF (l_debug = 1)
1982 THEN
1984 mydebug('Done Inserting MTLT with temp id '|| rec_child_mmtts.transaction_temp_id,l_api_name);
1985 END IF;
1986
1987 update_child(rec_child_mmtts.transaction_temp_id,
1988 rec_parent_mmtt_line.transaction_temp_id,
1989 x_new_txn_hdr_id
1990 );
1991
1992 l_progress := 3.6;
1993
1994 IF l_debug = 1 THEN
1995 mydebug('Update the GTMP TABLE, decrement with qty ' ||l_process_qty, l_api_name);
1996 mydebug('l_progress '|| l_progress,l_api_name);
1997 END IF;
1998
1999 UPDATE mtl_allocations_gtmp
2000 SET transaction_quantity = transaction_quantity - l_process_qty,
2001 primary_quantity = primary_quantity - l_primary_qty,
2002 secondary_quantity = secondary_quantity - l_process_sec_qty
2003 WHERE transaction_temp_id = rec_parent_mmtt_line.transaction_temp_id
2004 AND lot_number = rec_parent_mmtt_line.lot_number;
2005
2006 l_progress := 3.7;
2007
2008 DELETE FROM mtl_allocations_gtmp
2009 WHERE transaction_quantity = 0
2010 AND transaction_temp_id = rec_parent_mmtt_line.transaction_temp_id
2011 AND lot_number = rec_parent_mmtt_line.lot_number;
2012
2013 IF SQL%ROWCOUNT = 1 THEN
2014 EXIT;
2015 END IF;
2016
2017 END IF;
2018
2019 EXCEPTION
2020 WHEN OTHERS THEN
2021 IF l_debug = 1 THEN
2022 mydebug('Exception occurred in the FlexibleLotAlloc Code ' , l_api_name);
2023 mydebug('SQLCODE: ' ||SQLCODE, l_api_name);
2024 mydebug('SQLERRM: ' ||SQLERRM, l_api_name);
2025 mydebug('l_progress: ' ||l_progress, l_api_name);
2026 END IF;
2027 END;
2028 END LOOP;
2029 END LOOP;
2030 -- End changes for 14699845 (Flexible Lot Allocation)
2031 -- create sub transfer for over picked qty, they should be in the gtmp table
2032 IF (p_exception='OVER') THEN
2033 FOR over_qty_line IN over_picked_lines
2034 LOOP
2035 create_sub_transfer(over_qty_line.transaction_temp_id,
2036 over_qty_line.primary_quantity,
2037 over_qty_line.primary_quantity,
2038 over_qty_line.secondary_quantity,
2039 p_lot_controlled); --12747184 ,added the sec qty
2040 END LOOP;
2041 END IF;
2042
2043 ELSE -- plain item
2044
2045
2046 IF ( p_exception = 'SHORT') THEN
2047 IF (l_debug = 1) THEN mydebug('Opening c_parent_mmtt_shortpick v1 ',p_exception); END IF;
2048 OPEN c_parent_mmtt_shortpick;
2049 ELSE
2050 OPEN c_parent_mmtt_lines;
2051 END IF;
2052
2053 IF (l_transaction_action_id = 28 and p_exception = 'SHORT')THEN
2054 OPEN c_child_mmtt_lines_short;
2055 ELSIF l_transaction_action_id = 28 THEN
2056 OPEN c_child_mmtt_lines_so;
2057 ELSE OPEN c_child_mmtt_lines;
2058 END IF;
2059
2060
2061 LOOP
2062
2063
2064 IF c_parent_mmtt_shortpick%ISOPEN THEN
2065 IF (l_debug = 1) THEN mydebug('Fetching from c_parent_mmtt_shortpick v1',p_exception); END IF;
2066 FETCH c_parent_mmtt_shortpick INTO l_parent_txn_temp_id
2067 , l_item_id
2068 , l_parent_sub_code
2069 , l_parent_loc_id
2070 , l_lpn_id
2071 , l_transfer_lpn_id
2072 , l_parent_uom
2073 , l_parent_txn_qty
2074 , l_parent_pri_qty
2075 , l_parent_sec_qty; --bug 8197506
2076 EXIT WHEN c_parent_mmtt_shortpick%NOTFOUND;
2077 ELSE
2078 FETCH c_parent_mmtt_lines INTO l_parent_txn_temp_id
2079 , l_item_id
2080 , l_parent_sub_code
2081 , l_parent_loc_id
2082 , l_lpn_id
2083 , l_transfer_lpn_id
2084 , l_parent_uom
2085 , l_parent_txn_qty
2086 , l_parent_pri_qty
2087 , l_parent_sec_qty; --bug 8197506
2088 EXIT WHEN c_parent_mmtt_lines%NOTFOUND;
2089 END IF;
2090
2091 --Bug# 4185621: update parent line posting flag back to 'N'
2092 UPDATE mtl_material_transactions_temp
2093 SET posting_flag = 'N'
2094 WHERE transaction_temp_id = l_parent_txn_temp_id
2095 AND parent_line_id = transaction_temp_id;
2096 -- Bug# 4185621: end change
2097
2098 LOOP
2099 IF l_child_txn_qty = 0 THEN
2100 mydebug('BULK_PICK:fetching a new child record.',l_api_name);
2101
2102 IF c_child_mmtt_lines%ISOPEN THEN
2103 fetch c_child_mmtt_lines into l_child_txn_temp_id, l_child_uom, l_child_txn_qty, l_child_pri_qty, l_child_sec_qty; --bug 8197506
2104 EXIT WHEN c_child_mmtt_lines%NOTFOUND;
2105 ELSIF c_child_mmtt_lines_so%ISOPEN THEN
2106 fetch c_child_mmtt_lines_so into l_child_txn_temp_id, l_child_uom, l_child_txn_qty, l_child_pri_qty,l_child_sec_qty; --bug 8197506
2107 EXIT WHEN c_child_mmtt_lines_so%NOTFOUND;
2108 ELSE fetch c_child_mmtt_lines_short into l_child_txn_temp_id, l_child_uom, l_child_txn_qty, l_child_pri_qty,l_child_sec_qty; --bug 8197506
2109 EXIT WHEN c_child_mmtt_lines_short%NOTFOUND;
2110 END IF;
2111
2112 END IF;
2113
2114 IF l_debug = 1 THEN
2118 mydebug('Current Parent Qty = ' || l_parent_txn_qty || ' : Child Qty = ' || l_child_txn_qty,l_api_name);
2115 mydebug('Child Temp ID = ' || l_child_txn_temp_id,l_api_name);
2116 mydebug('Parent Temp ID = ' || l_parent_txn_temp_id,l_api_name);
2117 mydebug('parent transfer_lpn_id = '|| l_transfer_lpn_id,l_api_name);
2119 mydebug('Current Pri Parent Qty = ' || l_parent_pri_qty || ' : Child Pri Qty = ' || l_child_pri_qty,l_api_name); --bug 8197506
2120 mydebug('Current Sec Parent Qty = ' || l_parent_sec_qty || ' : Child SEc Qty = ' || l_child_sec_qty,l_api_name); --bug 8197506
2121 END IF;
2122
2123 IF l_parent_uom <> l_child_uom THEN
2124 l_child_txn_qty :=
2125 inv_convert.inv_um_convert(
2126 item_id => l_item_id
2127 , PRECISION => NULL
2128 , from_quantity => l_child_txn_qty
2129 , from_unit => l_child_uom
2130 , to_unit => l_parent_uom
2131 , from_name => NULL
2132 , to_name => NULL
2133 );
2134
2135 l_child_uom := l_parent_uom ; --bug#6848907.child qty is in parent uom now
2136 END IF;
2137
2138 IF l_parent_txn_qty >= l_child_txn_qty THEN
2139 UPDATE mtl_material_transactions_temp mmtt
2140 SET mmtt.transaction_header_id = x_new_txn_hdr_id
2141 , mmtt.transfer_lpn_id = l_transfer_lpn_id
2142 , mmtt.lpn_id = l_lpn_id
2143 , mmtt.parent_line_id = l_parent_txn_temp_id
2144 , mmtt.subinventory_code = l_parent_sub_code
2145 , mmtt.locator_id = l_parent_loc_id
2146 , mmtt.transaction_uom = mmtt.item_primary_uom_code
2147 , mmtt.transaction_quantity = mmtt.primary_quantity
2148 , mmtt.last_update_date = SYSDATE
2149 , mmtt.last_updated_by = p_user_id
2150 , mmtt.posting_flag = 'Y' -- Bug# 4185621: change child line posting flag back to 'Y'
2151 , mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line task status is loaded
2152 WHERE mmtt.transaction_temp_id = l_child_txn_temp_id;
2153
2154 l_parent_txn_qty := l_parent_txn_qty - l_child_txn_qty;
2155 l_parent_pri_qty := l_parent_pri_qty - l_child_pri_qty;
2156 l_parent_sec_qty := l_parent_sec_qty - l_child_sec_qty; --bug 8197506
2157 l_child_txn_qty := 0;
2158 l_child_pri_qty := 0;
2159
2160 EXIT WHEN l_parent_txn_qty = 0;
2161 ELSE -- Current Child Qty is greater than Parent Picked Qty
2162 select mtl_material_transactions_s.NEXTVAL
2163 into l_new_temp_id
2164 from dual;
2165
2166 split_child_mmtt(l_child_txn_temp_id,
2167 l_new_temp_id,
2168 l_child_pri_qty - l_parent_pri_qty,
2169 l_child_sec_qty - l_parent_sec_qty ); --bug 8197506
2170
2171
2172 UPDATE mtl_material_transactions_temp mmtt
2173 SET mmtt.transaction_header_id = x_new_txn_hdr_id
2174 , mmtt.primary_quantity = l_parent_pri_qty
2175 , mmtt.secondary_transaction_quantity = l_parent_sec_qty --bug 8197506
2176 , mmtt.parent_line_id = l_parent_txn_temp_id
2177 , mmtt.transfer_lpn_id = l_transfer_lpn_id
2178 , mmtt.lpn_id = l_lpn_id
2179 , mmtt.subinventory_code = l_parent_sub_code
2180 , mmtt.locator_id = l_parent_loc_id
2181 , mmtt.transaction_uom = mmtt.item_primary_uom_code
2182 , mmtt.transaction_quantity = mmtt.primary_quantity
2183 , mmtt.last_update_date = SYSDATE
2184 , mmtt.last_updated_by = p_user_id
2185 , mmtt.posting_flag = 'Y' -- Bug# 4185621: change child line posting flag back to 'Y'
2186 , mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure task status is loaded
2187 WHERE mmtt.transaction_temp_id = l_child_txn_temp_id;
2188
2189 l_child_txn_temp_id := l_new_temp_id;
2190 l_child_pri_qty := l_child_pri_qty - l_parent_pri_qty;
2191 l_child_txn_qty := l_child_txn_qty - l_parent_txn_qty;
2192 l_child_sec_qty := l_child_sec_qty - l_parent_sec_qty; --bug 8197506
2193 l_parent_txn_qty := 0;
2194 l_parent_pri_qty := 0;
2195
2196 mydebug('BULK_PICK:new MMTT tmp id:'||l_new_temp_id,l_api_name);
2197 mydebug('BULK_PICK:new child pri qty:'||l_child_pri_qty,l_api_name);
2198 mydebug('BULK_PICK:new child txn qty:'||l_child_txn_qty,l_api_name);
2199 mydebug('BULK_PICK:new child sec txn qty:'||l_child_sec_qty,l_api_name); --bug 8197506
2200 EXIT;
2201 END IF;
2202 END LOOP;
2203
2204 END LOOP;
2205 -- create subtransfer if there are over picked qty
2206 IF (l_parent_pri_qty >0) THEN
2207 mydebug('BULK_PICK:calling create_sub_transfer to create sub transfer for over qty',l_api_name);
2208 create_sub_transfer(l_parent_txn_temp_id,l_parent_pri_qty,l_parent_pri_qty,l_parent_sec_qty,1); --12747184,added sec qty.
2209 END IF;
2210 IF c_child_mmtt_lines%ISOPEN THEN
2211 close c_child_mmtt_lines;
2212 ELSIF c_child_mmtt_lines_so%ISOPEN THEN
2213 close c_child_mmtt_lines_so ;
2214 ELSE close c_child_mmtt_lines_short;
2215 END IF;
2216 IF c_parent_mmtt_lines%ISOPEN THEN
2217 CLOSE c_parent_mmtt_lines;
2218 ELSE
2219 CLOSE c_parent_mmtt_shortpick;
2220 END IF;
2221
2222 END IF; -- end the association between child and parent lines
2223
2224
2225 IF p_exception= 'SHORT' THEN -- picking short for lot controlled or plain item, need to make sure the cleanup
2226 -- task works for this case, no serial numbers if it is serial controlled
2227 -- should work since it is same as regular task
2228
2229
2230 mydebug('BULK_PICK: v1 calling cleanup_task for mmtt line:'||p_temp_id,l_api_name);
2231 wms_txnrsn_actions_pub.cleanup_task(
2232 p_temp_id => p_temp_id
2233 , p_qty_rsn_id => p_reason_id
2234 , p_user_id => p_user_id
2235 , p_employee_id => p_employee_id
2236 , x_return_status => x_return_status
2237 , x_msg_count => x_msg_count
2238 , x_msg_data => x_msg_data
2239 );
2240
2241 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2242 IF l_debug = 1 THEN
2243 mydebug('BULK_PICK: Error occurred while calling cleanup tasK ',l_api_name);
2244 END IF;
2245 RAISE fnd_api.g_exc_error;
2246 END IF;
2247
2248
2249 END IF;
2250
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 x_return_status := fnd_api.g_ret_sts_unexp_error;
2254 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2255
2256 IF (l_debug = 1) THEN
2257 mydebug('Unexpected Error occurred - ' || SQLERRM,l_api_name);
2258 END IF;
2259
2260 END bulk_pick;
2261
2262
2263 END wms_bulk_pick;
2264