[Home] [Help]
PACKAGE BODY: APPS.SERIAL_CHECK
Source
1 PACKAGE BODY SERIAL_CHECK AS
2 /* $Header: INVMKUMB.pls 120.5 2006/05/13 05:17:04 ramarava noship $ */
3
4 g_debug NUMBER;
5
6 PROCEDURE inv_mark_serial
7 ( from_serial_number VARCHAR2,
8 to_serial_number VARCHAR2 DEFAULT NULL,
9 item_id NUMBER,
10 org_id NUMBER,
11 hdr_id NUMBER,
12 temp_id NUMBER,
13 lot_temp_id NUMBER,
14 success IN OUT NOCOPY NUMBER)
15 IS
16 l_debug NUMBER := 0;
17 l_success NUMBER;
18 BEGIN
19
20 /*** {{ R12 Enhanced reservations code changes ***/
21 -- call the overloaded inv_mark_serial API, with null reservation_id
22 IF (g_debug IS NULL) THEN
23 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
24 END IF;
25
26 l_debug := g_debug;
27
28 IF (l_debug = 1) THEN
29 inv_log_util.trace('In inv_mark_serial, no reservation', 'SERIAL_CHECK');
30 END IF;
31
32 inv_mark_rsv_serial
33 ( from_serial_number => from_serial_number
34 , to_serial_number => to_serial_number
35 , item_id => item_id
36 , org_id => org_id
37 , hdr_id => hdr_id
38 , temp_id => temp_id
39 , lot_temp_id => lot_temp_id
40 , p_reservation_id => null
41 , p_update_reservation => fnd_api.g_false
42 , success => l_success
43 );
44
45 IF (l_debug = 1) THEN
46 inv_log_util.trace('success is ' || l_success, 'SERIAL_CHECK');
47 END IF;
48
49 success := l_success;
50 /*** End R12 }} ***/
51
52 EXCEPTION
53 WHEN OTHERS then
54 if( l_debug = 1 ) then
55 inv_log_util.trace('success is ' || success, 'SERIAL_CHECK');
56 inv_log_util.trace('sqlerrm is ' || substr(sqlerrm, 1, 200), 'SERIAL_CHECK');
57 end if;
58 success := -3;
59 END inv_mark_serial;
60
61
62 /*** {{ R12 Enhanced reservations code changes ***/
63 -- overloaded procedure inv_mark_serial to take input of reservation_id
64 PROCEDURE inv_mark_rsv_serial
65 ( from_serial_number VARCHAR2,
66 to_serial_number VARCHAR2 DEFAULT NULL,
67 item_id NUMBER,
68 org_id NUMBER,
69 hdr_id NUMBER,
70 temp_id NUMBER,
71 lot_temp_id NUMBER,
72 p_reservation_id NUMBER DEFAULT NULL, /*** {{ R12 Enhanced reservations code changes }} ***/
73 p_update_reservation VARCHAR2 DEFAULT fnd_api.g_true, /*** {{ R12 Enhanced reservations code changes }} ***/
74 success IN OUT NOCOPY NUMBER)
75 IS
76 l_debug NUMBER := 0;
77 /*** {{ R12 Enhanced reservations code changes ***/
78 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
79 l_msg_count NUMBER;
80 l_msg_data VARCHAR2(2000);
81 /*** End R12 }} ***/
82 BEGIN
83 IF (g_debug IS NULL) THEN
84 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
85 END IF;
86
87 l_debug := g_debug;
88
89 IF (l_debug = 1) THEN
90 inv_log_util.trace('In inv_mark_serial overloaded with reservation_id', 'SERIAL_CHECK');
91 END IF;
92
93 DECLARE
94 marked_numbers_found NUMBER:= 0;
95 l_update_count NUMBER:= 0; /*** {{ R12 Enhanced reservations code changes ***/
96 l_hdr_id NUMBER;
97 CURSOR serial_lock1 IS
98 SELECT group_mark_id
99 FROM mtl_serial_numbers
100 WHERE current_organization_id = org_id
101 AND inventory_item_id = item_id
102 AND serial_number BETWEEN from_serial_number
103 AND to_serial_number
104 AND LENGTH(serial_number) = Length(from_serial_number)
105 FOR UPDATE OF group_mark_id NOWAIT;
106
107 CURSOR serial_lock2 IS
108 SELECT group_mark_id
109 FROM mtl_serial_numbers
110 WHERE current_organization_id = org_id
111 AND inventory_item_id = item_id
112 AND serial_number = from_serial_number
113 FOR UPDATE OF group_mark_id NOWAIT;
114
115 BEGIN
116
117 SAVEPOINT mark_procedure_scope;
118 IF hdr_id IS NULL then
119 l_hdr_id := 99999;
120 else
121 l_hdr_id := hdr_id;
122 end if;
123 success := 1;
124
125 if( l_debug = 1 ) then
126 inv_log_util.trace('Inside inv_mark_serial hdr_id is ' || l_hdr_id, 'SERIAL_CHECK');
127 end if;
128 IF (to_serial_number IS NOT NULL) and NOT (from_serial_number = to_serial_number ) then
129 if( l_debug = 1 ) then
130 inv_log_util.trace('to_serial_number is ' || to_serial_number, 'SERIAL_CHECK');
131 end if;
132 success := 1 ;
133 OPEN serial_lock1 ;
134
135 SELECT COUNT(group_mark_id)
136 INTO marked_numbers_found
137 FROM mtl_serial_numbers
138 WHERE inventory_item_id = item_id
139 AND group_mark_id > 0
140 AND serial_number between from_serial_number
141 AND to_serial_number
142 AND LENGTH(serial_number) = LENGTH(from_serial_number) ;
143 --BUG 2249383 Cannot have same item with serial number in different org
144 --AND current_organization_id = org_id
145
146 if( l_debug = 1 ) then
147 inv_log_util.trace('marked_numbers_found is ' || marked_numbers_found, 'SERIAL_CHECK');
148 end if;
149 IF (marked_numbers_found > 0) then
150 success := -1 ;
151 CLOSE serial_lock1 ;
152 ROLLBACK TO mark_procedure_scope ;
153 else
154
155 /* Bug 2357069 --
156 -- Delete the condition of current_organization_id = org_id
157 -- Here is the scenario why we should not consider the current_organization_id = org_id in the
158 -- where clause.
159 -- Item Current Organization Serial_Status Serial Number Serial Type
160 -- ABC M1 4 (Issued from Stores) S1 to S10 Unique in Org
161 -- ABC M2 Unique within Item
162 -- Since Serial Number S1 to S10 has status 4 (issued from Stores), user should be able
163 -- to chose S1 to S10 for receipt transaction to organization M2, even though the current
164 -- organization of serial S1 to S10 is M1.
165 */
166 UPDATE mtl_serial_numbers
167 SET lot_line_mark_id = lot_temp_id,
168 line_mark_id = temp_id,
169 group_mark_id = l_hdr_id,
170 reservation_id = nvl(p_reservation_id, reservation_id) /*** {{ R12 Enhanced reservations code changes ***/
171 WHERE inventory_item_id = item_id
172 AND serial_number between from_serial_number
173 AND to_serial_number
174 AND LENGTH(serial_number) = LENGTH(from_serial_number) ;
175 l_update_count := SQL%ROWCOUNT; /*** {{ R12 Enhanced reservations code changes ***/
176 CLOSE serial_lock1;
177 success := 3;
178 end if;
179 if( l_debug = 1 ) then
180 inv_log_util.trace('success is ' || success, 'SERIAL_CHECK');
181 end if;
182 else
183 if( l_debug = 1) then
184 inv_log_util.trace('to_serial_number is null or same', 'SERIAL_CHECK');
185 end if;
186 success := 2 ;
187 OPEN serial_lock2 ;
188
189 SELECT COUNT(group_mark_id)
190 INTO marked_numbers_found
191 FROM mtl_serial_numbers
192 WHERE inventory_item_id = item_id
193 AND group_mark_id > 0
194 AND serial_number = from_serial_number ;
195 --BUG 2249383 Cannot have same item with serial number in different org
196 --AND current_organization_id = org_id
197
198 if( l_debug = 1 ) then
199 inv_log_util.trace('marked_numbers_found is ' || marked_numbers_found, 'SERIAL_CHECK');
200 end if;
201 IF (marked_numbers_found > 0) then
202 success := -1;
203 CLOSE serial_lock2;
204 ROLLBACK TO mark_procedure_scope;
205 else
206 /* Bug 2357069 --
207 -- Delete the condition of current_organization_id = org_id
208 -- Here is the scenario why we should not consider the current_organization_id = org_id in the
209 -- where clause.
210 -- Item Current Organization Serial_Status Serial Number Serial Type
211 -- ABC M1 4 (Issued from Stores) S1 to S10 Unique in Org
212 -- ABC M2 Unique within Item
213 -- Since Serial Number S1 to S10 has status 4 (issued from Stores), user should be able
214 -- to chose S1 to S10 for receipt transaction to organization M2, even though the current
215 -- organization of serial S1 to S10 is M1.
216 */
217
218 UPDATE mtl_serial_numbers
219 SET lot_line_mark_id = lot_temp_id,
220 line_mark_id = temp_id,
221 group_mark_id = l_hdr_id,
222 reservation_id = nvl(p_reservation_id, reservation_id) /*** {{ R12 Enhanced reservations code changes ***/
223 WHERE inventory_item_id = item_id
224 AND serial_number = from_serial_number ;
225 l_update_count := 1; /*** {{ R12 Enhanced reservations code changes ***/
226 CLOSE serial_lock2;
227 success := 3;
228 end if;
229 if( l_debug = 1 ) then
230 inv_log_util.trace('success is ' || success, 'SERIAL_CHECK');
231 end if;
232 end if;
233
234 /*** {{ R12 Enhanced reservations code changes ***/
235 IF (p_update_reservation = fnd_api.g_true and p_reservation_id is not null) THEN
236 BEGIN
237 update mtl_reservations
238 set serial_reservation_quantity = serial_reservation_quantity + l_update_count
239 where reservation_id = p_reservation_id;
240
241 EXCEPTION
242 WHEN others THEN
243 IF (l_debug = 1) THEN
244 inv_log_util.trace('Error updating serial_reservation_quantity in mtl_reservations', 'INV_MARK_SERIAL');
245 inv_log_util.trace('sqlerrm is ' || substr(sqlerrm, 1, 200), 'INV_MARK_SERIAL');
246 END IF;
247 END;
248 END IF;
249 /*** End R12 }} ***/
250 EXCEPTION
251 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION then
252 IF success = 1 then
253 CLOSE serial_lock1;
254 else
255 CLOSE serial_lock2;
256 end if;
257 success := -2;
258 if( l_debug = 1 ) then
259 inv_log_util.trace('success is ' || success, 'SERIAL_CHECK');
260 inv_log_util.trace('app_exceptions.record_lock_exception', 'SERIAL_CHECK');
261 end if;
262 WHEN OTHERS then
263 IF success = 1 then
264 CLOSE serial_lock1;
265 else
266 CLOSE serial_lock2;
267 end if;
268 success := -3;
269 if( l_debug = 1 ) then
270 inv_log_util.trace('success is ' || success, 'SERIAL_CHECK');
271 inv_log_util.trace('sqlerrm is ' || substr(sqlerrm, 1, 200), 'SERIAL_CHECK');
272 end if;
273 END;
274 null;
275 EXCEPTION
276 WHEN OTHERS then
277 if( l_debug = 1 ) then
278 inv_log_util.trace('success is ' || success, 'SERIAL_CHECK');
279 inv_log_util.trace('sqlerrm is ' || substr(sqlerrm, 1, 200), 'SERIAL_CHECK');
280 end if;
281 success := -3;
282 END inv_mark_rsv_serial;
283
284 PROCEDURE inv_unmark_serial
285 ( from_serial_number IN VARCHAR2,
286 to_serial_number IN VARCHAR2,
287 serial_code IN NUMBER,
288 hdr_id IN NUMBER,
289 temp_id IN NUMBER DEFAULT NULL,
290 lot_temp_id IN NUMBER DEFAULT NULL,
291 p_inventory_item_id IN NUMBER DEFAULT NULL)
292 IS
293 l_debug NUMBER := 0;
294 l_success NUMBER;
295 BEGIN
296
297 /*** {{ R12 Enhanced reservations code changes ***/
298 -- call the overloaded inv_unmark_rsv_serial API, with null reservation_id
299 IF (g_debug IS NULL) THEN
300 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
301 END IF;
302
303 l_debug := g_debug;
304
305 IF (l_debug = 1) THEN
306 inv_log_util.trace('In inv_unmark_serial, no reservation', 'SERIAL_CHECK');
307 END IF;
308
309 inv_unmark_rsv_serial
310 ( from_serial_number => from_serial_number
311 , to_serial_number => to_serial_number
312 , serial_code => serial_code
313 , hdr_id => hdr_id
314 , temp_id => temp_id
315 , lot_temp_id => lot_temp_id
316 , p_inventory_item_id => p_inventory_item_id
317 , p_update_reservation => fnd_api.g_false
318 );
319
320 /*** End R12 }} ***/
321
322 EXCEPTION
323 WHEN OTHERS then
324 if( l_debug = 1 ) then
325 inv_log_util.trace('exception in inv_unmark_serial, sqlerrm is ' || substr(sqlerrm, 1, 200), 'SERIAL_CHECK');
326 end if;
327 END inv_unmark_serial;
328
329
330 PROCEDURE inv_unmark_rsv_serial(
331 from_serial_number IN VARCHAR2
332 , to_serial_number IN VARCHAR2
333 , serial_code IN NUMBER
334 , hdr_id IN NUMBER
335 , temp_id IN NUMBER DEFAULT NULL
336 , lot_temp_id IN NUMBER DEFAULT NULL
337 , p_inventory_item_id IN NUMBER DEFAULT NULL
338 , p_update_reservation IN VARCHAR2 DEFAULT fnd_api.g_true
339 ) IS /*** {{ R12 Enhanced reservations code changes ***/
340 unmarked_value NUMBER := -1;
341 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
342 /*** {{ R12 Enhanced reservations code changes ***/
343 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
344 l_msg_count NUMBER;
345 l_msg_data VARCHAR2(2000);
346
347 TYPE rsv_table IS TABLE OF NUMBER
348 INDEX BY BINARY_INTEGER;
349
350 TYPE rsv_count_table IS TABLE OF NUMBER
351 INDEX BY BINARY_INTEGER;
352
353 l_rsv_id_tbl rsv_table;
354 l_rsv_count_tbl rsv_count_table;
355 l_update_count NUMBER;
356
357 CURSOR serial_rsv1 IS
358 SELECT reservation_id
359 , COUNT(reservation_id)
360 FROM mtl_serial_numbers
361 WHERE serial_number = from_serial_number AND
362 inventory_item_id = p_inventory_item_id AND
363 reservation_id IS NOT NULL
364 GROUP BY reservation_id;
365
366 CURSOR serial_rsv2 IS
367 SELECT reservation_id
368 , COUNT(reservation_id)
369 FROM mtl_serial_numbers
370 WHERE serial_number >= NVL(from_serial_number, serial_number) AND
371 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
372 inventory_item_id = p_inventory_item_id AND
373 reservation_id IS NOT NULL
374 GROUP BY reservation_id;
375
376 CURSOR serial_rsv3 IS
377 SELECT reservation_id
378 , COUNT(reservation_id)
379 FROM mtl_serial_numbers
380 WHERE group_mark_id IN(hdr_id, lot_temp_id, temp_id) AND
381 (
382 line_mark_id = temp_id OR
383 line_mark_id IS NULL OR
384 line_mark_id = -1
385 ) AND
386 (
387 lot_line_mark_id = lot_temp_id OR
388 lot_line_mark_id IS NULL
389 OR lot_line_mark_id = -1
390 ) AND
391 serial_number >= NVL(from_serial_number, serial_number) AND
392 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
393 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) ) AND
394 reservation_id IS NOT NULL
395 GROUP BY reservation_id;
396
397 CURSOR serial_rsv4 IS
398 SELECT reservation_id
399 , COUNT(reservation_id)
400 FROM mtl_serial_numbers
401 WHERE group_mark_id IN(hdr_id, temp_id) AND
402 (
403 line_mark_id = temp_id OR
404 line_mark_id IS NULL OR
405 line_mark_id = -1
406 ) AND
407 serial_number >= NVL(from_serial_number, serial_number) AND
408 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
409 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) ) AND
410 reservation_id IS NOT NULL
411 GROUP BY reservation_id;
412
413 CURSOR serial_rsv5 IS
414 SELECT reservation_id
415 , COUNT(reservation_id)
416 FROM mtl_serial_numbers
417 WHERE group_mark_id = hdr_id AND
418 serial_number >= NVL(from_serial_number, serial_number) AND
419 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
420 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) ) AND
421 reservation_id IS NOT NULL
422 GROUP BY reservation_id;
423 /*** End R12 }} ***/
424 BEGIN
425 -- Need to delete from table only if serial_control_code allowed dynamic
426 -- entries in the first place. If dynamic entries not allowed, we need
427 -- not do this delete statement, but only the update. We will always have
428 -- hdr_id and temp_id.
429
430 -- if (serial_code = 5 OR serial_code = 6) then
431 -- unmarked_value := -1 ;
432 -- end if;
433 IF (l_debug = 1) THEN
434 inv_log_util.TRACE('Inside inv_unmark_serial', 'SERIAL_CHECK');
435 inv_log_util.TRACE('from_serial_number = ' || from_serial_number
436 || ' to_serial_number = ' || to_serial_number, 'SERIAL_CHECK');
437 inv_log_util.TRACE('serial_code = ' || serial_code || ' hdr_id = ' || hdr_id
438 || ' temp_id = ' || temp_id || ' lot_temp_id = ' || lot_temp_id
439 || ' p_inventory_item_id = ' || p_inventory_item_id
440 || ' p_update_reservation = ' || p_update_reservation , 'SERIAL_CHECK');
441 END IF;
442
443 IF (p_inventory_item_id IS NOT NULL AND hdr_id IS NULL AND temp_id IS NULL AND
444 lot_temp_id IS NULL) THEN
445 IF (
446 ( (from_serial_number IS NOT NULL) AND(to_serial_number IS NULL) ) OR
447 (
448 from_serial_number = to_serial_number
449 )
450 ) THEN
451 IF (l_debug = 1) THEN
452 inv_log_util.TRACE('Update msn with serial_number= from_serial_number and '
453 || 'inventory_item_id = ' || p_inventory_item_id, 'SERIAL_CHECK');
454 END IF;
455
456 /*** {{ R12 Enhanced reservations code changes ***/
457 IF (p_update_reservation = fnd_api.g_true) THEN
458 OPEN serial_rsv1;
459
460 FETCH serial_rsv1
461 BULK COLLECT
462 INTO l_rsv_id_tbl
463 , l_rsv_count_tbl;
464 CLOSE serial_rsv1;
465 END IF;
466 /*** End R12 }} ***/
467
468 IF (p_update_reservation = fnd_api.g_true) THEN
469 IF (l_debug = 1) THEN
470 inv_log_util.TRACE('Case 1 and p_update_reservation is T', 'inv_unmark_rsv_serial');
471 END IF;
472 UPDATE mtl_serial_numbers
473 SET line_mark_id = unmarked_value
474 , group_mark_id = unmarked_value
475 , lot_line_mark_id = unmarked_value
476 , reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
477 WHERE serial_number = from_serial_number AND inventory_item_id = p_inventory_item_id;
478 IF (l_debug = 1) THEN
479 inv_log_util.trace('Case 1, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
480 END IF;
481 ELSE
482 IF (l_debug = 1) THEN
483 inv_log_util.TRACE('Case 1 and p_update_reservation it F', 'inv_unmark_rsv_serial');
484 END IF;
485 UPDATE mtl_serial_numbers
486 SET line_mark_id = unmarked_value
487 , group_mark_id = unmarked_value
488 , lot_line_mark_id = unmarked_value
489 WHERE serial_number = from_serial_number AND inventory_item_id = p_inventory_item_id;
490 IF (l_debug = 1) THEN
491 inv_log_util.trace('Case 1, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
492 END IF;
493 END IF;
494 ELSE
495 IF (l_debug = 1) THEN
496 inv_log_util.TRACE('Update msn with serial_number >= '
497 || ' nvl(from_serial_number, serial_number) '
498 || ' AND serial_number <= nvl(to_serial_number, '
499 || 'nvl(from_serial_number, serial_number)) AND inventory_item_id= '
500 || p_inventory_item_id, 'SERIAL_CHECK');
501 END IF;
502
503 /*** {{ R12 Enhanced reservations code changes ***/
504 IF (p_update_reservation = fnd_api.g_true) THEN
505 OPEN serial_rsv2;
506 FETCH serial_rsv2
507 BULK COLLECT
508 INTO l_rsv_id_tbl
509 , l_rsv_count_tbl;
510 CLOSE serial_rsv2;
511 END IF;
512 /*** End R12 }} ***/
513
514 IF (p_update_reservation = fnd_api.g_true) THEN
515 IF (l_debug = 1) THEN
516 inv_log_util.TRACE('Case 2 and p_update_reservation it T', 'inv_unmark_rsv_serial');
517 END IF;
518 UPDATE mtl_serial_numbers
519 SET line_mark_id = unmarked_value
520 , group_mark_id = unmarked_value
521 , lot_line_mark_id = unmarked_value
522 , reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
523 WHERE serial_number >= NVL(from_serial_number, serial_number) AND
524 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
525 inventory_item_id = p_inventory_item_id;
526 IF (l_debug = 1) THEN
527 inv_log_util.trace('Case 2, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
528 END IF;
529 ELSE
530 IF (l_debug = 1) THEN
531 inv_log_util.TRACE('Case 2 and p_update_reservation it F', 'inv_unmark_rsv_serial');
532 END IF;
533 UPDATE mtl_serial_numbers
534 SET line_mark_id = unmarked_value
535 , group_mark_id = unmarked_value
536 , lot_line_mark_id = unmarked_value
537 WHERE serial_number >= NVL(from_serial_number, serial_number) AND
538 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
539 inventory_item_id = p_inventory_item_id;
540 IF (l_debug = 1) THEN
541 inv_log_util.trace('Case 2, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
542 END IF;
543 END IF; --END IF p_update_reservation is T
544 END IF; --END IF fm_serial = to_serial
545 ELSE
546 IF (lot_temp_id IS NOT NULL) THEN
547 IF (l_debug = 1) THEN
548 inv_log_util.TRACE('Update msn with group_mark_id in ('
549 || hdr_id || ', ' || lot_temp_id || ', ' || temp_id
550 || 'AND (line_mark_id = ' || temp_id
551 || ' OR line_mark_id is NULL OR line_mark_id = -1)', 'SERIAL_CHECK');
552 inv_log_util.TRACE(' AND (lot_line_mark_id = '|| lot_temp_id
553 || ' OR lot_line_mark_id IS NULL OR '
554 || 'lot_line_mark_id = -1) AND serial_number >= nvl(from_serial_number, serial_number) '
555 || ' serial_number <= nvl(to_serial_number, nvl(from_serial_number, serial_number)) '
556 || ' AND length(serial_number) = length(nvl(from_serial_number, serial_number)) ', 'SERIAL_CHECK');
557 END IF;
558
559 /*** {{ R12 Enhanced reservations code changes ***/
560 IF (p_update_reservation = fnd_api.g_true) THEN
561 OPEN serial_rsv3;
562 FETCH serial_rsv3
563 BULK COLLECT
564 INTO l_rsv_id_tbl
565 , l_rsv_count_tbl;
566 CLOSE serial_rsv3;
567 END IF;
568 /*** End R12 }} ***/
569
570 IF (p_update_reservation = fnd_api.g_true) THEN
571 IF (l_debug = 1) THEN
572 inv_log_util.TRACE('Case 3 and p_update_reservation it T', 'inv_unmark_rsv_serial');
573 END IF;
574 UPDATE mtl_serial_numbers
575 SET line_mark_id = unmarked_value
576 , group_mark_id = unmarked_value
577 , lot_line_mark_id = unmarked_value
578 , reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
579 WHERE group_mark_id IN(hdr_id, lot_temp_id, temp_id) -- Bug 2491094: Added Temp ID also
580 AND
581 (
582 line_mark_id = temp_id OR line_mark_id IS NULL OR line_mark_id = -1
583 ) AND
584 (
585 lot_line_mark_id = lot_temp_id OR lot_line_mark_id IS NULL OR lot_line_mark_id = -1
586 ) AND
587 serial_number >= NVL(from_serial_number, serial_number) AND
588 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
589 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
590 IF (l_debug = 1) THEN
591 inv_log_util.trace('Case 3, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
592 END IF;
593 ELSE
594 IF (l_debug = 1) THEN
595 inv_log_util.TRACE('Case 3 and p_update_reservation it F', 'inv_unmark_rsv_serial');
596 END IF;
597 UPDATE mtl_serial_numbers
598 SET line_mark_id = unmarked_value
599 , group_mark_id = unmarked_value
600 , lot_line_mark_id = unmarked_value
601 WHERE group_mark_id IN(hdr_id, lot_temp_id, temp_id) -- Bug 2491094: Added Temp ID also
602 AND
603 (
604 line_mark_id = temp_id OR line_mark_id IS NULL OR line_mark_id = -1
605 ) AND
606 (
607 lot_line_mark_id = lot_temp_id OR lot_line_mark_id IS NULL OR lot_line_mark_id = -1
608 ) AND
609 serial_number >= NVL(from_serial_number, serial_number) AND
610 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
611 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
612 IF (l_debug = 1) THEN
613 inv_log_util.trace('Case 3, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
614 END IF;
615 END IF;
616 ELSE
617 IF (temp_id IS NOT NULL) THEN
618 IF (l_debug = 1) THEN
619 inv_log_util.TRACE(
620 'update msn with group_mark_id in ('|| hdr_id || ', '|| temp_id
621 || ' AND (line_mark_id = '|| temp_id || ' OR line_mark_id is NULL or line_mark_id = -1 ) AND '
622 || ' serial_number >= nvl(from_serial_number, serial_number) '
623 || ' AND serial_number <= nvl(to_serial_number, nvl(from_serial_number, serial_number)) '
624 || ' AND length(serial_number) = length(nvl(from_serial_number, serial_number))', 'SERIAL_CHECK');
625 END IF;
626
627 /*** {{ R12 Enhanced reservations code changes ***/
628 IF (p_update_reservation = fnd_api.g_true) THEN
629 OPEN serial_rsv4;
630 FETCH serial_rsv4
631 BULK COLLECT
632 INTO l_rsv_id_tbl
633 , l_rsv_count_tbl;
634 CLOSE serial_rsv4;
635 END IF;
636 /*** End R12 }} ***/
637
638 IF (p_update_reservation = fnd_api.g_true) THEN
639 IF (l_debug = 1) THEN
640 inv_log_util.TRACE('Case 4 and p_update_reservation it T', 'inv_unmark_rsv_serial');
641 END IF;
642 UPDATE mtl_serial_numbers
643 SET line_mark_id = unmarked_value
644 , group_mark_id = unmarked_value
645 , lot_line_mark_id = unmarked_value
646 , reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
647 WHERE group_mark_id IN(hdr_id, temp_id) AND
648 (
649 line_mark_id = temp_id OR line_mark_id IS NULL OR line_mark_id = -1
650 ) AND
651 serial_number >= NVL(from_serial_number, serial_number) AND
652 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
653 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
654 IF (l_debug = 1) THEN
655 inv_log_util.trace('Case 4, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
656 END IF;
657 ELSE
658 IF (l_debug = 1) THEN
659 inv_log_util.TRACE('Case 4 and p_update_reservation it F', 'inv_unmark_rsv_serial');
660 END IF;
661 UPDATE mtl_serial_numbers
662 SET line_mark_id = unmarked_value
663 , group_mark_id = unmarked_value
664 , lot_line_mark_id = unmarked_value
665 WHERE group_mark_id IN(hdr_id, temp_id) AND
666 (
667 line_mark_id = temp_id OR line_mark_id IS NULL OR line_mark_id = -1
668 ) AND
669 serial_number >= NVL(from_serial_number, serial_number) AND
670 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
671 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
672 IF (l_debug = 1) THEN
673 inv_log_util.trace('Case 4, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
674 END IF;
675 END IF;
676 ELSE
677 IF (l_debug = 1) THEN
678 inv_log_util.TRACE('update msn with group_mark_id = '
679 || hdr_id || ' AND serial_number >= nvl(from_serial_number, serial_number) '
680 || ' AND serial_number <= nvl(to_serial_number, nvl(from_serial_number, serial_number)) '
681 || ' AND length(serial_number) = length(nvl(from_serial_number, serial_number))', 'SERIAL_CHECK');
682 END IF;
683
684 /*** {{ R12 Enhanced reservations code changes ***/
685 IF (p_update_reservation = fnd_api.g_true) THEN
686 OPEN serial_rsv5;
687 FETCH serial_rsv5
688 BULK COLLECT
689 INTO l_rsv_id_tbl
690 , l_rsv_count_tbl;
691 CLOSE serial_rsv5;
692 END IF;
693 /*** End R12 }} ***/
694
695 IF (p_update_reservation = fnd_api.g_true) THEN
696 IF (l_debug = 1) THEN
697 inv_log_util.TRACE('Case 5 and p_update_reservation it T', 'inv_unmark_rsv_serial');
698 END IF;
699 UPDATE mtl_serial_numbers
700 SET line_mark_id = unmarked_value
701 , group_mark_id = unmarked_value
702 , lot_line_mark_id = unmarked_value
703 , reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
704 WHERE group_mark_id = hdr_id AND
705 serial_number >= NVL(from_serial_number, serial_number) AND
706 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
707 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
708 IF (l_debug = 1) THEN
709 inv_log_util.trace('Case 5, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
710 END IF;
711 ELSE
712 IF (l_debug = 1) THEN
713 inv_log_util.TRACE('Case 5 and p_update_reservation it F', 'inv_unmark_rsv_serial');
714 END IF;
715 UPDATE mtl_serial_numbers
716 SET line_mark_id = unmarked_value
717 , group_mark_id = unmarked_value
718 , lot_line_mark_id = unmarked_value
719 WHERE group_mark_id = hdr_id AND
720 serial_number >= NVL(from_serial_number, serial_number) AND
721 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
722 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
723 IF (l_debug = 1) THEN
724 inv_log_util.trace('Case 5, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
725 END IF;
726 END IF; --END IF p_update_reservation = F
727 END IF; --END IF p_temp_id IS NOT NULL
728 END IF; --END IF lote_temp_id IS NOT NULL
729 END IF; -- inventory_item_id is null
730
731 /*** {{ R12 Enhanced reservations code changes ***/
732 IF (p_update_reservation = fnd_api.g_true) THEN
733 FOR i IN 1 .. l_rsv_id_tbl.COUNT LOOP
734 l_update_count := l_rsv_count_tbl(i) * -1;
735 BEGIN
736 UPDATE mtl_reservations
737 SET serial_reservation_quantity = serial_reservation_quantity + l_update_count
738 WHERE reservation_id = l_rsv_id_tbl(i);
739 EXCEPTION
740 WHEN OTHERS THEN
741 IF (l_debug = 1) THEN
742 inv_log_util.TRACE('Error updating serial_reservation_quantity in mtl_reservations', 'INV_UNMARK_SERIAL');
743 inv_log_util.TRACE('sqlerrm is ' || SUBSTR(SQLERRM, 1, 200), 'INV_UNMARK_SERIAL');
744 END IF;
745 END;
746 END LOOP;
747 END IF;
748 /*** End R12 }} ***/
749 END inv_unmark_rsv_serial;
750
751 PROCEDURE inv_update_marked_serial
752 ( from_serial_number IN VARCHAR2,
753 to_serial_number IN VARCHAR2 DEFAULT NULL,
754 item_id IN NUMBER,
755 org_id IN NUMBER,
756 temp_id IN NUMBER DEFAULT NULL,
757 hdr_id IN NUMBER DEFAULT NULL,
758 lot_temp_id IN NUMBER DEFAULT NULL,
759 success OUT NOCOPY BOOLEAN ) IS
760
761 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
762 BEGIN
763 IF (l_debug = 1) THEN
764 inv_log_util.trace('Inside inv_update_marked_serial','SERIAL_CHECK');
765 inv_log_util.trace('from_serial_number='||from_serial_number,'SERIAL_CHECK');
766 inv_log_util.trace('to_serial_number='||to_serial_number,'SERIAL_CHECK');
767 inv_log_util.trace('item_id='||item_id,'SERIAL_CHECK');
768 inv_log_util.trace('org_id='||org_id,'SERIAL_CHECK');
769 inv_log_util.trace('temp_id='||temp_id,'SERIAL_CHECK');
770 inv_log_util.trace('hdr_id='||hdr_id,'SERIAL_CHECK');
771 inv_log_util.trace('lot_temp_id='||lot_temp_id,'SERIAL_CHECK');
772 END IF;
773
774 success := TRUE;
775
776 IF (temp_id IS NULL AND hdr_id IS NULL) THEN
777 IF (l_debug = 1) THEN
778 inv_log_util.trace('temp_id, hdr_id are both null, return false','SERIAL_CHECK');
779 END IF;
780 success := FALSE;
781 RETURN;
782 END IF;
783
784 IF (to_serial_number IS NULL OR (from_serial_number = to_serial_number)) THEN
785
786 IF (l_debug = 1) THEN
787 inv_log_util.trace('to_serial_number is null or same as from_serial_number','SERIAL_CHECK');
788 END IF;
789
790 BEGIN
791 UPDATE mtl_serial_numbers
792 SET group_mark_id = nvl(temp_id, hdr_id)
793 WHERE inventory_item_id = item_id
794 AND current_organization_id = org_id
795 AND serial_number = from_serial_number ;
796 EXCEPTION
797 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
798 success := FALSE;
799 IF (l_debug = 1) THEN
800 inv_log_util.trace('APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION','SERIAL_CHECK');
801 END IF;
802 END;
803
804 success := TRUE;
805 ELSIF (to_serial_number IS NOT NULL AND NOT (from_serial_number = to_serial_number)) THEN
806
807 IF (l_debug = 1) THEN
808 inv_log_util.trace('to_serial_number not null and different from from_serial_number','SERIAL_CHECK');
809 END IF;
810
811 BEGIN
812 UPDATE mtl_serial_numbers
813 SET group_mark_id = nvl(temp_id, hdr_id)
814 WHERE inventory_item_id = item_id
815 AND current_organization_id = org_id
816 AND serial_number between from_serial_number AND to_serial_number
817 AND LENGTH(serial_number) = LENGTH(from_serial_number) ;
818 EXCEPTION
819 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
820 success := FALSE;
821 IF (l_debug = 1) THEN
822 inv_log_util.trace('APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION','SERIAL_CHECK');
823 END IF;
824 END;
825
826 success := TRUE;
827
828
829 END IF;
830
831 EXCEPTION
832 WHEN others THEN
833 success := FALSE;
834 IF ( l_debug = 1 ) THEN
835 inv_log_util.trace('sqlerrm is ' || substr(sqlerrm, 1, 200),'SERIAL_CHECK');
836 END IF;
837
838 END inv_update_marked_serial;
839
840 END SERIAL_CHECK;