[Home] [Help]
PACKAGE BODY: APPS.INV_CYC_SERIALS
Source
1 PACKAGE BODY INV_CYC_SERIALS AS
2 /* $Header: INVCYCMB.pls 120.8.12020000.2 2012/07/09 08:04:55 asugandh ship $ */
3
4
5 PROCEDURE print_debug(p_err_msg VARCHAR2)
6 IS
7 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
8 BEGIN
9 IF ( l_debug = 1 )
10 THEN
11 inv_mobile_helper_functions.tracelog(p_err_msg => p_err_msg,
12 p_module => 'INV_CYC_SERIALS',
13 p_level => 4
14 );
15 END IF;
16 END print_debug;
17
18 PROCEDURE get_scheduled_serial_lov
19 (x_serials OUT NOCOPY t_genref ,
20 p_organization_id IN NUMBER ,
21 p_subinventory IN VARCHAR2 ,
22 p_locator_id IN NUMBER := NULL ,
23 p_inventory_item_id IN NUMBER ,
24 p_revision IN VARCHAR2 := NULL ,
25 p_lot_number IN VARCHAR2 := NULL ,
26 p_cycle_count_header_id IN NUMBER ,
27 p_parent_lpn_id IN NUMBER := NULL)
28 IS
29
30 BEGIN
31 -- Multiple serial that are scheduled
32 OPEN x_serials FOR
33 SELECT UNIQUE msn.serial_number,
34 msn.current_subinventory_code,
35 msn.current_locator_id,
36 msn.lot_number,
37 0,
38 msn.current_status,
39 mms.status_code
40 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
41 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
42 WHERE msn.inventory_item_id = p_inventory_item_id
43 AND msn.current_organization_id = p_organization_id
44 AND msn.current_status IN (1, 3)
45 AND msn.status_id = mms.status_id(+)
46 AND msn.serial_number = mcsn.serial_number
47 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
48 AND mcce.cycle_count_header_id = p_cycle_count_header_id
49 AND mcce.inventory_item_id = p_inventory_item_id
50 AND mcce.organization_id = p_organization_id
51 AND mcce.subinventory = p_subinventory
52 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
53 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
54 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
55 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
56 AND mcce.entry_status_code IN (1, 3)
57 AND NVL(mcce.export_flag, 2) = 2
58 -- added for bug 13691739 starts
59 AND NOT EXISTS (SELECT 1
60 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
61 mtl_material_transactions_temp mmtt
62 WHERE mmtt.inventory_item_id = p_inventory_item_id
63 AND mmtt.organization_id = p_organization_id
64 AND mmtt.wms_task_status = 4
65 AND mmtt.wms_task_type <> 2
66 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
67 AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
68 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
69 -- added for bug 13691739 ends
70 ORDER BY LPAD(msn.serial_number, 20);
71
72 END get_scheduled_serial_lov;
73
74
75 PROCEDURE get_serial_entry_lov
76 (x_serials OUT NOCOPY t_genref ,
77 p_organization_id IN NUMBER ,
78 p_subinventory IN VARCHAR2 ,
79 p_locator_id IN NUMBER := NULL ,
80 p_inventory_item_id IN NUMBER ,
81 p_revision IN VARCHAR2 := NULL ,
82 p_lot_number IN VARCHAR2 := NULL ,
83 p_cycle_count_header_id IN NUMBER ,
84 p_parent_lpn_id IN NUMBER := NULL)
85 IS
86
87 BEGIN
88 -- Multiple serial that are selected as existing already
89 OPEN x_serials FOR
90 SELECT UNIQUE msn.serial_number,
91 msn.current_subinventory_code,
92 msn.current_locator_id,
93 msn.lot_number,
94 0,
95 msn.current_status,
96 mms.status_code
97 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
98 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
99 WHERE msn.inventory_item_id = p_inventory_item_id
100 AND msn.group_mark_id = 1
101 AND msn.current_organization_id = p_organization_id
102 AND msn.current_status IN (1, 3)
103 AND msn.status_id = mms.status_id(+)
104 AND msn.serial_number = mcsn.serial_number
105 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
106 AND mcce.cycle_count_header_id = p_cycle_count_header_id
107 AND mcce.inventory_item_id = p_inventory_item_id
108 AND mcce.organization_id = p_organization_id
109 AND mcce.subinventory = p_subinventory
110 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
111 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
112 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
113 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
114 AND mcce.entry_status_code IN (1, 3)
115 AND NVL(mcce.export_flag, 2) = 2
116 AND NVL ( mcce.number_of_counts , 0 ) = NVL ( mcsn.number_of_counts , 0 ) -- Bug 4533713
117 -- added for bug 13691739 starts
118 AND NOT EXISTS (SELECT 1
119 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
120 mtl_material_transactions_temp mmtt
121 WHERE mmtt.inventory_item_id = p_inventory_item_id
122 AND mmtt.organization_id = p_organization_id
123 AND mmtt.wms_task_status = 4
124 AND mmtt.wms_task_type <> 2
125 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
126 AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
127 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
128 -- added for bug 13691739 ends
129 ORDER BY LPAD(msn.serial_number, 20);
130
131 END get_serial_entry_lov;
132
133
134 PROCEDURE initialize_scheduled_serials
135 (p_organization_id IN NUMBER ,
136 p_subinventory IN VARCHAR2 ,
137 p_locator_id IN NUMBER := NULL ,
138 p_inventory_item_id IN NUMBER ,
139 p_revision IN VARCHAR2 := NULL ,
140 p_lot_number IN VARCHAR2 := NULL ,
141 p_cycle_count_header_id IN NUMBER ,
142 p_parent_lpn_id IN NUMBER := NULL)
143 IS
144
145 BEGIN
146 -- Multiple serial that are scheduled
147 UPDATE mtl_serial_numbers
148 SET group_mark_id = -1
149 WHERE inventory_item_id = p_inventory_item_id
150 AND current_organization_id = p_organization_id
151 AND serial_number IN
152 (SELECT UNIQUE msn.serial_number
153 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
154 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
155 WHERE msn.inventory_item_id = p_inventory_item_id
156 AND msn.current_organization_id = p_organization_id
157 AND msn.current_status IN (1, 3)
158 AND msn.status_id = mms.status_id(+)
159 AND msn.serial_number = mcsn.serial_number
160 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
161 AND mcce.cycle_count_header_id = p_cycle_count_header_id
162 AND mcce.inventory_item_id = p_inventory_item_id
163 AND mcce.organization_id = p_organization_id
164 AND mcce.subinventory = p_subinventory
165 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
166 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
167 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
168 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
169 AND mcce.entry_status_code IN (1, 3)
170 AND NVL(mcce.export_flag, 2) = 2
171 -- added for bug 13691739 starts
172 AND NOT EXISTS (SELECT 1
173 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
174 mtl_material_transactions_temp mmtt
175 WHERE mmtt.inventory_item_id = p_inventory_item_id
176 AND mmtt.organization_id = p_organization_id
177 AND mmtt.wms_task_status = 4
178 AND mmtt.wms_task_type <> 2
179 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
180 AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
181 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
182 -- added for bug 13691739 ends
183 );
184
185 END initialize_scheduled_serials;
186
187
188 PROCEDURE mark_serial
189 (p_organization_id IN NUMBER ,
190 p_subinventory IN VARCHAR2 ,
191 p_locator_id IN NUMBER := NULL ,
192 p_inventory_item_id IN NUMBER ,
193 p_revision IN VARCHAR2 := NULL ,
194 p_lot_number IN VARCHAR2 := NULL ,
195 p_serial_number IN VARCHAR2 ,
196 p_parent_lpn_id IN NUMBER := NULL ,
197 p_cycle_count_header_id IN NUMBER ,
198 x_return_code OUT NOCOPY NUMBER)
199 IS
200 l_exist_temp NUMBER;
201 l_cycle_count_entry_id NUMBER;
202 l_approval_condition NUMBER;
203
204 BEGIN
205 -- Initialize the return code
206 x_return_code := 0;
207
208 -- First check if the multiple serial entry exists
209 SELECT COUNT(*)
210 INTO l_exist_temp
211 FROM DUAL
212 WHERE EXISTS
213 (SELECT 'multiple-serial'
214 FROM mtl_serial_numbers
215 WHERE inventory_item_id = p_inventory_item_id
216 AND current_organization_id = p_organization_id
217 AND serial_number IN
218 (SELECT UNIQUE msn.serial_number
219 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
220 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
221 WHERE msn.inventory_item_id = p_inventory_item_id
222 AND msn.current_organization_id = p_organization_id
223 AND msn.current_status IN (1, 3, 4) -- For bug 14144558, added 4 in the list and commented below check
224 --OR (msn.last_txn_source_type_id in (9,10) AND msn.current_status = 4)) --Bug# 3595723 Bug11875440
225 AND msn.status_id = mms.status_id(+)
226 AND msn.serial_number = p_serial_number
227 AND msn.serial_number = mcsn.serial_number
228 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
229 AND mcce.cycle_count_header_id = p_cycle_count_header_id
230 AND mcce.inventory_item_id = p_inventory_item_id
231 AND mcce.organization_id = p_organization_id
232 AND mcce.subinventory = p_subinventory
233 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
234 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
235 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
236 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
237 AND mcce.entry_status_code IN (1, 3)
238 AND NVL(mcce.export_flag, 2) = 2
239 -- added for bug 13691739 starts
240 AND NOT EXISTS (SELECT 1
241 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
242 mtl_material_transactions_temp mmtt
243 WHERE mmtt.inventory_item_id = p_inventory_item_id
244 AND mmtt.organization_id = p_organization_id
245 AND mmtt.wms_task_status = 4
246 AND mmtt.wms_task_type <> 2
247 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
248 AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
249 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
250 -- added for bug 13691739 ends
251 ));
252
253 print_debug('l_exist_temp = ' || l_exist_temp);
254
255 IF (l_exist_temp <> 0) THEN
256 -- The serial number entry exists and can be marked
257 UPDATE mtl_serial_numbers
258 SET group_mark_id = 1
259 WHERE inventory_item_id = p_inventory_item_id
260 AND current_organization_id = p_organization_id
261 AND serial_number = p_serial_number;
262 ELSE
263 -- Unscheduled multiple serial entries are not allowed
264 -- at this time
265 x_return_code := 1;
266 END IF;
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 x_return_code := -1;
271
272 END mark_serial;
273
274
275 PROCEDURE remove_serial
276 (p_organization_id IN NUMBER ,
277 p_subinventory IN VARCHAR2 ,
278 p_locator_id IN NUMBER := NULL ,
279 p_inventory_item_id IN NUMBER ,
280 p_revision IN VARCHAR2 := NULL ,
281 p_lot_number IN VARCHAR2 := NULL ,
282 p_serial_number IN VARCHAR2 ,
283 p_parent_lpn_id IN NUMBER := NULL ,
284 p_cycle_count_header_id IN NUMBER ,
285 x_return_code OUT NOCOPY NUMBER)
286 IS
287 l_exist_temp NUMBER;
288 l_group_mark_id NUMBER;
289 l_cycle_count_entry_id NUMBER;
290
291 BEGIN
292 -- Initialize the return code
293 x_return_code := 0;
294
295 -- First check if the multiple serial entry exists
296 SELECT COUNT(*)
297 INTO l_exist_temp
298 FROM DUAL
299 WHERE EXISTS
300 (SELECT 'multiple-serial'
301 FROM mtl_serial_numbers
302 WHERE inventory_item_id = p_inventory_item_id
303 AND current_organization_id = p_organization_id
304 AND serial_number IN
305 (SELECT UNIQUE msn.serial_number
306 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
307 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
308 WHERE msn.inventory_item_id = p_inventory_item_id
309 AND msn.current_organization_id = p_organization_id
310 AND msn.current_status IN (1, 3, 4) -- For bug 14144558, added 4 in the list
311 AND msn.status_id = mms.status_id(+)
312 AND msn.serial_number = p_serial_number
313 AND NVL(msn.lot_number, '###' ) = NVL(p_lot_number, '###')
314 AND msn.serial_number = mcsn.serial_number
315 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
316 AND mcce.cycle_count_header_id = p_cycle_count_header_id
317 AND mcce.inventory_item_id = p_inventory_item_id
318 AND mcce.organization_id = p_organization_id
319 AND mcce.subinventory = p_subinventory
320 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
321 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
322 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
323 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
324 AND mcce.entry_status_code IN (1, 3)
325 AND NVL(mcce.export_flag, 2) = 2
326 -- added for bug 13691739 starts
327 AND NOT EXISTS (SELECT 1
328 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
329 mtl_material_transactions_temp mmtt
330 WHERE mmtt.inventory_item_id = p_inventory_item_id
331 AND mmtt.organization_id = p_organization_id
332 AND mmtt.wms_task_status = 4
333 AND mmtt.wms_task_type <> 2
334 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
335 AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
336 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
337 -- added for bug 13691739 ends
338 ));
339
340 IF (l_exist_temp <> 0) THEN
341 -- The serial number entry exists so unmark that serial number
342 UPDATE mtl_serial_numbers
343 SET group_mark_id = -1
344 WHERE inventory_item_id = p_inventory_item_id
345 AND current_organization_id = p_organization_id
346 AND serial_number = p_serial_number;
347 ELSE
348 -- The serial number entry does not exist
349 x_return_code := 1;
350 END IF;
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 x_return_code := -1;
355
356 END remove_serial;
357
358
359 PROCEDURE mark_all_present
360 (p_organization_id IN NUMBER ,
361 p_subinventory IN VARCHAR2 ,
362 p_locator_id IN NUMBER := NULL ,
363 p_inventory_item_id IN NUMBER ,
364 p_revision IN VARCHAR2 := NULL ,
365 p_lot_number IN VARCHAR2 := NULL ,
366 p_cycle_count_header_id IN NUMBER ,
367 p_parent_lpn_id IN NUMBER := NULL)
368 IS
369
370 BEGIN
371 -- Multiple serial that are scheduled
372 UPDATE mtl_serial_numbers
373 SET group_mark_id = 1
374 WHERE inventory_item_id = p_inventory_item_id
375 AND current_organization_id = p_organization_id
376 AND serial_number IN
377 (SELECT UNIQUE msn.serial_number
378 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
379 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
380 WHERE msn.inventory_item_id = p_inventory_item_id
381 AND msn.current_organization_id = p_organization_id
382 AND msn.current_status IN (1, 3)
383 AND msn.status_id = mms.status_id(+)
384 AND NVL(msn.lot_number, '###' ) = NVL(p_lot_number, '###')
385 AND msn.serial_number = mcsn.serial_number
386 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
387 AND NVL(mcsn.unit_status_current,-1) <> 2 -- bug 13511103
388 AND mcce.cycle_count_header_id = p_cycle_count_header_id
389 AND mcce.inventory_item_id = p_inventory_item_id
390 AND mcce.organization_id = p_organization_id
391 AND mcce.subinventory = p_subinventory
392 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
393 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
394 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
395 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
396 AND mcce.entry_status_code IN (1, 3)
397 AND NVL(mcce.export_flag, 2) = 2
398 -- added for bug 13691739 starts
399 AND NOT EXISTS (SELECT 1
400 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
401 mtl_material_transactions_temp mmtt
402 WHERE mmtt.inventory_item_id = p_inventory_item_id
403 AND mmtt.organization_id = p_organization_id
404 AND mmtt.wms_task_status = 4
405 AND mmtt.wms_task_type <> 2
406 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
407 AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
408 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
409 -- added for bug 13691739 ends
410 );
411
412 END mark_all_present;
413
414 -- Bug 13481846
415 PROCEDURE mark_present_serials
416 (p_organization_id IN NUMBER ,
417 p_subinventory IN VARCHAR2 ,
418 p_locator_id IN NUMBER := NULL ,
419 p_inventory_item_id IN NUMBER ,
420 p_revision IN VARCHAR2 := NULL ,
421 p_lot_number IN VARCHAR2 := NULL ,
422 p_cycle_count_header_id IN NUMBER ,
423 p_parent_lpn_id IN NUMBER := NULL)
424 IS
425 BEGIN
426 -- This procedure is invoked for a Cycle Count with "Multiple Per Request"
427 -- Count and "Quantity Only" Detail
428
429 print_debug('Inside PROCEDURE mark_present_serials');
430
431 -- Difference between this procedure and mark_all_present procedure defined above
432 -- Here we are Updating group_mark_id to 1 for only those serials which are
433 -- in the same sub/loc (based on columns in MTL_SERIAL_NUMBERS table) as the sub/loc
434 -- of the cycle count entry (based on column values in MTL_CYCLE_COUNT_ENTRIES table)
435
436 UPDATE mtl_serial_numbers
437 SET group_mark_id = 1
438 WHERE inventory_item_id = p_inventory_item_id
439 AND current_organization_id = p_organization_id
440 AND serial_number IN
441 (SELECT UNIQUE msn.serial_number
442 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
443 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
444 WHERE msn.inventory_item_id = p_inventory_item_id
445 AND msn.current_organization_id = p_organization_id
446 AND msn.current_status IN (1, 3)
447 AND msn.status_id = mms.status_id(+)
448 AND NVL(msn.lot_number, '###' ) = NVL(p_lot_number, '###')
449 AND msn.serial_number = mcsn.serial_number
450 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
451 AND NVL(mcsn.unit_status_current,-1) <> 2
452 AND mcce.cycle_count_header_id = p_cycle_count_header_id
453 AND mcce.inventory_item_id = p_inventory_item_id
454 AND mcce.subinventory = msn.current_subinventory_code -- Bug 13481846 addition
455 AND NVL(mcce.locator_id, -99999) = NVL(msn.current_locator_id, -99999) -- Bug 13481846 addition
456 AND mcce.organization_id = p_organization_id
457 AND mcce.subinventory = p_subinventory
458 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
459 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
460 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
461 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
462 AND mcce.entry_status_code IN (1, 3)
463 AND NVL(mcce.export_flag, 2) = 2
464 AND NOT EXISTS (SELECT 1
465 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
466 mtl_material_transactions_temp mmtt
467 WHERE mmtt.inventory_item_id = p_inventory_item_id
468 AND mmtt.organization_id = p_organization_id
469 AND mmtt.wms_task_status = 4
470 AND mmtt.wms_task_type <> 2
471 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
472 AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
473 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
474 )
475 );
476
477 print_debug('Number of rows updated in MTL_SERIAL_NUMBERS: ' || SQL%ROWCOUNT);
478
479 END mark_present_serials;
480
481
482 PROCEDURE get_serial_entry_number
483 (p_organization_id IN NUMBER ,
484 p_subinventory IN VARCHAR2 ,
485 p_locator_id IN NUMBER := NULL ,
486 p_inventory_item_id IN NUMBER ,
487 p_revision IN VARCHAR2 := NULL ,
488 p_lot_number IN VARCHAR2 := NULL ,
489 p_cycle_count_header_id IN NUMBER ,
490 p_parent_lpn_id IN NUMBER := NULL ,
491 x_number OUT NOCOPY NUMBER)
492 IS
493
494 BEGIN
495 SELECT COUNT(*)
496 INTO x_number
497 FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
498 mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
499 WHERE msn.inventory_item_id = p_inventory_item_id
500 AND msn.group_mark_id = 1
501 AND msn.current_organization_id = p_organization_id
502 AND msn.current_status IN (1, 3)
503 AND msn.status_id = mms.status_id(+)
504 AND msn.serial_number = mcsn.serial_number
505 AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
506 AND mcce.cycle_count_header_id = p_cycle_count_header_id
507 AND mcce.inventory_item_id = p_inventory_item_id
508 AND mcce.organization_id = p_organization_id
509 AND mcce.subinventory = p_subinventory
510 AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
511 AND NVL(mcce.revision, '@@@@@') = NVL(p_revision, '@@@@@')
512 AND NVL(mcce.lot_number, '###' ) = NVL(p_lot_number, '###')
513 AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
514 AND mcce.entry_status_code IN (1, 3)
515 AND NVL(mcce.export_flag, 2) = 2
516 -- added for bug 13691739 starts
517 AND NOT EXISTS (SELECT 1
518 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
519 mtl_material_transactions_temp mmtt
520 WHERE mmtt.inventory_item_id = p_inventory_item_id
521 AND mmtt.organization_id = p_organization_id
522 AND mmtt.wms_task_status = 4
523 AND mmtt.wms_task_type <> 2
524 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
525 AND msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
526 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number);
527 -- added for bug 13691739 ends
528
529 END get_serial_entry_number;
530
531 FUNCTION exist_Serial_number(p_serial_number IN VARCHAR2 ,p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER)
532 RETURN BOOLEAN IS
533 l_temp_buf VARCHAR2(10);
534 BEGIN
535 SELECT 'exists'
536 INTO l_temp_buf
537 FROM mtl_Serial_numbers
538 WHERE serial_number = p_serial_number
539 AND inventory_item_id = p_inventory_item_id
540 AND current_organization_id = p_organization_id;
541
542 --serial number exists...return true
543 RETURN TRUE;
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 RETURN FALSE;
547
548 END exist_Serial_number;
549
550 /* Bug# 3595723 */
551 /* Inserts the serial number into mtl_cc_Serial_numbers if its new, else updates the same */
552 PROCEDURE insert_serial_number
553 (p_serial_number IN VARCHAR2 ,
554 p_cycle_count_header_id IN NUMBER,
555 p_organization_id IN NUMBER ,
556 p_subinventory IN VARCHAR2 ,
557 p_locator_id IN NUMBER := NULL ,
558 p_inventory_item_id IN NUMBER ,
559 p_revision IN VARCHAR2 := NULL ,
560 p_lot_number IN VARCHAR2 := NULL ,
561 p_parent_lpn_id IN NUMBER := NULL
562 -- Adding for bug#9959346
563 , p_serial_attribute_category IN VARCHAR2 := NULL
564 , p_orgination_date IN DATE := NULL
565 , p_c_attribute1 IN VARCHAR2 := NULL
566 , p_c_attribute2 IN VARCHAR2 := NULL
567 , p_c_attribute3 IN VARCHAR2 := NULL
568 , p_c_attribute4 IN VARCHAR2 := NULL
569 , p_c_attribute5 IN VARCHAR2 := NULL
570 , p_c_attribute6 IN VARCHAR2 := NULL
571 , p_c_attribute7 IN VARCHAR2 := NULL
572 , p_c_attribute8 IN VARCHAR2 := NULL
573 , p_c_attribute9 IN VARCHAR2 := NULL
574 , p_c_attribute10 IN VARCHAR2 := NULL
575 , p_c_attribute11 IN VARCHAR2 := NULL
576 , p_c_attribute12 IN VARCHAR2 := NULL
577 , p_c_attribute13 IN VARCHAR2 := NULL
578 , p_c_attribute14 IN VARCHAR2 := NULL
579 , p_c_attribute15 IN VARCHAR2 := NULL
580 , p_c_attribute16 IN VARCHAR2 := NULL
581 , p_c_attribute17 IN VARCHAR2 := NULL
582 , p_c_attribute18 IN VARCHAR2 := NULL
583 , p_c_attribute19 IN VARCHAR2 := NULL
584 , p_c_attribute20 IN VARCHAR2 := NULL
585 , p_d_attribute1 IN DATE := NULL
586 , p_d_attribute2 IN DATE := NULL
587 , p_d_attribute3 IN DATE := NULL
588 , p_d_attribute4 IN DATE := NULL
589 , p_d_attribute5 IN DATE := NULL
590 , p_d_attribute6 IN DATE := NULL
591 , p_d_attribute7 IN DATE := NULL
592 , p_d_attribute8 IN DATE := NULL
593 , p_d_attribute9 IN DATE := NULL
594 , p_d_attribute10 IN DATE := NULL
595 , p_n_attribute1 IN NUMBER := NULL
596 , p_n_attribute2 IN NUMBER := NULL
597 , p_n_attribute3 IN NUMBER := NULL
598 , p_n_attribute4 IN NUMBER := NULL
599 , p_n_attribute5 IN NUMBER := NULL
600 , p_n_attribute6 IN NUMBER := NULL
601 , p_n_attribute7 IN NUMBER := NULL
602 , p_n_attribute8 IN NUMBER := NULL
603 , p_n_attribute9 IN NUMBER := NULL
604 , p_n_attribute10 IN NUMBER := NULL
605 , p_territory_code IN VARCHAR2 := NULL
606 , p_time_since_new IN NUMBER := NULL
607 , p_cycles_since_new IN NUMBER := NULL
608 , p_time_since_overhaul IN NUMBER := NULL
609 , p_cycles_since_overhaul IN NUMBER := NULL
610 , p_time_since_repair IN NUMBER := NULL
611 , p_cycles_since_repair IN NUMBER := NULL
612 , p_time_since_visit IN NUMBER := NULL
613 , p_cycles_since_visit IN NUMBER := NULL
614 , p_time_since_mark IN NUMBER := NULL
615 , p_cycles_since_mark IN NUMBER := NULL
616 , p_number_of_repairs IN NUMBER := NULL
617 , p_attribute_category IN VARCHAR2 := NULL
618 , p_attribute1 IN VARCHAR2 := NULL
619 , p_attribute2 IN VARCHAR2 := NULL
620 , p_attribute3 IN VARCHAR2 := NULL
621 , p_attribute4 IN VARCHAR2 := NULL
622 , p_attribute5 IN VARCHAR2 := NULL
623 , p_attribute6 IN VARCHAR2 := NULL
624 , p_attribute7 IN VARCHAR2 := NULL
625 , p_attribute8 IN VARCHAR2 := NULL
626 , p_attribute9 IN VARCHAR2 := NULL
627 , p_attribute10 IN VARCHAR2 := NULL
628 , p_attribute11 IN VARCHAR2 := NULL
629 , p_attribute12 IN VARCHAR2 := NULL
630 , p_attribute13 IN VARCHAR2 := NULL
631 , p_attribute14 IN VARCHAR2 := NULL
632 , p_attribute15 IN VARCHAR2 := NULL
633 ) IS
634
635 l_number_of_counts NUMBER;
636 l_unit_status_current NUMBER;
637 l_unit_status_prior NUMBER;
638 l_unit_status_first NUMBER;
639 l_approval_condition NUMBER;
640 l_pos_adjustment_qty NUMBER;
641 l_neg_adjustment_qty NUMBER;
642 l_cycle_count_entry_id NUMBER;
643
644 BEGIN
645 print_debug('Call to insert_serial_number');
646 print_debug('p_serial_number ' || p_serial_number);
647 print_debug('p_cycle_count_header_id ' || p_cycle_count_header_id);
648 print_debug('p_organization_id ' || p_organization_id);
649 print_debug('p_subinventory ' || p_subinventory);
650 print_debug('p_locator_id ' || p_locator_id);
651 print_debug('p_revision ' || p_revision);
652 print_debug('p_lot_number ' || p_lot_number);
653 print_debug('p_parent_lpn_id ' || p_parent_lpn_id);
654
655 /*9452528-For LPN counts, we need not include sub and loc in query to find MCCE*/
656
657 IF (NVL(p_parent_lpn_id,0) = 0 ) THEN --No LPN passed
658 /* Get the cycle count entry id and approval_condition */
659 SELECT cycle_count_entry_id, approval_condition
660 INTO l_cycle_count_entry_id , l_approval_condition
661 FROM mtl_cycle_count_entries
662 WHERE cycle_count_header_id = p_cycle_count_header_id
663 AND entry_status_code IN (1,3)
664 AND inventory_item_id = p_inventory_item_id
665 AND organization_id = p_organization_id
666 AND subinventory = p_subinventory
667 AND nvl(locator_id,-999) = nvl(p_locator_id,-999)
668 AND nvl(lot_number,-999) = nvl(p_lot_number,-999)
669 AND nvl(revision,-999) = nvl(p_revision,-999)
670 AND nvl(parent_lpn_id,-999) = -999;
671 ELSE
672 SELECT cycle_count_entry_id, approval_condition
673 INTO l_cycle_count_entry_id , l_approval_condition
674 FROM mtl_cycle_count_entries
675 WHERE cycle_count_header_id = p_cycle_count_header_id
676 AND entry_status_code IN (1,3)
677 AND inventory_item_id = p_inventory_item_id
678 AND organization_id = p_organization_id
679 AND nvl(lot_number,-999) = nvl(p_lot_number,-999)
680 AND nvl(revision,-999) = nvl(p_revision,-999)
681 AND parent_lpn_id = p_parent_lpn_id ;
682 END IF;
683
684 print_debug('cycle_count_entry_id ' || l_cycle_count_entry_id);
685
686 /* Check if the serial number already exists */
687 SELECT number_of_counts, unit_status_current, unit_status_first
688 INTO l_number_of_counts, l_unit_status_prior, l_unit_status_first
689 FROM mtl_cc_serial_numbers
690 WHERE cycle_count_entry_id = l_cycle_count_entry_id
691 AND serial_number = p_serial_number;
692
693 print_debug('serial number ' || p_serial_number || ' already exists in mtl_cc_serial_numbers');
694 print_debug('l_number_of_counts ' || l_number_of_counts);
695 print_debug('l_unit_status_prior ' || l_unit_status_prior);
696 print_debug('l_unit_status_first ' || l_unit_status_first);
697
698 /* The serial number exists. Update the data */
699 IF (l_unit_status_prior = 2 ) THEN --9725018, this is for the case of recount.
700 l_pos_adjustment_qty := 1; --The serial was added in first count, so it is +ve adj
701 l_unit_status_current := l_unit_status_prior;
702 ELSE
703 l_unit_status_current := 1; -- 1 -> Present in the count location, 2 -> Absent
704 l_pos_adjustment_qty := 0; -- 1 -> New serial number found at the location
705 END IF;
706 l_neg_adjustment_qty := 0; -- 1 -> Serial number not found at the location
707
708 UPDATE MTL_CC_SERIAL_NUMBERS
709 SET
710 last_update_date = SYSDATE,
711 last_updated_by = fnd_global.user_id,
712 last_update_login = fnd_global.login_id,
713 -- number_of_counts = nvl(l_number_of_counts,0) + 1, commented for bug 9681558
714 unit_status_current = l_unit_status_current,
715 unit_status_prior = l_unit_status_prior,
716 unit_status_first = l_unit_status_first,
717 approval_condition = l_approval_condition,
718 pos_adjustment_qty = l_pos_adjustment_qty,
719 neg_adjustment_qty = l_neg_adjustment_qty
720 WHERE cycle_count_entry_id = l_cycle_count_entry_id
721 AND serial_number = p_serial_number;
722
723 EXCEPTION
724 WHEN NO_DATA_FOUND THEN
725 /* Serial number does not exist. Insert the serial number */
726 l_number_of_counts := NULL;
727 l_unit_status_current := NULL;
728 l_unit_status_prior := NULL;
729 l_unit_status_first := NULL;
730 l_pos_adjustment_qty := 1;
731 l_neg_adjustment_qty := NULL;
732
733 print_debug('could not find serial number ' || p_serial_number);
734
735 INSERT INTO MTL_CC_SERIAL_NUMBERS(
736 cycle_count_entry_id,
737 serial_number,
738 last_update_date,
739 last_updated_by,
740 creation_date,
741 created_by,
742 last_update_login,
743 number_of_counts,
744 unit_status_current,
745 unit_status_prior,
746 unit_status_first,
747 approval_condition,
748 pos_adjustment_qty,
749 neg_adjustment_qty
750 ) VALUES (
751 l_cycle_count_entry_id,
752 p_serial_number,
753 SYSDATE,
754 FND_GLOBAL.USER_ID,
755 SYSDATE,
756 FND_GLOBAL.USER_ID,
757 FND_GLOBAL.LOGIN_ID,
758 l_number_of_counts,
759 l_unit_status_current,
760 l_unit_status_prior,
761 l_unit_status_first,
762 l_approval_condition,
763 l_pos_adjustment_qty,
764 l_neg_adjustment_qty
765 );
766
767 IF(exist_Serial_number(p_serial_number,p_inventory_item_id,p_organization_id) = FALSE) THEN
768 print_Debug('Serial number doesnt exist in msn ');
769 --Insert into MSN in case the serial number doesn't exist there.
770 INSERT INTO MTL_SERIAL_NUMBERS (
771 inventory_item_id,
772 serial_number,
773 last_update_date,
774 last_updated_by,
775 creation_date,
776 created_by,
777 last_update_login,
778 initialization_date,
779 current_status,
780 revision,
781 lot_number,
782 current_subinventory_code,
783 current_locator_id,
784 current_organization_id,
785 last_txn_source_type_id,
786 last_receipt_issue_type,
787 last_txn_source_id,
788 gen_object_id
789 -- Adding for bug#9959346
790 , serial_attribute_category
791 , origination_date
792 , c_attribute1
793 , c_attribute2
794 , c_attribute3
795 , c_attribute4
796 , c_attribute5
797 , c_attribute6
798 , c_attribute7
799 , c_attribute8
800 , c_attribute9
801 , c_attribute10
802 , c_attribute11
803 , c_attribute12
804 , c_attribute13
805 , c_attribute14
806 , c_attribute15
807 , c_attribute16
808 , c_attribute17
809 , c_attribute18
810 , c_attribute19
811 , c_attribute20
812 , d_attribute1
813 , d_attribute2
814 , d_attribute3
815 , d_attribute4
816 , d_attribute5
817 , d_attribute6
818 , d_attribute7
819 , d_attribute8
820 , d_attribute9
821 , d_attribute10
822 , n_attribute1
823 , n_attribute2
824 , n_attribute3
825 , n_attribute4
826 , n_attribute5
827 , n_attribute6
828 , n_attribute7
829 , n_attribute8
830 , n_attribute9
831 , n_attribute10
832 , territory_code
833 , time_since_new
834 , cycles_since_new
835 , time_since_overhaul
836 , cycles_since_overhaul
837 , time_since_repair
838 , cycles_since_repair
839 , time_since_visit
840 , cycles_since_visit
841 , time_since_mark
842 , cycles_since_mark
843 , number_of_repairs
844 , attribute_category
845 , attribute1
846 , attribute2
847 , attribute3
848 , attribute4
849 , attribute5
850 , attribute6
851 , attribute7
852 , attribute8
853 , attribute9
854 , attribute10
855 , attribute11
856 , attribute12
857 , attribute13
858 , attribute14
859 , attribute15
860 ) VALUES (
861 p_inventory_item_id,
862 p_serial_number,
863 SYSDATE,
864 FND_GLOBAL.USER_ID,
865 SYSDATE,
866 FND_GLOBAL.USER_ID,
867 FND_GLOBAL.LOGIN_ID,
868 SYSDATE,
869 1,
870 p_revision,
871 p_lot_number,
872 p_subinventory,
873 p_locator_id,
874 p_organization_id,
875 9,
876 4,
877 l_cycle_count_entry_id,
878 mtl_gen_object_id_s.NEXTVAL,
879 -- Adding for bug#9959346
880 p_serial_attribute_category
881 , p_orgination_date
882 , p_c_attribute1
883 , p_c_attribute2
884 , p_c_attribute3
885 , p_c_attribute4
886 , p_c_attribute5
887 , p_c_attribute6
888 , p_c_attribute7
889 , p_c_attribute8
890 , p_c_attribute9
891 , p_c_attribute10
892 , p_c_attribute11
893 , p_c_attribute12
894 , p_c_attribute13
895 , p_c_attribute14
896 , p_c_attribute15
897 , p_c_attribute16
898 , p_c_attribute17
899 , p_c_attribute18
900 , p_c_attribute19
901 , p_c_attribute20
902 , p_d_attribute1
903 , p_d_attribute2
904 , p_d_attribute3
905 , p_d_attribute4
906 , p_d_attribute5
907 , p_d_attribute6
908 , p_d_attribute7
909 , p_d_attribute8
910 , p_d_attribute9
911 , p_d_attribute10
912 , p_n_attribute1
913 , p_n_attribute2
914 , p_n_attribute3
915 , p_n_attribute4
916 , p_n_attribute5
917 , p_n_attribute6
918 , p_n_attribute7
919 , p_n_attribute8
920 , p_n_attribute9
921 , p_n_attribute10
922 , p_territory_code
923 , p_time_since_new
924 , p_cycles_since_new
925 , p_time_since_overhaul
926 , p_cycles_since_overhaul
927 , p_time_since_repair
928 , p_cycles_since_repair
929 , p_time_since_visit
930 , p_cycles_since_visit
931 , p_time_since_mark
932 , p_cycles_since_mark
933 , p_number_of_repairs
934 , p_attribute_category
935 , p_attribute1
936 , p_attribute2
937 , p_attribute3
938 , p_attribute4
939 , p_attribute5
940 , p_attribute6
941 , p_attribute7
942 , p_attribute8
943 , p_attribute9
944 , p_attribute10
945 , p_attribute11
946 , p_attribute12
947 , p_attribute13
948 , p_attribute14
949 , p_attribute15);
950 END IF;
951 WHEN OTHERS THEN
952 print_debug('other exceptions');
953 NULL;
954 END insert_Serial_number;
955
956 /*Bug # 3646068
957 Remove the serial numbers from mtl_cc_Serial_numbers and unmark the serials in mtl_serial_numbers
958 for serials that have been entered till now */
959 PROCEDURE remove_serial_number
960 (p_cycle_count_header_id IN NUMBER,
961 p_organization_id IN NUMBER ,
962 p_subinventory IN VARCHAR2 ,
963 p_locator_id IN NUMBER := NULL ,
964 p_inventory_item_id IN NUMBER ,
965 p_revision IN VARCHAR2 := NULL ,
966 p_lot_number IN VARCHAR2 := NULL ,
967 p_parent_lpn_id IN NUMBER := NULL
968 ) IS
969 l_cycle_count_entry_id NUMBER;
970 l_serial_number VARCHAR2(30);
971 l_pos_adjustment_qty NUMBER;
972
973 CURSOR serial_cur IS
974 SELECT serial_number,
975 pos_adjustment_qty
976 FROM mtl_cc_Serial_numbers
977 WHERE cycle_count_entry_id = l_cycle_count_entry_id;
978
979 BEGIN
980
981 /* Get the cycle count entry id */
982 SELECT cycle_count_entry_id
983 INTO l_cycle_count_entry_id
984 FROM mtl_cycle_count_entries
985 WHERE cycle_count_header_id = p_cycle_count_header_id
986 AND entry_status_code IN (1,3)
987 AND inventory_item_id = p_inventory_item_id
988 AND organization_id = p_organization_id
989 AND subinventory = p_subinventory
990 AND nvl(locator_id,-999) = nvl(p_locator_id,-999)
991 AND nvl(lot_number,-999) = nvl(p_lot_number,-999)
992 AND nvl(revision,-999) = nvl(p_revision,-999)
993 AND nvl(parent_lpn_id,-999) = nvl(p_parent_lpn_id,-999);
994
995 OPEN serial_cur;
996 LOOP
997 FETCH serial_cur INTO l_serial_number, l_pos_adjustment_qty;
998 EXIT WHEN serial_cur%NOTFOUND;
999 IF(nvl(l_pos_adjustment_qty,0) = 1) THEN
1000 --This was inserted as part of call to insert_serial_number, Delete from mtl_cc_Serial_numbers
1001 BEGIN
1002 DELETE FROM mtl_cc_serial_numbers
1003 WHERE cycle_count_entry_id = l_cycle_count_entry_id
1004 AND serial_number = l_serial_number;
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 print_debug('Exception while trying to delete from mtl_cc_serial_numbers for serial ' || l_serial_number);
1008 END;
1009 END IF;
1010
1011 --Unmark the serial number in mtl_Serial_numbers
1012 BEGIN
1013
1014 UPDATE mtl_serial_numbers
1015 SET group_mark_id = NULL
1016 WHERE serial_number = l_serial_number
1017 AND inventory_item_id = p_inventory_item_id
1018 AND current_organization_id = p_organization_id;
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 print_debug('Exception while updating MSN for serial ' || l_serial_number);
1022 END;
1023 END LOOP;
1024 END remove_Serial_number;
1025
1026
1027
1028 END INV_CYC_SERIALS;