1 PACKAGE BODY WMS_LMS_EXPECTED_RES AS
2 /* $Header: WMSLMERB.pls 120.11 2006/11/14 13:41:35 salagars noship $ */
3
4 /**
5 * This is a Package that has procedures/functions that
6 * assists in estimating time for various activities like
7 * inbound, outbound, Warehousing Activities
8 **/
9
10
11 g_version_printed BOOLEAN := FALSE;
12 g_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
13
14 PROCEDURE DEBUG(p_message IN VARCHAR2,
15 p_module IN VARCHAR2 default 'abc',
16 p_level IN VARCHAR2 DEFAULT 9) IS
17 BEGIN
18
19 IF NOT g_version_printed THEN
20 INV_TRX_UTIL_PUB.TRACE('$Header: WMSLMERB.pls 120.11 2006/11/14 13:41:35 salagars noship $',g_pkg_name, 9);
21 g_version_printed := TRUE;
22 END IF;
23
24 INV_TRX_UTIL_PUB.TRACE( P_MESG =>P_MESSAGE
25 ,P_MOD => p_module
26 ,p_level => p_level
27 );
28 END DEBUG;
29
30
31 --This program populates the WMS_ELS_EXP_RESOURCE table, which is the base requirement
32 --for Expected Resource Requirements Analysis . WMS_ELS_EXP_RESOURCE table essentially
33 --has all the information for all future work that is expected in a Warehouse whether
34 --it is an Inbound, Outbound, Warehousing or Manufacturing activity. Based on this
35 --information the expected resource requirement will be calculated.
36 --The following is list if the inputs for populating this table.
37 --1. Receiving Inbound
38 -- o Expected Purchase Order Receipts to be received in the given time frame
39 -- o Expected ASN material to be received in the given time frame
40 -- o Expected Internal Transfers to be received in the given time frame
41 -- o Expected RMAs to be received in the given time frame
42 --2. Receiving Inbound
43 -- o Material that is received, but needs to be putaway
44 --3. Inventory Accuracy
45 -- Cycle count tasks outstanding
46 --4. Outbound Shipping / Manufacturing
47 -- o Unreleased / pending / queued / dispatched tasks for Sales orders,
48 -- manufacturing component picks, and internal orders
49 --5. Manufacturing Putaways
53 PROCEDURE POPULATE_EXPECTED_WORK
50 --6. Pending and outstanding replenishment tasks
51
52
54 ( x_return_status OUT NOCOPY VARCHAR2
55 , x_msg_count OUT NOCOPY VARCHAR2
56 , x_msg_data OUT NOCOPY VARCHAR2
57 , p_org_id IN NUMBER
58 )IS
59
60 l_num_rows_inserted NUMBER;
61 l_num_sql_failed NUMBER;
62 ALL_SQL_FAILED Exception;
63 g_total_sql NUMBER;
64
65 BEGIN
66 x_return_status := fnd_api.g_ret_sts_success;
67 l_num_rows_inserted := 0;
68 l_num_sql_failed :=0;
69 g_total_sql :=11;
70
71 IF g_debug=1 THEN
72 debug('The value of p_org_id '|| p_org_id,'POPULATE_EXPECTED_WORK');
73 END IF;
74 --1. Receiving Inbound
75 -- o Expected Purchase Order Receipts to be received in the given time frame
76 -- o Expected ASN material to be received in the given time frame
77 -- o Expected Internal Transfers to be received in the given time frame
78 -- The expected work is queried from mtl_supply table for these work sources.
79 BEGIN
80
81 IF g_debug=1 THEN
82 debug('Before populating work for Inbound(PO,REQ,SHIPMENT) ','POPULATE_EXPECTED_WORK');
83 END IF;
84
85 INSERT INTO WMS_ELS_EXP_RESOURCE
86 (els_exp_resource_id ,
87 organization_id,
88 activity_id,
89 activity_detail_id,
90 operation_id,
91 document_type,
92 source_subinventory,
93 transaction_uom ,
94 inventory_item_id ,
95 quantity,
96 source_header_id,
97 source_line_id,
98 group_id,
99 work_scheduled_date,
100 last_updated_by,
101 last_update_Date,
102 last_update_login,
103 created_by,
104 creation_Date
105 )
106 select
107 WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
108 to_organization_id,
109 1,--Inbound
110 1,--Recieve
111 1,--Reciept
112 supply_type_code,
113 from_subinventory,
114 mum.UOM_CODE,
115 item_id,
116 quantity,
117 decode(supply_type_code,
118 'PO', po_header_id,
119 'REQ', req_header_id,
120 'SHIPMENT',shipment_header_id
121 ),
122 decode(supply_type_code,
123 'PO', po_line_id,
124 'REQ',req_line_id,
125 'SHIPMENT',shipment_line_id
126 ),
127 1, --manual and user directed
128 receipt_date,
129 FND_GLOBAL.USER_ID,
130 SYSDATE,
131 FND_GLOBAL.LOGIN_ID,
132 FND_GLOBAL.USER_ID,
133 SYSDATE
134 from mtl_supply ms,
135 wms_els_parameters wep,
136 mtl_units_of_measure_vl mum
137 where to_organization_id = p_org_id
138 and wep.organization_id = to_organization_id
139 and supply_type_code IN( 'PO','REQ','SHIPMENT')
140 and mum.description = ms.unit_of_measure
141 and receipt_date < ( SYSDATE + decode ( wep.data_period_unit ,
142 1 ,
143 wep.data_period_value /24,
144 2 , wep.data_period_value,
145 3, (ADD_MONTHS (SYSDATE,
146 wep.data_period_value ) - SYSDATE)
147 )
148 );
149
150 l_num_rows_inserted := SQL%ROWCOUNT;
151
152 IF g_debug=1 THEN
153 debug('The no of rows inserted for inbound(PO,REQ,SHIPMENTS) '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
154 END IF;
155
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 IF g_debug=1 THEN
160 debug('Exception in populating rows for inbound(PO,REQ,SHIPMENTS) ','POPULATE_EXPECTED_WORK');
161 END IF;
162 x_return_status := fnd_api.g_ret_sts_error;
163 l_num_sql_failed := l_num_sql_failed+1;
164 END;
165
166
167 --1. Receiving Inbound
168 -- o Expected RMAs to be received in the given time frame
169 BEGIN
170 IF g_debug=1 THEN
171 debug('Before populating work for Inbound(RMA) ','POPULATE_EXPECTED_WORK');
172 END IF;
173
174 l_num_rows_inserted := 0;
175
176 insert into WMS_ELS_EXP_RESOURCE
177 (
178 els_exp_resource_id,
179 organization_id,
180 activity_id,
181 activity_detail_id,
182 operation_id,
183 document_type,
184 source_subinventory,
185 transaction_uom,
186 inventory_item_id,
187 quantity,
188 source_header_id,
189 source_line_id,
190 group_id,
191 work_scheduled_date,
192 last_updated_by,
193 last_update_date,
194 last_update_login,
195 created_by,
196 creation_Date
197 )
198 select wms_els_exp_resource_s.nextval,
199 ship_from_org_id,
200 1, -- Inbound
201 1, -- Recieving
202 1, -- Reciept
203 'RMA',
204 subinventory,
205 shipping_quantity_uom,
206 inventory_item_id,
207 shipping_quantity - shipped_quantity - cancelled_quantity,
211 promise_date,
208 header_id,
209 line_id,
210 1, -- Manual and user directed
212 FND_GLOBAL.USER_ID,
213 SYSDATE,
214 FND_GLOBAL.LOGIN_ID,
215 FND_GLOBAL.USER_ID,
216 SYSDATE
217 from oe_order_lines_all,
218 wms_els_parameters wep
219 where line_category_code like 'RETURN'
220 and booked_flag ='Y'
221 and cancelled_flag='N'
222 and open_flag='Y'
223 and flow_status_code not IN('CLOSED' , 'CANCELLED')
224 AND flow_status_code = 'AWAITING_RETURN'
225 and ship_from_org_id = p_org_id
226 and wep.organization_id = ship_from_org_id
227 and promise_date < ( SYSDATE + decode ( wep.data_period_unit ,
228 1 ,
229 wep.data_period_value /24,
230 2 ,wep.data_period_value,
231 3,(ADD_MONTHS (SYSDATE, wep.data_period_value
232 ) - SYSDATE)
233 )
234 );
235
236 l_num_rows_inserted := SQL%ROWCOUNT;
237
238 IF g_debug=1 THEN
239 debug('The no of rows inserted for inbound(RMA) '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
240 END IF;
241
242
243 EXCEPTION
244 WHEN OTHERS THEN
245
246 IF g_debug=1 THEN
247 debug('Exception in populating rows for inbound(RMA) ','POPULATE_EXPECTED_WORK');
248 END IF;
249
250 x_return_status := fnd_api.g_ret_sts_error;
251 l_num_sql_failed := l_num_sql_failed + 1;
252
253 END;
254
255 --2. Inbound Putaway
256 -- o Material that is received, but needs to be putaway
257 BEGIN
258 IF g_debug=1 THEN
259 debug('Before populating work for Inbound Putaway(PO) for DROP ','POPULATE_EXPECTED_WORK');
260 END IF;
261
262 l_num_rows_inserted := 0;
263
264 INSERT INTO WMS_ELS_EXP_RESOURCE
265 (els_exp_resource_id ,
266 organization_id,
267 activity_id,
268 activity_detail_id,
269 operation_id,
270 destination_subinventory,
271 destination_locator_id,
272 source_header_id,
273 source_line_id,
274 group_id,
275 operation_plan_id,
276 last_updated_by,
277 last_update_Date,
278 last_update_login,
279 created_by,
280 creation_Date
281 )
282 select
283 WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
284 organization_id,
285 1,--Inbound
286 2,--Putaway
287 3,--Drop
288 subinventory_code,
289 locator_id,
290 transaction_header_id,
291 transaction_temp_id,
292 1, --manual and user directed
293 operation_plan_id,
294 FND_GLOBAL.USER_ID,
295 SYSDATE,
296 FND_GLOBAL.LOGIN_ID,
297 FND_GLOBAL.USER_ID,
298 SYSDATE
299 from
300 mtl_material_transactions_temp
301 where organization_id = p_org_id
302 and transaction_type_id = 18
303 and transaction_action_id =27
304 and transaction_source_type_id =1
305 AND wms_task_type IN (2,8)
306 and move_order_line_id IS NULL;
307
308
309 l_num_rows_inserted := SQL%ROWCOUNT;
310
311 IF g_debug=1 THEN
312 debug('The no of rows inserted for inbound putaway PO '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
313 END IF;
314
315
316 EXCEPTION
317 WHEN OTHERS THEN
318
319 IF g_debug=1 THEN
320 debug('Exception in populating rows for inbound putaway PO','POPULATE_EXPECTED_WORK');
321 END IF;
322
323 x_return_status := fnd_api.g_ret_sts_error;
324 l_num_sql_failed := l_num_sql_failed + 1;
325
326 END;
327
328 --2. Inbound Putaway (RMA)
329 -- o Material that is received through RMA, but needs to be putaway
330 BEGIN
331 IF g_debug=1 THEN
332 debug('Before populating work for Inbound Putaway for DROP( RMA) ','POPULATE_EXPECTED_WORK');
333 END IF;
334
335 l_num_rows_inserted := 0;
336
337 INSERT INTO WMS_ELS_EXP_RESOURCE
338 (els_exp_resource_id ,
339 organization_id,
340 activity_id,
341 activity_detail_id,
342 operation_id,
343 destination_subinventory,
344 destination_locator_id,
345 source_header_id,
346 source_line_id,
347 group_id,
348 operation_plan_id,
349 last_updated_by,
350 last_update_Date,
351 last_update_login,
352 created_by,
353 creation_Date
354 )
355 select
356 WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
357 organization_id,
358 1,--Inbound
359 2,--Putaway
360 3,--Drop
361 subinventory_code,
362 locator_id,
363 transaction_header_id,
364 transaction_temp_id,
365 1, --manual and user directed
366 operation_plan_id,
367 FND_GLOBAL.USER_ID,
368 SYSDATE,
369 FND_GLOBAL.LOGIN_ID,
370 FND_GLOBAL.USER_ID,
371 SYSDATE
372 from
373 mtl_material_transactions_temp
377 and transaction_source_type_id =12
374 where organization_id = p_org_id
375 and transaction_type_id = 15
376 and transaction_action_id =27
378 AND wms_task_type IN (2,8)
379 and move_order_line_id IS NULL;
380
381
382 l_num_rows_inserted := SQL%ROWCOUNT;
383
384 IF g_debug=1 THEN
385 debug('The no of rows inserted for inbound putaway RMA '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
386 END IF;
387
388
389 EXCEPTION
390 WHEN OTHERS THEN
391
392 IF g_debug=1 THEN
393 debug('Exception in populating rows for inbound putaway RMA','POPULATE_EXPECTED_WORK');
394 END IF;
395
396 x_return_status := fnd_api.g_ret_sts_error;
397 l_num_sql_failed := l_num_sql_failed + 1;
398
399 END;
400
401 --2. Inbound Putaway (Intransit Shipment)
402 -- o Material that is received through Intransit Shipment, but needs to be putaway
403 BEGIN
404 IF g_debug=1 THEN
405 debug('Before populating work for Inbound Putaway for DROP( Intransit Shipment) ','POPULATE_EXPECTED_WORK');
406 END IF;
407
408 l_num_rows_inserted := 0;
409
410 INSERT INTO WMS_ELS_EXP_RESOURCE
411 (els_exp_resource_id ,
412 organization_id,
413 activity_id,
414 activity_detail_id,
415 operation_id,
416 destination_subinventory,
417 destination_locator_id,
418 source_header_id,
419 source_line_id,
420 group_id,
421 operation_plan_id,
422 last_updated_by,
423 last_update_Date,
424 last_update_login,
425 created_by,
426 creation_Date
427 )
428 select
429 WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
430 organization_id,
431 1,--Inbound
432 2,--Putaway
433 3,--Drop
434 subinventory_code,
435 locator_id,
436 transaction_header_id,
437 transaction_temp_id,
438 1, --manual and user directed
439 operation_plan_id,
440 FND_GLOBAL.USER_ID,
441 SYSDATE,
442 FND_GLOBAL.LOGIN_ID,
443 FND_GLOBAL.USER_ID,
444 SYSDATE
445 from
446 mtl_material_transactions_temp
447 where organization_id = p_org_id
448 and transaction_type_id = 12
449 and transaction_action_id =12
450 and transaction_source_type_id =13
451 AND wms_task_type IN (2,8)
452 and move_order_line_id IS NULL;
453
454
455 l_num_rows_inserted := SQL%ROWCOUNT;
456
457 IF g_debug=1 THEN
458 debug('The no of rows inserted for inbound putaway Intransit Shipment '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
459 END IF;
460
461
462 EXCEPTION
463 WHEN OTHERS THEN
464
465 IF g_debug=1 THEN
466 debug('Exception in populating rows for inbound putaway Intransit Shipment','POPULATE_EXPECTED_WORK');
467 END IF;
468
469 x_return_status := fnd_api.g_ret_sts_error;
470 l_num_sql_failed := l_num_sql_failed + 1;
471
472 END;
473
474 --2. Inbound Putaway (Internal Requisition)
475 -- o Material that is received through Internal Requisition, but needs to be putaway
476 BEGIN
477 IF g_debug=1 THEN
478 debug('Before populating work for Inbound Putaway for DROP( Intransit Shipment) ','POPULATE_EXPECTED_WORK');
479 END IF;
480
481 l_num_rows_inserted := 0;
482
483 INSERT INTO WMS_ELS_EXP_RESOURCE
484 (els_exp_resource_id ,
485 organization_id,
486 activity_id,
487 activity_detail_id,
488 operation_id,
489 destination_subinventory,
490 destination_locator_id,
491 source_header_id,
492 source_line_id,
493 group_id,
494 operation_plan_id,
495 last_updated_by,
496 last_update_Date,
497 last_update_login,
498 created_by,
499 creation_Date
500 )
501 select
502 WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
503 organization_id,
504 1,--Inbound
505 2,--Putaway
506 3,--Drop
507 subinventory_code,
508 locator_id,
509 transaction_header_id,
510 transaction_temp_id,
511 1, --manual and user directed
512 operation_plan_id,
513 FND_GLOBAL.USER_ID,
514 SYSDATE,
515 FND_GLOBAL.LOGIN_ID,
516 FND_GLOBAL.USER_ID,
517 SYSDATE
518 from
519 mtl_material_transactions_temp
520 where organization_id = p_org_id
521 and transaction_type_id = 61
522 and transaction_action_id =12
523 and transaction_source_type_id =7
524 AND wms_task_type IN (2,8)
525 and move_order_line_id IS NULL;
526
527
528 l_num_rows_inserted := SQL%ROWCOUNT;
529
530 IF g_debug=1 THEN
531 debug('The no of rows inserted for inbound putaway Intransit Shipment '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
532 END IF;
533
534
535 EXCEPTION
536 WHEN OTHERS THEN
537
538 IF g_debug=1 THEN
539 debug('Exception in populating rows for inbound putaway Intransit Shipment','POPULATE_EXPECTED_WORK');
540 END IF;
541
545 END;
542 x_return_status := fnd_api.g_ret_sts_error;
543 l_num_sql_failed := l_num_sql_failed + 1;
544
546
547
548
549 --5. Manufacturing Putaways
550 BEGIN
551 IF g_debug=1 THEN
552 debug('Before populating work for Manufacturing Putaway DROP ','POPULATE_EXPECTED_WORK');
553 END IF;
554
555 l_num_rows_inserted := 0;
556
557 INSERT INTO WMS_ELS_EXP_RESOURCE
558 (els_exp_resource_id ,
559 organization_id,
560 activity_id,
561 activity_detail_id,
562 operation_id,
563 destination_subinventory,
564 destination_locator_id,
565 source_header_id,
566 source_line_id,
567 group_id,
568 last_updated_by,
569 last_update_Date,
570 last_update_login,
571 created_by,
572 creation_Date
573 )
574 select
575 WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
576 organization_id,
577 2,-- Manufacturing
578 2,-- putaway
579 3,--DROP
580 subinventory_code,
581 locator_id,
582 transaction_header_id,
583 transaction_temp_id,
584 1, --manual and user directed
585 FND_GLOBAL.USER_ID,
586 SYSDATE,
587 FND_GLOBAL.LOGIN_ID,
588 FND_GLOBAL.USER_ID,
589 SYSDATE
590 from mtl_material_transactions_temp
591 where organization_id = p_org_id
592 and transaction_type_id = 44
593 and transaction_action_id =31
594 and transaction_source_type_id =5
595 and wms_task_type =2;
596
597 l_num_rows_inserted := SQL%ROWCOUNT;
598
599 IF g_debug=1 THEN
600 debug('The no of rows inserted for Manufacturing putaway DROP'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
601 END IF;
602
603
604 EXCEPTION
605 WHEN OTHERS THEN
606
607 IF g_debug=1 THEN
608 debug('Exception in populating rows for Manufacturing Putaway DROP','POPULATE_EXPECTED_WORK');
609 END IF;
610
611 x_return_status := fnd_api.g_ret_sts_error;
612 l_num_sql_failed := l_num_sql_failed + 1;
613
614 END;
615
616
617 --3. Inventory Accuracy
618 --Cycle count tasks outstanding
619
620 BEGIN
621 IF g_debug=1 THEN
622 debug('Before populating work for Cycle Counting ','POPULATE_EXPECTED_WORK');
623 END IF;
624
625 l_num_rows_inserted := 0;
626 -- removed the join with mtl_cc_schedule_requests because the
627 -- the counting tasks would be in pending state as soon as requests are generated.
628
629 insert into WMS_ELS_EXP_RESOURCE
630 ( els_exp_resource_id ,
631 organization_id,
632 activity_id,
633 activity_detail_id,
634 operation_id,
635 document_type,
636 source_subinventory,
637 source_locator_id,
638 inventory_item_id,
639 source_header_id,
640 source_line_id,
641 group_id,
642 last_updated_by,
643 last_update_Date,
644 last_update_login,
645 created_by,
646 creation_Date
647 )
648 select wms_els_exp_resource_s.nextval,
649 mcce.organization_id,
650 4,-- Warehousing
651 5,-- Counting
652 4,--Count
653 NULL,-- not inbound so document type is NULL
654 mcce.Subinventory,
655 mcce.locator_id,
656 mcce.inventory_item_id,
657 mcce.cycle_count_header_id,
658 mcce.cycle_count_entry_id,
659 1, --Individual and System Directed
660 FND_GLOBAL.USER_ID,
661 SYSDATE,
662 FND_GLOBAL.LOGIN_ID,
663 FND_GLOBAL.USER_ID,
664 SYSDATE
665 from
666 mtl_cycle_Count_entries mcce
667 WHERE
668 mcce.organization_id = p_org_id
669 and mcce.entry_status_code in (1,3); -- it is uncounted or for recounting.
670
671
672 l_num_rows_inserted := SQL%ROWCOUNT;
673
674 IF g_debug=1 THEN
675 debug('The no of rows inserted for Cycle Counting'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
676 END IF;
677
678
679 EXCEPTION
680 WHEN OTHERS THEN
681
682 IF g_debug=1 THEN
683 debug('Exception in populating rows for Cycle Counting','POPULATE_EXPECTED_WORK');
684 END IF;
685
686 x_return_status := fnd_api.g_ret_sts_error;
687 l_num_sql_failed := l_num_sql_failed + 1;
688
689 END;
690
691
692 --4. Outbound Shipping / Manufacturing
693 -- o Unreleased / pending for Sales orders,
694 -- manufacturing component picks, and internal orders
695 --6. Pending and outstanding replenishment tasks
696 -- These are FOR Load Operation
697
698 BEGIN
699 IF g_debug=1 THEN
700 debug('Before populating LOAD TASKS for Outbound/Relenishment tasks(pending,unreleased) ','POPULATE_EXPECTED_WORK');
701 END IF;
702
703 l_num_rows_inserted := 0;
704
705 insert into WMS_ELS_EXP_RESOURCE
706 ( els_exp_resource_id ,
707 organization_id,
708 activity_id,
709 activity_detail_id,
710 operation_id,
711 document_type,
712 source_subinventory,
713 source_locator_id,
717 source_header_id,
714 transaction_uom,
715 quantity,
716 inventory_item_id,
718 source_line_id,
719 group_id,
720 operation_plan_id,
721 last_updated_by,
722 last_update_Date,
723 last_update_login,
724 created_by,
725 creation_Date
726 )
727 select wms_els_exp_resource_s.nextval,
728 mmtt.organization_id,
729 (CASE when (
730 ( Transaction_Type_Id = 52
731 and Transaction_Action_Id =28
732 and Transaction_Source_Type_Id = 2
733 and Wms_Task_Type =1
734 )
735 OR
736 ( Transaction_Type_Id =53
737 and Transaction_Action_Id =28
738 and Transaction_Source_Type_Id = 8
739 and Wms_Task_Type =1
740 )
741 )
742 THEN 3
743 when (
744 ( Transaction_Type_Id =64
745 and Transaction_Action_Id =2
746 AND Transaction_Source_Type_Id = 4
747 and Wms_Task_Type =4
748 )
749 OR
750 ( Transaction_Type_Id =64
751 and Transaction_Action_Id =2
752 and Transaction_Source_Type_Id = 4
753 and Wms_Task_Type =5
754 )
755 OR
756 ( Transaction_Type_Id =63
757 and Transaction_Action_Id =1
758 and Transaction_Source_Type_Id = 4
759 and Wms_Task_Type =6
760 )
761 )
762 THEN 4 -- Warehousing
763 when (
764 ( Transaction_Type_Id =51 -- Pull Type
765 and Transaction_Action_Id =2
766 and Transaction_Source_Type_Id = 13
767 and Wms_Task_Type =1
768 )
769 OR
770 ( Transaction_Type_Id =35 -- Push Type
771 and Transaction_Action_Id =1
772 and Transaction_Source_Type_Id = 5
773 and Wms_Task_Type =1
774 )
775 )
776 THEN 2 -- Manufacturing*/
777 end
778 ) activity_id,
779 (CASE when (
780 ( Transaction_Type_Id =52
781 and Transaction_Action_Id =28
782 and Transaction_Source_Type_Id = 2
783 and Wms_Task_Type =1
784 )
785 OR
786 ( Transaction_Type_Id =53
787 and Transaction_Action_Id =28
788 and Transaction_Source_Type_Id = 8
789 and Wms_Task_Type =1
790 )
791 OR
792 ( Transaction_Type_Id =51 -- Pull Type
793 and Transaction_Action_Id =2
794 and Transaction_Source_Type_Id = 13
795 and Wms_Task_Type =1
796 )
797 OR
798 ( Transaction_Type_Id =35 -- Push Type
799 and Transaction_Action_Id =1
800 and Transaction_Source_Type_Id = 5
801 and Wms_Task_Type =1
802 )
803 )
804 THEN 3-- Picking
805 when ( Transaction_Type_Id =64
806 and Transaction_Action_Id =2
807 and Transaction_Source_Type_Id = 4
808 and Wms_Task_Type =4
809 )
810 THEN 8 -- Replenishment
811 when ( Transaction_Type_Id =64
812 and Transaction_Action_Id =2
813 and Transaction_Source_Type_Id = 4
814 and Wms_Task_Type =5
815 )
816 THEN 7 -- Move order transfer
817 when ( Transaction_Type_Id =63
818 and Transaction_Action_Id =1
819 and Transaction_Source_Type_Id = 4
820 and Wms_Task_Type =6
821 )
822 THEN 6 -- Move Order Issue
823 end
824 ) actvity_detail_id,
825 (CASE WHEN ( Transaction_Type_Id =63
826 and Transaction_Action_Id =1
827 and Transaction_Source_Type_Id = 4
828 and Wms_Task_Type =6
829 )
830 THEN 5 -- Issue
831 ELSE 2 --Load
832 END
833 )operation_id,
834 NULL,-- not inbound so document type is NULL
835 mmtt.subinventory_code,
836 mmtt.locator_id,
837 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
838 else mmtt.transaction_uom
839 end
840 ),-- so if LPN is populated we donot need item level information
841 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
842 else mmtt.transaction_quantity
843 end
844 ),-- so if LPN is populated we donot need item level information
845 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
846 else mmtt.inventory_item_id
847 end
848 ),-- so if LPN is populated we donot need item level information
849 mmtt.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
853 demand_source_line will be NULL. hence, parent_line_id will be populated for BULK tasks) */
850 (CASE WHEN mmtt.demand_source_line is NOT NULL THEN to_number(mmtt.demand_source_line)
851 else mmtt.parent_line_id
852 end), /*mmtt.demand_source_line, Modified for bug # 5478983(For Bulk Tasks,
854 3, --Individual and system directed
855 operation_plan_id,
856 FND_GLOBAL.USER_ID,
857 SYSDATE,
858 FND_GLOBAL.LOGIN_ID,
859 FND_GLOBAL.USER_ID,
860 SYSDATE
861 from
862 mtl_material_transactions_temp mmtt
863 where
864 mmtt.organization_id = p_org_id
865 and
866 (
867 ( mmtt.transaction_Type_Id = 52
868 and mmtt.Transaction_Action_Id =28
869 and mmtt.Transaction_Source_Type_Id = 2
870 and mmtt.Wms_Task_Type =1
871 )
872 OR
873 ( mmtt.transaction_Type_Id = 53
874 and mmtt.Transaction_Action_Id =28
875 and mmtt.Transaction_Source_Type_Id = 8
876 and mmtt.Wms_Task_Type =1
877 )
878 OR
879 ( mmtt.transaction_Type_Id = 64
880 and mmtt.Transaction_Action_Id =2
881 and mmtt.Transaction_Source_Type_Id = 4
882 and mmtt.Wms_Task_Type =4
883 )
884 OR
885 ( mmtt.transaction_Type_Id = 64
886 and mmtt.Transaction_Action_Id =2
887 and mmtt.Transaction_Source_Type_Id = 4
888 and mmtt.Wms_Task_Type =5
889 )
890 OR
891 ( mmtt.transaction_Type_Id = 63
892 and mmtt.Transaction_Action_Id =1
893 and mmtt.Transaction_Source_Type_Id = 4
894 and mmtt.Wms_Task_Type =6
895 )
896 OR
897 ( mmtt.transaction_Type_Id = 51 -- Pull Type
898 and mmtt.Transaction_Action_Id =2
899 and mmtt.Transaction_Source_Type_Id = 13
900 and mmtt.Wms_Task_Type =1
901 )
902 OR
903 ( mmtt.transaction_Type_Id = 35 -- Push Type
904 and mmtt.Transaction_Action_Id =1
905 and mmtt.Transaction_Source_Type_Id = 5
906 and mmtt.Wms_Task_Type =1
907 )
908 )
909 and mmtt.wms_task_status IN(1,8)
910 and mmtt.transaction_temp_id = nvl(mmtt.parent_line_id, mmtt.transaction_temp_id); -- Added for bug #5478983
911
912 l_num_rows_inserted := SQL%ROWCOUNT;
913
914 IF g_debug=1 THEN
915 debug('The no of rows inserted for LOAD TASKS Outbound/Relenishment tasks(pending,unreleased)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
916 END IF;
917
918
919 EXCEPTION
920 WHEN OTHERS THEN
921
922 IF g_debug=1 THEN
923 debug('Exception in populating rows for LOAD TASKS Outbound/Relenishment tasks(pending,unreleased)','POPULATE_EXPECTED_WORK');
924 END IF;
925
926 x_return_status := fnd_api.g_ret_sts_error;
927 l_num_sql_failed := l_num_sql_failed + 1;
928
929 END;
930
931
932 --4. Outbound Shipping / Manufacturing
933 -- o Queued/Dispatched for Sales orders,
934 -- manufacturing component picks, and internal orders
935 --6. Pending and outstanding replenishment tasks
936 -- These are FOR Load Operation
937
938 BEGIN
939 IF g_debug=1 THEN
940 debug('Before populating work for LOAD TASKS Outbound/Relenishment tasks(queued,dispatched) ','POPULATE_EXPECTED_WORK');
941 END IF;
942
943 l_num_rows_inserted := 0;
944
945 insert into WMS_ELS_EXP_RESOURCE
946 ( els_exp_resource_id ,
947 organization_id,
948 activity_id,
949 activity_detail_id,
950 operation_id,
951 document_type,
952 source_subinventory,
953 source_locator_id,
954 transaction_uom,
955 quantity,
956 inventory_item_id,
957 source_header_id,
958 source_line_id,
959 group_id,
960 operation_plan_id,
961 last_updated_by,
962 last_update_Date,
963 last_update_login,
964 created_by,
965 creation_Date
966 )
967 select wms_els_exp_resource_s.nextval,
968 mmtt.organization_id,
969 (CASE when (
970 ( Transaction_Type_Id = 52
971 and Transaction_Action_Id =28
972 and Transaction_Source_Type_Id = 2
973 and Wms_Task_Type =1
974 )
975 OR
976 ( Transaction_Type_Id =53
977 and Transaction_Action_Id =28
978 and Transaction_Source_Type_Id = 8
979 and Wms_Task_Type =1
980 )
981 )
982 THEN 3
983 when (
984 ( Transaction_Type_Id =64
985 and Transaction_Action_Id =2
986 AND Transaction_Source_Type_Id = 4
987 and Wms_Task_Type =4
988 )
989 OR
990 ( Transaction_Type_Id =64
991 and Transaction_Action_Id =2
992 and Transaction_Source_Type_Id = 4
993 and Wms_Task_Type =5
994 )
995 OR
996 ( Transaction_Type_Id =63
997 and Transaction_Action_Id =1
998 and Transaction_Source_Type_Id = 4
999 and Wms_Task_Type =6
1000 )
1001 )
1002 THEN 4 -- Warehousing
1003 when (
1007 and Wms_Task_Type =1
1004 ( Transaction_Type_Id =51 -- Pull type
1005 and Transaction_Action_Id =2
1006 and Transaction_Source_Type_Id = 13
1008 )
1009 OR
1010 ( Transaction_Type_Id =35 -- Push type
1011 and Transaction_Action_Id =1
1012 and Transaction_Source_Type_Id = 5
1013 and Wms_Task_Type =1
1014 )
1015
1016 )
1017 THEN 2 -- Manufacturing*/
1018 end
1019 ) activity_id,
1020 (CASE when (
1021 ( Transaction_Type_Id =52
1022 and Transaction_Action_Id =28
1023 and Transaction_Source_Type_Id = 2
1024 and Wms_Task_Type =1
1025 )
1026 OR
1027 ( Transaction_Type_Id =53
1028 and Transaction_Action_Id =28
1029 and Transaction_Source_Type_Id = 8
1030 and Wms_Task_Type =1
1031 )
1032 OR
1033 ( Transaction_Type_Id =51 -- Pull Type
1034 and Transaction_Action_Id =2
1035 and Transaction_Source_Type_Id = 13
1036 and Wms_Task_Type =1
1037 )
1038 OR
1039 ( Transaction_Type_Id =35 -- Push type
1040 and Transaction_Action_Id =1
1041 and Transaction_Source_Type_Id = 5
1042 and Wms_Task_Type =1
1043 )
1044 )
1045 THEN 3-- Picking
1046 when ( Transaction_Type_Id =64
1047 and Transaction_Action_Id =2
1048 and Transaction_Source_Type_Id = 4
1049 and Wms_Task_Type =4
1050 )
1051 THEN 8 -- Replenishment
1052 when ( Transaction_Type_Id =64
1053 and Transaction_Action_Id =2
1054 and Transaction_Source_Type_Id = 4
1055 and Wms_Task_Type =5
1056 )
1057 THEN 7 -- Move order transfer
1058 when ( Transaction_Type_Id =63
1059 and Transaction_Action_Id =1
1060 and Transaction_Source_Type_Id = 4
1061 and Wms_Task_Type =6
1062 )
1063 THEN 6 -- Move Order Issue
1064 end
1065 ) actvity_detail_id,
1066 (CASE WHEN ( Transaction_Type_Id =63
1067 and Transaction_Action_Id =1
1068 and Transaction_Source_Type_Id = 4
1069 and Wms_Task_Type =6
1070 )
1071 THEN 5 -- Issue
1072 ELSE 2 --Load
1073 END
1074 )operation_id,
1075 NULL,-- not inbound so document type is NULL
1076 mmtt.subinventory_code,
1077 mmtt.locator_id,
1078 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
1079 else mmtt.transaction_uom
1080 end
1081 ),-- so if LPN is populated we donot need item level information
1082 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
1083 else mmtt.transaction_quantity
1084 end
1085 ),-- so if LPN is populated we donot need item level information
1086 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
1087 else mmtt.inventory_item_id
1088 end
1089 ),-- so if LPN is populated we donot need item level information
1090 mmtt.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
1091 (CASE WHEN mmtt.demand_source_line is NOT NULL THEN to_number(mmtt.demand_source_line)
1092 else mmtt.parent_line_id
1093 end), /*mmtt.demand_source_line, Modified for bug # 5478983(For Bulk Tasks,
1094 demand_source_line will be NULL. hence, parent_line_id will be populated for BULK tasks) */
1095 3, --Individual and system directed
1096 mmtt.operation_plan_id,
1097 FND_GLOBAL.USER_ID,
1098 SYSDATE,
1099 FND_GLOBAL.LOGIN_ID,
1100 FND_GLOBAL.USER_ID,
1101 SYSDATE
1102 from
1103 mtl_material_transactions_temp mmtt,
1104 wms_dispatched_tasks wdt
1105 where
1106 mmtt.organization_id = p_org_id
1107 and
1108 (
1109 ( mmtt.transaction_Type_Id = 52
1110 and mmtt.Transaction_Action_Id =28
1111 and mmtt.Transaction_Source_Type_Id = 2
1112 and mmtt.Wms_Task_Type =1
1113 )
1114 OR
1115 ( mmtt.transaction_Type_Id = 53
1116 and mmtt.Transaction_Action_Id =28
1117 and mmtt.Transaction_Source_Type_Id = 8
1118 and mmtt.Wms_Task_Type =1
1119 )
1120 OR
1121 ( mmtt.transaction_Type_Id = 64
1122 and mmtt.Transaction_Action_Id =2
1123 and mmtt.Transaction_Source_Type_Id = 4
1124 and mmtt.Wms_Task_Type =4
1125 )
1126 OR
1127 ( mmtt.transaction_Type_Id = 64
1128 and mmtt.Transaction_Action_Id =2
1129 and mmtt.Transaction_Source_Type_Id = 4
1130 and mmtt.Wms_Task_Type =5
1131 )
1132 OR
1133 ( mmtt.transaction_Type_Id = 63
1134 and mmtt.Transaction_Action_Id =1
1135 and mmtt.Transaction_Source_Type_Id = 4
1136 and mmtt.Wms_Task_Type =6
1137 )
1138 OR
1139 ( mmtt.transaction_Type_Id = 51 --Pull Type
1140 and mmtt.Transaction_Action_Id =2
1144 OR
1141 and mmtt.Transaction_Source_Type_Id = 13
1142 and mmtt.Wms_Task_Type =1
1143 )
1145 ( mmtt.transaction_Type_Id = 35 --Push Type
1146 and mmtt.Transaction_Action_Id =1
1147 and mmtt.Transaction_Source_Type_Id = 5
1148 and mmtt.Wms_Task_Type =1
1149 )
1150 )
1151 and (wdt.status IN (2,3) and wdt.transaction_temp_id = mmtt.transaction_temp_id)
1152 and mmtt.transaction_temp_id = nvl(mmtt.parent_line_id, mmtt.transaction_temp_id); -- Added for bug #5478983
1153
1154 l_num_rows_inserted := SQL%ROWCOUNT;
1155
1156 IF g_debug=1 THEN
1157 debug('The no of rows inserted for LOAD TASKS Outbound/Relenishment tasks(queued,dispatched)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
1158 END IF;
1159
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163
1164 IF g_debug=1 THEN
1165 debug('Exception in populating rows for LOAD TASKS Outbound/Relenishment tasks(queued,dispatched)','POPULATE_EXPECTED_WORK');
1166 END IF;
1167
1168 x_return_status := fnd_api.g_ret_sts_error;
1169 l_num_sql_failed := l_num_sql_failed + 1;
1170
1171 END;
1172
1173
1174 --4. Outbound Shipping / Manufacturing
1175 -- o Unreleased / pending for Sales orders,
1176 -- manufacturing component picks, and internal orders
1177 --6. Pending and outstanding replenishment tasks
1178 -- These are FOR DROP Operation
1179
1180 BEGIN
1181 IF g_debug=1 THEN
1182 debug('Before populating work for DROP TASKS Outbound/Relenishment tasks(pending,unreleased) ','POPULATE_EXPECTED_WORK');
1183 END IF;
1184
1185 l_num_rows_inserted := 0;
1186
1187 insert into WMS_ELS_EXP_RESOURCE
1188 ( els_exp_resource_id ,
1189 organization_id,
1190 activity_id,
1191 activity_detail_id,
1192 operation_id,
1193 document_type,
1194 destination_subinventory,
1195 destination_locator_id,
1196 transaction_uom,
1197 quantity,
1198 inventory_item_id,
1199 source_header_id,
1200 source_line_id,
1201 group_id,
1202 operation_plan_id,
1203 last_updated_by,
1204 last_update_Date,
1205 last_update_login,
1206 created_by,
1207 creation_Date
1208 )
1209 select wms_els_exp_resource_s.nextval,
1210 mmtt.organization_id,
1211 (CASE when (
1212 ( Transaction_Type_Id = 52
1213 and Transaction_Action_Id =28
1214 and Transaction_Source_Type_Id = 2
1215 and Wms_Task_Type =1
1216 )
1217 OR
1218 ( Transaction_Type_Id =53
1219 and Transaction_Action_Id =28
1220 and Transaction_Source_Type_Id = 8
1221 and Wms_Task_Type =1
1222 )
1223 )
1224 THEN 3
1225 when (
1226 ( Transaction_Type_Id =64
1227 and Transaction_Action_Id =2
1228 AND Transaction_Source_Type_Id = 4
1229 and Wms_Task_Type =4
1230 )
1231 OR
1232 ( Transaction_Type_Id =64
1233 and Transaction_Action_Id =2
1234 and Transaction_Source_Type_Id = 4
1235 and Wms_Task_Type =5
1236 )
1237 )
1238 THEN 4 -- Warehousing
1239 when (
1240 ( Transaction_Type_Id =51 -- Pull Type
1241 and Transaction_Action_Id =2
1242 and Transaction_Source_Type_Id = 13
1243 and Wms_Task_Type =1
1244 )
1245 OR
1246 ( Transaction_Type_Id =35 -- Push Type
1247 and Transaction_Action_Id =1
1248 and Transaction_Source_Type_Id = 5
1249 and Wms_Task_Type =1
1250 )
1251 )
1252 THEN 2 -- Manufacturing*/
1253 end
1254 ) activity_id,
1255 (CASE when (
1256 ( Transaction_Type_Id =52
1257 and Transaction_Action_Id =28
1258 and Transaction_Source_Type_Id = 2
1259 and Wms_Task_Type =1
1260 )
1261 OR
1262 ( Transaction_Type_Id =53
1263 and Transaction_Action_Id =28
1264 and Transaction_Source_Type_Id = 8
1265 and Wms_Task_Type =1
1266 )
1267 OR
1268 ( Transaction_Type_Id =51 -- Pull Type
1269 and Transaction_Action_Id =2
1270 and Transaction_Source_Type_Id = 13
1271 and Wms_Task_Type =1
1272 )
1273 OR
1274 ( Transaction_Type_Id =35 -- Push Type
1275 and Transaction_Action_Id =1
1276 and Transaction_Source_Type_Id = 5
1277 and Wms_Task_Type =1
1278 )
1279 )
1280 THEN 3-- Picking
1281 when ( Transaction_Type_Id =64
1282 and Transaction_Action_Id =2
1283 and Transaction_Source_Type_Id = 4
1284 and Wms_Task_Type =4
1285 )
1289 and Transaction_Source_Type_Id = 4
1286 THEN 8 -- Replenishment
1287 when ( Transaction_Type_Id =64
1288 and Transaction_Action_Id =2
1290 and Wms_Task_Type =5
1291 )
1292 THEN 7 -- Move order transfer
1293 end
1294 ) actvity_detail_id,
1295 3,--Drop(Operation_ID)
1296 NULL,-- not inbound so document type is NULL
1297 mmtt.transfer_subinventory,
1298 mmtt.transfer_to_location,
1299 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
1300 else mmtt.transaction_uom
1301 end
1302 ),-- so if LPN is populated we donot need item level information
1303 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
1304 else mmtt.transaction_quantity
1305 end
1306 ),-- so if LPN is populated we donot need item level information
1307 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
1308 else mmtt.inventory_item_id
1309 end
1310 ),-- so if LPN is populated we donot need item level information
1311 mmtt.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
1312 mmtt.demand_source_line,
1313 3, --Individual and system directed
1314 mmtt.operation_plan_id,
1315 FND_GLOBAL.USER_ID,
1316 SYSDATE,
1317 FND_GLOBAL.LOGIN_ID,
1318 FND_GLOBAL.USER_ID,
1319 SYSDATE
1320 from
1321 mtl_material_transactions_temp mmtt
1322 where
1323 mmtt.organization_id = p_org_id
1324 and
1325 (
1326 ( mmtt.transaction_Type_Id = 52
1327 and mmtt.Transaction_Action_Id =28
1328 and mmtt.Transaction_Source_Type_Id = 2
1329 and mmtt.Wms_Task_Type =1
1330 )
1331 OR
1332 ( mmtt.transaction_Type_Id = 53
1333 and mmtt.Transaction_Action_Id =28
1334 and mmtt.Transaction_Source_Type_Id = 8
1335 and mmtt.Wms_Task_Type =1
1336 )
1337 OR
1338 ( mmtt.transaction_Type_Id = 64
1339 and mmtt.Transaction_Action_Id =2
1340 and mmtt.Transaction_Source_Type_Id = 4
1341 and mmtt.Wms_Task_Type =4
1342 )
1343 OR
1344 ( mmtt.transaction_Type_Id = 64
1345 and mmtt.Transaction_Action_Id =2
1346 and mmtt.Transaction_Source_Type_Id = 4
1347 and mmtt.Wms_Task_Type =5
1348 )
1349 OR
1350 ( mmtt.transaction_Type_Id = 51 -- Pull Type
1351 and mmtt.Transaction_Action_Id =2
1352 and mmtt.Transaction_Source_Type_Id = 13
1353 and mmtt.Wms_Task_Type =1
1354 )
1355 OR
1356 ( mmtt.transaction_Type_Id = 35 --Push Type
1357 and mmtt.Transaction_Action_Id =1
1358 and mmtt.Transaction_Source_Type_Id = 5
1359 and mmtt.Wms_Task_Type =1
1360 )
1361 )
1362 and mmtt.wms_task_status IN(1,8)
1363 and mmtt.transaction_temp_id <> nvl(mmtt.parent_line_id, -999); -- Added for bug # 5478983
1364
1365 l_num_rows_inserted := SQL%ROWCOUNT;
1366
1367 IF g_debug=1 THEN
1368 debug('The no of rows inserted for DROP TASKS Outbound/Relenishment tasks(pending,unreleased)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
1369 END IF;
1370
1371
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374
1375 IF g_debug=1 THEN
1376 debug('Exception in populating rows for DROP TASKS Outbound/Relenishment tasks(pending,unreleased)','POPULATE_EXPECTED_WORK');
1377 END IF;
1378
1379 x_return_status := fnd_api.g_ret_sts_error;
1380 l_num_sql_failed := l_num_sql_failed + 1;
1381
1382 END;
1383
1384
1385 --4. Outbound Shipping / Manufacturing
1386 -- o Queued/Dispatched for Sales orders,
1387 -- manufacturing component picks, and internal orders
1388 --6. Pending and outstanding replenishment tasks
1389 -- These are FOR Drop Operation
1390
1391 BEGIN
1392 IF g_debug=1 THEN
1393 debug('Before populating work for DROP TASKS Outbound/Relenishment tasks(queued,dispatched) ','POPULATE_EXPECTED_WORK');
1394 END IF;
1395
1396 l_num_rows_inserted := 0;
1397
1398 insert into WMS_ELS_EXP_RESOURCE
1399 ( els_exp_resource_id ,
1400 organization_id,
1401 activity_id,
1402 activity_detail_id,
1403 operation_id,
1404 document_type,
1405 destination_subinventory,
1406 destination_locator_id,
1407 transaction_uom,
1408 quantity,
1409 inventory_item_id,
1410 source_header_id,
1411 source_line_id,
1412 group_id,
1413 operation_plan_id,
1414 last_updated_by,
1415 last_update_Date,
1416 last_update_login,
1417 created_by,
1418 creation_Date
1419 )
1420 select wms_els_exp_resource_s.nextval,
1421 mmtt.organization_id,
1422 (CASE when (
1423 ( Transaction_Type_Id = 52
1424 and Transaction_Action_Id =28
1425 and Transaction_Source_Type_Id = 2
1426 and Wms_Task_Type =1
1427 )
1428 OR
1429 ( Transaction_Type_Id =53
1430 and Transaction_Action_Id =28
1431 and Transaction_Source_Type_Id = 8
1432 and Wms_Task_Type =1
1433 )
1434 )
1435 THEN 3
1436 when (
1437 ( Transaction_Type_Id =64
1441 )
1438 and Transaction_Action_Id =2
1439 AND Transaction_Source_Type_Id = 4
1440 and Wms_Task_Type =4
1442 OR
1443 ( Transaction_Type_Id =64
1444 and Transaction_Action_Id =2
1445 and Transaction_Source_Type_Id = 4
1446 and Wms_Task_Type =5
1447 )
1448 )
1449 THEN 4 -- Warehousing
1450 when (
1451 ( Transaction_Type_Id =51 -- Pull Type
1452 and Transaction_Action_Id =2
1453 and Transaction_Source_Type_Id = 13
1454 and Wms_Task_Type =1
1455 )
1456 OR
1457 ( Transaction_Type_Id =35 -- Push Type
1458 and Transaction_Action_Id =1
1459 and Transaction_Source_Type_Id = 5
1460 and Wms_Task_Type =1
1461 )
1462 )
1463 THEN 2 -- Manufacturing*/
1464 end
1465 ) activity_id,
1466 (CASE when (
1467 ( Transaction_Type_Id =52
1468 and Transaction_Action_Id =28
1469 and Transaction_Source_Type_Id = 2
1470 and Wms_Task_Type =1
1471 )
1472 OR
1473 ( Transaction_Type_Id =53
1474 and Transaction_Action_Id =28
1475 and Transaction_Source_Type_Id = 8
1476 and Wms_Task_Type =1
1477 )
1478 OR
1479 ( Transaction_Type_Id =51 -- Pull Type
1480 and Transaction_Action_Id =2
1481 and Transaction_Source_Type_Id = 13
1482 and Wms_Task_Type =1
1483 )
1484 OR
1485 ( Transaction_Type_Id =35 -- Push type
1486 and Transaction_Action_Id =1
1487 and Transaction_Source_Type_Id = 5
1488 and Wms_Task_Type =1
1489 )
1490 )
1491 THEN 3-- Picking
1492 when ( Transaction_Type_Id =64
1493 and Transaction_Action_Id =2
1494 and Transaction_Source_Type_Id = 4
1495 and Wms_Task_Type =4
1496 )
1497 THEN 8 -- Replenishment
1498 when ( Transaction_Type_Id =64
1499 and Transaction_Action_Id =2
1500 and Transaction_Source_Type_Id = 4
1501 and Wms_Task_Type =5
1502 )
1503 THEN 7 -- Move order transfer
1504 end
1505 ) actvity_detail_id,
1506 3,--Drop(Operation_ID)
1507 NULL,-- not inbound so document type is NULL
1508 mmtt.transfer_subinventory,
1509 mmtt.transfer_to_location,
1510 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
1511 else mmtt.transaction_uom
1512 end
1513 ),-- so if LPN is populated we donot need item level information
1514 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
1515 else mmtt.transaction_quantity
1516 end
1517 ),-- so if LPN is populated we donot need item level information
1518 (CASE when allocated_lpn_id IS NOT NULL THEN NULL
1519 else mmtt.inventory_item_id
1520 end
1521 ),-- so if LPN is populated we donot need item level information
1522 mmtt.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
1523 mmtt.demand_source_line,
1524 3, --Individual and system directed
1525 mmtt.operation_plan_id,
1526 FND_GLOBAL.USER_ID,
1527 SYSDATE,
1528 FND_GLOBAL.LOGIN_ID,
1529 FND_GLOBAL.USER_ID,
1530 SYSDATE
1531 from
1532 mtl_material_transactions_temp mmtt,
1533 wms_dispatched_tasks wdt
1534 where
1535 mmtt.organization_id = p_org_id
1536 and
1537 (
1538 ( mmtt.transaction_Type_Id = 52
1539 and mmtt.Transaction_Action_Id =28
1540 and mmtt.Transaction_Source_Type_Id = 2
1541 and mmtt.Wms_Task_Type =1
1542 )
1543 OR
1544 ( mmtt.transaction_Type_Id = 53
1545 and mmtt.Transaction_Action_Id =28
1546 and mmtt.Transaction_Source_Type_Id = 8
1547 and mmtt.Wms_Task_Type =1
1548 )
1549 OR
1550 ( mmtt.transaction_Type_Id = 64
1551 and mmtt.Transaction_Action_Id =2
1552 and mmtt.Transaction_Source_Type_Id = 4
1553 and mmtt.Wms_Task_Type =4
1554 )
1555 OR
1556 ( mmtt.transaction_Type_Id = 64
1557 and mmtt.Transaction_Action_Id =2
1558 and mmtt.Transaction_Source_Type_Id = 4
1559 and mmtt.Wms_Task_Type =5
1560 )
1561 OR
1562 ( mmtt.transaction_Type_Id = 51 -- Pull Type
1563 and mmtt.Transaction_Action_Id =2
1564 and mmtt.Transaction_Source_Type_Id = 13
1565 and mmtt.Wms_Task_Type =1
1566 )
1567 OR
1568 ( mmtt.transaction_Type_Id = 35 -- Push Type
1569 and mmtt.Transaction_Action_Id =1
1570 and mmtt.Transaction_Source_Type_Id = 5
1571 and mmtt.Wms_Task_Type =1
1572 )
1573 )
1574 and (wdt.status IN (2,3,4) and wdt.transaction_temp_id = mmtt.transaction_temp_id)
1575 and mmtt.parent_line_id is NULL; -- Added for bug # 5478983
1576
1577 l_num_rows_inserted := SQL%ROWCOUNT;
1578
1582
1579 IF g_debug=1 THEN
1580 debug('The no of rows inserted for DROP TASKS Outbound/Relenishment tasks(queued,dispatched)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
1581 END IF;
1583 /*
1584 * The following SQL has been added for the bug # 5478983.
1585 * The SQL will insert the information related to the Bulk drop tasks.
1586 *
1587 */
1588
1589 IF g_debug=1 THEN
1590 debug('Before populating work for BULK DROP TASKS Outbound/Relenishment tasks(queued,dispatched) ','POPULATE_EXPECTED_WORK');
1591 END IF;
1592
1593 l_num_rows_inserted := 0;
1594
1595 insert into WMS_ELS_EXP_RESOURCE
1596 ( els_exp_resource_id ,
1597 organization_id,
1598 activity_id,
1599 activity_detail_id,
1600 operation_id,
1601 document_type,
1602 destination_subinventory,
1603 destination_locator_id,
1604 transaction_uom,
1605 quantity,
1606 inventory_item_id,
1607 source_header_id,
1608 source_line_id,
1609 group_id,
1610 operation_plan_id,
1611 last_updated_by,
1612 last_update_Date,
1613 last_update_login,
1614 created_by,
1615 creation_Date
1616 )
1617 select wms_els_exp_resource_s.nextval,
1618 mmtt2.organization_id,
1619 (CASE when (
1620 ( mmtt2.Transaction_Type_Id = 52
1621 and mmtt2.Transaction_Action_Id =28
1622 and mmtt2.Transaction_Source_Type_Id = 2
1623 and mmtt2.Wms_Task_Type =1
1624 )
1625 OR
1626 ( mmtt2.Transaction_Type_Id =53
1627 and mmtt2.Transaction_Action_Id =28
1628 and mmtt2.Transaction_Source_Type_Id = 8
1629 and mmtt2.Wms_Task_Type =1
1630 )
1631 )
1632 THEN 3
1633 when (
1634 ( mmtt2.Transaction_Type_Id =64
1635 and mmtt2.Transaction_Action_Id =2
1636 AND mmtt2.Transaction_Source_Type_Id = 4
1637 and mmtt2.Wms_Task_Type =4
1638 )
1639 OR
1640 ( mmtt2.Transaction_Type_Id =64
1641 and mmtt2.Transaction_Action_Id =2
1642 and mmtt2.Transaction_Source_Type_Id = 4
1643 and mmtt2.Wms_Task_Type =5
1644 )
1645 )
1646 THEN 4 -- Warehousing
1647 when (
1648 ( mmtt2.Transaction_Type_Id =51 -- Pull Type
1649 and mmtt2.Transaction_Action_Id =2
1650 and mmtt2.Transaction_Source_Type_Id = 13
1651 and mmtt2.Wms_Task_Type =1
1652 )
1653 OR
1654 ( mmtt2.Transaction_Type_Id =35 -- Push Type
1655 and mmtt2.Transaction_Action_Id =1
1656 and mmtt2.Transaction_Source_Type_Id = 5
1657 and mmtt2.Wms_Task_Type =1
1658 )
1659 )
1660 THEN 2 -- Manufacturing*/
1661 end
1662 ) activity_id,
1663 (CASE when (
1664 ( mmtt2.Transaction_Type_Id =52
1665 and mmtt2.Transaction_Action_Id =28
1666 and mmtt2.Transaction_Source_Type_Id = 2
1667 and mmtt2.Wms_Task_Type =1
1668 )
1669 OR
1670 ( mmtt2.Transaction_Type_Id =53
1671 and mmtt2.Transaction_Action_Id =28
1672 and mmtt2.Transaction_Source_Type_Id = 8
1673 and mmtt2.Wms_Task_Type =1
1674 )
1675 OR
1676 ( mmtt2.Transaction_Type_Id =51 -- Pull Type
1677 and mmtt2.Transaction_Action_Id =2
1678 and mmtt2.Transaction_Source_Type_Id = 13
1679 and mmtt2.Wms_Task_Type =1
1680 )
1681 OR
1682 ( mmtt2.Transaction_Type_Id =35 -- Push type
1683 and mmtt2.Transaction_Action_Id =1
1684 and mmtt2.Transaction_Source_Type_Id = 5
1685 and mmtt2.Wms_Task_Type =1
1686 )
1687 )
1688 THEN 3-- Picking
1689 when ( mmtt2.Transaction_Type_Id =64
1690 and mmtt2.Transaction_Action_Id =2
1691 and mmtt2.Transaction_Source_Type_Id = 4
1692 and mmtt2.Wms_Task_Type =4
1693 )
1694 THEN 8 -- Replenishment
1695 when ( mmtt2.Transaction_Type_Id =64
1696 and mmtt2.Transaction_Action_Id =2
1697 and mmtt2.Transaction_Source_Type_Id = 4
1698 and mmtt2.Wms_Task_Type =5
1699 )
1700 THEN 7 -- Move order transfer
1701 end
1702 ) actvity_detail_id,
1703 3,--Drop(Operation_ID)
1704 NULL,-- not inbound so document type is NULL
1705 mmtt2.transfer_subinventory,
1706 mmtt2.transfer_to_location,
1707 (CASE when mmtt2.allocated_lpn_id IS NOT NULL THEN NULL
1708 else mmtt2.transaction_uom
1709 end
1710 ),-- so if LPN is populated we donot need item level information
1711 (CASE when mmtt2.allocated_lpn_id IS NOT NULL THEN NULL
1712 else mmtt2.transaction_quantity
1713 end
1714 ),-- so if LPN is populated we donot need item level information
1718 ),-- so if LPN is populated we donot need item level information
1715 (CASE when mmtt2.allocated_lpn_id IS NOT NULL THEN NULL
1716 else mmtt2.inventory_item_id
1717 end
1719 mmtt2.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
1720 mmtt2.demand_source_line,
1721 3, --Individual and system directed
1722 mmtt2.operation_plan_id,
1723 FND_GLOBAL.USER_ID,
1724 SYSDATE,
1725 FND_GLOBAL.LOGIN_ID,
1726 FND_GLOBAL.USER_ID,
1727 SYSDATE
1728 from
1729 mtl_material_transactions_temp mmtt1,
1730 mtl_material_transactions_temp mmtt2,
1731 wms_dispatched_tasks wdt
1732 where
1733 mmtt1.organization_id = p_org_id
1734 and mmtt2.organization_id = p_org_id
1735 and
1736 (
1737 ( mmtt2.transaction_Type_Id = 52
1738 and mmtt2.Transaction_Action_Id =28
1739 and mmtt2.Transaction_Source_Type_Id = 2
1740 and mmtt2.Wms_Task_Type =1
1741 )
1742 OR
1743 ( mmtt2.transaction_Type_Id = 53
1744 and mmtt2.Transaction_Action_Id =28
1745 and mmtt2.Transaction_Source_Type_Id = 8
1746 and mmtt2.Wms_Task_Type =1
1747 )
1748 OR
1749 ( mmtt2.transaction_Type_Id = 64
1750 and mmtt2.Transaction_Action_Id =2
1751 and mmtt2.Transaction_Source_Type_Id = 4
1752 and mmtt2.Wms_Task_Type =4
1753 )
1754 OR
1755 ( mmtt2.transaction_Type_Id = 64
1756 and mmtt2.Transaction_Action_Id =2
1757 and mmtt2.Transaction_Source_Type_Id = 4
1758 and mmtt2.Wms_Task_Type =5
1759 )
1760 OR
1761 ( mmtt2.transaction_Type_Id = 51 -- Pull Type
1762 and mmtt2.Transaction_Action_Id =2
1763 and mmtt2.Transaction_Source_Type_Id = 13
1764 and mmtt2.Wms_Task_Type =1
1765 )
1766 OR
1767 ( mmtt2.transaction_Type_Id = 35 -- Push Type
1768 and mmtt2.Transaction_Action_Id =1
1769 and mmtt2.Transaction_Source_Type_Id = 5
1770 and mmtt2.Wms_Task_Type =1
1771 )
1772 )
1773 and (wdt.status IN (2,3,4) and wdt.transaction_temp_id = mmtt1.transaction_temp_id)
1774 and mmtt1.parent_line_id = mmtt2.parent_line_id
1775 and mmtt1.parent_line_id <> mmtt2.transaction_temp_id
1776 and mmtt1.parent_line_id is NOT NULL
1777 and mmtt2.parent_line_id is NOT NULL;
1778
1779 l_num_rows_inserted := SQL%ROWCOUNT;
1780
1781 IF g_debug=1 THEN
1782 debug('The no of rows inserted for BULK DROP TASKS Outbound/Relenishment tasks(queued,dispatched)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
1783 END IF;
1784
1785
1786 EXCEPTION
1787 WHEN OTHERS THEN
1788
1789 IF g_debug=1 THEN
1790 debug('Exception in populating rows for DROP TASKS Outbound/Relenishment tasks(queued,dispatched)','POPULATE_EXPECTED_WORK');
1791 END IF;
1792
1793 x_return_status := fnd_api.g_ret_sts_error;
1794 l_num_sql_failed := l_num_sql_failed + 1;
1795
1796 END;
1797
1798
1799
1800 -- Inventory Move
1801 -- o Material that is Moved between the inventory locations
1802 BEGIN
1803 IF g_debug=1 THEN
1804 debug('Before populating work for Inventory Move for DROP ','POPULATE_EXPECTED_WORK');
1805 END IF;
1806
1807 l_num_rows_inserted := 0;
1808
1809 INSERT INTO WMS_ELS_EXP_RESOURCE
1810 (els_exp_resource_id ,
1811 organization_id,
1812 activity_id,
1813 activity_detail_id,
1814 operation_id,
1815 destination_subinventory,
1816 destination_locator_id,
1817 transaction_uom,
1818 quantity,
1819 inventory_item_id,
1820 source_header_id,
1821 source_line_id,
1822 group_id,
1823 last_updated_by,
1824 last_update_Date,
1825 last_update_login,
1826 created_by,
1827 creation_Date
1828 )
1829 SELECT WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
1830 organization_id,
1831 activity_id,
1832 activity_detail_id,
1833 operation_id,
1834 destination_subinventory,
1835 destination_locator_id,
1836 transaction_uom,
1837 transaction_quantity,
1838 inventory_item_id,
1839 source_header_id,
1840 source_line_id,
1841 group_id,
1842 FND_GLOBAL.USER_ID,
1843 SYSDATE,
1844 FND_GLOBAL.LOGIN_ID,
1845 FND_GLOBAL.USER_ID,
1846 SYSDATE
1847 FROM
1848 (
1849 SELECT DISTINCT
1850 mmtt.transfer_organization organization_id,
1851 4 activity_id,--Warehousing
1852 9 activity_detail_id,--Inventory Move
1853 3 operation_id,--Drop
1854 mmtt.transfer_subinventory destination_subinventory,
1855 mmtt.transfer_to_location destination_locator_id,
1856 decode(num_lines,1,mmtt.transaction_uom,NULL) transaction_uom,
1857 decode (num_lines,1,mmtt.transaction_quantity,NULL) transaction_quantity,
1858 decode (num_lines,1,mmtt.inventory_item_id,NULL) inventory_item_id,
1859 decode (num_lines,1,mmtt.transaction_header_id,0,NULL,mmtt.transaction_header_id) source_header_id,
1860 decode (num_lines,1,mmtt.transaction_temp_id,0,NULL,mmtt.lpn_id) source_line_id,
1861 1 group_id --manual and user directed
1862 FROM
1863 (SELECT lpn_id,transfer_to_location,count(*) num_lines
1864 FROM
1865 mtl_material_transactions_temp
1866 WHERE organization_id = p_org_id
1867 AND transaction_type_id = 64
1868 AND transaction_action_id =2
1872 WHERE mmtt.lpn_id = tab1.lpn_id
1869 AND transaction_source_type_id =4
1870 AND wms_task_type =2
1871 GROUP BY lpn_id,transfer_to_location ) tab1, mtl_material_transactions_temp mmtt
1873 AND mmtt.transfer_to_location = tab1.transfer_to_location
1874 );
1875
1876 l_num_rows_inserted := SQL%ROWCOUNT;
1877
1878 IF g_debug=1 THEN
1879 debug('The no of rows inserted for Inventory Move DROP '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
1880 END IF;
1881
1882
1883 EXCEPTION
1884 WHEN OTHERS THEN
1885
1886 IF g_debug=1 THEN
1887 debug('Exception in populating rows for Inventory Move DROP','POPULATE_EXPECTED_WORK');
1888 END IF;
1889
1890 x_return_status := fnd_api.g_ret_sts_error;
1891 l_num_sql_failed := l_num_sql_failed + 1;
1892
1893 END;
1894
1895 -- Staging Move
1896 --
1897
1898 BEGIN
1899 IF g_debug=1 THEN
1900 debug('Before populating work for Staging Move for DROP ','POPULATE_EXPECTED_WORK');
1901 END IF;
1902
1903 l_num_rows_inserted := 0;
1904
1905
1906 INSERT INTO WMS_ELS_EXP_RESOURCE
1907 (els_exp_resource_id ,
1908 organization_id,
1909 activity_id,
1910 activity_detail_id,
1911 operation_id,
1912 destination_subinventory,
1913 destination_locator_id,
1914 transaction_uom,
1915 quantity,
1916 inventory_item_id,
1917 source_header_id,
1918 source_line_id,
1919 operation_plan_id,
1920 group_id,
1921 last_updated_by,
1922 last_update_Date,
1923 last_update_login,
1924 created_by,
1925 creation_Date
1926 )
1927 select WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
1928 organization_id,
1929 activity_id,
1930 activity_detail_id,
1931 operation_id,
1932 subinventory_code,
1933 locator_id,
1934 transaction_uom,
1935 transaction_quantity,
1936 inventory_item_id,
1937 source_header_id,
1938 source_line_id,
1939 operation_plan_id,
1940 group_id,
1941 FND_GLOBAL.USER_ID,
1942 SYSDATE,
1943 FND_GLOBAL.LOGIN_ID,
1944 FND_GLOBAL.USER_ID,
1945 SYSDATE
1946 from
1947 (
1948 select distinct
1949 mmtt.organization_id organization_id,
1950 3 activity_id,--Outbound
1951 4 activity_detail_id,--Staging Move
1952 3 operation_id,--Drop
1953 mmtt.subinventory_code subinventory_code,
1954 mmtt.locator_id locator_id,
1955 decode(num_lines,1,mmtt.transaction_uom,NULL) transaction_uom,
1956 decode (num_lines,1,transaction_quantity,NULL) transaction_quantity,
1957 decode (num_lines,1,inventory_item_id,NULL) inventory_item_id,
1958 decode (num_lines,1,transaction_header_id,NULL) source_header_id,
1959 decode (num_lines,1,transaction_temp_id,NULL) source_line_id,
1960 mmtt.operation_plan_id,
1961 1 group_id --manual and user directed
1962 from
1963 (select content_lpn_id,locator_id,count(*) num_lines from
1964 mtl_material_transactions_temp
1965 where organization_id = p_org_id
1966 and transaction_type_id = 2
1967 and transaction_action_id =2
1968 and transaction_source_type_id =13
1969 AND wms_task_type =7
1970 group by content_lpn_id,locator_id ) tab1, mtl_material_transactions_temp mmtt
1971 where mmtt.content_lpn_id = tab1.content_lpn_id
1972 and mmtt.locator_id = tab1.locator_id
1973 );
1974
1975 l_num_rows_inserted := SQL%ROWCOUNT;
1976
1977 IF g_debug=1 THEN
1978 debug('The no of rows inserted for Staging Move DROP '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
1979 END IF;
1980
1981
1982 EXCEPTION
1983 WHEN OTHERS THEN
1984
1985 IF g_debug=1 THEN
1986 debug('Exception in populating rows for Staging Move DROP','POPULATE_EXPECTED_WORK');
1987 END IF;
1988
1989 x_return_status := fnd_api.g_ret_sts_error;
1990 l_num_sql_failed := l_num_sql_failed + 1;
1991
1992 END;
1993
1994
1995 IF l_num_sql_failed = g_total_sql THEN
1996 RAISE ALL_SQL_FAILED;
1997 END IF;
1998
1999 IF g_debug=1 THEN
2000 debug('The value of x_return_status '||x_return_status ,'POPULATE_EXPECTED_WORK');
2001 END IF;
2002
2003 EXCEPTION
2004 WHEN ALL_SQL_FAILED THEN
2005 IF g_debug=1 THEN
2006 debug('All SQLS failed','POPULATE_EXPECTED_WORK');
2007 END IF;
2008
2009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2010
2011 WHEN OTHERS THEN
2012
2013 IF g_debug=1 THEN
2014 debug('Unexpected error occured','POPULATE_EXPECTED_WORK');
2015 END IF;
2016
2017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2018
2019 END POPULATE_EXPECTED_WORK;
2020
2021
2022
2023 -- We would do the following in this procedure
2024
2025 --Delete all the rows that are already populated in the WMS_ELS_EXP_RESOURCE for
2026 --that organization. This is done so that no old rows are left in the table and the
2027 --table can be freshly populated with expected work. This also ensures that all the
2028 --tasks and expected work that is already done is flushed out and is not accounted any more.
2029
2030 -- Populate the WMS_ELS_EXP_RESOURCE table with the fresh set of expected work for
2031 --the given data period(populated in the global setup).This will be done by calling
2032 --the program WMS_ELS_EXPECTED_RES. Populate_Expecetd_Work.
2033
2037 --starting with the setup row having the least sequence number. Once a match is found
2034
2035 -- Do the matching of the rows in WMS_ELS_EXP_RESOURCE table with the setup rows
2036 --in WMS_ELS_INDIVIDUAL_TASKS_B using the where clause for that setup row(dynamic SQL),
2038 --stamp the Estimated_time column in WMS_ELS_EXP_RESOURCE table with the time required
2039 --to complete the transaction. Also stamp the Expecetd_Resource column based on the global
2040 --setup.
2041
2042 -- The parameters p_data_period_unit, p_data_period_value,
2043 --p_Num_work_hrs_day ,p_Utilization_rate will not be directly used in this
2044 -- program. They are being passed in to retain the link that at the time of
2045 -- running the concurrent what was the value of these global parameters
2046
2047 PROCEDURE MATCH_RATE_EXP_RESOURCE (
2048 errbuf OUT NOCOPY VARCHAR2
2049 , retcode OUT NOCOPY NUMBER
2050 , p_org_id IN NUMBER
2051 , p_data_period_unit IN NUMBER
2052 , p_data_period_value IN NUMBER
2053 , p_Num_work_hrs_day IN NUMBER
2054 , p_Utilization_rate IN NUMBER
2055 )IS
2056
2057
2058 -- cursor to get all the rows in wms_els_individual_tasks_b table
2059 -- for matching
2060
2061 CURSOR c_els_data(l_org_id NUMBER) IS
2062 SELECT els_data_id,
2063 organization_id,
2064 activity_id,
2065 activity_detail_id,
2066 operation_id,
2067 source_zone_id,
2068 source_subinventory,
2069 destination_zone_id,
2070 destination_subinventory,
2071 labor_txn_source_id,
2072 transaction_uom,
2073 from_quantity,
2074 to_quantity,
2075 item_category_id,
2076 operation_plan_id,
2077 group_id,
2078 task_type_id,
2079 expected_travel_time,
2080 expected_txn_time,
2081 expected_idle_time,
2082 travel_time_threshold,
2083 num_trx_matched
2084 FROM wms_els_individual_tasks_b
2085 WHERE organization_id = l_org_id
2086 AND history_flag IS NULL
2087 AND Analysis_id IN (2,4)
2088 ORDER BY group_id DESC,sequence_number ASC;
2089
2090
2091 l_els_data c_els_data%ROWTYPE;
2092
2093 l_sql VARCHAR2(20000);
2094
2095 l_where_clause VARCHAR2(10000);
2096
2097 l_time_per_day NUMBER;
2098
2099 c NUMBER;
2100
2101 l_ret BOOLEAN;
2102
2103 l_message VARCHAR2(250);
2104
2105 l_return_status VARCHAR2(1);
2106 l_msg_count VARCHAR2(10);
2107 l_msg_data VARCHAR2(100);
2108 l_update_count NUMBER;
2109
2110 l_total NUMBER;
2111
2112 l_populate_status NUMBER;
2113
2114 l_num_execution_failed NUMBER;
2115
2116 BEGIN
2117
2118 l_populate_status := 0;
2119 l_update_count := 0;
2120 l_total := 0;
2121 l_num_execution_failed := 0;
2122
2123
2124 SAVEPOINT l_exp_work_populate;
2125
2126 IF g_debug=1 THEN
2127 debug('Starting processing for Expected Resource Requiremnt Analysis','MATCH_RATE_EXP_RESOURCE');
2128 END IF;
2129
2130 -- proceed to any processing only is organization is labor enabled.
2131
2132 IF WMS_LMS_UTILS. ORG_LABOR_MGMT_ENABLED(p_org_id) THEN
2133
2134 -- Insering the data into Global temporary table for the selected organization
2135 -- Added for the bug # 5169490
2136
2137 IF g_debug=1 THEN
2138 debug('Before inserting the data into Global temporary table','MATCH_RATE_EXP_RESOURCE');
2139 END IF;
2140
2141 INSERT INTO WMS_ELS_EXP_RESOURCE_GTEMP
2142 (SELECT els_data_id
2143 , source_header_id
2144 , source_line_id
2145 , activity_id
2146 , activity_detail_id
2147 , operation_id
2148 FROM wms_els_exp_resource
2149 WHERE organization_id = p_org_id
2150 AND els_data_id IS NOT NULL);
2151
2152 IF g_debug=1 THEN
2153 debug('After inserting the data in Global temporary table','MATCH_RATE_EXP_RESOURCE');
2154 END IF;
2155
2156 --Delete all the rows that are already populated in the WMS_ELS_EXP_RESOURCE for
2157 --that organization. This is done so that no old rows are left in the table and the
2158 --table can be freshly populated with expected work. This also ensures that all the
2159 --tasks and expected work that is already done is flushed out and is not accounted any more.
2160
2161 DELETE FROM WMS_ELS_EXP_RESOURCE WHERE organization_id = p_org_id;
2162
2163 -- Populate the WMS_ELS_EXP_RESOURCE table with the fresh set of expected work for
2164 --the given data period(populated in the global setup).This will be done by calling
2165 --the program WMS_ELS_EXPECTED_RES. Populate_Expecetd_Work.
2166
2167 POPULATE_EXPECTED_WORK
2168 ( x_return_status => l_return_status
2169 ,x_msg_count => l_msg_count
2170 ,x_msg_data => l_msg_data
2171 ,p_org_id => p_org_id
2172 );
2173
2174 IF g_debug=1 THEN
2175 debug('The return status from Populate expected work is '|| l_return_status,'MATCH_RATE_EXP_RESOURCE');
2176 END IF;
2177
2181
2178 IF(l_return_status = FND_API.g_ret_sts_error )THEN
2179 -- Populate this status do that it can be used later for
2180 -- completeing the program with warning status
2182 l_populate_status := 1;
2183
2184 END IF;
2185
2186 IF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )THEN
2187
2188 --- Dont PROCEED;return error from here itself;
2189 ROLLBACK TO l_exp_work_populate;
2190 retcode := 2;
2191 fnd_message.set_name('WMS', 'WMS_POPULATE_EXP_WORK_ERROR');
2192 l_message := fnd_message.get;
2193 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
2194 RETURN;
2195
2196 END IF;
2197
2198 -- Added for the bug # 5169490
2199 -- Update the number of matched transactions in wms_els_individual_tasks_b
2200 -- i.e, Deduct the matched transaction count for the tasks/record that has been
2201 -- populated again in wms_els_exp_resource table.
2202
2203 IF g_debug=1 THEN
2204 debug('Before updating the data in wms_els_individual_tasks_b table','MATCH_RATE_EXP_RESOURCE');
2205 END IF;
2206
2207 UPDATE wms_els_individual_tasks_b weitb
2208 SET num_trx_matched = num_trx_matched - (SELECT count(weerg.els_data_id)
2209 FROM wms_els_exp_resource_gtemp weerg
2210 , wms_els_exp_resource weer
2211 WHERE weerg.source_header_id = weer.source_header_id
2212 AND weerg.source_line_id = weer.source_line_id
2213 AND weerg.activity_id = weer.activity_id
2214 AND weerg.activity_detail_id = weer.activity_detail_id
2215 AND weerg.operation_id = weer.operation_id
2216 AND weerg.els_data_id = weitb.els_data_id
2217 AND weer.organization_id = p_org_id)
2218 WHERE weitb.organization_id = p_org_id
2219 AND weitb.analysis_id IN (2, 4); -- Analysis_id should be Work outstanding (4) or both(2) */
2220
2221
2222 IF g_debug=1 THEN
2223 debug('After updating the data in wms_els_individual_tasks_b table','MATCH_RATE_EXP_RESOURCE');
2224 END IF;
2225
2226
2227 -- Now once all this is done Do the matching of the rows in WMS_ELS_EXP_RESOURCE table
2228 --with the setup rows
2229 --in WMS_ELS_INDIVIDUAL_TASKS_B using the where clause built dynamically (dynamic SQL),
2230 --starting with the setup row having the least sequence number. Once a match is found
2231 --stamp the Estimated_time column in WMS_ELS_EXP_RESOURCE table with the time required
2232 --to complete the transaction. Also stamp the Expecetd_Resource column based on the global
2233 --setup.
2234
2235
2236 OPEN c_els_data(p_org_id);
2237 LOOP
2238 FETCH c_els_data INTO l_els_data;
2239 EXIT WHEN c_els_data%NOTFOUND;
2240
2241 -- flush out v_sql and v_where_clause so that it does not hold any old values
2242
2243 BEGIN
2244
2245 l_where_clause := NULL;
2246
2247 l_sql:=NULL;
2248
2249 IF g_debug=1 THEN
2250 debug('Check if we have some more rows to process if no exit','MATCH_RATE_EXP_RESOURCE');
2251 END IF;
2252
2253 -- This fuction will return TRUE if more rows are left non matched after a certain pass of the
2254 -- setup data. It will return FALSE when no more rows are left to process. This fucntion will be
2255 -- used to exit the processing once all rows in wms_els_trx_src are exhaused even before
2256 -- all the rows in setup are exhausted.
2257
2258 IF WMS_LMS_UTILS.UNPROCESSED_ROWS_REMAINING (p_org_id) = 2 THEN
2259 IF g_debug=1 THEN
2260 debug('No More rows to process so exit','MATCH_RATE_EXP_RESOURCE');
2261 END IF;
2262 EXIT;
2263 END IF;
2264
2265 IF g_debug=1 THEN
2266 debug('Got some more rows to process so continue with the next setup row','MATCH_RATE_EXP_RESOURCE');
2267 END IF;
2268
2269 IF l_els_data.organization_id IS NOT NULL
2270 THEN
2271 l_where_clause := l_where_clause || ' AND organization_id = :organization_id ';
2272 END IF;
2273
2274 IF l_els_data.activity_id IS NOT NULL
2275 THEN
2276 l_where_clause := l_where_clause || ' AND activity_id = :activity_id ';
2277 END IF;
2278
2279 IF l_els_data.activity_detail_id IS NOT NULL
2280 THEN
2281 l_where_clause := l_where_clause || ' AND activity_detail_id = :activity_detail_id ';
2282 END IF;
2283
2284 IF l_els_data.operation_id IS NOT NULL
2285 THEN
2286 l_where_clause := l_where_clause || ' AND operation_id = :operation_id ';
2287 END IF;
2288
2289
2290 IF l_els_data.source_zone_id IS NOT NULL
2291 THEN
2292 -- here not only match the zone_id but also if the loactor lies in that zone.
2293 l_where_clause := l_where_clause || ' AND ((source_zone_id = :source_zone_id) '
2294 || ' OR ( '
2295 || 'source_locator_id'
2296 || ' IN (select inventory_location_id'
2297 || ' from WMS_ZONE_LOCATORS'
2298 || ' where zone_id= :source_zone_id AND organization_id = :org_id'
2299 || ' AND '
2300 ||' WMS_LMS_UTILS. ZONE_LABOR_MGMT_ENABLED(:org_id,:source_zone_id)=''Y'''
2301 || ')'
2302 || ')) ';
2303 END IF;
2304
2308 END IF;
2305 IF l_els_data.source_subinventory IS NOT NULL
2306 THEN
2307 l_where_clause := l_where_clause || ' AND source_subinventory = :source_subinventory ';
2309
2310 IF l_els_data.destination_zone_id IS NOT NULL
2311 THEN
2312 -- here not only match the zone_id but also if the loactor lies in that zone.
2313 l_where_clause := l_where_clause || ' AND ((destination_zone_id = :destination_zone_id)'
2314 || ' OR ( '
2315 || ' destination_locator_id '
2316 || ' IN (select inventory_location_id '
2317 || ' from WMS_ZONE_LOCATORS '
2318 || ' where zone_id= :destination_zone_id AND organization_id = :org_id'
2319 || ' AND '
2320 || ' WMS_LMS_UTILS. ZONE_LABOR_MGMT_ENABLED(:org_id,:destination_zone_id)=''Y'''
2321 || ')'
2322 || ')) ';
2323 END IF;
2324
2325 IF l_els_data.destination_subinventory IS NOT NULL
2326 THEN
2327 l_where_clause := l_where_clause ||' AND destination_subinventory = :destination_subinventory ';
2328 END IF;
2329
2330 --This condition check is not neede for Expected Resource Requirement Analysis
2331 -- as for expected work this attribute is not known.
2332
2333 /*IF l_els_data.labor_txn_source_id IS NOT NULL
2334 THEN
2335 l_where_clause := l_where_clause || ' AND labor_txn_source_id = :labor_txn_source_id ';
2336 END IF;
2337 */
2338
2339 IF l_els_data.transaction_uom IS NOT NULL
2340 THEN
2341 l_where_clause := l_where_clause || ' AND transaction_uom = :transaction_uom ';
2342 END IF;
2343
2344 IF l_els_data.from_quantity IS NOT NULL
2345 THEN
2346 l_where_clause := l_where_clause || ' AND quantity >= :from_quantity ';
2347 END IF;
2348
2349 IF l_els_data.to_quantity IS NOT NULL
2350 THEN
2351 l_where_clause := l_where_clause || ' AND quantity <= :to_quantity ';
2352 END IF;
2353
2354 IF l_els_data.item_category_id IS NOT NULL
2355 THEN
2356 -- here not only match the category_id but also if the item is assigned to that category.
2357 l_where_clause :=l_where_clause || ' AND (( item_category_id = :item_category_id)'
2358 || ' OR ('
2359 || ' inventory_item_id'
2360 || ' IN (select inventory_item_id'
2361 || ' from MTL_ITEM_CATEGORIES'
2362 || ' where category_id= :item_category_id AND organization_id =:org_id'
2363 || ')'
2364 || ')) ';
2365
2366 END IF;
2367
2368 IF l_els_data.operation_plan_id IS NOT NULL
2369 THEN
2370 l_where_clause := l_where_clause || ' AND operation_plan_id = :operation_plan_id ';
2371 END IF;
2372
2373 --This condition check is not neede for Expected Resource Requirement Analysis
2374 -- as for expected work this attribute is not known.
2375
2376 /*IF l_els_data.group_id IS NOT NULL
2377 THEN
2378 l_where_clause := l_where_clause || ' AND group_id = :group_id ';
2379 END IF;
2380 */
2381
2382 l_sql :='UPDATE wms_els_exp_resource SET '
2383 ||' els_data_id = :els_data_id'
2384 ||' ,source_zone_id = :source_zone'
2385 ||' ,destination_zone_id = :destination_zone'
2386 ||' ,item_category_id = :item_category'
2387 ||' , unattributed_flag = NULL'
2388 ||' ,estimated_time_required = (:expected_travel_time + :expected_txn_time + NVL(:expected_idle_time,0))'
2389 ||' ,estimated_resource_required =(:expected_travel_time + :expected_txn_time + NVL(:expected_idle_time,0))'
2390 || ' /(:time_per_day*60*60*:utilization_rate/100)'
2391 ||' where els_data_id IS NULL and organization_id = :org_id ';
2392
2393
2394 IF g_debug=1 THEN
2395 debug('The sql clause constructed','MATCH_RATE_EXP_RESOURCE');
2396 END IF;
2397
2398 --append the where clause
2399 l_sql := l_sql||l_where_clause;
2400
2401 IF g_debug=1 THEN
2402 debug('The sql clause finally is '|| l_sql,'MATCH_RATE_EXP_RESOURCE');
2403 END IF;
2404
2405 c:= dbms_sql.open_cursor;
2406
2407 IF g_debug=1 THEN
2408 debug('Opened the cursor for Binding ','MATCH_RATE_EXP_RESOURCE');
2409 END IF;
2410
2411 DBMS_SQL.parse(c, l_sql, DBMS_SQL.native);
2412
2413 IF g_debug=1 THEN
2414 debug('Starting Binding the variables ','MATCH_RATE_EXP_RESOURCE');
2415 END IF;
2416
2417 DBMS_SQL.bind_variable(c, 'els_data_id', l_els_data.els_data_id);
2418
2419 DBMS_SQL.bind_variable(c, 'source_zone', l_els_data.source_zone_id);
2420
2421 DBMS_SQL.bind_variable(c, 'destination_zone', l_els_data.destination_zone_id);
2422
2423 DBMS_SQL.bind_variable(c, 'item_category', l_els_data.item_category_id);
2424
2425 DBMS_SQL.bind_variable(c, 'expected_txn_time', l_els_data.expected_txn_time);
2426
2427 DBMS_SQL.bind_variable(c, 'expected_travel_time', l_els_data.expected_travel_time);
2428
2429 DBMS_SQL.bind_variable(c, 'expected_idle_time', l_els_data.expected_idle_time);
2430
2431 DBMS_SQL.bind_variable(c, 'time_per_day', p_Num_work_hrs_day);
2432
2433 DBMS_SQL.bind_variable(c, 'utilization_rate', p_Utilization_rate);
2434
2435 DBMS_SQL.bind_variable(c, 'org_id', p_org_id);
2436
2437 IF l_els_data.organization_id IS NOT NULL
2438 THEN
2439 DBMS_SQL.bind_variable(c, 'organization_id', l_els_data.organization_id);
2440 END IF;
2441
2442 IF l_els_data.activity_id IS NOT NULL
2443 THEN
2444 DBMS_SQL.bind_variable(c, 'activity_id', l_els_data.activity_id);
2445 END IF;
2446
2447 IF l_els_data.activity_detail_id IS NOT NULL
2448 THEN
2449 DBMS_SQL.bind_variable(c, 'activity_detail_id', l_els_data.activity_detail_id);
2450 END IF;
2451
2452 IF l_els_data.operation_id IS NOT NULL
2453 THEN
2454 DBMS_SQL.bind_variable(c, 'operation_id', l_els_data.operation_id);
2455 END IF;
2456
2457 IF l_els_data.source_zone_id IS NOT NULL
2458 THEN
2459 DBMS_SQL.bind_variable(c, 'source_zone_id', l_els_data.source_zone_id);
2460 END IF;
2461
2462 IF l_els_data.source_subinventory IS NOT NULL
2463 THEN
2464 DBMS_SQL.bind_variable(c, 'source_subinventory', l_els_data.source_subinventory);
2465 END IF;
2466
2467 IF l_els_data.destination_zone_id IS NOT NULL
2468 THEN
2469 DBMS_SQL.bind_variable(c, 'destination_zone_id', l_els_data.destination_zone_id);
2470 END IF;
2471
2472 IF l_els_data.destination_subinventory IS NOT NULL
2473 THEN
2474 DBMS_SQL.bind_variable(c, 'destination_subinventory', l_els_data.destination_subinventory);
2475 END IF;
2476 /*
2477 IF l_els_data.labor_txn_source_id IS NOT NULL
2478 THEN
2479 DBMS_SQL.bind_variable(c, 'labor_txn_source_id', l_els_data.labor_txn_source_id);
2480 END IF;
2481 */
2482
2483 IF l_els_data.transaction_uom IS NOT NULL
2484 THEN
2485 DBMS_SQL.bind_variable(c, 'transaction_uom', l_els_data.transaction_uom);
2486 END IF;
2487
2488 IF l_els_data.from_quantity IS NOT NULL
2489 THEN
2490 DBMS_SQL.bind_variable(c, 'from_quantity', l_els_data.from_quantity);
2491 END IF;
2492
2493 IF l_els_data.to_quantity IS NOT NULL
2494 THEN
2495 DBMS_SQL.bind_variable(c, 'to_quantity', l_els_data.to_quantity);
2496 END IF;
2497
2498 IF l_els_data.item_category_id IS NOT NULL
2499 THEN
2500 DBMS_SQL.bind_variable(c, 'item_category_id', l_els_data.item_category_id);
2501 END IF;
2502
2503 IF l_els_data.operation_plan_id IS NOT NULL
2504 THEN
2505 DBMS_SQL.bind_variable(c, 'operation_plan_id', l_els_data.operation_plan_id);
2506 END IF;
2507
2508 /*
2509 IF l_els_data.group_id IS NOT NULL
2510 THEN
2511 DBMS_SQL.bind_variable(c, 'group_id', l_els_data.group_id);
2512 END IF;
2513 */
2514
2515 IF g_debug=1 THEN
2516 debug('All variables bound '|| l_sql,'MATCH_RATE_EXP_RESOURCE');
2517 END IF;
2518
2519 l_update_count := DBMS_SQL.EXECUTE(c);
2520
2521 IF g_debug=1 THEN
2522 debug('SQL executed Number of rows updated '|| l_update_count,'MATCH_RATE_EXP_RESOURCE');
2523 END IF;
2524
2525
2526 --get the row count
2527
2528 l_total := l_update_count + NVL(l_els_data.num_trx_matched,0);
2529
2530 DBMS_SQL.close_cursor(c);
2531
2532 --update the count with newly matched transactions
2533
2534 UPDATE wms_els_individual_tasks_b
2535 SET
2536 num_trx_matched = l_total
2537 WHERE els_data_id = l_els_data.els_data_id;
2538
2539
2540 EXCEPTION
2541 WHEN OTHERS THEN
2542 l_num_execution_failed := l_num_execution_failed +1;
2543 IF g_debug=1 THEN
2544 debug('Execution failed for the els_data_id '|| l_els_data.els_data_id,'MATCH_RATE_EXP_RESOURCE');
2545 debug('Exception occured '|| sqlerrm,'MATCH_RATE_EXP_RESOURCE');
2546 END IF;
2547
2548 END;
2549
2550 END LOOP; -- all els_rows exhausted
2551
2552 -- now update all txns having els_data_id as NULL as non_attributed
2553 l_update_count := NULL;
2554
2555
2556 UPDATE wms_els_exp_resource SET unattributed_flag = 1
2557 WHERE els_data_id IS NULL AND organization_id = p_org_id;
2558
2559 l_update_count := SQL%ROWCOUNT;
2560
2561 IF g_debug=1 THEN
2562 debug('Number of rows updated as non-standardized '|| l_update_count,'MATCH_RATE_EXP_RESOURCE');
2563 debug('Value of l_num_execution_failed '|| l_num_execution_failed,'MATCH_RATE_EXP_RESOURCE');
2564 debug('Value of l_populate_status '|| l_populate_status,'MATCH_RATE_EXP_RESOURCE');
2565 END IF;
2566
2567 IF ( l_num_execution_failed = 0 AND l_populate_status = 0 )THEN
2568
2569 retcode := 1;
2570 fnd_message.set_name('WMS', 'WMS_LMS_EXP_RES_SUCCESS');
2571 l_message := fnd_message.get;
2572 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',l_message);
2573 COMMIT;
2574
2575 ELSIF ( l_num_execution_failed > 0 OR l_populate_status =1 ) THEN
2576
2577 retcode := 3;
2578 fnd_message.set_name('WMS', 'WMS_LMS_EXP_RES_WARN');
2579 l_message := fnd_message.get;
2580 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
2581 COMMIT;
2582 END IF;
2583
2584 ELSE -- org is not labor enabled
2585
2586 retcode := 3;
2587 fnd_message.set_name('WMS', 'WMS_ORG_NOT_LMS_ENABLED');
2588 l_message := fnd_message.get;
2589 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
2590
2591 END IF; -- If org is labor enabled
2592
2593 CLOSE C_ELS_DATA;
2594
2595 EXCEPTION
2596
2597 -- handle exception
2598 WHEN OTHERS THEN
2599 IF g_debug=1 THEN
2600 debug('Exception occured '|| sqlerrm,'MATCH_RATE_EXP_RESOURCE');
2601 END IF;
2602
2603 IF C_ELS_DATA%ISOPEN THEN
2604 CLOSE C_ELS_DATA;
2605 END IF;
2606
2607 retcode := 2;
2608 fnd_message.set_name('WMS', 'WMS_LMS_EXP_RES_ERROR');
2609 l_message := fnd_message.get;
2610 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
2611
2612
2613 END MATCH_RATE_EXP_RESOURCE;
2614
2615 END WMS_LMS_EXPECTED_RES;
2616
[Home] [Help]