[Home] [Help]
PACKAGE BODY: APPS.WMS_BULK_PICK
Source
1 PACKAGE BODY wms_bulk_pick AS
2 /* $Header: WMSBKPIB.pls 120.2.12010000.2 2008/10/29 22:26:00 mchemban 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_PICKING_PKG.' || 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||
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||
67 'p_start_release_date '||p_start_release_date ||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 IS
136 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
137 BEGIN
138 INSERT INTO mtl_material_transactions_temp
139 (
140 transaction_header_id
141 , transaction_temp_id
142 , source_code
143 , source_line_id
144 , transaction_mode
145 , lock_flag
146 , last_update_date
147 , last_updated_by
148 , creation_date
149 , created_by
150 , last_update_login
151 , request_id
152 , program_application_id
153 , program_id
154 , program_update_date
155 , inventory_item_id
156 , revision
157 , organization_id
158 , subinventory_code
159 , locator_id
160 , transaction_quantity
161 , primary_quantity
162 , transaction_uom
163 , transaction_cost
164 , transaction_type_id
165 , transaction_action_id
166 , transaction_source_type_id
167 , transaction_source_id
168 , transaction_source_name
169 , transaction_date
170 , acct_period_id
171 , distribution_account_id
172 , transaction_reference
173 , requisition_line_id
174 , requisition_distribution_id
175 , reason_id
176 , lot_number
177 , lot_expiration_date
178 , serial_number
179 , receiving_document
180 , demand_id
181 , rcv_transaction_id
182 , move_transaction_id
183 , completion_transaction_id
184 , wip_entity_type
185 , schedule_id
186 , repetitive_line_id
187 , employee_code
188 , primary_switch
189 , schedule_update_code
190 , setup_teardown_code
191 , item_ordering
192 , negative_req_flag
193 , operation_seq_num
194 , picking_line_id
195 , trx_source_line_id
196 , trx_source_delivery_id
197 , physical_adjustment_id
198 , cycle_count_id
199 , rma_line_id
200 , customer_ship_id
201 , currency_code
202 , currency_conversion_rate
203 , currency_conversion_type
204 , currency_conversion_date
205 , ussgl_transaction_code
206 , vendor_lot_number
207 , encumbrance_account
208 , encumbrance_amount
209 , ship_to_location
210 , shipment_number
211 , transfer_cost
212 , transportation_cost
213 , transportation_account
214 , freight_code
215 , containers
216 , waybill_airbill
217 , expected_arrival_date
218 , transfer_subinventory
219 , transfer_organization
220 , transfer_to_location
221 , new_average_cost
222 , value_change
223 , percentage_change
224 , material_allocation_temp_id
225 , demand_source_header_id
226 , demand_source_line
227 , demand_source_delivery
228 , item_segments
229 , item_description
230 , item_trx_enabled_flag
231 , item_location_control_code
232 , item_restrict_subinv_code
233 , item_restrict_locators_code
234 , item_revision_qty_control_code
235 , item_primary_uom_code
236 , item_uom_class
237 , item_shelf_life_code
238 , item_shelf_life_days
239 , item_lot_control_code
240 , item_serial_control_code
241 , item_inventory_asset_flag
242 , allowed_units_lookup_code
243 , department_id
244 , department_code
245 , wip_supply_type
246 , supply_subinventory
247 , supply_locator_id
248 , valid_subinventory_flag
249 , valid_locator_flag
250 , locator_segments
251 , current_locator_control_code
252 , number_of_lots_entered
253 , wip_commit_flag
254 , next_lot_number
255 , lot_alpha_prefix
256 , next_serial_number
257 , serial_alpha_prefix
258 , shippable_flag
259 , posting_flag
260 , required_flag
261 , process_flag
262 , ERROR_CODE
263 , error_explanation
264 , attribute_category
265 , attribute1
266 , attribute2
267 , attribute3
268 , attribute4
269 , attribute5
270 , attribute6
274 , attribute10
271 , attribute7
272 , attribute8
273 , attribute9
275 , attribute11
276 , attribute12
277 , attribute13
278 , attribute14
279 , attribute15
280 , movement_id
281 , reservation_quantity
282 , shipped_quantity
283 , transaction_line_number
284 , task_id
285 , to_task_id
286 , source_task_id
287 , project_id
288 , source_project_id
289 , pa_expenditure_org_id
290 , to_project_id
291 , expenditure_type
292 , final_completion_flag
293 , transfer_percentage
294 , transaction_sequence_id
295 , material_account
296 , material_overhead_account
297 , resource_account
298 , outside_processing_account
299 , overhead_account
300 , flow_schedule
301 , cost_group_id
302 , demand_class
303 , qa_collection_id
304 , kanban_card_id
305 , overcompletion_transaction_id
306 , overcompletion_primary_qty
307 , overcompletion_transaction_qty
308 , end_item_unit_number
309 , scheduled_payback_date
310 , line_type_code
311 , parent_transaction_temp_id
312 , put_away_strategy_id
313 , put_away_rule_id
314 , pick_strategy_id
315 , pick_rule_id
316 , common_bom_seq_id
317 , common_routing_seq_id
318 , cost_type_id
319 , org_cost_group_id
320 , move_order_line_id
321 , task_group_id
322 , pick_slip_number
323 , reservation_id
324 , transaction_status
325 , transfer_cost_group_id
326 , lpn_id
327 , transfer_lpn_id
328 , content_lpn_id
329 , cartonization_id
330 , standard_operation_id
331 , wms_task_type
332 , task_priority
333 , container_item_id
334 , operation_plan_id
335 , parent_line_id
336 , serial_allocated_flag
337 , move_order_header_id
338 , wms_task_status -- Bug# 4185621
339 )
340 (SELECT transaction_header_id
341 , p_new_child_temp_id
342 , source_code
343 , source_line_id
344 , transaction_mode
345 , lock_flag
346 , SYSDATE
347 , last_updated_by
348 , SYSDATE
349 , created_by
350 , last_update_login
351 , request_id
352 , program_application_id
353 , program_id
354 , program_update_date
355 , inventory_item_id
356 , revision
357 , organization_id
358 , subinventory_code
359 , locator_id
360 , p_split_pri_qty
361 , p_split_pri_qty -- only the primary UOM is used
362 , item_primary_uom_code -- transaction_uom
363 , transaction_cost
364 , transaction_type_id
365 , transaction_action_id
366 , transaction_source_type_id
367 , transaction_source_id
368 , transaction_source_name
369 , transaction_date
370 , acct_period_id
371 , distribution_account_id
372 , transaction_reference
373 , requisition_line_id
374 , requisition_distribution_id
375 , reason_id
376 , lot_number
377 , lot_expiration_date
378 , serial_number
379 , receiving_document
380 , demand_id
381 , rcv_transaction_id
382 , move_transaction_id
383 , completion_transaction_id
384 , wip_entity_type
385 , schedule_id
386 , repetitive_line_id
387 , employee_code
388 , primary_switch
389 , schedule_update_code
390 , setup_teardown_code
391 , item_ordering
392 , negative_req_flag
393 , operation_seq_num
394 , picking_line_id
395 , trx_source_line_id
396 , trx_source_delivery_id
400 , customer_ship_id
397 , physical_adjustment_id
398 , cycle_count_id
399 , rma_line_id
401 , currency_code
402 , currency_conversion_rate
403 , currency_conversion_type
404 , currency_conversion_date
405 , ussgl_transaction_code
406 , vendor_lot_number
407 , encumbrance_account
408 , encumbrance_amount
409 , ship_to_location
410 , shipment_number
411 , transfer_cost
412 , transportation_cost
413 , transportation_account
414 , freight_code
415 , containers
416 , waybill_airbill
417 , expected_arrival_date
418 , transfer_subinventory
419 , transfer_organization
420 , transfer_to_location
421 , new_average_cost
422 , value_change
423 , percentage_change
424 , material_allocation_temp_id
425 , demand_source_header_id
426 , demand_source_line
427 , demand_source_delivery
428 , item_segments
429 , item_description
430 , item_trx_enabled_flag
431 , item_location_control_code
432 , item_restrict_subinv_code
433 , item_restrict_locators_code
434 , item_revision_qty_control_code
435 , item_primary_uom_code
436 , item_uom_class
437 , item_shelf_life_code
438 , item_shelf_life_days
439 , item_lot_control_code
440 , item_serial_control_code
441 , item_inventory_asset_flag
442 , allowed_units_lookup_code
443 , department_id
444 , department_code
445 , wip_supply_type
446 , supply_subinventory
447 , supply_locator_id
448 , valid_subinventory_flag
449 , valid_locator_flag
450 , locator_segments
451 , current_locator_control_code
452 , number_of_lots_entered
453 , wip_commit_flag
454 , next_lot_number
455 , lot_alpha_prefix
456 , next_serial_number
457 , serial_alpha_prefix
458 , shippable_flag
459 , 'Y' --posting_flag Bug#4185621: make sure new child mmtt is posting
460 , required_flag
461 , process_flag
462 , ERROR_CODE
463 , error_explanation
464 , attribute_category
465 , attribute1
466 , attribute2
467 , attribute3
468 , attribute4
469 , attribute5
470 , attribute6
471 , attribute7
472 , attribute8
473 , attribute9
474 , attribute10
475 , attribute11
476 , attribute12
477 , attribute13
478 , attribute14
479 , attribute15
480 , movement_id
481 , reservation_quantity
482 , shipped_quantity
483 , transaction_line_number
484 , task_id
485 , to_task_id
486 , source_task_id
487 , project_id
488 , source_project_id
489 , pa_expenditure_org_id
490 , to_project_id
491 , expenditure_type
492 , final_completion_flag
493 , transfer_percentage
494 , transaction_sequence_id
495 , material_account
496 , material_overhead_account
497 , resource_account
498 , outside_processing_account
499 , overhead_account
500 , flow_schedule
501 , cost_group_id
502 , demand_class
503 , qa_collection_id
504 , kanban_card_id
505 , overcompletion_transaction_id
506 , overcompletion_primary_qty
507 , overcompletion_transaction_qty
508 , end_item_unit_number
509 , scheduled_payback_date
510 , line_type_code
511 , parent_transaction_temp_id
512 , put_away_strategy_id
513 , put_away_rule_id
514 , pick_strategy_id
515 , pick_rule_id
516 , common_bom_seq_id
517 , common_routing_seq_id
518 , cost_type_id
519 , org_cost_group_id
520 , move_order_line_id
521 , task_group_id
525 , transfer_cost_group_id
522 , pick_slip_number
523 , reservation_id
524 , transaction_status
526 , lpn_id
527 , transfer_lpn_id
528 , content_lpn_id
529 , cartonization_id
530 , standard_operation_id
531 , wms_task_type
532 , task_priority
533 , container_item_id
534 , operation_plan_id
535 , parent_line_id
536 , serial_allocated_flag
537 , move_order_line_id
538 , l_g_task_loaded -- Bug# 4185621: loaded status
539 FROM mtl_material_transactions_temp
540 WHERE transaction_temp_id = p_child_temp_id);
541
542 -- update the old line with remaining qty
543 update mtl_material_transactions_temp
544 set primary_quantity = primary_quantity-p_split_pri_qty, -- UOM
545 transaction_quantity = transaction_quantity-p_split_pri_qty
546 where transaction_temp_id = p_child_temp_id;
547
548 END split_child_mmtt;
549
550 PROCEDURE split_child_mtlt(p_child_temp_id NUMBER,
551 p_new_child_temp_id NUMBER,
552 p_new_serial_temp_id NUMBER,
553 p_split_pri_qty NUMBER,
554 p_child_lot_number VARCHAR2) --v1 Bug 3902766
555 IS
556 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
557 BEGIN
558 -- insert into mtlt and update the qty for old line
559 INSERT INTO mtl_transaction_lots_temp
560 (
561 transaction_temp_id
562 , last_update_date
563 , last_updated_by
564 , creation_date
565 , created_by
566 , last_update_login
567 , request_id
568 , program_application_id
569 , program_id
570 , program_update_date
571 , transaction_quantity
572 , primary_quantity
573 , lot_number
574 , lot_expiration_date
575 , ERROR_CODE
576 , serial_transaction_temp_id
577 , group_header_id
578 , put_away_rule_id
579 , pick_rule_id
580 , description
581 , vendor_id
582 , supplier_lot_number
583 , territory_code
584 , --country_of_origin,
585 origination_date
586 , date_code
587 , grade_code
588 , change_date
589 , maturity_date
590 , status_id
591 , retest_date
592 , age
593 , item_size
594 , color
595 , volume
596 , volume_uom
597 , place_of_origin
598 , --kill_date,
599 best_by_date
600 , LENGTH
601 , length_uom
602 , recycled_content
603 , thickness
604 , thickness_uom
605 , width
606 , width_uom
607 , curl_wrinkle_fold
608 , lot_attribute_category
609 , c_attribute1
610 , c_attribute2
611 , c_attribute3
612 , c_attribute4
613 , c_attribute5
614 , c_attribute6
615 , c_attribute7
616 , c_attribute8
617 , c_attribute9
618 , c_attribute10
619 , c_attribute11
620 , c_attribute12
621 , c_attribute13
622 , c_attribute14
623 , c_attribute15
624 , c_attribute16
625 , c_attribute17
626 , c_attribute18
627 , c_attribute19
628 , c_attribute20
629 , d_attribute1
630 , d_attribute2
631 , d_attribute3
632 , d_attribute4
633 , d_attribute5
634 , d_attribute6
635 , d_attribute7
636 , d_attribute8
637 , d_attribute9
638 , d_attribute10
639 , n_attribute1
640 , n_attribute2
641 , n_attribute3
642 , n_attribute4
643 , n_attribute5
644 , n_attribute6
645 , n_attribute7
646 , n_attribute8
647 , n_attribute9
648 , n_attribute10
649 , vendor_name
650 )
651 (SELECT
652 p_new_child_temp_id
653 , last_update_date
654 , last_updated_by
655 , creation_date
656 , created_by
657 , last_update_login
658 , request_id
659 , program_application_id
660 , program_id
664 , lot_number
661 , program_update_date
662 , p_split_pri_qty
663 , p_split_pri_qty
665 , lot_expiration_date
666 , ERROR_CODE
667 , p_new_serial_temp_id
668 , group_header_id
669 , put_away_rule_id
670 , pick_rule_id
671 , description
672 , vendor_id
673 , supplier_lot_number
674 , territory_code
675 , --country_of_origin,
676 origination_date
677 , date_code
678 , grade_code
679 , change_date
680 , maturity_date
681 , status_id
682 , retest_date
683 , age
684 , item_size
685 , color
686 , volume
687 , volume_uom
688 , place_of_origin
689 , --kill_date,
690 best_by_date
691 , LENGTH
692 , length_uom
693 , recycled_content
694 , thickness
695 , thickness_uom
696 , width
697 , width_uom
698 , curl_wrinkle_fold
699 , lot_attribute_category
700 , c_attribute1
701 , c_attribute2
702 , c_attribute3
703 , c_attribute4
704 , c_attribute5
705 , c_attribute6
706 , c_attribute7
707 , c_attribute8
708 , c_attribute9
709 , c_attribute10
710 , c_attribute11
711 , c_attribute12
712 , c_attribute13
713 , c_attribute14
714 , c_attribute15
715 , c_attribute16
716 , c_attribute17
717 , c_attribute18
718 , c_attribute19
719 , c_attribute20
720 , d_attribute1
721 , d_attribute2
722 , d_attribute3
723 , d_attribute4
724 , d_attribute5
725 , d_attribute6
726 , d_attribute7
727 , d_attribute8
728 , d_attribute9
729 , d_attribute10
730 , n_attribute1
731 , n_attribute2
732 , n_attribute3
733 , n_attribute4
734 , n_attribute5
735 , n_attribute6
736 , n_attribute7
737 , n_attribute8
738 , n_attribute9
739 , n_attribute10
740 , vendor_name
741 FROM mtl_transaction_lots_temp
742 WHERE transaction_temp_id = p_child_temp_id
743 AND lot_number = p_child_lot_number); --v1 Bug 3902766
744
745 -- update the old line with remaining qty
746 update mtl_transaction_lots_temp
747 set primary_quantity = primary_quantity-p_split_pri_qty, -- UOM
748 transaction_quantity = transaction_quantity-p_split_pri_qty -- UOM
749 where transaction_temp_id = p_child_temp_id
750 and lot_number = p_child_lot_number; --v1 Bug 3902766
751
752 END split_child_mtlt;
753
754 PROCEDURE update_child(p_child_temp_id NUMBER,
755 p_parent_temp_id NUMBER,
756 p_new_txn_hdr_id NUMBER) IS
757 l_parent_uom VARCHAR2(10);
758 l_child_uom VARCHAR2(10);
759
760 l_parent_sub_code VARCHAR2(30);
761 l_parent_loc_id NUMBER;
762 l_lpn_id NUMBER;
763 l_transfer_lpn_id NUMBER;
764 l_api_name VARCHAR2(32):= 'update_child';
765 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
766
767 BEGIN
768 IF l_debug=1 THEN
769 mydebug('get parent line info, parent temp id:'||p_parent_temp_id,l_api_name);
770 END IF;
771
772 -- get the information in parent
773 select transfer_lpn_id,nvl(lpn_id,content_lpn_id),subinventory_code,locator_id, transaction_uom
774 into l_transfer_lpn_id,l_lpn_id,l_parent_sub_code,l_parent_loc_id,l_parent_uom
775 from mtl_material_transactions_temp
776 where transaction_temp_id = p_parent_temp_id;
777
778 IF p_child_temp_id is not null THEN
779 IF l_debug=1 THEN
780 mydebug('update child line '||p_child_temp_id||' with parent info and new header id '||p_new_txn_hdr_id,l_api_name);
781 END IF;
782
783 -- update child line with the correct parent info
784 UPDATE mtl_material_transactions_temp mmtt
785 SET mmtt.transaction_header_id = p_new_txn_hdr_id
786 , mmtt.transfer_lpn_id = l_transfer_lpn_id
787 , mmtt.lpn_id = l_lpn_id
788 , mmtt.parent_line_id = p_parent_temp_id
789 , mmtt.subinventory_code = l_parent_sub_code
790 , mmtt.locator_id = l_parent_loc_id
791 , mmtt.transaction_uom = mmtt.item_primary_uom_code
792 , mmtt.transaction_quantity = mmtt.primary_quantity
793 , mmtt.last_update_date = SYSDATE
794 , mmtt.last_updated_by = FND_GLOBAL.USER_ID
795 , mmtt.posting_flag = 'Y' -- Bug# 4185621: make sure child line mmtt is now posting
799 -- update all child lines with the correct parent info
796 , mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line mmtt task status is loaded
797 WHERE mmtt.transaction_temp_id = p_child_temp_id;
798 ELSE
800 IF l_debug=1 THEN
801 mydebug('update all child lines with parent info and new header id '||p_new_txn_hdr_id,l_api_name);
802 END IF;
803 UPDATE mtl_material_transactions_temp mmtt
804 SET mmtt.transaction_header_id = p_new_txn_hdr_id
805 , mmtt.transfer_lpn_id = l_transfer_lpn_id
806 , mmtt.lpn_id = l_lpn_id
807 , mmtt.parent_line_id = p_parent_temp_id
808 , mmtt.subinventory_code = l_parent_sub_code
809 , mmtt.locator_id = l_parent_loc_id
810 , mmtt.transaction_uom = mmtt.item_primary_uom_code
811 , mmtt.transaction_quantity = mmtt.primary_quantity
812 , mmtt.last_update_date = SYSDATE
813 , mmtt.last_updated_by = FND_GLOBAL.USER_ID
814 , mmtt.posting_flag = 'Y' -- Bug# 4185621: make sure child line mmtt is now posting
815 , mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line mmtt task status is loaded
816 WHERE mmtt.transaction_temp_id <> p_parent_temp_id
817 and mmtt.parent_line_id = p_parent_temp_id;
818 END IF;
819
820 END update_child;
821
822 PROCEDURE create_sub_transfer(p_from_temp_id NUMBER,
823 p_pri_qty NUMBER,
824 p_txn_qty NUMBER,
825 p_lot_controlled VARCHAR2
826 ) IS
827 l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
828 l_mtlt_rec mtl_transaction_lots_temp%ROWTYPE;
829 l_new_temp_id NUMBER;
830 l_api_name VARCHAR2(30) := 'create_sub_transfer';
831 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
832
833 CURSOR over_picked_lots(p_temp_id NUMBER) IS
834 select sum(transaction_quantity) transaction_quantity,
835 sum(primary_quantity) primary_quantity, lot_number
836 from mtl_allocations_gtmp
837 where transaction_temp_id = p_temp_id
838 group by lot_number;
839
840 BEGIN
841 -- need to copy the child line since the child line contains the neccessary info
842 -- which the sub transfer needs
843 select *
844 into l_mmtt_rec
845 from mtl_material_transactions_temp
846 where transaction_temp_id <> p_from_temp_id
847 and parent_line_id = p_from_temp_id
848 and rownum <2;
849
850 IF l_debug=1 THEN
851 mydebug('BULK_PICK:create_sub_transfer:get the rec for the child:'||l_mmtt_rec.transaction_temp_id,
852 l_api_name);
853 END IF;
854
855 select mtl_material_transactions_s.NEXTVAL
856 into l_new_temp_id
857 from dual;
858
859 l_mmtt_rec.primary_quantity := p_pri_qty;
860 l_mmtt_rec.transaction_quantity := p_txn_qty;
861 l_mmtt_rec.transaction_temp_id := l_new_temp_id;
862 l_mmtt_rec.move_order_line_id := null;
863 l_mmtt_rec.operation_plan_id := null;
864 l_mmtt_rec.standard_operation_id := null;
865 l_mmtt_rec.cartonization_id := null;
866 l_mmtt_rec.trx_source_line_id := null;
867 l_mmtt_rec.transaction_source_id := null;
868 l_mmtt_rec.demand_source_line := null;
869 l_mmtt_rec.pick_rule_id := null;
870 l_mmtt_rec.reservation_id := null;
871 l_mmtt_rec.wms_task_type := null;
872 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
873 l_mmtt_rec.transfer_subinventory := null;
874 l_mmtt_rec.transfer_to_location := null;
875
876 l_mmtt_rec.posting_flag := 'Y'; -- will be shown in the qty tree
877 l_mmtt_rec.transaction_source_type_id := 13;
878 l_mmtt_rec.transaction_action_id := 2;
879 l_mmtt_rec.transaction_type_id := 2; -- inventory sub transfer
880 -- the sub transfer transaction will have the same parent_line_id as the
881 -- child line so that it can be used in unload
882
883
884
885 wms_task_dispatch_engine.insert_mmtt(l_mmtt_rec);
886
887 -- create lot record
888 IF (p_lot_controlled = 'Y') THEN -- lot controlled
889 FOR lot_line in over_picked_lots(p_from_temp_id) LOOP
890 select *
891 into l_mtlt_rec
892 from mtl_transaction_lots_temp
893 where transaction_temp_id = p_from_temp_id
894 and lot_number = lot_line.lot_number;
895
896 l_mtlt_rec.transaction_temp_id := l_new_temp_id;
897 l_mtlt_rec.primary_quantity := lot_line.primary_quantity;
898 l_mtlt_rec.transaction_quantity := lot_line.primary_quantity; -- UOM will be using the primary uom
899
900 l_mtlt_rec.serial_transaction_temp_id := null; -- serial numbers never be allocated, so we can't create
901 -- serial records for the sub transfer
902
903 -- insert lot rec
904 inv_rcv_common_apis.insert_mtlt(l_mtlt_rec);
905
906 END LOOP; -- end loop through each lot
907 END IF; -- end lot process
908
909 END create_sub_transfer;
910 /*
911 -- This procedure will be used for distributing the quantity
912 -- picked (in one or more parent MMTT lines) to the original
913 -- child MMTT lines (again one or more)
914 */
915
916
920 p_multiple_pick IN VARCHAR2, -- to indicate if this is multiple pick or not
917 PROCEDURE bulk_pick(p_temp_id IN NUMBER,
918 p_txn_hdr_id IN NUMBER,
919 p_org_id IN NUMBER,
921 p_exception IN VARCHAR2, -- to indicate if this is over picking or short pick
922 p_lot_controlled IN VARCHAR2,
923 p_user_id IN NUMBER,
924 p_employee_id IN NUMBER,
925 p_reason_id IN NUMBER,
926 x_new_txn_hdr_id OUT NOCOPY NUMBER,
927 x_return_status OUT NOCOPY VARCHAR2,
928 x_msg_count OUT NOCOPY NUMBER,
929 x_msg_data OUT NOCOPY VARCHAR2)
930 IS
931
932
933 CURSOR c_parent_mmtt_lines IS
934 SELECT mmtt.transaction_temp_id
935 , mmtt.inventory_item_id
936 , mmtt.subinventory_code
937 , mmtt.locator_id
938 , NVL(mmtt.content_lpn_id, mmtt.lpn_id)
939 , mmtt.transfer_lpn_id
940 , mmtt.transaction_uom
941 , mmtt.transaction_quantity
942 , mmtt.primary_quantity
943 FROM mtl_material_transactions_temp mmtt
944 WHERE mmtt.transaction_header_id = p_txn_hdr_id
945 AND mmtt.organization_id = p_org_id
946 AND mmtt.transaction_quantity > 0
947 AND mmtt.parent_line_id = mmtt.transaction_temp_id -- make sure it is only parent lines
948 ORDER BY mmtt.transaction_quantity DESC;
949
950 CURSOR c_parent_mmtt_shortpick IS
951 SELECT mmtt.transaction_temp_id
952 , mmtt.inventory_item_id
953 , mmtt.subinventory_code
954 , mmtt.locator_id
955 , NVL(mmtt.content_lpn_id, mmtt.lpn_id)
956 , mmtt.transfer_lpn_id
957 , mmtt.transaction_uom
958 , mmtt.transaction_quantity
959 , mmtt.primary_quantity
960 FROM mtl_material_transactions_temp mmtt
961 WHERE mmtt.transaction_header_id = p_txn_hdr_id
962 AND mmtt.organization_id = p_org_id
963 AND mmtt.transaction_quantity > 0
964 AND mmtt.parent_line_id <> p_temp_id
965 AND mmtt.parent_line_id = mmtt.transaction_temp_id -- make sure it is only parent lines
966 ORDER BY mmtt.transaction_quantity DESC;
967
968
969
970
971 CURSOR c_parent_mmtt_lines_for_lots IS
972 SELECT mmtt.transaction_temp_id
973 , mmtt.transfer_lpn_id
974 , mtlt.lot_number
975 , mtlt.transaction_quantity lot_trx_qty
976 , mtlt.primary_quantity lot_primary_qty
977 FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
978 WHERE mmtt.transaction_header_id = p_txn_hdr_id
979 AND mmtt.organization_id = p_org_id
980 AND mmtt.transaction_quantity > 0
981 AND mmtt.parent_line_id = mmtt.transaction_temp_id
982 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
983 ORDER BY mmtt.transaction_quantity DESC;
984
985 CURSOR c_parent_mmtt_shorpick_lots IS
986 SELECT mmtt.transaction_temp_id
987 , mmtt.transfer_lpn_id
988 , mtlt.lot_number
989 , mtlt.transaction_quantity lot_trx_qty
990 , mtlt.primary_quantity lot_primary_qty
991 FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
992 WHERE mmtt.transaction_header_id = p_txn_hdr_id
993 AND mmtt.organization_id = p_org_id
994 AND mmtt.transaction_quantity > 0
995 AND mmtt.parent_line_id <> p_temp_id
996 AND mmtt.parent_line_id = mmtt.transaction_temp_id
997 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
998 ORDER BY mmtt.transaction_quantity DESC;
999
1000
1001 CURSOR c_child_mmtt_lines IS
1002 SELECT mmtt.transaction_temp_id
1003 , mmtt.transaction_uom
1004 , mmtt.transaction_quantity
1005 , mmtt.primary_quantity
1006 FROM mtl_material_transactions_temp mmtt
1007 WHERE mmtt.parent_line_id = p_temp_id
1008 AND mmtt.parent_line_id <> mmtt.transaction_temp_id -- exclude the parent line
1009 AND mmtt.organization_id = p_org_id
1010 ORDER BY mmtt.transaction_quantity DESC;
1011
1012 CURSOR c_child_mmtt_lines_so IS
1013 SELECT mmtt.transaction_temp_id
1014 , mmtt.transaction_uom
1015 , mmtt.transaction_quantity
1016 , mmtt.primary_quantity
1017 FROM mtl_material_transactions_temp mmtt,mtl_txn_request_lines mol,
1018 wsh_delivery_details wdd,wsh_delivery_assignments_v wda
1019 WHERE mmtt.parent_line_id = p_temp_id
1020 AND mmtt.parent_line_id <> mmtt.transaction_temp_id -- exclude the parent line
1021 AND mmtt.organization_id = p_org_id
1022 AND mol.line_id = mmtt.move_order_line_id
1023 AND mol.line_id = wdd.move_order_line_id
1024 AND wdd.released_status = 'S' --Bug#6848907
1025 AND wda.delivery_detail_id = wdd.delivery_detail_id
1026 ORDER BY nvl(wda.delivery_id,mol.carton_grouping_id), mmtt.transaction_quantity DESC;
1030 , mmtt.transaction_uom
1027
1028 CURSOR c_child_mmtt_lines_short IS
1029 SELECT mmtt.transaction_temp_id
1031 , mmtt.transaction_quantity
1032 , mmtt.primary_quantity
1033 FROM mtl_material_transactions_temp mmtt,mtl_txn_request_lines mol,
1034 wsh_delivery_details wdd,wsh_delivery_assignments_v wda,
1035 oe_order_lines_all ol,mtl_txn_request_headers moh
1036 WHERE mmtt.parent_line_id = p_temp_id
1037 AND mmtt.parent_line_id <> mmtt.transaction_temp_id -- exclude the parent line
1038 AND mmtt.organization_id = p_org_id
1039 AND mol.line_id = mmtt.move_order_line_id
1040 AND mol.line_id = wdd.move_order_line_id
1041 AND wdd.released_status = 'S' --Bug#6848907
1042 AND wda.delivery_detail_id = wdd.delivery_detail_id
1043 AND ol.line_id = wdd.source_line_id
1044 AND mol.header_id = moh.header_id
1045 ORDER BY ol. SCHEDULE_SHIP_DATE,
1046 nvl(wda.delivery_id,mol.carton_grouping_id),
1047 moh.creation_date, -- mmtt creation date is changed for splitting, so moh date will be better
1048 mmtt.transaction_quantity DESC;
1049
1050 c_mmtt_line c_child_mmtt_lines%ROWTYPE;
1051
1052 CURSOR c_child_lots(p_child_transaction_temp_id NUMBER) IS
1053 SELECT
1054 mtlt.lot_number
1055 , mtlt.transaction_quantity
1056 , mtlt.primary_quantity
1057 FROM mtl_transaction_lots_temp mtlt
1058 WHERE
1059 mtlt.transaction_temp_id = p_child_transaction_temp_id;
1060
1061
1062 CURSOR c_lot_parents(p_lot_number VARCHAR2) IS
1063 SELECT mag.transaction_temp_id,
1064 mag.primary_quantity,
1065 mag.transaction_quantity
1066 FROM mtl_allocations_gtmp mag
1067 WHERE lot_number = p_lot_number
1068 ORDER BY search_sequence;
1069
1070
1071
1072 CURSOR over_picked_lines IS
1073 SELECT sum(transaction_quantity) transaction_quantity,
1074 sum(primary_quantity) primary_quantity, transaction_temp_id
1075 from mtl_allocations_gtmp
1076 group by transaction_temp_id;
1077
1078
1079 l_parent_txn_qty NUMBER;
1080 l_child_txn_qty NUMBER:= 0;
1081 l_parent_pri_qty NUMBER;
1082 l_child_pri_qty NUMBER;
1083 l_parent_uom VARCHAR2(10);
1084 l_child_uom VARCHAR2(10);
1085 l_primary_uom VARCHAR2(10);
1086 l_parent_txn_temp_id NUMBER;
1087 l_child_txn_temp_id NUMBER;
1088 l_item_id NUMBER;
1089 l_parent_sub_code VARCHAR2(30);
1090 l_parent_loc_id NUMBER;
1091 l_lpn_id NUMBER;
1092 l_transfer_lpn_id NUMBER;
1093 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1094 l_new_temp_id NUMBER;
1095 l_serial_temp_id NUMBER;
1096 l_api_name VARCHAR2(30) := 'bulk_pick';
1097 l_mmtt_qty NUMBER;
1098 l_new_serial_temp_id NUMBER;
1099 L_CHILD_LOT_TXN_QTY NUMBER;
1100 l_link_mtlt_needed VARCHAR2(1) := 'N';
1101 l_process_qty NUMBER;
1102 l_move_order_type NUMBER;
1103 g_not_lot_controlled constant NUMBER := 1;
1104 g_lot_controlled constant NUMBER := 2;
1105 l_unpicked_serial_rec_exists VARCHAR2(1);
1106 l_transaction_action_id NUMBER;
1107 l_search_sequence NUMBER;
1108 l_temp_id NUMBER;
1109
1110 BEGIN
1111 x_return_status := fnd_api.g_ret_sts_success;
1112
1113 IF l_debug = 1 THEN
1114 mydebug('Dispatching Bulk Pick Tasks for TxnHdrID = ' || p_txn_hdr_id || ' : TxnTempID = ' || p_temp_id,l_api_name);
1115 mydebug('p_multiple_pick:'||p_multiple_pick,l_api_name);
1116 mydebug('p_exception:'||p_exception,l_api_name);
1117 mydebug('Lot control code : '|| p_lot_controlled, l_api_name);
1118 END IF;
1119
1120 -- processed child lines will get a new header id
1121
1122 SELECT mtl_material_transactions_s.NEXTVAL
1123 INTO x_new_txn_hdr_id
1124 FROM DUAL;
1125
1126 IF (l_debug = 1) THEN
1127 mydebug('New header id the child lines will have :'||x_new_txn_hdr_id,l_api_name);
1128 END IF;
1129
1130 l_temp_id := p_temp_id;
1131 -- get the transaction action to be used later on
1132 BEGIN
1133 SELECT transaction_action_id
1134 INTO l_transaction_action_id
1135 FROM mtl_material_transactions_temp mmtt
1136 WHERE
1137 mmtt.transaction_temp_id = p_temp_id;
1138 EXCEPTION
1139 WHEN NO_DATA_FOUND THEN -- when the line merged from APL and old temp id is not there any more
1140 SELECT transaction_action_id,transaction_temp_id
1141 INTO l_transaction_action_id,l_temp_id
1142 FROM mtl_material_transactions_temp mmtt
1143 WHERE transaction_header_id = p_txn_hdr_id
1144 and rownum<2;
1145 END;
1146
1147 IF (l_debug = 1) THEN
1148 mydebug('transaction action id :'||l_transaction_action_id,l_api_name);
1149 END IF;
1150
1151
1152
1153 -- if it is complete single pick, nothing need to be done except to update the child line with
1154 -- the parent line info
1155 IF (p_exception is null and p_multiple_pick='N' ) THEN
1156
1160 update_child(null,l_temp_id,x_new_txn_hdr_id);
1157 IF (l_debug = 1) THEN mydebug('This is a complete single pick!',l_api_name);
1158 END IF;
1159 -- update all child line with the correct parent info
1161
1162 --Bug# 4185621: update parent line posting flag back to 'N'
1163 UPDATE mtl_material_transactions_temp
1164 SET posting_flag = 'N'
1165 WHERE transaction_temp_id = l_temp_id
1166 AND parent_line_id = transaction_temp_id;
1167 -- Bug# 4185621: end change
1168
1169 return;
1170 END IF;
1171
1172 -- make sure the global temp table is emplty
1173 IF (l_debug = 1) THEN mydebug('deleting the global temp table ',l_api_name); END IF;
1174 delete mtl_allocations_gtmp;
1175
1176 -- populate the temp table mtl_allocations_gtmp
1177 if (p_lot_controlled = 'Y' ) THEN -- lot controlled
1178
1179 IF (l_debug = 1) THEN mydebug('inserting the records to the tmp table',l_api_name); END IF;
1180
1181 l_search_sequence := 0;
1182
1183
1184
1185 IF (p_exception= 'SHORT') THEN
1186
1187 IF (l_debug = 1) THEN mydebug('Inserting from c_parent_mmtt_shorpick_lots v1',l_api_name); END IF;
1188 FOR c_parent_lines_rec IN c_parent_mmtt_shorpick_lots LOOP
1189 l_search_sequence := l_search_sequence +1;
1190 insert into mtl_allocations_gtmp
1191 ( TRANSACTION_TEMP_ID
1192 , TRANSFER_LPN_ID
1193 , LOT_NUMBER
1194 , TRANSACTION_QUANTITY
1195 , PRIMARY_QUANTITY
1196 , SEARCH_SEQUENCE)
1197 VALUES (
1198 c_parent_lines_rec.transaction_temp_id
1199 , c_parent_lines_rec.transfer_lpn_id
1200 , c_parent_lines_rec.lot_number
1201 , c_parent_lines_rec.lot_trx_qty
1202 , c_parent_lines_rec.lot_primary_qty
1203 , l_search_sequence);
1204
1205 --Bug# 4185621: update parent line posting flag back to 'N'
1206 UPDATE mtl_material_transactions_temp
1207 SET posting_flag = 'N'
1208 WHERE transaction_temp_id = c_parent_lines_rec.transaction_temp_id
1209 AND parent_line_id = transaction_temp_id;
1210 -- Bug# 4185621: end change
1211
1212 END LOOP;
1213 ELSE
1214 FOR c_parent_lines_rec IN c_parent_mmtt_lines_for_lots LOOP
1215 l_search_sequence := l_search_sequence +1;
1216 insert into mtl_allocations_gtmp
1217 ( TRANSACTION_TEMP_ID
1218 , TRANSFER_LPN_ID
1219 , LOT_NUMBER
1220 , TRANSACTION_QUANTITY
1221 , PRIMARY_QUANTITY
1222 , SEARCH_SEQUENCE)
1223 VALUES (
1224 c_parent_lines_rec.transaction_temp_id
1225 , c_parent_lines_rec.transfer_lpn_id
1226 , c_parent_lines_rec.lot_number
1227 , c_parent_lines_rec.lot_trx_qty
1228 , c_parent_lines_rec.lot_primary_qty
1229 , l_search_sequence);
1230
1231 --Bug# 4185621: update parent line posting flag back to 'N'
1232 UPDATE mtl_material_transactions_temp
1233 SET posting_flag = 'N'
1234 WHERE transaction_temp_id = c_parent_lines_rec.transaction_temp_id
1235 AND parent_line_id = transaction_temp_id;
1236 -- Bug# 4185621: end change
1237
1238 END LOOP;
1239 END IF;
1240
1241 IF (l_debug = 1) THEN mydebug( l_search_sequence||' parent lines are inserted',l_api_name); END IF;
1242
1243 IF (l_transaction_action_id = 28 and p_exception= 'SHORT') THEN
1244 IF (l_debug = 1) THEN mydebug('opening c_child_mmtt_lines_short...',l_api_name); END IF;
1245 OPEN c_child_mmtt_lines_short;
1246 ELSIF l_transaction_action_id = 28 THEN
1247 IF (l_debug = 1) THEN mydebug('opening c_child_mmtt_lines_so ...',l_api_name); END IF;
1248 OPEN c_child_mmtt_lines_so;
1249 ELSE OPEN c_child_mmtt_lines;
1250 END IF;
1251 LOOP -- loop through each child mmtt line
1252 IF c_child_mmtt_lines%ISOPEN THEN
1253 fetch c_child_mmtt_lines into c_mmtt_line;
1254 EXIT WHEN c_child_mmtt_lines%NOTFOUND;
1255 ELSIF c_child_mmtt_lines_so%ISOPEN THEN
1256 fetch c_child_mmtt_lines_so into c_mmtt_line;
1257 EXIT WHEN c_child_mmtt_lines_so%NOTFOUND;
1258 ELSE fetch c_child_mmtt_lines_short into c_mmtt_line;
1259 EXIT WHEN c_child_mmtt_lines_short%NOTFOUND;
1260 END IF;
1261 IF (l_debug = 1) THEN mydebug('child transaction temp id'||c_mmtt_line.transaction_temp_id,l_api_name);
1262 END IF;
1263 l_child_txn_temp_id := c_mmtt_line.transaction_temp_id;
1264 FOR c_child_lots_rec in c_child_lots(c_mmtt_line.transaction_temp_id) LOOP -- loop through each child mtlt line
1265 l_child_lot_txn_qty := c_child_lots_rec.primary_quantity; -- get the lot qty
1266 IF (l_debug = 1) THEN mydebug('lot number '||c_child_lots_rec.lot_number||' lot qty:'|| c_child_lots_rec.primary_quantity,l_api_name); END IF;
1267 FOR c_lot_parents_rec in c_lot_parents(c_child_lots_rec.lot_number) LOOP -- loop though parent lines to find the
1268 -- the proper parent lines for the lot
1269 IF (l_debug = 1) THEN mydebug('parent transaction temp id:'||c_lot_parents_rec.transaction_temp_id
1270 ||' parent lot qty:'|| c_lot_parents_rec.primary_quantity,l_api_name);
1271 END IF;
1275 ELSE l_process_qty := c_lot_parents_rec.primary_quantity;
1272 -- find what's the lot qty can be processed
1273 IF c_lot_parents_rec.primary_quantity >= l_child_lot_txn_qty THEN
1274 l_process_qty := l_child_lot_txn_qty;
1276 END IF;
1277 IF (l_debug = 1) THEN mydebug('processed lot qty :'||l_process_qty,l_api_name); END IF;
1278
1279 -- initialize the local variable
1280 l_link_mtlt_needed := 'N';
1281 -- first find out if the mmtt line for this parent line has been created or not, for multiple lots
1282 BEGIN
1283 select child_transaction_temp_id
1284 into l_child_txn_temp_id
1285 from mtl_allocations_gtmp
1286 where transaction_temp_id = c_lot_parents_rec.transaction_temp_id
1287 and child_transaction_temp_id is not null
1288 and rownum = 1;
1289
1290 l_link_mtlt_needed := 'Y'; -- remember it since the mtlt may be needed splitting
1291 IF (l_debug = 1) THEN mydebug(' child line created before id:'|| l_child_txn_temp_id,l_api_name);
1292 END IF;
1293 -- update the qty with the new process qty
1294 update mtl_material_transactions_temp
1295 set primary_quantity = primary_quantity+l_process_qty
1296 , transaction_quantity = transaction_quantity+l_process_qty
1297 , posting_flag = 'Y' -- Bug# 4185621: change child line posting flag back to 'Y'
1298 , wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line task status is loaded
1299 where transaction_temp_id = l_child_txn_temp_id;
1300 IF (l_debug = 1) THEN mydebug('updated the chld line with the new processed qty',l_api_name);
1301 END IF;
1302
1303 EXCEPTION
1304 WHEN NO_DATA_FOUND THEN
1305
1306 IF (l_debug = 1) THEN mydebug('child line was not created',l_api_name); END IF;
1307
1308 -- child line is not created,split the mmtt line if needed
1309 IF c_mmtt_line.primary_quantity = l_process_qty THEN -- no need to split
1310 IF (l_debug = 1) THEN mydebug('no need to split....',l_api_name); END IF;
1311 l_child_txn_temp_id := c_mmtt_line.transaction_temp_id;
1312 l_link_mtlt_needed := 'N';
1313 ELSE -- qty in mmtt > qty processed ,split anyway, will be cleaned up later on
1314
1315 select mtl_material_transactions_s.NEXTVAL
1316 into l_new_temp_id
1317 from dual;
1318
1319 IF (l_debug = 1) THEN mydebug('split the child line with the new temp id '||l_new_temp_id,l_api_name);
1320 END IF;
1321 split_child_mmtt(c_mmtt_line.transaction_temp_id,
1322 l_new_temp_id,
1323 l_process_qty
1324 );
1325
1326 l_child_txn_temp_id := l_new_temp_id;
1327 IF (l_debug = 1) THEN mydebug('update the processed qty for the new child line',l_api_name);
1328 END IF;
1329 update mtl_allocations_gtmp
1330 set child_transaction_temp_id = l_new_temp_id
1331 where transaction_temp_id = c_lot_parents_rec.transaction_temp_id;
1332
1333 l_link_mtlt_needed := 'Y';
1334
1335 END IF;
1336 END;
1337
1338 -- split the lot line if needed
1339 if (l_process_qty= c_child_lots_rec.primary_quantity ) then -- no need to split
1340 IF (l_debug = 1) THEN mydebug('No need to split the mtlt....',l_api_name); END IF;
1341 if l_link_mtlt_needed = 'Y' then
1342 IF (l_debug = 1) THEN mydebug('update the lot qty for temp id:'||c_mmtt_line.transaction_temp_id,
1343 l_api_name);
1344 END IF;
1345 update mtl_transaction_lots_temp
1346 set transaction_Temp_id = l_child_txn_temp_id
1347 where transaction_temp_id = c_mmtt_line.transaction_temp_id
1348 and lot_number = c_child_lots_rec.lot_number;
1349 end if;
1350 else
1351 -- split lot is needed, l_process_qty <the lot qty
1352 IF (l_debug = 1) THEN mydebug('splitting the mtlt line with new temp id '|| l_child_txn_temp_id,l_api_name);
1353 END IF;
1354 split_child_mtlt(c_mmtt_line.transaction_temp_id,
1355 l_child_txn_temp_id, -- new temp id
1356 null, -- new serial temp id
1357 l_process_qty,
1358 c_child_lots_rec.lot_number --v1
1359 );
1360
1361 end if;
1362
1363 -- update child line with the correct parent info
1364 IF (l_debug = 1) THEN mydebug('updating the child line with the correct parent....',l_api_name); END IF;
1365 update_child(l_child_txn_temp_id,c_lot_parents_rec.transaction_temp_id,
1366 x_new_txn_hdr_id);
1367
1368 -- update the processed lot quantity
1369 l_child_lot_txn_qty := l_child_lot_txn_qty-l_process_qty;
1370 IF (l_debug = 1) THEN mydebug('left lot qty to be processed '||l_child_lot_txn_qty,l_api_name); END IF;
1371 -- update the quantity for the parent line in the gtmp
1372 update mtl_allocations_gtmp
1373 set primary_quantity = primary_quantity-l_process_qty -- only primary UOM will be used
1374 where transaction_temp_id = c_lot_parents_rec.transaction_temp_id
1378 -- delete the record if the qty has became 0
1375 and lot_number = c_child_lots_rec.lot_number;
1376 IF (l_debug = 1) THEN mydebug('after updating the parent qty in the temp table.',l_api_name); END IF;
1377
1379 delete mtl_allocations_gtmp
1380 where transaction_temp_id = c_lot_parents_rec.transaction_temp_id
1381 and primary_quantity = 0;
1382 IF (l_debug = 1 and SQL%ROWCOUNT>0) THEN
1383 mydebug('records deteted for qty 0 in the temp table '||SQL%ROWCOUNT,l_api_name);
1384 mydebug('child lot txn qty to be processed :'||l_child_lot_txn_qty,l_api_name);
1385 END IF;
1386 EXIT WHEN l_child_lot_txn_qty = 0;
1387
1388 END LOOP; -- for parent lines, c_lot_parents_rec
1389
1390 END LOOP; -- for mtlt,c_child_lots_rec
1391
1392 -- delete the qty 0 mmtt due to the split
1393 DELETE FROM mtl_material_transactions_temp mmtt
1394 WHERE transaction_temp_id = c_mmtt_line.transaction_temp_id
1395 AND primary_quantity = 0;
1396
1397 -- before process another mmtt line, the child transaction temp id should be nullified
1398 update mtl_allocations_gtmp
1399 set child_transaction_temp_id = null;
1400 END LOOP; -- for mmtt
1401
1402 IF c_child_mmtt_lines%ISOPEN THEN
1403 close c_child_mmtt_lines;
1404 ELSIF c_child_mmtt_lines_so%ISOPEN THEN
1405 close c_child_mmtt_lines_so ;
1406 ELSE close c_child_mmtt_lines_short;
1407 END IF;
1408
1409 -- create sub transfer for over picked qty, they should be in the gtmp table
1410 IF (p_exception='OVER') THEN
1411 FOR over_qty_line IN over_picked_lines LOOP
1412 create_sub_transfer(over_qty_line.transaction_temp_id,
1413 over_qty_line.primary_quantity,
1414 over_qty_line.primary_quantity,
1415 p_lot_controlled);
1416 END LOOP;
1417 END IF;
1418
1419 ELSE -- plain item
1420
1421
1422 IF ( p_exception = 'SHORT') THEN
1423 IF (l_debug = 1) THEN mydebug('Opening c_parent_mmtt_shortpick v1 ',p_exception); END IF;
1424 OPEN c_parent_mmtt_shortpick;
1425 ELSE
1426 OPEN c_parent_mmtt_lines;
1427 END IF;
1428
1429 IF (l_transaction_action_id = 28 and p_exception = 'SHORT')THEN
1430 OPEN c_child_mmtt_lines_short;
1431 ELSIF l_transaction_action_id = 28 THEN
1432 OPEN c_child_mmtt_lines_so;
1433 ELSE OPEN c_child_mmtt_lines;
1434 END IF;
1435
1436
1437 LOOP
1438
1439
1440 IF c_parent_mmtt_shortpick%ISOPEN THEN
1441 IF (l_debug = 1) THEN mydebug('Fetching from c_parent_mmtt_shortpick v1',p_exception); END IF;
1442 FETCH c_parent_mmtt_shortpick INTO l_parent_txn_temp_id
1443 , l_item_id
1444 , l_parent_sub_code
1445 , l_parent_loc_id
1446 , l_lpn_id
1447 , l_transfer_lpn_id
1448 , l_parent_uom
1449 , l_parent_txn_qty
1450 , l_parent_pri_qty;
1451 EXIT WHEN c_parent_mmtt_shortpick%NOTFOUND;
1452 ELSE
1453 FETCH c_parent_mmtt_lines INTO l_parent_txn_temp_id
1454 , l_item_id
1455 , l_parent_sub_code
1456 , l_parent_loc_id
1457 , l_lpn_id
1458 , l_transfer_lpn_id
1459 , l_parent_uom
1460 , l_parent_txn_qty
1461 , l_parent_pri_qty;
1462 EXIT WHEN c_parent_mmtt_lines%NOTFOUND;
1463 END IF;
1464
1465 --Bug# 4185621: update parent line posting flag back to 'N'
1466 UPDATE mtl_material_transactions_temp
1467 SET posting_flag = 'N'
1468 WHERE transaction_temp_id = l_parent_txn_temp_id
1469 AND parent_line_id = transaction_temp_id;
1470 -- Bug# 4185621: end change
1471
1472 LOOP
1473 IF l_child_txn_qty = 0 THEN
1474 mydebug('BULK_PICK:fetching a new child record.',l_api_name);
1475
1476 IF c_child_mmtt_lines%ISOPEN THEN
1477 fetch c_child_mmtt_lines into l_child_txn_temp_id, l_child_uom, l_child_txn_qty, l_child_pri_qty;
1478 EXIT WHEN c_child_mmtt_lines%NOTFOUND;
1479 ELSIF c_child_mmtt_lines_so%ISOPEN THEN
1480 fetch c_child_mmtt_lines_so into l_child_txn_temp_id, l_child_uom, l_child_txn_qty, l_child_pri_qty;
1481 EXIT WHEN c_child_mmtt_lines_so%NOTFOUND;
1482 ELSE fetch c_child_mmtt_lines_short into l_child_txn_temp_id, l_child_uom, l_child_txn_qty, l_child_pri_qty;
1483 EXIT WHEN c_child_mmtt_lines_short%NOTFOUND;
1484 END IF;
1485
1486 END IF;
1487
1488 IF l_debug = 1 THEN
1489 mydebug('Child Temp ID = ' || l_child_txn_temp_id,l_api_name);
1490 mydebug('Parent Temp ID = ' || l_parent_txn_temp_id,l_api_name);
1491 mydebug('parent transfer_lpn_id = '|| l_transfer_lpn_id,l_api_name);
1492 mydebug('Current Parent Qty = ' || l_parent_txn_qty || ' : Child Qty = ' || l_child_txn_qty,l_api_name);
1493 END IF;
1494
1495 IF l_parent_uom <> l_child_uom THEN
1496 l_child_txn_qty :=
1497 inv_convert.inv_um_convert(
1498 item_id => l_item_id
1499 , PRECISION => NULL
1500 , from_quantity => l_child_txn_qty
1501 , from_unit => l_child_uom
1502 , to_unit => l_parent_uom
1503 , from_name => NULL
1504 , to_name => NULL
1508 END IF;
1505 );
1506
1507 l_child_uom := l_parent_uom ; --bug#6848907.child qty is in parent uom now
1509
1510 IF l_parent_txn_qty >= l_child_txn_qty THEN
1511 UPDATE mtl_material_transactions_temp mmtt
1512 SET mmtt.transaction_header_id = x_new_txn_hdr_id
1513 , mmtt.transfer_lpn_id = l_transfer_lpn_id
1514 , mmtt.lpn_id = l_lpn_id
1515 , mmtt.parent_line_id = l_parent_txn_temp_id
1516 , mmtt.subinventory_code = l_parent_sub_code
1517 , mmtt.locator_id = l_parent_loc_id
1518 , mmtt.transaction_uom = mmtt.item_primary_uom_code
1519 , mmtt.transaction_quantity = mmtt.primary_quantity
1520 , mmtt.last_update_date = SYSDATE
1521 , mmtt.last_updated_by = p_user_id
1522 , mmtt.posting_flag = 'Y' -- Bug# 4185621: change child line posting flag back to 'Y'
1523 , mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line task status is loaded
1524 WHERE mmtt.transaction_temp_id = l_child_txn_temp_id;
1525
1526 l_parent_txn_qty := l_parent_txn_qty - l_child_txn_qty;
1527 l_parent_pri_qty := l_parent_pri_qty - l_child_pri_qty;
1528 l_child_txn_qty := 0;
1529 l_child_pri_qty := 0;
1530
1531 EXIT WHEN l_parent_txn_qty = 0;
1532 ELSE -- Current Child Qty is greater than Parent Picked Qty
1533 select mtl_material_transactions_s.NEXTVAL
1534 into l_new_temp_id
1535 from dual;
1536
1537 split_child_mmtt(l_child_txn_temp_id,
1538 l_new_temp_id,
1539 l_child_pri_qty - l_parent_pri_qty
1540 );
1541
1542 UPDATE mtl_material_transactions_temp mmtt
1543 SET mmtt.transaction_header_id = x_new_txn_hdr_id
1544 , mmtt.primary_quantity = l_parent_pri_qty
1545 , mmtt.parent_line_id = l_parent_txn_temp_id
1546 , mmtt.transfer_lpn_id = l_transfer_lpn_id
1547 , mmtt.lpn_id = l_lpn_id
1548 , mmtt.subinventory_code = l_parent_sub_code
1549 , mmtt.locator_id = l_parent_loc_id
1550 , mmtt.transaction_uom = mmtt.item_primary_uom_code
1551 , mmtt.transaction_quantity = mmtt.primary_quantity
1552 , mmtt.last_update_date = SYSDATE
1553 , mmtt.last_updated_by = p_user_id
1554 , mmtt.posting_flag = 'Y' -- Bug# 4185621: change child line posting flag back to 'Y'
1555 , mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure task status is loaded
1556 WHERE mmtt.transaction_temp_id = l_child_txn_temp_id;
1557
1558 l_child_txn_temp_id := l_new_temp_id;
1559 l_child_pri_qty := l_child_pri_qty - l_parent_pri_qty;
1560 l_child_txn_qty := l_child_txn_qty - l_parent_txn_qty;
1561 l_parent_txn_qty := 0;
1562 l_parent_pri_qty := 0;
1563
1564 mydebug('BULK_PICK:new MMTT tmp id:'||l_new_temp_id,l_api_name);
1565 mydebug('BULK_PICK:new child pri qty:'||l_child_pri_qty,l_api_name);
1566 mydebug('BULK_PICK:new child txn qty:'||l_child_txn_qty,l_api_name);
1567 EXIT;
1568 END IF;
1569 END LOOP;
1570
1571 END LOOP;
1572 -- create subtransfer if there are over picked qty
1573 IF (l_parent_pri_qty >0) THEN
1574 mydebug('BULK_PICK:calling create_sub_transfer to create sub transfer for over qty',l_api_name);
1575 create_sub_transfer(l_parent_txn_temp_id,l_parent_pri_qty,l_parent_pri_qty,1);
1576 END IF;
1577 IF c_child_mmtt_lines%ISOPEN THEN
1578 close c_child_mmtt_lines;
1579 ELSIF c_child_mmtt_lines_so%ISOPEN THEN
1580 close c_child_mmtt_lines_so ;
1581 ELSE close c_child_mmtt_lines_short;
1582 END IF;
1583 IF c_parent_mmtt_lines%ISOPEN THEN
1584 CLOSE c_parent_mmtt_lines;
1585 ELSE
1586 CLOSE c_parent_mmtt_shortpick;
1587 END IF;
1588
1589 END IF; -- end the association between child and parent lines
1590
1591
1592 IF p_exception= 'SHORT' THEN -- picking short for lot controlled or plain item, need to make sure the cleanup
1593 -- task works for this case, no serial numbers if it is serial controlled
1594 -- should work since it is same as regular task
1595
1596
1597 mydebug('BULK_PICK: v1 calling cleanup_task for mmtt line:'||p_temp_id,l_api_name);
1598 wms_txnrsn_actions_pub.cleanup_task(
1599 p_temp_id => p_temp_id
1600 , p_qty_rsn_id => p_reason_id
1601 , p_user_id => p_user_id
1602 , p_employee_id => p_employee_id
1603 , x_return_status => x_return_status
1604 , x_msg_count => x_msg_count
1605 , x_msg_data => x_msg_data
1606 );
1607
1608 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1609 IF l_debug = 1 THEN
1610 mydebug('BULK_PICK: Error occurred while calling cleanup tasK ',l_api_name);
1611 END IF;
1612 RAISE fnd_api.g_exc_error;
1613 END IF;
1614
1615
1616 END IF;
1617
1618 EXCEPTION
1619 WHEN OTHERS THEN
1620 x_return_status := fnd_api.g_ret_sts_unexp_error;
1624 mydebug('Unexpected Error occurred - ' || SQLERRM,l_api_name);
1621 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1622
1623 IF (l_debug = 1) THEN
1625 END IF;
1626
1627 END bulk_pick;
1628
1629
1630 END wms_bulk_pick;
1631