[Home] [Help]
PACKAGE BODY: APPS.CSP_VALIDATE_PUB
Source
1 PACKAGE BODY CSP_VALIDATE_PUB AS
2 /*$Header: cspgtvpb.pls 120.4.12020000.2 2013/01/29 10:05:18 htank ship $*/
3 -- Start of Comments
4 -- Package name : CSP_VALIDATE_PUB
5 -- File name : cspgtvpb.pls
6 -- Purpose : The package includes public procedures used for CSP.
7 -- History :
11 -- NOTE :
8 -- 25-Mar-2000, Modified error messages to comply with the CRM standards.
9 -- 20-Dev-1999, Included Get_Avail_Qty function
10 -- 10-Dec-1999, created by Vernon Lou
12 -- End of Comments
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_VALIDATE_PUB';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtvpb.pls';
15
16 FUNCTION Get_Onhand_Qty RETURN NUMBER Is
17 Begin
18 Return(G_qoh);
19 End;
20
21 FUNCTION Get_Available_Qty RETURN NUMBER Is
22 Begin
23 Return(G_atr);
24 End;
25
26 PROCEDURE CHECK_PART_AVAILABLE (
27 /* This procedure returns the avalibale quantity based on the given organization, subinventory, locator*/
28 P_API_VERSION_NUMBER IN NUMBER,
29 P_INVENTORY_ITEM_ID IN NUMBER,
30 P_ORGANIZATION_ID IN NUMBER,
31 P_SUBINVENTORY_CODE IN VARCHAR2,
32 P_LOCATOR_ID IN NUMBER,
33 P_REVISION IN VARCHAR2,
34 P_SERIAL_NUMBER IN VARCHAR2,
35 P_LOT_NUMBER IN VARCHAR2,
36 X_AVAILABLE_QUANTITY OUT NOCOPY NUMBER,
37 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
38 X_MSG_COUNT OUT NOCOPY NUMBER,
39 X_MSG_DATA OUT NOCOPY VARCHAR2
40 )
41 IS
42 l_return_status VARCHAR2(1);
43 l_msg_count NUMBER := 0;
44 l_msg_data VARCHAR2(200) := '';
45 l_api_version_number CONSTANT NUMBER := 1.00;
46 l_api_name CONSTANT VARCHAR2(30) := 'Check_Part_Available';
47 l_onhand_qty NUMBER;
48 l_reserved_qty NUMBER;
49 l_count_qty NUMBER;
50 l_check_existence NUMBER;
51 l_is_revision_control BOOLEAN := false;
52 l_is_lot_control BOOLEAN := false;
53 l_is_serial_control BOOLEAN := false;
54 l_org_id mtl_onhand_quantities.organization_id%type;
55 l_sub_code mtl_onhand_quantities.subinventory_code%type;
56 l_locator_id mtl_onhand_quantities.locator_id%type;
57 EXCP_USER_DEFINED EXCEPTION;
58 l_serial_control_code NUMBER := 0;
59 l_revision_control_code NUMBER := 0;
60 l_lot_control_code NUMBER := 0;
61 l_serial_status NUMBER := 0;
62 l_qoh NUMBER := 0;
63 l_rqoh NUMBER := 0;
64 l_qr NUMBER := 0;
65 l_qs NUMBER := 0;
66 l_att NUMBER := 0;
67 l_atr NUMBER := 0;
68
69 BEGIN
70 -- initialize message list
71 FND_MSG_PUB.initialize;
72 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
73 p_api_version_number,
74 l_api_name,
75 G_PKG_NAME)
76 THEN
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78 END IF;
79
80 IF p_organization_id IS NULL THEN
81 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
82 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
83 FND_MSG_PUB.ADD;
84 ELSE
85 BEGIN
86 select organization_id into l_check_existence
87 from mtl_parameters
88 where organization_id = p_organization_id;
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
92 FND_MSG_PUB.ADD;
93 RAISE EXCP_USER_DEFINED;
94 WHEN OTHERS THEN
95 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
96 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
97 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
98 fnd_message.set_token('TABLE', 'MTL_ORGANIZATIONS', TRUE);
99 FND_MSG_PUB.ADD;
100 RAISE EXCP_USER_DEFINED;
101 END;
102 END IF;
103
104 IF p_inventory_item_id IS NULL THEN
105 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
106 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_inventory_item_id ', TRUE);
107 FND_MSG_PUB.ADD;
108 RAISE EXCP_USER_DEFINED;
109 ELSE
110 BEGIN
111 -- validate whether the inventory_item_is exists in the given oranization_id
112 select inventory_item_id into l_check_existence
113 from mtl_system_items_kfv
114 where inventory_item_id = p_inventory_item_id
115 and organization_id = P_organization_id;
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 fnd_message.set_name('INV', 'INV-NO ITEM RECROD');
119 fnd_msg_pub.add;
120 RAISE EXCP_USER_DEFINED;
121 WHEN OTHERS THEN
122 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
123 fnd_message.set_token('ERR_FIELD', 'p_inventory_item_id', TRUE);
124 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
125 fnd_message.set_token('TABLE', 'MTL_SYSTEM_ITEMS', TRUE);
126 FND_MSG_PUB.ADD;
127 RAISE EXCP_USER_DEFINED;
128 END;
129 END IF;
130
131 -- check whether the item is under serial control
135 and inventory_item_id = p_inventory_item_id;
132 select serial_number_control_code into l_serial_control_code
133 from mtl_system_items
134 where organization_id = p_organization_id
136
137 IF l_serial_control_code <> 1 THEN
138 l_is_serial_control := true;
139 END IF;
140
141 -- check whether the item is under revision control
142 select revision_qty_control_code into l_revision_control_code
143 from mtl_system_items
144 where organization_id = p_organization_id
145 and inventory_item_id = p_inventory_item_id;
146
147 IF l_revision_control_code <> 1 THEN
148 l_is_revision_control := true;
149 END IF;
150
151 -- check whether the item is under lot control
152 select lot_control_code into l_lot_control_code
153 from mtl_system_items
154 where organization_id = p_organization_id
155 and inventory_item_id = p_inventory_item_id;
156
157 IF l_lot_control_code <> 1 THEN
158 l_is_lot_control := true;
159 END IF;
160
161 IF p_serial_number IS NULL THEN
162 -- If serial number is null, it means that the user does not want to query for the quantity of the item on a
163 -- specific serial number. It should not be taken for granted that the item is not under seiral control.
164 -- The same theory should be applied to the validation of revision control and serial control.
165 -- First check whether the item is under serial control, if yes, set l_is_serial_control to true.
166
167 inv_quantity_tree_pub.query_quantities(
168 p_api_version_number => l_api_version_number
169 , p_init_msg_lst => fnd_api.g_false
170 , x_return_status => l_return_status
171 , x_msg_count => l_msg_count
172 , x_msg_data => l_msg_data
173 , p_organization_id => p_organization_id
174 , p_inventory_item_id => p_inventory_item_id
175 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
176 , p_is_revision_control => l_is_revision_control
177 , p_is_lot_control => l_is_lot_control
178 , p_is_serial_control => l_is_serial_control
179 , p_demand_source_type_id => NULL
180 , p_demand_source_header_id => NULL
181 , p_demand_source_line_id => NULL
182 , p_demand_source_name => NULL
183 , p_lot_expiration_date => NULL
184 , p_revision => p_revision
185 , p_lot_number => p_lot_number
186 , p_subinventory_code => p_subinventory_code
187 , p_locator_id => p_locator_id
188 , x_qoh => l_qoh
189 , x_rqoh => l_rqoh
190 , x_qr => l_qr
191 , x_qs => l_qs
192 , x_att => l_att
193 , x_atr => l_atr);
194
195 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198
199
200 if l_qr is null then
201 l_qr := 0;
202 end if;
203
204 x_available_quantity := l_qoh - l_qr;
205
206 ELSE
207 -- serial number = not null
208 -- If the item is under serial control, we do not have to check its availability at subinventory and locator level.
209 -- The reason is that item_id (plus its serial number) should be unique under one organization.
210 IF l_serial_control_code = 1 THEN
211 fnd_message.set_name('INV', 'INV_ITEM_NOT_SERIAL_CONTROLLED');
212 FND_MSG_PUB.ADD;
213 RAISE EXCP_USER_DEFINED;
214 END IF;
215
216 l_serial_control_code := 0; -- reset l_serial_control_code for reuse
217
218 -- check whether the serial number resides in the store
219 select nvl(current_status, 1) into l_serial_status
220 from mtl_serial_numbers
221 where inventory_item_id = p_inventory_item_id
222 and serial_number = p_serial_number
223 and current_organization_id = p_organization_id;
224
225
226 IF l_serial_status = 3 THEN
227 l_onhand_qty := 1;
228 -- check whether the item with that specified serial number is reserved
229 select count(inventory_item_id) into l_count_qty
230 from mtl_reservations
231 where inventory_item_id = p_inventory_item_id
232 and organization_id = p_organization_id
233 and serial_number = p_serial_number;
234
235 x_available_quantity := l_onhand_qty - l_count_qty;
236 END IF;
237
238 -- l_msg_count := l_msg_count + 1;
239 -- l_msg_data := 'Operation completed successfully.';
240 END IF;
241
242 x_msg_count := l_msg_count;
243 x_msg_data := l_msg_data;
244 x_return_status := FND_API.G_RET_STS_SUCCESS;
245
246 EXCEPTION
247 WHEN EXCP_USER_DEFINED THEN
248 x_return_status := FND_API.G_RET_STS_ERROR;
249 x_available_quantity := NULL;
250 fnd_msg_pub.count_and_get
251 ( p_count => x_msg_count
252 , p_data => x_msg_data
253 );
254
255 /* WHEN EXCP_INVALID_ITEMS THEN
256 x_return_status := FND_API.G_RET_STS_SUCCESS;
257 x_msg_data := l_msg_data||'Return 0 quantity.';
258 x_available_quantity := NULL;
259 fnd_message.set_name('CSP', 'CSP_AVAIL_QTY');
260 fnd_message.set_token('ERROR', l_msg_data);
261 fnd_msg_pub.ADD;
262 fnd_msg_pub.count_and_get
263 ( p_count => x_msg_count
264 , p_data => x_msg_data
265 );
266 */
270 ( p_count => x_msg_count
267 WHEN FND_API.G_EXC_ERROR THEN
268 x_return_status := l_return_status;
269 fnd_msg_pub.count_and_get
271 , p_data => x_msg_data );
272 x_available_quantity := NULL;
273
274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 fnd_msg_pub.count_and_get
277 ( p_count => x_msg_count
278 , p_data => x_msg_data );
279 x_available_quantity := NULL;
280
281 WHEN OTHERS THEN
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
284 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
285 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
286 fnd_msg_pub.add;
287 fnd_msg_pub.count_and_get
288 ( p_count => x_msg_count
289 , p_data => x_msg_data);
290 x_available_quantity := NULL;
291
292 END CHECK_PART_AVAILABLE;
293
294
295 FUNCTION Get_Avail_Qty (
296 /* Name: get_avail_qty
297 Purpose: Get the available quantity of an item at organization level, subinventory level or locator level.
298 For Spares Management, avail_qty = unreserved_qty = total_onhand_qty - reserved_qty.
299 This function returns NULL when there is an error.
300 MODIFICATION HISTORY
301 -- Person Date Comments
302 -- --------- ------ ------------------------------------------
303 -- klou 02-Nov-99 Created
304 -- klou 25-Mar-00 Change the p_tree_mode from g_transaction_mode to g_reservation_mode when calling
305 the inv_quantity_tree_pub.query_quantities procedure.
306 -- End of comments
307 Author: Vernon Lou
308 Date: 2-Nov-99
309 03/25/00:
310 */
311 p_organization_id NUMBER,
312 p_subinventory_code VARCHAR2,
313 p_locator_id NUMBER,
314 p_inventory_item_id NUMBER)
315
316 RETURN NUMBER IS
317 l_return_status VARCHAR2(1);
318 l_msg_count NUMBER := 0;
319 l_msg_data VARCHAR2(200) := '';
320 l_api_version_number CONSTANT NUMBER := 1.00;
321 l_is_revision_control BOOLEAN := false;
322 l_is_lot_control BOOLEAN := false;
323 l_is_serial_control BOOLEAN := false;
324 l_serial_control_code NUMBER := 0;
325 l_revision_control_code NUMBER := 0;
326 l_lot_control_code NUMBER := 0;
327 l_serial_status NUMBER := 0;
328 l_qoh NUMBER := 0;
329 l_rqoh NUMBER := 0;
330 l_qr NUMBER := 0;
331 l_qs NUMBER := 0;
332 l_att NUMBER := 0;
333 l_atr NUMBER := 0;
334 l_count_qty NUMBER := 0;
335 EXCP_NO_REQ_PARAMETERS EXCEPTION;
336 EXCP_INVALID_ITEMS EXCEPTION;
337 BEGIN
338 g_qoh := null;
339 g_atr := null;
340 IF p_organization_id IS NULL THEN
341 RAISE EXCP_NO_REQ_PARAMETERS;
342 END IF;
343 IF p_inventory_item_id IS NULL THEN
344 RAISE EXCP_NO_REQ_PARAMETERS;
345 ELSE -- verify whether the item is assigned to the given organization_id
346 select count(organization_id) into l_count_qty
347 from mtl_system_items_kfv
348 where organization_id = p_organization_id
349 and inventory_item_id = p_inventory_item_id;
350
351 IF l_count_qty = 0 THEN
352 RAISE EXCP_INVALID_ITEMS;
353 ELSE
354 l_count_qty := 0;
355 END IF;
356 END IF;
357
358 -- check whether the item is under serial control
359 select serial_number_control_code into l_serial_control_code
360 from mtl_system_items
361 where organization_id = p_organization_id
362 and inventory_item_id = p_inventory_item_id;
363
364 IF l_serial_control_code <> 1 THEN
365 l_is_serial_control := true;
366 END IF;
367
368 -- check whether the item is under revision control
369 select revision_qty_control_code into l_revision_control_code
370 from mtl_system_items
371 where organization_id = p_organization_id
372 and inventory_item_id = p_inventory_item_id;
373
374 IF l_revision_control_code <> 1 THEN
375 l_is_revision_control := true;
376 END IF;
377
378 -- check whether the item is under lot control
379 select lot_control_code into l_lot_control_code
380 from mtl_system_items
381 where organization_id = p_organization_id
382 and inventory_item_id = p_inventory_item_id;
383
384 IF l_lot_control_code <> 1 THEN
385 l_is_lot_control := true;
386 END IF;
387
388 inv_quantity_tree_pub.clear_quantity_cache;
389
390 inv_quantity_tree_pub.query_quantities(
391 p_api_version_number => l_api_version_number
392 , p_init_msg_lst => fnd_api.g_false
393 , x_return_status => l_return_status
394 , x_msg_count => l_msg_count
395 , x_msg_data => l_msg_data
396 , p_organization_id => p_organization_id
397 , p_inventory_item_id => p_inventory_item_id
398 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
399 , p_is_revision_control => l_is_revision_control
400 , p_is_lot_control => l_is_lot_control
401 , p_is_serial_control => l_is_serial_control
402 , p_demand_source_type_id => NULL
406 , p_lot_expiration_date => NULL
403 , p_demand_source_header_id => NULL
404 , p_demand_source_line_id => NULL
405 , p_demand_source_name => NULL
407 , p_revision => NULL
408 , p_lot_number => NULL
409 , p_subinventory_code => p_subinventory_code
410 , p_locator_id => p_locator_id
411 , x_qoh => l_qoh
412 , x_rqoh => l_rqoh
413 , x_qr => l_qr
414 , x_qs => l_qs
415 , x_att => l_att
416 , x_atr => l_atr);
417
418 g_qoh := l_qoh;
419 g_atr := l_atr;
420
421 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424
425 return l_att;
426
427 EXCEPTION
428 WHEN EXCP_NO_REQ_PARAMETERS THEN
429 return NULL;
430
431 WHEN FND_API.G_EXC_ERROR THEN
432 return NULL;
433
434 WHEN OTHERS THEN
435 return NULL;
436
437 END GET_AVAIL_QTY;
438
439 FUNCTION Get_Avail_Qty (
440 /* Name: get_avail_qty
441 Purpose: Get the available quantity of an item at organization level, subinventory level or locator level.
442 For Spares Management, avail_qty = unreserved_qty = total_onhand_qty - reserved_qty.
443 This function returns NULL when there is an error.
444 MODIFICATION HISTORY
445 -- Person Date Comments
446 -- --------- ------ ------------------------------------------
447 -- hhaugeru 22-Jun-01 Created
448
449 -- End of comments
450 Author: Hans Haugerud
451 Date: 22-Jun-01
452 */
453 p_organization_id NUMBER,
454 p_subinventory_code VARCHAR2,
455 p_locator_id NUMBER,
456 p_inventory_item_id NUMBER,
457 p_revision VARCHAR2)
458
459 RETURN NUMBER IS
460 l_return_status VARCHAR2(1);
461 l_msg_count NUMBER := 0;
462 l_msg_data VARCHAR2(200) := '';
463 l_api_version_number CONSTANT NUMBER := 1.00;
464 l_is_revision_control BOOLEAN := false;
465 l_is_lot_control BOOLEAN := false;
466 l_is_serial_control BOOLEAN := false;
467 l_serial_control_code NUMBER := 0;
468 l_revision_control_code NUMBER := 0;
469 l_lot_control_code NUMBER := 0;
470 l_serial_status NUMBER := 0;
471 l_qoh NUMBER := 0;
472 l_rqoh NUMBER := 0;
473 l_qr NUMBER := 0;
474 l_qs NUMBER := 0;
475 l_att NUMBER := 0;
476 l_atr NUMBER := 0;
477 l_count_qty NUMBER := 0;
478 EXCP_NO_REQ_PARAMETERS EXCEPTION;
479 EXCP_INVALID_ITEMS EXCEPTION;
480 BEGIN
481
482 IF p_organization_id IS NULL THEN
483 RAISE EXCP_NO_REQ_PARAMETERS;
484 END IF;
485 IF p_inventory_item_id IS NULL THEN
486 RAISE EXCP_NO_REQ_PARAMETERS;
487 END IF;
488
489 -- check whether the item is under serial, lot and revision control
490 select serial_number_control_code,
491 lot_control_code,
492 revision_qty_control_code
493 into l_serial_control_code,
494 l_lot_control_code,
495 l_revision_control_code
496 from mtl_system_items
497 where organization_id = p_organization_id
498 and inventory_item_id = p_inventory_item_id;
499
500 IF l_serial_control_code <> 1 THEN
501 l_is_serial_control := true;
502 END IF;
503
504 IF l_revision_control_code <> 1 THEN
505 l_is_revision_control := true;
506 END IF;
507
508 -- bug # 7171956
509 -- if we want to total available qty for an item then don't say it is lot_controlled and this API will
510 -- return toal of all the lot_numbers otherwise we have to pass lot_number as well
511 /*
512 IF l_lot_control_code <> 1 THEN
513 l_is_lot_control := true;
514 END IF;
515 */
516
517 inv_quantity_tree_pub.clear_quantity_cache;
518
519 inv_quantity_tree_pub.query_quantities(
520 p_api_version_number => l_api_version_number
521 , p_init_msg_lst => fnd_api.g_false
522 , x_return_status => l_return_status
523 , x_msg_count => l_msg_count
524 , x_msg_data => l_msg_data
525 , p_organization_id => p_organization_id
526 , p_inventory_item_id => p_inventory_item_id
527 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
528 , p_is_revision_control => l_is_revision_control
529 , p_is_lot_control => l_is_lot_control
530 , p_is_serial_control => l_is_serial_control
531 , P_ONHAND_SOURCE => inv_quantity_tree_pvt.g_all_subs
532 , p_demand_source_type_id => NULL
533 , p_demand_source_header_id => NULL
534 , p_demand_source_line_id => NULL
535 , p_demand_source_name => NULL
536 , p_lot_expiration_date => NULL
537 , p_revision => p_revision
538 , p_lot_number => NULL
539 , p_subinventory_code => p_subinventory_code
540 , p_locator_id => p_locator_id
541 , x_qoh => l_qoh
542 , x_rqoh => l_rqoh
543 , x_qr => l_qr
544 , x_qs => l_qs
545 , x_att => l_att
546 , x_atr => l_atr);
547
548 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
549 RAISE FND_API.G_EXC_ERROR;
550 END IF;
551
552 return l_att;
553
554 EXCEPTION
555 WHEN EXCP_NO_REQ_PARAMETERS THEN
556 return NULL;
557
558 WHEN FND_API.G_EXC_ERROR THEN
559 return NULL;
560
561 WHEN OTHERS THEN
562 return NULL;
563
564 END GET_AVAIL_QTY;
565
566
567 -- bug # 7171956
568 -- new function to calculate available qty for lot controlled item
569
570 FUNCTION Get_Avail_Qty (
571 /* Name: get_avail_qty
572 Purpose: Get the available quantity of an item at organization level, subinventory level or locator level.
573 For Spares Management, avail_qty = unreserved_qty = total_onhand_qty - reserved_qty.
574 This function returns NULL when there is an error.
575 */
576 p_organization_id NUMBER,
577 p_subinventory_code VARCHAR2,
578 p_locator_id NUMBER,
579 p_inventory_item_id NUMBER,
580 p_revision VARCHAR2,
581 p_lot_num VARCHAR2)
582
583 RETURN NUMBER IS
584 l_return_status VARCHAR2(1);
585 l_msg_count NUMBER := 0;
586 l_msg_data VARCHAR2(200) := '';
587 l_api_version_number CONSTANT NUMBER := 1.00;
588 l_is_revision_control BOOLEAN := false;
589 l_is_lot_control BOOLEAN := false;
590 l_is_serial_control BOOLEAN := false;
591 l_serial_control_code NUMBER := 0;
592 l_revision_control_code NUMBER := 0;
593 l_lot_control_code NUMBER := 0;
594 l_serial_status NUMBER := 0;
595 l_qoh NUMBER := 0;
596 l_rqoh NUMBER := 0;
597 l_qr NUMBER := 0;
598 l_qs NUMBER := 0;
599 l_att NUMBER := 0;
600 l_atr NUMBER := 0;
601 l_count_qty NUMBER := 0;
602 EXCP_NO_REQ_PARAMETERS EXCEPTION;
603 EXCP_INVALID_ITEMS EXCEPTION;
604 BEGIN
605
606 IF p_organization_id IS NULL THEN
607 RAISE EXCP_NO_REQ_PARAMETERS;
608 END IF;
609 IF p_inventory_item_id IS NULL THEN
610 RAISE EXCP_NO_REQ_PARAMETERS;
611 END IF;
612
613 -- check whether the item is under serial, lot and revision control
614 select serial_number_control_code,
615 lot_control_code,
616 revision_qty_control_code
617 into l_serial_control_code,
618 l_lot_control_code,
619 l_revision_control_code
620 from mtl_system_items
621 where organization_id = p_organization_id
622 and inventory_item_id = p_inventory_item_id;
623
624 IF l_serial_control_code <> 1 THEN
625 l_is_serial_control := true;
626 END IF;
627
628 IF l_revision_control_code <> 1 THEN
629 l_is_revision_control := true;
630 END IF;
631
632 if l_is_revision_control and p_revision is NULL then
633 l_is_revision_control := false;
634 end if;
635
636 IF l_lot_control_code <> 1 THEN
637 l_is_lot_control := true;
638 END IF;
639
640 inv_quantity_tree_pub.clear_quantity_cache;
641
642 inv_quantity_tree_pub.query_quantities(
643 p_api_version_number => l_api_version_number
644 , p_init_msg_lst => fnd_api.g_false
645 , x_return_status => l_return_status
646 , x_msg_count => l_msg_count
647 , x_msg_data => l_msg_data
648 , p_organization_id => p_organization_id
649 , p_inventory_item_id => p_inventory_item_id
650 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
651 , p_is_revision_control => l_is_revision_control
652 , p_is_lot_control => l_is_lot_control
653 , p_is_serial_control => l_is_serial_control
654 , p_demand_source_type_id => NULL
655 , p_demand_source_header_id => NULL
656 , p_demand_source_line_id => NULL
657 , p_demand_source_name => NULL
658 , p_lot_expiration_date => NULL
659 , p_revision => p_revision
660 , p_lot_number => p_lot_num
661 , P_ONHAND_SOURCE => inv_quantity_tree_pvt.g_all_subs
662 , p_subinventory_code => p_subinventory_code
663 , p_locator_id => p_locator_id
664 , x_qoh => l_qoh
665 , x_rqoh => l_rqoh
666 , x_qr => l_qr
667 , x_qs => l_qs
668 , x_att => l_att
669 , x_atr => l_atr);
670
671 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
672 RAISE FND_API.G_EXC_ERROR;
673 END IF;
674
675 return l_att;
676
677 EXCEPTION
678 WHEN EXCP_NO_REQ_PARAMETERS THEN
679 return NULL;
680
681 WHEN FND_API.G_EXC_ERROR THEN
682 return NULL;
683
684 WHEN OTHERS THEN
685 return NULL;
686
687 END GET_AVAIL_QTY;
688 -- enf of bug # 7171956
689
690 END CSP_VALIDATE_PUB;