[Home] [Help]
PACKAGE BODY: APPS.SERIAL_CHECK
Source
1 PACKAGE BODY SERIAL_CHECK AS
2 /* $Header: INVMKUMB.pls 120.7 2011/03/02 06:26:21 honwei ship $ */
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
394 reservation_id IS NOT NULL
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
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
518 UPDATE mtl_serial_numbers
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;
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 in (select SERIAL_TRANSACTION_TEMP_ID'
622 || ' from mtl_transaction_lots_temp where group_header_id = '|| hdr_id||' and transaction_temp_id = '||temp_id
626 || ' AND length(serial_number) = length(nvl(from_serial_number, serial_number))', 'SERIAL_CHECK');
623 || ') OR line_mark_id is NULL or line_mark_id = -1 ) AND '
624 || ' serial_number >= nvl(from_serial_number, serial_number) '
625 || ' AND serial_number <= nvl(to_serial_number, nvl(from_serial_number, serial_number)) '
627 END IF;
628
629 /*** {{ R12 Enhanced reservations code changes ***/
630 IF (p_update_reservation = fnd_api.g_true) THEN
631 OPEN serial_rsv4;
632 FETCH serial_rsv4
633 BULK COLLECT
634 INTO l_rsv_id_tbl
635 , l_rsv_count_tbl;
636 CLOSE serial_rsv4;
637 END IF;
638 /*** End R12 }} ***/
639
640 IF (p_update_reservation = fnd_api.g_true) THEN
641 IF (l_debug = 1) THEN
642 inv_log_util.TRACE('Case 4 and p_update_reservation it T', 'inv_unmark_rsv_serial');
643 END IF;
644 UPDATE mtl_serial_numbers
645 SET line_mark_id = unmarked_value
646 , group_mark_id = unmarked_value
647 , lot_line_mark_id = unmarked_value
648 , reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
649 WHERE group_mark_id IN(hdr_id, temp_id) AND
650 (line_mark_id = temp_id OR
651 --Added code for bug 11820774,as for both lot and serial
652 --controlled item line_mark_id != temp_id
653 line_mark_id in (select SERIAL_TRANSACTION_TEMP_ID
654 from mtl_transaction_lots_temp
655 where group_header_id = hdr_id
656 and transaction_temp_id = temp_id)
657 --end fix for bug 11820774
658 OR line_mark_id IS NULL OR line_mark_id = -1
659 ) AND
660 serial_number >= NVL(from_serial_number, serial_number) AND
661 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
662 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
663 IF (l_debug = 1) THEN
664 inv_log_util.trace('Case 4, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
665 END IF;
666 ELSE
667 IF (l_debug = 1) THEN
668 inv_log_util.TRACE('Case 4 and p_update_reservation it F', 'inv_unmark_rsv_serial');
669 END IF;
670 UPDATE mtl_serial_numbers
671 SET line_mark_id = unmarked_value
672 , group_mark_id = unmarked_value
673 , lot_line_mark_id = unmarked_value
674 WHERE group_mark_id IN(hdr_id, temp_id) AND
675 (line_mark_id = temp_id OR
676 --Added code for bug 11820774,as for both lot and serial
677 --controlled item line_mark_id != temp_id
678 line_mark_id in (select SERIAL_TRANSACTION_TEMP_ID
679 from mtl_transaction_lots_temp
680 where group_header_id = hdr_id
681 and transaction_temp_id = temp_id)
682 --end fix for bug 11820774
683 OR line_mark_id IS NULL OR line_mark_id = -1
684 ) AND
685 serial_number >= NVL(from_serial_number, serial_number) AND
686 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
687 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
688 IF (l_debug = 1) THEN
689 inv_log_util.trace('Case 4, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
690 END IF;
691 END IF;
692 ELSE
693 IF (l_debug = 1) THEN
694 inv_log_util.TRACE('update msn with group_mark_id = '
695 || hdr_id || ' AND serial_number >= nvl(from_serial_number, serial_number) '
696 || ' AND serial_number <= nvl(to_serial_number, nvl(from_serial_number, serial_number)) '
697 || ' AND length(serial_number) = length(nvl(from_serial_number, serial_number))', 'SERIAL_CHECK');
698 END IF;
699
700 /*** {{ R12 Enhanced reservations code changes ***/
701 IF (p_update_reservation = fnd_api.g_true) THEN
702 OPEN serial_rsv5;
703 FETCH serial_rsv5
704 BULK COLLECT
705 INTO l_rsv_id_tbl
706 , l_rsv_count_tbl;
707 CLOSE serial_rsv5;
708 END IF;
709 /*** End R12 }} ***/
710
711 IF (p_update_reservation = fnd_api.g_true) THEN
712 IF (l_debug = 1) THEN
713 inv_log_util.TRACE('Case 5 and p_update_reservation it T', '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 , reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
720 WHERE group_mark_id = hdr_id AND
721 serial_number >= NVL(from_serial_number, serial_number) AND
722 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
723 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
724 IF (l_debug = 1) THEN
725 inv_log_util.trace('Case 5, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
726 END IF;
727 ELSE
728 IF (l_debug = 1) THEN
729 inv_log_util.TRACE('Case 5 and p_update_reservation it F', 'inv_unmark_rsv_serial');
730 END IF;
731 UPDATE mtl_serial_numbers
732 SET line_mark_id = unmarked_value
733 , group_mark_id = unmarked_value
734 , lot_line_mark_id = unmarked_value
735 WHERE group_mark_id = hdr_id AND
736 serial_number >= NVL(from_serial_number, serial_number) AND
737 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
738 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
739 IF (l_debug = 1) THEN
740 inv_log_util.trace('Case 5, no. of serials unmarked: ' || SQL%rowcount, 'inv_unmark_rsv_serial');
741 END IF;
742 END IF; --END IF p_update_reservation = F
743 END IF; --END IF p_temp_id IS NOT NULL
744 END IF; --END IF lote_temp_id IS NOT NULL
745 END IF; -- inventory_item_id is null
746
747 /*** {{ R12 Enhanced reservations code changes ***/
748 IF (p_update_reservation = fnd_api.g_true) THEN
749 FOR i IN 1 .. l_rsv_id_tbl.COUNT LOOP
750 l_update_count := l_rsv_count_tbl(i) * -1;
751 BEGIN
752 UPDATE mtl_reservations
753 SET serial_reservation_quantity = serial_reservation_quantity + l_update_count
754 WHERE reservation_id = l_rsv_id_tbl(i);
755 EXCEPTION
756 WHEN OTHERS THEN
757 IF (l_debug = 1) THEN
758 inv_log_util.TRACE('Error updating serial_reservation_quantity in mtl_reservations', 'INV_UNMARK_SERIAL');
759 inv_log_util.TRACE('sqlerrm is ' || SUBSTR(SQLERRM, 1, 200), 'INV_UNMARK_SERIAL');
760 END IF;
761 END;
762 END LOOP;
763 END IF;
764 /*** End R12 }} ***/
765 END inv_unmark_rsv_serial;
766
767 PROCEDURE inv_update_marked_serial
768 ( from_serial_number IN VARCHAR2,
769 to_serial_number IN VARCHAR2 DEFAULT NULL,
770 item_id IN NUMBER,
771 org_id IN NUMBER,
772 temp_id IN NUMBER DEFAULT NULL,
773 hdr_id IN NUMBER DEFAULT NULL,
774 lot_temp_id IN NUMBER DEFAULT NULL,
775 success OUT NOCOPY BOOLEAN ) IS
776
777 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
778 BEGIN
779 IF (l_debug = 1) THEN
780 inv_log_util.trace('Inside inv_update_marked_serial','SERIAL_CHECK');
781 inv_log_util.trace('from_serial_number='||from_serial_number,'SERIAL_CHECK');
782 inv_log_util.trace('to_serial_number='||to_serial_number,'SERIAL_CHECK');
783 inv_log_util.trace('item_id='||item_id,'SERIAL_CHECK');
784 inv_log_util.trace('org_id='||org_id,'SERIAL_CHECK');
785 inv_log_util.trace('temp_id='||temp_id,'SERIAL_CHECK');
786 inv_log_util.trace('hdr_id='||hdr_id,'SERIAL_CHECK');
787 inv_log_util.trace('lot_temp_id='||lot_temp_id,'SERIAL_CHECK');
788 END IF;
789
790 success := TRUE;
791
792 IF (temp_id IS NULL AND hdr_id IS NULL) THEN
793 IF (l_debug = 1) THEN
794 inv_log_util.trace('temp_id, hdr_id are both null, return false','SERIAL_CHECK');
795 END IF;
796 success := FALSE;
797 RETURN;
798 END IF;
799
800 IF (to_serial_number IS NULL OR (from_serial_number = to_serial_number)) THEN
801
802 IF (l_debug = 1) THEN
803 inv_log_util.trace('to_serial_number is null or same as from_serial_number','SERIAL_CHECK');
804 END IF;
805
806 BEGIN
807 UPDATE mtl_serial_numbers
808 --SET group_mark_id = nvl(temp_id, hdr_id)
809 --rtv enhancement
810 SET group_mark_id = Nvl(hdr_id,temp_id)
811 ,line_mark_id = Decode(hdr_id,NULL,NULL,temp_id)
812 ,lot_line_mark_id = Decode(hdr_id,NULL,NULL,lot_temp_id)
813 WHERE inventory_item_id = item_id
814 AND current_organization_id = org_id
815 AND serial_number = from_serial_number ;
816 EXCEPTION
817 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
818 success := FALSE;
819 IF (l_debug = 1) THEN
820 inv_log_util.trace('APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION','SERIAL_CHECK');
821 END IF;
822 END;
823
824 success := TRUE;
825 ELSIF (to_serial_number IS NOT NULL AND NOT (from_serial_number = to_serial_number)) THEN
826
827 IF (l_debug = 1) THEN
828 inv_log_util.trace('to_serial_number not null and different from from_serial_number','SERIAL_CHECK');
829 END IF;
830
831 BEGIN
832 UPDATE mtl_serial_numbers
833 --SET group_mark_id = nvl(temp_id, hdr_id)
834 --rtv enhancement
835 SET group_mark_id = Nvl(hdr_id,temp_id)
836 ,line_mark_id = Decode(hdr_id,NULL,NULL,temp_id)
837 ,lot_line_mark_id = Decode(hdr_id,NULL,NULL,lot_temp_id)
838 WHERE inventory_item_id = item_id
839 AND current_organization_id = org_id
840 AND serial_number between from_serial_number AND to_serial_number
841 AND LENGTH(serial_number) = LENGTH(from_serial_number) ;
842 EXCEPTION
843 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
844 success := FALSE;
845 IF (l_debug = 1) THEN
846 inv_log_util.trace('APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION','SERIAL_CHECK');
847 END IF;
848 END;
849
850 success := TRUE;
851
852
853 END IF;
854
855 EXCEPTION
856 WHEN others THEN
857 success := FALSE;
858 IF ( l_debug = 1 ) THEN
859 inv_log_util.trace('sqlerrm is ' || substr(sqlerrm, 1, 200),'SERIAL_CHECK');
860 END IF;
861
862 END inv_update_marked_serial;
863
864 END SERIAL_CHECK;