1 PACKAGE BODY inv_ui_item_att_lovs AS
2 /* $Header: INVITATB.pls 120.12 2008/04/14 12:42:51 abaid noship $ */
3
4 -- This is equivalent to inv_serial4 in the serial entry form INVTTESR
5 PROCEDURE get_serial_lov_rcv(x_serial_number OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_serial IN VARCHAR2, p_transaction_type_id IN NUMBER, p_wms_installed IN VARCHAR2) IS
6 BEGIN
7 OPEN x_serial_number FOR
8 SELECT serial_number
9 , current_subinventory_code
10 , current_locator_id
11 , lot_number
12 , 0
13 , current_status
14 , mms.status_code
15 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
16 WHERE inventory_item_id = p_item_id
17 AND (group_mark_id IS NULL
18 OR group_mark_id = -1
19 )
20 AND ((current_organization_id = p_organization_id
21 AND current_status = 1
22 )
23 OR (current_status = 4 AND
24 Nvl(to_number(fnd_profile.value('INV_RESTRICT_RCPT_SER')), 2) = 2
25 )
26 )
27 AND msn.status_id = mms.status_id(+)
28 AND mms.language (+) = userenv('LANG')
29 AND serial_number LIKE (p_serial)
30 AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
31 ORDER BY LPAD(serial_number, 20);
32 END get_serial_lov_rcv;
33
34 -- This is equivalent to inv_serial3 in the serial entry form INVTTESR
35
36 PROCEDURE get_serial_lov_rma_rcv(x_serial_number OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_serial IN VARCHAR2, p_transaction_type_id IN NUMBER, p_wms_installed IN VARCHAR2, p_oe_order_header_id IN NUMBER) IS
37
38 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
39 l_msg_count NUMBER;
40 l_msg_data VARCHAR2(4000);
41 l_errorcode VARCHAR2(4000);
42 l_enforce_rma_sn VARCHAR2(10);
43
44 BEGIN
45
46 -- Bug 3907968
47 -- Changes applicable for patchJ onwards
48 -- File needed for I branch is ARU: 3439979 and 3810978
49 -- GET the SERIAL ENFORCE paramneter from Receiving Options
50 -- IF enforce is YES
51 -- then
52 -- For all Order lines matching with the ITEM call INV_RMA_SERIAL_PVT.POPULATE_TEMP_TABLE
53 -- to populate the temporary serial table MTL_RMA_SERIAL_TEMP
54 -- Modify the LOV to join with MTL_RMA_SERIAL_TEMP
55 -- Else
56 -- the Existing LOV
57 -- End if
58
59 select nvl(ENFORCE_RMA_SERIAL_NUM,'N')
60 into l_enforce_rma_sn
61 from RCV_PARAMETERS
62 where organization_id = p_organization_id;
63
64 IF ( l_enforce_rma_sn = 'Y' and p_oe_order_header_id is not null) THEN
65
66 For c_rma_line in ( select line_id
67 FROM
68 OE_ORDER_LINES_all OEL,
69 OE_ORDER_HEADERS_all OEH
70 WHERE OEL.LINE_CATEGORY_CODE='RETURN'
71 AND OEL.INVENTORY_ITEM_ID = p_item_id
72 AND nvl(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) = p_organization_id
73 AND OEL.HEADER_ID = OEH.HEADER_ID
74 AND OEH.HEADER_ID = p_oe_order_header_id
75 AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY,0)
76 AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
77 )
78 Loop
79
80 INV_RMA_SERIAL_PVT.POPULATE_TEMP_TABLE(
81 p_api_version => 0.9
82 , p_init_msg_list => FND_API.G_FALSE
83 , p_commit => FND_API.G_FALSE
84 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
85 , x_return_status => l_return_status
86 , x_msg_count => l_msg_count
87 , x_msg_data => l_msg_data
88 , x_errorcode => l_errorcode
89 , p_rma_line_id => c_rma_line.LINE_ID
90 , p_org_id => P_ORGANIZATION_ID
91 , p_item_id => p_item_id
92 );
93
94 -- No error check from the Previous API.
95
96 End loop;
97
98 -- Set the new LOV below..
99 OPEN x_serial_number FOR
100 SELECT serial_number
101 , current_subinventory_code
102 , current_locator_id
103 , lot_number
104 , 0
105 , current_status
106 , mms.status_code
107 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
108 WHERE msn.inventory_item_id = p_item_id
109 AND (group_mark_id IS NULL
110 OR group_mark_id = -1
111 )
112 AND current_status = 4
113 AND msn.status_id = mms.status_id(+)
114 AND mms.language (+) = userenv('LANG')
115 AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
116 AND msn.serial_number LIKE (p_serial)
117 AND EXISTS ( select 'x' from mtl_rma_serial_temp msrt
118 where msrt.organization_id = p_organization_id
119 and msrt.inventory_item_id = p_item_id
120 and msrt.serial_number = msn.serial_number
121 and msrt.serial_number LIKE (p_serial)
122 )
123 ORDER BY LPAD(serial_number, 20);
124
125 Else
126 -- the OLD LOV will work and will not restrict
127 OPEN x_serial_number FOR
128 SELECT serial_number
129 , current_subinventory_code
130 , current_locator_id
131 , lot_number
132 , 0
133 , current_status
134 , mms.status_code
135 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
136 WHERE inventory_item_id = p_item_id
137 AND (group_mark_id IS NULL
138 OR group_mark_id = -1
139 )
140 AND current_status = 4
141 AND msn.status_id = mms.status_id(+)
142 AND mms.language (+) = userenv('LANG')
143 AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
144 AND serial_number LIKE (p_serial)
145 ORDER BY LPAD(serial_number, 20);
146 End if;
147 END get_serial_lov_rma_rcv;
148
149 -- This is equivalent to inv_serial7 in the serial entry form INVTTESR
150
151 -- Bug #3350460
152 -- Added a new parameter (default NULL) to pass the ID of the From LPN
153 -- The serials will be filtered on the LPNs they are a part of while shipping
154 -- This is applicable only if WMS and PO J are installed
155 PROCEDURE get_serial_lov_int_shp_rcv(
156 x_serial_number OUT NOCOPY t_genref
157 , p_organization_id IN NUMBER
158 , p_item_id IN NUMBER
159 , p_shipment_header_id IN NUMBER
160 , p_lot_num IN VARCHAR2
161 , p_serial IN VARCHAR2
162 , p_transaction_type_id IN NUMBER
163 , p_wms_installed IN VARCHAR2
164 , p_from_lpn_id IN NUMBER DEFAULT NULL
165 ) IS
166 l_src_org_lot_ctrl NUMBER := 1;
167 l_rcv_org_lot_ctrl NUMBER := 1;
168 l_src_org_srl_ctrl NUMBER := 1;
169 l_source_document_code rcv_shipment_lines.source_document_code%TYPE;
170 BEGIN
171
172 BEGIN
173 --Get the lot control in source and receiving orgs and the
174 --serial control code in the sending org
175 SELECT msi1.lot_control_code src_lot_ctrl
176 , msi1.serial_number_control_code src_srl_ctrl
177 , msi2.lot_control_code rcv_lot_ctrl
178 , rsl.source_document_code
179 INTO l_src_org_lot_ctrl
180 , l_src_org_srl_ctrl
181 , l_rcv_org_lot_ctrl
182 , l_source_document_code
183 FROM mtl_system_items msi1
184 , mtl_system_items msi2
185 , rcv_shipment_lines rsl
186 WHERE rsl.shipment_header_id = p_shipment_header_id
187 AND rsl.to_organization_id = p_organization_id
188 AND rsl.item_id = p_item_id
189 AND msi1.inventory_item_id = p_item_id
190 AND msi1.organization_id = rsl.from_organization_id
191 AND msi1.inventory_item_id = msi2.inventory_item_id
192 AND msi2.organization_id = p_organization_id
193 AND ROWNUM=1;
194 EXCEPTION
195 WHEN OTHERS THEN
196 l_src_org_lot_ctrl := 1;
197 l_src_org_srl_ctrl := 1;
198 l_rcv_org_lot_ctrl := 'INVENTORY';
199 END;
200
201 --For intransit shipment, if serial control code in source org is
202 --dynamic at SO Issue, serials would not be shipped and treat serial control code as 1
203 IF l_source_document_code = 'INVENTORY' AND l_src_org_srl_ctrl = 6 THEN
204 l_src_org_srl_ctrl := 1;
205 END IF;
206
207 --If the item is serial controlled in the source organization, then the
208 --shipped serials would be there in rcv_serials_supply and we should be
209 --filter the serials in RSS and MSN
210 IF (l_src_org_srl_ctrl <> 1) THEN
211 OPEN x_serial_number FOR
212 SELECT msn.serial_number
213 , ''
214 , 0
215 , rss.lot_num
216 , 0
217 , msn.current_status
218 , mms.status_code
219 FROM rcv_serials_supply rss
220 , rcv_shipment_lines rsl
221 , mtl_serial_numbers msn
222 , mtl_material_statuses_tl mms
223 WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
224 --BUG 3417870: The RSL.shipment_line_status_code will be FULLY
225 -- RECEIVED, so we need to comment it out.
226 -- AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
227 AND nvl(rss.supply_type_code, 'SHIPMENT') = 'SHIPMENT'
228 AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
229 AND rsl.shipment_header_id = p_shipment_header_id
230 AND rsl.to_organization_id = p_organization_id
231 AND rsl.item_id = p_item_id
232 AND msn.inventory_item_id = p_item_id
233 AND msn.serial_number = rss.serial_num
234 AND msn.current_status = 5
235 AND Nvl(msn.lpn_id,NVL(p_from_lpn_id,-1)) = NVL(p_from_lpn_id, NVL(msn.lpn_id, -1))
236 AND ( (l_rcv_org_lot_ctrl = 1 OR l_src_org_lot_ctrl = 1) OR
237 ((l_rcv_org_lot_ctrl = 2 AND l_src_org_lot_ctrl = 2) AND
238 (Nvl(rss.lot_num,'@@@') = Nvl(p_lot_num,'@@@')))
239 )
240 AND msn.status_id = mms.status_id(+)
241 AND mms.language (+) = userenv('LANG')
242 AND inv_material_status_grp.is_status_applicable(
243 p_wms_installed
244 , p_transaction_type_id
245 , NULL
246 , NULL
247 , p_organization_id
248 , p_item_id
249 , NULL
250 , NULL
251 , NULL
252 , msn.serial_number
253 , 'S') = 'Y'
254 AND msn.serial_number LIKE (p_serial)
255 ORDER BY LPAD(msn.serial_number, 20);
256
257 --If the item is not serial controlled in source org, then fetch the
258 --serials from mtl_serial_numbers which reside in the receiving org
259 -- bug #5508238, Displaying ISSUED OUT serials in the LOV if the profile
260 -- 'INV: Restrict receipt of serials' is set to "No"
261 ELSE
262 OPEN x_serial_number FOR
263 SELECT msn.serial_number
264 , ''
265 , 0
266 , p_lot_num
267 , 0
268 , msn.current_status
269 , mms.status_code
270 FROM mtl_serial_numbers msn
271 , rcv_shipment_lines rsl
272 , mtl_material_statuses_tl mms
273 WHERE msn.inventory_item_id = p_item_id
274 AND rsl.shipment_header_id = p_shipment_header_id
275 AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
276 AND ( ( msn.current_status IN (1, 6)
277 AND msn.current_organization_id = p_organization_id
278 ) OR
279 ( msn.current_status = 4
280 AND nvl(to_number(fnd_profile.value('INV_RESTRICT_RCPT_SER')), 2) = 2
281 ) )
282 AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
283 AND rsl.to_organization_id = p_organization_id
284 AND rsl.item_id = p_item_id
285 AND msn.status_id = mms.status_id(+)
286 AND mms.language (+) = userenv('LANG')
287 AND inv_material_status_grp.is_status_applicable(
288 p_wms_installed
289 , p_transaction_type_id
290 , NULL
291 , NULL
292 , p_organization_id
293 , p_item_id
294 , NULL
295 , NULL
296 , NULL
297 , msn.serial_number
298 , 'S') = 'Y'
299 AND msn.serial_number LIKE (p_serial)
300 ORDER BY LPAD(msn.serial_number, 20);
301 END IF; --END IF check serial control code in src org
302 END get_serial_lov_int_shp_rcv;
303
304 -- Name: GET_SERIAL_LOV_LMT
305 --
306 -- Input parameters:
307 -- p_Organization_Id which restricts LOV SQL to current org
308 -- p_item_id which restricts LOV SQL to current item
309 -- p_subinv_code restricts to Subinventory
310 -- p_locator_id restricts to Locator ID. If not used, set to -1
311 -- p_serial which restricts LOV SQL to the serial entered
312 --
313 -- Output parameters:
314 -- x_serial_number returns LOV rows as reference cursor
315 --
316 -- Functions: This API is to return serial numbers limited by
317 -- the specified Subinventory and Locator with status = 3;
318 --
319 PROCEDURE get_serial_lov_lmt(
320 x_serial_number OUT NOCOPY t_genref
321 , p_organization_id IN NUMBER
322 , p_item_id IN NUMBER
323 , p_subinv_code IN VARCHAR2
324 , p_locator_id IN NUMBER
325 , p_serial IN VARCHAR2
326 , p_transaction_type_id IN NUMBER
327 , p_wms_installed IN VARCHAR2
328 , p_group_mark_id IN NUMBER := NULL
329 ) IS
330 BEGIN
331 OPEN x_serial_number FOR
332 SELECT serial_number
333 , current_subinventory_code
334 , current_locator_id
335 , lot_number
336 , 0
337 , current_status
338 , mms.status_code
339 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
340 WHERE inventory_item_id = p_item_id
341 AND (group_mark_id IS NULL
342 OR group_mark_id = -1
343 OR group_mark_id in (select a.serial_transaction_temp_id from mtl_transaction_lots_temp a
344 where a.transaction_temp_id = p_group_mark_id)
345 OR group_mark_id = p_group_mark_id
346 )
347 AND (line_mark_id IS NULL
348 OR line_mark_id = -1
349 OR line_mark_id in (select a.serial_transaction_temp_id from mtl_transaction_lots_temp a
350 where a.transaction_temp_id = p_group_mark_id)
351 OR line_mark_id = p_group_mark_id)
352 AND current_organization_id = p_organization_id
353 AND current_status = 3
354 AND current_subinventory_code = p_subinv_code
355 AND msn.lpn_id IS NULL
356 AND NVL(current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, current_locator_id, p_locator_id), 0)
357 AND msn.status_id = mms.status_id(+)
358 AND mms.language (+) = userenv('LANG')
359 AND serial_number LIKE (p_serial)
360 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'S') = 'Y'
361 ORDER BY LPAD(serial_number, 20);
362 END;
363
364 PROCEDURE get_lot_info(
365 p_organization_id IN NUMBER
366 , p_inventory_item_id IN NUMBER
367 , p_lot_number IN VARCHAR2
368 , p_shelf_life_code IN NUMBER
369 , p_shelf_life_days IN NUMBER
370 , p_lot_status_enabled IN VARCHAR2
371 , p_default_lot_status_id IN NUMBER
372 , p_wms_installed IN VARCHAR2
373 , x_expiration_date OUT NOCOPY DATE
374 , x_is_new_lot OUT NOCOPY VARCHAR2
375 , x_is_valid_lot OUT NOCOPY VARCHAR2
376 , x_lot_status OUT NOCOPY VARCHAR2
377 ) IS
378 l_valid_lot BOOLEAN := TRUE;
379 l_wms_installed VARCHAR2(10) := 'FALSE';
380 l_number NUMBER;
381 BEGIN
382 x_expiration_date := '';
383 x_lot_status := '';
384
385 IF (p_wms_installed = 'I'
386 OR p_wms_installed = 'TRUE'
387 ) THEN
388 l_wms_installed := 'TRUE';
389 END IF;
390
391 l_valid_lot := inv_lot_api_pub.validate_unique_lot(p_organization_id, p_inventory_item_id, '', p_lot_number);
392
393 IF l_valid_lot THEN
394 x_is_valid_lot := 'TRUE';
395 ELSE
396 x_is_valid_lot := 'FALSE';
397 RETURN;
398 END IF;
399
400 IF p_shelf_life_code = 1 THEN
401 BEGIN
402 SELECT status_id
403 INTO l_number
404 FROM mtl_lot_numbers
405 WHERE organization_id = p_organization_id
406 AND inventory_item_id = p_inventory_item_id
407 AND lot_number = p_lot_number;
408
409 x_is_new_lot := 'FALSE';
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 x_is_new_lot := 'TRUE';
413 END;
414
415 IF (l_wms_installed = 'TRUE'
416 AND p_lot_status_enabled = 'Y'
417 ) THEN
418 BEGIN
419 SELECT NVL(status_code, '')
420 INTO x_lot_status
421 FROM mtl_material_statuses_tl mms
422 WHERE mms.status_id = NVL(l_number, p_default_lot_status_id)
423 AND mms.language = userenv('LANG');
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 x_lot_status := '';
427 END;
428 END IF;
429
430 RETURN;
431 ELSE
432 BEGIN
433 SELECT expiration_date
434 , NVL(status_code, '')
435 INTO x_expiration_date
436 , x_lot_status
437 FROM mtl_lot_numbers_all_v
438 WHERE organization_id = p_organization_id
439 AND inventory_item_id = p_inventory_item_id
440 AND lot_number = p_lot_number
441 AND ROWNUM < 2
442 ORDER BY expiration_date;
443
444 x_is_new_lot := 'FALSE';
445
446 IF x_expiration_date IS NULL THEN
447 IF p_shelf_life_code = 2 THEN
448 SELECT (SYSDATE + NVL(p_shelf_life_days, 0))
449 INTO x_expiration_date
450 FROM DUAL;
451 END IF;
452 END IF;
453
454 RETURN;
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 x_is_new_lot := 'TRUE';
458
459 IF (l_wms_installed = 'TRUE'
460 AND p_lot_status_enabled = 'Y'
461 ) THEN
462 BEGIN
463 SELECT NVL(status_code, '')
464 INTO x_lot_status
465 FROM mtl_material_statuses_tl mms
466 WHERE mms.status_id = p_default_lot_status_id
467 AND mms.language = userenv('LANG');
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 x_lot_status := '';
471 END;
472 END IF;
473
474 IF p_shelf_life_code = 2 THEN
475 SELECT (SYSDATE + NVL(p_shelf_life_days, 0))
476 INTO x_expiration_date
477 FROM DUAL;
478 END IF;
479
480 RETURN;
481 END;
482 END IF;
483
484 RETURN;
485 END get_lot_info;
486
487 -- procedure to get the serial information in case of a dynamically entered
488 -- serial number.
489 PROCEDURE get_serial_info(p_item_id IN NUMBER, p_serial IN VARCHAR2, p_serial_status_enabled IN VARCHAR2, p_default_serial_status IN NUMBER, p_wms_installed IN VARCHAR2, x_current_status OUT NOCOPY VARCHAR2, x_serial_status OUT NOCOPY VARCHAR2) IS
490 l_wms_installed VARCHAR2(10) := 'FALSE';
491 BEGIN
492 IF (p_wms_installed = 'I'
493 OR p_wms_installed = 'TRUE'
494 ) THEN
495 l_wms_installed := 'TRUE';
496 END IF;
497
498 BEGIN
499 -- Bug 2263020
500 -- Modified the following to fix the problem where the STATUS field
501 -- shows in the mobile page even when item is not serial status enabled
502 -- The value for x_serial_status needs to be set only when the serial
503 -- is SERIAL STATUS ENABLED.
504 x_serial_status := '';
505
506 IF (l_wms_installed = 'TRUE'
507 AND p_serial_status_enabled = 'Y'
508 ) THEN
509 SELECT msn.current_status
510 , NVL(mms.status_code, '')
511 INTO x_current_status
512 , x_serial_status
513 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
514 WHERE msn.inventory_item_id = p_item_id
515 AND msn.serial_number = p_serial
516 AND msn.status_id = mms.status_id(+)
517 AND mms.language (+) = userenv('LANG');
518 ELSE
519 SELECT msn.current_status
520 INTO x_current_status
521 FROM mtl_serial_numbers msn
522 WHERE msn.inventory_item_id = p_item_id
523 AND msn.serial_number = p_serial;
524 END IF;
525 EXCEPTION
526 WHEN NO_DATA_FOUND THEN
527 x_current_status := 6;
528
529 IF (l_wms_installed = 'TRUE'
530 AND p_serial_status_enabled = 'Y'
531 ) THEN
532 BEGIN
533 SELECT NVL(mms.status_code, '')
534 INTO x_serial_status
535 FROM mtl_material_statuses_tl mms
536 WHERE mms.status_id = p_default_serial_status
537 AND mms.language (+) = userenv('LANG');
538 EXCEPTION
539 WHEN OTHERS THEN
540 x_serial_status := '';
541 END;
542 END IF;
543 END;
544 END get_serial_info;
545
546 --During an issue, if it is the first serial number then
547 --we can accept any serial that resides in stores
548 --however, after the first serial has been scanned we must
549 --make sure that all subsequent serials are from the same
550 --locator and same sub.
551 --Consignment and VMI Changes - Added Planning Org and TP Type and Owning Org and TP Type.
552 PROCEDURE get_valid_serial_issue(
553 x_rserials OUT NOCOPY t_genref
554 , p_current_organization_id IN NUMBER
555 , p_revision IN VARCHAR2
556 , p_current_subinventory_code IN VARCHAR2
557 , p_current_locator_id IN NUMBER
558 , p_current_lot_number IN VARCHAR2
559 , p_inventory_item_id IN NUMBER
560 , p_serial_number IN VARCHAR2
561 , p_transaction_type_id IN NUMBER
562 , p_wms_installed IN VARCHAR2
563 , p_lpn_id IN NUMBER
564 , p_planning_org_id IN NUMBER
565 , p_planning_tp_type IN NUMBER
566 , p_owning_org_id IN NUMBER
567 , p_owning_tp_type IN NUMBER
568 ) IS
569 BEGIN
570 IF p_current_subinventory_code IS NULL THEN
571 OPEN x_rserials FOR
572 SELECT a.serial_number
573 , a.current_subinventory_code
574 , a.current_locator_id
575 , a.lot_number
576 , b.expiration_date
577 , a.current_status
578 , mms.status_code
579 , inv_project.get_locsegs(a.current_locator_id, p_current_organization_id)
580 , inv_project.get_project_id
581 , inv_project.get_project_number
582 , inv_project.get_task_id
583 , inv_project.get_task_number
584 FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
585 WHERE a.current_organization_id = p_current_organization_id
586 AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
587 AND a.inventory_item_id = p_inventory_item_id
588 AND (a.group_mark_id IS NULL OR a.group_mark_id = -1)
589 AND ((a.revision = p_revision)
590 OR (a.revision IS NULL AND p_revision IS NULL))
591 AND a.current_status = 3
592 AND b.inventory_item_id(+) = a.inventory_item_id
593 AND b.organization_id(+) = a.current_organization_id
594 AND b.lot_number(+) = a.lot_number
595 AND mms.status_id(+) = a.status_id
596 AND mms.language (+) = userenv('LANG')
597 AND a.serial_number LIKE (p_serial_number)
598 AND (p_planning_org_id IS NULL
599 OR planning_organization_id = p_planning_org_id)
600 AND (p_planning_tp_type IS NULL
601 OR planning_tp_type = p_planning_tp_type)
602 AND (p_owning_org_id IS NULL
603 OR owning_organization_id = p_owning_org_id)
604 AND (p_owning_tp_type IS NULL
605 OR owning_tp_type = p_owning_tp_type)
606 AND a.serial_number LIKE (p_serial_number)
607 AND inv_material_status_grp.is_status_applicable(
608 p_wms_installed
609 , NULL
610 , p_transaction_type_id
611 , NULL
612 , NULL
613 , p_current_organization_id
614 , p_inventory_item_id
615 , a.current_subinventory_code
616 , a.current_locator_id
617 , a.lot_number
618 , a.serial_number
619 , 'A'
620 ) = 'Y' -- modified by mxgupta because we want to check all statuses (lot and serial)
621 ORDER BY a.serial_number;
622 ELSE
623 OPEN x_rserials FOR
624 SELECT a.serial_number
625 , a.current_subinventory_code
626 , NVL(a.current_locator_id, -1)
627 , a.lot_number
628 , b.expiration_date
629 , a.current_status
630 , mms.status_code
631 , inv_project.get_locsegs(a.current_locator_id, p_current_organization_id)
632 , inv_project.get_project_id
633 , inv_project.get_project_number
634 , inv_project.get_task_id
635 , inv_project.get_task_number
636 FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
637 WHERE a.current_organization_id = p_current_organization_id
638 AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
639 AND a.inventory_item_id = p_inventory_item_id
640 AND a.current_subinventory_code = p_current_subinventory_code
641 AND (a.group_mark_id IS NULL OR a.group_mark_id = -1)
642 AND a.current_status = 3
643 AND mms.status_id(+) = a.status_id
644 AND mms.language (+) = userenv('LANG')
645 AND ((a.revision = p_revision)
646 OR (a.revision IS NULL AND p_revision IS NULL))
647 AND ((a.current_locator_id = p_current_locator_id)
648 OR (a.current_locator_id IS NULL
649 AND (p_current_locator_id IS NULL OR p_current_locator_id = -1))) -- Bug2564817
650 AND b.inventory_item_id(+) = a.inventory_item_id
651 AND b.organization_id(+) = a.current_organization_id
652 AND b.lot_number(+) = a.lot_number
653 AND (p_planning_org_id IS NULL
654 OR planning_organization_id = p_planning_org_id)
655 AND (p_planning_tp_type IS NULL
656 OR planning_tp_type = p_planning_tp_type)
657 AND (p_owning_org_id IS NULL
658 OR owning_organization_id = p_owning_org_id)
659 AND (p_owning_tp_type IS NULL
660 OR owning_tp_type = p_owning_tp_type)
661 AND a.serial_number LIKE (p_serial_number)
662 AND inv_material_status_grp.is_status_applicable(
663 p_wms_installed
664 , NULL
665 , p_transaction_type_id
666 , NULL
667 , NULL
668 , p_current_organization_id
669 , p_inventory_item_id
670 , p_current_subinventory_code
671 , a.current_locator_id
672 , a.lot_number
673 , a.serial_number
674 , 'S'
675 ) = 'Y'
676 ORDER BY a.serial_number;
677 END IF;
678 END get_valid_serial_issue;
679
680 PROCEDURE get_cost_group_lov(x_cost_group OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN VARCHAR2, p_subinventory_code IN VARCHAR2, p_locator_id IN VARCHAR2, p_cost_group IN VARCHAR2) IS
681 BEGIN
682 OPEN x_cost_group FOR
683 SELECT cost_group
684 , cost_group_id
685 , description
686 FROM cst_cost_groups
687 WHERE NVL(organization_id, p_organization_id) = p_organization_id
688 AND cost_group_type = 3
689 AND cost_group LIKE (p_cost_group)
690 AND cost_group_id IN (SELECT cost_group_id
691 FROM mtl_onhand_quantities_detail moq
692 WHERE organization_id = p_organization_id
693 AND NVL(subinventory_code, '@') = NVL(p_subinventory_code, NVL(subinventory_code, '@'))
694 AND NVL(locator_id, -999) = NVL(TO_NUMBER(p_locator_id), NVL(locator_id, -999))
695 AND inventory_item_id = NVL(TO_NUMBER(p_inventory_item_id), inventory_item_id));
696 END get_cost_group_lov;
697
698 PROCEDURE get_phyinv_serial_lov(
699 x_serials OUT NOCOPY t_genref
700 , p_organization_id IN NUMBER
701 , p_subinventory_code IN VARCHAR2
702 , p_locator_id IN NUMBER
703 , p_inventory_item_id IN NUMBER
704 , p_serial_number IN VARCHAR2
705 , p_lot_number IN VARCHAR2
706 , p_dynamic_entry_flag IN NUMBER
707 , p_physical_inventory_id IN NUMBER
708 , p_parent_lpn_id IN NUMBER
709 ) IS
710 BEGIN
711 IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
712 OPEN x_serials FOR
713 SELECT serial_number
714 ,current_subinventory_code
715 ,current_locator_id
716 ,lot_number
717 ,0
718 ,current_status
719 ,status_code
720 FROM
721 (
722 SELECT serial_number
723 ,current_subinventory_code
724 ,current_locator_id
725 ,lot_number
726 ,0
727 ,current_status
728 ,mms.status_code
729 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
730 WHERE inventory_item_id = p_inventory_item_id
731 AND (group_mark_id IS NULL
732 OR group_mark_id = -1
733 )
734 AND ((current_organization_id = p_organization_id
735 AND current_status IN (1, 3, 4, 6)
736 )
737 OR current_status = 5
738 )
739 AND msn.current_subinventory_code = p_subinventory_code
740 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
741 AND (NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
742 OR current_status IN (1, 6)
743 ) --newly generated
744 AND serial_number LIKE (p_serial_number)
745 AND msn.status_id = mms.status_id(+)
746 AND mms.language (+) = userenv('LANG')
747 UNION
748 SELECT serial_number
749 ,current_subinventory_code
750 ,current_locator_id
751 ,lot_number
752 ,0
753 ,current_status
754 ,mms.status_code
755 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
756 WHERE inventory_item_id = p_inventory_item_id
757 AND (group_mark_id IS NULL OR group_mark_id = -1)
758 AND ((current_organization_id = p_organization_id
759 AND current_status =1
760 )
761 OR current_status = 5
762 )
763 AND serial_number LIKE (p_serial_number)
764 AND msn.status_id = mms.status_id(+)
765 AND mms.language (+) = userenv('LANG')
766 ) ORDER BY SERIAL_NUMBER;
767 ELSE -- Dynamic entries are not allowed
768 OPEN x_serials FOR
769 SELECT UNIQUE msn.serial_number
770 , msn.current_subinventory_code
771 , msn.current_locator_id
772 , msn.lot_number
773 , 0
774 , msn.current_status
775 , mms.status_code
776 FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
777 WHERE msn.inventory_item_id = p_inventory_item_id
778 AND (msn.group_mark_id IS NULL
779 OR msn.group_mark_id = -1
780 )
781 AND ((msn.current_organization_id = p_organization_id
782 AND msn.current_status IN (3, 4)
783 )
784 OR msn.current_status = 5
785 )
786 AND msn.current_subinventory_code = p_subinventory_code
787 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
788 AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
789 AND msn.serial_number LIKE (p_serial_number)
790 AND msn.serial_number = mpit.serial_num
791 AND msn.status_id = mms.status_id(+)
792 AND mms.language (+) = userenv('LANG')
793 AND mpit.physical_inventory_id = p_physical_inventory_id
794 AND mpit.inventory_item_id = p_inventory_item_id
795 AND mpit.organization_id = p_organization_id
796 AND NVL(mpit.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
797 AND NVL(mpit.void_flag, 2) = 2
798 AND mpit.adjustment_id IN (SELECT adjustment_id
799 FROM mtl_physical_adjustments
800 WHERE physical_inventory_id = p_physical_inventory_id
801 AND organization_id = p_organization_id
802 AND approval_status IS NULL)
803 ORDER BY LPAD(msn.serial_number, 20);
804 END IF;
805 END get_phyinv_serial_lov;
806
807 PROCEDURE get_phyinv_to_serial_lov(
808 x_serials OUT NOCOPY t_genref
809 , p_organization_id IN NUMBER
810 , p_subinventory_code IN VARCHAR2
811 , p_locator_id IN NUMBER
812 , p_inventory_item_id IN NUMBER
813 , p_to_serial_number IN VARCHAR2
814 , p_lot_number IN VARCHAR2
815 , p_dynamic_entry_flag IN NUMBER
816 , p_physical_inventory_id IN NUMBER
817 , p_from_serial_number IN VARCHAR2
818 , p_parent_lpn_id IN NUMBER
819 ) IS
820 l_prefix VARCHAR2(30);
821 l_quantity NUMBER;
822 l_from_number NUMBER;
823 l_to_number NUMBER;
824 l_errorcode NUMBER;
825 l_temp_boolean BOOLEAN;
826 BEGIN
827 l_temp_boolean := mtl_serial_check.inv_serial_info(p_from_serial_number, NULL, l_prefix, l_quantity, l_from_number, l_to_number, l_errorcode);
828
829 IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
830 OPEN x_serials FOR
831 SELECT serial_number
832 ,current_subinventory_code
833 ,current_locator_id
834 ,lot_number
835 ,0
836 ,current_status
837 ,status_code
838 FROM (SELECT serial_number, current_subinventory_code, current_locator_id,
839 lot_number, 0, current_status, mms.status_code
840 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
841 WHERE inventory_item_id = p_inventory_item_id
842 AND (group_mark_id IS NULL OR group_mark_id = -1)
843 AND (( current_organization_id = p_organization_id
844 AND current_status IN (1, 3, 4, 6)
845 )
846 OR current_status = 5
847 )
848 AND msn.current_subinventory_code = p_subinventory_code
849 AND NVL (msn.current_locator_id, -99999) = NVL (p_locator_id,-99999)
850 AND (NVL (msn.lot_number, '###') = NVL (p_lot_number, '###')
851 OR current_status IN (1, 6)
852 ) --newly generated
853 AND serial_number LIKE (p_to_serial_number)
854 AND serial_number LIKE (l_prefix || '%')
855 AND msn.status_id = mms.status_id(+)
856 AND mms.LANGUAGE(+) = USERENV ('LANG')
857 AND serial_number > p_from_serial_number
858 -- Bug# 2770853. Honor the serial material status for physical inventory adjustments.
859 AND inv_material_status_grp.is_status_applicable
860 (NULL,
861 NULL,
862 8,
863 NULL,
864 'Y',
865 p_organization_id,
866 p_inventory_item_id,
867 NULL,
868 NULL,
869 NULL,
870 msn.serial_number,
871 'S'
872 ) = 'Y'
873 UNION
874 SELECT serial_number, current_subinventory_code, current_locator_id,
875 lot_number, 0, current_status, mms.status_code
876 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
877 WHERE inventory_item_id = p_inventory_item_id
878 AND (group_mark_id IS NULL OR group_mark_id = -1)
879 AND ( ( current_organization_id = p_organization_id
880 AND current_status = 1
881 )
882 OR current_status = 5
883 )
884 AND serial_number LIKE (p_to_serial_number)
885 AND serial_number LIKE (l_prefix || '%')
886 AND msn.status_id = mms.status_id(+)
887 AND mms.LANGUAGE(+) = USERENV ('LANG')
888 AND serial_number > p_from_serial_number
889 -- Bug# 2770853. Honor the serial material status for physical inventory adjustments.
890 AND inv_material_status_grp.is_status_applicable
891 (NULL,
892 NULL,
893 8,
894 NULL,
895 'Y',
896 p_organization_id,
897 p_inventory_item_id,
898 NULL,
899 NULL,
900 NULL,
901 msn.serial_number,
902 'S'
903 ) = 'Y'
904 ) ORDER BY serial_number;
905 ELSE -- Dynamic entries are not allowed
906 OPEN x_serials FOR
907 SELECT UNIQUE msn.serial_number
908 , msn.current_subinventory_code
909 , msn.current_locator_id
910 , msn.lot_number
911 , 0
912 , msn.current_status
913 , mms.status_code
914 FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
915 WHERE msn.inventory_item_id = p_inventory_item_id
916 AND (msn.group_mark_id IS NULL
917 OR msn.group_mark_id = -1
918 )
919 AND ((msn.current_organization_id = p_organization_id
920 AND msn.current_status IN (3, 4)
921 )
922 OR msn.current_status = 5
923 )
924 AND msn.current_subinventory_code = p_subinventory_code
925 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
926 AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
927 AND msn.serial_number LIKE (p_to_serial_number)
928 AND msn.serial_number LIKE (l_prefix || '%')
929 AND msn.serial_number = mpit.serial_num
930 AND mpit.physical_inventory_id = p_physical_inventory_id
931 AND mpit.inventory_item_id = p_inventory_item_id
932 AND mpit.organization_id = p_organization_id
933 AND msn.status_id = mms.status_id(+)
934 AND mms.language (+) = userenv('LANG')
935 AND msn.serial_number > p_from_serial_number
936 AND NVL(mpit.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
937 AND NVL(mpit.void_flag, 2) = 2
938 AND mpit.adjustment_id IN (SELECT adjustment_id
939 FROM mtl_physical_adjustments
940 WHERE physical_inventory_id = p_physical_inventory_id
941 AND organization_id = p_organization_id
942 AND approval_status IS NULL)
943 -- Bug# 2770853
944 -- Honor the serial material status for physical inventory adjustments
945 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
946 NULL,
947 8,
948 NULL,
949 'Y',
950 p_organization_id,
951 p_inventory_item_id,
952 NULL,
953 NULL,
954 NULL,
955 msn.serial_number,
956 'S') = 'Y'
957 ORDER BY LPAD(msn.serial_number, 20);
958 END IF;
959 END get_phyinv_to_serial_lov;
960
961 PROCEDURE get_phyinv_serial_count_lov(
962 x_serials OUT NOCOPY t_genref
963 , p_organization_id IN NUMBER
964 , p_subinventory_code IN VARCHAR2
965 , p_locator_id IN NUMBER
966 , p_serial_number IN VARCHAR2
967 , p_dynamic_entry_flag IN NUMBER
968 , p_physical_inventory_id IN NUMBER
969 ) IS
970 BEGIN
971 IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
972 OPEN x_serials FOR
973 SELECT msn.serial_number
974 , msn.current_subinventory_code
975 , msn.current_locator_id
976 , msn.inventory_item_id
977 , msik.concatenated_segments
978 , msn.revision
979 , msn.lot_number
980 , msn.lpn_id
981 , wlpn.license_plate_number
982 , msn.current_status
983 , msik.primary_uom_code
984 FROM mtl_serial_numbers msn, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
985 WHERE (msn.group_mark_id IS NULL
986 OR msn.group_mark_id = -1
987 )
988 AND ((msn.current_organization_id = p_organization_id
989 AND msn.current_status IN (1, 3, 4, 6)
990 )
991 OR msn.current_status = 5
992 )
993 AND msn.serial_number LIKE (p_serial_number)
994 AND msn.inventory_item_id = msik.inventory_item_id
995 AND msn.current_organization_id = msik.organization_id
996 AND wlpn.lpn_id(+) = msn.lpn_id
997 -- Bug# 2770853
998 -- Honor the serial material status for physical inventory adjustments
999 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1000 NULL,
1001 8,
1002 NULL,
1003 'Y',
1004 p_organization_id,
1005 msn.inventory_item_id,
1006 NULL,
1007 NULL,
1008 NULL,
1009 msn.serial_number,
1010 'S') = 'Y'
1011 ORDER BY LPAD(msn.serial_number, 20);
1012 ELSE -- Dynamic entries are not allowed
1013 OPEN x_serials FOR
1014 SELECT UNIQUE msn.serial_number
1015 , msn.current_subinventory_code
1016 , msn.current_locator_id
1017 , msn.inventory_item_id
1018 , msik.concatenated_segments
1019 , msn.revision
1020 , msn.lot_number
1021 , msn.lpn_id
1022 , wlpn.license_plate_number
1023 , msn.current_status
1024 , msik.primary_uom_code
1025 FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1026 WHERE (msn.group_mark_id IS NULL
1027 OR msn.group_mark_id = -1
1028 )
1029 AND msn.current_organization_id = p_organization_id
1030 AND msn.current_subinventory_code = p_subinventory_code
1031 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
1032 AND msn.serial_number LIKE (p_serial_number)
1033 AND msn.serial_number = mpit.serial_num
1034 AND msn.inventory_item_id = mpit.inventory_item_id
1035 AND NVL(msn.lpn_id, -99999) = NVL(mpit.parent_lpn_id, -99999)
1036 AND mpit.physical_inventory_id = p_physical_inventory_id
1037 AND mpit.organization_id = p_organization_id
1038 AND mpit.subinventory = p_subinventory_code
1039 AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
1040 AND NVL(mpit.void_flag, 2) = 2
1041 AND mpit.tag_quantity IS NULL
1042 AND mpit.adjustment_id IN (SELECT adjustment_id
1043 FROM mtl_physical_adjustments
1044 WHERE physical_inventory_id = p_physical_inventory_id
1045 AND organization_id = p_organization_id
1046 AND approval_status IS NULL)
1047 AND msn.inventory_item_id = msik.inventory_item_id
1048 AND msn.current_organization_id = msik.organization_id
1049 AND wlpn.lpn_id(+) = msn.lpn_id
1050 -- Bug# 2770853
1051 -- Honor the serial material status for physical inventory adjustments
1052 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1053 NULL,
1054 8,
1055 NULL,
1056 'Y',
1057 p_organization_id,
1058 msn.inventory_item_id,
1059 NULL,
1060 NULL,
1061 NULL,
1062 msn.serial_number,
1063 'S') = 'Y'
1064 ORDER BY LPAD(msn.serial_number, 20);
1065 END IF;
1066 END get_phyinv_serial_count_lov;
1067
1068 PROCEDURE get_cyc_serial_lov(
1069 x_serials OUT NOCOPY t_genref
1070 , p_organization_id IN NUMBER
1071 , p_subinventory_code IN VARCHAR2
1072 , p_locator_id IN NUMBER
1073 , p_inventory_item_id IN NUMBER
1074 , p_serial_number IN VARCHAR2
1075 , p_lot_number IN VARCHAR2
1076 , p_unscheduled_entry IN NUMBER
1077 , p_cycle_count_header_id IN NUMBER
1078 , p_parent_lpn_id IN NUMBER
1079 , p_serial_count_option IN NUMBER
1080 ) IS
1081 l_serial_discrepancy_option NUMBER;
1082 l_container_discrepancy_option NUMBER;
1083 l_orientation_code NUMBER;
1084 BEGIN
1085 -- Get the cycle count discrepancy option flags and orientation code
1086 SELECT NVL(serial_discrepancy_option, 2),
1087 NVL(container_discrepancy_option, 2),
1088 NVL(orientation_code, 1)
1089 INTO l_serial_discrepancy_option, l_container_discrepancy_option,
1090 l_orientation_code
1091 FROM mtl_cycle_count_headers
1092 WHERE cycle_count_header_id = p_cycle_count_header_id;
1093
1094 IF (p_unscheduled_entry = 1) THEN
1095 -- Unscheduled entries are allowed
1096 OPEN x_serials FOR
1097 SELECT serial_number
1098 , current_subinventory_code
1099 , current_locator_id
1100 , lot_number
1101 , 0
1102 , current_status
1103 , mms.status_code
1104 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1105 WHERE inventory_item_id = p_inventory_item_id
1106 AND (group_mark_id IS NULL
1107 OR group_mark_id = -1
1108 )
1109 AND ((current_organization_id = p_organization_id
1110 AND current_status IN (1, 3, 4, 6)
1111 )
1112 OR current_status = 5
1113 )
1114 AND ((msn.current_subinventory_code = p_subinventory_code
1115 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
1116 )
1117 OR l_serial_discrepancy_option = 1
1118 OR (p_parent_lpn_id IS NOT NULL
1119 AND l_container_discrepancy_option = 1
1120 )
1121 )
1122 -- Bug# 2591158
1123 -- Only allow serials that are within the scope of the header
1124 -- for unscheduled cycle count entries
1125 -- Bug# 2778771
1126 -- Do this check only if the serial status is 3, resides in stores
1127 AND (l_orientation_code = 1 OR
1128 (msn.current_status = 3
1129 AND msn.current_subinventory_code IN
1130 (SELECT subinventory
1131 FROM mtl_cc_subinventories
1132 WHERE cycle_count_header_id = p_cycle_count_header_id))
1133 OR msn.current_status <> 3
1134 )
1135 AND serial_number LIKE (p_serial_number)
1136 AND msn.status_id = mms.status_id(+)
1137 AND mms.language (+) = userenv('LANG')
1138 AND (NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
1139 OR current_status IN (1, 6)
1140 ) --newly generated
1141 -- Do not include the serial numbers which are pending approval
1142 -- for the same cycle count header
1143 AND msn.serial_number NOT IN
1144 (SELECT mcce.serial_number
1145 FROM mtl_cycle_count_entries mcce
1146 WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
1147 AND mcce.inventory_item_id = p_inventory_item_id
1148 AND mcce.organization_id = p_organization_id
1149 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1150 AND mcce.entry_status_code = 2
1151 AND NVL(mcce.export_flag, 2) = 2)
1152 AND msn.serial_number NOT IN
1153 (SELECT mcsn.serial_number
1154 FROM mtl_cc_serial_numbers mcsn, mtl_cycle_count_entries mcce
1155 WHERE mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
1156 AND mcce.cycle_count_header_id = p_cycle_count_header_id
1157 AND mcce.inventory_item_id = p_inventory_item_id
1158 AND mcce.organization_id = p_organization_id
1159 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1160 AND mcce.entry_status_code = 2
1161 AND NVL(mcce.export_flag, 2) = 2)
1162 -- Bug# 2770853
1163 -- Honor the serial material status for cycle count adjustments
1164 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1165 NULL,
1166 4,
1167 NULL,
1168 'Y',
1169 p_organization_id,
1170 p_inventory_item_id,
1171 NULL,
1172 NULL,
1173 NULL,
1174 msn.serial_number,
1175 'S') = 'Y'
1176 ORDER BY 1 ASC;
1177 ELSE
1178 -- Unscheduled entries are not allowed
1179 IF (p_serial_count_option = 2) THEN
1180 -- Single serial
1181 OPEN x_serials FOR
1182 SELECT UNIQUE msn.serial_number
1183 , msn.current_subinventory_code
1184 , msn.current_locator_id
1185 , msn.lot_number
1186 , 0
1187 , msn.current_status
1188 , mms.status_code
1189 FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1190 WHERE msn.inventory_item_id = p_inventory_item_id
1191 AND ((msn.current_organization_id = p_organization_id
1192 AND msn.current_status IN (3, 4)
1193 )
1194 OR msn.current_status = 5
1195 )
1196 AND ((msn.current_subinventory_code = p_subinventory_code
1197 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
1198 )
1199 OR l_serial_discrepancy_option = 1
1200 OR (p_parent_lpn_id IS NOT NULL
1201 AND l_container_discrepancy_option = 1
1202 )
1203 )
1204 AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
1205 AND msn.serial_number LIKE (p_serial_number)
1206 AND msn.serial_number = mcce.serial_number
1207 AND msn.status_id = mms.status_id(+)
1208 AND mms.language (+) = userenv('LANG')
1209 AND mcce.cycle_count_header_id = p_cycle_count_header_id
1210 AND mcce.inventory_item_id = p_inventory_item_id
1211 AND mcce.organization_id = p_organization_id
1212 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1213 AND mcce.entry_status_code IN (1, 3)
1214 AND NVL(mcce.export_flag, 2) = 2
1215 -- Bug# 2770853
1216 -- Honor the serial material status for cycle count adjustments
1217 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1218 NULL,
1219 4,
1220 NULL,
1221 'Y',
1222 p_organization_id,
1223 p_inventory_item_id,
1224 NULL,
1225 NULL,
1226 NULL,
1227 msn.serial_number,
1228 'S') = 'Y'
1229 ORDER BY LPAD(msn.serial_number, 20);
1230 ELSIF (p_serial_count_option = 3) THEN
1231 -- Multiple serial
1232 OPEN x_serials FOR
1233 SELECT UNIQUE msn.serial_number
1234 , msn.current_subinventory_code
1235 , msn.current_locator_id
1236 , msn.lot_number
1237 , 0
1238 , msn.current_status
1239 , mms.status_code
1240 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
1241 mtl_material_statuses_tl mms, mtl_cycle_count_entries mcce
1242 WHERE msn.inventory_item_id = p_inventory_item_id
1243 AND (msn.group_mark_id IS NULL
1244 OR msn.group_mark_id = -1
1245 )
1246 AND ((msn.current_organization_id = p_organization_id
1247 AND msn.current_status IN (3, 4)
1248 )
1249 OR msn.current_status = 5
1250 )
1251 AND ((msn.current_subinventory_code = p_subinventory_code
1252 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
1253 )
1254 OR l_serial_discrepancy_option = 1
1255 OR (p_parent_lpn_id IS NOT NULL
1256 AND l_container_discrepancy_option = 1
1257 )
1258 )
1259 AND msn.serial_number LIKE (p_serial_number)
1260 AND msn.status_id = mms.status_id(+)
1261 AND mms.language (+) = userenv('LANG')
1262 AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
1263 AND msn.serial_number = mcsn.serial_number
1264 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
1265 AND mcce.cycle_count_header_id = p_cycle_count_header_id
1266 AND mcce.inventory_item_id = p_inventory_item_id
1267 AND mcce.organization_id = p_organization_id
1268 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1269 AND mcce.entry_status_code IN (1, 3)
1270 AND NVL(mcce.export_flag, 2) = 2
1271 -- Bug# 2770853
1272 -- Honor the serial material status for cycle count adjustments
1273 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1274 NULL,
1275 4,
1276 NULL,
1277 'Y',
1278 p_organization_id,
1279 p_inventory_item_id,
1280 NULL,
1281 NULL,
1282 NULL,
1283 msn.serial_number,
1284 'S') = 'Y'
1285 ORDER BY LPAD(msn.serial_number, 20);
1286 END IF;
1287 END IF;
1288 END get_cyc_serial_lov;
1289
1290 PROCEDURE get_cyc_to_serial_lov(
1291 x_serials OUT NOCOPY t_genref
1292 , p_organization_id IN NUMBER
1293 , p_subinventory_code IN VARCHAR2
1294 , p_locator_id IN NUMBER
1295 , p_inventory_item_id IN NUMBER
1296 , p_to_serial_number IN VARCHAR2
1297 , p_lot_number IN VARCHAR2
1298 , p_unscheduled_entry IN NUMBER
1299 , p_cycle_count_header_id IN NUMBER
1300 , p_from_serial_number IN VARCHAR2
1301 , p_parent_lpn_id IN NUMBER
1302 , p_serial_count_option IN NUMBER
1303 ) IS
1304 l_prefix VARCHAR2(30);
1305 l_quantity NUMBER;
1306 l_from_number NUMBER;
1307 l_to_number NUMBER;
1308 l_errorcode NUMBER;
1309 l_temp_boolean BOOLEAN;
1310 l_serial_discrepancy_option NUMBER;
1311 l_container_discrepancy_option NUMBER;
1312 l_orientation_code NUMBER;
1313 BEGIN
1314 -- Get the cycle count discrepancy option flags and orientation code
1315 SELECT NVL(serial_discrepancy_option, 2),
1316 NVL(container_discrepancy_option, 2),
1317 NVL(orientation_code, 1)
1318 INTO l_serial_discrepancy_option, l_container_discrepancy_option,
1319 l_orientation_code
1320 FROM mtl_cycle_count_headers
1321 WHERE cycle_count_header_id = p_cycle_count_header_id;
1322
1323 l_temp_boolean :=
1324 mtl_serial_check.inv_serial_info(p_from_serial_number, NULL, l_prefix,
1325 l_quantity, l_from_number, l_to_number, l_errorcode);
1326
1327 IF (p_unscheduled_entry = 1) THEN
1328 -- Unscheduled entries are allowed
1329 OPEN x_serials FOR
1330 SELECT serial_number
1331 , current_subinventory_code
1332 , current_locator_id
1333 , lot_number
1334 , 0
1335 , current_status
1336 , mms.status_code
1337 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1338 WHERE inventory_item_id = p_inventory_item_id
1339 AND (group_mark_id IS NULL
1340 OR group_mark_id = -1
1341 )
1342 AND ((current_organization_id = p_organization_id
1343 AND current_status IN (1, 3, 4, 6)
1344 )
1345 OR current_status = 5
1346 )
1347 AND ((msn.current_subinventory_code = p_subinventory_code
1348 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
1349 )
1350 OR l_serial_discrepancy_option = 1
1351 OR (p_parent_lpn_id IS NOT NULL
1352 AND l_container_discrepancy_option = 1
1353 )
1354 )
1355 -- Bug# 2591158
1356 -- Only allow serials that are within the scope of the header
1357 -- for unscheduled cycle count entries
1358 -- Bug# 2778771
1359 -- Do this check only if the serial status is 3, resides in stores
1360 AND (l_orientation_code = 1 OR
1361 (msn.current_status = 3
1362 AND msn.current_subinventory_code IN
1363 (SELECT subinventory
1364 FROM mtl_cc_subinventories
1365 WHERE cycle_count_header_id = p_cycle_count_header_id))
1366 OR msn.current_status <> 3
1367 )
1368 AND (NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
1369 OR current_status IN (1, 6)
1370 ) --newly generated
1371 AND serial_number LIKE (p_to_serial_number)
1372 AND serial_number LIKE (l_prefix || '%')
1373 AND msn.status_id = mms.status_id(+)
1374 AND mms.language (+) = userenv('LANG')
1375 AND serial_number > p_from_serial_number
1376 -- Do not include the serial numbers which are pending approval
1377 -- for the same cycle count header
1378 AND msn.serial_number NOT IN
1379 (SELECT mcce.serial_number
1380 FROM mtl_cycle_count_entries mcce
1381 WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
1382 AND mcce.inventory_item_id = p_inventory_item_id
1383 AND mcce.organization_id = p_organization_id
1384 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1385 AND mcce.entry_status_code = 2
1386 AND NVL(mcce.export_flag, 2) = 2)
1387 AND msn.serial_number NOT IN
1388 (SELECT mcsn.serial_number
1389 FROM mtl_cc_serial_numbers mcsn, mtl_cycle_count_entries mcce
1390 WHERE mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
1391 AND mcce.cycle_count_header_id = p_cycle_count_header_id
1392 AND mcce.inventory_item_id = p_inventory_item_id
1393 AND mcce.organization_id = p_organization_id
1394 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1395 AND mcce.entry_status_code = 2
1396 AND NVL(mcce.export_flag, 2) = 2)
1397 -- Bug# 2770853
1398 -- Honor the serial material status for cycle count adjustments
1399 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1400 NULL,
1401 4,
1402 NULL,
1403 'Y',
1404 p_organization_id,
1405 p_inventory_item_id,
1406 NULL,
1407 NULL,
1408 NULL,
1409 msn.serial_number,
1410 'S') = 'Y'
1411 ORDER BY 1 ASC;
1412 ELSE
1413 -- Unscheduled entries are not allowed
1414 IF (p_serial_count_option = 2) THEN
1415 -- Single serial
1416 OPEN x_serials FOR
1417 SELECT UNIQUE msn.serial_number
1418 , msn.current_subinventory_code
1419 , msn.current_locator_id
1420 , msn.lot_number
1421 , 0
1422 , msn.current_status
1423 , mms.status_code
1424 FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1425 WHERE msn.inventory_item_id = p_inventory_item_id
1426 AND ((msn.current_organization_id = p_organization_id
1427 AND msn.current_status IN (3, 4)
1428 )
1429 OR msn.current_status = 5
1430 )
1431 AND ((msn.current_subinventory_code = p_subinventory_code
1432 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
1433 )
1434 OR l_serial_discrepancy_option = 1
1435 OR (p_parent_lpn_id IS NOT NULL
1436 AND l_container_discrepancy_option = 1
1437 )
1438 )
1439 AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
1440 AND msn.serial_number LIKE (p_to_serial_number)
1441 AND msn.serial_number LIKE (l_prefix || '%')
1442 AND msn.serial_number = mcce.serial_number
1443 AND mcce.cycle_count_header_id = p_cycle_count_header_id
1444 AND mcce.inventory_item_id = p_inventory_item_id
1445 AND mcce.organization_id = p_organization_id
1446 AND msn.status_id = mms.status_id(+)
1447 AND mms.language (+) = userenv('LANG')
1448 AND msn.serial_number > p_from_serial_number
1449 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1450 AND mcce.entry_status_code IN (1, 3)
1451 AND NVL(mcce.export_flag, 2) = 2
1452 -- Bug# 2770853
1453 -- Honor the serial material status for cycle count adjustments
1454 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1455 NULL,
1456 4,
1457 NULL,
1458 'Y',
1459 p_organization_id,
1460 p_inventory_item_id,
1461 NULL,
1462 NULL,
1463 NULL,
1464 msn.serial_number,
1465 'S') = 'Y'
1466 ORDER BY LPAD(msn.serial_number, 20);
1467 ELSIF (p_serial_count_option = 3) THEN
1468 -- Multiple serial
1469 OPEN x_serials FOR
1470 SELECT UNIQUE msn.serial_number
1471 , msn.current_subinventory_code
1472 , msn.current_locator_id
1473 , msn.lot_number
1474 , 0
1475 , msn.current_status
1476 , mms.status_code
1477 FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
1478 mtl_material_statuses_tl mms, mtl_cc_serial_numbers mcsn
1479 WHERE msn.inventory_item_id = p_inventory_item_id
1480 AND (msn.group_mark_id IS NULL
1481 OR msn.group_mark_id = -1
1482 )
1483 AND ((msn.current_organization_id = p_organization_id
1484 AND msn.current_status IN (3, 4)
1485 )
1486 OR msn.current_status = 5
1487 )
1488 AND ((msn.current_subinventory_code = p_subinventory_code
1489 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
1490 )
1491 OR l_serial_discrepancy_option = 1
1492 OR (p_parent_lpn_id IS NOT NULL
1493 AND l_container_discrepancy_option = 1
1494 )
1495 )
1496 AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
1497 AND msn.serial_number LIKE (p_to_serial_number)
1498 AND msn.serial_number LIKE (l_prefix || '%')
1499 AND msn.status_id = mms.status_id(+)
1500 AND mms.language (+) = userenv('LANG')
1501 AND msn.serial_number > p_from_serial_number
1502 AND msn.serial_number = mcsn.serial_number
1503 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
1504 AND mcce.cycle_count_header_id = p_cycle_count_header_id
1505 AND mcce.inventory_item_id = p_inventory_item_id
1506 AND mcce.organization_id = p_organization_id
1507 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
1508 AND mcce.entry_status_code IN (1, 3)
1509 AND NVL(mcce.export_flag, 2) = 2
1510 -- Bug# 2770853
1511 -- Honor the serial material status for cycle count adjustments
1512 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1513 NULL,
1514 4,
1515 NULL,
1516 'Y',
1517 p_organization_id,
1518 p_inventory_item_id,
1519 NULL,
1520 NULL,
1521 NULL,
1522 msn.serial_number,
1523 'S') = 'Y'
1524 ORDER BY LPAD(msn.serial_number, 20);
1525 END IF;
1526 END IF;
1527 END get_cyc_to_serial_lov;
1528
1529 PROCEDURE get_cyc_serial_count_lov
1530 ( x_serials OUT NOCOPY t_genref
1531 , p_organization_id IN NUMBER
1532 , p_subinventory_code IN VARCHAR2
1533 , p_locator_id IN NUMBER
1534 , p_serial_number IN VARCHAR2
1535 , p_unscheduled_entry IN NUMBER
1536 , p_cycle_count_header_id IN NUMBER
1537 ) IS
1538 l_serial_discrepancy_option NUMBER;
1539 l_orientation_code NUMBER;
1540 BEGIN
1541 -- Get the cycle count serial discrepancy option and orientation code
1542 SELECT NVL(serial_discrepancy_option, 2), NVL(orientation_code, 1)
1543 INTO l_serial_discrepancy_option, l_orientation_code
1544 FROM mtl_cycle_count_headers
1545 WHERE cycle_count_header_id = p_cycle_count_header_id;
1546
1547 IF (p_unscheduled_entry = 1) THEN
1548 -- Unscheduled entries are allowed
1549 OPEN x_serials FOR
1550 SELECT msn.serial_number
1551 , msn.current_subinventory_code
1552 , msn.current_locator_id
1553 , msn.inventory_item_id
1554 , msik.concatenated_segments
1555 , msn.revision
1556 , msn.lot_number
1557 , msn.lpn_id
1558 , wlpn.license_plate_number
1559 , msn.current_status
1560 , msik.primary_uom_code
1561 FROM mtl_serial_numbers msn, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1562 WHERE (msn.group_mark_id IS NULL
1563 OR msn.group_mark_id = -1
1564 )
1565 AND ((msn.current_organization_id = p_organization_id
1566 AND msn.current_status IN (1, 3, 4, 6)
1567 )
1568 OR msn.current_status = 5
1569 )
1570 AND ((msn.current_subinventory_code = p_subinventory_code
1571 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
1572 )
1573 OR l_serial_discrepancy_option = 1
1574 )
1575 -- Bug# 2591158
1576 -- Only allow serials that are within the scope of the header
1577 -- for unscheduled cycle count entries
1578 -- Bug# 2778771
1579 -- Do this check only if the serial status is 3, resides in stores
1580 AND (l_orientation_code = 1 OR
1581 (msn.current_status = 3
1582 AND msn.current_subinventory_code IN
1583 (SELECT subinventory
1584 FROM mtl_cc_subinventories
1585 WHERE cycle_count_header_id = p_cycle_count_header_id))
1586 OR msn.current_status <> 3
1587 )
1588 AND msn.serial_number LIKE (p_serial_number)
1589 AND msn.inventory_item_id = msik.inventory_item_id
1590 AND msn.current_organization_id = msik.organization_id
1591 AND wlpn.lpn_id(+) = msn.lpn_id
1592 -- Do not include the serial numbers which are pending approval
1593 -- for the same cycle count header
1594 AND msn.serial_number NOT IN (SELECT mcce.serial_number
1595 FROM mtl_cycle_count_entries mcce
1596 WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
1597 AND mcce.organization_id = p_organization_id
1598 AND mcce.inventory_item_id = msn.inventory_item_id
1599 AND mcce.entry_status_code = 2
1600 AND NVL(mcce.export_flag, 2) = 2)
1601 -- Bug# 2770853
1602 -- Honor the serial material status for cycle count adjustments
1603 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1604 NULL,
1605 4,
1606 NULL,
1607 'Y',
1608 p_organization_id,
1609 msn.inventory_item_id,
1610 NULL,
1611 NULL,
1612 NULL,
1613 msn.serial_number,
1614 'S') = 'Y'
1615 ORDER BY LPAD(msn.serial_number, 20);
1616 ELSE
1617 -- Unscheduled entries are not allowed
1618 -- Single serial
1619 OPEN x_serials FOR
1620 SELECT UNIQUE msn.serial_number
1621 , msn.current_subinventory_code
1622 , msn.current_locator_id
1623 , msn.inventory_item_id
1624 , msik.concatenated_segments
1625 , msn.revision
1626 , msn.lot_number
1627 , msn.lpn_id
1628 , wlpn.license_plate_number
1629 , msn.current_status
1630 , msik.primary_uom_code
1631 FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
1632 mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
1633 WHERE (msn.group_mark_id IS NULL
1634 OR msn.group_mark_id = -1
1635 )
1636 AND msn.current_organization_id = p_organization_id
1637 AND ((msn.current_subinventory_code = p_subinventory_code
1638 AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
1639 )
1640 OR l_serial_discrepancy_option = 1
1641 )
1642 AND msn.serial_number LIKE (p_serial_number)
1643 AND msn.serial_number = mcce.serial_number
1644 AND msn.inventory_item_id = mcce.inventory_item_id
1645 AND NVL(msn.lpn_id, -99999) = NVL(mcce.parent_lpn_id, -99999)
1646 AND mcce.cycle_count_header_id = p_cycle_count_header_id
1647 AND mcce.organization_id = p_organization_id
1648 AND mcce.entry_status_code IN (1, 3)
1649 AND NVL(mcce.export_flag, 2) = 2
1650 AND msn.inventory_item_id = msik.inventory_item_id
1651 AND msn.current_organization_id = msik.organization_id
1652 AND wlpn.lpn_id(+) = msn.lpn_id
1653 -- Bug# 2770853
1654 -- Honor the serial material status for cycle count adjustments
1655 AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
1656 NULL,
1657 4,
1658 NULL,
1659 'Y',
1660 p_organization_id,
1661 msn.inventory_item_id,
1662 NULL,
1663 NULL,
1664 NULL,
1665 msn.serial_number,
1666 'S') = 'Y'
1667 ORDER BY LPAD(msn.serial_number, 20);
1668 END IF;
1669 END get_cyc_serial_count_lov;
1670
1671 PROCEDURE get_serial_lov_status(x_seriallov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_from_lot_number IN VARCHAR2, p_to_lot_number IN VARCHAR2, p_serial_number IN VARCHAR2) IS
1672 BEGIN
1673 /* FP-J Lot/Serial Support Enhancements
1674 * Add current status of resides in receiving
1675 */
1676 OPEN x_seriallov FOR
1677 SELECT serial_number
1678 , current_subinventory_code
1679 , current_locator_id
1680 , lot_number
1681 , 0
1682 , current_status
1683 , mms.status_code
1684 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1685 WHERE current_organization_id = p_organization_id
1686 AND inventory_item_id = p_inventory_item_id
1687 --AND current_status IN (1, 3, 5)
1688 AND current_status IN (1, 3, 5, 7)
1689 AND (p_from_lot_number IS NULL
1690 OR lot_number >= p_from_lot_number
1691 )
1692 AND (p_to_lot_number IS NULL
1693 OR lot_number <= p_to_lot_number
1694 )
1695 AND msn.status_id = mms.status_id(+)
1696 AND mms.language (+) = userenv('LANG')
1697 AND serial_number LIKE (p_serial_number);
1698 END;
1699
1700 PROCEDURE get_to_status_serial_lov(
1701 x_seriallov OUT NOCOPY t_genref
1702 , p_organization_id IN NUMBER
1703 , p_inventory_item_id IN NUMBER
1704 , p_from_lot_number IN VARCHAR2
1705 , p_to_lot_number IN VARCHAR2
1706 , p_from_serial_number IN VARCHAR2
1707 , p_serial_number IN VARCHAR2
1708 ) IS
1709 BEGIN
1710 /* FP-J Lot/Serial Support Enhancements
1711 * Add current status of resides in receiving
1712 */
1713 OPEN x_seriallov FOR
1714 SELECT serial_number
1715 , current_subinventory_code
1716 , current_locator_id
1717 , lot_number
1718 , 0
1719 , current_status
1720 , status_code
1721 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1722 WHERE current_organization_id = p_organization_id
1723 AND inventory_item_id = p_inventory_item_id
1724 --AND current_status IN (1, 3, 5)
1725 AND current_status IN (1, 3, 5, 7)
1726 AND (p_from_lot_number IS NULL
1727 OR lot_number >= p_from_lot_number
1728 )
1729 AND (p_to_lot_number IS NULL
1730 OR lot_number <= p_to_lot_number
1731 )
1732 AND msn.status_id = mms.status_id(+)
1733 AND mms.language (+) = userenv('LANG')
1734 AND serial_number >= p_from_serial_number
1735 AND serial_number LIKE (p_serial_number);
1736 END;
1737
1738 PROCEDURE get_serial_lov_lpn(x_serial_number OUT NOCOPY t_genref, p_lpn_id IN NUMBER, p_organization_id IN NUMBER, p_item_id IN NUMBER := NULL, p_lot IN VARCHAR2 := NULL, p_serial IN VARCHAR2) IS
1739 BEGIN
1740 OPEN x_serial_number FOR
1741 SELECT serial_number
1742 , 0
1743 , 0
1744 , 0
1745 , 0
1746 , ''
1747 , ''
1748 FROM mtl_serial_numbers
1749 WHERE lpn_id = p_lpn_id
1750 AND inventory_item_id = p_item_id
1751 AND NVL(lot_number, 'NOLOT') = NVL(p_lot, 'NOLOT')
1752 AND serial_number LIKE (p_serial)
1753 AND group_mark_id IS NULL
1754 ORDER BY LPAD(serial_number, 20);
1755 END get_serial_lov_lpn;
1756
1757 -- Name: GET_SERIAL_INSPECTLOV_RCV
1758 --
1759 -- Input parameters:
1760 -- p_Organization_Id which restricts LOV SQL to current org
1761 -- p_item_id which restricts LOV SQL to current item
1762 -- p_lpn_id restricts serial nos to LPN that is being inspected
1763 -- p_serial which restricts LOV SQL to the serial entered
1764 --
1765 -- Output parameters:
1766 -- x_serial_number returns LOV rows as reference cursor
1767 --
1768 -- Functions: This API is to return serial numbers for mobile inspection
1769 --
1770 PROCEDURE get_serial_inspect_lov_rcv
1771 (x_serial_number OUT NOCOPY t_genref,
1772 p_organization_id IN NUMBER,
1773 p_item_id IN NUMBER,
1774 p_lpn_id IN NUMBER,
1775 p_serial IN VARCHAR2,
1776 p_lot_number IN VARCHAR2 ) IS
1777 BEGIN
1778 /* FP-J Lot/Serial Support Enhancements
1779 * Add current status of resides in receiving
1780 */
1781 OPEN x_serial_number FOR
1782 SELECT serial_number
1783 , current_subinventory_code
1784 , current_locator_id
1785 , lot_number
1786 , 0
1787 , current_status
1788 , mms.status_code
1789 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1790 WHERE msn.inventory_item_id = p_item_id
1791 AND msn.lpn_id = p_lpn_id
1792 AND msn.current_organization_id = p_organization_id
1793 --AND msn.current_status = 5 /* Intransit */
1794 AND msn.current_status IN (5, 7) /* Intransit, Resides in Receiving */
1795 AND msn.status_id = mms.status_id(+)
1796 AND mms.language (+) = userenv('LANG')
1797 AND msn.inspection_status = 1 /* yet to be inspected */
1798 AND msn.serial_number LIKE (p_serial)
1799 AND Nvl(msn.lot_number,'@@@') = Nvl(p_lot_number,Nvl(msn.lot_number,'@@@'))
1800 AND Nvl(msn.group_mark_id,-1) <> 2
1801 ORDER BY LPAD(serial_number, 20);
1802 END get_serial_inspect_lov_rcv;
1803
1804 -- Name: GET_SERIAL_LOV_SO
1805 --
1806 -- Input parameters:
1807 -- p_Organization_Id which restricts LOV SQL to current org
1808 -- p_inventory_item_id which restricts LOV SQL to current item
1809 -- p_subinventory_code which restricts LOV SQL to current sub
1810 -- p_locator_id which restricts LOV SQL to current locator
1811 -- p_revision which restricts LOV SQL to current revision
1812 -- p_lot_number which restricts LOV SQL to current lot
1813 -- p_serial_number which restricts LOV SQL to the serial entered
1814 --
1815 -- Output parameters:
1816 -- x_serial_number returns LOV rows as reference cursor
1817 --
1818 -- Functions: This API is to return serial numbers for mobile inspection
1819 --
1820 PROCEDURE get_serial_lov_so(
1821 x_serial OUT NOCOPY t_genref
1822 , p_delivery_id IN NUMBER
1823 , p_inventory_item_id IN NUMBER
1824 , p_organization_id IN NUMBER
1825 , p_subinventory_code IN VARCHAR2
1826 , p_locator_id IN NUMBER
1827 , p_revision IN VARCHAR2
1828 , p_lot_number IN VARCHAR2
1829 , p_serial_number IN VARCHAR2
1830 ) IS
1831 l_serial_number_control_code NUMBER;
1832 BEGIN
1833 SELECT serial_number_control_code
1834 INTO l_serial_number_control_code
1835 FROM mtl_system_items_b
1836 WHERE organization_id = p_organization_id
1837 AND inventory_item_id = p_inventory_item_id;
1838
1839 IF l_serial_number_control_code = 6 THEN
1840 OPEN x_serial FOR
1841 SELECT serial_number
1842 , current_subinventory_code
1843 , current_locator_id
1844 , lot_number
1845 , 0
1846 , current_status
1847 , ' '
1848 FROM mtl_serial_numbers
1849 WHERE inventory_item_id = p_inventory_item_id
1850 AND current_organization_id = p_organization_id
1851 AND (group_mark_id IS NULL
1852 OR group_mark_id = -1
1853 )
1854 AND current_status = 1
1855 AND serial_number LIKE (p_serial_number)
1856 ORDER BY LPAD(serial_number, 20);
1857 ELSE
1858 OPEN x_serial FOR
1859 select serial_number,current_subinventory_code,current_locator_id,lot_number,0,0,''
1860 from mtl_serial_numbers msn
1861 where inventory_item_id = p_inventory_item_id
1862 and current_organization_id = p_organization_id
1863 and (group_mark_id is null or group_mark_id = -1 )
1864 and nvl(current_subinventory_code,'@@@') = nvl(p_subinventory_code,'@@@')
1865 and nvl(current_locator_id,0) = nvl(p_locator_id,0)
1866 and current_status = 3
1867 and (lpn_id is NULL OR lpn_id = 0)
1868 and wip_entity_id is NULL
1869 and msn.serial_number like (p_serial_number || '%')
1870 order by lpad(msn.serial_number,20);
1871 END IF;
1872 END get_serial_lov_so;
1873
1874 PROCEDURE get_cont_serial_lov(x_serial_number OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lpn_id IN NUMBER, p_revision IN VARCHAR2, p_lot_number IN VARCHAR2, p_serial IN VARCHAR2) IS
1875 BEGIN
1876 OPEN x_serial_number FOR
1877 SELECT msn.serial_number
1878 , msn.current_subinventory_code
1879 , msn.current_locator_id
1880 , msn.lot_number
1881 , 0
1882 , msn.current_status
1883 , ''
1884 FROM mtl_serial_numbers msn
1885 WHERE msn.current_organization_id = p_organization_id
1886 AND msn.inventory_item_id = p_item_id
1887 AND msn.lpn_id = p_lpn_id
1888 AND NVL(line_mark_id, -999) <> 1
1889 AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
1890 AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
1891 AND msn.serial_number LIKE p_serial
1892 AND inv_material_status_grp.is_status_applicable('TRUE', NULL, inv_globals.g_type_container_unpack, NULL, NULL, p_organization_id, msn.inventory_item_id, NULL, NULL, NULL, msn.serial_number, 'S') = 'Y'
1893 AND NOT EXISTS (select 1
1894 from mtl_reservations mr
1895 where mr.reservation_id = msn.reservation_id
1896 and mr.lpn_id = p_lpn_id)
1897 ORDER BY LPAD(msn.serial_number, 20);
1898 END get_cont_serial_lov;
1899
1900 PROCEDURE get_split_cont_serial_lov(x_serial_number OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lpn_id IN NUMBER, p_revision IN VARCHAR2, p_lot_number IN VARCHAR2, p_transaction_subtype IN NUMBER, p_serial IN VARCHAR2) IS
1901 BEGIN
1902 IF ( p_transaction_subtype = 1 ) THEN -- Inventory Split
1903 OPEN x_serial_number FOR
1904 SELECT msn.serial_number
1905 , msn.current_subinventory_code
1906 , msn.current_locator_id
1907 , msn.lot_number
1908 , 0
1909 , msn.current_status
1910 , ''
1911 FROM mtl_serial_numbers msn
1912 WHERE msn.current_organization_id = p_organization_id
1913 AND msn.inventory_item_id = p_item_id
1914 AND msn.lpn_id = p_lpn_id
1915 AND NVL(line_mark_id, -9) <> 1
1916 AND NVL(lot_number, '@') = NVL(NVL(p_lot_number, '@'), NVL(lot_number, '@'))
1917 AND NVL(revision, '@') = NVL(NVL(p_revision, '@'), NVL(revision, '@'))
1918 AND msn.serial_number LIKE (p_serial)
1919 AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, inv_globals.g_type_container_split, NULL, NULL, p_organization_id, msn.inventory_item_id, NULL, NULL, NULL, msn.serial_number, 'S') = 'Y')
1920 AND NOT EXISTS (select 1
1921 from mtl_reservations mr
1922 where mr.reservation_id = msn.reservation_id
1923 and mr.lpn_id = p_lpn_id)
1924 ORDER BY LPAD(msn.serial_number, 20);
1925 ELSE -- Outbound or Salesorder ( p_transaction_subtype in (2, 3) )
1926 OPEN x_serial_number FOR
1927 SELECT msn.serial_number
1928 , msn.current_subinventory_code
1929 , msn.current_locator_id
1930 , msn.lot_number
1931 , 0
1932 , msn.current_status
1933 , ''
1934 FROM mtl_serial_numbers msn
1935 WHERE msn.current_organization_id = p_organization_id
1936 AND msn.inventory_item_id = p_item_id
1937 AND msn.lpn_id = p_lpn_id
1938 AND NVL(line_mark_id, -9) <> 1
1939 AND NVL(lot_number, '@') = NVL(NVL(p_lot_number, '@'), NVL(lot_number, '@'))
1940 AND NVL(revision, '@') = NVL(NVL(p_revision, '@'), NVL(revision, '@'))
1941 AND msn.serial_number LIKE (p_serial)
1942 AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, inv_globals.g_type_container_split, NULL, NULL, p_organization_id, msn.inventory_item_id, NULL, NULL, NULL, msn.serial_number, 'S') = 'Y')
1943 ORDER BY LPAD(msn.serial_number, 20);
1944 END IF;
1945 END get_split_cont_serial_lov;
1946
1947 PROCEDURE get_pupcont_serial_lov(
1948 x_serial_number OUT NOCOPY t_genref
1949 , p_organization_id IN NUMBER
1950 , p_item_id IN NUMBER
1951 , p_lpn_id IN NUMBER
1952 , p_revision IN VARCHAR2
1953 , p_lot_number IN VARCHAR2
1954 , p_serial IN VARCHAR2
1955 , p_txn_type_id IN NUMBER := 0
1956 , p_wms_installed IN VARCHAR2 := 'TRUE'
1957 ) IS
1958 BEGIN
1959 OPEN x_serial_number FOR
1960 SELECT msn.serial_number
1961 , msn.current_subinventory_code
1962 , msn.current_locator_id
1963 , msn.lot_number
1964 FROM mtl_serial_numbers msn
1965 WHERE msn.current_organization_id = p_organization_id
1966 AND msn.inventory_item_id = p_item_id
1967 AND msn.lpn_id = p_lpn_id
1968 AND (group_mark_id IS NULL
1969 OR group_mark_id = -1
1970 )
1971 AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
1972 AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
1973 AND msn.serial_number LIKE (p_serial)
1974 AND current_status = 3
1975 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, NULL, NULL, NULL, p_serial, 'S') = 'Y'
1976 ORDER BY LPAD(msn.serial_number, 20);
1977 END get_pupcont_serial_lov;
1978
1979 -- Name: GET_INV_SERIAL_LOV
1980 --
1981 -- Input parameters:
1982 -- p_Organization_Id which restricts LOV SQL to current org
1983 -- p_inventory_item_id which restricts LOV SQL to current item
1984 -- p_subinventory_code which restricts LOV SQL to current sub
1985 -- p_locator_id which restricts LOV SQL to current locator
1986 -- p_revision which restricts LOV SQL to current revision
1987 -- p_lot_number which restricts LOV SQL to current lot
1988 -- p_serial_number which restricts LOV SQL to the serial entered
1989 --
1990 -- Output parameters:
1991 -- x_serial_number returns LOV rows as reference cursor
1992 --
1993 -- Functions: This API is to return serial numbers for mobile inspection
1994 --
1995 PROCEDURE get_inv_serial_lov(
1996 x_serial OUT NOCOPY t_genref
1997 , p_organization_id IN NUMBER
1998 , p_inventory_item_id IN NUMBER
1999 , p_subinventory_code IN VARCHAR2
2000 , p_locator_id IN VARCHAR2
2001 , p_revision IN VARCHAR2
2002 , p_lot_number IN VARCHAR2
2003 , p_serial_number IN VARCHAR2
2004 ) IS
2005 BEGIN
2006 OPEN x_serial FOR
2007 SELECT serial_number
2008 , current_subinventory_code
2009 , current_locator_id
2010 , lot_number
2011 , 'NULL'
2012 , current_status
2013 , 'NULL'
2014 FROM mtl_serial_numbers
2015 WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
2016 AND current_organization_id = p_organization_id
2017 AND (group_mark_id IS NULL
2018 OR group_mark_id = -1
2019 )
2020 AND ((NVL(current_subinventory_code, '@@@') = NVL(NVL(p_subinventory_code, '@@@'), NVL(current_subinventory_code, '@@@'))
2021 AND NVL(current_locator_id, -1) = NVL(NVL(TO_NUMBER(p_locator_id), -1), NVL(current_locator_id, -1))
2022 AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
2023 AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
2024 AND current_status = 3
2025 )
2026 OR current_status = 1
2027 OR current_status = 6
2028 )
2029 AND serial_number LIKE (p_serial_number)
2030 ORDER BY LPAD(serial_number, 20);
2031 END get_inv_serial_lov;
2032
2033 PROCEDURE get_pack_serial_lov(
2034 x_serial OUT NOCOPY t_genref
2035 , p_organization_id IN NUMBER
2036 , p_inventory_item_id IN NUMBER
2037 , p_subinventory_code IN VARCHAR2
2038 , p_locator_id IN VARCHAR2
2039 , p_revision IN VARCHAR2
2040 , p_lot_number IN VARCHAR2
2041 , p_serial_number IN VARCHAR2) IS
2042 BEGIN
2043 OPEN x_serial FOR
2044 SELECT serial_number
2045 , current_subinventory_code
2046 , current_locator_id
2047 , lot_number
2048 , 'NULL'
2049 , current_status
2050 , 'NULL'
2051 FROM mtl_serial_numbers
2052 WHERE inventory_item_id = p_inventory_item_id
2053 AND current_organization_id = p_organization_id
2054 AND NVL(line_mark_id, -999) <> 1
2055 AND current_subinventory_code = p_subinventory_code
2056 AND NVL(current_locator_id, -1) = NVL(NVL(TO_NUMBER(p_locator_id), -1), NVL(current_locator_id, -1))
2057 AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
2058 AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
2059 AND current_status = 3
2060 AND lpn_id IS NULL
2061 AND serial_number LIKE p_serial_number
2062 AND inv_material_status_grp.is_status_applicable
2063 (
2064 'TRUE', NULL,
2065 inv_globals.g_type_container_pack,
2066 NULL, NULL,
2067 p_organization_id, inventory_item_id,
2068 NULL, NULL, NULL, serial_number, 'S') = 'Y'
2069 ORDER BY LPAD(serial_number, 20);
2070 END get_pack_serial_lov;
2071
2072 -- Name: GET_INV_SERIAL_LOV_BULK
2073 --
2074 -- Input parameters:
2075 -- p_Organization_Id which restricts LOV SQL to current org
2076 -- p_inventory_item_id which restricts LOV SQL to current item
2077 -- p_subinventory_code which restricts LOV SQL to current sub
2078 -- p_locator_id which restricts LOV SQL to current locator
2079 -- p_revision which restricts LOV SQL to current revision
2080 -- p_lot_number which restricts LOV SQL to current lot
2081 -- p_serial_number which restricts LOV SQL to the serial entered
2082 --
2083 -- Output parameters:
2084 -- x_serial_number returns LOV rows as reference cursor
2085 --
2086 -- Functions: This API is to return serial numbers for mobile inspection
2087 --
2088 PROCEDURE get_inv_serial_lov_bulk(
2089 x_serial OUT NOCOPY t_genref
2090 , p_organization_id IN NUMBER
2091 , p_inventory_item_id IN NUMBER
2092 , p_subinventory_code IN VARCHAR2
2093 , p_locator_id IN VARCHAR2
2094 , p_revision IN VARCHAR2
2095 , p_lot_number IN VARCHAR2
2096 , p_from_serial_number IN VARCHAR2
2097 , p_serial_number IN VARCHAR2
2098 ) IS
2099 l_prefix VARCHAR2(30);
2100 l_quantity NUMBER;
2101 l_from_number NUMBER;
2102 l_to_number NUMBER;
2103 l_errorcode NUMBER;
2104 l_temp_boolean BOOLEAN;
2105 BEGIN
2106 IF (p_from_serial_number IS NOT NULL) THEN
2107 l_temp_boolean := mtl_serial_check.inv_serial_info(p_from_serial_number, NULL, l_prefix, l_quantity, l_from_number, l_to_number, l_errorcode);
2108 ELSE
2109 l_prefix := '';
2110 END IF;
2111
2112 OPEN x_serial FOR
2113 SELECT serial_number
2114 , current_subinventory_code
2115 , current_locator_id
2116 , lot_number
2117 , 'NULL'
2118 , current_status
2119 , 'NULL'
2120 FROM mtl_serial_numbers
2121 WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
2122 AND current_organization_id = p_organization_id
2123 AND (group_mark_id IS NULL
2124 OR group_mark_id = -1
2125 )
2126 AND ((NVL(current_subinventory_code, '@@@') = NVL(NVL(p_subinventory_code, '@@@'), NVL(current_subinventory_code, '@@@'))
2127 AND NVL(current_locator_id, -1) = NVL(NVL(TO_NUMBER(p_locator_id), -1), NVL(current_locator_id, -1))
2128 AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
2129 AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
2130 AND current_status = 3
2131 )
2132 )
2133 AND serial_number LIKE (l_prefix || '%')
2134 AND lpn_id IS NULL
2135 AND serial_number >= NVL(p_from_serial_number, serial_number)
2136 AND serial_number LIKE (p_serial_number)
2137 AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, inv_globals.g_type_container_pack, NULL, NULL, p_organization_id, inventory_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y')
2138 ORDER BY LPAD(serial_number, 20);
2139 END get_inv_serial_lov_bulk;
2140
2141 -- Name: GET_CGUPDATE_SERIAL_LOV
2142 --
2143 -- Input parameters:
2144 -- p_Organization_Id which restricts LOV SQL to current org
2145 -- p_inventory_item_id which restricts LOV SQL to current item
2146 -- p_serial_number which restricts LOV SQL to the serial entered
2147 -- p_subinventory_code which restricts LOV SQL to current sub
2148 -- p_locator_id which restricts LOV SQL to current locator
2149 --
2150 -- Output parameters:
2151 -- x_serial_number returns LOV rows as reference cursor
2152 --
2153 -- Functions: This API is to return serial numbers for mobile inspection
2154 --
2155 PROCEDURE get_cgupdate_serial_lov(
2156 x_serial OUT NOCOPY t_genref
2157 , p_organization_id IN NUMBER
2158 , p_inventory_item_id IN NUMBER
2159 , p_lpn_id IN NUMBER
2160 , p_serial_number IN VARCHAR2
2161 , p_subinventory_code IN VARCHAR2
2162 , p_locator_id IN NUMBER
2163 , p_revision IN VARCHAR2
2164 , p_cost_group_id IN NUMBER
2165 ) IS
2166 BEGIN
2167 IF p_lpn_id IS NULL THEN
2168 OPEN x_serial FOR
2169 SELECT msn.serial_number
2170 , msn.current_subinventory_code
2171 , msn.current_locator_id
2172 , msn.lot_number
2173 , ''
2174 , msn.current_status
2175 , mms.status_code
2176 , mil.concatenated_segments
2177 , msn.revision
2178 , msn.cost_group_id
2179 , ccg.cost_group
2180 FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2181 WHERE (group_mark_id IS NULL
2182 OR group_mark_id = -1
2183 )
2184 AND mms.status_id(+) = msn.status_id
2185 AND mms.language (+) = userenv('LANG')
2186 AND ccg.cost_group_id = msn.cost_group_id
2187 AND msn.current_locator_id = mil.inventory_location_id
2188 AND mil.organization_id = p_organization_id
2189 AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, msn.current_subinventory_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'A') = 'Y'
2190 AND msn.current_status = 3
2191 AND (msn.group_mark_id IS NULL
2192 OR (msn.group_mark_id <> 1)
2193 )
2194 AND (p_revision IS NULL
2195 OR (msn.revision = p_revision)
2196 )
2197 AND msn.cost_group_id = NVL(p_cost_group_id, msn.cost_group_id)
2198 AND msn.current_locator_id = NVL(p_locator_id, msn.current_locator_id)
2199 AND msn.current_subinventory_code = NVL(p_subinventory_code, msn.current_subinventory_code)
2200 AND msn.serial_number LIKE (p_serial_number)
2201 AND msn.lpn_id IS NULL
2202 AND msn.inventory_item_id = p_inventory_item_id
2203 AND msn.current_organization_id = p_organization_id
2204 ORDER BY serial_number;
2205 ELSE
2206 OPEN x_serial FOR
2207 SELECT msn.serial_number
2208 , msn.current_subinventory_code
2209 , msn.current_locator_id
2210 , msn.lot_number
2211 , ''
2212 , msn.current_status
2213 , mms.status_code
2214 , mil.concatenated_segments
2215 , msn.revision
2216 , msn.cost_group_id
2217 , ccg.cost_group
2218 FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2219 WHERE (group_mark_id IS NULL
2220 OR group_mark_id = -1
2221 )
2222 AND mms.status_id(+) = msn.status_id
2223 AND mms.language (+) = userenv('LANG')
2224 AND ccg.cost_group_id = msn.cost_group_id
2225 AND msn.current_locator_id = mil.inventory_location_id
2226 AND mil.organization_id = p_organization_id
2227 AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, msn.current_subinventory_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'A') = 'Y'
2228 AND msn.current_status = 3
2229 AND (msn.group_mark_id IS NULL
2230 OR (msn.group_mark_id <> 1)
2231 )
2232 AND (p_revision IS NULL
2233 OR (msn.revision = p_revision)
2234 )
2235 AND msn.cost_group_id = NVL(p_cost_group_id, msn.cost_group_id)
2236 AND msn.current_locator_id = NVL(p_locator_id, msn.current_locator_id)
2237 AND msn.current_subinventory_code = NVL(p_subinventory_code, msn.current_subinventory_code)
2238 AND msn.serial_number LIKE (p_serial_number)
2239 AND msn.lpn_id = p_lpn_id
2240 AND msn.inventory_item_id = p_inventory_item_id
2241 AND msn.current_organization_id = p_organization_id
2242 ORDER BY serial_number;
2243 END IF;
2244 END get_cgupdate_serial_lov;
2245
2246 PROCEDURE get_lot_expiration_date(p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_lot_number IN VARCHAR2, p_shelf_life_code IN NUMBER, p_shelf_life_days IN NUMBER, x_expiration_date OUT NOCOPY DATE) IS
2247 BEGIN
2248 x_expiration_date := '';
2249
2250 IF p_shelf_life_code = 1 THEN
2251 RETURN;
2252 ELSE
2253 BEGIN
2254 SELECT MIN(expiration_date)
2255 INTO x_expiration_date
2256 FROM mtl_lot_numbers
2257 WHERE organization_id = p_organization_id
2258 AND inventory_item_id = p_inventory_item_id
2259 AND lot_number = p_lot_number;
2260
2261 IF x_expiration_date IS NULL THEN
2262 IF p_shelf_life_code = 2 THEN
2263 SELECT (SYSDATE + NVL(p_shelf_life_days, 0))
2264 INTO x_expiration_date
2265 FROM DUAL;
2266 END IF;
2267 END IF;
2268
2269 RETURN;
2270 EXCEPTION
2271 WHEN NO_DATA_FOUND THEN
2272 IF p_shelf_life_code = 2 THEN
2273 SELECT (SYSDATE + NVL(p_shelf_life_days, 0))
2274 INTO x_expiration_date
2275 FROM DUAL;
2276 END IF;
2277
2278 RETURN;
2279 END;
2280 END IF;
2281
2282 RETURN;
2283 END get_lot_expiration_date;
2284
2285 -- Name: GET_SERIAL_LOV_PICKING
2286 --
2287 -- Input parameters:
2288 -- p_Organization_Id which restricts LOV SQL to current org
2289 -- p_item_id which restricts LOV SQL to current item
2290 -- p_subinv_code restricts to Subinventory
2291 -- p_locator_id restricts to Locator ID. If not used, set to -1
2292 -- p_serial which restricts LOV SQL to the serial entered
2293 -- p_lpn_id which restricts LOV SQL to current LPN
2294 --
2295 -- Output parameters:
2296 -- x_serial_number returns LOV rows as reference cursor
2297 --
2298 -- Functions: This API is to return serial numbers limited by
2299 -- the specified Subinventory and Locator with status = 3;
2300 --
2301
2302
2303 PROCEDURE get_serial_lov_picking(
2304 x_serial_number OUT NOCOPY t_genref
2305 , p_organization_id IN NUMBER
2306 , p_item_id IN NUMBER
2307 , p_subinv_code IN VARCHAR2
2308 , p_locator_id IN NUMBER
2309 , p_serial IN VARCHAR2
2310 , p_transaction_type_id IN NUMBER
2311 , p_lpn_id IN NUMBER
2312 , p_lot_number IN VARCHAR2
2313 ) IS
2314 l_wms_installed VARCHAR2(10) := 'TRUE';
2315 BEGIN
2316 OPEN x_serial_number FOR
2317 SELECT serial_number
2318 , current_subinventory_code
2319 , current_locator_id
2320 , lot_number
2321 , 0
2322 , current_status
2323 , mms.status_code
2324 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2325 WHERE inventory_item_id = p_item_id
2326 AND (group_mark_id IS NULL
2327 OR group_mark_id = -1
2328 )
2329 AND current_organization_id = p_organization_id
2330 AND current_status = 3
2331 AND current_subinventory_code = p_subinv_code
2332 AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
2333 AND NVL(current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, current_locator_id, p_locator_id), 0)
2334 AND msn.status_id = mms.status_id(+)
2335 AND mms.language (+) = userenv('LANG')
2336 AND serial_number LIKE (p_serial)
2337 AND NVL(msn.lot_number, 0) = NVL(p_lot_number, 0) --retrict to lot numbers
2338 AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'S') = 'Y'
2339 ORDER BY LPAD(serial_number, 20);
2340 END get_serial_lov_picking;
2341
2342 -- Name: GET_SERIAL_LOV_ALLOC_PICKING
2343 --
2344 -- Input parameters:
2345 -- p_transaction_temp_id the transaction temp id from the
2346 -- mtl_material_transactions_temp table
2347 -- p_lot_code if '1' means not lot controlled
2348 -- if '2' means IS lot controlled
2349 -- the caller function would have to ensure that
2350 -- these are the only numbers used.
2351 -- Output parameters:
2352 -- x_serial_number returns LOV rows as reference cursor
2353 --
2354 -- Functions: This API is to return serial numbers allocated at receipt
2355 --
2356 --
2357
2358 PROCEDURE get_serial_lov_alloc_picking(
2359 x_serial_number OUT NOCOPY t_genref
2360 , p_organization_id IN NUMBER
2361 , p_item_id IN NUMBER
2362 , p_subinv_code IN VARCHAR2
2363 , p_locator_id IN NUMBER
2364 , p_serial IN VARCHAR2
2365 , p_transaction_type_id IN NUMBER
2366 , p_lpn_id IN NUMBER
2367 , p_transaction_temp_id IN NUMBER
2368 , p_lot_code IN NUMBER
2369 , p_lot_number IN VARCHAR2
2370 ) IS
2371 lp_lot_code NUMBER;
2372 l_wms_installed VARCHAR2(10) := 'TRUE';
2373 BEGIN
2374 IF (p_lot_code IS NULL) THEN
2375 lp_lot_code := 1;
2376 ELSE
2377 lp_lot_code := p_lot_code;
2378 END IF;
2379
2380 -- if is NOT lot controlled, do this
2381 IF (lp_lot_code = 1) THEN
2382 OPEN x_serial_number FOR
2383 SELECT msnt.fm_serial_number
2384 , msn.current_subinventory_code
2385 , msn.current_locator_id
2386 , msn.lot_number
2387 , 0
2388 , msn.current_status
2389 , mms.status_code
2390 FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2391 WHERE msn.inventory_item_id = p_item_id
2392 AND msn.current_organization_id = p_organization_id
2393 AND msn.current_status = 3
2394 AND msn.current_subinventory_code = p_subinv_code
2395 AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
2396 AND NVL(msn.current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, msn.current_locator_id, p_locator_id), 0)
2397 AND msn.status_id = mms.status_id(+)
2398 AND mms.language (+) = userenv('LANG')
2399 AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, msnt.fm_serial_number, 'S') = 'Y'
2400 AND msn.serial_number = msnt.fm_serial_number
2401 AND msnt.fm_serial_number LIKE (p_serial)
2402 AND msnt.transaction_temp_id = p_transaction_temp_id
2403 ORDER BY LPAD(msnt.fm_serial_number, 20);
2404 -- else if IS lot controlled do this
2405 ELSIF (lp_lot_code = 2) THEN
2406 OPEN x_serial_number FOR
2407 SELECT msnt.fm_serial_number
2408 , msn.current_subinventory_code
2409 , msn.current_locator_id
2410 , msn.lot_number
2411 , 0
2412 , msn.current_status
2413 , mms.status_code
2414 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2415 WHERE msn.inventory_item_id = p_item_id
2416 AND msn.current_organization_id = p_organization_id
2417 AND msn.current_status = 3
2418 AND msn.current_subinventory_code = p_subinv_code
2419 AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
2420 AND NVL(msn.current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, msn.current_locator_id, p_locator_id), 0)
2421 AND msn.status_id = mms.status_id(+)
2422 AND mms.language (+) = userenv('LANG')
2423 AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, msnt.fm_serial_number, 'S') = 'Y'
2424 AND msn.serial_number = msnt.fm_serial_number
2425 AND msnt.fm_serial_number LIKE (p_serial)
2426 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
2427 AND mtlt.lot_number = p_lot_number
2428 AND mtlt.transaction_temp_id = p_transaction_temp_id
2429 ORDER BY LPAD(msnt.fm_serial_number, 20);
2430 END IF;
2431 END get_serial_lov_alloc_picking;
2432
2433
2434 PROCEDURE get_serial_lov_apl_picking(
2435 x_serial_number OUT NOCOPY t_genref
2436 , p_organization_id IN NUMBER
2437 , p_item_id IN NUMBER
2438 , p_subinv_code IN VARCHAR2
2439 , p_locator_id IN NUMBER
2440 , p_serial IN VARCHAR2
2441 , p_transaction_type_id IN NUMBER
2442 , p_lpn_id IN NUMBER
2443 , p_lot_number IN VARCHAR2
2444 , p_revision IN VARCHAR2
2445 ) IS
2446 l_wms_installed VARCHAR2(10) := 'TRUE';
2447 BEGIN
2448 OPEN x_serial_number FOR
2449 SELECT serial_number
2450 , current_subinventory_code
2451 , current_locator_id
2452 , lot_number
2453 , 0
2454 , current_status
2455 , mms.status_code
2456 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2457 WHERE inventory_item_id = p_item_id
2458 AND (group_mark_id IS NULL
2459 OR group_mark_id = -1
2460 )
2461 AND current_organization_id = p_organization_id
2462 AND current_status = 3
2463 AND current_subinventory_code = p_subinv_code
2464 AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
2465 AND NVL(current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, current_locator_id, p_locator_id), 0)
2466 AND msn.status_id = mms.status_id(+)
2467 AND mms.language (+) = userenv('LANG')
2468 AND serial_number LIKE (p_serial)
2469 AND (p_revision IS NULL
2470 OR (msn.revision = p_revision)
2471 )
2472 AND NVL(msn.lot_number, 0) = NVL(p_lot_number, 0) --retrict to lot numbers
2473 AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'S') = 'Y'
2474 ORDER BY LPAD(serial_number, 20);
2475 END get_serial_lov_apl_picking;
2476
2477
2478 PROCEDURE get_serial_lov_apl_alloc_pick(
2479 x_serial_number OUT NOCOPY t_genref
2480 , p_organization_id IN NUMBER
2481 , p_item_id IN NUMBER
2482 , p_subinv_code IN VARCHAR2
2483 , p_locator_id IN NUMBER
2484 , p_serial IN VARCHAR2
2485 , p_transaction_type_id IN NUMBER
2486 , p_lpn_id IN NUMBER
2487 , p_transaction_temp_id IN NUMBER
2488 , p_lot_code IN NUMBER
2489 , p_lot_number IN VARCHAR2
2490 , p_revision IN VARCHAR2
2491 ) IS
2492 lp_lot_code NUMBER;
2493 l_wms_installed VARCHAR2(10) := 'TRUE';
2494 BEGIN
2495 IF (p_lot_code IS NULL) THEN
2496 lp_lot_code := 1;
2497 ELSE
2498 lp_lot_code := p_lot_code;
2499 END IF;
2500
2501 -- if is NOT lot controlled, do this
2502 IF (lp_lot_code = 1) THEN
2503 OPEN x_serial_number FOR
2504 SELECT mag.serial_number
2505 , msn.current_subinventory_code
2506 , msn.current_locator_id
2507 , msn.lot_number
2508 , 0
2509 , msn.current_status
2510 , mms.status_code
2511 FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2512 WHERE msn.inventory_item_id = p_item_id
2513 AND msn.current_organization_id = p_organization_id
2514 AND msn.current_status = 3
2515 AND msn.current_subinventory_code = p_subinv_code
2516 --AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
2517 AND NVL(msn.current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, msn.current_locator_id, p_locator_id), 0)
2518 AND msn.status_id = mms.status_id(+)
2519 AND mms.language (+) = userenv('LANG')
2520 AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, mag.serial_number, 'S') = 'Y'
2521 AND msn.serial_number = mag.serial_number
2522 AND mag.serial_number LIKE (p_serial)
2523 AND (p_revision IS NULL
2524 OR (msn.revision = p_revision)
2525 )
2526 ORDER BY LPAD(mag.serial_number, 20);
2527 -- else if IS lot controlled do this
2528 ELSIF (lp_lot_code = 2) THEN
2529 OPEN x_serial_number FOR
2530 SELECT mag.serial_number
2531 , msn.current_subinventory_code
2532 , msn.current_locator_id
2533 , msn.lot_number
2534 , 0
2535 , msn.current_status
2536 , mms.status_code
2537 FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2538 WHERE msn.inventory_item_id = p_item_id
2539 AND msn.current_organization_id = p_organization_id
2540 AND msn.current_status = 3
2541 AND msn.current_subinventory_code = p_subinv_code
2542 --AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
2543 AND NVL(msn.current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, msn.current_locator_id, p_locator_id), 0)
2544 AND msn.status_id = mms.status_id(+)
2545 AND mms.language (+) = userenv('LANG')
2546 AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, mag.serial_number, 'S') = 'Y'
2547 AND msn.serial_number = mag.serial_number
2548 AND mag.serial_number LIKE (p_serial)
2549 AND mag.lot_number = p_lot_number
2550 AND (p_revision IS NULL
2551 OR (msn.revision = p_revision)
2552 )
2553 ORDER BY LPAD(mag.serial_number, 20);
2554 END IF;
2555 END get_serial_lov_apl_alloc_pick;
2556
2557 PROCEDURE get_all_serial_lov(x_serial OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_serial IN VARCHAR2) IS
2558 BEGIN
2559 OPEN x_serial FOR
2560 SELECT DISTINCT serial_number
2561 , 'NULL'
2562 , 0
2563 , 'NULL'
2564 , --lot_number,
2565 'NULL'
2566 , 0
2567 , --current_status,
2568 'NULL'
2569 FROM mtl_serial_numbers
2570 WHERE current_organization_id = p_organization_id
2571 AND serial_number LIKE (p_serial)
2572 ORDER BY LPAD(serial_number, 20);
2573 END get_all_serial_lov;
2574
2575 PROCEDURE get_all_to_serial_lov(x_serial OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_from_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER, p_serial IN VARCHAR2) IS
2576 l_prefix VARCHAR2(30);
2577 l_quantity NUMBER;
2578 l_from_number NUMBER;
2579 l_to_number NUMBER;
2580 l_errorcode NUMBER;
2581 l_temp_boolean BOOLEAN;
2582 BEGIN
2583 l_temp_boolean := mtl_serial_check.inv_serial_info(p_from_serial_number, NULL, l_prefix, l_quantity, l_from_number, l_to_number, l_errorcode);
2584 OPEN x_serial FOR
2585 SELECT DISTINCT serial_number
2586 , 'NULL'
2587 , 0
2588 , 'NULL'
2589 , --lot_number,
2590 'NULL'
2591 , 0
2592 , --current_status,
2593 'NULL'
2594 FROM mtl_serial_numbers
2595 WHERE current_organization_id = p_organization_id
2596 AND inventory_item_id = p_inventory_item_id
2597 AND LENGTH(serial_number) = LENGTH(p_from_serial_number)
2598 AND serial_number LIKE (l_prefix || '%')
2599 AND serial_number LIKE (p_serial)
2600 ORDER BY LPAD(serial_number, 20);
2601 END get_all_to_serial_lov;
2602
2603 --"Returns"
2604 PROCEDURE get_return_serial_lov(x_serial OUT NOCOPY t_genref, p_org_id IN NUMBER, p_lpn_id IN NUMBER, p_item_id IN NUMBER, p_revision IN VARCHAR2, p_serial IN VARCHAR2, p_upd_group_id IN NUMBER) IS
2605 dummy_s VARCHAR2(20);
2606 dummy_a VARCHAR2(20);
2607 dummy_b NUMBER;
2608 dummy_c VARCHAR2(20);
2609 dummy_d VARCHAR2(20);
2610 dummy_e NUMBER;
2611 dummy_f VARCHAR2(20);
2612 BEGIN
2613 IF (p_upd_group_id = 1) THEN
2614 UPDATE mtl_serial_numbers
2615 SET group_mark_id = NULL
2616 WHERE current_organization_id = p_org_id
2617 AND group_mark_id IS NOT NULL
2618 AND lpn_id = p_lpn_id
2619 AND inventory_item_id = p_item_id
2620 AND ((revision = p_revision
2621 AND p_revision IS NOT NULL
2622 )
2623 OR (revision IS NULL
2624 AND p_revision IS NULL
2625 )
2626 )
2627 AND last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING');
2628 END IF;
2629
2630 OPEN x_serial FOR
2631 SELECT DISTINCT serial_number
2632 , 'NULL'
2633 , 0
2634 , NVL(lot_number, '')
2635 , --lot_number,
2636 'NULL'
2637 , 0
2638 , --current_status,
2639 'NULL'
2640 FROM mtl_serial_numbers
2641 WHERE current_organization_id = p_org_id
2642 AND (group_mark_id IS NULL
2643 OR group_mark_id = -1
2644 )
2645 AND lpn_id = p_lpn_id
2646 AND inventory_item_id = p_item_id
2647 AND ((revision = p_revision
2648 AND p_revision IS NOT NULL
2649 )
2650 OR (revision IS NULL
2651 AND p_revision IS NULL
2652 )
2653 )
2654 AND last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
2655 AND serial_number LIKE (p_serial)
2656 ORDER BY LPAD(serial_number, 20);
2657 END get_return_serial_lov;
2658
2659 --"Returns"
2660
2661 PROCEDURE get_task_serial_lov(x_serial_number OUT NOCOPY t_genref, p_temp_id IN NUMBER, p_lot_code IN NUMBER) IS
2662 BEGIN
2663 IF (p_lot_code = 1) THEN
2664 OPEN x_serial_number FOR
2665 SELECT fm_serial_number || '-' || to_serial_number
2666 , 0
2667 , 0
2668 , 0
2669 , 0
2670 , ''
2671 , ''
2672 FROM mtl_serial_numbers_temp
2673 WHERE transaction_temp_id = p_temp_id
2674 ORDER BY LPAD(fm_serial_number, 20);
2675 ELSE
2676 OPEN x_serial_number FOR
2677 SELECT msnt.fm_serial_number || '-' || msnt.to_serial_number
2678 , 0
2679 , 0
2680 , 0
2681 , 0
2682 , ''
2683 , ''
2684 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
2685 WHERE mtlt.transaction_temp_id = p_temp_id
2686 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
2687 ORDER BY LPAD(fm_serial_number, 20);
2688 END IF;
2689 END get_task_serial_lov;
2690
2691 -- LOV query for serial triggered subinventory transfer
2692 PROCEDURE get_serial_subxfr_lov(x_serials OUT NOCOPY t_genref, p_current_organization_id IN NUMBER, p_serial_number IN VARCHAR2, p_transaction_type_id IN NUMBER, p_wms_installed IN VARCHAR2) IS
2693 BEGIN
2694 -- For serial triggered subinventory transfer
2695 /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
2696 'MTL_SYSTEM_ITEMS_VL'.*/
2697 OPEN x_serials FOR
2698 SELECT DISTINCT msn.serial_number
2699 , msn.current_subinventory_code
2700 , msn.current_locator_id
2701 , --I Development Bug 2634570
2702 --milv.concatenated_segments,
2703 inv_project.get_locsegs(msn.current_locator_id, p_current_organization_id)
2704 , msn.inventory_item_id
2705 , msiv.concatenated_segments
2706 , msiv.description
2707 , msn.revision
2708 , msn.lot_number
2709 , NVL(msiv.restrict_subinventories_code, 2)
2710 , NVL(msiv.restrict_locators_code, 2)
2711 , msiv.serial_number_control_code
2712 , msi.asset_inventory
2713 , msiv.location_control_code
2714 , msiv.primary_uom_code
2715 , --I Development Bug 2634570
2716 inv_project.get_project_id
2717 , inv_project.get_project_number
2718 , inv_project.get_task_id
2719 , inv_project.get_task_number
2720 FROM mtl_serial_numbers msn
2721 , mtl_system_items_vl msiv
2722 , mtl_item_locations_kfv milv
2723 , mtl_secondary_inventories msi
2724 WHERE msn.current_organization_id = p_current_organization_id
2725 AND msn.lpn_id IS NULL
2726 AND (msn.group_mark_id IS NULL
2727 OR msn.group_mark_id = -1
2728 OR ( msn.group_mark_id IS NOT NULL
2729 -- Performance Bug : 5367744
2730 AND NOT EXISTS (
2731 SELECT 1
2732 FROM mtl_reservations mr
2733 WHERE mr.reservation_id = msn.reservation_id
2734 AND NVL(mr.staged_flag, 'N') = 'Y')
2735 AND NOT EXISTS (
2736 SELECT 1
2737 FROM mtl_serial_numbers_temp msnt
2738 WHERE msn.serial_number BETWEEN msnt.fm_serial_number
2739 AND msnt.to_serial_number)
2740 )
2741
2742 )
2743 AND msn.current_status = 3
2744 AND msn.serial_number LIKE (p_serial_number || '%')
2745 AND milv.organization_id(+) = p_current_organization_id
2746 AND milv.inventory_location_id(+) = msn.current_locator_id
2747 AND msiv.organization_id = p_current_organization_id
2748 AND msiv.inventory_item_id = msn.inventory_item_id
2749 AND msi.organization_id = p_current_organization_id
2750 AND msi.secondary_inventory_name = msn.current_subinventory_code;
2751 END get_serial_subxfr_lov;
2752
2753 -- Name: GET_SERIAL_LOV_MO
2754 --
2755 -- Input parameters:
2756 -- p_Organization_Id which restricts LOV SQL to current org
2757 -- p_item_id which restricts LOV SQL to current item
2758 -- p_serial which restricts LOV SQL to the serial entered
2759 -- p_transaction_type_id trx_type_id
2760 -- p_wms_installed whether WMS-enabled ORG
2761 -- p_move_order_line_id which include the serials allocated to the
2762 -- move order line
2763 --
2764 -- Output parameters:
2765 -- x_serial_number returns LOV rows as reference cursor
2766 --
2767 -- Functions: This API is to return serial numbers limited by
2768 -- the specified move order line and all other avialable serial
2769 -- numbers and status='Received';
2770 PROCEDURE get_serial_lov_mo(x_serial_number OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_serial IN VARCHAR2, p_transaction_type_id IN NUMBER, p_wms_installed IN VARCHAR2, p_move_order_line_id IN NUMBER := NULL) IS
2771 BEGIN
2772 OPEN x_serial_number FOR
2773 SELECT serial_number
2774 , current_subinventory_code
2775 , current_locator_id
2776 , lot_number
2777 , 0
2778 , current_status
2779 , mms.status_code
2780 , ''
2781 , msn.revision
2782 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2783 WHERE inventory_item_id = p_item_id
2784 AND (group_mark_id IS NULL
2785 OR group_mark_id = -1
2786 OR group_mark_id IN (SELECT transaction_temp_id
2787 FROM mtl_material_transactions_temp
2788 WHERE move_order_line_id = p_move_order_line_id
2789 UNION
2790 SELECT mtlt.serial_transaction_temp_id
2791 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
2792 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
2793 AND mtlt.serial_transaction_temp_id IS NOT NULL)
2794 )
2795 AND current_organization_id = p_organization_id
2796 AND current_status = 3
2797 AND msn.lpn_id IS NULL
2798 AND msn.status_id = mms.status_id(+)
2799 AND mms.language (+) = userenv('LANG')
2800 AND serial_number LIKE (p_serial)
2801 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msn.current_subinventory_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'A') = 'Y'
2802 ORDER BY LPAD(serial_number, 20);
2803 END get_serial_lov_mo;
2804
2805 -- Name: GET_SERIAL_LOV_WMA_NEGISS
2806 --
2807 -- Input parameters:
2808 -- p_Organization_Id which restricts LOV SQL to current org
2809 -- p_item_id which restricts LOV SQL to current item
2810 -- p_serial which restricts LOV SQL to the serial entered
2811 -- p_transaction_type_id trx_type_id
2812 -- p_wms_installed whether WMS-enabled ORG
2813 --
2814 -- Output parameters:
2815 -- x_serial_number returns LOV rows as reference cursor
2816 --
2817 -- Functions: This API is to return serial numbers limited to
2818 -- status of 'DEFINED NOT USED' and 'ISSUED OUT OF STORES' (to WIP).
2819 -- Used by WMA negative issue.
2820 --
2821 PROCEDURE get_serial_lov_wma_negiss(x_serial_number OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_serial IN VARCHAR2, p_lot_number IN VARCHAR2, p_transaction_type_id IN NUMBER, p_wms_installed IN VARCHAR2) IS
2822 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2823 l_lot_number VARCHAR2(80);
2824 BEGIN
2825 IF (p_lot_number IS NULL) THEN
2826 l_lot_number := '%';
2827 ELSE
2828 l_lot_number := p_lot_number || '%';
2829 END IF;
2830
2831 OPEN x_serial_number FOR
2832 SELECT serial_number
2833 , current_subinventory_code
2834 , current_locator_id
2835 , lot_number
2836 , ''
2837 , current_status
2838 , mms.status_code
2839 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2840 WHERE current_organization_id = p_organization_id
2841 AND inventory_item_id = p_item_id
2842 AND msn.status_id = mms.status_id(+)
2843 AND mms.language (+) = userenv('LANG')
2844 AND (current_status = 1 or current_status = 6
2845 OR (current_status = 4
2846 AND last_txn_source_type_id = 5 -- returned to WIP
2847 AND (NVL(lot_number, '%') LIKE l_lot_number)))
2848 AND serial_number LIKE (p_serial)
2849 AND (group_mark_id IS NULL OR group_mark_id = -1)
2850 AND (inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, current_organization_id, inventory_item_id, current_subinventory_code, current_locator_id, lot_number, serial_number, 'S')) = 'Y'
2851 ORDER BY LPAD(serial_number, 20);
2852 END get_serial_lov_wma_negiss;
2853
2854 -- Name: GET_SERIAL_LOV_WMA_ISS
2855 --
2856 -- Input parameters:
2857 -- p_Organization_Id which restricts LOV SQL to current org
2858 -- p_item_id which restricts LOV SQL to current item
2859 -- p_serial which restricts LOV SQL to the serial entered
2860 -- p_transaction_type_id trx_type_id
2861 -- p_wms_installed whether WMS-enabled ORG
2862 -- p_lot which restricts LOV SQL to the current lot
2863 --
2864 -- Output parameters:
2865 -- x_serial_number returns LOV rows as reference cursor
2866 --
2867 -- Functions: This API is to return serial numbers limited to
2868 -- a specific lot and status of 'RESIDES IN STORES'. Used by WMA
2869 -- transaction that issue out of inventory.
2870 --
2871 PROCEDURE get_serial_lov_wma_iss(
2872 x_serial_number OUT NOCOPY t_genref
2873 , p_organization_id IN NUMBER
2874 , p_item_id IN NUMBER
2875 , p_serial IN VARCHAR2
2876 , p_transaction_type_id IN NUMBER
2877 , p_wms_installed IN VARCHAR2
2878 , p_subinv IN VARCHAR2
2879 , p_locator_id IN NUMBER
2880 , p_revision IN VARCHAR2
2881 , p_lot IN VARCHAR2
2882 ) IS
2883 BEGIN
2884 OPEN x_serial_number FOR
2885 SELECT DISTINCT serial_number
2886 , current_subinventory_code
2887 , current_locator_id
2888 , lot_number
2889 , ''
2890 , current_status
2891 , mms.status_code
2892 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2893 WHERE current_organization_id = p_organization_id
2894 AND inventory_item_id = p_item_id
2895 AND current_status = 3
2896 AND msn.status_id = mms.status_id(+)
2897 AND mms.language (+) = userenv('LANG')
2898 -- bug 2360642: don't select serials that are packed into lpns
2899 AND msn.lpn_id IS NULL
2900 AND NVL(current_subinventory_code, '$@#$%') = NVL(p_subinv, NVL(current_subinventory_code, '$@#$%'))
2901 AND NVL(current_locator_id, -1) = DECODE(p_locator_id, -1, NVL(current_locator_id, -1), p_locator_id)
2902 AND NVL(lot_number, '$@#$%') = NVL(p_lot, NVL(lot_number, '$@#$%'))
2903 AND NVL(revision, '$@#$%') = NVL(p_revision, NVL(revision, '$@#$%'))
2904 AND serial_number LIKE (p_serial)
2905 AND (group_mark_id IS NULL
2906 OR group_mark_id = -1
2907 )
2908 AND (inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, current_organization_id, inventory_item_id, current_subinventory_code, current_locator_id, lot_number, serial_number, 'S')) =
2909 'Y'
2910 ORDER BY LPAD(serial_number, 20);
2911 END get_serial_lov_wma_iss;
2912
2913 -- Name: GET_SERIAL_LOV_WMA_RCV
2914 --
2915 -- Input parameters:
2916 -- p_Organization_Id which restricts LOV SQL to current org
2917 -- p_item_id which restricts LOV SQL to current item
2918 -- p_serial which restricts LOV SQL to the serial entered
2919 -- p_transaction_type_id trx_type_id
2920 -- p_wms_installed whether WMS-enabled ORG
2921 -- p_wip_entity_id for SN that are 'ISSUED OUT OF STORES' (returned
2922 -- from inventory), restrict to current job/schedule
2923 --
2924 -- Output parameters:
2925 -- x_serial_number returns LOV rows as reference cursor
2926 --
2927 -- Functions: This API is to return serial numbers limited to
2928 -- status of 'DEFINED NOT USED'. Used by WMA completion and negative
2929 -- issue transactions.
2930 --
2931 PROCEDURE get_serial_lov_wma_rcv(
2932 x_serial_number OUT NOCOPY t_genref
2933 , p_organization_id IN NUMBER
2934 , p_item_id IN NUMBER
2935 , p_serial IN VARCHAR2
2936 , p_lot_number IN VARCHAR2
2937 , p_transaction_type_id IN NUMBER
2938 , p_wms_installed IN VARCHAR2
2939 , p_wip_entity_id IN NUMBER
2940 ) IS
2941 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2942 l_lot_number VARCHAR2(80);
2943 BEGIN
2944 IF (p_lot_number IS NULL) THEN
2945 l_lot_number := '%';
2946 ELSE
2947 l_lot_number := p_lot_number || '%';
2948 END IF;
2949
2950 OPEN x_serial_number FOR
2951 SELECT serial_number
2952 , current_subinventory_code
2953 , current_locator_id
2954 , lot_number
2955 , ''
2956 , current_status
2957 , mms.status_code
2958 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2959 WHERE current_organization_id = p_organization_id
2960 AND inventory_item_id = p_item_id
2961 AND msn.status_id = mms.status_id(+)
2962 AND mms.language (+) = userenv('LANG')
2963 AND (current_status = 1 or current_status = 6
2964 OR (current_status = 4
2965 AND last_txn_source_type_id = 5 -- returned to WIP
2966 AND (((p_wip_entity_id <> -1)
2967 AND (p_wip_entity_id = last_txn_source_id)
2968 AND (NVL(lot_number, '%') LIKE l_lot_number)
2969 )
2970 OR ((p_wip_entity_id = -1)
2971 AND (NVL(lot_number, '%') LIKE l_lot_number)
2972 AND (4 = (SELECT entity_type
2973 FROM wip_entities
2974 WHERE wip_entity_id = last_txn_source_id)
2975 )
2976 )
2977 )
2978 )
2979 )
2980 AND serial_number LIKE (p_serial)
2981 AND (group_mark_id IS NULL
2982 OR group_mark_id = -1
2983 )
2984 AND (inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, current_organization_id, inventory_item_id, current_subinventory_code, current_locator_id, lot_number, serial_number, 'S')) = 'Y'
2985 ORDER BY LPAD(serial_number, 20);
2986 END get_serial_lov_wma_rcv;
2987
2988 -- Name: GET_SERIAL_LOV_WMA_RETCOMP
2989 --
2990 -- Input parameters:
2991 -- p_Organization_Id which restricts LOV SQL to current org
2992 -- p_item_id which restricts LOV SQL to current item
2993 -- p_serial which restricts LOV SQL to the serial entered
2994 -- p_transaction_type_id trx_type_id
2995 -- p_wms_installed whether WMS-enabled ORG
2996 -- p_wip_entity_id restricts to SN that were issued to the same job/schedule
2997 --
2998 -- Output parameters:
2999 -- x_serial_number returns LOV rows as reference cursor
3000 --
3001 -- Functions: This API is to return serial numbers limited to
3002 -- status of 'ISSUED OUT OF STORES". Use by WMA component return transactions.
3003 --
3004 PROCEDURE get_serial_lov_wma_retcomp(
3005 x_serial_number OUT NOCOPY t_genref
3006 , p_organization_id IN NUMBER
3007 , p_item_id IN NUMBER
3008 , p_serial IN VARCHAR2
3009 , p_transaction_type_id IN NUMBER
3010 , p_wms_installed IN VARCHAR2
3011 , p_wip_entity_id IN NUMBER, p_lot IN VARCHAR2
3012 ) IS
3013 BEGIN
3014 OPEN x_serial_number FOR
3015 SELECT serial_number
3016 , current_subinventory_code
3017 , current_locator_id
3018 , lot_number
3019 , 0
3020 , current_status
3021 , mms.status_code
3022 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3023 WHERE inventory_item_id = p_item_id
3024 AND (group_mark_id IS NULL
3025 OR group_mark_id = -1
3026 )
3027 AND current_status = 4
3028 AND last_txn_source_type_id = 5 -- issued to WIP
3029 AND msn.status_id = mms.status_id(+)
3030 AND mms.language (+) = userenv('LANG')
3031 AND NVL(lot_number, '$@#$%') = NVL(p_lot, NVL(lot_number, '$@#$%'))
3032 AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
3033 AND (((p_wip_entity_id <> -1)
3034 AND (p_wip_entity_id = last_txn_source_id)
3035 )
3036 OR ((p_wip_entity_id = -1)
3037 AND (4 = (SELECT entity_type
3038 FROM wip_entities
3039 WHERE wip_entity_id = last_txn_source_id)
3040 )
3041 )
3042 )
3043 AND serial_number LIKE (p_serial)
3044 ORDER BY LPAD(serial_number, 20);
3045 END get_serial_lov_wma_retcomp;
3046
3047 PROCEDURE get_serial_lov_wma_retcomp(
3048 x_serial_number OUT NOCOPY t_genref
3049 , p_organization_id IN NUMBER
3050 , p_item_id IN NUMBER
3051 , p_serial IN VARCHAR2
3052 , p_transaction_type_id IN NUMBER
3053 , p_wms_installed IN VARCHAR2
3054 , p_wip_entity_id IN NUMBER
3055 , p_lot IN VARCHAR2
3056 , p_revision IN VARCHAR2
3057 ) IS
3058 BEGIN
3059 OPEN x_serial_number FOR
3060 SELECT serial_number
3061 , current_subinventory_code
3062 , current_locator_id
3063 , lot_number
3064 , 0
3065 , current_status
3066 , mms.status_code
3067 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3068 WHERE inventory_item_id = p_item_id
3069 AND (group_mark_id IS NULL
3070 OR group_mark_id = -1
3071 )
3072 AND current_status = 4
3073 AND last_txn_source_type_id = 5 -- issued to WIP
3074 AND msn.status_id = mms.status_id(+)
3075 AND mms.language (+) = userenv('LANG')
3076 AND NVL(lot_number, '$@#$%') = NVL(p_lot, NVL(lot_number, '$@#$%'))
3077 AND nvl(msn.revision, '$@#$%') = nvl(p_revision, '$@#$%')
3078 AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL,
3079 p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
3080 AND (((p_wip_entity_id <> -1)
3081 AND (p_wip_entity_id = last_txn_source_id)
3082 )
3083 OR ((p_wip_entity_id = -1)
3084 AND (4 = (SELECT entity_type
3085 FROM wip_entities
3086 WHERE wip_entity_id = last_txn_source_id)
3087 )
3088 )
3089 )
3090 AND serial_number LIKE (p_serial)
3091 ORDER BY LPAD(serial_number, 20);
3092 END get_serial_lov_wma_retcomp;
3093
3094 /* Serial Tracking in WIP: Added the following procedure to display the parent serial lov on the
3095 Mobile WIP component issue transactions */
3096
3097 PROCEDURE get_parent_serial_lov_wma(
3098 x_serial_number OUT NOCOPY t_genref,
3099 p_organization_id IN NUMBER,
3100 p_item_id IN NUMBER,
3101 p_serial IN VARCHAR2,
3102 p_transaction_type_id IN NUMBER,
3103 p_transaction_action_id IN NUMBER,
3104 p_wip_entity_id IN NUMBER,
3105 p_wip_assembly_id IN NUMBER,
3106 p_wms_installed IN VARCHAR2)
3107 IS
3108 l_restrict_rcpt_ser NUMBER;
3109 l_wip_assembly_id NUMBER;
3110 BEGIN
3111
3112 BEGIN
3113 select primary_item_id
3114 into l_wip_assembly_id
3115 From wip_discrete_jobs
3116 where wip_entity_id = p_wip_entity_id
3117 And organization_id = p_organization_id;
3118 EXCEPTION
3119 when others then
3120 l_wip_assembly_id := p_wip_assembly_id;
3121 END;
3122
3123 OPEN x_serial_number FOR
3124 select serial_number
3125 , current_subinventory_code
3126 , current_locator_id
3127 , lot_number
3128 , 0
3129 , current_status
3130 , mms.status_code
3131 from mtl_serial_numbers msn, mtl_material_statuses_tl mms
3132 where inventory_item_id = l_wip_assembly_id
3133 and msn.status_id = mms.status_id(+)
3134 AND mms.language (+) = userenv('LANG')
3135 AND CURRENT_organization_id = p_organization_id
3136 and (
3137 ((current_status = 1 or current_status = 6 )
3138 AND p_transaction_action_id =1
3139 AND (wip_entity_id = p_wip_entity_id OR wip_entity_id is null)
3140 )
3141 or ((current_status = 3 OR current_status = 4)
3142 AND last_txn_source_type_id =5
3143 AND last_txn_source_id = p_wip_entity_id
3144 AND p_transaction_type_id = 35
3145 )
3146 )--changed for bug 2767928
3147 and inv_material_status_grp.is_status_applicable(
3148 p_wms_installed, p_transaction_type_id,NULL,NULL,
3149 p_organization_id, p_item_id, NULL, NULL, NULL,
3150 serial_number,'S') = 'Y'
3151 and serial_number like (p_serial)
3152 order by lpad(serial_number,20);
3153 End get_parent_serial_lov_wma;
3154
3155
3156 --
3157 -- New Procedure to get the Flexfield Data for a Lot
3158 --
3159 --
3160 PROCEDURE get_lot_flex_info(
3161 p_org_id IN NUMBER
3162 , p_lot_number IN VARCHAR2
3163 , p_inventory_item_id IN NUMBER
3164 , x_vendor_id OUT NOCOPY NUMBER
3165 , x_grade_code OUT NOCOPY VARCHAR2
3166 , x_origination_date OUT NOCOPY VARCHAR2
3167 , x_date_code OUT NOCOPY VARCHAR2
3168 , x_status_id OUT NOCOPY NUMBER
3169 , x_change_date OUT NOCOPY VARCHAR2
3170 , x_age OUT NOCOPY NUMBER
3171 , x_retest_date OUT NOCOPY VARCHAR2
3172 , x_maturity_date OUT NOCOPY VARCHAR2
3173 , x_lot_attribute_category OUT NOCOPY VARCHAR2
3174 , x_item_size OUT NOCOPY NUMBER
3175 , x_color OUT NOCOPY VARCHAR2
3176 , x_volume OUT NOCOPY NUMBER
3177 , x_volume_uom OUT NOCOPY VARCHAR2
3178 , x_place_of_origin OUT NOCOPY VARCHAR2
3179 , x_best_by_date OUT NOCOPY VARCHAR2
3180 , x_length OUT NOCOPY NUMBER
3181 , x_length_uom OUT NOCOPY VARCHAR2
3182 , x_recycled_content OUT NOCOPY NUMBER
3183 , x_thickness OUT NOCOPY NUMBER
3184 , x_thickness_uom OUT NOCOPY VARCHAR2
3185 , x_width OUT NOCOPY NUMBER
3186 , x_width_uom OUT NOCOPY VARCHAR2
3187 , x_curl_wrinkle_fold OUT NOCOPY VARCHAR2
3188 , x_c_attribute1 OUT NOCOPY VARCHAR2
3189 , x_c_attribute2 OUT NOCOPY VARCHAR2
3190 , x_c_attribute3 OUT NOCOPY VARCHAR2
3191 , x_c_attribute4 OUT NOCOPY VARCHAR2
3192 , x_c_attribute5 OUT NOCOPY VARCHAR2
3193 , x_c_attribute6 OUT NOCOPY VARCHAR2
3194 , x_c_attribute7 OUT NOCOPY VARCHAR2
3195 , x_c_attribute8 OUT NOCOPY VARCHAR2
3196 , x_c_attribute9 OUT NOCOPY VARCHAR2
3197 , x_c_attribute10 OUT NOCOPY VARCHAR2
3198 , x_c_attribute11 OUT NOCOPY VARCHAR2
3199 , x_c_attribute12 OUT NOCOPY VARCHAR2
3200 , x_c_attribute13 OUT NOCOPY VARCHAR2
3201 , x_c_attribute14 OUT NOCOPY VARCHAR2
3202 , x_c_attribute15 OUT NOCOPY VARCHAR2
3203 , x_c_attribute16 OUT NOCOPY VARCHAR2
3204 , x_c_attribute17 OUT NOCOPY VARCHAR2
3205 , x_c_attribute18 OUT NOCOPY VARCHAR2
3206 , x_c_attribute19 OUT NOCOPY VARCHAR2
3207 , x_c_attribute20 OUT NOCOPY VARCHAR2
3208 , x_d_attribute1 OUT NOCOPY VARCHAR2
3209 , x_d_attribute2 OUT NOCOPY VARCHAR2
3210 , x_d_attribute3 OUT NOCOPY VARCHAR2
3211 , x_d_attribute4 OUT NOCOPY VARCHAR2
3212 , x_d_attribute5 OUT NOCOPY VARCHAR2
3213 , x_d_attribute6 OUT NOCOPY VARCHAR2
3214 , x_d_attribute7 OUT NOCOPY VARCHAR2
3215 , x_d_attribute8 OUT NOCOPY VARCHAR2
3216 , x_d_attribute9 OUT NOCOPY VARCHAR2
3217 , x_d_attribute10 OUT NOCOPY VARCHAR2
3218 , x_n_attribute1 OUT NOCOPY NUMBER
3219 , x_n_attribute2 OUT NOCOPY NUMBER
3220 , x_n_attribute3 OUT NOCOPY NUMBER
3221 , x_n_attribute4 OUT NOCOPY NUMBER
3222 , x_n_attribute5 OUT NOCOPY NUMBER
3223 , x_n_attribute6 OUT NOCOPY NUMBER
3224 , x_n_attribute7 OUT NOCOPY NUMBER
3225 , x_n_attribute8 OUT NOCOPY NUMBER
3226 , x_n_attribute9 OUT NOCOPY NUMBER
3227 , x_n_attribute10 OUT NOCOPY NUMBER
3228 , x_supplier_lot_number OUT NOCOPY VARCHAR2
3229 , x_territory_code OUT NOCOPY VARCHAR2
3230 , x_vendor_name OUT NOCOPY VARCHAR2
3231 , x_description OUT NOCOPY VARCHAR2
3232 ) IS
3233 BEGIN
3234 SELECT vendor_id
3235 , grade_code
3236 , TO_CHAR(origination_date, 'YYYY/MM/DD HH24:MI:SS')
3237 , date_code
3238 , status_id
3239 , TO_CHAR(change_date, 'YYYY/MM/DD HH24:MI:SS')
3240 , age
3241 , TO_CHAR(retest_date, 'YYYY/MM/DD HH24:MI:SS')
3242 , TO_CHAR(maturity_date, 'YYYY/MM/DD HH24:MI:SS')
3243 , lot_attribute_category
3244 , item_size
3245 , color
3246 , volume
3247 , volume_uom
3248 , place_of_origin
3249 , TO_CHAR(best_by_date, 'YYYY/MM/DD HH24:MI:SS')
3250 , LENGTH
3251 , length_uom
3252 , recycled_content
3253 , thickness
3254 , thickness_uom
3255 , width
3256 , width_uom
3257 , curl_wrinkle_fold
3258 , c_attribute1
3259 , c_attribute2
3260 , c_attribute3
3261 , c_attribute4
3262 , c_attribute5
3263 , c_attribute6
3264 , c_attribute7
3265 , c_attribute8
3266 , c_attribute9
3267 , c_attribute10
3268 , c_attribute11
3269 , c_attribute12
3270 , c_attribute13
3271 , c_attribute14
3272 , c_attribute15
3273 , c_attribute16
3274 , c_attribute17
3275 , c_attribute18
3276 , c_attribute19
3277 , c_attribute20
3278 , TO_CHAR(d_attribute1, 'YYYY/MM/DD HH24:MI:SS')
3279 , TO_CHAR(d_attribute2, 'YYYY/MM/DD HH24:MI:SS')
3280 , TO_CHAR(d_attribute3, 'YYYY/MM/DD HH24:MI:SS')
3281 , TO_CHAR(d_attribute4, 'YYYY/MM/DD HH24:MI:SS')
3282 , TO_CHAR(d_attribute5, 'YYYY/MM/DD HH24:MI:SS')
3283 , TO_CHAR(d_attribute6, 'YYYY/MM/DD HH24:MI:SS')
3284 , TO_CHAR(d_attribute7, 'YYYY/MM/DD HH24:MI:SS')
3285 , TO_CHAR(d_attribute8, 'YYYY/MM/DD HH24:MI:SS')
3286 , TO_CHAR(d_attribute9, 'YYYY/MM/DD HH24:MI:SS')
3287 , TO_CHAR(d_attribute10, 'YYYY/MM/DD HH24:MI:SS')
3288 , n_attribute1
3289 , n_attribute2
3290 , n_attribute3
3291 , n_attribute4
3292 , n_attribute5
3293 , n_attribute6
3294 , n_attribute7
3295 , n_attribute8
3296 , n_attribute9
3297 , n_attribute10
3298 , supplier_lot_number
3299 , territory_code
3300 , vendor_name
3301 , description
3302 INTO x_vendor_id
3303 , x_grade_code
3304 , x_origination_date
3305 , x_date_code
3306 , x_status_id
3307 , x_change_date
3308 , x_age
3309 , x_retest_date
3310 , x_maturity_date
3311 , x_lot_attribute_category
3312 , x_item_size
3313 , x_color
3314 , x_volume
3315 , x_volume_uom
3316 , x_place_of_origin
3317 , x_best_by_date
3318 , x_length
3319 , x_length_uom
3320 , x_recycled_content
3321 , x_thickness
3322 , x_thickness_uom
3323 , x_width
3324 , x_width_uom
3325 , x_curl_wrinkle_fold
3326 , x_c_attribute1
3327 , x_c_attribute2
3328 , x_c_attribute3
3329 , x_c_attribute4
3330 , x_c_attribute5
3331 , x_c_attribute6
3332 , x_c_attribute7
3333 , x_c_attribute8
3334 , x_c_attribute9
3335 , x_c_attribute10
3336 , x_c_attribute11
3337 , x_c_attribute12
3338 , x_c_attribute13
3339 , x_c_attribute14
3340 , x_c_attribute15
3341 , x_c_attribute16
3342 , x_c_attribute17
3343 , x_c_attribute18
3344 , x_c_attribute19
3345 , x_c_attribute20
3346 , x_d_attribute1
3347 , x_d_attribute2
3348 , x_d_attribute3
3349 , x_d_attribute4
3350 , x_d_attribute5
3351 , x_d_attribute6
3352 , x_d_attribute7
3353 , x_d_attribute8
3354 , x_d_attribute9
3355 , x_d_attribute10
3356 , x_n_attribute1
3357 , x_n_attribute2
3358 , x_n_attribute3
3359 , x_n_attribute4
3360 , x_n_attribute5
3361 , x_n_attribute6
3362 , x_n_attribute7
3363 , x_n_attribute8
3364 , x_n_attribute9
3365 , x_n_attribute10
3366 , x_supplier_lot_number
3367 , x_territory_code
3368 , x_vendor_name
3369 , x_description
3370 FROM mtl_lot_numbers
3371 WHERE organization_id = p_org_id
3372 AND inventory_item_id = p_inventory_item_id
3373 AND lot_number = p_lot_number;
3374 EXCEPTION
3375 WHEN OTHERS THEN
3376 NULL;
3377 END get_lot_flex_info;
3378
3379 -- Bug# 4176656
3380 -- New Procedure to get the Flexfield Data for a given Serial Number
3381 --
3382 --
3383 PROCEDURE get_serial_flex_info(
3384 p_serial_number IN VARCHAR2
3385 , p_inventory_item_id IN NUMBER
3386 , x_attribute_category OUT NOCOPY VARCHAR2
3387 , x_attribute1 OUT NOCOPY VARCHAR2
3388 , x_attribute2 OUT NOCOPY VARCHAR2
3389 , x_attribute3 OUT NOCOPY VARCHAR2
3390 , x_attribute4 OUT NOCOPY VARCHAR2
3391 , x_attribute5 OUT NOCOPY VARCHAR2
3392 , x_attribute6 OUT NOCOPY VARCHAR2
3393 , x_attribute7 OUT NOCOPY VARCHAR2
3394 , x_attribute8 OUT NOCOPY VARCHAR2
3395 , x_attribute9 OUT NOCOPY VARCHAR2
3396 , x_attribute10 OUT NOCOPY VARCHAR2
3397 , x_attribute11 OUT NOCOPY VARCHAR2
3398 , x_attribute12 OUT NOCOPY VARCHAR2
3399 , x_attribute13 OUT NOCOPY VARCHAR2
3400 , x_attribute14 OUT NOCOPY VARCHAR2
3401 , x_attribute15 OUT NOCOPY VARCHAR2
3402 , x_group_mark_id OUT NOCOPY NUMBER
3403 , x_serial_attribute_category OUT NOCOPY VARCHAR2
3404 , x_c_attribute1 OUT NOCOPY VARCHAR2
3405 , x_c_attribute2 OUT NOCOPY VARCHAR2
3406 , x_c_attribute3 OUT NOCOPY VARCHAR2
3407 , x_c_attribute4 OUT NOCOPY VARCHAR2
3408 , x_c_attribute5 OUT NOCOPY VARCHAR2
3409 , x_c_attribute6 OUT NOCOPY VARCHAR2
3410 , x_c_attribute7 OUT NOCOPY VARCHAR2
3411 , x_c_attribute8 OUT NOCOPY VARCHAR2
3412 , x_c_attribute9 OUT NOCOPY VARCHAR2
3413 , x_c_attribute10 OUT NOCOPY VARCHAR2
3414 , x_c_attribute11 OUT NOCOPY VARCHAR2
3415 , x_c_attribute12 OUT NOCOPY VARCHAR2
3416 , x_c_attribute13 OUT NOCOPY VARCHAR2
3417 , x_c_attribute14 OUT NOCOPY VARCHAR2
3418 , x_c_attribute15 OUT NOCOPY VARCHAR2
3419 , x_c_attribute16 OUT NOCOPY VARCHAR2
3420 , x_c_attribute17 OUT NOCOPY VARCHAR2
3421 , x_c_attribute18 OUT NOCOPY VARCHAR2
3422 , x_c_attribute19 OUT NOCOPY VARCHAR2
3423 , x_c_attribute20 OUT NOCOPY VARCHAR2
3424 , x_d_attribute1 OUT NOCOPY VARCHAR2
3425 , x_d_attribute2 OUT NOCOPY VARCHAR2
3426 , x_d_attribute3 OUT NOCOPY VARCHAR2
3427 , x_d_attribute4 OUT NOCOPY VARCHAR2
3428 , x_d_attribute5 OUT NOCOPY VARCHAR2
3429 , x_d_attribute6 OUT NOCOPY VARCHAR2
3430 , x_d_attribute7 OUT NOCOPY VARCHAR2
3431 , x_d_attribute8 OUT NOCOPY VARCHAR2
3432 , x_d_attribute9 OUT NOCOPY VARCHAR2
3433 , x_d_attribute10 OUT NOCOPY VARCHAR2
3434 , x_n_attribute1 OUT NOCOPY NUMBER
3435 , x_n_attribute2 OUT NOCOPY NUMBER
3436 , x_n_attribute3 OUT NOCOPY NUMBER
3437 , x_n_attribute4 OUT NOCOPY NUMBER
3438 , x_n_attribute5 OUT NOCOPY NUMBER
3439 , x_n_attribute6 OUT NOCOPY NUMBER
3440 , x_n_attribute7 OUT NOCOPY NUMBER
3441 , x_n_attribute8 OUT NOCOPY NUMBER
3442 , x_n_attribute9 OUT NOCOPY NUMBER
3443 , x_n_attribute10 OUT NOCOPY NUMBER
3444 ) IS
3445 BEGIN
3446 SELECT attribute_category
3447 , attribute1
3448 , attribute2
3449 , attribute3
3450 , attribute4
3451 , attribute5
3452 , attribute6
3453 , attribute7
3454 , attribute8
3455 , attribute9
3456 , attribute10
3457 , attribute11
3458 , attribute12
3459 , attribute13
3460 , attribute14
3461 , attribute15
3462 , group_mark_id
3463 , serial_attribute_category
3464 , c_attribute1
3465 , c_attribute2
3466 , c_attribute3
3467 , c_attribute4
3468 , c_attribute5
3469 , c_attribute6
3470 , c_attribute7
3471 , c_attribute8
3472 , c_attribute9
3473 , c_attribute10
3474 , c_attribute11
3475 , c_attribute12
3476 , c_attribute13
3477 , c_attribute14
3478 , c_attribute15
3479 , c_attribute16
3480 , c_attribute17
3481 , c_attribute18
3482 , c_attribute19
3483 , c_attribute20
3484 , TO_CHAR(d_attribute1, 'YYYY/MM/DD HH24:MI:SS')
3485 , TO_CHAR(d_attribute2, 'YYYY/MM/DD HH24:MI:SS')
3486 , TO_CHAR(d_attribute3, 'YYYY/MM/DD HH24:MI:SS')
3487 , TO_CHAR(d_attribute4, 'YYYY/MM/DD HH24:MI:SS')
3488 , TO_CHAR(d_attribute5, 'YYYY/MM/DD HH24:MI:SS')
3489 , TO_CHAR(d_attribute6, 'YYYY/MM/DD HH24:MI:SS')
3490 , TO_CHAR(d_attribute7, 'YYYY/MM/DD HH24:MI:SS')
3491 , TO_CHAR(d_attribute8, 'YYYY/MM/DD HH24:MI:SS')
3492 , TO_CHAR(d_attribute9, 'YYYY/MM/DD HH24:MI:SS')
3493 , TO_CHAR(d_attribute10, 'YYYY/MM/DD HH24:MI:SS')
3494 , n_attribute1
3495 , n_attribute2
3496 , n_attribute3
3497 , n_attribute4
3498 , n_attribute5
3499 , n_attribute6
3500 , n_attribute7
3501 , n_attribute8
3502 , n_attribute9
3503 , n_attribute10
3504 INTO x_attribute_category
3505 , x_attribute1
3506 , x_attribute2
3507 , x_attribute3
3508 , x_attribute4
3509 , x_attribute5
3510 , x_attribute6
3511 , x_attribute7
3512 , x_attribute8
3513 , x_attribute9
3514 , x_attribute10
3515 , x_attribute11
3516 , x_attribute12
3517 , x_attribute13
3518 , x_attribute14
3519 , x_attribute15
3520 , x_group_mark_id
3521 , x_serial_attribute_category
3522 , x_c_attribute1
3523 , x_c_attribute2
3524 , x_c_attribute3
3525 , x_c_attribute4
3526 , x_c_attribute5
3527 , x_c_attribute6
3528 , x_c_attribute7
3529 , x_c_attribute8
3530 , x_c_attribute9
3531 , x_c_attribute10
3532 , x_c_attribute11
3533 , x_c_attribute12
3534 , x_c_attribute13
3535 , x_c_attribute14
3536 , x_c_attribute15
3537 , x_c_attribute16
3538 , x_c_attribute17
3539 , x_c_attribute18
3540 , x_c_attribute19
3541 , x_c_attribute20
3542 , x_d_attribute1
3543 , x_d_attribute2
3544 , x_d_attribute3
3545 , x_d_attribute4
3546 , x_d_attribute5
3547 , x_d_attribute6
3548 , x_d_attribute7
3549 , x_d_attribute8
3550 , x_d_attribute9
3551 , x_d_attribute10
3552 , x_n_attribute1
3553 , x_n_attribute2
3554 , x_n_attribute3
3555 , x_n_attribute4
3556 , x_n_attribute5
3557 , x_n_attribute6
3558 , x_n_attribute7
3559 , x_n_attribute8
3560 , x_n_attribute9
3561 , x_n_attribute10
3562 FROM mtl_serial_numbers
3563 WHERE inventory_item_id = p_inventory_item_id
3564 AND serial_number = p_serial_number;
3565 EXCEPTION
3566 WHEN OTHERS THEN
3567 NULL;
3568 END get_serial_flex_info;
3569
3570
3571 PROCEDURE get_item_load_serial_lov
3572 (x_serial_number OUT NOCOPY t_genref ,
3573 p_lpn_id IN NUMBER ,
3574 p_organization_id IN NUMBER ,
3575 p_item_id IN NUMBER ,
3576 p_lot_number IN VARCHAR2 ,
3577 p_serial_number IN VARCHAR2)
3578 IS
3579
3580 BEGIN
3581 OPEN x_serial_number FOR
3582 SELECT serial_number
3583 , current_subinventory_code
3584 , current_locator_id
3585 , lot_number
3586 , 0
3587 , current_status
3588 , mms.status_code
3589 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3590 WHERE lpn_id = p_lpn_id
3591 AND current_organization_id = p_organization_id
3592 AND inventory_item_id = p_item_id
3593 AND NVL(lot_number, 'NOLOT') = NVL(p_lot_number, 'NOLOT')
3594 AND (group_mark_id IS NULL
3595 OR group_mark_id = -1
3596 )
3597 AND msn.status_id = mms.status_id(+)
3598 AND mms.language (+) = userenv('LANG')
3599 AND serial_number LIKE (p_serial_number)
3600 AND inv_material_status_grp.is_status_applicable('TRUE',
3601 NULL,
3602 INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
3603 NULL,
3604 NULL,
3605 p_organization_id,
3606 p_item_id,
3607 NULL,
3608 NULL,
3609 NULL,
3610 msn.serial_number,
3611 'S') = 'Y'
3612 ORDER BY LPAD(msn.serial_number, 20);
3613
3614 END get_item_load_serial_lov;
3615
3616
3617 PROCEDURE get_serial_load_serial_lov
3618 (x_serial_number OUT NOCOPY t_genref ,
3619 p_lpn_id IN NUMBER ,
3620 p_organization_id IN NUMBER ,
3621 p_item_id IN NUMBER ,
3622 p_serial_number IN VARCHAR2)
3623 IS
3624 BEGIN
3625 OPEN x_serial_number FOR
3626 SELECT serial_number
3627 , current_subinventory_code
3628 , current_locator_id
3629 , lot_number
3630 , 0
3631 , current_status
3632 , mms.status_code
3633 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3634 WHERE lpn_id = p_lpn_id
3635 AND current_organization_id = p_organization_id
3636 AND inventory_item_id = p_item_id
3637 AND (group_mark_id IS NULL
3638 OR group_mark_id = -1
3639 )
3640 AND msn.status_id = mms.status_id(+)
3641 AND mms.language (+) = userenv('LANG')
3642 AND serial_number LIKE (p_serial_number)
3643 AND inv_material_status_grp.is_status_applicable('TRUE',
3644 NULL,
3645 INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
3646 NULL,
3647 NULL,
3648 p_organization_id,
3649 p_item_id,
3650 NULL,
3651 NULL,
3652 NULL,
3653 msn.serial_number,
3654 'S') = 'Y'
3655 ORDER BY LPAD(msn.serial_number, 20);
3656
3657 END get_serial_load_serial_lov;
3658
3659 /**
3660 * This procedure fetches the Serial Numbers for an item
3661 * inside a LPN that "Resides in Receiving". It uses the
3662 * serial number in RCV_SERIALS_SUPPLY that corresponds to the
3663 * parent transaction.
3664 * This LOV would be called from the Item-based Putaway Drop
3665 * mobile page when the user confirms a quantity lesser than
3666 * the suggested quantity.
3667 * @param x_serial_number REF cursor containing the serial numbers fetched
3668 * @param p_lpn_id Identifer for the LPN containing the serials
3669 * @param p_organization_id Current Organization
3670 * @param p_inventory_item_id Inventory Item
3671 * @param p_lot_number Lot Number
3672 * @param p_txn_header_id Transaction Header ID. This would be used to match
3673 * with rcv_serials_supply
3674 * @param p_serial Serial Number entered on the UI
3675 **/
3676 PROCEDURE get_rcv_lpn_serial_lov(
3677 x_serial_number OUT NOCOPY t_genref
3678 , p_lpn_id IN NUMBER
3679 , p_organization_id IN NUMBER
3680 , p_inventory_item_id IN NUMBER
3681 , p_lot_number IN VARCHAR2
3682 , p_txn_header_id IN NUMBER
3683 , p_serial IN VARCHAR2) IS
3684 BEGIN
3685 OPEN x_serial_number FOR
3686 SELECT serial_number
3687 , 0
3688 , 0
3689 , 0
3690 , 0
3691 , ''
3692 , ''
3693 FROM mtl_serial_numbers msn
3694 , rcv_serials_supply rss
3695 , rcv_supply rs
3696 WHERE msn.lpn_id = p_lpn_id
3697 AND msn.inventory_item_id = p_inventory_item_id
3698 AND msn.current_organization_id = p_organization_id
3699 AND NVL(msn.lot_number, '&*^') = NVL(p_lot_number, '&*^')
3700 AND msn.serial_number LIKE (p_serial)
3701 AND msn.current_status = 7
3702 AND (group_mark_id IS NULL or group_mark_id = -1)
3703 AND rss.serial_num = msn.serial_number
3704 AND rs.lpn_id = p_lpn_id
3705 AND rss.transaction_id = rs.rcv_transaction_id
3706 AND rs.supply_type_code = 'RECEIVING'
3707 ORDER BY LPAD(serial_number, 20);
3708 END get_rcv_lpn_serial_lov;
3709
3710
3711 /* Bug 4574714-Added the procedure to insert into temp table
3712 based on the ENFORCE_RMA_SERIAL_NUM value in
3713 rcv_parameters. This is called before firing
3714 the LOV query for serials for RMA*/
3715
3716
3717 PROCEDURE insert_temp_table_for_serials
3718 (p_organization_id IN NUMBER,
3719 p_item_id IN NUMBER,
3720 p_wms_installed IN VARCHAR2,
3721 p_oe_order_header_id IN NUMBER,
3722 x_returnSerialVal OUT NOCOPY VARCHAR2,
3723 x_return_status OUT NOCOPY VARCHAR2,
3724 x_errorcode OUT NOCOPY NUMBER) IS
3725
3726 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3727 l_msg_count NUMBER;
3728 l_msg_data VARCHAR2(4000);
3729 l_errorcode VARCHAR2(4000);
3730 l_enforce_rma_sn VARCHAR2(10);
3731 l_count_rows NUMBER;
3732
3733 BEGIN
3734
3735 -- Bug 3907968
3736 -- Changes applicable for patchJ onwards
3737 -- File needed for I branch is ARU: 3439979 and 3810978
3738 -- GET the SERIAL ENFORCE paramneter from Receiving Options
3739 -- IF enforce is YES
3740 -- then
3741 -- For all Order lines matching with the ITEM call INV_RMA_SERIAL_PVT.POPULATE_TEMP_TABLE
3742 -- to populate the temporary serial table MTL_RMA_SERIAL_TEMP
3743 -- Modify the LOV to join with MTL_RMA_SERIAL_TEMP
3744 -- Else
3745 -- the Existing LOV
3746 -- End if
3747
3748
3749 x_returnSerialVal:='N';
3750
3751 select nvl(ENFORCE_RMA_SERIAL_NUM,'N')
3752 into l_enforce_rma_sn
3753 from RCV_PARAMETERS
3754 where organization_id = p_organization_id;
3755
3756
3757 IF ( l_enforce_rma_sn = 'Y' and p_oe_order_header_id is not null) THEN
3758
3759 For c_rma_line in ( select line_id
3760 FROM
3761 OE_ORDER_LINES_all OEL,
3762 OE_ORDER_HEADERS_all OEH
3763 WHERE OEL.LINE_CATEGORY_CODE='RETURN'
3764 AND OEL.INVENTORY_ITEM_ID = p_item_id
3765 AND nvl(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) = p_organization_id
3766 AND OEL.HEADER_ID = OEH.HEADER_ID
3767 AND OEH.HEADER_ID = p_oe_order_header_id
3768 AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY,0)
3769 AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
3770 )
3771 Loop
3772
3773 INV_RMA_SERIAL_PVT.POPULATE_TEMP_TABLE(
3774 p_api_version => 0.9
3775 , p_init_msg_list => FND_API.G_FALSE
3776 , p_commit => FND_API.G_FALSE
3777 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3778 , x_return_status => l_return_status
3779 , x_msg_count => l_msg_count
3780 , x_msg_data => l_msg_data
3781 , x_errorcode => l_errorcode
3782 , p_rma_line_id => c_rma_line.LINE_ID
3783 , p_org_id => P_ORGANIZATION_ID
3784 , p_item_id => p_item_id
3785 );
3786
3787 End loop;
3788
3789 SELECT count(line_id)
3790 INTO l_count_rows
3791 FROM mtl_rma_serial_temp msrt
3792 WHERE msrt.organization_id = p_organization_id
3793 AND msrt.inventory_item_id = p_item_id ;
3794
3795
3796 IF l_count_rows > 0 THEN
3797 x_returnSerialVal:= 'Y' ;
3798 ELSE
3799 x_returnSerialVal:= 'N' ;
3800 END IF;
3801
3802 Else
3803
3804 x_returnSerialVal:= 'N' ;
3805
3806 End if;
3807
3808
3809 END insert_temp_table_for_serials;
3810
3811 /* End of fix for Bug 4574714 */
3812
3813
3814 /* Bug 4574714-Added the new procedure for the serial LOV query
3815 for RMAs. The additional input parameter p_restrict
3816 decides whether the old LOV query or the new one, i.e from
3817 the temp table should be fired.*/
3818
3819 PROCEDURE get_serial_lov_rma_restrict
3820 (x_serial_number OUT NOCOPY t_genref,
3821 p_organization_id IN NUMBER,
3822 p_item_id IN NUMBER,
3823 p_serial IN VARCHAR2,
3824 p_transaction_type_id IN NUMBER,
3825 p_wms_installed IN VARCHAR2,
3826 p_oe_order_header_id IN NUMBER,
3827 p_restrict IN VARCHAR2) IS
3828
3829 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3830 l_msg_count NUMBER;
3831 l_msg_data VARCHAR2(4000);
3832 l_errorcode VARCHAR2(4000);
3833 l_enforce_rma_sn VARCHAR2(10);
3834
3835 BEGIN
3836
3837 IF ( p_restrict = 'Y') THEN
3838
3839 -- Set the new LOV below..
3840 OPEN x_serial_number FOR
3841 SELECT serial_number
3842 , current_subinventory_code
3843 , current_locator_id
3844 , lot_number
3845 , 0
3846 , current_status
3847 , mms.status_code
3848 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3849 WHERE msn.inventory_item_id = p_item_id
3850 AND (group_mark_id IS NULL
3851 OR group_mark_id = -1
3852 )
3853 AND current_status = 4
3854 AND msn.status_id = mms.status_id(+)
3855 AND mms.language (+) = userenv('LANG')
3856 AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
3857 AND msn.serial_number LIKE (p_serial)
3858 AND EXISTS ( select 'x' from mtl_rma_serial_temp msrt
3859 where msrt.organization_id = p_organization_id
3860 and msrt.inventory_item_id = p_item_id
3861 and msrt.serial_number = msn.serial_number
3862 and msrt.serial_number LIKE (p_serial)
3863 )
3864 ORDER BY LPAD(serial_number, 20);
3865
3866 Else
3867
3868 -- the OLD LOV will work and will not restrict
3869 OPEN x_serial_number FOR
3870 SELECT serial_number
3871 , current_subinventory_code
3872 , current_locator_id
3873 , lot_number
3874 , 0
3875 , current_status
3876 , mms.status_code
3877 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3878 WHERE inventory_item_id = p_item_id
3879 AND (group_mark_id IS NULL
3880 OR group_mark_id = -1
3881 )
3882 AND current_status = 4
3883 AND msn.status_id = mms.status_id(+)
3884 AND mms.language (+) = userenv('LANG')
3885 AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
3886 AND serial_number LIKE (p_serial)
3887 ORDER BY LPAD(serial_number, 20);
3888 End if;
3889 END get_serial_lov_rma_restrict;
3890
3891 /* End of fix for Bug 4574714 */
3892
3893 /*Bug 4703782 (FP of BUG 4639427) -Added the procedure for the serial lov for asn */
3894
3895 PROCEDURE get_serial_lov_asn_rcv
3896 (x_serial_number OUT NOCOPY t_genref,
3897 p_organization_id IN NUMBER,
3898 p_item_id IN NUMBER,
3899 p_shipment_header_id IN NUMBER,
3900 p_serial IN VARCHAR2,
3901 p_transaction_type_id IN NUMBER,
3902 p_wms_installed IN VARCHAR2,
3903 p_from_lpn_id IN NUMBER DEFAULT NULL)
3904
3905 IS
3906
3907 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3908
3909 BEGIN
3910
3911
3912 OPEN x_serial_number FOR
3913 SELECT serial_number
3914 , current_subinventory_code
3915 , current_locator_id
3916 , lot_number
3917 , 0
3918 , current_status
3919 , status_code
3920 FROM
3921 (SELECT serial_number
3922 , current_subinventory_code
3923 , current_locator_id
3924 , lot_number
3925 , 0
3926 , current_status
3927 , mms.status_code
3928 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3929 WHERE inventory_item_id = p_item_id
3930 AND (group_mark_id IS NULL
3931 OR group_mark_id = -1
3932 )
3933 AND ((current_organization_id = p_organization_id
3934 AND current_status = 1
3935 )
3936 OR (current_status = 4 AND
3937 Nvl(to_number(fnd_profile.value('INV_RESTRICT_RCPT_SER')), 2) = 2)
3938 )
3939 AND msn.status_id = mms.status_id(+)
3940 AND mms.language (+) = userenv('LANG')
3941 AND serial_number LIKE (p_serial)
3942 AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
3943 UNION
3944 SELECT serial_number
3945 , current_subinventory_code
3946 , current_locator_id
3947 , lot_number
3948 , 0
3949 , current_status
3950 , mms.status_code
3951 FROM rcv_serials_supply rss
3952 , rcv_shipment_lines rsl
3953 , mtl_serial_numbers msn
3954 , mtl_material_statuses_tl mms
3955 WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
3956 AND nvl(rss.supply_type_code, 'SHIPMENT') = 'SHIPMENT'
3957 AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
3958 AND rsl.shipment_header_id = p_shipment_header_id
3959 AND rsl.to_organization_id = p_organization_id
3960 AND rsl.item_id = p_item_id
3961 AND msn.inventory_item_id = p_item_id
3962 AND msn.serial_number = rss.serial_num
3963 AND msn.current_status = 5
3964 AND Nvl(msn.lpn_id,NVL(p_from_lpn_id,-1)) = NVL(p_from_lpn_id, NVL(msn.lpn_id, -1))
3965 AND msn.status_id = mms.status_id(+)
3966 AND mms.language (+) = userenv('LANG')
3967 AND serial_number LIKE (p_serial)
3968 AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y')
3969 ORDER BY LPAD(serial_number, 20) ;
3970
3971 END get_serial_lov_asn_rcv;
3972
3973 /* End of Bug 4703782 */
3974
3975 /* Bug 5577789-Added the procedure to insert into temp table
3976 based on the ENFORCE_RMA_SERIAL_NUM value in
3977 rcv_parameters. This is called before firing
3978 the LOV query for serials for RMA. This is for the deliver step*/
3979
3980
3981 PROCEDURE insert_RMA_serials_for_deliver
3982 (p_organization_id IN NUMBER,
3983 p_item_id IN NUMBER,
3984 p_wms_installed IN VARCHAR2,
3985 p_oe_order_header_id IN NUMBER,
3986 x_returnSerialVal OUT NOCOPY VARCHAR2,
3987 x_return_status OUT NOCOPY VARCHAR2,
3988 x_errorcode OUT NOCOPY NUMBER) IS
3989
3990 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3991 l_msg_count NUMBER;
3992 l_msg_data VARCHAR2(4000);
3993 l_errorcode VARCHAR2(4000);
3994 l_enforce_rma_sn VARCHAR2(10);
3995 l_count_rows NUMBER;
3996
3997 BEGIN
3998
3999 -- Bug 3907968
4000 -- Changes applicable for patchJ onwards
4001 -- File needed for I branch is ARU: 3439979 and 3810978
4002 -- GET the SERIAL ENFORCE paramneter from Receiving Options
4003 -- IF enforce is YES
4004 -- then
4005 -- For all Order lines matching with the ITEM call INV_RMA_SERIAL_PVT.POPULATE_TEMP_TABLE
4006 -- to populate the temporary serial table MTL_RMA_SERIAL_TEMP
4007 -- Modify the LOV to join with MTL_RMA_SERIAL_TEMP
4008 -- Else
4009 -- the Existing LOV
4010 -- End if
4011
4012
4013 x_returnSerialVal:='N';
4014
4015 select nvl(ENFORCE_RMA_SERIAL_NUM,'N')
4016 into l_enforce_rma_sn
4017 from RCV_PARAMETERS
4018 where organization_id = p_organization_id;
4019
4020
4021 IF ( l_enforce_rma_sn = 'Y' and p_oe_order_header_id is not null) THEN
4022
4023 For c_rma_line in ( select line_id
4024 FROM
4025 OE_ORDER_LINES_all OEL,
4026 OE_ORDER_HEADERS_all OEH
4027 WHERE OEL.LINE_CATEGORY_CODE='RETURN'
4028 AND OEL.INVENTORY_ITEM_ID = p_item_id
4029 AND nvl(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) = p_organization_id
4030 AND OEL.HEADER_ID = OEH.HEADER_ID
4031 AND OEH.HEADER_ID = p_oe_order_header_id
4032 AND OEL.ORDERED_QUANTITY >= NVL(OEL.SHIPPED_QUANTITY,0)
4033 AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN_DISPOSITION'
4034 )
4035 Loop
4036
4037 INV_RMA_SERIAL_PVT.POPULATE_TEMP_TABLE(
4038 p_api_version => 0.9
4039 , p_init_msg_list => FND_API.G_FALSE
4040 , p_commit => FND_API.G_FALSE
4041 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
4042 , x_return_status => l_return_status
4043 , x_msg_count => l_msg_count
4044 , x_msg_data => l_msg_data
4045 , x_errorcode => l_errorcode
4046 , p_rma_line_id => c_rma_line.LINE_ID
4047 , p_org_id => P_ORGANIZATION_ID
4048 , p_item_id => p_item_id
4049 );
4050
4051 End loop;
4052
4053 SELECT count(line_id)
4054 INTO l_count_rows
4055 FROM mtl_rma_serial_temp msrt
4056 WHERE msrt.organization_id = p_organization_id
4057 AND msrt.inventory_item_id = p_item_id ;
4058
4059 IF l_count_rows > 0 THEN
4060 x_returnSerialVal:= 'Y' ;
4061 ELSE
4062 x_returnSerialVal:= 'N' ;
4063 END IF;
4064
4065 Else
4066
4067 x_returnSerialVal:= 'N' ;
4068
4069 End if;
4070
4071
4072 END insert_RMA_serials_for_deliver;
4073
4074 /* End of fix for Bug 5577789 */
4075 --bug 6928897
4076 PROCEDURE get_to_ostatus_serial_lov(
4077 x_seriallov OUT NOCOPY t_genref
4078 , p_organization_id IN NUMBER
4079 , p_inventory_item_id IN NUMBER
4080 , p_from_lot_number IN VARCHAR2
4081 , p_to_lot_number IN VARCHAR2
4082 , p_from_serial_number IN VARCHAR2
4083 , p_serial_number IN VARCHAR2
4084 ) IS
4085 BEGIN
4086
4087 OPEN x_seriallov FOR
4088 SELECT serial_number
4089 , current_subinventory_code
4090 , current_locator_id
4091 , lot_number
4092 , 0
4093 , current_status
4094 , status_code
4095 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4096 WHERE current_organization_id = p_organization_id
4097 AND inventory_item_id = p_inventory_item_id
4098 --AND current_status IN (1, 3, 5)
4099 AND current_status IN (1, 3, 5, 7)
4100 AND (p_from_lot_number IS NULL
4101 OR lot_number >= p_from_lot_number
4102 )
4103 AND (p_to_lot_number IS NULL
4104 OR lot_number <= p_to_lot_number
4105 )
4106 AND msn.status_id = mms.status_id(+)
4107 AND mms.language (+) = userenv('LANG')
4108 AND msn.lpn_id is null
4109 AND serial_number >= p_from_serial_number
4110 AND serial_number LIKE (p_serial_number);
4111 END;
4112
4113 PROCEDURE get_serial_lov_ostatus(x_seriallov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_from_lot_number IN VARCHAR2, p_to_lot_number IN VARCHAR2, p_serial_number IN VARCHAR2) IS
4114 BEGIN
4115
4116 OPEN x_seriallov FOR
4117 SELECT serial_number
4118 , current_subinventory_code
4119 , current_locator_id
4120 , lot_number
4121 , 0
4122 , current_status
4123 , mms.status_code
4124 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4125 WHERE current_organization_id = p_organization_id
4126 AND inventory_item_id = p_inventory_item_id
4127 --AND current_status IN (1, 3, 5)
4128 AND current_status IN (1, 3, 5, 7)
4129 AND (p_from_lot_number IS NULL
4130 OR lot_number >= p_from_lot_number
4131 )
4132 AND (p_to_lot_number IS NULL
4133 OR lot_number <= p_to_lot_number
4134 )
4135 AND msn.status_id = mms.status_id(+)
4136 AND mms.language (+) = userenv('LANG')
4137 AND serial_number LIKE (p_serial_number)
4138 AND msn.lpn_id is NULL;
4139 END;
4140 --end of bug 6928897
4141 --bug 6952533
4142 PROCEDURE GET_TO_LPN_SERIAL_LOV_OSTATUS(
4143 x_seriallov OUT NOCOPY t_genref
4144 , p_organization_id IN NUMBER
4145 , p_inventory_item_id IN NUMBER
4146 ,p_lpn_id NUMBER
4147 , p_lot_number IN VARCHAR2
4148 , p_from_serial_number IN VARCHAR2
4149 , p_serial_number IN VARCHAR2
4150 ) IS
4151 BEGIN
4152 /* FP-J Lot/Serial Support Enhancements
4153 * Add current status of resides in receiving
4154 */
4155 OPEN x_seriallov FOR
4156 SELECT serial_number
4157 , current_subinventory_code
4158 , current_locator_id
4159 , lot_number
4160 , 0
4161 , current_status
4162 , status_code
4163 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4164 WHERE current_organization_id = p_organization_id
4165 AND inventory_item_id = p_inventory_item_id
4166 AND (p_lot_number IS NULL
4167 OR lot_number = p_lot_number)
4168 AND msn.status_id = mms.status_id(+)
4169 AND mms.language (+) = userenv('LANG')
4170 AND msn.lpn_id = p_lpn_id
4171 AND serial_number >= p_from_serial_number
4172 AND serial_number LIKE (p_serial_number)
4173 ;
4174 END;
4175
4176 PROCEDURE GET_LPN_STATUS_SERIAL_LOV(x_seriallov OUT NOCOPY t_genref,
4177 p_organization_id IN NUMBER,
4178 p_inventory_item_id IN NUMBER,
4179 p_lpn_id IN NUMBER,
4180 p_lot_number IN VARCHAR2,
4181 p_serial_number IN VARCHAR2) IS
4182 BEGIN
4183 /* FP-J Lot/Serial Support Enhancements
4184 * Add current status of resides in receiving
4185 */
4186 OPEN x_seriallov FOR
4187 SELECT serial_number
4188 , current_subinventory_code
4189 , current_locator_id
4190 , lot_number
4191 , 0
4192 , current_status
4193 , mms.status_code
4194 FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4195 WHERE current_organization_id = p_organization_id
4196 AND inventory_item_id = p_inventory_item_id
4197 --AND current_status IN (1, 3, 5)
4198 AND current_status IN (1, 3, 5, 7)
4199 AND (p_lot_number IS NULL
4200 OR lot_number = p_lot_number
4201 )
4202 AND msn.status_id = mms.status_id(+)
4203 AND mms.language (+) = userenv('LANG')
4204 AND msn.lpn_id = p_lpn_id
4205 AND serial_number LIKE (p_serial_number)
4206 ;
4207 END;
4208 --bug 6952533
4209 END inv_ui_item_att_lovs;
[Home] [Help]