1 PACKAGE BODY INV_CYC_SERIALS AS
2 /* $Header: INVCYCMB.pls 120.2 2005/08/22 11:59:29 pojha noship $ */
3
4
5 PROCEDURE print_debug (
6 p_err_msg VARCHAR2
7 )
8 IS
9 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
10 BEGIN
11 IF ( l_debug = 1 ) THEN
12 inv_mobile_helper_functions.tracelog ( p_err_msg => p_err_msg,
13 p_module => 'INV_CYC_SERIALS',
14 p_level => 4
15 );
16 END IF;
17 -- dbms_output.put_line(p_err_msg);
18 END print_debug;
19
20 PROCEDURE get_scheduled_serial_lov
21 (x_serials OUT NOCOPY t_genref ,
22 p_organization_id IN NUMBER ,
23 p_subinventory IN VARCHAR2 ,
24 p_locator_id IN NUMBER := NULL ,
25 p_inventory_item_id IN NUMBER ,
26 p_revision IN VARCHAR2 := NULL ,
27 p_lot_number IN VARCHAR2 := NULL ,
28 p_cycle_count_header_id IN NUMBER ,
29 p_parent_lpn_id IN NUMBER := NULL)
30 IS
31
32 BEGIN
33 -- Multiple serial that are scheduled
34 OPEN x_serials FOR
35 SELECT UNIQUE msn.serial_number,
36 msn.current_subinventory_code,
37 msn.current_locator_id,
38 msn.lot_number,
39 0,
40 msn.current_status,
41 mms.status_code
42 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
43 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
44 WHERE msn.inventory_item_id = p_inventory_item_id
45 AND msn.current_organization_id = p_organization_id
46 AND msn.current_status IN (1, 3)
47 AND msn.status_id = mms.status_id(+)
48 AND msn.serial_number = mcsn.serial_number
49 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
50 AND mcce.cycle_count_header_id = p_cycle_count_header_id
51 AND mcce.inventory_item_id = p_inventory_item_id
52 AND mcce.organization_id = p_organization_id
53 AND mcce.subinventory = p_subinventory
54 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
55 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
56 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
57 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
58 AND mcce.entry_status_code IN (1, 3)
59 AND NVL(mcce.export_flag, 2) = 2
60 ORDER BY LPAD(msn.serial_number, 20);
61
62 END get_scheduled_serial_lov;
63
64
65 PROCEDURE get_serial_entry_lov
66 (x_serials OUT NOCOPY t_genref ,
67 p_organization_id IN NUMBER ,
68 p_subinventory IN VARCHAR2 ,
69 p_locator_id IN NUMBER := NULL ,
70 p_inventory_item_id IN NUMBER ,
71 p_revision IN VARCHAR2 := NULL ,
72 p_lot_number IN VARCHAR2 := NULL ,
73 p_cycle_count_header_id IN NUMBER ,
74 p_parent_lpn_id IN NUMBER := NULL)
75 IS
76
77 BEGIN
78 -- Multiple serial that are selected as existing already
79 OPEN x_serials FOR
80 SELECT UNIQUE msn.serial_number,
81 msn.current_subinventory_code,
82 msn.current_locator_id,
83 msn.lot_number,
84 0,
85 msn.current_status,
86 mms.status_code
87 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
88 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
89 WHERE msn.inventory_item_id = p_inventory_item_id
90 AND msn.group_mark_id = 1
91 AND msn.current_organization_id = p_organization_id
92 AND msn.current_status IN (1, 3)
93 AND msn.status_id = mms.status_id(+)
94 AND msn.serial_number = mcsn.serial_number
95 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
96 AND mcce.cycle_count_header_id = p_cycle_count_header_id
97 AND mcce.inventory_item_id = p_inventory_item_id
98 AND mcce.organization_id = p_organization_id
99 AND mcce.subinventory = p_subinventory
100 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
101 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
102 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
103 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
104 AND mcce.entry_status_code IN (1, 3)
105 AND NVL(mcce.export_flag, 2) = 2
106 AND NVL ( mcce.number_of_counts , 0 ) = NVL ( mcsn.number_of_counts , 0 ) -- Bug 4533713
107 ORDER BY LPAD(msn.serial_number, 20);
108
109 END get_serial_entry_lov;
110
111
112 PROCEDURE initialize_scheduled_serials
113 (p_organization_id IN NUMBER ,
114 p_subinventory IN VARCHAR2 ,
115 p_locator_id IN NUMBER := NULL ,
116 p_inventory_item_id IN NUMBER ,
117 p_revision IN VARCHAR2 := NULL ,
118 p_lot_number IN VARCHAR2 := NULL ,
119 p_cycle_count_header_id IN NUMBER ,
120 p_parent_lpn_id IN NUMBER := NULL)
121 IS
122
123 BEGIN
124 -- Multiple serial that are scheduled
125 UPDATE mtl_serial_numbers
126 SET group_mark_id = -1
127 WHERE inventory_item_id = p_inventory_item_id
128 AND current_organization_id = p_organization_id
129 AND serial_number IN
130 (SELECT UNIQUE msn.serial_number
131 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
132 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
133 WHERE msn.inventory_item_id = p_inventory_item_id
134 AND msn.current_organization_id = p_organization_id
135 AND msn.current_status IN (1, 3)
136 AND msn.status_id = mms.status_id(+)
137 AND msn.serial_number = mcsn.serial_number
138 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
139 AND mcce.cycle_count_header_id = p_cycle_count_header_id
140 AND mcce.inventory_item_id = p_inventory_item_id
141 AND mcce.organization_id = p_organization_id
142 AND mcce.subinventory = p_subinventory
143 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
144 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
145 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
146 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
147 AND mcce.entry_status_code IN (1, 3)
148 AND NVL(mcce.export_flag, 2) = 2);
149
150 END initialize_scheduled_serials;
151
152
153 PROCEDURE mark_serial
154 (p_organization_id IN NUMBER ,
155 p_subinventory IN VARCHAR2 ,
156 p_locator_id IN NUMBER := NULL ,
157 p_inventory_item_id IN NUMBER ,
158 p_revision IN VARCHAR2 := NULL ,
159 p_lot_number IN VARCHAR2 := NULL ,
160 p_serial_number IN VARCHAR2 ,
161 p_parent_lpn_id IN NUMBER := NULL ,
162 p_cycle_count_header_id IN NUMBER ,
163 x_return_code OUT NOCOPY NUMBER)
164 IS
165 l_exist_temp NUMBER;
166 l_cycle_count_entry_id NUMBER;
167 l_approval_condition NUMBER;
168
169 BEGIN
170 -- Initialize the return code
171 x_return_code := 0;
172
173 -- First check if the multiple serial entry exists
174 SELECT COUNT(*)
175 INTO l_exist_temp
176 FROM DUAL
177 WHERE EXISTS
178 (SELECT 'multiple-serial'
179 FROM mtl_serial_numbers
180 WHERE inventory_item_id = p_inventory_item_id
181 AND current_organization_id = p_organization_id
182 AND serial_number IN
183 (SELECT UNIQUE msn.serial_number
184 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
185 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
186 WHERE msn.inventory_item_id = p_inventory_item_id
187 AND msn.current_organization_id = p_organization_id
188 AND ( msn.current_status IN (1, 3)
189 OR (msn.last_txn_source_type_id = 9 AND msn.current_status = 4)) --Bug# 3595723
190 AND msn.status_id = mms.status_id(+)
191 AND msn.serial_number = p_serial_number
192 AND msn.serial_number = mcsn.serial_number
193 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
194 AND mcce.cycle_count_header_id = p_cycle_count_header_id
195 AND mcce.inventory_item_id = p_inventory_item_id
196 AND mcce.organization_id = p_organization_id
197 AND mcce.subinventory = p_subinventory
198 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
199 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
200 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
201 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
202 AND mcce.entry_status_code IN (1, 3)
203 AND NVL(mcce.export_flag, 2) = 2));
204
205 print_debug('l_exist_temp = ' || l_exist_temp);
206
207 IF (l_exist_temp <> 0) THEN
208 -- The serial number entry exists and can be marked
209 UPDATE mtl_serial_numbers
210 SET group_mark_id = 1
211 WHERE inventory_item_id = p_inventory_item_id
212 AND current_organization_id = p_organization_id
213 AND serial_number = p_serial_number;
214 ELSE
215 -- Unscheduled multiple serial entries are not allowed
216 -- at this time
217 x_return_code := 1;
218 END IF;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 x_return_code := -1;
223
224 END mark_serial;
225
226
227 PROCEDURE remove_serial
228 (p_organization_id IN NUMBER ,
229 p_subinventory IN VARCHAR2 ,
230 p_locator_id IN NUMBER := NULL ,
231 p_inventory_item_id IN NUMBER ,
232 p_revision IN VARCHAR2 := NULL ,
233 p_lot_number IN VARCHAR2 := NULL ,
234 p_serial_number IN VARCHAR2 ,
238 IS
235 p_parent_lpn_id IN NUMBER := NULL ,
236 p_cycle_count_header_id IN NUMBER ,
237 x_return_code OUT NOCOPY NUMBER)
239 l_exist_temp NUMBER;
240 l_group_mark_id NUMBER;
241 l_cycle_count_entry_id NUMBER;
242
243 BEGIN
244 -- Initialize the return code
245 x_return_code := 0;
246
247 -- First check if the multiple serial entry exists
248 SELECT COUNT(*)
249 INTO l_exist_temp
250 FROM DUAL
251 WHERE EXISTS
252 (SELECT 'multiple-serial'
253 FROM mtl_serial_numbers
254 WHERE inventory_item_id = p_inventory_item_id
255 AND current_organization_id = p_organization_id
256 AND serial_number IN
257 (SELECT UNIQUE msn.serial_number
258 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
259 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
260 WHERE msn.inventory_item_id = p_inventory_item_id
261 AND msn.current_organization_id = p_organization_id
262 AND msn.current_status IN (1, 3)
263 AND msn.status_id = mms.status_id(+)
264 AND msn.serial_number = p_serial_number
265 AND NVL(msn.lot_number, '###' ) = NVL(p_lot_number, '###')
266 AND msn.serial_number = mcsn.serial_number
267 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
268 AND mcce.cycle_count_header_id = p_cycle_count_header_id
269 AND mcce.inventory_item_id = p_inventory_item_id
270 AND mcce.organization_id = p_organization_id
271 AND mcce.subinventory = p_subinventory
272 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
273 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
274 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
275 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
276 AND mcce.entry_status_code IN (1, 3)
277 AND NVL(mcce.export_flag, 2) = 2));
278
279 IF (l_exist_temp <> 0) THEN
280 -- The serial number entry exists so unmark that serial number
281 UPDATE mtl_serial_numbers
282 SET group_mark_id = -1
283 WHERE inventory_item_id = p_inventory_item_id
284 AND current_organization_id = p_organization_id
285 AND serial_number = p_serial_number;
286 ELSE
287 -- The serial number entry does not exist
288 x_return_code := 1;
289 END IF;
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 x_return_code := -1;
294
295 END remove_serial;
296
297
298 PROCEDURE mark_all_present
299 (p_organization_id IN NUMBER ,
300 p_subinventory IN VARCHAR2 ,
301 p_locator_id IN NUMBER := NULL ,
302 p_inventory_item_id IN NUMBER ,
303 p_revision IN VARCHAR2 := NULL ,
304 p_lot_number IN VARCHAR2 := NULL ,
305 p_cycle_count_header_id IN NUMBER ,
306 p_parent_lpn_id IN NUMBER := NULL)
307 IS
308
309 BEGIN
310 -- Multiple serial that are scheduled
311 UPDATE mtl_serial_numbers
312 SET group_mark_id = 1
313 WHERE inventory_item_id = p_inventory_item_id
314 AND current_organization_id = p_organization_id
315 AND serial_number IN
316 (SELECT UNIQUE msn.serial_number
317 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
318 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
319 WHERE msn.inventory_item_id = p_inventory_item_id
320 AND msn.current_organization_id = p_organization_id
321 AND msn.current_status IN (1, 3)
322 AND msn.status_id = mms.status_id(+)
323 AND NVL(msn.lot_number, '###' ) = NVL(p_lot_number, '###')
324 AND msn.serial_number = mcsn.serial_number
325 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
326 AND mcce.cycle_count_header_id = p_cycle_count_header_id
327 AND mcce.inventory_item_id = p_inventory_item_id
328 AND mcce.organization_id = p_organization_id
329 AND mcce.subinventory = p_subinventory
330 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
331 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
332 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
333 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
334 AND mcce.entry_status_code IN (1, 3)
335 AND NVL(mcce.export_flag, 2) = 2);
336
337 END mark_all_present;
338
339
340 PROCEDURE get_serial_entry_number
341 (p_organization_id IN NUMBER ,
342 p_subinventory IN VARCHAR2 ,
343 p_locator_id IN NUMBER := NULL ,
344 p_inventory_item_id IN NUMBER ,
345 p_revision IN VARCHAR2 := NULL ,
346 p_lot_number IN VARCHAR2 := NULL ,
347 p_cycle_count_header_id IN NUMBER ,
348 p_parent_lpn_id IN NUMBER := NULL ,
349 x_number OUT NOCOPY NUMBER)
350 IS
351
352 BEGIN
353 SELECT COUNT(*)
354 INTO x_number
355 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
356 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
357 WHERE msn.inventory_item_id = p_inventory_item_id
361 AND msn.status_id = mms.status_id(+)
358 AND msn.group_mark_id = 1
359 AND msn.current_organization_id = p_organization_id
360 AND msn.current_status IN (1, 3)
362 AND msn.serial_number = mcsn.serial_number
363 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
364 AND mcce.cycle_count_header_id = p_cycle_count_header_id
365 AND mcce.inventory_item_id = p_inventory_item_id
366 AND mcce.organization_id = p_organization_id
367 AND mcce.subinventory = p_subinventory
368 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
369 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
370 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
371 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
372 AND mcce.entry_status_code IN (1, 3)
373 AND NVL(mcce.export_flag, 2) = 2;
374
375 END get_serial_entry_number;
376
377 FUNCTION exist_Serial_number(p_serial_number IN VARCHAR2 ,p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER)
378 RETURN BOOLEAN IS
379 l_temp_buf VARCHAR2(10);
380 BEGIN
381 SELECT 'exists'
382 INTO l_temp_buf
383 FROM mtl_Serial_numbers
384 WHERE serial_number = p_serial_number
385 AND inventory_item_id = p_inventory_item_id
386 AND current_organization_id = p_organization_id;
387
388 --serial number exists...return true
389 RETURN TRUE;
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 RETURN FALSE;
393
394 END exist_Serial_number;
395
396 /* Bug# 3595723 */
397 /* Inserts the serial number into mtl_cc_Serial_numbers if its new, else updates the same */
398 PROCEDURE insert_serial_number
399 (p_serial_number IN VARCHAR2 ,
400 p_cycle_count_header_id IN NUMBER,
401 p_organization_id IN NUMBER ,
402 p_subinventory IN VARCHAR2 ,
403 p_locator_id IN NUMBER := NULL ,
404 p_inventory_item_id IN NUMBER ,
405 p_revision IN VARCHAR2 := NULL ,
406 p_lot_number IN VARCHAR2 := NULL ,
407 p_parent_lpn_id IN NUMBER := NULL
408 ) IS
409
410 l_number_of_counts NUMBER;
411 l_unit_status_current NUMBER;
412 l_unit_status_prior NUMBER;
413 l_unit_status_first NUMBER;
414 l_approval_condition NUMBER;
415 l_pos_adjustment_qty NUMBER;
416 l_neg_adjustment_qty NUMBER;
417 l_cycle_count_entry_id NUMBER;
418
419 BEGIN
420 print_debug('Call to insert_serial_number');
421 print_debug('p_serial_number ' || p_serial_number);
422 print_debug('p_cycle_count_header_id ' || p_cycle_count_header_id);
423 print_debug('p_organization_id ' || p_organization_id);
424 print_debug('p_subinventory ' || p_subinventory);
425 print_debug('p_locator_id ' || p_locator_id);
426 print_debug('p_revision ' || p_revision);
427 print_debug('p_lot_number ' || p_lot_number);
428 print_debug('p_parent_lpn_id ' || p_parent_lpn_id);
429
430 /* Get the cycle count entry id and approval_condition */
431 SELECT cycle_count_entry_id, approval_condition
432 INTO l_cycle_count_entry_id , l_approval_condition
433 FROM mtl_cycle_count_entries
434 WHERE cycle_count_header_id = p_cycle_count_header_id
435 AND entry_status_code IN (1,3)
436 AND inventory_item_id = p_inventory_item_id
437 AND organization_id = p_organization_id
438 AND subinventory = p_subinventory
439 AND nvl(locator_id,-999) = nvl(p_locator_id,-999)
440 AND nvl(lot_number,-999) = nvl(p_lot_number,-999)
441 AND nvl(revision,-999) = nvl(p_revision,-999)
442 AND nvl(parent_lpn_id,-999) = nvl(p_parent_lpn_id,-999);
443
444 print_debug('cycle_count_entry_id ' || l_cycle_count_entry_id);
445
446 /* Check if the serial number already exists */
447 SELECT number_of_counts, unit_status_current, unit_status_first
448 INTO l_number_of_counts, l_unit_status_prior, l_unit_status_first
449 FROM mtl_cc_serial_numbers
450 WHERE cycle_count_entry_id = l_cycle_count_entry_id
451 AND serial_number = p_serial_number;
452
453 print_debug('serial number ' || p_serial_number || ' already exists in mtl_cc_serial_numbers');
454 print_debug('l_number_of_counts ' || l_number_of_counts);
455 print_debug('l_unit_status_prior ' || l_unit_status_prior);
456 print_debug('l_unit_status_first ' || l_unit_status_first);
457
458 /* The serial number exists. Update the data */
459 l_unit_status_current := 1; -- 1 -> Present in the count location, 2 -> Absent
460 l_pos_adjustment_qty := 0; -- 1 -> New serial number found at the location
461 l_neg_adjustment_qty := 0; -- 1 -> Serial number not found at the location
462
463 UPDATE MTL_CC_SERIAL_NUMBERS
464 SET
465 last_update_date = SYSDATE,
466 last_updated_by = fnd_global.user_id,
467 last_update_login = fnd_global.login_id,
468 number_of_counts = nvl(l_number_of_counts,0) + 1,
469 unit_status_current = l_unit_status_current,
470 unit_status_prior = l_unit_status_prior,
471 unit_status_first = l_unit_status_first,
475 WHERE cycle_count_entry_id = l_cycle_count_entry_id
472 approval_condition = l_approval_condition,
473 pos_adjustment_qty = l_pos_adjustment_qty,
474 neg_adjustment_qty = l_neg_adjustment_qty
476 AND serial_number = p_serial_number;
477
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 /* Serial number does not exist. Insert the serial number */
481 l_number_of_counts := NULL;
482 l_unit_status_current := NULL;
483 l_unit_status_prior := NULL;
484 l_unit_status_first := NULL;
485 l_pos_adjustment_qty := 1;
486 l_neg_adjustment_qty := NULL;
487
488 print_debug('could not find serial number ' || p_serial_number);
489
490 INSERT INTO MTL_CC_SERIAL_NUMBERS(
491 cycle_count_entry_id,
492 serial_number,
493 last_update_date,
494 last_updated_by,
495 creation_date,
496 created_by,
497 last_update_login,
498 number_of_counts,
499 unit_status_current,
500 unit_status_prior,
501 unit_status_first,
502 approval_condition,
503 pos_adjustment_qty,
504 neg_adjustment_qty
505 ) VALUES (
506 l_cycle_count_entry_id,
507 p_serial_number,
508 SYSDATE,
509 FND_GLOBAL.USER_ID,
510 SYSDATE,
511 FND_GLOBAL.USER_ID,
512 FND_GLOBAL.LOGIN_ID,
513 l_number_of_counts,
514 l_unit_status_current,
515 l_unit_status_prior,
516 l_unit_status_first,
517 l_approval_condition,
518 l_pos_adjustment_qty,
519 l_neg_adjustment_qty
520 );
521
522 IF(exist_Serial_number(p_serial_number,p_inventory_item_id,p_organization_id) = FALSE) THEN
523 print_Debug('Serial number doesnt exist in msn ');
524 --Insert into MSN in case the serial number doesn't exist there.
525 INSERT INTO MTL_SERIAL_NUMBERS (
526 inventory_item_id,
527 serial_number,
528 last_update_date,
529 last_updated_by,
530 creation_date,
531 created_by,
532 last_update_login,
533 initialization_date,
534 current_status,
535 revision,
536 lot_number,
537 current_subinventory_code,
538 current_locator_id,
539 current_organization_id,
540 last_txn_source_type_id,
541 last_receipt_issue_type,
542 last_txn_source_id,
543 gen_object_id
544 ) VALUES (
545 p_inventory_item_id,
546 p_serial_number,
547 SYSDATE,
548 FND_GLOBAL.USER_ID,
549 SYSDATE,
550 FND_GLOBAL.USER_ID,
551 FND_GLOBAL.LOGIN_ID,
552 SYSDATE,
553 1,
554 p_revision,
555 p_lot_number,
556 p_subinventory,
557 p_locator_id,
558 p_organization_id,
559 9,
560 4,
561 l_cycle_count_entry_id,
562 mtl_gen_object_id_s.NEXTVAL);
563 END IF;
564 WHEN OTHERS THEN
565 print_debug('other exceptions');
566 NULL;
567 END insert_Serial_number;
568
569 /*Bug # 3646068
570 Remove the serial numbers from mtl_cc_Serial_numbers and unmark the serials in mtl_serial_numbers
571 for serials that have been entered till now */
572 PROCEDURE remove_serial_number
573 (p_cycle_count_header_id IN NUMBER,
574 p_organization_id IN NUMBER ,
575 p_subinventory IN VARCHAR2 ,
576 p_locator_id IN NUMBER := NULL ,
577 p_inventory_item_id IN NUMBER ,
578 p_revision IN VARCHAR2 := NULL ,
579 p_lot_number IN VARCHAR2 := NULL ,
580 p_parent_lpn_id IN NUMBER := NULL
581 ) IS
582 l_cycle_count_entry_id NUMBER;
583 l_serial_number VARCHAR2(30);
584 l_pos_adjustment_qty NUMBER;
585
586 CURSOR serial_cur IS
587 SELECT serial_number,
588 pos_adjustment_qty
589 FROM mtl_cc_Serial_numbers
590 WHERE cycle_count_entry_id = l_cycle_count_entry_id;
591
592 BEGIN
593
594 /* Get the cycle count entry id */
595 SELECT cycle_count_entry_id
596 INTO l_cycle_count_entry_id
597 FROM mtl_cycle_count_entries
598 WHERE cycle_count_header_id = p_cycle_count_header_id
599 AND entry_status_code IN (1,3)
600 AND inventory_item_id = p_inventory_item_id
601 AND organization_id = p_organization_id
602 AND subinventory = p_subinventory
603 AND nvl(locator_id,-999) = nvl(p_locator_id,-999)
604 AND nvl(lot_number,-999) = nvl(p_lot_number,-999)
605 AND nvl(revision,-999) = nvl(p_revision,-999)
606 AND nvl(parent_lpn_id,-999) = nvl(p_parent_lpn_id,-999);
607
608 OPEN serial_cur;
609 LOOP
610 FETCH serial_cur INTO l_serial_number, l_pos_adjustment_qty;
611 EXIT WHEN serial_cur%NOTFOUND;
612 IF(nvl(l_pos_adjustment_qty,0) = 1) THEN
613 --This was inserted as part of call to insert_serial_number, Delete from mtl_cc_Serial_numbers
614 BEGIN
615 DELETE FROM mtl_cc_serial_numbers
616 WHERE cycle_count_entry_id = l_cycle_count_entry_id
617 AND serial_number = l_serial_number;
618 EXCEPTION
619 WHEN OTHERS THEN
620 print_debug('Exception while trying to delete from mtl_cc_serial_numbers for serial ' || l_serial_number);
621 END;
622 END IF;
623
624 --Unmark the serial number in mtl_Serial_numbers
625 BEGIN
626
627 UPDATE mtl_serial_numbers
628 SET group_mark_id = NULL
629 WHERE serial_number = l_serial_number
630 AND inventory_item_id = p_inventory_item_id
631 AND current_organization_id = p_organization_id;
632 EXCEPTION
633 WHEN OTHERS THEN
634 print_debug('Exception while updating MSN for serial ' || l_serial_number);
635 END;
636 END LOOP;
637 END remove_Serial_number;
638
639
640
641 END INV_CYC_SERIALS;
[Home] [Help]