1 PACKAGE BODY MTL_INV_UTIL_GRP AS
2 /* $Header: INVGIVUB.pls 120.2.12010000.2 2008/07/29 12:52:21 ptkumar ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MTL_INV_UTIL_GRP';
4
5 procedure mdebug(msg in varchar2)
6 is
7 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 begin
9 --dbms_output.put_line(msg);
10 null;
11 end;
12
13
14 --
15 -- Gets the item cost for a specific item.
16 PROCEDURE Get_Item_Cost(
17 p_api_version IN NUMBER ,
18 p_init_msg_list IN VARCHAR2,
19 p_commit IN VARCHAR2 ,
20 p_validation_level IN NUMBER ,
21 x_return_status OUT NOCOPY VARCHAR2 ,
22 x_msg_count OUT NOCOPY NUMBER ,
23 x_msg_data OUT NOCOPY VARCHAR2 ,
24 p_organization_id IN NUMBER ,
25 p_inventory_item_id IN NUMBER ,
26 p_locator_id IN NUMBER ,
27 x_item_cost OUT NOCOPY NUMBER )
28 IS
29 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
30 BEGIN
31 -- Start OF comments
32 -- API name : Get_Item_Cost
33 -- TYPE : Private
34 -- Pre-reqs : None
35 -- FUNCTION :
36 -- selects the cost of the specific item
37 -- Parameters:
38 -- IN :
39 -- p_api_version IN NUMBER (required)
40 -- API Version of this procedure
41 --
42 -- p_init_msg_level IN VARCHAR2 (optional)
43 -- DEFAULT = FND_API.G_FALSE
44 --
45 -- p_commit IN VARCHAR2 (optional)
46 -- DEFAULT = FND_API.G_FALSE
47 --
48 -- p_validation_level IN NUMBER (optional)
49 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
50 --
51 -- p_organization_id IN NUMBER (required)
52 -- ID OF the organization
53 --
54 -- p_inventory_item_id IN NUMBER (required)
55 -- ID OF the infentory item
56 --
57 -- p_locator_id IN NUMBER (optional - defaulted)
58 -- default = NULL (IF dynamic locator)
59 -- Locator ID
60 --
61 -- OUT :
62 -- X_return_status OUT NUMBER
63 -- Result of all the operations
64 --
65 -- x_msg_count OUT NUMBER,
66 --
67 -- x_msg_data OUT VARCHAR2,
68 --
69 -- x_item_cost OUT NUMBER
70 -- selected item cost
71 --
72 -- Version: Current Version 0.9
73 -- Changed : Nothing
74 -- No Previous Version 0.0
75 -- Initial version 0.9
76 -- Notes : Note text
77 -- END OF comments
78 DECLARE
79 --
80 L_locator_Id NUMBER := p_locator_id;
81 --
82 L_api_version CONSTANT NUMBER := 0.9;
83 L_api_name CONSTANT VARCHAR2(30) := 'Get_Item_Cost';
84 BEGIN
85 -- Standard start of API savepoint
86 SAVEPOINT Get_Item_Cost;
87 --
88 /*
89 -- Standard Call to check for call compatibility
90 IF NOT FND_API.Compatible_API_Call(l_api_version
91 , p_api_version
92 , l_api_name
93 , G_PKG_NAME) THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END IF;
96 --
97 -- Initialize message list if p_init_msg_list is set to true
98 IF FND_API.to_Boolean(p_init_msg_list) THEN
99 FND_MSG_PUB.initialize;
100 END IF;
101 --
102 */
103 -- Initialisize API return status to access
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105 --
106 -- API body
107 --
108 -- We doing this for dynamic locator.
109 IF (L_locator_Id = -1) THEN -- XXX ???
110 L_locator_Id := NULL;
111 END IF;
112
113 /* Bug# 2942493
114 ** Instead of duplicating the code, reusing the common utility to
115 ** to get the item cost. That way its easier to maintain.
116 */
117
118 INV_UTILITIES.GET_ITEM_COST(
119 v_org_id => p_organization_id,
120 v_item_id => p_inventory_item_id,
121 v_locator_id => L_locator_Id,
122 v_item_cost => x_item_cost);
123
124 IF (x_item_cost = -999) THEN
125 x_item_cost := 0;
126 END IF;
127
128 IF (l_debug = 1) THEN
129 mdebug('start4 '||to_char(x_item_cost));
130 END IF;
131
132
133 -- END of API body
134 /*
135 -- Standard check of p_commit
136 IF FND_API.to_Boolean(p_commit) THEN
137 COMMIT;
138 END IF;
139 -- Standard call to get message count and if count is 1, get message info
140 FND_MSG_PUB.Count_And_Get
141 (p_count => x_msg_count
142 , p_data => x_msg_data);
143 */
144 EXCEPTION
145 WHEN FND_API.G_EXC_ERROR THEN
146 --
147 ROLLBACK TO Get_Item_Cost;
148 --
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 --
151 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
152 , p_data => x_msg_data);
153 --
154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
155 --
156 ROLLBACK TO Get_Item_Cost;
157 --
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159 --
160 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
161 , p_data => x_msg_data);
162 --
163 WHEN OTHERS THEN
164 --
165 ROLLBACK TO Get_Item_Cost;
166 --
167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
168 --
169 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
170 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
171 END IF;
172 --
173 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
174 , p_data => x_msg_data);
175 END;
176 END;
177 --
178 -- calculate the system quantity of an given item
179 PROCEDURE Calculate_Systemquantity(
180 p_api_version IN NUMBER ,
181 p_init_msg_list IN VARCHAR2 ,
182 p_commit IN VARCHAR2 ,
183 p_validation_level IN NUMBER ,
184 x_return_status OUT NOCOPY VARCHAR2 ,
185 x_msg_count OUT NOCOPY NUMBER ,
186 x_msg_data OUT NOCOPY VARCHAR2 ,
187 p_organization_id IN NUMBER ,
188 p_inventory_item_id IN NUMBER ,
189 p_subinventory IN VARCHAR2 ,
190 p_lot_number IN VARCHAR2 ,
191 p_revision IN VARCHAR2 ,
192 p_locator_id IN NUMBER ,
193 p_cost_group_id IN NUMBER ,
194 p_serial_number IN VARCHAR2 ,
195 p_serial_number_control IN NUMBER ,
196 p_serial_count_option IN NUMBER ,
197 x_system_quantity OUT NOCOPY NUMBER )
198 IS
199 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
200 BEGIN
201 -- Start OF comments
202 -- API name : Calculate_Systemquantity
203 -- TYPE : Private
204 -- Pre-reqs : None
205 -- FUNCTION :
206 -- Parameters:
207 -- IN :
208 -- p_api_version IN NUMBER (required)
209 -- API Version of this procedure
210 --
211 -- p_init_msg_list IN VARCHAR2 (optional)
212 -- DEFAULT = FND_API.G_FALSE,
213 --
214 -- p_commit IN VARCHAR2 (optional)
215 -- DEFAULT = FND_API.G_FALSE
216 --
217 -- p_validation_level IN NUMBER (optional)
218 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
219 --
220 -- p_serial_number IN VARCHAR2 (required)
221 --
222 -- p_inventory_item_id IN NUMBER (required)
223 --
224 -- p_organization_id IN NUMBER (required)
225 --
226 -- p_subinventory IN VARCHAR2 (required)
227 --
228 -- p_LOT_NUMBER IN VARCHAR2 (required)
229 --
230 -- p_REVISION IN VARCHAR2 (required)
231 --
232 -- p_LOCATOR_ID IN NUMBER (required)
233 --
234 -- p_serial_number_control_code IN NUMBER (required)
235 --
236 -- p_serial_count_option
237 --
238 -- OUT :
239 -- X_return_status OUT NUMBER
240 -- Result of all the operations
241 --
242 -- x_msg_count OUT NUMBER,
243 --
244 -- x_msg_data OUT VARCHAR2,
245 --
246 -- x_system_quantity OUT NUMBER - qty specified in UOM of G_UOM_CODE
247 -- which would be either count uom if count info was specified
248 -- or primary uom if count info was entered into the primary uom
249 -- quantity field of interface rec
250 --
251 -- Version: Current Version 0.9
252 -- Changed : Nothing
253 -- No Previous Version 0.0
254 -- Initial version 0.9
255 -- Notes : Note text
256 -- END OF comments
257 DECLARE
258 --
259 CURSOR L_SysQty_Csr(itemid IN NUMBER, org IN NUMBER,
260 subinv IN VARCHAR2, rev IN VARCHAR2, loc IN NUMBER,
261 lot IN VARCHAR2, cost IN NUMBER) IS
262 SELECT
263 NVL(sum(primary_transaction_quantity), 0) SYSTEM_QUANTITY
264 FROM MTL_ONHAND_QUANTITIES_DETAIL
265 WHERE inventory_item_id = itemid
266 AND organization_id = org
267 AND subinventory_code = subinv
268 AND NVL(lot_number, '@') = NVL(lot, '@')
269 AND NVL(revision, '@') = NVL(rev, '@')
270 AND NVL(locator_id, 99) = NVL(loc, 99)
271 AND NVL(cost_group_id, -1) = NVL(cost, -1)
272 AND NVL(containerized_flag, 2) = 2;
273 --
274 CURSOR L_SysQtySer_Csr(itemid IN NUMBER, org IN NUMBER,
275 subinv IN VARCHAR2, rev IN VARCHAR2, loc IN NUMBER,
276 lot IN VARCHAR2, ser IN VARCHAR2) IS
277 SELECT
278 NVL(sum(DECODE(msn.current_status, 3, 1, 0)), 0) SYSTEM_QUANTITY
279 FROM mtl_serial_numbers msn
280 WHERE msn.serial_number = NVL(ser, serial_number)
281 AND msn.inventory_item_id = itemid
282 AND msn.current_organization_id = org
283 AND msn.current_subinventory_code = subinv
284 AND NVL(msn.LOT_NUMBER, 'XX') = NVL(lot, 'XX')
285 AND NVL(msn.REVISION, 'XXX') = NVL(rev, 'XXX')
286 AND NVL(msn.CURRENT_LOCATOR_ID, -2) = NVL(loc, -2);
287 --
288 L_api_version CONSTANT NUMBER := 0.9;
289 L_api_name CONSTANT VARCHAR2(30) := 'Calculate_Systemquantity';
290 BEGIN
291 IF (l_debug = 1) THEN
292 MDEBUG( 'Begin of Calculate_Systemquantity1');
293 END IF;
294 -- Standard start of API savepoint
295 SAVEPOINT Calculate_Systemquantity;
296 --
297 -- for Testing marked by suresh
298 -- Standard Call to check for call compatibility
299 IF NOT FND_API.Compatible_API_Call(l_api_version
300 , p_api_version
301 , l_api_name
302 , G_PKG_NAME) THEN
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 END IF;
305
306 --
307 IF (l_debug = 1) THEN
308 MDEBUG( 'Begin of Calculate_Systemquantity2');
309 END IF;
310 -- Initialize message list if p_init_msg_list is set to true
311 IF FND_API.to_Boolean(p_init_msg_list) THEN
312 FND_MSG_PUB.initialize;
313 END IF;
314
315 --
316 IF (l_debug = 1) THEN
317 MDEBUG( 'Begin of Calculate_Systemquantity3');
318 END IF;
319 -- Initialisize API return status to access
320 x_return_status := FND_API.G_RET_STS_SUCCESS;
321 x_system_quantity := NULL;
322 --
323 -- API body
324 --
325
326 IF (l_debug = 1) THEN
327 MDEBUG( 'Begin of Calculate_Systemquantity4');
328 END IF;
329 IF(p_serial_number_control IN (1, 6) OR
330 p_serial_count_option = 1) THEN
331 --
332 FOR c_rec IN L_SysQty_Csr(p_inventory_item_id,
333 p_organization_id, p_subinventory,
334 p_revision, p_locator_id,
335 p_lot_number, p_cost_group_id) LOOP
336 --
337 x_system_quantity := c_rec.system_quantity;
338 IF (l_debug = 1) THEN
339 MDEBUG( 'Calc.SystemQty : Inside loop-1 ');
340 END IF;
341 --
342 END LOOP;
343 IF (l_debug = 1) THEN
344 MDEBUG( 'Calc.SystemQty =: '||to_char(x_system_quantity));
345 END IF;
346 -- serial control
347 ELSIF
348 (p_serial_number_control IN(2, 5) AND
349 p_serial_count_option > 1) THEN
350 FOR c_rec IN L_SysQtySer_Csr(p_inventory_item_id,
351 p_organization_id, p_subinventory,
352 p_revision, p_locator_id,
353 p_lot_number, p_serial_number) LOOP
354 --
355 x_system_quantity := c_rec.system_quantity;
356 IF (l_debug = 1) THEN
357 MDEBUG( 'Calc.SystemQty : Inside loop-2 ');
358 END IF;
359 --
360 END LOOP;
361 IF (l_debug = 1) THEN
362 MDEBUG( 'Calc.SystemQty 2=: '||to_char(x_system_quantity));
363 END IF;
364 END IF;
365 IF MTL_CCEOI_VAR_PVT.G_PRIMARY_UOM_CODE <> MTL_CCEOI_VAR_PVT.G_UOM_CODE
366 THEN
367 x_system_quantity := nvl( INV_CONVERT.inv_um_convert(
368 item_id =>p_inventory_item_id
369 , precision => 5
370 , from_quantity => x_system_quantity
371 , from_unit => MTL_CCEOI_VAR_PVT.G_PRIMARY_UOM_CODE
372 , to_unit => MTL_CCEOI_VAR_PVT.G_UOM_CODE
373 , from_name => NULL
374 , to_name => NULL
375 ),0);
376 IF (l_debug = 1) THEN
377 MDEBUG( 'Calc.convert System Qty =: '||to_char(x_system_quantity));
378 END IF;
379 END IF;
380 --
381 -- END of API body
382 -- Standard check of p_commit
383 IF FND_API.to_Boolean(p_commit) THEN
384 COMMIT;
385 END IF;
386 -- Standard call to get message count and if count is 1, get message info
387 FND_MSG_PUB.Count_And_Get
388 (p_count => x_msg_count
389 , p_data => x_msg_data);
390 EXCEPTION
391 WHEN FND_API.G_EXC_ERROR THEN
392 --
393 ROLLBACK TO Calculate_Systemquantity;
394 --
395 IF (l_debug = 1) THEN
396 MDEBUG( 'Calc.sys : Exception Error ' || sqlerrm);
397 END IF;
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 --
400 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
401 , p_data => x_msg_data);
402 --
403 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
404 --
405 ROLLBACK TO Calculate_Systemquantity;
406 --
407 IF (l_debug = 1) THEN
408 MDEBUG( 'Calc.sys : Unexp Exception Error: '|| sqlerrm);
409 END IF;
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411 --
412 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
413 , p_data => x_msg_data);
414 --
415 WHEN OTHERS THEN
416 --
417 IF (l_debug = 1) THEN
418 MDEBUG( 'Calc.sys : Others Exception Error ' || sqlerrm);
419 END IF;
420 ROLLBACK TO Calculate_Systemquantity;
421 --
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
423 --
424 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
425 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
426 END IF;
427 --
428 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
429 , p_data => x_msg_data);
430 END;
431 END;
432
433
434 -- BEGIN INVCONV
435 -- Overloaded procedure to return secondary quantity
436 PROCEDURE calculate_systemquantity (
437 p_api_version IN NUMBER,
438 p_init_msg_list IN VARCHAR2,
439 p_commit IN VARCHAR2,
440 p_validation_level IN NUMBER,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_msg_count OUT NOCOPY NUMBER,
443 x_msg_data OUT NOCOPY VARCHAR2,
444 p_organization_id IN NUMBER,
445 p_inventory_item_id IN NUMBER,
446 p_subinventory IN VARCHAR2,
447 p_lot_number IN VARCHAR2,
448 p_revision IN VARCHAR2,
449 p_locator_id IN NUMBER,
450 p_cost_group_id IN NUMBER,
451 p_serial_number IN VARCHAR2,
452 p_serial_number_control IN NUMBER,
453 p_serial_count_option IN NUMBER,
454 x_system_quantity OUT NOCOPY NUMBER,
455 x_sec_system_quantity OUT NOCOPY NUMBER
456 )
457 IS
458 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
459 BEGIN
460 -- Start OF comments
461 -- API name : Calculate_Systemquantity
462 -- TYPE : Private
463 -- Pre-reqs : None
464 -- FUNCTION :
465 -- Parameters:
466 -- IN :
467 -- p_api_version IN NUMBER (required)
468 -- API Version of this procedure
469 --
470 -- p_init_msg_list IN VARCHAR2 (optional)
471 -- DEFAULT = FND_API.G_FALSE,
472 --
473 -- p_commit IN VARCHAR2 (optional)
474 -- DEFAULT = FND_API.G_FALSE
475 --
476 -- p_validation_level IN NUMBER (optional)
477 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
478 --
479 -- p_serial_number IN VARCHAR2 (required)
480 --
481 -- p_inventory_item_id IN NUMBER (required)
482 --
483 -- p_organization_id IN NUMBER (required)
484 --
485 -- p_subinventory IN VARCHAR2 (required)
486 --
487 -- p_LOT_NUMBER IN VARCHAR2 (required)
488 --
489 -- p_REVISION IN VARCHAR2 (required)
490 --
491 -- p_LOCATOR_ID IN NUMBER (required)
492 --
493 -- p_serial_number_control_code IN NUMBER (required)
494 --
495 -- p_serial_count_option
496 --
497 -- OUT :
498 -- X_return_status OUT NUMBER
499 -- Result of all the operations
500 --
501 -- x_msg_count OUT NUMBER,
502 --
503 -- x_msg_data OUT VARCHAR2,
504 --
505 -- x_system_quantity OUT NUMBER - qty specified in UOM of G_UOM_CODE
506 -- which would be either count uom if count info was specified
507 -- or primary uom if count info was entered into the primary uom
508 -- quantity field of interface rec
509 --
510 -- x_sec_system_quantity OUT NUMBER - qty specified in UOM of G_UOM_CODE
511 -- which would be either count uom if count info was specified
512 -- or primary uom if count info was entered into the primary uom
513 -- quantity field of interface rec
514 --
515 -- Version: Current Version 0.9
516 -- Changed : Nothing
517 -- No Previous Version 0.0
518 -- Initial version 0.9
519 -- Notes : Note text
520 -- END OF comments
521 DECLARE
522 --
523 CURSOR l_sysqty_csr (
524 itemid IN NUMBER,
525 org IN NUMBER,
526 subinv IN VARCHAR2,
527 rev IN VARCHAR2,
528 loc IN NUMBER,
529 lot IN VARCHAR2,
530 COST IN NUMBER
531 )
532 IS
533 SELECT NVL (SUM (primary_transaction_quantity), 0) system_quantity,
534 NVL (SUM (secondary_transaction_quantity), 0) secondary_system_quantity
535 FROM mtl_onhand_quantities_detail
536 WHERE inventory_item_id = itemid
537 AND organization_id = org
538 AND subinventory_code = subinv
539 AND NVL (lot_number, '@') = NVL (lot, '@')
540 AND NVL (revision, '@') = NVL (rev, '@')
541 AND NVL (locator_id, 99) = NVL (loc, 99)
542 AND NVL (cost_group_id, -1) = NVL (COST, -1)
543 AND NVL (containerized_flag, 2) = 2;
544
545 --
546 CURSOR l_sysqtyser_csr (
547 itemid IN NUMBER,
548 org IN NUMBER,
549 subinv IN VARCHAR2,
550 rev IN VARCHAR2,
551 loc IN NUMBER,
552 lot IN VARCHAR2,
553 ser IN VARCHAR2
554 )
555 IS
556 SELECT NVL (SUM (DECODE (msn.current_status, 3, 1, 0)),
557 0
558 ) system_quantity
559 FROM mtl_serial_numbers msn
560 WHERE msn.serial_number = NVL (ser, serial_number)
561 AND msn.inventory_item_id = itemid
562 AND msn.current_organization_id = org
563 AND msn.current_subinventory_code = subinv
564 AND NVL (msn.lot_number, 'XX') = NVL (lot, 'XX')
565 AND NVL (msn.revision, 'XXX') = NVL (rev, 'XXX')
566 AND NVL (msn.current_locator_id, -2) = NVL (loc, -2);
567
568 --
569 l_api_version CONSTANT NUMBER := 0.9;
570 l_api_name CONSTANT VARCHAR2 (30) := 'Calculate_Systemquantity2';
571 BEGIN
572 IF (l_debug = 1)
573 THEN
574 mdebug ('Begin of Calculate_Systemquantity1');
575 END IF;
576
577 -- Standard start of API savepoint
578 SAVEPOINT calculate_systemquantity;
579
580 --
581 -- for Testing marked by suresh
582 -- Standard Call to check for call compatibility
583 IF NOT fnd_api.compatible_api_call (l_api_version,
584 p_api_version,
585 l_api_name,
586 g_pkg_name
587 )
588 THEN
589 RAISE fnd_api.g_exc_unexpected_error;
590 END IF;
591
592 --
593 IF (l_debug = 1)
594 THEN
595 mdebug ('Begin of Calculate_Systemquantity2');
596 END IF;
597
598 -- Initialize message list if p_init_msg_list is set to true
599 IF fnd_api.to_boolean (p_init_msg_list)
600 THEN
601 fnd_msg_pub.initialize;
602 END IF;
603
604 --
605 IF (l_debug = 1)
606 THEN
607 mdebug ('Begin of Calculate_Systemquantity3');
608 END IF;
609
610 -- Initialisize API return status to access
611 x_return_status := fnd_api.g_ret_sts_success;
612 x_system_quantity := NULL;
613 x_sec_system_quantity := NULL;
614
615 --
616 -- API body
617 --
618 IF (l_debug = 1)
619 THEN
620 mdebug ('Begin of Calculate_Systemquantity4');
621 END IF;
622
623 IF (p_serial_number_control IN (1, 6) OR p_serial_count_option = 1)
624 THEN
625 --
626 FOR c_rec IN l_sysqty_csr (p_inventory_item_id,
627 p_organization_id,
628 p_subinventory,
629 p_revision,
630 p_locator_id,
631 p_lot_number,
632 p_cost_group_id
633 )
634 LOOP
635 --
636 x_system_quantity := c_rec.system_quantity;
637 x_sec_system_quantity := c_rec.secondary_system_quantity;
638
639 IF (l_debug = 1)
640 THEN
641 mdebug ('Calc.SystemQty : Inside loop-1 ');
642 END IF;
643 --
644 END LOOP;
645
646 IF (l_debug = 1)
647 THEN
648 mdebug ('Calc.SystemQty =: ' || TO_CHAR (x_system_quantity));
649 mdebug ('Calc. Secondary SystemQty =: ' || TO_CHAR (x_sec_system_quantity));
650 END IF;
651 -- serial control
652 ELSIF (p_serial_number_control IN (2, 5) AND p_serial_count_option > 1)
653 THEN
654 FOR c_rec IN l_sysqtyser_csr (p_inventory_item_id,
655 p_organization_id,
656 p_subinventory,
657 p_revision,
658 p_locator_id,
659 p_lot_number,
660 p_serial_number
661 )
662 LOOP
663 --
664 x_system_quantity := c_rec.system_quantity;
665 x_sec_system_quantity := NULL;
666
667 IF (l_debug = 1)
668 THEN
669 mdebug ('Calc.SystemQty : Inside loop-2 ');
670 END IF;
671 --
672 END LOOP;
673
674 IF (l_debug = 1)
675 THEN
676 mdebug ('Calc.SystemQty 2=: ' || TO_CHAR (x_system_quantity));
677 END IF;
678 END IF;
679
680 IF mtl_cceoi_var_pvt.g_primary_uom_code <> mtl_cceoi_var_pvt.g_uom_code
681 THEN
682 x_system_quantity :=
683 NVL
684 (inv_convert.inv_um_convert
685 (item_id => p_inventory_item_id,
686 PRECISION => 5,
687 from_quantity => x_system_quantity,
688 from_unit => mtl_cceoi_var_pvt.g_primary_uom_code,
689 to_unit => mtl_cceoi_var_pvt.g_uom_code,
690 from_name => NULL,
691 to_name => NULL
692 ),
693 0
694 );
695
696 IF (l_debug = 1)
697 THEN
698 mdebug ('Calc.convert System Qty =: ' || TO_CHAR (x_system_quantity));
699 END IF;
700 END IF;
701
702 --
703 -- END of API body
704 -- Standard check of p_commit
705 IF fnd_api.to_boolean (p_commit)
706 THEN
707 COMMIT;
708 END IF;
709
710 -- Standard call to get message count and if count is 1, get message info
711 fnd_msg_pub.count_and_get (p_count => x_msg_count,
712 p_data => x_msg_data);
713 EXCEPTION
714 WHEN fnd_api.g_exc_error
715 THEN
716 --
717 ROLLBACK TO calculate_systemquantity;
718
719 --
720 IF (l_debug = 1)
721 THEN
722 mdebug ('Calc.sys : Exception Error ' || SQLERRM);
723 END IF;
724
725 x_return_status := fnd_api.g_ret_sts_error;
726 --
727 fnd_msg_pub.count_and_get (p_count => x_msg_count,
728 p_data => x_msg_data
729 );
730 --
731 WHEN fnd_api.g_exc_unexpected_error
732 THEN
733 --
734 ROLLBACK TO calculate_systemquantity;
735
736 --
737 IF (l_debug = 1)
738 THEN
739 mdebug ('Calc.sys : Unexp Exception Error: ' || SQLERRM);
740 END IF;
741
742 x_return_status := fnd_api.g_ret_sts_unexp_error;
743 --
744 fnd_msg_pub.count_and_get (p_count => x_msg_count,
745 p_data => x_msg_data
746 );
747 --
748 WHEN OTHERS
749 THEN
750 --
751 IF (l_debug = 1)
752 THEN
753 mdebug ('Calc.sys : Others Exception Error ' || SQLERRM);
754 END IF;
755
756 ROLLBACK TO calculate_systemquantity;
757 --
758 x_return_status := fnd_api.g_ret_sts_unexp_error;
759
760 --
761 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
762 THEN
763 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
764 END IF;
765
766 --
767 fnd_msg_pub.count_and_get (p_count => x_msg_count,
768 p_data => x_msg_data
769 );
770 END;
771 END;
772 -- END INVCONV
773
774 /*============================================================================+
775 | Copyright (c) 1998 Oracle Corporation Belmont, California, USA |
776 | All rights reserved. |
777 | Oracle Manufacturing |
778 +=============================================================================+
779 |
780 | PROGRAM NAME: MTL_INVCCEOI_SERIAL.pls
781 | PURPOSE: Description
782 |
783 | DESIGN:
784 |
785 | CALLING FORMAT:
786 | MTL_INV_SERIAL_CHK user/pass arg1 ...
787 |
788 | CALLED BY: Oracle Manufacturing Form
789 |
790 | HISTORY
791 | 22-May-98 Suresh Created
792 +============================================================================*/
793 /*-------------------------------------------------------------------------+
794 | Every serial number is given a current_status which indicates where the
795 | unit is and for what transactions it is available. Supported statuses
796 | are:
797 | o 1 The unit is defined but has not been received into or issued out
798 | of stores.
799 | o 3 The unit has been received into stores.
800 | o 4 The unit has been issued out of stores.
801 | o 5 The unit has been issued out of stores and now resides in
802 | intransit.
803 | In addition, there are several types of serial control which determine
804 | under what conditions serialized units are required. Supported serial
805 | controls are:
806 | o 1 No serial number control.
807 | o 2 Predefined S/N - full control.
808 | o 3 Predefined S/N - inventory receipt.
809 | o 5 Dynamic entry at inventory receipt.
810 | o 6 Dynamic entry at sales order issue.
811 |
812 | Dynamically create a new serial number record. We must
813 | follow the serial number uniqueness criteria specified
814 | in the inventory parameters of this organization. The
815 | possible criteria are:
816 |
817 | o 1 Unique serial numbers within inventory items.
818 | No duplicate serial numbers for any particular
819 | inventory item across all organizations.
820 |
821 | A serial number may be assigned to at most one
822 | unit of each item across all organizations. This
823 | translates into at most one record in
824 | MTL_SERIAL_NUMBERS for each combination of
825 | SERIAL_NUMBER and INVENTORY_ITEM_ID.
826 |
827 | o 2 Unique serial numbers within organization.
828 | No duplicate serial numbers within any particular
829 | organization.
830 |
831 | A serial number may be assigned to at most one unit
832 | of one item in each organization, with the caveat
833 | that the same serial number may not be assigned to
834 | the same item in two different organizations. This
835 | translates into at most one record in
836 | MTL_SERIAL_NUMBERS for each combination of
837 | SERIAL_NUMBER and INVENTORY_ITEM_ID with the
838 | overriding condition that there be at most one
839 | record for any given combination of SERIAL_NUMBER
840 | and ORGANIZATION_ID.
841 |
842 | o 3 Unique serial numbers across organizations.
843 | No duplicate serial numbers in the entire system.
844 |
845 | A serial number may be assigned to at most one unit
846 | of one item across all organizations. This
847 | translates into at most one record in
848 | MTL_SERIAL_NUMBERS for each value of SERIAL_NUMBER.
849 +--------------------------------------------------------------------------*/
850 FUNCTION CHECK_SERIAL_NUMBER_LOCATION
851 (
852 P_SERIAL_NUMBER IN VARCHAR2,
853 P_ITEM_ID IN NUMBER,
854 P_ORGANIZATION_ID IN NUMBER,
855 P_SERIAL_NUMBER_TYPE IN NUMBER,
856 P_SERIAL_CONTROL IN NUMBER,
857 P_REVISION IN VARCHAR2,
858 P_LOT_NUMBER IN VARCHAR2,
859 P_SUBINVENTORY IN VARCHAR2,
860 P_LOCATOR_ID IN NUMBER,
861 P_ISSUE_RECEIPT IN VARCHAR2 -- R -receipt I - issue
862
863 ) RETURN BOOLEAN IS
864 -- Declare Local variables
865 L_current_status NUMBER(38);
866 L_current_revision VARCHAR2(4);
867 L_current_lot_number VARCHAR2(80); -- INVCONV
868 L_current_subinventory VARCHAR2(10);
869 L_current_locator_id NUMBER(38) ;
870 L_current_organization_id NUMBER(38) ;
871 L_nothing VARCHAR2(30);
872 L_user_id NUMBER;
873 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
874 BEGIN
875 IF P_SERIAL_CONTROL NOT IN (2,5) THEN
876 RETURN(FALSE);
877 END IF;
878 /*-------------------------------------------+
879 | Check for existence of the serial number
880 +-------------------------------------------*/
881 BEGIN
882 IF (l_debug = 1) THEN
883 MDEBUG( 'Begin of CheckSerl');
884 END IF;
885 -- Validate Serial Number for exist
886 SELECT decode(current_status,6,1,current_status),
887 revision,
888 lot_number,
889 current_subinventory_code,
890 current_locator_id,
891 current_organization_id
892 INTO L_current_status,
893 L_current_revision,
894 L_current_lot_number,
895 L_current_subinventory,
896 L_current_locator_id,
897 L_current_organization_id
898 FROM MTL_SERIAL_NUMBERS
899 WHERE inventory_item_id = P_Item_id
900 AND serial_number = P_serial_number;
901 IF (l_debug = 1) THEN
902 MDEBUG( 'CheckSerl - found');
903 END IF;
904 IF (P_ISSUE_RECEIPT = 'I' and L_current_status = 3
905 and P_ORGANIZATION_ID = L_current_organization_id
906 and P_REVISION = L_current_revision
907 and P_LOCATOR_ID = L_current_locator_id
908 and P_LOT_NUMBER = L_current_lot_number
909 and P_SUBINVENTORY = L_current_subinventory )
910 OR
911 (P_ISSUE_RECEIPT IN ('I','R') and L_current_status = 1
912 and P_ORGANIZATION_ID = L_current_organization_id)
913 OR
914 (P_ISSUE_RECEIPT = 'R' and L_current_status = 4)
915 THEN
916 IF (l_debug = 1) THEN
917 MDEBUG( 'CheckSerl - conditin satisfied');
918 END IF;
919 RETURN(TRUE);
920 ELSE
921 IF (l_debug = 1) THEN
922 MDEBUG( 'CheckSerl - conditin not satisfied');
923 END IF;
924 RETURN(FALSE);
925 END IF;
926 EXCEPTION
927 WHEN NO_DATA_FOUND THEN
928 IF (l_debug = 1) THEN
929 MDEBUG( 'CheckSerl - no-data-found-1');
930 END IF;
931 IF P_SERIAL_CONTROL = 5 THEN -- Dynamic Serial Control Check
932 -- UniqueCheck Routine
933 BEGIN
934 IF P_SERIAL_NUMBER_TYPE = 2 THEN
935 IF (l_debug = 1) THEN
936 MDEBUG( 'CheckSerl - Serl No - 2');
937 END IF;
938 BEGIN
939 SELECT 'x'
940 INTO L_nothing
941 FROM MTL_SERIAL_NUMBERS
942 WHERE SERIAL_NUMBER = P_serial_number
943 AND CURRENT_ORGANIZATION_ID + 0 = P_organization_id;
944 IF L_nothing IS NOT NULL then
945 IF (l_debug = 1) THEN
946 MDEBUG( 'CheckSerl - data-found-2');
947 END IF;
948 RETURN(FALSE);
949 END IF;
950 EXCEPTION
951 WHEN NO_DATA_FOUND THEN
952 BEGIN
953 SELECT 'x'
954 INTO L_nothing
955 FROM MTL_SERIAL_NUMBERS S,
956 MTL_PARAMETERS P
957 WHERE S.CURRENT_ORGANIZATION_ID = P.ORGANIZATION_ID
958 AND S.SERIAL_NUMBER = P_serial_number
959 AND P.SERIAL_NUMBER_TYPE = 3;
960 IF L_nothing IS NOT NULL then
961 IF (l_debug = 1) THEN
962 MDEBUG( 'CheckSerl - data-found-3');
963 END IF;
964 RETURN(FALSE);
965 END IF;
966 EXCEPTION
967 WHEN NO_DATA_FOUND THEN
968 -- Dynamic Create Serial No.
969 IF (l_debug = 1) THEN
970 MDEBUG( 'CheckSerl - data-not found-Dynamic creation');
971 END IF;
972 L_user_id := FND_GLOBAL.USER_ID ;
973 begin
974 INSERT INTO MTL_SERIAL_NUMBERS
975 (INVENTORY_ITEM_ID,
976 SERIAL_NUMBER,
977 LAST_UPDATE_DATE,
978 LAST_UPDATED_BY,
979 INITIALIZATION_DATE,
980 CREATION_DATE,
981 CREATED_BY,
982 LAST_UPDATE_LOGIN,
983 CURRENT_STATUS,
984 CURRENT_ORGANIZATION_ID)
985 VALUES
986 (P_item_id, P_SERIAL_NUMBER, sysdate,
987 L_user_id, sysdate, sysdate,
988 L_user_id, -1, 6,P_organization_id);
989
990 exception
991 when others then null;
992 IF (l_debug = 1) THEN
993 MDEBUG( 'CheckSerl - data-not found-Dynamic creation - exception');
994 END IF;
995 end;
996 RETURN(TRUE);
997 WHEN OTHERS THEN
998 IF (l_debug = 1) THEN
999 MDEBUG( 'CheckSerl - data-not found-exception');
1000 END IF;
1001 RETURN(FALSE);
1002 END;
1003 WHEN OTHERS THEN
1004 RETURN(FALSE);
1005 END;
1006 ELSIF P_SERIAL_NUMBER_TYPE = 3 THEN
1007 IF (l_debug = 1) THEN
1008 MDEBUG( 'CheckSerl - Serl No - 3');
1009 END IF;
1010 BEGIN
1011 SELECT 'x'
1012 INTO L_nothing
1013 FROM MTL_SERIAL_NUMBERS
1014 WHERE SERIAL_NUMBER = P_serial_number;
1015 IF L_nothing IS NOT NULL THEN
1016 IF (l_debug = 1) THEN
1017 MDEBUG( 'CheckSerl - Serl No found - 1');
1018 END IF;
1019 RETURN(FALSE);
1020 END IF;
1021 EXCEPTION
1022 WHEN NO_DATA_FOUND THEN
1023 IF (l_debug = 1) THEN
1024 MDEBUG( 'CheckSerl - Serl No inserting - 1');
1025 END IF;
1026 L_user_id := FND_GLOBAL.USER_ID ;
1027 begin
1028 INSERT INTO MTL_SERIAL_NUMBERS
1029 (INVENTORY_ITEM_ID,
1030 SERIAL_NUMBER,
1031 LAST_UPDATE_DATE,
1032 LAST_UPDATED_BY,
1033 INITIALIZATION_DATE,
1034 CREATION_DATE,
1035 CREATED_BY,
1036 LAST_UPDATE_LOGIN,
1037 CURRENT_STATUS,
1038 CURRENT_ORGANIZATION_ID)
1039 VALUES
1040 (P_item_id, P_SERIAL_NUMBER, sysdate,
1041 L_user_id, sysdate, sysdate,
1042 L_user_id, -1, 6, P_ORGANIZATION_ID);
1043
1044 exception
1045 when others then null;
1046 IF (l_debug = 1) THEN
1047 MDEBUG( 'CheckSerl - Serl No inserting Except- 1');
1048 END IF;
1049 end;
1050 RETURN(TRUE);
1051 WHEN OTHERS THEN
1052 IF (l_debug = 1) THEN
1053 MDEBUG( 'CheckSerl - Serl No inserting Except- 2');
1054 END IF;
1055 RETURN(FALSE);
1056 END;
1057 ELSIF P_SERIAL_NUMBER_TYPE = 1 THEN
1058 BEGIN
1059 SELECT 'x'
1060 INTO L_nothing
1061 FROM MTL_SERIAL_NUMBERS S,
1062 MTL_PARAMETERS P
1063 WHERE S.INVENTORY_ITEM_ID = P_item_id
1064 AND S.CURRENT_ORGANIZATION_ID = P.ORGANIZATION_ID
1065 AND S.SERIAL_NUMBER = P_serial_number
1066 AND P.SERIAL_NUMBER_TYPE = 1;
1067 IF L_nothing IS NOT NULL then
1068 IF (l_debug = 1) THEN
1069 MDEBUG( 'CheckSerl - data-found-4');
1070 END IF;
1071 RETURN(FALSE);
1072 END IF;
1073 EXCEPTION
1074 WHEN NO_DATA_FOUND THEN
1075 L_user_id := FND_GLOBAL.USER_ID ;
1076 begin
1077 INSERT INTO MTL_SERIAL_NUMBERS
1078 (INVENTORY_ITEM_ID,
1079 SERIAL_NUMBER,
1080 LAST_UPDATE_DATE,
1081 LAST_UPDATED_BY,
1082 INITIALIZATION_DATE,
1083 CREATION_DATE,
1084 CREATED_BY,
1085 LAST_UPDATE_LOGIN,
1086 CURRENT_STATUS,
1087 CURRENT_ORGANIZATION_ID)
1088 VALUES
1089 (P_item_id, P_SERIAL_NUMBER, sysdate,
1090 L_user_id, sysdate, sysdate,
1091 L_user_id, -1, 6, P_ORGANIZATION_ID);
1092
1093 exception
1094 when others then null;
1095 IF (l_debug = 1) THEN
1096 MDEBUG( 'CheckSerl - Serl No inserting Except- 4');
1097 END IF;
1098 end;
1099 WHEN OTHERS THEN
1100 IF (l_debug = 1) THEN
1101 MDEBUG( 'CheckSerl - Serl No inserting Except- 4');
1102 END IF;
1103 RETURN(FALSE);
1104 END;
1105 END IF;
1106 END ;
1107 IF (l_debug = 1) THEN
1108 MDEBUG( 'CheckSerl - Last');
1109 END IF;
1110 RETURN(TRUE);
1111 ELSE
1112 IF (l_debug = 1) THEN
1113 MDEBUG( 'CheckSerl - Last - 2');
1114 END IF;
1115 RETURN(FALSE);
1116 END IF;
1117 WHEN OTHERS THEN
1118 IF (l_debug = 1) THEN
1119 MDEBUG( 'CheckSerl - Last - 3');
1120 END IF;
1121 RETURN(FALSE);
1122 END;
1123 END;
1124
1125
1126 PROCEDURE Get_LPN_Item_SysQty
1127 (
1128 p_api_version IN NUMBER
1129 , p_init_msg_lst IN VARCHAR2
1130 , p_commit IN VARCHAR2
1131 , x_return_status OUT NOCOPY VARCHAR2
1132 , x_msg_count OUT NOCOPY NUMBER
1133 , x_msg_data OUT NOCOPY VARCHAR2
1134 , p_organization_id IN NUMBER
1135 , p_lpn_id IN NUMBER
1136 , p_inventory_item_id IN NUMBER
1137 , p_lot_number IN VARCHAR2
1138 , p_revision IN VARCHAR2
1139 , p_serial_number IN VARCHAR2
1140 , p_cost_group_id IN NUMBER
1141 , x_lpn_systemqty OUT NOCOPY NUMBER
1142 )
1143 IS
1144 l_subinventory_code VARCHAR2(25);
1145 l_locator_id NUMBER;
1146 l_result NUMBER;
1147 l_org INV_Validate.ORG;
1148 l_lpn WMS_CONTAINER_PUB.LPN;
1149 l_content_item INV_Validate.ITEM;
1150 l_lot INV_Validate.LOT;
1151 l_loaded_sys_qty NUMBER; --bug 2640378
1152
1153 e_Invalid_Inputs EXCEPTION;
1154
1155 L_api_version CONSTANT NUMBER := 0.9;
1156 L_api_name CONSTANT VARCHAR2(30) := 'Get_LPN_Item_SysQty';
1157 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1158 BEGIN
1159 -- Standard start of API savepoint
1160 SAVEPOINT Get_LPN_Item_SysQty;
1161
1162 -- Standard Call to check for call compatibility
1163 IF NOT FND_API.Compatible_API_Call(l_api_version
1164 , p_api_version
1165 , l_api_name
1166 , G_PKG_NAME) THEN
1167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1168 END IF;
1169
1170 -- Initialisize API return status to access
1171 x_return_status := FND_API.G_RET_STS_SUCCESS;
1172
1173 -- Validate Inputs
1174 -- Validate Organization ID
1175 IF (p_organization_id IS NOT NULL) THEN
1176 l_org.organization_id := p_organization_id;
1177 l_result := INV_Validate.Organization(l_org);
1178 IF (l_result = INV_Validate.F) THEN
1179 IF (l_debug = 1) THEN
1180 mdebug('invalid org id');
1181 END IF;
1182 RAISE e_Invalid_Inputs;
1183 END IF;
1184 END IF;
1185
1186 -- Validate LPN
1187 IF p_lpn_id IS NOT NULL THEN
1188 l_lpn.lpn_id := p_lpn_id;
1189 l_lpn.license_plate_number := NULL;
1190 l_result := WMS_CONTAINER_PUB.Validate_LPN(l_lpn);
1191 IF (l_result = INV_Validate.F) THEN
1192 IF (l_debug = 1) THEN
1193 mdebug('invalid lpn id');
1194 END IF;
1195 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
1196 FND_MSG_PUB.ADD;
1197 RAISE e_Invalid_Inputs;
1198 END IF;
1199 END IF;
1200
1201 -- Validate Inventory Item ID
1202 IF (p_inventory_item_id IS NOT NULL) THEN
1203 l_content_item.inventory_item_id := p_inventory_item_id;
1204 l_result := INV_Validate.inventory_item(l_content_item, l_org);
1205 IF (l_result = INV_Validate.F) THEN
1206 IF (l_debug = 1) THEN
1207 mdebug('invalid inventory item id');
1208 END IF;
1209 RAISE e_Invalid_Inputs;
1210 END IF;
1211 END IF;
1212
1213 /*
1214 -- Validate Lot Number
1215 IF (p_container_item_id IS NOT NULL) THEN
1216 IF (l_container_item.lot_control_code = 2) THEN
1217 IF (p_lot_number IS NOT NULL) THEN
1218 l_lot.lot_number := p_lot_number;
1219 l_result := INV_Validate.Lot_Number(l_lot, l_org, l_container_item,
1220 l_sub, l_locator, p_revision);
1221 IF (l_result = INV_Validate.F) THEN
1222 IF (l_debug = 1) THEN
1223 mdebug('invalid lot number');
1224 END IF;
1225 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LOT');
1226 FND_MSG_PUB.ADD;
1227 RAISE FND_API.G_EXC_ERROR;
1228 END IF;
1229 END IF;
1230 END IF;
1231 END IF;
1232
1233 -- Validate Revision Number
1234 IF (p_container_item_id IS NOT NULL) THEN
1235 IF (l_container_item.revision_qty_control_code = 2) THEN
1236 IF (p_revision IS NOT NULL) THEN
1237 l_result := INV_Validate.Revision(p_revision, l_org, l_container_item);
1238 IF (l_result = INV_Validate.F) THEN
1239 IF (l_debug = 1) THEN
1240 mdebug('invalid revision number');
1241 END IF;
1242 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_REVISION');
1243 FND_MSG_PUB.ADD;
1244 RAISE FND_API.G_EXC_ERROR;
1245 END IF;
1246 END IF;
1247 END IF;
1248 END IF;
1249
1250 -- Validate Serial Number
1251 IF (p_serial_number IS NOT NULL) THEN
1252 l_serial.serial_number := p_serial_number;
1253 l_result := INV_Validate.validate_serial(l_serial, l_org, l_container_item,
1254 l_sub, l_lot, l_locator, p_revision);
1255 IF (l_result = INV_Validate.F) THEN
1256 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SERIAL');
1257 FND_MSG_PUB.ADD;
1258 RAISE FND_API.G_EXC_ERROR;
1259 END IF;
1260 END IF;
1261 */
1262
1263 SELECT subinventory_code, locator_id
1264 INTO l_subinventory_code, l_locator_id
1265 FROM WMS_LICENSE_PLATE_NUMBERS
1266 WHERE organization_id = p_organization_id
1267 AND lpn_id = p_lpn_id;
1268
1269 -- Find quantity of item within container.
1270 IF (p_serial_number IS NULL) THEN
1271 BEGIN
1272 SELECT nvl(sum(quantity),0) --BUG3026540
1273 INTO x_lpn_systemqty
1274 FROM WMS_LPN_CONTENTS
1275 WHERE parent_lpn_id = p_lpn_id
1276 AND organization_id = p_organization_id
1277 AND inventory_item_id = p_inventory_item_id
1278 AND NVL(lot_number, '@') = NVL(p_lot_number, '@')
1279 AND NVL(revision, '@') = NVL(p_revision, '@')
1280 AND NVL(serial_number, '@') = NVL(p_serial_number, '@')
1281 AND NVL(cost_group_id, -1) = NVL(p_cost_group_id, -1);
1282
1283 --bug 2640378 start
1284 select nvl(sum(quantity),0)
1285 into l_loaded_sys_qty
1286 from wms_loaded_quantities_v
1287 where nvl(content_lpn_id,nvl(lpn_id,-1)) = p_lpn_id
1288 and inventory_item_id = p_inventory_item_id
1289 and NVL(lot_number, '@') = NVL(p_lot_number, '@')
1290 and NVL(revision, '@') = NVL(p_revision, '@');
1291 -- and NVL(cost_group_id, -1) = NVL(p_cost_group_id, -1);--bug3681566
1292
1293 IF (l_debug = 1) THEN
1294 mdebug('LPN ID ' || p_lpn_id ||' loaded quantity ' || l_loaded_sys_qty);
1295 END IF;
1296 IF l_loaded_sys_qty > 0 THEN
1297 x_lpn_systemqty := x_lpn_systemqty - l_loaded_sys_qty;
1298 END IF; -- bug 2640378
1299 EXCEPTION
1300 WHEN NO_DATA_FOUND THEN
1301 x_lpn_systemqty := 0;
1302 END;
1303
1304 ELSE -- serial number is not null
1305 IF (l_debug = 1) THEN
1306 mdebug('serial being counted ' || p_serial_number);
1307 END IF;
1308 SELECT COUNT(*)
1309 INTO x_lpn_systemqty
1310 FROM mtl_serial_numbers
1311 WHERE lpn_id = p_lpn_id
1312 AND inventory_item_id = p_inventory_item_id
1313 AND current_organization_id = p_organization_id
1314 AND serial_number = p_serial_number
1315 AND NVL(lot_number, '@') = NVL(p_lot_number, '@')
1316 AND NVL(revision, '@') = NVL(p_revision, '@')
1317 AND NVL(cost_group_id, -1) = NVL(p_cost_group_id, -1)
1318 AND INV_CYC_LOVS.is_serial_loaded(p_organization_id,p_inventory_item_id,p_serial_number,p_lpn_id) = 2;
1319 END IF;
1320
1321 IF (x_lpn_systemqty IS NULL) THEN
1322 x_lpn_systemqty := 0;
1323 END IF;
1324
1325 EXCEPTION
1326 WHEN e_Invalid_Inputs THEN
1327 ROLLBACK TO Get_LPN_Item_SysQty;
1328 x_return_status := FND_API.G_RET_STS_ERROR;
1329 FND_MSG_PUB.Count_And_Get
1330 ( p_count => x_msg_count,
1331 p_data => x_msg_data
1332 );
1333 IF (l_debug = 1) THEN
1334 mdebug('Invalid Inputs');
1335 END IF;
1336
1337 WHEN OTHERS THEN
1338 IF (l_debug = 1) THEN
1339 mdebug('Others exception');
1340 END IF;
1341 x_lpn_systemqty := 0;
1342
1343 END Get_LPN_Item_SysQty;
1344
1345 -- BEGIN INVCONV
1346 -- Overloaded procedure to return secondary quantity
1347 PROCEDURE Get_LPN_Item_SysQty (
1348 p_api_version IN NUMBER
1349 , p_init_msg_lst IN VARCHAR2
1350 , p_commit IN VARCHAR2
1351 , x_return_status OUT NOCOPY VARCHAR2
1352 , x_msg_count OUT NOCOPY NUMBER
1353 , x_msg_data OUT NOCOPY VARCHAR2
1354 , p_organization_id IN NUMBER
1355 , p_lpn_id IN NUMBER
1356 , p_inventory_item_id IN NUMBER
1357 , p_lot_number IN VARCHAR2
1358 , p_revision IN VARCHAR2
1359 , p_serial_number IN VARCHAR2
1360 , p_cost_group_id IN NUMBER
1361 , x_lpn_systemqty OUT NOCOPY NUMBER
1362 , x_lpn_sec_systemqty OUT NOCOPY NUMBER
1363 ) IS
1364 l_subinventory_code VARCHAR2 (25);
1365 l_locator_id NUMBER;
1366 l_result NUMBER;
1367 l_org inv_validate.org;
1368 l_lpn wms_container_pub.lpn;
1369 l_content_item inv_validate.item;
1370 l_lot inv_validate.lot;
1371 l_loaded_sys_qty NUMBER;
1372 l_loaded_sec_sys_qty NUMBER;
1373 e_invalid_inputs EXCEPTION;
1374 l_api_version CONSTANT NUMBER := 0.9;
1375 l_api_name CONSTANT VARCHAR2 (30) := 'Get_LPN_Item_SysQty2';
1376 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1377 BEGIN
1378 -- Standard start of API savepoint
1379 SAVEPOINT get_lpn_item_sysqty;
1380
1381 -- Standard Call to check for call compatibility
1382 IF NOT fnd_api.compatible_api_call (l_api_version
1383 , p_api_version
1384 , l_api_name
1385 , g_pkg_name
1386 ) THEN
1387 RAISE fnd_api.g_exc_unexpected_error;
1388 END IF;
1389
1390 -- Initialisize API return status to access
1391 x_return_status := fnd_api.g_ret_sts_success;
1392
1393 -- Validate Inputs
1394 -- Validate Organization ID
1395 IF (p_organization_id IS NOT NULL) THEN
1396 l_org.organization_id := p_organization_id;
1397 l_result := inv_validate.ORGANIZATION (l_org);
1398
1399 IF (l_result = inv_validate.f) THEN
1400 IF (l_debug = 1) THEN
1401 mdebug ('invalid org id');
1402 END IF;
1403
1404 RAISE e_invalid_inputs;
1405 END IF;
1406 END IF;
1407
1408 -- Validate LPN
1409 IF p_lpn_id IS NOT NULL THEN
1410 l_lpn.lpn_id := p_lpn_id;
1411 l_lpn.license_plate_number := NULL;
1412 l_result := wms_container_pub.validate_lpn (l_lpn);
1413
1414 IF (l_result = inv_validate.f) THEN
1415 IF (l_debug = 1) THEN
1416 mdebug ('invalid lpn id');
1417 END IF;
1418
1419 fnd_message.set_name ('WMS', 'WMS_CONT_INVALID_LPN');
1420 fnd_msg_pub.ADD;
1421 RAISE e_invalid_inputs;
1422 END IF;
1423 END IF;
1424
1425 -- Validate Inventory Item ID
1426 IF (p_inventory_item_id IS NOT NULL) THEN
1427 l_content_item.inventory_item_id := p_inventory_item_id;
1428 l_result := inv_validate.inventory_item (l_content_item, l_org);
1429
1430 IF (l_result = inv_validate.f) THEN
1431 IF (l_debug = 1) THEN
1432 mdebug ('invalid inventory item id');
1433 END IF;
1434
1435 RAISE e_invalid_inputs;
1436 END IF;
1437 END IF;
1438
1439 SELECT subinventory_code
1440 , locator_id
1441 INTO l_subinventory_code
1442 , l_locator_id
1443 FROM wms_license_plate_numbers
1444 WHERE organization_id = p_organization_id
1445 AND lpn_id = p_lpn_id;
1446
1447 -- Find quantity of item within container.
1448 IF (p_serial_number IS NULL) THEN
1449 BEGIN
1450 --For R12 we need to consider primary_quantity instead of quantity from WLC (bug 6833992)
1451 SELECT NVL (SUM (primary_quantity), 0)
1452 , NVL (SUM (secondary_quantity), 0)
1453 INTO x_lpn_systemqty
1454 , x_lpn_sec_systemqty
1455 FROM wms_lpn_contents
1456 WHERE parent_lpn_id = p_lpn_id
1457 AND organization_id = p_organization_id
1458 AND inventory_item_id = p_inventory_item_id
1459 AND NVL (lot_number, '@') = NVL (p_lot_number, '@')
1460 AND NVL (revision, '@') = NVL (p_revision, '@')
1461 AND NVL (serial_number, '@') = NVL (p_serial_number, '@')
1462 AND NVL (cost_group_id, -1) = NVL (p_cost_group_id, -1);
1463
1464 SELECT NVL (SUM (quantity), 0)
1465 , NVL (SUM (secondary_quantity), 0)
1466 INTO l_loaded_sys_qty
1467 , l_loaded_sec_sys_qty
1468 FROM wms_loaded_quantities_v
1469 WHERE NVL (content_lpn_id, NVL (lpn_id, -1) ) = p_lpn_id
1470 AND inventory_item_id = p_inventory_item_id
1471 AND NVL (lot_number, '@') = NVL (p_lot_number, '@')
1472 AND NVL (revision, '@') = NVL (p_revision, '@');
1473
1474 IF (l_debug = 1) THEN
1475 mdebug ('LPN ID ' || p_lpn_id || ' loaded quantity ' || l_loaded_sys_qty);
1476 END IF;
1477
1478 IF l_loaded_sys_qty > 0 THEN
1479 x_lpn_systemqty := x_lpn_systemqty - l_loaded_sys_qty;
1480 END IF;
1481
1482 IF l_loaded_sec_sys_qty > 0 THEN
1483 x_lpn_sec_systemqty := x_lpn_sec_systemqty - l_loaded_sec_sys_qty;
1484 END IF;
1485 EXCEPTION
1486 WHEN NO_DATA_FOUND THEN
1487 x_lpn_systemqty := 0;
1488 x_lpn_sec_systemqty := 0;
1489 END;
1490 ELSE -- serial number is not null
1491 IF (l_debug = 1) THEN
1492 mdebug ('serial being counted ' || p_serial_number);
1493 END IF;
1494
1495 SELECT COUNT (*)
1496 INTO x_lpn_systemqty
1497 FROM mtl_serial_numbers
1498 WHERE lpn_id = p_lpn_id
1499 AND inventory_item_id = p_inventory_item_id
1500 AND current_organization_id = p_organization_id
1501 AND serial_number = p_serial_number
1502 AND NVL (lot_number, '@') = NVL (p_lot_number, '@')
1503 AND NVL (revision, '@') = NVL (p_revision, '@')
1504 AND NVL (cost_group_id, -1) = NVL (p_cost_group_id, -1)
1505 AND inv_cyc_lovs.is_serial_loaded (p_organization_id
1506 , p_inventory_item_id
1507 , p_serial_number
1508 , p_lpn_id
1509 ) = 2;
1510
1511 x_lpn_sec_systemqty := 0;
1512 END IF;
1513
1514 IF (x_lpn_systemqty IS NULL) THEN
1515 x_lpn_systemqty := 0;
1516 END IF;
1517 EXCEPTION
1518 WHEN e_invalid_inputs THEN
1519 ROLLBACK TO get_lpn_item_sysqty;
1520 x_return_status := fnd_api.g_ret_sts_error;
1521 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1522
1523 IF (l_debug = 1) THEN
1524 mdebug ('Invalid Inputs');
1525 END IF;
1526 WHEN OTHERS THEN
1527 IF (l_debug = 1) THEN
1528 mdebug ('Others exception');
1529 END IF;
1530
1531 x_lpn_systemqty := 0;
1532 x_lpn_sec_systemqty := 0;
1533 END Get_LPN_Item_SysQty;
1534 -- END INVCONV
1535
1536 FUNCTION Exists_CC_Entries
1537 (
1538 p_organization_id IN NUMBER
1539 , p_parent_lpn_id IN NUMBER
1540 , p_inventory_item_id IN NUMBER
1541 , p_cost_group_id IN NUMBER
1542 , p_lot_number IN VARCHAR2
1543 , p_revision IN VARCHAR2
1544 , p_serial_number IN VARCHAR2
1545 )
1546 RETURN BOOLEAN IS
1547
1548 CURSOR cce_csr IS
1549 SELECT inventory_item_id
1550 FROM MTL_CYCLE_COUNT_ENTRIES
1551 WHERE organization_id = p_organization_id
1552 AND parent_lpn_id = p_parent_lpn_id
1553 AND inventory_item_id = p_inventory_item_id
1554 AND NVL(lot_number, '@') = NVL(p_lot_number, '@')
1555 AND NVL(revision, '@') = NVL(p_revision, '@')
1556 AND NVL(serial_number, '@') = NVL(p_serial_number, '@')
1557 AND NVL(cost_group_id, -1) = NVL(p_cost_group_id, -1)
1558 AND entry_status_code IN (1, 3);
1559
1560 l_dummy MTL_CYCLE_COUNT_ENTRIES.inventory_item_id%TYPE;
1561
1562 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1563 BEGIN
1564 OPEN cce_csr;
1565 FETCH cce_csr INTO l_dummy;
1566 IF cce_csr%FOUND THEN
1567 CLOSE cce_csr;
1568 RETURN TRUE;
1569 END IF;
1570 CLOSE cce_csr;
1571 RETURN FALSE;
1572
1573 EXCEPTION
1574 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
1575 RETURN FALSE;
1576
1577 WHEN OTHERS THEN
1578 RETURN FALSE;
1579
1580 END Exists_CC_Entries;
1581
1582
1583 FUNCTION Exists_CC_Items
1584 (
1585 p_cc_header_id IN VARCHAR2
1586 , p_inventory_item_id IN NUMBER
1587 )
1588 RETURN BOOLEAN IS
1589
1590 CURSOR cci_csr IS
1591 SELECT inventory_item_id
1592 FROM MTL_CYCLE_COUNT_ITEMS
1593 WHERE inventory_item_id = p_inventory_item_id
1594 AND cycle_count_header_id = p_cc_header_id;
1595
1596 l_dummy MTL_CYCLE_COUNT_ITEMS.inventory_item_id%TYPE;
1597
1598 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1599 BEGIN
1600 OPEN cci_csr;
1601 FETCH cci_csr INTO l_dummy;
1602 IF cci_csr%FOUND THEN
1603 CLOSE cci_csr;
1604 RETURN TRUE;
1605 END IF;
1606 CLOSE cci_csr;
1607 RETURN FALSE;
1608
1609 EXCEPTION
1610 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
1611 RETURN FALSE;
1612
1613 WHEN OTHERS THEN
1614 RETURN FALSE;
1615
1616 END Exists_CC_Items;
1617
1618 --R12 Procedure to purge the mtl_item_bulkload_recs table
1619 PROCEDURE purge_bulkloadrecs_table
1620 ( p_request_id NUMBER ,
1621 p_commit BOOLEAN DEFAULT TRUE
1622 ) IS
1623 BEGIN
1624 DELETE
1625 FROM MTL_ITEM_BULKLOAD_RECS
1626 WHERE REQUEST_ID = p_request_id;
1627
1628 IF p_commit = TRUE THEN
1629 COMMIT;
1630 END IF;
1631 END purge_bulkloadrecs_table;
1632 --R12
1633
1634 END MTL_INV_UTIL_GRP;
[Home] [Help]