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