1 PACKAGE BODY INV_PHY_INV_LOVS AS
2 /* $Header: INVPINLB.pls 120.23.12020000.9 2013/02/25 12:08:04 ragudise ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_PHY_INV_LOVS';
6 g_user_id NUMBER :=0; --Bug 11881386
7 g_employee_id NUMBER :=0; --Bug 11881386
8
9 PROCEDURE print_debug(p_err_msg VARCHAR2)
10 IS
11 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
12 BEGIN
13
14 IF (l_debug = 1) THEN
15 inv_mobile_helper_functions.tracelog
16 (p_err_msg => p_err_msg,
17 p_module => 'INV_PHY_INV_LOVS',
18 p_level => 4);
19 END IF;
20
21 -- dbms_output.put_line(p_err_msg);
22 END print_debug;
23
24
25 -- Name: GET_PHY_INV_LOV
26 --
27 -- Input parameters:
28 -- p_lpn which restricts LOV SQL to the user input text
29 -- p_organization_id Organization ID
30 --
31 -- Output parameters:
32 -- x_phy_inv_lov returns LOV rows as reference cursor
33 --
34 -- Functions: This API returns valid physical inventories
35 --
36 PROCEDURE get_phy_inv_lov
37 (x_phy_inv_lov OUT NOCOPY t_genref,
38 p_phy_inv IN VARCHAR2,
39 p_organization_id IN NUMBER)
40
41 IS
42
43 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
44 BEGIN
45 OPEN x_phy_inv_lov FOR
46 SELECT physical_inventory_name,
47 physical_inventory_id,
48 description,
49 freeze_date,
50 adjustments_posted,
51 approval_required,
52 cost_variance_neg,
53 cost_variance_pos,
54 approval_tolerance_neg,
55 approval_tolerance_pos,
56 all_subinventories_flag,
57 dynamic_tag_entry_flag
58 FROM mtl_physical_inventories_v
59 WHERE organization_id = p_organization_id
60 AND snapshot_complete = 1
61 AND adjustments_posted <> 1
62 AND physical_inventory_name LIKE (p_phy_inv)
63 ORDER BY physical_inventory_name;
64 END get_phy_inv_lov;
65
66
67 -- Name: GET_SERIAL_COUNT_NUMBER
68 --
69 -- Input parameters:
70 -- p_physical_inventory_id Physical Inventory ID
71 -- p_organization_id Organization ID
72 -- p_serial_number Serial Number
73 -- p_inventory_item_id Inventory Item ID
74 --
75 -- Output parameters:
76 -- x_number Returns the serial count for the number
77 -- of physical tags with that particular
78 -- serial number that has already been counted
79 -- as present and existing in a given location.
80 -- x_serial_in_scope Returns 1 if the serial is within the scope
81 -- of the physical inventory. Otherwise it will
82 -- return 0.
83 --
84 -- Functions: This API returns the count of physical tag records
85 -- for the given serial number inputted.
86 -- It has also been overloaded so that it will also
87 -- check if the inputted serial is within the scope
88 -- of the physical inventory, i.e. exists in a subinventory
89 -- for which the physical inventory covers
90 --
91 PROCEDURE get_serial_count_number
92 (p_physical_inventory_id IN NUMBER ,
93 p_organization_id IN NUMBER ,
94 p_serial_number IN VARCHAR2 ,
95 p_inventory_item_id IN NUMBER ,
96 x_number OUT NOCOPY NUMBER ,
97 x_serial_in_scope OUT NOCOPY NUMBER)
98 IS
99 l_all_sub_flag NUMBER;
100 l_serial_sub VARCHAR2(10);
101
102 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
103 BEGIN
104 IF (l_debug = 1) THEN
105 print_debug('***Calling get_serial_count_number***');
106 END IF;
107 -- First get the serial count number to see if the serial has
108 -- already been found for this physical inventory
109 SELECT COUNT(*)
110 INTO x_number
111 FROM mtl_physical_inventory_tags
112 WHERE physical_inventory_id = p_physical_inventory_id
113 AND organization_id = p_organization_id
114 AND serial_num = p_serial_number
115 AND inventory_item_id = p_inventory_item_id
116 AND tag_quantity IS NOT NULL
117 AND tag_quantity <> 0
118 AND void_flag = 2
119 AND adjustment_id IN
120 (SELECT adjustment_id
121 FROM mtl_physical_adjustments
122 WHERE physical_inventory_id = p_physical_inventory_id
123 AND organization_id = p_organization_id
124 AND approval_status IS NULL);
125
126 -- Now see if the serial is within the scope of the physical
127 -- inventory
128 SELECT all_subinventories_flag
129 INTO l_all_sub_flag
130 FROM mtl_physical_inventories
131 WHERE physical_inventory_id = p_physical_inventory_id
132 AND organization_id = p_organization_id;
133 IF (l_debug = 1) THEN
134 print_debug('All subinventories flag: ' || l_all_sub_flag);
135 END IF;
136
137 IF (l_all_sub_flag = 1) THEN
138 -- All subinventories are included for this physical inventory
139 -- so the serial should be within the scope
140 x_serial_in_scope := 1;
141 ELSE
142 -- Get the current sub where the serial resides according to the system
143 SELECT NVL(current_subinventory_code, '@@@@@')
144 INTO l_serial_sub
145 FROM mtl_serial_numbers
146 WHERE inventory_item_id = p_inventory_item_id
147 AND serial_number = p_serial_number
148 AND current_organization_id = p_organization_id;
149 IF (l_debug = 1) THEN
150 print_debug('Current subinventory of serial: ' || l_serial_sub);
151 END IF;
152 -- See if the serial's subinventory is one of the subinventories
153 -- associated with the physical inventory
154 SELECT COUNT(*)
155 INTO x_serial_in_scope
156 FROM mtl_physical_subinventories
157 WHERE organization_id = p_organization_id
158 AND physical_inventory_id = p_physical_inventory_id
159 AND subinventory = l_serial_sub;
160 END IF;
161 IF (l_debug = 1) THEN
162 print_debug('Serial count number: ' || x_number);
163 print_debug('Serial in scope: ' || x_serial_in_scope);
164 END IF;
165
166 END get_serial_count_number;
167
168
169 PROCEDURE process_tag
170 (p_physical_inventory_id IN NUMBER,
171 p_organization_id IN NUMBER,
172 p_subinventory IN VARCHAR2,
173 p_locator_id IN NUMBER := NULL,
174 p_parent_lpn_id IN NUMBER := NULL,
175 p_inventory_item_id IN NUMBER,
176 p_revision IN VARCHAR2 := NULL,
177 p_lot_number IN VARCHAR2 := NULL,
178 p_from_serial_number IN VARCHAR2 := NULL,
179 p_to_serial_number IN VARCHAR2 := NULL,
180 p_tag_quantity IN NUMBER,
181 p_tag_uom IN VARCHAR2,
182 p_dynamic_tag_entry_flag IN NUMBER,
183 p_user_id IN NUMBER,
184 p_cost_group_id IN NUMBER := NULL
185 --INVCONV, NSRIVAST, START
186 ,p_tag_sec_uom IN VARCHAR2 := NULL
187 ,p_tag_sec_quantity IN NUMBER := NULL
188 --INVCONV, NSRIVAST, END
189 )
190 IS
191 l_current_serial VARCHAR2(30);
192 CURSOR tag_entry IS
193 SELECT *
194 FROM mtl_physical_inventory_tags
195 WHERE physical_inventory_id = p_physical_inventory_id
196 AND organization_id = p_organization_id
197 AND subinventory = p_subinventory
198 AND NVL(locator_id, -99999) = NVL(p_locator_id, -99999)
199 AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
200 AND inventory_item_id = p_inventory_item_id
201 AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
202 AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
203 AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
204 -- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
205 AND void_flag = 2
206 AND (adjustment_id IN
207 (SELECT adjustment_id
208 FROM mtl_physical_adjustments
209 WHERE physical_inventory_id = p_physical_inventory_id
210 AND organization_id = p_organization_id
211 AND approval_status IS NULL) OR adjustment_id IS NULL); --bug#9772069
212
213 CURSOR discrepant_serial_cursor IS
214 SELECT *
215 FROM mtl_physical_inventory_tags
216 WHERE physical_inventory_id = p_physical_inventory_id
217 AND organization_id = p_organization_id
218 AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
219 AND inventory_item_id = p_inventory_item_id
220 AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
221 AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
222 AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
223 -- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
224 AND void_flag = 2
225 AND (adjustment_id IN
226 (SELECT adjustment_id
227 FROM mtl_physical_adjustments
228 WHERE physical_inventory_id = p_physical_inventory_id
229 AND organization_id = p_organization_id
230 AND approval_status IS NULL) OR adjustment_id IS NULL); --bug#9772069
231
232 CURSOR tag_entry_wo_serial IS --bug#9772069
233 SELECT *
234 FROM mtl_physical_inventory_tags
235 WHERE physical_inventory_id = p_physical_inventory_id
236 AND organization_id = p_organization_id
237 AND subinventory = p_subinventory
238 AND NVL(locator_id, -99999) = NVL(p_locator_id, -99999)
239 AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
240 AND inventory_item_id = p_inventory_item_id
241 AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
242 AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
243 AND serial_num IS NULL --bug12799495
244 --AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
245 -- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
246 AND void_flag = 2
247 AND (adjustment_id IN
248 (SELECT adjustment_id
249 FROM mtl_physical_adjustments
250 WHERE physical_inventory_id = p_physical_inventory_id
251 AND organization_id = p_organization_id
252 AND approval_status IS NULL) OR adjustment_id IS NULL);
253
254 tag_record MTL_PHYSICAL_INVENTORY_TAGS%ROWTYPE;
255 l_prefix VARCHAR2(30);
256 l_quantity NUMBER;
257 l_from_number NUMBER;
258 l_to_number NUMBER;
259 l_errorcode NUMBER;
260 l_length NUMBER;
261 l_padded_length NUMBER;
262 l_current_number NUMBER;
263 l_adjustment_id NUMBER;
264 l_cost_group_id NUMBER;
265
266 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
267 BEGIN
268 IF (l_debug = 1) THEN
269 print_debug('***Calling process_tag with the following parameters***');
270 print_debug('p_physical_inventory_id: ---> ' || p_physical_inventory_id);
271 print_debug('p_organization_id: ---------> ' || p_organization_id);
272 print_debug('p_subinventory: ------------> ' || p_subinventory);
273 print_debug('p_locator_id: --------------> ' || p_locator_id);
274 print_debug('p_parent_lpn_id: -----------> ' || p_parent_lpn_id);
275 print_debug('p_inventory_item_id: -------> ' || p_inventory_item_id);
276 print_debug('p_revision: ----------------> ' || p_revision);
277 print_debug('p_lot_number: --------------> ' || p_lot_number);
278 print_debug('p_from_serial_number: ------> ' || p_from_serial_number);
279 print_debug('p_to_serial_number: --------> ' || p_to_serial_number);
280 print_debug('p_tag_quantity: ------------> ' || p_tag_quantity);
281 print_debug('p_tag_uom: -----------------> ' || p_tag_uom);
282 print_debug('p_dynamic_tag_entry_flag: --> ' || p_dynamic_tag_entry_flag);
283 print_debug('p_user_id: -----------------> ' || p_user_id);
284 print_debug('p_cost_group_id: -----------> ' || p_cost_group_id);
285 END IF;
286
287 -- First check if the tag item is a serial controlled item
288 IF ((p_from_serial_number IS NOT NULL) AND
289 (p_to_serial_number IS NOT NULL)) THEN
290 IF (l_debug = 1) THEN
291 print_debug('Serial controlled item');
292 END IF;
293
294 -- Call this API to parse the serial numbers into prefixes and numbers.
295 -- Only call this procedure if the from and to serial numbers differ
296 IF (p_from_serial_number <> p_to_serial_number) THEN
297 IF (NOT MTL_Serial_Check.inv_serial_info
298 ( p_from_serial_number => p_from_serial_number,
299 p_to_serial_number => p_to_serial_number,
300 x_prefix => l_prefix,
301 x_quantity => l_quantity,
302 x_from_number => l_from_number,
303 x_to_number => l_to_number,
304 x_errorcode => l_errorcode)) THEN
305 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SER');
306 FND_MSG_PUB.ADD;
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309 END IF;
310
311 -- Check that in the case of a range of serial numbers, that the
312 -- inputted p_tag_quantity equals the amount of items in the serial
313 -- range. Do this check only if a range of serials is submitted
314 IF (p_from_serial_number <> p_to_serial_number) THEN
315 IF (p_tag_quantity <> l_quantity) THEN
316 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_X_QTY');
317 FND_MSG_PUB.ADD;
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320 END IF;
321
322 -- Get the serial number length.
323 -- Note that the from and to serial numbers must be of the same length.
324 l_length := length(p_from_serial_number);
325
326 -- Initialize the current pointer variables
327 l_current_serial := p_from_serial_number;
328 IF (l_from_number IS NOT NULL) THEN
329 l_current_number := l_from_number;
330 ELSE
331 l_current_number := 0;
332 END IF;
333
334 LOOP
335 -- For each serial number check if a tag entry for it already
336 -- exists or not
337 OPEN tag_entry;
338 FETCH tag_entry INTO tag_record;
339 IF (tag_entry%FOUND) THEN
340 IF (l_debug = 1) THEN
341 print_debug('tag_entry record found.');
342 print_debug('tag_record.adjustment_id = '||tag_record.adjustment_id);
343 END IF;
344 -- Entry already exists so update the row
345 -- Check if an adjustment ID for this tag already exists or not
346 IF (tag_record.adjustment_id IS NULL) THEN
347 find_existing_adjustment
348 ( p_physical_inventory_id => p_physical_inventory_id,
349 p_organization_id => p_organization_id,
350 p_subinventory => p_subinventory,
351 p_locator_id => p_locator_id,
352 p_parent_lpn_id => p_parent_lpn_id,
353 p_inventory_item_id => p_inventory_item_id,
354 p_revision => p_revision,
355 p_lot_number => p_lot_number,
356 p_serial_number => l_current_serial,
357 p_user_id => p_user_id,
358 p_cost_group_id => tag_record.cost_group_id,
359 x_adjustment_id => l_adjustment_id
360 );
361 ELSE
362 l_adjustment_id := tag_record.adjustment_id;
363 END IF;
364 IF (l_debug = 1) THEN
365 print_debug('l_adjustment_id = '||l_adjustment_id);
366 END IF;
367 update_row( p_tag_id => tag_record.tag_id,
368 p_physical_inventory_id => p_physical_inventory_id,
369 p_organization_id => p_organization_id,
370 p_subinventory => p_subinventory,
371 p_locator_id => p_locator_id,
372 p_parent_lpn_id => p_parent_lpn_id,
373 p_inventory_item_id => p_inventory_item_id,
374 p_revision => p_revision,
375 p_lot_number => p_lot_number,
376 p_serial_number => l_current_serial,
377 p_tag_quantity => p_tag_quantity,
378 p_tag_uom => p_tag_uom,
379 p_user_id => p_user_id,
380 p_cost_group_id => tag_record.cost_group_id,
381 p_adjustment_id => l_adjustment_id
382 );
383 update_adjustment
384 (p_adjustment_id => l_adjustment_id,
385 p_physical_inventory_id => p_physical_inventory_id,
386 p_organization_id => p_organization_id,
387 p_user_id => p_user_id
388 );
389 ELSE
390 --Begin bug#9772069
391 OPEN tag_entry_wo_serial;
392 FETCH tag_entry_wo_serial INTO tag_record;
393 IF (tag_entry_wo_serial%FOUND) THEN
394 IF (l_debug = 1) THEN
395 print_debug('tag_entry_wo_serial record found.');
396 print_debug('tag_record.adjustment_id = '||tag_record.adjustment_id);
397 END IF;
398 IF (tag_record.adjustment_id IS NULL) THEN
399 find_existing_adjustment
400 ( p_physical_inventory_id => p_physical_inventory_id,
401 p_organization_id => p_organization_id,
402 p_subinventory => p_subinventory,
403 p_locator_id => p_locator_id,
404 p_parent_lpn_id => p_parent_lpn_id,
405 p_inventory_item_id => p_inventory_item_id,
406 p_revision => p_revision,
407 p_lot_number => p_lot_number,
408 p_serial_number => l_current_serial,
409 p_user_id => p_user_id,
410 p_cost_group_id => tag_record.cost_group_id,
411 x_adjustment_id => l_adjustment_id
412 );
413 ELSE
414 l_adjustment_id := tag_record.adjustment_id;
415 END IF;
416 IF (l_debug = 1) THEN
417 print_debug('l_adjustment_id = '||l_adjustment_id);
418 END IF;
419 update_row( p_tag_id => tag_record.tag_id,
420 p_physical_inventory_id => p_physical_inventory_id,
421 p_organization_id => p_organization_id,
422 p_subinventory => p_subinventory,
423 p_locator_id => p_locator_id,
424 p_parent_lpn_id => p_parent_lpn_id,
425 p_inventory_item_id => p_inventory_item_id,
426 p_revision => p_revision,
427 p_lot_number => p_lot_number,
428 p_serial_number => l_current_serial,
429 p_tag_quantity => p_tag_quantity,
430 p_tag_uom => p_tag_uom,
431 p_user_id => p_user_id,
432 p_cost_group_id => tag_record.cost_group_id,
433 p_adjustment_id => l_adjustment_id
434 );
435 update_adjustment
436 (p_adjustment_id => l_adjustment_id,
437 p_physical_inventory_id => p_physical_inventory_id,
438 p_organization_id => p_organization_id,
439 p_user_id => p_user_id
440 );
441
442 ELSE
443
444 --End bug#9772069
445 -- Entry does not exist so insert the row
446 IF (p_dynamic_tag_entry_flag = 1) THEN
447 IF (l_debug = 1) THEN
448 print_debug('Dynamic serial tag entry to be inserted');
449 END IF;
450 -- Dynamic tag entries are allowed
451
452 -- First check to see if a tag exists for this serial number
453 -- already if the serial is found in a discrepant location.
454 -- If a tag already exists, we want to update the tag and
455 -- adjustment record to signify that we have counted it
456 -- already, although it is considered as missing.
457 OPEN discrepant_serial_cursor;
458 FETCH discrepant_serial_cursor INTO tag_record;
459 IF (discrepant_serial_cursor%FOUND) THEN
460 -- Entry for discrepant serial exists so update the row
461 -- Check if an adjustment ID for this tag already exists or not
462 IF (l_debug = 1) THEN
463 print_debug('Discrepant serial so updating the original tag');
464 END IF;
465 IF (tag_record.adjustment_id IS NULL) THEN
466 find_existing_adjustment
467 ( p_physical_inventory_id => p_physical_inventory_id,
468 p_organization_id => p_organization_id,
469 p_subinventory => tag_record.subinventory,
470 p_locator_id => tag_record.locator_id,
471 p_parent_lpn_id => p_parent_lpn_id,
472 p_inventory_item_id => p_inventory_item_id,
473 p_revision => p_revision,
474 p_lot_number => p_lot_number,
475 p_serial_number => l_current_serial,
476 p_user_id => p_user_id,
477 p_cost_group_id => tag_record.cost_group_id,
478 x_adjustment_id => l_adjustment_id
479 );
480 ELSE
481 l_adjustment_id := tag_record.adjustment_id;
482 END IF;
483 update_row( p_tag_id => tag_record.tag_id,
484 p_physical_inventory_id => p_physical_inventory_id,
485 p_organization_id => p_organization_id,
486 p_subinventory => tag_record.subinventory,
487 p_locator_id => tag_record.locator_id,
488 p_parent_lpn_id => p_parent_lpn_id,
489 p_inventory_item_id => p_inventory_item_id,
490 p_revision => p_revision,
491 p_lot_number => p_lot_number,
492 p_serial_number => l_current_serial,
493 p_tag_quantity => 0,
494 p_tag_uom => p_tag_uom,
495 p_user_id => p_user_id,
496 p_cost_group_id => tag_record.cost_group_id,
497 p_adjustment_id => l_adjustment_id
498 );
499 update_adjustment
500 (p_adjustment_id => l_adjustment_id,
501 p_physical_inventory_id => p_physical_inventory_id,
502 p_organization_id => p_organization_id,
503 p_user_id => p_user_id
504 );
505 END IF;
506 CLOSE discrepant_serial_cursor;
507
508 -- Now deal with inserting the new dynamic tag entry
509 -- Get the cost group ID for this entry
510 inv_cyc_lovs.get_cost_group_id
511 (p_organization_id => p_organization_id,
512 p_subinventory => p_subinventory,
513 p_locator_id => p_locator_id,
514 p_parent_lpn_id => p_parent_lpn_id,
515 p_inventory_item_id => p_inventory_item_id,
516 p_revision => p_revision,
517 p_lot_number => p_lot_number,
518 p_serial_number => l_current_serial,
519 x_out => l_cost_group_id);
520 -- Bug# 2607187
521 -- Do not get the default cost group ID. If the item is
522 -- new and does not exist in onhand, pass a NULL value
523 -- for the cost group ID. The transaction manager will
524 -- call the cost group rules engine for that if the
525 -- cost group ID passed into MMTT is null.
526 IF (l_cost_group_id = -999) THEN
527 l_cost_group_id := NULL;
528 END IF;
529 -- Get the default cost group ID based on the given org
530 -- and sub if cost group ID was not retrieved successfully
531 /*IF (l_cost_group_id = -999) THEN
532 inv_cyc_lovs.get_default_cost_group_id
533 (p_organization_id => p_organization_id,
534 p_subinventory => p_subinventory,
535 x_out => l_cost_group_id);
536 END IF;
537 -- Default the cost group ID to 1 if nothing can be found
538 IF (l_cost_group_id = -999) THEN
539 l_cost_group_id := 1;
540 END IF;*/
541
542 -- Generate a new adjustment ID for this tag
543 find_existing_adjustment
544 ( p_physical_inventory_id => p_physical_inventory_id,
545 p_organization_id => p_organization_id,
546 p_subinventory => p_subinventory,
547 p_locator_id => p_locator_id,
548 p_parent_lpn_id => p_parent_lpn_id,
549 p_inventory_item_id => p_inventory_item_id,
550 p_revision => p_revision,
551 p_lot_number => p_lot_number,
552 p_serial_number => l_current_serial,
553 p_user_id => p_user_id,
554 p_cost_group_id => l_cost_group_id,
555 x_adjustment_id => l_adjustment_id
556 );
557 insert_row( p_physical_inventory_id => p_physical_inventory_id,
558 p_organization_id => p_organization_id,
559 p_subinventory => p_subinventory,
560 p_locator_id => p_locator_id,
561 p_parent_lpn_id => p_parent_lpn_id,
562 p_inventory_item_id => p_inventory_item_id,
563 p_revision => p_revision,
564 p_lot_number => p_lot_number,
565 p_serial_number => l_current_serial,
566 p_tag_quantity => p_tag_quantity,
567 p_tag_uom => p_tag_uom,
568 p_user_id => p_user_id,
569 p_cost_group_id => l_cost_group_id,
570 p_adjustment_id => l_adjustment_id
571 );
572 update_adjustment
573 (p_adjustment_id => l_adjustment_id,
574 p_physical_inventory_id => p_physical_inventory_id,
575 p_organization_id => p_organization_id,
576 p_user_id => p_user_id
577 );
578 ELSE
579 -- Dynamic tag entries are not allowed
580 -- This shouldn't happen if the mobile form's LOV
581 -- statements are correctly set
582 FND_MESSAGE.SET_NAME('INV','INV_NO_DYNAMIC_TAGS');
583 FND_MSG_PUB.ADD;
584 --RAISE FND_API.G_EXC_ERROR;
585 END IF;
586 END IF;
587 CLOSE tag_entry_wo_serial; --bug#9772069
588 END IF;
589 CLOSE tag_entry;
590
591 EXIT WHEN l_current_serial = p_to_serial_number;
592 -- Increment the current serial number if serial range inputted
593 IF (p_from_serial_number <> p_to_serial_number) THEN
594 l_current_number := l_current_number + 1;
595 l_padded_length := l_length - length(l_current_number);
596 l_current_serial := RPAD(l_prefix, l_padded_length, '0') ||
597 l_current_number;
598 END IF;
599 END LOOP;
600
601 ELSE -- Item is not serial controlled
602 IF (l_debug = 1) THEN
603 print_debug('Non-Serial controlled item');
604 END IF;
605 OPEN tag_entry;
606 FETCH tag_entry INTO tag_record;
607 IF (tag_entry%FOUND) THEN
608 -- Check if an adjustment ID for this tag already exists or not
609 IF (tag_record.adjustment_id IS NULL) THEN
610 find_existing_adjustment
611 ( p_physical_inventory_id => p_physical_inventory_id,
612 p_organization_id => p_organization_id,
613 p_subinventory => p_subinventory,
614 p_locator_id => p_locator_id,
615 p_parent_lpn_id => p_parent_lpn_id,
616 p_inventory_item_id => p_inventory_item_id,
617 p_revision => p_revision,
618 p_lot_number => p_lot_number,
619 p_serial_number => NULL,
620 p_user_id => p_user_id,
621 p_cost_group_id => tag_record.cost_group_id,
622 x_adjustment_id => l_adjustment_id
623 );
624 ELSE
625 l_adjustment_id := tag_record.adjustment_id;
626 END IF;
627 update_row( p_tag_id => tag_record.tag_id,
628 p_physical_inventory_id => p_physical_inventory_id,
629 p_organization_id => p_organization_id,
630 p_subinventory => p_subinventory,
631 p_locator_id => p_locator_id,
632 p_parent_lpn_id => p_parent_lpn_id,
633 p_inventory_item_id => p_inventory_item_id,
634 p_revision => p_revision,
635 p_lot_number => p_lot_number,
636 p_serial_number => NULL,
637 p_tag_quantity => p_tag_quantity,
638 p_tag_uom => p_tag_uom,
639 p_user_id => p_user_id,
640 p_cost_group_id => tag_record.cost_group_id,
641 p_adjustment_id => l_adjustment_id
642 ,p_tag_sec_quantity => p_tag_sec_quantity --INVCONV, NSRIVAST
643 );
644 update_adjustment
645 (p_adjustment_id => l_adjustment_id,
646 p_physical_inventory_id => p_physical_inventory_id,
647 p_organization_id => p_organization_id,
648 p_user_id => p_user_id
649 );
650 ELSE
651 IF (p_dynamic_tag_entry_flag = 1) THEN
652 IF (l_debug = 1) THEN
653 print_debug('Dynamic non-serial tag entry to be inserted');
654 END IF;
655 -- Dynamic tag entries are allowed
656
657 -- Get the cost group ID for this entry
658 inv_cyc_lovs.get_cost_group_id
659 (p_organization_id => p_organization_id,
660 p_subinventory => p_subinventory,
661 p_locator_id => p_locator_id,
662 p_parent_lpn_id => p_parent_lpn_id,
663 p_inventory_item_id => p_inventory_item_id,
664 p_revision => p_revision,
665 p_lot_number => p_lot_number,
666 p_serial_number => l_current_serial,
667 x_out => l_cost_group_id);
668 -- Bug# 2607187
669 -- Do not get the default cost group ID. If the item is
670 -- new and does not exist in onhand, pass a NULL value
671 -- for the cost group ID. The transaction manager will
672 -- call the cost group rules engine for that if the
673 -- cost group ID passed into MMTT is null.
674 IF (l_cost_group_id = -999) THEN
675 l_cost_group_id := NULL;
676 END IF;
677 -- Get the default cost group ID based on the given org
678 -- and sub if cost group ID was not retrieved successfully
679 /*IF (l_cost_group_id = -999) THEN
680 inv_cyc_lovs.get_default_cost_group_id
681 (p_organization_id => p_organization_id,
682 p_subinventory => p_subinventory,
683 x_out => l_cost_group_id);
684 END IF;
685 -- Default the cost group ID to 1 if nothing can be found
686 IF (l_cost_group_id = -999) THEN
687 l_cost_group_id := 1;
688 END IF;*/
689
690 -- Generate a new adjustment ID for this tag
691 find_existing_adjustment
692 ( p_physical_inventory_id => p_physical_inventory_id,
693 p_organization_id => p_organization_id,
694 p_subinventory => p_subinventory,
695 p_locator_id => p_locator_id,
696 p_parent_lpn_id => p_parent_lpn_id,
697 p_inventory_item_id => p_inventory_item_id,
698 p_revision => p_revision,
699 p_lot_number => p_lot_number,
700 p_serial_number => NULL,
701 p_user_id => p_user_id,
702 p_cost_group_id => l_cost_group_id,
703 x_adjustment_id => l_adjustment_id
704 );
705 insert_row( p_physical_inventory_id => p_physical_inventory_id,
706 p_organization_id => p_organization_id,
707 p_subinventory => p_subinventory,
708 p_locator_id => p_locator_id,
709 p_parent_lpn_id => p_parent_lpn_id,
710 p_inventory_item_id => p_inventory_item_id,
711 p_revision => p_revision,
712 p_lot_number => p_lot_number,
713 p_serial_number => NULL,
714 p_tag_quantity => p_tag_quantity,
715 p_tag_uom => p_tag_uom,
716 p_user_id => p_user_id,
717 p_cost_group_id => l_cost_group_id,
718 p_adjustment_id => l_adjustment_id
719 --INVCONV, NSRIVAST, START
720 ,p_tag_sec_quantity => p_tag_sec_quantity
721 ,p_tag_sec_uom => p_tag_sec_uom
722 --INVCONV, NSRIVAST, END
723
724 );
725 update_adjustment
726 (p_adjustment_id => l_adjustment_id,
727 p_physical_inventory_id => p_physical_inventory_id,
728 p_organization_id => p_organization_id,
729 p_user_id => p_user_id
730 );
731 ELSE
732 -- Dynamic tag entries are not allowed
733 -- This shouldn't happen if the mobile form's LOV
734 -- statements are correctly set
735 FND_MESSAGE.SET_NAME('INV','INV_NO_DYNAMIC_TAGS');
736 FND_MSG_PUB.ADD;
737 --RAISE FND_API.G_EXC_ERROR;
738 END IF;
739 END IF;
740 END IF;
741
742 -- Changes for Phy Inv ER - bug 13865417 , starts here
743 IF p_parent_lpn_id IS NOT NULL THEN
744 IF (l_debug = 1) THEN
745 print_debug('PI_ER.. Calling delete_duplicate_entries from process_tag>>> ');
746 END IF;
747 delete_duplicate_entries(
748 p_physical_inventory_id,
749 p_organization_id,
750 p_parent_lpn_id,
751 p_inventory_item_id,
752 p_revision,
753 p_lot_number,
754 p_from_serial_number,
755 l_adjustment_id
756 );
757
758 END IF;
759 -- Changes for Phy Inv ER - bug 13865417 , ends here
760
761 END process_tag;
762
763
764 PROCEDURE insert_row
765 (p_physical_inventory_id IN NUMBER,
766 p_organization_id IN NUMBER,
767 p_subinventory IN VARCHAR2,
768 p_locator_id IN NUMBER,
769 p_parent_lpn_id IN NUMBER,
770 p_inventory_item_id IN NUMBER,
771 p_revision IN VARCHAR2,
772 p_lot_number IN VARCHAR2,
773 p_serial_number IN VARCHAR2,
774 p_tag_quantity IN NUMBER,
775 p_tag_uom IN VARCHAR2,
776 p_user_id IN NUMBER,
777 p_cost_group_id IN NUMBER,
778 p_adjustment_id IN NUMBER
779 --INVCONV, NSRIVAST, START
780 ,p_tag_sec_quantity IN NUMBER := NULL
781 ,p_tag_sec_uom IN VARCHAR2 := NULL
782 --INVCONV, NSRIVAST, END
783 )
784 IS
785 l_tag_id NUMBER;
786 l_tag_number VARCHAR2(40);
787 l_next_tag_number VARCHAR2(40);
788 l_tag_qty_at_standard_uom NUMBER;
789 l_outermost_lpn_id NUMBER;
790 l_lot_expiration_date Date; /* Bug8199582 */
791 CURSOR tag_number_cursor IS
792 SELECT next_tag_number
793 FROM mtl_physical_inventories
794 WHERE physical_inventory_id = p_physical_inventory_id
795 AND organization_id = p_organization_id;
796 l_return_status VARCHAR2(300);
797 l_msg_count NUMBER;
798 l_msg_data VARCHAR2(300);
799 l_lpn_list WMS_Container_PUB.LPN_Table_Type;
800 l_temp_bool BOOLEAN;
801 l_prefix VARCHAR2(30);
802 l_quantity NUMBER;
803 l_from_number NUMBER;
804 l_to_number NUMBER;
805 l_errorcode NUMBER;
806 l_length NUMBER;
807 l_padded_length NUMBER;
808 l_item_standard_uom VARCHAR2(3);
809 l_employee_id NUMBER;
810
811 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
812 BEGIN
813 IF (l_debug = 1) THEN
814 print_debug('***insert_row***');
815 END IF;
816 -- Get the next tag ID for this new record
817 SELECT mtl_physical_inventory_tags_s.nextval
818 INTO l_tag_id
819 FROM dual;
820 IF (l_debug = 1) THEN
821 print_debug('Dynamic tag ID: ' || l_tag_id);
822 END IF;
823
824 -- Generate a new tag number for this record
825 OPEN tag_number_cursor;
826 FETCH tag_number_cursor INTO l_tag_number;
827 IF tag_number_cursor%NOTFOUND THEN
828 -- No value set for next_tag_number so manually
829 -- generate the next sequence value
830 SELECT MAX(tag_number)
831 INTO l_tag_number
832 FROM mtl_physical_inventory_tags
833 WHERE physical_inventory_id = p_physical_inventory_id
834 AND organization_id = p_organization_id;
835 -- Now parse the tag number and increment the numerical part
836 l_temp_bool := MTL_Serial_Check.inv_serial_info
837 ( p_from_serial_number => l_tag_number,
838 p_to_serial_number => NULL,
839 x_prefix => l_prefix,
840 x_quantity => l_quantity,
841 x_from_number => l_from_number,
842 x_to_number => l_to_number,
843 x_errorcode => l_errorcode);
844 l_length := length(l_tag_number);
845 l_from_number := l_from_number + 1;
846 l_padded_length := l_length - length(l_from_number);
847 l_tag_number := RPAD(l_prefix, l_padded_length, '0') ||
848 l_from_number;
849 END IF;
850 CLOSE tag_number_cursor;
851
852 -- Update the next_tag_number column in the physical inventories table
853 -- since we have just generated a new tag number value here
854 l_temp_bool := MTL_Serial_Check.inv_serial_info
855 ( p_from_serial_number => l_tag_number,
856 p_to_serial_number => NULL,
857 x_prefix => l_prefix,
858 x_quantity => l_quantity,
859 x_from_number => l_from_number,
860 x_to_number => l_to_number,
861 x_errorcode => l_errorcode);
862 l_length := length(l_tag_number);
863 l_from_number := l_from_number + 1;
864 l_padded_length := l_length - length(l_from_number);
865 l_next_tag_number := RPAD(NVL(l_prefix, '0'), l_padded_length, '0') ||
866 l_from_number;
867 UPDATE MTL_PHYSICAL_INVENTORIES
868 SET next_tag_number = l_next_tag_number
869 WHERE physical_inventory_id = p_physical_inventory_id
870 AND organization_id = p_organization_id;
871 IF (l_debug = 1) THEN
872 print_debug('Update physical inventory with next tag number: ' || l_next_tag_number);
873 END IF;
874
875 -- Calculate the tag quantity at standard uom
876 SELECT primary_uom_code
877 INTO l_item_standard_uom
878 FROM mtl_system_items
879 WHERE inventory_item_id = p_inventory_item_id
880 AND organization_id = p_organization_id;
881 -- I assume that the primary_uom_code is always given for an item
882 --bug 8526693 added lot no and org id parameters to invoke inv_convert to honor lot specific conversions
883 l_tag_qty_at_standard_uom := inv_convert.inv_um_convert
884 ( item_id => p_inventory_item_id,
885 lot_number => p_lot_number,
886 organization_id => p_organization_id,
887 precision => 5,
888 from_quantity => p_tag_quantity,
889 from_unit => p_tag_uom,
890 to_unit => l_item_standard_uom,
891 from_name => NULL,
892 to_name => NULL);
893 -- Conversion will return -99999 if unsuccessful so need to check for this
894 IF (l_tag_qty_at_standard_uom = -99999) THEN
895 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONVERSION');
896 FND_MSG_PUB.ADD;
897 RAISE FND_API.G_EXC_ERROR;
898 END IF;
899
900 -- Get the outermost LPN ID for this record if necessary
901 IF (p_parent_lpn_id IS NOT NULL) THEN
902 --Bug2935754 starts
903 /*
904 WMS_CONTAINER_PUB.GET_OUTERMOST_LPN
905 ( p_api_version => 1.0,
906 x_return_status => l_return_status,
907 x_msg_count => l_msg_count,
908 x_msg_data => l_msg_data,
909 p_lpn_id => p_parent_lpn_id,
910 x_lpn_list => l_lpn_list);
911 l_outermost_lpn_id := l_lpn_list(1).lpn_id;
912 */
913 BEGIN
914 SELECT outermost_lpn_id
915 INTO l_outermost_lpn_id
916 FROM WMS_LICENSE_PLATE_NUMBERS
917 WHERE lpn_id = p_parent_lpn_id;
918 EXCEPTION
919 WHEN OTHERS THEN
920 IF (l_debug = 1) THEN
921 print_debug('Unable to fetch outermost LPN for LPN ID: ' || p_parent_lpn_id);
922 END IF;
923 RAISE FND_API.G_EXC_ERROR;
924 END;
925 --Bug2935754 ends
926
927 IF (l_debug = 1) THEN
928 print_debug('LPN ID passed in so get the outermost LPN: ' || l_outermost_lpn_id);
929 END IF;
930 END IF;
931
932 print_debug('Deriving the employee id based on user ID: ' || p_user_id);
933
934 --Bug 6600166, raising error if user is not a valid employee
935 -- Bug 8717415, Commented out the join with organization id and selecting DISTINCT employee id,
936 -- to allow employees across business group to enter the physical inventory tag count depending
937 -- upon the 'HR:Cross Business Group' profile value.
938
939 l_employee_id := g_employee_id; --bug 11881386
940 if ( p_user_id <> g_user_id) then --bug 11881386
941
942 BEGIN
943 --Bug 12365935 starts
944
945 SELECT DISTINCT(fus.employee_id)
946 INTO l_employee_id
947 FROM PER_WORKFORCE_CURRENT_X mec, fnd_user fus
948 WHERE fus.user_id = p_user_id
949 AND mec.person_id = fus.employee_id
950 AND rownum = 1;
951
952 --Bug 12365935 ends
953
954 g_user_id := p_user_id; --bug 11881386
955 g_employee_id := l_employee_id; --bug 11881386
956
957 EXCEPTION
958 WHEN OTHERS THEN
959 FND_MESSAGE.SET_NAME('INV', 'INV_EMP');
960 FND_MSG_PUB.ADD;
961 RAISE FND_API.G_EXC_ERROR;
962 END;
963 end if;
964 print_debug('the employee id is: ' || l_employee_id);
965
966 /* Select clause Added for Bug8199582 */
967 BEGIN
968
969 Select expiration_date
970 into l_lot_expiration_date
971 from mtl_lot_numbers
972 where lot_number = p_lot_number
973 and inventory_item_id = p_inventory_item_id
974 and organization_id= p_organization_id
975 and expiration_date is not null;
976
977 EXCEPTION
978 WHEN NO_DATA_FOUND THEN
979 l_lot_expiration_date := null ;
980 END ;
981
982 -- Insert the new record
983 IF (l_debug = 1) THEN
984 print_debug('Inserting the new record here');
985 END IF;
986 INSERT INTO MTL_PHYSICAL_INVENTORY_TAGS
987 (tag_id,
988 physical_inventory_id,
989 organization_id,
990 last_update_date,
991 last_updated_by,
992 creation_date,
993 created_by,
994 last_update_login,
995 void_flag,
996 tag_number,
997 adjustment_id,
998 inventory_item_id,
999 tag_quantity,
1000 tag_uom,
1001 tag_quantity_at_standard_uom,
1002 standard_uom,
1003 subinventory,
1004 locator_id,
1005 lot_number,
1006 revision,
1007 serial_num,
1008 counted_by_employee_id,
1009 parent_lpn_id,
1010 outermost_lpn_id,
1011 cost_group_id
1012 --INVCONV, NSRIVAST, START
1013 ,tag_secondary_uom
1014 ,tag_secondary_quantity
1015 ,LOT_EXPIRATION_DATE -- -- Inserting Expiration Date , Bug8199582
1016 --INVCONV, NSRIVAST, END
1017 ) VALUES
1018 (l_tag_id,
1019 p_physical_inventory_id,
1020 p_organization_id,
1021 SYSDATE,
1022 p_user_id,
1023 SYSDATE,
1024 p_user_id,
1025 p_user_id,
1026 2,
1027 l_tag_number,
1028 p_adjustment_id,
1029 p_inventory_item_id,
1030 p_tag_quantity,
1031 p_tag_uom,
1032 l_tag_qty_at_standard_uom,
1033 l_item_standard_uom,
1034 p_subinventory,
1035 p_locator_id,
1036 p_lot_number,
1037 p_revision,
1038 p_serial_number,
1039 l_employee_id,
1040 p_parent_lpn_id,
1041 l_outermost_lpn_id,
1042 p_cost_group_id
1043 --INVCONV, NSRIVAST, START
1044 ,p_tag_sec_uom
1045 ,p_tag_sec_quantity
1046 ,l_lot_expiration_date -- Inserting Expiration Date , Bug8199582
1047 --INVCONV, NSRIVAST, END
1048 );
1049
1050 EXCEPTION
1051 WHEN fnd_api.g_exc_error THEN
1052 raise fnd_api.g_exc_error;
1053 WHEN OTHERS THEN
1054 print_debug(SQLERRM);
1055 raise fnd_api.g_exc_unexpected_error;
1056
1057 END insert_row;
1058
1059
1060 PROCEDURE update_row
1061 (p_tag_id IN NUMBER,
1062 p_physical_inventory_id IN NUMBER,
1063 p_organization_id IN NUMBER,
1064 p_subinventory IN VARCHAR2,
1065 p_locator_id IN NUMBER,
1066 p_parent_lpn_id IN NUMBER,
1067 p_inventory_item_id IN NUMBER,
1068 p_revision IN VARCHAR2,
1069 p_lot_number IN VARCHAR2,
1070 p_serial_number IN VARCHAR2,
1071 p_tag_quantity IN NUMBER,
1072 p_tag_uom IN VARCHAR2,
1073 p_user_id IN NUMBER,
1074 p_cost_group_id IN NUMBER,
1075 p_adjustment_id IN NUMBER
1076 ,p_tag_sec_quantity IN NUMBER := NULL --INVCONV, NSRIVAST, START
1077 )
1078 IS
1079 l_tag_qty_at_standard_uom NUMBER;
1080 l_outermost_lpn_id NUMBER;
1081 l_item_standard_uom VARCHAR2(3);
1082 l_return_status VARCHAR2(300);
1083 l_msg_count NUMBER;
1084 l_msg_data VARCHAR2(300);
1085 l_lpn_list WMS_Container_PUB.LPN_Table_Type;
1086 l_employee_id NUMBER;
1087
1088 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1089 BEGIN
1090 IF (l_debug = 1) THEN
1091 print_debug('***update_row***');
1092 END IF;
1093 -- Calculate the tag quantity at standard uom
1094 SELECT primary_uom_code
1095 INTO l_item_standard_uom
1096 FROM mtl_system_items
1097 WHERE inventory_item_id = p_inventory_item_id
1098 AND organization_id = p_organization_id;
1099 -- I assume that the primary_uom_code is always given for an item
1100 --bug 8526693 added lot no and org id parameters to invoke inv_convert to honor lot specific conversions
1101 l_tag_qty_at_standard_uom := inv_convert.inv_um_convert
1102 ( item_id => p_inventory_item_id,
1103 lot_number => p_lot_number,
1104 organization_id => p_organization_id,
1105 precision => 5,
1106 from_quantity => p_tag_quantity,
1107 from_unit => p_tag_uom,
1108 to_unit => l_item_standard_uom,
1109 from_name => NULL,
1110 to_name => NULL);
1111 -- Conversion will return -99999 if unsuccessful so need to check for this
1112 IF (l_tag_qty_at_standard_uom = -99999) THEN
1113 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONVERSION');
1114 FND_MSG_PUB.ADD;
1115 RAISE FND_API.G_EXC_ERROR;
1116 END IF;
1117
1118 -- Get the outermost LPN ID for this record if necessary
1119 IF (p_parent_lpn_id IS NOT NULL) THEN
1120 --Bug2935754 starts
1121 /*
1122 WMS_CONTAINER_PUB.GET_OUTERMOST_LPN
1123 ( p_api_version => 1.0,
1124 x_return_status => l_return_status,
1125 x_msg_count => l_msg_count,
1126 x_msg_data => l_msg_data,
1127 p_lpn_id => p_parent_lpn_id,
1128 x_lpn_list => l_lpn_list);
1129 l_outermost_lpn_id := l_lpn_list(1).lpn_id;
1130 */
1131 BEGIN
1132 SELECT outermost_lpn_id
1133 INTO l_outermost_lpn_id
1134 FROM WMS_LICENSE_PLATE_NUMBERS
1135 WHERE lpn_id = p_parent_lpn_id;
1136 EXCEPTION
1137 WHEN OTHERS THEN
1138 IF (l_debug = 1) THEN
1139 print_debug('Unable to fetch outermost LPN for LPN ID: ' || p_parent_lpn_id);
1140 END IF;
1141 RAISE FND_API.G_EXC_ERROR;
1142 END;
1143 --Bug2935754 ends
1144
1145 IF (l_debug = 1) THEN
1146 print_debug('LPN ID passed in so get the outermost LPN: ' || l_outermost_lpn_id);
1147 END IF;
1148 END IF;
1149
1150 print_debug('Deriving the employee id based on user ID: ' || p_user_id);
1151
1152 --Bug 6600166, raising error if user is not a valid employee
1153 -- Bug 8717415, Commented out the join with organization id and selecting DISTINCT employee id,
1154 -- to allow employees across business group to enter the physical inventory tag count depending
1155 -- upon the 'HR:Cross Business Group' profile value.
1156
1157 l_employee_id := g_employee_id; --bug 11881386
1158 if ( p_user_id <> g_user_id) then --bug 11881386
1159 BEGIN
1160 --Bug12365935 starts
1161
1162 SELECT DISTINCT(fus.employee_id)
1163 INTO l_employee_id
1164 FROM PER_WORKFORCE_CURRENT_X mec, fnd_user fus
1165 WHERE fus.user_id = p_user_id
1166 AND mec.person_id = fus.employee_id
1167 AND rownum = 1;
1168
1169 --Bug12365935 ends
1170
1171 g_user_id := p_user_id; --bug 11881386
1172 g_employee_id := l_employee_id; --bug 11881386
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 FND_MESSAGE.SET_NAME('INV', 'INV_EMP');
1176 FND_MSG_PUB.ADD;
1177 RAISE FND_API.G_EXC_ERROR;
1178 END;
1179 end if;
1180 print_debug('the employee id is: ' || l_employee_id);
1181
1182 -- Update the record
1183 IF (l_debug = 1) THEN
1184 print_debug('Updating the physical inventory tag record for tag ID: ' || p_tag_id);
1185 END IF;
1186 UPDATE MTL_PHYSICAL_INVENTORY_TAGS
1187 SET
1188 last_update_date = SYSDATE,
1189 last_updated_by = p_user_id,
1190 last_update_login = p_user_id,
1191 adjustment_id = p_adjustment_id,
1192 inventory_item_id = p_inventory_item_id,
1193 tag_quantity = p_tag_quantity,
1194 tag_uom = p_tag_uom,
1195 tag_quantity_at_standard_uom = l_tag_qty_at_standard_uom,
1196 standard_uom = l_item_standard_uom,
1197 subinventory = p_subinventory,
1198 locator_id = p_locator_id,
1199 lot_number = p_lot_number,
1200 revision = p_revision,
1201 serial_num = p_serial_number,
1202 counted_by_employee_id = l_employee_id,
1203 parent_lpn_id = p_parent_lpn_id,
1204 outermost_lpn_id = l_outermost_lpn_id,
1205 cost_group_id = p_cost_group_id
1206 ,tag_secondary_quantity = p_tag_sec_quantity --INVCONV, NSRIVAST, START
1207 WHERE tag_id = p_tag_id;
1208
1209 IF (SQL%NOTFOUND) THEN
1210 RAISE NO_DATA_FOUND;
1211 END IF;
1212
1213 END update_row;
1214
1215
1216 PROCEDURE update_adjustment
1217 (p_adjustment_id IN NUMBER,
1218 p_physical_inventory_id IN NUMBER,
1219 p_organization_id IN NUMBER,
1220 p_user_id IN NUMBER
1221 )
1222 IS
1223 l_adj_count_quantity NUMBER;
1224 l_adj2_count_quantity NUMBER; -- Fix for Bug#7591655
1225 -- Variables needed for calling the label printing API
1226 l_inventory_item_id NUMBER;
1227 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
1228 l_lot_number VARCHAR2(80);
1229 l_serial_number VARCHAR2(30);
1230 l_lpn_id NUMBER;
1231 l_subinventory VARCHAR2(10);
1232 l_locator_id NUMBER;
1233 l_adjustment_quantity NUMBER;
1234 l_standard_uom_code VARCHAR2(3);
1235 l_label_status VARCHAR2(300) := NULL;
1236 l_return_status VARCHAR2(3000);
1237 l_msg_count NUMBER;
1238 l_msg_data VARCHAR2(3000);
1239
1240 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1241 BEGIN
1242 IF (l_debug = 1) THEN
1243 print_debug('***update_adjustment***');
1244 END IF;
1245
1246 /* Fix for Bug#7591655. Added tag_secondary_quantity in following select. Secondary qty is always at
1247 secondary uom. Hence no need of tag_secondary_at_standard_uom */
1248
1249 SELECT NVL(SUM(tag_quantity_at_standard_uom),0),
1250 NVL(SUM(tag_secondary_quantity),0)
1251 INTO l_adj_count_quantity,
1252 l_adj2_count_quantity
1253 FROM mtl_physical_inventory_tags
1254 WHERE adjustment_id = p_adjustment_id
1255 AND organization_id = p_organization_id
1256 AND physical_inventory_id = p_physical_inventory_id
1257 AND void_flag = 2;
1258
1259 IF (l_debug = 1) THEN
1260 print_debug('Updating the physical adjustment record for adjustment ID: ' || p_adjustment_id);
1261 END IF;
1262 /* Fix for Bug#7591655 . Added secondary_count_qty and secondary_adjustment_qty in following update */
1263 UPDATE mtl_physical_adjustments
1264 SET last_update_date = SYSDATE,
1265 last_updated_by = NVL(p_user_id, -1),
1266 count_quantity = l_adj_count_quantity,
1267 adjustment_quantity = NVL(l_adj_count_quantity, NVL(system_quantity,0))
1268 - NVL(system_quantity,0),
1269 secondary_count_qty = l_adj2_count_quantity,
1270 secondary_adjustment_qty = NVL(l_adj2_count_quantity, NVL(secondary_system_qty,0))
1271 - NVL(secondary_system_qty,0),
1272 approval_status = NULL,
1273 approved_by_employee_id = NULL
1274 WHERE adjustment_id = p_adjustment_id
1275 AND physical_inventory_id = p_physical_inventory_id
1276 AND organization_id = p_organization_id;
1277
1278 -- Get the adjustment record values needed for label printing
1279 IF (l_debug = 1) THEN
1280 print_debug('Get the adjustment record values for label printing');
1281 END IF;
1282 SELECT inventory_item_id, lot_number, serial_number, parent_lpn_id,
1283 subinventory_name, locator_id, NVL(adjustment_quantity, 0)
1284 INTO l_inventory_item_id, l_lot_number, l_serial_number,
1285 l_lpn_id, l_subinventory, l_locator_id, l_adjustment_quantity
1286 FROM mtl_physical_adjustments
1287 WHERE adjustment_id = p_adjustment_id
1288 AND physical_inventory_id = p_physical_inventory_id
1289 AND organization_id = p_organization_id;
1290
1291 -- Get the primary UOM for the inventory item
1292 IF (l_debug = 1) THEN
1293 print_debug('Get the primary UOM code: ' || 'Item ID: ' || l_inventory_item_id || ': ' || 'Org ID: ' || p_organization_id);
1294 END IF;
1295 SELECT primary_uom_code
1296 INTO l_standard_uom_code
1297 FROM mtl_system_items
1298 WHERE inventory_item_id = l_inventory_item_id
1299 AND organization_id = p_organization_id;
1300
1301 -- Call the label printing API if an adjustment is required
1302 IF (l_debug = 1) THEN
1303 print_debug('Adjustment quantity: ' || l_adjustment_quantity);
1304 END IF;
1305 IF (l_adjustment_quantity <> 0) THEN
1306 IF (l_debug = 1) THEN
1307 print_debug('Calling print_label_manual_wrap with the following input parameters');
1308 print_debug('p_business_flow_code: -> ' || 9);
1309 END IF;
1310 --print_debug('p_label_type: ---------> ' || 1);
1311 IF (l_debug = 1) THEN
1312 print_debug('p_organization_id: ----> ' || p_organization_id);
1313 print_debug('p_inventory_item_id: --> ' || l_inventory_item_id);
1314 print_debug('p_lot_number: ---------> ' || l_lot_number);
1315 print_debug('p_fm_serial_number: ---> ' || l_serial_number);
1316 print_debug('p_to_serial_number: ---> ' || l_serial_number);
1317 print_debug('p_lpn_id: -------------> ' || l_lpn_id);
1318 print_debug('p_subinventory_code: --> ' || l_subinventory);
1319 print_debug('p_locator_id: ---------> ' || l_locator_id);
1320 print_debug('p_quantity: -----------> ' || l_adjustment_quantity);
1321 print_debug('p_uom: ----------------> ' || l_standard_uom_code);
1322 print_debug('p_no_of_copies: -------> ' || 1);
1323 END IF;
1324
1325 -- Bug# 2301732
1326 -- Make the call to the label printing API more robust
1327 -- by trapping for exceptions when calling it
1328 -- Bug# 2412674
1329 -- Don't pass in the value for the label type
1330 BEGIN
1331 inv_label.print_label_manual_wrap
1332 ( x_return_status => l_return_status ,
1333 x_msg_count => l_msg_count ,
1334 x_msg_data => l_msg_data ,
1335 x_label_status => l_label_status ,
1336 p_business_flow_code => 9 ,
1337 --p_label_type => 1 ,
1338 p_organization_id => p_organization_id ,
1339 p_inventory_item_id => l_inventory_item_id ,
1340 p_lot_number => l_lot_number ,
1341 p_fm_serial_number => l_serial_number ,
1342 p_to_serial_number => l_serial_number ,
1343 p_lpn_id => l_lpn_id ,
1344 p_subinventory_code => l_subinventory ,
1345 p_locator_id => l_locator_id ,
1346 p_quantity => l_adjustment_quantity ,
1347 p_uom => l_standard_uom_code ,
1348 p_no_of_copies => 1
1349 );
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 IF (l_debug = 1) THEN
1353 print_debug('Error while calling label printing API');
1354 END IF;
1355 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_PRINT_LABEL_FAILE');
1356 FND_MSG_PUB.ADD;
1357 END;
1358 IF (l_debug = 1) THEN
1359 print_debug('After calling label printing API: ' || l_return_status || ', ' || l_label_status || ', ' || l_msg_data);
1360 END IF;
1361
1362 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1363 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_PRINT_LABEL_FAILE');
1364 FND_MSG_PUB.ADD;
1365 END IF;
1366
1367 END IF;
1368
1369 END update_adjustment;
1370
1371
1372
1373 PROCEDURE find_existing_adjustment
1374 (p_physical_inventory_id IN NUMBER,
1375 p_organization_id IN NUMBER,
1376 p_subinventory IN VARCHAR2,
1377 p_locator_id IN NUMBER,
1378 p_parent_lpn_id IN NUMBER,
1379 p_inventory_item_id IN NUMBER,
1380 p_revision IN VARCHAR2,
1381 p_lot_number IN VARCHAR2,
1382 p_serial_number IN VARCHAR2,
1383 p_user_id IN NUMBER,
1384 p_cost_group_id IN NUMBER,
1385 x_adjustment_id OUT NOCOPY NUMBER
1386 )
1387 IS
1388 l_rev_code NUMBER;
1389 l_org_locator_type NUMBER;
1390 l_sub_locator_type NUMBER;
1391 l_location_control_code NUMBER;
1392 l_lot_control_code NUMBER;
1393 l_serial_control_code NUMBER;
1394 l_adj_id NUMBER:= -1;
1395 l_actual_cost NUMBER;
1396 l_outermost_lpn_id NUMBER;
1397 l_return_status VARCHAR2(300);
1398 l_msg_count NUMBER;
1399 l_msg_data VARCHAR2(300);
1400 l_lpn_list WMS_Container_PUB.LPN_Table_Type;
1401 l_approval_status NUMBER:= -1;
1402 l_lot_expiration_date Date; /* Added by 8199582 */
1403 l_process_enabled_flag VARCHAR2(1);
1404
1405 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1406 BEGIN
1407 IF (l_debug = 1) THEN
1408 print_debug('***find_existing_adjustment***');
1409 END IF;
1410 -- Get the required information for the local variables
1411 -- Locator control type for the org
1412 SELECT stock_locator_control_code
1413 INTO l_org_locator_type
1414 FROM mtl_parameters
1415 WHERE organization_id = p_organization_id;
1416
1417 -- Locator control type for the sub
1418 SELECT locator_type
1419 INTO l_sub_locator_type
1420 FROM mtl_secondary_inventories
1421 WHERE secondary_inventory_name = p_subinventory
1422 AND organization_id = p_organization_id;
1423
1424 -- Locator control type for the item plus revision, lot, and serial
1425 -- control codes
1426 SELECT revision_qty_control_code, location_control_code,
1427 lot_control_code, serial_number_control_code
1428 INTO l_rev_code, l_location_control_code,
1429 l_lot_control_code, l_serial_control_code
1430 FROM mtl_system_items
1431 WHERE inventory_item_id = p_inventory_item_id
1432 AND organization_id = p_organization_id;
1433
1434 -- Get the adjustment ID if it is existing
1435 IF (l_debug = 1) THEN
1436 print_debug('Try to find the adjustment ID if it exists');
1437 END IF;
1438 SELECT MIN(ADJUSTMENT_ID)
1439 INTO l_adj_id
1440 FROM MTL_PHYSICAL_ADJUSTMENTS
1441 WHERE ORGANIZATION_ID = p_organization_id
1442 AND PHYSICAL_INVENTORY_ID = p_physical_inventory_id
1443 AND INVENTORY_ITEM_ID = p_inventory_item_id
1444 AND SUBINVENTORY_NAME = p_subinventory
1445 AND ( NVL(REVISION,'@@@@@') = NVL(p_revision,'@@@@@')
1446 OR l_rev_code = 1 )
1447 AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999) --Bug 6929248 Posted adjustments should not be allowed to enter tags
1448 AND (approval_status=3 OR NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999))
1449 AND (NVL(LOCATOR_ID, -99999) = NVL(p_locator_id, -99999)
1450 OR l_org_locator_type = 1
1451 OR (l_org_locator_type = 4
1452 AND (l_sub_locator_type = 1
1453 OR (l_sub_locator_type = 5
1454 AND l_location_control_code = 1)))
1455 OR (l_location_control_code = 5
1456 AND l_location_control_code = 1))
1457 AND ( NVL(LOT_NUMBER,'@@@@@') = NVL(p_lot_number,'@@@@@')
1458 OR l_lot_control_code = 1 )
1459 AND ( NVL(SERIAL_NUMBER,'@@@@@') = NVL(p_serial_number,'@@@@@')
1460 OR l_serial_control_code = 1 )
1461 GROUP BY ORGANIZATION_ID,
1462 PHYSICAL_INVENTORY_ID,
1463 INVENTORY_ITEM_ID,
1464 SUBINVENTORY_NAME,
1465 REVISION,
1466 LOCATOR_ID,
1467 PARENT_LPN_ID,
1468 COST_GROUP_ID,
1469 LOT_NUMBER,
1470 SERIAL_NUMBER;
1471
1472 /* Bug 4350316, if the corresponding adjustment is posted, not allowing the user to enter a dynamic tag*/
1473
1474 IF l_adj_id IS NOT NULL THEN
1475 select approval_status
1476 into l_approval_status
1477 from mtl_physical_adjustments
1478 where adjustment_id = l_adj_id
1479 and physical_inventory_id = p_physical_inventory_id;
1480
1481 if (nvl(l_approval_status,0) = 3) then
1482 print_debug('Error: The corresponding adjustment_id '||l_adj_id||' is already posted');
1483 fnd_message.set_name('INV','INV_PHYSICAL_ADJ_POSTED');
1484 fnd_message.set_token('TOKEN1', l_adj_id);
1485 fnd_msg_pub.add;
1486 raise fnd_api.g_exc_error;
1487 end if;
1488 END IF;
1489
1490
1491 x_adjustment_id := l_adj_id;
1492
1493 EXCEPTION
1494 WHEN NO_DATA_FOUND THEN
1495 -- insert new adjustment row
1496 IF (l_debug = 1) THEN
1497 print_debug('No adjustment record found so insert a new one');
1498 END IF;
1499
1500 -- Get the actual cost of the item in the current tag
1501 IF (l_debug = 1) THEN
1502 print_debug('Get the actual cost of the item');
1503 END IF;
1504
1505 /* Bug# 2942493
1506 ** Instead of duplicating the code, reusing the common utility to
1507 ** to get the item cost. That way its easier to maintain.
1508 */
1509 -- Bug 9046942
1510 -- for OPM get the item cost from OPM costing tables.
1511
1512 SELECT NVL(process_enabled_flag, 'N')
1513 INTO l_process_enabled_flag
1514 FROM mtl_parameters
1515 WHERE organization_id = p_organization_id;
1516
1517 IF (l_debug = 1) THEN
1518 print_debug('Process enabled flag : ' || l_process_enabled_flag);
1519 print_debug('p_inventory_item_id : ' || p_inventory_item_id);
1520 print_debug('p_organization_id : ' || p_organization_id);
1521 print_debug('p_locator_id : ' || p_locator_id);
1522 END IF;
1523
1524 IF (l_process_enabled_flag = 'Y') THEN
1525 -- get opm cost for the item
1526 --
1527 l_actual_cost := gmf_cmcommon.process_item_unit_cost (p_inventory_item_id,p_organization_id,SYSDATE);
1528 IF (l_debug = 1) THEN
1529 print_debug('OPM cost: ' || l_actual_cost);
1530 END IF;
1531
1532 ELSE
1533 INV_UTILITIES.GET_ITEM_COST(
1534 v_org_id => p_organization_id,
1535 v_item_id => p_inventory_item_id,
1536 v_locator_id => p_locator_id,
1537 v_item_cost => l_actual_cost);
1538
1539 IF (l_actual_cost = -999) THEN
1540 l_actual_cost := 0;
1541 END IF;
1542 END IF;
1543
1544 -- Get a valid adjustment ID for the new record
1545 SELECT mtl_physical_adjustments_s.NEXTVAL
1546 INTO l_adj_id
1547 FROM dual;
1548 IF (l_debug = 1) THEN
1549 print_debug('New adjustment ID: ' || l_adj_id);
1550 END IF;
1551
1552 -- Get the outermost LPN ID if necessary
1553 IF (p_parent_lpn_id IS NOT NULL) THEN
1554 --Bug2935754 starts
1555 /*
1556 WMS_CONTAINER_PUB.GET_OUTERMOST_LPN
1557 ( p_api_version => 1.0,
1558 x_return_status => l_return_status,
1559 x_msg_count => l_msg_count,
1560 x_msg_data => l_msg_data,
1561 p_lpn_id => p_parent_lpn_id,
1562 x_lpn_list => l_lpn_list);
1563 l_outermost_lpn_id := l_lpn_list(1).lpn_id;
1564 */
1565
1566 BEGIN
1567 SELECT outermost_lpn_id
1568 INTO l_outermost_lpn_id
1569 FROM WMS_LICENSE_PLATE_NUMBERS
1570 WHERE lpn_id = p_parent_lpn_id;
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573 IF (l_debug = 1) THEN
1574 print_debug('Unable to fetch outermost LPN for LPN ID: ' || p_parent_lpn_id);
1575 END IF;
1576 RAISE FND_API.G_EXC_ERROR;
1577 END;
1578 --Bug2935754 ends
1579
1580 IF (l_debug = 1) THEN
1581 print_debug('LPN ID passed in so get the outermost LPN: ' || l_outermost_lpn_id);
1582 END IF;
1583 END IF;
1584
1585 -- Insert the new adjustment record
1586 IF (l_debug = 1) THEN
1587 print_debug('Get the expire date for the lot');
1588 END IF;
1589
1590 /* Select clause Added for Bug8199582 */
1591 BEGIN
1592
1593 Select expiration_date
1594 into l_lot_expiration_date
1595 from mtl_lot_numbers
1596 where lot_number = p_lot_number
1597 and inventory_item_id = p_inventory_item_id
1598 and organization_id = p_organization_id
1599 and expiration_date is not null;
1600
1601 EXCEPTION
1602 WHEN NO_DATA_FOUND THEN
1603 l_lot_expiration_date := null ;
1604 END ;
1605
1606 IF (l_debug = 1) THEN
1607 print_debug('Inserting the new physical adjustment record');
1608 END IF;
1609
1610 /* Fix for Bug#7591655. Added secondary_count_qty and secondary_adjustment_qty in insert */
1611
1612 INSERT INTO mtl_physical_adjustments
1613 ( adjustment_id,
1614 organization_id,
1615 physical_inventory_id,
1616 inventory_item_id,
1617 subinventory_name,
1618 system_quantity,
1619 last_update_date,
1620 last_updated_by,
1621 creation_date,
1622 created_by,
1623 last_update_login,
1624 count_quantity,
1625 adjustment_quantity,
1626 revision,
1627 locator_id,
1628 parent_lpn_id,
1629 outermost_lpn_id,
1630 cost_group_id,
1631 lot_number,
1632 serial_number,
1633 actual_cost ,
1634 secondary_count_qty,
1635 secondary_adjustment_qty,
1636 lot_expiration_date ) /* Inserting Expiration Date , Bug8199582 */
1637 VALUES ( l_adj_id,
1638 p_organization_id,
1639 p_physical_inventory_id,
1640 p_inventory_item_id,
1641 p_subinventory,
1642 0,
1643 SYSDATE,
1644 p_user_id,
1645 SYSDATE,
1646 p_user_id,
1647 p_user_id,
1648 0,
1649 0,
1650 p_revision,
1651 p_locator_id,
1652 p_parent_lpn_id,
1653 l_outermost_lpn_id,
1654 p_cost_group_id,
1655 p_lot_number,
1656 p_serial_number,
1657 l_actual_cost,
1658 0,
1659 0,
1660 l_lot_expiration_date); /* Inserting Expiration Date , Bug8199582 */
1661
1662 x_adjustment_id := l_adj_id;
1663
1664 END find_existing_adjustment;
1665
1666
1667
1668 PROCEDURE process_summary
1669 (p_physical_inventory_id IN NUMBER,
1670 p_organization_id IN NUMBER,
1671 p_subinventory IN VARCHAR2,
1672 p_locator_id IN NUMBER := NULL,
1673 p_parent_lpn_id IN NUMBER := NULL,
1674 p_dynamic_tag_entry_flag IN NUMBER,
1675 p_user_id IN NUMBER
1676 )
1677 IS
1678 l_current_lpn NUMBER;
1679 l_temp_uom_code VARCHAR2(3);
1680 CURSOR nested_lpn_cursor IS
1681 SELECT *
1682 FROM WMS_LICENSE_PLATE_NUMBERS
1683 START WITH lpn_id = p_parent_lpn_id
1684 CONNECT BY parent_lpn_id = PRIOR lpn_id;
1685 CURSOR lpn_contents_cursor IS
1686 SELECT *
1687 FROM WMS_LPN_CONTENTS
1688 WHERE parent_lpn_id = l_current_lpn
1689 AND NVL(serial_summary_entry, 2) = 2;
1690 CURSOR lpn_serial_contents_cursor IS
1691 SELECT *
1692 FROM MTL_SERIAL_NUMBERS
1693 WHERE lpn_id = l_current_lpn;
1694
1695 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1696 BEGIN
1697 IF (l_debug = 1) THEN
1698 print_debug('***process_summary***');
1699 END IF;
1700 -- Use the cursor that searches through all levels in the parent child relationship
1701 FOR v_lpn_id IN nested_lpn_cursor LOOP
1702 l_current_lpn := v_lpn_id.lpn_id;
1703
1704 -- Process the tag for the LPN item itself if it is associated with
1705 -- an inventory item
1706 IF (v_lpn_id.inventory_item_id IS NOT NULL) THEN
1707 -- Get the primary UOM for the container inventory item
1708 SELECT primary_uom_code
1709 INTO l_temp_uom_code
1710 FROM mtl_system_items
1711 WHERE inventory_item_id = v_lpn_id.inventory_item_id
1712 AND organization_id = v_lpn_id.organization_id;
1713
1714 IF (l_debug = 1) THEN
1715 print_debug('Counting an LPN');
1716 END IF;
1717 process_tag
1718 (p_physical_inventory_id => p_physical_inventory_id,
1719 p_organization_id => p_organization_id,
1720 p_subinventory => p_subinventory,
1721 p_locator_id => p_locator_id,
1722 p_parent_lpn_id => v_lpn_id.parent_lpn_id,
1723 p_inventory_item_id => v_lpn_id.inventory_item_id,
1724 p_revision => v_lpn_id.revision,
1725 p_lot_number => v_lpn_id.lot_number,
1726 p_from_serial_number => v_lpn_id.serial_number,
1727 p_to_serial_number => v_lpn_id.serial_number,
1728 p_tag_quantity => 1,
1729 p_tag_uom => l_temp_uom_code,
1730 p_dynamic_tag_entry_flag => p_dynamic_tag_entry_flag,
1731 p_user_id => p_user_id,
1732 p_cost_group_id => v_lpn_id.cost_group_id
1733 );
1734 END IF;
1735
1736 -- Process the tags for the LPN content items
1737 FOR v_lpn_content IN lpn_contents_cursor LOOP
1738
1739 IF (l_debug = 1) THEN
1740 print_debug('Counting an LPN content item');
1741 END IF;
1742 process_tag
1743 (p_physical_inventory_id => p_physical_inventory_id,
1744 p_organization_id => p_organization_id,
1745 p_subinventory => p_subinventory,
1746 p_locator_id => p_locator_id,
1747 p_parent_lpn_id => v_lpn_content.parent_lpn_id,
1748 p_inventory_item_id => v_lpn_content.inventory_item_id,
1749 p_revision => v_lpn_content.revision,
1750 p_lot_number => v_lpn_content.lot_number,
1751 p_from_serial_number => NULL,
1752 p_to_serial_number => NULL,
1753 p_tag_quantity => v_lpn_content.quantity,
1754 p_tag_uom => v_lpn_content.uom_code,
1755 p_dynamic_tag_entry_flag => p_dynamic_tag_entry_flag,
1756 p_user_id => p_user_id,
1757 p_cost_group_id => v_lpn_content.cost_group_id,
1758 p_tag_sec_uom => v_lpn_content.secondary_uom_code, -- Added for Bug 13101781
1759 p_tag_sec_quantity => v_lpn_content.secondary_quantity -- Added for Bug 13101781
1760 );
1761
1762 END LOOP;
1763
1764 -- Process the tags for serialized items
1765 FOR v_lpn_serial_content IN lpn_serial_contents_cursor LOOP
1766 -- Get the primary UOM for the serialized item
1767 SELECT primary_uom_code
1768 INTO l_temp_uom_code
1769 FROM mtl_system_items
1770 WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
1771 AND organization_id = v_lpn_serial_content.current_organization_id;
1772
1773 IF (l_debug = 1) THEN
1774 print_debug('Counting an LPN serial controlled item');
1775 END IF;
1776 process_tag
1777 (p_physical_inventory_id => p_physical_inventory_id,
1778 p_organization_id => p_organization_id,
1779 p_subinventory => p_subinventory,
1780 p_locator_id => p_locator_id,
1781 p_parent_lpn_id => v_lpn_serial_content.lpn_id,
1782 p_inventory_item_id => v_lpn_serial_content.inventory_item_id,
1783 p_revision => v_lpn_serial_content.revision,
1784 p_lot_number => v_lpn_serial_content.lot_number,
1785 p_from_serial_number => v_lpn_serial_content.serial_number,
1786 p_to_serial_number => v_lpn_serial_content.serial_number,
1787 p_tag_quantity => 1,
1788 p_tag_uom => l_temp_uom_code,
1789 p_dynamic_tag_entry_flag => p_dynamic_tag_entry_flag,
1790 p_user_id => p_user_id,
1791 p_cost_group_id => v_lpn_serial_content.cost_group_id
1792 );
1793
1794 END LOOP;
1795
1796 END LOOP;
1797
1798 END process_summary;
1799
1800 --Fix for bug #4654210
1801 PROCEDURE unmark_serials
1802 (p_physical_inventory_id IN NUMBER,
1803 p_organization_id IN NUMBER,
1804 p_item_id IN NUMBER,
1805 x_status OUT NOCOPY NUMBER
1806 )
1807 IS
1808 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1809 BEGIN
1810 IF (l_debug = 1) THEN
1811 print_debug('***unmark_serials***');
1812 END IF;
1813
1814 IF (p_physical_inventory_id IS NULL) THEN
1815 IF (l_debug = 1) THEN
1816 print_debug('Physical Inventory Id is not provided. Returning from the Procedure');
1817 END IF;
1818 x_status := -1;
1819 ELSIF (p_organization_id IS NULL) THEN
1820 IF (l_debug = 1) THEN
1821 print_debug('Organization Id is not provided. Returning from the Procedure');
1822 END IF;
1823 x_status := -1;
1824 ELSIF (p_item_id IS NULL) THEN
1825 IF (l_debug = 1) THEN
1826 print_debug('Inventory Item Id is not provided. Returning from the Procedure');
1827 END IF;
1828 x_status := -1;
1829 ELSE
1830
1831 UPDATE mtl_serial_numbers
1832 SET group_mark_id = -1
1833 WHERE inventory_item_id = p_item_id
1834 AND serial_number in
1835 (SELECT DISTINCT serial_num
1836 FROM mtl_physical_inventory_tags
1837 WHERE organization_id = p_organization_id
1838 AND physical_inventory_id = p_physical_inventory_id
1839 AND inventory_item_id = p_item_id
1840 AND serial_num is not null
1841 )
1842 AND nvl(group_mark_id,-1) <> -1;
1843
1844 IF (l_debug = 1) THEN
1845 print_debug('Updated ' || SQL%ROWCOUNT || ' Records in mtl_serial_numbers for the inventory_item_id ' || p_item_id);
1846 print_debug('*** end unmark_serials***');
1847 END IF;
1848
1849 x_status := 0;
1850 END IF;
1851
1852 END unmark_serials;
1853
1854 --End of Fix for bug # 4654210
1855
1856 -- Fix for 5660272 to get the serial uniquiness type or a given organization
1857 PROCEDURE GET_SERIAL_NUMBER_TYPE
1858 ( x_serial_number_type OUT NOCOPY NUMBER,
1859 p_organization_id IN NUMBER
1860 )
1861 IS
1862 BEGIN
1863 SELECT NVL(SERIAL_NUMBER_TYPE, 0)
1864 INTO x_serial_number_type
1865 FROM MTL_PARAMETERS
1866 WHERE ORGANIZATION_ID = p_organization_id;
1867 EXCEPTION
1868 WHEN NO_DATA_FOUND THEN
1869 x_serial_number_type :=0;
1870 END;
1871 --End of Fix for bug # 5660272
1872 -- Fix for 5660272 to check for the validity of the serial number --
1873 PROCEDURE VALIDATE_SERIAL_STATUS
1874 ( x_status OUT NOCOPY NUMBER, -- 1 FOR SUCCESS AND ANY OTHER VALUE FOR FAILURE
1875 x_organization_code OUT NOCOPY VARCHAR2,
1876 x_current_status OUT NOCOPY VARCHAR2,
1877 p_serial_num IN VARCHAR2,
1878 p_organization_id IN NUMBER,
1879 p_subinventory_code IN VARCHAR2,
1880 p_locator_id IN NUMBER,
1881 p_inventory_item_id IN NUMBER,
1882 p_serial_number_type IN NUMBER
1883 )
1884 IS
1885 l_valid_serial NUMBER := 0;
1886 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1887 BEGIN
1888 x_status := 0;
1889 IF (l_debug = 1) THEN
1890 print_debug('Start Validating the serial for the current status');
1891 END IF;
1892 l_valid_serial := 0;
1893
1894 /*
1895 * Checking if the serial number exist with same serial number in the same subinventory
1896 * organization and locator for the same item.
1897 * Bug# 6354645:
1898 * Added the condition to check for inventory_item_id as the same serial number can be assigned
1899 * to 2 different items in the same org/sub/locator if the serial uniqueness is 'Within Inventory Items'
1900 * This is to avoid TOO_MANY_ROWS_FOUND exception.
1901 */
1902 /*Bug7829724-Commeneted locator*/
1903 BEGIN
1904 SELECT 1,MP.ORGANIZATION_CODE,ML.MEANING
1905 INTO l_valid_serial,x_organization_code,x_current_status
1906 FROM MTL_SERIAL_NUMBERS MSN , MTL_PARAMETERS MP, MFG_LOOKUPS ML
1907 WHERE SERIAL_NUMBER like p_serial_num
1908 AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
1909 AND MSN.CURRENT_ORGANIZATION_ID = p_organization_id
1910 AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1911 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1912 -- AND MSN.CURRENT_SUBINVENTORY_CODE like p_subinventory_code /*Bug14778466-Commeneted subinv*/
1913 -- AND NVL(MSN.CURRENT_LOCATOR_ID,-9999) = NVL(p_locator_id, -9999)
1914 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1915 AND CURRENT_STATUS = 3;
1916 EXCEPTION
1917 WHEN NO_DATA_FOUND THEN
1918 l_valid_serial :=0;
1919 END;
1920
1921 -- If the serial number type is 0. Invalid serial Control Option, so error out. Very rare case.
1922 IF p_serial_number_type = 0 THEN
1923 x_status := -1;
1924 RETURN;
1925 END IF;
1926
1927 --Changed the return value from -1 to 1. Because this will not be a error case. for bug 5903566
1928 IF l_valid_serial = 1 THEN
1929 -- Serial Number is present in the org/subinv/loc given by user. Hence return Success.
1930 x_status := 1;
1931 RETURN;
1932
1933 -- New Serial Given here. So check if it violates the serial Number Uniqueness logic.
1934 ELSIF l_valid_serial = 0 THEN
1935 IF p_serial_number_type = 3 THEN
1936 /*
1937 * If serial uniquiness is accross organizations (3)
1938 * serial should not exist anywhere
1939 */
1940 SELECT MP.ORGANIZATION_CODE,ML.MEANING
1941 INTO x_organization_code,x_current_status
1942 FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1943 WHERE SERIAL_NUMBER like p_serial_num
1944 AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1945 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1946 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1947 AND CURRENT_STATUS NOT IN (1,4);
1948 ELSIF p_serial_number_type IN (1,4) THEN
1949 /*
1950 * If serial uniquiness is within inventory (1,4)
1951 * serial should not exist in same org, same item
1952 */
1953 SELECT MP.ORGANIZATION_CODE,ML.MEANING
1954 INTO x_organization_code,x_current_status
1955 FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1956 WHERE MSN.SERIAL_NUMBER like p_serial_num
1957 AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
1958 AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1959 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1960 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1961 AND CURRENT_STATUS NOT IN (1,4);
1962 ELSIF p_serial_number_type = 2 THEN
1963 /*
1964 * If serial uniquiness is within organization (2)
1965 * serial should first be unique within inventory items and
1966 * then within organizations.
1967 * Added the below condition because it could be the case that the
1968 * same serial could be assigned to the same item in a different org.
1969 */
1970 BEGIN
1971
1972 SELECT MP.ORGANIZATION_CODE,ML.MEANING
1973 INTO x_organization_code,x_current_status
1974 FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1975 WHERE MSN.SERIAL_NUMBER like p_serial_num
1976 AND MSN.INVENTORY_ITEM_ID = p_inventory_item_id
1977 AND MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
1978 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1979 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
1980 AND CURRENT_STATUS NOT IN (1,4);
1981
1982 x_status := -1;
1983 RETURN;
1984 EXCEPTION
1985 WHEN NO_DATA_FOUND THEN
1986 NULL;-- SETTING NULL HERE TO EXECUTE THE SECOND QUERY.
1987 END;
1988 /*
1989 * If serial uniquiness is within organization (2)
1990 * serial should not exist in same org
1991 */
1992 SELECT MP.ORGANIZATION_CODE,ML.MEANING
1993 INTO x_organization_code,x_current_status
1994 FROM MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
1995 WHERE MSN.SERIAL_NUMBER like p_serial_num
1996 AND MSN.CURRENT_ORGANIZATION_ID = p_organization_id
1997 AND MSN.CURRENT_ORGANIZATION_ID = MP. ORGANIZATION_ID
1998 AND MSN.CURRENT_STATUS = ML.LOOKUP_CODE
1999 AND ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
2000 AND CURRENT_STATUS NOT IN (1,4);
2001
2002 END IF;
2003 END IF;
2004 x_status := -1;
2005 EXCEPTION
2006 WHEN NO_DATA_FOUND THEN -- No Data Found means the given new serial doesnt violate any of the serial uniqueness conditions.
2007 IF (l_debug = 1) THEN
2008 print_debug('Serial Number status is valid so go ahaead');
2009 END IF;
2010 x_status := 1;
2011 END VALIDATE_SERIAL_STATUS;
2012
2013 --End of Fix for bug # 5660272
2014
2015
2016 /* Added the procedure DELETE_DUPLICATE_ENTRIES for Phy Inv ER - bug 13865417
2017 Itis called from procedure PROCESS_TAG while PI Count using Mobile apps, and it is called in INVADPTE.pld file when the PI Count using FORMS Apps .
2018 */
2019 PROCEDURE delete_duplicate_entries(
2020 p_physical_inventory_id IN NUMBER,
2021 p_organization_id IN NUMBER,
2022 p_parent_lpn_id IN NUMBER,
2023 p_inventory_item_id IN NUMBER,
2024 p_revision IN VARCHAR2,
2025 p_lot_number IN VARCHAR2,
2026 p_serial_number IN VARCHAR2,
2027 p_adjustment_id IN NUMBER
2028 )
2029 IS
2030 l_adjustment_id NUMBER;
2031 l_new_tag VARCHAR2(1);
2032 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2033 /* Added below variables for bug 15926209, start */
2034 l_subinv MTL_PHYSICAL_ADJUSTMENTS.SUBINVENTORY_NAME%TYPE;
2035 l_locator NUMBER;
2036 l_update BOOLEAN;
2037 l_sys_qty NUMBER;
2038 l_sec_sys_qty NUMBER;
2039 /* For bug 15926209, end */
2040
2041 BEGIN
2042
2043 IF (l_debug = 1) THEN
2044 print_debug('PI_ER..*** Start delete_duplicate_entries ***');
2045 print_debug('PI_ER.. p_physical_inventory_id -->>> '|| p_physical_inventory_id);
2046 print_debug('PI_ER.. p_organization_id -------->>> '|| p_organization_id);
2047 print_debug('PI_ER.. p_parent_lpn_id ---------->>> '|| p_parent_lpn_id);
2048 print_debug('PI_ER.. p_inventory_item_id ------>>> '|| p_inventory_item_id);
2049 print_debug('PI_ER.. p_revision --------------->>> '|| p_revision);
2050 print_debug('PI_ER.. p_lot_number ------------->>> '|| p_lot_number);
2051 print_debug('PI_ER.. p_serial_number ---------->>> '|| p_serial_number);
2052 print_debug('PI_ER.. p_adjustment_id ---------->>> '|| p_adjustment_id);
2053 END IF;
2054
2055 -- check if any of below params are null then exit from api
2056 IF (p_parent_lpn_id IS NULL OR p_adjustment_id IS NULL OR p_physical_inventory_id IS NULL OR p_inventory_item_id IS NULL) THEN
2057 RETURN;
2058 END IF;
2059
2060 BEGIN
2061 -- check if the adjustment_Id passed is a New Tag , otherwise exit from api
2062 SELECT 'Y', subinventory_name, locator_id INTO l_new_tag, l_subinv, l_locator -- added subinv, locator for bug 15926209
2063 FROM MTL_PHYSICAL_ADJUSTMENTS
2064 WHERE adjustment_id = p_adjustment_id
2065 AND approval_status IS NULL
2066 AND system_quantity = 0;
2067 --AND (count_quantity = adjustment_quantity OR count_quantity > 0);
2068
2069 EXCEPTION
2070 WHEN No_data_found THEN
2071 IF (l_debug = 1) THEN
2072 print_debug('PI_ER.. Adjustment/Tag is already existing, Hence no Adj is deleted >>> ');
2073 END IF;
2074 RETURN;
2075 END;
2076
2077 BEGIN
2078 -- Get the old adjustment id
2079 SELECT NULLIF(MIN(adjustment_id),MAX(adjustment_id)) INTO l_adjustment_id
2080 FROM mtl_physical_adjustments
2081 WHERE organization_id = p_organization_id
2082 AND physical_inventory_id = p_physical_inventory_id
2083 AND inventory_item_id = p_inventory_item_id
2084 AND parent_lpn_id = p_parent_lpn_id
2085 AND nvl(approval_status,0) <> 3
2086 AND NVL(revision,'@@@@') = NVL(p_revision,'@@@@')
2087 AND NVL(lot_number,'@@@@') = NVL(p_lot_number,'@@@@')
2088 AND NVL(serial_number,'@@@@') = NVL(p_serial_number,'@@@@')
2089 AND adjustment_id IN (SELECT adjustment_id
2090 FROM mtl_physical_inventory_tags
2091 WHERE organization_id = p_organization_id
2092 AND physical_inventory_id = p_physical_inventory_id
2093 AND inventory_item_id = p_inventory_item_id
2094 AND parent_lpn_id = p_parent_lpn_id
2095 AND NVL(revision,'@@@@') = NVL(p_revision,'@@@@')
2096 AND NVL(lot_number,'@@@@') = NVL(p_lot_number,'@@@@')
2097 AND NVL(serial_num,'@@@@') = NVL(p_serial_number,'@@@@')
2098 AND void_flag = 2);
2099
2100 EXCEPTION
2101 WHEN NO_DATA_FOUND THEN
2102 IF (l_debug = 1) THEN
2103 print_debug('PI_ER.. Could not find old Tag Adj id >>>');
2104 END IF;
2105 RETURN;
2106 END;
2107
2108 IF (l_debug = 1) THEN
2109 print_debug('Got the old adjustment id >>> '|| l_adjustment_id);
2110 END IF;
2111
2112 IF l_adjustment_id IS NOT NULL THEN
2113
2114 /* For bug 15926209, start */
2115 BEGIN
2116
2117 IF p_serial_number IS NULL
2118 THEN
2119 SELECT quantity, secondary_quantity INTO l_sys_qty, l_sec_sys_qty
2120 FROM wms_license_plate_numbers wlpn, wms_lpn_contents wlc
2121 WHERE wlpn.lpn_id = wlc.parent_lpn_id
2122 AND wlpn.lpn_id = p_parent_lpn_id
2123 AND wlpn.subinventory_code = l_subinv
2124 AND wlpn.locator_id = l_locator
2125 AND Nvl(wlc.lot_number,'@@@') = Nvl(p_lot_number,'@@@')
2126 AND Nvl(wlc.revision,'@@@') = Nvl(p_revision,'@@@')
2127 AND wlc.inventory_item_id = p_inventory_item_id
2128 AND wlc.organization_id = p_organization_id;
2129
2130 ELSIF p_serial_number IS NOT NULL
2131 THEN
2132
2133 SELECT 1 INTO l_sys_qty
2134 FROM wms_license_plate_numbers wlpn
2135 WHERE wlpn.lpn_id = p_parent_lpn_id
2136 AND wlpn.subinventory_code = l_subinv
2137 AND wlpn.locator_id = l_locator;
2138
2139 l_sec_sys_qty := 0;
2140
2141 END IF;
2142
2143 l_update := true;
2144
2145 EXCEPTION
2146 WHEN OTHERS THEN
2147 l_update := false;
2148 END;
2149
2150 IF l_update THEN
2151 UPDATE mtl_physical_adjustments
2152 SET system_quantity = l_sys_qty,
2153 adjustment_quantity = Nvl(count_quantity,0) - Nvl(l_sys_qty,0),
2154 secondary_system_qty = l_sec_sys_qty,
2155 secondary_adjustment_qty = nvl(secondary_count_qty,0) - nvl(l_sec_sys_qty,0)
2156 WHERE adjustment_id = p_adjustment_id;
2157 END IF;
2158 /* For bug 15926209, end */
2159
2160 DELETE FROM mtl_physical_inventory_tags
2161 WHERE adjustment_id = l_adjustment_id;
2162
2163 IF (l_debug = 1) THEN
2164 print_debug('PI_ER.. Count of deleted recs for mtl_physical_inventory_tags >>> '||SQL%ROWCOUNT||' for LPN Id, Adj Id '||p_parent_lpn_id ||' , '||l_adjustment_id);
2165 END IF;
2166
2167 DELETE FROM mtl_physical_adjustments
2168 WHERE adjustment_id = l_adjustment_id;
2169
2170 IF (l_debug = 1) THEN
2171 print_debug('PI_ER.. Count of deleted recs for mtl_physical_adjustments >>> '||SQL%ROWCOUNT||' for LPN Id, Adj Id '||p_parent_lpn_id ||' , '||l_adjustment_id);
2172 END IF;
2173 END IF;
2174
2175 IF (l_debug = 1) THEN
2176 print_debug('PI_ER..*** End delete_duplicate_entries ***');
2177 END IF;
2178
2179 EXCEPTION
2180 WHEN OTHERS THEN
2181 IF (l_debug = 1) THEN
2182 print_debug('PI_ER.. Exception occur while delete_duplicate_entries >>> '||SUBSTR(SQLERRM,1,100)||' for LPN '||p_parent_lpn_id);
2183 END IF;
2184
2185 END delete_duplicate_entries;
2186
2187 /* Added the procedure PROCESS_PHY_INV_SUBXFER for Phy Inv ER - bug 13865417
2188 It is called from INCAPA.opp file for Physical Inventory Sub Transfer work.
2189 */
2190 PROCEDURE Process_Phy_Inv_SubXfer(
2191 p_organization_id IN NUMBER,
2192 p_physical_inv_id IN NUMBER,
2193 p_txn_header_id IN NUMBER,
2194 p_gl_acct_id IN NUMBER,
2195 p_request_id IN NUMBER,
2196 p_program_app_id IN NUMBER,
2197 p_program_id IN NUMBER,
2198 x_proc_status OUT NOCOPY NUMBER,
2199 x_record_count OUT NOCOPY NUMBER,
2200 p_txn_date IN VARCHAR2 -- BUG 16188610
2201 )
2202 IS
2203
2204 CURSOR c_physical_xfer (p_org_id NUMBER, p_phy_inv_id NUMBER ) IS
2205 SELECT mpa_xfr.physical_inventory_id,
2206 mpa_xfr.adjustment_id xfr_adjustment_id,
2207 mpa_sys.locator_id from_locator,
2208 mpa_xfr.locator_id to_locator,
2209 mpa_xfr.subinventory_name xfr_subinventory_name,
2210 mpa_sys.system_quantity,
2211 mpa_xfr.count_quantity,
2212 (Nvl(mpa_xfr.count_quantity,0) - nvl(mpa_sys.system_quantity,0)) adjustment_quantity,
2213 mpa_xfr.secondary_count_qty,
2214 (Nvl(mpa_xfr.secondary_count_qty,0) - nvl(mpa_sys.secondary_system_qty,0)) secondary_adjustment_qty,
2215 mpa_sys.inventory_item_id,
2216 mpa_sys.organization_id,
2217 mpa_sys.subinventory_name,
2218 mpa_sys.cost_group_id,
2219 mpa_sys.parent_lpn_id,
2220 mpa_sys.lot_number,
2221 mpa_sys.lot_expiration_date,
2222 mpa_sys.serial_number,
2223 mpa_sys.outermost_lpn_id,
2224 mpa_sys.revision
2225 FROM (SELECT wms.lpn_id parent_lpn_id,
2226 wms.outermost_lpn_id,
2227 wms.organization_id,
2228 wms.inventory_item_id,
2229 wms.subinventory_name,
2230 wms.locator_id,
2231 wms.lot_number,
2232 msn.serial_number,
2233 wms.revision,
2234 wms.cost_group_id,
2235 mln.expiration_date lot_expiration_date,
2236 Decode (wms.serial_summary_entry, 1, 1, wms.primary_quantity) system_quantity,
2237 Decode (wms.serial_summary_entry, 1, 0, wms.secondary_quantity) secondary_system_qty --bug 14778466
2238 FROM (SELECT DISTINCT wlp.lpn_id,
2239 wlp.outermost_lpn_id,
2240 wlp.organization_id,
2241 wlc.inventory_item_id,
2242 wlp.subinventory_code subinventory_name,
2243 wlp.locator_id,
2244 wlc.lot_number,
2245 wlc.primary_quantity,
2246 wlc.secondary_quantity,
2247 wlc.serial_summary_entry,
2248 wlc.revision,
2249 wlc.cost_group_id
2250 FROM wms_license_plate_numbers wlp, mtl_physical_adjustments mpa, wms_lpn_contents wlc
2251 WHERE wlp.lpn_id = wlc.parent_lpn_id
2252 AND mpa.parent_lpn_id = wlp.lpn_id
2253 AND mpa.organization_id = p_org_id
2254 AND mpa.physical_inventory_id = p_phy_inv_id
2255 AND ( mpa.subinventory_name <> wlp.subinventory_code OR mpa.locator_id <> wlp.locator_id )
2256 AND Nvl(mpa.count_quantity, 0) <> 0
2257 AND mpa.approval_status = 1
2258 AND mpa.parent_lpn_id IS NOT NULL
2259 AND mpa.adjustment_id IN (SELECT adjustment_id FROM mtl_physical_inventory_tags
2260 WHERE organization_id = p_org_id
2261 AND physical_inventory_id = p_phy_inv_id AND void_flag = 2)
2262
2263 ) wms,
2264 mtl_serial_numbers msn,
2265 mtl_lot_numbers mln
2266 WHERE wms.organization_id = msn.current_organization_id (+)
2267 AND wms.inventory_item_id = msn.inventory_item_id (+)
2268 AND wms.subinventory_name = msn.current_subinventory_code(+)
2269 AND wms.locator_id = msn.current_locator_id(+)
2270 --modified for bug 14778466
2271 AND wms.lpn_id = msn.lpn_id (+)
2272 AND wms.organization_id = mln.organization_id (+)
2273 AND wms.inventory_item_id = mln.inventory_item_id (+)
2274 AND Nvl(wms.cost_group_id,-999) = Nvl(msn.cost_group_id(+),-999)
2275 AND Nvl(wms.revision,'@#@#@') = Nvl(msn.revision(+),'@#@#@')
2276 AND Nvl(wms.lot_number,'@#@#@') = Nvl(msn.lot_number(+),'@#@#@')
2277 AND Nvl(wms.lot_number,'@#@#@') = Nvl(mln.lot_number(+),'@#@#@')
2278 ) mpa_sys,
2279 (SELECT mpa.*
2280 FROM mtl_physical_adjustments mpa, mtl_parameters mp
2281 WHERE mpa.organization_id= mp.organization_id
2282 and mpa.organization_id = p_org_id
2283 AND physical_inventory_id = p_phy_inv_id
2284 AND system_quantity = 0
2285 AND nvl(count_quantity,0) <> 0
2286 AND approval_status = 1
2287 AND parent_lpn_id IS NOT NULL
2288 AND adjustment_id IN (SELECT adjustment_id FROM mtl_physical_inventory_tags
2289 WHERE organization_id = p_org_id
2290 AND physical_inventory_id = p_phy_inv_id
2291 AND void_flag=2)
2292 ) mpa_xfr
2293 WHERE mpa_sys.organization_id = mpa_xfr.organization_id(+)
2294 AND mpa_sys.inventory_item_id = mpa_xfr.inventory_item_id(+)
2295 AND mpa_sys.parent_lpn_id = mpa_xfr.parent_lpn_id(+)
2296 AND mpa_sys.outermost_lpn_id = mpa_xfr.outermost_lpn_id(+)
2297 AND Nvl(mpa_sys.cost_group_id,-999) = Nvl(mpa_xfr.cost_group_id(+),-999) -- bug 14778466
2298 AND Nvl(mpa_sys.serial_number, '@@@') = Nvl(mpa_xfr.serial_number(+), '@@@')
2299 AND Nvl(mpa_sys.lot_number, '@@@') = Nvl(mpa_xfr.lot_number(+), '@@@')
2300 AND Nvl(mpa_sys.revision, '@@@') = Nvl(mpa_xfr.revision(+), '@@@')
2301 AND mpa_sys.locator_id <> mpa_xfr.locator_id(+)
2302 ORDER BY mpa_sys.outermost_lpn_id, mpa_sys.parent_lpn_id,mpa_xfr.subinventory_name,mpa_sys.inventory_item_id,mpa_sys.lot_number, mpa_xfr.adjustment_id;
2303
2304 l_transaction_header_id NUMBER := p_txn_header_id;
2305 l_transaction_temp_id NUMBER;
2306 l_transaction_reference mtl_material_transactions_temp.transaction_reference%TYPE;
2307 l_uom_code mtl_system_items.primary_uom_code%TYPE;
2308 l_sec_uom_code mtl_system_items.secondary_uom_code%TYPE;
2309 l_debug NUMBER := Nvl(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2310 l_proc_name VARCHAR2(20) := 'PHY_INV_SUB_XFER ';
2311
2312 l_period_id NUMBER;
2313 l_open_past_period BOOLEAN := FALSE;
2314 l_profile_value NUMBER := 0;
2315 l_txn_date DATE := fnd_date.canonical_to_date(p_txn_date); -- BUG 16188610
2316
2317 l_txn_action_id NUMBER := 2;
2318 l_txn_type_id NUMBER := 9;
2319 l_txn_source_type_id NUMBER := 10;
2320 l_TxnProcMode NUMBER := 1;
2321 l_process_flag VARCHAR2(1):= 'Y';
2322 l_last_updated_by number := fnd_global.user_id;
2323 l_last_update_login number := fnd_global.login_id;
2324
2325 l_result NUMBER;
2326 l_proc_msg VARCHAR2(300);
2327 l_first_rec BOOLEAN :=TRUE;
2328 l_prev_lpn_id mtl_physical_adjustments.parent_lpn_id%TYPE;
2329 l_prev_subinv mtl_physical_adjustments.subinventory_name%TYPE;
2330 l_prev_loctor mtl_physical_adjustments.locator_id%TYPE;
2331 l_rec_count NUMBER:=0;
2332 l_adj_id mtl_physical_adjustments.adjustment_id%TYPE;
2333 l_process_enabled_flag mtl_parameters.process_enabled_flag%TYPE;
2334 l_actual_cost mtl_physical_adjustments.actual_cost%TYPE;
2335
2336 /* Procedure to log the debug mesages */
2337 PROCEDURE Mydebug (p_msg VARCHAR2)
2338 IS
2339 BEGIN
2340 -- dbms_output.Put_line(p_msg);
2341 IF (l_debug = 1) THEN
2342 INV_LOG_UTIL.TRACE(p_msg, l_proc_name, 5);
2343 END IF;
2344 EXCEPTION
2345 WHEN OTHERS THEN
2346 NULL;
2347 END mydebug;
2348 BEGIN
2349 mydebug('*** Entered PROCESS_PHY_INV_SUBXFER API ***');
2350 mydebug('With following argument values ...');
2351 mydebug('p_organization_id-------------->>'||p_organization_id);
2352 mydebug('p_physical_inv_id-------------->>'||p_physical_inv_id);
2353 mydebug('p_txn_header_id---------------->>'||p_txn_header_id);
2354 mydebug('p_gl_acct_id------------------->>'||p_gl_acct_id);
2355 mydebug('p_request_id------------------->>'||p_request_id);
2356 mydebug('p_txn_date--------------------->>'||p_txn_date);
2357
2358 x_proc_status := 0;
2359
2360 IF (fnd_profile.defined('TRANSACTION_DATE')) THEN
2361 l_profile_value := TO_NUMBER(fnd_profile.value('TRANSACTION_DATE'));
2362
2363 -- Profile value of:
2364 -- 1 = Any open period
2365 -- 2 = No past date
2366 -- 3 = No past periods
2367 -- 4 = Warn when past period
2368
2369 IF (l_profile_value = 3) THEN
2370 l_open_past_period := TRUE;
2371 END IF;
2372 ELSE
2373 FND_MESSAGE.SET_NAME('FND','PROFILES-CANNOT READ');
2374 FND_MESSAGE.SET_TOKEN('OPTION','TRANSACTION_DATE',TRUE);
2375 FND_MESSAGE.SET_TOKEN('ROUTINE', 'MTL_CC_TRANSACT_PKG.CC_TRANSACT ',TRUE); -- todo
2376
2377 FND_MSG_PUB.ADD;
2378 Mydebug('Errors out here with the message to user: ' || 'PROFILES-CANNOT READ');
2379
2380 app_exception.raise_exception;
2381 x_proc_status := -99;
2382 RETURN;
2383 END IF;
2384
2385 Mydebug(' l_txn_date: '||l_txn_date||' Current Date: '||sysdate);
2386
2387 IF (l_profile_value = 2 AND l_txn_date < TRUNC(SYSDATE)) THEN
2388 FND_MESSAGE.SET_NAME('INV', 'INV_NO_PAST_TXN_DATES');
2389
2390 FND_MSG_PUB.ADD;
2391 Mydebug(' Errors out here with the message to user: ' || 'INV_NO_PAST_TXN_DATES');
2392
2393 app_exception.raise_exception;
2394 x_proc_status := -99;
2395 RETURN;
2396 END IF;
2397
2398 invttmtx.tdatechk(p_organization_id,
2399 l_txn_date,
2400 l_period_id,
2401 l_open_past_period);
2402
2403 IF (l_period_id = 0) THEN
2404 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OPEN_PERIOD');
2405
2406 FND_MSG_PUB.ADD;
2407 Mydebug(' Errors out here with the message to user: ' || 'INV_NO_OPEN_PERIOD');
2408
2409 app_exception.raise_exception;
2410 x_proc_status := -99;
2411 RETURN;
2412 ELSIF (l_period_id = -1) THEN
2413 app_exception.raise_exception;
2414 RETURN;
2415 ELSE
2416 IF (l_profile_value = 3) AND
2417 NOT (l_open_past_period) THEN
2418 FND_MESSAGE.SET_NAME('INV', 'INV_NO_PAST_TXN_PERIODS');
2419 FND_MSG_PUB.ADD;
2420 Mydebug(' Errors out here with the message to user: ' || 'INV_NO_PAST_TXN_PERIODS');
2421
2422 app_exception.raise_exception;
2423 x_proc_status := -99;
2424 RETURN;
2425 END IF;
2426 END IF;
2427
2428
2429 /*------------------------------------------|
2430 | Status Approval: |
2431 | |
2432 | 1, 'Approved' |
2433 | 2, 'Rejected' |
2434 | 3, 'Posted' |
2435 | NULL, 'No Status entered' |
2436 |-----------------------------------------*/
2437 Mydebug(' Start Processing the Implicit Physical Inventory Xfer ');
2438 /*
2439 SELECT mtl_material_transactions_s.nextval
2440 INTO l_transaction_header_id
2441 FROM dual;
2442 */
2443 Mydebug(' l_transaction_header_id ..'||l_transaction_header_id);
2444
2445
2446 SELECT description INTO l_transaction_reference
2447 FROM mtl_physical_inventories
2448 WHERE physical_inventory_id = p_physical_inv_id;
2449
2450 FOR i IN c_physical_xfer(p_organization_id, p_physical_inv_id)
2451 LOOP
2452
2453 Mydebug(' Inside FOR Loop .. for Phy Adj Id: ' || i.xfr_adjustment_id);
2454
2455 IF (l_first_rec OR l_prev_lpn_id <> i.parent_lpn_id) THEN
2456
2457 l_first_rec:= FALSE;
2458 l_prev_lpn_id:= i.parent_lpn_id;
2459 l_prev_subinv:= i.xfr_subinventory_name;
2460 l_prev_loctor:= i.to_locator;
2461 l_rec_count := l_rec_count+1;
2462
2463 Mydebug(' Inside IF ... Phy Adj Id: ' || i.xfr_adjustment_id);
2464
2465 IF inv_cache.set_item_rec(i.organization_id,i.inventory_item_id) THEN
2466 l_uom_code := inv_cache.item_rec.primary_uom_code;
2467 l_sec_uom_code := inv_cache.item_rec.secondary_uom_code;
2468 ELSE
2469 Mydebug(' Errors while fetching UOM : ' || substr(sqlerrm,1,100));
2470 app_exception.raise_exception;
2471 x_proc_status := -99;
2472 RETURN;
2473 END IF;
2474 mydebug(' Inserting MMTT with l_transaction_header_id : '||l_transaction_header_id ||' content_lpn_id : '||i.parent_lpn_id);
2475
2476 INSERT INTO mtl_material_transactions_temp
2477 (transaction_header_id
2478 ,transaction_temp_id
2479 ,transaction_mode
2480 ,last_update_date
2481 ,last_updated_by
2482 ,creation_date
2483 ,created_by
2484 ,last_update_login
2485 ,inventory_item_id
2486 ,organization_id
2487 ,subinventory_code
2488 ,locator_id
2489 ,transaction_quantity
2490 ,primary_quantity
2491 ,transaction_uom
2492 ,transaction_type_id
2493 ,transaction_action_id
2494 ,transaction_source_type_id
2495 ,transaction_source_id
2496 ,transaction_reference
2497 ,transaction_date
2498 ,acct_period_id
2499 ,distribution_account_id
2500 ,physical_adjustment_id
2501 ,transfer_subinventory
2502 ,transfer_to_location
2503 ,process_flag
2504 ,content_lpn_id
2505 ,transaction_batch_id
2506 ,transaction_batch_seq
2507 )
2508 VALUES ( l_transaction_header_id
2509 ,mtl_material_transactions_s.nextval
2510 ,l_txnprocmode
2511 ,sysdate
2512 ,l_last_updated_by
2513 ,sysdate
2514 ,l_last_updated_by
2515 ,-1
2516 ,-1
2517 ,p_organization_id
2518 ,i.subinventory_name
2519 ,i.from_locator
2520 ,1
2521 ,1
2522 ,l_uom_code
2523 ,l_txn_type_id
2524 ,l_txn_action_id
2525 ,l_txn_source_type_id
2526 ,p_physical_inv_id
2527 ,l_transaction_reference
2528 ,l_txn_date -- bug 16188610
2529 ,l_period_id
2530 ,p_gl_acct_id
2531 ,i.xfr_adjustment_id
2532 ,i.xfr_subinventory_name
2533 ,i.to_locator
2534 ,l_process_flag
2535 ,i.parent_lpn_id
2536 ,mtl_material_transactions_s.currval
2537 ,mtl_material_transactions_s.currval
2538 );
2539
2540 END IF;
2541
2542
2543 IF i.adjustment_quantity <> 0 THEN
2544
2545 IF i.xfr_adjustment_id IS NOT NULL THEN
2546
2547 mydebug(' Updating MPA with adjustment_id : '||i.xfr_adjustment_id );
2548
2549 UPDATE mtl_physical_adjustments
2550 set locator_id = nvl(i.to_locator, l_prev_loctor)
2551 , subinventory_name = nvl(i.xfr_subinventory_name,l_prev_subinv)
2552 , system_quantity = i.system_quantity --bug 14778466
2553 , adjustment_quantity = i.adjustment_quantity
2554 , secondary_adjustment_qty = i.secondary_adjustment_qty
2555 , approval_status = decode(approval_status, null,1, approval_status)
2556 , last_update_date = sysdate
2557 , last_updated_by = l_last_updated_by
2558 , last_update_login = l_last_update_login
2559 WHERE adjustment_id = i.xfr_adjustment_id
2560 AND physical_inventory_ID = p_physical_inv_id
2561 AND organization_id = p_organization_id;
2562
2563 /* --bug 14778466, commented below code since the count qty remains same, SO no need to update MPIT table .
2564 BEGIN
2565 mydebug(' Updating MPIT with adjustment_id : '||i.xfr_adjustment_id );
2566 UPDATE mtl_physical_inventory_tags
2567 set locator_id = nvl(i.to_locator, l_prev_loctor)
2568 , subinventory = nvl(i.xfr_subinventory_name,l_prev_subinv)
2569 , tag_quantity = i.count_quantity --bug 14778466
2570 , tag_quantity_at_standard_uom = inv_convert.inv_um_convert
2571 ( item_id => inventory_item_id,
2572 lot_number => lot_number,
2573 organization_id => organization_id,
2574 precision => 5,
2575 from_quantity => i.count_quantity,
2576 from_unit => tag_uom,
2577 to_unit => l_uom_code,
2578 from_name => NULL,
2579 to_name => NULL)
2580 , tag_secondary_quantity = i.secondary_adjustment_qty
2581 , last_update_date = sysdate
2582 , last_updated_by = l_last_updated_by
2583 , last_update_login = l_last_update_login
2584 WHERE adjustment_id = i.xfr_adjustment_id
2585 AND physical_inventory_ID = p_physical_inv_id
2586 AND organization_id = p_organization_id;
2587 Exception
2588 when others then
2589 UPDATE mtl_physical_inventory_tags
2590 set locator_id = nvl(i.to_locator, l_prev_loctor)
2591 , subinventory = nvl(i.xfr_subinventory_name,l_prev_subinv)
2592 , tag_quantity = i.count_quantity
2593 , tag_secondary_quantity = i.secondary_count_qty --bug 14778466
2594 , last_update_date = sysdate
2595 , last_updated_by = l_last_updated_by
2596 , last_update_login = l_last_update_login
2597 WHERE adjustment_id = i.xfr_adjustment_id
2598 AND physical_inventory_ID = p_physical_inv_id
2599 AND organization_id = p_organization_id;
2600 End;*/
2601
2602 ELSE
2603
2604 SELECT mtl_physical_adjustments_s.nextval INTO l_adj_id FROM dual;
2605
2606 mydebug(' Creating a new adjustment : '||l_adj_id );
2607
2608 SELECT NVL(process_enabled_flag, 'N')
2609 INTO l_process_enabled_flag
2610 FROM mtl_parameters
2611 WHERE organization_id = p_organization_id;
2612
2613 Mydebug('Process enabled flag : ' || l_process_enabled_flag);
2614
2615 IF (l_process_enabled_flag = 'Y') THEN
2616 -- get opm cost for the item
2617 l_actual_cost := gmf_cmcommon.process_item_unit_cost (i.inventory_item_id,p_organization_id,SYSDATE);
2618 Mydebug('OPM cost: ' || l_actual_cost);
2619
2620 ELSE
2621 INV_UTILITIES.GET_ITEM_COST(
2622 v_org_id => p_organization_id,
2623 v_item_id => i.inventory_item_id,
2624 v_locator_id => l_prev_loctor,
2625 v_item_cost => l_actual_cost);
2626
2627 IF (l_actual_cost = -999) THEN
2628 l_actual_cost := 0;
2629 END IF;
2630 END IF;
2631 mydebug(' Inserting MPA with adjustment_id : '||l_adj_id );
2632
2633 INSERT INTO mtl_physical_adjustments
2634 ( adjustment_id,
2635 organization_id,
2636 physical_inventory_id,
2637 inventory_item_id,
2638 subinventory_name,
2639 system_quantity,
2640 last_update_date,
2641 last_updated_by,
2642 creation_date,
2643 created_by,
2644 last_update_login,
2645 count_quantity,
2646 adjustment_quantity,
2647 revision,
2648 locator_id,
2649 parent_lpn_id,
2650 outermost_lpn_id,
2651 cost_group_id,
2652 lot_number,
2653 serial_number,
2654 actual_cost ,
2655 secondary_count_qty,
2656 secondary_adjustment_qty,
2657 lot_expiration_date,
2658 approval_status
2659 )
2660 VALUES ( l_adj_id,
2661 p_organization_id,
2662 p_physical_inv_id,
2663 i.inventory_item_id,
2664 l_prev_subinv,
2665 i.system_quantity,
2666 SYSDATE,
2667 l_last_updated_by,
2668 SYSDATE,
2669 l_last_updated_by,
2670 l_last_update_login,
2671 i.count_quantity,
2672 i.adjustment_quantity,
2673 i.revision,
2674 l_prev_loctor,
2675 i.parent_lpn_id,
2676 i.outermost_lpn_id,
2677 i.cost_group_id,
2678 i.lot_number,
2679 i.serial_number,
2680 l_actual_cost,
2681 i.secondary_count_qty,
2682 i.secondary_adjustment_qty,
2683 i.lot_expiration_date,
2684 1 --by default approved status
2685 );
2686 mydebug(' Inserting MPIT with adjustment_id : '||l_adj_id );
2687
2688 insert_row( p_physical_inventory_id => p_physical_inv_id,
2689 p_organization_id => p_organization_id,
2690 p_subinventory => l_prev_subinv,
2691 p_locator_id => l_prev_loctor,
2692 p_parent_lpn_id => i.parent_lpn_id,
2693 p_inventory_item_id => i.inventory_item_id,
2694 p_revision => i.revision,
2695 p_lot_number => i.lot_number,
2696 p_serial_number => i.serial_number,
2697 p_tag_quantity => i.count_quantity, --bug 14778466
2698 p_tag_uom => l_uom_code,
2699 p_user_id => l_last_updated_by,
2700 p_cost_group_id => i.cost_group_id,
2701 p_adjustment_id => l_adj_id
2702 );
2703
2704 --bug 14778466, added below code to delete the old tag which is not counted at all for that lpn.
2705 --Suppose, LPN1A in Loc1 with plain items IT1,IT2 as per system. User then counted LPN1A in Loc2 with item IT1 only and then approved it.
2706 --So, while launch adj, we will insert a new adj rec for LPN1A with IT2 as -ve adj from Loc2, and delete adj tag which is there for Loc1.
2707 BEGIN
2708 -- Get the old adjustment id
2709 SELECT NULLIF(MIN(adjustment_id),MAX(adjustment_id)) INTO l_adj_id
2710 FROM mtl_physical_adjustments
2711 WHERE organization_id = p_organization_id
2712 AND physical_inventory_id = p_physical_inv_id
2713 AND inventory_item_id = i.inventory_item_id
2714 AND parent_lpn_id = i.parent_lpn_id
2715 AND approval_status = 1
2716 AND NVL(revision,'@@@@') = NVL(i.revision,'@@@@')
2717 AND NVL(lot_number,'@@@@') = NVL(i.lot_number,'@@@@')
2718 AND NVL(serial_number,'@@@@') = NVL(i.serial_number,'@@@@')
2719 AND adjustment_id IN (SELECT adjustment_id
2720 FROM mtl_physical_inventory_tags
2721 WHERE organization_id = p_organization_id
2722 AND physical_inventory_id = p_physical_inv_id
2723 AND inventory_item_id = i.inventory_item_id
2724 AND parent_lpn_id = i.parent_lpn_id
2725 AND NVL(revision,'@@@@') = NVL(i.revision,'@@@@')
2726 AND NVL(lot_number,'@@@@') = NVL(i.lot_number,'@@@@')
2727 AND NVL(serial_num,'@@@@') = NVL(i.serial_number,'@@@@')
2728 AND void_flag = 2);
2729
2730 EXCEPTION
2731 WHEN NO_DATA_FOUND THEN
2732 mydebug(' There is no OLD adjustment to delete ');
2733 l_adj_id := null;
2734 WHEN others THEN
2735 mydebug(' Exception while fetching old adj tag '||substr(sqlerrm,1,100));
2736 l_adj_id := null;
2737 END;
2738
2739 mydebug(' Got the Adj id to delete recs from MPA, MPIT : '||l_adj_id);
2740
2741 IF l_adj_id IS NOT NULL THEN
2742
2743 DELETE FROM mtl_physical_inventory_tags
2744 WHERE adjustment_id = l_adj_id
2745 and physical_inventory_id = p_physical_inv_id
2746 and organization_id = p_organization_id;
2747
2748 mydebug('Recs deleted from MPA >>> '||SQL%ROWCOUNT);
2749
2750 DELETE FROM mtl_physical_adjustments
2751 WHERE adjustment_id = l_adj_id
2752 and physical_inventory_id = p_physical_inv_id
2753 and organization_id = p_organization_id;
2754
2755 mydebug('Recs deleted from MPIT >>> '||SQL%ROWCOUNT);
2756 END IF;
2757
2758 l_adj_id := null;
2759
2760 END IF;
2761
2762 ELSE
2763 mydebug(' Updating MPA with approval_status = 3 (Posted) for adjustment_id : '||i.xfr_adjustment_id );
2764 UPDATE mtl_physical_adjustments
2765 set approval_status = 3
2766 , system_quantity = i.system_quantity --bug 14778466
2767 , adjustment_quantity = i.adjustment_quantity --bug 14778466
2768 , secondary_adjustment_qty = i.secondary_adjustment_qty --bug 14778466
2769 , last_update_date = sysdate
2770 , last_updated_by = l_last_updated_by
2771 , last_update_login = l_last_update_login
2772 where adjustment_id = i.xfr_adjustment_id
2773 and physical_inventory_id = p_physical_inv_id
2774 and organization_id = p_organization_id;
2775
2776 END IF;
2777
2778 IF l_rec_count > 100 THEN
2779
2780 BEGIN
2781 Mydebug(' Starts Processing the MMTTs.. ');
2782
2783 l_result := inv_lpn_trx_pub.Process_lpn_trx (
2784 p_trx_hdr_id => l_transaction_header_id,
2785 p_commit => fnd_api.g_true,
2786 p_proc_mode => 1,
2787 p_process_trx => fnd_api.g_true,
2788 p_atomic => fnd_api.g_false,
2789 x_proc_msg => l_proc_msg);
2790
2791 Mydebug(' result : '||l_result);
2792
2793 IF ( l_result <> 0 ) THEN
2794 Mydebug(' Process_lpn_trx Failed\errors ');
2795 app_exception.raise_exception;
2796 ELSE
2797 Mydebug(' Process_lpn_trx Successful ');
2798 END IF;
2799
2800 -- reseting count to zero
2801 l_rec_count := 0;
2802
2803 Mydebug(' End of Processing the MMTTs.. ');
2804 EXCEPTION
2805 WHEN OTHERS THEN
2806 Mydebug(' Exception in Process MMTTs Block :'||Substr(sqlerrm, 1, 200));
2807 x_proc_status := -99;
2808 RETURN;
2809 END;
2810 END IF;
2811
2812
2813 END LOOP;
2814
2815 --bug 14778466, We will process the physical subxfer before going back to incapa code.
2816 IF l_rec_count >= 1 THEN
2817
2818 BEGIN
2819 Mydebug(' Starts Processing the MMTTs.. ');
2820
2821 l_result := inv_lpn_trx_pub.Process_lpn_trx (
2822 p_trx_hdr_id => l_transaction_header_id,
2823 p_commit => fnd_api.g_true,
2824 p_proc_mode => 1,
2825 p_process_trx => fnd_api.g_true,
2826 p_atomic => fnd_api.g_false,
2827 x_proc_msg => l_proc_msg);
2828
2829 Mydebug(' result : '||l_result);
2830
2831 IF ( l_result <> 0 ) THEN
2832 Mydebug(' Process_lpn_trx Failed\errors ');
2833 app_exception.raise_exception;
2834 ELSE
2835 Mydebug(' Process_lpn_trx Successful ');
2836 END IF;
2837
2838 -- reseting count to zero
2839 l_rec_count := 0;
2840
2841 Mydebug(' End of Processing the MMTTs.. ');
2842 EXCEPTION
2843 WHEN OTHERS THEN
2844 Mydebug(' Exception in Process MMTTs Block :'||Substr(sqlerrm, 1, 200));
2845 x_proc_status := -99;
2846 RETURN;
2847 END;
2848 END IF;
2849
2850 x_record_count := l_rec_count;
2851
2852 mydebug('*** End of procedure PROCESS_PHY_INV_SUBXFER ***');
2853
2854 EXCEPTION
2855 WHEN OTHERS THEN
2856 Mydebug(' Exception inside procedure PROCESS_PHY_INV_SUBXFER :' ||Substr(sqlerrm, 1, 100));
2857 x_proc_status := -99;
2858 END Process_Phy_Inv_SubXfer;
2859
2860
2861 END INV_PHY_INV_LOVS;
[Home] [Help]