[Home] [Help]
PACKAGE BODY: APPS.WMS_LPN_LOVS
Source
1 PACKAGE BODY WMS_LPN_LOVS AS
2 /* $Header: WMSLPNLB.pls 120.13.12020000.6 2013/03/01 07:01:21 ssingams ship $ */
3
4 -- Name: GET_LPN_LOV
5 --
6 -- Input parameters:
7 -- p_lpn which restricts LOV SQL to the user input text
8 --
9 -- Output parameters:
10 -- x_lpn_lov returns LOV rows as reference cursor
11 --
12 -- Functions: This API returns valid LPN and lpn_id
13 --
14
15
16 PROCEDURE mydebug(msg in varchar2)
17 IS
18 l_msg VARCHAR2(5100);
19 l_ts VARCHAR2(30);
20 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
21 BEGIN
22 -- select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
23 -- l_msg:=l_ts||' '||msg;
24
25 l_msg := msg;
26
27 inv_mobile_helper_functions.tracelog
28 (p_err_msg => l_msg,
29 p_module => 'WMS_LPN_LOVS',
30 p_level => 4);
31
32 --dbms_output.put_line(l_msg);
33 END;
34
35 PROCEDURE GET_SOURCE_LOV
36 (x_source_lov OUT NOCOPY t_genref,
37 p_lookup_type IN VARCHAR2
38 )
39 IS
40 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
41 BEGIN
42
43 OPEN x_source_lov FOR
44 SELECT meaning, lookup_code
45 FROM mfg_lookups
46 WHERE lookup_type = 'WMS_PREPACK_SOURCE'
47 AND meaning LIKE (p_lookup_type)
48 ORDER BY lookup_code;
49
50 END get_source_lov;
51
52
53
54 PROCEDURE GET_LPN_LOV
55 (x_lpn_lov OUT NOCOPY t_genref,
56 p_lpn IN VARCHAR2
57 )
58 IS
59 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
60 BEGIN
61
62 OPEN x_lpn_lov FOR
63 SELECT license_plate_number,
64 lpn_id,
65 NVL(inventory_item_id, 0),
66 NVL(organization_id, 0),
67 revision,
68 lot_number,
69 serial_number,
70 subinventory_code,
71 NVL(locator_id, 0),
72 NVL(parent_lpn_id, 0),
73 NVL(sealed_status, 2),
74 gross_weight_uom_code,
75 NVL(gross_weight, 0),
76 content_volume_uom_code,
77 NVL(content_volume, 0)
78 FROM wms_license_plate_numbers
79 WHERE license_plate_number LIKE (p_lpn)
80 ORDER BY license_plate_number;
81
82 END GET_LPN_LOV;
83
84 PROCEDURE GET_LPN_LOV
85 (x_lpn_lov OUT NOCOPY t_genref,
86 p_lpn IN VARCHAR2,
87 p_orgid IN VARCHAR2
88 )
89 IS
90 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
91 BEGIN
92
93 OPEN x_lpn_lov FOR
94 SELECT license_plate_number,
95 lpn_id,
96 NVL(inventory_item_id, 0),
97 NVL(organization_id, 0),
98 revision,
99 lot_number,
100 serial_number,
101 subinventory_code,
102 NVL(locator_id, 0),
103 NVL(parent_lpn_id, 0),
104 NVL(sealed_status, 2),
105 gross_weight_uom_code,
106 NVL(gross_weight, 0),
107 content_volume_uom_code,
108 NVL(content_volume, 0)
109 FROM wms_license_plate_numbers
110 WHERE license_plate_number LIKE (p_lpn)
111 and organization_id LIKE (p_orgid)
112 ORDER BY license_plate_number;
113
114 END GET_LPN_LOV;
115
116
117 PROCEDURE GET_LABEL_PICK_LPN_LOV
118 (x_lpn_lov OUT NOCOPY t_genref,
119 p_lpn IN VARCHAR2,
120 p_org_id IN NUMBER,
121 p_sub_code IN VARCHAR2 DEFAULT NULL
122 )
123 IS
124 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
125 BEGIN
126
127 OPEN x_lpn_lov FOR
128 SELECT distinct wlpn.license_plate_number,
129 wlpn.lpn_id,
130 NVL(wlpn.inventory_item_id, 0),
131 NVL(wlpn.organization_id, 0),
132 wlpn.revision,
133 wlpn.lot_number,
134 wlpn.serial_number,
135 wlpn.subinventory_code,
136 NVL(wlpn.locator_id, 0),
137 NVL(wlpn.parent_lpn_id, 0),
138 NVL(wlpn.sealed_status, 2),
139 wlpn.gross_weight_uom_code,
140 NVL(wlpn.gross_weight, 0),
141 wlpn.content_volume_uom_code,
142 NVL(wlpn.content_volume, 0)
143 FROM wms_license_plate_numbers wlpn,
144 mtl_material_transactions_temp mmtt
145 WHERE wlpn.license_plate_number LIKE (p_lpn) and
146 mmtt.organization_id = p_org_id and
147 mmtt.cartonization_id = wlpn.lpn_id and
148 mmtt.subinventory_code = nvl(p_sub_code, mmtt.subinventory_code)
149 ORDER BY license_plate_number;
150
151 END GET_LABEL_PICK_LPN_LOV;
152
153 -- This LOV has been deprecated because it uses dynamic SQL. Please create
154 -- a NEW LOV if you need to use this
155
156 PROCEDURE GET_WHERE_LPN_LOV
157 (x_lpn_lov OUT NOCOPY t_genref,
158 p_lpn IN VARCHAR2,
159 p_where_clause IN VARCHAR2
160 )
161 IS
162 l_sql_stmt VARCHAR2(4000) :=
163 'SELECT DISTINCT wlpn.license_plate_number, ' ||
164 ' wlpn.lpn_id, ' ||
165 ' NVL(wlpn.inventory_item_id, 0), ' ||
166 ' NVL(wlpn.organization_id, 0), ' ||
167 ' wlpn.revision, ' ||
168 ' wlpn.lot_number, ' ||
169 ' wlpn.serial_number, ' ||
170 ' wlpn.subinventory_code, ' ||
171 ' NVL(wlpn.locator_id, 0), ' ||
172 ' NVL(wlpn.parent_lpn_id, 0), ' ||
173 ' NVL(wlpn.sealed_status, 2), ' ||
174 ' wlpn.gross_weight_uom_code, ' ||
175 ' NVL(wlpn.gross_weight, 0), ' ||
176 ' wlpn.content_volume_uom_code, ' ||
177 ' NVL(wlpn.content_volume, 0), ' ||
178 ' milk.concatenated_segments, ' ||
179 ' wlpn.lpn_context ' ||
180 'FROM wms_license_plate_numbers wlpn, ' ||
181 ' mtl_item_locations_kfv milk, ' ||
182 ' wms_lpn_contents wlc ' ||
183 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
184 ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
185 ' AND wlc.parent_lpn_id (+) = wlpn.lpn_id ';
186 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
187 BEGIN
188 l_sql_stmt := l_sql_stmt ||
189 'AND wlpn.license_plate_number LIKE :p_lpn ' ||
190 p_where_clause;
191 --dbms_output.put_line( length(l_sql_stmt) );
192 --dbms_output.put_line( Substr(l_sql_stmt, 1, 255) );
193 --dbms_output.put_line( Substr(l_sql_stmt, 256, 255));
194 --dbms_output.put_line( Substr(l_sql_stmt, 512, 255));
195 OPEN x_lpn_lov FOR l_sql_stmt USING p_lpn;
196 END GET_WHERE_LPN_LOV;
197
198 /*******************************************************************
199 WMS - PJM Integration Enhancements
200 Added a new Procedure WHERE_PJM_LPN_LOV which is similar to
201 GET_WHERE_LPN_LOV. This returns the locator concatenated segments
202 without the SEGMENT19 and SEGMENT20. Also it returns the Project
203 and Task Information
204 ********************************************************************/
205 PROCEDURE GET_WHERE_PJM_LPN_LOV
206 (x_lpn_lov OUT NOCOPY t_genref,
207 p_lpn IN VARCHAR2,
208 p_where_clause IN VARCHAR2
209 )
210 IS
211 l_sql_stmt VARCHAR2(4000) :=
212 'SELECT DISTINCT wlpn.license_plate_number, ' ||
213 ' wlpn.lpn_id, ' ||
214 ' NVL(wlpn.inventory_item_id, 0), ' ||
215 ' NVL(wlpn.organization_id, 0), ' ||
216 ' wlpn.revision, ' ||
217 ' wlpn.lot_number, ' ||
218 ' wlpn.serial_number, ' ||
219 ' wlpn.subinventory_code, ' ||
220 ' NVL(wlpn.locator_id, 0), ' ||
221 ' NVL(wlpn.parent_lpn_id, 0), ' ||
222 ' NVL(wlpn.sealed_status, 2), ' ||
223 ' wlpn.gross_weight_uom_code, ' ||
224 ' NVL(wlpn.gross_weight, 0), ' ||
225 ' wlpn.content_volume_uom_code, ' ||
226 ' NVL(wlpn.content_volume, 0), ' ||
227 ' INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id,milk.organization_id), ' ||
228 ' INV_PROJECT.GET_PROJECT_ID, ' ||
229 ' INV_PROJECT.GET_PROJECT_NUMBER, ' ||
230 ' INV_PROJECT.GET_TASK_ID, ' ||
231 ' INV_PROJECT.GET_TASK_NUMBER, ' ||
232 ' wlpn.lpn_context ' ||
233 'FROM wms_license_plate_numbers wlpn, ' ||
234 ' mtl_item_locations milk, ' ||
235 ' wms_lpn_contents wlc ' ||
236 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
237 ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
238 ' AND wlc.parent_lpn_id (+) = wlpn.lpn_id ';
239 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
240 BEGIN
241 IF (l_debug = 1) THEN
242 inv_log_util.trace('In Get_WHERE_PJM_LPN_LOV', 'WMS_LPN_LOVs',1);
243 END IF;
244 l_sql_stmt := l_sql_stmt ||
245 'AND wlpn.license_plate_number LIKE :p_lpn ' ||
246 p_where_clause;
247 OPEN x_lpn_lov FOR l_sql_stmt USING p_lpn;
248 END GET_WHERE_PJM_LPN_LOV;
249
250
251 PROCEDURE GET_PUTAWAY_WHERE_LPN_LOV
252 (x_lpn_lov OUT NOCOPY t_genref,
253 p_lpn IN VARCHAR2,
254 p_organization_id IN VARCHAR2
255 )
256 IS
257
258 l_lpn VARCHAR2(50);
259
260 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
261 BEGIN
262
263 l_lpn := p_lpn;
264
265 OPEN x_lpn_lov FOR
266
267 SELECT DISTINCT wlpn.license_plate_number,
268 wlpn.lpn_id,
269 NVL(wlpn.inventory_item_id, 0),
270 NVL(wlpn.organization_id, 0),
271 wlpn.revision,
272 wlpn.lot_number,
273 wlpn.serial_number,
274 wlpn.subinventory_code,
275 NVL(wlpn.locator_id, 0),
276 NVL(wlpn.parent_lpn_id, 0),
277 NVL(wlpn.sealed_status, 2),
278 wlpn.gross_weight_uom_code,
279 NVL(wlpn.gross_weight, 0),
280 wlpn.content_volume_uom_code,
281 NVL(wlpn.content_volume, 0),
282 milk.concatenated_segments,
283 wlpn.lpn_context
284 FROM wms_license_plate_numbers wlpn,
285 mtl_item_locations_kfv milk,
286 wms_lpn_contents wlc
287 WHERE wlpn.organization_id = To_number(p_organization_id)
288 AND wlpn.organization_id = milk.organization_id (+)
289 AND wlpn.locator_id = milk.inventory_location_id(+)
290 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
291 AND wlpn.lpn_context < 4
292 AND wlpn.license_plate_number LIKE l_lpn
293 ORDER BY wlpn.license_plate_number;
294
295 END GET_PUTAWAY_WHERE_LPN_LOV;
296
297
298 PROCEDURE GET_PICK_LOAD_TO_LPN_LOV
299 (x_lpn_lov OUT NOCOPY t_genref,
300 p_lpn IN VARCHAR2
301 )
302 IS
303
304 l_lpn VARCHAR2(50);
305
306 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
307 BEGIN
308
309 l_lpn := p_lpn;
310 OPEN x_lpn_lov FOR
311
312 SELECT DISTINCT wlpn.license_plate_number,
313 wlpn.lpn_id,
314 NVL(wlpn.inventory_item_id, 0),
315 NVL(wlpn.organization_id, 0),
316 wlpn.revision,
317 wlpn.lot_number,
318 wlpn.serial_number,
319 wlpn.subinventory_code,
320 NVL(wlpn.locator_id, 0),
321 NVL(wlpn.parent_lpn_id, 0),
322 NVL(wlpn.sealed_status, 2),
323 wlpn.gross_weight_uom_code,
324 NVL(wlpn.gross_weight, 0),
325 wlpn.content_volume_uom_code,
326 NVL(wlpn.content_volume, 0),
327 milk.concatenated_segments,
328 wlpn.lpn_context
329 FROM wms_license_plate_numbers wlpn,
330 mtl_item_locations_kfv milk,
331 wms_lpn_contents wlc
332 WHERE wlpn.organization_id = milk.organization_id (+)
333 AND wlpn.locator_id = milk.inventory_location_id(+)
334 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
335 AND (lpn_context = 8 OR lpn_context = 5)
336 AND wlpn.license_plate_number LIKE l_lpn
337 ORDER BY wlpn.license_plate_number;
338
339 END GET_PICK_LOAD_TO_LPN_LOV;
340
341 PROCEDURE validate_pick_load_to_lpn
342 (p_tolpn IN VARCHAR2,
343 x_is_valid_tolpn OUT NOCOPY VARCHAR2,
344 x_tolpn_id OUT NOCOPY NUMBER
345 )
346 IS
347 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
348
349 TYPE tolpn_record_type IS RECORD
350 (tolpn VARCHAR2(30),
351 tolpn_id NUMBER,
352 tolpn_inventory_item_id NUMBER,
353 organization_id NUMBER,
354 revision VARCHAR2(3),
355 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
356 lot_number VARCHAR2(80),
357 serial_number VARCHAR2(30),
358 subinventory_code VARCHAR2(10),
359 locator_id NUMBER,
360 parent_lpn_id NUMBER,
361 sealed_status NUMBER,
362 gross_weight_uom_code VARCHAR2(3),
363 gross_weight NUMBER,
364 content_volume_uom_code VARCHAR2(3),
365 content_volume NUMBER,
366 concatenated_segments VARCHAR2(30),
367 lpn_context NUMBER);
368
369
370 tolpn_rec tolpn_record_type;
371 l_tolpns t_genref;
372
373 BEGIN
374 x_is_valid_tolpn := 'N';
375 get_pick_load_to_lpn_lov( x_lpn_lov => l_tolpns,
376 p_lpn => p_tolpn);
377 LOOP
378 FETCH l_tolpns INTO tolpn_rec;
379 EXIT WHEN l_tolpns%notfound;
380
381 IF tolpn_rec.tolpn = p_tolpn THEN
382 x_is_valid_tolpn := 'Y';
383 x_tolpn_id := tolpn_rec.tolpn_id;
384 EXIT;
385 END IF;
386
387 END LOOP;
388
389 END validate_pick_load_to_lpn;
390
391 /* BUG#2905646 Added Project and Task to show LPN's belonging to Project and Tasks in From LPN for PJM Org's. */
392 PROCEDURE GET_PICK_LOAD_LPN_LOV
393 (x_lpn_lov OUT NOCOPY t_genref,
394 p_lpn IN VARCHAR2,
395 p_organization_id IN NUMBER,
396 p_revision IN VARCHAR2,
397 p_inventory_item_id IN NUMBER,
398 p_cost_group_id IN NUMBER,
399 p_subinventory_code IN VARCHAR2,
400 p_locator_id IN NUMBER,
401 p_project_id IN NUMBER := NULL,
402 p_task_id IN NUMBER := NULL
403 )
404 IS
405
406 l_lpn VARCHAR2(50);
407 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
408
409 BEGIN
410
411 l_lpn := p_lpn;
412
413 OPEN x_lpn_lov FOR
414
415 SELECT DISTINCT wlpn.license_plate_number,
416 wlpn.lpn_id,
417 NVL(wlpn.inventory_item_id, 0),
418 NVL(wlpn.organization_id, 0),
419 wlpn.revision,
420 wlpn.lot_number,
421 wlpn.serial_number,
422 wlpn.subinventory_code,
423 NVL(wlpn.locator_id, 0),
424 NVL(wlpn.parent_lpn_id, 0),
425 NVL(wlpn.sealed_status, 2),
426 wlpn.gross_weight_uom_code,
427 NVL(wlpn.gross_weight, 0),
428 wlpn.content_volume_uom_code,
429 NVL(wlpn.content_volume, 0),
430 milk.concatenated_segments,
431 wlpn.lpn_context
432 FROM wms_license_plate_numbers wlpn,
433 mtl_item_locations_kfv milk,
434 wms_lpn_contents wlc
435 WHERE wlpn.organization_id = milk.organization_id (+)
436 AND wlpn.locator_id = milk.inventory_location_id(+)
437 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
438 AND wlpn.license_plate_number LIKE l_lpn
439 AND lpn_context = 1
440 AND wlpn.organization_id = p_organization_id
441 AND Nvl(wlc.revision, '-999') = Nvl(p_revision, '-999')
442 AND wlc.inventory_item_id = p_inventory_item_id
443 --AND wlc.cost_group_id = nvl(l_cost_group_id, wlc.cost_group_id)
444 AND wlpn.subinventory_code = p_subinventory_code
445 AND wlpn.locator_id = p_locator_id
446 -- PJM changes: Bug 2774506/2905646 : Added project_id and task_id to show LPN's belonging to PJM locators.
447 AND ( wlpn.locator_id IS NULL OR
448 wlpn.locator_id IN
449 (SELECT DISTINCT mil.inventory_location_id
450 FROM mtl_item_locations mil
451 WHERE NVL(mil.project_id, -1) = NVL(p_project_id, -1)
452 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1))
453 )
454 ORDER BY wlpn.license_plate_number;
455
456
457 END GET_PICK_LOAD_LPN_LOV;
458
459 PROCEDURE GET_ALL_APL_LPN_LOV
460 (x_lpn_lov OUT NOCOPY t_genref,
461 p_lpn IN VARCHAR2,
462 p_organization_id IN NUMBER,
463 p_revision IN VARCHAR2,
464 p_inventory_item_id IN NUMBER,
465 p_project_id IN NUMBER := NULL,
466 p_task_id IN NUMBER := NULL
467 )
468 IS
469
470 l_lpn VARCHAR2(50);
471 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
472
473 BEGIN
474
475 l_lpn := p_lpn;
476
477 OPEN x_lpn_lov FOR
478
479 SELECT DISTINCT wlpn.license_plate_number,
480 wlpn.lpn_id,
481 NVL(wlpn.inventory_item_id, 0),
482 NVL(wlpn.organization_id, 0),
483 wlpn.revision,
484 wlpn.lot_number,
485 wlpn.serial_number,
486 wlpn.subinventory_code,
487 NVL(wlpn.locator_id, 0),
488 NVL(wlpn.parent_lpn_id, 0),
489 NVL(wlpn.sealed_status, 2),
490 wlpn.gross_weight_uom_code,
491 NVL(wlpn.gross_weight, 0),
492 wlpn.content_volume_uom_code,
493 NVL(wlpn.content_volume, 0),
494 milk.concatenated_segments,
495 wlpn.lpn_context
496 FROM wms_license_plate_numbers wlpn,
497 mtl_item_locations_kfv milk,
498 wms_lpn_contents wlc
499 WHERE wlpn.organization_id = milk.organization_id (+)
500 AND wlpn.locator_id = milk.inventory_location_id(+)
501 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
502 AND wlpn.license_plate_number LIKE l_lpn
503 AND lpn_context = 1
504 AND wlpn.organization_id = p_organization_id
505 AND Nvl(wlc.revision, '-999') = Nvl(p_revision, '-999')
506 AND wlc.inventory_item_id = p_inventory_item_id
507 AND ( wlpn.locator_id IS NULL OR
508 wlpn.locator_id IN
509 (SELECT DISTINCT mil.inventory_location_id
510 FROM mtl_item_locations mil
511 WHERE NVL(mil.project_id, -1) = NVL(p_project_id, -1)
512 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1))
513 )
514 ORDER BY wlpn.license_plate_number;
515
516
517 END GET_ALL_APL_LPN_LOV;
518
519 PROCEDURE GET_SUB_APL_LPN_LOV
520 (x_lpn_lov OUT NOCOPY t_genref,
521 p_lpn IN VARCHAR2,
522 p_organization_id IN NUMBER,
523 p_revision IN VARCHAR2,
524 p_inventory_item_id IN NUMBER,
525 p_subinventory_code IN VARCHAR2,
526 p_project_id IN NUMBER := NULL,
527 p_task_id IN NUMBER := NULL
528 )
529 IS
530
531 l_lpn VARCHAR2(50);
532 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
533
534 BEGIN
535
536 l_lpn := p_lpn;
537
538 OPEN x_lpn_lov FOR
539
540 SELECT DISTINCT wlpn.license_plate_number,
541 wlpn.lpn_id,
542 NVL(wlpn.inventory_item_id, 0),
543 NVL(wlpn.organization_id, 0),
544 wlpn.revision,
545 wlpn.lot_number,
546 wlpn.serial_number,
547 wlpn.subinventory_code,
548 NVL(wlpn.locator_id, 0),
549 NVL(wlpn.parent_lpn_id, 0),
550 NVL(wlpn.sealed_status, 2),
551 wlpn.gross_weight_uom_code,
552 NVL(wlpn.gross_weight, 0),
553 wlpn.content_volume_uom_code,
554 NVL(wlpn.content_volume, 0),
555 milk.concatenated_segments,
556 wlpn.lpn_context
557 FROM wms_license_plate_numbers wlpn,
558 mtl_item_locations_kfv milk,
559 wms_lpn_contents wlc
560 WHERE wlpn.organization_id = milk.organization_id (+)
561 AND wlpn.locator_id = milk.inventory_location_id(+)
562 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
563 AND wlpn.license_plate_number LIKE l_lpn
564 AND lpn_context = 1
565 AND wlpn.organization_id = p_organization_id
566 AND Nvl(wlc.revision, '-999') = Nvl(p_revision, '-999')
567 AND wlc.inventory_item_id = p_inventory_item_id
568 AND wlpn.subinventory_code = p_subinventory_code
569 AND ( wlpn.locator_id IS NULL OR
570 wlpn.locator_id IN
571 (SELECT DISTINCT mil.inventory_location_id
572 FROM mtl_item_locations mil
573 WHERE NVL(mil.project_id, -1) = NVL(p_project_id, -1)
574 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1))
575 )
576 ORDER BY wlpn.license_plate_number;
577
578 END GET_SUB_APL_LPN_LOV;
579
580 PROCEDURE validate_pick_load_lpn_lov
581 (p_fromlpn IN VARCHAR2,
582 p_organization_id IN NUMBER,
583 p_revision IN VARCHAR2,
584 p_inventory_item_id IN NUMBER,
585 p_cost_group_id IN NUMBER,
586 p_subinventory_code IN VARCHAR2,
587 p_locator_id IN NUMBER,
588 p_project_id IN NUMBER := NULL,
589 p_task_id IN NUMBER := NULL,
590 p_transaction_temp_id IN NUMBER,
591 p_serial_allocated IN VARCHAR2,
592 x_is_valid_fromlpn OUT NOCOPY VARCHAR2,
593 x_fromlpn_id OUT NOCOPY NUMBER)
594
595 IS
596 TYPE fromlpn_record_type IS RECORD
597 (license_plate_number VARCHAR2(30),
598 lpn_id NUMBER,
599 inventory_item_id NUMBER,
600 organization_id NUMBER,
601 revision VARCHAR2(3),
602 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
603 lot_number VARCHAR2(80),
604 serial_number VARCHAR2(30),
605 subinventory_code VARCHAR2(10),
606 locator_id NUMBER,
607 parent_lpn_id NUMBER,
608 sealed_status NUMBER,
609 gross_weight_uom_code VARCHAR2(3),
610 gross_weight NUMBER,
611 content_volume_uom_code VARCHAR2(3),
612 content_volume NUMBER,
613 concatenated_segments VARCHAR2(204),
614 lpn_context NUMBER);
615
616 fromlpn_rec fromlpn_record_type;
617 l_fromlpns t_genref;
618 l_project_id NUMBER;
619 l_task_id NUMBER;
620
621 BEGIN
622
623 x_is_valid_fromlpn := 'N';
624
625 IF p_project_id IS NOT NULL THEN
626 IF p_project_id = 0 THEN
627 l_project_id := NULL;
628 END IF;
629 ELSE
630 l_project_id := NULL;
631 END IF;
632
633 IF p_task_id IS NOT NULL THEN
634 IF p_task_id = 0 THEN
635 l_task_id := NULL;
636 END IF;
637 ELSE
638 l_task_id := NULL;
639 END IF;
640
641 IF p_serial_allocated = 'Y' THEN
642
643 GET_PICK_LOAD_SERIAL_LPN_LOV
644 (x_lpn_lov => l_fromlpns,
645 p_lpn => p_fromlpn,
646 p_organization_id => p_organization_id,
647 p_revision => p_revision,
648 p_inventory_item_id => p_inventory_item_id,
649 p_cost_group_id => p_cost_group_id,
650 p_subinventory_code => p_subinventory_code,
651 p_locator_id => p_locator_id,
652 p_transaction_temp_id => p_transaction_temp_id);
653
654 ELSE
655 GET_PICK_LOAD_LPN_LOV
656 (x_lpn_lov => l_fromlpns,
657 p_lpn => p_fromlpn,
658 p_organization_id => p_organization_id,
659 p_revision => p_revision,
660 p_inventory_item_id => p_inventory_item_id,
661 p_cost_group_id => p_cost_group_id,
662 p_subinventory_code => p_subinventory_code,
663 p_locator_id => p_locator_id,
664 p_project_id => l_project_id,
665 p_task_id => l_task_id);
666 END IF;
667
668
669 LOOP
670 FETCH l_fromlpns INTO fromlpn_rec;
671 EXIT WHEN l_fromlpns%notfound;
672
673 IF fromlpn_rec.license_plate_number = p_fromlpn THEN
674 x_is_valid_fromlpn := 'Y';
675 x_fromlpn_id := fromlpn_rec.lpn_id;
676 EXIT;
677 END IF;
678
679 END LOOP;
680
681 END validate_pick_load_lpn_lov;
682
683
684 PROCEDURE GET_PICK_DROP_LPN_LOV
685 ( x_lpn_lov OUT NOCOPY t_genref
686 , p_lpn IN VARCHAR2
687 , p_pick_to_lpn_id IN NUMBER
688 , p_org_id IN NUMBER
689 , p_drop_sub IN VARCHAR2
690 , p_drop_loc IN NUMBER
691 )
692 -- passed p_drop_sub and p_drop_loc --vipartha
693 IS
694 l_lpn VARCHAR2(50);
695 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
696
697 BEGIN
698
699 IF p_lpn IS NOT NULL then
700 l_lpn := p_lpn;
701 ELSE
702 l_lpn := '%';
703 END IF;
704
705 OPEN x_lpn_lov FOR
706 SELECT DISTINCT wlpn.license_plate_number
707 , wlpn.lpn_id
708 , NVL(wlpn.inventory_item_id, 0)
709 , NVL(wlpn.organization_id, 0)
710 , wlpn.revision
711 , wlpn.lot_number
712 , wlpn.serial_number
713 , wlpn.subinventory_code
714 , NVL(wlpn.locator_id, 0)
715 , NVL(wlpn.parent_lpn_id, 0)
716 , NVL(wlpn.sealed_status, 2)
717 , wlpn.gross_weight_uom_code
718 , NVL(wlpn.gross_weight, 0)
719 , wlpn.content_volume_uom_code
720 , NVL(wlpn.content_volume, 0)
721 , milk.concatenated_segments
722 , wlpn.lpn_context
723 FROM wms_license_plate_numbers wlpn
724 , mtl_item_locations_kfv milk
725 WHERE wlpn.organization_id = milk.organization_id (+)
726 AND wlpn.locator_id = milk.inventory_location_id (+)
727 AND wlpn.outermost_lpn_id = wlpn.lpn_id
728 AND wlpn.lpn_context = 11
729 AND wlpn.subinventory_code = p_drop_sub
730 AND wlpn.locator_id = p_drop_loc
731 AND wlpn.license_plate_number LIKE l_lpn
732 AND WMS_task_dispatch_gen.validate_pick_drop_lpn
733 ( 1.0
734 , 'F'
735 , p_pick_to_lpn_id
736 , p_org_id
737 , wlpn.license_plate_number
738 , p_drop_sub
739 , p_drop_loc
740 ) = 1
741 ORDER BY license_plate_number;
742
743 END GET_PICK_DROP_LPN_LOV;
744
745
746 -- This LOV has been deprecated because it uses dynamic SQL. Please create
747 -- a NEW LOV if you need to use this
748
749 PROCEDURE GET_WHERE_SERIAL_LPN_LOV
750 (x_lpn_lov OUT NOCOPY t_genref,
751 p_lpn IN VARCHAR2,
752 p_where_clause IN VARCHAR2
753 )
754 IS
755 l_sql_stmt VARCHAR2(4000) :=
756 'SELECT DISTINCT wlpn.license_plate_number, ' ||
757 ' wlpn.lpn_id, ' ||
758 ' NVL(wlpn.inventory_item_id, 0), ' ||
759 ' NVL(wlpn.organization_id, 0), ' ||
760 ' wlpn.revision, ' ||
761 ' wlpn.lot_number, ' ||
762 ' wlpn.serial_number, ' ||
763 ' wlpn.subinventory_code, ' ||
764 ' NVL(wlpn.locator_id, 0), ' ||
765 ' NVL(wlpn.parent_lpn_id, 0), ' ||
766 ' NVL(wlpn.sealed_status, 2), ' ||
767 ' wlpn.gross_weight_uom_code, ' ||
768 ' NVL(wlpn.gross_weight, 0), ' ||
769 ' wlpn.content_volume_uom_code, ' ||
770 ' NVL(wlpn.content_volume, 0), ' ||
771 ' milk.concatenated_segments, ' ||
772 ' wlpn.lpn_context ' ||
773 'FROM wms_license_plate_numbers wlpn, ' ||
774 ' mtl_item_locations_kfv milk, ' ||
775 ' wms_lpn_contents wlc, ' ||
776 ' mtl_serial_numbers msn, ' ||
777 ' mtl_serial_numbers_temp msnt ' ||
778 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
779 ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
780 ' AND wlc.parent_lpn_id (+) = wlpn.lpn_id ' ||
781 ' AND msn.serial_number = msnt.fm_serial_number ' ||
782 ' AND msn.lpn_id = wlpn.lpn_id ';
783 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
784 BEGIN
785 l_sql_stmt := l_sql_stmt ||
786 ' AND wlpn.license_plate_number LIKE :p_lpn ' ||
787 p_where_clause;
788
789 OPEN x_lpn_lov FOR l_sql_stmt USING p_lpn;
790 END GET_WHERE_SERIAL_LPN_LOV;
791
792
793 PROCEDURE GET_PICK_LOAD_SERIAL_LPN_LOV
794 (x_lpn_lov OUT NOCOPY t_genref,
795 p_lpn IN VARCHAR2,
796 p_organization_id IN NUMBER,
797 p_revision IN VARCHAR2,
798 p_inventory_item_id IN NUMBER,
799 p_cost_group_id IN NUMBER,
800 p_subinventory_code IN VARCHAR2,
801 p_locator_id IN NUMBER,
802 p_transaction_temp_id IN NUMBER
803 )
804 IS
805
806 l_lpn VARCHAR2(50);
807 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
808 BEGIN
809
810 l_lpn := p_lpn;
811
812 OPEN x_lpn_lov FOR
813
814 SELECT DISTINCT wlpn.license_plate_number,
815 wlpn.lpn_id,
816 NVL(wlpn.inventory_item_id, 0),
817 NVL(wlpn.organization_id, 0),
818 wlpn.revision,
819 wlpn.lot_number,
820 wlpn.serial_number,
821 wlpn.subinventory_code,
822 NVL(wlpn.locator_id, 0),
823 NVL(wlpn.parent_lpn_id, 0),
824 NVL(wlpn.sealed_status, 2),
825 wlpn.gross_weight_uom_code,
826 NVL(wlpn.gross_weight, 0),
827 wlpn.content_volume_uom_code,
828 NVL(wlpn.content_volume, 0),
829 milk.concatenated_segments,
830 wlpn.lpn_context
831 FROM wms_license_plate_numbers wlpn,
832 mtl_item_locations_kfv milk,
833 wms_lpn_contents wlc,
834 mtl_serial_numbers msn,
835 mtl_serial_numbers_temp msnt
836 WHERE wlpn.organization_id = milk.organization_id (+)
837 AND wlpn.locator_id = milk.inventory_location_id(+)
838 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
839 AND msn.serial_number = msnt.fm_serial_number
840 AND msn.lpn_id = wlpn.lpn_id
841 AND wlpn.license_plate_number LIKE l_lpn
842 AND lpn_context = 1
843 AND wlpn.organization_id = p_organization_id
844 AND Nvl(wlc.revision, '-999') = Nvl(p_revision, '-999')
845 AND wlc.inventory_item_id = p_inventory_item_id
846 -- AND wlc.cost_group_id = nvl(l_cost_group_id, wlc.cost_group_id) --bug 2748240
847 AND wlpn.subinventory_code = p_subinventory_code
848 AND wlpn.locator_id = p_locator_id
849 AND msnt.transaction_temp_id = p_transaction_temp_id
850 UNION
851 SELECT DISTINCT wlpn.license_plate_number,
852 wlpn.lpn_id,
853 NVL(wlpn.inventory_item_id, 0),
854 NVL(wlpn.organization_id, 0),
855 wlpn.revision,
856 wlpn.lot_number,
857 wlpn.serial_number,
858 wlpn.subinventory_code,
859 NVL(wlpn.locator_id, 0),
860 NVL(wlpn.parent_lpn_id, 0),
861 NVL(wlpn.sealed_status, 2),
862 wlpn.gross_weight_uom_code,
863 NVL(wlpn.gross_weight, 0),
864 wlpn.content_volume_uom_code,
865 NVL(wlpn.content_volume, 0),
866 milk.concatenated_segments,
867 wlpn.lpn_context
868 FROM wms_license_plate_numbers wlpn,
869 mtl_item_locations_kfv milk,
870 wms_lpn_contents wlc,
871 mtl_serial_numbers msn,
872 mtl_serial_numbers_temp msnt,
873 mtl_transaction_lots_temp mtlt,
874 mtl_material_transactions_temp mmtt
875 WHERE wlpn.organization_id = milk.organization_id (+)
876 AND wlpn.locator_id = milk.inventory_location_id(+)
877 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
878 AND msn.serial_number = msnt.fm_serial_number
879 AND msn.lpn_id = wlpn.lpn_id
880 AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
881 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
882 AND wlpn.license_plate_number LIKE l_lpn
883 AND lpn_context = 1
884 AND wlpn.organization_id = p_organization_id
885 AND Nvl(wlc.revision, '-999') = Nvl(p_revision, '-999')
886 AND wlc.inventory_item_id = p_inventory_item_id
887 -- AND wlc.cost_group_id = nvl(l_cost_group_id, wlc.cost_group_id) -- bug 2748240
888 AND wlpn.subinventory_code = p_subinventory_code
889 AND wlpn.locator_id = p_locator_id
890 AND mmtt.transaction_temp_id = p_transaction_temp_id
891 ORDER BY license_plate_number;
892
893 END GET_PICK_LOAD_SERIAL_LPN_LOV;
894
895
896 -- Bug 3452436 : Added for patchset J project Advanced Pick Load.
897 -- This LOV fetches all the LPN in the given Org, containing the givn Item
898 -- and allocated serials
899 PROCEDURE GET_ALL_APL_SERIAL_LPN_LOV
900 (x_lpn_lov OUT NOCOPY t_genref,
901 p_lpn IN VARCHAR2,
902 p_organization_id IN NUMBER,
903 p_revision IN VARCHAR2,
904 p_inventory_item_id IN NUMBER,
905 p_transaction_temp_id IN NUMBER
906 )
907 IS
908
909 l_lpn VARCHAR2(50);
910 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
911 BEGIN
912
913 l_lpn := p_lpn;
914
915 OPEN x_lpn_lov FOR
916
917 SELECT DISTINCT wlpn.license_plate_number,
918 wlpn.lpn_id,
919 NVL(wlpn.inventory_item_id, 0),
920 NVL(wlpn.organization_id, 0),
921 wlpn.revision,
922 wlpn.lot_number,
923 wlpn.serial_number,
924 wlpn.subinventory_code,
925 NVL(wlpn.locator_id, 0),
926 NVL(wlpn.parent_lpn_id, 0),
927 NVL(wlpn.sealed_status, 2),
928 wlpn.gross_weight_uom_code,
929 NVL(wlpn.gross_weight, 0),
930 wlpn.content_volume_uom_code,
931 NVL(wlpn.content_volume, 0),
932 milk.concatenated_segments,
933 wlpn.lpn_context
934 FROM wms_license_plate_numbers wlpn,
935 mtl_item_locations_kfv milk,
936 wms_lpn_contents wlc,
937 mtl_serial_numbers msn,
938 mtl_serial_numbers_temp msnt
939 WHERE wlpn.organization_id = milk.organization_id (+)
940 AND wlpn.locator_id = milk.inventory_location_id(+)
941 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
942 AND msn.serial_number = msnt.fm_serial_number
943 AND msn.lpn_id = wlpn.lpn_id
944 AND wlpn.license_plate_number LIKE l_lpn
945 AND lpn_context = 1
946 AND wlpn.organization_id = p_organization_id
947 AND Nvl(wlc.revision, '-999') = Nvl(p_revision, '-999')
948 AND wlc.inventory_item_id = p_inventory_item_id
949 AND msnt.transaction_temp_id = p_transaction_temp_id
950 UNION
951 SELECT DISTINCT wlpn.license_plate_number,
952 wlpn.lpn_id,
953 NVL(wlpn.inventory_item_id, 0),
954 NVL(wlpn.organization_id, 0),
955 wlpn.revision,
956 wlpn.lot_number,
957 wlpn.serial_number,
958 wlpn.subinventory_code,
959 NVL(wlpn.locator_id, 0),
960 NVL(wlpn.parent_lpn_id, 0),
961 NVL(wlpn.sealed_status, 2),
962 wlpn.gross_weight_uom_code,
963 NVL(wlpn.gross_weight, 0),
964 wlpn.content_volume_uom_code,
965 NVL(wlpn.content_volume, 0),
966 milk.concatenated_segments,
967 wlpn.lpn_context
968 FROM wms_license_plate_numbers wlpn,
969 mtl_item_locations_kfv milk,
970 wms_lpn_contents wlc,
971 mtl_serial_numbers msn,
972 mtl_serial_numbers_temp msnt,
973 mtl_transaction_lots_temp mtlt,
974 mtl_material_transactions_temp mmtt
975 WHERE wlpn.organization_id = milk.organization_id (+)
976 AND wlpn.locator_id = milk.inventory_location_id(+)
977 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
978 AND msn.serial_number = msnt.fm_serial_number
979 AND msn.lpn_id = wlpn.lpn_id
980 AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
981 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
982 AND wlpn.license_plate_number LIKE l_lpn
983 AND lpn_context = 1
984 AND wlpn.organization_id = p_organization_id
985 AND Nvl(wlc.revision, '-999') = Nvl(p_revision, '-999')
986 AND wlc.inventory_item_id = p_inventory_item_id
987 AND mmtt.transaction_temp_id = p_transaction_temp_id
988 ORDER BY license_plate_number;
989
990 END GET_ALL_APL_SERIAL_LPN_LOV;
991
992 -- Bug 3452436 : Added for patchset J project Advanced Pick Load.
993 -- This LOV fetches all the LPN in the given Org, sub, containing the givn Item
994 -- and allocated serials
995 PROCEDURE GET_SUB_APL_SERIAL_LPN_LOV
996 (x_lpn_lov OUT NOCOPY t_genref,
997 p_lpn IN VARCHAR2,
998 p_organization_id IN NUMBER,
999 p_revision IN VARCHAR2,
1000 p_inventory_item_id IN NUMBER,
1001 p_subinventory_code IN VARCHAR2,
1002 p_transaction_temp_id IN NUMBER
1003 )
1004 IS
1005
1006 l_lpn VARCHAR2(50);
1007 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1008 BEGIN
1009
1010 l_lpn := p_lpn;
1011
1012 OPEN x_lpn_lov FOR
1013
1014 SELECT DISTINCT wlpn.license_plate_number,
1015 wlpn.lpn_id,
1016 NVL(wlpn.inventory_item_id, 0),
1017 NVL(wlpn.organization_id, 0),
1018 wlpn.revision,
1019 wlpn.lot_number,
1020 wlpn.serial_number,
1021 wlpn.subinventory_code,
1022 NVL(wlpn.locator_id, 0),
1023 NVL(wlpn.parent_lpn_id, 0),
1024 NVL(wlpn.sealed_status, 2),
1025 wlpn.gross_weight_uom_code,
1026 NVL(wlpn.gross_weight, 0),
1027 wlpn.content_volume_uom_code,
1028 NVL(wlpn.content_volume, 0),
1029 milk.concatenated_segments,
1030 wlpn.lpn_context
1031 FROM wms_license_plate_numbers wlpn,
1032 mtl_item_locations_kfv milk,
1033 wms_lpn_contents wlc,
1034 mtl_serial_numbers msn,
1035 mtl_serial_numbers_temp msnt
1036 WHERE wlpn.organization_id = milk.organization_id (+)
1037 AND wlpn.locator_id = milk.inventory_location_id(+)
1038 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
1039 AND msn.serial_number = msnt.fm_serial_number
1040 AND msn.lpn_id = wlpn.lpn_id
1041 AND wlpn.license_plate_number LIKE l_lpn
1042 AND lpn_context = 1
1043 AND wlpn.organization_id = p_organization_id
1044 AND Nvl(wlc.revision, '-999') = Nvl(p_revision, '-999')
1045 AND wlc.inventory_item_id = p_inventory_item_id
1046 AND wlpn.subinventory_code = p_subinventory_code
1047 AND msnt.transaction_temp_id = p_transaction_temp_id
1048 UNION
1049 SELECT DISTINCT wlpn.license_plate_number,
1050 wlpn.lpn_id,
1051 NVL(wlpn.inventory_item_id, 0),
1052 NVL(wlpn.organization_id, 0),
1053 wlpn.revision,
1054 wlpn.lot_number,
1055 wlpn.serial_number,
1056 wlpn.subinventory_code,
1057 NVL(wlpn.locator_id, 0),
1058 NVL(wlpn.parent_lpn_id, 0),
1059 NVL(wlpn.sealed_status, 2),
1060 wlpn.gross_weight_uom_code,
1061 NVL(wlpn.gross_weight, 0),
1062 wlpn.content_volume_uom_code,
1063 NVL(wlpn.content_volume, 0),
1064 milk.concatenated_segments,
1065 wlpn.lpn_context
1066 FROM wms_license_plate_numbers wlpn,
1067 mtl_item_locations_kfv milk,
1068 wms_lpn_contents wlc,
1069 mtl_serial_numbers msn,
1070 mtl_serial_numbers_temp msnt,
1071 mtl_transaction_lots_temp mtlt,
1072 mtl_material_transactions_temp mmtt
1073 WHERE wlpn.organization_id = milk.organization_id (+)
1074 AND wlpn.locator_id = milk.inventory_location_id(+)
1075 AND wlc.parent_lpn_id (+) = wlpn.lpn_id
1076 AND msn.serial_number = msnt.fm_serial_number
1077 AND msn.lpn_id = wlpn.lpn_id
1078 AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
1079 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1080 AND wlpn.license_plate_number LIKE l_lpn
1081 AND lpn_context = 1
1082 AND wlpn.organization_id = p_organization_id
1083 AND Nvl(wlc.revision, '-999') = Nvl(p_revision, '-999')
1084 AND wlc.inventory_item_id = p_inventory_item_id
1085 AND wlpn.subinventory_code = p_subinventory_code
1086 AND mmtt.transaction_temp_id = p_transaction_temp_id
1087 ORDER BY license_plate_number;
1088
1089 END GET_SUB_APL_SERIAL_LPN_LOV;
1090
1091
1092
1093
1094 PROCEDURE GET_PHYINV_PARENT_LPN_LOV
1095 (x_lpn_lov OUT NOCOPY t_genref ,
1096 p_lpn IN VARCHAR2 ,
1097 p_dynamic_entry_flag IN NUMBER ,
1098 p_physical_inventory_id IN NUMBER ,
1099 p_organization_id IN NUMBER ,
1100 p_subinventory_code IN VARCHAR2 ,
1101 p_locator_id IN NUMBER ,
1102 p_project_id IN NUMBER := NULL,
1103 p_task_id IN NUMBER := NULL
1104 )
1105 IS
1106 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1107 BEGIN
1108
1109 IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
1110 -- Select all LPN's which exist in the given org, sub, loc
1111 OPEN x_lpn_lov FOR
1112 SELECT license_plate_number,
1113 lpn_id,
1114 inventory_item_id,
1115 organization_id,
1116 revision,
1117 lot_number,
1118 serial_number,
1119 subinventory_code,
1120 locator_id,
1121 parent_lpn_id,
1122 NVL(sealed_status, 2),
1123 gross_weight_uom_code,
1124 NVL(gross_weight, 0),
1125 content_volume_uom_code,
1126 NVL(content_volume, 0),
1127 lpn_context -- Added for resolution of Bug# 4349304, The LPN Context is required by the LOVs called
1128 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
1129 --organization, whether the LPN is "Issued out of Stores".
1130 FROM wms_license_plate_numbers
1131 WHERE organization_id = p_organization_id
1132 AND subinventory_code = p_subinventory_code
1133 AND lpn_context not in ( 4,6) --Bug#4267956.Added 6
1134 AND Nvl(locator_id, -99999) = Nvl(p_locator_id, -99999)
1135 -- PJM Changes
1136 AND ( locator_id IS NULL OR
1137 locator_id IN
1138 (SELECT DISTINCT mil.inventory_location_id
1139 FROM mtl_item_locations mil
1140 WHERE NVL(mil.project_id, -1) = NVL(p_project_id, -1)
1141 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1))
1142 )
1143 AND license_plate_number LIKE (p_lpn)
1144 ORDER BY license_plate_number;
1145 ELSE -- Dynamic entries are not allowed
1146 -- Select only LPN's that exist in table MTL_PHYSICAL_INVENTORY_TAGS
1147 OPEN x_lpn_lov FOR
1148 SELECT UNIQUE wlpn.license_plate_number,
1149 wlpn.lpn_id,
1150 wlpn.inventory_item_id,
1151 wlpn.organization_id,
1152 wlpn.revision,
1153 wlpn.lot_number,
1154 wlpn.serial_number,
1155 wlpn.subinventory_code,
1156 wlpn.locator_id,
1157 wlpn.parent_lpn_id,
1158 NVL(wlpn.sealed_status, 2),
1159 wlpn.gross_weight_uom_code,
1160 NVL(wlpn.gross_weight, 0),
1161 wlpn.content_volume_uom_code,
1162 NVL(wlpn.content_volume, 0),
1163 wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
1164 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
1165 --organization, whether the LPN is "Issued out of Stores".
1166 FROM wms_license_plate_numbers wlpn,
1167 mtl_physical_inventory_tags mpit
1168 WHERE wlpn.organization_id = p_organization_id
1169 AND wlpn.subinventory_code = p_subinventory_code
1170 -- Bug# 1609449
1171 --AND Nvl(wlpn.locator_id, -99999) = Nvl(p_locator_id, -99999)
1172 AND wlpn.license_plate_number LIKE (p_lpn)
1173 AND wlpn.lpn_id = mpit.parent_lpn_id
1174 AND wlpn.lpn_context not in ( 4,6) --Bug#4267956.Added 6
1175 AND mpit.organization_id = p_organization_id
1176 AND mpit.physical_inventory_id = p_physical_inventory_id
1177 AND mpit.subinventory = p_subinventory_code
1178 AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
1179 -- PJM Changes
1180 AND ( mpit.locator_id IS NULL OR
1181 mpit.locator_id IN
1182 (SELECT DISTINCT mil.inventory_location_id
1183 FROM mtl_item_locations mil
1184 WHERE NVL(mil.project_id, -1) = NVL(p_project_id, -1)
1185 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1))
1186 )
1187 AND NVL(mpit.void_flag, 2) = 2
1188 AND mpit.adjustment_id IN
1189 (SELECT adjustment_id
1190 FROM mtl_physical_adjustments
1191 WHERE physical_inventory_id = p_physical_inventory_id
1192 AND organization_id = p_organization_id
1193 AND approval_status IS NULL);
1194 END IF;
1195
1196 END GET_PHYINV_PARENT_LPN_LOV;
1197
1198
1199 PROCEDURE GET_PHYINV_LPN_LOV
1200 (x_lpn_lov OUT NOCOPY t_genref ,
1201 p_lpn IN VARCHAR2 ,
1202 p_dynamic_entry_flag IN NUMBER ,
1203 p_physical_inventory_id IN NUMBER ,
1204 p_organization_id IN NUMBER ,
1205 p_subinventory_code IN VARCHAR2 ,
1206 p_locator_id IN NUMBER ,
1207 p_parent_lpn_id IN NUMBER ,
1208 p_project_id IN NUMBER := NULL,
1209 p_task_id IN NUMBER := NULL
1210 )
1211 IS
1212 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1213 BEGIN
1214
1215 IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
1216 -- Select all LPN's which exist in the given org, sub, loc
1217 OPEN x_lpn_lov FOR
1218 SELECT license_plate_number,
1219 lpn_id,
1220 inventory_item_id,
1221 organization_id,
1222 revision,
1223 lot_number,
1224 serial_number,
1225 subinventory_code,
1226 locator_id,
1227 parent_lpn_id,
1228 NVL(sealed_status, 2),
1229 gross_weight_uom_code,
1230 NVL(gross_weight, 0),
1231 content_volume_uom_code,
1232 NVL(content_volume, 0)
1233 lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
1234 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
1235 --organization, whether the LPN is "Issued out of Stores".
1236 FROM wms_license_plate_numbers
1237 WHERE organization_id = p_organization_id
1238 AND subinventory_code = p_subinventory_code
1239 AND lpn_context not in ( 4,6) --Bug#4267956.Added 6
1240 AND Nvl(locator_id, -99999) = Nvl(p_locator_id, -99999)
1241 -- PJM Changes
1242 AND ( locator_id IS NULL OR
1243 locator_id IN
1244 (SELECT DISTINCT mil.inventory_location_id
1245 FROM mtl_item_locations mil
1246 WHERE NVL(mil.project_id, -1) = NVL(p_project_id, -1)
1247 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1))
1248 )
1249 AND license_plate_number LIKE (p_lpn)
1250 AND parent_lpn_id = p_parent_lpn_id
1251 ORDER BY license_plate_number;
1252 ELSE -- Dynamic entries are not allowed
1253 -- Select only LPN's that exist in table MTL_PHYSICAL_INVENTORY_TAGS
1254 OPEN x_lpn_lov FOR
1255 SELECT UNIQUE wlpn.license_plate_number,
1256 wlpn.lpn_id,
1257 wlpn.inventory_item_id,
1258 wlpn.organization_id,
1259 wlpn.revision,
1260 wlpn.lot_number,
1261 wlpn.serial_number,
1262 wlpn.subinventory_code,
1263 wlpn.locator_id,
1264 wlpn.parent_lpn_id,
1265 NVL(wlpn.sealed_status, 2),
1266 wlpn.gross_weight_uom_code,
1267 NVL(wlpn.gross_weight, 0),
1268 wlpn.content_volume_uom_code,
1269 NVL(wlpn.content_volume, 0),
1270 wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
1271 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
1272 --organization, whether the LPN is "Issued out of Stores".
1273 FROM wms_license_plate_numbers wlpn,
1274 mtl_physical_inventory_tags mpit
1275 WHERE wlpn.organization_id = p_organization_id
1276 AND wlpn.subinventory_code = p_subinventory_code
1277 -- Bug# 1609449
1278 -- AND Nvl(wlpn.locator_id, -99999) = Nvl(p_locator_id, -99999)
1279 AND wlpn.license_plate_number LIKE (p_lpn)
1280 AND wlpn.parent_lpn_id = p_parent_lpn_id
1281 AND wlpn.lpn_id = mpit.parent_lpn_id
1282 AND wlpn.lpn_context not in ( 4,6) --Bug#4267956.Added 6
1283 AND mpit.organization_id = p_organization_id
1284 AND mpit.physical_inventory_id = p_physical_inventory_id
1285 AND mpit.subinventory = p_subinventory_code
1286 AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
1287 AND NVL(mpit.void_flag, 2) = 2
1288 -- PJM Changes
1289 AND ( mpit.locator_id IS NULL OR
1290 mpit.locator_id IN
1291 (SELECT DISTINCT mil.inventory_location_id
1292 FROM mtl_item_locations mil
1293 WHERE NVL(mil.project_id, -1) = NVL(p_project_id, -1)
1294 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1))
1295 )
1296 AND mpit.adjustment_id IN
1297 (SELECT adjustment_id
1298 FROM mtl_physical_adjustments
1299 WHERE physical_inventory_id = p_physical_inventory_id
1300 AND organization_id = p_organization_id
1301 AND approval_status IS NULL);
1302 END IF;
1303
1304 END GET_PHYINV_LPN_LOV;
1305
1306
1307 /********************************************************************************
1308 WMS - PJM Integration Changes
1309 Changed the second part of the Union so that the LPNs returned are filtered
1310 based on the project and task IDs passed as parameters.
1311 ********************************************************************************/
1312 PROCEDURE GET_PUTAWAY_LPN_LOV
1313 (x_lpn_lov OUT NOCOPY t_genref,
1314 p_org_id IN NUMBER,
1315 p_sub IN VARCHAR2 := NULL,
1316 p_loc_id IN VARCHAR2 := NULL,
1317 p_orig_lpn_id IN VARCHAR2 := NULL,
1318 p_lpn IN VARCHAR2,
1319 p_project_id IN NUMBER := NULL,
1320 p_task_id IN NUMBER := NULL,
1321 p_lpn_context IN NUMBER := NULL,
1322 p_rcv_sub_only IN NUMBER
1323 )
1324 IS
1325 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1326 BEGIN
1327 OPEN x_lpn_lov FOR
1328 -- Select the same LPN as the original source LPN.
1329 -- Note that this might not make sense for the manual/consolidated
1330 -- Into LPN since you can't nest an LPN into itself. However, this LOV
1331 -- is also used in the Item Drop scenario where it is possible for
1332 -- the Into/destination LPN to be the same as the source LPN if it is
1333 -- the last item and you are putting the entire LPN away.
1334 SELECT license_plate_number,
1335 lpn_id,
1336 inventory_item_id,
1337 organization_id,
1338 revision,
1339 lot_number,
1340 serial_number,
1341 subinventory_code,
1342 locator_id,
1343 parent_lpn_id,
1344 NVL(sealed_status, 2),
1345 gross_weight_uom_code,
1346 NVL(gross_weight, 0),
1347 content_volume_uom_code,
1348 NVL(content_volume, 0)
1349 FROM wms_license_plate_numbers wlpn
1350 WHERE wlpn.organization_id = p_org_id
1351 AND wlpn.lpn_id = p_orig_lpn_id
1352
1353 UNION
1354
1355 SELECT wlpn.license_plate_number,
1356 wlpn.lpn_id,
1357 wlpn.inventory_item_id,
1358 wlpn.organization_id,
1359 wlpn.revision,
1360 wlpn.lot_number,
1361 wlpn.serial_number,
1362 wlpn.subinventory_code,
1363 wlpn.locator_id,
1364 wlpn.parent_lpn_id,
1365 NVL(wlpn.sealed_status, 2),
1366 wlpn.gross_weight_uom_code,
1367 NVL(wlpn.gross_weight, 0),
1368 wlpn.content_volume_uom_code,
1369 NVL(wlpn.content_volume, 0)
1370 FROM wms_license_plate_numbers wlpn
1371 --,mtl_item_locations mil
1372 WHERE wlpn.organization_id = p_org_id
1373 AND wlpn.license_plate_number LIKE (p_lpn)
1374 AND (wlpn.lpn_context = 5
1375 OR (wlpn.lpn_context = 1
1376 -- Include Inventory LPN's only if we allow both INV and RCV subs
1377 AND p_rcv_sub_only = 2
1378 AND NVL(p_lpn_context, 1) IN (1,2,3)
1379 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1380 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1381 -- Project, Task comingling check will be done
1382 -- in validate_into_lpn for better performance.
1383 --AND wlpn.locator_id = mil.inventory_location_id
1384 --AND NVL(mil.project_id, -1) = NVL(p_project_id, -1)
1385 --AND NVL(mil.task_id, -1) = NVL(p_task_id, -1)
1386 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_orig_lpn_id), -999)
1387 AND inv_material_status_grp.is_status_applicable(
1388 'TRUE',
1389 NULL,
1390 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
1391 NULL,
1392 NULL,
1393 p_org_id,
1394 NULL,
1395 wlpn.subinventory_code,
1396 wlpn.locator_id,
1397 NULL,
1398 NULL,
1399 'Z'
1400 ) = 'Y'
1401 AND inv_material_status_grp.is_status_applicable(
1402 'TRUE',
1403 NULL,
1404 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
1405 NULL,
1406 NULL,
1407 p_org_id,
1408 NULL,
1409 wlpn.subinventory_code,
1410 wlpn.locator_id,
1411 NULL,
1412 NULL,
1413 'L'
1414 ) = 'Y'
1415 ) -- or for LPN context = 1
1416 OR (wlpn.lpn_context = 3
1417 AND NVL(p_lpn_context, -999) = 3
1418 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1419 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1420 -- Project, Task comingling check will be done
1421 -- in validate_into_lpn for better performance.
1422 --AND wlpn.locator_id = mil.inventory_location_id
1423 ) -- OR for lpn_context = 3
1424 )-- For AND lpn context = 5
1425 ORDER BY license_plate_number;
1426 END;
1427
1428
1429 --Private procedures called from GET_PKUPK_LPN_LOV
1430 --Procedure to fetch LPNs when when transaction type is Pack/Unpack
1431 --and the LPN Context passed is 0 (fetch LPNs with context 1 and 5)
1432 PROCEDURE GET_PACK_INV_LPNS (x_lpn_lov OUT NOCOPY t_genref ,
1433 p_org_id IN NUMBER ,
1434 p_sub IN VARCHAR2 := NULL ,
1435 p_loc_id IN VARCHAR2 := NULL ,
1436 p_not_lpn_id IN VARCHAR2 := NULL ,
1437 p_parent_lpn_id IN VARCHAR2 := '0' ,
1438 p_txn_type_id IN NUMBER := 0 ,
1439 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
1440 p_lpn IN VARCHAR2,
1441 p_context IN NUMBER := 0,
1442 p_project_id IN nUMBER := NULL,
1443 p_task_id IN NUMBER := NULL,
1444 p_mtrl_sts_check IN VARCHAR2 := 'Y'--Bug 3980914-Added the parameter.
1445 )
1446 IS
1447 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1448 BEGIN
1449 IF p_incl_pre_gen_lpn = 'TRUE' THEN
1450 IF (l_debug = 1) THEN
1451 mydebug('pack and inv; pregen=true');
1452 END IF;
1453 --Select LPNs with context "1" or "5"
1454 open x_lpn_lov for
1455 SELECT wlpn.license_plate_number,
1456 wlpn.lpn_id,
1457 wlpn.inventory_item_id,
1458 wlpn.organization_id,
1459 wlpn.revision,
1460 wlpn.lot_number,
1461 wlpn.serial_number,
1462 wlpn.subinventory_code,
1463 wlpn.locator_id,
1464 wlpn.parent_lpn_id,
1465 NVL(wlpn.sealed_status, 2),
1466 wlpn.gross_weight_uom_code,
1467 NVL(wlpn.gross_weight, 0),
1468 wlpn.content_volume_uom_code,
1469 NVL(wlpn.content_volume, 0),
1470 wlpn.lpn_context --Added for bug#4202068.
1471 FROM wms_license_plate_numbers wlpn
1472 WHERE wlpn.organization_id = p_org_id
1473 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1474 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1475 NVL(wlpn.parent_lpn_id, 0))
1476 AND wlpn.license_plate_number LIKE (p_lpn)
1477 /* Bug 3980914 -For LPN's with context 5, the following condition is not required
1478 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
1479 AND (wlpn.lpn_context = 5)
1480 UNION ALL
1481 SELECT wlpn.license_plate_number,
1482 wlpn.lpn_id,
1483 wlpn.inventory_item_id,
1484 wlpn.organization_id,
1485 wlpn.revision,
1486 wlpn.lot_number,
1487 wlpn.serial_number,
1488 wlpn.subinventory_code,
1489 wlpn.locator_id,
1490 wlpn.parent_lpn_id,
1491 NVL(wlpn.sealed_status, 2),
1492 wlpn.gross_weight_uom_code,
1493 NVL(wlpn.gross_weight, 0),
1494 wlpn.content_volume_uom_code,
1495 NVL(wlpn.content_volume, 0),
1496 wlpn.lpn_context --Added for bug#4202068.
1497 FROM wms_license_plate_numbers wlpn
1498 WHERE wlpn.organization_id = p_org_id
1499 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1500 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1501 NVL(wlpn.parent_lpn_id, 0))
1502 AND wlpn.license_plate_number LIKE (p_lpn)
1503 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y'
1504 AND wlpn.lpn_context = 1
1505 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1506 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1507 AND ( ( p_mtrl_sts_check = 'Y' -- Bug 3980914
1508 AND inv_material_status_grp.is_status_applicable
1509 ('TRUE', NULL, p_txn_type_id, NULL,
1510 NULL, p_org_id, NULL, wlpn.subinventory_code,
1511 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
1512 AND inv_material_status_grp.is_status_applicable
1513 ('TRUE', NULL, p_txn_type_id, NULL,
1514 NULL, p_org_id, NULL, wlpn.subinventory_code,
1515 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
1516 )
1517 OR p_mtrl_sts_check = 'N' --Bug 3980914
1518 )
1519 ORDER BY license_plate_number;
1520 ELSE
1521 IF (l_debug = 1) THEN
1522 mydebug('pack and inv; pregen=false');
1523 END IF;
1524 -- Select LPNs with context "1"
1525 open x_lpn_lov for
1526 SELECT wlpn.license_plate_number,
1527 wlpn.lpn_id,
1528 wlpn.inventory_item_id,
1529 wlpn.organization_id,
1530 wlpn.revision,
1531 wlpn.lot_number,
1532 wlpn.serial_number,
1533 wlpn.subinventory_code,
1534 wlpn.locator_id,
1535 wlpn.parent_lpn_id,
1536 NVL(wlpn.sealed_status, 2),
1537 wlpn.gross_weight_uom_code,
1538 NVL(wlpn.gross_weight, 0),
1539 wlpn.content_volume_uom_code,
1540 NVL(wlpn.content_volume, 0),
1541 wlpn.lpn_context --Added for bug#4202068.
1542 FROM wms_license_plate_numbers wlpn
1543 WHERE wlpn.organization_id = p_org_id
1544 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1545 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1546 NVL(wlpn.parent_lpn_id, 0))
1547 AND wlpn.license_plate_number LIKE (p_lpn)
1548 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y')
1549 AND wlpn.lpn_context = 1
1550 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1551 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1552 AND ( ( p_mtrl_sts_check = 'Y' -- Bug 3980914
1553 AND inv_material_status_grp.is_status_applicable
1554 ('TRUE', NULL, p_txn_type_id, NULL,
1555 NULL, p_org_id, NULL, wlpn.subinventory_code,
1556 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
1557 AND inv_material_status_grp.is_status_applicable
1558 ('TRUE', NULL, p_txn_type_id, NULL,
1559 NULL, p_org_id, NULL, wlpn.subinventory_code,
1560 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
1561 )
1562 OR p_mtrl_sts_check = 'N'
1563 )
1564 --End of fix for Bug 3980914
1565 ORDER BY license_plate_number;
1566 END IF;
1567
1568 END GET_PACK_INV_LPNS;
1569
1570 --Procedure to fetch LPNs when when transaction type is Pack/Unpack
1571 --and the LPN Context passed is 0 (fetch LPNs with context 11 and 5)
1572 PROCEDURE GET_PACK_PICKED_LPNS(x_lpn_lov OUT NOCOPY t_genref ,
1573 p_org_id IN NUMBER ,
1574 p_sub IN VARCHAR2 := NULL ,
1575 p_loc_id IN VARCHAR2 := NULL ,
1576 p_not_lpn_id IN VARCHAR2 := NULL ,
1577 p_parent_lpn_id IN VARCHAR2 := '0' ,
1578 p_txn_type_id IN NUMBER := 0 ,
1579 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
1580 p_lpn IN VARCHAR2,
1581 p_context IN NUMBER := 0,
1582 p_project_id IN NUMBER := NULL,
1583 p_task_id IN NUMBER := NULL,
1584 p_mtrl_sts_check IN VARCHAR2 := 'Y', --Bug 3980914
1585 p_calling IN VARCHAR2 := NULL -- Bug 7210544
1586 )
1587 IS
1588 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1589 BEGIN
1590 IF p_incl_pre_gen_lpn = 'TRUE' THEN
1591 IF (l_debug = 1) THEN
1592 mydebug('pack and picked; pregen=true');
1593 END IF;
1594 --Select LPNs with context "11" or "5"
1595 open x_lpn_lov for
1596 SELECT wlpn.license_plate_number,
1597 wlpn.lpn_id,
1598 wlpn.inventory_item_id,
1599 wlpn.organization_id,
1600 wlpn.revision,
1601 wlpn.lot_number,
1602 wlpn.serial_number,
1603 wlpn.subinventory_code,
1604 wlpn.locator_id,
1605 wlpn.parent_lpn_id,
1606 NVL(wlpn.sealed_status, 2),
1607 wlpn.gross_weight_uom_code,
1608 NVL(wlpn.gross_weight, 0),
1609 wlpn.content_volume_uom_code,
1610 NVL(wlpn.content_volume, 0),
1611 wlpn.lpn_context --Added for bug#4202068.
1612 FROM wms_license_plate_numbers wlpn
1613 WHERE wlpn.organization_id = p_org_id
1614 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1615 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1616 NVL(wlpn.parent_lpn_id, 0))
1617 AND wlpn.license_plate_number LIKE (p_lpn)
1618 /* Bug 3980914 - For LPN's with context 5, no check for subinventory required.
1619 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
1620 AND (wlpn.lpn_context = 5)
1621 UNION ALL
1622 SELECT wlpn.license_plate_number,
1623 wlpn.lpn_id,
1624 wlpn.inventory_item_id,
1625 wlpn.organization_id,
1626 wlpn.revision,
1627 wlpn.lot_number,
1628 wlpn.serial_number,
1629 wlpn.subinventory_code,
1630 wlpn.locator_id,
1631 wlpn.parent_lpn_id,
1632 NVL(wlpn.sealed_status, 2),
1633 wlpn.gross_weight_uom_code,
1634 NVL(wlpn.gross_weight, 0),
1635 content_volume_uom_code,
1636 NVL(wlpn.content_volume, 0),
1637 wlpn.lpn_context --Added for bug#4202068.
1638 FROM wms_license_plate_numbers wlpn,
1639 mtl_item_locations mil
1640 WHERE wlpn.organization_id = p_org_id
1641 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1642 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1643 NVL(wlpn.parent_lpn_id, 0))
1644 AND wlpn.license_plate_number LIKE (p_lpn)
1645 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y')
1646 AND wlpn.lpn_context = p_context
1647 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1648 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1649 AND mil.inventory_location_id = wlpn.locator_id
1650 -- Bug 4452535
1651 -- If user provide project/task, select LPN with that project/task
1652 -- If user provide NULL proj/task, only select LPN with NULL project/task
1653 --AND NVL(mil.SEGMENT19,-1) = NVL(p_project_id, NVL(mil.SEGMENT19,-1))
1654 --AND NVL(mil.SEGMENT20,-1) = NVL(p_task_id, NVL(mil.SEGMENT20,-1))
1655 AND ( (p_project_id IS NOT NULL
1656 AND NVL(mil.SEGMENT19,-1) = p_project_id)
1657 OR
1658 (p_project_id IS NULL
1659 AND (Nvl(p_calling,-1)='SHIP_UNPACK' OR NVL(mil.SEGMENT19,-1) = -1)) -- Bug 7210544
1660 )
1661 AND ( (p_task_id IS NOT NULL
1662 AND NVL(mil.SEGMENT20,-1) = p_task_id)
1663 OR
1664 (p_task_id IS NULL
1665 AND (Nvl(p_calling,-1)='SHIP_UNPACK' OR NVL(mil.SEGMENT20,-1) = -1)) -- Bug 7210544
1666 )
1667 AND (( p_mtrl_sts_check = 'Y' --Bug 3980914
1668 AND inv_material_status_grp.is_status_applicable
1669 ('TRUE', NULL, p_txn_type_id, NULL,
1670 NULL, p_org_id, NULL, wlpn.subinventory_code,
1671 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
1672 AND inv_material_status_grp.is_status_applicable
1673 ('TRUE', NULL, p_txn_type_id, NULL,
1674 NULL, p_org_id, NULL, wlpn.subinventory_code,
1675 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
1676 )
1677 OR
1678 p_mtrl_sts_check = 'N' --Bug 3980914
1679 )
1680 ORDER BY license_plate_number;
1681 ELSE
1682 IF (l_debug = 1) THEN
1683 mydebug('pack and picked; pregen=false '||' p_mtrl_sts_check '||p_mtrl_sts_check);
1684 END IF;
1685 -- Select LPNs with context "11"
1686 open x_lpn_lov for
1687 SELECT wlpn.license_plate_number,
1688 wlpn.lpn_id,
1689 wlpn.inventory_item_id,
1690 wlpn.organization_id,
1691 wlpn.revision,
1692 wlpn.lot_number,
1693 wlpn.serial_number,
1694 wlpn.subinventory_code,
1695 wlpn.locator_id,
1696 wlpn.parent_lpn_id,
1697 NVL(wlpn.sealed_status, 2),
1698 wlpn.gross_weight_uom_code,
1699 NVL(wlpn.gross_weight, 0),
1700 content_volume_uom_code,
1701 NVL(wlpn.content_volume, 0),
1702 wlpn.lpn_context --Added for bug#4202068.
1703 FROM wms_license_plate_numbers wlpn,
1704 mtl_item_locations mil
1705 WHERE wlpn.organization_id = p_org_id
1706 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1707 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1708 NVL(wlpn.parent_lpn_id, 0))
1709 AND wlpn.license_plate_number LIKE (p_lpn)
1710 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y')
1711 AND wlpn.lpn_context = p_context
1712 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1713 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1714 AND mil.inventory_location_id = wlpn.locator_id
1715 -- Bug 4452535
1716 -- If user provide project/task, select LPN with that project/task
1717 -- If user provide NULL proj/task, only select LPN with NULL project/task
1718 --AND NVL(mil.SEGMENT19,-1) = NVL(p_project_id, NVL(mil.SEGMENT19,-1))
1719 --AND NVL(mil.SEGMENT20,-1) = NVL(p_task_id, NVL(mil.SEGMENT20,-1))
1720 AND ( (p_project_id IS NOT NULL
1721 AND NVL(mil.SEGMENT19,-1) = p_project_id)
1722 OR
1723 (p_project_id IS NULL
1724 AND (Nvl(p_calling,-1)='SHIP_UNPACK' OR NVL(mil.SEGMENT19,-1) = -1)) -- Bug 7210544
1725 )
1726 AND ( (p_task_id IS NOT NULL
1727 AND NVL(mil.SEGMENT20,-1) = p_task_id)
1728 OR
1729 (p_task_id IS NULL
1730 AND (Nvl(p_calling,-1)='SHIP_UNPACK' OR NVL(mil.SEGMENT20,-1) = -1)) -- Bug 7210544
1731 )
1732 AND ( ( p_mtrl_sts_check = 'Y' --Bug 3980914
1733 AND inv_material_status_grp.is_status_applicable
1734 ('TRUE', NULL, p_txn_type_id, NULL,
1735 NULL, p_org_id, NULL, wlpn.subinventory_code,
1736 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
1737 AND inv_material_status_grp.is_status_applicable
1738 ('TRUE', NULL, p_txn_type_id, NULL,
1739 NULL, p_org_id, NULL, wlpn.subinventory_code,
1740 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
1741 )
1742 OR p_mtrl_sts_check = 'N' --Bug 3980914
1743 )
1744 ORDER BY license_plate_number;
1745 END IF;
1746
1747 END GET_PACK_PICKED_LPNS;
1748
1749
1750
1751 /* For the FP Bug 4057223 --> Base bug#4021746
1752 This procedure is used in following flows.
1753 1. For Unpacking of a Child LPN from the Parent LPN. Here no
1754 need of doing the material check. Because while selecting the
1755 Parent LPN itself, the material status for the sub/loc had been
1756 done.So no need for the same check while selecting the Child LPN.
1757 2. For Consolidating the Child LPNs into a Parent LPN.
1758 We can consolidate one LPN into another if both reside in
1759 same SKU. So no need of performing the mtrl status check
1760 for the child LPNs which are to be consolidated.
1761 and this applicable only for ***Inventory LPNs***
1762 */
1763
1764 PROCEDURE GET_PK_UNPK_INV_LPNS_NO_CHECK
1765 (
1766 x_lpn_lov OUT nocopy t_genref ,
1767 p_org_id IN NUMBER ,
1768 p_sub IN VARCHAR2 := NULL ,
1769 p_loc_id IN VARCHAR2 := NULL ,
1770 p_not_lpn_id IN VARCHAR2 := NULL ,
1771 p_parent_lpn_id IN VARCHAR2 := '0' ,
1772 p_txn_type_id IN NUMBER := 0 ,
1773 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
1774 p_lpn IN VARCHAR2,
1775 p_context IN NUMBER := 0,
1776 p_project_id IN nUMBER := NULL,
1777 p_task_id IN NUMBER := NULL,
1778 p_mtrl_sts_check IN VARCHAR2 := 'Y'
1779 )
1780 IS
1781 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1782 BEGIN
1783 IF p_incl_pre_gen_lpn = 'TRUE' THEN
1784 IF (l_debug = 1) THEN
1785 mydebug('pack / unpack and inv lpns with minimal check: pregen=true');
1786 END IF;
1787 --Select LPNs with context "1" or "5"
1788 open x_lpn_lov for
1789 SELECT wlpn.license_plate_number,
1790 wlpn.lpn_id,
1791 wlpn.inventory_item_id,
1792 wlpn.organization_id,
1793 wlpn.revision,
1794 wlpn.lot_number,
1795 wlpn.serial_number,
1796 wlpn.subinventory_code,
1797 wlpn.locator_id,
1798 wlpn.parent_lpn_id,
1799 NVL(wlpn.sealed_status, 2),
1800 wlpn.gross_weight_uom_code,
1801 NVL(wlpn.gross_weight, 0),
1802 wlpn.content_volume_uom_code,
1803 NVL(wlpn.content_volume, 0),
1804 wlpn.lpn_context
1805 FROM wms_license_plate_numbers wlpn
1806 WHERE wlpn.organization_id = p_org_id
1807 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1808 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(wlpn.parent_lpn_id, 0))
1809 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1810 AND (wlpn.lpn_context = 5)
1811 UNION ALL
1812 SELECT wlpn.license_plate_number,
1813 wlpn.lpn_id,
1814 wlpn.inventory_item_id,
1815 wlpn.organization_id,
1816 wlpn.revision,
1817 wlpn.lot_number,
1818 wlpn.serial_number,
1819 wlpn.subinventory_code,
1820 wlpn.locator_id,
1821 wlpn.parent_lpn_id,
1822 NVL(wlpn.sealed_status, 2),
1823 wlpn.gross_weight_uom_code,
1824 NVL(wlpn.gross_weight, 0),
1825 wlpn.content_volume_uom_code,
1826 NVL(wlpn.content_volume, 0),
1827 wlpn.lpn_context
1828 FROM wms_license_plate_numbers wlpn
1829 WHERE wlpn.organization_id = p_org_id
1830 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1831 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(wlpn.parent_lpn_id, 0))
1832 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1833 AND wlpn.lpn_context = 1
1834 AND wlpn.subinventory_code = p_sub
1835 AND wlpn.locator_id = p_loc_id
1836 AND wlpn.lpn_id NOT IN (SELECT Nvl(lpn_id , -999)
1837 FROM wms_license_plate_numbers wlpn1
1838 WHERE wlpn1.organization_id = wlpn.organization_id
1839 START WITH wlpn1.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1840 CONNECT BY wlpn1.lpn_id = PRIOR wlpn1.parent_lpn_id ) --13535759
1841 ORDER BY license_plate_number;
1842 ELSE
1843
1844 IF (l_debug = 1) THEN
1845 mydebug('pack / unpack and inv lpns with minimal check ; pregen=false');
1846 END IF;
1847
1848 -- Select LPNs with context "1"
1849 open x_lpn_lov for
1850 SELECT wlpn.license_plate_number,
1851 wlpn.lpn_id,
1852 wlpn.inventory_item_id,
1853 wlpn.organization_id,
1854 wlpn.revision,
1855 wlpn.lot_number,
1856 wlpn.serial_number,
1857 wlpn.subinventory_code,
1858 wlpn.locator_id,
1859 wlpn.parent_lpn_id,
1860 NVL(wlpn.sealed_status, 2),
1861 wlpn.gross_weight_uom_code,
1862 NVL(wlpn.gross_weight, 0),
1863 wlpn.content_volume_uom_code,
1864 NVL(wlpn.content_volume, 0) ,
1865 wlpn.lpn_context
1866 FROM wms_license_plate_numbers wlpn
1867 WHERE wlpn.organization_id = p_org_id
1868 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1869 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(wlpn.parent_lpn_id, 0))
1870 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1871 AND wlpn.lpn_context = 1
1872 AND wlpn.subinventory_code = p_sub
1873 AND wlpn.locator_id = p_loc_id
1874 AND wlpn.lpn_id NOT IN (SELECT Nvl(lpn_id , -999)
1875 FROM wms_license_plate_numbers wlpn1
1876 WHERE wlpn1.organization_id = wlpn.organization_id
1877 START WITH wlpn1.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1878 CONNECT BY wlpn1.lpn_id = PRIOR wlpn1.parent_lpn_id ) --13535759
1879 ORDER BY license_plate_number;
1880 END IF;
1881
1882 END GET_PK_UNPK_INV_LPNS_NO_CHECK ;
1883
1884
1885
1886 --Procedure to fetch LPNs when when transaction type is Split
1887 --and the LPN Context passed is 0 (fetch LPNs with context 1 and 5)
1888 PROCEDURE GET_SPLIT_INV_LPNS(x_lpn_lov OUT NOCOPY t_genref ,
1889 p_org_id IN NUMBER ,
1890 p_sub IN VARCHAR2 := NULL ,
1891 p_loc_id IN VARCHAR2 := NULL ,
1892 p_not_lpn_id IN VARCHAR2 := NULL ,
1893 p_parent_lpn_id IN VARCHAR2 := '0' ,
1894 p_txn_type_id IN NUMBER := 0 ,
1895 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
1896 p_lpn IN VARCHAR2,
1897 p_context IN NUMBER := 0,
1898 p_project_id IN NUMBER := NULL,
1899 p_task_id IN NUMBER := NULL,
1900 p_mtrl_sts_check IN VARCHAR2 := 'Y' --Bug 3980914
1901 )
1902 IS
1903 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1904 BEGIN
1905 IF p_incl_pre_gen_lpn = 'TRUE' THEN
1906 IF (l_debug = 1) THEN
1907 mydebug('split and inv; pregen=true');
1908 END IF;
1909 --Select LPNs with context "1" or "5"
1910 open x_lpn_lov for
1911 SELECT wlpn.license_plate_number,
1912 wlpn.lpn_id,
1913 wlpn.inventory_item_id,
1914 wlpn.organization_id,
1915 revision,
1916 wlpn.lot_number,
1917 wlpn.serial_number,
1918 wlpn.subinventory_code,
1919 wlpn.locator_id,
1920 wlpn.parent_lpn_id,
1921 NVL(wlpn.sealed_status, 2),
1922 wlpn.gross_weight_uom_code,
1923 NVL(wlpn.gross_weight, 0),
1924 wlpn.content_volume_uom_code,
1925 NVL(wlpn.content_volume, 0),
1926 wlpn.lpn_context --Added for bug#4202068.
1927 FROM wms_license_plate_numbers wlpn
1928 WHERE wlpn.organization_id = p_org_id
1929 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1930 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1931 NVL(wlpn.parent_lpn_id, 0))
1932 AND wlpn.license_plate_number LIKE (p_lpn)
1933 /* Bug 3980914 -For LPN's with context 5, the following condition is not required
1934 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
1935 AND (wlpn.lpn_context = 5)
1936 UNION ALL
1937 SELECT wlpn.license_plate_number,
1938 wlpn.lpn_id,
1939 NVL(wlpn.inventory_item_id, 0),
1940 NVL(wlpn.organization_id, 0),
1941 wlpn.revision,
1942 wlpn.lot_number,
1943 wlpn.serial_number,
1944 wlpn.subinventory_code,
1945 NVL(wlpn.locator_id, 0),
1946 NVL(wlpn.parent_lpn_id, 0),
1947 NVL(wlpn.sealed_status, 2),
1948 wlpn.gross_weight_uom_code,
1949 NVL(wlpn.gross_weight, 0),
1950 wlpn.content_volume_uom_code,
1951 NVL(wlpn.content_volume, 0),
1952 wlpn.lpn_context --Added for bug#4202068.
1953 FROM wms_license_plate_numbers wlpn
1954 WHERE wlpn.organization_id = p_org_id
1955 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1956 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1957 NVL(wlpn.parent_lpn_id, 0))
1958 AND wlpn.license_plate_number LIKE (p_lpn)
1959 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
1960 AND wlpn.lpn_context = 1
1961 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
1962 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1963 AND (( p_mtrl_sts_check = 'Y' --Bug 3980914
1964 AND inv_material_status_grp.is_status_applicable
1965 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
1966 NULL, p_org_id, NULL, wlpn.subinventory_code,
1967 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
1968 AND inv_material_status_grp.is_status_applicable
1969 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
1970 NULL, p_org_id, NULL, wlpn.subinventory_code,
1971 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
1972 AND inv_material_status_grp.is_status_applicable
1973 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
1974 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
1975 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
1976 AND inv_material_status_grp.is_status_applicable
1977 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
1978 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
1979 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
1980 )
1981 OR p_mtrl_sts_check = 'N' --Bug 3980914
1982 )
1983 ORDER BY license_plate_number;
1984 ELSE
1985 IF (l_debug = 1) THEN
1986 mydebug('split and inv; pregen=false');
1987 END IF;
1988 --Select LPNs with context "1"
1989 OPEN x_lpn_lov FOR
1990 SELECT DISTINCT wlpn.license_plate_number, -- Bug 14345460
1991 wlpn.lpn_id,
1992 NVL(wlpn.inventory_item_id, 0),
1993 NVL(wlpn.organization_id, 0),
1994 wlpn.revision,
1995 wlpn.lot_number,
1996 wlpn.serial_number,
1997 wlpn.subinventory_code,
1998 NVL(wlpn.locator_id, 0),
1999 NVL(wlpn.parent_lpn_id, 0),
2000 NVL(wlpn.sealed_status, 2),
2001 wlpn.gross_weight_uom_code,
2002 NVL(wlpn.gross_weight, 0),
2003 wlpn.content_volume_uom_code,
2004 NVL(wlpn.content_volume, 0),
2005 wlpn.lpn_context
2006 FROM wms_license_plate_numbers wlpn,
2007 wms_lpn_contents wlc -- Bug 14345460
2008 WHERE wlpn.organization_id = p_org_id
2009 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2010 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(wlpn.parent_lpn_id, 0))
2011 AND wlpn.license_plate_number LIKE (p_lpn)
2012 AND wlpn.lpn_id = wlc.parent_lpn_id -- Bug 14345460
2013 AND wms_lpn_lovs.sub_lpn_controlled(wlpn.subinventory_code, p_org_id) = 'Y'
2014 AND wlpn.lpn_context = 1
2015 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
2016 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
2017 AND (( p_mtrl_sts_check = 'Y' AND inv_material_status_grp.is_status_applicable('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_SPLIT,
2018 NULL, NULL, p_org_id, wlc.inventory_item_id, wlpn.subinventory_code, wlpn.locator_id, wlc.lot_number, NULL, 'A', wlpn.lpn_id) = 'Y' -- Bug 14345460
2019 )
2020 OR p_mtrl_sts_check = 'N' --Bug 3980914
2021 )
2022 ORDER BY license_plate_number;
2023
2024 END IF;
2025 END GET_SPLIT_INV_LPNS;
2026
2027 --Procedure to fetch LPNs when when transaction type is Split
2028 --and the LPN Context passed is 0 (fetch LPNs with context 11 and 5)
2029 PROCEDURE GET_SPLIT_PICKED_LPNS(x_lpn_lov OUT NOCOPY t_genref ,
2030 p_org_id IN NUMBER ,
2031 p_sub IN VARCHAR2 := NULL ,
2032 p_loc_id IN VARCHAR2 := NULL ,
2033 p_not_lpn_id IN VARCHAR2 := NULL ,
2034 p_parent_lpn_id IN VARCHAR2 := '0' ,
2035 p_txn_type_id IN NUMBER := 0 ,
2036 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
2037 p_lpn IN VARCHAR2,
2038 p_context IN NUMBER := 0,
2039 p_project_id IN NUMBER := NULL,
2040 p_task_id IN NUMBER := NULL,
2041 p_mtrl_sts_check IN VARCHAR2 := 'Y' --Bug 3980914
2042 )
2043 IS
2044 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2045 BEGIN
2046 IF p_incl_pre_gen_lpn = 'TRUE' THEN
2047 IF (l_debug = 1) THEN
2048 mydebug('split and picked; pregen=true');
2049 END IF;
2050 --Select LPNs with context "11" or "5"
2051 open x_lpn_lov for
2052 SELECT wlpn.license_plate_number,
2053 wlpn.lpn_id,
2054 wlpn.inventory_item_id,
2055 wlpn.organization_id,
2056 revision,
2057 wlpn.lot_number,
2058 wlpn.serial_number,
2059 wlpn.subinventory_code,
2060 wlpn.locator_id,
2061 wlpn.parent_lpn_id,
2062 NVL(wlpn.sealed_status, 2),
2063 wlpn.gross_weight_uom_code,
2064 NVL(wlpn.gross_weight, 0),
2065 wlpn.content_volume_uom_code,
2066 NVL(wlpn.content_volume, 0),
2067 wlpn.lpn_context --Added for bug#4202068.
2068 FROM wms_license_plate_numbers wlpn
2069 WHERE wlpn.organization_id = p_org_id
2070 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2071 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2072 NVL(wlpn.parent_lpn_id, 0))
2073 AND wlpn.license_plate_number LIKE (p_lpn)
2074 /* Bug 3980914 -For LPN's with context 5, the following condition is not required
2075 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
2076 AND (wlpn.lpn_context = 5)
2077 UNION ALL
2078 SELECT wlpn.license_plate_number,
2079 wlpn.lpn_id,
2080 NVL(wlpn.inventory_item_id, 0),
2081 NVL(wlpn.organization_id, 0),
2082 wlpn.revision,
2083 wlpn.lot_number,
2084 wlpn.serial_number,
2085 wlpn.subinventory_code,
2086 NVL(wlpn.locator_id, 0),
2087 NVL(wlpn.parent_lpn_id, 0),
2088 NVL(wlpn.sealed_status, 2),
2089 wlpn.gross_weight_uom_code,
2090 NVL(wlpn.gross_weight, 0),
2091 wlpn.content_volume_uom_code,
2092 NVL(wlpn.content_volume, 0),
2093 wlpn.lpn_context --Added for bug#4202068.
2094 FROM wms_license_plate_numbers wlpn,
2095 mtl_item_locations mil
2096 WHERE wlpn.organization_id = p_org_id
2097 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2098 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2099 NVL(wlpn.parent_lpn_id, 0))
2100 AND wlpn.license_plate_number LIKE (p_lpn)
2101 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
2102 AND wlpn.lpn_context = p_context
2103 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
2104 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
2105 AND mil.inventory_location_id = wlpn.locator_id
2106 AND NVL(mil.SEGMENT19, -1) = NVL(p_project_id, NVL(mil.SEGMENT19, -1))
2107 AND NVL(mil.SEGMENT20, -1) = NVL(p_task_id, NVL(mil.SEGMENT20, -1))
2108 AND ( ( p_mtrl_sts_check = 'Y' --Bug 3980914
2109 AND inv_material_status_grp.is_status_applicable
2110 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2111 NULL, p_org_id, NULL, wlpn.subinventory_code,
2112 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2113 AND inv_material_status_grp.is_status_applicable
2114 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2115 NULL, p_org_id, NULL, wlpn.subinventory_code,
2116 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2117 AND inv_material_status_grp.is_status_applicable
2118 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2119 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2120 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2121 AND inv_material_status_grp.is_status_applicable
2122 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2123 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2124 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2125 )
2126 OR p_mtrl_sts_check = 'N' --Bug 3980914
2127 )
2128 ORDER BY license_plate_number;
2129 ELSE
2130 IF (l_debug = 1) THEN
2131 mydebug('split and picked; pregen=false');
2132 END IF;
2133 --Select LPNs with context "11"
2134 open x_lpn_lov for
2135 SELECT wlpn.license_plate_number,
2136 wlpn.lpn_id,
2137 NVL(wlpn.inventory_item_id, 0),
2138 NVL(wlpn.organization_id, 0),
2139 wlpn.revision,
2140 wlpn.lot_number,
2141 wlpn.serial_number,
2142 wlpn.subinventory_code,
2143 NVL(wlpn.locator_id, 0),
2144 NVL(wlpn.parent_lpn_id, 0),
2145 NVL(wlpn.sealed_status, 2),
2146 wlpn.gross_weight_uom_code,
2147 NVL(wlpn.gross_weight, 0),
2148 wlpn.content_volume_uom_code,
2149 NVL(wlpn.content_volume, 0),
2150 wlpn.lpn_context --Added for bug#4202068.
2151 FROM wms_license_plate_numbers wlpn,
2152 mtl_item_locations mil
2153 WHERE wlpn.organization_id = p_org_id
2154 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2155 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2156 NVL(wlpn.parent_lpn_id, 0))
2157 AND wlpn.license_plate_number LIKE (p_lpn)
2158 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
2159 AND wlpn.lpn_context = p_context
2160 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
2161 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
2162 AND mil.inventory_location_id = wlpn.locator_id
2163 AND NVL(mil.SEGMENT19, -1) = NVL(p_project_id, NVL(mil.SEGMENT19, -1))
2164 ANd NVL(mil.SEGMENT20, -1) = NVL(p_task_id, NVL(mil.SEGMENT20, -1))
2165 AND ( ( p_mtrl_sts_check = 'Y' --Bug 3980914
2166 AND inv_material_status_grp.is_status_applicable
2167 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2168 NULL, p_org_id, NULL, wlpn.subinventory_code,
2169 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2170 AND inv_material_status_grp.is_status_applicable
2171 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2172 NULL, p_org_id, NULL, wlpn.subinventory_code,
2173 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2174 AND inv_material_status_grp.is_status_applicable
2175 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2176 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2177 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2178 AND inv_material_status_grp.is_status_applicable
2179 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2180 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2181 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2182 )
2183 OR p_mtrl_sts_check = 'N' --Bug 3980914
2184 )
2185 ORDER BY license_plate_number;
2186 END IF;
2187
2188 END GET_SPLIT_PICKED_LPNS;
2189
2190 PROCEDURE GET_PKUPK_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref ,
2191 p_org_id IN NUMBER ,
2192 p_sub IN VARCHAR2 := NULL ,
2193 p_loc_id IN VARCHAR2 := NULL ,
2194 p_not_lpn_id IN VARCHAR2 := NULL ,
2195 p_parent_lpn_id IN VARCHAR2 := '0' ,
2196 p_txn_type_id IN NUMBER := 0 ,
2197 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
2198 p_lpn IN VARCHAR2,
2199 p_context IN NUMBER := 0,
2200 p_project_id IN NUMBER := NULL,
2201 p_task_id IN NUMBER := NULL,
2202 p_mtrl_sts_check IN VARCHAR2 := 'Y', -- Bug 3980914
2203 p_calling IN VARCHAR2 := NULL -- Bug 7210544
2204 )
2205 IS
2206 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2207 BEGIN
2208
2209 IF (l_debug = 1) THEN
2210 mydebug('org:'||p_org_id || ' sub:' || p_sub || ' loc:'||p_loc_id ||' parent_lpn:' || p_parent_lpn_id || ' not_lpn:' || p_not_lpn_id );
2211 mydebug('txn:'||p_txn_type_id ||' incfl:'|| p_incl_pre_gen_lpn || ' con:'||p_context || ' mtrl_chk:'|| p_mtrl_sts_check||' lpn:'|| p_lpn||' Calling '||p_calling);
2212
2213
2214 END IF;
2215
2216
2217 If p_txn_type_id IN (INV_GLOBALS.G_TYPE_CONTAINER_PACK,
2218 INV_GLOBALS.G_TYPE_CONTAINER_UNPACK) THEN
2219 IF p_context = 11 THEN
2220 mydebug('calling GET_PACK_PICKED_LPNS');
2221 GET_PACK_PICKED_LPNS(
2222 x_lpn_lov => x_lpn_lov,
2223 p_org_id => p_org_id,
2224 p_sub => p_sub,
2225 p_loc_id => p_loc_id,
2226 p_not_lpn_id => p_not_lpn_id,
2227 p_parent_lpn_id => p_parent_lpn_id,
2228 p_txn_type_id => p_txn_type_id,
2229 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2230 p_lpn => p_lpn,
2231 p_context => p_context,
2232 p_project_id => p_project_id,
2233 p_task_id => p_task_id,
2234 p_mtrl_sts_check => p_mtrl_sts_check, --Bug 3980914
2235 p_calling => p_calling); --Bug 7210544
2236 --Added for the For bug 4057223 --> Base Bug #4021746
2237 ELSIF p_sub IS NOT NULL
2238 AND p_loc_id IS NOT NULL
2239 AND p_mtrl_sts_check = 'N' THEN
2240
2241 IF (l_debug = 1) THEN
2242 mydebug('calling GET_PK_UNPK_INV_LPNS_NO_CHECK');
2243 END IF;
2244
2245 GET_PK_UNPK_INV_LPNS_NO_CHECK
2246 (
2247 x_lpn_lov => x_lpn_lov,
2248 p_org_id => p_org_id,
2249 p_sub => p_sub,
2250 p_loc_id => p_loc_id,
2254 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2251 p_not_lpn_id => p_not_lpn_id,
2252 p_parent_lpn_id => p_parent_lpn_id,
2253 p_txn_type_id => p_txn_type_id,
2255 p_lpn => p_lpn,
2256 p_context => p_context,
2257 p_project_id => p_project_id,
2258 p_task_id => p_task_id,
2259 p_mtrl_sts_check => p_mtrl_sts_check);
2260 --Added for the For bug 4057223 --> Base Bug #4021746
2261 ELSE
2262
2263 IF (l_debug = 1) THEN
2264 mydebug('calling GET_PACK_INV_LPNS');
2265 END IF;
2266
2267 GET_PACK_INV_LPNS(
2268 x_lpn_lov => x_lpn_lov,
2269 p_org_id => p_org_id,
2270 p_sub => p_sub,
2271 p_loc_id => p_loc_id,
2272 p_not_lpn_id => p_not_lpn_id,
2273 p_parent_lpn_id => p_parent_lpn_id,
2274 p_txn_type_id => p_txn_type_id,
2275 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2276 p_lpn => p_lpn,
2277 p_context => p_context,
2278 p_project_id => p_project_id,
2279 p_task_id => p_task_id,
2280 p_mtrl_sts_check => p_mtrl_sts_check); --Bug 3980914
2281
2282 END IF;
2283 ELSIF p_txn_type_id = INV_GLOBALS.G_TYPE_CONTAINER_SPLIT THEN
2284 IF p_context = 11 THEN
2285 GET_SPLIT_PICKED_LPNS(
2286 x_lpn_lov => x_lpn_lov,
2287 p_org_id => p_org_id,
2288 p_sub => p_sub,
2289 p_loc_id => p_loc_id,
2290 p_not_lpn_id => p_not_lpn_id,
2291 p_parent_lpn_id => p_parent_lpn_id,
2292 p_txn_type_id => p_txn_type_id,
2293 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2294 p_lpn => p_lpn,
2295 p_context => p_context,
2296 p_project_id => p_project_id,
2297 p_task_id => p_task_id,
2298 p_mtrl_sts_check => p_mtrl_sts_check); --Bug 3980914
2299 ELSE
2300 GET_SPLIT_INV_LPNS(
2301 x_lpn_lov => x_lpn_lov,
2302 p_org_id => p_org_id,
2303 p_sub => p_sub,
2304 p_loc_id => p_loc_id,
2305 p_not_lpn_id => p_not_lpn_id,
2306 p_parent_lpn_id => p_parent_lpn_id,
2307 p_txn_type_id => p_txn_type_id,
2308 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2309 p_lpn => p_lpn,
2310 p_context => p_context,
2311 p_project_id => p_project_id,
2312 p_task_id => p_task_id,
2313 p_mtrl_sts_check => p_mtrl_sts_check); --Bug 3980914
2314
2315 END IF;
2316 ELSE
2317 --will paste the other sql
2318 IF (l_debug = 1) THEN
2319 mydebug('calling other sql');
2320 END IF;
2321
2322 open x_lpn_lov for
2323 select license_plate_number,
2324 lpn_id,
2325 NVL(inventory_item_id, 0),
2326 NVL(organization_id, 0),
2327 revision,
2328 lot_number,
2329 serial_number,
2330 subinventory_code,
2331 NVL(locator_id, 0),
2332 NVL(parent_lpn_id, 0),
2333 NVL(sealed_status, 2),
2334 gross_weight_uom_code,
2335 NVL(gross_weight, 0),
2336 content_volume_uom_code,
2337 NVL(content_volume, 0),
2338 wlpn.lpn_context --Added for bug#4202068.
2339 FROM wms_license_plate_numbers wlpn
2340 WHERE
2341 wlpn.organization_id = p_org_id
2342 AND (wlpn.lpn_context = p_context
2343 OR (p_context = 0
2344 AND (wlpn.lpn_context = 1 OR wlpn.lpn_context = 5)))
2345 AND license_plate_number LIKE (p_lpn)
2346 ORDER BY license_plate_number;
2347
2348 END IF;
2349 END GET_PKUPK_LPN_LOV;
2350
2351 PROCEDURE GET_PUP_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref ,
2352 p_org_id IN NUMBER ,
2353 p_sub IN VARCHAR2 := NULL ,
2354 p_loc_id IN VARCHAR2 := NULL ,
2355 p_not_lpn_id IN VARCHAR2 := NULL ,
2356 p_parent_lpn_id IN VARCHAR2 := '0' ,
2357 p_lpn IN VARCHAR2
2358 )
2359 IS
2360 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2361 BEGIN
2362 open x_lpn_lov for
2363 select license_plate_number,
2364 lpn_id,
2365 NVL(inventory_item_id, 0),
2366 NVL(organization_id, 0),
2367 revision,
2368 lot_number,
2369 serial_number,
2370 subinventory_code,
2371 NVL(locator_id, 0),
2372 NVL(parent_lpn_id, 0),
2373 NVL(sealed_status, 2),
2374 gross_weight_uom_code,
2375 NVL(gross_weight, 0),
2376 content_volume_uom_code,
2377 NVL(content_volume, 0)
2378 FROM wms_license_plate_numbers wlpn
2379 WHERE wlpn.organization_id = p_org_id
2380 AND NVL(subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
2381 AND NVL(locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(locator_id, '0'))
2382 AND NOT lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2383 AND NVL(parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(parent_lpn_id, 0))
2384 AND license_plate_number LIKE (p_lpn)
2385 ORDER BY license_plate_number;
2386
2387 END GET_PUP_LPN_LOV;
2388
2389 PROCEDURE CHILD_LPN_EXISTS(p_lpn_id IN NUMBER ,
2390 x_out OUT NOCOPY NUMBER
2391 )
2392 IS
2393 l_temp_num NUMBER;
2394 CURSOR child_lpn_cursor IS
2395 SELECT lpn_id
2396 FROM wms_license_plate_numbers
2397 WHERE parent_lpn_id = p_lpn_id;
2398
2399 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2400 BEGIN
2401 OPEN child_lpn_cursor;
2402 FETCH child_lpn_cursor INTO l_temp_num;
2403 IF child_lpn_cursor%notfound THEN
2404 x_out := 2;
2405 ELSE
2406 x_out := 1;
2407 END IF;
2408 CLOSE child_lpn_cursor;
2409
2410 END CHILD_LPN_EXISTS;
2411
2412
2413 PROCEDURE VALIDATE_PHYINV_LPN
2414 (p_lpn IN VARCHAR2 ,
2415 p_dynamic_entry_flag IN NUMBER ,
2416 p_physical_inventory_id IN NUMBER ,
2417 p_organization_id IN NUMBER ,
2418 p_subinventory_code IN VARCHAR2 ,
2419 p_locator_id IN NUMBER ,
2420 x_result OUT NOCOPY NUMBER)
2421 IS
2422 l_count NUMBER;
2423
2424 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2425 BEGIN
2426 IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
2427 -- Select all LPN's which exist in the given org, sub, loc
2428 SELECT COUNT(*)
2429 INTO l_count
2430 FROM wms_license_plate_numbers
2431 WHERE organization_id = p_organization_id
2432 AND subinventory_code = p_subinventory_code
2433 AND Nvl(locator_id, -99999) = Nvl(p_locator_id, -99999)
2434 AND license_plate_number = p_lpn;
2435
2436 IF (l_count = 1) THEN
2437 -- Validation is successful
2438 x_result := 1;
2439 ELSE
2440 -- Validation is not successful
2441 x_result := 2;
2442 END IF;
2443
2444 ELSE -- Dynamic entries are not allowed
2445 -- Select only LPN's that exist in table MTL_PHYSICAL_INVENTORY_TAGS
2446 SELECT COUNT(*)
2447 INTO l_count
2448 FROM wms_license_plate_numbers wlpn,
2449 mtl_physical_inventory_tags mpit
2450 WHERE wlpn.organization_id = p_organization_id
2451 AND wlpn.subinventory_code = p_subinventory_code
2452 AND Nvl(wlpn.locator_id, -99999) = Nvl(p_locator_id, -99999)
2453 AND wlpn.license_plate_number LIKE (p_lpn)
2454 AND wlpn.lpn_id = mpit.parent_lpn_id
2455 AND mpit.organization_id = p_organization_id
2456 AND mpit.physical_inventory_id = p_physical_inventory_id;
2457
2458 IF (l_count = 1) THEN
2459 -- Validation is successful
2460 x_result := 1;
2461 ELSE
2462 -- Validation is not successful
2463 x_result := 2;
2464 END IF;
2465
2466 END IF;
2467
2468 END VALIDATE_PHYINV_LPN;
2469
2470
2471 PROCEDURE VALIDATE_CYCLECOUNT_LPN
2472 (p_lpn IN VARCHAR2 ,
2473 p_unscheduled_entry IN NUMBER ,
2474 p_cycle_count_header_id IN NUMBER ,
2475 p_organization_id IN NUMBER ,
2476 p_subinventory_code IN VARCHAR2 ,
2477 p_locator_id IN NUMBER ,
2478 x_result OUT NOCOPY NUMBER)
2479 IS
2480 l_count NUMBER;
2481
2482 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2483 BEGIN
2484 IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
2485 -- Select all LPN's which exist in the given org, sub, loc
2486 SELECT COUNT(*)
2487 INTO l_count
2488 FROM wms_license_plate_numbers
2489 WHERE organization_id = p_organization_id
2490 AND subinventory_code = p_subinventory_code
2491 AND NVL(locator_id, -99999) = NVL(p_locator_id, -99999)
2492 AND license_plate_number = p_lpn;
2493
2494 IF (l_count = 1) THEN
2495 -- Validation is successful
2496 x_result := 1;
2497 ELSE
2498 -- Validation is not successful
2499 x_result := 2;
2500 END IF;
2501
2502 ELSE -- Unscheduled entries are not allowed
2503 -- Select only LPN's that exist in table MTL_CYCLE_COUNT_ENTRIES
2504 SELECT COUNT(*)
2505 INTO l_count
2506 FROM wms_license_plate_numbers wlpn,
2507 mtl_cycle_count_entries mcce
2508 WHERE wlpn.organization_id = p_organization_id
2509 AND wlpn.subinventory_code = p_subinventory_code
2510 AND NVL(wlpn.locator_id, -99999) = NVL(p_locator_id, -99999)
2511 AND wlpn.license_plate_number LIKE (p_lpn)
2512 AND wlpn.lpn_id = mcce.parent_lpn_id
2513 AND mcce.organization_id = p_organization_id
2514 AND mcce.cycle_count_header_id = p_cycle_count_header_id;
2515
2516 IF (l_count = 1) THEN
2517 -- Validation is successful
2518 x_result := 1;
2519 ELSE
2520 -- Validation is not successful
2521 x_result := 2;
2522 END IF;
2523
2524 END IF;
2525
2526 END VALIDATE_CYCLECOUNT_LPN;
2527
2528
2529
2530 PROCEDURE VALIDATE_LPN_AGAINST_ORG
2531 (p_lpn IN VARCHAR2 ,
2532 p_organization_id IN NUMBER ,
2533 x_result OUT NOCOPY NUMBER)
2534 IS
2535 l_lpn WMS_container_pub.LPN;
2536 l_result NUMBER;
2537
2538 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2539 BEGIN
2540 l_lpn.lpn_id := NULL;
2541 l_lpn.license_plate_number := p_lpn;
2542 l_result := wms_container_pub.Validate_LPN(l_lpn);
2543 IF (l_result = INV_Validate.F) THEN
2544 -- LPN was not found
2545 x_result := 2;
2546 ELSE
2547 -- LPN was found and is therefore valid
2548 x_result := 1;
2549 END IF;
2550
2551 END VALIDATE_LPN_AGAINST_ORG;
2552
2553
2554
2555 PROCEDURE GET_LPN_VALUES
2556 (p_lpn IN VARCHAR2 ,
2557 p_organization_id IN NUMBER ,
2558 x_license_plate_number OUT NOCOPY VARCHAR2 ,
2559 x_lpn_id OUT NOCOPY NUMBER ,
2560 x_inventory_item_id OUT NOCOPY NUMBER ,
2561 x_organization_id OUT NOCOPY NUMBER ,
2562 x_revision OUT NOCOPY VARCHAR2 ,
2563 x_lot_number OUT NOCOPY VARCHAR2 ,
2564 x_serial_number OUT NOCOPY VARCHAR2 ,
2565 x_subinventory_code OUT NOCOPY VARCHAR2 ,
2566 x_locator_id OUT NOCOPY NUMBER ,
2567 x_parent_lpn_id OUT NOCOPY NUMBER ,
2568 x_sealed_status OUT NOCOPY NUMBER ,
2569 x_gross_weight_uom_code OUT NOCOPY VARCHAR2 ,
2570 x_gross_weight OUT NOCOPY NUMBER ,
2571 x_content_volume_uom_code OUT NOCOPY VARCHAR2 ,
2572 x_content_volume OUT NOCOPY NUMBER)
2573 IS
2574 l_lpn_record LPN_RECORD;
2575
2576 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2577 BEGIN
2578 SELECT license_plate_number,
2579 lpn_id,
2580 NVL(inventory_item_id, 0),
2581 NVL(organization_id, 0),
2582 revision,
2583 lot_number,
2584 serial_number,
2585 subinventory_code,
2586 NVL(locator_id, 0),
2587 NVL(parent_lpn_id, 0),
2588 NVL(sealed_status, 2),
2589 gross_weight_uom_code,
2590 NVL(gross_weight, 0),
2591 content_volume_uom_code,
2592 NVL(content_volume, 0)
2593 INTO l_lpn_record
2594 FROM wms_license_plate_numbers
2595 WHERE license_plate_number = p_lpn
2596 AND organization_id = p_organization_id
2597 ORDER BY license_plate_number;
2598
2599 x_license_plate_number := l_lpn_record.license_plate_number;
2600 x_lpn_id := l_lpn_record.lpn_id;
2601 x_inventory_item_id := l_lpn_record.inventory_item_id;
2602 x_organization_id := l_lpn_record.organization_id;
2603 x_revision := l_lpn_record.revision;
2604 x_lot_number := l_lpn_record.lot_number;
2605 x_serial_number := l_lpn_record.serial_number;
2606 x_subinventory_code := l_lpn_record.subinventory_code;
2607 x_locator_id := l_lpn_record.locator_id;
2608 x_parent_lpn_id := l_lpn_record.parent_lpn_id;
2609 x_sealed_status := l_lpn_record.sealed_status;
2610 x_gross_weight_uom_code := l_lpn_record.gross_weight_uom_code;
2611 x_gross_weight := l_lpn_record.gross_weight;
2612 x_content_volume_uom_code := l_lpn_record.content_volume_uom_code;
2613 x_content_volume := l_lpn_record.content_volume;
2614
2615 END GET_LPN_VALUES;
2616
2617
2618
2619
2620
2621
2622
2623
2624 -- Name: GET_INSPECT_LPN_LOV
2625 --
2626 -- Input parameters:
2627 -- p_lpn which restricts LOV SQL to the user input text
2628 --
2629 -- Output parameters:
2630 -- x_lpn_lov returns LOV rows as reference cursor
2631 --
2632 -- Functions: This API returns valid LPN and lpn_id whose contents have to be inspected
2633 -- in Mobile Inspection form
2634 --
2635
2636 PROCEDURE GET_INSPECT_LPN_LOV
2637 (x_lpn_lov OUT NOCOPY t_genref,
2638 p_lpn IN VARCHAR2,
2639 p_organization_id IN NUMBER
2640 )
2641 IS
2642 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2643 BEGIN
2644 -- inspection_status in mtl_txn_request_lines could have values {null,1,2,3}
2645 -- mapping to {Inspection not needed, Inspection needed, Accepted, Rejected }
2646 -- bug 8405606 changed the condition so as to be able to inspect an accepted lpn and rejected lpn also
2647
2648
2649 OPEN x_lpn_lov FOR
2650 SELECT distinct
2651 a.license_plate_number,
2652 a.lpn_id,
2653 NVL(a.inventory_item_id, 0),
2654 NVL(a.organization_id, 0),
2655 a.revision,
2656 a.lot_number,
2657 a.serial_number,
2658 a.subinventory_code,
2659 NVL(a.locator_id, 0),
2660 NVL(a.parent_lpn_id, 0),
2661 NVL(a.sealed_status, 2),
2662 a.gross_weight_uom_code,
2663 NVL(a.gross_weight, 0),
2664 a.content_volume_uom_code,
2665 NVL(a.content_volume, 0),
2666 nvl(rec_count.lpn_content_count, 0)
2667 FROM wms_license_plate_numbers a,
2668 mtl_txn_request_lines b,
2669 (SELECT count(*) lpn_content_count,grouped_contents.lpn_id
2670 FROM (SELECT mtrl.lpn_id lpn_id, -- Need extra grouping to group
2671 mtrl.inventory_item_id item_id,
2672 mtrl.revision revision
2673 --BUG 3358288: Use MOL to calculate the count instead of
2674 --using WLC because there may be items there does not
2675 --require inspection
2676 FROM wms_license_plate_numbers wlpn, mtl_txn_request_lines mtrl
2677 WHERE wlpn.license_plate_number LIKE (p_lpn)
2678 AND mtrl.lpn_id = wlpn.lpn_id
2679 AND mtrl.inspection_status is not null -- bug 8405606
2680 AND mtrl.wms_process_flag = 1
2681 AND mtrl.line_status = 7
2682 AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0))>0
2683 --GROUP BY mtrl.lpn_id, mtrl.inventory_item_id,Nvl(mtrl.revision,-1)) grouped_contents
2684 --Fix for Bug 11858596. Taken out NVL() for mtrl.revision to
2685 --make it in line with select statement
2686 GROUP BY mtrl.lpn_id, mtrl.inventory_item_id,mtrl.revision) grouped_contents
2687 GROUP BY grouped_contents.lpn_id) rec_count
2688 WHERE a. license_plate_number LIKE (p_lpn)
2689 and a.lpn_id = b.lpn_id
2690 and a.lpn_context in (3,5)
2691 and b.inspection_status is not null -- bug 8405606
2692 -- Bug 2377796
2693 -- Check to make sure that the processing for mtl_txn_request_lines is completed or not.
2694 and b.wms_process_flag = 1
2695 AND b.line_status = 7
2696 AND (b.quantity-Nvl(b.quantity_delivered,0))>0
2697 and b.organization_id = p_organization_id
2698 and a.lpn_id = rec_count.lpn_id --(+) //Bug 3435093
2699 and nvl(rec_count.lpn_content_count, 0) > 0;
2700
2701
2702 END GET_INSPECT_LPN_LOV;
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713 --
2714 -- Procedure to retrieve LPNs in Inventory which contain only the specific
2715 -- item having less than equal to specified qty in the specified location.
2716 -- Called from LPNLOV.java
2717 --
2718 PROCEDURE GET_MO_LPN
2719 (x_lpn_lov OUT NOCOPY t_genref ,
2720 p_lpn IN VARCHAR2 ,
2721 p_inv_item_id IN NUMBER ,
2722 p_organization_id IN NUMBER ,
2723 p_subinventory_code IN VARCHAR2 ,
2724 p_locator_id IN NUMBER ,
2725 p_qty IN NUMBER) IS
2726 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2727 begin
2728 OPEN x_lpn_lov FOR
2729 select lpnc.parent_lpn_id lpn_id,
2730 lpn.license_plate_number lpn,
2731 sum(lpnc.quantity) quantity
2732 from wms_lpn_contents lpnc, wms_license_plate_numbers lpn
2733 where lpn.organization_id = p_organization_id
2734 and lpnc.inventory_item_id = p_inv_item_id
2735 and lpnc.parent_lpn_id = lpn.lpn_id
2736 and nvl(lpn.SUBINVENTORY_CODE,'@@@') = nvl(p_subinventory_code,'@@@')
2737 and nvl(lpn.LOCATOR_ID, 0) = nvl(p_locator_id, 0)
2738 and lpn.license_plate_number like (p_lpn)
2739 and lpn.lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
2740 and not exists (select null
2741 from wms_lpn_contents
2742 where parent_lpn_id = lpnc.parent_lpn_id
2743 and inventory_item_id <> lpnc.inventory_item_id)
2744 group by lpnc.parent_lpn_id, lpn.license_plate_number
2745 having sum(lpnc.quantity) <= p_qty;
2746
2747 end;
2748
2749
2750 -- Neted LPN changes added p_mode parameter.
2751 -- For express receipts the value of Mode will be 'E'
2752 -- For confirm receipts the value of Mode will be 'C'
2753 -- If the value of p_mode is NULL then that means the customer
2754 -- has Patchset I Java page. In this case we will use the old query.
2755
2756
2757 PROCEDURE
2758 GET_VENDOR_LPN
2759 (x_lpn_lov OUT NOCOPY t_genref ,
2760 p_lpn IN VARCHAR2 ,
2761 p_shipment_header_id IN VARCHAR2 ,
2762 p_mode IN VARCHAR2 ,
2763 p_inventory_item_id IN VARCHAR2
2764 )
2765 IS
2766 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2767 BEGIN
2768
2769
2770 -- Nested LPN changes if p_mode is NULL then it is called Before Patchset J,
2771 -- If p_mode is 'E' called from Express page. If it is 'C' then called from confirm page.
2772
2773 -- For getting the No of LPNs attached to a Shipment Header, the Like clause
2774 -- is commented, because it is not necessary to check with pattern matching
2775 -- in this query. (Base Bug : 3080274).
2776
2777 IF p_mode IS NULL THEN
2778 OPEN x_lpn_lov FOR
2779 SELECT
2780 lpn.license_plate_number,
2781 lpn.lpn_id,
2782 NVL(lpn.inventory_item_id, 0),
2783 NVL(lpn.organization_id, 0),
2784 lpn.revision,
2785 lpn.lot_number,
2786 lpn.serial_number,
2787 lpn.subinventory_code,
2788 NVL(lpn.locator_id, 0),
2789 NVL(lpn.parent_lpn_id, 0),
2790 NVL(lpn.sealed_status, 2),
2791 lpn.gross_weight_uom_code,
2792 NVL(lpn.gross_weight, 0),
2793 lpn.content_volume_uom_code,
2794 NVL(lpn.content_volume, 0),
2795 lpn.source_header_id,
2796 rsh.shipment_num,
2797 count_row.n,
2798 rsh.shipment_header_id
2799 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2800 (SELECT COUNT(*) n
2801 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2802 --WHERE lpn.license_plate_number LIKE (p_lpn)--Bug 3090000
2803 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2804 AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2805 AND (lpn.source_header_id = rsh.shipment_header_id
2806 OR lpn.source_name = rsh.shipment_num)
2807 ) count_row
2808 WHERE lpn.license_plate_number LIKE (p_lpn)
2809 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2810 AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2811 AND (lpn.source_header_id = rsh.shipment_header_id
2812 OR lpn.source_name = rsh.shipment_num)
2813 -- Nested LPN changes , For I Patchset donot show nested LPNs
2814 AND lpn.lpn_id NOT IN (SELECT parent_lpn_id FROM wms_license_plate_numbers WHERE parent_lpn_id = lpn.lpn_id )
2815 AND lpn.parent_lpn_id IS NULL;
2816 ELSIF p_mode = 'E' THEN
2817
2818 -- As Part of per bug 3435093 if shipment_header_id is not null
2819 -- removed the Nvl condition around shipment_header_id to pick the index.
2820
2821 IF p_shipment_header_id IS NOT NULL THEN
2822
2823 OPEN x_lpn_lov FOR
2824 SELECT
2825 wlpn1.license_plate_number,
2826 wlpn1.lpn_id,
2827 NVL(wlpn1.inventory_item_id, 0),
2828 NVL(wlpn1.organization_id, 0),
2829 wlpn1.revision,
2830 wlpn1.lot_number,
2831 wlpn1.serial_number,
2832 wlpn1.subinventory_code,
2833 NVL(wlpn1.locator_id, 0),
2834 NVL(wlpn1.parent_lpn_id, 0),
2835 NVL(wlpn1.sealed_status, 2),
2836 wlpn1.gross_weight_uom_code,
2837 NVL(wlpn1.gross_weight, 0),
2838 wlpn1.content_volume_uom_code,
2839 NVL(wlpn1.content_volume, 0),
2840 wlpn1.source_header_id,
2841 rsh.shipment_num,
2842 1,--This is a dummy value. Actually cound will be calculated in validate_from_lpn
2843 rsh.shipment_header_id
2844 FROM wms_license_plate_numbers wlpn1, rcv_shipment_headers rsh
2845 WHERE rsh.shipment_header_id = p_shipment_header_id
2846 AND ((wlpn1.lpn_context = 6 AND wlpn1.organization_id = rsh.organization_id) OR
2847 (wlpn1.lpn_context = 7 AND wlpn1.organization_id = rsh.ship_to_org_id))
2848 AND wlpn1.source_name = rsh.shipment_num
2849 AND wlpn1.license_plate_number LIKE (p_lpn)
2850 and exists (SELECT wlpn2.lpn_id
2851 FROM wms_license_plate_numbers wlpn2
2852 START WITH wlpn2.lpn_id = wlpn1.lpn_id
2853 CONNECT BY PRIOR wlpn2.lpn_id = wlpn2.parent_lpn_id
2854 INTERSECT
2855 SELECT asn_lpn_id
2856 FROM rcv_shipment_lines rsl
2857 WHERE rsl.shipment_header_id = p_shipment_header_id
2858 AND NOT exists (SELECT 1
2859 FROM rcv_transactions_interface rti
2860 WHERE rti.lpn_id = rsl.asn_lpn_id
2861 AND rti.transfer_lpn_id = rsl.asn_lpn_id
2862 AND rti.to_organization_id = rsl.to_organization_id
2863 AND rti.processing_status_code <> 'ERROR'
2864 AND rti.transaction_status_code <> 'ERROR'
2865 )
2866 );
2867 ELSE
2868 OPEN x_lpn_lov FOR
2869 SELECT
2870 lpn.license_plate_number,
2871 lpn.lpn_id,
2872 NVL(lpn.inventory_item_id, 0),
2873 NVL(lpn.organization_id, 0),
2874 lpn.revision,
2875 lpn.lot_number,
2876 lpn.serial_number,
2877 lpn.subinventory_code,
2878 NVL(lpn.locator_id, 0),
2879 NVL(lpn.parent_lpn_id, 0),
2880 NVL(lpn.sealed_status, 2),
2881 lpn.gross_weight_uom_code,
2882 NVL(lpn.gross_weight, 0),
2883 lpn.content_volume_uom_code,
2884 NVL(lpn.content_volume, 0),
2885 lpn.source_header_id,
2886 rsh.shipment_num,
2887 count_row.n,
2888 rsh.shipment_header_id
2889 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2890 (SELECT COUNT(*) n
2891 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2892 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2893 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2894 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2895 AND (lpn.source_header_id = rsh.shipment_header_id
2896 OR lpn.source_name = rsh.shipment_num)
2897 ) count_row
2898 WHERE lpn.license_plate_number LIKE (p_lpn)
2899 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2900 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2901 AND (lpn.source_header_id = rsh.shipment_header_id
2902 OR lpn.source_name = rsh.shipment_num) ;
2903 END IF; -- For shipment Header id is null
2904
2905 -- Nested LPN changes If mode is 'C' then the LOV is for Confirm transactions
2906 -- In case of Confirm transaction we show LPNs which have immediate contents.
2907 ELSIF p_mode= 'C' THEN
2908
2909 -- This is changed based on Item Info, case for Item Initiated Receipt.
2910 -- If Item info is present or passed from the UI then LPN should be restrcied based on Item
2911 -- Otherwise all the LPN's for the shipment should be displayed in the LOV
2912
2913 if p_inventory_item_id is null then
2914 IF p_shipment_header_id IS NOT NULL THEN
2915 OPEN x_lpn_lov FOR
2916 SELECT
2917 lpn.license_plate_number,
2918 lpn.lpn_id,
2919 NVL(lpn.inventory_item_id, 0),
2920 NVL(lpn.organization_id, 0),
2921 lpn.revision,
2922 lpn.lot_number,
2923 lpn.serial_number,
2924 lpn.subinventory_code,
2925 NVL(lpn.locator_id, 0),
2926 NVL(lpn.parent_lpn_id, 0),
2927 NVL(lpn.sealed_status, 2),
2928 lpn.gross_weight_uom_code,
2929 NVL(lpn.gross_weight, 0),
2930 lpn.content_volume_uom_code,
2931 NVL(lpn.content_volume, 0),
2932 lpn.source_header_id,
2933 rsh.shipment_num,
2934 count_row.n,
2935 rsh.shipment_header_id
2936 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2937 ( SELECT COUNT(*) n
2938 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2939 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2940 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2941 AND rsh.shipment_header_id = p_shipment_header_id
2942 AND (lpn.source_header_id = rsh.shipment_header_id
2943 OR lpn.source_name = rsh.shipment_num)
2944 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
2945 ) count_row
2946 WHERE lpn.license_plate_number LIKE (p_lpn)
2947 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2948 AND rsh.shipment_header_id = p_shipment_header_id
2949 AND (lpn.source_header_id = rsh.shipment_header_id
2950 OR lpn.source_name = rsh.shipment_num)
2951 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id);
2952
2953 ELSE -- for if p_shipment_header_id is not null
2954 OPEN x_lpn_lov FOR
2955 SELECT
2956 lpn.license_plate_number,
2957 lpn.lpn_id,
2958 NVL(lpn.inventory_item_id, 0),
2959 NVL(lpn.organization_id, 0),
2960 lpn.revision,
2961 lpn.lot_number,
2962 lpn.serial_number,
2963 lpn.subinventory_code,
2964 NVL(lpn.locator_id, 0),
2965 NVL(lpn.parent_lpn_id, 0),
2966 NVL(lpn.sealed_status, 2),
2967 lpn.gross_weight_uom_code,
2968 NVL(lpn.gross_weight, 0),
2969 lpn.content_volume_uom_code,
2970 NVL(lpn.content_volume, 0),
2971 lpn.source_header_id,
2972 rsh.shipment_num,
2973 count_row.n,
2974 rsh.shipment_header_id
2975 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2976 ( SELECT COUNT(*) n
2977 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2978 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2979 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2980 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2981 AND (lpn.source_header_id = rsh.shipment_header_id
2982 OR lpn.source_name = rsh.shipment_num)
2983 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
2984 ) count_row
2985 WHERE lpn.license_plate_number LIKE (p_lpn)
2986 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2987 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2988 AND (lpn.source_header_id = rsh.shipment_header_id
2989 OR lpn.source_name = rsh.shipment_num)
2990 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id);
2991
2992 END IF; -- end if for if p_shipment_header_id is not null
2993 ELSE
2994 IF p_shipment_header_id IS NOT NULL THEN
2995 OPEN x_lpn_lov FOR
2996 SELECT
2997 lpn.license_plate_number,
2998 lpn.lpn_id,
2999 NVL(lpn.inventory_item_id, 0),
3000 NVL(lpn.organization_id, 0),
3001 lpn.revision,
3002 lpn.lot_number,
3003 lpn.serial_number,
3004 lpn.subinventory_code,
3005 NVL(lpn.locator_id, 0),
3006 NVL(lpn.parent_lpn_id, 0),
3007 NVL(lpn.sealed_status, 2),
3008 lpn.gross_weight_uom_code,
3009 NVL(lpn.gross_weight, 0),
3010 lpn.content_volume_uom_code,
3011 NVL(lpn.content_volume, 0),
3012 lpn.source_header_id,
3013 rsh.shipment_num,
3014 count_row.n,
3015 rsh.shipment_header_id
3016 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
3017 ( SELECT COUNT(*) n
3018 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
3019 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
3020 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3021 AND rsh.shipment_header_id = p_shipment_header_id
3022 AND (lpn.source_header_id = rsh.shipment_header_id
3023 OR lpn.source_name = rsh.shipment_num)
3024 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
3025 ) count_row
3026 WHERE lpn.license_plate_number LIKE (p_lpn)
3027 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3028 AND rsh.shipment_header_id = p_shipment_header_id
3029 AND (lpn.source_header_id = rsh.shipment_header_id
3030 OR lpn.source_name = rsh.shipment_num)
3031 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id
3032 and inventory_item_id = p_inventory_item_id );
3033
3034 ELSE -- if p_shipment_header_id is null
3035 OPEN x_lpn_lov FOR
3036 SELECT
3037 lpn.license_plate_number,
3038 lpn.lpn_id,
3039 NVL(lpn.inventory_item_id, 0),
3040 NVL(lpn.organization_id, 0),
3041 lpn.revision,
3042 lpn.lot_number,
3043 lpn.serial_number,
3044 lpn.subinventory_code,
3045 NVL(lpn.locator_id, 0),
3046 NVL(lpn.parent_lpn_id, 0),
3047 NVL(lpn.sealed_status, 2),
3048 lpn.gross_weight_uom_code,
3049 NVL(lpn.gross_weight, 0),
3050 lpn.content_volume_uom_code,
3051 NVL(lpn.content_volume, 0),
3052 lpn.source_header_id,
3053 rsh.shipment_num,
3054 count_row.n,
3055 rsh.shipment_header_id
3056 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
3057 ( SELECT COUNT(*) n
3058 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
3059 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
3060 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3061 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3062 AND (lpn.source_header_id = rsh.shipment_header_id
3063 OR lpn.source_name = rsh.shipment_num)
3064 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
3065 ) count_row
3066 WHERE lpn.license_plate_number LIKE (p_lpn)
3067 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3068 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3069 AND (lpn.source_header_id = rsh.shipment_header_id
3070 OR lpn.source_name = rsh.shipment_num)
3071 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id
3072 and inventory_item_id = p_inventory_item_id );
3073 END IF; -- else part of if p_shipment_header_id is not null
3074 END IF; -- else part of if p_inventory_item_id is null
3075 END IF;
3076
3077
3078 END GET_VENDOR_LPN;
3079
3080 /* PJM Integration: Added to get the concatenated segments of physical locator,
3081 * project id, project number, task id and task number.
3082 * Use the table mtl_item_locations instead of mtl_item_locations_kfv.
3083 */
3084 PROCEDURE GET_ITEM_LPN_LOV
3085 (x_lpn_lov OUT NOCOPY t_genref,
3086 p_organization_id IN NUMBER,
3087 p_lot_number IN VARCHAR2,
3088 p_inventory_item_id IN NUMBER,
3089 p_revision IN VARCHAR2,
3090 p_lpn IN VARCHAR2)
3091 IS
3092 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3093 BEGIN
3094 OPEN x_lpn_lov
3095 FOR
3096 SELECT wlpn.license_plate_number,
3097 wlpn.lpn_id,
3098 NVL(wlc.inventory_item_id, 0),
3099 NVL(wlpn.organization_id, 0),
3100 wlc.revision,
3101 wlc.lot_number,
3102 wlc.serial_number,
3103 wlpn.subinventory_code,
3104 NVL(wlpn.locator_id, 0),
3105 NVL(wlpn.parent_lpn_id, 0),
3106 NVL(wlpn.sealed_status, 2),
3107 wlpn.gross_weight_uom_code,
3108 NVL(wlpn.gross_weight, 0),
3109 wlpn.content_volume_uom_code,
3110 NVL(wlpn.content_volume, 0),
3111 --milk.concatenated_segments locator_code,
3112 INV_PROJECT.GET_LOCSEGS(mil.inventory_location_id, p_organization_id),
3113 wlc.cost_group_id,
3114 INV_PROJECT.GET_PROJECT_ID,
3115 INV_PROJECT.GET_PROJECT_NUMBER,
3116 INV_PROJECT.GET_TASK_ID,
3117 INV_PROJECT.GET_TASK_NUMBER
3118 FROM wms_license_plate_numbers wlpn,
3119 wms_lpn_contents wlc,
3120 mtl_item_locations mil
3121 WHERE (mil.inventory_location_id = wlpn.locator_id
3122 AND wlpn.locator_id IS NOT NULL)
3123 AND ( (wlc.revision = p_revision
3124 AND p_revision IS NOT NULL)
3125 OR (wlc.revision IS NULL
3126 AND p_revision IS NULL))
3127 AND wlc.inventory_item_id = p_inventory_item_id
3128 AND ( (wlc.lot_number = p_lot_number
3129 AND p_lot_number IS NOT NULL) OR
3130 (wlc.lot_number LIKE '%'
3131 AND p_lot_number IS NULL))
3132 AND wlpn.license_plate_number LIKE (p_lpn)
3133 AND wlpn.lpn_id = wlc.parent_lpn_id
3134 AND wlpn.lpn_context = 1
3135 AND wlpn.parent_lpn_id IS NULL
3136 AND wlpn.organization_id = p_organization_id;
3137 END GET_ITEM_LPN_LOV;
3138
3139 -- Procedure for the result lot of Lot split/merge/translate.
3140 -- For the result lot, do not need to check for inventory item id.
3141 /* PJM Integration: Added to get the concatenated segments of physical locator,
3142 * project id, project number, task id and task number.
3143 * Use the table mtl_item_locations instead of mtl_item_locations_kfv.
3144 */
3145 PROCEDURE GET_LOT_LPN_LOV
3146 (x_lpn_lov OUT NOCOPY t_genref,
3147 p_organization_id IN NUMBER,
3148 p_lpn IN VARCHAR2)
3149 IS
3150 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3151 BEGIN
3152 OPEN x_lpn_lov FOR
3153 SELECT distinct wlpn.license_plate_number,
3154 wlpn.lpn_id,
3155 NVL(wlpn.inventory_item_id, 0),
3156 NVL(wlpn.organization_id, 0),
3157 wlpn.revision,
3158 wlpn.lot_number,
3159 wlpn.serial_number,
3160 wlpn.subinventory_code,
3161 NVL(wlpn.locator_id, 0),
3162 NVL(wlpn.parent_lpn_id, 0),
3163 NVL(wlpn.sealed_status, 2),
3164 wlpn.gross_weight_uom_code,
3165 NVL(wlpn.gross_weight, 0),
3166 wlpn.content_volume_uom_code,
3167 NVL(wlpn.content_volume, 0),
3168 --milk.concatenated_segments locator_code
3169 INV_PROJECT.GET_LOCSEGS(mil.inventory_location_id, p_organization_id),
3170 INV_PROJECT.GET_PROJECT_ID,
3171 INV_PROJECT.GET_PROJECT_NUMBER,
3172 INV_PROJECT.GET_TASK_ID,
3173 INV_PROJECT.GET_TASK_NUMBER
3174 FROM wms_license_plate_numbers wlpn,
3175 mtl_item_locations mil
3176 WHERE mil.inventory_location_id(+) = wlpn.locator_id --OUTER JOIN is added for bug 3876495
3177 AND wlpn.license_plate_number LIKE (p_lpn)
3178 AND wlpn.organization_id = p_organization_id
3179 AND wlpn.lpn_context IN (1,5) --LPN_CONTEXT 5 is Added for bug3876495.
3180 ORDER BY license_plate_number;
3181 END GET_LOT_LPN_LOV;
3182
3183
3184
3185 PROCEDURE GET_RCV_LPN
3186 (x_lpn_lov OUT NOCOPY t_genref,
3187 p_org_id IN NUMBER,
3188 p_lpn IN VARCHAR2,
3189 p_from_lpn_id IN VARCHAR2,
3190 p_project_id in number,
3191 p_task_id in number
3192 )
3193 IS
3194 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3195 BEGIN
3196 IF p_project_id is not null then
3197 OPEN x_lpn_lov FOR
3198 SELECT wlpn.license_plate_number,
3199 wlpn.lpn_id,
3200 NVL(wlpn.inventory_item_id, 0),
3201 NVL(wlpn.organization_id, 0),
3202 wlpn.revision,
3203 wlpn.lot_number,
3204 wlpn.serial_number,
3205 wlpn.subinventory_code,
3206 NVL(wlpn.locator_id, 0),
3207 NVL(wlpn.parent_lpn_id, 0),
3208 NVL(wlpn.sealed_status, 2),
3209 wlpn.gross_weight_uom_code,
3210 NVL(wlpn.gross_weight, 0),
3211 wlpn.content_volume_uom_code,
3212 NVL(wlpn.content_volume, 0)
3213 FROM wms_license_plate_numbers wlpn
3214 WHERE wlpn.license_plate_number LIKE (p_lpn)
3215 AND wlpn.organization_id = p_org_id
3216 and wlpn.lpn_context = 3
3217 AND exists (
3218 select lpn_id
3219 from mtl_txn_request_lines mtrl
3220 where mtrl.organization_id = p_org_id
3221 and mtrl.project_id = p_project_id
3222 and mtrl.lpn_id = wlpn.lpn_id
3223 and nvl(task_id,-9999) = nvl(p_task_id,-9999)
3224 )
3225 UNION
3226 SELECT wlpn.license_plate_number,
3227 wlpn.lpn_id,
3228 NVL(wlpn.inventory_item_id, 0),
3229 NVL(wlpn.organization_id, 0),
3230 wlpn.revision,
3231 wlpn.lot_number,
3232 wlpn.serial_number,
3233 wlpn.subinventory_code,
3234 NVL(wlpn.locator_id, 0),
3235 NVL(wlpn.parent_lpn_id, 0),
3236 NVL(wlpn.sealed_status, 2),
3237 wlpn.gross_weight_uom_code,
3238 NVL(wlpn.gross_weight, 0),
3239 wlpn.content_volume_uom_code,
3240 NVL(wlpn.content_volume, 0)
3241 FROM wms_license_plate_numbers wlpn
3242 WHERE wlpn.license_plate_number LIKE (p_lpn)
3243 AND wlpn.organization_id = p_org_id
3244 and exists
3245 ( select inventory_location_id
3246 from mtl_item_locations mil
3247 where organization_id = p_org_id
3248 and nvl(wlpn.subinventory_code,'@@@') = nvl(mil.subinventory_code,'@@@')
3249 and mil.project_id = p_project_id
3250 and wlpn.locator_id = mil.inventory_location_id
3251 and nvl(task_id,-9999) = nvl(p_task_id,-9999)
3252 )
3253 and wlpn.lpn_context = 1
3254 AND inv_material_status_grp.is_status_applicable
3255 ('TRUE',
3256 NULL,
3257 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
3258 NULL,
3259 NULL,
3260 p_org_id,
3261 NULL,
3262 wlpn.subinventory_code,
3263 wlpn.locator_id,
3264 NULL,
3265 NULL,
3266 'Z') = 'Y'
3267 AND inv_material_status_grp.is_status_applicable
3268 ('TRUE',
3269 NULL,
3270 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
3271 NULL,
3272 NULL,
3273 p_org_id,
3274 NULL,
3275 wlpn.subinventory_code,
3276 wlpn.locator_id,
3277 NULL,
3278 NULL,
3279 'L') = 'Y'
3280 UNION
3281 SELECT wlpn.license_plate_number,
3282 wlpn.lpn_id,
3283 NVL(wlpn.inventory_item_id, 0),
3284 NVL(wlpn.organization_id, 0),
3285 wlpn.revision,
3286 wlpn.lot_number,
3287 wlpn.serial_number,
3288 wlpn.subinventory_code,
3289 NVL(wlpn.locator_id, 0),
3290 NVL(wlpn.parent_lpn_id, 0),
3291 NVL(wlpn.sealed_status, 2),
3292 wlpn.gross_weight_uom_code,
3293 NVL(wlpn.gross_weight, 0),
3294 wlpn.content_volume_uom_code,
3295 NVL(wlpn.content_volume, 0)
3296 FROM wms_license_plate_numbers wlpn
3297 WHERE wlpn.license_plate_number LIKE (p_lpn)
3298 AND wlpn.organization_id = p_org_id
3299 and ( lpn_context = 5 or lpn_id = p_from_lpn_id )
3300 ORDER BY 1;
3301 elsif p_project_id is null then
3302 OPEN x_lpn_lov FOR
3303 SELECT license_plate_number,
3304 lpn_id,
3305 NVL(inventory_item_id, 0),
3306 NVL(organization_id, 0),
3307 revision,
3308 lot_number,
3309 serial_number,
3310 subinventory_code,
3311 NVL(locator_id, 0),
3312 NVL(parent_lpn_id, 0),
3313 NVL(sealed_status, 2),
3314 gross_weight_uom_code,
3315 NVL(gross_weight, 0),
3316 content_volume_uom_code,
3317 NVL(content_volume, 0)
3318 FROM wms_license_plate_numbers wlpn
3319 WHERE license_plate_number LIKE (p_lpn)
3320 AND organization_id = p_org_id
3321 AND lpn_context = 3
3322 and exists (
3323 select mtrl.lpn_id
3324 from mtl_txn_request_lines mtrl
3325 where mtrl.organization_id = p_org_id
3326 and mtrl.project_id is null
3327 and mtrl.lpn_id = wlpn.lpn_id
3328 and nvl(mtrl.task_id,-9999) = nvl(p_task_id,-9999)
3329 )
3330 UNION
3331 SELECT license_plate_number,
3332 lpn_id,
3333 NVL(inventory_item_id, 0),
3334 NVL(organization_id, 0),
3335 revision,
3336 lot_number,
3337 serial_number,
3338 subinventory_code,
3339 NVL(locator_id, 0),
3340 NVL(parent_lpn_id, 0),
3341 NVL(sealed_status, 2),
3342 gross_weight_uom_code,
3343 NVL(gross_weight, 0),
3344 content_volume_uom_code,
3345 NVL(content_volume, 0)
3346 FROM wms_license_plate_numbers wlpn
3347 WHERE license_plate_number LIKE (p_lpn)
3348 and organization_id = p_org_id
3349 AND lpn_context = 1
3350 and exists
3351 ( select inventory_location_id
3352 from mtl_item_locations mil
3353 where organization_id = p_org_id
3354 and nvl(wlpn.subinventory_code,'@@@') = nvl(mil.subinventory_code,'@@@')
3355 and mil.project_id is null
3356 and wlpn.locator_id = mil.inventory_location_id
3357 and nvl(task_id,-9999) = nvl(p_task_id,-9999)
3358 )
3359 AND inv_material_status_grp.is_status_applicable
3360 ('TRUE',
3361 NULL,
3362 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
3363 NULL,
3364 NULL,
3365 p_org_id,
3366 NULL,
3367 wlpn.subinventory_code,
3368 wlpn.locator_id,
3369 NULL,
3370 NULL,
3371 'Z') = 'Y'
3372 AND inv_material_status_grp.is_status_applicable
3373 ('TRUE',
3374 NULL,
3375 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
3376 NULL,
3377 NULL,
3378 p_org_id,
3379 NULL,
3380 wlpn.subinventory_code,
3381 wlpn.locator_id,
3382 NULL,
3383 NULL,
3384 'L') = 'Y'
3385 UNION
3386 SELECT license_plate_number,
3387 lpn_id,
3388 NVL(inventory_item_id, 0),
3389 NVL(organization_id, 0),
3390 revision,
3391 lot_number,
3392 serial_number,
3393 subinventory_code,
3394 NVL(locator_id, 0),
3395 NVL(parent_lpn_id, 0),
3396 NVL(sealed_status, 2),
3397 gross_weight_uom_code,
3398 NVL(gross_weight, 0),
3399 content_volume_uom_code,
3400 NVL(content_volume, 0)
3401 FROM wms_license_plate_numbers wlpn
3402 WHERE license_plate_number LIKE (p_lpn)
3403 and organization_id = p_org_id
3404 and (lpn_context = 5 or lpn_id = p_from_lpn_id )
3405 ORDER BY 1;
3406 end if;
3407
3408 END GET_RCV_LPN;
3409
3410
3411
3412 PROCEDURE GET_CYC_PARENT_LPN_LOV
3413 (x_lpn_lov OUT NOCOPY t_genref ,
3414 p_lpn IN VARCHAR2 ,
3415 p_unscheduled_entry IN NUMBER ,
3416 p_cycle_count_header_id IN NUMBER ,
3417 p_organization_id IN NUMBER ,
3418 p_subinventory_code IN VARCHAR2 ,
3419 p_locator_id IN NUMBER ,
3420 p_project_id IN NUMBER ,
3421 p_task_id IN NUMBER )
3422 IS
3423 l_container_discrepancy_option NUMBER;
3424
3425 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3426 BEGIN
3427 -- Get the cycle count container discrepancy flag
3428 SELECT NVL(container_discrepancy_option, 2)
3429 INTO l_container_discrepancy_option
3430 FROM mtl_cycle_count_headers
3431 WHERE cycle_count_header_id = p_cycle_count_header_id;
3432
3433 IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
3434 -- Select all LPN's which exist in the given org, sub, loc
3435 OPEN x_lpn_lov FOR
3436 SELECT license_plate_number,
3437 lpn_id,
3438 inventory_item_id,
3439 organization_id,
3440 revision,
3441 lot_number,
3442 serial_number,
3443 subinventory_code,
3444 locator_id,
3445 parent_lpn_id,
3446 NVL(sealed_status, 2),
3447 gross_weight_uom_code,
3448 NVL(gross_weight, 0),
3449 content_volume_uom_code,
3450 NVL(content_volume, 0),
3451 lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3452 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3453 --organization, whether the LPN is "Issued out of Stores".
3454 FROM wms_license_plate_numbers
3455 WHERE organization_id = p_organization_id
3456 AND (subinventory_code = p_subinventory_code OR
3457 l_container_discrepancy_option = 1)
3458 AND (NVL(locator_id, -99999) = NVL(p_locator_id, -99999) OR
3459 (l_container_discrepancy_option = 1
3460 AND locator_id in (
3461 select inventory_location_id
3462 from mtl_item_locations
3463 where nvl(segment19,-9999) = nvl(p_project_id,-9999)
3464 and nvl(segment20,-9999) = nvl(p_task_id,-9999)
3465 )
3466 )
3467 )
3468 AND license_plate_number LIKE (p_lpn)
3469 --AND lpn_context not in (4,6) --Bug# 4205672 --bug#4267956.Added 6 --Commented for bug#4886188
3470 ORDER BY license_plate_number;
3471 ELSE -- Unscheduled entries are not allowed
3472 -- Select only LPN's that exist in table MTL_CYCLE_COUNT_ENTRIES
3473 OPEN x_lpn_lov FOR
3474 SELECT UNIQUE wlpn.license_plate_number,
3475 wlpn.lpn_id,
3476 wlpn.inventory_item_id,
3477 wlpn.organization_id,
3478 wlpn.revision,
3479 wlpn.lot_number,
3480 wlpn.serial_number,
3481 wlpn.subinventory_code,
3482 wlpn.locator_id,
3483 wlpn.parent_lpn_id,
3484 NVL(wlpn.sealed_status, 2),
3485 wlpn.gross_weight_uom_code,
3486 NVL(wlpn.gross_weight, 0),
3487 wlpn.content_volume_uom_code,
3488 NVL(wlpn.content_volume, 0),
3489 wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3490 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3491 --organization, whether the LPN is "Issued out of Stores".
3492 FROM wms_license_plate_numbers wlpn,
3493 mtl_cycle_count_entries mcce
3494 WHERE wlpn.organization_id = p_organization_id
3495 AND (wlpn.subinventory_code = p_subinventory_code OR
3496 l_container_discrepancy_option = 1)
3497 -- Bug# 1609449
3498 --AND NVL(wlpn.locator_id, -99999) = NVL(p_locator_id, -99999)
3499 AND wlpn.license_plate_number LIKE (p_lpn)
3500 --AND wlpn.lpn_context not in (4,6) --Bug# 4205672 --bug#4267956.Added 6 --Commented for bug#4886188
3501 AND wlpn.lpn_id = mcce.parent_lpn_id
3502 AND mcce.organization_id = p_organization_id
3503 AND mcce.cycle_count_header_id = p_cycle_count_header_id
3504 AND (mcce.subinventory = p_subinventory_code OR
3505 l_container_discrepancy_option = 1)
3506 AND (NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999) OR
3507 (l_container_discrepancy_option = 1
3508 AND mcce.locator_id in (
3509 select inventory_location_id
3510 from mtl_item_locations
3511 where nvl(segment19,-9999) = nvl(p_project_id,-9999)
3512 and nvl(segment20,-9999) = nvl(p_task_id,-9999)
3513 )
3514 )
3515 )
3516 AND mcce.entry_status_code IN (1, 3)
3517 AND NVL(TRUNC(mcce.count_due_date, 'DD'), TRUNC(SYSDATE, 'DD'))
3518 >= TRUNC(SYSDATE, 'DD');
3519 END IF;
3520
3521 END GET_CYC_PARENT_LPN_LOV;
3522
3523
3524
3525 PROCEDURE GET_CYC_LPN_LOV
3526 (x_lpn_lov OUT NOCOPY t_genref ,
3527 p_lpn IN VARCHAR2 ,
3528 p_unscheduled_entry IN NUMBER ,
3529 p_cycle_count_header_id IN NUMBER ,
3530 p_organization_id IN NUMBER ,
3531 p_subinventory_code IN VARCHAR2 ,
3532 p_locator_id IN NUMBER ,
3533 p_parent_lpn_id IN NUMBER ,
3534 p_project_id IN NUMBER ,
3535 p_task_id IN NUMBER )
3536 IS
3537 l_container_discrepancy_option NUMBER;
3538
3539 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3540 BEGIN
3541 -- Get the cycle count container discrepancy flag
3542 SELECT NVL(container_discrepancy_option, 2)
3543 INTO l_container_discrepancy_option
3544 FROM mtl_cycle_count_headers
3545 WHERE cycle_count_header_id = p_cycle_count_header_id;
3546
3547 IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
3548 -- Select all LPN's which exist in the given org, sub, loc
3549 OPEN x_lpn_lov FOR
3550 SELECT license_plate_number,
3551 lpn_id,
3552 inventory_item_id,
3553 organization_id,
3554 revision,
3555 lot_number,
3556 serial_number,
3557 subinventory_code,
3558 locator_id,
3559 parent_lpn_id,
3560 NVL(sealed_status, 2),
3561 gross_weight_uom_code,
3562 NVL(gross_weight, 0),
3563 content_volume_uom_code,
3564 NVL(content_volume, 0),
3565 lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3566 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3567 --organization, whether the LPN is "Issued out of Stores".
3568 FROM wms_license_plate_numbers
3569 WHERE organization_id = p_organization_id
3570 AND (subinventory_code = p_subinventory_code OR
3571 l_container_discrepancy_option = 1)
3572 AND (NVL(locator_id, -99999) = NVL(p_locator_id, -99999) OR
3573 (l_container_discrepancy_option = 1
3574 AND locator_id in (
3575 select inventory_location_id
3576 from mtl_item_locations
3577 where nvl(segment19,-9999) = nvl(p_project_id,-9999)
3578 and nvl(segment20,-9999) = nvl(p_task_id,-9999)
3579 )
3580 )
3581 )
3582 AND license_plate_number LIKE (p_lpn)
3583 --AND lpn_context not in (4,6) --Bug# 4205672 --bug#4267956.Added 6 --Commented for bug#4886188
3584 ORDER BY license_plate_number;
3585 ELSE -- Unscheduled entries are not allowed
3586 -- Select only LPN's that exist in table MTL_CYCLE_COUNT_ENTRIES
3587 OPEN x_lpn_lov FOR
3588 SELECT UNIQUE wlpn.license_plate_number,
3589 wlpn.lpn_id,
3590 wlpn.inventory_item_id,
3591 wlpn.organization_id,
3592 wlpn.revision,
3593 wlpn.lot_number,
3594 wlpn.serial_number,
3595 wlpn.subinventory_code,
3596 wlpn.locator_id,
3597 wlpn.parent_lpn_id,
3598 NVL(wlpn.sealed_status, 2),
3599 wlpn.gross_weight_uom_code,
3600 NVL(wlpn.gross_weight, 0),
3601 wlpn.content_volume_uom_code,
3602 NVL(wlpn.content_volume, 0),
3603 wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3604 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3605 --organization, whether the LPN is "Issued out of Stores".
3606 FROM wms_license_plate_numbers wlpn,
3607 mtl_cycle_count_entries mcce
3608 WHERE wlpn.organization_id = p_organization_id
3609 AND (wlpn.subinventory_code = p_subinventory_code OR
3610 l_container_discrepancy_option = 1)
3611 -- Bug# 1609449
3612 --AND NVL(wlpn.locator_id, -99999) = NVL(p_locator_id, -99999)
3613 AND wlpn.license_plate_number LIKE (p_lpn)
3614 --AND wlpn.lpn_context not in (4,6) --Bug# 4205672 --bug#4267956.Added 6 --Commented for bug#4886188
3615 AND wlpn.parent_lpn_id = p_parent_lpn_id
3616 AND wlpn.lpn_id = mcce.parent_lpn_id
3617 AND mcce.organization_id = p_organization_id
3618 AND mcce.cycle_count_header_id = p_cycle_count_header_id
3619 AND (mcce.subinventory = p_subinventory_code OR
3620 l_container_discrepancy_option = 1)
3621 AND (NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999) OR
3622 (l_container_discrepancy_option = 1
3623 AND mcce.locator_id in (
3624 select inventory_location_id
3625 from mtl_item_locations
3626 where nvl(segment19,-9999) = nvl(p_project_id,-9999)
3627 and nvl(segment20,-9999) = nvl(p_task_id,-9999)
3628 )
3629 )
3630 )
3631 AND mcce.entry_status_code IN (1, 3)
3632 AND NVL(TRUNC(mcce.count_due_date, 'DD'), TRUNC(SYSDATE, 'DD'))
3633 >= TRUNC(SYSDATE, 'DD');
3634 END IF;
3635
3636 END GET_CYC_LPN_LOV;
3637
3638 /* PJM-WMS Integration:Return only the LPNs residing in physical locators.
3639 * Use the table mtl_item_locations instead of mtl_item_locations_kfv.
3640 * Use the function INV_PROJECT.get_locsegs() to retrieve the
3641 * concatenated segments.
3642 */
3643 PROCEDURE GET_CGUPDATE_LPN
3644 (x_lpn_lov OUT NOCOPY t_genref,
3645 p_org_id IN NUMBER,
3646 p_lpn IN VARCHAR2)
3647 IS
3648 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3649 BEGIN
3650
3651 OPEN x_lpn_lov FOR
3652 SELECT wlpn.license_plate_number,
3653 wlpn.lpn_id,
3654 wlpn.subinventory_code,
3655 wlpn.locator_id,
3656 -- PJM-WMS Integration
3657 INV_PROJECT.GET_LOCSEGS(mil.inventory_location_id,p_org_id)
3658 FROM mtl_item_locations mil,-- -PJM-WMS Integration
3659 wms_license_plate_numbers wlpn
3660 WHERE mil.inventory_location_id = wlpn.locator_id
3661 AND mil.organization_id = wlpn.organization_id
3662 AND mil.segment19 is null
3663 -- bug 2267845 fix. checking this conditon
3664 -- for identifying non project locators instead of
3665 -- 'phyiscal_location_id is null'
3666 AND wlpn.license_plate_number LIKE (p_lpn)
3667 AND wlpn.organization_id = p_org_id
3668 AND wlpn.lpn_context = 1
3669 ORDER BY license_plate_number;
3670
3671 END GET_CGUPDATE_LPN;
3672
3673 PROCEDURE GET_PALLET_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref,
3674 p_org_id IN NUMBER,
3675 p_lpn VARCHAR2
3676 )
3677 IS
3678 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3679 BEGIN
3680 OPEN x_lpn_lov FOR
3681 SELECT wlpn.license_plate_number,
3682 wlpn.lpn_id,
3683 NVL(wlpn.inventory_item_id, 0),
3684 NVL(wlpn.organization_id, 0),
3685 wlpn.revision,
3686 wlpn.lot_number,
3687 wlpn.serial_number,
3688 wlpn.subinventory_code,
3689 NVL(wlpn.locator_id, 0),
3690 NVL(wlpn.parent_lpn_id, 0),
3691 NVL(wlpn.sealed_status, 2),
3692 wlpn.gross_weight_uom_code,
3693 NVL(wlpn.gross_weight, 0),
3694 wlpn.content_volume_uom_code,
3695 NVL(wlpn.content_volume, 0)
3696 /*select license_plate_number,
3697 wlpn.lpn_id,
3698 NVL(wlpn.inventory_item_id, 0),
3699 NVL(wlpn.organization_id, 0),
3700 wlpn.revision,
3701 wlpn.lot_number,
3702 wlpn.serial_number,
3703 wlpn.subinventory_code,
3704 NVL(wlpn.locator_id, 0),
3705 NVL(wlpn.parent_lpn_id, 0),
3706 NVL(wlpn.sealed_status, 2),
3707 wlpn.gross_weight_uom_code,
3708 NVL(wlpn.gross_weight, 0),
3709 wlpn.content_volume_uom_code,
3710 NVL(wlpn.content_volume, 0)*/
3711 FROM wms_license_plate_numbers wlpn,
3712 mtl_system_items_kfv msik
3713 WHERE wlpn.organization_id = p_org_id
3714 AND wlpn.inventory_item_id IS NOT NULL
3715 AND msik.inventory_item_id = wlpn.inventory_item_id
3716 AND msik.organization_id = wlpn.organization_id
3717 AND msik.container_type_code = 'PALLET'
3718 AND wlpn.license_plate_number LIKE (p_lpn);
3719 END GET_PALLET_LPN_LOV;
3720
3721 PROCEDURE CHECK_LPN_LOV
3722 ( p_lpn IN VARCHAR2,
3723 p_organization_id IN NUMBER,
3724 x_lpn_id OUT NOCOPY NUMBER,
3725 x_inventory_item_id OUT NOCOPY NUMBER,
3726 x_organization_id OUT NOCOPY NUMBER,
3727 x_lot_number OUT NOCOPY VARCHAR2,
3728 x_revision OUT NOCOPY VARCHAR2,
3729 x_serial_number OUT NOCOPY VARCHAR2,
3730 x_subinventory OUT NOCOPY VARCHAR2,
3731 x_locator_id OUT NOCOPY NUMBER,
3732 x_parent_lpn_id OUT NOCOPY NUMBER,
3733 x_sealed_status OUT NOCOPY NUMBER,
3734 x_gross_weight OUT NOCOPY NUMBER,
3735 x_gross_weight_uom_code OUT NOCOPY VARCHAR2,
3736 x_content_volume OUT NOCOPY NUMBER,
3737 x_content_volume_uom_code OUT NOCOPY VARCHAR2,
3738 x_source_type_id OUT NOCOPY NUMBER,
3739 x_source_header_id OUT NOCOPY NUMBER,
3740 x_source_name OUT NOCOPY VARCHAR2,
3741 x_source_line_id OUT NOCOPY NUMBER,
3742 x_source_line_detail_id OUT NOCOPY NUMBER,
3743 x_cost_group_id OUT NOCOPY NUMBER,
3744 x_newLPN OUT NOCOPY VARCHAR2,
3745 x_concat_segments OUT NOCOPY VARCHAR2,
3746 x_context OUT NOCOPY VARCHAR2,
3747 x_return_status OUT NOCOPY VARCHAR2,
3748 x_msg_data OUT NOCOPY VARCHAR2,
3749 p_createnewlpn_flag IN VARCHAR2
3750 )
3751 IS
3752 l_flag1 NUMBER:=0;
3753 l_flag2 NUMBER:=0;
3754 l_locator_id NUMBER:=-1;
3755 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3756 BEGIN
3757 -- l_flag1 := 0;
3758 -- l_flag2 := 0;
3759
3760 BEGIN
3761 SELECT LPN_ID,
3762 INVENTORY_ITEM_ID,
3763 ORGANIZATION_ID,
3764 LOT_NUMBER,
3765 REVISION,
3766 SERIAL_NUMBER,
3767 SUBINVENTORY_CODE,
3768 LOCATOR_ID,
3769 PARENT_LPN_ID,
3770 SEALED_STATUS,
3771 GROSS_WEIGHT_UOM_CODE,
3772 GROSS_WEIGHT,
3773 CONTENT_VOLUME_UOM_CODE,
3774 CONTENT_VOLUME,
3775 SOURCE_TYPE_ID,
3776 SOURCE_HEADER_ID,
3777 SOURCE_NAME,
3778 SOURCE_LINE_ID,
3779 SOURCE_LINE_DETAIL_ID,
3780 cost_group_id,
3781 'FALSE',
3782 1,
3783 LOCATOR_ID,
3784 LPN_CONTEXT
3785 INTO x_lpn_id,
3786 x_inventory_item_id,
3787 x_organization_id,
3788 x_lot_number,
3789 x_revision,
3790 x_serial_number,
3791 x_subinventory,
3792 x_locator_id,
3793 x_parent_lpn_id,
3794 x_sealed_status,
3795 x_gross_weight_uom_code,
3796 x_gross_weight,
3797 x_content_volume_uom_code,
3798 x_content_volume,
3799 x_source_type_id,
3800 x_source_header_id,
3801 x_source_name,
3802 x_source_line_id,
3803 x_source_line_detail_id,
3804 x_cost_group_id,
3805 x_newLPN,
3806 l_flag1,
3807 l_locator_id,
3808 x_context
3809 FROM wms_license_plate_numbers
3810 WHERE license_plate_number = p_lpn;
3811
3812 EXCEPTION
3813 WHEN no_data_found THEN
3814
3815 x_newLPN := 'TRUE';
3816 x_concat_segments := 'NULL';
3817
3818 -- Create new lpn
3819 IF (p_createnewlpn_flag = 'TRUE') THEN
3820 inv_rcv_common_apis.create_lpn(
3821 p_organization_id,
3822 p_lpn,
3823 x_lpn_id,
3824 x_return_status,
3825 x_msg_data);
3826 END IF;
3827
3828 -- return;
3829 END;
3830
3831 -- Only get from milk if the locator is not null
3832 IF (l_flag1 = 1 AND Nvl(l_locator_id,-1)<>-1) THEN
3833 select 1,
3834 milk.concatenated_segments
3835 INTO l_flag2,
3836 x_concat_segments
3837 FROM wms_license_plate_numbers w,
3838 mtl_item_locations_kfv milk
3839 WHERE w.license_plate_number = p_lpn
3840 AND w.locator_id = milk.inventory_location_id
3841 AND w.organization_id = milk.organization_id;
3842
3843 IF l_flag2 = 0 THEN
3844 x_concat_segments := 'NULL';
3845 END IF;
3846 END IF;
3847 END CHECK_LPN_LOV;
3848
3849 /**********************************************************************************
3850 WMS - PJM Integration Enhancements
3851 Differences from CHECK_LPN_LOV
3852 1. Returns the locator concatenated segments without SEGMENT19 and SEGMENT20.
3853 by making a call to the procedure INV_PROJECT.GET_LOCSEGS
3854 2. Returns the Project ID, Project Number, Task ID and Task Number associated
3855 with the locator by making a call to the package INV_PROJECT.
3856 **********************************************************************************/
3857 PROCEDURE CHECK_PJM_LPN_LOV
3858 ( p_lpn IN VARCHAR2,
3859 p_organization_id IN NUMBER,
3860 x_lpn_id OUT NOCOPY NUMBER,
3861 x_inventory_item_id OUT NOCOPY NUMBER,
3862 x_organization_id OUT NOCOPY NUMBER,
3863 x_lot_number OUT NOCOPY VARCHAR2,
3864 x_revision OUT NOCOPY VARCHAR2,
3865 x_serial_number OUT NOCOPY VARCHAR2,
3866 x_subinventory OUT NOCOPY VARCHAR2,
3867 x_locator_id OUT NOCOPY NUMBER,
3868 x_parent_lpn_id OUT NOCOPY NUMBER,
3869 x_sealed_status OUT NOCOPY NUMBER,
3870 x_gross_weight OUT NOCOPY NUMBER,
3871 x_gross_weight_uom_code OUT NOCOPY VARCHAR2,
3872 x_content_volume OUT NOCOPY NUMBER,
3873 x_content_volume_uom_code OUT NOCOPY VARCHAR2,
3874 x_source_type_id OUT NOCOPY NUMBER,
3875 x_source_header_id OUT NOCOPY NUMBER,
3876 x_source_name OUT NOCOPY VARCHAR2,
3877 x_source_line_id OUT NOCOPY NUMBER,
3878 x_source_line_detail_id OUT NOCOPY NUMBER,
3879 x_cost_group_id OUT NOCOPY NUMBER,
3880 x_newLPN OUT NOCOPY VARCHAR2,
3881 x_concat_segments OUT NOCOPY VARCHAR2,
3882 x_project_id OUT NOCOPY VARCHAR2,
3883 x_project_number OUT NOCOPY VARCHAR2,
3884 x_task_id OUT NOCOPY VARCHAR2,
3885 x_task_number OUT NOCOPY VARCHAR2,
3886 x_context OUT NOCOPY VARCHAR2,
3887 x_return_status OUT NOCOPY VARCHAR2,
3888 x_msg_data OUT NOCOPY VARCHAR2,
3889 p_createnewlpn_flag IN VARCHAR2
3890 )
3891 IS
3892 l_locator_id NUMBER:=-1;
3893 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3894 BEGIN
3895 BEGIN
3896 SELECT LPN_ID,
3897 INVENTORY_ITEM_ID,
3898 ORGANIZATION_ID,
3899 LOT_NUMBER,
3900 REVISION,
3901 SERIAL_NUMBER,
3902 SUBINVENTORY_CODE,
3903 LOCATOR_ID,
3904 PARENT_LPN_ID,
3905 SEALED_STATUS,
3906 GROSS_WEIGHT_UOM_CODE,
3907 GROSS_WEIGHT,
3908 CONTENT_VOLUME_UOM_CODE,
3909 CONTENT_VOLUME,
3910 SOURCE_TYPE_ID,
3911 SOURCE_HEADER_ID,
3912 SOURCE_NAME,
3913 SOURCE_LINE_ID,
3914 SOURCE_LINE_DETAIL_ID,
3915 cost_group_id,
3916 'FALSE',
3917 LOCATOR_ID,
3918 LPN_CONTEXT,
3919 INV_PROJECT.GET_LOCSEGS(LOCATOR_ID,ORGANIZATION_ID),
3920 INV_PROJECT.GET_PROJECT_ID,
3921 INV_PROJECT.GET_PROJECT_NUMBER,
3922 INV_PROJECT.GET_TASK_ID,
3923 INV_PROJECT.GET_TASK_NUMBER
3924 INTO x_lpn_id,
3925 x_inventory_item_id,
3926 x_organization_id,
3927 x_lot_number,
3928 x_revision,
3929 x_serial_number,
3930 x_subinventory,
3931 x_locator_id,
3932 x_parent_lpn_id,
3933 x_sealed_status,
3934 x_gross_weight_uom_code,
3935 x_gross_weight,
3936 x_content_volume_uom_code,
3937 x_content_volume,
3938 x_source_type_id,
3939 x_source_header_id,
3940 x_source_name,
3941 x_source_line_id,
3942 x_source_line_detail_id,
3943 x_cost_group_id,
3944 x_newLPN,
3945 l_locator_id,
3946 x_context,
3947 x_concat_segments,
3948 x_project_id,
3949 x_project_number,
3950 x_task_id,
3951 x_task_number
3952 FROM wms_license_plate_numbers
3953 WHERE license_plate_number = p_lpn;
3954
3955 EXCEPTION
3956 WHEN no_data_found THEN
3957 x_newLPN := 'TRUE';
3958 x_concat_segments := 'NULL';
3959 -- Create new lpn
3960 IF (p_createnewlpn_flag = 'TRUE') THEN
3961 inv_rcv_common_apis.create_lpn
3962 (
3963 p_organization_id,
3964 p_lpn,
3965 x_lpn_id,
3966 x_return_status,
3967 x_msg_data
3968 );
3969 END IF;
3970 END;
3971 END CHECK_PJM_LPN_LOV;
3972
3973
3974 PROCEDURE GET_CONTEXT_LPN_LOV
3975 (x_lpn_lov OUT NOCOPY t_genref,
3976 p_organization_id IN NUMBER,
3977 p_context IN VARCHAR2,
3978 p_lpn IN VARCHAR2
3979 )
3980 IS
3981 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3982 BEGIN
3983 OPEN x_lpn_lov FOR
3984 SELECT distinct
3985 license_plate_number,
3986 lpn_id,
3987 NVL(inventory_item_id, 0),
3988 NVL(organization_id, 0),
3989 revision,
3990 lot_number,
3991 serial_number,
3992 subinventory_code,
3993 NVL(locator_id, 0),
3994 NVL(parent_lpn_id, 0),
3995 NVL(sealed_status, 2),
3996 gross_weight_uom_code,
3997 NVL(gross_weight, 0),
3998 content_volume_uom_code,
3999 NVL(content_volume, 0)
4000 FROM wms_license_plate_numbers
4001 WHERE license_plate_number LIKE (p_lpn)
4002 AND organization_id = p_organization_id
4003 AND lpn_context = NVL(TO_NUMBER(p_context), lpn_context);
4004
4005 END GET_CONTEXT_LPN_LOV;
4006
4007 --"Returns"
4008 PROCEDURE GET_RETURN_LPN
4009 (x_lpn_lov OUT NOCOPY t_genref,
4010 p_org_id IN NUMBER,
4011 p_lpn IN VARCHAR2)
4012 IS
4013 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4014 BEGIN
4015
4016 OPEN x_lpn_lov FOR
4017 SELECT wlpn.license_plate_number,
4018 wlpn.lpn_id,
4019 NVL(wlpn.inventory_item_id, 0),
4020 NVL(wlpn.organization_id, 0),
4021 wlpn.revision,
4022 wlpn.lot_number,
4023 wlpn.serial_number,
4024 'FULL', -- Instead of Subinventory
4025 NVL(wlpn.locator_id, 0),
4026 NVL(wlpn.parent_lpn_id, 0),
4027 NVL(wlpn.sealed_status, 2),
4028 wlpn.gross_weight_uom_code,
4029 NVL(wlpn.gross_weight, 0),
4030 wlpn.content_volume_uom_code,
4031 NVL(wlpn.content_volume, 0)
4032 FROM wms_license_plate_numbers wlpn
4033 WHERE wlpn.license_plate_number LIKE (p_lpn)
4034 AND wlpn.organization_id = p_org_id
4035 AND WMS_RETURN_SV.GET_LPN_MARKED_STATUS(wlpn.lpn_id, wlpn.organization_id)='FULL'
4036 UNION ALL
4037 SELECT wlpn.license_plate_number,
4038 wlpn.lpn_id,
4039 NVL(wlpn.inventory_item_id, 0),
4040 NVL(wlpn.organization_id, 0),
4041 wlpn.revision,
4042 wlpn.lot_number,
4043 wlpn.serial_number,
4044 'PARTIAL', -- Instead of Subinventory
4045 NVL(wlpn.locator_id, 0),
4046 NVL(wlpn.parent_lpn_id, 0),
4047 NVL(wlpn.sealed_status, 2),
4048 wlpn.gross_weight_uom_code,
4049 NVL(wlpn.gross_weight, 0),
4050 wlpn.content_volume_uom_code,
4051 NVL(wlpn.content_volume, 0)
4052 FROM wms_license_plate_numbers wlpn
4053 WHERE wlpn.license_plate_number LIKE (p_lpn)
4054 AND wlpn.organization_id = p_org_id
4055 AND WMS_RETURN_SV.GET_LPN_MARKED_STATUS(wlpn.lpn_id, wlpn.organization_id)='PARTIAL'
4056 ORDER BY 1;
4057
4058 END GET_RETURN_LPN;
4059 --"Returns"
4060
4061
4062 PROCEDURE GET_REQEXP_LPN
4063 (x_lpn_lov OUT NOCOPY t_genref ,
4064 p_lpn IN VARCHAR2 ,
4065 p_requisition_header_id IN VARCHAR2 ,
4066 p_mode IN VARCHAR2 DEFAULT NULL,
4067 p_inventory_item_id IN VARCHAR2 DEFAULT NULL
4068 )
4069 IS
4070 l_req_num VARCHAR2(10);
4071 l_progress VARCHAR2(10);
4072 l_order_header_id NUMBER;
4073 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4074 BEGIN
4075 l_progress := '10';
4076 IF (p_requisition_header_id IS NOT NULL) THEN
4077 SELECT segment1
4078 INTO l_req_num
4079 FROM po_requisition_headers_all
4080 WHERE requisition_header_id = p_requisition_header_id;
4081 END IF;
4082
4083 l_progress := '20';
4084 SELECT header_id
4085 INTO l_order_header_id
4086 FROM oe_order_headers_all
4087 WHERE orig_sys_document_ref = l_req_num
4088 AND order_source_id = 10;
4089 --AND order_type_id = 1023;
4090
4091 l_progress := '30';
4092
4093 -- Nested LPN changes. Changed this lov to show all child LPNs also.
4094 -- If Mode is confirm then show only those LPNs with contents, otherwise show all LPNs
4095
4096 IF p_mode IS NULL THEN
4097 OPEN x_lpn_lov FOR
4098 SELECT distinct wlpn.license_plate_number
4099 , wlpn.lpn_id
4100 , count_row.n
4101 FROM wsh_delivery_details_ob_grp_v wdd
4102 , wsh_delivery_assignments_v wda
4103 , wsh_delivery_details_ob_grp_v wdd1
4104 , wms_license_plate_numbers wlpn, (SELECT count(*) n
4105 FROM wsh_delivery_details_ob_grp_v wdd
4106 , wms_license_plate_numbers wlpn
4107 WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4108 FROM wsh_delivery_assignments_v wda
4109 , wsh_delivery_details_ob_grp_v wdd
4110 WHERE wda.delivery_detail_id in (select delivery_detail_id
4111 FROM wsh_delivery_details_ob_grp_v
4112 WHERE source_header_id = l_order_header_id)
4113 AND wda.PARENT_DELIVERY_DETAIL_ID = wdd.delivery_detail_id)
4114 AND wlpn.lpn_context = 6
4115 AND wlpn.organization_id = wdd.organization_id
4116 AND wlpn.outermost_lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4117 WHERE wdd.source_header_id = l_order_header_id
4118 AND wdd.delivery_detail_id = wda.delivery_detail_id
4119 AND wdd1.delivery_detail_id = wda.PARENT_DELIVERY_DETAIL_ID
4120 AND wlpn.lpn_context = 6
4121 AND wlpn.organization_id = wdd1.organization_id
4122 AND wlpn.outermost_lpn_id = NVL(wdd1.lpn_id, -9999)
4123 AND wlpn.license_plate_number LIKE (p_lpn)
4124 ORDER BY wlpn.license_plate_number;
4125 ELSIF p_mode = 'E' THEN
4126 /* OPEN x_lpn_lov FOR
4127 SELECT distinct wlpn.license_plate_number
4128 , wlpn.lpn_id
4129 , count_row.n
4130 FROM wsh_delivery_details_ob_grp_v wdd
4131 , wsh_delivery_assignments_v wda
4132 , wsh_delivery_details_ob_grp_v wdd1
4133 , wms_license_plate_numbers wlpn, (SELECT count(*) n
4134 FROM wsh_delivery_details_ob_grp_v wdd
4135 , wms_license_plate_numbers wlpn
4136 WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4137 FROM wsh_delivery_assignments_v wda
4138 , wsh_delivery_details_ob_grp_v wdd
4139 WHERE wda.delivery_detail_id in (select delivery_detail_id
4140 FROM wsh_delivery_details_ob_grp_v
4141 WHERE source_header_id = l_order_header_id)
4142 AND wda.PARENT_DELIVERY_DETAIL_ID = wdd.delivery_detail_id)
4143 AND wlpn.lpn_context = 6
4144 AND wlpn.organization_id = wdd.organization_id
4145 AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4146 WHERE wdd.source_header_id = l_order_header_id
4147 AND wdd.delivery_detail_id = wda.delivery_detail_id
4148 AND wdd1.delivery_detail_id = wda.PARENT_DELIVERY_DETAIL_ID
4149 AND wlpn.lpn_context = 6
4150 AND wlpn.organization_id = wdd1.organization_id
4151 AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4152 AND wlpn.license_plate_number LIKE (p_lpn)
4153 ORDER BY wlpn.license_plate_number;*/
4154
4155 -- Getting Count is deprecated from Patchset J We will get count from the validation logic itself.
4156 OPEN x_lpn_lov FOR
4157 SELECT distinct wln.license_plate_number
4158 , wln.lpn_id
4159 , 1
4160 FROM wms_license_plate_numbers wln,
4161 wsh_delivery_details_ob_grp_v wdd
4162 WHERE wln.lpn_context= 6
4163 AND wln.lpn_id = wdd.lpn_id
4164 AND wln.license_plate_number LIKE (p_lpn)
4165 ORDER BY wln.license_plate_number;
4166
4167 ELSIF p_mode = 'C' THEN
4168 -- This is changed based on Item Info, case for Item Initiated Receipt.
4169 -- If Item info is present or passed from the UI then LPN should be restrcied based on Item
4170 -- Otherwise all the LPN's for the shipment should be displayed in the LOV
4171
4172 if p_inventory_item_id is null then
4173 OPEN x_lpn_lov FOR
4174 SELECT distinct wlpn.license_plate_number
4175 , wlpn.lpn_id
4176 , count_row.n
4177 FROM wsh_delivery_details_ob_grp_v wdd
4178 , wsh_delivery_assignments_v wda
4179 , wsh_delivery_details_ob_grp_v wdd1
4180 , wms_license_plate_numbers wlpn, (SELECT count(*) n
4181 FROM wsh_delivery_details_ob_grp_v wdd
4182 , wms_license_plate_numbers wlpn
4183 WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4184 FROM wsh_delivery_assignments_v wda
4185 , wsh_delivery_details_ob_grp_v wdd
4186 WHERE wda.delivery_detail_id in (select delivery_detail_id
4187 FROM wsh_delivery_details_ob_grp_v
4188 WHERE source_header_id = l_order_header_id)
4189 AND wda.PARENT_DELIVERY_DETAIL_ID = wdd.delivery_detail_id)
4190 AND wlpn.lpn_context = 6
4191 AND wlpn.organization_id = wdd.organization_id
4192 -- Nested LPN changes
4193 AND EXISTS (SELECT parent_lpn_id
4194 FROM wms_lpn_contents wlc
4195 WHERE parent_lpn_id = wlpn.lpn_id)
4196 AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4197 WHERE wdd.source_header_id = l_order_header_id
4198 AND wdd.delivery_detail_id = wda.delivery_detail_id
4199 AND wdd1.delivery_detail_id = wda.PARENT_DELIVERY_DETAIL_ID
4200 AND wlpn.lpn_context = 6
4201 AND wlpn.organization_id = wdd1.organization_id
4202 AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4203 AND wlpn.license_plate_number LIKE (p_lpn)
4204 -- Nested LPN changes
4205 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents wlc WHERE parent_lpn_id = wlpn.lpn_id)
4206 ORDER BY wlpn.license_plate_number;
4207 Else
4208 OPEN x_lpn_lov FOR
4209 SELECT distinct wlpn.license_plate_number
4210 , wlpn.lpn_id
4211 , count_row.n
4212 FROM wsh_delivery_details_ob_grp_v wdd
4213 , wsh_delivery_assignments_v wda
4214 , wsh_delivery_details_ob_grp_v wdd1
4215 , wms_license_plate_numbers wlpn, (SELECT count(*) n
4216 FROM wsh_delivery_details_ob_grp_v wdd
4217 , wms_license_plate_numbers wlpn
4218 WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4219 FROM wsh_delivery_assignments_v wda
4220 , wsh_delivery_details_ob_grp_v wdd
4221 WHERE wda.delivery_detail_id in (select delivery_detail_id
4222 FROM wsh_delivery_details_ob_grp_v
4223 WHERE source_header_id = l_order_header_id)
4224 AND wda.PARENT_DELIVERY_DETAIL_ID = wdd.delivery_detail_id)
4225 AND wlpn.lpn_context = 6
4226 AND wlpn.organization_id = wdd.organization_id
4227 -- Nested LPN changes
4228 AND EXISTS (SELECT parent_lpn_id
4229 FROM wms_lpn_contents wlc
4230 WHERE parent_lpn_id = wlpn.lpn_id)
4231 AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4232 WHERE wdd.source_header_id = l_order_header_id
4233 AND wdd.delivery_detail_id = wda.delivery_detail_id
4234 AND wdd1.delivery_detail_id = wda.PARENT_DELIVERY_DETAIL_ID
4235 AND wlpn.lpn_context = 6
4236 AND wlpn.organization_id = wdd1.organization_id
4237 AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4238 AND wlpn.license_plate_number LIKE (p_lpn)
4239 -- Nested LPN changes
4240 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents wlc WHERE parent_lpn_id = wlpn.lpn_id
4241 and wlc.inventory_item_id = p_inventory_item_id)
4242 ORDER BY wlpn.license_plate_number;
4243 End if;
4244 END IF;
4245
4246 END GET_REQEXP_LPN;
4247
4248
4249 PROCEDURE GET_UPDATE_LPN
4250 (x_lpn_lov OUT NOCOPY t_genref,
4251 p_org_id IN NUMBER,
4252 p_lpn IN VARCHAR2)
4253 IS
4254 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4255 BEGIN
4256
4257 OPEN x_lpn_lov FOR
4258 SELECT license_plate_number,
4259 lpn_id,
4260 inventory_item_id,
4261 organization_id,
4262 revision,
4263 lot_number,
4264 serial_number,
4265 subinventory_code,
4266 locator_id,
4267 parent_lpn_id,
4268 NVL(sealed_status, 2),
4269 gross_weight_uom_code,
4270 NVL(gross_weight, 0),
4271 content_volume_uom_code,
4272 NVL(content_volume, 0),
4273 lpn_context --Added for Bug#6504032
4274 FROM wms_license_plate_numbers wlpn
4275 WHERE wlpn.organization_id = p_org_id
4276 AND wlpn.license_plate_number LIKE (p_lpn)
4277 AND wlpn.lpn_context IN (1, 2, 3 , 5, 8, 11); --Inventory, pregenerated, picked contexts /*Resides in WIP(2) added for bug#3953941*/
4278 -- Added 3 to pick LPNS in status 'Resides n Receiving' Bug 5501058
4279 --Added context 8 for Bug#6870562
4280 END GET_UPDATE_LPN;
4281
4282
4283
4284 PROCEDURE GET_RECONFIG_LPN
4285 (x_lpn_lov OUT NOCOPY t_genref,
4286 p_org_id IN NUMBER,
4287 p_lpn IN VARCHAR2)
4288 IS
4289 BEGIN
4290
4291 OPEN x_lpn_lov FOR
4292 select distinct outer.license_plate_number,
4293 outer.subinventory_code,
4294 milk.concatenated_segments,
4295 outer.locator_id,
4296 outer.lpn_id,
4297 outer.lpn_context,
4298 NVL(outer.sealed_status, 2),
4299 outer.gross_weight_uom_code,
4300 NVL(outer.gross_weight, 0),
4301 outer.content_volume_uom_code,
4302 NVL(outer.content_volume, 0)
4303 from wms_license_plate_numbers outer, wms_license_plate_numbers inner,
4304 mtl_item_locations_kfv milk
4305 where inner.outermost_lpn_id <> inner.lpn_id
4306 AND inner.outermost_lpn_id = outer.lpn_id
4307 AND outer.locator_id = milk.inventory_location_id(+)
4308 and outer.lpn_context in (1, 11)
4309 and outer.organization_id = p_org_id
4310 and outer.license_plate_number LIKE (p_lpn);
4311 END GET_RECONFIG_LPN;
4312
4313
4314
4315
4316 FUNCTION SUB_LPN_CONTROLLED(p_subinventory_code IN VARCHAR2,
4317 p_org_id IN NUMBER)
4318 RETURN VARCHAR2
4319 IS
4320 l_ret_val VARCHAR2(1) := 'Y';
4321 l_lpn_cf_flag NUMBER;
4322 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4323 BEGIN
4324 IF (p_subinventory_code IS NULL) THEN
4325 RETURN 'Y';
4326 ELSE
4327 SELECT lpn_controlled_flag
4328 INTO l_lpn_cf_flag
4329 FROM MTL_SECONDARY_INVENTORIES msi
4330 WHERE msi.organization_id = p_org_id
4331 AND msi.secondary_inventory_name = p_subinventory_code;
4332
4333 IF ((l_lpn_cf_flag) IS NULL OR (l_lpn_cf_flag = 2)) THEN
4334 RETURN 'N';
4335 ELSE
4336 RETURN 'Y';
4337 END IF;
4338 END IF;
4339
4340 END SUB_LPN_CONTROLLED;
4341
4342 PROCEDURE GET_BULK_PACK_LPN
4343 (x_lpn_lov OUT NOCOPY t_genref,
4344 p_org_id IN NUMBER,
4345 p_lpn IN VARCHAR2,
4346 p_subinventory IN VARCHAR2,
4347 p_locator IN NUMBER
4348 )
4349 IS
4350 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4351 BEGIN
4352
4353 OPEN x_lpn_lov FOR
4354 SELECT license_plate_number,
4355 lpn_id,
4356 inventory_item_id,
4357 organization_id,
4358 revision,
4359 lot_number,
4360 serial_number,
4361 subinventory_code,
4362 locator_id,
4363 parent_lpn_id,
4364 NVL(sealed_status, 2),
4365 gross_weight_uom_code,
4366 NVL(gross_weight, 0),
4367 content_volume_uom_code,
4368 NVL(content_volume, 0)
4369 FROM wms_license_plate_numbers wlpn
4370 WHERE wlpn.organization_id = p_org_id
4371 AND wlpn.license_plate_number LIKE (p_lpn)
4372 AND wlpn.subinventory_code = nvl(p_subinventory,wlpn.subinventory_Code)
4373 AND wlpn.locator_id = decode(p_locator,0,wlpn.locator_id,p_locator)
4374 AND wlpn.inventory_item_id is not null
4375 AND wlpn.lpn_id NOT IN ( select content_lpn_id from mtl_material_transactions_temp where content_lpn_id = wlpn.lpn_id)
4376 AND wlpn.parent_lpn_id is null
4377 AND wlpn.lpn_context = 1
4378 ORDER BY license_plate_number; --Inventory
4379 END GET_BULK_PACK_LPN;
4380
4381
4382 PROCEDURE Get_Picked_Split_From_LPNs(
4383 x_lpn_lov OUT NOCOPY t_genref
4384 , p_organization_id IN NUMBER
4385 , p_lpn_id IN VARCHAR2
4386 ) IS
4387 BEGIN
4388 open x_lpn_lov for
4389 SELECT wlpn.license_plate_number,
4390 wlpn.lpn_id,
4391 NVL(wlpn.inventory_item_id, 0),
4392 NVL(wlpn.organization_id, 0),
4393 wlpn.revision,
4394 wlpn.lot_number,
4395 wlpn.serial_number,
4396 wlpn.subinventory_code,
4397 NVL(wlpn.locator_id, 0),
4398 NVL(wlpn.parent_lpn_id, 0),
4399 NVL(wlpn.sealed_status, 2),
4400 wlpn.gross_weight_uom_code,
4401 NVL(wlpn.gross_weight, 0),
4402 wlpn.content_volume_uom_code,
4403 NVL(wlpn.content_volume, 0),
4404 wdd.delivery_detail_id
4405 FROM wms_license_plate_numbers wlpn,
4406 wsh_delivery_details wdd
4407 WHERE wlpn.organization_id = p_organization_id
4408 AND wlpn.lpn_context = 11
4409 AND wlpn.license_plate_number LIKE (p_lpn_id)
4410 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_organization_id) ='Y'
4411 AND inv_material_status_grp.is_status_applicable (
4412 'TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_SPLIT, NULL,
4413 NULL, p_organization_id, NULL, wlpn.subinventory_code,
4414 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
4415 AND inv_material_status_grp.is_status_applicable (
4416 'TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_SPLIT, NULL,
4417 NULL, p_organization_id, NULL, wlpn.subinventory_code,
4418 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
4419 AND wdd.lpn_id = wlpn.lpn_id
4420 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
4421 ORDER BY license_plate_number;
4422 END Get_Picked_Split_From_LPNs;
4423
4424 --RTV Change 16197273
4425 --New navigation is created for split,under Inbound flow.
4426 --Created new prceedures for from and to_lpn fields specific for RTV ER.
4427
4428 PROCEDURE Get_Return_Split_From_LPNs(
4429 x_lpn_lov OUT NOCOPY t_genref
4430 , p_organization_id IN NUMBER
4431 , p_lpn_id IN VARCHAR2
4432 ) IS
4433
4434 l_rtv_shipment_flag VARCHAR2(1) := NVL(FND_PROFILE.VALUE('RCV_CREATE_SHIPMENT_FOR_RETURNS'),'N');
4435
4436 BEGIN
4437
4438 if l_rtv_shipment_flag = 'Y' then
4439
4440 open x_lpn_lov for
4441 SELECT wlpn.license_plate_number,
4442 wlpn.lpn_id,
4443 NVL(wlpn.inventory_item_id, 0),
4444 NVL(wlpn.organization_id, 0),
4445 wlpn.revision,
4446 wlpn.lot_number,
4447 wlpn.serial_number,
4448 wlpn.subinventory_code,
4449 NVL(wlpn.locator_id, 0),
4450 NVL(wlpn.parent_lpn_id, 0),
4451 NVL(wlpn.sealed_status, 2),
4452 wlpn.gross_weight_uom_code,
4453 NVL(wlpn.gross_weight, 0),
4454 wlpn.content_volume_uom_code,
4455 NVL(wlpn.content_volume, 0),
4456 wdd.delivery_detail_id
4457 FROM wms_license_plate_numbers wlpn,
4458 wsh_delivery_details wdd ,
4459 wms_lpn_contents wlc
4460 WHERE wlpn.organization_id = p_organization_id
4461 AND wlpn.lpn_id = wlc.parent_lpn_id
4462 AND wlpn.lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
4463 AND wlc.source_name IS NOT null
4464 AND wlpn.license_plate_number LIKE (p_lpn_id)
4465 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_organization_id) ='Y'
4466 AND inv_material_status_grp.is_status_applicable (
4467 'TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_SPLIT, NULL,
4468 NULL, p_organization_id, NULL, wlpn.subinventory_code,
4469 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
4470 AND inv_material_status_grp.is_status_applicable (
4471 'TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_SPLIT, NULL,
4472 NULL, p_organization_id, NULL, wlpn.subinventory_code,
4473 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
4474 AND wdd.lpn_id = wlpn.lpn_id
4475 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
4476 ORDER BY license_plate_number;
4477
4478 ELSE
4479
4480 open x_lpn_lov for
4481 SELECT NULL,
4482 NULL,
4483 NULL,
4484 NULL,
4485 NULL,
4486 NULL,
4487 NULL,
4488 NULL,
4489 NULL,
4490 NULL,
4491 NULL,
4492 NULL,
4493 NULL,
4494 NULL,
4495 NULL,
4496 NULL
4497 FROM dual where 1=2;
4498
4499
4500 END IF ;
4501
4502 END Get_Return_Split_From_LPNs;
4503
4504 --RTV Change 16197273
4505
4506 PROCEDURE Get_Return_Split_To_LPNs(
4507 x_lpn_lov OUT NOCOPY t_genref
4508 , p_organization_id IN NUMBER
4509 , p_lpn_id IN VARCHAR2
4510 ) IS
4511
4512 BEGIN
4513 open x_lpn_lov for
4514 SELECT wlpn.license_plate_number,
4515 wlpn.lpn_id
4516
4517 FROM wms_license_plate_numbers wlpn
4518 WHERE wlpn.organization_id = p_organization_id
4519 AND wlpn.license_plate_number LIKE (p_lpn_id)
4520 AND wlpn.lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_PREGENERATED
4521 ORDER BY license_plate_number;
4522
4523 END Get_Return_Split_To_LPNs;
4524
4525
4526 PROCEDURE get_item_load_lpn_lov
4527 (x_lpn_lov OUT NOCOPY t_genref ,
4528 p_organization_id IN NUMBER ,
4529 p_lpn_id IN NUMBER ,
4530 p_lpn_context IN NUMBER ,
4531 p_employee_id IN NUMBER ,
4532 p_into_lpn IN VARCHAR2)
4533 IS
4534 BEGIN
4535
4536 -- If an LPN does not have the pregenerated LPN context and matches
4537 -- the LPN context of the source LPN, it must either be empty or
4538 -- be an LPN loaded for putaway by the same user/employee.
4539 OPEN x_lpn_lov FOR
4540 SELECT wlpn.license_plate_number,
4541 wlpn.lpn_id,
4542 wlpn.inventory_item_id,
4543 wlpn.organization_id,
4544 wlpn.revision,
4545 wlpn.lot_number,
4546 wlpn.serial_number,
4547 wlpn.subinventory_code,
4548 wlpn.locator_id,
4549 wlpn.parent_lpn_id,
4550 NVL(wlpn.sealed_status, 2),
4551 wlpn.gross_weight_uom_code,
4552 NVL(wlpn.gross_weight, 0),
4553 wlpn.content_volume_uom_code,
4554 NVL(wlpn.content_volume, 0)
4555 FROM wms_license_plate_numbers wlpn
4556 WHERE wlpn.organization_id = p_organization_id
4557 AND wlpn.lpn_id <> p_lpn_id
4558 AND (wlpn.lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_PREGENERATED
4559 OR (wlpn.lpn_context = p_lpn_context
4560 AND ( (NOT EXISTS (SELECT 'LPN_HAS_MATERIAL'
4561 FROM wms_lpn_contents
4562 WHERE parent_lpn_id IN (SELECT wlpn1.lpn_id
4563 FROM
4564 wms_license_plate_numbers wlpn1
4565 START WITH
4566 wlpn1.lpn_id =
4567 wlpn.outermost_lpn_id
4568 CONNECT BY PRIOR
4569 wlpn1.lpn_id = wlpn1.parent_lpn_id)))
4570 OR
4571 (EXISTS (SELECT 'LOADED_BY_SAME_USER'
4572 FROM mtl_material_transactions_temp mmtt,
4573 wms_dispatched_tasks wdt
4574 WHERE mmtt.organization_id = p_organization_id
4575 AND mmtt.transaction_temp_id = wdt.transaction_temp_id
4576 AND wdt.organization_id = p_organization_id
4577 AND wdt.task_type = 2
4578 AND wdt.status = 4
4579 AND wdt.person_id = p_employee_id
4580 AND mmtt.lpn_id IN (SELECT lpn_id
4581 FROM wms_license_plate_numbers
4582 START WITH lpn_id = wlpn.outermost_lpn_id
4583 CONNECT BY PRIOR lpn_id = parent_lpn_id
4584 )
4585 )
4586 )
4587 )
4588 )
4589 )
4590 AND wlpn.license_plate_number LIKE (p_into_lpn)
4591 AND inv_material_status_grp.is_status_applicable('TRUE',
4592 NULL,
4593 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
4594 NULL,
4595 NULL,
4596 p_organization_id,
4597 NULL,
4598 wlpn.subinventory_code,
4599 wlpn.locator_id,
4600 NULL,
4601 NULL,
4602 'Z') = 'Y'
4603 AND inv_material_status_grp.is_status_applicable('TRUE',
4604 NULL,
4605 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
4606 NULL,
4607 NULL,
4608 p_organization_id,
4609 NULL,
4610 wlpn.subinventory_code,
4611 wlpn.locator_id,
4612 NULL,
4613 NULL,
4614 'L') = 'Y'
4615 ORDER BY wlpn.license_plate_number;
4616
4617 END get_item_load_lpn_lov;
4618
4619 PROCEDURE get_from_gtmp_lov
4620 (x_lpn_lov OUT NOCOPY t_genref ,
4621 p_organization_id IN NUMBER ,
4622 p_drop_type IN VARCHAR2 ,
4623 p_lpn_name IN VARCHAR2
4624 )
4625 IS
4626
4627
4628 BEGIN
4629
4630 OPEN x_lpn_lov FOR
4631 SELECT DISTINCT
4632 wlpn.license_plate_number, wlpn.lpn_id,
4633 NVL (wlpn.inventory_item_id, 0),
4634 NVL (wlpn.organization_id, 0),
4635 wlpn.revision,
4636 wlpn.lot_number,
4637 wlpn.serial_number,
4638 wlpn.subinventory_code,
4639 NVL (wlpn.locator_id, 0),
4640 NVL (wlpn.parent_lpn_id, 0),
4641 NVL (wlpn.sealed_status, 2),
4642 wlpn.gross_weight_uom_code,
4643 NVL (wlpn.gross_weight, 0),
4644 wlpn.content_volume_uom_code,
4645 NVL (wlpn.content_volume, 0),
4646 milk.concatenated_segments,
4647 wlpn.lpn_context
4648 FROM wms_license_plate_numbers wlpn,
4649 mtl_item_locations_kfv milk,
4650 wms_putaway_group_tasks_gtmp wpgt
4651 WHERE wlpn.organization_id = TO_NUMBER (p_organization_id)
4652 AND wlpn.organization_id = milk.organization_id(+)
4653 AND wlpn.locator_id = milk.inventory_location_id(+)
4654 AND wlpn.lpn_id = wpgt.lpn_id
4655 AND wpgt.row_type = 'Group Task'
4656 AND drop_type = p_drop_type
4657 AND wlpn.license_plate_number LIKE p_lpn_name
4658 ORDER BY wlpn.license_plate_number;
4659 END get_from_gtmp_lov;
4660
4661 -- Procedure to get lpns in status 5 and 1 for the org, sub,locator combination. For bug 12853197
4662 PROCEDURE GET_PICK_DROP_SUBXFR_LPN_LOV
4663 (x_lpn_lov OUT NOCOPY t_genref ,
4664 p_lpn IN VARCHAR2 ,
4665 p_pick_to_lpn_id IN NUMBER ,
4666 p_org_id IN NUMBER ,
4667 p_drop_sub IN VARCHAR2 ,
4668 p_drop_loc IN NUMBER
4669 )
4670 -- passed p_drop_sub and p_drop_loc --vipartha
4671 IS
4672 l_lpn VARCHAR2(50);
4673 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4674
4675 BEGIN
4676
4677 IF p_lpn IS NOT NULL then
4678 l_lpn := p_lpn;
4679 ELSE
4680 l_lpn := '%';
4681 END IF;
4682
4683 OPEN x_lpn_lov FOR
4684 SELECT DISTINCT wlpn.license_plate_number
4685 , wlpn.lpn_id
4686 , NVL(wlpn.inventory_item_id, 0)
4687 , NVL(wlpn.organization_id, 0)
4688 , wlpn.revision
4689 , wlpn.lot_number
4690 , wlpn.serial_number
4691 , wlpn.subinventory_code
4692 , NVL(wlpn.locator_id, 0)
4693 , NVL(wlpn.parent_lpn_id, 0)
4694 , NVL(wlpn.sealed_status, 2)
4695 , wlpn.gross_weight_uom_code
4696 , NVL(wlpn.gross_weight, 0)
4697 , wlpn.content_volume_uom_code
4698 , NVL(wlpn.content_volume, 0)
4699 , milk.concatenated_segments
4700 , wlpn.lpn_context
4701 FROM wms_license_plate_numbers wlpn
4702 , mtl_item_locations_kfv milk
4703 WHERE wlpn.organization_id = milk.organization_id (+)
4704 AND wlpn.locator_id = milk.inventory_location_id (+)
4705 AND wlpn.outermost_lpn_id = wlpn.lpn_id
4706 AND wlpn.lpn_context = 1
4707 AND wlpn.subinventory_code = p_drop_sub
4708 AND wlpn.locator_id = p_drop_loc
4709 AND wlpn.license_plate_number LIKE l_lpn
4710 AND wlpn.organization_id = p_org_id
4711 /* AND WMS_Container2_PUB.validate_pick_drop_lpn
4712 ( 1.0
4713 , 'F'
4714 , p_pick_to_lpn_id
4715 , p_org_id
4716 , wlpn.license_plate_number
4717 , p_drop_sub
4718 , p_drop_loc
4719 ) = 1 */
4720 UNION
4721 SELECT DISTINCT wlpn.license_plate_number
4722 , wlpn.lpn_id
4723 , NVL(wlpn.inventory_item_id, 0)
4724 , NVL(wlpn.organization_id, 0)
4725 , wlpn.revision
4726 , wlpn.lot_number
4727 , wlpn.serial_number
4728 , wlpn.subinventory_code
4729 , NVL(wlpn.locator_id, 0)
4730 , NVL(wlpn.parent_lpn_id, 0)
4731 , NVL(wlpn.sealed_status, 2)
4732 , wlpn.gross_weight_uom_code
4733 , NVL(wlpn.gross_weight, 0)
4734 , wlpn.content_volume_uom_code
4735 , NVL(wlpn.content_volume, 0)
4736 , milk.concatenated_segments
4737 , wlpn.lpn_context
4738 FROM wms_license_plate_numbers wlpn
4739 , mtl_item_locations_kfv milk
4740 WHERE wlpn.organization_id = milk.organization_id (+)
4741 AND wlpn.locator_id = milk.inventory_location_id (+)
4742 AND wlpn.outermost_lpn_id = wlpn.lpn_id
4743 AND wlpn.lpn_context = 5
4744 AND wlpn.license_plate_number LIKE l_lpn
4745 AND wlpn.organization_id = p_org_id
4746 ORDER BY license_plate_number;
4747
4748 END GET_PICK_DROP_SUBXFR_LPN_LOV;
4749
4750 END WMS_LPN_LOVS;