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