[Home] [Help]
PACKAGE BODY: APPS.WMS_TASK_DISPATCH_LOV
Source
1 PACKAGE BODY WMS_Task_Dispatch_LOV AS
2 /* $Header: WMSTSKLB.pls 120.19.12020000.4 2013/01/24 10:53:02 pramadur ship $ */
3
4
5 -- Global constant holding the package name
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_Task_Dispatch_LOV';
8 g_gtin_cross_ref_type VARCHAR2(25) := fnd_profile.value('INV:GTIN_CROSS_REFERENCE_TYPE');
9 g_gtin_code_length NUMBER := 14;
10
11
12 PROCEDURE mydebug(msg in varchar2)
13 IS
14 l_msg VARCHAR2(5100);
15 l_ts VARCHAR2(30);
16 BEGIN
17 -- select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
18 -- l_msg:=l_ts||' '||msg;
19
20 l_msg := msg;
21
22 inv_mobile_helper_functions.tracelog
23 (p_err_msg => l_msg,
24 p_module => 'WMS_Task_Dispatch_LOV',
25 p_level => 4);
26
27 --dbms_output.put_line(l_msg);
28
29 null;
30 END;
31
32
33
34 PROCEDURE get_tasks_lov
35 ( x_tasks OUT NOCOPY t_genref
36 , p_Organization_Id IN NUMBER
37 , p_User_Id IN NUMBER
38 , p_concat_segments IN VARCHAR2
39 , p_page_type IN VARCHAR2
40 , p_unload_receiving IN VARCHAR2 DEFAULT NULL
41
42 )
43 IS
44 l_wms_po_j_or_higher NUMBER := 0;
45 BEGIN
46 IF ((WMS_UI_TASKS_APIS.g_wms_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j) AND
47 (WMS_UI_TASKS_APIS.g_po_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j_po)) THEN
48 l_wms_po_j_or_higher := 1;
49 END IF;
50
51 --Unload ER
52 inv_trx_util_pub.trace( 'WMS_Task_Dispatch_LOV - GET_TASk_LOV Unload ER Changes - p_page_type type' || p_page_type || 'p_unload Receiving -' || p_unload_receiving , '~~~~ ', 1);
53
54
55
56 IF p_page_type = 'SYSTEM_DROP'
57 THEN
58 OPEN x_tasks FOR
59 -- Putaway J or higher
60 SELECT ' ' status
61 , wlpn1.license_plate_number lpn
62 , 'PUTAWAY' task_type
63 , 'NORMAL' task_mo_status
64 , '' to_sub
65 , '' to_loc
66 , '' item
67 , To_number(NULL) transaction_quantity
68 , '' transaction_uom
69 , wlpn1.lpn_id
70 , To_number(NULL) taskid
71 , wlpn1.lpn_context
72 , 'FALSE' is_bulk_pick
73 , 1 dummy_sort
74 FROM wms_license_plate_numbers wlpn1
75 WHERE wlpn1.license_plate_number LIKE p_concat_segments
76 START WITH
77 wlpn1.lpn_id IN (SELECT DISTINCT wlpn2.outermost_lpn_id
78 FROM mtl_material_transactions_temp mmtt,
79 wms_dispatched_tasks wdt,
80 wms_license_plate_numbers wlpn2
81 WHERE l_wms_po_j_or_higher = 1
82 AND mmtt.organization_id = p_organization_id
83 AND mmtt.transaction_temp_id = wdt.transaction_temp_id
84 AND wdt.organization_id = p_organization_id
85 AND wdt.task_type = 2
86 AND wdt.status = 4
87 AND wdt.person_id = p_user_id
88 AND wlpn2.lpn_id = mmtt.lpn_id)
89 CONNECT BY PRIOR wlpn1.lpn_id = wlpn1.parent_lpn_id
90 UNION ALL
91 -- Picking, replenishment and move order transfers
92 SELECT /*+ ORDERED */
93 DECODE( m.parent_line_id
94 , NULL, DECODE( mtrl.line_status
95 , 6,'*'
96 , 9, '*'
97 , ' '
98 )
99 , ' '
100 ) status
101 , l.license_plate_number lpn
102 , DECODE( m.parent_line_id
103 , NULL, DECODE( m.transaction_type_ID
104 , 35,'WIP_PICKING'
105 , DECODE( w.task_type
106 , 1,'PICKING'
107 , 5,'MOXFER'
108 , 'REPLENISHMENT'
109 )
110 )
111 , 'PICKING'
112 ) task_type
113 , DECODE( m.parent_line_id
114 , NULL, DECODE( mtrl.line_status
115 , 6,'CANCELLED'
116 , 9,'CANCELLED'
117 , 'NORMAL'
118 )
119 , 'NORMAL'
120 ) task_mo_status
121 , DECODE( m.parent_line_id
122 , NULL, m.transfer_subinventory
123 , NULL
124 ) to_sub
125 , DECODE( m.parent_line_id
126 , NULL, get_locator( m.wms_task_type
127 , m.locator_id
128 , m.transfer_to_location
129 , m.transaction_type_id
130 , m.organization_id
131 )
132 , NULL
133 ) to_loc
134 , i.concatenated_segments item
135 , m.transaction_quantity
136 , m.transaction_uom
137 , m.transfer_lpn_id lpn_id
138 , m.transaction_temp_id taskid
139 , l.lpn_context
140 , DECODE( m.parent_line_id
141 , NULL, 'FALSE'
142 , 'TRUE'
143 ) is_bulk_pick
144 , 2 dummy_sort
145 FROM wms_dispatched_tasks w
146 , mtl_material_transactions_temp m
147 , mtl_system_items_kfv i
148 , wms_license_plate_numbers l
149 , mtl_txn_request_lines mtrl
150 WHERE w.person_id = p_user_id
151 AND w.organization_id = p_organization_id
152 AND w.status = 4
153 AND w.transaction_temp_id = m.transaction_temp_id
154 AND m.organization_id = i.organization_id
155 AND m.inventory_item_id = i.inventory_item_id
156 AND m.organization_id = l.organization_id
157 AND w.task_type IN (1,4,5)
158 AND m.transfer_lpn_id = l.lpn_id
159 AND m.move_order_line_id = mtrl.line_id (+)
160 --Bug 6891745
161 AND decode(l.lpn_context, 8 , l.lpn_id, l.outermost_lpn_id) = l.outermost_lpn_id
162 AND (m.parent_line_id IS NULL
163 OR
164 (m.parent_line_id IS NOT NULL
165 AND
166 m.parent_line_id = m.transaction_temp_id
167 )
168 )
169 AND l.license_plate_number LIKE (p_concat_segments)
170 /* MRANA - MDC Staging drop should not be supported using Current task Page*/
171 /* UNION ALL
172 -- Staging moves
173 SELECT ' ' status
174 , l.license_plate_number lpn
175 , 'STAGING MOVE' task_type
176 , 'NORMAL' task_mo_status
177 , m.transfer_subinventory to_sub
178 , get_locator( m.wms_task_type
179 , m.locator_id
180 , m.transfer_to_location
181 , m.transaction_type_id
182 , m.organization_id
183 ) to_loc
184 , i.concatenated_segments item
185 , to_number(null) transaction_quantity
186 , to_char(null) transaction_uom
187 , m.transfer_lpn_id lpn_id
188 , m.transaction_temp_id taskid
189 , l.lpn_context
190 , 'FALSE' is_bulk_pick
191 , 3 dummy_sort
192 FROM wms_dispatched_tasks w
193 , mtl_material_transactions_temp m
194 , mtl_system_items_kfv i
195 , wms_license_plate_numbers l
196 WHERE w.person_id = p_user_id
197 AND w.organization_id = p_organization_id
198 AND w.status = 4
199 AND w.transaction_temp_id = m.transaction_temp_id
200 AND m.organization_id = i.organization_id
201 AND m.inventory_item_id = i.inventory_item_id
202 AND m.organization_id = l.organization_id
203 AND w.task_type = 7
204 AND m.transfer_lpn_id = l.lpn_id
205 AND l.license_plate_number LIKE (p_concat_segments)
206 ORDER BY task_type
207 , to_sub
208 , to_loc
209 , lpn; MRANA - MDC */
210 -- dummy_sort not used for ordering SYSTEM_DROP tasks
211 --Added UNION for bug 6682436
212 UNION ALL
213
214 SELECT /*+ ORDERED */
215 DECODE( m.parent_line_id
216 , NULL, DECODE( mtrl.line_status
217 , 6,'*'
218 , 9, '*'
219 , ' '
220 )
221 , ' '
222 ) status
223 --, l.license_plate_number lpn
224 , DECODE(wlpn2.lpn_context ,8 , wlpn2.license_plate_number , l.license_plate_number) lpn --12984304
225 , DECODE( m.parent_line_id
226 , NULL, DECODE( m.transaction_type_ID
227 , 35,'WIP_PICKING'
228 , DECODE( w.task_type
229 , 1,'PICKING'
230 , 5,'MOXFER'
231 , 'REPLENISHMENT'
232 )
233 )
234 , 'PICKING'
235 ) task_type
236 , DECODE( m.parent_line_id
237 , NULL, DECODE( mtrl.line_status
238 , 6,'CANCELLED'
239 , 9,'CANCELLED'
240 , 'NORMAL'
241 )
242 , 'NORMAL'
243 ) task_mo_status
244 , DECODE( m.parent_line_id
245 , NULL, m.transfer_subinventory
246 , NULL
247 ) to_sub
248 , DECODE( m.parent_line_id
249 , NULL, get_locator( m.wms_task_type
250 , m.locator_id
251 , m.transfer_to_location
252 , m.transaction_type_id
253 , m.organization_id
254 )
255 , NULL
256 ) to_loc
257 , i.concatenated_segments item
258 , m.transaction_quantity
259 , m.transaction_uom
260 , l.lpn_id lpn_id /*modified for bug 6717052*/
261 , m.transaction_temp_id taskid
262 , l.lpn_context
263 , DECODE( m.parent_line_id
264 , NULL, 'FALSE'
265 , 'TRUE'
266 ) is_bulk_pick
267 , 2 dummy_sort
268 FROM wms_dispatched_tasks w
269 , mtl_material_transactions_temp m
270 , mtl_system_items_kfv i
271 , wms_license_plate_numbers l
272 , mtl_txn_request_lines mtrl
273 , wms_license_plate_numbers wlpn2
274 WHERE w.person_id = p_user_id
275 AND w.organization_id = p_organization_id
276 AND w.status = 4
277 AND w.transaction_temp_id = m.transaction_temp_id
278 AND m.organization_id = i.organization_id
279 AND m.inventory_item_id = i.inventory_item_id
280 AND m.organization_id = l.organization_id
281 AND w.task_type IN (1,4,5)
282 AND l.outermost_lpn_id = wlpn2.lpn_id --12984304
283 AND l.lpn_id = m.transfer_lpn_id --12984304
284 AND l.lpn_id <> l.outermost_lpn_id --12984304
285 --AND l.lpn_id IN (SELECT DISTINCT(wlpn1.outermost_lpn_id) FROM wms_license_plate_numbers wlpn1 WHERE wlpn1.lpn_id = m.transfer_lpn_id AND wlpn1.lpn_id <> wlpn1.outermost_lpn_id)
286 AND m.move_order_line_id = mtrl.line_id (+)
287 AND (m.parent_line_id IS NULL
288 OR
289 (m.parent_line_id IS NOT NULL
290 AND
291 m.parent_line_id = m.transaction_temp_id
292 )
293 )
294 AND l.license_plate_number LIKE (p_concat_segments);
295
296 ElSIF (p_unload_receiving IS NULL OR p_unload_receiving = 'NO') THEN --Changes for Unload ER:13773615 :Incase Inbound Unload Form function parameter not set.
297
298 OPEN x_tasks FOR
299 -- Putaway J or higher
300 SELECT ' ' status
301 , wlpn1.license_plate_number lpn
302 , 'PUTAWAY' task_type
303 , 'NORMAL' task_mo_status
304 , '' to_sub
305 , '' to_loc
306 , '' item
307 , To_number(NULL) transaction_quantity
308 , '' transaction_uom
309 , wlpn1.lpn_id
310 , To_number(NULL) taskid
311 , wlpn1.lpn_context
312 , 'FALSE' is_bulk_pick
313 , 1 dummy_sort
314 FROM wms_license_plate_numbers wlpn1
315 WHERE wlpn1.license_plate_number LIKE p_concat_segments
316 START WITH
317 wlpn1.lpn_id IN (SELECT DISTINCT wlpn2.outermost_lpn_id
318 FROM mtl_material_transactions_temp mmtt,
319 wms_dispatched_tasks wdt,
320 wms_license_plate_numbers wlpn2
321 WHERE l_wms_po_j_or_higher = 1
322 AND (p_page_type IS NULL OR p_page_type <> 'MANUAL_UNLOAD')
323 AND mmtt.organization_id = p_organization_id
324 AND mmtt.transaction_temp_id = wdt.transaction_temp_id
325 AND wdt.organization_id = p_organization_id
326 AND wdt.task_type = 2
327 AND wdt.status = 4
328 AND wdt.person_id = p_user_id
329 AND wlpn2.lpn_id = mmtt.lpn_id)
330 CONNECT BY PRIOR wlpn1.lpn_id = wlpn1.parent_lpn_id
331 UNION ALL
332 -- Putaway for I or lower
333 SELECT /*+ ORDERED */
334 ' ' status
335 , l.license_plate_number lpn
336 , 'PUTAWAY' task_type
337 , 'NORMAL' task_mo_status
338 , m.subinventory_code to_sub
339 , Decode(m.transfer_to_location,
340 NULL,
341 Decode(m.locator_id,
342 NULL,
343 -- In the case of load, the transfer_to_location
344 -- AND locator_id will be null. So no need to
345 -- call get_locator.
346 Decode(l_wms_po_j_or_higher,
347 1,
348 NULL,
349 get_locator( m.wms_task_type
350 , m.locator_id
351 , m.transfer_to_location
352 , m.transaction_type_id
353 , m.organization_id
354 )
355 ),
356 get_locator( m.wms_task_type
357 , m.locator_id
358 , m.transfer_to_location
359 , m.transaction_type_id
360 , m.organization_id
361 )
362 ),
363 -- The transfer_to_location column is usually populated in
364 -- MMTT FOR putaway
365 get_locator( m.wms_task_type
366 , m.transfer_to_location
367 , m.transfer_to_location
368 , m.transaction_type_id
369 , m.organization_id
370 )
371 ) to_loc
372 , i.concatenated_segments item
373 , m.transaction_quantity
374 , m.transaction_uom
375 , m.lpn_id
376 , m.transaction_temp_id taskid
377 , l.lpn_context
378 , 'FALSE' is_bulk_pick
379 , 1 dummy_sort
380 FROM wms_dispatched_tasks w
381 , mtl_material_transactions_temp m
382 , mtl_system_items_kfv i
383 , wms_license_plate_numbers l
384 , mtl_txn_request_lines mtrl
385 WHERE l_wms_po_j_or_higher <> 1
386 AND w.person_id = p_user_id
387 AND w.organization_id = p_organization_id
388 AND w.status = 4
389 AND w.transaction_temp_id = m.transaction_temp_id
390 AND m.organization_id = i.organization_id
391 AND m.inventory_item_id = i.inventory_item_id
392 AND m.organization_id = l.organization_id
393 AND w.task_type = 2
394 AND m.lpn_id = l.lpn_id
395 AND m.move_order_line_id = mtrl.line_id
396 AND l.license_plate_number LIKE (p_concat_segments)
397 UNION ALL
398 -- Material packed into content LPNs
399 SELECT /*+ ORDERED */
400 DECODE( m.parent_line_id
401 , NULL, DECODE( mtrl.line_status
402 , 6,'*'
403 , 9, '*'
404 , ' '
405 )
406 , ' '
407 ) status
408 , l.license_plate_number lpn
409 , DECODE( m.parent_line_id
410 , NULL, DECODE( m.transaction_type_ID
411 , 35,'WIP_PICKING'
412 , DECODE( w.task_type
413 , 1,'PICKING'
414 , 5,'MOXFER'
415 , 6,'MOISSUE'--13807883
416 , 'REPLENISHMENT'
417 )
418 )
419 , 'PICKING'
420 ) task_type
421 , DECODE( m.parent_line_id
422 , NULL, DECODE( mtrl.line_status
423 , 6,'CANCELLED'
424 , 9,'CANCELLED'
425 , 'NORMAL'
426 )
427 , 'NORMAL'
428 ) task_mo_status
429 , DECODE( m.parent_line_id
430 , NULL, m.transfer_subinventory
431 , NULL
432 ) to_sub
433 , DECODE( m.parent_line_id
434 , NULL, get_locator( m.wms_task_type
435 , m.locator_id
436 , m.transfer_to_location
437 , m.transaction_type_id
438 , m.organization_id
439 )
440 , NULL
441 ) to_loc
442 , i.concatenated_segments item
443 , m.transaction_quantity
444 , m.transaction_uom
445 , m.transfer_lpn_id lpn_id
446 , m.transaction_temp_id taskid
447 , l.lpn_context
448 , DECODE( m.parent_line_id
449 , NULL, 'FALSE'
450 , 'TRUE'
451 ) is_bulk_pick
452 , 2 dummy_sort
453 FROM wms_dispatched_tasks w
454 , mtl_material_transactions_temp m
455 , mtl_system_items_kfv i
456 , wms_license_plate_numbers l
457 , mtl_txn_request_lines mtrl
458 WHERE w.person_id = p_user_id
459 AND w.organization_id = p_organization_id
460 AND w.status = 4
461 AND w.transaction_temp_id = m.transaction_temp_id
462 AND m.organization_id = i.organization_id
463 AND m.inventory_item_id = i.inventory_item_id
464 AND m.organization_id = l.organization_id
465 AND w.task_type IN (1,4,5,DECODE(p_page_type , 'MANUAL_UNLOAD' , 6))--13807883
466 AND m.transfer_lpn_id = l.lpn_id
467 AND m.move_order_line_id = mtrl.line_id (+)
468 --Bug 6891745
469 AND decode(l.lpn_context, 8 , l.lpn_id, l.outermost_lpn_id) = l.outermost_lpn_id
470 AND (m.parent_line_id IS NULL
471 OR
472 (m.parent_line_id IS NOT NULL
473 AND
474 m.parent_line_id = m.transaction_temp_id
475 )
476 )
477 AND EXISTS
478 ( SELECT 'x'
479 FROM mtl_material_transactions_temp m2
480 WHERE m2.transfer_lpn_id = m.transfer_lpn_id
481 AND m2.organization_id = m.organization_id
482 AND m2.content_lpn_id = m.transfer_lpn_id
483 AND m2.transaction_temp_id <> m.transaction_temp_id
484 AND DECODE( m2.parent_line_id
485 , NULL, 0
486 , m2.transaction_temp_id, 1
487 , 2
488 ) = DECODE( m.parent_line_id
489 , NULL, 0
490 , 1
491 )
492 )
493 AND l.license_plate_number LIKE (p_concat_segments)
494 UNION ALL
495 -- Content LPNs
496 SELECT /*+ ORDERED */
497 DECODE( m.parent_line_id
498 , NULL, DECODE( mtrl.line_status
499 , 6,'*'
500 , 9, '*'
501 , ' '
502 )
503 , ' '
504 ) status
505 , l.license_plate_number lpn
506 , DECODE( m.parent_line_id
507 , NULL, DECODE( m.transaction_type_ID
508 , 35,'WIP_PICKING'
509 , DECODE( w.task_type
510 , 1,'PICKING'
511 , 5,'MOXFER'
512 , 6,'MOISSUE'--13807883
513 , 'REPLENISHMENT'
514 )
515 )
516 , 'PICKING'
517 ) task_type
518 , DECODE( m.parent_line_id
519 , NULL, DECODE( mtrl.line_status
520 , 6,'CANCELLED'
521 , 9,'CANCELLED'
522 , 'NORMAL'
523 )
524 , 'NORMAL'
525 ) task_mo_status
526 , DECODE( m.parent_line_id
527 , NULL, m.transfer_subinventory
528 , NULL
529 ) to_sub
530 , DECODE( m.parent_line_id
531 , NULL, get_locator( m.wms_task_type
532 , m.locator_id
533 , m.transfer_to_location
534 , m.transaction_type_id
535 , m.organization_id
536 )
537 , NULL
538 ) to_loc
539 , i.concatenated_segments item
540 , m.transaction_quantity
541 , m.transaction_uom
542 , m.transfer_lpn_id lpn_id
543 , m.transaction_temp_id taskid
544 , l.lpn_context
545 , DECODE( m.parent_line_id
546 , NULL, 'FALSE'
547 , 'TRUE'
548 ) is_bulk_pick
549 , 3 dummy_sort
550 FROM wms_dispatched_tasks w
551 , mtl_material_transactions_temp m
552 , mtl_system_items_kfv i
553 , wms_license_plate_numbers l
554 , mtl_txn_request_lines mtrl
555 WHERE w.person_id = p_user_id
556 AND w.organization_id = p_organization_id
557 AND w.status = 4
558 AND w.transaction_temp_id = m.transaction_temp_id
559 AND m.organization_id = i.organization_id
560 AND m.inventory_item_id = i.inventory_item_id
561 AND m.organization_id = l.organization_id
562 AND w.task_type IN (1,4,5,DECODE(p_page_type , 'MANUAL_UNLOAD' , 6))--13807883
563 AND m.transfer_lpn_id = l.lpn_id
564 AND m.move_order_line_id = mtrl.line_id (+)
565 --Bug 6891745
566 AND decode(l.lpn_context, 8 , l.lpn_id, l.outermost_lpn_id) = l.outermost_lpn_id
567 AND (m.parent_line_id IS NULL
568 OR
569 (m.parent_line_id IS NOT NULL
570 AND
571 m.parent_line_id = m.transaction_temp_id
572 )
573 )
574 AND m.content_lpn_id IS NOT NULL
575 AND l.license_plate_number LIKE (p_concat_segments)
576 UNION ALL
577 -- Material unpacked from content LPNs
578 SELECT /*+ ORDERED */
579 DECODE( m.parent_line_id
580 , NULL, DECODE( mtrl.line_status
581 , 6,'*'
582 , 9, '*'
583 , ' '
584 )
585 , ' '
586 ) status
587 , l.license_plate_number lpn
588 , DECODE( m.parent_line_id
589 , NULL, DECODE( m.transaction_type_ID
590 , 35,'WIP_PICKING'
591 , DECODE( w.task_type
592 , 1,'PICKING'
593 , 5,'MOXFER'
594 , 6,'MOISSUE'--13807883
595 , 'REPLENISHMENT'
596 )
597 )
598 , 'PICKING'
599 ) task_type
600 , DECODE( m.parent_line_id
601 , NULL, DECODE( mtrl.line_status
602 , 6,'CANCELLED'
603 , 9,'CANCELLED'
604 , 'NORMAL'
605 )
606 , 'NORMAL'
607 ) task_mo_status
608 , DECODE( m.parent_line_id
609 , NULL, m.transfer_subinventory
610 , NULL
611 ) to_sub
612 , DECODE( m.parent_line_id
613 , NULL, get_locator( m.wms_task_type
614 , m.locator_id
615 , m.transfer_to_location
616 , m.transaction_type_id
617 , m.organization_id
618 )
619 , NULL
620 ) to_loc
621 , i.concatenated_segments item
622 , m.transaction_quantity
623 , m.transaction_uom
624 , m.transfer_lpn_id lpn_id
625 , m.transaction_temp_id taskid
626 , l.lpn_context
627 , DECODE( m.parent_line_id
628 , NULL, 'FALSE'
629 , 'TRUE'
630 ) is_bulk_pick
631 , 4 dummy_sort
632 FROM wms_dispatched_tasks w
633 , mtl_material_transactions_temp m
634 , mtl_system_items_kfv i
635 , wms_license_plate_numbers l
636 , mtl_txn_request_lines mtrl
637 WHERE w.person_id = p_user_id
638 AND w.organization_id = p_organization_id
639 AND w.status = 4
640 AND w.transaction_temp_id = m.transaction_temp_id
641 AND m.organization_id = i.organization_id
642 AND m.inventory_item_id = i.inventory_item_id
643 AND m.organization_id = l.organization_id
644 AND w.task_type IN (1,4,5,DECODE(p_page_type , 'MANUAL_UNLOAD' , 6))--13807883
645 AND m.transfer_lpn_id = l.lpn_id
646 AND m.move_order_line_id = mtrl.line_id (+)
647 --Bug 6891745
648 AND decode(l.lpn_context, 8 , l.lpn_id, l.outermost_lpn_id) = l.outermost_lpn_id
649 AND (m.parent_line_id IS NULL
650 OR
651 (m.parent_line_id IS NOT NULL
652 AND
653 m.parent_line_id = m.transaction_temp_id
654 )
655 )
656 AND m.lpn_id IS NOT NULL
657 AND EXISTS
658 ( SELECT 'x'
659 FROM mtl_material_transactions_temp m2
660 WHERE m2.transfer_lpn_id = m.transfer_lpn_id
661 AND m2.organization_id = m.organization_id
662 AND m2.content_lpn_id = m.lpn_id
663 AND m2.transaction_temp_id <> m.transaction_temp_id
664 AND DECODE( m2.parent_line_id
665 , NULL, 0
666 , m2.transaction_temp_id, 1
667 , 2
668 ) = DECODE( m.parent_line_id
669 , NULL, 0
670 , 1
671 )
672 )
673 AND l.license_plate_number LIKE (p_concat_segments)
674 UNION ALL
675 -- All other picked material
676 SELECT /*+ ORDERED */
677 DECODE( m.parent_line_id
678 , NULL, DECODE( mtrl.line_status
679 , 6,'*'
680 , 9, '*'
681 , ' '
682 )
683 , ' '
684 ) status
685 , l.license_plate_number lpn
686 , DECODE( m.parent_line_id
687 , NULL, DECODE( m.transaction_type_ID
688 , 35,'WIP_PICKING'
689 , DECODE( w.task_type
690 , 1,'PICKING'
691 , 5,'MOXFER'
692 , 6,'MOISSUE'--13807883
693 , 'REPLENISHMENT'
694 )
695 )
696 , 'PICKING'
697 ) task_type
698 , DECODE( m.parent_line_id
699 , NULL, DECODE( mtrl.line_status
700 , 6,'CANCELLED'
701 , 9,'CANCELLED'
702 , 'NORMAL'
703 )
704 , 'NORMAL'
705 ) task_mo_status
706 , DECODE( m.parent_line_id
707 , NULL, m.transfer_subinventory
708 , NULL
709 ) to_sub
710 , DECODE( m.parent_line_id
711 , NULL, get_locator( m.wms_task_type
712 , m.locator_id
713 , m.transfer_to_location
714 , m.transaction_type_id
718 ) to_loc
715 , m.organization_id
716 )
717 , NULL
719 , i.concatenated_segments item
720 , m.transaction_quantity
721 , m.transaction_uom
722 , m.transfer_lpn_id lpn_id
723 , m.transaction_temp_id taskid
724 , l.lpn_context
725 , DECODE( m.parent_line_id
726 , NULL, 'FALSE'
727 , 'TRUE'
728 ) is_bulk_pick
729 , 5 dummy_sort
730 FROM wms_dispatched_tasks w
731 , mtl_material_transactions_temp m
732 , mtl_system_items_kfv i
733 , wms_license_plate_numbers l
734 , mtl_txn_request_lines mtrl
735 WHERE w.person_id = p_user_id
736 AND w.organization_id = p_organization_id
737 AND w.status = 4
738 AND w.transaction_temp_id = m.transaction_temp_id
739 AND m.organization_id = i.organization_id
740 AND m.inventory_item_id = i.inventory_item_id
741 AND m.organization_id = l.organization_id
742 AND w.task_type IN (1,4,5,DECODE(p_page_type , 'MANUAL_UNLOAD' , 6))--13807883
743 AND m.transfer_lpn_id = l.lpn_id
744 AND m.move_order_line_id = mtrl.line_id (+)
745 AND (m.parent_line_id IS NULL
746 OR
747 (m.parent_line_id IS NOT NULL
748 AND
749 m.parent_line_id = m.transaction_temp_id
750 )
751 )
752 AND m.content_lpn_id IS NULL
753 AND ( (m.lpn_id IS NOT NULL
754 AND NOT EXISTS
755 ( SELECT 'x'
756 FROM mtl_material_transactions_temp m2
757 WHERE m2.transfer_lpn_id = m.transfer_lpn_id
758 AND m2.organization_id = m.organization_id
759 AND m2.content_lpn_id = m.lpn_id
760 AND m2.transaction_temp_id <> m.transaction_temp_id
761 AND DECODE( m2.parent_line_id
762 , NULL, 0
763 , m2.transaction_temp_id, 1
764 , 2
765 ) = DECODE( m.parent_line_id
766 , NULL, 0
767 , 1
768 )
769 )
770 )
771 OR m.lpn_id IS NULL
772 )
773 AND NOT EXISTS
774 ( SELECT 'x'
775 FROM mtl_material_transactions_temp m3
776 WHERE m3.transfer_lpn_id = m.transfer_lpn_id
777 AND m3.organization_id = m.organization_id
778 AND m3.content_lpn_id = m.transfer_lpn_id
779 AND m3.transaction_temp_id <> m.transaction_temp_id
780 AND DECODE( m3.parent_line_id
781 , NULL, 0
782 , m3.transaction_temp_id, 1
783 , 2
784 ) = DECODE( m.parent_line_id
785 , NULL, 0
786 , 1
787 )
788 )
789 AND l.license_plate_number LIKE (p_concat_segments)
790 --Added UNION for bug 6682436
791 UNION ALL
792
793 SELECT /*+ ORDERED */
794 DECODE( m.parent_line_id
795 , NULL, DECODE( mtrl.line_status
796 , 6,'*'
797 , 9, '*'
798 , ' '
799 )
800 , ' '
801 ) status
802 --, l.license_plate_number lpn
803 , DECODE(wlpn2.lpn_context ,8 , wlpn2.license_plate_number , l.license_plate_number) lpn --12984304
804 , DECODE( m.parent_line_id
805 , NULL, DECODE( m.transaction_type_ID
806 , 35,'WIP_PICKING'
807 , DECODE( w.task_type
808 , 1,'PICKING'
809 , 5,'MOXFER'
810 , 6,'MOISSUE'--13807883
811 , 'REPLENISHMENT'
812 )
813 )
814 , 'PICKING'
815 ) task_type
816 , DECODE( m.parent_line_id
817 , NULL, DECODE( mtrl.line_status
818 , 6,'CANCELLED'
819 , 9,'CANCELLED'
820 , 'NORMAL'
821 )
822 , 'NORMAL'
823 ) task_mo_status
824 , DECODE( m.parent_line_id
825 , NULL, m.transfer_subinventory
826 , NULL
827 ) to_sub
828 , DECODE( m.parent_line_id
829 , NULL, get_locator( m.wms_task_type
830 , m.locator_id
831 , m.transfer_to_location
832 , m.transaction_type_id
833 , m.organization_id
834 )
835 , NULL
836 ) to_loc
837 , i.concatenated_segments item
838 , m.transaction_quantity
839 , m.transaction_uom
840 , l.lpn_id lpn_id
841 /*modified for bug 6717052*/
842 , m.transaction_temp_id taskid
843 , l.lpn_context
844 , DECODE( m.parent_line_id
845 , NULL, 'FALSE'
846 , 'TRUE'
847 ) is_bulk_pick
848 , 2 dummy_sort
849 FROM wms_dispatched_tasks w
850 , mtl_material_transactions_temp m
851 , mtl_system_items_kfv i
852 , wms_license_plate_numbers l
853 , mtl_txn_request_lines mtrl
854 , wms_license_plate_numbers wlpn2
855 WHERE w.person_id = p_user_id
856 AND w.organization_id = p_organization_id
857 AND w.status = 4
858 AND w.transaction_temp_id = m.transaction_temp_id
859 AND m.organization_id = i.organization_id
860 AND m.inventory_item_id = i.inventory_item_id
861 AND m.organization_id = l.organization_id
862 AND w.task_type IN (1,4,5,DECODE(p_page_type , 'MANUAL_UNLOAD' , 6))--13807883
863 AND l.outermost_lpn_id = wlpn2.lpn_id --12984304
864 AND m.transfer_lpn_id = l.lpn_id --12984304
865 AND l.lpn_id <> l.outermost_lpn_id --12984304
866 --AND l.lpn_id IN (SELECT DISTINCT(wlpn1.outermost_lpn_id) FROM wms_license_plate_numbers wlpn1 WHERE wlpn1.lpn_id = m.transfer_lpn_id AND wlpn1.lpn_id <> wlpn1.outermost_lpn_id)
867 AND m.move_order_line_id = mtrl.line_id (+)
868 AND (m.parent_line_id IS NULL
869 OR
870 (m.parent_line_id IS NOT NULL
871 AND
872 m.parent_line_id = m.transaction_temp_id
873 )
874 )
875 AND l.license_plate_number LIKE (p_concat_segments)
876 UNION ALL
877 -- Staging moves
878 SELECT /*+ ORDERED */
879 ' ' status
880 , l.license_plate_number lpn
881 , 'STAGING MOVE' task_type
882 , 'NORMAL' task_mo_status
883 , m.transfer_subinventory to_sub
884 , get_locator( m.wms_task_type
885 , m.locator_id
886 , m.transfer_to_location
887 , m.transaction_type_id
888 , m.organization_id
889 ) to_loc
890 , i.concatenated_segments item
891 , to_number(null) transaction_quantity
892 , to_char(null) transaction_uom
893 , m.transfer_lpn_id lpn_id
894 , m.transaction_temp_id taskid
895 , l.lpn_context
896 , 'FALSE' is_bulk_pick
897 , 6 dummy_sort
898 FROM wms_dispatched_tasks w
899 , mtl_material_transactions_temp m
900 , mtl_system_items_kfv i
901 , wms_license_plate_numbers l
902 WHERE w.person_id = p_user_id
903 AND w.organization_id = p_organization_id
904 AND w.status = 4
905 AND w.transaction_temp_id = m.transaction_temp_id
906 AND m.organization_id = i.organization_id
907 AND m.inventory_item_id = i.inventory_item_id
908 AND m.organization_id = l.organization_id
909 AND w.task_type = 7
910 AND m.transfer_lpn_id = l.lpn_id
911 AND l.license_plate_number LIKE (p_concat_segments)
912 ORDER BY task_type
913 , to_sub
914 , to_loc
915 , lpn
916 , dummy_sort ;
917
918 ELSIF (p_unload_receiving = 'YES') THEN --Changes for Unload ER:13773615 :Incase Inbound Unload Form function parameter set.
919
920
921 OPEN x_tasks FOR
922 -- Putaway J or higher
923
924
925 SELECT ' ' status
926 , wlpn1.license_plate_number lpn
927 , 'RECEIVING' task_type
928 , 'NORMAL' task_mo_status
929 , '' to_sub
930 , '' to_loc
931 , '' item
932 , To_number(NULL) transaction_quantity
933 , '' transaction_uom
934 , wlpn1.lpn_id
935 , To_number(NULL) taskid
936 , wlpn1.lpn_context
937 , 'FALSE' is_bulk_pick
938 , 1 dummy_sort
939 FROM wms_license_plate_numbers wlpn1
940 WHERE wlpn1.license_plate_number LIKE p_concat_segments
941 and lpn_context IN (3,6,7)
942 and organization_id=p_Organization_Id
943 and (p_page_type = 'MANUAL_UNLOAD' and p_unload_receiving='YES')
944 ORDER BY task_type
945 , to_sub
946 , to_loc
947 , lpn
948 , dummy_sort;
949
950
951 END IF;
952 END get_tasks_lov;
953
954
955
956 PROCEDURE GET_REASONS_LOV(x_reasons OUT NOCOPY t_genref,
957 p_reason_type IN NUMBER,
958 p_concat_segments IN VARCHAR2) IS
959
960
961 BEGIN
962 inv_trx_util_pub.trace( 'WMS_Task_Dispatch_LOV - GET_REASONS_LOV - p_reason type' || p_reason_type || 'p_concat_segments -' || p_concat_segments , '~~~~ ', 1); --/* Bug 9448490 Lot Substitution Project */
963
964 OPEN x_reasons FOR
965 SELECT reason_name,description, reason_id
966 FROM mtl_transaction_reasons
967 WHERE reason_type=p_reason_type
968 AND nvl(DISABLE_DATE, SYSDATE+1) > SYSDATE
969 AND reason_name LIKE (p_concat_segments || '%') --12970028
970 ORDER BY reason_name;
971
972 END get_reasons_lov;
973
974 --
975 -- Procedure overloaded for Transaction Reason Security build.
976 -- 4505091, nsrivast
977
978 PROCEDURE GET_REASONS_LOV(x_reasons OUT NOCOPY t_genref,
979 p_reason_type IN NUMBER,
980 p_concat_segments IN VARCHAR2,
981 p_txn_type_id IN NUMBER ) IS
982
983
984 BEGIN
985 mydebug('In GET_LPN_ITEMS_LOV fnd_global.resp_id:' || fnd_global.resp_id || '...fnd_profile.value_wnps_INV_TRANS_REASON_SECURITY:'||fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'));
986 mydebug('In p_txn_type_id :' || p_txn_type_id);
987 OPEN x_reasons FOR
988 SELECT reason_name,description, reason_id
989 FROM mtl_transaction_reasons
990 WHERE reason_type=p_reason_type
991 AND nvl(DISABLE_DATE, SYSDATE+1) > SYSDATE
992 AND reason_name LIKE p_concat_segments || '%' -- Bug 14369838
993 -- nsrivast, invconv , transaction reason security
994 AND ( NVL ( fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'N'
995 OR
996 reason_id IN (SELECT reason_id FROM mtl_trans_reason_security mtrs
997 WHERE(( responsibility_id = fnd_global.resp_id OR NVL(responsibility_id, -1) = -1 )
998 AND
999 ( mtrs.transaction_type_id = p_txn_type_id OR NVL(mtrs.transaction_type_id, -1) = -1 )
1000 )-- where ends
1001 )-- select ends
1002 ) -- and condn ends ,-- nsrivast, invconv
1003 UNION ALL -- Bug 14369838
1004 SELECT reason_name,description, reason_id
1005 FROM mtl_transaction_reasons
1006 WHERE reason_type=p_reason_type
1007 AND nvl(DISABLE_DATE, SYSDATE+1) > SYSDATE
1008 AND reason_name LIKE p_concat_segments || '%'
1009 -- nsrivast, invconv , transaction reason security
1010 AND NVL ( fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'Y'
1011 AND
1012 reason_id NOT IN (SELECT mtrs.reason_id FROM mtl_trans_reason_security mtrs )
1013 ORDER BY reason_name;
1014
1015 END get_reasons_lov;
1016
1017
1018 PROCEDURE GET_LPN_ITEMS_LOV(x_items OUT NOCOPY t_genref,
1019 p_lpn_id IN NUMBER,
1020 p_concat_segments IN VARCHAR2) IS
1021
1022 BEGIN
1023
1024 OPEN x_items FOR
1025 SELECT k.concatenated_segments, k.inventory_item_id
1026 FROM mtl_material_transactions_temp m, mtl_system_items_vl k
1027 WHERE m.transfer_lpn_id=p_lpn_id
1028 AND m.organization_id=k.organization_id
1029 AND m.inventory_item_id=k.inventory_item_id
1030 AND k.concatenated_segments LIKE (p_concat_segments);
1031 mydebug('In GET_LPN_ITEMS_LOV');
1032 END GET_LPN_ITEMS_LOV;
1033
1034 PROCEDURE get_container_items_lov
1035 (x_container_items OUT NOCOPY t_genref,
1036 p_org_id IN NUMBER,
1037 p_concat_segments IN VARCHAR2)
1038 IS
1039
1040 BEGIN
1041
1042 OPEN x_container_items FOR
1043 SELECT k.concatenated_segments, k.inventory_item_id
1044 FROM mtl_system_items_vl k
1045 WHERE k.organization_id=p_org_id
1046 AND k.container_item_flag='Y'
1047 AND k.concatenated_segments LIKE (p_concat_segments);
1048 mydebug('In get_container_items_lov');
1049
1050 END GET_CONTAINER_ITEMS_LOV;
1051
1052 PROCEDURE validate_container_items
1053 (p_organization_id IN NUMBER,
1054 p_concat_segments IN VARCHAR2,
1055 x_is_valid_container OUT NOCOPY VARCHAR2,
1056 x_container_item_id OUT NOCOPY NUMBER)
1057 IS
1058 TYPE container_item_record_type IS RECORD
1059 (concatenated_segments VARCHAR2(40),
1060 inventory_item_id NUMBER);
1061
1062 container_item_rec container_item_record_type;
1063 l_container_items t_genref;
1064 BEGIN
1065 x_is_valid_container := 'N';
1066
1067 get_container_items_lov(x_container_items => l_container_items,
1068 p_org_id => p_organization_id,
1069 p_concat_segments => p_concat_segments);
1070
1071 LOOP
1072 FETCH l_container_items INTO container_item_rec;
1073 EXIT WHEN l_container_items%notfound;
1074
1075 IF container_item_rec.concatenated_segments = p_concat_segments THEN
1076 x_is_valid_container := 'Y';
1077 x_container_item_id := container_item_rec.inventory_item_id;
1078 EXIT;
1079 END IF;
1080
1081 END LOOP;
1082
1083 END validate_container_items;
1084
1085 PROCEDURE get_eqp_lov
1086 (x_eqps OUT NOCOPY t_genref,
1087 p_Organization_Id IN NUMBER,
1088 p_concat_segments IN VARCHAR2)
1089 IS
1090
1091 BEGIN
1092
1093 OPEN x_eqps FOR
1094 SELECT
1095 mtl_serial_numbers.serial_number EQP_INS,
1096 mtl_system_items_vl.concatenated_segments EQP_NAME,
1097 mtl_system_items_vl.description EQP_DESC ,
1098 mtl_serial_numbers.inventory_item_id EQP_ID
1099 from
1100 mtl_serial_numbers,
1101 mtl_system_items_vl /* Bug 5581528 */
1102 where mtl_serial_numbers.inventory_item_id=mtl_system_items_vl.inventory_item_id
1103 and mtl_system_items_vl.organization_id=p_Organization_Id
1104 and mtl_system_items_vl.equipment_type=1
1105 AND mtl_serial_numbers.serial_number LIKE (p_concat_segments)
1106 UNION ALL
1107 SELECT
1108 'NONE' EQP_INS,
1109 'NONE'eqp_name,
1110 'NONE' EQP_DESC,
1111 -999 eqp_id
1112 FROM DUAL
1113 where 'NONE' like (upper(p_concat_segments));
1114
1115
1116 END get_eqp_lov;
1117
1118
1119 PROCEDURE get_device_lov
1120 (x_devices OUT NOCOPY t_genref,
1121 p_Organization_Id IN NUMBER,
1122 p_concat_segments IN VARCHAR2)
1123 IS
1124
1125 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1126 BEGIN
1127 IF (l_debug = 1) THEN
1128 mydebug('In get_device_lov');
1129 END IF;
1130
1131 OPEN x_devices FOR
1132 SELECT NAME device_name,
1133 DEVICE_TYPE device_type,
1134 DESCRIPTION device_desc,
1135 DEVICE_ID device_id,
1136 SUBINVENTORY_CODE subinventory
1137 FROM WMS_DEVICES_VL
1138 WHERE SUBINVENTORY_CODE is not null
1139 and ORGANIZATION_ID = p_Organization_Id
1140 and NAME like (p_concat_segments)
1141 UNION ALL
1142 SELECT 'NONE' device_name,'NONE' device_type,'NONE'device_desc,-999 EQP_ID,'NONE' SUBINVENTORY FROM DUAL
1143 where 'NONE' like (upper(p_concat_segments))
1144 ORDER BY 1;
1145
1146
1147 END get_device_lov;
1148
1149
1150 PROCEDURE get_current_device_lov
1151 (x_devices OUT NOCOPY t_genref,
1152 p_Employee_Id IN NUMBER,
1153 p_concat_segments IN VARCHAR2)
1154 IS
1155 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1156 BEGIN
1157 IF (l_debug = 1) THEN
1158 mydebug('In get_device_lov');
1159 END IF;
1160
1161 OPEN x_devices FOR
1162 SELECT wdv.NAME device_name,
1163 wdv.DEVICE_TYPE device_type,
1164 wdv.DESCRIPTION device_desc,
1165 wdat.ASSIGNMENT_TEMP_ID temp_id,
1166 wdv.SUBINVENTORY_CODE subinventory
1167 FROM WMS_DEVICES_VL wdv, WMS_DEVICE_ASSIGNMENT_TEMP wdat
1168 WHERE
1169 wdat.EMPLOYEE_ID = p_Employee_Id
1170 and wdv.device_id = wdat.device_id
1171 and wdv.NAME like (p_concat_segments)
1172 order by 4;
1173
1174 END get_current_device_lov;
1175
1176
1177 Function get_locator(p_wms_task_type IN NUMBER,
1178 p_locator_id IN NUMBER,
1179 p_transfer_to_location_id IN NUMBER,
1180 p_transaction_TYPE_id IN NUMBER,
1181 p_organization_id IN NUMBER)
1182 RETURN VARCHAR2 IS
1183 v_concatenated_segments varchar2(204):= null;
1184 begin
1185 if p_transaction_type_ID = 35 then
1186 v_concatenated_segments := NULL;
1187 else
1188 if p_wms_task_type in (1,4,5) then
1189 begin
1190 select concatenated_segments into v_concatenated_segments
1191 from mtl_item_locations_kfv k
1192 where k.inventory_location_id = p_transfer_to_location_id
1193 and k.organization_id = p_organization_id;
1194 EXCEPTION
1195 WHEN no_data_found THEN
1196 RAISE fnd_api.g_exc_error;
1197 WHEN OTHERS THEN
1198 RAISE fnd_api.g_exc_unexpected_error;
1199 END;
1200 elsif p_wms_task_type = 2 then
1201 begin
1202 select concatenated_segments into v_concatenated_segments
1203 from mtl_item_locations_kfv k
1204 where k.inventory_location_id = p_locator_id
1205 and k.organization_id = p_organization_id;
1206 EXCEPTION
1207 WHEN no_data_found THEN
1208 RAISE fnd_api.g_exc_error;
1209 WHEN OTHERS THEN
1210 RAISE fnd_api.g_exc_unexpected_error;
1211 END;
1212 end if;
1213 end if;
1214 return v_concatenated_segments;
1215
1216 end get_locator;
1217
1218
1219
1220 PROCEDURE get_item_lov
1221 (x_Items OUT NOCOPY t_genref,
1222 p_Organization_Id IN NUMBER,
1223 p_Concatenated_Segments IN VARCHAR2,
1224 p_where_clause IN VARCHAR2,
1225 p_lpn_id IN NUMBER)
1226 IS
1227 l_cross_ref varchar2(204);
1228 l_lov_sql VARCHAR2(4000);
1229 l_append varchar2(2):='';
1230
1231 BEGIN
1232
1233 l_append := wms_deploy.get_item_suffix_for_lov(p_Concatenated_Segments);
1234
1235 l_cross_ref := lpad(Rtrim(p_concatenated_segments, '%'), g_gtin_code_length, '00000000000000');
1236
1237 IF p_lpn_id IS NULL THEN
1238 OPEN x_items FOR
1239 SELECT concatenated_segments,
1240 inventory_item_id, description,
1241 Nvl(revision_qty_control_code,1),
1242 Nvl(lot_control_code, 1),
1243 Nvl(serial_number_control_code, 1),
1244 Nvl(restrict_subinventories_code, 2),
1245 Nvl(restrict_locators_code, 2),
1246 Nvl(location_control_code, 1),
1247 primary_uom_code,
1248 Nvl(inspection_required_flag, 'N'),
1249 Nvl(shelf_life_code, 1),
1250 Nvl(shelf_life_days,0),
1251 Nvl(allowed_units_lookup_code, 2),
1252 Nvl(effectivity_control,1), 0, 0,
1253 Nvl(default_serial_status_id,1),
1254 Nvl(serial_status_enabled,'N'),
1255 Nvl(default_lot_status_id,0),
1256 Nvl(lot_status_enabled,'N'),
1257 '',
1258 'N',
1259 inventory_item_flag,
1260 0,
1261 wms_deploy.get_item_client_name(inventory_item_id),
1262 inventory_asset_flag,
1263 outside_operation_flag,
1264 NVL(grade_control_flag,'N'),
1265 NVL(default_grade,''),
1266 NVL(expiration_action_interval,0),
1267 NVL(expiration_action_code,''),
1268 NVL(hold_days,0),
1269 NVL(maturity_days,0),
1270 NVL(retest_interval,0),
1271 NVL(copy_lot_attribute_flag,'N'),
1272 NVL(child_lot_flag,'N'),
1276 NVL(secondary_default_ind,''),
1273 NVL(child_lot_validation_flag,'N'),
1274 NVL(lot_divisible_flag,'Y'),
1275 NVL(secondary_uom_code,''),
1277 NVL(tracking_quantity_ind,'P'),
1278 NVL(dual_uom_deviation_high,0),
1279 NVL(dual_uom_deviation_low,0)
1280 FROM mtl_system_items_vl /* Bug 5581528 */
1281 WHERE organization_id = p_organization_id
1282 AND concatenated_segments LIKE p_concatenated_segments || l_append
1283
1284 --Changes for GTIN
1285 UNION
1286
1287 SELECT concatenated_segments,
1288 msik.inventory_item_id, msik.description,
1289 Nvl(revision_qty_control_code,1),
1290 Nvl(lot_control_code, 1),
1291 Nvl(serial_number_control_code, 1),
1292 Nvl(restrict_subinventories_code, 2),
1293 Nvl(restrict_locators_code, 2),
1294 Nvl(location_control_code, 1),
1295 primary_uom_code,
1296 Nvl(inspection_required_flag, 'N'),
1297 Nvl(shelf_life_code, 1),
1298 Nvl(shelf_life_days,0),
1299 Nvl(allowed_units_lookup_code, 2),
1300 Nvl(effectivity_control,1), 0, 0,
1301 Nvl(default_serial_status_id,1),
1302 Nvl(serial_status_enabled,'N'),
1303 Nvl(default_lot_status_id,0),
1304 Nvl(lot_status_enabled,'N'),
1305 'mcr.cross_reference',
1306 'N',
1307 inventory_item_flag,
1308 0,
1309 wms_deploy.get_item_client_name(msik.inventory_item_id),
1310 inventory_asset_flag,
1311 outside_operation_flag,
1312 NVL(grade_control_flag,'N'),
1313 NVL(default_grade,''),
1314 NVL(expiration_action_interval,0),
1315 NVL(expiration_action_code,''),
1316 NVL(hold_days,0),
1317 NVL(maturity_days,0),
1318 NVL(retest_interval,0),
1319 NVL(copy_lot_attribute_flag,'N'),
1320 NVL(child_lot_flag,'N'),
1321 NVL(child_lot_validation_flag,'N'),
1322 NVL(lot_divisible_flag,'Y'),
1323 NVL(secondary_uom_code,''),
1324 NVL(secondary_default_ind,''),
1325 NVL(tracking_quantity_ind,'P'),
1326 NVL(dual_uom_deviation_high,0),
1327 NVL(dual_uom_deviation_low,0)
1328 FROM mtl_system_items_vl msik, /* Bug 5581528 */
1329 mtl_cross_references mcr
1330 WHERE msik.organization_id = p_organization_id
1331 AND msik.inventory_item_id = mcr.inventory_item_id
1332 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1333 AND mcr.cross_reference LIKE l_cross_ref
1334 AND (mcr.organization_id = msik.organization_id
1335 OR
1336 mcr.org_independent_flag = 'Y');
1337 ELSE
1338
1339 /** Make the above sql dynamic to avoid dependency on
1340 wms_grouped_tasks_gtemp for patchset I
1341 and use bind variables for better performance*/
1342 OPEN x_items FOR
1343
1344 SELECT concatenated_segments,
1345 inventory_item_id, description,
1346 Nvl(revision_qty_control_code,1),
1347 Nvl(lot_control_code, 1),
1348 Nvl(serial_number_control_code, 1),
1349 Nvl(restrict_subinventories_code, 2),
1350 Nvl(restrict_locators_code, 2),
1351 Nvl(location_control_code, 1),
1352 primary_uom_code,
1353 Nvl(inspection_required_flag, 'N'),
1354 Nvl(shelf_life_code, 1),
1355 Nvl(shelf_life_days,0),
1356 Nvl(allowed_units_lookup_code, 2),
1357 Nvl(effectivity_control,1), 0, 0,
1358 Nvl(default_serial_status_id,1),
1359 Nvl(serial_status_enabled,'N'),
1360 Nvl(default_lot_status_id,0),
1361 Nvl(lot_status_enabled,'N'),
1362 '',
1363 'N',
1364 inventory_item_flag,
1365 0,
1366 wms_deploy.get_item_client_name(inventory_item_id),
1367 inventory_asset_flag,
1368 outside_operation_flag,
1369 NVL(grade_control_flag,'N'),
1370 NVL(default_grade,''),
1371 NVL(expiration_action_interval,0),
1372 NVL(expiration_action_code,''),
1373 NVL(hold_days,0),
1374 NVL(maturity_days,0),
1375 NVL(retest_interval,0),
1376 NVL(copy_lot_attribute_flag,'N'),
1377 NVL(child_lot_flag,'N'),
1378 NVL(child_lot_validation_flag,'N'),
1379 NVL(lot_divisible_flag,'Y'),
1380 NVL(secondary_uom_code,''),
1381 NVL(secondary_default_ind,''),
1382 NVL(tracking_quantity_ind,'P'),
1383 NVL(dual_uom_deviation_high,0),
1384 NVL(dual_uom_deviation_low,0)
1385 FROM mtl_system_items_vl msik /* Bug 5581528 */
1386 WHERE organization_id = p_organization_id
1387 AND concatenated_segments LIKE p_concatenated_segments||l_append
1388 AND exists
1389 ( select 1
1390 from wms_putaway_group_tasks_gtmp wpgt
1391 where wpgt.inventory_item_id = msik.inventory_item_id
1392 and lpn_id = p_lpn_id
1393 and drop_type = 'ID')
1394 --Changes for GTIN
1395 UNION
1396
1397 SELECT concatenated_segments,
1398 msik.inventory_item_id, msik.description,
1399 Nvl(revision_qty_control_code,1),
1400 Nvl(lot_control_code, 1),
1401 Nvl(serial_number_control_code, 1),
1402 Nvl(restrict_subinventories_code, 2),
1403 Nvl(restrict_locators_code, 2),
1404 Nvl(location_control_code, 1),
1405 primary_uom_code,
1406 Nvl(inspection_required_flag, 'N'),
1407 Nvl(shelf_life_code, 1),
1408 Nvl(shelf_life_days,0),
1409 Nvl(allowed_units_lookup_code, 2),
1410 Nvl(effectivity_control,1), 0, 0,
1411 Nvl(default_serial_status_id,1),
1412 Nvl(serial_status_enabled,'N'),
1413 Nvl(default_lot_status_id,0),
1414 Nvl(lot_status_enabled,'N'),
1415 'mcr.cross_reference',
1416 'N',
1417 inventory_item_flag,
1418 0,
1419 wms_deploy.get_item_client_name(msik.inventory_item_id),
1420 inventory_asset_flag,
1421 outside_operation_flag,
1422 NVL(grade_control_flag,'N'),
1423 NVL(default_grade,''),
1424 NVL(expiration_action_interval,0),
1425 NVL(expiration_action_code,''),
1426 NVL(hold_days,0),
1427 NVL(maturity_days,0),
1428 NVL(retest_interval,0),
1429 NVL(copy_lot_attribute_flag,'N'),
1430 NVL(child_lot_flag,'N'),
1431 NVL(child_lot_validation_flag,'N'),
1432 NVL(lot_divisible_flag,'Y'),
1433 NVL(secondary_uom_code,''),
1434 NVL(secondary_default_ind,''),
1435 NVL(tracking_quantity_ind,'P'),
1436 NVL(dual_uom_deviation_high,0),
1437 NVL(dual_uom_deviation_low,0)
1438 FROM mtl_system_items_vl msik, /* Bug 5581528 */
1439 mtl_cross_references mcr
1440 WHERE msik.organization_id = p_organization_id
1441 AND msik.inventory_item_id = mcr.inventory_item_id
1442 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1443 AND mcr.cross_reference LIKE l_cross_ref
1444 AND (mcr.organization_id = msik.organization_id
1445 OR
1446 mcr.org_independent_flag = 'Y')
1447 AND exists
1448 ( select 1
1449 from wms_putaway_group_tasks_gtmp wpgt
1450 where wpgt.inventory_item_id = msik.inventory_item_id
1451 and lpn_id = p_lpn_id
1452 and drop_type = 'ID');
1453
1454 END IF;
1455 mydebug('In get_item_lov');
1456 END get_item_lov;
1457
1458
1459 -- Overloaded procedure for the reason LOV in the discrepancy page for APL
1460 PROCEDURE get_reasons_lov(x_reasons OUT NOCOPY t_genref,
1461 p_reason_type IN NUMBER,
1462 p_reason_contexts IN VARCHAR2,
1463 p_concat_segments IN VARCHAR2)
1464 IS
1465 l_context_count NUMBER := 1;
1466 l_cp_allowed NUMBER := 0;
1467 l_le_allowed NUMBER := 0;
1468 l_pn_allowed NUMBER := 0;
1469 l_po_allowed NUMBER := 0;
1470 l_pp_allowed NUMBER := 0;
1471 l_sl_allowed NUMBER := 0;
1472 l_um_allowed NUMBER := 0;
1473 l_pl_allowed NUMBER := 0;
1474 l_cl_allowed NUMBER := 0; --/* Bug 9448490 Lot Substitution Project */
1475 l_context VARCHAR2(2);
1476 l_reason_count NUMBER := 0;
1477 BEGIN
1478 /* Bug 9448490 Lot Substitution Project */
1479 inv_trx_util_pub.trace( 'WMSTSKLB ~getreasonlov 2 - entered overloaded proc -p_reason_type-'|| p_reason_type ||'-p_reason_contexts-'||p_reason_contexts||'-p_concat_segments-'||p_concat_segments , 'wms_alloc_gtmp_trigger', 1);
1480
1481 WHILE l_context_count < Length(p_reason_contexts) LOOP
1482 l_context := Substr(p_reason_contexts, l_context_count, 2);
1483
1484 IF l_context = 'CP' THEN
1485 l_cp_allowed := 1;
1486 ELSIF l_context = 'LE' THEN
1487 l_le_allowed := 1;
1488 ELSIF l_context = 'PN' THEN
1489 l_pn_allowed := 1;
1490 ELSIF l_context = 'PO' THEN
1491 l_po_allowed := 1;
1492 ELSIF l_context = 'PP' THEN
1493 l_pp_allowed := 1;
1494 ELSIF l_context = 'SL' THEN
1495 l_sl_allowed := 1;
1496 ELSIF l_context = 'UM' THEN
1497 l_um_allowed := 1;
1498 ELSIF l_context = 'PL' THEN
1499 l_pl_allowed := 1;
1500 ELSIF l_context = 'CL' THEN
1501 l_cl_allowed := 1; --/* Bug 9448490 Lot Substitution Project */
1502 END IF;
1503
1504 l_context_count := l_context_count + 2;
1505 END LOOP;
1506
1507 BEGIN
1508 SELECT COUNT(reason_name)
1509 INTO l_reason_count
1510 FROM mtl_transaction_reasons
1511 WHERE reason_type = p_reason_type -- Picking
1512 AND Nvl(DISABLE_DATE, SYSDATE+1) > SYSDATE
1513 AND Decode(reason_context_code,
1514 'CP', l_cp_allowed,
1515 'LE', l_le_allowed,
1516 'PN', l_pn_allowed,
1517 'PO', l_po_allowed,
1518 'PP', l_pp_allowed,
1519 'SL', l_sl_allowed,
1520 'UM', l_um_allowed,
1521 'PL', l_pl_allowed,
1522 'CL', l_cl_allowed) = 1 --/* Bug 9448490 Lot Substitution Project */
1523 AND reason_name LIKE p_concat_segments || '%' ;
1524 EXCEPTION
1525 WHEN no_data_found THEN
1526 l_reason_count := 0;
1527 END;
1528
1529 OPEN x_reasons FOR
1530 SELECT reason_name, description,
1531 reason_id, reason_context_code,
1532 workflow_name, workflow_process, l_reason_count
1533 FROM mtl_transaction_reasons
1534 WHERE reason_type = p_reason_type -- Picking
1535 AND Nvl(DISABLE_DATE, Sysdate+1) > Sysdate
1536 AND Decode(reason_context_code,
1537 'CP', l_cp_allowed,
1538 'LE', l_le_allowed,
1539 'PN', l_pn_allowed,
1540 'PO', l_po_allowed,
1541 'PP', l_pp_allowed,
1542 'SL', l_sl_allowed,
1543 'UM', l_um_allowed,
1544 'PL', l_pl_allowed,
1545 'CL', l_cl_allowed) = 1 --/* Bug 9448490 Lot Substitution Project */
1546 AND reason_name LIKE p_concat_segments || '%'
1550
1547 ORDER BY reason_name;
1548
1549 END get_reasons_lov;
1551
1552 -- Overloaded procedure for the reason LOV in the discrepancy page for APL
1553 -- Procedure overloaded for Transaction Reason Security build. 4505091, nsrivast
1554 PROCEDURE get_reasons_lov(x_reasons OUT NOCOPY t_genref,
1555 p_reason_type IN NUMBER,
1556 p_reason_contexts IN VARCHAR2,
1557 p_concat_segments IN VARCHAR2,
1558 p_txn_type_id IN VARCHAR2 )
1559 IS
1560 l_context_count NUMBER := 1;
1561 l_cp_allowed NUMBER := 0;
1562 l_le_allowed NUMBER := 0;
1563 l_pn_allowed NUMBER := 0;
1564 l_po_allowed NUMBER := 0;
1565 l_pp_allowed NUMBER := 0;
1566 l_sl_allowed NUMBER := 0;
1567 l_um_allowed NUMBER := 0;
1568 l_pl_allowed NUMBER := 0;
1569 l_context VARCHAR2(2);
1570 l_reason_count NUMBER := 0;
1571 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0); -- 14319939
1572 BEGIN
1573 -- bug 14319939 start
1574 IF (l_debug = 1) THEN
1575 mydebug('In get_reasons_lov( 5 para)');
1576 mydebug('p_reason_type: '|| p_reason_type);
1577 mydebug('p_reason_contexts: '|| p_reason_contexts);
1578 mydebug('p_concat_segments: '|| p_concat_segments);
1579 mydebug('p_txn_type_id: '|| p_txn_type_id);
1580 mydebug('fnd_global.resp_id: '|| fnd_global.resp_id);
1581 END IF;
1582 -- end 14319939
1583 WHILE l_context_count < Length(p_reason_contexts) LOOP
1584 l_context := Substr(p_reason_contexts, l_context_count, 2);
1585
1586 IF l_context = 'CP' THEN
1587 l_cp_allowed := 1;
1588 ELSIF l_context = 'LE' THEN
1589 l_le_allowed := 1;
1590 ELSIF l_context = 'PN' THEN
1591 l_pn_allowed := 1;
1592 ELSIF l_context = 'PO' THEN
1593 l_po_allowed := 1;
1594 ELSIF l_context = 'PP' THEN
1595 l_pp_allowed := 1;
1596 ELSIF l_context = 'SL' THEN
1597 l_sl_allowed := 1;
1598 ELSIF l_context = 'UM' THEN
1599 l_um_allowed := 1;
1600 ELSIF l_context = 'PL' THEN
1601 l_pl_allowed := 1;
1602 END IF;
1603
1604 l_context_count := l_context_count + 2;
1605 END LOOP;
1606
1607 BEGIN
1608 SELECT COUNT(reason_name)
1609 INTO l_reason_count
1610 FROM ( -- Added for Bug 14319939
1611 select reason_name -- Added for Bug 14319939
1612 FROM mtl_transaction_reasons
1613 WHERE reason_type = p_reason_type -- Picking
1614 AND Nvl(DISABLE_DATE, SYSDATE+1) > SYSDATE
1615 AND Decode(reason_context_code,
1616 'CP', l_cp_allowed,
1617 'LE', l_le_allowed,
1618 'PN', l_pn_allowed,
1619 'PO', l_po_allowed,
1620 'PP', l_pp_allowed,
1621 'SL', l_sl_allowed,
1622 'UM', l_um_allowed,
1623 'PL', l_pl_allowed) = 1
1624 AND reason_name LIKE p_concat_segments || '%'
1625 -- nsrivast, invconv , transaction reason security
1626 AND ( NVL ( fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'N'
1627 OR
1628 reason_id IN (SELECT reason_id FROM mtl_trans_reason_security mtrs
1629 WHERE(( responsibility_id = fnd_global.resp_id OR NVL(responsibility_id, -1) = -1 )
1630 AND
1631 ( mtrs.transaction_type_id = p_txn_type_id OR NVL(mtrs.transaction_type_id, -1) = -1 )
1632 )-- where ends
1633 )-- select ends
1634 ) -- and condn ends ,-- nsrivast, invconv
1635 UNION ALL -- Added for bug 14319939 start
1636 SELECT reason_name
1637 FROM mtl_transaction_reasons
1638 WHERE reason_type = p_reason_type
1639 AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE
1640 AND Decode(reason_context_code,
1641 'CP', l_cp_allowed,
1642 'LE', l_le_allowed,
1643 'PN', l_pn_allowed,
1644 'PO', l_po_allowed,
1645 'PP', l_pp_allowed,
1646 'SL', l_sl_allowed,
1647 'UM', l_um_allowed,
1648 'PL', l_pl_allowed) = 1
1649 AND reason_name LIKE p_concat_segments || '%'
1650 -- nsrivast, invconv , transaction reason security
1651 AND NVL ( fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'Y'
1652 AND reason_id NOT IN
1653 (SELECT mtrs.reason_id FROM mtl_trans_reason_security mtrs
1654 )
1655 ) -- Added for bug 14319939 end
1656 ;
1657 EXCEPTION
1658 WHEN no_data_found THEN
1659 l_reason_count := 0;
1660 END;
1661 -- bug 14319939 start
1662 IF (l_debug = 1) THEN
1663 mydebug('l_reason_count: '|| l_reason_count);
1664 END IF;
1665 OPEN x_reasons FOR
1666 SELECT reason_name, description,
1667 reason_id, reason_context_code,
1668 workflow_name, workflow_process, l_reason_count
1669 FROM mtl_transaction_reasons
1670 WHERE reason_type = p_reason_type -- Picking
1671 AND Nvl(DISABLE_DATE, Sysdate+1) > Sysdate
1672 AND Decode(reason_context_code,
1673 'CP', l_cp_allowed,
1674 'LE', l_le_allowed,
1675 'PN', l_pn_allowed,
1676 'PO', l_po_allowed,
1677 'PP', l_pp_allowed,
1678 'SL', l_sl_allowed,
1679 'UM', l_um_allowed,
1680 'PL', l_pl_allowed) = 1
1681 AND reason_name LIKE p_concat_segments || '%'
1682 -- nsrivast, invconv , transaction reason security
1683 AND ( NVL ( fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'N'
1684 OR
1685 reason_id IN (SELECT reason_id FROM mtl_trans_reason_security mtrs
1686 WHERE(( responsibility_id = fnd_global.resp_id OR NVL(responsibility_id, -1) = -1 )
1687 AND
1688 ( mtrs.transaction_type_id = p_txn_type_id OR NVL(mtrs.transaction_type_id, -1) = -1 )
1689 )-- where ends
1690 )-- select ends
1691 ) -- and condn ends ,-- nsrivast, invconv
1692 UNION ALL -- Bug 14319939 start
1693 SELECT reason_name, description,
1694 reason_id, reason_context_code,
1695 workflow_name, workflow_process, l_reason_count
1696 FROM mtl_transaction_reasons
1697 WHERE reason_type = p_reason_type
1698 AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE
1699 AND Decode(reason_context_code,
1700 'CP', l_cp_allowed,
1701 'LE', l_le_allowed,
1702 'PN', l_pn_allowed,
1703 'PO', l_po_allowed,
1704 'PP', l_pp_allowed,
1705 'SL', l_sl_allowed,
1706 'UM', l_um_allowed,
1707 'PL', l_pl_allowed) = 1
1708 AND reason_name LIKE p_concat_segments || '%'
1709 -- nsrivast, invconv , transaction reason security
1710 AND NVL ( fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'Y'
1711 AND reason_id NOT IN
1712 (SELECT mtrs.reason_id FROM mtl_trans_reason_security mtrs
1713 ) -- Bug 14319939 end
1714 ORDER BY reason_name;
1715
1716 END get_reasons_lov;
1717
1718 END wms_task_dispatch_LOV;
1719