1 PACKAGE BODY INV_PHY_INV_LOVS AS
2 /* $Header: INVPINLB.pls 120.7.12010000.2 2008/07/29 13:42:44 ptkumar ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_PHY_INV_LOVS';
6
7 PROCEDURE print_debug(p_err_msg VARCHAR2)
8 IS
9 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11
12 IF (l_debug = 1) THEN
13 inv_mobile_helper_functions.tracelog
14 (p_err_msg => p_err_msg,
15 p_module => 'INV_PHY_INV_LOVS',
16 p_level => 4);
17 END IF;
18
19 -- dbms_output.put_line(p_err_msg);
20 END print_debug;
21
22
23 -- Name: GET_PHY_INV_LOV
24 --
25 -- Input parameters:
26 -- p_lpn which restricts LOV SQL to the user input text
27 -- p_organization_id Organization ID
28 --
29 -- Output parameters:
30 -- x_phy_inv_lov returns LOV rows as reference cursor
31 --
32 -- Functions: This API returns valid physical inventories
33 --
34 PROCEDURE get_phy_inv_lov
35 (x_phy_inv_lov OUT NOCOPY t_genref,
36 p_phy_inv IN VARCHAR2,
37 p_organization_id IN NUMBER)
38
39 IS
40
41 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
42 BEGIN
43 OPEN x_phy_inv_lov FOR
44 SELECT physical_inventory_name,
45 physical_inventory_id,
46 description,
47 freeze_date,
48 adjustments_posted,
49 approval_required,
50 cost_variance_neg,
51 cost_variance_pos,
52 approval_tolerance_neg,
53 approval_tolerance_pos,
54 all_subinventories_flag,
55 dynamic_tag_entry_flag
56 FROM mtl_physical_inventories_v
57 WHERE organization_id = p_organization_id
58 AND snapshot_complete = 1
59 AND adjustments_posted <> 1
60 AND physical_inventory_name LIKE (p_phy_inv)
61 ORDER BY physical_inventory_name;
62 END get_phy_inv_lov;
63
64
65 -- Name: GET_SERIAL_COUNT_NUMBER
66 --
67 -- Input parameters:
68 -- p_physical_inventory_id Physical Inventory ID
69 -- p_organization_id Organization ID
70 -- p_serial_number Serial Number
71 -- p_inventory_item_id Inventory Item ID
72 --
73 -- Output parameters:
74 -- x_number Returns the serial count for the number
75 -- of physical tags with that particular
76 -- serial number that has already been counted
77 -- as present and existing in a given location.
78 -- x_serial_in_scope Returns 1 if the serial is within the scope
79 -- of the physical inventory. Otherwise it will
80 -- return 0.
81 --
82 -- Functions: This API returns the count of physical tag records
83 -- for the given serial number inputted.
84 -- It has also been overloaded so that it will also
85 -- check if the inputted serial is within the scope
86 -- of the physical inventory, i.e. exists in a subinventory
87 -- for which the physical inventory covers
88 --
89 PROCEDURE get_serial_count_number
90 (p_physical_inventory_id IN NUMBER ,
91 p_organization_id IN NUMBER ,
92 p_serial_number IN VARCHAR2 ,
93 p_inventory_item_id IN NUMBER ,
94 x_number OUT NOCOPY NUMBER ,
95 x_serial_in_scope OUT NOCOPY NUMBER)
96 IS
97 l_all_sub_flag NUMBER;
98 l_serial_sub VARCHAR2(10);
99
100 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
101 BEGIN
102 IF (l_debug = 1) THEN
103 print_debug('***Calling get_serial_count_number***');
104 END IF;
105 -- First get the serial count number to see if the serial has
106 -- already been found for this physical inventory
107 SELECT COUNT(*)
108 INTO x_number
109 FROM mtl_physical_inventory_tags
110 WHERE physical_inventory_id = p_physical_inventory_id
111 AND organization_id = p_organization_id
112 AND serial_num = p_serial_number
113 AND inventory_item_id = p_inventory_item_id
114 AND tag_quantity IS NOT NULL
115 AND tag_quantity <> 0
116 AND void_flag = 2
117 AND adjustment_id IN
118 (SELECT adjustment_id
119 FROM mtl_physical_adjustments
120 WHERE physical_inventory_id = p_physical_inventory_id
121 AND organization_id = p_organization_id
122 AND approval_status IS NULL);
123
124 -- Now see if the serial is within the scope of the physical
125 -- inventory
126 SELECT all_subinventories_flag
127 INTO l_all_sub_flag
128 FROM mtl_physical_inventories
129 WHERE physical_inventory_id = p_physical_inventory_id
130 AND organization_id = p_organization_id;
131 IF (l_debug = 1) THEN
132 print_debug('All subinventories flag: ' || l_all_sub_flag);
133 END IF;
134
135 IF (l_all_sub_flag = 1) THEN
136 -- All subinventories are included for this physical inventory
137 -- so the serial should be within the scope
138 x_serial_in_scope := 1;
139 ELSE
140 -- Get the current sub where the serial resides according to the system
141 SELECT NVL(current_subinventory_code, '@@@@@')
142 INTO l_serial_sub
143 FROM mtl_serial_numbers
144 WHERE inventory_item_id = p_inventory_item_id
145 AND serial_number = p_serial_number
146 AND current_organization_id = p_organization_id;
147 IF (l_debug = 1) THEN
148 print_debug('Current subinventory of serial: ' || l_serial_sub);
149 END IF;
150 -- See if the serial's subinventory is one of the subinventories
151 -- associated with the physical inventory
152 SELECT COUNT(*)
153 INTO x_serial_in_scope
154 FROM mtl_physical_subinventories
155 WHERE organization_id = p_organization_id
156 AND physical_inventory_id = p_physical_inventory_id
157 AND subinventory = l_serial_sub;
158 END IF;
159 IF (l_debug = 1) THEN
160 print_debug('Serial count number: ' || x_number);
161 print_debug('Serial in scope: ' || x_serial_in_scope);
162 END IF;
163
164 END get_serial_count_number;
165
166
167 PROCEDURE process_tag
168 (p_physical_inventory_id IN NUMBER,
169 p_organization_id IN NUMBER,
170 p_subinventory IN VARCHAR2,
171 p_locator_id IN NUMBER := NULL,
172 p_parent_lpn_id IN NUMBER := NULL,
173 p_inventory_item_id IN NUMBER,
174 p_revision IN VARCHAR2 := NULL,
175 p_lot_number IN VARCHAR2 := NULL,
176 p_from_serial_number IN VARCHAR2 := NULL,
177 p_to_serial_number IN VARCHAR2 := NULL,
178 p_tag_quantity IN NUMBER,
179 p_tag_uom IN VARCHAR2,
180 p_dynamic_tag_entry_flag IN NUMBER,
181 p_user_id IN NUMBER,
182 p_cost_group_id IN NUMBER := NULL
183 --INVCONV, NSRIVAST, START
184 ,p_tag_sec_uom IN VARCHAR2 := NULL
185 ,p_tag_sec_quantity IN NUMBER := NULL
186 --INVCONV, NSRIVAST, END
187 )
188 IS
189 l_current_serial VARCHAR2(30);
190 CURSOR tag_entry IS
191 SELECT *
192 FROM mtl_physical_inventory_tags
193 WHERE physical_inventory_id = p_physical_inventory_id
194 AND organization_id = p_organization_id
195 AND subinventory = p_subinventory
196 AND NVL(locator_id, -99999) = NVL(p_locator_id, -99999)
197 AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
198 AND inventory_item_id = p_inventory_item_id
199 AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
200 AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
201 AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
202 -- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
203 AND void_flag = 2
204 AND adjustment_id IN
205 (SELECT adjustment_id
206 FROM mtl_physical_adjustments
207 WHERE physical_inventory_id = p_physical_inventory_id
208 AND organization_id = p_organization_id
209 AND approval_status IS NULL);
210 CURSOR discrepant_serial_cursor IS
211 SELECT *
212 FROM mtl_physical_inventory_tags
213 WHERE physical_inventory_id = p_physical_inventory_id
214 AND organization_id = p_organization_id
215 AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
216 AND inventory_item_id = p_inventory_item_id
217 AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
218 AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
219 AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
220 -- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
221 AND void_flag = 2
222 AND adjustment_id IN
223 (SELECT adjustment_id
224 FROM mtl_physical_adjustments
225 WHERE physical_inventory_id = p_physical_inventory_id
226 AND organization_id = p_organization_id
227 AND approval_status IS NULL);
228 tag_record MTL_PHYSICAL_INVENTORY_TAGS%ROWTYPE;
229 l_prefix VARCHAR2(30);
230 l_quantity NUMBER;
231 l_from_number NUMBER;
232 l_to_number NUMBER;
233 l_errorcode NUMBER;
234 l_length NUMBER;
235 l_padded_length NUMBER;
236 l_current_number NUMBER;
237 l_adjustment_id NUMBER;
238 l_cost_group_id NUMBER;
239
240 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
241 BEGIN
242 IF (l_debug = 1) THEN
243 print_debug('***Calling process_tag with the following parameters***');
244 print_debug('p_physical_inventory_id: ---> ' || p_physical_inventory_id);
245 print_debug('p_organization_id: ---------> ' || p_organization_id);
246 print_debug('p_subinventory: ------------> ' || p_subinventory);
247 print_debug('p_locator_id: --------------> ' || p_locator_id);
248 print_debug('p_parent_lpn_id: -----------> ' || p_parent_lpn_id);
249 print_debug('p_inventory_item_id: -------> ' || p_inventory_item_id);
250 print_debug('p_revision: ----------------> ' || p_revision);
251 print_debug('p_lot_number: --------------> ' || p_lot_number);
252 print_debug('p_from_serial_number: ------> ' || p_from_serial_number);
253 print_debug('p_to_serial_number: --------> ' || p_to_serial_number);
254 print_debug('p_tag_quantity: ------------> ' || p_tag_quantity);
255 print_debug('p_tag_uom: -----------------> ' || p_tag_uom);
256 print_debug('p_dynamic_tag_entry_flag: --> ' || p_dynamic_tag_entry_flag);
257 print_debug('p_user_id: -----------------> ' || p_user_id);
258 print_debug('p_cost_group_id: -----------> ' || p_cost_group_id);
259 END IF;
260
261 -- First check if the tag item is a serial controlled item
262 IF ((p_from_serial_number IS NOT NULL) AND
263 (p_to_serial_number IS NOT NULL)) THEN
264 IF (l_debug = 1) THEN
265 print_debug('Serial controlled item');
266 END IF;
267
268 -- Call this API to parse the serial numbers into prefixes and numbers.
269 -- Only call this procedure if the from and to serial numbers differ
270 IF (p_from_serial_number <> p_to_serial_number) THEN
271 IF (NOT MTL_Serial_Check.inv_serial_info
272 ( p_from_serial_number => p_from_serial_number,
273 p_to_serial_number => p_to_serial_number,
274 x_prefix => l_prefix,
275 x_quantity => l_quantity,
276 x_from_number => l_from_number,
277 x_to_number => l_to_number,
278 x_errorcode => l_errorcode)) THEN
279 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SER');
280 FND_MSG_PUB.ADD;
281 RAISE FND_API.G_EXC_ERROR;
282 END IF;
283 END IF;
284
285 -- Check that in the case of a range of serial numbers, that the
286 -- inputted p_tag_quantity equals the amount of items in the serial
287 -- range. Do this check only if a range of serials is submitted
288 IF (p_from_serial_number <> p_to_serial_number) THEN
289 IF (p_tag_quantity <> l_quantity) THEN
290 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_X_QTY');
291 FND_MSG_PUB.ADD;
292 RAISE FND_API.G_EXC_ERROR;
293 END IF;
294 END IF;
295
296 -- Get the serial number length.
297 -- Note that the from and to serial numbers must be of the same length.
298 l_length := length(p_from_serial_number);
299
300 -- Initialize the current pointer variables
301 l_current_serial := p_from_serial_number;
302 IF (l_from_number IS NOT NULL) THEN
303 l_current_number := l_from_number;
304 ELSE
305 l_current_number := 0;
306 END IF;
307
308 LOOP
309 -- For each serial number check if a tag entry for it already
310 -- exists or not
311 OPEN tag_entry;
312 FETCH tag_entry INTO tag_record;
313 IF (tag_entry%FOUND) THEN
314 -- Entry already exists so update the row
315 -- Check if an adjustment ID for this tag already exists or not
316 IF (tag_record.adjustment_id IS NULL) THEN
317 find_existing_adjustment
318 ( p_physical_inventory_id => p_physical_inventory_id,
319 p_organization_id => p_organization_id,
320 p_subinventory => p_subinventory,
321 p_locator_id => p_locator_id,
322 p_parent_lpn_id => p_parent_lpn_id,
323 p_inventory_item_id => p_inventory_item_id,
324 p_revision => p_revision,
325 p_lot_number => p_lot_number,
326 p_serial_number => l_current_serial,
327 p_user_id => p_user_id,
328 p_cost_group_id => tag_record.cost_group_id,
329 x_adjustment_id => l_adjustment_id
330 );
331 ELSE
332 l_adjustment_id := tag_record.adjustment_id;
333 END IF;
334 update_row( p_tag_id => tag_record.tag_id,
335 p_physical_inventory_id => p_physical_inventory_id,
336 p_organization_id => p_organization_id,
337 p_subinventory => p_subinventory,
338 p_locator_id => p_locator_id,
339 p_parent_lpn_id => p_parent_lpn_id,
340 p_inventory_item_id => p_inventory_item_id,
341 p_revision => p_revision,
342 p_lot_number => p_lot_number,
343 p_serial_number => l_current_serial,
344 p_tag_quantity => p_tag_quantity,
345 p_tag_uom => p_tag_uom,
346 p_user_id => p_user_id,
347 p_cost_group_id => tag_record.cost_group_id,
348 p_adjustment_id => l_adjustment_id
349 );
350 update_adjustment
351 (p_adjustment_id => l_adjustment_id,
352 p_physical_inventory_id => p_physical_inventory_id,
353 p_organization_id => p_organization_id,
354 p_user_id => p_user_id
355 );
356 ELSE
357 -- Entry does not exist so insert the row
358 IF (p_dynamic_tag_entry_flag = 1) THEN
359 IF (l_debug = 1) THEN
360 print_debug('Dynamic serial tag entry to be inserted');
361 END IF;
362 -- Dynamic tag entries are allowed
363
364 -- First check to see if a tag exists for this serial number
365 -- already if the serial is found in a discrepant location.
366 -- If a tag already exists, we want to update the tag and
367 -- adjustment record to signify that we have counted it
368 -- already, although it is considered as missing.
369 OPEN discrepant_serial_cursor;
370 FETCH discrepant_serial_cursor INTO tag_record;
371 IF (discrepant_serial_cursor%FOUND) THEN
372 -- Entry for discrepant serial exists so update the row
373 -- Check if an adjustment ID for this tag already exists or not
374 IF (l_debug = 1) THEN
375 print_debug('Discrepant serial so updating the original tag');
376 END IF;
377 IF (tag_record.adjustment_id IS NULL) THEN
378 find_existing_adjustment
379 ( p_physical_inventory_id => p_physical_inventory_id,
380 p_organization_id => p_organization_id,
381 p_subinventory => tag_record.subinventory,
382 p_locator_id => tag_record.locator_id,
383 p_parent_lpn_id => p_parent_lpn_id,
384 p_inventory_item_id => p_inventory_item_id,
385 p_revision => p_revision,
386 p_lot_number => p_lot_number,
387 p_serial_number => l_current_serial,
388 p_user_id => p_user_id,
389 p_cost_group_id => tag_record.cost_group_id,
390 x_adjustment_id => l_adjustment_id
391 );
392 ELSE
393 l_adjustment_id := tag_record.adjustment_id;
394 END IF;
395 update_row( p_tag_id => tag_record.tag_id,
396 p_physical_inventory_id => p_physical_inventory_id,
397 p_organization_id => p_organization_id,
398 p_subinventory => tag_record.subinventory,
399 p_locator_id => tag_record.locator_id,
400 p_parent_lpn_id => p_parent_lpn_id,
401 p_inventory_item_id => p_inventory_item_id,
402 p_revision => p_revision,
403 p_lot_number => p_lot_number,
404 p_serial_number => l_current_serial,
405 p_tag_quantity => 0,
406 p_tag_uom => p_tag_uom,
407 p_user_id => p_user_id,
408 p_cost_group_id => tag_record.cost_group_id,
409 p_adjustment_id => l_adjustment_id
410 );
411 update_adjustment
412 (p_adjustment_id => l_adjustment_id,
413 p_physical_inventory_id => p_physical_inventory_id,
414 p_organization_id => p_organization_id,
415 p_user_id => p_user_id
416 );
417 END IF;
418 CLOSE discrepant_serial_cursor;
419
420 -- Now deal with inserting the new dynamic tag entry
421 -- Get the cost group ID for this entry
422 inv_cyc_lovs.get_cost_group_id
423 (p_organization_id => p_organization_id,
424 p_subinventory => p_subinventory,
425 p_locator_id => p_locator_id,
426 p_parent_lpn_id => p_parent_lpn_id,
427 p_inventory_item_id => p_inventory_item_id,
428 p_revision => p_revision,
429 p_lot_number => p_lot_number,
430 p_serial_number => l_current_serial,
431 x_out => l_cost_group_id);
432 -- Bug# 2607187
433 -- Do not get the default cost group ID. If the item is
434 -- new and does not exist in onhand, pass a NULL value
435 -- for the cost group ID. The transaction manager will
436 -- call the cost group rules engine for that if the
437 -- cost group ID passed into MMTT is null.
438 IF (l_cost_group_id = -999) THEN
439 l_cost_group_id := NULL;
440 END IF;
441 -- Get the default cost group ID based on the given org
442 -- and sub if cost group ID was not retrieved successfully
443 /*IF (l_cost_group_id = -999) THEN
444 inv_cyc_lovs.get_default_cost_group_id
445 (p_organization_id => p_organization_id,
446 p_subinventory => p_subinventory,
447 x_out => l_cost_group_id);
448 END IF;
449 -- Default the cost group ID to 1 if nothing can be found
450 IF (l_cost_group_id = -999) THEN
451 l_cost_group_id := 1;
452 END IF;*/
453
454 -- Generate a new adjustment ID for this tag
455 find_existing_adjustment
456 ( p_physical_inventory_id => p_physical_inventory_id,
457 p_organization_id => p_organization_id,
458 p_subinventory => p_subinventory,
459 p_locator_id => p_locator_id,
460 p_parent_lpn_id => p_parent_lpn_id,
461 p_inventory_item_id => p_inventory_item_id,
462 p_revision => p_revision,
463 p_lot_number => p_lot_number,
464 p_serial_number => l_current_serial,
465 p_user_id => p_user_id,
466 p_cost_group_id => l_cost_group_id,
467 x_adjustment_id => l_adjustment_id
468 );
469 insert_row( p_physical_inventory_id => p_physical_inventory_id,
470 p_organization_id => p_organization_id,
471 p_subinventory => p_subinventory,
472 p_locator_id => p_locator_id,
473 p_parent_lpn_id => p_parent_lpn_id,
474 p_inventory_item_id => p_inventory_item_id,
475 p_revision => p_revision,
476 p_lot_number => p_lot_number,
477 p_serial_number => l_current_serial,
478 p_tag_quantity => p_tag_quantity,
479 p_tag_uom => p_tag_uom,
480 p_user_id => p_user_id,
481 p_cost_group_id => l_cost_group_id,
482 p_adjustment_id => l_adjustment_id
483 );
484 update_adjustment
485 (p_adjustment_id => l_adjustment_id,
486 p_physical_inventory_id => p_physical_inventory_id,
487 p_organization_id => p_organization_id,
488 p_user_id => p_user_id
489 );
490 ELSE
491 -- Dynamic tag entries are not allowed
492 -- This shouldn't happen if the mobile form's LOV
493 -- statements are correctly set
494 FND_MESSAGE.SET_NAME('INV','INV_NO_DYNAMIC_TAGS');
495 FND_MSG_PUB.ADD;
496 --RAISE FND_API.G_EXC_ERROR;
497 END IF;
498 END IF;
499 CLOSE tag_entry;
500
501 EXIT WHEN l_current_serial = p_to_serial_number;
502 -- Increment the current serial number if serial range inputted
503 IF (p_from_serial_number <> p_to_serial_number) THEN
504 l_current_number := l_current_number + 1;
505 l_padded_length := l_length - length(l_current_number);
506 l_current_serial := RPAD(l_prefix, l_padded_length, '0') ||
507 l_current_number;
508 END IF;
509 END LOOP;
510
511 ELSE -- Item is not serial controlled
512 IF (l_debug = 1) THEN
513 print_debug('Non-Serial controlled item');
514 END IF;
515 OPEN tag_entry;
516 FETCH tag_entry INTO tag_record;
517 IF (tag_entry%FOUND) THEN
518 -- Check if an adjustment ID for this tag already exists or not
519 IF (tag_record.adjustment_id IS NULL) THEN
520 find_existing_adjustment
521 ( p_physical_inventory_id => p_physical_inventory_id,
522 p_organization_id => p_organization_id,
523 p_subinventory => p_subinventory,
524 p_locator_id => p_locator_id,
525 p_parent_lpn_id => p_parent_lpn_id,
526 p_inventory_item_id => p_inventory_item_id,
527 p_revision => p_revision,
528 p_lot_number => p_lot_number,
529 p_serial_number => NULL,
530 p_user_id => p_user_id,
531 p_cost_group_id => tag_record.cost_group_id,
532 x_adjustment_id => l_adjustment_id
533 );
534 ELSE
535 l_adjustment_id := tag_record.adjustment_id;
536 END IF;
537 update_row( p_tag_id => tag_record.tag_id,
538 p_physical_inventory_id => p_physical_inventory_id,
539 p_organization_id => p_organization_id,
540 p_subinventory => p_subinventory,
541 p_locator_id => p_locator_id,
542 p_parent_lpn_id => p_parent_lpn_id,
543 p_inventory_item_id => p_inventory_item_id,
544 p_revision => p_revision,
545 p_lot_number => p_lot_number,
546 p_serial_number => NULL,
547 p_tag_quantity => p_tag_quantity,
548 p_tag_uom => p_tag_uom,
549 p_user_id => p_user_id,
550 p_cost_group_id => tag_record.cost_group_id,
551 p_adjustment_id => l_adjustment_id
552 ,p_tag_sec_quantity => p_tag_sec_quantity --INVCONV, NSRIVAST
553 );
554 update_adjustment
555 (p_adjustment_id => l_adjustment_id,
556 p_physical_inventory_id => p_physical_inventory_id,
557 p_organization_id => p_organization_id,
558 p_user_id => p_user_id
559 );
560 ELSE
561 IF (p_dynamic_tag_entry_flag = 1) THEN
562 IF (l_debug = 1) THEN
563 print_debug('Dynamic non-serial tag entry to be inserted');
564 END IF;
565 -- Dynamic tag entries are allowed
566
567 -- Get the cost group ID for this entry
568 inv_cyc_lovs.get_cost_group_id
569 (p_organization_id => p_organization_id,
570 p_subinventory => p_subinventory,
571 p_locator_id => p_locator_id,
572 p_parent_lpn_id => p_parent_lpn_id,
573 p_inventory_item_id => p_inventory_item_id,
574 p_revision => p_revision,
575 p_lot_number => p_lot_number,
576 p_serial_number => l_current_serial,
577 x_out => l_cost_group_id);
578 -- Bug# 2607187
579 -- Do not get the default cost group ID. If the item is
580 -- new and does not exist in onhand, pass a NULL value
581 -- for the cost group ID. The transaction manager will
582 -- call the cost group rules engine for that if the
583 -- cost group ID passed into MMTT is null.
584 IF (l_cost_group_id = -999) THEN
585 l_cost_group_id := NULL;
586 END IF;
587 -- Get the default cost group ID based on the given org
588 -- and sub if cost group ID was not retrieved successfully
589 /*IF (l_cost_group_id = -999) THEN
590 inv_cyc_lovs.get_default_cost_group_id
591 (p_organization_id => p_organization_id,
592 p_subinventory => p_subinventory,
593 x_out => l_cost_group_id);
594 END IF;
595 -- Default the cost group ID to 1 if nothing can be found
596 IF (l_cost_group_id = -999) THEN
597 l_cost_group_id := 1;
598 END IF;*/
599
600 -- Generate a new adjustment ID for this tag
601 find_existing_adjustment
602 ( p_physical_inventory_id => p_physical_inventory_id,
603 p_organization_id => p_organization_id,
604 p_subinventory => p_subinventory,
605 p_locator_id => p_locator_id,
606 p_parent_lpn_id => p_parent_lpn_id,
607 p_inventory_item_id => p_inventory_item_id,
608 p_revision => p_revision,
609 p_lot_number => p_lot_number,
610 p_serial_number => NULL,
611 p_user_id => p_user_id,
612 p_cost_group_id => l_cost_group_id,
613 x_adjustment_id => l_adjustment_id
614 );
615 insert_row( p_physical_inventory_id => p_physical_inventory_id,
616 p_organization_id => p_organization_id,
617 p_subinventory => p_subinventory,
618 p_locator_id => p_locator_id,
619 p_parent_lpn_id => p_parent_lpn_id,
620 p_inventory_item_id => p_inventory_item_id,
621 p_revision => p_revision,
622 p_lot_number => p_lot_number,
623 p_serial_number => NULL,
624 p_tag_quantity => p_tag_quantity,
625 p_tag_uom => p_tag_uom,
626 p_user_id => p_user_id,
627 p_cost_group_id => l_cost_group_id,
628 p_adjustment_id => l_adjustment_id
629 --INVCONV, NSRIVAST, START
630 ,p_tag_sec_quantity => p_tag_sec_quantity
631 ,p_tag_sec_uom => p_tag_sec_uom
632 --INVCONV, NSRIVAST, END
633
634 );
635 update_adjustment
636 (p_adjustment_id => l_adjustment_id,
637 p_physical_inventory_id => p_physical_inventory_id,
638 p_organization_id => p_organization_id,
639 p_user_id => p_user_id
640 );
641 ELSE
642 -- Dynamic tag entries are not allowed
643 -- This shouldn't happen if the mobile form's LOV
644 -- statements are correctly set
645 FND_MESSAGE.SET_NAME('INV','INV_NO_DYNAMIC_TAGS');
646 FND_MSG_PUB.ADD;
647 --RAISE FND_API.G_EXC_ERROR;
648 END IF;
649 END IF;
650 END IF;
651
652 END process_tag;
653
654
655 PROCEDURE insert_row
656 (p_physical_inventory_id IN NUMBER,
657 p_organization_id IN NUMBER,
658 p_subinventory IN VARCHAR2,
659 p_locator_id IN NUMBER,
660 p_parent_lpn_id IN NUMBER,
661 p_inventory_item_id IN NUMBER,
662 p_revision IN VARCHAR2,
663 p_lot_number IN VARCHAR2,
664 p_serial_number IN VARCHAR2,
665 p_tag_quantity IN NUMBER,
666 p_tag_uom IN VARCHAR2,
667 p_user_id IN NUMBER,
668 p_cost_group_id IN NUMBER,
669 p_adjustment_id IN NUMBER
670 --INVCONV, NSRIVAST, START
671 ,p_tag_sec_quantity IN NUMBER := NULL
672 ,p_tag_sec_uom IN VARCHAR2 := NULL
673 --INVCONV, NSRIVAST, END
674 )
675 IS
676 l_tag_id NUMBER;
677 l_tag_number VARCHAR2(40);
678 l_next_tag_number VARCHAR2(40);
679 l_tag_qty_at_standard_uom NUMBER;
680 l_outermost_lpn_id NUMBER;
681 CURSOR tag_number_cursor IS
682 SELECT next_tag_number
683 FROM mtl_physical_inventories
684 WHERE physical_inventory_id = p_physical_inventory_id
685 AND organization_id = p_organization_id;
686 l_return_status VARCHAR2(300);
687 l_msg_count NUMBER;
688 l_msg_data VARCHAR2(300);
689 l_lpn_list WMS_Container_PUB.LPN_Table_Type;
690 l_temp_bool BOOLEAN;
691 l_prefix VARCHAR2(30);
692 l_quantity NUMBER;
693 l_from_number NUMBER;
694 l_to_number NUMBER;
695 l_errorcode NUMBER;
696 l_length NUMBER;
697 l_padded_length NUMBER;
698 l_item_standard_uom VARCHAR2(3);
699 l_employee_id NUMBER;
700
701 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
702 BEGIN
703 IF (l_debug = 1) THEN
704 print_debug('***insert_row***');
705 END IF;
706 -- Get the next tag ID for this new record
707 SELECT mtl_physical_inventory_tags_s.nextval
708 INTO l_tag_id
709 FROM dual;
710 IF (l_debug = 1) THEN
711 print_debug('Dynamic tag ID: ' || l_tag_id);
712 END IF;
713
714 -- Generate a new tag number for this record
715 OPEN tag_number_cursor;
716 FETCH tag_number_cursor INTO l_tag_number;
717 IF tag_number_cursor%NOTFOUND THEN
718 -- No value set for next_tag_number so manually
719 -- generate the next sequence value
720 SELECT MAX(tag_number)
721 INTO l_tag_number
722 FROM mtl_physical_inventory_tags
723 WHERE physical_inventory_id = p_physical_inventory_id
724 AND organization_id = p_organization_id;
725 -- Now parse the tag number and increment the numerical part
726 l_temp_bool := MTL_Serial_Check.inv_serial_info
727 ( p_from_serial_number => l_tag_number,
728 p_to_serial_number => NULL,
729 x_prefix => l_prefix,
730 x_quantity => l_quantity,
731 x_from_number => l_from_number,
732 x_to_number => l_to_number,
733 x_errorcode => l_errorcode);
734 l_length := length(l_tag_number);
735 l_from_number := l_from_number + 1;
736 l_padded_length := l_length - length(l_from_number);
737 l_tag_number := RPAD(l_prefix, l_padded_length, '0') ||
738 l_from_number;
739 END IF;
740 CLOSE tag_number_cursor;
741
742 -- Update the next_tag_number column in the physical inventories table
743 -- since we have just generated a new tag number value here
744 l_temp_bool := MTL_Serial_Check.inv_serial_info
745 ( p_from_serial_number => l_tag_number,
746 p_to_serial_number => NULL,
747 x_prefix => l_prefix,
748 x_quantity => l_quantity,
749 x_from_number => l_from_number,
750 x_to_number => l_to_number,
751 x_errorcode => l_errorcode);
752 l_length := length(l_tag_number);
753 l_from_number := l_from_number + 1;
754 l_padded_length := l_length - length(l_from_number);
755 l_next_tag_number := RPAD(NVL(l_prefix, '0'), l_padded_length, '0') ||
756 l_from_number;
757 UPDATE MTL_PHYSICAL_INVENTORIES
758 SET next_tag_number = l_next_tag_number
759 WHERE physical_inventory_id = p_physical_inventory_id
760 AND organization_id = p_organization_id;
761 IF (l_debug = 1) THEN
762 print_debug('Update physical inventory with next tag number: ' || l_next_tag_number);
763 END IF;
764
765 -- Calculate the tag quantity at standard uom
766 SELECT primary_uom_code
767 INTO l_item_standard_uom
768 FROM mtl_system_items
769 WHERE inventory_item_id = p_inventory_item_id
770 AND organization_id = p_organization_id;
771 -- I assume that the primary_uom_code is always given for an item
772 l_tag_qty_at_standard_uom := inv_convert.inv_um_convert
773 ( item_id => p_inventory_item_id,
774 precision => 5,
775 from_quantity => p_tag_quantity,
776 from_unit => p_tag_uom,
777 to_unit => l_item_standard_uom,
778 from_name => NULL,
779 to_name => NULL);
780 -- Conversion will return -99999 if unsuccessful so need to check for this
781 IF (l_tag_qty_at_standard_uom = -99999) THEN
782 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONVERSION');
783 FND_MSG_PUB.ADD;
784 RAISE FND_API.G_EXC_ERROR;
785 END IF;
786
787 -- Get the outermost LPN ID for this record if necessary
788 IF (p_parent_lpn_id IS NOT NULL) THEN
789 --Bug2935754 starts
790 /*
791 WMS_CONTAINER_PUB.GET_OUTERMOST_LPN
792 ( p_api_version => 1.0,
793 x_return_status => l_return_status,
794 x_msg_count => l_msg_count,
795 x_msg_data => l_msg_data,
796 p_lpn_id => p_parent_lpn_id,
797 x_lpn_list => l_lpn_list);
798 l_outermost_lpn_id := l_lpn_list(1).lpn_id;
799 */
800 BEGIN
801 SELECT outermost_lpn_id
802 INTO l_outermost_lpn_id
803 FROM WMS_LICENSE_PLATE_NUMBERS
804 WHERE lpn_id = p_parent_lpn_id;
805 EXCEPTION
806 WHEN OTHERS THEN
807 IF (l_debug = 1) THEN
808 print_debug('Unable to fetch outermost LPN for LPN ID: ' || p_parent_lpn_id);
809 END IF;
810 RAISE FND_API.G_EXC_ERROR;
811 END;
812 --Bug2935754 ends
813
814 IF (l_debug = 1) THEN
815 print_debug('LPN ID passed in so get the outermost LPN: ' || l_outermost_lpn_id);
816 END IF;
817 END IF;
818
819 print_debug('Deriving the employee id based on user ID: ' || p_user_id);
820
821 --Bug 6600166, raising error if user is not a valid employee
822 BEGIN
823 SELECT fus.employee_id
824 INTO l_employee_id
825 FROM mtl_employees_current_view mec, fnd_user fus
826 WHERE fus.user_id = p_user_id
827 AND mec.employee_id = fus.employee_id
828 AND mec.organization_id = p_organization_id;
829 EXCEPTION
830 WHEN OTHERS THEN
831 FND_MESSAGE.SET_NAME('INV', 'INV_EMP');
832 FND_MSG_PUB.ADD;
833 RAISE FND_API.G_EXC_ERROR;
834 END;
835
836 print_debug('the employee id is: ' || l_employee_id);
837
838 -- Insert the new record
839 IF (l_debug = 1) THEN
840 print_debug('Inserting the new record here');
841 END IF;
842 INSERT INTO MTL_PHYSICAL_INVENTORY_TAGS
843 (tag_id,
844 physical_inventory_id,
845 organization_id,
846 last_update_date,
847 last_updated_by,
848 creation_date,
849 created_by,
850 last_update_login,
851 void_flag,
852 tag_number,
853 adjustment_id,
854 inventory_item_id,
855 tag_quantity,
856 tag_uom,
857 tag_quantity_at_standard_uom,
858 standard_uom,
859 subinventory,
860 locator_id,
861 lot_number,
862 revision,
863 serial_num,
864 counted_by_employee_id,
865 parent_lpn_id,
866 outermost_lpn_id,
867 cost_group_id
868 --INVCONV, NSRIVAST, START
869 ,tag_secondary_uom
870 ,tag_secondary_quantity
871 --INVCONV, NSRIVAST, END
872 ) VALUES
873 (l_tag_id,
874 p_physical_inventory_id,
875 p_organization_id,
876 SYSDATE,
877 p_user_id,
878 SYSDATE,
879 p_user_id,
880 p_user_id,
881 2,
882 l_tag_number,
883 p_adjustment_id,
884 p_inventory_item_id,
885 p_tag_quantity,
886 p_tag_uom,
887 l_tag_qty_at_standard_uom,
888 l_item_standard_uom,
889 p_subinventory,
890 p_locator_id,
891 p_lot_number,
892 p_revision,
893 p_serial_number,
894 l_employee_id,
895 p_parent_lpn_id,
896 l_outermost_lpn_id,
897 p_cost_group_id
898 --INVCONV, NSRIVAST, START
899 ,p_tag_sec_uom
900 ,p_tag_sec_quantity
901 --INVCONV, NSRIVAST, END
902 );
903
904 EXCEPTION
905 WHEN fnd_api.g_exc_error THEN
906 raise fnd_api.g_exc_error;
907 WHEN OTHERS THEN
908 print_debug(SQLERRM);
909 raise fnd_api.g_exc_unexpected_error;
910
911 END insert_row;
912
913
914 PROCEDURE update_row
915 (p_tag_id IN NUMBER,
916 p_physical_inventory_id IN NUMBER,
917 p_organization_id IN NUMBER,
918 p_subinventory IN VARCHAR2,
919 p_locator_id IN NUMBER,
920 p_parent_lpn_id IN NUMBER,
921 p_inventory_item_id IN NUMBER,
922 p_revision IN VARCHAR2,
923 p_lot_number IN VARCHAR2,
924 p_serial_number IN VARCHAR2,
925 p_tag_quantity IN NUMBER,
926 p_tag_uom IN VARCHAR2,
927 p_user_id IN NUMBER,
928 p_cost_group_id IN NUMBER,
929 p_adjustment_id IN NUMBER
930 ,p_tag_sec_quantity IN NUMBER := NULL --INVCONV, NSRIVAST, START
931 )
932 IS
933 l_tag_qty_at_standard_uom NUMBER;
934 l_outermost_lpn_id NUMBER;
935 l_item_standard_uom VARCHAR2(3);
936 l_return_status VARCHAR2(300);
937 l_msg_count NUMBER;
938 l_msg_data VARCHAR2(300);
939 l_lpn_list WMS_Container_PUB.LPN_Table_Type;
940 l_employee_id NUMBER;
941
942 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
943 BEGIN
944 IF (l_debug = 1) THEN
945 print_debug('***update_row***');
946 END IF;
947 -- Calculate the tag quantity at standard uom
948 SELECT primary_uom_code
949 INTO l_item_standard_uom
950 FROM mtl_system_items
951 WHERE inventory_item_id = p_inventory_item_id
952 AND organization_id = p_organization_id;
953 -- I assume that the primary_uom_code is always given for an item
954 l_tag_qty_at_standard_uom := inv_convert.inv_um_convert
955 ( item_id => p_inventory_item_id,
956 precision => 5,
957 from_quantity => p_tag_quantity,
958 from_unit => p_tag_uom,
959 to_unit => l_item_standard_uom,
960 from_name => NULL,
961 to_name => NULL);
962 -- Conversion will return -99999 if unsuccessful so need to check for this
963 IF (l_tag_qty_at_standard_uom = -99999) THEN
964 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONVERSION');
965 FND_MSG_PUB.ADD;
966 RAISE FND_API.G_EXC_ERROR;
967 END IF;
968
969 -- Get the outermost LPN ID for this record if necessary
970 IF (p_parent_lpn_id IS NOT NULL) THEN
971 --Bug2935754 starts
972 /*
973 WMS_CONTAINER_PUB.GET_OUTERMOST_LPN
974 ( p_api_version => 1.0,
975 x_return_status => l_return_status,
976 x_msg_count => l_msg_count,
977 x_msg_data => l_msg_data,
978 p_lpn_id => p_parent_lpn_id,
979 x_lpn_list => l_lpn_list);
980 l_outermost_lpn_id := l_lpn_list(1).lpn_id;
981 */
982 BEGIN
983 SELECT outermost_lpn_id
984 INTO l_outermost_lpn_id
985 FROM WMS_LICENSE_PLATE_NUMBERS
986 WHERE lpn_id = p_parent_lpn_id;
987 EXCEPTION
988 WHEN OTHERS THEN
989 IF (l_debug = 1) THEN
990 print_debug('Unable to fetch outermost LPN for LPN ID: ' || p_parent_lpn_id);
991 END IF;
992 RAISE FND_API.G_EXC_ERROR;
993 END;
994 --Bug2935754 ends
995
996 IF (l_debug = 1) THEN
997 print_debug('LPN ID passed in so get the outermost LPN: ' || l_outermost_lpn_id);
998 END IF;
999 END IF;
1000
1001 print_debug('Deriving the employee id based on user ID: ' || p_user_id);
1002
1003 --Bug 6600166, raising error if user is not a valid employee
1004 BEGIN
1005 SELECT fus.employee_id
1006 INTO l_employee_id
1007 FROM mtl_employees_current_view mec, fnd_user fus
1008 WHERE fus.user_id = p_user_id
1009 AND mec.employee_id = fus.employee_id
1010 AND mec.organization_id = p_organization_id;
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 FND_MESSAGE.SET_NAME('INV', 'INV_EMP');
1014 FND_MSG_PUB.ADD;
1015 RAISE FND_API.G_EXC_ERROR;
1016 END;
1017
1018 print_debug('the employee id is: ' || l_employee_id);
1019
1020 -- Update the record
1021 IF (l_debug = 1) THEN
1022 print_debug('Updating the physical inventory tag record for tag ID: ' || p_tag_id);
1023 END IF;
1024 UPDATE MTL_PHYSICAL_INVENTORY_TAGS
1025 SET
1026 last_update_date = SYSDATE,
1027 last_updated_by = p_user_id,
1028 last_update_login = p_user_id,
1029 adjustment_id = p_adjustment_id,
1030 inventory_item_id = p_inventory_item_id,
1031 tag_quantity = p_tag_quantity,
1032 tag_uom = p_tag_uom,
1033 tag_quantity_at_standard_uom = l_tag_qty_at_standard_uom,
1034 standard_uom = l_item_standard_uom,
1035 subinventory = p_subinventory,
1036 locator_id = p_locator_id,
1037 lot_number = p_lot_number,
1038 revision = p_revision,
1039 serial_num = p_serial_number,
1040 counted_by_employee_id = l_employee_id,
1041 parent_lpn_id = p_parent_lpn_id,
1042 outermost_lpn_id = l_outermost_lpn_id,
1043 cost_group_id = p_cost_group_id
1044 ,tag_secondary_quantity = p_tag_sec_quantity --INVCONV, NSRIVAST, START
1045 WHERE tag_id = p_tag_id;
1046
1047 IF (SQL%NOTFOUND) THEN
1048 RAISE NO_DATA_FOUND;
1049 END IF;
1050
1051 END update_row;
1052
1053
1054 PROCEDURE update_adjustment
1055 (p_adjustment_id IN NUMBER,
1056 p_physical_inventory_id IN NUMBER,
1057 p_organization_id IN NUMBER,
1058 p_user_id IN NUMBER
1059 )
1060 IS
1061 l_adj_count_quantity NUMBER;
1062 -- Variables needed for calling the label printing API
1063 l_inventory_item_id NUMBER;
1064 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
1065 l_lot_number VARCHAR2(80);
1066 l_serial_number VARCHAR2(30);
1067 l_lpn_id NUMBER;
1068 l_subinventory VARCHAR2(10);
1069 l_locator_id NUMBER;
1070 l_adjustment_quantity NUMBER;
1071 l_standard_uom_code VARCHAR2(3);
1072 l_label_status VARCHAR2(300) := NULL;
1073 l_return_status VARCHAR2(3000);
1074 l_msg_count NUMBER;
1075 l_msg_data VARCHAR2(3000);
1076
1077 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1078 BEGIN
1079 IF (l_debug = 1) THEN
1080 print_debug('***update_adjustment***');
1081 END IF;
1082 SELECT NVL(SUM(tag_quantity_at_standard_uom),0)
1083 INTO l_adj_count_quantity
1084 FROM mtl_physical_inventory_tags
1085 WHERE adjustment_id = p_adjustment_id
1086 AND organization_id = p_organization_id
1087 AND physical_inventory_id = p_physical_inventory_id
1088 AND void_flag = 2;
1089
1090 IF (l_debug = 1) THEN
1091 print_debug('Updating the physical adjustment record for adjustment ID: ' || p_adjustment_id);
1092 END IF;
1093 UPDATE mtl_physical_adjustments
1094 SET last_update_date = SYSDATE,
1095 last_updated_by = NVL(p_user_id, -1),
1096 count_quantity = l_adj_count_quantity,
1097 adjustment_quantity = NVL(l_adj_count_quantity, NVL(system_quantity,0))
1098 - NVL(system_quantity,0),
1099 approval_status = NULL,
1100 approved_by_employee_id = NULL
1101 WHERE adjustment_id = p_adjustment_id
1102 AND physical_inventory_id = p_physical_inventory_id
1103 AND organization_id = p_organization_id;
1104
1105 -- Get the adjustment record values needed for label printing
1106 IF (l_debug = 1) THEN
1107 print_debug('Get the adjustment record values for label printing');
1108 END IF;
1109 SELECT inventory_item_id, lot_number, serial_number, parent_lpn_id,
1110 subinventory_name, locator_id, NVL(adjustment_quantity, 0)
1111 INTO l_inventory_item_id, l_lot_number, l_serial_number,
1112 l_lpn_id, l_subinventory, l_locator_id, l_adjustment_quantity
1113 FROM mtl_physical_adjustments
1114 WHERE adjustment_id = p_adjustment_id
1115 AND physical_inventory_id = p_physical_inventory_id
1116 AND organization_id = p_organization_id;
1117
1118 -- Get the primary UOM for the inventory item
1119 IF (l_debug = 1) THEN
1120 print_debug('Get the primary UOM code: ' || 'Item ID: ' || l_inventory_item_id || ': ' || 'Org ID: ' || p_organization_id);
1121 END IF;
1122 SELECT primary_uom_code
1123 INTO l_standard_uom_code
1124 FROM mtl_system_items
1125 WHERE inventory_item_id = l_inventory_item_id
1126 AND organization_id = p_organization_id;
1127
1128 -- Call the label printing API if an adjustment is required
1129 IF (l_debug = 1) THEN
1130 print_debug('Adjustment quantity: ' || l_adjustment_quantity);
1131 END IF;
1132 IF (l_adjustment_quantity <> 0) THEN
1133 IF (l_debug = 1) THEN
1134 print_debug('Calling print_label_manual_wrap with the following input parameters');
1135 print_debug('p_business_flow_code: -> ' || 9);
1136 END IF;
1137 --print_debug('p_label_type: ---------> ' || 1);
1138 IF (l_debug = 1) THEN
1139 print_debug('p_organization_id: ----> ' || p_organization_id);
1140 print_debug('p_inventory_item_id: --> ' || l_inventory_item_id);
1141 print_debug('p_lot_number: ---------> ' || l_lot_number);
1142 print_debug('p_fm_serial_number: ---> ' || l_serial_number);
1143 print_debug('p_to_serial_number: ---> ' || l_serial_number);
1144 print_debug('p_lpn_id: -------------> ' || l_lpn_id);
1145 print_debug('p_subinventory_code: --> ' || l_subinventory);
1146 print_debug('p_locator_id: ---------> ' || l_locator_id);
1147 print_debug('p_quantity: -----------> ' || l_adjustment_quantity);
1148 print_debug('p_uom: ----------------> ' || l_standard_uom_code);
1149 print_debug('p_no_of_copies: -------> ' || 1);
1150 END IF;
1151
1152 -- Bug# 2301732
1153 -- Make the call to the label printing API more robust
1154 -- by trapping for exceptions when calling it
1155 -- Bug# 2412674
1156 -- Don't pass in the value for the label type
1157 BEGIN
1158 inv_label.print_label_manual_wrap
1159 ( x_return_status => l_return_status ,
1160 x_msg_count => l_msg_count ,
1161 x_msg_data => l_msg_data ,
1162 x_label_status => l_label_status ,
1163 p_business_flow_code => 9 ,
1164 --p_label_type => 1 ,
1165 p_organization_id => p_organization_id ,
1166 p_inventory_item_id => l_inventory_item_id ,
1167 p_lot_number => l_lot_number ,
1168 p_fm_serial_number => l_serial_number ,
1169 p_to_serial_number => l_serial_number ,
1170 p_lpn_id => l_lpn_id ,
1171 p_subinventory_code => l_subinventory ,
1172 p_locator_id => l_locator_id ,
1173 p_quantity => l_adjustment_quantity ,
1174 p_uom => l_standard_uom_code ,
1175 p_no_of_copies => 1
1176 );
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 IF (l_debug = 1) THEN
1180 print_debug('Error while calling label printing API');
1181 END IF;
1182 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_PRINT_LABEL_FAILE');
1183 FND_MSG_PUB.ADD;
1184 END;
1185 IF (l_debug = 1) THEN
1186 print_debug('After calling label printing API: ' || l_return_status || ', ' || l_label_status || ', ' || l_msg_data);
1187 END IF;
1188
1189 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1190 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_PRINT_LABEL_FAILE');
1191 FND_MSG_PUB.ADD;
1192 END IF;
1193
1194 END IF;
1195
1196 END update_adjustment;
1197
1198
1199
1200 PROCEDURE find_existing_adjustment
1201 (p_physical_inventory_id IN NUMBER,
1202 p_organization_id IN NUMBER,
1203 p_subinventory IN VARCHAR2,
1204 p_locator_id IN NUMBER,
1205 p_parent_lpn_id IN NUMBER,
1206 p_inventory_item_id IN NUMBER,
1207 p_revision IN VARCHAR2,
1208 p_lot_number IN VARCHAR2,
1209 p_serial_number IN VARCHAR2,
1210 p_user_id IN NUMBER,
1211 p_cost_group_id IN NUMBER,
1212 x_adjustment_id OUT NOCOPY NUMBER
1213 )
1214 IS
1215 l_rev_code NUMBER;
1216 l_org_locator_type NUMBER;
1217 l_sub_locator_type NUMBER;
1218 l_location_control_code NUMBER;
1219 l_lot_control_code NUMBER;
1220 l_serial_control_code NUMBER;
1221 l_adj_id NUMBER:= -1;
1222 l_actual_cost NUMBER;
1223 l_outermost_lpn_id NUMBER;
1224 l_return_status VARCHAR2(300);
1225 l_msg_count NUMBER;
1226 l_msg_data VARCHAR2(300);
1227 l_lpn_list WMS_Container_PUB.LPN_Table_Type;
1228 l_approval_status NUMBER:= -1;
1229
1230 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1231 BEGIN
1232 IF (l_debug = 1) THEN
1233 print_debug('***find_existing_adjustment***');
1234 END IF;
1235 -- Get the required information for the local variables
1236 -- Locator control type for the org
1237 SELECT stock_locator_control_code
1238 INTO l_org_locator_type
1239 FROM mtl_parameters
1240 WHERE organization_id = p_organization_id;
1241
1242 -- Locator control type for the sub
1243 SELECT locator_type
1244 INTO l_sub_locator_type
1245 FROM mtl_secondary_inventories
1246 WHERE secondary_inventory_name = p_subinventory
1247 AND organization_id = p_organization_id;
1248
1249 -- Locator control type for the item plus revision, lot, and serial
1250 -- control codes
1251 SELECT revision_qty_control_code, location_control_code,
1252 lot_control_code, serial_number_control_code
1253 INTO l_rev_code, l_location_control_code,
1254 l_lot_control_code, l_serial_control_code
1255 FROM mtl_system_items
1256 WHERE inventory_item_id = p_inventory_item_id
1257 AND organization_id = p_organization_id;
1258
1259 -- Get the adjustment ID if it is existing
1260 IF (l_debug = 1) THEN
1261 print_debug('Try to find the adjustment ID if it exists');
1262 END IF;
1263 SELECT MIN(ADJUSTMENT_ID)
1264 INTO l_adj_id
1265 FROM MTL_PHYSICAL_ADJUSTMENTS
1266 WHERE ORGANIZATION_ID = p_organization_id
1267 AND PHYSICAL_INVENTORY_ID = p_physical_inventory_id
1268 AND INVENTORY_ITEM_ID = p_inventory_item_id
1269 AND SUBINVENTORY_NAME = p_subinventory
1270 AND ( NVL(REVISION,'@@@@@') = NVL(p_revision,'@@@@@')
1271 OR l_rev_code = 1 )
1272 AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999) --Bug 6929248 Posted adjustments should not be allowed to enter tags
1273 AND (approval_status=3 OR NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999))
1274 AND (NVL(LOCATOR_ID, -99999) = NVL(p_locator_id, -99999)
1275 OR l_org_locator_type = 1
1276 OR (l_org_locator_type = 4
1277 AND (l_sub_locator_type = 1
1278 OR (l_sub_locator_type = 5
1279 AND l_location_control_code = 1)))
1280 OR (l_location_control_code = 5
1281 AND l_location_control_code = 1))
1282 AND ( NVL(LOT_NUMBER,'@@@@@') = NVL(p_lot_number,'@@@@@')
1283 OR l_lot_control_code = 1 )
1284 AND ( NVL(SERIAL_NUMBER,'@@@@@') = NVL(p_serial_number,'@@@@@')
1285 OR l_serial_control_code = 1 )
1286 GROUP BY ORGANIZATION_ID,
1287 PHYSICAL_INVENTORY_ID,
1288 INVENTORY_ITEM_ID,
1289 SUBINVENTORY_NAME,
1290 REVISION,
1291 LOCATOR_ID,
1292 PARENT_LPN_ID,
1293 COST_GROUP_ID,
1294 LOT_NUMBER,
1295 SERIAL_NUMBER;
1296
1297 /* Bug 4350316, if the corresponding adjustment is posted, not allowing the user to enter a dynamic tag*/
1298
1299 IF l_adj_id IS NOT NULL THEN
1300 select approval_status
1301 into l_approval_status
1302 from mtl_physical_adjustments
1303 where adjustment_id = l_adj_id
1304 and physical_inventory_id = p_physical_inventory_id;
1305
1306 if (nvl(l_approval_status,0) = 3) then
1307 print_debug('Error: The corresponding adjustment_id '||l_adj_id||' is already posted');
1308 fnd_message.set_name('INV','INV_PHYSICAL_ADJ_POSTED');
1309 fnd_message.set_token('TOKEN1', l_adj_id);
1310 fnd_msg_pub.add;
1311 raise fnd_api.g_exc_error;
1312 end if;
1313 END IF;
1314
1315
1316 x_adjustment_id := l_adj_id;
1317
1318 EXCEPTION
1319 WHEN NO_DATA_FOUND THEN
1320 -- insert new adjustment row
1321 IF (l_debug = 1) THEN
1322 print_debug('No adjustment record found so insert a new one');
1323 END IF;
1324
1325 -- Get the actual cost of the item in the current tag
1326 IF (l_debug = 1) THEN
1327 print_debug('Get the actual cost of the item');
1328 END IF;
1329
1330 /* Bug# 2942493
1331 ** Instead of duplicating the code, reusing the common utility to
1332 ** to get the item cost. That way its easier to maintain.
1333 */
1334
1335 INV_UTILITIES.GET_ITEM_COST(
1336 v_org_id => p_organization_id,
1337 v_item_id => p_inventory_item_id,
1338 v_locator_id => p_locator_id,
1339 v_item_cost => l_actual_cost);
1340
1341 IF (l_actual_cost = -999) THEN
1342 l_actual_cost := 0;
1343 END IF;
1344
1345 -- Get a valid adjustment ID for the new record
1346 SELECT mtl_physical_adjustments_s.NEXTVAL
1347 INTO l_adj_id
1348 FROM dual;
1349 IF (l_debug = 1) THEN
1350 print_debug('New adjustment ID: ' || l_adj_id);
1351 END IF;
1352
1353 -- Get the outermost LPN ID if necessary
1354 IF (p_parent_lpn_id IS NOT NULL) THEN
1355 --Bug2935754 starts
1356 /*
1357 WMS_CONTAINER_PUB.GET_OUTERMOST_LPN
1358 ( p_api_version => 1.0,
1359 x_return_status => l_return_status,
1360 x_msg_count => l_msg_count,
1361 x_msg_data => l_msg_data,
1362 p_lpn_id => p_parent_lpn_id,
1363 x_lpn_list => l_lpn_list);
1364 l_outermost_lpn_id := l_lpn_list(1).lpn_id;
1365 */
1366
1367 BEGIN
1368 SELECT outermost_lpn_id
1369 INTO l_outermost_lpn_id
1370 FROM WMS_LICENSE_PLATE_NUMBERS
1371 WHERE lpn_id = p_parent_lpn_id;
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374 IF (l_debug = 1) THEN
1375 print_debug('Unable to fetch outermost LPN for LPN ID: ' || p_parent_lpn_id);
1376 END IF;
1377 RAISE FND_API.G_EXC_ERROR;
1378 END;
1379 --Bug2935754 ends
1380
1381 IF (l_debug = 1) THEN
1382 print_debug('LPN ID passed in so get the outermost LPN: ' || l_outermost_lpn_id);
1383 END IF;
1384 END IF;
1385
1386 -- Insert the new adjustment record
1387 IF (l_debug = 1) THEN
1388 print_debug('Inserting the new physical adjustment record');
1389 END IF;
1390 INSERT INTO mtl_physical_adjustments
1391 ( adjustment_id,
1392 organization_id,
1393 physical_inventory_id,
1394 inventory_item_id,
1395 subinventory_name,
1396 system_quantity,
1397 last_update_date,
1398 last_updated_by,
1399 creation_date,
1400 created_by,
1401 last_update_login,
1402 count_quantity,
1403 adjustment_quantity,
1404 revision,
1405 locator_id,
1406 parent_lpn_id,
1407 outermost_lpn_id,
1408 cost_group_id,
1409 lot_number,
1410 serial_number,
1411 actual_cost )
1412 VALUES ( l_adj_id,
1413 p_organization_id,
1414 p_physical_inventory_id,
1415 p_inventory_item_id,
1416 p_subinventory,
1417 0,
1418 SYSDATE,
1419 p_user_id,
1420 SYSDATE,
1421 p_user_id,
1422 p_user_id,
1423 0,
1424 0,
1425 p_revision,
1426 p_locator_id,
1427 p_parent_lpn_id,
1428 l_outermost_lpn_id,
1429 p_cost_group_id,
1430 p_lot_number,
1431 p_serial_number,
1432 l_actual_cost);
1433
1434 x_adjustment_id := l_adj_id;
1435
1436 END find_existing_adjustment;
1437
1438
1439
1440 PROCEDURE process_summary
1441 (p_physical_inventory_id IN NUMBER,
1442 p_organization_id IN NUMBER,
1443 p_subinventory IN VARCHAR2,
1444 p_locator_id IN NUMBER := NULL,
1445 p_parent_lpn_id IN NUMBER := NULL,
1446 p_dynamic_tag_entry_flag IN NUMBER,
1447 p_user_id IN NUMBER
1448 )
1449 IS
1450 l_current_lpn NUMBER;
1451 l_temp_uom_code VARCHAR2(3);
1452 CURSOR nested_lpn_cursor IS
1453 SELECT *
1454 FROM WMS_LICENSE_PLATE_NUMBERS
1455 START WITH lpn_id = p_parent_lpn_id
1456 CONNECT BY parent_lpn_id = PRIOR lpn_id;
1457 CURSOR lpn_contents_cursor IS
1458 SELECT *
1459 FROM WMS_LPN_CONTENTS
1460 WHERE parent_lpn_id = l_current_lpn
1461 AND NVL(serial_summary_entry, 2) = 2;
1462 CURSOR lpn_serial_contents_cursor IS
1463 SELECT *
1464 FROM MTL_SERIAL_NUMBERS
1465 WHERE lpn_id = l_current_lpn;
1466
1467 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1468 BEGIN
1469 IF (l_debug = 1) THEN
1470 print_debug('***process_summary***');
1471 END IF;
1472 -- Use the cursor that searches through all levels in the parent child relationship
1473 FOR v_lpn_id IN nested_lpn_cursor LOOP
1474 l_current_lpn := v_lpn_id.lpn_id;
1475
1476 -- Process the tag for the LPN item itself if it is associated with
1477 -- an inventory item
1478 IF (v_lpn_id.inventory_item_id IS NOT NULL) THEN
1479 -- Get the primary UOM for the container inventory item
1480 SELECT primary_uom_code
1481 INTO l_temp_uom_code
1482 FROM mtl_system_items
1483 WHERE inventory_item_id = v_lpn_id.inventory_item_id
1484 AND organization_id = v_lpn_id.organization_id;
1485
1486 IF (l_debug = 1) THEN
1487 print_debug('Counting an LPN');
1488 END IF;
1489 process_tag
1490 (p_physical_inventory_id => p_physical_inventory_id,
1491 p_organization_id => p_organization_id,
1492 p_subinventory => p_subinventory,
1493 p_locator_id => p_locator_id,
1494 p_parent_lpn_id => v_lpn_id.parent_lpn_id,
1495 p_inventory_item_id => v_lpn_id.inventory_item_id,
1496 p_revision => v_lpn_id.revision,
1497 p_lot_number => v_lpn_id.lot_number,
1498 p_from_serial_number => v_lpn_id.serial_number,
1499 p_to_serial_number => v_lpn_id.serial_number,
1500 p_tag_quantity => 1,
1501 p_tag_uom => l_temp_uom_code,
1502 p_dynamic_tag_entry_flag => p_dynamic_tag_entry_flag,
1503 p_user_id => p_user_id,
1504 p_cost_group_id => v_lpn_id.cost_group_id
1505 );
1506 END IF;
1507
1508 -- Process the tags for the LPN content items
1509 FOR v_lpn_content IN lpn_contents_cursor LOOP
1510
1511 IF (l_debug = 1) THEN
1512 print_debug('Counting an LPN content item');
1513 END IF;
1514 process_tag
1515 (p_physical_inventory_id => p_physical_inventory_id,
1516 p_organization_id => p_organization_id,
1517 p_subinventory => p_subinventory,
1518 p_locator_id => p_locator_id,
1519 p_parent_lpn_id => v_lpn_content.parent_lpn_id,
1520 p_inventory_item_id => v_lpn_content.inventory_item_id,
1521 p_revision => v_lpn_content.revision,
1522 p_lot_number => v_lpn_content.lot_number,
1523 p_from_serial_number => NULL,
1524 p_to_serial_number => NULL,
1525 p_tag_quantity => v_lpn_content.quantity,
1526 p_tag_uom => v_lpn_content.uom_code,
1527 p_dynamic_tag_entry_flag => p_dynamic_tag_entry_flag,
1528 p_user_id => p_user_id,
1529 p_cost_group_id => v_lpn_content.cost_group_id
1530 );
1531
1532 END LOOP;
1533
1534 -- Process the tags for serialized items
1535 FOR v_lpn_serial_content IN lpn_serial_contents_cursor LOOP
1536 -- Get the primary UOM for the serialized item
1537 SELECT primary_uom_code
1538 INTO l_temp_uom_code
1539 FROM mtl_system_items
1540 WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
1541 AND organization_id = v_lpn_serial_content.current_organization_id;
1542
1543 IF (l_debug = 1) THEN
1544 print_debug('Counting an LPN serial controlled item');
1545 END IF;
1546 process_tag
1547 (p_physical_inventory_id => p_physical_inventory_id,
1548 p_organization_id => p_organization_id,
1549 p_subinventory => p_subinventory,
1550 p_locator_id => p_locator_id,
1551 p_parent_lpn_id => v_lpn_serial_content.lpn_id,
1552 p_inventory_item_id => v_lpn_serial_content.inventory_item_id,
1553 p_revision => v_lpn_serial_content.revision,
1554 p_lot_number => v_lpn_serial_content.lot_number,
1555 p_from_serial_number => v_lpn_serial_content.serial_number,
1556 p_to_serial_number => v_lpn_serial_content.serial_number,
1557 p_tag_quantity => 1,
1558 p_tag_uom => l_temp_uom_code,
1559 p_dynamic_tag_entry_flag => p_dynamic_tag_entry_flag,
1560 p_user_id => p_user_id,
1561 p_cost_group_id => v_lpn_serial_content.cost_group_id
1562 );
1563
1564 END LOOP;
1565
1566 END LOOP;
1567
1568 END process_summary;
1569
1570 --Fix for bug #4654210
1571 PROCEDURE unmark_serials
1572 (p_physical_inventory_id IN NUMBER,
1573 p_organization_id IN NUMBER,
1574 p_item_id IN NUMBER,
1575 x_status OUT NOCOPY NUMBER
1576 )
1577 IS
1578 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1579 BEGIN
1580 IF (l_debug = 1) THEN
1581 print_debug('***unmark_serials***');
1582 END IF;
1583
1584 IF (p_physical_inventory_id IS NULL) THEN
1585 IF (l_debug = 1) THEN
1586 print_debug('Physical Inventory Id is not provided. Returning from the Procedure');
1587 END IF;
1588 x_status := -1;
1589 ELSIF (p_organization_id IS NULL) THEN
1590 IF (l_debug = 1) THEN
1591 print_debug('Organization Id is not provided. Returning from the Procedure');
1592 END IF;
1593 x_status := -1;
1594 ELSIF (p_item_id IS NULL) THEN
1595 IF (l_debug = 1) THEN
1596 print_debug('Inventory Item Id is not provided. Returning from the Procedure');
1597 END IF;
1598 x_status := -1;
1599 ELSE
1600
1601 UPDATE mtl_serial_numbers
1602 SET group_mark_id = -1
1603 WHERE inventory_item_id = p_item_id
1604 AND serial_number in
1605 (SELECT DISTINCT serial_num
1606 FROM mtl_physical_inventory_tags
1607 WHERE organization_id = p_organization_id
1608 AND physical_inventory_id = p_physical_inventory_id
1609 AND inventory_item_id = p_item_id
1610 AND serial_num is not null
1611 )
1612 AND nvl(group_mark_id,-1) <> -1;
1613
1614 IF (l_debug = 1) THEN
1615 print_debug('Updated ' || SQL%ROWCOUNT || ' Records in mtl_serial_numbers for the inventory_item_id ' || p_item_id);
1616 print_debug('*** end unmark_serials***');
1617 END IF;
1618
1619 x_status := 0;
1620 END IF;
1621
1622 END unmark_serials;
1623
1624 --End of Fix for bug # 4654210
1625
1626 -- Fix for 5660272 to get the serial uniquiness type or a given organization
1627 PROCEDURE GET_SERIAL_NUMBER_TYPE
1628 ( x_serial_number_type OUT NOCOPY NUMBER,
1629 p_organization_id IN NUMBER
1630 )
1631 IS
1632 BEGIN
1633 SELECT NVL(SERIAL_NUMBER_TYPE, 0)
1634 INTO x_serial_number_type
1635 FROM MTL_PARAMETERS
1636 WHERE ORGANIZATION_ID = p_organization_id;
1637 EXCEPTION
1638 WHEN NO_DATA_FOUND THEN
1639 x_serial_number_type :=0;
1640 END;
1641 --End of Fix for bug # 5660272
1642 -- Fix for 5660272 to check for the validity of the serial number --
1643 PROCEDURE VALIDATE_SERIAL_STATUS
1644 ( x_status OUT NOCOPY NUMBER, -- 1 FOR SUCCESS AND ANY OTHER VALUE FOR FAILURE
1645 x_organization_code OUT NOCOPY VARCHAR2,
1646 x_current_status OUT NOCOPY VARCHAR2,
1647 p_serial_num IN VARCHAR2,
1648 p_organization_id IN NUMBER,
1649 p_subinventory_code IN VARCHAR2,
1650 p_locator_id IN NUMBER,
1651 p_inventory_item_id IN NUMBER,
1652 p_serial_number_type IN NUMBER
1653 )
1654 IS
1655 l_valid_serial NUMBER := 0;
1656 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1657 BEGIN
1658 x_status := 0;
1659 IF (l_debug = 1) THEN
1660 print_debug('Start Validating the serial for the current status');
1661 END IF;
1662 l_valid_serial := 0;
1663
1664 /*
1665 * Checking if the serial number exist with same serial number in the same subinventory
1666 * organization and locator for the same item.
1667 * Bug# 6354645:
1668 * Added the condition to check for inventory_item_id as the same serial number can be assigned
1669 * to 2 different items in the same org/sub/locator if the serial uniqueness is 'Within Inventory Items'
1670 * This is to avoid TOO_MANY_ROWS_FOUND exception.
1671 */
1672 BEGIN
1673 SELECT 1,MP.ORGANIZATION_CODE,ML.MEANING
1674 INTO l_valid_serial,x_organization_code,x_current_status
1675 FROM MTL_SERIAL_NUMBERS MSN , MTL_PARAMETERS MP, MFG_LOOKUPS ML
1676 WHERE SERIAL_NUMBER like p_serial_num
1677 AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
1678 AND MSN.CURRENT_ORGANIZATION_ID = p_organization_id
1679 AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1680 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1681 AND MSN.CURRENT_SUBINVENTORY_CODE like p_subinventory_code
1682 AND NVL(MSN.CURRENT_LOCATOR_ID,-9999) = NVL(p_locator_id, -9999)
1683 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1684 AND CURRENT_STATUS = 3;
1685 EXCEPTION
1686 WHEN NO_DATA_FOUND THEN
1687 l_valid_serial :=0;
1688 END;
1689
1690 -- If the serial number type is 0. Invalid serial Control Option, so error out. Very rare case.
1691 IF p_serial_number_type = 0 THEN
1692 x_status := -1;
1693 RETURN;
1694 END IF;
1695
1696 --Changed the return value from -1 to 1. Because this will not be a error case. for bug 5903566
1697 IF l_valid_serial = 1 THEN
1698 -- Serial Number is present in the org/subinv/loc given by user. Hence return Success.
1699 x_status := 1;
1700 RETURN;
1701
1702 -- New Serial Given here. So check if it violates the serial Number Uniqueness logic.
1703 ELSIF l_valid_serial = 0 THEN
1704 IF p_serial_number_type = 3 THEN
1705 /*
1706 * If serial uniquiness is accross organizations (3)
1707 * serial should not exist anywhere
1708 */
1709 SELECT MP.ORGANIZATION_CODE,ML.MEANING
1710 INTO x_organization_code,x_current_status
1711 FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1712 WHERE SERIAL_NUMBER like p_serial_num
1713 AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1714 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1715 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1716 AND CURRENT_STATUS NOT IN (1,4);
1717 ELSIF p_serial_number_type IN (1,4) THEN
1718 /*
1719 * If serial uniquiness is within inventory (1,4)
1720 * serial should not exist in same org, same item
1721 */
1722 SELECT MP.ORGANIZATION_CODE,ML.MEANING
1723 INTO x_organization_code,x_current_status
1724 FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1725 WHERE MSN.SERIAL_NUMBER like p_serial_num
1726 AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
1727 AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1728 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1729 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1730 AND CURRENT_STATUS NOT IN (1,4);
1731 ELSIF p_serial_number_type = 2 THEN
1732 /*
1733 * If serial uniquiness is within organization (2)
1734 * serial should first be unique within inventory items and
1735 * then within organizations.
1736 * Added the below condition because it could be the case that the
1737 * same serial could be assigned to the same item in a different org.
1738 */
1739 BEGIN
1740
1741 SELECT MP.ORGANIZATION_CODE,ML.MEANING
1742 INTO x_organization_code,x_current_status
1743 FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1744 WHERE MSN.SERIAL_NUMBER like p_serial_num
1745 AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
1746 AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1747 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1748 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1749 AND CURRENT_STATUS NOT IN (1,4);
1750
1751 x_status := -1;
1752 RETURN;
1753 EXCEPTION
1754 WHEN NO_DATA_FOUND THEN
1755 NULL;-- SETTING NULL HERE TO EXECUTE THE SECOND QUERY.
1756 END;
1757 /*
1758 * If serial uniquiness is within organization (2)
1759 * serial should not exist in same org
1760 */
1761 SELECT MP.ORGANIZATION_CODE,ML.MEANING
1762 INTO x_organization_code,x_current_status
1763 FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1764 WHERE MSN.SERIAL_NUMBER like p_serial_num
1765 AND MSN.CURRENT_ORGANIZATION_ID = p_organization_id
1766 AND MSN.CURRENT_ORGANIZATION_ID = MP. ORGANIZATION_ID
1767 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1768 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1769 AND CURRENT_STATUS NOT IN (1,4);
1770
1771 END IF;
1772 END IF;
1773 x_status := -1;
1774 EXCEPTION
1775 WHEN NO_DATA_FOUND THEN -- No Data Found means the given new serial doesnt violate any of the serial uniqueness conditions.
1776 IF (l_debug = 1) THEN
1777 print_debug('Serial Number status is valid so go ahaead');
1778 END IF;
1779 x_status := 1;
1780 END VALIDATE_SERIAL_STATUS;
1781
1782 --End of Fix for bug # 5660272
1783
1784
1785
1786 END INV_PHY_INV_LOVS;
[Home] [Help]