[Home] [Help]
PACKAGE BODY: APPS.WMS_LPN_LOVS
Source
1 PACKAGE BODY WMS_LPN_LOVS AS
2 /* $Header: WMSLPNLB.pls 120.7.12010000.2 2008/08/05 22:56:22 ammathew 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 ORDER BY license_plate_number;
1837 ELSE
1838
1839 IF (l_debug = 1) THEN
1840 mydebug('pack / unpack and inv lpns with minimal check ; pregen=false');
1841 END IF;
1842
1843 -- Select LPNs with context "1"
1844 open x_lpn_lov for
1845 SELECT wlpn.license_plate_number,
1846 wlpn.lpn_id,
1847 wlpn.inventory_item_id,
1848 wlpn.organization_id,
1849 wlpn.revision,
1850 wlpn.lot_number,
1851 wlpn.serial_number,
1852 wlpn.subinventory_code,
1853 wlpn.locator_id,
1854 wlpn.parent_lpn_id,
1855 NVL(wlpn.sealed_status, 2),
1856 wlpn.gross_weight_uom_code,
1857 NVL(wlpn.gross_weight, 0),
1858 wlpn.content_volume_uom_code,
1859 NVL(wlpn.content_volume, 0) ,
1860 wlpn.lpn_context
1861 FROM wms_license_plate_numbers wlpn
1862 WHERE wlpn.organization_id = p_org_id
1863 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1864 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(wlpn.parent_lpn_id, 0))
1865 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1866 AND wlpn.lpn_context = 1
1867 AND wlpn.subinventory_code = p_sub
1868 AND wlpn.locator_id = p_loc_id
1869 ORDER BY license_plate_number;
1870 END IF;
1871
1872 END GET_PK_UNPK_INV_LPNS_NO_CHECK ;
1873
1874
1875
1876 --Procedure to fetch LPNs when when transaction type is Split
1877 --and the LPN Context passed is 0 (fetch LPNs with context 1 and 5)
1878 PROCEDURE GET_SPLIT_INV_LPNS(x_lpn_lov OUT NOCOPY t_genref ,
1879 p_org_id IN NUMBER ,
1880 p_sub IN VARCHAR2 := NULL ,
1881 p_loc_id IN VARCHAR2 := NULL ,
1882 p_not_lpn_id IN VARCHAR2 := NULL ,
1883 p_parent_lpn_id IN VARCHAR2 := '0' ,
1884 p_txn_type_id IN NUMBER := 0 ,
1885 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
1886 p_lpn IN VARCHAR2,
1887 p_context IN NUMBER := 0,
1888 p_project_id IN NUMBER := NULL,
1889 p_task_id IN NUMBER := NULL,
1890 p_mtrl_sts_check IN VARCHAR2 := 'Y' --Bug 3980914
1891 )
1892 IS
1893 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1894 BEGIN
1895 IF p_incl_pre_gen_lpn = 'TRUE' THEN
1896 IF (l_debug = 1) THEN
1897 mydebug('split and inv; pregen=true');
1898 END IF;
1899 --Select LPNs with context "1" or "5"
1900 open x_lpn_lov for
1901 SELECT wlpn.license_plate_number,
1902 wlpn.lpn_id,
1903 wlpn.inventory_item_id,
1904 wlpn.organization_id,
1905 revision,
1906 wlpn.lot_number,
1907 wlpn.serial_number,
1908 wlpn.subinventory_code,
1909 wlpn.locator_id,
1910 wlpn.parent_lpn_id,
1911 NVL(wlpn.sealed_status, 2),
1912 wlpn.gross_weight_uom_code,
1913 NVL(wlpn.gross_weight, 0),
1914 wlpn.content_volume_uom_code,
1915 NVL(wlpn.content_volume, 0),
1916 wlpn.lpn_context --Added for bug#4202068.
1917 FROM wms_license_plate_numbers wlpn
1918 WHERE wlpn.organization_id = p_org_id
1919 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1920 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1921 NVL(wlpn.parent_lpn_id, 0))
1922 AND wlpn.license_plate_number LIKE (p_lpn)
1923 /* Bug 3980914 -For LPN's with context 5, the following condition is not required
1924 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
1925 AND (wlpn.lpn_context = 5)
1926 UNION ALL
1927 SELECT wlpn.license_plate_number,
1928 wlpn.lpn_id,
1929 NVL(wlpn.inventory_item_id, 0),
1930 NVL(wlpn.organization_id, 0),
1931 wlpn.revision,
1932 wlpn.lot_number,
1933 wlpn.serial_number,
1934 wlpn.subinventory_code,
1935 NVL(wlpn.locator_id, 0),
1936 NVL(wlpn.parent_lpn_id, 0),
1937 NVL(wlpn.sealed_status, 2),
1938 wlpn.gross_weight_uom_code,
1939 NVL(wlpn.gross_weight, 0),
1940 wlpn.content_volume_uom_code,
1941 NVL(wlpn.content_volume, 0),
1942 wlpn.lpn_context --Added for bug#4202068.
1943 FROM wms_license_plate_numbers wlpn
1944 WHERE wlpn.organization_id = p_org_id
1945 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1946 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1947 NVL(wlpn.parent_lpn_id, 0))
1948 AND wlpn.license_plate_number LIKE (p_lpn)
1949 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
1950 AND wlpn.lpn_context = 1
1951 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
1952 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1953 AND (( p_mtrl_sts_check = 'Y' --Bug 3980914
1954 AND inv_material_status_grp.is_status_applicable
1955 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
1956 NULL, p_org_id, NULL, wlpn.subinventory_code,
1957 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
1958 AND inv_material_status_grp.is_status_applicable
1959 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
1960 NULL, p_org_id, NULL, wlpn.subinventory_code,
1961 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
1962 AND inv_material_status_grp.is_status_applicable
1963 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
1964 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
1965 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
1966 AND inv_material_status_grp.is_status_applicable
1967 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
1968 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
1969 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
1970 )
1971 OR p_mtrl_sts_check = 'N' --Bug 3980914
1972 )
1973 ORDER BY license_plate_number;
1974 ELSE
1975 IF (l_debug = 1) THEN
1976 mydebug('split and inv; pregen=false');
1977 END IF;
1978 --Select LPNs with context "1"
1979 open x_lpn_lov for
1980 SELECT wlpn.license_plate_number,
1981 wlpn.lpn_id,
1982 NVL(wlpn.inventory_item_id, 0),
1983 NVL(wlpn.organization_id, 0),
1984 wlpn.revision,
1985 wlpn.lot_number,
1986 wlpn.serial_number,
1987 wlpn.subinventory_code,
1988 NVL(wlpn.locator_id, 0),
1989 NVL(wlpn.parent_lpn_id, 0),
1990 NVL(wlpn.sealed_status, 2),
1991 wlpn.gross_weight_uom_code,
1992 NVL(wlpn.gross_weight, 0),
1993 wlpn.content_volume_uom_code,
1994 NVL(wlpn.content_volume, 0),
1995 wlpn.lpn_context --Added for bug#4202068.
1996 FROM wms_license_plate_numbers wlpn
1997 WHERE wlpn.organization_id = p_org_id
1998 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1999 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2000 NVL(wlpn.parent_lpn_id, 0))
2001 AND wlpn.license_plate_number LIKE (p_lpn)
2002 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
2003 AND wlpn.lpn_context = 1
2004 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
2005 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
2006 AND ( ( p_mtrl_sts_check = 'Y' --Bug 3980914
2007 AND inv_material_status_grp.is_status_applicable
2008 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2009 NULL, p_org_id, NULL, wlpn.subinventory_code,
2010 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2011 AND inv_material_status_grp.is_status_applicable
2012 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2013 NULL, p_org_id, NULL, wlpn.subinventory_code,
2014 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2015 AND inv_material_status_grp.is_status_applicable
2016 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2017 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2018 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2019 AND inv_material_status_grp.is_status_applicable
2020 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2021 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2022 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2023 )
2024 OR p_mtrl_sts_check = 'N' --Bug 3980914
2025 )
2026 ORDER BY license_plate_number;
2027 END IF;
2028 END GET_SPLIT_INV_LPNS;
2029
2030 --Procedure to fetch LPNs when when transaction type is Split
2031 --and the LPN Context passed is 0 (fetch LPNs with context 11 and 5)
2032 PROCEDURE GET_SPLIT_PICKED_LPNS(x_lpn_lov OUT NOCOPY t_genref ,
2033 p_org_id IN NUMBER ,
2034 p_sub IN VARCHAR2 := NULL ,
2035 p_loc_id IN VARCHAR2 := NULL ,
2036 p_not_lpn_id IN VARCHAR2 := NULL ,
2037 p_parent_lpn_id IN VARCHAR2 := '0' ,
2038 p_txn_type_id IN NUMBER := 0 ,
2039 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
2040 p_lpn IN VARCHAR2,
2041 p_context IN NUMBER := 0,
2042 p_project_id IN NUMBER := NULL,
2043 p_task_id IN NUMBER := NULL,
2044 p_mtrl_sts_check IN VARCHAR2 := 'Y' --Bug 3980914
2045 )
2046 IS
2047 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2048 BEGIN
2049 IF p_incl_pre_gen_lpn = 'TRUE' THEN
2050 IF (l_debug = 1) THEN
2051 mydebug('split and picked; pregen=true');
2052 END IF;
2053 --Select LPNs with context "11" or "5"
2054 open x_lpn_lov for
2055 SELECT wlpn.license_plate_number,
2056 wlpn.lpn_id,
2057 wlpn.inventory_item_id,
2058 wlpn.organization_id,
2059 revision,
2060 wlpn.lot_number,
2061 wlpn.serial_number,
2062 wlpn.subinventory_code,
2063 wlpn.locator_id,
2064 wlpn.parent_lpn_id,
2065 NVL(wlpn.sealed_status, 2),
2066 wlpn.gross_weight_uom_code,
2067 NVL(wlpn.gross_weight, 0),
2068 wlpn.content_volume_uom_code,
2069 NVL(wlpn.content_volume, 0),
2070 wlpn.lpn_context --Added for bug#4202068.
2071 FROM wms_license_plate_numbers wlpn
2072 WHERE wlpn.organization_id = p_org_id
2073 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2074 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2075 NVL(wlpn.parent_lpn_id, 0))
2076 AND wlpn.license_plate_number LIKE (p_lpn)
2077 /* Bug 3980914 -For LPN's with context 5, the following condition is not required
2078 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
2079 AND (wlpn.lpn_context = 5)
2080 UNION ALL
2081 SELECT wlpn.license_plate_number,
2082 wlpn.lpn_id,
2083 NVL(wlpn.inventory_item_id, 0),
2084 NVL(wlpn.organization_id, 0),
2085 wlpn.revision,
2086 wlpn.lot_number,
2087 wlpn.serial_number,
2088 wlpn.subinventory_code,
2089 NVL(wlpn.locator_id, 0),
2090 NVL(wlpn.parent_lpn_id, 0),
2091 NVL(wlpn.sealed_status, 2),
2092 wlpn.gross_weight_uom_code,
2093 NVL(wlpn.gross_weight, 0),
2094 wlpn.content_volume_uom_code,
2095 NVL(wlpn.content_volume, 0),
2096 wlpn.lpn_context --Added for bug#4202068.
2097 FROM wms_license_plate_numbers wlpn,
2098 mtl_item_locations mil
2099 WHERE wlpn.organization_id = p_org_id
2100 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2101 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2102 NVL(wlpn.parent_lpn_id, 0))
2103 AND wlpn.license_plate_number LIKE (p_lpn)
2104 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
2105 AND wlpn.lpn_context = p_context
2106 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
2107 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
2108 AND mil.inventory_location_id = wlpn.locator_id
2109 AND NVL(mil.SEGMENT19, -1) = NVL(p_project_id, NVL(mil.SEGMENT19, -1))
2110 AND NVL(mil.SEGMENT20, -1) = NVL(p_task_id, NVL(mil.SEGMENT20, -1))
2111 AND ( ( p_mtrl_sts_check = 'Y' --Bug 3980914
2112 AND inv_material_status_grp.is_status_applicable
2113 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2114 NULL, p_org_id, NULL, wlpn.subinventory_code,
2115 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2116 AND inv_material_status_grp.is_status_applicable
2117 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2118 NULL, p_org_id, NULL, wlpn.subinventory_code,
2119 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2120 AND inv_material_status_grp.is_status_applicable
2121 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2122 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2123 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2124 AND inv_material_status_grp.is_status_applicable
2125 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2126 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2127 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2128 )
2129 OR p_mtrl_sts_check = 'N' --Bug 3980914
2130 )
2131 ORDER BY license_plate_number;
2132 ELSE
2133 IF (l_debug = 1) THEN
2134 mydebug('split and picked; pregen=false');
2135 END IF;
2136 --Select LPNs with context "11"
2137 open x_lpn_lov for
2138 SELECT wlpn.license_plate_number,
2139 wlpn.lpn_id,
2140 NVL(wlpn.inventory_item_id, 0),
2141 NVL(wlpn.organization_id, 0),
2142 wlpn.revision,
2143 wlpn.lot_number,
2144 wlpn.serial_number,
2145 wlpn.subinventory_code,
2146 NVL(wlpn.locator_id, 0),
2147 NVL(wlpn.parent_lpn_id, 0),
2148 NVL(wlpn.sealed_status, 2),
2149 wlpn.gross_weight_uom_code,
2150 NVL(wlpn.gross_weight, 0),
2151 wlpn.content_volume_uom_code,
2152 NVL(wlpn.content_volume, 0),
2153 wlpn.lpn_context --Added for bug#4202068.
2154 FROM wms_license_plate_numbers wlpn,
2155 mtl_item_locations mil
2156 WHERE wlpn.organization_id = p_org_id
2157 AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2158 AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2159 NVL(wlpn.parent_lpn_id, 0))
2160 AND wlpn.license_plate_number LIKE (p_lpn)
2161 AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
2162 AND wlpn.lpn_context = p_context
2163 AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
2164 AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
2165 AND mil.inventory_location_id = wlpn.locator_id
2166 AND NVL(mil.SEGMENT19, -1) = NVL(p_project_id, NVL(mil.SEGMENT19, -1))
2167 ANd NVL(mil.SEGMENT20, -1) = NVL(p_task_id, NVL(mil.SEGMENT20, -1))
2168 AND ( ( p_mtrl_sts_check = 'Y' --Bug 3980914
2169 AND inv_material_status_grp.is_status_applicable
2170 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2171 NULL, p_org_id, NULL, wlpn.subinventory_code,
2172 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2173 AND inv_material_status_grp.is_status_applicable
2174 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_PACK, NULL,
2175 NULL, p_org_id, NULL, wlpn.subinventory_code,
2176 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2177 AND inv_material_status_grp.is_status_applicable
2178 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2179 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2180 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
2181 AND inv_material_status_grp.is_status_applicable
2182 ('TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
2183 NULL, NULL, p_org_id, NULL, wlpn.subinventory_code,
2184 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
2185 )
2186 OR p_mtrl_sts_check = 'N' --Bug 3980914
2187 )
2188 ORDER BY license_plate_number;
2189 END IF;
2190
2191 END GET_SPLIT_PICKED_LPNS;
2192
2193 PROCEDURE GET_PKUPK_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref ,
2194 p_org_id IN NUMBER ,
2195 p_sub IN VARCHAR2 := NULL ,
2196 p_loc_id IN VARCHAR2 := NULL ,
2197 p_not_lpn_id IN VARCHAR2 := NULL ,
2198 p_parent_lpn_id IN VARCHAR2 := '0' ,
2199 p_txn_type_id IN NUMBER := 0 ,
2200 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
2201 p_lpn IN VARCHAR2,
2202 p_context IN NUMBER := 0,
2203 p_project_id IN NUMBER := NULL,
2204 p_task_id IN NUMBER := NULL,
2205 p_mtrl_sts_check IN VARCHAR2 := 'Y', -- Bug 3980914
2206 p_calling IN VARCHAR2 := NULL -- Bug 7210544
2207 )
2208 IS
2209 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2210 BEGIN
2211
2212 IF (l_debug = 1) THEN
2213 mydebug('org:'||p_org_id || ' sub:' || p_sub || ' loc:'||p_loc_id ||' parent_lpn:' || p_parent_lpn_id || ' not_lpn:' || p_not_lpn_id );
2214 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);
2215
2216
2217 END IF;
2218
2219
2220 If p_txn_type_id IN (INV_GLOBALS.G_TYPE_CONTAINER_PACK,
2221 INV_GLOBALS.G_TYPE_CONTAINER_UNPACK) THEN
2222 IF p_context = 11 THEN
2223 mydebug('calling GET_PACK_PICKED_LPNS');
2224 GET_PACK_PICKED_LPNS(
2225 x_lpn_lov => x_lpn_lov,
2226 p_org_id => p_org_id,
2227 p_sub => p_sub,
2228 p_loc_id => p_loc_id,
2229 p_not_lpn_id => p_not_lpn_id,
2230 p_parent_lpn_id => p_parent_lpn_id,
2231 p_txn_type_id => p_txn_type_id,
2232 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2233 p_lpn => p_lpn,
2234 p_context => p_context,
2235 p_project_id => p_project_id,
2236 p_task_id => p_task_id,
2237 p_mtrl_sts_check => p_mtrl_sts_check, --Bug 3980914
2238 p_calling => p_calling); --Bug 7210544
2239 --Added for the For bug 4057223 --> Base Bug #4021746
2240 ELSIF p_sub IS NOT NULL
2241 AND p_loc_id IS NOT NULL
2242 AND p_mtrl_sts_check = 'N' THEN
2243
2244 IF (l_debug = 1) THEN
2245 mydebug('calling GET_PK_UNPK_INV_LPNS_NO_CHECK');
2246 END IF;
2247
2248 GET_PK_UNPK_INV_LPNS_NO_CHECK
2249 (
2250 x_lpn_lov => x_lpn_lov,
2251 p_org_id => p_org_id,
2252 p_sub => p_sub,
2253 p_loc_id => p_loc_id,
2254 p_not_lpn_id => p_not_lpn_id,
2255 p_parent_lpn_id => p_parent_lpn_id,
2256 p_txn_type_id => p_txn_type_id,
2257 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2258 p_lpn => p_lpn,
2259 p_context => p_context,
2260 p_project_id => p_project_id,
2261 p_task_id => p_task_id,
2262 p_mtrl_sts_check => p_mtrl_sts_check);
2263 --Added for the For bug 4057223 --> Base Bug #4021746
2264 ELSE
2265
2266 IF (l_debug = 1) THEN
2267 mydebug('calling GET_PACK_INV_LPNS');
2268 END IF;
2269
2270 GET_PACK_INV_LPNS(
2271 x_lpn_lov => x_lpn_lov,
2272 p_org_id => p_org_id,
2273 p_sub => p_sub,
2274 p_loc_id => p_loc_id,
2275 p_not_lpn_id => p_not_lpn_id,
2276 p_parent_lpn_id => p_parent_lpn_id,
2277 p_txn_type_id => p_txn_type_id,
2278 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2279 p_lpn => p_lpn,
2280 p_context => p_context,
2281 p_project_id => p_project_id,
2282 p_task_id => p_task_id,
2283 p_mtrl_sts_check => p_mtrl_sts_check); --Bug 3980914
2284
2285 END IF;
2286 ELSIF p_txn_type_id = INV_GLOBALS.G_TYPE_CONTAINER_SPLIT THEN
2287 IF p_context = 11 THEN
2288 GET_SPLIT_PICKED_LPNS(
2289 x_lpn_lov => x_lpn_lov,
2290 p_org_id => p_org_id,
2291 p_sub => p_sub,
2292 p_loc_id => p_loc_id,
2293 p_not_lpn_id => p_not_lpn_id,
2294 p_parent_lpn_id => p_parent_lpn_id,
2295 p_txn_type_id => p_txn_type_id,
2296 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2297 p_lpn => p_lpn,
2298 p_context => p_context,
2299 p_project_id => p_project_id,
2300 p_task_id => p_task_id,
2301 p_mtrl_sts_check => p_mtrl_sts_check); --Bug 3980914
2302 ELSE
2303 GET_SPLIT_INV_LPNS(
2304 x_lpn_lov => x_lpn_lov,
2305 p_org_id => p_org_id,
2306 p_sub => p_sub,
2307 p_loc_id => p_loc_id,
2308 p_not_lpn_id => p_not_lpn_id,
2309 p_parent_lpn_id => p_parent_lpn_id,
2310 p_txn_type_id => p_txn_type_id,
2311 p_incl_pre_gen_lpn => p_incl_pre_gen_lpn,
2312 p_lpn => p_lpn,
2313 p_context => p_context,
2314 p_project_id => p_project_id,
2315 p_task_id => p_task_id,
2316 p_mtrl_sts_check => p_mtrl_sts_check); --Bug 3980914
2317
2318 END IF;
2319 ELSE
2320 --will paste the other sql
2321 IF (l_debug = 1) THEN
2322 mydebug('calling other sql');
2323 END IF;
2324
2325 open x_lpn_lov for
2326 select license_plate_number,
2327 lpn_id,
2328 NVL(inventory_item_id, 0),
2329 NVL(organization_id, 0),
2330 revision,
2331 lot_number,
2332 serial_number,
2333 subinventory_code,
2334 NVL(locator_id, 0),
2335 NVL(parent_lpn_id, 0),
2336 NVL(sealed_status, 2),
2337 gross_weight_uom_code,
2338 NVL(gross_weight, 0),
2339 content_volume_uom_code,
2340 NVL(content_volume, 0),
2341 wlpn.lpn_context --Added for bug#4202068.
2342 FROM wms_license_plate_numbers wlpn
2343 WHERE
2344 wlpn.organization_id = p_org_id
2345 AND (wlpn.lpn_context = p_context
2346 OR (p_context = 0
2347 AND (wlpn.lpn_context = 1 OR wlpn.lpn_context = 5)))
2348 AND license_plate_number LIKE (p_lpn)
2349 ORDER BY license_plate_number;
2350
2351 END IF;
2352 END GET_PKUPK_LPN_LOV;
2353
2354 PROCEDURE GET_PUP_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref ,
2355 p_org_id IN NUMBER ,
2356 p_sub IN VARCHAR2 := NULL ,
2357 p_loc_id IN VARCHAR2 := NULL ,
2358 p_not_lpn_id IN VARCHAR2 := NULL ,
2359 p_parent_lpn_id IN VARCHAR2 := '0' ,
2360 p_lpn IN VARCHAR2
2361 )
2362 IS
2363 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2364 BEGIN
2365 open x_lpn_lov for
2366 select license_plate_number,
2367 lpn_id,
2368 NVL(inventory_item_id, 0),
2369 NVL(organization_id, 0),
2370 revision,
2371 lot_number,
2372 serial_number,
2373 subinventory_code,
2374 NVL(locator_id, 0),
2375 NVL(parent_lpn_id, 0),
2376 NVL(sealed_status, 2),
2377 gross_weight_uom_code,
2378 NVL(gross_weight, 0),
2379 content_volume_uom_code,
2380 NVL(content_volume, 0)
2381 FROM wms_license_plate_numbers wlpn
2382 WHERE wlpn.organization_id = p_org_id
2383 AND NVL(subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
2384 AND NVL(locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(locator_id, '0'))
2385 AND NOT lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2386 AND NVL(parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(parent_lpn_id, 0))
2387 AND license_plate_number LIKE (p_lpn)
2388 ORDER BY license_plate_number;
2389
2390 END GET_PUP_LPN_LOV;
2391
2392 PROCEDURE CHILD_LPN_EXISTS(p_lpn_id IN NUMBER ,
2393 x_out OUT NOCOPY NUMBER
2394 )
2395 IS
2396 l_temp_num NUMBER;
2397 CURSOR child_lpn_cursor IS
2398 SELECT lpn_id
2399 FROM wms_license_plate_numbers
2400 WHERE parent_lpn_id = p_lpn_id;
2401
2402 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2403 BEGIN
2404 OPEN child_lpn_cursor;
2405 FETCH child_lpn_cursor INTO l_temp_num;
2406 IF child_lpn_cursor%notfound THEN
2407 x_out := 2;
2408 ELSE
2409 x_out := 1;
2410 END IF;
2411 CLOSE child_lpn_cursor;
2412
2413 END CHILD_LPN_EXISTS;
2414
2415
2416 PROCEDURE VALIDATE_PHYINV_LPN
2417 (p_lpn IN VARCHAR2 ,
2418 p_dynamic_entry_flag IN NUMBER ,
2419 p_physical_inventory_id IN NUMBER ,
2420 p_organization_id IN NUMBER ,
2421 p_subinventory_code IN VARCHAR2 ,
2422 p_locator_id IN NUMBER ,
2423 x_result OUT NOCOPY NUMBER)
2424 IS
2425 l_count NUMBER;
2426
2427 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2428 BEGIN
2429 IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
2430 -- Select all LPN's which exist in the given org, sub, loc
2431 SELECT COUNT(*)
2432 INTO l_count
2433 FROM wms_license_plate_numbers
2434 WHERE organization_id = p_organization_id
2435 AND subinventory_code = p_subinventory_code
2436 AND Nvl(locator_id, -99999) = Nvl(p_locator_id, -99999)
2437 AND license_plate_number = p_lpn;
2438
2439 IF (l_count = 1) THEN
2440 -- Validation is successful
2441 x_result := 1;
2442 ELSE
2443 -- Validation is not successful
2444 x_result := 2;
2445 END IF;
2446
2447 ELSE -- Dynamic entries are not allowed
2448 -- Select only LPN's that exist in table MTL_PHYSICAL_INVENTORY_TAGS
2449 SELECT COUNT(*)
2450 INTO l_count
2451 FROM wms_license_plate_numbers wlpn,
2452 mtl_physical_inventory_tags mpit
2453 WHERE wlpn.organization_id = p_organization_id
2454 AND wlpn.subinventory_code = p_subinventory_code
2455 AND Nvl(wlpn.locator_id, -99999) = Nvl(p_locator_id, -99999)
2456 AND wlpn.license_plate_number LIKE (p_lpn)
2457 AND wlpn.lpn_id = mpit.parent_lpn_id
2458 AND mpit.organization_id = p_organization_id
2459 AND mpit.physical_inventory_id = p_physical_inventory_id;
2460
2461 IF (l_count = 1) THEN
2462 -- Validation is successful
2463 x_result := 1;
2464 ELSE
2465 -- Validation is not successful
2466 x_result := 2;
2467 END IF;
2468
2469 END IF;
2470
2471 END VALIDATE_PHYINV_LPN;
2472
2473
2474 PROCEDURE VALIDATE_CYCLECOUNT_LPN
2475 (p_lpn IN VARCHAR2 ,
2476 p_unscheduled_entry IN NUMBER ,
2477 p_cycle_count_header_id IN NUMBER ,
2478 p_organization_id IN NUMBER ,
2479 p_subinventory_code IN VARCHAR2 ,
2480 p_locator_id IN NUMBER ,
2481 x_result OUT NOCOPY NUMBER)
2482 IS
2483 l_count NUMBER;
2484
2485 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2486 BEGIN
2487 IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
2488 -- Select all LPN's which exist in the given org, sub, loc
2489 SELECT COUNT(*)
2490 INTO l_count
2491 FROM wms_license_plate_numbers
2492 WHERE organization_id = p_organization_id
2493 AND subinventory_code = p_subinventory_code
2494 AND NVL(locator_id, -99999) = NVL(p_locator_id, -99999)
2495 AND license_plate_number = p_lpn;
2496
2497 IF (l_count = 1) THEN
2498 -- Validation is successful
2499 x_result := 1;
2500 ELSE
2501 -- Validation is not successful
2502 x_result := 2;
2503 END IF;
2504
2505 ELSE -- Unscheduled entries are not allowed
2506 -- Select only LPN's that exist in table MTL_CYCLE_COUNT_ENTRIES
2507 SELECT COUNT(*)
2508 INTO l_count
2509 FROM wms_license_plate_numbers wlpn,
2510 mtl_cycle_count_entries mcce
2511 WHERE wlpn.organization_id = p_organization_id
2512 AND wlpn.subinventory_code = p_subinventory_code
2513 AND NVL(wlpn.locator_id, -99999) = NVL(p_locator_id, -99999)
2514 AND wlpn.license_plate_number LIKE (p_lpn)
2515 AND wlpn.lpn_id = mcce.parent_lpn_id
2516 AND mcce.organization_id = p_organization_id
2517 AND mcce.cycle_count_header_id = p_cycle_count_header_id;
2518
2519 IF (l_count = 1) THEN
2520 -- Validation is successful
2521 x_result := 1;
2522 ELSE
2523 -- Validation is not successful
2524 x_result := 2;
2525 END IF;
2526
2527 END IF;
2528
2529 END VALIDATE_CYCLECOUNT_LPN;
2530
2531
2532
2533 PROCEDURE VALIDATE_LPN_AGAINST_ORG
2534 (p_lpn IN VARCHAR2 ,
2535 p_organization_id IN NUMBER ,
2536 x_result OUT NOCOPY NUMBER)
2537 IS
2538 l_lpn WMS_container_pub.LPN;
2539 l_result NUMBER;
2540
2541 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2542 BEGIN
2543 l_lpn.lpn_id := NULL;
2544 l_lpn.license_plate_number := p_lpn;
2545 l_result := wms_container_pub.Validate_LPN(l_lpn);
2546 IF (l_result = INV_Validate.F) THEN
2547 -- LPN was not found
2548 x_result := 2;
2549 ELSE
2550 -- LPN was found and is therefore valid
2551 x_result := 1;
2552 END IF;
2553
2554 END VALIDATE_LPN_AGAINST_ORG;
2555
2556
2557
2558 PROCEDURE GET_LPN_VALUES
2559 (p_lpn IN VARCHAR2 ,
2560 p_organization_id IN NUMBER ,
2561 x_license_plate_number OUT NOCOPY VARCHAR2 ,
2562 x_lpn_id OUT NOCOPY NUMBER ,
2563 x_inventory_item_id OUT NOCOPY NUMBER ,
2564 x_organization_id OUT NOCOPY NUMBER ,
2565 x_revision OUT NOCOPY VARCHAR2 ,
2566 x_lot_number OUT NOCOPY VARCHAR2 ,
2567 x_serial_number OUT NOCOPY VARCHAR2 ,
2568 x_subinventory_code OUT NOCOPY VARCHAR2 ,
2569 x_locator_id OUT NOCOPY NUMBER ,
2570 x_parent_lpn_id OUT NOCOPY NUMBER ,
2571 x_sealed_status OUT NOCOPY NUMBER ,
2572 x_gross_weight_uom_code OUT NOCOPY VARCHAR2 ,
2573 x_gross_weight OUT NOCOPY NUMBER ,
2574 x_content_volume_uom_code OUT NOCOPY VARCHAR2 ,
2575 x_content_volume OUT NOCOPY NUMBER)
2576 IS
2577 l_lpn_record LPN_RECORD;
2578
2579 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2580 BEGIN
2581 SELECT license_plate_number,
2582 lpn_id,
2583 NVL(inventory_item_id, 0),
2584 NVL(organization_id, 0),
2585 revision,
2586 lot_number,
2587 serial_number,
2588 subinventory_code,
2589 NVL(locator_id, 0),
2590 NVL(parent_lpn_id, 0),
2591 NVL(sealed_status, 2),
2592 gross_weight_uom_code,
2593 NVL(gross_weight, 0),
2594 content_volume_uom_code,
2595 NVL(content_volume, 0)
2596 INTO l_lpn_record
2597 FROM wms_license_plate_numbers
2598 WHERE license_plate_number = p_lpn
2599 AND organization_id = p_organization_id
2600 ORDER BY license_plate_number;
2601
2602 x_license_plate_number := l_lpn_record.license_plate_number;
2603 x_lpn_id := l_lpn_record.lpn_id;
2604 x_inventory_item_id := l_lpn_record.inventory_item_id;
2605 x_organization_id := l_lpn_record.organization_id;
2606 x_revision := l_lpn_record.revision;
2607 x_lot_number := l_lpn_record.lot_number;
2608 x_serial_number := l_lpn_record.serial_number;
2609 x_subinventory_code := l_lpn_record.subinventory_code;
2610 x_locator_id := l_lpn_record.locator_id;
2611 x_parent_lpn_id := l_lpn_record.parent_lpn_id;
2612 x_sealed_status := l_lpn_record.sealed_status;
2613 x_gross_weight_uom_code := l_lpn_record.gross_weight_uom_code;
2614 x_gross_weight := l_lpn_record.gross_weight;
2615 x_content_volume_uom_code := l_lpn_record.content_volume_uom_code;
2616 x_content_volume := l_lpn_record.content_volume;
2617
2618 END GET_LPN_VALUES;
2619
2620
2621
2622
2623
2624
2625
2626
2627 -- Name: GET_INSPECT_LPN_LOV
2628 --
2629 -- Input parameters:
2630 -- p_lpn which restricts LOV SQL to the user input text
2631 --
2632 -- Output parameters:
2633 -- x_lpn_lov returns LOV rows as reference cursor
2634 --
2635 -- Functions: This API returns valid LPN and lpn_id whose contents have to be inspected
2636 -- in Mobile Inspection form
2637 --
2638
2639 PROCEDURE GET_INSPECT_LPN_LOV
2640 (x_lpn_lov OUT NOCOPY t_genref,
2641 p_lpn IN VARCHAR2,
2642 p_organization_id IN NUMBER
2643 )
2644 IS
2645 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2646 BEGIN
2647 -- inspection_status in mtl_txn_request_lines could have values {null,1,2,3}
2648 -- mapping to {Inspection not needed, Inspection needed, Accepted, Rejected }
2649 -- We want those that are pending inspection
2650
2651 OPEN x_lpn_lov FOR
2652 SELECT distinct
2653 a.license_plate_number,
2654 a.lpn_id,
2655 NVL(a.inventory_item_id, 0),
2656 NVL(a.organization_id, 0),
2657 a.revision,
2658 a.lot_number,
2659 a.serial_number,
2660 a.subinventory_code,
2661 NVL(a.locator_id, 0),
2662 NVL(a.parent_lpn_id, 0),
2663 NVL(a.sealed_status, 2),
2664 a.gross_weight_uom_code,
2665 NVL(a.gross_weight, 0),
2666 a.content_volume_uom_code,
2667 NVL(a.content_volume, 0),
2668 nvl(rec_count.lpn_content_count, 0)
2669 FROM wms_license_plate_numbers a,
2670 mtl_txn_request_lines b,
2671 (SELECT count(*) lpn_content_count,grouped_contents.lpn_id
2672 FROM (SELECT mtrl.lpn_id lpn_id, -- Need extra grouping to group
2673 mtrl.inventory_item_id item_id,
2674 mtrl.revision revision
2675 --BUG 3358288: Use MOL to calculate the count instead of
2676 --using WLC because there may be items there does not
2677 --require inspection
2678 FROM wms_license_plate_numbers wlpn, mtl_txn_request_lines mtrl
2679 WHERE wlpn.license_plate_number LIKE (p_lpn)
2680 AND mtrl.lpn_id = wlpn.lpn_id
2681 AND mtrl.inspection_status = 1
2682 AND mtrl.wms_process_flag = 1
2683 AND mtrl.line_status = 7
2684 AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0))>0
2685 GROUP BY mtrl.lpn_id, mtrl.inventory_item_id,Nvl(mtrl.revision,-1)) grouped_contents
2686 GROUP BY grouped_contents.lpn_id) rec_count
2687 WHERE a. license_plate_number LIKE (p_lpn)
2688 and a.lpn_id = b.lpn_id
2689 and a.lpn_context in (3,5)
2690 and b.inspection_status = 1
2691 -- Bug 2377796
2692 -- Check to make sure that the processing for mtl_txn_request_lines is completed or not.
2693 and b.wms_process_flag = 1
2694 AND b.line_status = 7
2695 AND (b.quantity-Nvl(b.quantity_delivered,0))>0
2696 and b.organization_id = p_organization_id
2697 and a.lpn_id = rec_count.lpn_id --(+) //Bug 3435093
2698 and nvl(rec_count.lpn_content_count, 0) > 0;
2699
2700
2701 END GET_INSPECT_LPN_LOV;
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712 --
2713 -- Procedure to retrieve LPNs in Inventory which contain only the specific
2714 -- item having less than equal to specified qty in the specified location.
2715 -- Called from LPNLOV.java
2716 --
2717 PROCEDURE GET_MO_LPN
2718 (x_lpn_lov OUT NOCOPY t_genref ,
2719 p_lpn IN VARCHAR2 ,
2720 p_inv_item_id IN NUMBER ,
2721 p_organization_id IN NUMBER ,
2722 p_subinventory_code IN VARCHAR2 ,
2723 p_locator_id IN NUMBER ,
2724 p_qty IN NUMBER) IS
2725 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2726 begin
2727 OPEN x_lpn_lov FOR
2728 select lpnc.parent_lpn_id lpn_id,
2729 lpn.license_plate_number lpn,
2730 sum(lpnc.quantity) quantity
2731 from wms_lpn_contents lpnc, wms_license_plate_numbers lpn
2732 where lpn.organization_id = p_organization_id
2733 and lpnc.inventory_item_id = p_inv_item_id
2734 and lpnc.parent_lpn_id = lpn.lpn_id
2735 and nvl(lpn.SUBINVENTORY_CODE,'@@@') = nvl(p_subinventory_code,'@@@')
2736 and nvl(lpn.LOCATOR_ID, 0) = nvl(p_locator_id, 0)
2737 and lpn.license_plate_number like (p_lpn)
2738 and lpn.lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
2739 and not exists (select null
2740 from wms_lpn_contents
2741 where parent_lpn_id = lpnc.parent_lpn_id
2742 and inventory_item_id <> lpnc.inventory_item_id)
2743 group by lpnc.parent_lpn_id, lpn.license_plate_number
2744 having sum(lpnc.quantity) <= p_qty;
2745
2746 end;
2747
2748
2749 -- Neted LPN changes added p_mode parameter.
2750 -- For express receipts the value of Mode will be 'E'
2751 -- For confirm receipts the value of Mode will be 'C'
2752 -- If the value of p_mode is NULL then that means the customer
2753 -- has Patchset I Java page. In this case we will use the old query.
2754
2755
2756 PROCEDURE
2757 GET_VENDOR_LPN
2758 (x_lpn_lov OUT NOCOPY t_genref ,
2759 p_lpn IN VARCHAR2 ,
2760 p_shipment_header_id IN VARCHAR2 ,
2761 p_mode IN VARCHAR2 ,
2762 p_inventory_item_id IN VARCHAR2
2763 )
2764 IS
2765 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2766 BEGIN
2767
2768
2769 -- Nested LPN changes if p_mode is NULL then it is called Before Patchset J,
2770 -- If p_mode is 'E' called from Express page. If it is 'C' then called from confirm page.
2771
2772 -- For getting the No of LPNs attached to a Shipment Header, the Like clause
2773 -- is commented, because it is not necessary to check with pattern matching
2774 -- in this query. (Base Bug : 3080274).
2775
2776 IF p_mode IS NULL THEN
2777 OPEN x_lpn_lov FOR
2778 SELECT
2779 lpn.license_plate_number,
2780 lpn.lpn_id,
2781 NVL(lpn.inventory_item_id, 0),
2782 NVL(lpn.organization_id, 0),
2783 lpn.revision,
2784 lpn.lot_number,
2785 lpn.serial_number,
2786 lpn.subinventory_code,
2787 NVL(lpn.locator_id, 0),
2788 NVL(lpn.parent_lpn_id, 0),
2789 NVL(lpn.sealed_status, 2),
2790 lpn.gross_weight_uom_code,
2791 NVL(lpn.gross_weight, 0),
2792 lpn.content_volume_uom_code,
2793 NVL(lpn.content_volume, 0),
2794 lpn.source_header_id,
2795 rsh.shipment_num,
2796 count_row.n,
2797 rsh.shipment_header_id
2798 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2799 (SELECT COUNT(*) n
2800 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2801 --WHERE lpn.license_plate_number LIKE (p_lpn)--Bug 3090000
2802 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2803 AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2804 AND (lpn.source_header_id = rsh.shipment_header_id
2805 OR lpn.source_name = rsh.shipment_num)
2806 ) count_row
2807 WHERE lpn.license_plate_number LIKE (p_lpn)
2808 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2809 AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2810 AND (lpn.source_header_id = rsh.shipment_header_id
2811 OR lpn.source_name = rsh.shipment_num)
2812 -- Nested LPN changes , For I Patchset donot show nested LPNs
2813 AND lpn.lpn_id NOT IN (SELECT parent_lpn_id FROM wms_license_plate_numbers WHERE parent_lpn_id = lpn.lpn_id )
2814 AND lpn.parent_lpn_id IS NULL;
2815 ELSIF p_mode = 'E' THEN
2816
2817 -- As Part of per bug 3435093 if shipment_header_id is not null
2818 -- removed the Nvl condition around shipment_header_id to pick the index.
2819
2820 IF p_shipment_header_id IS NOT NULL THEN
2821
2822 OPEN x_lpn_lov FOR
2823 SELECT
2824 wlpn1.license_plate_number,
2825 wlpn1.lpn_id,
2826 NVL(wlpn1.inventory_item_id, 0),
2827 NVL(wlpn1.organization_id, 0),
2828 wlpn1.revision,
2829 wlpn1.lot_number,
2830 wlpn1.serial_number,
2831 wlpn1.subinventory_code,
2832 NVL(wlpn1.locator_id, 0),
2833 NVL(wlpn1.parent_lpn_id, 0),
2834 NVL(wlpn1.sealed_status, 2),
2835 wlpn1.gross_weight_uom_code,
2836 NVL(wlpn1.gross_weight, 0),
2837 wlpn1.content_volume_uom_code,
2838 NVL(wlpn1.content_volume, 0),
2839 wlpn1.source_header_id,
2840 rsh.shipment_num,
2841 1,--This is a dummy value. Actually cound will be calculated in validate_from_lpn
2842 rsh.shipment_header_id
2843 FROM wms_license_plate_numbers wlpn1, rcv_shipment_headers rsh
2844 WHERE rsh.shipment_header_id = p_shipment_header_id
2845 AND ((wlpn1.lpn_context = 6 AND wlpn1.organization_id = rsh.organization_id) OR
2846 (wlpn1.lpn_context = 7 AND wlpn1.organization_id = rsh.ship_to_org_id))
2847 AND wlpn1.source_name = rsh.shipment_num
2848 AND wlpn1.license_plate_number LIKE (p_lpn)
2849 and exists (SELECT wlpn2.lpn_id
2850 FROM wms_license_plate_numbers wlpn2
2851 START WITH wlpn2.lpn_id = wlpn1.lpn_id
2852 CONNECT BY PRIOR wlpn2.lpn_id = wlpn2.parent_lpn_id
2853 INTERSECT
2854 SELECT asn_lpn_id
2855 FROM rcv_shipment_lines rsl
2856 WHERE rsl.shipment_header_id = p_shipment_header_id
2857 AND NOT exists (SELECT 1
2858 FROM rcv_transactions_interface rti
2859 WHERE rti.lpn_id = rsl.asn_lpn_id
2860 AND rti.transfer_lpn_id = rsl.asn_lpn_id
2861 AND rti.to_organization_id = rsl.to_organization_id
2862 AND rti.processing_status_code <> 'ERROR'
2863 AND rti.transaction_status_code <> 'ERROR'
2864 )
2865 );
2866 ELSE
2867 OPEN x_lpn_lov FOR
2868 SELECT
2869 lpn.license_plate_number,
2870 lpn.lpn_id,
2871 NVL(lpn.inventory_item_id, 0),
2872 NVL(lpn.organization_id, 0),
2873 lpn.revision,
2874 lpn.lot_number,
2875 lpn.serial_number,
2876 lpn.subinventory_code,
2877 NVL(lpn.locator_id, 0),
2878 NVL(lpn.parent_lpn_id, 0),
2879 NVL(lpn.sealed_status, 2),
2880 lpn.gross_weight_uom_code,
2881 NVL(lpn.gross_weight, 0),
2882 lpn.content_volume_uom_code,
2883 NVL(lpn.content_volume, 0),
2884 lpn.source_header_id,
2885 rsh.shipment_num,
2886 count_row.n,
2887 rsh.shipment_header_id
2888 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2889 (SELECT COUNT(*) n
2890 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2891 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2892 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2893 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2894 AND (lpn.source_header_id = rsh.shipment_header_id
2895 OR lpn.source_name = rsh.shipment_num)
2896 ) count_row
2897 WHERE lpn.license_plate_number LIKE (p_lpn)
2898 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2899 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2900 AND (lpn.source_header_id = rsh.shipment_header_id
2901 OR lpn.source_name = rsh.shipment_num) ;
2902 END IF; -- For shipment Header id is null
2903
2904 -- Nested LPN changes If mode is 'C' then the LOV is for Confirm transactions
2905 -- In case of Confirm transaction we show LPNs which have immediate contents.
2906 ELSIF p_mode= 'C' THEN
2907
2908 -- This is changed based on Item Info, case for Item Initiated Receipt.
2909 -- If Item info is present or passed from the UI then LPN should be restrcied based on Item
2910 -- Otherwise all the LPN's for the shipment should be displayed in the LOV
2911
2912 if p_inventory_item_id is null then
2913 IF p_shipment_header_id IS NOT NULL THEN
2914 OPEN x_lpn_lov FOR
2915 SELECT
2916 lpn.license_plate_number,
2917 lpn.lpn_id,
2918 NVL(lpn.inventory_item_id, 0),
2919 NVL(lpn.organization_id, 0),
2920 lpn.revision,
2921 lpn.lot_number,
2922 lpn.serial_number,
2923 lpn.subinventory_code,
2924 NVL(lpn.locator_id, 0),
2925 NVL(lpn.parent_lpn_id, 0),
2926 NVL(lpn.sealed_status, 2),
2927 lpn.gross_weight_uom_code,
2928 NVL(lpn.gross_weight, 0),
2929 lpn.content_volume_uom_code,
2930 NVL(lpn.content_volume, 0),
2931 lpn.source_header_id,
2932 rsh.shipment_num,
2933 count_row.n,
2934 rsh.shipment_header_id
2935 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2936 ( SELECT COUNT(*) n
2937 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2938 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2939 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2940 AND rsh.shipment_header_id = p_shipment_header_id
2941 AND (lpn.source_header_id = rsh.shipment_header_id
2942 OR lpn.source_name = rsh.shipment_num)
2943 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
2944 ) count_row
2945 WHERE lpn.license_plate_number LIKE (p_lpn)
2946 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2947 AND rsh.shipment_header_id = p_shipment_header_id
2948 AND (lpn.source_header_id = rsh.shipment_header_id
2949 OR lpn.source_name = rsh.shipment_num)
2950 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id);
2951
2952 ELSE -- for if p_shipment_header_id is not null
2953 OPEN x_lpn_lov FOR
2954 SELECT
2955 lpn.license_plate_number,
2956 lpn.lpn_id,
2957 NVL(lpn.inventory_item_id, 0),
2958 NVL(lpn.organization_id, 0),
2959 lpn.revision,
2960 lpn.lot_number,
2961 lpn.serial_number,
2962 lpn.subinventory_code,
2963 NVL(lpn.locator_id, 0),
2964 NVL(lpn.parent_lpn_id, 0),
2965 NVL(lpn.sealed_status, 2),
2966 lpn.gross_weight_uom_code,
2967 NVL(lpn.gross_weight, 0),
2968 lpn.content_volume_uom_code,
2969 NVL(lpn.content_volume, 0),
2970 lpn.source_header_id,
2971 rsh.shipment_num,
2972 count_row.n,
2973 rsh.shipment_header_id
2974 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2975 ( SELECT COUNT(*) n
2976 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2977 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2978 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2979 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2980 AND (lpn.source_header_id = rsh.shipment_header_id
2981 OR lpn.source_name = rsh.shipment_num)
2982 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
2983 ) count_row
2984 WHERE lpn.license_plate_number LIKE (p_lpn)
2985 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2986 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2987 AND (lpn.source_header_id = rsh.shipment_header_id
2988 OR lpn.source_name = rsh.shipment_num)
2989 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id);
2990
2991 END IF; -- end if for if p_shipment_header_id is not null
2992 ELSE
2993 IF p_shipment_header_id IS NOT NULL THEN
2994 OPEN x_lpn_lov FOR
2995 SELECT
2996 lpn.license_plate_number,
2997 lpn.lpn_id,
2998 NVL(lpn.inventory_item_id, 0),
2999 NVL(lpn.organization_id, 0),
3000 lpn.revision,
3001 lpn.lot_number,
3002 lpn.serial_number,
3003 lpn.subinventory_code,
3004 NVL(lpn.locator_id, 0),
3005 NVL(lpn.parent_lpn_id, 0),
3006 NVL(lpn.sealed_status, 2),
3007 lpn.gross_weight_uom_code,
3008 NVL(lpn.gross_weight, 0),
3009 lpn.content_volume_uom_code,
3010 NVL(lpn.content_volume, 0),
3011 lpn.source_header_id,
3012 rsh.shipment_num,
3013 count_row.n,
3014 rsh.shipment_header_id
3015 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
3016 ( SELECT COUNT(*) n
3017 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
3018 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
3019 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3020 AND rsh.shipment_header_id = p_shipment_header_id
3021 AND (lpn.source_header_id = rsh.shipment_header_id
3022 OR lpn.source_name = rsh.shipment_num)
3023 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
3024 ) count_row
3025 WHERE lpn.license_plate_number LIKE (p_lpn)
3026 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3027 AND rsh.shipment_header_id = p_shipment_header_id
3028 AND (lpn.source_header_id = rsh.shipment_header_id
3029 OR lpn.source_name = rsh.shipment_num)
3030 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id
3031 and inventory_item_id = p_inventory_item_id );
3032
3033 ELSE -- if p_shipment_header_id is null
3034 OPEN x_lpn_lov FOR
3035 SELECT
3036 lpn.license_plate_number,
3037 lpn.lpn_id,
3038 NVL(lpn.inventory_item_id, 0),
3039 NVL(lpn.organization_id, 0),
3040 lpn.revision,
3041 lpn.lot_number,
3042 lpn.serial_number,
3043 lpn.subinventory_code,
3044 NVL(lpn.locator_id, 0),
3045 NVL(lpn.parent_lpn_id, 0),
3046 NVL(lpn.sealed_status, 2),
3047 lpn.gross_weight_uom_code,
3048 NVL(lpn.gross_weight, 0),
3049 lpn.content_volume_uom_code,
3050 NVL(lpn.content_volume, 0),
3051 lpn.source_header_id,
3052 rsh.shipment_num,
3053 count_row.n,
3054 rsh.shipment_header_id
3055 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
3056 ( SELECT COUNT(*) n
3057 FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
3058 --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
3059 WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3060 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3061 AND (lpn.source_header_id = rsh.shipment_header_id
3062 OR lpn.source_name = rsh.shipment_num)
3063 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
3064 ) count_row
3065 WHERE lpn.license_plate_number LIKE (p_lpn)
3066 AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3067 --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3068 AND (lpn.source_header_id = rsh.shipment_header_id
3069 OR lpn.source_name = rsh.shipment_num)
3070 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id
3071 and inventory_item_id = p_inventory_item_id );
3072 END IF; -- else part of if p_shipment_header_id is not null
3073 END IF; -- else part of if p_inventory_item_id is null
3074 END IF;
3075
3076
3077 END GET_VENDOR_LPN;
3078
3079 /* PJM Integration: Added to get the concatenated segments of physical locator,
3080 * project id, project number, task id and task number.
3081 * Use the table mtl_item_locations instead of mtl_item_locations_kfv.
3082 */
3083 PROCEDURE GET_ITEM_LPN_LOV
3084 (x_lpn_lov OUT NOCOPY t_genref,
3085 p_organization_id IN NUMBER,
3086 p_lot_number IN VARCHAR2,
3087 p_inventory_item_id IN NUMBER,
3088 p_revision IN VARCHAR2,
3089 p_lpn IN VARCHAR2)
3090 IS
3091 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3092 BEGIN
3093 OPEN x_lpn_lov
3094 FOR
3095 SELECT wlpn.license_plate_number,
3096 wlpn.lpn_id,
3097 NVL(wlc.inventory_item_id, 0),
3098 NVL(wlpn.organization_id, 0),
3099 wlc.revision,
3100 wlc.lot_number,
3101 wlc.serial_number,
3102 wlpn.subinventory_code,
3103 NVL(wlpn.locator_id, 0),
3104 NVL(wlpn.parent_lpn_id, 0),
3105 NVL(wlpn.sealed_status, 2),
3106 wlpn.gross_weight_uom_code,
3107 NVL(wlpn.gross_weight, 0),
3108 wlpn.content_volume_uom_code,
3109 NVL(wlpn.content_volume, 0),
3110 --milk.concatenated_segments locator_code,
3111 INV_PROJECT.GET_LOCSEGS(mil.inventory_location_id, p_organization_id),
3112 wlc.cost_group_id,
3113 INV_PROJECT.GET_PROJECT_ID,
3114 INV_PROJECT.GET_PROJECT_NUMBER,
3115 INV_PROJECT.GET_TASK_ID,
3116 INV_PROJECT.GET_TASK_NUMBER
3117 FROM wms_license_plate_numbers wlpn,
3118 wms_lpn_contents wlc,
3119 mtl_item_locations mil
3120 WHERE (mil.inventory_location_id = wlpn.locator_id
3121 AND wlpn.locator_id IS NOT NULL)
3122 AND ( (wlc.revision = p_revision
3123 AND p_revision IS NOT NULL)
3124 OR (wlc.revision IS NULL
3125 AND p_revision IS NULL))
3126 AND wlc.inventory_item_id = p_inventory_item_id
3127 AND ( (wlc.lot_number = p_lot_number
3128 AND p_lot_number IS NOT NULL) OR
3129 (wlc.lot_number LIKE '%'
3130 AND p_lot_number IS NULL))
3131 AND wlpn.license_plate_number LIKE (p_lpn)
3132 AND wlpn.lpn_id = wlc.parent_lpn_id
3133 AND wlpn.lpn_context = 1
3134 AND wlpn.parent_lpn_id IS NULL
3135 AND wlpn.organization_id = p_organization_id;
3136 END GET_ITEM_LPN_LOV;
3137
3138 -- Procedure for the result lot of Lot split/merge/translate.
3139 -- For the result lot, do not need to check for inventory item id.
3140 /* PJM Integration: Added to get the concatenated segments of physical locator,
3141 * project id, project number, task id and task number.
3142 * Use the table mtl_item_locations instead of mtl_item_locations_kfv.
3143 */
3144 PROCEDURE GET_LOT_LPN_LOV
3145 (x_lpn_lov OUT NOCOPY t_genref,
3146 p_organization_id IN NUMBER,
3147 p_lpn IN VARCHAR2)
3148 IS
3149 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3150 BEGIN
3151 OPEN x_lpn_lov FOR
3152 SELECT distinct wlpn.license_plate_number,
3153 wlpn.lpn_id,
3154 NVL(wlpn.inventory_item_id, 0),
3155 NVL(wlpn.organization_id, 0),
3156 wlpn.revision,
3157 wlpn.lot_number,
3158 wlpn.serial_number,
3159 wlpn.subinventory_code,
3160 NVL(wlpn.locator_id, 0),
3161 NVL(wlpn.parent_lpn_id, 0),
3162 NVL(wlpn.sealed_status, 2),
3163 wlpn.gross_weight_uom_code,
3164 NVL(wlpn.gross_weight, 0),
3165 wlpn.content_volume_uom_code,
3166 NVL(wlpn.content_volume, 0),
3167 --milk.concatenated_segments locator_code
3168 INV_PROJECT.GET_LOCSEGS(mil.inventory_location_id, p_organization_id),
3169 INV_PROJECT.GET_PROJECT_ID,
3170 INV_PROJECT.GET_PROJECT_NUMBER,
3171 INV_PROJECT.GET_TASK_ID,
3172 INV_PROJECT.GET_TASK_NUMBER
3173 FROM wms_license_plate_numbers wlpn,
3174 mtl_item_locations mil
3175 WHERE mil.inventory_location_id(+) = wlpn.locator_id --OUTER JOIN is added for bug 3876495
3176 AND wlpn.license_plate_number LIKE (p_lpn)
3177 AND wlpn.organization_id = p_organization_id
3178 AND wlpn.lpn_context IN (1,5) --LPN_CONTEXT 5 is Added for bug3876495.
3179 ORDER BY license_plate_number;
3180 END GET_LOT_LPN_LOV;
3181
3182
3183
3184 PROCEDURE GET_RCV_LPN
3185 (x_lpn_lov OUT NOCOPY t_genref,
3186 p_org_id IN NUMBER,
3187 p_lpn IN VARCHAR2,
3188 p_from_lpn_id IN VARCHAR2,
3189 p_project_id in number,
3190 p_task_id in number
3191 )
3192 IS
3193 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3194 BEGIN
3195 IF p_project_id is not null then
3196 OPEN x_lpn_lov FOR
3197 SELECT wlpn.license_plate_number,
3198 wlpn.lpn_id,
3199 NVL(wlpn.inventory_item_id, 0),
3200 NVL(wlpn.organization_id, 0),
3201 wlpn.revision,
3202 wlpn.lot_number,
3203 wlpn.serial_number,
3204 wlpn.subinventory_code,
3205 NVL(wlpn.locator_id, 0),
3206 NVL(wlpn.parent_lpn_id, 0),
3207 NVL(wlpn.sealed_status, 2),
3208 wlpn.gross_weight_uom_code,
3209 NVL(wlpn.gross_weight, 0),
3210 wlpn.content_volume_uom_code,
3211 NVL(wlpn.content_volume, 0)
3212 FROM wms_license_plate_numbers wlpn
3213 WHERE wlpn.license_plate_number LIKE (p_lpn)
3214 AND wlpn.organization_id = p_org_id
3215 and wlpn.lpn_context = 3
3216 AND exists (
3217 select lpn_id
3218 from mtl_txn_request_lines mtrl
3219 where mtrl.organization_id = p_org_id
3220 and mtrl.project_id = p_project_id
3221 and mtrl.lpn_id = wlpn.lpn_id
3222 and nvl(task_id,-9999) = nvl(p_task_id,-9999)
3223 )
3224 UNION
3225 SELECT wlpn.license_plate_number,
3226 wlpn.lpn_id,
3227 NVL(wlpn.inventory_item_id, 0),
3228 NVL(wlpn.organization_id, 0),
3229 wlpn.revision,
3230 wlpn.lot_number,
3231 wlpn.serial_number,
3232 wlpn.subinventory_code,
3233 NVL(wlpn.locator_id, 0),
3234 NVL(wlpn.parent_lpn_id, 0),
3235 NVL(wlpn.sealed_status, 2),
3236 wlpn.gross_weight_uom_code,
3237 NVL(wlpn.gross_weight, 0),
3238 wlpn.content_volume_uom_code,
3239 NVL(wlpn.content_volume, 0)
3240 FROM wms_license_plate_numbers wlpn
3241 WHERE wlpn.license_plate_number LIKE (p_lpn)
3242 AND wlpn.organization_id = p_org_id
3243 and exists
3244 ( select inventory_location_id
3245 from mtl_item_locations mil
3246 where organization_id = p_org_id
3247 and nvl(wlpn.subinventory_code,'@@@') = nvl(mil.subinventory_code,'@@@')
3248 and mil.project_id = p_project_id
3249 and wlpn.locator_id = mil.inventory_location_id
3250 and nvl(task_id,-9999) = nvl(p_task_id,-9999)
3251 )
3252 and wlpn.lpn_context = 1
3253 AND inv_material_status_grp.is_status_applicable
3254 ('TRUE',
3255 NULL,
3256 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
3257 NULL,
3258 NULL,
3259 p_org_id,
3260 NULL,
3261 wlpn.subinventory_code,
3262 wlpn.locator_id,
3263 NULL,
3264 NULL,
3265 'Z') = 'Y'
3266 AND inv_material_status_grp.is_status_applicable
3267 ('TRUE',
3268 NULL,
3269 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
3270 NULL,
3271 NULL,
3272 p_org_id,
3273 NULL,
3274 wlpn.subinventory_code,
3275 wlpn.locator_id,
3276 NULL,
3277 NULL,
3278 'L') = 'Y'
3279 UNION
3280 SELECT wlpn.license_plate_number,
3281 wlpn.lpn_id,
3282 NVL(wlpn.inventory_item_id, 0),
3283 NVL(wlpn.organization_id, 0),
3284 wlpn.revision,
3285 wlpn.lot_number,
3286 wlpn.serial_number,
3287 wlpn.subinventory_code,
3288 NVL(wlpn.locator_id, 0),
3289 NVL(wlpn.parent_lpn_id, 0),
3290 NVL(wlpn.sealed_status, 2),
3291 wlpn.gross_weight_uom_code,
3292 NVL(wlpn.gross_weight, 0),
3293 wlpn.content_volume_uom_code,
3294 NVL(wlpn.content_volume, 0)
3295 FROM wms_license_plate_numbers wlpn
3296 WHERE wlpn.license_plate_number LIKE (p_lpn)
3297 AND wlpn.organization_id = p_org_id
3298 and ( lpn_context = 5 or lpn_id = p_from_lpn_id )
3299 ORDER BY 1;
3300 elsif p_project_id is null then
3301 OPEN x_lpn_lov FOR
3302 SELECT license_plate_number,
3303 lpn_id,
3304 NVL(inventory_item_id, 0),
3305 NVL(organization_id, 0),
3306 revision,
3307 lot_number,
3308 serial_number,
3309 subinventory_code,
3310 NVL(locator_id, 0),
3311 NVL(parent_lpn_id, 0),
3312 NVL(sealed_status, 2),
3313 gross_weight_uom_code,
3314 NVL(gross_weight, 0),
3315 content_volume_uom_code,
3316 NVL(content_volume, 0)
3317 FROM wms_license_plate_numbers wlpn
3318 WHERE license_plate_number LIKE (p_lpn)
3319 AND organization_id = p_org_id
3320 AND lpn_context = 3
3321 and exists (
3322 select mtrl.lpn_id
3323 from mtl_txn_request_lines mtrl
3324 where mtrl.organization_id = p_org_id
3325 and mtrl.project_id is null
3326 and mtrl.lpn_id = wlpn.lpn_id
3327 and nvl(mtrl.task_id,-9999) = nvl(p_task_id,-9999)
3328 )
3329 UNION
3330 SELECT license_plate_number,
3331 lpn_id,
3332 NVL(inventory_item_id, 0),
3333 NVL(organization_id, 0),
3334 revision,
3335 lot_number,
3336 serial_number,
3337 subinventory_code,
3338 NVL(locator_id, 0),
3339 NVL(parent_lpn_id, 0),
3340 NVL(sealed_status, 2),
3341 gross_weight_uom_code,
3342 NVL(gross_weight, 0),
3343 content_volume_uom_code,
3344 NVL(content_volume, 0)
3345 FROM wms_license_plate_numbers wlpn
3346 WHERE license_plate_number LIKE (p_lpn)
3347 and organization_id = p_org_id
3348 AND lpn_context = 1
3349 and exists
3350 ( select inventory_location_id
3351 from mtl_item_locations mil
3352 where organization_id = p_org_id
3353 and nvl(wlpn.subinventory_code,'@@@') = nvl(mil.subinventory_code,'@@@')
3354 and mil.project_id is null
3355 and wlpn.locator_id = mil.inventory_location_id
3356 and nvl(task_id,-9999) = nvl(p_task_id,-9999)
3357 )
3358 AND inv_material_status_grp.is_status_applicable
3359 ('TRUE',
3360 NULL,
3361 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
3362 NULL,
3363 NULL,
3364 p_org_id,
3365 NULL,
3366 wlpn.subinventory_code,
3367 wlpn.locator_id,
3368 NULL,
3369 NULL,
3370 'Z') = 'Y'
3371 AND inv_material_status_grp.is_status_applicable
3372 ('TRUE',
3373 NULL,
3374 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
3375 NULL,
3376 NULL,
3377 p_org_id,
3378 NULL,
3379 wlpn.subinventory_code,
3380 wlpn.locator_id,
3381 NULL,
3382 NULL,
3383 'L') = 'Y'
3384 UNION
3385 SELECT license_plate_number,
3386 lpn_id,
3387 NVL(inventory_item_id, 0),
3388 NVL(organization_id, 0),
3389 revision,
3390 lot_number,
3391 serial_number,
3392 subinventory_code,
3393 NVL(locator_id, 0),
3394 NVL(parent_lpn_id, 0),
3395 NVL(sealed_status, 2),
3396 gross_weight_uom_code,
3397 NVL(gross_weight, 0),
3398 content_volume_uom_code,
3399 NVL(content_volume, 0)
3400 FROM wms_license_plate_numbers wlpn
3401 WHERE license_plate_number LIKE (p_lpn)
3402 and organization_id = p_org_id
3403 and (lpn_context = 5 or lpn_id = p_from_lpn_id )
3404 ORDER BY 1;
3405 end if;
3406
3407 END GET_RCV_LPN;
3408
3409
3410
3411 PROCEDURE GET_CYC_PARENT_LPN_LOV
3412 (x_lpn_lov OUT NOCOPY t_genref ,
3413 p_lpn IN VARCHAR2 ,
3414 p_unscheduled_entry IN NUMBER ,
3415 p_cycle_count_header_id IN NUMBER ,
3416 p_organization_id IN NUMBER ,
3417 p_subinventory_code IN VARCHAR2 ,
3418 p_locator_id IN NUMBER ,
3419 p_project_id IN NUMBER ,
3420 p_task_id IN NUMBER )
3421 IS
3422 l_container_discrepancy_option NUMBER;
3423
3424 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3425 BEGIN
3426 -- Get the cycle count container discrepancy flag
3427 SELECT NVL(container_discrepancy_option, 2)
3428 INTO l_container_discrepancy_option
3429 FROM mtl_cycle_count_headers
3430 WHERE cycle_count_header_id = p_cycle_count_header_id;
3431
3432 IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
3433 -- Select all LPN's which exist in the given org, sub, loc
3434 OPEN x_lpn_lov FOR
3435 SELECT license_plate_number,
3436 lpn_id,
3437 inventory_item_id,
3438 organization_id,
3439 revision,
3440 lot_number,
3441 serial_number,
3442 subinventory_code,
3443 locator_id,
3444 parent_lpn_id,
3445 NVL(sealed_status, 2),
3446 gross_weight_uom_code,
3447 NVL(gross_weight, 0),
3448 content_volume_uom_code,
3449 NVL(content_volume, 0),
3450 lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3451 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3452 --organization, whether the LPN is "Issued out of Stores".
3453 FROM wms_license_plate_numbers
3454 WHERE organization_id = p_organization_id
3455 AND (subinventory_code = p_subinventory_code OR
3456 l_container_discrepancy_option = 1)
3457 AND (NVL(locator_id, -99999) = NVL(p_locator_id, -99999) OR
3458 (l_container_discrepancy_option = 1
3459 AND locator_id in (
3460 select inventory_location_id
3461 from mtl_item_locations
3462 where nvl(segment19,-9999) = nvl(p_project_id,-9999)
3463 and nvl(segment20,-9999) = nvl(p_task_id,-9999)
3464 )
3465 )
3466 )
3467 AND license_plate_number LIKE (p_lpn)
3468 --AND lpn_context not in (4,6) --Bug# 4205672 --bug#4267956.Added 6 --Commented for bug#4886188
3469 ORDER BY license_plate_number;
3470 ELSE -- Unscheduled entries are not allowed
3471 -- Select only LPN's that exist in table MTL_CYCLE_COUNT_ENTRIES
3472 OPEN x_lpn_lov FOR
3473 SELECT UNIQUE wlpn.license_plate_number,
3474 wlpn.lpn_id,
3475 wlpn.inventory_item_id,
3476 wlpn.organization_id,
3477 wlpn.revision,
3478 wlpn.lot_number,
3479 wlpn.serial_number,
3480 wlpn.subinventory_code,
3481 wlpn.locator_id,
3482 wlpn.parent_lpn_id,
3483 NVL(wlpn.sealed_status, 2),
3484 wlpn.gross_weight_uom_code,
3485 NVL(wlpn.gross_weight, 0),
3486 wlpn.content_volume_uom_code,
3487 NVL(wlpn.content_volume, 0),
3488 wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3489 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3490 --organization, whether the LPN is "Issued out of Stores".
3491 FROM wms_license_plate_numbers wlpn,
3492 mtl_cycle_count_entries mcce
3493 WHERE wlpn.organization_id = p_organization_id
3494 AND (wlpn.subinventory_code = p_subinventory_code OR
3495 l_container_discrepancy_option = 1)
3496 -- Bug# 1609449
3497 --AND NVL(wlpn.locator_id, -99999) = NVL(p_locator_id, -99999)
3498 AND wlpn.license_plate_number LIKE (p_lpn)
3499 --AND wlpn.lpn_context not in (4,6) --Bug# 4205672 --bug#4267956.Added 6 --Commented for bug#4886188
3500 AND wlpn.lpn_id = mcce.parent_lpn_id
3501 AND mcce.organization_id = p_organization_id
3502 AND mcce.cycle_count_header_id = p_cycle_count_header_id
3503 AND (mcce.subinventory = p_subinventory_code OR
3504 l_container_discrepancy_option = 1)
3505 AND (NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999) OR
3506 (l_container_discrepancy_option = 1
3507 AND mcce.locator_id in (
3508 select inventory_location_id
3509 from mtl_item_locations
3510 where nvl(segment19,-9999) = nvl(p_project_id,-9999)
3511 and nvl(segment20,-9999) = nvl(p_task_id,-9999)
3512 )
3513 )
3514 )
3515 AND mcce.entry_status_code IN (1, 3)
3516 AND NVL(TRUNC(mcce.count_due_date, 'DD'), TRUNC(SYSDATE, 'DD'))
3517 >= TRUNC(SYSDATE, 'DD');
3518 END IF;
3519
3520 END GET_CYC_PARENT_LPN_LOV;
3521
3522
3523
3524 PROCEDURE GET_CYC_LPN_LOV
3525 (x_lpn_lov OUT NOCOPY t_genref ,
3526 p_lpn IN VARCHAR2 ,
3527 p_unscheduled_entry IN NUMBER ,
3528 p_cycle_count_header_id IN NUMBER ,
3529 p_organization_id IN NUMBER ,
3530 p_subinventory_code IN VARCHAR2 ,
3531 p_locator_id IN NUMBER ,
3532 p_parent_lpn_id IN NUMBER ,
3533 p_project_id IN NUMBER ,
3534 p_task_id IN NUMBER )
3535 IS
3536 l_container_discrepancy_option NUMBER;
3537
3538 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3539 BEGIN
3540 -- Get the cycle count container discrepancy flag
3541 SELECT NVL(container_discrepancy_option, 2)
3542 INTO l_container_discrepancy_option
3543 FROM mtl_cycle_count_headers
3544 WHERE cycle_count_header_id = p_cycle_count_header_id;
3545
3546 IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
3547 -- Select all LPN's which exist in the given org, sub, loc
3548 OPEN x_lpn_lov FOR
3549 SELECT license_plate_number,
3550 lpn_id,
3551 inventory_item_id,
3552 organization_id,
3553 revision,
3554 lot_number,
3555 serial_number,
3556 subinventory_code,
3557 locator_id,
3558 parent_lpn_id,
3559 NVL(sealed_status, 2),
3560 gross_weight_uom_code,
3561 NVL(gross_weight, 0),
3562 content_volume_uom_code,
3563 NVL(content_volume, 0),
3564 lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3565 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3566 --organization, whether the LPN is "Issued out of Stores".
3567 FROM wms_license_plate_numbers
3568 WHERE organization_id = p_organization_id
3569 AND (subinventory_code = p_subinventory_code OR
3570 l_container_discrepancy_option = 1)
3571 AND (NVL(locator_id, -99999) = NVL(p_locator_id, -99999) OR
3572 (l_container_discrepancy_option = 1
3573 AND locator_id in (
3574 select inventory_location_id
3575 from mtl_item_locations
3576 where nvl(segment19,-9999) = nvl(p_project_id,-9999)
3577 and nvl(segment20,-9999) = nvl(p_task_id,-9999)
3578 )
3579 )
3580 )
3581 AND license_plate_number LIKE (p_lpn)
3582 --AND lpn_context not in (4,6) --Bug# 4205672 --bug#4267956.Added 6 --Commented for bug#4886188
3583 ORDER BY license_plate_number;
3584 ELSE -- Unscheduled entries are not allowed
3585 -- Select only LPN's that exist in table MTL_CYCLE_COUNT_ENTRIES
3586 OPEN x_lpn_lov FOR
3587 SELECT UNIQUE wlpn.license_plate_number,
3588 wlpn.lpn_id,
3589 wlpn.inventory_item_id,
3590 wlpn.organization_id,
3591 wlpn.revision,
3592 wlpn.lot_number,
3593 wlpn.serial_number,
3594 wlpn.subinventory_code,
3595 wlpn.locator_id,
3596 wlpn.parent_lpn_id,
3597 NVL(wlpn.sealed_status, 2),
3598 wlpn.gross_weight_uom_code,
3599 NVL(wlpn.gross_weight, 0),
3600 wlpn.content_volume_uom_code,
3601 NVL(wlpn.content_volume, 0),
3602 wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3603 -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3604 --organization, whether the LPN is "Issued out of Stores".
3605 FROM wms_license_plate_numbers wlpn,
3606 mtl_cycle_count_entries mcce
3607 WHERE wlpn.organization_id = p_organization_id
3608 AND (wlpn.subinventory_code = p_subinventory_code OR
3609 l_container_discrepancy_option = 1)
3610 -- Bug# 1609449
3611 --AND NVL(wlpn.locator_id, -99999) = NVL(p_locator_id, -99999)
3612 AND wlpn.license_plate_number LIKE (p_lpn)
3613 --AND wlpn.lpn_context not in (4,6) --Bug# 4205672 --bug#4267956.Added 6 --Commented for bug#4886188
3614 AND wlpn.parent_lpn_id = p_parent_lpn_id
3615 AND wlpn.lpn_id = mcce.parent_lpn_id
3616 AND mcce.organization_id = p_organization_id
3617 AND mcce.cycle_count_header_id = p_cycle_count_header_id
3618 AND (mcce.subinventory = p_subinventory_code OR
3619 l_container_discrepancy_option = 1)
3620 AND (NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999) OR
3621 (l_container_discrepancy_option = 1
3622 AND mcce.locator_id in (
3623 select inventory_location_id
3624 from mtl_item_locations
3625 where nvl(segment19,-9999) = nvl(p_project_id,-9999)
3626 and nvl(segment20,-9999) = nvl(p_task_id,-9999)
3627 )
3628 )
3629 )
3630 AND mcce.entry_status_code IN (1, 3)
3631 AND NVL(TRUNC(mcce.count_due_date, 'DD'), TRUNC(SYSDATE, 'DD'))
3632 >= TRUNC(SYSDATE, 'DD');
3633 END IF;
3634
3635 END GET_CYC_LPN_LOV;
3636
3637 /* PJM-WMS Integration:Return only the LPNs residing in physical locators.
3638 * Use the table mtl_item_locations instead of mtl_item_locations_kfv.
3639 * Use the function INV_PROJECT.get_locsegs() to retrieve the
3640 * concatenated segments.
3641 */
3642 PROCEDURE GET_CGUPDATE_LPN
3643 (x_lpn_lov OUT NOCOPY t_genref,
3644 p_org_id IN NUMBER,
3645 p_lpn IN VARCHAR2)
3646 IS
3647 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3648 BEGIN
3649
3650 OPEN x_lpn_lov FOR
3651 SELECT wlpn.license_plate_number,
3652 wlpn.lpn_id,
3653 wlpn.subinventory_code,
3654 wlpn.locator_id,
3655 -- PJM-WMS Integration
3656 INV_PROJECT.GET_LOCSEGS(mil.inventory_location_id,p_org_id)
3657 FROM mtl_item_locations mil,-- -PJM-WMS Integration
3658 wms_license_plate_numbers wlpn
3659 WHERE mil.inventory_location_id = wlpn.locator_id
3660 AND mil.organization_id = wlpn.organization_id
3661 AND mil.segment19 is null
3662 -- bug 2267845 fix. checking this conditon
3663 -- for identifying non project locators instead of
3664 -- 'phyiscal_location_id is null'
3665 AND wlpn.license_plate_number LIKE (p_lpn)
3666 AND wlpn.organization_id = p_org_id
3667 AND wlpn.lpn_context = 1
3668 ORDER BY license_plate_number;
3669
3670 END GET_CGUPDATE_LPN;
3671
3672 PROCEDURE GET_PALLET_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref,
3673 p_org_id IN NUMBER,
3674 p_lpn VARCHAR2
3675 )
3676 IS
3677 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3678 BEGIN
3679 OPEN x_lpn_lov FOR
3680 SELECT wlpn.license_plate_number,
3681 wlpn.lpn_id,
3682 NVL(wlpn.inventory_item_id, 0),
3683 NVL(wlpn.organization_id, 0),
3684 wlpn.revision,
3685 wlpn.lot_number,
3686 wlpn.serial_number,
3687 wlpn.subinventory_code,
3688 NVL(wlpn.locator_id, 0),
3689 NVL(wlpn.parent_lpn_id, 0),
3690 NVL(wlpn.sealed_status, 2),
3691 wlpn.gross_weight_uom_code,
3692 NVL(wlpn.gross_weight, 0),
3693 wlpn.content_volume_uom_code,
3694 NVL(wlpn.content_volume, 0)
3695 /*select license_plate_number,
3696 wlpn.lpn_id,
3697 NVL(wlpn.inventory_item_id, 0),
3698 NVL(wlpn.organization_id, 0),
3699 wlpn.revision,
3700 wlpn.lot_number,
3701 wlpn.serial_number,
3702 wlpn.subinventory_code,
3703 NVL(wlpn.locator_id, 0),
3704 NVL(wlpn.parent_lpn_id, 0),
3705 NVL(wlpn.sealed_status, 2),
3706 wlpn.gross_weight_uom_code,
3707 NVL(wlpn.gross_weight, 0),
3708 wlpn.content_volume_uom_code,
3709 NVL(wlpn.content_volume, 0)*/
3710 FROM wms_license_plate_numbers wlpn,
3711 mtl_system_items_kfv msik
3712 WHERE wlpn.organization_id = p_org_id
3713 AND wlpn.inventory_item_id IS NOT NULL
3714 AND msik.inventory_item_id = wlpn.inventory_item_id
3715 AND msik.organization_id = wlpn.organization_id
3716 AND msik.container_type_code = 'PALLET'
3717 AND wlpn.license_plate_number LIKE (p_lpn);
3718 END GET_PALLET_LPN_LOV;
3719
3720 PROCEDURE CHECK_LPN_LOV
3721 ( p_lpn IN VARCHAR2,
3722 p_organization_id IN NUMBER,
3723 x_lpn_id OUT NOCOPY NUMBER,
3724 x_inventory_item_id OUT NOCOPY NUMBER,
3725 x_organization_id OUT NOCOPY NUMBER,
3726 x_lot_number OUT NOCOPY VARCHAR2,
3727 x_revision OUT NOCOPY VARCHAR2,
3728 x_serial_number OUT NOCOPY VARCHAR2,
3729 x_subinventory OUT NOCOPY VARCHAR2,
3730 x_locator_id OUT NOCOPY NUMBER,
3731 x_parent_lpn_id OUT NOCOPY NUMBER,
3732 x_sealed_status OUT NOCOPY NUMBER,
3733 x_gross_weight OUT NOCOPY NUMBER,
3734 x_gross_weight_uom_code OUT NOCOPY VARCHAR2,
3735 x_content_volume OUT NOCOPY NUMBER,
3736 x_content_volume_uom_code OUT NOCOPY VARCHAR2,
3737 x_source_type_id OUT NOCOPY NUMBER,
3738 x_source_header_id OUT NOCOPY NUMBER,
3739 x_source_name OUT NOCOPY VARCHAR2,
3740 x_source_line_id OUT NOCOPY NUMBER,
3741 x_source_line_detail_id OUT NOCOPY NUMBER,
3742 x_cost_group_id OUT NOCOPY NUMBER,
3743 x_newLPN OUT NOCOPY VARCHAR2,
3744 x_concat_segments OUT NOCOPY VARCHAR2,
3745 x_context OUT NOCOPY VARCHAR2,
3746 x_return_status OUT NOCOPY VARCHAR2,
3747 x_msg_data OUT NOCOPY VARCHAR2,
3748 p_createnewlpn_flag IN VARCHAR2
3749 )
3750 IS
3751 l_flag1 NUMBER:=0;
3752 l_flag2 NUMBER:=0;
3753 l_locator_id NUMBER:=-1;
3754 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3755 BEGIN
3756 -- l_flag1 := 0;
3757 -- l_flag2 := 0;
3758
3759 BEGIN
3760 SELECT LPN_ID,
3761 INVENTORY_ITEM_ID,
3762 ORGANIZATION_ID,
3763 LOT_NUMBER,
3764 REVISION,
3765 SERIAL_NUMBER,
3766 SUBINVENTORY_CODE,
3767 LOCATOR_ID,
3768 PARENT_LPN_ID,
3769 SEALED_STATUS,
3770 GROSS_WEIGHT_UOM_CODE,
3771 GROSS_WEIGHT,
3772 CONTENT_VOLUME_UOM_CODE,
3773 CONTENT_VOLUME,
3774 SOURCE_TYPE_ID,
3775 SOURCE_HEADER_ID,
3776 SOURCE_NAME,
3777 SOURCE_LINE_ID,
3778 SOURCE_LINE_DETAIL_ID,
3779 cost_group_id,
3780 'FALSE',
3781 1,
3782 LOCATOR_ID,
3783 LPN_CONTEXT
3784 INTO x_lpn_id,
3785 x_inventory_item_id,
3786 x_organization_id,
3787 x_lot_number,
3788 x_revision,
3789 x_serial_number,
3790 x_subinventory,
3791 x_locator_id,
3792 x_parent_lpn_id,
3793 x_sealed_status,
3794 x_gross_weight_uom_code,
3795 x_gross_weight,
3796 x_content_volume_uom_code,
3797 x_content_volume,
3798 x_source_type_id,
3799 x_source_header_id,
3800 x_source_name,
3801 x_source_line_id,
3802 x_source_line_detail_id,
3803 x_cost_group_id,
3804 x_newLPN,
3805 l_flag1,
3806 l_locator_id,
3807 x_context
3808 FROM wms_license_plate_numbers
3809 WHERE license_plate_number = p_lpn;
3810
3811 EXCEPTION
3812 WHEN no_data_found THEN
3813
3814 x_newLPN := 'TRUE';
3815 x_concat_segments := 'NULL';
3816
3817 -- Create new lpn
3818 IF (p_createnewlpn_flag = 'TRUE') THEN
3819 inv_rcv_common_apis.create_lpn(
3820 p_organization_id,
3821 p_lpn,
3822 x_lpn_id,
3823 x_return_status,
3824 x_msg_data);
3825 END IF;
3826
3827 -- return;
3828 END;
3829
3830 -- Only get from milk if the locator is not null
3831 IF (l_flag1 = 1 AND Nvl(l_locator_id,-1)<>-1) THEN
3832 select 1,
3833 milk.concatenated_segments
3834 INTO l_flag2,
3835 x_concat_segments
3836 FROM wms_license_plate_numbers w,
3837 mtl_item_locations_kfv milk
3838 WHERE w.license_plate_number = p_lpn
3839 AND w.locator_id = milk.inventory_location_id
3840 AND w.organization_id = milk.organization_id;
3841
3842 IF l_flag2 = 0 THEN
3843 x_concat_segments := 'NULL';
3844 END IF;
3845 END IF;
3846 END CHECK_LPN_LOV;
3847
3848 /**********************************************************************************
3849 WMS - PJM Integration Enhancements
3850 Differences from CHECK_LPN_LOV
3851 1. Returns the locator concatenated segments without SEGMENT19 and SEGMENT20.
3852 by making a call to the procedure INV_PROJECT.GET_LOCSEGS
3853 2. Returns the Project ID, Project Number, Task ID and Task Number associated
3854 with the locator by making a call to the package INV_PROJECT.
3855 **********************************************************************************/
3856 PROCEDURE CHECK_PJM_LPN_LOV
3857 ( p_lpn IN VARCHAR2,
3858 p_organization_id IN NUMBER,
3859 x_lpn_id OUT NOCOPY NUMBER,
3860 x_inventory_item_id OUT NOCOPY NUMBER,
3861 x_organization_id OUT NOCOPY NUMBER,
3862 x_lot_number OUT NOCOPY VARCHAR2,
3863 x_revision OUT NOCOPY VARCHAR2,
3864 x_serial_number OUT NOCOPY VARCHAR2,
3865 x_subinventory OUT NOCOPY VARCHAR2,
3866 x_locator_id OUT NOCOPY NUMBER,
3867 x_parent_lpn_id OUT NOCOPY NUMBER,
3868 x_sealed_status OUT NOCOPY NUMBER,
3869 x_gross_weight OUT NOCOPY NUMBER,
3870 x_gross_weight_uom_code OUT NOCOPY VARCHAR2,
3871 x_content_volume OUT NOCOPY NUMBER,
3872 x_content_volume_uom_code OUT NOCOPY VARCHAR2,
3873 x_source_type_id OUT NOCOPY NUMBER,
3874 x_source_header_id OUT NOCOPY NUMBER,
3875 x_source_name OUT NOCOPY VARCHAR2,
3876 x_source_line_id OUT NOCOPY NUMBER,
3877 x_source_line_detail_id OUT NOCOPY NUMBER,
3878 x_cost_group_id OUT NOCOPY NUMBER,
3879 x_newLPN OUT NOCOPY VARCHAR2,
3880 x_concat_segments OUT NOCOPY VARCHAR2,
3881 x_project_id OUT NOCOPY VARCHAR2,
3882 x_project_number OUT NOCOPY VARCHAR2,
3883 x_task_id OUT NOCOPY VARCHAR2,
3884 x_task_number OUT NOCOPY VARCHAR2,
3885 x_context OUT NOCOPY VARCHAR2,
3886 x_return_status OUT NOCOPY VARCHAR2,
3887 x_msg_data OUT NOCOPY VARCHAR2,
3888 p_createnewlpn_flag IN VARCHAR2
3889 )
3890 IS
3891 l_locator_id NUMBER:=-1;
3892 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3893 BEGIN
3894 BEGIN
3895 SELECT LPN_ID,
3896 INVENTORY_ITEM_ID,
3897 ORGANIZATION_ID,
3898 LOT_NUMBER,
3899 REVISION,
3900 SERIAL_NUMBER,
3901 SUBINVENTORY_CODE,
3902 LOCATOR_ID,
3903 PARENT_LPN_ID,
3904 SEALED_STATUS,
3905 GROSS_WEIGHT_UOM_CODE,
3906 GROSS_WEIGHT,
3907 CONTENT_VOLUME_UOM_CODE,
3908 CONTENT_VOLUME,
3909 SOURCE_TYPE_ID,
3910 SOURCE_HEADER_ID,
3911 SOURCE_NAME,
3912 SOURCE_LINE_ID,
3913 SOURCE_LINE_DETAIL_ID,
3914 cost_group_id,
3915 'FALSE',
3916 LOCATOR_ID,
3917 LPN_CONTEXT,
3918 INV_PROJECT.GET_LOCSEGS(LOCATOR_ID,ORGANIZATION_ID),
3919 INV_PROJECT.GET_PROJECT_ID,
3920 INV_PROJECT.GET_PROJECT_NUMBER,
3921 INV_PROJECT.GET_TASK_ID,
3922 INV_PROJECT.GET_TASK_NUMBER
3923 INTO x_lpn_id,
3924 x_inventory_item_id,
3925 x_organization_id,
3926 x_lot_number,
3927 x_revision,
3928 x_serial_number,
3929 x_subinventory,
3930 x_locator_id,
3931 x_parent_lpn_id,
3932 x_sealed_status,
3933 x_gross_weight_uom_code,
3934 x_gross_weight,
3935 x_content_volume_uom_code,
3936 x_content_volume,
3937 x_source_type_id,
3938 x_source_header_id,
3939 x_source_name,
3940 x_source_line_id,
3941 x_source_line_detail_id,
3942 x_cost_group_id,
3943 x_newLPN,
3944 l_locator_id,
3945 x_context,
3946 x_concat_segments,
3947 x_project_id,
3948 x_project_number,
3949 x_task_id,
3950 x_task_number
3951 FROM wms_license_plate_numbers
3952 WHERE license_plate_number = p_lpn;
3953
3954 EXCEPTION
3955 WHEN no_data_found THEN
3956 x_newLPN := 'TRUE';
3957 x_concat_segments := 'NULL';
3958 -- Create new lpn
3959 IF (p_createnewlpn_flag = 'TRUE') THEN
3960 inv_rcv_common_apis.create_lpn
3961 (
3962 p_organization_id,
3963 p_lpn,
3964 x_lpn_id,
3965 x_return_status,
3966 x_msg_data
3967 );
3968 END IF;
3969 END;
3970 END CHECK_PJM_LPN_LOV;
3971
3972
3973 PROCEDURE GET_CONTEXT_LPN_LOV
3974 (x_lpn_lov OUT NOCOPY t_genref,
3975 p_organization_id IN NUMBER,
3976 p_context IN VARCHAR2,
3977 p_lpn IN VARCHAR2
3978 )
3979 IS
3980 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3981 BEGIN
3982 OPEN x_lpn_lov FOR
3983 SELECT distinct
3984 license_plate_number,
3985 lpn_id,
3986 NVL(inventory_item_id, 0),
3987 NVL(organization_id, 0),
3988 revision,
3989 lot_number,
3990 serial_number,
3991 subinventory_code,
3992 NVL(locator_id, 0),
3993 NVL(parent_lpn_id, 0),
3994 NVL(sealed_status, 2),
3995 gross_weight_uom_code,
3996 NVL(gross_weight, 0),
3997 content_volume_uom_code,
3998 NVL(content_volume, 0)
3999 FROM wms_license_plate_numbers
4000 WHERE license_plate_number LIKE (p_lpn)
4001 AND organization_id = p_organization_id
4002 AND lpn_context = NVL(TO_NUMBER(p_context), lpn_context);
4003
4004 END GET_CONTEXT_LPN_LOV;
4005
4006 --"Returns"
4007 PROCEDURE GET_RETURN_LPN
4008 (x_lpn_lov OUT NOCOPY t_genref,
4009 p_org_id IN NUMBER,
4010 p_lpn IN VARCHAR2)
4011 IS
4012 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4013 BEGIN
4014
4015 OPEN x_lpn_lov FOR
4016 SELECT wlpn.license_plate_number,
4017 wlpn.lpn_id,
4018 NVL(wlpn.inventory_item_id, 0),
4019 NVL(wlpn.organization_id, 0),
4020 wlpn.revision,
4021 wlpn.lot_number,
4022 wlpn.serial_number,
4023 'FULL', -- Instead of Subinventory
4024 NVL(wlpn.locator_id, 0),
4025 NVL(wlpn.parent_lpn_id, 0),
4026 NVL(wlpn.sealed_status, 2),
4027 wlpn.gross_weight_uom_code,
4028 NVL(wlpn.gross_weight, 0),
4029 wlpn.content_volume_uom_code,
4030 NVL(wlpn.content_volume, 0)
4031 FROM wms_license_plate_numbers wlpn
4032 WHERE wlpn.license_plate_number LIKE (p_lpn)
4033 AND wlpn.organization_id = p_org_id
4034 AND WMS_RETURN_SV.GET_LPN_MARKED_STATUS(wlpn.lpn_id, wlpn.organization_id)='FULL'
4035 UNION ALL
4036 SELECT wlpn.license_plate_number,
4037 wlpn.lpn_id,
4038 NVL(wlpn.inventory_item_id, 0),
4039 NVL(wlpn.organization_id, 0),
4040 wlpn.revision,
4041 wlpn.lot_number,
4042 wlpn.serial_number,
4043 'PARTIAL', -- Instead of Subinventory
4044 NVL(wlpn.locator_id, 0),
4045 NVL(wlpn.parent_lpn_id, 0),
4046 NVL(wlpn.sealed_status, 2),
4047 wlpn.gross_weight_uom_code,
4048 NVL(wlpn.gross_weight, 0),
4049 wlpn.content_volume_uom_code,
4050 NVL(wlpn.content_volume, 0)
4051 FROM wms_license_plate_numbers wlpn
4052 WHERE wlpn.license_plate_number LIKE (p_lpn)
4053 AND wlpn.organization_id = p_org_id
4054 AND WMS_RETURN_SV.GET_LPN_MARKED_STATUS(wlpn.lpn_id, wlpn.organization_id)='PARTIAL'
4055 ORDER BY 1;
4056
4057 END GET_RETURN_LPN;
4058 --"Returns"
4059
4060
4061 PROCEDURE GET_REQEXP_LPN
4062 (x_lpn_lov OUT NOCOPY t_genref ,
4063 p_lpn IN VARCHAR2 ,
4064 p_requisition_header_id IN VARCHAR2 ,
4065 p_mode IN VARCHAR2 DEFAULT NULL,
4066 p_inventory_item_id IN VARCHAR2 DEFAULT NULL
4067 )
4068 IS
4069 l_req_num VARCHAR2(10);
4070 l_progress VARCHAR2(10);
4071 l_order_header_id NUMBER;
4072 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4073 BEGIN
4074 l_progress := '10';
4075 IF (p_requisition_header_id IS NOT NULL) THEN
4076 SELECT segment1
4077 INTO l_req_num
4078 FROM po_requisition_headers_all
4079 WHERE requisition_header_id = p_requisition_header_id;
4080 END IF;
4081
4082 l_progress := '20';
4083 SELECT header_id
4084 INTO l_order_header_id
4085 FROM oe_order_headers_all
4086 WHERE orig_sys_document_ref = l_req_num
4087 AND order_source_id = 10;
4088 --AND order_type_id = 1023;
4089
4090 l_progress := '30';
4091
4092 -- Nested LPN changes. Changed this lov to show all child LPNs also.
4093 -- If Mode is confirm then show only those LPNs with contents, otherwise show all LPNs
4094
4095 IF p_mode IS NULL THEN
4096 OPEN x_lpn_lov FOR
4097 SELECT distinct wlpn.license_plate_number
4098 , wlpn.lpn_id
4099 , count_row.n
4100 FROM wsh_delivery_details_ob_grp_v wdd
4101 , wsh_delivery_assignments_v wda
4102 , wsh_delivery_details_ob_grp_v wdd1
4103 , wms_license_plate_numbers wlpn, (SELECT count(*) n
4104 FROM wsh_delivery_details_ob_grp_v wdd
4105 , wms_license_plate_numbers wlpn
4106 WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4107 FROM wsh_delivery_assignments_v wda
4108 , wsh_delivery_details_ob_grp_v wdd
4109 WHERE wda.delivery_detail_id in (select delivery_detail_id
4110 FROM wsh_delivery_details_ob_grp_v
4111 WHERE source_header_id = l_order_header_id)
4112 AND wda.PARENT_DELIVERY_DETAIL_ID = wdd.delivery_detail_id)
4113 AND wlpn.lpn_context = 6
4114 AND wlpn.organization_id = wdd.organization_id
4115 AND wlpn.outermost_lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4116 WHERE wdd.source_header_id = l_order_header_id
4117 AND wdd.delivery_detail_id = wda.delivery_detail_id
4118 AND wdd1.delivery_detail_id = wda.PARENT_DELIVERY_DETAIL_ID
4119 AND wlpn.lpn_context = 6
4120 AND wlpn.organization_id = wdd1.organization_id
4121 AND wlpn.outermost_lpn_id = NVL(wdd1.lpn_id, -9999)
4122 AND wlpn.license_plate_number LIKE (p_lpn)
4123 ORDER BY wlpn.license_plate_number;
4124 ELSIF p_mode = 'E' THEN
4125 /* OPEN x_lpn_lov FOR
4126 SELECT distinct wlpn.license_plate_number
4127 , wlpn.lpn_id
4128 , count_row.n
4129 FROM wsh_delivery_details_ob_grp_v wdd
4130 , wsh_delivery_assignments_v wda
4131 , wsh_delivery_details_ob_grp_v wdd1
4132 , wms_license_plate_numbers wlpn, (SELECT count(*) n
4133 FROM wsh_delivery_details_ob_grp_v wdd
4134 , wms_license_plate_numbers wlpn
4135 WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4136 FROM wsh_delivery_assignments_v wda
4137 , wsh_delivery_details_ob_grp_v wdd
4138 WHERE wda.delivery_detail_id in (select delivery_detail_id
4139 FROM wsh_delivery_details_ob_grp_v
4140 WHERE source_header_id = l_order_header_id)
4141 AND wda.PARENT_DELIVERY_DETAIL_ID = wdd.delivery_detail_id)
4142 AND wlpn.lpn_context = 6
4143 AND wlpn.organization_id = wdd.organization_id
4144 AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4145 WHERE wdd.source_header_id = l_order_header_id
4146 AND wdd.delivery_detail_id = wda.delivery_detail_id
4147 AND wdd1.delivery_detail_id = wda.PARENT_DELIVERY_DETAIL_ID
4148 AND wlpn.lpn_context = 6
4149 AND wlpn.organization_id = wdd1.organization_id
4150 AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4151 AND wlpn.license_plate_number LIKE (p_lpn)
4152 ORDER BY wlpn.license_plate_number;*/
4153
4154 -- Getting Count is deprecated from Patchset J We will get count from the validation logic itself.
4155 OPEN x_lpn_lov FOR
4156 SELECT distinct wln.license_plate_number
4157 , wln.lpn_id
4158 , 1
4159 FROM wms_license_plate_numbers wln,
4160 wsh_delivery_details_ob_grp_v wdd
4161 WHERE wln.lpn_context= 6
4162 AND wln.lpn_id = wdd.lpn_id
4163 AND wln.license_plate_number LIKE (p_lpn)
4164 ORDER BY wln.license_plate_number;
4165
4166 ELSIF p_mode = 'C' THEN
4167 -- This is changed based on Item Info, case for Item Initiated Receipt.
4168 -- If Item info is present or passed from the UI then LPN should be restrcied based on Item
4169 -- Otherwise all the LPN's for the shipment should be displayed in the LOV
4170
4171 if p_inventory_item_id is null then
4172 OPEN x_lpn_lov FOR
4173 SELECT distinct wlpn.license_plate_number
4174 , wlpn.lpn_id
4175 , count_row.n
4176 FROM wsh_delivery_details_ob_grp_v wdd
4177 , wsh_delivery_assignments_v wda
4178 , wsh_delivery_details_ob_grp_v wdd1
4179 , wms_license_plate_numbers wlpn, (SELECT count(*) n
4180 FROM wsh_delivery_details_ob_grp_v wdd
4181 , wms_license_plate_numbers wlpn
4182 WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4183 FROM wsh_delivery_assignments_v wda
4184 , wsh_delivery_details_ob_grp_v wdd
4185 WHERE wda.delivery_detail_id in (select delivery_detail_id
4186 FROM wsh_delivery_details_ob_grp_v
4187 WHERE source_header_id = l_order_header_id)
4188 AND wda.PARENT_DELIVERY_DETAIL_ID = wdd.delivery_detail_id)
4189 AND wlpn.lpn_context = 6
4190 AND wlpn.organization_id = wdd.organization_id
4191 -- Nested LPN changes
4192 AND EXISTS (SELECT parent_lpn_id
4193 FROM wms_lpn_contents wlc
4194 WHERE parent_lpn_id = wlpn.lpn_id)
4195 AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4196 WHERE wdd.source_header_id = l_order_header_id
4197 AND wdd.delivery_detail_id = wda.delivery_detail_id
4198 AND wdd1.delivery_detail_id = wda.PARENT_DELIVERY_DETAIL_ID
4199 AND wlpn.lpn_context = 6
4200 AND wlpn.organization_id = wdd1.organization_id
4201 AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4202 AND wlpn.license_plate_number LIKE (p_lpn)
4203 -- Nested LPN changes
4204 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents wlc WHERE parent_lpn_id = wlpn.lpn_id)
4205 ORDER BY wlpn.license_plate_number;
4206 Else
4207 OPEN x_lpn_lov FOR
4208 SELECT distinct wlpn.license_plate_number
4209 , wlpn.lpn_id
4210 , count_row.n
4211 FROM wsh_delivery_details_ob_grp_v wdd
4212 , wsh_delivery_assignments_v wda
4213 , wsh_delivery_details_ob_grp_v wdd1
4214 , wms_license_plate_numbers wlpn, (SELECT count(*) n
4215 FROM wsh_delivery_details_ob_grp_v wdd
4216 , wms_license_plate_numbers wlpn
4217 WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4218 FROM wsh_delivery_assignments_v wda
4219 , wsh_delivery_details_ob_grp_v wdd
4220 WHERE wda.delivery_detail_id in (select delivery_detail_id
4221 FROM wsh_delivery_details_ob_grp_v
4222 WHERE source_header_id = l_order_header_id)
4223 AND wda.PARENT_DELIVERY_DETAIL_ID = wdd.delivery_detail_id)
4224 AND wlpn.lpn_context = 6
4225 AND wlpn.organization_id = wdd.organization_id
4226 -- Nested LPN changes
4227 AND EXISTS (SELECT parent_lpn_id
4228 FROM wms_lpn_contents wlc
4229 WHERE parent_lpn_id = wlpn.lpn_id)
4230 AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4231 WHERE wdd.source_header_id = l_order_header_id
4232 AND wdd.delivery_detail_id = wda.delivery_detail_id
4233 AND wdd1.delivery_detail_id = wda.PARENT_DELIVERY_DETAIL_ID
4234 AND wlpn.lpn_context = 6
4235 AND wlpn.organization_id = wdd1.organization_id
4236 AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4237 AND wlpn.license_plate_number LIKE (p_lpn)
4238 -- Nested LPN changes
4239 AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents wlc WHERE parent_lpn_id = wlpn.lpn_id
4240 and wlc.inventory_item_id = p_inventory_item_id)
4241 ORDER BY wlpn.license_plate_number;
4242 End if;
4243 END IF;
4244
4245 END GET_REQEXP_LPN;
4246
4247
4248 PROCEDURE GET_UPDATE_LPN
4249 (x_lpn_lov OUT NOCOPY t_genref,
4250 p_org_id IN NUMBER,
4251 p_lpn IN VARCHAR2)
4252 IS
4253 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4254 BEGIN
4255
4256 OPEN x_lpn_lov FOR
4257 SELECT license_plate_number,
4258 lpn_id,
4259 inventory_item_id,
4260 organization_id,
4261 revision,
4262 lot_number,
4263 serial_number,
4264 subinventory_code,
4265 locator_id,
4266 parent_lpn_id,
4267 NVL(sealed_status, 2),
4268 gross_weight_uom_code,
4269 NVL(gross_weight, 0),
4270 content_volume_uom_code,
4271 NVL(content_volume, 0),
4272 lpn_context --Added for Bug#6504032
4273 FROM wms_license_plate_numbers wlpn
4274 WHERE wlpn.organization_id = p_org_id
4275 AND wlpn.license_plate_number LIKE (p_lpn)
4276 AND wlpn.lpn_context IN (1, 2, 3 , 5, 11); --Inventory, pregenerated, picked contexts /*Resides in WIP(2) added for bug#3953941*/
4277 -- Added 3 to pick LPNS in status 'Resides n Receiving' Bug 5501058
4278 END GET_UPDATE_LPN;
4279
4280
4281
4282 PROCEDURE GET_RECONFIG_LPN
4283 (x_lpn_lov OUT NOCOPY t_genref,
4284 p_org_id IN NUMBER,
4285 p_lpn IN VARCHAR2)
4286 IS
4287 BEGIN
4288
4289 OPEN x_lpn_lov FOR
4290 select distinct outer.license_plate_number,
4291 outer.subinventory_code,
4292 milk.concatenated_segments,
4293 outer.locator_id,
4294 outer.lpn_id,
4295 outer.lpn_context,
4296 NVL(outer.sealed_status, 2),
4297 outer.gross_weight_uom_code,
4298 NVL(outer.gross_weight, 0),
4299 outer.content_volume_uom_code,
4300 NVL(outer.content_volume, 0)
4301 from wms_license_plate_numbers outer, wms_license_plate_numbers inner,
4302 mtl_item_locations_kfv milk
4303 where inner.outermost_lpn_id <> inner.lpn_id
4304 AND inner.outermost_lpn_id = outer.lpn_id
4305 AND outer.locator_id = milk.inventory_location_id(+)
4306 and outer.lpn_context in (1, 11)
4307 and outer.organization_id = p_org_id
4308 and outer.license_plate_number LIKE (p_lpn);
4309 END GET_RECONFIG_LPN;
4310
4311
4312
4313
4314 FUNCTION SUB_LPN_CONTROLLED(p_subinventory_code IN VARCHAR2,
4315 p_org_id IN NUMBER)
4316 RETURN VARCHAR2
4317 IS
4318 l_ret_val VARCHAR2(1) := 'Y';
4319 l_lpn_cf_flag NUMBER;
4320 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4321 BEGIN
4322 IF (p_subinventory_code IS NULL) THEN
4323 RETURN 'Y';
4324 ELSE
4325 SELECT lpn_controlled_flag
4326 INTO l_lpn_cf_flag
4327 FROM MTL_SECONDARY_INVENTORIES msi
4328 WHERE msi.organization_id = p_org_id
4329 AND msi.secondary_inventory_name = p_subinventory_code;
4330
4331 IF ((l_lpn_cf_flag) IS NULL OR (l_lpn_cf_flag = 2)) THEN
4332 RETURN 'N';
4333 ELSE
4334 RETURN 'Y';
4335 END IF;
4336 END IF;
4337
4338 END SUB_LPN_CONTROLLED;
4339
4340 PROCEDURE GET_BULK_PACK_LPN
4341 (x_lpn_lov OUT NOCOPY t_genref,
4342 p_org_id IN NUMBER,
4343 p_lpn IN VARCHAR2,
4344 p_subinventory IN VARCHAR2,
4345 p_locator IN NUMBER
4346 )
4347 IS
4348 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4349 BEGIN
4350
4351 OPEN x_lpn_lov FOR
4352 SELECT license_plate_number,
4353 lpn_id,
4354 inventory_item_id,
4355 organization_id,
4356 revision,
4357 lot_number,
4358 serial_number,
4359 subinventory_code,
4360 locator_id,
4361 parent_lpn_id,
4362 NVL(sealed_status, 2),
4363 gross_weight_uom_code,
4364 NVL(gross_weight, 0),
4365 content_volume_uom_code,
4366 NVL(content_volume, 0)
4367 FROM wms_license_plate_numbers wlpn
4368 WHERE wlpn.organization_id = p_org_id
4369 AND wlpn.license_plate_number LIKE (p_lpn)
4370 AND wlpn.subinventory_code = nvl(p_subinventory,wlpn.subinventory_Code)
4371 AND wlpn.locator_id = decode(p_locator,0,wlpn.locator_id,p_locator)
4372 AND wlpn.inventory_item_id is not null
4373 AND wlpn.lpn_id NOT IN ( select content_lpn_id from mtl_material_transactions_temp where content_lpn_id = wlpn.lpn_id)
4374 AND wlpn.parent_lpn_id is null
4375 AND wlpn.lpn_context = 1
4376 ORDER BY license_plate_number; --Inventory
4377 END GET_BULK_PACK_LPN;
4378
4379
4380 PROCEDURE Get_Picked_Split_From_LPNs(
4381 x_lpn_lov OUT NOCOPY t_genref
4382 , p_organization_id IN NUMBER
4383 , p_lpn_id IN VARCHAR2
4384 ) IS
4385 BEGIN
4386 open x_lpn_lov for
4387 SELECT wlpn.license_plate_number,
4388 wlpn.lpn_id,
4389 NVL(wlpn.inventory_item_id, 0),
4390 NVL(wlpn.organization_id, 0),
4391 wlpn.revision,
4392 wlpn.lot_number,
4393 wlpn.serial_number,
4394 wlpn.subinventory_code,
4395 NVL(wlpn.locator_id, 0),
4396 NVL(wlpn.parent_lpn_id, 0),
4397 NVL(wlpn.sealed_status, 2),
4398 wlpn.gross_weight_uom_code,
4399 NVL(wlpn.gross_weight, 0),
4400 wlpn.content_volume_uom_code,
4401 NVL(wlpn.content_volume, 0),
4402 wdd.delivery_detail_id
4403 FROM wms_license_plate_numbers wlpn,
4404 wsh_delivery_details wdd
4405 WHERE wlpn.organization_id = p_organization_id
4406 AND wlpn.lpn_context = 11
4407 AND wlpn.license_plate_number LIKE (p_lpn_id)
4408 AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_organization_id) ='Y'
4409 AND inv_material_status_grp.is_status_applicable (
4410 'TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_SPLIT, NULL,
4411 NULL, p_organization_id, NULL, wlpn.subinventory_code,
4412 wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
4413 AND inv_material_status_grp.is_status_applicable (
4414 'TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_SPLIT, NULL,
4415 NULL, p_organization_id, NULL, wlpn.subinventory_code,
4416 wlpn.locator_id, NULL, NULL, 'L') = 'Y'
4417 AND wdd.lpn_id = wlpn.lpn_id
4418 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
4419 ORDER BY license_plate_number;
4420 END Get_Picked_Split_From_LPNs;
4421
4422
4423 PROCEDURE get_item_load_lpn_lov
4424 (x_lpn_lov OUT NOCOPY t_genref ,
4425 p_organization_id IN NUMBER ,
4426 p_lpn_id IN NUMBER ,
4427 p_lpn_context IN NUMBER ,
4428 p_employee_id IN NUMBER ,
4429 p_into_lpn IN VARCHAR2)
4430 IS
4431 BEGIN
4432
4433 -- If an LPN does not have the pregenerated LPN context and matches
4434 -- the LPN context of the source LPN, it must either be empty or
4435 -- be an LPN loaded for putaway by the same user/employee.
4436 OPEN x_lpn_lov FOR
4437 SELECT wlpn.license_plate_number,
4438 wlpn.lpn_id,
4439 wlpn.inventory_item_id,
4440 wlpn.organization_id,
4441 wlpn.revision,
4442 wlpn.lot_number,
4443 wlpn.serial_number,
4444 wlpn.subinventory_code,
4445 wlpn.locator_id,
4446 wlpn.parent_lpn_id,
4447 NVL(wlpn.sealed_status, 2),
4448 wlpn.gross_weight_uom_code,
4449 NVL(wlpn.gross_weight, 0),
4450 wlpn.content_volume_uom_code,
4451 NVL(wlpn.content_volume, 0)
4452 FROM wms_license_plate_numbers wlpn
4453 WHERE wlpn.organization_id = p_organization_id
4454 AND wlpn.lpn_id <> p_lpn_id
4455 AND (wlpn.lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_PREGENERATED
4456 OR (wlpn.lpn_context = p_lpn_context
4457 AND ( (NOT EXISTS (SELECT 'LPN_HAS_MATERIAL'
4458 FROM wms_lpn_contents
4459 WHERE parent_lpn_id IN (SELECT wlpn1.lpn_id
4460 FROM
4461 wms_license_plate_numbers wlpn1
4462 START WITH
4463 wlpn1.lpn_id =
4464 wlpn.outermost_lpn_id
4465 CONNECT BY PRIOR
4466 wlpn1.lpn_id = wlpn1.parent_lpn_id)))
4467 OR
4468 (EXISTS (SELECT 'LOADED_BY_SAME_USER'
4469 FROM mtl_material_transactions_temp mmtt,
4470 wms_dispatched_tasks wdt
4471 WHERE mmtt.organization_id = p_organization_id
4472 AND mmtt.transaction_temp_id = wdt.transaction_temp_id
4473 AND wdt.organization_id = p_organization_id
4474 AND wdt.task_type = 2
4475 AND wdt.status = 4
4476 AND wdt.person_id = p_employee_id
4477 AND mmtt.lpn_id IN (SELECT lpn_id
4478 FROM wms_license_plate_numbers
4479 START WITH lpn_id = wlpn.outermost_lpn_id
4480 CONNECT BY PRIOR lpn_id = parent_lpn_id
4481 )
4482 )
4483 )
4484 )
4485 )
4486 )
4487 AND wlpn.license_plate_number LIKE (p_into_lpn)
4488 AND inv_material_status_grp.is_status_applicable('TRUE',
4489 NULL,
4490 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
4491 NULL,
4492 NULL,
4493 p_organization_id,
4494 NULL,
4495 wlpn.subinventory_code,
4496 wlpn.locator_id,
4497 NULL,
4498 NULL,
4499 'Z') = 'Y'
4500 AND inv_material_status_grp.is_status_applicable('TRUE',
4501 NULL,
4502 INV_GLOBALS.G_TYPE_CONTAINER_PACK,
4503 NULL,
4504 NULL,
4505 p_organization_id,
4506 NULL,
4507 wlpn.subinventory_code,
4508 wlpn.locator_id,
4509 NULL,
4510 NULL,
4511 'L') = 'Y'
4512 ORDER BY wlpn.license_plate_number;
4513
4514 END get_item_load_lpn_lov;
4515
4516 PROCEDURE get_from_gtmp_lov
4517 (x_lpn_lov OUT NOCOPY t_genref ,
4518 p_organization_id IN NUMBER ,
4519 p_drop_type IN VARCHAR2 ,
4520 p_lpn_name IN VARCHAR2
4521 )
4522 IS
4523
4524
4525 BEGIN
4526
4527 OPEN x_lpn_lov FOR
4528 SELECT DISTINCT
4529 wlpn.license_plate_number, wlpn.lpn_id,
4530 NVL (wlpn.inventory_item_id, 0),
4531 NVL (wlpn.organization_id, 0),
4532 wlpn.revision,
4533 wlpn.lot_number,
4534 wlpn.serial_number,
4535 wlpn.subinventory_code,
4536 NVL (wlpn.locator_id, 0),
4537 NVL (wlpn.parent_lpn_id, 0),
4538 NVL (wlpn.sealed_status, 2),
4539 wlpn.gross_weight_uom_code,
4540 NVL (wlpn.gross_weight, 0),
4541 wlpn.content_volume_uom_code,
4542 NVL (wlpn.content_volume, 0),
4543 milk.concatenated_segments,
4544 wlpn.lpn_context
4545 FROM wms_license_plate_numbers wlpn,
4546 mtl_item_locations_kfv milk,
4547 wms_putaway_group_tasks_gtmp wpgt
4548 WHERE wlpn.organization_id = TO_NUMBER (p_organization_id)
4549 AND wlpn.organization_id = milk.organization_id(+)
4550 AND wlpn.locator_id = milk.inventory_location_id(+)
4551 AND wlpn.lpn_id = wpgt.lpn_id
4552 AND wpgt.row_type = 'Group Task'
4553 AND drop_type = p_drop_type
4554 AND wlpn.license_plate_number LIKE p_lpn_name
4555 ORDER BY wlpn.license_plate_number;
4556 END get_from_gtmp_lov;
4557
4558 END WMS_LPN_LOVS;