1 PACKAGE BODY inv_ui_item_sub_loc_lovs AS
2 /* $Header: INVITPSB.pls 120.20 2008/05/08 10:54:44 aambulka ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_UI_ITEM_SUB_LOC_LOVS';
5
6 PROCEDURE debug(p_msg VARCHAR2) IS
7
8 BEGIN
9
10 inv_mobile_helper_functions.tracelog(
11 p_err_msg => p_msg,
12 p_module => g_pkg_name,
13 p_level => 4
14 );
15
16 END debug;
17
18 PROCEDURE update_locator(p_sub_code IN VARCHAR2, p_org_id IN NUMBER, p_locator_id IN NUMBER) IS
19 l_return_status VARCHAR2(10);
20 l_msg_count NUMBER;
21 l_msg_data VARCHAR2(20);
22 l_wms_org BOOLEAN;
23 l_sub_type NUMBER;
24 l_locator_status NUMBER;
25 l_loc_type NUMBER;
26 l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
27 l_picking_order NUMBER;
28 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
29 BEGIN
30 IF (l_debug = 1) THEN
31 DEBUG('In the update locator');
32 END IF;
33 --To check if it is wms org
34 /*Passing p_organization_id in below call as null as passing p_org_id was checking whether organization is wms
35 enabled or not and because of the updation logic was not working properly for non wms enabled organizations.
36 Here we need to check whether wms is installed or not.Bug # 6936019 */
37 --l_wms_org := wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => p_org_id);
38
39 l_wms_org := wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => NULL);
40
41 IF l_return_status <> fnd_api.g_ret_sts_success THEN
42 IF (l_debug = 1) THEN
43 DEBUG('Check if WMS installed');
44 END IF;
45 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
46
47 END IF;
48
49 --If it is a WMS org get the default status ,picking order and set the locator type and insert status history
50 IF l_wms_org THEN
51
52 -- get the sub type and based on it set the loc_type
53 SELECT nvl(subinventory_type,1)
54 INTO l_sub_type
55 FROM mtl_secondary_inventories
56 WHERE organization_id = p_org_id
57 AND secondary_inventory_name = p_sub_code;
58
59 IF l_sub_type = 2 THEN
60 -- Receiving sub hence set the locator status to receiving
61 l_loc_type := 6;
62 IF (l_debug = 1) THEN
63 DEBUG('Receiving sub hence set the locator status to receiving = ' || l_loc_type);
64 END IF;
65 ELSE
66 -- Storage sub hence set the locator status to storage
67 l_loc_type := 3;
68 IF (l_debug = 1) THEN
69 DEBUG('Storage sub hence set the locator status to storage = ' || l_loc_type);
70 END IF;
71 END IF; -- sub type check
72
73
74 l_locator_status := inv_material_status_pkg.get_default_locator_status(p_org_id, p_sub_code);
75 --l_loc_type := 3;
76 l_status_rec.organization_id := p_org_id;
77 l_status_rec.inventory_item_id := NULL;
78 l_status_rec.lot_number := NULL;
79 l_status_rec.serial_number := NULL;
80 l_status_rec.update_method := inv_material_status_pub.g_update_method_manual;
81 l_status_rec.status_id := l_locator_status;
82 l_status_rec.zone_code := p_sub_code;
83 l_status_rec.locator_id := p_locator_id;
84 l_status_rec.creation_date := SYSDATE;
85 l_status_rec.created_by := fnd_global.user_id;
86 l_status_rec.last_update_date := SYSDATE;
87 l_status_rec.last_update_login := fnd_global.user_id;
88 l_status_rec.initial_status_flag := 'Y';
89 l_status_rec.from_mobile_apps_flag := 'Y';
90 IF (l_debug = 1) THEN
91 DEBUG('befire inserting status history');
92 END IF;
93 inv_material_status_pkg.insert_status_history(l_status_rec);
94 IF (l_debug = 1) THEN
95 DEBUG('Status history inserted');
96 END IF;
97 END IF;
98
99 --Default the picking order from the org parameters
100 SELECT default_locator_order_value
101 INTO l_picking_order
102 FROM mtl_parameters
103 WHERE organization_id = p_org_id;
104
105 UPDATE mtl_item_locations
106 SET subinventory_code = p_sub_code
107 , status_id = l_locator_status
108 , inventory_location_type = l_loc_type
109 , picking_order = l_picking_order
110 WHERE organization_id = p_org_id
111 AND inventory_location_id = p_locator_id;
112 END update_locator;
113
114
115 /** Changes done for Patchset J project -Receiving Locator Support and Item Based Putaway
116 * Added two new parameters - p_location_id and p_lpn_context
117 * p_location_id will be passed from Receiving Pages in which case we will show only
118 * Receiving Type subinventories.
119 * lpn context will be passed from Putaway page where there are two requirements -
120 * i. if lpn context = 3 then show all storage as well as receiving type subs
121 * ii. if lpn context = 1 then show only inventory type subinventories
122 * Added one more new parameter - p_putaway_code
123 * PutawayDropPage will use this parameter to indicate what type of
124 * subinventory should be shown:
125 * 1 - show only storage sub (with no restrictions)
126 * 2 - show only receiving sub
127 * 3 - show only lpn-controlled and reservable storage sub (for SO xdock)
128 * 4 - show only non-lpn-controlled and non-reservable storage sub (for
129 * wip xdock)
130 * NULL - show both storage sub and rcv sub, just like how it works before
131 */
132 --- Obsoleted
133 PROCEDURE get_sub_lov_rcv(
134 x_sub OUT NOCOPY t_genref
135 , p_organization_id IN NUMBER
136 , p_item_id IN NUMBER
137 , p_sub IN VARCHAR2
138 , p_restrict_subinventories_code IN NUMBER
139 , p_transaction_type_id IN NUMBER
140 , p_wms_installed IN VARCHAR2
141 , p_location_id IN NUMBER --RCVLOCATORSSUPPORT
142 , p_lpn_context IN NUMBER
143 , p_putaway_code IN NUMBER
144 ) IS --RCVLOCATORSSUPPORT
145 l_debug NUMBER;
146 l_procedure_name VARCHAR2(30);
147 BEGIN
148
149 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
150 l_procedure_name := 'GET_SUB_LOV_RCV';
151
152 IF l_debug > 0 THEN
153 debug(l_procedure_name);
154 debug('p_lpn_context => ' || p_lpn_context);
155 debug('p_location_id => ' || p_location_id);
156 debug('p_putaway_code => ' || p_putaway_code);
157 END IF;
158 IF p_lpn_context = 3 THEN
159
160 IF (p_item_id IS NULL OR p_restrict_subinventories_code <> 1 ) THEN
161 OPEN x_sub FOR
162 SELECT msub.secondary_inventory_name
163 , NVL(msub.locator_type, 1)
164 , msub.description
165 , msub.asset_inventory
166 , msub.lpn_controlled_flag
167 , nvl(msub.subinventory_type, 1)
168 , msub.reservable_type
169 , msub.enable_locator_alias
170 FROM mtl_secondary_inventories msub
171 WHERE msub.organization_id = p_organization_id
172 AND Nvl(subinventory_type,1) = Decode(p_putaway_code,
173 2, --Don't show any storage sub
174 -1,--if system suggested a RCV sub
175 1)
176 AND Nvl(lpn_controlled_flag,-1) = Decode(p_putaway_code,
177 3,--For SO xdock, sub must be
178 1,--LPN controlled
179 Decode(p_putaway_code,
180 4,--For WIP xdock, sub must
181 2,--NOT be LPN controlled
182 Nvl(lpn_controlled_flag,-1)))
183 AND reservable_type = Decode(p_putaway_code,
184 3,--For SO xdock, sub must be
185 1,--reservable
186 Decode(p_putaway_code,
187 4,--For WIP xdock, sub must
188 2,--not be reservable
189 reservable_type))
190 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
191 AND msub.secondary_inventory_name LIKE (p_sub)
192 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
193 UNION ALL
194 SELECT msub.secondary_inventory_name
195 , NVL(msub.locator_type, 1)
196 , msub.description
197 , msub.asset_inventory
198 , lpn_controlled_flag
199 , Nvl(subinventory_type, 1)
200 , reservable_type
201 , enable_locator_alias
202 FROM mtl_secondary_inventories msub
203 WHERE organization_id = p_organization_id
204 AND Nvl(subinventory_type,1) = Decode(p_putaway_code,
205 2,--Only show rcv sub if the
206 2,--system has suggested a rcv sub
207 Decode(p_putaway_code,
208 NULL,
209 2,
210 -1))
211 AND msub.secondary_inventory_name LIKE (p_sub)
212 AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
213 disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
214 ORDER BY 1;
215
216 ELSE -- It is a restricted item,
217 OPEN x_sub FOR
218 SELECT msub.secondary_inventory_name
219 , NVL(msub.locator_type, 1)
220 , msub.description
221 , msub.asset_inventory
222 , lpn_controlled_flag
223 , Nvl(subinventory_type, 1)
224 , reservable_type
225 , enable_locator_alias
226 FROM mtl_secondary_inventories msub
227 WHERE msub.organization_id = p_organization_id
228 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
229 AND msub.secondary_inventory_name LIKE (p_sub)
230 AND Nvl(msub.subinventory_type,1) = Decode(p_putaway_code,
231 2, --Don't show any storage sub
232 -1,--if system suggested a RCV sub
233 1)
234 AND Nvl(msub.lpn_controlled_flag,-1) = Decode(p_putaway_code,
235 3,--For SO xdock, sub must be
236 1,--LPN controlled
237 Decode(p_putaway_code,
238 4,--For WIP xdock, sub must
239 2,--NOT be LPN controlled
240 Nvl(msub.lpn_controlled_flag,-1)))
241 AND msub.reservable_type = Decode(p_putaway_code,
242 3,--For SO xdock, sub must be
243 1,--reservable
244 Decode(p_putaway_code,
245 4,--For WIP xdock, sub must
246 2,--not be reservable
247 msub.reservable_type))
248
249 AND EXISTS( SELECT NULL
250 FROM mtl_item_sub_inventories mis
251 WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
252 AND mis.inventory_item_id = p_item_id
253 AND mis.secondary_inventory = msub.secondary_inventory_name)
254 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
255 UNION ALL
256 SELECT msub.secondary_inventory_name
257 , NVL(msub.locator_type, 1)
258 , msub.description
259 , msub.asset_inventory
260 , lpn_controlled_flag
261 , Nvl(subinventory_type, 1)
262 , reservable_type
263 , enable_locator_alias
264 FROM mtl_secondary_inventories msub
265 WHERE msub.organization_id = p_organization_id
266 AND Nvl(msub.subinventory_type,1) = Decode(p_putaway_code,
267 2,--Only show rcv sub if the
268 2,--system has suggested a rcv sub
269 Decode(p_putaway_code,
270 NULL,
271 2,
272 -1))
273 AND msub.secondary_inventory_name LIKE (p_sub)
274 AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
275 disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
276 ORDER BY 1;
277
278 END IF;
279 ELSIF (p_lpn_context IN (1,2) OR p_lpn_context IS NULL) AND p_location_id IS NULL THEN
280 IF (p_item_id IS NULL
281 OR p_restrict_subinventories_code <> 1
282 ) THEN
283 OPEN x_sub FOR
284 SELECT msub.secondary_inventory_name
285 , NVL(msub.locator_type, 1)
286 , msub.description
287 , msub.asset_inventory
288 , lpn_controlled_flag
289 , Nvl(subinventory_type, 1)
290 , reservable_type
291 , enable_locator_alias
292 FROM mtl_secondary_inventories msub
293 WHERE msub.organization_id = p_organization_id
294 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
295 AND msub.secondary_inventory_name LIKE (p_sub)
296 AND Nvl(msub.lpn_controlled_flag,-1) = Decode(p_lpn_context,
297 2,
298 Decode(p_putaway_code,
299 3,
300 1,
301 Decode(p_putaway_code,
302 4,
303 2,
304 Nvl(msub.lpn_controlled_flag,-1))
305 ),
306 Nvl(msub.lpn_controlled_flag,-1)
307 )
308 AND msub.reservable_type = Decode(p_lpn_context,
309 2,
310 Decode(p_putaway_code,
311 3,
312 1,
313 Decode(p_putaway_code,
314 4,
315 2,
316 msub.reservable_type)
317 ),
318 msub.reservable_type
319 )
320 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
321 ORDER BY UPPER(msub.secondary_inventory_name);
322 ELSE
323 -- It is a restricted item,
324 OPEN x_sub FOR
325 SELECT msub.secondary_inventory_name
326 , NVL(msub.locator_type, 1)
327 , msub.description
328 , msub.asset_inventory
329 , lpn_controlled_flag
330 , Nvl(subinventory_type, 1)
331 , reservable_type
332 , enable_locator_alias
333 FROM mtl_secondary_inventories msub
334 WHERE msub.organization_id = p_organization_id
335 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
336 AND msub.secondary_inventory_name LIKE (p_sub)
337 AND Nvl(msub.lpn_controlled_flag,-1) = Decode(p_lpn_context,
338 2,
339 Decode(p_putaway_code,
340 3,-- SO XDOCK
341 1,--Must be LPN controlled
342 Decode(p_putaway_code,
343 4,--WIP XDOCK
344 2,--Must be non LPN controlled
345 Nvl(msub.lpn_controlled_flag,-1))
346 ),
347 Nvl(msub.lpn_controlled_flag,-1)
348 )
349 AND msub.reservable_type = Decode(p_lpn_context,
350 2,
351 Decode(p_putaway_code,
352 3,--SO XDOCK
353 1,--Must be reservable
354 Decode(p_putaway_code,
355 4,--WIP XDOCK
356 2,--Must be non reservable
357 msub.reservable_type)
358 ),
359 msub.reservable_type
360 )
361 AND EXISTS( SELECT NULL
362 FROM mtl_item_sub_inventories mis
363 WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
364 AND mis.inventory_item_id = p_item_id
365 AND mis.secondary_inventory = msub.secondary_inventory_name)
366 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
367 ORDER BY UPPER(msub.secondary_inventory_name);
368 END IF;
369 ELSIF p_location_id IS NOT NULL THEN
370 -- For Putaway, p_location_id will always be null, so no change
371 -- is needed below
372 OPEN x_sub FOR
373 SELECT msub.secondary_inventory_name
374 , NVL(msub.locator_type, 1)
375 , msub.description
376 , msub.asset_inventory
377 , lpn_controlled_flag
378 , Nvl(subinventory_type, 1)
379 , reservable_type
380 , enable_locator_alias
381 FROM mtl_secondary_inventories msub
382 WHERE organization_id = p_organization_id
383 AND subinventory_type = 2
384 AND msub.secondary_inventory_name LIKE (p_sub)
385 AND location_id = p_location_id
386 AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
387 disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
388 ORDER BY UPPER(msub.secondary_inventory_name);
389 END IF;
390 END get_sub_lov_rcv;
391
392 -- Name: GET_MO_FROMSUB_LOV
393 --
394 -- Input parameters:
395 -- p_organization_id OrgId
396 -- p_MOheader_id MoveOrder Header Id
397 -- p_subinv_code SubInventory Code
398 --
399 -- Output parameters:
400 -- x_fromsub_lov returns LOV rows as reference cursor
401 --
402 -- Functions: This API returns Transaction Reasons
403 --
404 ---- Obsolete
405 PROCEDURE get_mo_fromsub_lov(x_fromsub_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_subinv_code IN VARCHAR) IS
406
407 BEGIN
408
409 OPEN x_fromsub_lov FOR
410 SELECT secondary_inventory_name
411 , NVL(locator_type, 1)
412 , description
413 , asset_inventory
414 , lpn_controlled_flag
415 , enable_locator_alias
416 FROM mtl_secondary_inventories
417 WHERE organization_id = p_organization_id
418 AND secondary_inventory_name IN (
419 SELECT from_subinventory_code
420 FROM mtl_txn_request_lines
421 WHERE header_id = p_moheader_id
422 )
423 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
424 AND secondary_inventory_name LIKE (p_subinv_code);
425
426 END get_mo_fromsub_lov;
427
428 -- Name: GET_MO_TOSUB_LOV
429 --
430 -- Input parameters:
431 -- p_organization_id OrgId
432 -- p_MOheader_id MoveOrder Header Id
433 -- p_subinv_code SubInventory Code
434 --
435 -- Output parameters:
436 -- x_tosub_lov returns LOV rows as reference cursor
437 --
438 -- Functions: This API returns Transaction Reasons
439 --
440 --- Obsolete
441 PROCEDURE get_mo_tosub_lov(x_tosub_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_subinv_code IN VARCHAR) IS
442 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
443 BEGIN
444 OPEN x_tosub_lov FOR
445 SELECT secondary_inventory_name
446 , NVL(locator_type, 1)
447 , description
448 , asset_inventory
449 , lpn_controlled_flag
450 , enable_locator_alias
451 FROM mtl_secondary_inventories
452 WHERE organization_id = p_organization_id
453 AND secondary_inventory_name IN (SELECT to_subinventory_code
454 FROM mtl_txn_request_lines
455 WHERE header_id = p_moheader_id)
456 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
457 AND secondary_inventory_name LIKE (p_subinv_code);
458 END;
459
460 -- Name: GET_LOC_LOV
461 --
462 -- Input parameters:
463 -- p_Organization_Id which restricts LOV SQL to current org
464 -- p_Concatenated_Segments which restricts LOV SQL to the user input text
465 -- e.g. 1-1%
466 -- p_Inventory_item_id restrict to those item restricted locators
467 -- p_Subinventory_Code restrict to this sub
468 -- p_restrict_Locators_code item restricted locator flag
469 --
470 -- Output parameters:
471 -- x_sub returns LOV rows as reference cursor
472 --
473 -- Functions: This API is to returns locator for given org and sub
474 -- It returns different LOVs for item-restricted locator
475 --
476 PROCEDURE get_loc_lov(
477 x_locators OUT NOCOPY t_genref
478 , p_organization_id IN NUMBER
479 , p_subinventory_code IN VARCHAR2
480 , p_restrict_locators_code IN NUMBER
481 , p_inventory_item_id IN NUMBER
482 , p_concatenated_segments IN VARCHAR2
483 , p_transaction_type_id IN NUMBER
484 , p_wms_installed IN VARCHAR2
485 ) IS
486 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
487 BEGIN
488 IF (l_debug = 1) THEN
489 inv_log_util.TRACE('Im in GET_pick_LOV', 'WMS_LPN_LOVs', 9);
490 END IF;
491
492 IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
493 OPEN x_locators FOR
494 SELECT a.inventory_location_id
495 --, a.concatenated_segments----Bug4398337:Commented this line and added below line
496 , a.locator_segments concatenated_segments
497 , a.description
498 FROM wms_item_locations_kfv a, mtl_secondary_locators b
499 WHERE b.organization_id = p_organization_id
500 AND b.inventory_item_id = p_inventory_item_id
501 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
502 AND b.subinventory_code = p_subinventory_code
503 AND a.inventory_location_id = b.secondary_locator
504 AND a.concatenated_segments LIKE (p_concatenated_segments)
505 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
506 ORDER BY a.concatenated_segments;
507 ELSE --Locators not restricted
508 OPEN x_locators FOR
509 SELECT inventory_location_id
510 -- , concatenated_segments--Bug4398337:Commented this line and added below line
511 , locator_segments concatenated_segments
512 , description
513 FROM wms_item_locations_kfv
514 WHERE organization_id = p_organization_id
515 AND subinventory_code = p_subinventory_code
516 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
517 AND concatenated_segments LIKE (p_concatenated_segments)
518 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
519 ORDER BY concatenated_segments;
520 END IF;
521 END get_loc_lov;
522
523 -- Name: GET_LOC_LOV_PJM
524 --
525 -- Input parameters:
526 -- p_Organization_Id which restricts LOV SQL to current org
527 -- p_Concatenated_Segments which restricts LOV SQL to the user input text
528 -- e.g. 1-1%
529 -- p_Inventory_item_id restrict to those item restricted locators
530 -- p_Subinventory_Code restrict to this sub
531 -- p_restrict_Locators_code item restricted locator flag
532 --
533 -- Output parameters:
534 -- x_sub returns LOV rows as reference cursor and the concatenated segments
535 -- returned doesnt contain SEGMENT 19 and 20.
536 --
537 -- Functions: This API is to returns locator for given org and sub.
538 -- The concatenated segments returned doesnt contain SEGMENT 19 and 20.
539 -- It returns different LOVs for item-restricted locator
540 --
541
542 PROCEDURE get_loc_lov_pjm(
543 x_locators OUT NOCOPY t_genref
544 , p_organization_id IN NUMBER
545 , p_subinventory_code IN VARCHAR2
546 , p_restrict_locators_code IN NUMBER
547 , p_inventory_item_id IN NUMBER
548 , p_concatenated_segments IN VARCHAR2
549 , p_transaction_type_id IN NUMBER
550 , p_wms_installed IN VARCHAR2
551 ) IS
552 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
553 BEGIN
554 IF (l_debug = 1) THEN
555 inv_trx_util_pub.TRACE('Im in GET_LOC_LOV_PJM', 'WMS_LPN_LOVs', 9);
556 END IF;
557
558 IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
559 OPEN x_locators FOR
560 SELECT a.inventory_location_id
561 --, a.concatenated_segments concatenated_segments--Bug4398337:Commented this line and added below line
562 , a.locator_segments concatenated_segments
563 , a.description
564 FROM wms_item_locations_kfv a, mtl_secondary_locators b
565 WHERE b.organization_id = p_organization_id
566 AND b.inventory_item_id = p_inventory_item_id
567 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
568 AND b.subinventory_code = p_subinventory_code
569 AND a.inventory_location_id = b.secondary_locator
570 AND a.project_id IS NULL
571 AND a.task_id IS NULL
572 AND a.concatenated_segments LIKE (p_concatenated_segments)
573 AND NVL(a.physical_location_id, a.inventory_location_id) = a.inventory_location_id
574 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
575 ORDER BY 2;
576 ELSE --Locators not restricted
577 OPEN x_locators FOR
578 SELECT inventory_location_id
579 --, concatenated_segments concatenated_segments--Bug4398337:Commented this line and added below line
580 , locator_segments concatenated_segments
581 , description
582 FROM wms_item_locations_kfv
583 WHERE organization_id = p_organization_id
584 AND subinventory_code = p_subinventory_code
585 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
586 AND concatenated_segments LIKE (p_concatenated_segments)
587 AND project_id IS NULL
588 AND task_id IS NULL
589 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
590 ORDER BY 2;
591 END IF;
592 END get_loc_lov_pjm;
593
594 ------------------------------------------------
595 -- GET_INQ_LOC_LOV for inquiry form.
596 -------------------------------------------------
597 PROCEDURE get_inq_loc_lov(
598 x_locators OUT NOCOPY t_genref
599 , p_organization_id IN NUMBER
600 , p_subinventory_code IN VARCHAR2
601 , p_restrict_locators_code IN NUMBER
602 , p_inventory_item_id IN NUMBER
603 , p_concatenated_segments IN VARCHAR2
604 , p_project_id IN NUMBER := NULL
605 , p_task_id IN NUMBER := NULL
606 ) IS
607 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
608 l_ispjm_org VARCHAR2(1);
609 BEGIN
610 BEGIN
611 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
612 INTO l_ispjm_org
613 FROM pjm_org_parameters
614 WHERE organization_id=p_organization_id;
615 EXCEPTION
616 WHEN NO_DATA_FOUND THEN
617 l_ispjm_org:='N';
618 END;
619 IF l_ispjm_org='N'THEN
620 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
621 OPEN x_Locators FOR
622 SELECT a.inventory_location_id,
623 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
624 a.locator_segments locsegs,
625 a.description
626 FROM wms_item_locations_kfv a,
627 mtl_secondary_locators b
628 WHERE b.organization_id = p_Organization_Id
629 AND b.inventory_item_id = p_Inventory_Item_Id
630 AND b.subinventory_code = p_Subinventory_Code
631 AND a.inventory_location_id = b.secondary_locator
632 and nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
633 AND a.concatenated_segments LIKE (p_concatenated_segments )
634 /* BUG#28101405: To show only common locators in the LOV */
635 ORDER BY 2;
636
637 ELSE --Locators not restricted
638 --bug#3440453 Remove the NVL on organization_id if user passes it.
639 IF p_organization_id IS NULL THEN
640 OPEN x_Locators FOR
641 SELECT inventory_location_id,
642 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
643 locator_segments locsegs,
644 description
645 FROM wms_item_locations_kfv
646 WHERE organization_id = Nvl(p_organization_id, organization_id)
647 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
648 and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
649 AND concatenated_segments LIKE (p_concatenated_segments)
650 ORDER BY 2;
651 ELSE -- Organization_id is not null
652 OPEN x_Locators FOR
653 SELECT inventory_location_id,
654 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
655 locator_segments locsegs,
656 description
657 FROM wms_item_locations_kfv
658 WHERE organization_id = p_organization_id
659 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
660 and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
661 AND concatenated_segments LIKE (p_concatenated_segments)
662 ORDER BY 2;
663 END IF;
664 END IF;
665 ELSE /*PJM Org*/
666 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
667 OPEN x_Locators FOR
668 SELECT a.inventory_location_id,
669 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
670 a.locator_segments locsegs,
671 a.description
672 FROM wms_item_locations_kfv a,
673 mtl_secondary_locators b
674 WHERE b.organization_id = p_Organization_Id
675 AND b.inventory_item_id = p_Inventory_Item_Id
676 AND b.subinventory_code = p_Subinventory_Code
677 AND a.inventory_location_id = b.secondary_locator
678 and nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
679 AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
680 AND a.concatenated_segments LIKE (p_concatenated_segments )
681 ORDER BY 2;
682 ELSE --Locators not restricted
683 --bug#3440453 Remove the NVL on organization_id if user passes it.
684 IF p_organization_id IS NULL THEN
685 OPEN x_Locators FOR
686 SELECT inventory_location_id,
687 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
688 locator_segments locsegs,
689 description
690 FROM wms_item_locations_kfv
691 WHERE organization_id = Nvl(p_organization_id, organization_id)
692 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
693 and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
694 AND concatenated_segments LIKE (p_concatenated_segments )
695 AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
696 ORDER BY 2;
697 ELSE -- Organization_id is not null
698 OPEN x_Locators FOR
699 SELECT inventory_location_id,
700 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
701 locator_segments locsegs,
702 description
703 FROM wms_item_locations_kfv
704 WHERE organization_id = p_organization_id
705 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
706 and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
707 AND concatenated_segments LIKE (p_concatenated_segments )
708 AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
709 ORDER BY 2;
710 END IF;
711 END IF;
712 END IF;
713 END get_inq_loc_lov;
714 PROCEDURE get_inq_loc_lov(
715 x_locators OUT NOCOPY t_genref
716 , p_organization_id IN NUMBER
717 , p_subinventory_code IN VARCHAR2
718 , p_restrict_locators_code IN NUMBER
719 , p_inventory_item_id IN NUMBER
720 , p_concatenated_segments IN VARCHAR2
721 , p_project_id IN NUMBER := NULL
722 , p_task_id IN NUMBER := NULL
723 , p_alias IN VARCHAR2
724 ) IS
725 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
726 l_ispjm_org VARCHAR2(1);
727 BEGIN
728 IF p_alias IS NULL THEN
729 get_inq_loc_lov(
730 x_locators => x_locators
731 , p_organization_id => p_organization_id
732 , p_subinventory_code => p_subinventory_code
733 , p_restrict_locators_code => p_restrict_locators_code
734 , p_inventory_item_id => p_inventory_item_id
735 , p_concatenated_segments => p_concatenated_segments
736 , p_project_id => p_project_id
737 , p_task_id => p_task_id
738 );
739 RETURN;
740 END IF;
741
742 BEGIN
743 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
744 INTO l_ispjm_org
745 FROM pjm_org_parameters
746 WHERE organization_id=p_organization_id;
747 EXCEPTION
748 WHEN NO_DATA_FOUND THEN
749 l_ispjm_org:='N';
750 END;
751 IF l_ispjm_org='N'THEN
752 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
753 OPEN x_Locators FOR
754 SELECT a.inventory_location_id,
755 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
756 a.locator_segments locsegs,
757 a.description
758 FROM wms_item_locations_kfv a,
759 mtl_secondary_locators b
760 WHERE b.organization_id = p_Organization_Id
761 AND b.inventory_item_id = p_Inventory_Item_Id
762 AND b.subinventory_code = p_Subinventory_Code
763 AND a.inventory_location_id = b.secondary_locator
764 and nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
765 AND a.alias = p_alias
766 /* BUG#28101405: To show only common locators in the LOV */
767 ORDER BY 2;
768
769 ELSE --Locators not restricted
770 --bug#3440453 Remove the NVL on organization_id if user passes it.
771 IF p_organization_id IS NULL THEN
772 OPEN x_Locators FOR
773 SELECT inventory_location_id,
774 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
775 locator_segments locsegs,
776 description
777 FROM wms_item_locations_kfv
778 WHERE organization_id = Nvl(p_organization_id, organization_id)
779 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
780 and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
781 AND alias = p_alias
782 ORDER BY 2;
783 ELSE -- Organization_id is not null
784 OPEN x_Locators FOR
785 SELECT inventory_location_id,
786 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
787 locator_segments locsegs,
788 description
789 FROM wms_item_locations_kfv
790 WHERE organization_id = p_organization_id
791 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
792 and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
793 AND alias = p_alias
794 ORDER BY 2;
795 END IF;
796 END IF;
797 ELSE /*PJM Org*/
798 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
799 OPEN x_Locators FOR
800 SELECT a.inventory_location_id,
801 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
802 a.locator_segments locsegs,
803 a.description
804 FROM wms_item_locations_kfv a,
805 mtl_secondary_locators b
806 WHERE b.organization_id = p_Organization_Id
807 AND b.inventory_item_id = p_Inventory_Item_Id
808 AND b.subinventory_code = p_Subinventory_Code
809 AND a.inventory_location_id = b.secondary_locator
810 and nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
811 AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
812 AND a.alias = p_alias
813 ORDER BY 2;
814 ELSE --Locators not restricted
815 --bug#3440453 Remove the NVL on organization_id if user passes it.
816 IF p_organization_id IS NULL THEN
817 OPEN x_Locators FOR
818 SELECT inventory_location_id,
819 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
820 locator_segments locsegs,
821 description
822 FROM wms_item_locations_kfv
823 WHERE organization_id = Nvl(p_organization_id, organization_id)
824 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
825 and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
826 AND alias = p_alias
827 AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
828 ORDER BY 2;
829 ELSE -- Organization_id is not null
830 OPEN x_Locators FOR
831 SELECT inventory_location_id,
832 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
833 locator_segments locsegs,
834 description
835 FROM wms_item_locations_kfv
836 WHERE organization_id = p_organization_id
837 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
838 and nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate) /* 2915024 */
839 AND alias = p_alias
840 AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
841 ORDER BY 2;
842 END IF;
843 END IF;
844 END IF;
845 END get_inq_loc_lov;
846
847 -- This returns the locator id for an existing locator and if
848 -- it does not exist then it creates a new one.
849 PROCEDURE get_dynamic_locator(x_location_id OUT NOCOPY NUMBER, x_description OUT NOCOPY VARCHAR2, x_result OUT NOCOPY VARCHAR2, x_exist_or_create OUT NOCOPY VARCHAR2, p_org_id IN NUMBER, p_sub_code IN VARCHAR2, p_concat_segs IN VARCHAR2) IS
850 l_keystat_val BOOLEAN;
851 l_sub_default_status NUMBER;
852 l_validity_check VARCHAR2(10);
853 l_wms_org BOOLEAN;
854 l_loc_type NUMBER;
855 l_return_status VARCHAR2(10);
856 l_msg_count NUMBER;
857 l_msg_data VARCHAR2(20);
858 l_label_status VARCHAR2(20);
859 l_status_rec inv_material_status_pub.mtl_status_update_rec_type; -- bug# 1695432
860 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
861 BEGIN
862 x_result := 'S';
863 l_validity_check := 'passed';
864
865 BEGIN
866 SELECT inventory_location_id
867 , description
868 INTO x_location_id
869 , x_description
870 FROM wms_item_locations_kfv
871 WHERE organization_id = p_org_id
872 AND subinventory_code = p_sub_code
873 AND concatenated_segments = p_concat_segs
874 AND ROWNUM < 2;
875
876 x_exist_or_create := 'EXISTS';
877 RETURN;
878 EXCEPTION
879 WHEN NO_DATA_FOUND THEN
880 l_keystat_val :=
881 fnd_flex_keyval.validate_segs(operation => 'CREATE_COMB_NO_AT', --changed for bug1881366
882 appl_short_name => 'INV', key_flex_code => 'MTLL', structure_number => 101, concat_segments => p_concat_segs, values_or_ids => 'V', data_set => p_org_id);
883
884 IF (l_keystat_val = FALSE) THEN
885 x_result := 'E';
886 x_exist_or_create := '';
887 RETURN;
888 ELSE
889 x_location_id := fnd_flex_keyval.combination_id;
890 x_exist_or_create := 'EXISTS';
891
892 IF fnd_flex_keyval.new_combination THEN
893 x_exist_or_create := 'CREATE';
894
895 IF p_sub_code IS NOT NULL THEN
896 BEGIN
897 --- check validity
898 SELECT 'failed'
899 INTO l_validity_check
900 FROM DUAL
901 WHERE EXISTS( SELECT subinventory_code
902 FROM wms_item_locations_kfv
903 WHERE concatenated_segments = p_concat_segs
904 AND p_sub_code <> subinventory_code
905 AND organization_id = p_org_id);
906 EXCEPTION
907 WHEN NO_DATA_FOUND THEN
908 NULL;
909 END;
910
911 IF l_validity_check = 'failed' THEN
912 x_result := 'E';
913 x_exist_or_create := '';
914 RETURN;
915 END IF;
916
917 SELECT NVL(default_loc_status_id, 1)
918 INTO l_sub_default_status
919 FROM mtl_secondary_inventories
920 WHERE organization_id = p_org_id
921 AND secondary_inventory_name = p_sub_code;
922
923 l_wms_org := wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => p_org_id);
924
925 IF l_return_status = 'S' THEN
926 IF l_wms_org THEN
927 l_loc_type := 3;
928 ELSE
929 l_loc_type := NULL;
930 END IF;
931 ELSE
932 x_result := 'E';
933 x_exist_or_create := '';
934 RETURN;
935 END IF;
936
937 UPDATE mtl_item_locations
938 SET subinventory_code = p_sub_code
939 , status_id = l_sub_default_status
940 , inventory_location_type = l_loc_type
941 WHERE organization_id = p_org_id
942 AND inventory_location_id = x_location_id;
943 END IF;
944 ELSE
945 BEGIN
946 --- check validity
947 SELECT 'failed'
948 INTO l_validity_check
949 FROM DUAL
950 WHERE EXISTS( SELECT subinventory_code
951 FROM mtl_item_locations_kfv
952 WHERE concatenated_segments = p_concat_segs
953 AND p_sub_code <> subinventory_code
954 AND organization_id = p_org_id);
955 EXCEPTION
956 WHEN NO_DATA_FOUND THEN
957 NULL;
958 END;
959
960 IF l_validity_check = 'failed' THEN
961 x_result := 'E';
962 x_exist_or_create := '';
963 RETURN;
964 END IF;
965 END IF;
966
967 IF x_exist_or_create = 'CREATE' THEN
968 -- If a new locator is created then create a status history for it, bug# 1695432
969 l_status_rec.organization_id := p_org_id;
970 l_status_rec.inventory_item_id := NULL;
971 l_status_rec.lot_number := NULL;
972 l_status_rec.serial_number := NULL;
973 l_status_rec.update_method := inv_material_status_pub.g_update_method_manual;
974 l_status_rec.status_id := l_sub_default_status;
975 l_status_rec.zone_code := p_sub_code;
976 l_status_rec.locator_id := x_location_id;
977 l_status_rec.creation_date := SYSDATE;
978 l_status_rec.created_by := fnd_global.user_id;
979 l_status_rec.last_update_date := SYSDATE;
980 l_status_rec.last_update_login := fnd_global.user_id;
981 l_status_rec.initial_status_flag := 'Y';
982 l_status_rec.from_mobile_apps_flag := 'Y';
983 inv_material_status_pkg.insert_status_history(l_status_rec);
984 -- If a new locator is created, call label printing API
985
986 IF (l_debug = 1) THEN
987 inv_mobile_helper_functions.tracelog(p_err_msg => 'Before calling label printing in dynamic locator generation', p_module => 'Dynamic Locator', p_level => 3);
988 END IF;
989 inv_label.print_label_manual_wrap(
990 x_return_status => l_return_status
991 , x_msg_count => l_msg_count
992 , x_msg_data => l_msg_data
993 , x_label_status => l_label_status
994 , p_business_flow_code => 24
995 , p_organization_id => p_org_id
996 , p_subinventory_code => p_sub_code
997 , p_locator_id => x_location_id
998 );
999 IF (l_debug = 1) THEN
1000 inv_mobile_helper_functions.tracelog(p_err_msg => 'After calling label printing in dynamic locator generation, status=' || l_return_status, p_module => 'Dynamic Locator', p_level => 3);
1001 END IF;
1002 END IF;
1003 END IF;
1004 END;
1005 END;
1006
1007 -- This procedure validates a locator
1008 PROCEDURE check_dynamic_locator(x_result OUT NOCOPY VARCHAR2, p_org_id IN NUMBER, p_sub_code IN VARCHAR2, p_inventory_location_id IN VARCHAR2) IS
1009 l_temp NUMBER;
1010 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1011 BEGIN
1012 x_result := 'S';
1013
1014 BEGIN
1015 SELECT 1
1016 INTO l_temp
1017 FROM mtl_item_locations
1018 WHERE organization_id = p_org_id
1019 AND inventory_location_id = p_inventory_location_id;
1020 EXCEPTION
1021 WHEN NO_DATA_FOUND THEN
1022 x_result := 'U';
1023 END;
1024 END;
1025
1026 --
1027 --
1028 PROCEDURE get_valid_to_locs(
1029 x_locators OUT NOCOPY t_genref
1030 , p_transaction_action_id IN NUMBER
1031 , p_to_organization_id IN NUMBER
1032 , p_organization_id IN NUMBER
1033 , p_subinventory_code IN VARCHAR2
1034 , p_restrict_locators_code IN NUMBER
1035 , p_inventory_item_id IN NUMBER
1036 , p_concatenated_segments IN VARCHAR2
1037 , p_transaction_type_id IN NUMBER
1038 , p_wms_installed IN VARCHAR2
1039 ) IS
1040 l_org NUMBER;
1041 l_restrict_locators_code NUMBER;
1042 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1043 BEGIN
1044 IF p_transaction_action_id IN (3, 21) THEN
1045 l_org := p_to_organization_id;
1046
1047 SELECT restrict_locators_code
1048 INTO l_restrict_locators_code
1049 FROM mtl_system_items
1050 WHERE inventory_item_id = p_inventory_item_id
1051 AND organization_id = l_org;
1052 ELSE
1053 l_org := p_organization_id;
1054 l_restrict_locators_code := p_restrict_locators_code;
1055 END IF;
1056
1057 get_loc_lov(x_locators, l_org, p_subinventory_code, l_restrict_locators_code, p_inventory_item_id, p_concatenated_segments, p_transaction_type_id, p_wms_installed);
1058 END;
1059
1060 -- Name: GET_MO_FROMLOC_LOV
1061 --
1062 -- Input parameters:
1063 -- p_Organization_Id which restricts LOV SQL to current org
1064 -- p_Concatenated_Segments which restricts LOV SQL to user input text
1065 -- e.g. 1-1%
1066 --
1067 -- Output parameters:
1068 -- x_Locators returns LOV rows as reference cursor
1069 --
1070 -- Functions: This API is to return "src" locator for a given MO
1071 --
1072 PROCEDURE get_mo_fromloc_lov(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_project_id IN NUMBER := NULL, p_task_id IN NUMBER := NULL) IS
1073 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1074 BEGIN
1075 OPEN x_locators FOR
1076 SELECT mil.inventory_location_id
1077 --, mil.concatenated_segments conseg--Bug4398337:Commented this line and added below line
1078 , mil.locator_segments conseg
1079 , mil.description
1080 FROM wms_item_locations_kfv mil
1081 WHERE mil.organization_id = p_organization_id
1082 AND mil.inventory_location_id IN (SELECT from_locator_id
1083 FROM mtl_txn_request_lines
1084 WHERE header_id = p_moheader_id)
1085 AND mil.concatenated_segments LIKE (p_concatenated_segments)
1086 AND NVL(mil.project_id, -9999) = NVL(p_project_id, -9999)
1087 AND NVL(mil.task_id, -9999) = NVL(p_task_id, -9999);
1088 END;
1089 PROCEDURE get_mo_fromloc_lov(
1090 x_locators OUT NOCOPY t_genref,
1091 p_organization_id IN NUMBER,
1092 p_moheader_id IN NUMBER,
1093 p_concatenated_segments IN VARCHAR2,
1094 p_project_id IN NUMBER := NULL,
1095 p_task_id IN NUMBER := NULL,
1096 p_alias IN VARCHAR2
1097 ) IS
1098 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1099 BEGIN
1100 IF p_alias IS NULL THEN
1101 get_mo_fromloc_lov(
1102 x_locators => x_locators
1103 ,p_organization_id => p_organization_id
1104 ,p_moheader_id => p_moheader_id
1105 ,p_concatenated_segments => p_concatenated_segments
1106 ,p_project_id => p_project_id
1107 ,p_task_id => p_task_id
1108 );
1109 RETURN;
1110 END IF;
1111 OPEN x_locators FOR
1112 SELECT mil.inventory_location_id
1113 --, mil.concatenated_segments conseg--Bug4398337:Commented this line and added below line
1114 , mil.locator_segments conseg
1115 , mil.description
1116 FROM wms_item_locations_kfv mil
1117 WHERE mil.organization_id = p_organization_id
1118 AND mil.inventory_location_id IN (SELECT from_locator_id
1119 FROM mtl_txn_request_lines
1120 WHERE header_id = p_moheader_id)
1121 AND mil.alias = p_alias
1122 AND NVL(mil.project_id, -9999) = NVL(p_project_id, -9999)
1123 AND NVL(mil.task_id, -9999) = NVL(p_task_id, -9999);
1124 END GET_MO_FROMLOC_LOV;
1125
1126 -- Name: GET_MO_TOLOC_LOV
1127 --
1128 -- Input parameters:
1129 -- p_Organization_Id which restricts LOV SQL to current org
1130 -- p_Concatenated_Segments which restricts LOV SQL to user input text
1131 -- e.g. 1-1%
1132 --
1133 -- Output parameters:
1134 -- x_Locators returns LOV rows as reference cursor
1135 --
1136 -- Functions: This API is to return "destination" locator for a given MO
1137 --
1138 PROCEDURE get_mo_toloc_lov(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_project_id IN NUMBER := NULL, p_task_id IN NUMBER := NULL) IS
1139 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1140 BEGIN
1141 OPEN x_locators FOR
1142 SELECT mil.inventory_location_id
1143 --, mil.concatenated_segments consegs--Bug4398337:Commented this line and added below line
1144 , mil.locator_segments consegs
1145 , mil.description
1146 FROM wms_item_locations_kfv mil
1147 WHERE mil.organization_id = p_organization_id
1148 AND inventory_location_id IN (SELECT to_locator_id
1149 FROM mtl_txn_request_lines
1150 WHERE header_id = p_moheader_id)
1151 AND mil.concatenated_segments LIKE (p_concatenated_segments)
1152 AND NVL(mil.project_id, -9999) = NVL(p_project_id, -9999)
1153 AND NVL(mil.task_id, -9999) = NVL(p_task_id, -9999);
1154 END;
1155
1156 /* PJM-WMS Integration:Return only the the physical locators.
1157 * Use the table mtl_item_locations instead of mtl_item_locations_kfv.
1158 * Use the function INV_PROJECT.get_locsegs() to retrieve the
1159 * concatenated segments.Filter the locators based on the Project
1160 * and Task passed to the procedure.
1161 */
1162 PROCEDURE get_loc_with_status(x_locators OUT NOCOPY t_genref,
1163 p_organization_id IN NUMBER,
1164 p_subinventory_code IN VARCHAR2,
1165 p_concatenated_segments IN VARCHAR2,
1166 p_project_id IN NUMBER , -- PJM_WMS Integration
1167 p_task_id IN NUMBER ) -- PJM_WMS Integration
1168 IS
1169 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1170 BEGIN
1171 OPEN x_locators FOR
1172 SELECT mil.inventory_location_id
1173 --, mil.concatenated_segments--Bug4398337:Commented this line and added below line
1174 , mil.locator_segments concatenated_segments
1175 , mil.description
1176 , mil.status_id
1177 , mmsv.status_code
1178 FROM wms_item_locations_kfv mil, mtl_material_statuses_vl mmsv
1179 WHERE mil.organization_id = p_organization_id
1180 AND mil.subinventory_code = p_subinventory_code
1181 AND mil.concatenated_segments LIKE (p_concatenated_segments)
1182 AND mmsv.status_id = mil.status_id
1183 AND NVL(mil.project_id, -1) = NVL(p_project_id, -1)
1184 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1)
1185 ORDER BY mil.concatenated_segments; -- PJM-WMS Integration
1186 END get_loc_with_status;
1187
1188 PROCEDURE get_loc_with_status(
1189 x_locators OUT NOCOPY t_genref,
1190 p_organization_id IN NUMBER,
1191 p_subinventory_code IN VARCHAR2,
1192 p_concatenated_segments IN VARCHAR2,
1193 p_project_id IN NUMBER , -- PJM_WMS Integration
1194 p_task_id IN NUMBER, -- PJM_WMS Integration
1195 p_alias IN VARCHAR2
1196 ) IS
1197 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1198 BEGIN
1199 IF p_alias IS NULL THEN
1200 get_loc_with_status(
1201 x_locators => x_locators
1202 ,p_organization_id => p_organization_id
1203 ,p_subinventory_code => p_subinventory_code
1204 ,p_concatenated_segments => p_concatenated_segments
1205 ,p_project_id => p_project_id
1206 ,p_task_id => p_task_id
1207 );
1208 RETURN;
1209 END IF;
1210 OPEN x_locators FOR
1211 SELECT mil.inventory_location_id
1212 --, mil.concatenated_segments--Bug4398337:Commented this line and added below line
1213 , mil.locator_segments concatenated_segments
1214 , mil.description
1215 , mil.status_id
1216 , mmsv.status_code
1217 FROM wms_item_locations_kfv mil, mtl_material_statuses_vl mmsv
1218 WHERE mil.organization_id = p_organization_id
1219 AND mil.subinventory_code = p_subinventory_code
1220 AND mil.alias = p_alias
1221 AND mmsv.status_id = mil.status_id
1222 AND NVL(mil.project_id, -1) = NVL(p_project_id, -1)
1223 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1)
1224 ORDER BY mil.concatenated_segments; -- PJM-WMS Integration
1225 END get_loc_with_status;
1226
1227 ---- Obsolete
1228 PROCEDURE get_from_subs(
1229 x_zones OUT NOCOPY t_genref
1230 , p_organization_id IN NUMBER
1231 , p_inventory_item_id IN NUMBER
1232 , p_restrict_subinventories_code IN NUMBER
1233 , p_secondary_inventory_name IN VARCHAR2
1234 , p_transaction_action_id IN NUMBER
1235 , p_transaction_type_id IN NUMBER
1236 , p_wms_installed IN VARCHAR2
1237 ) IS
1238 l_expense_to_asset VARCHAR2(1);
1239 l_debug NUMBER;
1240 BEGIN
1241 fnd_profile.get('INV:EXPENSE_TO_ASSET_TRANSFER', l_expense_to_asset);
1242 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1243
1244 IF (NVL(l_expense_to_asset, '2') = '1') THEN
1245 IF (p_transaction_action_id <> 2
1246 AND p_transaction_action_id <> 3
1247 ) THEN
1248 IF p_restrict_subinventories_code = 1 THEN
1249 OPEN x_zones FOR
1250 SELECT a.secondary_inventory_name
1251 , NVL(a.locator_type, 1)
1252 , a.description
1253 , a.asset_inventory
1254 , a.lpn_controlled_flag
1255 , a.enable_locator_alias
1256 FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
1257 WHERE a.organization_id = p_organization_id
1258 AND a.organization_id = b.organization_id
1259 AND b.inventory_item_id = p_inventory_item_id
1260 AND a.secondary_inventory_name = b.secondary_inventory
1261 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1262 AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1263 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1264 ELSE
1265 OPEN x_zones FOR
1266 SELECT secondary_inventory_name
1267 , NVL(locator_type, 1)
1268 , description
1269 , asset_inventory
1270 , lpn_controlled_flag
1271 , enable_locator_alias
1272 FROM mtl_secondary_inventories
1273 WHERE organization_id = p_organization_id
1274 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1275 AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1276 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1277 END IF;
1278 ELSE
1279 IF p_restrict_subinventories_code = 1 THEN
1280 OPEN x_zones FOR
1281 SELECT a.secondary_inventory_name
1282 , NVL(a.locator_type, 1)
1283 , a.description
1284 , a.asset_inventory
1285 , a.lpn_controlled_flag
1286 , a.enable_locator_alias
1287 FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
1288 WHERE a.organization_id = p_organization_id
1289 AND a.organization_id = b.organization_id
1290 AND a.secondary_inventory_name = b.secondary_inventory
1291 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1292 AND b.inventory_item_id = p_inventory_item_id
1293 AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1294 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1295 ELSE
1296 OPEN x_zones FOR
1297 SELECT secondary_inventory_name
1298 , NVL(locator_type, 1)
1299 , description
1300 , asset_inventory
1301 , lpn_controlled_flag
1302 , enable_locator_alias
1303 FROM mtl_secondary_inventories
1304 WHERE organization_id = p_organization_id
1305 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1306 AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1307 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1308 END IF;
1309 END IF;
1310 ELSE
1311 IF (p_transaction_action_id <> 2
1312 AND p_transaction_action_id <> 3
1313 ) THEN
1314 IF p_restrict_subinventories_code = 1 THEN
1315 OPEN x_zones FOR
1316 SELECT a.secondary_inventory_name
1317 , NVL(a.locator_type, 1)
1318 , a.description
1319 , a.asset_inventory
1320 , a.lpn_controlled_flag
1321 , a.enable_locator_alias
1322 FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
1323 WHERE a.organization_id = p_organization_id
1324 AND a.organization_id = b.organization_id
1325 AND a.secondary_inventory_name = b.secondary_inventory
1326 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1327 AND b.inventory_item_id = p_inventory_item_id
1328 AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1329 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1330 ELSE
1331 OPEN x_zones FOR
1332 SELECT secondary_inventory_name
1333 , NVL(locator_type, 1)
1334 , description
1335 , asset_inventory
1336 , lpn_controlled_flag
1337 , enable_locator_alias
1338 FROM mtl_secondary_inventories
1339 WHERE organization_id = p_organization_id
1340 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1341 AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1342 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1343 END IF;
1344 ELSE
1345 IF p_restrict_subinventories_code = 1 THEN
1346 OPEN x_zones FOR
1347 SELECT a.secondary_inventory_name
1348 , NVL(a.locator_type, 1)
1349 , a.description
1350 , a.asset_inventory
1351 , a.lpn_controlled_flag
1352 , a.enable_locator_alias
1353 FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
1354 WHERE a.organization_id = p_organization_id
1355 AND a.organization_id = b.organization_id
1356 AND a.secondary_inventory_name = b.secondary_inventory
1357 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1358 AND b.inventory_item_id = p_inventory_item_id
1359 AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1360 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1361 ELSE
1362 OPEN x_zones FOR
1363 SELECT secondary_inventory_name
1364 , NVL(locator_type, 1)
1365 , description
1366 , asset_inventory
1367 , lpn_controlled_flag
1368 , enable_locator_alias
1369 FROM mtl_secondary_inventories
1370 WHERE organization_id = p_organization_id
1371 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1372 AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1373 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1374 END IF;
1375 END IF;
1376 END IF;
1377 END get_from_subs;
1378
1379 ---obsolete
1380 PROCEDURE get_to_sub(
1381 x_to_sub OUT NOCOPY t_genref
1382 , p_organization_id IN NUMBER
1383 , p_inventory_item_id IN NUMBER
1384 , p_from_secondary_name IN VARCHAR2
1385 , p_restrict_subinventories_code IN NUMBER
1386 , p_secondary_inventory_name IN VARCHAR2
1387 , p_from_sub_asset_inventory IN VARCHAR2
1388 , p_transaction_action_id IN NUMBER
1389 , p_to_organization_id IN NUMBER
1390 , p_serial_number_control_code IN NUMBER
1391 , p_transaction_type_id IN NUMBER
1392 , p_wms_installed IN VARCHAR2
1393 ) IS
1394 l_expense_to_asset VARCHAR2(1);
1395 l_inventory_asset_flag VARCHAR2(1);
1396 l_org NUMBER;
1397 l_restrict_subinventories_code NUMBER;
1398 l_from_sub VARCHAR2(10);
1399 l_from_sub_asset_inventory VARCHAR2(1);
1400 l_debug NUMBER;
1401 BEGIN
1402
1403 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1404
1405 IF p_transaction_action_id IN (3, 21) THEN
1406 l_org := p_to_organization_id;
1407
1408 SELECT restrict_subinventories_code
1409 INTO l_restrict_subinventories_code
1410 FROM mtl_system_items
1411 WHERE organization_id = l_org
1412 AND inventory_item_id = p_inventory_item_id;
1413 ELSE
1414 l_org := p_organization_id;
1415 l_restrict_subinventories_code := p_restrict_subinventories_code;
1416 END IF;
1417
1418 l_from_sub := p_from_secondary_name;
1419 l_from_sub_asset_inventory := p_from_sub_asset_inventory;
1420
1421 SELECT inventory_asset_flag
1422 INTO l_inventory_asset_flag
1423 FROM mtl_system_items
1424 WHERE inventory_item_id = p_inventory_item_id
1425 AND organization_id = l_org;
1426
1427 fnd_profile.get('INV:EXPENSE_TO_ASSET_TRANSFER', l_expense_to_asset);
1428
1429 IF (NVL(l_expense_to_asset, '2') = '1') THEN
1430 IF l_restrict_subinventories_code = 1 THEN
1431 OPEN x_to_sub FOR
1432 SELECT a.secondary_inventory_name
1433 , NVL(a.locator_type, 1)
1434 , a.description
1435 , a.asset_inventory
1436 , a.lpn_controlled_flag
1437 , a.enable_locator_alias
1438 FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
1439 WHERE a.organization_id = l_org
1440 AND a.organization_id = b.organization_id
1441 AND a.secondary_inventory_name = b.secondary_inventory
1442 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1443 AND b.inventory_item_id = p_inventory_item_id
1444 AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1445 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1446 ELSE
1447 OPEN x_to_sub FOR
1448 SELECT secondary_inventory_name
1449 , NVL(locator_type, 1)
1450 , description
1451 , asset_inventory
1452 , lpn_controlled_flag
1453 , enable_locator_alias
1454 FROM mtl_secondary_inventories
1455 WHERE organization_id = l_org
1456 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1457 AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1458 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1459 END IF;
1460 ELSE
1461 IF l_restrict_subinventories_code = 1 THEN
1462 IF l_inventory_asset_flag = 'Y' THEN
1463 IF l_from_sub_asset_inventory = 1 THEN
1464 OPEN x_to_sub FOR
1465 SELECT a.secondary_inventory_name
1466 , NVL(a.locator_type, 1)
1467 , a.description
1468 , a.asset_inventory
1469 , a.lpn_controlled_flag
1470 , a.enable_locator_alias
1471 FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
1472 WHERE a.organization_id = l_org
1473 AND a.organization_id = b.organization_id
1474 -- and a.asset_inventory = 1
1475 AND b.inventory_item_id = p_inventory_item_id
1476 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1477 AND a.secondary_inventory_name = b.secondary_inventory
1478 AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1479 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1480 ELSE
1481 OPEN x_to_sub FOR
1482 SELECT a.secondary_inventory_name
1483 , NVL(a.locator_type, 1)
1484 , a.description
1485 , a.asset_inventory
1486 , a.lpn_controlled_flag
1487 , a.enable_locator_alias
1488 FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
1489 WHERE a.organization_id = l_org
1490 AND a.organization_id = b.organization_id
1491 AND a.asset_inventory = 2
1492 AND b.inventory_item_id = p_inventory_item_id
1493 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1494 AND a.secondary_inventory_name = b.secondary_inventory
1495 AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1496 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1497 END IF;
1498 ELSE
1499 OPEN x_to_sub FOR
1500 SELECT a.secondary_inventory_name
1501 , NVL(a.locator_type, 1)
1502 , a.description
1503 , a.asset_inventory
1504 , a.lpn_controlled_flag
1505 , a.enable_locator_alias
1506 FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
1507 WHERE a.organization_id = l_org
1508 AND a.organization_id = b.organization_id
1509 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1510 AND a.secondary_inventory_name = b.secondary_inventory
1511 AND b.inventory_item_id = p_inventory_item_id
1512 AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1513 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1514 END IF;
1515 ELSE
1516 IF l_inventory_asset_flag = 'Y' THEN
1517 IF l_from_sub_asset_inventory = 1 THEN
1518 OPEN x_to_sub FOR
1519 SELECT secondary_inventory_name
1520 , NVL(locator_type, 1)
1521 , description
1522 , asset_inventory
1523 , lpn_controlled_flag
1524 , enable_locator_alias
1525 FROM mtl_secondary_inventories
1526 WHERE organization_id = l_org
1527 --and asset_inventory = 1
1528 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1529 AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1530 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1531 ELSE
1532 OPEN x_to_sub FOR
1533 SELECT secondary_inventory_name
1534 , NVL(locator_type, 1)
1535 , description
1536 , asset_inventory
1537 , lpn_controlled_flag
1538 , enable_locator_alias
1539 FROM mtl_secondary_inventories
1540 WHERE organization_id = l_org
1541 AND asset_inventory = 2
1542 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1543 AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1544 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1545 END IF;
1546 ELSE
1547 OPEN x_to_sub FOR
1548 SELECT secondary_inventory_name
1549 , NVL(locator_type, 1)
1550 , description
1551 , asset_inventory
1552 , lpn_controlled_flag
1553 , enable_locator_alias
1554 FROM mtl_secondary_inventories
1555 WHERE organization_id = l_org
1556 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1557 AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1558 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1559 END IF;
1560 END IF;
1561 END IF;
1562 END get_to_sub;
1563
1564 -- Obsolete
1565 PROCEDURE get_valid_subs(
1566 x_zones OUT NOCOPY t_genref
1567 , p_organization_id IN NUMBER
1568 , p_subinventory_code IN VARCHAR2
1569 ) IS
1570 l_debug NUMBER;
1571 BEGIN
1572 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1573 --bug#3440453 Remove the NVL on organization_id if user passes it.
1574 IF p_organization_id IS NULL THEN
1575 OPEN x_zones FOR
1576 SELECT secondary_inventory_name
1577 , NVL(locator_type, 1)
1578 , description
1579 , asset_inventory
1580 , lpn_controlled_flag
1581 , enable_locator_alias
1582 FROM mtl_secondary_inventories
1583 WHERE organization_id = NVL(p_organization_id, organization_id)
1584 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1585 AND secondary_inventory_name LIKE (p_subinventory_code)
1586 ORDER BY secondary_inventory_name;
1587 ELSE -- Organization_id is not null
1588 OPEN x_zones FOR
1589 SELECT secondary_inventory_name
1590 , NVL(locator_type, 1)
1591 , description
1592 , asset_inventory
1593 , lpn_controlled_flag
1594 , enable_locator_alias
1595 FROM mtl_secondary_inventories
1596 WHERE organization_id = p_organization_id
1597 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1598 AND secondary_inventory_name LIKE (p_subinventory_code)
1599 ORDER BY secondary_inventory_name;
1600 END IF;
1601 END get_valid_subs;
1602
1603 --=Obsolete
1604 PROCEDURE get_valid_subinvs(
1605 x_zones OUT NOCOPY t_genref
1606 , p_organization_id IN NUMBER
1607 , p_subinventory_code IN VARCHAR2
1608 , p_txn_type_id IN NUMBER
1609 , p_wms_installed IN VARCHAR2
1610 ) IS
1611 l_debug NUMBER;
1612 BEGIN
1613 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1614 --bug#3440453 Remove the NVL on organization_id if user passes a value to it.
1615 IF p_organization_id IS NULL THEN
1616 OPEN x_zones FOR
1617 SELECT secondary_inventory_name
1618 , NVL(locator_type, 1)
1619 , description
1620 , asset_inventory
1621 , lpn_controlled_flag
1622 , enable_locator_alias
1623 FROM mtl_secondary_inventories
1624 WHERE organization_id = NVL(p_organization_id, organization_id)
1625 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1626 AND secondary_inventory_name LIKE (p_subinventory_code)
1627 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
1628 ORDER BY secondary_inventory_name;
1629 ELSE -- Organization_id is not null
1630 OPEN x_zones FOR
1631 SELECT secondary_inventory_name
1632 , NVL(locator_type, 1)
1633 , description
1634 , asset_inventory
1635 , lpn_controlled_flag
1636 , enable_locator_alias
1637 FROM mtl_secondary_inventories
1638 WHERE organization_id = p_organization_id
1639 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1640 AND secondary_inventory_name LIKE (p_subinventory_code)
1641 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
1642 ORDER BY secondary_inventory_name;
1643 END IF;
1644 END get_valid_subinvs;
1645
1646 FUNCTION check_loc_existence(p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2)
1647 RETURN NUMBER IS
1648 loc_control NUMBER;
1649 loc_exists NUMBER := 0;
1650 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1651 BEGIN
1652 /* Bug #1613379
1653 SELECT 1
1654 INTO loc_exists
1655 FROM DUAL
1656 WHERE exists (select 1
1657 FROM mtl_item_locations
1658 WHERE organization_id = p_organization_id
1659 AND subinventory_code = p_subinventory_code);
1660 */
1661
1662 SELECT locator_type
1663 INTO loc_control
1664 FROM mtl_secondary_inventories
1665 WHERE organization_id = p_organization_id
1666 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1667 AND secondary_inventory_name = p_subinventory_code;
1668
1669 IF loc_control <> 0 THEN
1670 loc_exists := 1;
1671 END IF;
1672
1673 RETURN loc_exists;
1674 EXCEPTION
1675 WHEN NO_DATA_FOUND THEN
1676 loc_exists := 0;
1677 RETURN loc_exists;
1678 END check_loc_existence;
1679
1680 /* This procedure is for the status update find page, here the locatorType
1681 in the second parameter represents if any locators exist in the current
1682 subinventory but doesnt really mean the locator control type */
1683 -- Obsolete
1684 PROCEDURE get_sub_with_loc(
1685 x_zones OUT NOCOPY t_genref
1686 , p_organization_id IN NUMBER
1687 , p_subinventory_code IN VARCHAR2
1688 ) IS
1689 l_debug NUMBER;
1690 BEGIN
1691 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1692 OPEN x_zones FOR
1693 SELECT secondary_inventory_name
1694 , inv_ui_item_sub_loc_lovs.check_loc_existence(p_organization_id, secondary_inventory_name)
1695 , msi.description
1696 , asset_inventory
1697 , mmsv.status_id
1698 , status_code
1699 , enable_locator_alias
1700 FROM mtl_secondary_inventories msi, mtl_material_statuses_vl mmsv
1701 WHERE organization_id = p_organization_id
1702 AND mmsv.status_id = msi.status_id
1703 AND NVL(msi.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1704 AND secondary_inventory_name LIKE (p_subinventory_code);
1705 END get_sub_with_loc;
1706
1707 --- Obsolete
1708 PROCEDURE get_sub_lov_ship(
1709 x_sub_lov OUT NOCOPY t_genref
1710 , p_txn_dock IN VARCHAR2
1711 , p_organization_id IN NUMBER
1712 , p_dock_appointment_id IN NUMBER
1713 , p_sub IN VARCHAR2
1714 ) IS
1715 l_debug NUMBER;
1716 BEGIN
1717 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1718 IF (p_txn_dock = 'Y') THEN
1719 OPEN x_sub_lov FOR
1720 SELECT msub.secondary_inventory_name
1721 , NVL(msub.locator_type, 1)
1722 , msub.description
1723 , msub.asset_inventory
1724 , msub.lpn_controlled_flag
1725 , msub.enable_locator_alias
1726 FROM mtl_secondary_inventories msub
1727 WHERE msub.organization_id = p_organization_id
1728 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1729 AND EXISTS( SELECT wda.staging_lane_id
1730 FROM wms_dock_appointments_b wda, mtl_item_locations milk, wsh_trip_stops pickup_stop
1731 WHERE milk.inventory_location_id(+) = wda.staging_lane_id
1732 AND milk.organization_id(+) = wda.organization_id
1733 AND milk.organization_id = p_organization_id
1734 AND milk.subinventory_code = msub.secondary_inventory_name
1735 AND wda.dock_appointment_id = p_dock_appointment_id
1736 AND wda.trip_stop = pickup_stop.stop_id(+))
1737 AND msub.secondary_inventory_name LIKE (p_sub);
1738 ELSIF (p_txn_dock = 'N') THEN
1739 OPEN x_sub_lov FOR
1740 SELECT msub.secondary_inventory_name
1741 , NVL(msub.locator_type, 1)
1742 , msub.description
1743 , msub.asset_inventory
1744 , lpn_controlled_flag
1745 , enable_locator_alias
1746 FROM mtl_secondary_inventories msub
1747 WHERE msub.organization_id = p_organization_id
1748 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1749 AND EXISTS( SELECT milk.inventory_location_id
1750 FROM mtl_item_locations milk, wms_license_plate_numbers lpn
1751 WHERE milk.inventory_location_id(+) = lpn.locator_id
1752 AND milk.organization_id(+) = lpn.organization_id
1753 AND milk.organization_id = p_organization_id
1754 AND milk.subinventory_code = msub.secondary_inventory_name
1755 AND (lpn.lpn_context = 1
1756 OR lpn.lpn_context = 11
1757 ))
1758 AND msub.secondary_inventory_name LIKE (p_sub);
1759 END IF;
1760 END get_sub_lov_ship;
1761
1762 PROCEDURE get_to_xsubs(x_to_xsubs OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2) IS
1763 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1764 BEGIN
1765 OPEN x_to_xsubs FOR
1766 SELECT secondary_inventory_name
1767 , locator_type
1768 , description
1769 , asset_inventory
1770 , lpn_controlled_flag
1771 FROM mtl_secondary_inventories
1772 WHERE organization_id = p_organization_id
1773 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1774 AND secondary_inventory_name LIKE (p_subinventory_code)
1775 ORDER BY secondary_inventory_name;
1776 END get_to_xsubs;
1777
1778 -- Name: GET_PHYINV_SUBS
1779 --
1780 -- Input parameters:
1781 -- p_subinventory_code - restricts the subinventory to those like
1782 -- the user inputted text if given
1783 -- p_organization_id - restricts LOV SQL to current org
1784 -- p_all_sub_flag - all subinventories flag which indicates
1785 -- whether all the subs associated with the
1786 -- org are used or only those that are defined
1787 -- for that particular physical inventory
1788 -- p_physical_inventory_id - The physical inventory for which we are
1789 -- querying up the subs for
1790 --
1791 --
1792 -- Output parameters:
1793 -- x_phy_inv_sub_lov - Returns LOV rows as reference cursor
1794 --
1795 -- Functions: This API returns the valid subs associated with a
1796 -- physical inventory
1797 --
1798 --- obsolete
1799 PROCEDURE get_phyinv_subs(
1800 x_phy_inv_sub_lov OUT NOCOPY t_genref
1801 , p_subinventory_code IN VARCHAR2
1802 , p_organization_id IN NUMBER
1803 , p_all_sub_flag IN NUMBER
1804 , p_physical_inventory_id IN NUMBER
1805 ) IS
1806 --l_dynamic_tag_entry_flag NUMBER;
1807 l_debug NUMBER;
1808 BEGIN
1809
1810 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1811 /* bug 1727378 fix*/
1812 -- Get the dynamic tag entry flag
1813 --SELECT dynamic_tag_entry_flag
1814 -- INTO l_dynamic_tag_entry_flag
1815 -- FROM mtl_physical_inventories
1816 -- WHERE physical_inventory_id = p_physical_inventory_id
1817 -- AND organization_id = p_organization_id;
1818
1819 --IF (l_dynamic_tag_entry_flag = 1) THEN
1820 -- Dynamic tags are allowed
1821 -- OPEN x_phy_inv_sub_lov FOR
1822 -- SELECT msub.secondary_inventory_name
1823 -- , Nvl(msub.locator_type, 1)
1824 -- , msub.description
1825 -- , msub.asset_inventory
1826 -- , msub.lpn_controlled_flag
1827 -- FROM mtl_secondary_inventories msub
1828 -- WHERE msub.organization_id = p_organization_id
1829 -- AND nvl(msub.disable_date, trunc(sysdate+1)) > trunc(sysdate)
1830 -- AND msub.secondary_inventory_name LIKE (p_subinventory_code || '%')
1831 -- ORDER BY UPPER(msub.secondary_inventory_name);
1832 -- ELSE
1833 -- Dynamic tags are not allowed
1834 /* bug 1727378 fix*/
1835
1836 IF (p_all_sub_flag = 1) THEN
1837 -- All Subinventories included for this physical inventory
1838 OPEN x_phy_inv_sub_lov FOR
1839 SELECT msub.secondary_inventory_name
1840 , NVL(msub.locator_type, 1)
1841 , msub.description
1842 , msub.asset_inventory
1843 , msub.lpn_controlled_flag
1844 , msub.enable_locator_alias
1845 FROM mtl_secondary_inventories msub
1846 WHERE msub.organization_id = p_organization_id
1847 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1848 AND msub.secondary_inventory_name LIKE (p_subinventory_code)
1849 ORDER BY UPPER(msub.secondary_inventory_name);
1850 ELSE
1851 -- Include only those subinventories which have been explicitly
1852 -- associated with this physical inventory
1853 OPEN x_phy_inv_sub_lov FOR
1854 SELECT UNIQUE msub.secondary_inventory_name
1855 , NVL(msub.locator_type, 1)
1856 , msub.description
1857 , msub.asset_inventory
1858 , msub.lpn_controlled_flag
1859 , msub.enable_locator_alias
1860 FROM mtl_secondary_inventories msub, mtl_physical_subinventories mpsub
1861 WHERE msub.organization_id = p_organization_id
1862 AND mpsub.organization_id = p_organization_id
1863 AND mpsub.subinventory = msub.secondary_inventory_name
1864 AND mpsub.physical_inventory_id = p_physical_inventory_id
1865 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1866 AND msub.secondary_inventory_name LIKE (p_subinventory_code)
1867 ORDER BY UPPER(msub.secondary_inventory_name);
1868 END IF;
1869
1870 END get_phyinv_subs;
1871
1872 -- Name: GET_PHYINV_LOCS
1873 --
1874 -- Input parameters:
1875 -- p_organization_id - restricts LOV SQL to current org
1876 -- p_subinventory_code - restricts LOV to the current subinventory
1877 -- p_concatenated_segments - restricts the locator to those that are
1878 -- similar to the user inputted text.
1879 -- locators are a key flex field so this
1880 -- is how the user represents/identifies locators
1881 -- p_dynamic_entry_flag - this flag determines whether or not
1882 -- dynamic tag entries are allowed
1883 -- p_physical_inventory_id - The physical inventory for which we are
1884 -- querying up the locators for
1885 --
1886 --
1887 -- Output parameters:
1888 -- x_locators - Returns LOV rows as reference cursor
1889 --
1890 -- Functions: This API returns the valid locators associated with a
1891 -- physical inventory
1892 --
1893 PROCEDURE get_phyinv_locs
1894 (x_locators OUT NOCOPY t_genref ,
1895 p_organization_id IN NUMBER ,
1896 p_subinventory_code IN VARCHAR2 ,
1897 p_concatenated_segments IN VARCHAR2 ,
1898 p_dynamic_entry_flag IN NUMBER ,
1899 p_physical_inventory_id IN NUMBER ,
1900 p_project_id IN NUMBER := NULL ,
1901 p_task_id IN NUMBER := NULL
1902 )
1903 IS
1904 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1905 BEGIN
1906 IF (p_dynamic_entry_flag = 2) THEN
1907 -- Dynamic entries are not allowed
1908 OPEN x_locators FOR
1909 SELECT UNIQUE mil.inventory_location_id
1910 --, mil.concatenated_segments--Bug4398337:Commented this line and added below line
1911 , mil.locator_segments concatenated_segments
1912 , mil.description
1913 FROM wms_item_locations_kfv mil, mtl_physical_inventory_tags mpit
1914 WHERE mil.organization_id = p_organization_id
1915 AND mil.subinventory_code = p_subinventory_code
1916 AND mil.concatenated_segments LIKE (p_concatenated_segments)
1917 AND mil.inventory_location_id = mpit.locator_id
1918 AND mpit.physical_inventory_id = p_physical_inventory_id
1919 AND mpit.organization_id = p_organization_id
1920 AND NVL(mpit.void_flag, 2) = 2
1921 -- WMS PJM Integration: Restrict Locators based on the project and task
1922 AND NVL(mil.project_id, -1) = NVL(p_project_id, -1)
1923 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1)
1924 --For bug number 4885951
1925 AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1926 AND mpit.adjustment_id IN (SELECT adjustment_id
1927 FROM mtl_physical_adjustments
1928 WHERE physical_inventory_id = p_physical_inventory_id
1929 AND organization_id = p_organization_id
1930 AND approval_status IS NULL);
1931 ELSE
1932 -- dynamic entries are allowed
1933 OPEN x_locators FOR
1934 SELECT inventory_location_id
1935 --, concatenated_segments--Bug4398337:Commented this line and added below line
1936 , locator_segments concatenated_segments
1937 , description
1938 FROM wms_item_locations_kfv mil
1939 WHERE organization_id = p_organization_id
1940 AND subinventory_code = p_subinventory_code
1941 AND concatenated_segments LIKE (p_concatenated_segments)
1942 -- WMS PJM Integration: Restrict Locators based on the project and task
1943 AND NVL(project_id, -1) = NVL(p_project_id, -1)
1944 AND NVL(task_id, -1) = NVL(p_task_id, -1)
1945 --For bug number 4885951
1946 AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE);
1947
1948 END IF;
1949 END get_phyinv_locs;
1950
1951 PROCEDURE get_phyinv_locs
1952 (x_locators OUT NOCOPY t_genref ,
1953 p_organization_id IN NUMBER ,
1954 p_subinventory_code IN VARCHAR2 ,
1955 p_concatenated_segments IN VARCHAR2 ,
1956 p_dynamic_entry_flag IN NUMBER ,
1957 p_physical_inventory_id IN NUMBER ,
1958 p_project_id IN NUMBER := NULL ,
1959 p_task_id IN NUMBER := NULL ,
1960 p_alias IN VARCHAR2
1961 )
1962 IS
1963 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1964 BEGIN
1965
1966 IF p_alias IS NULL THEN
1967 get_phyinv_locs(
1968 x_locators => x_locators
1969 ,p_organization_id => p_organization_id
1970 ,p_subinventory_code => p_subinventory_code
1971 ,p_concatenated_segments => p_concatenated_Segments
1972 ,p_dynamic_entry_flag => p_dynamic_entry_flag
1973 ,p_physical_inventory_id => p_physical_inventory_id
1974 ,p_project_id => p_project_id
1975 ,p_task_id => p_task_id
1976 );
1977 RETURN;
1978 END IF;
1979 IF (p_dynamic_entry_flag = 2) THEN
1980 -- Dynamic entries are not allowed
1981 OPEN x_locators FOR
1982 SELECT UNIQUE mil.inventory_location_id
1983 --, mil.concatenated_segments--Bug4398337:Commented this line and added below line
1984 , mil.locator_segments concatenated_segments
1985 , mil.description
1986 FROM wms_item_locations_kfv mil, mtl_physical_inventory_tags mpit
1987 WHERE mil.organization_id = p_organization_id
1988 AND mil.subinventory_code = p_subinventory_code
1989 AND mil.alias = p_alias
1990 AND mil.inventory_location_id = mpit.locator_id
1991 AND mpit.physical_inventory_id = p_physical_inventory_id
1992 AND mpit.organization_id = p_organization_id
1993 AND NVL(mpit.void_flag, 2) = 2
1994 -- WMS PJM Integration: Restrict Locators based on the project and task
1995 AND NVL(mil.project_id, -1) = NVL(p_project_id, -1)
1996 AND NVL(mil.task_id, -1) = NVL(p_task_id, -1)
1997 --For bug number 4885951
1998 AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1999 AND mpit.adjustment_id IN (SELECT adjustment_id
2000 FROM mtl_physical_adjustments
2001 WHERE physical_inventory_id = p_physical_inventory_id
2002 AND organization_id = p_organization_id
2003 AND approval_status IS NULL);
2004 ELSE
2005 -- dynamic entries are allowed
2006 OPEN x_locators FOR
2007 SELECT inventory_location_id
2008 --, concatenated_segments--Bug4398337:Commented this line and added below line
2009 , locator_segments concatenated_segments
2010 , description
2011 FROM wms_item_locations_kfv mil
2012 WHERE organization_id = p_organization_id
2013 AND subinventory_code = p_subinventory_code
2014 AND alias = p_alias
2015 -- WMS PJM Integration: Restrict Locators based on the project and task
2016 AND NVL(project_id, -1) = NVL(p_project_id, -1)
2017 AND NVL(task_id, -1) = NVL(p_task_id, -1)
2018 --For bug number 4885951
2019 AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE);
2020
2021 END IF;
2022 END get_phyinv_locs;
2023
2024 -- Name: GET_CYC_SUBS
2025 --
2026 -- Input parameters:
2027 -- p_subinventory_code - restricts the subinventory to those like
2028 -- the user inputted text if given
2029 -- p_organization_id - restricts LOV SQL to current org
2030 -- p_orientation_code - orientation code which indicates
2031 -- whether all the subs associated with the
2032 -- org are used or only those that are defined
2033 -- for that particular cycle count
2034 -- p_cycle_count_header_id - The physical inventory for which we are
2035 -- querying up the subs for
2036 --
2037 --
2038 -- Output parameters:
2039 -- x_cyc_sub_lov - Returns LOV rows as reference cursor
2040 --
2041 -- Functions: This API returns the valid subs associated with a
2042 -- cycle count
2043 --
2044 --- obsolete
2045 PROCEDURE get_cyc_subs(
2046 x_cyc_sub_lov OUT NOCOPY t_genref
2047 , p_subinventory_code IN VARCHAR2
2048 , p_organization_id IN NUMBER
2049 , p_orientation_code IN NUMBER
2050 , p_cycle_count_header_id IN NUMBER
2051 ) IS
2052 l_unscheduled_count_entry NUMBER;
2053 l_debug NUMBER;
2054 BEGIN
2055 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2056
2057 IF (p_orientation_code = 1) THEN
2058 -- All subinventories in the org are included for this cycle count
2059 OPEN x_cyc_sub_lov FOR
2060 SELECT msub.secondary_inventory_name
2061 , NVL(msub.locator_type, 1)
2062 , msub.description
2063 , msub.asset_inventory
2064 , msub.lpn_controlled_flag
2065 , msub.enable_locator_alias
2066 FROM mtl_secondary_inventories msub
2067 WHERE msub.organization_id = p_organization_id
2068 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2069 AND msub.secondary_inventory_name LIKE (p_subinventory_code)
2070 -- Bug# 2770853
2071 -- Check for material status at the subinventory level
2072 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2073 NULL,
2074 4,
2075 NULL,
2076 NULL,
2077 msub.organization_id,
2078 NULL,
2079 msub.secondary_inventory_name,
2080 NULL,
2081 NULL,
2082 NULL,
2083 'Z') = 'Y')
2084 ORDER BY UPPER(msub.secondary_inventory_name);
2085 ELSE
2086 -- Include only those subinventories which have been explicitly
2087 -- associated with this cycle count
2088 OPEN x_cyc_sub_lov FOR
2089 SELECT UNIQUE msub.secondary_inventory_name
2090 , NVL(msub.locator_type, 1)
2091 , msub.description
2092 , msub.asset_inventory
2093 , msub.lpn_controlled_flag
2094 , msub.enable_locator_alias
2095 FROM mtl_secondary_inventories msub, mtl_cc_subinventories mccs
2096 WHERE msub.organization_id = p_organization_id
2097 AND mccs.cycle_count_header_id = p_cycle_count_header_id
2098 AND mccs.subinventory = msub.secondary_inventory_name
2099 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2100 AND msub.secondary_inventory_name LIKE (p_subinventory_code)
2101 -- Bug# 2770853
2102 -- Check for material status at the subinventory level
2103 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2104 NULL,
2105 4,
2106 NULL,
2107 NULL,
2108 msub.organization_id,
2109 NULL,
2110 msub.secondary_inventory_name,
2111 NULL,
2112 NULL,
2113 NULL,
2114 'Z') = 'Y')
2115 ORDER BY UPPER(msub.secondary_inventory_name);
2116 END IF;
2117 END get_cyc_subs;
2118
2119
2120 -- Patchset I: WMS-PJM integration
2121 -- Name: GET_CYC_LOCS
2122 --
2123 -- Input parameters:
2124 -- p_organization_id - restricts LOV SQL to current org
2125 -- p_subinventory_code - restricts LOV to the current subinventory
2126 -- p_concatenated_segments - restricts the locator to those that are
2127 -- similar to the user inputted text.
2128 -- locators are a key flex field so this
2129 -- is how the user represents/identifies locators
2130 -- p_unscheduled_entry - this flag determines whether or not
2131 -- unscheduled count entries are allowed
2132 -- p_cycle_count_header_id - The cycle count header for which we are
2133 -- querying up the locators for.
2134 -- p_project_id - restrict LOV SQL to this Project Id(Default null)
2135 -- p_task_id - restrict LOV SQL to this Task Id(Default null)
2136 --
2137 --
2138 -- Output parameters:
2139 -- x_locators - Returns LOV rows as reference cursor
2140 --
2141 -- Functions: This API returns the valid locators associated with a
2142 -- cycle count
2143 --
2144 --
2145 --
2146 PROCEDURE get_cyc_locs
2147 (x_locators OUT NOCOPY t_genref ,
2148 p_organization_id IN NUMBER ,
2149 p_subinventory_code IN VARCHAR2 ,
2150 p_concatenated_segments IN VARCHAR2 ,
2151 p_unscheduled_entry IN NUMBER ,
2152 p_cycle_count_header_id IN NUMBER ,
2153 p_project_id IN NUMBER ,
2154 p_task_id IN NUMBER
2155 )
2156 IS
2157 l_proc_name CONSTANT VARCHAR2(30) := 'INV_UI_ITEM_SUB_LOCS';
2158 l_serial_discrepancy_option NUMBER;
2159 l_container_discrepancy_option NUMBER;
2160 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2161 l_ispjm_org VARCHAR2(1);
2162 BEGIN
2163 IF (l_debug = 1) THEN
2164 Inv_log_util.trace('p_organization_id: =======> ' || p_organization_id ,l_proc_name ,9);
2165 Inv_log_util.trace('p_subinventory_code: =====> ' || p_subinventory_code , l_proc_name,9);
2166 Inv_log_util.trace('p_concatenated_segments: => ' || p_concatenated_segments , l_proc_name,9);
2167 Inv_log_util.trace('p_unscheduled_entry: =====> ' || p_unscheduled_entry , l_proc_name,9);
2168 Inv_log_util.trace('p_cycle_count_header_id: => ' || p_cycle_count_header_id , l_proc_name,9);
2169 Inv_log_util.trace('p_project_id: ============> ' || p_project_id , l_proc_name,9);
2170 Inv_log_util.trace('p_task_id: ===============> ' || p_task_id, l_proc_name,9);
2171 END IF;
2172 BEGIN
2173 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
2174 INTO l_ispjm_org
2175 FROM pjm_org_parameters
2176 WHERE organization_id=p_organization_id;
2177 EXCEPTION
2178 WHEN NO_DATA_FOUND THEN
2179 l_ispjm_org:='N';
2180 END; -- Get the cycle count discrepancy option flags
2181 SELECT NVL(serial_discrepancy_option, 2), NVL(container_discrepancy_option, 2)
2182 INTO l_serial_discrepancy_option, l_container_discrepancy_option
2183 FROM mtl_cycle_count_headers
2184 WHERE cycle_count_header_id = p_cycle_count_header_id;
2185
2186 IF (l_debug = 1) THEN
2187 Inv_log_util.trace('l_serial_discrepancy_option: ' || l_serial_discrepancy_option , l_proc_name,9);
2188 Inv_log_util.trace('l_container_discrepancy_option: ' || l_container_discrepancy_option, l_proc_name,9);
2189 END IF;
2190
2191 IF (p_unscheduled_entry = 2 AND
2192 l_serial_discrepancy_option = 2 AND
2193 l_container_discrepancy_option = 2) THEN
2194 -- unscheduled count entries are not allowed
2195 -- and serial and container discrepancies are also not allowed
2196 OPEN x_locators FOR
2197 SELECT UNIQUE mil.inventory_location_id,
2198 --mil.concatenated_segments concatenated_segments,--Bug4398337:Commented this line and added below line
2199 mil.locator_segments concatenated_segments,
2200 mil.description
2201 FROM wms_item_locations_kfv mil, mtl_cycle_count_entries mcce
2202 WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
2203 AND mil.organization_id = p_organization_id
2204 AND mil.subinventory_code = p_subinventory_code
2205 AND NVL(mil.project_id,-1) = NVL(p_project_id,-1)
2206 AND NVL(mil.task_id,-1) = NVL(p_task_id,-1)
2207 AND mil.concatenated_segments LIKE (p_concatenated_segments)
2208 AND mcce.organization_id = mil.organization_id
2209 AND mcce.subinventory = mil.subinventory_code
2210 AND mil.inventory_location_id = mcce.locator_id
2211 AND mcce.entry_status_code IN (1,3)
2212 AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2213 -- Bug# 2770853
2214 -- Check for material status at the locator level
2215 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2216 NULL,
2217 4,
2218 NULL,
2219 NULL,
2220 mcce.organization_id,
2221 mcce.inventory_item_id,
2222 mcce.subinventory,
2223 mcce.locator_id,
2224 NULL,
2225 NULL,
2226 'L') = 'Y')
2227 ORDER BY concatenated_segments;
2228 ELSE
2229 -- unscheduled count entries are allowed
2230 -- or serial or container discrepancy is allowed
2231
2232 IF (l_ispjm_org = 'Y' and p_project_id is not null ) then
2233 Inv_log_util.trace('p_ispjm_org = yes and p_project_id is not null' , 'INV_UI_ITEM_SUB_LOCS',9);
2234 IF ( p_task_id is not null ) then
2235 Inv_log_util.trace('task id is not null ' , 'INV_UI_ITEM_SUB_LOCS',9);
2236 OPEN x_locators FOR
2237 SELECT inventory_location_id,
2238 -- concatenated_segments, --Bug4398337:Commented this line and added below line
2239 locator_segments concatenated_segments,
2240 description
2241 FROM wms_item_locations_kfv
2242 WHERE organization_id = p_organization_id
2243 AND subinventory_code = p_subinventory_code
2244 AND concatenated_segments LIKE (p_concatenated_segments )-- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2245 AND project_id = p_project_id
2246 AND task_id = p_task_id
2247 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2248 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2249 NULL,
2250 4,
2251 NULL,
2252 NULL,
2253 organization_id,
2254 inventory_item_id,
2255 subinventory_code,
2256 inventory_location_id,
2257 NULL,
2258 NULL,
2259 'L') = 'Y')
2260 ORDER BY 2;
2261 ELSE -- task_id is null then
2262 Inv_log_util.trace('Task is null ' , 'INV_UI_ITEM_SUB_LOCS',9);
2263
2264 OPEN x_locators FOR
2265 SELECT inventory_location_id,
2266 -- concatenated_segments ,--Bug4398337:Commented this line and added below line
2267 locator_segments concatenated_segments,
2268 description
2269 FROM wms_item_locations_kfv
2270 WHERE organization_id = p_organization_id
2271 AND subinventory_code = p_subinventory_code
2272 AND concatenated_segments LIKE (p_concatenated_segments )--inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2273 AND project_id = p_project_id
2274 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2275 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2276 NULL,
2277 4,
2278 NULL,
2279 NULL,
2280 organization_id,
2281 inventory_item_id,
2282 subinventory_code,
2283 inventory_location_id,
2284 NULL,
2285 NULL,
2286 'L') = 'Y')
2287 ORDER BY 2;
2288
2289 END IF;
2290 ELSE -- non pjm org or project is not passed
2291 Inv_log_util.trace('non pjm org or project is not passed' , 'INV_UI_ITEM_SUB_LOCS',9);
2292 OPEN x_locators FOR
2293 SELECT inventory_location_id,
2294 -- concatenated_segments, --Bug4398337:Commented this line and added below line
2295 locator_segments concatenated_segments,
2296 description
2297 FROM wms_item_locations_kfv
2298 WHERE organization_id = p_organization_id
2299 AND subinventory_code = p_subinventory_code
2300 AND concatenated_segments LIKE (p_concatenated_segments )-- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2301 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2302 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2303 NULL,
2304 4,
2305 NULL,
2306 NULL,
2307 organization_id,
2308 inventory_item_id,
2309 subinventory_code,
2310 inventory_location_id,
2311 NULL,
2312 NULL,
2313 'L') = 'Y')
2314 ORDER BY 2;
2315 END IF;
2316 END IF;
2317 END GET_CYC_LOCS;
2318
2319 PROCEDURE get_cyc_locs
2320 (x_locators OUT NOCOPY t_genref ,
2321 p_organization_id IN NUMBER ,
2322 p_subinventory_code IN VARCHAR2 ,
2323 p_concatenated_segments IN VARCHAR2 ,
2324 p_unscheduled_entry IN NUMBER ,
2325 p_cycle_count_header_id IN NUMBER ,
2326 p_project_id IN NUMBER ,
2327 p_task_id IN NUMBER ,
2328 p_alias IN VARCHAR2
2329 )
2330 IS
2331 l_proc_name CONSTANT VARCHAR2(30) := 'INV_UI_ITEM_SUB_LOCS';
2332 l_serial_discrepancy_option NUMBER;
2333 l_container_discrepancy_option NUMBER;
2334 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2335 l_ispjm_org VARCHAR2(1);
2336 BEGIN
2337 IF (l_debug = 1) THEN
2338 Inv_log_util.trace('p_organization_id: =======> ' || p_organization_id ,l_proc_name ,9);
2339 Inv_log_util.trace('p_subinventory_code: =====> ' || p_subinventory_code , l_proc_name,9);
2340 Inv_log_util.trace('p_concatenated_segments: => ' || p_concatenated_segments , l_proc_name,9);
2341 Inv_log_util.trace('p_unscheduled_entry: =====> ' || p_unscheduled_entry , l_proc_name,9);
2342 Inv_log_util.trace('p_cycle_count_header_id: => ' || p_cycle_count_header_id , l_proc_name,9);
2343 Inv_log_util.trace('p_project_id: ============> ' || p_project_id , l_proc_name,9);
2344 Inv_log_util.trace('p_task_id: ===============> ' || p_task_id, l_proc_name,9);
2345 END IF;
2346
2347 IF p_alias IS NULL THEN
2348 get_cyc_locs(
2349 x_locators => x_locators
2350 , p_organization_id => p_organization_id
2351 , p_subinventory_code => p_subinventory_code
2352 , p_concatenated_segments => p_concatenated_segments
2353 , p_unscheduled_entry => p_unscheduled_entry
2354 , p_cycle_count_header_id => p_cycle_count_header_id
2355 , p_project_id => p_project_id
2356 , p_task_id => p_task_id
2357 );
2358 RETURN;
2359 END IF;
2360 BEGIN
2361 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
2362 INTO l_ispjm_org
2363 FROM pjm_org_parameters
2364 WHERE organization_id=p_organization_id;
2365 EXCEPTION
2366 WHEN NO_DATA_FOUND THEN
2367 l_ispjm_org:='N';
2368 END; -- Get the cycle count discrepancy option flags
2369 SELECT NVL(serial_discrepancy_option, 2), NVL(container_discrepancy_option, 2)
2370 INTO l_serial_discrepancy_option, l_container_discrepancy_option
2371 FROM mtl_cycle_count_headers
2372 WHERE cycle_count_header_id = p_cycle_count_header_id;
2373
2374 IF (l_debug = 1) THEN
2375 Inv_log_util.trace('l_serial_discrepancy_option: ' || l_serial_discrepancy_option , l_proc_name,9);
2376 Inv_log_util.trace('l_container_discrepancy_option: ' || l_container_discrepancy_option, l_proc_name,9);
2377 END IF;
2378
2379 IF (p_unscheduled_entry = 2 AND
2380 l_serial_discrepancy_option = 2 AND
2381 l_container_discrepancy_option = 2) THEN
2382 -- unscheduled count entries are not allowed
2383 -- and serial and container discrepancies are also not allowed
2384 OPEN x_locators FOR
2385 SELECT UNIQUE mil.inventory_location_id,
2386 --mil.concatenated_segments concatenated_segments,--Bug4398337:Commented this line and added below line
2387 mil.locator_segments concatenated_segments,
2388 mil.description
2389 FROM wms_item_locations_kfv mil, mtl_cycle_count_entries mcce
2390 WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
2391 AND mil.organization_id = p_organization_id
2392 AND mil.subinventory_code = p_subinventory_code
2393 AND NVL(mil.project_id,-1) = NVL(p_project_id,-1)
2394 AND NVL(mil.task_id,-1) = NVL(p_task_id,-1)
2395 AND mil.alias = p_alias
2396 AND mcce.organization_id = mil.organization_id
2397 AND mcce.subinventory = mil.subinventory_code
2398 AND mil.inventory_location_id = mcce.locator_id
2399 AND mcce.entry_status_code IN (1,3)
2400 AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2401 -- Bug# 2770853
2402 -- Check for material status at the locator level
2403 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2404 NULL,
2405 4,
2406 NULL,
2407 NULL,
2408 mcce.organization_id,
2409 mcce.inventory_item_id,
2410 mcce.subinventory,
2411 mcce.locator_id,
2412 NULL,
2413 NULL,
2414 'L') = 'Y')
2415 ORDER BY concatenated_segments;
2416 ELSE
2417 -- unscheduled count entries are allowed
2418 -- or serial or container discrepancy is allowed
2419
2420 IF (l_ispjm_org = 'Y' and p_project_id is not null ) then
2421 Inv_log_util.trace('p_ispjm_org = yes and p_project_id is not null' , 'INV_UI_ITEM_SUB_LOCS',9);
2422 IF ( p_task_id is not null ) then
2423 Inv_log_util.trace('task id is not null ' , 'INV_UI_ITEM_SUB_LOCS',9);
2424 OPEN x_locators FOR
2425 SELECT inventory_location_id,
2426 -- concatenated_segments, --Bug4398337:Commented this line and added below line
2427 locator_segments concatenated_segments,
2428 description
2429 FROM wms_item_locations_kfv
2430 WHERE organization_id = p_organization_id
2431 AND subinventory_code = p_subinventory_code
2432 AND alias = p_alias
2433 AND project_id = p_project_id
2434 AND task_id = p_task_id
2435 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2436 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2437 NULL,
2438 4,
2439 NULL,
2440 NULL,
2441 organization_id,
2442 inventory_item_id,
2443 subinventory_code,
2444 inventory_location_id,
2445 NULL,
2446 NULL,
2447 'L') = 'Y')
2448 ORDER BY 2;
2449 ELSE -- task_id is null then
2450 Inv_log_util.trace('Task is null ' , 'INV_UI_ITEM_SUB_LOCS',9);
2451
2452 OPEN x_locators FOR
2453 SELECT inventory_location_id,
2454 -- concatenated_segments ,--Bug4398337:Commented this line and added below line
2455 locator_segments concatenated_segments,
2456 description
2457 FROM wms_item_locations_kfv
2458 WHERE organization_id = p_organization_id
2459 AND subinventory_code = p_subinventory_code
2460 AND alias = p_alias
2461 --inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2462 AND project_id = p_project_id
2463 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2464 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2465 NULL,
2466 4,
2467 NULL,
2468 NULL,
2469 organization_id,
2470 inventory_item_id,
2471 subinventory_code,
2472 inventory_location_id,
2473 NULL,
2474 NULL,
2475 'L') = 'Y')
2476 ORDER BY 2;
2477
2478 END IF;
2479 ELSE -- non pjm org or project is not passed
2480 Inv_log_util.trace('non pjm org or project is not passed' , 'INV_UI_ITEM_SUB_LOCS',9);
2481 OPEN x_locators FOR
2482 SELECT inventory_location_id,
2483 -- concatenated_segments, --Bug4398337:Commented this line and added below line
2484 locator_segments concatenated_segments,
2485 description
2486 FROM wms_item_locations_kfv
2487 WHERE organization_id = p_organization_id
2488 AND subinventory_code = p_subinventory_code
2489 AND alias = p_alias
2490 -- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2491 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2492 AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2493 NULL,
2494 4,
2495 NULL,
2496 NULL,
2497 organization_id,
2498 inventory_item_id,
2499 subinventory_code,
2500 inventory_location_id,
2501 NULL,
2502 NULL,
2503 'L') = 'Y')
2504 ORDER BY 2;
2505 END IF;
2506 END IF;
2507 END GET_CYC_LOCS;
2508
2509
2510 -- Consignment and VMI Changes: Added Planning Org, TP Type, Owning Org and TP Type.
2511 PROCEDURE get_valid_lpn_org_level(
2512 x_lpns OUT NOCOPY t_genref
2513 , p_organization_id IN NUMBER
2514 , p_lpn_segments IN VARCHAR2
2515 , p_planning_org_id IN NUMBER
2516 , p_planning_tp_type IN NUMBER
2517 , p_owning_org_id IN NUMBER
2518 , p_owning_tp_type IN NUMBER
2519 ) IS
2520 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2521 BEGIN
2522 OPEN x_lpns FOR
2523 SELECT DISTINCT a.license_plate_number
2524 , a.outermost_lpn_id
2525 , a.subinventory_code
2526 , NVL(a.locator_id, 0)
2527 , NVL(b.asset_inventory, '0')
2528 , 0
2529 , inv_project.get_locsegs(a.locator_id, p_organization_id)
2530 , inv_project.get_project_id
2531 , inv_project.get_project_number
2532 , inv_project.get_task_id
2533 , inv_project.get_task_number
2534 FROM wms_license_plate_numbers a, mtl_secondary_inventories b
2535 WHERE a.organization_id = p_organization_id
2536 AND (a.lpn_context = 1 OR a.lpn_context = 11)
2537 AND b.organization_id(+) = a.organization_id
2538 AND b.secondary_inventory_name(+) = a.subinventory_code
2539 AND NVL(b.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2540 AND a.license_plate_number LIKE (p_lpn_segments)
2541 AND a.parent_lpn_id IS NULL
2542 AND (p_owning_org_id IS NULL
2543 OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
2544 ,wms_license_plate_numbers wlpn
2545 WHERE moqd.lpn_id in (wlpn.lpn_id)
2546 AND wlpn.outermost_lpn_id = a.outermost_lpn_id
2547 AND moqd.organization_id = a.organization_id
2548 AND moqd.owning_organization_id = p_owning_org_id
2549 AND moqd.owning_tp_type = p_owning_tp_type))
2550 AND (p_planning_org_id IS NULL
2551 OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
2552 ,wms_license_plate_numbers wlpn
2553 WHERE moqd.lpn_id in (wlpn.lpn_id)
2554 AND wlpn.outermost_lpn_id = a.outermost_lpn_id
2555 AND moqd.organization_id = a.organization_id
2556 AND moqd.planning_organization_id = p_planning_org_id
2557 AND moqd.planning_tp_type = p_planning_tp_type));
2558 END get_valid_lpn_org_level;
2559
2560 --Bug 5512205 Introduced a new overloaded procedure that validates the LPN status before populating the LPN LOV for sub xfer
2561 PROCEDURE get_valid_lpn_org_level(
2562 x_lpns OUT NOCOPY t_genref
2563 , p_organization_id IN NUMBER
2564 , p_lpn_segments IN VARCHAR2
2565 , p_planning_org_id IN NUMBER
2566 , p_planning_tp_type IN NUMBER
2567 , p_owning_org_id IN NUMBER
2568 , p_owning_tp_type IN NUMBER
2569 , p_to_organization_id IN NUMBER
2570 , p_transaction_type_id IN NUMBER
2571 , p_wms_installed IN VARCHAR2
2572 ) IS
2573 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2574 BEGIN
2575 OPEN x_lpns FOR
2576 SELECT DISTINCT a.license_plate_number
2577 , a.outermost_lpn_id
2578 , a.subinventory_code
2579 , NVL(a.locator_id, 0)
2580 , NVL(b.asset_inventory, '0')
2581 , 0
2582 , inv_project.get_locsegs(a.locator_id, p_organization_id)
2583 , inv_project.get_project_id
2584 , inv_project.get_project_number
2585 , inv_project.get_task_id
2586 , inv_project.get_task_number
2587 FROM wms_license_plate_numbers a, mtl_secondary_inventories b
2588 WHERE a.organization_id = p_organization_id
2589 AND (a.lpn_context = 1 OR a.lpn_context = 11)
2590 AND b.organization_id(+) = a.organization_id
2591 AND b.secondary_inventory_name(+) = a.subinventory_code
2592 AND NVL(b.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2593 AND a.license_plate_number LIKE (p_lpn_segments)
2594 AND a.parent_lpn_id IS NULL
2595 AND vaildate_lpn_status(a.outermost_lpn_id
2596 ,a.organization_id
2597 ,p_to_organization_id
2598 ,p_wms_installed
2599 ,p_transaction_type_id) = 'Y'
2600 AND (p_owning_org_id IS NULL
2601 OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
2602 ,wms_license_plate_numbers wlpn
2603 WHERE moqd.lpn_id in (wlpn.lpn_id)
2604 AND wlpn.outermost_lpn_id = a.outermost_lpn_id
2605 AND moqd.organization_id = a.organization_id
2606 AND moqd.owning_organization_id = p_owning_org_id
2607 AND moqd.owning_tp_type = p_owning_tp_type))
2608 AND (p_planning_org_id IS NULL
2609 OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
2610 ,wms_license_plate_numbers wlpn
2611 WHERE moqd.lpn_id in (wlpn.lpn_id)
2612 AND wlpn.outermost_lpn_id = a.outermost_lpn_id
2613 AND moqd.organization_id = a.organization_id
2614 AND moqd.planning_organization_id = p_planning_org_id
2615 AND moqd.planning_tp_type = p_planning_tp_type));
2616 END get_valid_lpn_org_level;
2617 --End Bug 5512205
2618
2619 FUNCTION validate_lpn_for_toorg(p_lpn_id IN NUMBER, p_to_organization_id IN NUMBER, p_orgid IN NUMBER, p_transaction_type_id IN NUMBER)
2620 RETURN VARCHAR2 IS
2621 x_return VARCHAR(1);
2622 l_count NUMBER;
2623 l_item_id NUMBER;
2624 l_invalid_count NUMBER := 0;
2625
2626 CURSOR l_item_cursor IS
2627 SELECT DISTINCT inventory_item_id
2628 FROM wms_lpn_contents
2629 WHERE parent_lpn_id IN (SELECT lpn_id
2630 FROM wms_license_plate_numbers
2631 WHERE outermost_lpn_id = p_lpn_id)
2632 AND inventory_item_id IS NOT NULL;
2633 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2634 BEGIN
2635 x_return := 'Y';
2636
2637 IF (p_orgid IS NOT NULL AND p_transaction_type_id = 3 ) THEN
2638
2639 -- bug#3440453 Split the existing SQL into 2 for performance reasons
2640 -- re-using the existing item_cursor
2641 -- checking for invalid combinations for each content in the outermost lpn
2642 -- and exitting from the loop as soon as one invalid combination is found.
2643
2644 OPEN l_item_cursor;
2645 LOOP --loop for all the contents inside the outermost LPN
2646 FETCH l_item_cursor INTO l_item_id;
2647 EXIT WHEN l_item_cursor%NOTFOUND;
2648
2649 BEGIN
2650 -- Check for invalid item control codes.
2651 SELECT 1
2652 INTO l_invalid_count
2653 FROM dual
2654 WHERE EXISTS (SELECT a.inventory_item_id
2655 FROM mtl_system_items a
2656 , mtl_system_items b
2657 WHERE a.inventory_item_id = b.inventory_item_id
2658 AND a.organization_id = p_orgid
2659 AND b.organization_id = p_to_organization_id
2660 AND a.inventory_item_id = l_item_id
2661 AND ((a.serial_number_control_code IN (1,6) AND b.serial_number_control_code IN (2,5))
2662 OR
2663 (a.revision_qty_control_code = 1 AND b.revision_qty_control_code = 2)
2664 OR
2665 (a.lot_control_code = 1 AND b.lot_control_code = 2))
2666 );
2667
2668 IF (l_invalid_count <> 0) THEN
2669 x_return := 'N';
2670 IF l_item_cursor%isopen THEN
2671 CLOSE l_item_cursor;
2672 END IF;
2673 RETURN x_return;
2674 END IF;
2675
2676 EXCEPTION
2677 WHEN no_data_found THEN
2678
2679 -- No data found can be for 2 reasons
2680 -- a. Item doesn't exist in TO Org which is a failure case
2681 -- b. The lot/serial/revision control code combinations are perfect
2682 -- We should check for scenario (a) and throw error.
2683 SELECT COUNT(*)
2684 INTO l_count
2685 FROM mtl_system_items
2686 WHERE organization_id = p_to_organization_id
2687 AND inventory_item_id = l_item_id;
2688
2689 IF l_count = 1 THEN
2690 x_return := 'Y';
2691 ELSE
2692 x_return := 'N';
2693 IF l_item_cursor%isopen THEN
2694 CLOSE l_item_cursor;
2695 END IF;
2696 RETURN x_return;
2697 END IF;
2698
2699 WHEN OTHERS THEN
2700 x_return := 'N';
2701 IF l_item_cursor%isopen THEN
2702 CLOSE l_item_cursor;
2703 END IF;
2704 RETURN x_return;
2705 END;
2706
2707 END LOOP;--loop for all the contents inside the outermost LPN
2708
2709 IF l_item_cursor%isopen THEN
2710 CLOSE l_item_cursor;
2711 END IF;
2712
2713 ELSE --Interorg transfer. Just check whether item exits in the dest org
2714
2715 OPEN l_item_cursor;
2716 LOOP
2717 FETCH l_item_cursor INTO l_item_id;
2718 EXIT WHEN l_item_cursor%NOTFOUND;
2719 l_count := 0;
2720
2721 SELECT COUNT(*)
2722 INTO l_count
2723 FROM mtl_system_items
2724 WHERE organization_id = p_to_organization_id
2725 AND inventory_item_id = l_item_id;
2726
2727 IF l_count = 1 THEN
2728 x_return := 'Y';
2729 ELSE
2730 x_return := 'N';
2731 IF l_item_cursor%isopen THEN
2732 CLOSE l_item_cursor;
2733 END IF;
2734 RETURN x_return;
2735 END IF;
2736 END LOOP;
2737
2738 IF l_item_cursor%isopen THEN
2739 CLOSE l_item_cursor;
2740 END IF;
2741 END IF;
2742
2743 RETURN x_return;
2744 END validate_lpn_for_toorg;
2745
2746 --- Obsolete
2747 PROCEDURE get_valid_lpn_tosubs(
2748 x_to_sub OUT NOCOPY t_genref
2749 , p_organization_id IN NUMBER
2750 , p_lpn_id IN NUMBER
2751 , p_from_secondary_name IN VARCHAR2
2752 , p_from_sub_asset_inventory IN VARCHAR2
2753 , p_transaction_action_id IN NUMBER
2754 , p_to_organization_id IN NUMBER
2755 , p_transaction_type_id IN NUMBER
2756 , p_wms_installed IN VARCHAR2
2757 , p_secondary_inventory_name IN VARCHAR2
2758 ) IS
2759 l_org NUMBER;
2760 l_lpn_rsvd NUMBER;
2761 l_debug NUMBER;
2762 l_procedure_name VARCHAR2(30);
2763 BEGIN
2764 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2765 l_procedure_name := 'GET_VALID_LPN_TOSUBS';
2766
2767 IF p_transaction_action_id IN (3, 21) THEN
2768 l_org := p_to_organization_id;
2769 ELSE
2770 l_org := p_organization_id;
2771 END IF;
2772
2773 /* LPN reservation impact */
2774 BEGIN
2775 /*SELECT COUNT(*)
2776 INTO l_lpn_rsvd
2777 FROM mtl_reservations
2778 WHERE lpn_id = p_lpn_id;*/
2779
2780 --Bug 5942895 Modified above query to consider nesting of LPNs.
2781 SELECT COUNT(*)
2782 INTO l_lpn_rsvd
2783 FROM mtl_reservations
2784 WHERE lpn_id IN (SELECT lpn_id
2785 FROM wms_license_plate_numbers
2786 WHERE outermost_lpn_id = p_lpn_id);
2787 EXCEPTION
2788 WHEN NO_DATA_FOUND THEN
2789 l_lpn_rsvd := 0;
2790 WHEN OTHERS THEN
2791 l_lpn_rsvd := 0;
2792 END;
2793
2794 IF l_lpn_rsvd = 0 THEN -- the lpn is not reserved
2795 OPEN x_to_sub FOR
2796 SELECT secondary_inventory_name
2797 , NVL(locator_type, 1)
2798 , description
2799 , asset_inventory
2800 , lpn_controlled_flag
2801 , enable_locator_alias
2802 FROM mtl_secondary_inventories
2803 WHERE organization_id = l_org
2804 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2805 AND inv_ui_item_sub_loc_lovs.vaildate_to_lpn_sub(p_lpn_id, secondary_inventory_name, l_org, p_from_sub_asset_inventory, p_wms_installed, p_transaction_type_id) = 'Y'
2806 AND secondary_inventory_name LIKE (p_secondary_inventory_name);
2807 ELSE
2808 OPEN x_to_sub FOR
2809 SELECT secondary_inventory_name
2810 , NVL(locator_type, 1)
2811 , description
2812 , asset_inventory
2813 , lpn_controlled_flag
2814 , enable_locator_alias
2815 FROM mtl_secondary_inventories
2816 WHERE organization_id = l_org
2817 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2818 AND inv_ui_item_sub_loc_lovs.vaildate_to_lpn_sub(p_lpn_id, secondary_inventory_name, l_org, p_from_sub_asset_inventory, p_wms_installed, p_transaction_type_id) = 'Y'
2819 AND secondary_inventory_name LIKE (p_secondary_inventory_name)
2820 AND reservable_type = 1
2821 AND lpn_controlled_flag = 1;
2822 END IF;
2823 END get_valid_lpn_tosubs;
2824
2825 FUNCTION vaildate_to_lpn_sub(p_lpn_id IN NUMBER, p_to_subinventory IN VARCHAR2, p_orgid IN NUMBER, p_from_sub_asset_inventory IN VARCHAR2, p_wms_installed IN VARCHAR2, p_transaction_type_id IN NUMBER)
2826 RETURN VARCHAR2 IS
2827 l_item_id NUMBER;
2828 l_restrict_subinventories_code NUMBER;
2829 l_inventory_asset_flag VARCHAR2(1);
2830 l_expense_to_asset VARCHAR2(1);
2831 l_count NUMBER;
2832 x_return VARCHAR2(1);
2833 l_content_type NUMBER;
2834 -- Changed for Bug 1795328
2835 l_lpn_content NUMBER;
2836
2837 CURSOR l_item_cursor IS
2838 SELECT DISTINCT inventory_item_id
2839 FROM wms_lpn_contents
2840 WHERE parent_lpn_id IN (SELECT lpn_id
2841 FROM wms_license_plate_numbers
2842 WHERE outermost_lpn_id = p_lpn_id)
2843 AND inventory_item_id IS NOT NULL;
2844 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2845 BEGIN
2846 /*
2847 SELECT DISTINCT content_type INTO l_content_type
2848 FROM wms_lpn_contents
2849 WHERE outermost_lpn_id = p_lpn_id;
2850
2851 IF l_content_type NOT IN (1) THEN
2852 RETURN 'Y';
2853 END IF;
2854 */
2855
2856 OPEN l_item_cursor;
2857 l_lpn_content := 0;
2858
2859 LOOP
2860 FETCH l_item_cursor INTO l_item_id;
2861 EXIT WHEN l_item_cursor%NOTFOUND;
2862 l_lpn_content := 1;
2863
2864 IF inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_orgid, l_item_id, p_to_subinventory, NULL, NULL, NULL, 'Z') = 'Y' THEN
2865 SELECT restrict_subinventories_code
2866 , inventory_asset_flag
2867 INTO l_restrict_subinventories_code
2868 , l_inventory_asset_flag
2869 FROM mtl_system_items
2870 WHERE inventory_item_id = l_item_id
2871 AND organization_id = p_orgid;
2872
2873 fnd_profile.get('INV:EXPENSE_TO_ASSET_TRANSFER', l_expense_to_asset);
2874
2875 IF (NVL(l_expense_to_asset, '2') = '1') THEN
2876 IF l_restrict_subinventories_code = 1 THEN
2877 SELECT COUNT(*)
2878 INTO l_count
2879 FROM mtl_item_sub_inventories
2880 WHERE inventory_item_id = l_item_id
2881 AND organization_id = p_orgid
2882 AND secondary_inventory = p_to_subinventory;
2883
2884 IF l_count = 0 THEN
2885 x_return := 'N';
2886 RETURN x_return;
2887 ELSE
2888 x_return := 'Y';
2889 END IF;
2890 ELSE
2891 x_return := 'Y';
2892 END IF;
2893 ELSE
2894 IF l_restrict_subinventories_code = 1 THEN
2895 IF l_inventory_asset_flag = 'Y' THEN
2896 IF p_from_sub_asset_inventory = 1 THEN
2897 SELECT COUNT(*)
2898 INTO l_count
2899 FROM mtl_item_sub_inventories
2900 WHERE inventory_item_id = l_item_id
2901 AND organization_id = p_orgid
2902 AND secondary_inventory = p_to_subinventory;
2903
2904 IF l_count = 0 THEN
2905 x_return := 'N';
2906 RETURN x_return;
2907 ELSE
2908 x_return := 'Y';
2909 END IF;
2910 ELSE
2911 SELECT COUNT(*)
2912 INTO l_count
2913 FROM mtl_item_sub_exp_val_v
2914 WHERE inventory_item_id = l_item_id
2915 AND organization_id = p_orgid
2916 AND secondary_inventory_name = p_to_subinventory;
2917
2918 IF l_count = 0 THEN
2919 x_return := 'N';
2920 RETURN x_return;
2921 ELSE
2922 x_return := 'Y';
2923 END IF;
2924 END IF;
2925 ELSE
2926 SELECT COUNT(*)
2927 INTO l_count
2928 FROM mtl_item_sub_inventories
2929 WHERE inventory_item_id = l_item_id
2930 AND organization_id = p_orgid
2931 AND secondary_inventory = p_to_subinventory;
2932
2933 IF l_count = 0 THEN
2934 x_return := 'N';
2935 RETURN x_return;
2936 ELSE
2937 x_return := 'Y';
2938 END IF;
2939 END IF;
2940 ELSE
2941 x_return := 'Y';
2942 END IF;
2943 --dbms_output.putline('jj');
2944 END IF;
2945 ELSE
2946 x_return := 'N';
2947 RETURN x_return;
2948 END IF;
2949 END LOOP;
2950
2951 CLOSE l_item_cursor;
2952
2953 -- Changed for 1795328
2954 IF l_lpn_content = 0 THEN
2955 x_return := 'Y';
2956 END IF;
2957
2958 --END ;
2959
2960 RETURN x_return;
2961 END vaildate_to_lpn_sub;
2962
2963 FUNCTION vaildate_lpn_toloc(p_lpn_id IN NUMBER, p_to_subinventory IN VARCHAR2, p_orgid IN NUMBER, p_locator_id IN NUMBER, p_wms_installed IN VARCHAR2, p_transaction_type_id IN NUMBER)
2964 RETURN VARCHAR2 IS
2965 l_item_id NUMBER;
2966 l_restrict_locators_code NUMBER;
2967 l_count NUMBER;
2968 x_return VARCHAR2(1);
2969 -- Changed for Bug 1795328
2970 l_lpn_content NUMBER;
2971
2972 CURSOR l_item_cursor IS
2973 SELECT DISTINCT inventory_item_id
2974 FROM wms_lpn_contents
2975 WHERE parent_lpn_id IN (SELECT lpn_id
2976 FROM wms_license_plate_numbers
2977 WHERE outermost_lpn_id = p_lpn_id)
2978 AND inventory_item_id IS NOT NULL;
2979 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2980 BEGIN
2981 IF inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_orgid, l_item_id, p_to_subinventory, p_locator_id, NULL, NULL, 'L') = 'Y' THEN
2982 OPEN l_item_cursor;
2983 l_lpn_content := 0;
2984
2985 LOOP
2986 FETCH l_item_cursor INTO l_item_id;
2987 EXIT WHEN l_item_cursor%NOTFOUND;
2988 l_lpn_content := 1;
2989
2990 SELECT restrict_locators_code
2991 INTO l_restrict_locators_code
2992 FROM mtl_system_items
2993 WHERE inventory_item_id = l_item_id
2994 AND organization_id = p_orgid;
2995
2996 IF l_restrict_locators_code = 1 THEN
2997 SELECT COUNT(*)
2998 INTO l_count
2999 FROM mtl_secondary_locators
3000 WHERE p_locator_id = secondary_locator
3001 AND inventory_item_id = l_item_id
3002 AND organization_id = p_orgid;
3003
3004 IF l_count = 0 THEN
3005 x_return := 'N';
3006 ELSE
3007 x_return := 'Y';
3008 END IF;
3009 ELSE
3010 x_return := 'Y';
3011 END IF;
3012 END LOOP;
3013
3014 CLOSE l_item_cursor;
3015 ELSE
3016 --x_return := 'Y';
3017 -- bug 3390030, the function should return N if the material status
3018 -- does not return Y
3019 x_return := 'N';
3020 END IF;
3021
3022 -- Changed for 1795328
3023 IF l_lpn_content = 0 THEN
3024 x_return := 'Y';
3025 END IF;
3026
3027 RETURN x_return;
3028 END vaildate_lpn_toloc;
3029
3030 PROCEDURE get_lpnloc_lov(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_lpn_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_concatenated_segments IN VARCHAR2, p_transaction_type_id IN NUMBER, p_wms_installed IN VARCHAR2) IS
3031 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3032 BEGIN
3033 OPEN x_locators FOR
3034 SELECT inventory_location_id
3035 --, concatenated_segments--Bug4398337:Commented this line and added below line
3036 , locator_segments concatenated_segments
3037 , description
3038 FROM wms_item_locations_kfv
3039 WHERE organization_id = p_organization_id
3040 AND subinventory_code = p_subinventory_code
3041 AND concatenated_segments LIKE (p_concatenated_segments)
3042 AND inv_ui_item_sub_loc_lovs.vaildate_lpn_toloc(p_lpn_id, p_subinventory_code, p_organization_id, inventory_location_id, p_wms_installed, p_transaction_type_id) = 'Y';
3043 END get_lpnloc_lov;
3044
3045 FUNCTION validate_sub_loc_status(p_lpn IN VARCHAR2, p_org_id IN NUMBER, p_sub IN VARCHAR2, p_loc_id IN NUMBER, p_not_lpn_id IN VARCHAR2 := NULL, p_parent_lpn_id IN VARCHAR2 := '0', p_txn_type_id IN NUMBER)
3046 RETURN VARCHAR2 IS
3047 x_return VARCHAR2(1) := 'U';
3048 pack_sub_check VARCHAR2(1) := 'U';
3049 pack_loc_check VARCHAR2(1) := 'U';
3050 unpack_sub_check VARCHAR2(1) := 'U';
3051 unpack_loc_check VARCHAR2(1) := 'U';
3052 oth_val VARCHAR2(1) := 'U';
3053 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3054 BEGIN
3055 BEGIN
3056 SELECT 'Y'
3057 INTO oth_val
3058 FROM DUAL
3059 WHERE EXISTS( SELECT wlpn.license_plate_number
3060 FROM wms_license_plate_numbers wlpn
3061 WHERE (wlpn.organization_id = p_org_id
3062 AND wlpn.lpn_context = 5
3063 AND license_plate_number = p_lpn
3064 )
3065 OR (wlpn.organization_id = p_org_id
3066 AND (wlpn.lpn_context = 1
3067 OR wlpn.lpn_context = 11
3068 )
3069 AND NVL(subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
3070 AND NVL(locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(locator_id, '0'))
3071 AND NOT lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
3072 AND NVL(parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(parent_lpn_id, 0))
3073 AND license_plate_number = p_lpn
3074 ));
3075 EXCEPTION
3076 WHEN NO_DATA_FOUND THEN
3077 x_return := 'N';
3078 RETURN x_return;
3079 END;
3080
3081 IF oth_val <> 'Y' THEN
3082 x_return := 'N';
3083 RETURN x_return;
3084 END IF;
3085
3086 IF p_txn_type_id IN (500, 502) THEN
3087 pack_sub_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 500, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'Z');
3088 pack_loc_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 500, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'L');
3089
3090 IF pack_sub_check = 'N'
3091 OR pack_loc_check = 'N' THEN
3092 x_return := 'N';
3093 RETURN x_return;
3094 END IF;
3095 ELSIF p_txn_type_id IN (501, 502) THEN
3096 unpack_sub_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'Z');
3097 unpack_loc_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'L');
3098
3099 IF unpack_sub_check = 'N'
3100 OR unpack_loc_check = 'N' THEN
3101 x_return := 'N';
3102 RETURN x_return;
3103 END IF;
3104 END IF;
3105
3106 x_return := 'Y';
3107 RETURN x_return;
3108 END validate_sub_loc_status;
3109
3110 FUNCTION vaildate_lpn_status(p_lpn_id IN NUMBER, p_orgid IN NUMBER, p_to_org_id IN NUMBER, p_wms_installed IN VARCHAR2, p_transaction_type_id IN NUMBER)
3111 RETURN VARCHAR2 IS
3112 x_return VARCHAR2(1);
3113
3114 TYPE l_rec IS RECORD(
3115 serial_number VARCHAR2(30)
3116 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
3117 , lot_number VARCHAR2(80)
3118 , inventory_item_id NUMBER
3119 , subinventory_code VARCHAR2(10)
3120 , locator_id NUMBER);
3121
3122 l_record l_rec;
3123
3124 CURSOR l_cursor IS
3125 SELECT DISTINCT wlc.serial_number
3126 , wlc.lot_number
3127 , wlc.inventory_item_id
3128 , wlpn.subinventory_code
3129 , wlpn.locator_id
3130 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
3131 WHERE wlc.organization_id = p_orgid
3132 AND wlc.parent_lpn_id = wlpn.lpn_id
3133 AND wlc.organization_id = wlpn.organization_id
3134 AND wlc.parent_lpn_id IN (SELECT lpn_id
3135 FROM wms_license_plate_numbers
3136 WHERE outermost_lpn_id = p_lpn_id)
3137 AND wlc.inventory_item_id IS NOT NULL
3138 UNION
3139 SELECT DISTINCT serial_number
3140 , lot_number
3141 , inventory_item_id
3142 , current_subinventory_code
3143 , current_locator_id
3144 FROM mtl_serial_numbers
3145 WHERE current_organization_id = p_orgid
3146 AND lpn_id IN (SELECT lpn_id
3147 FROM wms_license_plate_numbers
3148 WHERE outermost_lpn_id = p_lpn_id);
3149 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3150 BEGIN
3151 OPEN l_cursor;
3152
3153 LOOP
3154 FETCH l_cursor INTO l_record;
3155 EXIT WHEN l_cursor%NOTFOUND;
3156
3157 IF inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_orgid, l_record.inventory_item_id, l_record.subinventory_code, l_record.locator_id, l_record.lot_number, l_record.serial_number, 'A') =
3158 'Y' THEN
3159 x_return := 'Y';
3160 ELSE
3161 x_return := 'N';
3162 RETURN x_return;
3163 END IF;
3164 END LOOP;
3165
3166 x_return := 'Y';
3167 CLOSE l_cursor;
3168 RETURN x_return;
3169 END vaildate_lpn_status;
3170
3171 -- Name: GET_CGUPDATE_SUBS
3172 --
3173 -- Input parameters:
3174 -- p_subinventory_code - restricts the subinventory to those like
3175 -- the user inputted text if given
3176 -- p_organization_id - restricts LOV SQL to current org
3177 -- p_inventory_item_id - restricts the subs to only those having
3178 -- this item.
3179 -- p_revision
3180 --
3181 -- Output parameters:
3182 -- x_cgupdate_sub_lov - Returns LOV rows as reference cursor
3183 --
3184 -- Functions: This API returns the valid subs associated with
3185 -- the Cost Group Update
3186 --
3187 -- Obsolete
3188 PROCEDURE get_cgupdate_subs(
3189 x_cgupdate_sub_lov OUT NOCOPY t_genref
3190 , p_subinventory_code IN VARCHAR2
3191 , p_organization_id IN NUMBER
3192 , p_inventory_item_id IN NUMBER
3193 , p_revision IN VARCHAR2
3194 ) IS
3195 l_debug NUMBER;
3196 BEGIN
3197 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3198 -- Include only those subinventories which are in the current org
3199 -- and include material with the item number entered
3200 OPEN x_cgupdate_sub_lov FOR
3201 SELECT DISTINCT moq.subinventory_code
3202 , '0'
3203 , msi.description
3204 , '0'
3205 , msi.lpn_controlled_flag
3206 , msi.enable_locator_alias
3207 FROM mtl_secondary_inventories msi, MTL_ONHAND_QUANTITIES_DETAIL moq
3208 WHERE msi.secondary_inventory_name = moq.subinventory_code
3209 AND msi.organization_id = moq.organization_id
3210 AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, moq.subinventory_code, NULL, NULL, NULL, 'Z') = 'Y'
3211 AND moq.containerized_flag = 2
3212 AND moq.subinventory_code LIKE (p_subinventory_code)
3213 AND (moq.revision = p_revision
3214 OR (moq.revision IS NULL
3215 AND p_revision IS NULL
3216 )
3217 )
3218 AND moq.inventory_item_id = p_inventory_item_id
3219 AND moq.organization_id = p_organization_id
3220 ORDER BY moq.subinventory_code;
3221 END get_cgupdate_subs;
3222
3223 -- Name: GET_CGUPDATE_LOCS
3224 --
3225 -- Input parameters:
3226 -- p_organization_id - restricts LOV SQL to current org
3227 -- p_subinventory_code - restricts LOV to the current subinventory
3228 -- p_concatenated_segments - restricts the locator to those that are
3229 -- similar to the user inputted text.
3230 -- locators are a key flex field so this
3231 -- is how the user represents/identifies locators
3232 -- p_inventory_item_id -
3233 -- p_revision
3234 --
3235 --
3236 -- Output parameters:
3237 -- x_locators - Returns LOV rows as reference cursor
3238 --
3239 -- Functions: This API returns the valid locators associated with a
3240 -- cycle count
3241 --
3242 /* PJM-WMS Integration:Return only the the physical locators.
3243 * Use the table mtl_item_locations instead of mtl_item_locations_kfv.
3244 * Use the function INV_PROJECT.get_locsegs() to retrieve the
3245 * concatenated segments.
3246 */
3247 PROCEDURE get_cgupdate_locs(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_concatenated_segments IN VARCHAR2, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2) IS
3248 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3249 BEGIN
3250 OPEN x_locators FOR
3251 SELECT moq.locator_id
3252 --, mil.concatenated_segments--Bug4398337:Commented this line and added below line
3253 , mil.locator_segments concatenated_segments
3254 , mil.description
3255 FROM wms_item_locations_kfv mil, MTL_ONHAND_QUANTITIES_DETAIL moq
3256 WHERE mil.concatenated_segments LIKE (p_concatenated_segments)
3257 AND mil.inventory_location_id = moq.locator_id
3258 AND mil.organization_id = p_organization_id
3259 -- Bug 2325664 AND mil.physical_location_id is null -- PJM-WMS Integration
3260 AND mil.project_id IS NULL
3261 AND mil.task_id IS NULL
3262 AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, moq.locator_id, NULL, NULL, 'L') = 'Y'
3263 AND moq.containerized_flag = 2
3264 AND (moq.revision = p_revision
3265 OR (moq.revision IS NULL
3266 AND p_revision IS NULL
3267 )
3268 )
3269 AND moq.inventory_item_id = p_inventory_item_id
3270 AND moq.locator_id IS NOT NULL
3271 AND moq.subinventory_code = p_subinventory_code
3272 AND moq.organization_id = p_organization_id
3273 GROUP BY moq.locator_id, mil.concatenated_segments, mil.description
3274 ORDER BY 2;
3275 END get_cgupdate_locs;
3276
3277 PROCEDURE get_cgupdate_locs(
3278 x_locators OUT NOCOPY t_genref,
3279 p_organization_id IN NUMBER,
3280 p_subinventory_code IN VARCHAR2,
3281 p_concatenated_segments IN VARCHAR2,
3282 p_inventory_item_id IN NUMBER,
3283 p_revision IN VARCHAR2,
3284 p_alias IN VARCHAR2
3285 ) IS
3286 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3287 BEGIN
3288 IF p_alias IS NULL THEN
3289 get_cgupdate_locs(
3290 x_locators => x_locators
3291 ,p_organization_id => p_organization_id
3292 ,p_subinventory_code => p_subinventory_code
3293 ,p_concatenated_segments => p_concatenated_segments
3294 ,p_inventory_item_id => p_inventory_item_id
3295 ,p_revision => p_revision
3296 );
3297 RETURN;
3298 END IF;
3299 OPEN x_locators FOR
3300 SELECT moq.locator_id
3301 --, mil.concatenated_segments--Bug4398337:Commented this line and added below line
3302 , mil.locator_segments concatenated_segments
3303 , mil.description
3304 FROM wms_item_locations_kfv mil, MTL_ONHAND_QUANTITIES_DETAIL moq
3305 WHERE alias = p_alias
3306 AND mil.inventory_location_id = moq.locator_id
3307 AND mil.organization_id = p_organization_id
3308 -- Bug 2325664 AND mil.physical_location_id is null -- PJM-WMS Integration
3309 AND mil.project_id IS NULL
3310 AND mil.task_id IS NULL
3311 AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, moq.locator_id, NULL, NULL, 'L') = 'Y'
3312 AND moq.containerized_flag = 2
3313 AND (moq.revision = p_revision
3314 OR (moq.revision IS NULL
3315 AND p_revision IS NULL
3316 )
3317 )
3318 AND moq.inventory_item_id = p_inventory_item_id
3319 AND moq.locator_id IS NOT NULL
3320 AND moq.subinventory_code = p_subinventory_code
3321 AND moq.organization_id = p_organization_id
3322 GROUP BY moq.locator_id, mil.concatenated_segments, mil.description
3323 ORDER BY 2;
3324 END get_cgupdate_locs;
3325
3326 -- Obsolete
3327 PROCEDURE get_with_all_subs(
3328 x_zones OUT NOCOPY t_genref
3329 , p_organization_id IN NUMBER
3330 , p_subinventory_code IN VARCHAR2
3331 ) IS
3332 l_debug NUMBER;
3333 BEGIN
3334 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3335 --bug#3440453 Remove the NVL on organization_id if user passes it.
3336 IF p_organization_id IS NULL THEN
3337 OPEN x_zones FOR
3338 SELECT secondary_inventory_name
3339 , NVL(locator_type, 1)
3340 , description
3341 , asset_inventory
3342 , 0 dummy
3343 , enable_locator_alias
3344 FROM mtl_secondary_inventories
3345 WHERE organization_id = NVL(p_organization_id, organization_id)
3346 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3347 AND secondary_inventory_name LIKE (p_subinventory_code)
3348 UNION ALL
3349 SELECT 'All Subinventories'
3350 , 0
3351 , ''
3352 , 0
3353 , 1 dummy
3354 , 'N' enable_locator_alias
3355 FROM DUAL
3356 WHERE 'All Subinventories' LIKE (p_subinventory_code)
3357 ORDER BY dummy DESC, secondary_inventory_name;
3358 ELSE -- Organization_id is not null
3359 OPEN x_zones FOR
3360 SELECT secondary_inventory_name
3361 , NVL(locator_type, 1)
3362 , description
3363 , asset_inventory
3364 , 0 dummy
3365 , enable_locator_alias
3366 FROM mtl_secondary_inventories
3367 WHERE organization_id = p_organization_id
3368 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3369 AND secondary_inventory_name LIKE (p_subinventory_code)
3370 UNION ALL
3371 SELECT 'All Subinventories'
3372 , 0
3373 , ''
3374 , 0
3375 , 1 dummy
3376 , 'N' enable_locator_alias
3377 FROM DUAL
3378 WHERE 'All Subinventories' LIKE (p_subinventory_code)
3379 ORDER BY dummy DESC, secondary_inventory_name;
3380 END IF;
3381
3382 END get_with_all_subs;
3383
3384 PROCEDURE get_with_all_loc(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_concatenated_segments IN VARCHAR2) IS
3385 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3386 BEGIN
3387 OPEN x_locators FOR
3388 SELECT milv.inventory_location_id
3389 --, milv.concatenated_segments --Bug4398337:Commented this line and added below line
3390 , milv.locator_segments concatenated_segments
3391 , milv.description
3392 , 0 dummy
3393 , mmsv.status_code
3394 FROM wms_item_locations_kfv milv, mtl_material_statuses_tl mmsv
3395 WHERE milv.organization_id = p_organization_id
3396 AND milv.subinventory_code = p_subinventory_code
3397 AND milv.concatenated_segments LIKE (p_concatenated_segments)
3398 AND (mmsv.status_id(+)/*Added outer join 2918529*/ = milv.status_id )
3399 AND mmsv.language(+) = userenv('LANG')
3400 UNION ALL
3401 SELECT 0
3402 , 'All Locators'
3403 , ''
3404 , 1 dummy
3405 , ''
3406 FROM DUAL
3407 WHERE 'All Locators' LIKE (p_concatenated_segments)
3408 ORDER BY dummy DESC, concatenated_segments;
3409 END get_with_all_loc;
3410
3411 /* Start of fix for bug # 5166308 */
3412 /* The following overloaded procedure has been added as a part of Locator Alias Project. */
3413
3414 PROCEDURE get_with_all_loc(x_locators OUT NOCOPY t_genref
3415 , p_organization_id IN NUMBER
3416 , p_subinventory_code IN VARCHAR2
3417 , p_concatenated_segments IN VARCHAR2
3418 , p_alias IN VARCHAR2) IS
3419 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3420 BEGIN
3421 IF (l_debug = 1) THEN
3422 DEBUG('Alias is '||p_alias);
3423 END IF;
3424 IF p_alias IS NULL THEN
3425 get_with_all_loc(x_locators => x_locators
3426 , p_organization_id => p_organization_id
3427 , p_subinventory_code => p_subinventory_code
3428 , p_concatenated_segments => p_concatenated_segments);
3429 RETURN;
3430 END IF;
3431 OPEN x_locators FOR
3432 SELECT milv.inventory_location_id
3433 --, milv.concatenated_segments --Bug4398337:Commented this line and added below line
3434 , milv.locator_segments concatenated_segments
3435 , milv.description
3436 , 0 dummy
3437 , mmsv.status_code
3438 FROM wms_item_locations_kfv milv, mtl_material_statuses_tl mmsv
3439 WHERE milv.organization_id = p_organization_id
3440 AND milv.subinventory_code = p_subinventory_code
3441 AND milv.alias = p_alias
3442 AND (mmsv.status_id(+)/*Added outer join 2918529*/ = milv.status_id )
3443 AND mmsv.language(+) = userenv('LANG');
3444 END get_with_all_loc;
3445 /* End of fix for bug # 5166308 */
3446
3447 PROCEDURE update_dynamic_locator(
3448 x_msg_count OUT NOCOPY NUMBER,
3449 x_msg_data OUT NOCOPY VARCHAR2,
3450 x_result OUT NOCOPY VARCHAR2,
3451 x_exist_or_create OUT NOCOPY VARCHAR2,
3452 p_locator_id IN NUMBER,
3453 p_org_id IN NUMBER,
3454 p_sub_code IN VARCHAR2) IS
3455
3456 PRAGMA AUTONOMOUS_TRANSACTION;
3457 l_sub_default_status NUMBER;
3458 l_sub_code VARCHAR2(10);
3459 l_wms_org BOOLEAN;
3460 l_loc_type NUMBER;
3461 l_return_status VARCHAR2(10);
3462 l_msg_count NUMBER;
3463 l_msg_data VARCHAR2(20);
3464 l_label_status VARCHAR2(20);
3465 l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
3466 l_required VARCHAR2(1) := 'N';
3467 l_project_id NUMBER;
3468 l_task_id NUMBER;
3469 l_picking_order NUMBER;
3470 l_return_value BOOLEAN := FALSE;
3471 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3472 BEGIN
3473 x_result := fnd_api.g_ret_sts_success;
3474
3475 --
3476 -- The LocatorKFF does an autonomous commit
3477 -- so a record is always present in MIL
3478 -- However a new Locator has a null subinventory
3479 -- code
3480 --
3481
3482 SELECT subinventory_code
3483 , project_id
3484 , task_id
3485 INTO l_sub_code
3486 , l_project_id
3487 , l_task_id
3488 FROM mtl_item_locations
3489 WHERE inventory_location_id = p_locator_id
3490 AND organization_id = p_org_id;
3491
3492 --
3493 -- If the Locator already exists then we dont
3494 -- need to do anything. Return immediatly
3495 --
3496
3497 IF l_sub_code IS NOT NULL THEN
3498 x_exist_or_create := 'EXISTS';
3499 GOTO success;
3500 END IF;
3501
3502 --
3503 -- For a New Locator ...
3504 -- Set X_EXIST_OR_CREATE is set to 'CREATE' and
3505 --
3506 -- If WMS is installed then the Locator must be
3507 -- assigned the Default Locator Status defined
3508 -- for the Subinventory and the Locator type is
3509 -- set to 'STORAGE_LOCATOR'
3510 -- i.e. MTL_LOCATOR_TYPES (MFG_LOOKUP)
3511 --
3512 -- Also a record must be inserted into Status
3513 -- history.
3514 --
3515
3516 x_exist_or_create := 'CREATE';
3517 update_locator(p_sub_code, p_org_id, p_locator_id);
3518 IF (l_debug = 1) THEN
3519 DEBUG('After inserting the default values');
3520 END IF;
3521 --
3522 -- Now that we have a complete valid row in MTL_ITEM_LOCATIONS
3523 -- we call the PJM Locator API to create the physical locator.
3524 -- This happens only if the physical locator does not already
3525 -- exist.
3526 --
3527 l_return_value := inv_projectlocator_pub.get_physical_location(p_organization_id => p_org_id, p_locator_id => p_locator_id);
3528
3529 IF NOT l_return_value THEN
3530 IF (l_debug = 1) THEN
3531 DEBUG('GET_PHYSICAL_LOCATION: ERROR');
3532 END IF;
3533 RAISE fnd_api.g_exc_unexpected_error;
3534 END IF;
3535
3536 --
3537 -- Print the Label for the new Locator
3538 --
3539 IF (l_debug = 1) THEN
3540 DEBUG('CALLING PRINTING');
3541 END IF;
3542 inv_label.print_label_manual_wrap(
3543 x_return_status => l_return_status
3544 , x_msg_count => l_msg_count
3545 , x_msg_data => l_msg_data
3546 , x_label_status => l_label_status
3547 , p_business_flow_code => 24
3548 , p_organization_id => p_org_id
3549 , p_subinventory_code => p_sub_code
3550 , p_locator_id => p_locator_id
3551 );
3552 IF (l_debug = 1) THEN
3553 DEBUG('AFTER CALLING PRINTING');
3554 END IF;
3555
3556 --
3557 -- Do not check the returns status of above API as the
3558 -- transaction should go through even though label
3559 -- printing failed.
3560 --
3561
3562 <<success>>
3563 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3564 x_result := fnd_api.g_ret_sts_success;
3565 COMMIT;
3566 EXCEPTION
3567 WHEN OTHERS THEN
3568 IF (l_debug = 1) THEN
3569 DEBUG(SQLERRM);
3570 END IF;
3571 x_result := fnd_api.g_ret_sts_unexp_error;
3572 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3573 ROLLBACK;
3574 END update_dynamic_locator;
3575
3576 -- Obsolete
3577 PROCEDURE get_valid_lpn_controlled_subs(
3578 x_zones OUT NOCOPY t_genref
3579 , p_organization_id IN NUMBER
3580 , p_subinventory_code IN VARCHAR2
3581 , p_txn_type_id IN NUMBER
3582 , p_wms_installed IN VARCHAR2
3583 ) IS
3584 l_debug NUMBER;
3585 BEGIN
3586 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3587 --bug#3440453 Remove the NVL on organization_id if user passes a value to it.
3588 IF p_organization_id IS NULL THEN
3589 OPEN x_zones FOR
3590 SELECT secondary_inventory_name
3591 , NVL(locator_type, 1)
3592 , description
3593 , asset_inventory
3594 , lpn_controlled_flag
3595 , enable_locator_alias
3596 FROM mtl_secondary_inventories
3597 WHERE organization_id = NVL(p_organization_id, organization_id)
3598 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3599 AND secondary_inventory_name LIKE (p_subinventory_code)
3600 AND lpn_controlled_flag = 1
3601 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
3602 ORDER BY secondary_inventory_name;
3603 ELSE -- Organization_id is not null
3604 OPEN x_zones FOR
3605 SELECT secondary_inventory_name
3606 , NVL(locator_type, 1)
3607 , description
3608 , asset_inventory
3609 , lpn_controlled_flag
3610 , enable_locator_alias
3611 FROM mtl_secondary_inventories
3612 WHERE organization_id = p_organization_id
3613 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3614 AND secondary_inventory_name LIKE (p_subinventory_code)
3615 AND lpn_controlled_flag = 1
3616 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
3617 ORDER BY secondary_inventory_name;
3618 END IF;
3619
3620 END get_valid_lpn_controlled_subs;
3621
3622 ------------------------------------------------
3623 -- GET_PRJ_LOC_LOV - Get Locators filtered
3624 -- on project and task
3625 ------------------------------------------------
3626 PROCEDURE get_prj_loc_lov(
3627 x_locators OUT NOCOPY t_genref
3628 , p_organization_id IN NUMBER
3629 , p_subinventory_code IN VARCHAR2
3630 , p_restrict_locators_code IN NUMBER
3631 , p_inventory_item_id IN NUMBER
3632 , p_concatenated_segments IN VARCHAR2
3633 , p_transaction_type_id IN NUMBER
3634 , p_wms_installed IN VARCHAR2
3635 , p_project_id IN NUMBER
3636 , p_task_id IN NUMBER
3637 ) IS
3638 x_return_status VARCHAR2(100);
3639 x_display VARCHAR2(100);
3640 x_project_col NUMBER;
3641 x_task_col NUMBER;
3642 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3643 l_ispjm_org VARCHAR2(1);
3644 l_sub_type NUMBER;
3645 BEGIN
3646
3647 BEGIN
3648 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
3649 INTO l_ispjm_org
3650 FROM pjm_org_parameters
3651 WHERE organization_id=p_organization_id;
3652 EXCEPTION
3653 WHEN NO_DATA_FOUND THEN
3654 l_ispjm_org:='N';
3655 END;
3656
3657 BEGIN
3658 SELECT Nvl(subinventory_type,1)
3659 INTO l_sub_type
3660 FROM mtl_secondary_inventories
3661 WHERE secondary_inventory_name = p_subinventory_code
3662 AND organization_id = p_organization_id;
3663 EXCEPTION
3664 WHEN OTHERS THEN
3665 l_sub_type := 1;
3666 END;
3667
3668 IF l_ispjm_org='N' THEN /*Non PJM Org*/
3669 IF p_Restrict_Locators_Code = 1 AND l_sub_type = 1 THEN --Locators restricted to predefined list
3670 OPEN x_Locators FOR
3671 select a.inventory_location_id,
3672 --a.concatenated_segments,--Bug4398337:Commented this line and added below line
3673 a.locator_segments concatenated_segments,
3674 nvl( a.description, -1)
3675 FROM wms_item_locations_kfv a,mtl_secondary_locators b
3676 WHERE b.organization_id = p_Organization_Id
3677 AND b.inventory_item_id = p_Inventory_Item_Id
3678 AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
3679 AND b.subinventory_code = p_Subinventory_Code
3680 AND a.inventory_location_id = b.secondary_locator
3681 AND a.concatenated_segments LIKE (p_concatenated_segments)
3682 /* BUG#2810405: To show only common locators in the LOV */
3683 AND inv_material_status_grp.is_status_applicable
3684 ( p_wms_installed,
3685 NULL,
3686 p_transaction_type_id,
3687 NULL,
3688 NULL,
3689 p_Organization_Id,
3690 p_Inventory_Item_Id,
3691 p_Subinventory_Code,
3692 a.inventory_location_id,
3693 NULL,
3694 NULL,
3695 'L') = 'Y'
3696 ORDER BY 2;
3697
3698 ELSE --Locators not restricted
3699 OPEN x_Locators FOR
3700 select inventory_location_id,
3701 --concatenated_segments,--Bug4398337:Commented this line and added below line
3702 locator_segments concatenated_segments,
3703 description
3704 FROM wms_item_locations_kfv
3705 WHERE organization_id = p_Organization_Id
3706 AND subinventory_code = p_Subinventory_Code
3707 AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
3708 AND concatenated_segments LIKE (p_concatenated_segments )
3709 /* BUG#2810405: To show only common locators in the LOV */
3710 AND inv_material_status_grp.is_status_applicable
3711 ( p_wms_installed,
3712 NULL,
3713 p_transaction_type_id,
3714 NULL,
3715 NULL,
3716 p_Organization_Id,
3717 p_Inventory_Item_Id,
3718 p_Subinventory_Code,
3719 inventory_location_id,
3720 NULL,
3721 NULL,
3722 'L') = 'Y'
3723 ORDER BY 2;
3724 END IF;
3725 ELSE /*PJM org*/
3726 IF p_Restrict_Locators_Code = 1 AND l_sub_type = 1 THEN --Locators restricted to predefined list
3727 OPEN x_Locators FOR
3728 select a.inventory_location_id,
3729 --a.concatenated_segments,--Bug4398337:Commented this line and added below line
3730 a.locator_segments concatenated_segments,
3731 nvl( a.description, -1)
3732 FROM wms_item_locations_kfv a,mtl_secondary_locators b
3733 WHERE b.organization_id = p_Organization_Id
3734 AND b.inventory_item_id = p_Inventory_Item_Id
3735 AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
3736 AND b.subinventory_code = p_Subinventory_Code
3737 AND a.inventory_location_id = b.secondary_locator
3738 AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
3739 AND a.concatenated_segments like (p_concatenated_segments )
3740 /* BUG#2810405: To show only common locators in the LOV */
3741 AND inv_material_status_grp.is_status_applicable
3742 ( p_wms_installed,
3743 NULL,
3744 p_transaction_type_id,
3745 NULL,
3746 NULL,
3747 p_Organization_Id,
3748 p_Inventory_Item_Id,
3749 p_Subinventory_Code,
3750 a.inventory_location_id,
3751 NULL,
3752 NULL,
3753 'L') = 'Y'
3754 ORDER BY 2;
3755
3756 ELSE --Locators not restricted
3757 OPEN x_Locators FOR
3758 select inventory_location_id,
3759 --concatenated_segments,--Bug4398337:Commented this line and added below line
3760 locator_segments concatenated_segments,
3761 description
3762 FROM wms_item_locations_kfv
3763 WHERE organization_id = p_Organization_Id
3764 AND subinventory_code = p_Subinventory_Code
3765 AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
3766 AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
3767 AND concatenated_segments LIKE (p_concatenated_segments )
3768 /* BUG#2810405: To show only common locators in the LOV */
3769 AND inv_material_status_grp.is_status_applicable
3770 ( p_wms_installed,
3771 NULL,
3772 p_transaction_type_id,
3773 NULL,
3774 NULL,
3775 p_Organization_Id,
3776 p_Inventory_Item_Id,
3777 p_Subinventory_Code,
3778 inventory_location_id,
3779 NULL,
3780 NULL,
3781 'L') = 'Y'
3782 ORDER BY 2;
3783 END IF;
3784 END IF;
3785 END get_prj_loc_lov;
3786 /**
3787 * For Locator alias project
3788 */
3789 PROCEDURE get_prj_loc_lov(
3790 x_locators OUT NOCOPY t_genref
3791 , p_organization_id IN NUMBER
3792 , p_subinventory_code IN VARCHAR2
3793 , p_restrict_locators_code IN NUMBER
3794 , p_inventory_item_id IN NUMBER
3795 , p_concatenated_segments IN VARCHAR2
3796 , p_transaction_type_id IN NUMBER
3797 , p_wms_installed IN VARCHAR2
3798 , p_project_id IN NUMBER
3799 , p_task_id IN NUMBER
3800 , p_alias IN VARCHAR2
3801 ) IS
3802 x_return_status VARCHAR2(100);
3803 x_display VARCHAR2(100);
3804 x_project_col NUMBER;
3805 x_task_col NUMBER;
3806 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3807 l_ispjm_org VARCHAR2(1);
3808 l_sub_type NUMBER;
3809 BEGIN
3810
3811 IF (l_debug = 1) THEN
3812 DEBUG('Alias is '||p_alias);
3813 END IF;
3814 IF p_alias IS NULL THEN
3815 get_prj_loc_lov(
3816 x_locators => x_locators
3817 , p_organization_id => p_organization_id
3818 , p_subinventory_code => p_subinventory_code
3819 , p_restrict_locators_code => p_restrict_locators_code
3820 , p_inventory_item_id => p_inventory_item_id
3821 , p_concatenated_segments => p_concatenated_segments
3822 , p_transaction_type_id => p_transaction_type_id
3823 , p_wms_installed => p_wms_installed
3824 , p_project_id => p_project_id
3825 , p_task_id => p_task_id
3826 );
3827 RETURN;
3828 END IF;
3829
3830 BEGIN
3831 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
3832 INTO l_ispjm_org
3833 FROM pjm_org_parameters
3834 WHERE organization_id=p_organization_id;
3835 EXCEPTION
3836 WHEN NO_DATA_FOUND THEN
3837 l_ispjm_org:='N';
3838 END;
3839
3840 BEGIN
3841 SELECT Nvl(subinventory_type,1)
3842 INTO l_sub_type
3843 FROM mtl_secondary_inventories
3844 WHERE secondary_inventory_name = p_subinventory_code
3845 AND organization_id = p_organization_id;
3846 EXCEPTION
3847 WHEN OTHERS THEN
3848 l_sub_type := 1;
3849 END;
3850
3851 IF l_ispjm_org='N' THEN /*Non PJM Org*/
3852 IF p_Restrict_Locators_Code = 1 AND l_sub_type = 1 THEN --Locators restricted to predefined list
3853 OPEN x_Locators FOR
3854 select a.inventory_location_id,
3855 --a.concatenated_segments,--Bug4398337:Commented this line and added below line
3856 a.locator_segments concatenated_segments,
3857 nvl( a.description, -1)
3858 FROM wms_item_locations_kfv a,mtl_secondary_locators b
3859 WHERE b.organization_id = p_Organization_Id
3860 AND b.inventory_item_id = p_Inventory_Item_Id
3861 AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
3862 AND b.subinventory_code = p_Subinventory_Code
3863 AND a.inventory_location_id = b.secondary_locator
3864 -- AND a.concatenated_segments LIKE (p_concatenated_segments)
3865 AND a.alias = p_alias
3866 /* BUG#2810405: To show only common locators in the LOV */
3867 AND inv_material_status_grp.is_status_applicable
3868 ( p_wms_installed,
3869 NULL,
3870 p_transaction_type_id,
3871 NULL,
3872 NULL,
3873 p_Organization_Id,
3874 p_Inventory_Item_Id,
3875 p_Subinventory_Code,
3876 a.inventory_location_id,
3877 NULL,
3878 NULL,
3879 'L') = 'Y'
3880 ORDER BY 2;
3881
3882 ELSE --Locators not restricted
3883 OPEN x_Locators FOR
3884 select inventory_location_id,
3885 --concatenated_segments,--Bug4398337:Commented this line and added below line
3886 locator_segments concatenated_segments,
3887 description
3888 FROM wms_item_locations_kfv
3889 WHERE organization_id = p_Organization_Id
3890 AND subinventory_code = p_Subinventory_Code
3891 AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
3892 -- AND concatenated_segments LIKE (p_concatenated_segments )
3893 AND alias = p_alias
3894 /* BUG#2810405: To show only common locators in the LOV */
3895 AND inv_material_status_grp.is_status_applicable
3896 ( p_wms_installed,
3897 NULL,
3898 p_transaction_type_id,
3899 NULL,
3900 NULL,
3901 p_Organization_Id,
3902 p_Inventory_Item_Id,
3903 p_Subinventory_Code,
3904 inventory_location_id,
3905 NULL,
3906 NULL,
3907 'L') = 'Y'
3908 ORDER BY 2;
3909 END IF;
3910 ELSE /*PJM org*/
3911 IF p_Restrict_Locators_Code = 1 AND l_sub_type = 1 THEN --Locators restricted to predefined list
3912 OPEN x_Locators FOR
3913 select a.inventory_location_id,
3914 --a.concatenated_segments,--Bug4398337:Commented this line and added below line
3915 a.locator_segments concatenated_segments,
3916 nvl( a.description, -1)
3917 FROM wms_item_locations_kfv a,mtl_secondary_locators b
3918 WHERE b.organization_id = p_Organization_Id
3919 AND b.inventory_item_id = p_Inventory_Item_Id
3920 AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
3921 AND b.subinventory_code = p_Subinventory_Code
3922 AND a.inventory_location_id = b.secondary_locator
3923 AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
3924 -- AND a.concatenated_segments like (p_concatenated_segments )
3925 AND a.alias = p_alias
3926 /* BUG#2810405: To show only common locators in the LOV */
3927 AND inv_material_status_grp.is_status_applicable
3928 ( p_wms_installed,
3929 NULL,
3930 p_transaction_type_id,
3931 NULL,
3932 NULL,
3933 p_Organization_Id,
3934 p_Inventory_Item_Id,
3935 p_Subinventory_Code,
3936 a.inventory_location_id,
3937 NULL,
3938 NULL,
3939 'L') = 'Y'
3940 ORDER BY 2;
3941
3942 ELSE --Locators not restricted
3943 OPEN x_Locators FOR
3944 select inventory_location_id,
3945 --concatenated_segments,--Bug4398337:Commented this line and added below line
3946 locator_segments concatenated_segments,
3947 description
3948 FROM wms_item_locations_kfv
3949 WHERE organization_id = p_Organization_Id
3950 AND subinventory_code = p_Subinventory_Code
3951 AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
3952 AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
3953 -- AND concatenated_segments LIKE (p_concatenated_segments )
3954 AND alias = p_alias
3955 /* BUG#2810405: To show only common locators in the LOV */
3956 AND inv_material_status_grp.is_status_applicable
3957 ( p_wms_installed,
3958 NULL,
3959 p_transaction_type_id,
3960 NULL,
3961 NULL,
3962 p_Organization_Id,
3963 p_Inventory_Item_Id,
3964 p_Subinventory_Code,
3965 inventory_location_id,
3966 NULL,
3967 NULL,
3968 'L') = 'Y'
3969 ORDER BY 2;
3970 END IF;
3971 END IF;
3972 END get_prj_loc_lov;
3973
3974 PROCEDURE get_valid_prj_to_locs(
3975 x_locators OUT NOCOPY t_genref
3976 , p_transaction_action_id IN NUMBER
3977 , p_to_organization_id IN NUMBER
3978 , p_organization_id IN NUMBER
3979 , p_subinventory_code IN VARCHAR2
3980 , p_restrict_locators_code IN NUMBER
3981 , p_inventory_item_id IN NUMBER
3982 , p_concatenated_segments IN VARCHAR2
3983 , p_transaction_type_id IN NUMBER
3984 , p_wms_installed IN VARCHAR2
3985 , p_project_id IN NUMBER
3986 , p_task_id IN NUMBER
3987 , p_alias IN VARCHAR2
3988 ) IS
3989 l_org NUMBER;
3990 l_restrict_locators_code NUMBER;
3991 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3992 BEGIN
3993
3994 IF p_alias IS NULL THEN
3995 get_valid_prj_to_locs(
3996 x_locators => x_locators
3997 , p_transaction_action_id => p_transaction_action_id
3998 , p_to_organization_id => p_to_organization_id
3999 , p_organization_id => p_organization_id
4000 , p_subinventory_code => p_subinventory_code
4001 , p_restrict_locators_code => p_restrict_locators_code
4002 , p_inventory_item_id => p_inventory_item_id
4003 , p_concatenated_segments => p_concatenated_segments
4004 , p_transaction_type_id => p_transaction_type_id
4005 , p_wms_installed => p_wms_installed
4006 , p_project_id => p_project_id
4007 , p_task_id => p_task_id
4008 );
4009 END IF;
4010 IF p_transaction_action_id IN (3, 21) THEN
4011 l_org := p_to_organization_id;
4012
4013 SELECT restrict_locators_code
4014 INTO l_restrict_locators_code
4015 FROM mtl_system_items
4016 WHERE inventory_item_id = p_inventory_item_id
4017 AND organization_id = l_org;
4018 ELSE
4019 l_org := p_organization_id;
4020 l_restrict_locators_code := p_restrict_locators_code;
4021 END IF;
4022
4023 get_prj_loc_lov(
4024 x_locators => x_locators
4025 , p_organization_id => l_org
4026 , p_subinventory_code => p_subinventory_code
4027 , p_restrict_locators_code => l_restrict_locators_code
4028 , p_inventory_item_id => p_inventory_item_id
4029 , p_concatenated_segments => p_concatenated_segments
4030 , p_transaction_type_id => p_transaction_type_id
4031 , p_wms_installed => p_wms_installed
4032 , p_project_id => p_project_id
4033 , p_task_id => p_task_id
4034 , p_alias => p_alias
4035 );
4036 END get_valid_prj_to_locs;
4037 --
4038 --
4039 PROCEDURE get_valid_prj_to_locs(
4040 x_locators OUT NOCOPY t_genref
4041 , p_transaction_action_id IN NUMBER
4042 , p_to_organization_id IN NUMBER
4043 , p_organization_id IN NUMBER
4044 , p_subinventory_code IN VARCHAR2
4045 , p_restrict_locators_code IN NUMBER
4046 , p_inventory_item_id IN NUMBER
4047 , p_concatenated_segments IN VARCHAR2
4048 , p_transaction_type_id IN NUMBER
4049 , p_wms_installed IN VARCHAR2
4050 , p_project_id IN NUMBER
4051 , p_task_id IN NUMBER
4052 ) IS
4053 l_org NUMBER;
4054 l_restrict_locators_code NUMBER;
4055 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4056 BEGIN
4057 IF p_transaction_action_id IN (3, 21) THEN
4058 l_org := p_to_organization_id;
4059
4060 SELECT restrict_locators_code
4061 INTO l_restrict_locators_code
4062 FROM mtl_system_items
4063 WHERE inventory_item_id = p_inventory_item_id
4064 AND organization_id = l_org;
4065 ELSE
4066 l_org := p_organization_id;
4067 l_restrict_locators_code := p_restrict_locators_code;
4068 END IF;
4069
4070 get_prj_loc_lov(
4071 x_locators => x_locators
4072 , p_organization_id => l_org
4073 , p_subinventory_code => p_subinventory_code
4074 , p_restrict_locators_code => l_restrict_locators_code
4075 , p_inventory_item_id => p_inventory_item_id
4076 , p_concatenated_segments => p_concatenated_segments
4077 , p_transaction_type_id => p_transaction_type_id
4078 , p_wms_installed => p_wms_installed
4079 , p_project_id => p_project_id
4080 , p_task_id => p_task_id
4081 );
4082 END get_valid_prj_to_locs;
4083
4084 --
4085 --
4086 PROCEDURE get_prj_lpnloc_lov(
4087 x_locators OUT NOCOPY t_genref
4088 , p_organization_id IN NUMBER
4089 , p_lpn_id IN NUMBER
4090 , p_subinventory_code IN VARCHAR2
4091 , p_concatenated_segments IN VARCHAR2
4092 , p_transaction_type_id IN NUMBER
4093 , p_wms_installed IN VARCHAR2
4094 , p_project_id IN NUMBER
4095 , p_task_id IN NUMBER
4096 ) IS
4097 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4098 BEGIN
4099 /*Bug 2769628: Restricted the LOV to list only common locators and anot logical locators*/
4100 OPEN x_locators FOR
4101 SELECT inventory_location_id
4102 --, concatenated_segments--Bug4398337:Commented this line and added below line
4103 , locator_segments concatenated_segments
4104 , description
4105 FROM wms_item_locations_kfv
4106 WHERE organization_id = p_organization_id
4107 AND subinventory_code = p_subinventory_code
4108 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4109 AND concatenated_segments LIKE (p_concatenated_segments)
4110 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4111 AND inv_ui_item_sub_loc_lovs.vaildate_lpn_toloc(p_lpn_id, p_subinventory_code, p_organization_id, inventory_location_id, p_wms_installed, p_transaction_type_id) = 'Y';
4112 END get_prj_lpnloc_lov;
4113 PROCEDURE get_prj_lpnloc_lov(
4114 x_locators OUT NOCOPY t_genref
4115 , p_organization_id IN NUMBER
4116 , p_lpn_id IN NUMBER
4117 , p_subinventory_code IN VARCHAR2
4118 , p_concatenated_segments IN VARCHAR2
4119 , p_transaction_type_id IN NUMBER
4120 , p_wms_installed IN VARCHAR2
4121 , p_project_id IN NUMBER
4122 , p_task_id IN NUMBER
4123 , p_alias IN VARCHAR2
4124 ) IS
4125 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4126 BEGIN
4127
4128 IF p_alias IS NULL THEN
4129 get_prj_lpnloc_lov(
4130 x_locators => x_locators
4131 , p_organization_id => p_organization_id
4132 , p_lpn_id => p_lpn_id
4133 , p_subinventory_code => p_subinventory_code
4134 , p_concatenated_segments => p_concatenated_segments
4135 , p_transaction_type_id => p_transaction_type_id
4136 , p_wms_installed => p_wms_installed
4137 , p_project_id => p_project_id
4138 , p_task_id => p_task_id
4139 );
4140 RETURN;
4141 END IF;
4142 /*Bug 2769628: Restricted the LOV to list only common locators and anot logical locators*/
4143 OPEN x_locators FOR
4144 SELECT inventory_location_id
4145 --, concatenated_segments--Bug4398337:Commented this line and added below line
4146 , locator_segments concatenated_segments
4147 , description
4148 FROM wms_item_locations_kfv
4149 WHERE organization_id = p_organization_id
4150 AND subinventory_code = p_subinventory_code
4151 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4152 AND alias = p_alias
4153 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4154 AND inv_ui_item_sub_loc_lovs.vaildate_lpn_toloc(p_lpn_id, p_subinventory_code, p_organization_id, inventory_location_id, p_wms_installed, p_transaction_type_id) = 'Y';
4155 END get_prj_lpnloc_lov;
4156
4157
4158 -- This procedure is used for user directed putaway
4159 -- to get the LOV cursor for the Subinventory
4160 --- obsolete
4161 PROCEDURE get_userput_subs(
4162 x_sub OUT NOCOPY t_genref
4163 , p_organization_id IN NUMBER
4164 , p_subinventory_code IN VARCHAR2
4165 , p_lpn_id IN NUMBER
4166 , p_lpn_context IN NUMBER
4167 , p_rcv_sub_only IN NUMBER
4168 ) IS
4169 l_debug NUMBER;
4170 BEGIN
4171 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4172 IF (l_debug = 1) THEN
4173 DEBUG('Entering get_userput_subs:');
4174 DEBUG(' p_rcv_sub_only ====> ' || p_rcv_sub_only);
4175 END IF;
4176
4177 IF (p_lpn_context in (2,3)) THEN
4178 IF (p_rcv_sub_only = 2) THEN
4179 -- Include both RCV and INV subs, with no restriction on INV subs
4180 OPEN x_sub FOR
4181 SELECT secondary_inventory_name
4182 , NVL(locator_type, 1)
4183 , description
4184 , asset_inventory
4185 , lpn_controlled_flag
4186 , Nvl(subinventory_type,1)
4187 , reservable_type
4188 , enable_locator_alias
4189 FROM mtl_secondary_inventories
4190 WHERE organization_id = p_organization_id
4191 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4192 AND secondary_inventory_name LIKE (p_subinventory_code)
4193 AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4194 secondary_inventory_name,
4195 p_lpn_id) = 'Y'
4196 UNION ALL
4197 SELECT msub.secondary_inventory_name
4198 , NVL(msub.locator_type, 1)
4199 , msub.description
4200 , msub.asset_inventory
4201 , lpn_controlled_flag
4202 , Nvl(subinventory_type,1)
4203 , reservable_type
4204 , enable_locator_alias
4205 FROM mtl_secondary_inventories msub
4206 WHERE organization_id = p_organization_id
4207 AND Nvl(subinventory_type,1) = 2
4208 AND msub.secondary_inventory_name LIKE (p_subinventory_code)
4209 AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
4210 disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
4211 ORDER BY 1;
4212 ELSIF (p_rcv_sub_only = 1 OR p_rcv_sub_only IS NULL) THEN
4213 -- Only include RCV subs.
4214 OPEN x_sub FOR
4215 SELECT msub.secondary_inventory_name
4216 , NVL(msub.locator_type, 1)
4217 , msub.description
4218 , msub.asset_inventory
4219 , lpn_controlled_flag
4220 , Nvl(subinventory_type,1)
4221 , reservable_type
4222 , enable_locator_alias
4223 FROM mtl_secondary_inventories msub
4224 WHERE organization_id = p_organization_id
4225 AND Nvl(subinventory_type,1) = 2
4226 AND msub.secondary_inventory_name LIKE (p_subinventory_code)
4227 AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
4228 disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
4229 ORDER BY 1;
4230 ELSIF (p_rcv_sub_only = 3) THEN
4231 -- Only include inventory subs, with no restrictions
4232 OPEN x_sub FOR
4233 SELECT secondary_inventory_name
4234 , NVL(locator_type, 1)
4235 , description
4236 , asset_inventory
4237 , lpn_controlled_flag
4238 , Nvl(subinventory_type,1)
4239 , reservable_type
4240 , enable_locator_alias
4241 FROM mtl_secondary_inventories
4242 WHERE organization_id = p_organization_id
4243 AND Nvl(subinventory_type,1) = 1
4244 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4245 AND secondary_inventory_name LIKE (p_subinventory_code)
4246 AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4247 secondary_inventory_name,
4248 p_lpn_id) = 'Y';
4249 ELSIF (p_rcv_sub_only = 4) THEN
4250 -- SO XDOCK
4251 -- Only include inventory subs that are reservable and LPN controlled
4252 OPEN x_sub FOR
4253 SELECT secondary_inventory_name
4254 , NVL(locator_type, 1)
4255 , description
4256 , asset_inventory
4257 , lpn_controlled_flag
4258 , Nvl(subinventory_type,1)
4259 , reservable_type
4260 , enable_locator_alias
4261 FROM mtl_secondary_inventories
4262 WHERE organization_id = p_organization_id
4263 AND Nvl(subinventory_type,1) = 1
4264 AND lpn_controlled_flag = 1
4265 AND reservable_type = 1
4266 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4267 AND secondary_inventory_name LIKE (p_subinventory_code)
4268 AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4269 secondary_inventory_name,
4270 p_lpn_id) = 'Y';
4271 ELSIF (p_rcv_sub_only = 5) THEN
4272 -- Only include INV Subs that are non-reservable and non-LPN-Controlled
4273 OPEN x_sub FOR
4274 SELECT secondary_inventory_name
4275 , NVL(locator_type, 1)
4276 , description
4277 , asset_inventory
4278 , lpn_controlled_flag
4279 , Nvl(subinventory_type,1)
4280 , reservable_type
4281 , enable_locator_alias
4282 FROM mtl_secondary_inventories
4283 WHERE organization_id = p_organization_id
4284 AND Nvl(subinventory_type,1) = 1
4285 AND lpn_controlled_flag = 2
4286 AND reservable_type = 2
4287 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4288 AND secondary_inventory_name LIKE (p_subinventory_code)
4289 AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4290 secondary_inventory_name,
4291 p_lpn_id) = 'Y';
4292
4293 ELSIF (p_rcv_sub_only = 6) THEN
4294 -- Include RCV Subs and INV subs that are reservable and LPN Controlled
4295 OPEN x_sub FOR
4296 SELECT secondary_inventory_name
4297 , NVL(locator_type, 1)
4298 , description
4299 , asset_inventory
4300 , lpn_controlled_flag
4301 , Nvl(subinventory_type,1)
4302 , reservable_type
4303 , enable_locator_alias
4304 FROM mtl_secondary_inventories
4305 WHERE organization_id = p_organization_id
4306 AND Nvl(subinventory_type,1) = 1
4307 AND lpn_controlled_flag = 1
4308 AND reservable_type = 1
4309 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4310 AND secondary_inventory_name LIKE (p_subinventory_code)
4311 AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4312 secondary_inventory_name,
4313 p_lpn_id) = 'Y'
4314 UNION ALL
4315 SELECT msub.secondary_inventory_name
4316 , NVL(msub.locator_type, 1)
4317 , msub.description
4318 , msub.asset_inventory
4319 , lpn_controlled_flag
4320 , Nvl(subinventory_type,1)
4321 , reservable_type
4322 , enable_locator_alias
4323 FROM mtl_secondary_inventories msub
4324 WHERE organization_id = p_organization_id
4325 AND Nvl(subinventory_type,1) = 2
4326 AND msub.secondary_inventory_name LIKE (p_subinventory_code)
4327 AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
4328 disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
4329 ORDER BY 1;
4330 ELSIF (p_rcv_sub_only = 7) THEN
4331 -- Include RCV Subs and INV subs that are non-reservable and non-LPN-Controlled
4332 OPEN x_sub FOR
4333 SELECT secondary_inventory_name
4334 , NVL(locator_type, 1)
4335 , description
4336 , asset_inventory
4337 , lpn_controlled_flag
4338 , Nvl(subinventory_type,1)
4339 , reservable_type
4340 , enable_locator_alias
4341 FROM mtl_secondary_inventories
4342 WHERE organization_id = p_organization_id
4343 AND Nvl(subinventory_type,1) = 1
4344 AND lpn_controlled_flag = 2
4345 AND reservable_type = 2
4346 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4347 AND secondary_inventory_name LIKE (p_subinventory_code)
4348 AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4349 secondary_inventory_name,
4350 p_lpn_id) = 'Y'
4351 UNION ALL
4352 SELECT msub.secondary_inventory_name
4353 , NVL(msub.locator_type, 1)
4354 , msub.description
4355 , msub.asset_inventory
4356 , lpn_controlled_flag
4357 , Nvl(subinventory_type,1)
4358 , reservable_type
4359 , enable_locator_alias
4360 FROM mtl_secondary_inventories msub
4361 WHERE organization_id = p_organization_id
4362 AND Nvl(subinventory_type,1) = 2
4363 AND msub.secondary_inventory_name LIKE (p_subinventory_code)
4364 AND (trunc(disable_date + (300*365)) >= trunc(SYSDATE) OR
4365 disable_date = TO_DATE('01/01/1700','DD/MM/RRRR'))
4366 ORDER BY 1;
4367 END IF;
4368 ELSE
4369 -- Non-receiving LPN case
4370 OPEN x_sub FOR
4371 SELECT secondary_inventory_name
4372 , NVL(locator_type, 1)
4373 , description
4374 , asset_inventory
4375 , lpn_controlled_flag
4376 , Nvl(subinventory_type,1)
4377 , reservable_type
4378 , enable_locator_alias
4379 FROM mtl_secondary_inventories
4380 WHERE organization_id = p_organization_id
4381 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4382 AND secondary_inventory_name LIKE (p_subinventory_code)
4383 AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4384 secondary_inventory_name,
4385 p_lpn_id) = 'Y'
4386 ORDER BY UPPER(secondary_inventory_name);
4387 END IF;
4388
4389 END get_userput_subs;
4390
4391
4392 -- This function will validate the LPN for item/sub restrictions
4393 -- and also for sub material status for each move order line transaction.
4394 -- This function should only be called from the procedure
4395 -- get_userput_subs in this package: INV_UI_ITEM_SUB_LOC_LOVS
4396 FUNCTION validate_lpn_sub(
4397 p_organization_id IN NUMBER
4398 , p_subinventory_code IN VARCHAR2
4399 , p_lpn_id IN NUMBER)
4400 RETURN VARCHAR2 IS
4401 x_return VARCHAR(1);
4402 l_item_id NUMBER;
4403 l_restrict_sub NUMBER;
4404 l_transaction_type NUMBER;
4405 l_count NUMBER;
4406
4407 CURSOR l_item_cursor IS
4408 SELECT DISTINCT wlc.inventory_item_id
4409 , msi.restrict_subinventories_code
4410 FROM wms_lpn_contents wlc, mtl_system_items msi
4411 WHERE wlc.parent_lpn_id IN(SELECT lpn_id
4412 FROM wms_license_plate_numbers
4413 WHERE outermost_lpn_id = p_lpn_id)
4414 AND wlc.inventory_item_id IS NOT NULL
4415 AND wlc.inventory_item_id = msi.inventory_item_id
4416 AND msi.organization_id = p_organization_id;
4417
4418 CURSOR l_item_txn_cursor IS
4419 SELECT inventory_item_id
4420 , transaction_type_id
4421 FROM mtl_txn_request_lines
4422 WHERE organization_id = p_organization_id
4423 AND lpn_id = p_lpn_id;
4424
4425 l_debug NUMBER := nvl(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4426 BEGIN
4427 -- Initialize the return variable
4428 x_return := 'Y';
4429 -- Loop through each item packed within the LPN
4430 OPEN l_item_cursor;
4431
4432 LOOP
4433 FETCH l_item_cursor INTO l_item_id, l_restrict_sub;
4434 EXIT WHEN l_item_cursor%NOTFOUND;
4435
4436 -- If the item restricts subinventories,
4437 -- check that the sub is allowed
4438 IF (l_restrict_sub = 1) THEN
4439 SELECT COUNT(*)
4440 INTO l_count
4441 FROM mtl_item_sub_inventories
4442 WHERE organization_id = p_organization_id
4443 AND inventory_item_id = l_item_id
4444 AND secondary_inventory = p_subinventory_code;
4445
4446 -- No rows returned so the sub is not valid
4447 -- for the current item
4448 IF (l_count = 0) THEN
4449 x_return := 'N';
4450 EXIT;
4451 END IF;
4452 END IF;
4453 END LOOP;
4454
4455 CLOSE l_item_cursor;
4456
4457 -- Sub has already failed item/sub restrictions
4458 -- so no need to do any further validation.
4459 IF (x_return = 'N') THEN
4460 RETURN x_return;
4461 END IF;
4462
4463 -- Loop through each move order line for the LPN
4464 OPEN l_item_txn_cursor;
4465
4466 LOOP
4467 FETCH l_item_txn_cursor INTO l_item_id, l_transaction_type;
4468 EXIT WHEN l_item_txn_cursor%NOTFOUND;
4469 -- Check if the sub's material status is valid
4470 -- for the current move order line's transaction type
4471 x_return := inv_material_status_grp.is_status_applicable(p_wms_installed => 'TRUE', p_trx_status_enabled => NULL, p_trx_type_id => l_transaction_type
4472 , p_lot_status_enabled => NULL, p_serial_status_enabled => NULL, p_organization_id => p_organization_id
4473 , p_inventory_item_id => l_item_id, p_sub_code => p_subinventory_code, p_locator_id => NULL, p_lot_number => NULL
4474 , p_serial_number => NULL, p_object_type => 'Z');
4475
4476 -- The function returned 'N' so the sub is not valid
4477 -- for the transaction type in the current move order line
4478 IF (x_return = 'N') THEN
4479 EXIT;
4480 END IF;
4481 END LOOP;
4482
4483 CLOSE l_item_txn_cursor;
4484 -- If all of the items in the LPN passed validation
4485 -- for the given sub, the return variable should be 'Y'
4486 RETURN x_return;
4487 EXCEPTION
4488 WHEN OTHERS THEN
4489 IF (l_debug = 1) THEN
4490 DEBUG('Exception occurred in function validate_lpn_sub!');
4491 END IF;
4492
4493 x_return := 'N';
4494 RETURN x_return;
4495 END validate_lpn_sub;
4496
4497 PROCEDURE get_userput_locs
4498 (x_locators OUT NOCOPY t_genref ,
4499 p_organization_id IN NUMBER ,
4500 p_subinventory_code IN VARCHAR2 ,
4501 p_concatenated_segments IN VARCHAR2 ,
4502 p_project_id IN NUMBER ,
4503 p_task_id IN NUMBER ,
4504 p_lpn_id IN NUMBER ,
4505 p_alias IN VARCHAR2
4506 ) IS
4507 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4508 BEGIN
4509 /*Bug 2769628:To list all the common locators and not the logical locators*/
4510 IF p_alias IS NULL THEN
4511 get_userput_locs(
4512 x_locators => x_locators
4513 ,p_organization_id => p_organization_id
4514 ,p_subinventory_code => p_subinventory_code
4515 ,p_concatenated_segments => p_concatenated_segments
4516 ,p_project_id => p_project_id
4517 ,p_task_id => p_task_id
4518 ,p_lpn_id => p_lpn_id
4519 );
4520 RETURN;
4521 END IF;
4522 OPEN x_locators FOR
4523 SELECT inventory_location_id
4524 -- , concatenated_segments--Bug4398337:Commented this line and added below line
4525 , locator_segments concatenated_segments
4526 , description
4527 , inventory_location_type
4528 FROM wms_item_locations_kfv
4529 WHERE organization_id = p_organization_id
4530 AND subinventory_code = p_subinventory_code
4531 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4532 AND alias = p_alias
4533 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4534 AND inv_ui_item_sub_loc_lovs.validate_lpn_loc(p_organization_id,
4535 p_subinventory_code,
4536 inventory_location_id,
4537 p_lpn_id) = 'Y'
4538 ORDER BY 2;
4539 END get_userput_locs;
4540 PROCEDURE get_userput_locs
4541 (x_locators OUT NOCOPY t_genref ,
4542 p_organization_id IN NUMBER ,
4543 p_subinventory_code IN VARCHAR2 ,
4544 p_concatenated_segments IN VARCHAR2 ,
4545 p_project_id IN NUMBER ,
4546 p_task_id IN NUMBER ,
4547 p_lpn_id IN NUMBER
4548 ) IS
4549 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4550 BEGIN
4551 /*Bug 2769628:To list all the common locators and not the logical locators*/
4552 OPEN x_locators FOR
4553 SELECT inventory_location_id
4554 -- , concatenated_segments--Bug4398337:Commented this line and added below line
4555 , locator_segments concatenated_segments
4556 , description
4557 , inventory_location_type
4558 FROM wms_item_locations_kfv
4559 WHERE organization_id = p_organization_id
4560 AND subinventory_code = p_subinventory_code
4561 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4562 AND concatenated_segments LIKE (p_concatenated_segments)
4563 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4564 AND inv_ui_item_sub_loc_lovs.validate_lpn_loc(p_organization_id,
4565 p_subinventory_code,
4566 inventory_location_id,
4567 p_lpn_id) = 'Y'
4568 ORDER BY 2;
4569 END get_userput_locs;
4570
4571 -- This function will validate the LPN for item/sub/loc restrictions
4572 -- and also for locator material status for each move order line transaction.
4573 -- This function should only be called from the procedure
4574 -- get_userput_locs in this package: INV_UI_ITEM_SUB_LOC_LOVS
4575 FUNCTION validate_lpn_loc(p_organization_id IN NUMBER ,
4576 p_subinventory_code IN VARCHAR2 ,
4577 p_locator_id IN NUMBER ,
4578 p_lpn_id IN NUMBER)
4579 RETURN VARCHAR2 IS
4580 x_return VARCHAR(1);
4581 l_item_id NUMBER;
4582 l_restrict_loc NUMBER;
4583 l_transaction_type NUMBER;
4584 l_count NUMBER;
4585 CURSOR l_item_cursor IS
4586 SELECT DISTINCT wlc.inventory_item_id, msi.restrict_locators_code
4587 FROM wms_lpn_contents wlc, mtl_system_items msi
4588 WHERE wlc.parent_lpn_id IN (SELECT lpn_id
4589 FROM wms_license_plate_numbers
4590 WHERE outermost_lpn_id = p_lpn_id)
4591 AND wlc.inventory_item_id IS NOT NULL
4592 AND wlc.inventory_item_id = msi.inventory_item_id
4593 AND msi.organization_id = p_organization_id;
4594 CURSOR l_item_txn_cursor IS
4595 SELECT inventory_item_id, transaction_type_id
4596 FROM mtl_txn_request_lines
4597 WHERE organization_id = p_organization_id
4598 AND lpn_id = p_lpn_id;
4599 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4600 l_sub_type NUMBER;
4601 BEGIN
4602 -- Initialize the return variable
4603 x_return := 'Y';
4604
4605 -- get the sub type and based on it set the loc_type
4606 BEGIN
4607 SELECT nvl(subinventory_type,1)
4608 INTO l_sub_type
4609 FROM mtl_secondary_inventories
4610 WHERE organization_id = p_organization_id
4611 AND secondary_inventory_name = p_subinventory_code;
4612 EXCEPTION
4613 WHEN OTHERS THEN
4614 l_sub_type := 1;
4615 END;
4616
4617 --Only if it is a storage sub then we need to do the
4618 --following validation
4619 IF l_sub_type = 1 THEN
4620 -- Loop through each item packed within the LPN
4621 OPEN l_item_cursor;
4622 LOOP
4623 FETCH l_item_cursor INTO l_item_id, l_restrict_loc;
4624 EXIT WHEN l_item_cursor%NOTFOUND;
4625
4626 -- If the item restricts locators,
4627 -- check that the locator is allowed
4628 IF (l_restrict_loc = 1) THEN
4629 SELECT COUNT(*)
4630 INTO l_count
4631 FROM mtl_secondary_locators
4632 WHERE organization_id = p_organization_id
4633 AND inventory_item_id = l_item_id
4634 AND subinventory_code = p_subinventory_code
4635 AND secondary_locator = p_locator_id;
4636 -- No rows returned so the loc is not valid
4637 -- for the current item
4638 IF (l_count = 0) THEN
4639 x_return := 'N';
4640 EXIT;
4641 END IF;
4642 END IF;
4643 END LOOP;
4644 CLOSE l_item_cursor;
4645 END IF;
4646
4647 -- Loc has already failed item/sub/loc restrictions
4648 -- so no need to do any further validation.
4649 IF (x_return = 'N') THEN
4650 RETURN x_return;
4651 END IF;
4652
4653 -- Loop through each move order line for the LPN
4654 OPEN l_item_txn_cursor;
4655 LOOP
4656 FETCH l_item_txn_cursor INTO l_item_id, l_transaction_type;
4657 EXIT WHEN l_item_txn_cursor%NOTFOUND;
4658
4659 -- Check if the loc's material status is valid
4660 -- for the current move order line's transaction type
4661 x_return := inv_material_status_grp.is_status_applicable
4662 (p_wms_installed => 'TRUE',
4663 p_trx_status_enabled => NULL,
4664 p_trx_type_id => l_transaction_type,
4665 p_lot_status_enabled => NULL,
4666 p_serial_status_enabled => NULL,
4667 p_organization_id => p_organization_id,
4668 p_inventory_item_id => l_item_id,
4669 p_sub_code => p_subinventory_code,
4670 p_locator_id => p_locator_id,
4671 p_lot_number => NULL,
4672 p_serial_number => NULL,
4673 p_object_type => 'L');
4674 -- The function returned 'N' so the loc is not valid
4675 -- for the transaction type in the current move order line
4676 IF (x_return = 'N') THEN
4677 EXIT;
4678 END IF;
4679 END LOOP;
4680 CLOSE l_item_txn_cursor;
4681
4682 -- If all of the items in the LPN passed validation
4683 -- for the given loc, the return variable should be 'Y'
4684 RETURN x_return;
4685
4686 EXCEPTION
4687 WHEN OTHERS THEN
4688 IF (l_debug = 1) THEN
4689 DEBUG('Exception occurred in function validate_lpn_loc!');
4690 END IF;
4691 x_return := 'N';
4692 RETURN x_return;
4693 END validate_lpn_loc;
4694
4695
4696 PROCEDURE get_pickload_loc_lov(
4697 x_locators OUT NOCOPY t_genref
4698 , p_organization_id IN NUMBER
4699 , p_subinventory_code IN VARCHAR2
4700 , p_restrict_locators_code IN NUMBER
4701 , p_inventory_item_id IN NUMBER
4702 , p_concatenated_segments IN VARCHAR2
4703 , p_transaction_type_id IN NUMBER
4704 , p_wms_installed IN VARCHAR2
4705 , p_project_id IN NUMBER
4706 , p_task_id IN NUMBER
4707 , p_alias IN VARCHAR2
4708 ) IS
4709
4710 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4711 l_ispjm_org VARCHAR2(1);
4712 /*Bug 2902336:Modfied the select clauses for performance enhancements*/
4713 BEGIN
4714
4715 IF p_alias IS NULL THEN
4716 get_pickload_loc_lov(
4717 x_locators => x_locators
4718 , p_organization_id => p_organization_id
4719 , p_subinventory_code => p_subinventory_code
4720 , p_restrict_locators_code => p_restrict_locators_code
4721 , p_inventory_item_id => p_inventory_item_id
4722 , p_concatenated_segments => p_concatenated_segments
4723 , p_transaction_type_id => p_transaction_type_id
4724 , p_wms_installed => p_wms_installed
4725 , p_project_id => p_project_id
4726 , p_task_id => p_task_id
4727 );
4728 RETURN;
4729 END IF;
4730 BEGIN
4731 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
4732 INTO l_ispjm_org
4733 FROM pjm_org_parameters
4734 WHERE organization_id=p_organization_id;
4735 EXCEPTION
4736 WHEN NO_DATA_FOUND THEN
4737 l_ispjm_org:='N';
4738 END;
4739 IF (l_ispjm_org='N') THEN/*Non PJM org*/
4740 IF p_restrict_locators_code=1 THEN
4741 OPEN x_locators FOR
4742 SELECT a.inventory_location_id
4743 --, a.concatenated_segments--Bug4398337:Commented this line and added below line
4744 , a.locator_segments
4745 , NVL(a.description, -1)
4746 FROM wms_item_locations_kfv a, mtl_secondary_locators b
4747 WHERE b.organization_id = p_organization_id
4748 AND b.inventory_item_id = p_inventory_item_id
4749 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4750 AND b.subinventory_code = p_subinventory_code
4751 AND a.inventory_location_id = b.secondary_locator
4752 /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
4753 AND a.alias = p_alias
4754 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4755 ORDER BY 2;
4756 ELSE --Locators not restricted
4757 OPEN x_locators FOR
4758 SELECT inventory_location_id
4759 --, concatenated_segments--Bug4398337:Commented this line and added below line
4760 , locator_segments concatenated_segments
4761 , description
4762 FROM wms_item_locations_kfv
4763 WHERE organization_id = p_organization_id
4764 AND subinventory_code = p_subinventory_code
4765 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4766 --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4767 AND alias = p_alias
4768 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4769 ORDER BY 2;
4770 END IF;
4771 ELSE /*PJM org*/
4772 IF p_project_id IS NULL THEN
4773 IF p_restrict_locators_code=1 THEN
4774 OPEN x_locators FOR
4775 SELECT a.inventory_location_id
4776 --, a.concatenated_segments--Bug4398337:Commented this line and added below line
4777 , a.locator_segments concatenated_segments
4778 , NVL(a.description, -1)
4779 FROM wms_item_locations_kfv a, mtl_secondary_locators b
4780 WHERE b.organization_id = p_organization_id
4781 AND b.inventory_item_id = p_inventory_item_id
4782 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4783 AND b.subinventory_code = p_subinventory_code
4784 AND a.inventory_location_id = b.secondary_locator
4785 AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
4786 AND a.alias = p_alias
4787 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4788 ORDER BY 2;
4789 ELSE --Locators not restricted
4790 OPEN x_locators FOR
4791 SELECT inventory_location_id
4792 --, concatenated_segments--Bug4398337:Commented this line and added below line
4793 , locator_segments concatenated_segments
4794 , description
4795 FROM wms_item_locations_kfv
4796 WHERE organization_id = p_organization_id
4797 AND subinventory_code = p_subinventory_code
4798 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4799 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4800 AND alias = p_alias
4801 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4802 ORDER BY 2;
4803 END IF;
4804 ELSE /*PJM org:Project id not null */
4805 IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
4806 OPEN x_locators FOR
4807 SELECT a.inventory_location_id
4808 --, a.concatenated_segments--Bug4398337:Commented this line and added below line
4809 , a.locator_segments concatenated_segments
4810 , NVL(a.description, -1)
4811 FROM wms_item_locations_kfv a, mtl_secondary_locators b
4812 WHERE b.organization_id = p_organization_id
4813 AND b.inventory_item_id = p_inventory_item_id
4814 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4815 AND b.subinventory_code = p_subinventory_code
4816 AND a.inventory_location_id = b.secondary_locator
4817 --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4818 AND a.alias = p_alias
4819 AND a.project_id = p_project_id
4820 AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
4821 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4822 ORDER BY 2;
4823 ELSE --Locators not restricted
4824 OPEN x_locators FOR
4825 SELECT inventory_location_id
4826 --, concatenated_segments--Bug4398337:Commented this line and added below line
4827 , locator_segments concatenated_segments
4828 , description
4829 FROM wms_item_locations_kfv
4830 WHERE organization_id = p_organization_id
4831 AND subinventory_code = p_subinventory_code
4832 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4833 --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4834 AND alias = p_alias
4835 AND project_id = p_project_id
4836 AND NVL(task_id, -1) = NVL(p_task_id, -1)
4837 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4838 ORDER BY 2;
4839 END IF;
4840 END IF;
4841 END IF;
4842 END get_pickload_loc_lov;
4843 /* Bug 2769628: Procedure to list the locators during Pick Load as they have to be restricted by project and task*/
4844 PROCEDURE get_pickload_loc_lov(
4845 x_locators OUT NOCOPY t_genref
4846 , p_organization_id IN NUMBER
4847 , p_subinventory_code IN VARCHAR2
4848 , p_restrict_locators_code IN NUMBER
4849 , p_inventory_item_id IN NUMBER
4850 , p_concatenated_segments IN VARCHAR2
4851 , p_transaction_type_id IN NUMBER
4852 , p_wms_installed IN VARCHAR2
4853 , p_project_id IN NUMBER
4854 , p_task_id IN NUMBER) IS
4855
4856 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4857 l_ispjm_org VARCHAR2(1);
4858 /*Bug 2902336:Modfied the select clauses for performance enhancements*/
4859 BEGIN
4860 BEGIN
4861 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
4862 INTO l_ispjm_org
4863 FROM pjm_org_parameters
4864 WHERE organization_id=p_organization_id;
4865 EXCEPTION
4866 WHEN NO_DATA_FOUND THEN
4867 l_ispjm_org:='N';
4868 END;
4869 IF (l_ispjm_org='N') THEN/*Non PJM org*/
4870 IF p_restrict_locators_code=1 THEN
4871 OPEN x_locators FOR
4872 SELECT a.inventory_location_id
4873 --, a.concatenated_segments--Bug4398337:Commented this line and added below line
4874 , a.locator_segments
4875 , NVL(a.description, -1)
4876 FROM wms_item_locations_kfv a, mtl_secondary_locators b
4877 WHERE b.organization_id = p_organization_id
4878 AND b.inventory_item_id = p_inventory_item_id
4879 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4880 AND b.subinventory_code = p_subinventory_code
4881 AND a.inventory_location_id = b.secondary_locator
4882 /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
4883 AND a.concatenated_segments LIKE (p_concatenated_segments)
4884 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4885 ORDER BY 2;
4886 ELSE --Locators not restricted
4887 OPEN x_locators FOR
4888 SELECT inventory_location_id
4889 --, concatenated_segments--Bug4398337:Commented this line and added below line
4890 , locator_segments concatenated_segments
4891 , description
4892 FROM wms_item_locations_kfv
4893 WHERE organization_id = p_organization_id
4894 AND subinventory_code = p_subinventory_code
4895 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4896 --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4897 AND concatenated_segments LIKE (p_concatenated_segments)
4898 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4899 ORDER BY 2;
4900 END IF;
4901 ELSE /*PJM org*/
4902 IF p_project_id IS NULL THEN
4903 IF p_restrict_locators_code=1 THEN
4904 OPEN x_locators FOR
4905 SELECT a.inventory_location_id
4906 --, a.concatenated_segments--Bug4398337:Commented this line and added below line
4907 , a.locator_segments concatenated_segments
4908 , NVL(a.description, -1)
4909 FROM wms_item_locations_kfv a, mtl_secondary_locators b
4910 WHERE b.organization_id = p_organization_id
4911 AND b.inventory_item_id = p_inventory_item_id
4912 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4913 AND b.subinventory_code = p_subinventory_code
4914 AND a.inventory_location_id = b.secondary_locator
4915 AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
4916 AND a.concatenated_segments LIKE (p_concatenated_segments)
4917 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4918 ORDER BY 2;
4919 ELSE --Locators not restricted
4920 OPEN x_locators FOR
4921 SELECT inventory_location_id
4922 --, concatenated_segments--Bug4398337:Commented this line and added below line
4923 , locator_segments concatenated_segments
4924 , description
4925 FROM wms_item_locations_kfv
4926 WHERE organization_id = p_organization_id
4927 AND subinventory_code = p_subinventory_code
4928 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4929 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4930 AND concatenated_segments LIKE (p_concatenated_segments)
4931 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4932 ORDER BY 2;
4933 END IF;
4934 ELSE /*PJM org:Project id not null */
4935 IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
4936 OPEN x_locators FOR
4937 SELECT a.inventory_location_id
4938 --, a.concatenated_segments--Bug4398337:Commented this line and added below line
4939 , a.locator_segments concatenated_segments
4940 , NVL(a.description, -1)
4941 FROM wms_item_locations_kfv a, mtl_secondary_locators b
4942 WHERE b.organization_id = p_organization_id
4943 AND b.inventory_item_id = p_inventory_item_id
4944 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4945 AND b.subinventory_code = p_subinventory_code
4946 AND a.inventory_location_id = b.secondary_locator
4947 --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4948 AND a.concatenated_segments LIKE (p_concatenated_segments)
4949 AND a.project_id = p_project_id
4950 AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
4951 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4952 ORDER BY 2;
4953 ELSE --Locators not restricted
4954 OPEN x_locators FOR
4955 SELECT inventory_location_id
4956 --, concatenated_segments--Bug4398337:Commented this line and added below line
4957 , locator_segments concatenated_segments
4958 , description
4959 FROM wms_item_locations_kfv
4960 WHERE organization_id = p_organization_id
4961 AND subinventory_code = p_subinventory_code
4962 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4963 --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4964 AND concatenated_segments LIKE (p_concatenated_segments)
4965 AND project_id = p_project_id
4966 AND NVL(task_id, -1) = NVL(p_task_id, -1)
4967 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4968 ORDER BY 2;
4969 END IF;
4970 END IF;
4971 END IF;
4972 END get_pickload_loc_lov;
4973
4974 /* The following procedure is added for bug 4990550. Since the locator field in pick load page is no longer an LOV
4975 from 11510,the new procedure is added to validate the locator field. */
4976 PROCEDURE get_pickload_loc(
4977 x_locators OUT NOCOPY t_genref
4978 , p_organization_id IN NUMBER
4979 , p_subinventory_code IN VARCHAR2
4980 , p_restrict_locators_code IN NUMBER
4981 , p_inventory_item_id IN NUMBER
4982 , p_concatenated_segments IN VARCHAR2
4983 , p_transaction_type_id IN NUMBER
4984 , p_wms_installed IN VARCHAR2
4985 , p_project_id IN NUMBER
4986 , p_task_id IN NUMBER) IS
4987
4988 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4989 l_ispjm_org VARCHAR2(1);
4990 /*Bug 2902336:Modfied the select clauses for performance enhancements*/
4991 BEGIN
4992 BEGIN
4993 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
4994 INTO l_ispjm_org
4995 FROM pjm_org_parameters
4996 WHERE organization_id=p_organization_id;
4997 EXCEPTION
4998 WHEN NO_DATA_FOUND THEN
4999 l_ispjm_org:='N';
5000 END;
5001 IF (l_ispjm_org='N') THEN/*Non PJM org*/
5002 IF p_restrict_locators_code=1 THEN
5003 OPEN x_locators FOR
5004 SELECT a.inventory_location_id
5005 , a.concatenated_segments -- Bug 4398336
5006 --, a.locator_segments concatenated_segments
5007 , NVL(a.description, -1)
5008 FROM wms_item_locations_kfv a, mtl_secondary_locators b
5009 WHERE b.organization_id = p_organization_id
5010 AND b.inventory_item_id = p_inventory_item_id
5011 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5012 AND b.subinventory_code = p_subinventory_code
5013 AND a.inventory_location_id = b.secondary_locator
5014 /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5015 AND a.concatenated_segments LIKE (p_concatenated_segments)
5016 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5017 ORDER BY 2;
5018 ELSE --Locators not restricted
5019 OPEN x_locators FOR
5020 SELECT inventory_location_id
5021 , concatenated_segments -- Bug 4398336
5022 --, locator_segments concatenated_segments
5023 , description
5024 FROM wms_item_locations_kfv
5025 WHERE organization_id = p_organization_id
5026 AND subinventory_code = p_subinventory_code
5027 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5028 --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5029 AND concatenated_segments LIKE (p_concatenated_segments)
5030 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5031 ORDER BY 2;
5032 END IF;
5033 ELSE /*PJM org*/
5034 IF p_project_id IS NULL THEN
5035 IF p_restrict_locators_code=1 THEN
5036 OPEN x_locators FOR
5037 SELECT a.inventory_location_id
5038 , a.concatenated_segments -- Bug 4398336
5039 --, a.locator_segments concatenated_segments
5040 , NVL(a.description, -1)
5041 FROM wms_item_locations_kfv a, mtl_secondary_locators b
5042 WHERE b.organization_id = p_organization_id
5043 AND b.inventory_item_id = p_inventory_item_id
5044 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5045 AND b.subinventory_code = p_subinventory_code
5046 AND a.inventory_location_id = b.secondary_locator
5047 -- AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5048 AND a.project_id is null
5049 AND a.concatenated_segments LIKE (p_concatenated_segments)
5050 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5051 ORDER BY 2;
5052 ELSE --Locators not restricted
5053 OPEN x_locators FOR
5054 SELECT inventory_location_id
5055 , concatenated_segments -- Bug 4398336
5056 --, locator_segments concatenated_segments
5057 , description
5058 FROM wms_item_locations_kfv
5059 WHERE organization_id = p_organization_id
5060 AND subinventory_code = p_subinventory_code
5061 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5062 -- AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5063 AND project_id is null
5064 AND concatenated_segments LIKE (p_concatenated_segments)
5065 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5066 ORDER BY 2;
5067 END IF;
5068 ELSE /*PJM org:Project id not null */
5069 IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5070 OPEN x_locators FOR
5071 SELECT a.inventory_location_id
5072 , a.concatenated_segments -- Bug 4398336
5073 --, a.locator_segments concatenated_segments
5074 , NVL(a.description, -1)
5075 FROM wms_item_locations_kfv a, mtl_secondary_locators b
5076 WHERE b.organization_id = p_organization_id
5077 AND b.inventory_item_id = p_inventory_item_id
5078 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5079 AND b.subinventory_code = p_subinventory_code
5080 AND a.inventory_location_id = b.secondary_locator
5081 --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5082 AND a.concatenated_segments LIKE (p_concatenated_segments)
5083 AND a.project_id = p_project_id
5084 AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5085 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5086 ORDER BY 2;
5087 ELSE --Locators not restricted
5088 OPEN x_locators FOR
5089 SELECT inventory_location_id
5090 , concatenated_segments -- Bug 4398336
5091 --, locator_segments concatenated_segments
5092 , description
5093 FROM wms_item_locations_kfv
5094 WHERE organization_id = p_organization_id
5095 AND subinventory_code = p_subinventory_code
5096 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5097 --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5098 AND concatenated_segments LIKE (p_concatenated_segments)
5099 AND project_id = p_project_id
5100 AND NVL(task_id, -1) = NVL(p_task_id, -1)
5101 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5102 ORDER BY 2;
5103 END IF;
5104 END IF;
5105 END IF;
5106 END get_pickload_loc;
5107
5108
5109 -- Bug #3075665. ADDED IN PATCHSET J PROJECT ADVANCED PICKLOAD
5110 -- Patchset J: Procedure used to get the locs including project locs
5111 -- Procedure Name: get_pickload_all_loc_lov
5112 --
5113 -- Input parameters:
5114 -- p_organization_id - Organization Id
5115 --
5116 -- Output value:
5117 -- x_locators Ref. cursor
5118 --
5119 PROCEDURE GET_APL_PRJ_LOC_LOV(
5120 x_locators OUT NOCOPY t_genref
5121 , p_organization_id IN NUMBER
5122 , p_subinventory_code IN VARCHAR2
5123 , p_restrict_locators_code IN NUMBER
5124 , p_inventory_item_id IN NUMBER
5125 , p_concatenated_segments IN VARCHAR2
5126 , p_transaction_type_id IN NUMBER
5127 , p_wms_installed IN VARCHAR2
5128 , p_project_id IN NUMBER
5129 , p_task_id IN NUMBER) IS
5130
5131 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5132 l_ispjm_org VARCHAR2(1);
5133 BEGIN
5134 BEGIN
5135 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
5136 INTO l_ispjm_org
5137 FROM pjm_org_parameters
5138 WHERE organization_id=p_organization_id;
5139 EXCEPTION
5140 WHEN NO_DATA_FOUND THEN
5141 l_ispjm_org:='N';
5142 END;
5143 IF (l_ispjm_org='N') THEN/*Non PJM org*/
5144 IF p_restrict_locators_code=1 THEN
5145 OPEN x_locators FOR
5146 SELECT a.inventory_location_id
5147 --, a.concatenated_segments--Bug4398337:Commented this line and added below line
5148 , a.locator_segments concatenated_segments
5149 , NVL(a.description, -1)
5150 , a.subinventory_code
5151 FROM wms_item_locations_kfv a, mtl_secondary_locators b
5152 WHERE b.organization_id = p_organization_id
5153 AND b.inventory_item_id = p_inventory_item_id
5154 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5155 AND b.subinventory_code = p_subinventory_code
5156 AND a.inventory_location_id = b.secondary_locator
5157 /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5158 AND a.concatenated_segments LIKE (p_concatenated_segments)
5159 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5160 ORDER BY 2;
5161 ELSE --Locators not restricted
5162 OPEN x_locators FOR
5163 SELECT inventory_location_id
5164 -- , concatenated_segments--Bug4398337:Commented this line and added below line
5165 , locator_segments concatenated_segments
5166 , description
5167 , subinventory_code
5168 FROM wms_item_locations_kfv
5169 WHERE organization_id = p_organization_id
5170 AND subinventory_code = p_subinventory_code
5171 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5172 --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5173 AND concatenated_segments LIKE (p_concatenated_segments)
5174 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5175 ORDER BY 2;
5176 END IF;
5177 ELSE /*PJM org*/
5178 IF p_project_id IS NULL THEN
5179 IF p_restrict_locators_code=1 THEN
5180 OPEN x_locators FOR
5181 SELECT a.inventory_location_id
5182 --, a.concatenated_segments--Bug4398337:Commented this line and added below line
5183 , a.locator_segments concatenated_segments
5184 , NVL(a.description, -1)
5185 , a.subinventory_code
5186 FROM wms_item_locations_kfv a, mtl_secondary_locators b
5187 WHERE b.organization_id = p_organization_id
5188 AND b.inventory_item_id = p_inventory_item_id
5189 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5190 AND b.subinventory_code = p_subinventory_code
5191 AND a.inventory_location_id = b.secondary_locator
5192 /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5193 AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5194 AND a.concatenated_segments LIKE (p_concatenated_segments)
5195 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5196 ORDER BY 2;
5197 ELSE --Locators not restricted
5198 OPEN x_locators FOR
5199 SELECT inventory_location_id
5200 -- , concatenated_segments--Bug4398337:Commented this line and added below line
5201 , locator_segments concatenated_segments
5202 , description
5203 , subinventory_code
5204 FROM wms_item_locations_kfv
5205 WHERE organization_id = p_organization_id
5206 AND subinventory_code = p_subinventory_code
5207 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5208 --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5209 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5210 AND concatenated_segments LIKE (p_concatenated_segments)
5211 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5212 ORDER BY 2;
5213 END IF;
5214 ELSE /*PJM org:Project id not null */
5215 IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5216 OPEN x_locators FOR
5217 SELECT a.inventory_location_id
5218 --, a.concatenated_segments--Bug4398337:Commented this line and added below line
5219 , a.locator_segments concatenated_segments
5220 , NVL(a.description, -1)
5221 , a.subinventory_code
5222 FROM wms_item_locations_kfv a, mtl_secondary_locators b
5223 WHERE b.organization_id = p_organization_id
5224 AND b.inventory_item_id = p_inventory_item_id
5225 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5226 AND b.subinventory_code = p_subinventory_code
5227 AND a.inventory_location_id = b.secondary_locator
5228 --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5229 AND a.concatenated_segments LIKE (p_concatenated_segments)
5230 AND a.project_id = p_project_id
5231 AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5232 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5233 ORDER BY 2;
5234 ELSE --Locators not restricted
5235 OPEN x_locators FOR
5236 SELECT inventory_location_id
5237 -- , concatenated_segments--Bug4398337:Commented this line and added below line
5238 , locator_segments concatenated_segments
5239 , description
5240 , subinventory_code
5241 FROM wms_item_locations_kfv
5242 WHERE organization_id = p_organization_id
5243 AND subinventory_code = p_subinventory_code
5244 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5245 --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5246 AND concatenated_segments LIKE (p_concatenated_segments)
5247 AND project_id = p_project_id
5248 AND NVL(task_id, -1) = NVL(p_task_id, -1)
5249 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5250 ORDER BY 2;
5251 END IF;
5252 END IF;
5253 END IF;
5254 END GET_APL_PRJ_LOC_LOV;
5255
5256 /* Bug #3075665. ADDED IN PATCHSET J PROJECT ADVANCED PICKLOAD
5257 * All the locators for the given org are selected, not restricting on the subinventory
5258 */
5259 -- Patchset J: Procedure used to get all the locs in the org
5260 -- restricted by proj, task if passed and
5261 -- NOT restricted by subinventory
5262 -- Procedure Name: get_pickload_all_loc_lov
5263 --
5264 -- Input parameters:
5265 -- p_organization_id - Organization Id
5266 --
5267 -- Output value:
5268 -- x_locators Ref. cursor
5269 --
5270 PROCEDURE get_pickload_all_loc_lov(
5271 x_locators OUT NOCOPY t_genref
5272 , p_organization_id IN NUMBER
5273 , p_restrict_locators_code IN NUMBER
5274 , p_inventory_item_id IN NUMBER
5275 , p_concatenated_segments IN VARCHAR2
5276 , p_transaction_type_id IN NUMBER
5277 , p_wms_installed IN VARCHAR2
5278 , p_project_id IN NUMBER
5279 , p_task_id IN NUMBER) IS
5280
5281 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5282 l_ispjm_org VARCHAR2(1);
5283 /*Bug 2902336:Modfied the select clauses for performance enhancements*/
5284 BEGIN
5285 BEGIN
5286 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
5287 INTO l_ispjm_org
5288 FROM pjm_org_parameters
5289 WHERE organization_id=p_organization_id;
5290 EXCEPTION
5291 WHEN NO_DATA_FOUND THEN
5292 l_ispjm_org:='N';
5293 END;
5294 IF (l_ispjm_org='N') THEN/*Non PJM org*/
5295 IF p_restrict_locators_code=1 THEN
5296 OPEN x_locators FOR
5297 SELECT a.inventory_location_id
5298 -- , a.concatenated_segments--Bug4398337:Commented this line and added below line
5299 , a.locator_segments concatenated_segments
5300 , NVL(a.description, -1)
5301 , a.subinventory_code
5302 FROM wms_item_locations_kfv a, mtl_secondary_locators b
5303 WHERE b.organization_id = p_organization_id
5304 AND b.inventory_item_id = p_inventory_item_id
5305 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5306 /*AND b.subinventory_code = p_subinventory_code*/ /*Removed Subinventory restriction, displayes all locs in the org*/
5307 AND a.inventory_location_id = b.secondary_locator
5308 AND a.concatenated_segments LIKE (p_concatenated_segments)
5309 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5310 ORDER BY 2;
5311 ELSE --Locators not restricted
5312 OPEN x_locators FOR
5313 SELECT inventory_location_id
5314 --, concatenated_segments--Bug4398337:Commented this line and added below line
5315 , locator_segments concatenated_segments
5316 , description
5317 , subinventory_code
5318 FROM wms_item_locations_kfv
5319 WHERE organization_id = p_organization_id
5320 /*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5321 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5322 AND concatenated_segments LIKE (p_concatenated_segments)
5323 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, inventory_location_id, NULL, NULL, 'L') = 'Y'
5324 ORDER BY 2;
5325 END IF;
5326 ELSE /*PJM org*/
5327 IF p_project_id IS NULL THEN
5328 IF p_restrict_locators_code=1 THEN
5329 OPEN x_locators FOR
5330 SELECT a.inventory_location_id
5331 -- , a.concatenated_segments--Bug4398337:Commented this line and added below line
5332 , a.locator_segments concatenated_segments
5333 , NVL(a.description, -1)
5334 ,a.subinventory_code
5335 FROM wms_item_locations_kfv a, mtl_secondary_locators b
5336 WHERE b.organization_id = p_organization_id
5337 AND b.inventory_item_id = p_inventory_item_id
5338 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5339 /*AND b.subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5340 AND a.inventory_location_id = b.secondary_locator
5341 AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5342 AND a.concatenated_segments LIKE (p_concatenated_segments)
5343 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5344 ORDER BY 2;
5345 ELSE --Locators not restricted
5346 OPEN x_locators FOR
5347 SELECT inventory_location_id
5348 -- , concatenated_segments--Bug4398337:Commented this line and added below line
5349 , locator_segments concatenated_segments
5350 , description
5351 , subinventory_code
5352 FROM wms_item_locations_kfv
5353 WHERE organization_id = p_organization_id
5354 /*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5355 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5356 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5357 AND concatenated_segments LIKE (p_concatenated_segments)
5358 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, inventory_location_id, NULL, NULL, 'L') = 'Y'
5359 ORDER BY 2;
5360 END IF;
5361 ELSE /*PJM org:Project id not null */
5362 IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5363 OPEN x_locators FOR
5364 SELECT a.inventory_location_id
5365 -- , a.concatenated_segments--Bug4398337:Commented this line and added below line
5366 , a.locator_segments concatenated_segments
5367 , NVL(a.description, -1)
5368 , a.subinventory_code
5369 FROM wms_item_locations_kfv a, mtl_secondary_locators b
5370 WHERE b.organization_id = p_organization_id
5371 AND b.inventory_item_id = p_inventory_item_id
5372 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5373 /*AND b.subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5374 AND a.inventory_location_id = b.secondary_locator
5375 AND a.concatenated_segments LIKE (p_concatenated_segments)
5376 AND a.project_id = p_project_id
5377 AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5378 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5379 ORDER BY 2;
5380 ELSE --Locators not restricted
5381 OPEN x_locators FOR
5382 SELECT inventory_location_id
5383 -- , concatenated_segments--Bug4398337:Commented this line and added below line
5384 , locator_segments concatenated_segments
5385 , description
5386 , subinventory_code
5387 FROM wms_item_locations_kfv
5388 WHERE organization_id = p_organization_id
5389 /*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5390 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5391 AND concatenated_segments LIKE (p_concatenated_segments)
5392 AND project_id = p_project_id
5393 AND NVL(task_id, -1) = NVL(p_task_id, -1)
5394 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, inventory_location_id, NULL, NULL, 'L') = 'Y'
5395 ORDER BY 2;
5396 END IF;
5397 END IF;
5398 END IF;
5399 END get_pickload_all_loc_lov;
5400
5401 PROCEDURE validate_pickload_loc
5402 (p_organization_id IN NUMBER,
5403 p_subinventory_code IN VARCHAR2,
5404 p_restrict_locators_code IN NUMBER,
5405 p_inventory_item_id IN NUMBER,
5406 p_locator IN VARCHAR2,
5407 p_transaction_type_id IN NUMBER,
5408 p_project_id IN NUMBER,
5409 p_task_id IN NUMBER,
5410 x_is_valid_locator OUT nocopy VARCHAR2,
5411 x_locator_id OUT nocopy NUMBER)
5412 IS
5413
5414 TYPE loc_record_type IS RECORD
5415 (locator_id NUMBER,
5416 locator VARCHAR2(204),
5417 description VARCHAR2(50));
5418
5419 loc_rec loc_record_type;
5420 l_locators t_genref;
5421 l_project_id NUMBER;
5422 l_task_id NUMBER;
5423
5424 BEGIN
5425 x_is_valid_locator := 'N';
5426
5427 IF p_project_id = 0 THEN
5428 l_project_id := NULL;
5429 END IF;
5430
5431 IF p_task_id = 0 THEN
5432 l_task_id := NULL;
5433 END IF;
5434
5435 get_pickload_loc_lov(x_locators => l_locators,
5436 p_organization_id => p_organization_id,
5437 p_subinventory_code => p_subinventory_code,
5438 p_restrict_locators_code => p_restrict_locators_code,
5439 p_inventory_item_id => p_inventory_item_id,
5440 p_concatenated_segments => p_locator,
5441 p_transaction_type_id => p_transaction_type_id,
5442 p_wms_installed => 'Y',
5443 p_project_id => l_project_id,
5444 p_task_id => l_task_id);
5445
5446 LOOP
5447 FETCH l_locators INTO loc_rec;
5448 EXIT WHEN l_locators%notfound;
5449
5450 IF loc_rec.locator = p_locator THEN
5451 x_is_valid_locator := 'Y';
5452 x_locator_id := loc_rec.locator_id;
5453 EXIT;
5454 END IF;
5455
5456 END LOOP;
5457
5458 END;
5459
5460 PROCEDURE get_inq_prj_loc_lov(
5461 x_Locators OUT NOCOPY t_genref,
5462 p_Organization_Id IN NUMBER,
5463 p_Subinventory_Code IN VARCHAR2,
5464 p_Restrict_Locators_Code IN NUMBER,
5465 p_Inventory_Item_Id IN NUMBER,
5466 p_Concatenated_Segments IN VARCHAR2,
5467 p_project_id IN NUMBER := NULL,
5468 p_task_id IN NUMBER := NULL
5469 -- p_alias IN VARCHAR2 := NULL
5470 -- p_suggestion IN VARCHAR2 := NULL
5471 ) IS
5472 l_ispjm_org VARCHAR2(1);
5473 BEGIN
5474 BEGIN
5475 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
5476 INTO l_ispjm_org
5477 FROM pjm_org_parameters
5478 WHERE organization_id=p_organization_id;
5479 EXCEPTION
5480 WHEN NO_DATA_FOUND THEN
5481 l_ispjm_org:='N';
5482 END;
5483
5484 IF l_ispjm_org='N' THEN /*Non PJM Org*/
5485 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
5486 OPEN x_Locators FOR
5487 SELECT a.inventory_location_id,
5488 -- a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5489 a.locator_segments locsegs,
5490 a.description
5491 FROM wms_item_locations_kfv a,
5492 mtl_secondary_locators b
5493 WHERE b.organization_id = p_Organization_Id
5494 AND b.inventory_item_id = p_Inventory_Item_Id
5495 AND b.subinventory_code = p_Subinventory_Code
5496 AND a.inventory_location_id = b.secondary_locator
5497 AND a.concatenated_segments LIKE (p_concatenated_segments )
5498 -- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
5499 /* BUG#28101405: To show only common locators in the LOV */
5500 ORDER BY 2;
5501 ELSE --Locators not restricted
5502 --bug#3440453 Remove the NVL on organization_id if user passes it.
5503 IF p_organization_id IS NULL THEN
5504 OPEN x_Locators FOR
5505 SELECT inventory_location_id,
5506 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5507 locator_segments locsegs,
5508 description
5509 FROM wms_item_locations_kfv
5510 WHERE organization_id = Nvl(p_organization_id, organization_id)
5511 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5512 AND concatenated_segments LIKE (p_concatenated_segments )
5513 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5514 ORDER BY 2;
5515 ELSE -- Organization_id is not null
5516 OPEN x_Locators FOR
5517 SELECT inventory_location_id,
5518 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5519 locator_segments locsegs,
5520 description
5521 FROM wms_item_locations_kfv
5522 WHERE organization_id = p_organization_id
5523 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5524 AND concatenated_segments LIKE (p_concatenated_segments )
5525 -- AND alias = nvl(p_alias, alias)
5526 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5527 ORDER BY 2;
5528 END IF;
5529 END IF;
5530 ELSE /*PJM org*/
5531 IF p_project_id IS NOT NULL THEN
5532
5533 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
5534 OPEN x_Locators FOR
5535 SELECT a.inventory_location_id,
5536 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5537 a.locator_segments locsegs,
5538 a.description
5539 FROM wms_item_locations_kfv a,
5540 mtl_secondary_locators b
5541 WHERE b.organization_id = p_Organization_Id
5542 AND b.inventory_item_id = p_Inventory_Item_Id
5543 AND b.subinventory_code = p_Subinventory_Code
5544 AND a.inventory_location_id = b.secondary_locator
5545 AND a.concatenated_segments LIKE (p_concatenated_segments )
5546 -- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
5547 /* BUG#28101405: To show only common locators in the LOV */
5548 AND a.project_id = p_project_id
5549 AND NVL(a.task_id, -9999) = NVL(p_task_id, -9999)
5550 ORDER BY 2;
5551
5552 ELSE --Locators not restricted
5553 --bug#3440453 Remove the NVL on organization_id if user passes it.
5554 IF p_organization_id IS NULL THEN
5555 OPEN x_Locators FOR
5556 SELECT inventory_location_id,
5557 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5558 locator_segments locsegs,
5559 description
5560 FROM wms_item_locations_kfv
5561 WHERE organization_id = Nvl(p_organization_id, organization_id)
5562 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5563 AND concatenated_segments LIKE (p_concatenated_segments )
5564 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5565 AND project_id = p_project_id
5566 AND NVL(task_id, -1) = NVL(p_task_id, -1)
5567 ORDER BY 2;
5568 ELSE -- Organization_id is not null
5569 OPEN x_Locators FOR
5570 SELECT inventory_location_id,
5571 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5572 locator_segments locsegs,
5573 description
5574 FROM wms_item_locations_kfv
5575 WHERE organization_id = p_organization_id
5576 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5577 AND concatenated_segments LIKE (p_concatenated_segments )
5578 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5579 AND project_id = p_project_id
5580 AND NVL(task_id, -1) = NVL(p_task_id, -1)
5581 ORDER BY 2;
5582 END IF;
5583 END IF;
5584
5585 ELSE /*PJM org project id null*/
5586
5587 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
5588 OPEN x_Locators FOR
5589 SELECT a.inventory_location_id,
5590 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5591 a.locator_segments locsegs,
5592 a.description
5593 FROM wms_item_locations_kfv a,
5594 mtl_secondary_locators b
5595 WHERE b.organization_id = p_Organization_Id
5596 AND b.inventory_item_id = p_Inventory_Item_Id
5597 AND b.subinventory_code = p_Subinventory_Code
5598 AND a.inventory_location_id = b.secondary_locator
5599 AND a.concatenated_segments LIKE (p_concatenated_segments )
5600 -- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
5601 AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
5602 /* BUG#28101405: To show only common locators in the LOV */
5603 ORDER BY 2;
5604
5605 ELSE --Locators not restricted
5606 --bug#3440453 Remove the NVL on organization_id if user passes it.
5607 IF p_organization_id IS NULL THEN
5608 OPEN x_Locators FOR
5609 SELECT inventory_location_id,
5610 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5611 locator_segments locsegs,
5612 description
5613 FROM wms_item_locations_kfv
5614 WHERE organization_id = Nvl(p_organization_id, organization_id)
5615 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5616 AND concatenated_segments LIKE (p_concatenated_segments )
5617 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5618 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5619 ORDER BY 2;
5620 ELSE -- Organization_id is not null
5621 OPEN x_Locators FOR
5622 SELECT inventory_location_id,
5623 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5624 locator_segments locsegs,
5625 description
5626 FROM wms_item_locations_kfv
5627 WHERE organization_id = p_organization_id
5628 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5629 AND concatenated_segments LIKE (p_concatenated_segments )
5630 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5631 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5632 ORDER BY 2;
5633 END IF;
5634 END IF;
5635 END IF;
5636 END IF;
5637 END get_inq_prj_loc_lov;
5638
5639 PROCEDURE get_inq_prj_loc_lov(
5640 x_Locators OUT NOCOPY t_genref,
5641 p_Organization_Id IN NUMBER,
5642 p_Subinventory_Code IN VARCHAR2,
5643 p_Restrict_Locators_Code IN NUMBER,
5644 p_Inventory_Item_Id IN NUMBER,
5645 p_Concatenated_Segments IN VARCHAR2,
5646 p_project_id IN NUMBER := NULL,
5647 p_task_id IN NUMBER := NULL,
5648 p_alias IN VARCHAR2
5649 ) IS
5650 l_ispjm_org VARCHAR2(1);
5651 BEGIN
5652 IF p_alias IS NULL THEN
5653 get_inq_prj_loc_lov(
5654 x_Locators => x_locators
5655 ,p_Organization_Id => p_organization_id
5656 ,p_Subinventory_Code => p_subinventory_code
5657 ,p_Restrict_Locators_Code => p_restrict_locators_code
5658 ,p_Inventory_Item_Id => p_inventory_item_id
5659 ,p_Concatenated_Segments => p_concatenated_segments
5660 ,p_project_id => p_project_id
5661 ,p_task_id => p_task_id
5662 );
5663 RETURN;
5664 END IF;
5665 BEGIN
5666 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
5667 INTO l_ispjm_org
5668 FROM pjm_org_parameters
5669 WHERE organization_id=p_organization_id;
5670 EXCEPTION
5671 WHEN NO_DATA_FOUND THEN
5672 l_ispjm_org:='N';
5673 END;
5674
5675 IF l_ispjm_org='N' THEN /*Non PJM Org*/
5676 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
5677 OPEN x_Locators FOR
5678 SELECT a.inventory_location_id,
5679 -- a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5680 a.locator_segments locsegs,
5681 a.description
5682 FROM wms_item_locations_kfv a,
5683 mtl_secondary_locators b
5684 WHERE b.organization_id = p_Organization_Id
5685 AND b.inventory_item_id = p_Inventory_Item_Id
5686 AND b.subinventory_code = p_Subinventory_Code
5687 AND a.inventory_location_id = b.secondary_locator
5688 AND a.alias = p_alias
5689 -- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
5690 /* BUG#28101405: To show only common locators in the LOV */
5691 ORDER BY 2;
5692 ELSE --Locators not restricted
5693 --bug#3440453 Remove the NVL on organization_id if user passes it.
5694 IF p_organization_id IS NULL THEN
5695 OPEN x_Locators FOR
5696 SELECT inventory_location_id,
5697 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5698 locator_segments locsegs,
5699 description
5700 FROM wms_item_locations_kfv
5701 WHERE organization_id = Nvl(p_organization_id, organization_id)
5702 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5703 AND alias = p_alias
5704 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5705 ORDER BY 2;
5706 ELSE -- Organization_id is not null
5707 OPEN x_Locators FOR
5708 SELECT inventory_location_id,
5709 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5710 locator_segments locsegs,
5711 description
5712 FROM wms_item_locations_kfv
5713 WHERE organization_id = p_organization_id
5714 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5715 AND alias = p_alias
5716 ORDER BY 2;
5717 END IF;
5718 END IF;
5719 ELSE /*PJM org*/
5720 IF p_project_id IS NOT NULL THEN
5721
5722 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
5723 OPEN x_Locators FOR
5724 SELECT a.inventory_location_id,
5725 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5726 a.locator_segments locsegs,
5727 a.description
5728 FROM wms_item_locations_kfv a,
5729 mtl_secondary_locators b
5730 WHERE b.organization_id = p_Organization_Id
5731 AND b.inventory_item_id = p_Inventory_Item_Id
5732 AND b.subinventory_code = p_Subinventory_Code
5733 AND a.inventory_location_id = b.secondary_locator
5734 AND a.alias = p_alias
5735 -- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
5736 /* BUG#28101405: To show only common locators in the LOV */
5737 AND a.project_id = p_project_id
5738 AND NVL(a.task_id, -9999) = NVL(p_task_id, -9999)
5739 ORDER BY 2;
5740
5741 ELSE --Locators not restricted
5742 --bug#3440453 Remove the NVL on organization_id if user passes it.
5743 IF p_organization_id IS NULL THEN
5744 OPEN x_Locators FOR
5745 SELECT inventory_location_id,
5746 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5747 locator_segments locsegs,
5748 description
5749 FROM wms_item_locations_kfv
5750 WHERE organization_id = Nvl(p_organization_id, organization_id)
5751 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5752 AND alias = p_alias
5753 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5754 AND project_id = p_project_id
5755 AND NVL(task_id, -1) = NVL(p_task_id, -1)
5756 ORDER BY 2;
5757 ELSE -- Organization_id is not null
5758 OPEN x_Locators FOR
5759 SELECT inventory_location_id,
5760 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5761 locator_segments locsegs,
5762 description
5763 FROM wms_item_locations_kfv
5764 WHERE organization_id = p_organization_id
5765 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5766 AND alias = p_alias
5767 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5768 AND project_id = p_project_id
5769 AND NVL(task_id, -1) = NVL(p_task_id, -1)
5770 ORDER BY 2;
5771 END IF;
5772 END IF;
5773
5774 ELSE /*PJM org project id null*/
5775
5776 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
5777 OPEN x_Locators FOR
5778 SELECT a.inventory_location_id,
5779 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5780 a.locator_segments locsegs,
5781 a.description
5782 FROM wms_item_locations_kfv a,
5783 mtl_secondary_locators b
5784 WHERE b.organization_id = p_Organization_Id
5785 AND b.inventory_item_id = p_Inventory_Item_Id
5786 AND b.subinventory_code = p_Subinventory_Code
5787 AND a.inventory_location_id = b.secondary_locator
5788 AND a.alias = p_alias
5789 -- AND a.concatenated_segments = nvl(p_suggestion, a.concatenated_segments)
5790 AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
5791 /* BUG#28101405: To show only common locators in the LOV */
5792 ORDER BY 2;
5793
5794 ELSE --Locators not restricted
5795 --bug#3440453 Remove the NVL on organization_id if user passes it.
5796 IF p_organization_id IS NULL THEN
5797 OPEN x_Locators FOR
5798 SELECT inventory_location_id,
5799 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5800 locator_segments locsegs,
5801 description
5802 FROM wms_item_locations_kfv
5803 WHERE organization_id = Nvl(p_organization_id, organization_id)
5804 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5805 AND alias = p_alias
5806 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5807 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5808 ORDER BY 2;
5809 ELSE -- Organization_id is not null
5810 OPEN x_Locators FOR
5811 SELECT inventory_location_id,
5812 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5813 locator_segments locsegs,
5814 description
5815 FROM wms_item_locations_kfv
5816 WHERE organization_id = p_organization_id
5817 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5818 AND alias = p_alias
5819 -- AND concatenated_segments = nvl(p_suggestion, concatenated_segments)
5820 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5821 ORDER BY 2;
5822 END IF;
5823 END IF;
5824 END IF;
5825 END IF;
5826 END get_inq_prj_loc_lov;
5827
5828
5829
5830
5831
5832 PROCEDURE get_inq_prj_loc_lov_nvl
5833 (x_Locators OUT NOCOPY t_genref,
5834 p_Organization_Id IN NUMBER,
5835 p_Subinventory_Code IN VARCHAR2,
5836 p_Restrict_Locators_Code IN NUMBER,
5837 p_Inventory_Item_Id IN NUMBER,
5838 p_Concatenated_Segments IN VARCHAR2,
5839 p_project_id IN NUMBER := NULL,
5840 p_task_id IN NUMBER := NULL)
5841 IS
5842 l_ispjm_org VARCHAR2(1);
5843 BEGIN
5844 BEGIN
5845 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
5846 INTO l_ispjm_org
5847 FROM pjm_org_parameters
5848 WHERE organization_id=p_organization_id;
5849 EXCEPTION
5850 WHEN NO_DATA_FOUND THEN
5851 l_ispjm_org:='N';
5852 END;
5853
5854 IF l_ispjm_org='N' THEN /*Non PJM Org*/
5855 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
5856 OPEN x_Locators FOR
5857 SELECT a.inventory_location_id,
5858 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5859 a.locator_segments locsegs,
5860 a.description
5861 FROM wms_item_locations_kfv a,
5862 mtl_secondary_locators b
5863 WHERE b.organization_id = p_Organization_Id
5864 AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
5865 AND b.subinventory_code = p_Subinventory_Code
5866 AND a.inventory_location_id = b.secondary_locator
5867 AND a.concatenated_segments LIKE (p_concatenated_segments )
5868 /* BUG#28101405: To show only common locators in the LOV */
5869 ORDER BY 2;
5870 ELSE --Locators not restricted
5871 --bug#3440453 Remove the NVL on organization_id if user passes it.
5872 IF p_organization_id IS NULL THEN
5873 OPEN x_Locators FOR
5874 SELECT inventory_location_id,
5875 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5876 locator_segments locsegs,
5877 description
5878 FROM wms_item_locations_kfv
5879 WHERE organization_id = Nvl(p_organization_id, organization_id)
5880 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5881 AND concatenated_segments LIKE (p_concatenated_segments )
5882 ORDER BY 2;
5883 ELSE -- Organization_id is not null
5884 OPEN x_Locators FOR
5885 SELECT inventory_location_id,
5886 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5887 locator_segments locsegs,
5888 description
5889 FROM wms_item_locations_kfv
5890 WHERE organization_id = p_organization_id
5891 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5892 AND concatenated_segments LIKE (p_concatenated_segments )
5893 ORDER BY 2;
5894 END IF;
5895 END IF;
5896 ELSE /*PJM org*/
5897 IF p_project_id IS NOT NULL THEN
5898
5899 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
5900 OPEN x_Locators FOR
5901 SELECT a.inventory_location_id,
5902 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5903 a.locator_segments locsegs,
5904 a.description
5905 FROM wms_item_locations_kfv a,
5906 mtl_secondary_locators b
5907 WHERE b.organization_id = p_Organization_Id
5908 AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
5909 AND b.subinventory_code = p_Subinventory_Code
5910 AND a.inventory_location_id = b.secondary_locator
5911 AND a.concatenated_segments LIKE (p_concatenated_segments )
5912 /* BUG#28101405: To show only common locators in the LOV */
5913 AND a.project_id = p_project_id
5914 AND NVL(a.task_id, -9999) = NVL(p_task_id, -9999)
5915 ORDER BY 2;
5916
5917 ELSE --Locators not restricted
5918 --bug#3440453 Remove the NVL on organization_id if user passes it.
5919 IF p_organization_id IS NULL THEN
5920 OPEN x_Locators FOR
5921 SELECT inventory_location_id,
5922 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5923 locator_segments locsegs,
5924 description
5925 FROM wms_item_locations_kfv
5926 WHERE organization_id = Nvl(p_organization_id, organization_id)
5927 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5928 AND concatenated_segments LIKE (p_concatenated_segments )
5929 AND project_id = p_project_id
5930 AND NVL(task_id, -1) = NVL(p_task_id, -1)
5931 ORDER BY 2;
5932 ELSE -- Organization_id is not null
5933 OPEN x_Locators FOR
5934 SELECT inventory_location_id,
5935 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5936 locator_segments locsegs,
5937 description
5938 FROM wms_item_locations_kfv
5939 WHERE organization_id = p_organization_id
5940 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5941 AND concatenated_segments LIKE (p_concatenated_segments )
5942 AND project_id = p_project_id
5943 AND NVL(task_id, -1) = NVL(p_task_id, -1)
5944 ORDER BY 2;
5945 END IF;
5946 END IF;
5947
5948 ELSE /*PJM org project id null*/
5949
5950 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
5951 OPEN x_Locators FOR
5952 SELECT a.inventory_location_id,
5953 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5954 a.locator_segments locsegs,
5955 a.description
5956 FROM wms_item_locations_kfv a,
5957 mtl_secondary_locators b
5958 WHERE b.organization_id = p_Organization_Id
5959 AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
5960 AND b.subinventory_code = p_Subinventory_Code
5961 AND a.inventory_location_id = b.secondary_locator
5962 AND a.concatenated_segments LIKE (p_concatenated_segments )
5963 AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
5964 /* BUG#28101405: To show only common locators in the LOV */
5965 ORDER BY 2;
5966
5967 ELSE --Locators not restricted
5968 --bug#3440453 Remove the NVL on organization_id if user passes it.
5969 IF p_organization_id IS NULL THEN
5970 OPEN x_Locators FOR
5971 SELECT inventory_location_id,
5972 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5973 locator_segments locsegs,
5974 description
5975 FROM wms_item_locations_kfv
5976 WHERE organization_id = Nvl(p_organization_id, organization_id)
5977 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5978 AND concatenated_segments LIKE (p_concatenated_segments )
5979 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5980 ORDER BY 2;
5981 ELSE -- Organization_id is not null
5982 OPEN x_Locators FOR
5983 SELECT inventory_location_id,
5984 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
5985 locator_segments locsegs,
5986 description
5987 FROM wms_item_locations_kfv
5988 WHERE organization_id = p_organization_id
5989 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
5990 AND concatenated_segments LIKE (p_concatenated_segments )
5991 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5992 ORDER BY 2;
5993 END IF;
5994 END IF;
5995 END IF;
5996 END IF;
5997 END get_inq_prj_loc_lov_nvl;
5998 PROCEDURE get_inq_prj_loc_lov_nvl(
5999 x_Locators OUT NOCOPY t_genref,
6000 p_Organization_Id IN NUMBER,
6001 p_Subinventory_Code IN VARCHAR2,
6002 p_Restrict_Locators_Code IN NUMBER,
6003 p_Inventory_Item_Id IN NUMBER,
6004 p_Concatenated_Segments IN VARCHAR2,
6005 p_project_id IN NUMBER := NULL,
6006 p_task_id IN NUMBER := NULL,
6007 p_alias IN VARCHAR2)
6008 IS
6009 l_ispjm_org VARCHAR2(1);
6010 BEGIN
6011 IF p_alias IS NULL THEN
6012 get_inq_prj_loc_lov_nvl(
6013 x_Locators => x_locators
6014 ,p_Organization_Id => p_organization_id
6015 ,p_Subinventory_Code => p_subinventory_code
6016 ,p_Restrict_Locators_Code => p_restrict_locators_code
6017 ,p_Inventory_Item_Id => p_inventory_item_id
6018 ,p_Concatenated_Segments => p_concatenated_segments
6019 ,p_project_id => p_project_id
6020 ,p_task_id => p_task_id
6021 );
6022 RETURN;
6023 END IF;
6024 BEGIN
6025 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
6026 INTO l_ispjm_org
6027 FROM pjm_org_parameters
6028 WHERE organization_id=p_organization_id;
6029 EXCEPTION
6030 WHEN NO_DATA_FOUND THEN
6031 l_ispjm_org:='N';
6032 END;
6033
6034 IF l_ispjm_org='N' THEN /*Non PJM Org*/
6035 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
6036 OPEN x_Locators FOR
6037 SELECT a.inventory_location_id,
6038 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
6039 a.locator_segments locsegs,
6040 a.description
6041 FROM wms_item_locations_kfv a,
6042 mtl_secondary_locators b
6043 WHERE b.organization_id = p_Organization_Id
6044 AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
6045 AND b.subinventory_code = p_Subinventory_Code
6046 AND a.inventory_location_id = b.secondary_locator
6047 AND a.alias = p_alias
6048 /* BUG#28101405: To show only common locators in the LOV */
6049 ORDER BY 2;
6050 ELSE --Locators not restricted
6051 --bug#3440453 Remove the NVL on organization_id if user passes it.
6052 IF p_organization_id IS NULL THEN
6053 OPEN x_Locators FOR
6054 SELECT inventory_location_id,
6055 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
6056 locator_segments locsegs,
6057 description
6058 FROM wms_item_locations_kfv
6059 WHERE organization_id = Nvl(p_organization_id, organization_id)
6060 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
6061 AND alias = p_alias
6062 ORDER BY 2;
6063 ELSE -- Organization_id is not null
6064 OPEN x_Locators FOR
6065 SELECT inventory_location_id,
6066 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
6067 locator_segments locsegs,
6068 description
6069 FROM wms_item_locations_kfv
6070 WHERE organization_id = p_organization_id
6071 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
6072 AND alias = p_alias
6073 ORDER BY 2;
6074 END IF;
6075 END IF;
6076 ELSE /*PJM org*/
6077 IF p_project_id IS NOT NULL THEN
6078
6079 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
6080 OPEN x_Locators FOR
6081 SELECT a.inventory_location_id,
6082 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
6083 a.locator_segments locsegs,
6084 a.description
6085 FROM wms_item_locations_kfv a,
6086 mtl_secondary_locators b
6087 WHERE b.organization_id = p_Organization_Id
6088 AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
6089 AND b.subinventory_code = p_Subinventory_Code
6090 AND a.inventory_location_id = b.secondary_locator
6091 AND a.alias = p_alias
6092 /* BUG#28101405: To show only common locators in the LOV */
6093 AND a.project_id = p_project_id
6094 AND NVL(a.task_id, -9999) = NVL(p_task_id, -9999)
6095 ORDER BY 2;
6096
6097 ELSE --Locators not restricted
6098 --bug#3440453 Remove the NVL on organization_id if user passes it.
6099 IF p_organization_id IS NULL THEN
6100 OPEN x_Locators FOR
6101 SELECT inventory_location_id,
6102 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
6103 locator_segments locsegs,
6104 description
6105 FROM wms_item_locations_kfv
6106 WHERE organization_id = Nvl(p_organization_id, organization_id)
6107 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
6108 AND alias = p_alias
6109 AND project_id = p_project_id
6110 AND NVL(task_id, -1) = NVL(p_task_id, -1)
6111 ORDER BY 2;
6112 ELSE -- Organization_id is not null
6113 OPEN x_Locators FOR
6114 SELECT inventory_location_id,
6115 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
6116 locator_segments locsegs,
6117 description
6118 FROM wms_item_locations_kfv
6119 WHERE organization_id = p_organization_id
6120 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
6121 AND alias = p_alias
6122 AND project_id = p_project_id
6123 AND NVL(task_id, -1) = NVL(p_task_id, -1)
6124 ORDER BY 2;
6125 END IF;
6126 END IF;
6127
6128 ELSE /*PJM org project id null*/
6129
6130 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
6131 OPEN x_Locators FOR
6132 SELECT a.inventory_location_id,
6133 --a.concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
6134 a.locator_segments locsegs,
6135 a.description
6136 FROM wms_item_locations_kfv a,
6137 mtl_secondary_locators b
6138 WHERE b.organization_id = p_Organization_Id
6139 AND b.inventory_item_id = nvl(p_Inventory_Item_Id, b.inventory_item_id)
6140 AND b.subinventory_code = p_Subinventory_Code
6141 AND a.inventory_location_id = b.secondary_locator
6142 AND a.alias = p_alias
6143 AND a.inventory_location_id=NVL(a.physical_location_id,a.inventory_location_id)
6144 /* BUG#28101405: To show only common locators in the LOV */
6145 ORDER BY 2;
6146
6147 ELSE --Locators not restricted
6148 --bug#3440453 Remove the NVL on organization_id if user passes it.
6149 IF p_organization_id IS NULL THEN
6150 OPEN x_Locators FOR
6151 SELECT inventory_location_id,
6152 --concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
6153 locator_segments locsegs,
6154 description
6155 FROM wms_item_locations_kfv
6156 WHERE organization_id = Nvl(p_organization_id, organization_id)
6157 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
6158 AND alias = p_alias
6159 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
6160 ORDER BY 2;
6161 ELSE -- Organization_id is not null
6162 OPEN x_Locators FOR
6163 SELECT inventory_location_id,
6164 -- concatenated_segments locsegs,--Bug4398337:Commented this line and added below line
6165 locator_segments locsegs,
6166 description
6167 FROM wms_item_locations_kfv
6168 WHERE organization_id = p_organization_id
6169 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
6170 AND alias = p_alias
6171 AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
6172 ORDER BY 2;
6173 END IF;
6174 END IF;
6175 END IF;
6176 END IF;
6177 END get_inq_prj_loc_lov_nvl;
6178
6179
6180
6181
6182 /*Bug #3075665 - FOR PATCHSET J PROJECT - ADVANCED PICK LOAD
6183 * This procedure gets the locator details - concat segs, loc desc,
6184 * project, task, sub for a given org id, loc id.
6185 * The procedure also returns if the given locator exists or not.
6186 */
6187 PROCEDURE GET_PICKLOAD_LOC_DETAILS(
6188 p_organization_id IN NUMBER
6189 , p_inventory_location_id IN NUMBER
6190 , x_subinventory_code OUT NOCOPY VARCHAR2
6191 , x_concatenated_segments OUT NOCOPY VARCHAR2
6192 , x_description OUT NOCOPY VARCHAR2
6193 , x_project_id OUT NOCOPY NUMBER
6194 , x_task_id OUT NOCOPY NUMBER
6195 , x_loc_exists OUT NOCOPY VARCHAR
6196 , x_msg_count OUT NOCOPY NUMBER
6197 , x_msg_data OUT NOCOPY VARCHAR2
6198 , x_return_status OUT NOCOPY VARCHAR2 ) IS
6199 BEGIN
6200 x_loc_exists := 'Y';
6201 BEGIN
6202 DEBUG('INV_UI_ITEM_SUB_LOC_LOVS.GET_PICKLOAD_LOC_DETAILS');
6203 DEBUG('p_inventory_location_id -> '||p_inventory_location_id);
6204 DEBUG('p_organization_id -> '||p_organization_id);
6205 SELECT subinventory_code
6206 , CONCATENATED_SEGMENTS
6207 , DESCRIPTION
6208 , project_id
6209 , task_id
6210 INTO x_subinventory_code
6211 , x_concatenated_segments
6212 , x_description
6213 , x_project_id
6214 , x_task_id
6215 FROM wms_item_locations_kfv
6216 WHERE inventory_location_id = p_inventory_location_id
6217 AND organization_id = p_organization_id;
6218 EXCEPTION
6219 WHEN NO_DATA_FOUND THEN
6220 x_loc_exists := 'N';
6221 END;
6222
6223 IF(x_subinventory_code IS NULL) THEN
6224 x_loc_exists := 'N';
6225 END IF;
6226 DEBUG('x_loc_exists -> '||x_loc_exists);
6227
6228 END GET_PICKLOAD_LOC_DETAILS;
6229
6230
6231
6232 --
6233 ----------------------------------
6234 -- Name: GET_LOCATION_TYPE_LOCATORS
6235 -- To query locators of a sub and org without status check
6236 -- that is also filtered by mtl_item_locations.inventory_location_type
6237 -- Input Parameter:
6238 -- p_organization_id: Organization ID
6239 -- p_subinventory_code Sub
6240 -- p_inventory_location_type Location Type: Dock Door, Staging, Storage
6241 -- p_concatenated_segments LOV
6242 --
6243 PROCEDURE Get_Location_Type_Locators(
6244 x_locators OUT NOCOPY t_genref
6245 , p_organization_id IN NUMBER
6246 , p_subinventory_code IN VARCHAR2
6247 , p_inventory_location_type IN NUMBER
6248 , p_concatenated_segments IN VARCHAR2
6249 ) IS
6250 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6251 l_ispjm_org VARCHAR2(1);
6252 BEGIN
6253 BEGIN
6254 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
6255 INTO l_ispjm_org
6256 FROM pjm_org_parameters
6257 WHERE organization_id=p_organization_id;
6258 EXCEPTION
6259 WHEN NO_DATA_FOUND THEN
6260 l_ispjm_org:='N';
6261 END;
6262
6263 IF ( l_ispjm_org = 'N' )THEN
6264 OPEN x_Locators FOR
6265 SELECT inventory_location_id
6266 , locator_segments locsegs
6267 , description
6268 FROM wms_item_locations_kfv
6269 WHERE organization_id = p_organization_id
6270 AND subinventory_code = NVL(p_Subinventory_Code ,subinventory_code)
6271 AND inventory_location_type = p_inventory_location_type
6272 AND NVL(disable_date, trunc(sysdate+1)) > trunc(sysdate)
6273 AND concatenated_segments LIKE (p_concatenated_segments)
6274 ORDER BY 2;
6275 ELSE --PJM Org
6276 OPEN x_Locators FOR
6277 SELECT inventory_location_id
6278 , locator_segments locsegs
6279 , description
6280 FROM wms_item_locations_kfv
6281 WHERE organization_id = p_organization_id
6282 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
6283 AND inventory_location_type = p_inventory_location_type
6284 AND NVL(disable_date, trunc(sysdate+1)) > trunc(sysdate)
6285 AND concatenated_segments LIKE (p_concatenated_segments )
6286 AND inventory_location_id = NVL(physical_location_id,inventory_location_id)
6287 ORDER BY 2;
6288 END IF;
6289 END Get_Location_Type_Locators;
6290
6291 PROCEDURE Get_Location_Type_Locators(
6292 x_locators OUT NOCOPY t_genref
6293 , p_organization_id IN NUMBER
6294 , p_subinventory_code IN VARCHAR2
6295 , p_inventory_location_type IN NUMBER
6296 , p_concatenated_segments IN VARCHAR2
6297 , p_alias IN VARCHAR2
6298 ) IS
6299 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6300 l_ispjm_org VARCHAR2(1);
6301 BEGIN
6302
6303 IF p_alias IS NULL THEN
6304 Get_Location_Type_Locators(
6305 x_locators => x_locators
6306 , p_organization_id => p_organization_id
6307 , p_subinventory_code => p_subinventory_code
6308 , p_inventory_location_type => p_inventory_location_type
6309 , p_concatenated_segments => p_concatenated_segments
6310 );
6311 RETURN;
6312 END IF;
6313 BEGIN
6314 SELECT nvl(PROJECT_REFERENCE_ENABLED,'N')
6315 INTO l_ispjm_org
6316 FROM pjm_org_parameters
6317 WHERE organization_id=p_organization_id;
6318 EXCEPTION
6319 WHEN NO_DATA_FOUND THEN
6320 l_ispjm_org:='N';
6321 END;
6322
6323 IF ( l_ispjm_org = 'N' )THEN
6324 OPEN x_Locators FOR
6325 SELECT inventory_location_id
6326 , locator_segments locsegs
6327 , description
6328 FROM wms_item_locations_kfv
6329 WHERE organization_id = p_organization_id
6330 AND subinventory_code = NVL(p_Subinventory_Code ,subinventory_code)
6331 AND inventory_location_type = p_inventory_location_type
6332 AND NVL(disable_date, trunc(sysdate+1)) > trunc(sysdate)
6333 AND alias = p_alias
6334 ORDER BY 2;
6335 ELSE --PJM Org
6336 OPEN x_Locators FOR
6337 SELECT inventory_location_id
6338 , locator_segments locsegs
6339 , description
6340 FROM wms_item_locations_kfv
6341 WHERE organization_id = p_organization_id
6342 AND subinventory_code = Nvl(p_Subinventory_Code ,subinventory_code)
6343 AND inventory_location_type = p_inventory_location_type
6344 AND NVL(disable_date, trunc(sysdate+1)) > trunc(sysdate)
6345 AND inventory_location_id = NVL(physical_location_id,inventory_location_id)
6346 AND alias = p_alias
6347 ORDER BY 2;
6348 END IF;
6349 END Get_Location_Type_Locators;
6350
6351 PROCEDURE get_value_from_alias(
6352 x_return_status OUT NOCOPY VARCHAR2
6353 ,x_msg_data OUT NOCOPY VARCHAR2
6354 ,x_msg_count OUT NOCOPY NUMBER
6355 ,x_match OUT NOCOPY VARCHAR2
6356 ,x_value OUT NOCOPY VARCHAR2
6357 ,p_org_id IN NUMBER
6358 ,p_sub_code IN VARCHAR2
6359 ,p_alias IN VARCHAR2
6360 ,p_suggested IN VARCHAR2
6361 ) IS
6362 BEGIN
6363
6364 x_return_status := 'S';
6365 x_value := NULL;
6366 x_match := NULL;
6367 x_msg_data := NULL;
6368 x_msg_count := 0;
6369
6370 SELECT locator_segments
6371 INTO x_value
6372 FROM wms_item_locations_kfv
6373 WHERE alias = p_alias
6374 AND organization_id = p_org_id
6375 AND subinventory_code = p_sub_code
6376 AND project_id IS NULL
6377 AND task_id IS NULL;
6378
6379 EXCEPTION
6380 WHEN NO_DATA_FOUND THEN
6381 x_match := 'NONE';
6382 WHEN TOO_MANY_ROWS THEN
6383 IF p_suggested IS NOT NULL THEN
6384 BEGIN
6385 SELECT locator_segments
6386 INTO x_value
6387 FROM wms_item_locations_kfv
6388 WHERE alias = p_alias
6389 AND organization_id = p_org_id
6390 AND subinventory_code = p_sub_code
6391 AND locator_segments = p_suggested
6392 AND project_id IS NULL
6393 AND task_id IS NULL;
6394 EXCEPTION
6395 WHEN NO_DATA_FOUND THEN
6396 x_match := 'MULTIPLE';
6397 WHEN OTHERS THEN
6398 x_return_status := 'U';
6399 x_value := NULL;
6400 x_match := NULL;
6401 x_msg_data := SQLERRM;
6402 x_msg_count := 1;
6403 END;
6404 ELSE
6405 x_match := 'MULTIPLE';
6406 END IF;
6407 WHEN OTHERS THEN
6408 x_return_status := 'U';
6409 x_value := NULL;
6410 x_match := NULL;
6411 x_msg_data := SQLERRM;
6412 x_msg_count := 1;
6413 END get_value_from_alias;
6414
6415 END inv_ui_item_sub_loc_lovs;
[Home] [Help]