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:
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
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;
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:
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;
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');
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:
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 ;
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');
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:
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;
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 ***/
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
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;
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;
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
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;
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:
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
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:
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
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');
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
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
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
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)) '
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
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
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
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');
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');
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');
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');
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;
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:
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;
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:
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
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;
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
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;
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:
836: END IF;
837:
838: END inv_update_marked_serial;
839:
840: END SERIAL_CHECK;