[Home] [Help]
PACKAGE BODY: APPS.INV_RESERVATION_AVAIL_PVT
Source
1 PACKAGE BODY inv_reservation_avail_pvt AS
2 /* $Header: INVVRVAB.pls 120.12.12010000.2 2008/10/14 23:19:29 mchemban ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RESERVATION_AVAIL_PVT';
5 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: INVVRVAB.pls 120.12.12010000.2 2008/10/14 23:19:29 mchemban ship $';
6 g_debug NUMBER;
7
8 -- procedure to print inventory debug message
9 PROCEDURE debug_print(p_message IN VARCHAR2, p_level IN NUMBER := 9) IS
10 BEGIN
11 inv_log_util.TRACE(p_message, 'INV_RESERVATION_AVAIL_PVT', p_level);
12 END debug_print;
13
14 PROCEDURE available_supply_to_reserve
15 (
16 p_api_version_number IN NUMBER DEFAULT 1.0
17 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
18 , x_return_status OUT NOCOPY VARCHAR2
19 , x_msg_count OUT NOCOPY NUMBER
20 , x_msg_data OUT NOCOPY VARCHAR2
21 , p_organization_id IN NUMBER DEFAULT NULL
22 , p_item_id IN NUMBER DEFAULT NULL
23 , p_revision IN VARCHAR2 DEFAULT NULL
24 , p_lot_number IN VARCHAR2 DEFAULT NULL
25 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
26 , p_locator_id IN NUMBER DEFAULT NULL
27 , p_lpn_id IN NUMBER DEFAULT fnd_api.g_miss_num
28 , p_fm_supply_source_type_id IN NUMBER DEFAULT 0
29 , p_supply_source_type_id IN NUMBER
30 , p_supply_source_header_id IN NUMBER
31 , p_supply_source_line_id IN NUMBER
32 , p_supply_source_line_detail IN NUMBER DEFAULT fnd_api.g_miss_num
33 , p_project_id IN NUMBER DEFAULT NULL
34 , p_task_id IN NUMBER DEFAULT NULL
35 , x_qty_available_to_reserve OUT NOCOPY NUMBER
36 , x_qty_available OUT NOCOPY NUMBER
37 ) IS
38 l_api_version_number CONSTANT NUMBER := 1.0;
39 l_api_name CONSTANT VARCHAR2(30) := 'avilable_supply_to_reserve';
40 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
41 l_msg_count NUMBER;
42 l_msg_data VARCHAR2(2000);
43 l_debug NUMBER;
44 l_wip_entity_type NUMBER;
45 l_wip_job_type VARCHAR2(15);
46 l_available_quantity NUMBER;
47 l_source_uom_code VARCHAR2(3);
48 l_source_primary_uom_code VARCHAR2(3);
49 l_primary_reserved_quantity NUMBER;
50 l_qty_available_to_reserve NUMBER;
51 l_primary_available_qty NUMBER;
52 l_return_txn NUMBER := 0;
53
54 BEGIN
55 IF (g_debug IS NULL) THEN
56 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
57 END IF;
58
59 l_debug := g_debug;
60
61 IF (l_debug = 1) THEN
62 debug_print('In available_supply_to_reserve');
63 debug_print('organization id = ' || p_organization_id);
64 debug_print('inventory item id = ' || p_item_id);
65 debug_print('revision = ' || p_revision);
66 debug_print('lot number = ' || p_lot_number);
67 debug_print('subinventory = ' || p_subinventory_code);
68 debug_print('locator id = ' || p_locator_id);
69 debug_print('supply source type id = ' || p_supply_source_type_id);
70 debug_print('supply source header id = ' || p_supply_source_header_id);
71 debug_print('supply source line id = ' || p_supply_source_line_id);
72 debug_print('supply source line detail = ' || p_supply_source_line_detail);
73 debug_print('project id = ' || p_project_id);
74 debug_print('task id = ' || p_task_id);
75 END IF;
76
77 -- error out if supply source type id is null
78 IF (p_supply_source_type_id is null) THEN
79 fnd_message.set_name('INV', 'INV_NO_SUPPLY_TYPE');
80 fnd_msg_pub.add;
81 RAISE fnd_api.g_exc_error;
82 END IF;
83
84 -- for WIP supply source
85 IF (p_supply_source_type_id = inv_reservation_global.g_source_type_wip) THEN
86
87 -- error out if supply source header id is null
88 IF (p_supply_source_header_id is null) THEN
89 fnd_message.set_name('INV','INV_NO_SUPPLY_INFO');
90 fnd_msg_pub.add;
91 RAISE fnd_api.g_exc_error;
92 END IF;
93
94 -- get wip entity type from wip_record_cache
95 inv_reservation_util_pvt.get_wip_cache
96 (
97 x_return_status => l_return_status
98 , p_wip_entity_id => p_supply_source_header_id
99 );
100
101 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
102 RAISE fnd_api.g_exc_error;
103 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
104 RAISE fnd_api.g_exc_unexpected_error;
105 ELSE
106 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_type;
107 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_job;
108 END IF;
109
110 IF (l_debug = 1) THEN
111 debug_print('wip entity type = ' || l_wip_entity_type);
112 END IF;
113
114 -- call availability API for the WIP entity type to get the quantity
115 -- available on the document. This quantity is the quantity ordered
116 -- minus the quantity already delivered on that document. It is the
117 -- expected supply still remainin to be satisfied against the document
118 -- line.
119 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete) THEN
120 -- remove comment later
121 IF (l_debug = 1) THEN
122 debug_print('calling WIP discrete get_available_supply_demand');
123 END IF;
124
125 IF (p_fm_supply_source_type_id =
126 inv_reservation_global.g_source_type_inv) THEN
127 IF (l_debug = 1) THEN
128 debug_print('It is a return transaction.');
129 END IF;
130 l_return_txn := 1;
131 -- 1 means it is a return txn. we are transferring from inv to
132 -- wip and
133 -- 0 means not a return transaction.
134 END IF;
135
136 WIP_RESERVATIONS_GRP.get_available_supply_demand
137 (
138 x_return_status => l_return_status
139 , x_msg_count => l_msg_count
140 , x_msg_data => l_msg_data
141 , x_available_quantity => l_available_quantity
142 , x_source_uom_code => l_source_uom_code
143 , x_source_primary_uom_code => l_source_primary_uom_code
144 , p_organization_id => p_organization_id
145 , p_item_id => p_item_id
146 , p_revision => p_revision
147 , p_lot_number => p_lot_number
148 , p_subinventory_code => p_subinventory_code
149 , p_locator_id => p_locator_id
150 , p_supply_demand_code => 1
151 , p_supply_demand_type_id => p_supply_source_type_id
152 , p_supply_demand_header_id => p_supply_source_header_id
153 , p_supply_demand_line_id => p_supply_source_line_id
154 , p_supply_demand_line_detail => p_supply_source_line_detail
155 , p_lpn_id => p_lpn_id
156 , p_project_id => null -- p_project_id
157 , p_task_id => null -- p_task_id
158 , p_api_version_number => 1.0
159 , p_init_msg_lst => fnd_api.g_false
160 , p_return_txn => l_return_txn
161 );
162
163 IF (l_debug = 1) THEN
164 debug_print('return status from get_available_supply_demand = ' || l_return_status);
165 debug_print('available quantity = ' || l_available_quantity);
166 debug_print('source uom code = ' || l_source_uom_code);
167 debug_print('source primary uom code = ' || l_source_primary_uom_code);
168 END IF;
169
170 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
171 raise fnd_api.g_exc_error;
172 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
173 raise fnd_api.g_exc_unexpected_error;
174 END IF;
175 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_osfm) THEN
176 -- remove comment later
177 IF (l_debug = 1) THEN
178 debug_print('calling osfm get_available_supply_demand');
179 END IF;
180
181 WSM_RESERVATIONS_GRP.get_available_supply_demand
182 (
183 x_return_status => l_return_status
184 , x_msg_count => l_msg_count
185 , x_msg_data => l_msg_data
186 , x_available_quantity => l_available_quantity
187 , x_source_uom_code => l_source_uom_code
188 , x_source_primary_uom_code => l_source_primary_uom_code
189 , p_organization_id => p_organization_id
190 , p_item_id => p_item_id
191 , p_revision => p_revision
192 , p_lot_number => p_lot_number
193 , p_subinventory_code => p_subinventory_code
194 , p_locator_id => p_locator_id
195 , p_supply_demand_code => 1
196 , p_supply_demand_type_id => p_supply_source_type_id
197 , p_supply_demand_header_id => p_supply_source_header_id
198 , p_supply_demand_line_id => p_supply_source_line_id
199 , p_supply_demand_line_detail => p_supply_source_line_detail
200 , p_lpn_id => p_lpn_id
201 , p_project_id => null -- p_project_id
202 , p_task_id => null -- p_task_id
203 , p_api_version_number => 1.0
204 , p_init_msg_lst => fnd_api.g_false
205 );
206
207 IF (l_debug = 1) THEN
208 debug_print('return status from get_available_supply_demand = ' || l_return_status);
209 debug_print('available quantity = ' || l_available_quantity);
210 debug_print('source uom code = ' || l_source_uom_code);
211 debug_print('source primary uom code = ' || l_source_primary_uom_code);
212 END IF;
213
214 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
215 raise fnd_api.g_exc_error;
216 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
217 raise fnd_api.g_exc_unexpected_error;
218 END IF;
219 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo OR
220 l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
221 -- remove comment later
222 IF (l_debug = 1) THEN
223 debug_print('calling fpo get_available_supply_demand');
224 END IF;
225 GME_API_GRP.get_available_supply_demand
226 (
227 x_return_status => l_return_status
228 , x_msg_count => l_msg_count
229 , x_msg_data => l_msg_data
230 , x_available_quantity => l_available_quantity
231 , x_source_uom_code => l_source_uom_code
232 , x_source_primary_uom_code => l_source_primary_uom_code
233 , p_organization_id => p_organization_id
234 , p_item_id => p_item_id
235 , p_revision => p_revision
236 , p_lot_number => p_lot_number
237 , p_subinventory_code => p_subinventory_code
238 , p_locator_id => p_locator_id
239 , p_supply_demand_code => 1
240 , p_supply_demand_type_id => p_supply_source_type_id
241 , p_supply_demand_header_id => p_supply_source_header_id
242 , p_supply_demand_line_id => p_supply_source_line_id
243 , p_supply_demand_line_detail => p_supply_source_line_detail
244 , p_lpn_id => p_lpn_id
245 , p_project_id => null -- p_project_id
246 , p_task_id => null -- p_task_id
247 , p_api_version_number => 1.0
248 , p_init_msg_lst => fnd_api.g_false
249 );
250 END IF;
251
252 IF (l_debug = 1) THEN
253 debug_print('return status from get_available_supply_demand = ' || l_return_status);
254 debug_print('available quantity = ' || l_available_quantity);
255 debug_print('source uom code = ' || l_source_uom_code);
256 debug_print('source primary uom code = ' || l_source_primary_uom_code);
257 END IF;
258
259 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
260 raise fnd_api.g_exc_error;
261 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
262 raise fnd_api.g_exc_unexpected_error;
263 END IF;
264
265 -- need uom conversion if source uom is different from primary uom
266 IF (l_available_quantity > 0 AND l_source_uom_code is not NULL AND l_source_uom_code <> l_source_primary_uom_code) THEN
267 IF (l_debug = 1) THEN
268 debug_print('calling inv_convert.inv_um_convert');
269 debug_print('item_id = ' || p_item_id);
270 debug_print('org_id = ' || p_organization_id);
271 debug_print('lot_number = ' || p_lot_number);
272 debug_print('l_available_quantity = ' || l_available_quantity);
273 debug_print('l_source_uom_code = ' || l_source_uom_code);
274 debug_print('l_source_primary_uom_code = ' || l_source_primary_uom_code);
275 END IF;
276
277 l_primary_available_qty := inv_convert.inv_um_convert
278 (
279 item_id => p_item_id
280 , lot_number => p_lot_number
281 , organization_id => p_organization_id
282 , precision => null
283 , from_quantity => l_available_quantity
284 , from_unit => l_source_uom_code
285 , to_unit => l_source_primary_uom_code
286 , from_name => null
287 , to_name => null
288 );
289 ELSE
290 l_primary_available_qty := l_available_quantity;
291 END IF;
292
293
294 -- get the sum of quantity that is already reserved on the document.
295 BEGIN
296 -- BUG 5052424 BEGIN
297 -- For OPM assess exisiting reservations at line level
298 -- Otherwise assess at header level
299 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo OR
300 l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
301 SELECT nvl(sum(primary_reservation_quantity), 0)
302 INTO l_primary_reserved_quantity
303 FROM mtl_reservations
304 WHERE supply_source_type_id = p_supply_source_type_id
305 AND supply_source_header_id = p_supply_source_header_id
306 AND supply_source_line_id = p_supply_source_line_id;
307 ELSE
308 SELECT nvl(sum(primary_reservation_quantity), 0)
309 INTO l_primary_reserved_quantity
310 FROM mtl_reservations
311 WHERE supply_source_type_id = p_supply_source_type_id
312 AND supply_source_header_id = p_supply_source_header_id;
313 END IF;
314 -- BUG 5052424 END
315 EXCEPTION
316 WHEN no_data_found THEN
317 IF (l_debug = 1) THEN
318 debug_print('No reservation found');
319 END IF;
320
321 l_primary_reserved_quantity := 0;
322 END;
323
324 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_po OR
325 p_supply_source_type_id = inv_reservation_global.g_source_type_asn OR
326 p_supply_source_type_id = inv_reservation_global.g_source_type_intransit OR
327 p_supply_source_type_id = inv_reservation_global.g_source_type_internal_req) THEN
328
329 -- error out if supply source header or line id is null
330 IF (p_supply_source_header_id is null or p_supply_source_line_id is null) THEN
331 fnd_message.set_name('INV','INV_NO_SUPPLY_INFO');
332 fnd_msg_pub.add;
333 RAISE fnd_api.g_exc_error;
334 END IF;
335
336 -- for ASN supply, error if if supply source line detail is null
337 IF (p_supply_source_type_id = inv_reservation_global.g_source_type_asn
338 and p_supply_source_line_detail is null) THEN
339 fnd_message.set_name('INV','INV_NO_SUPPLY_INFO');
340 fnd_msg_pub.add;
341 RAISE fnd_api.g_exc_error;
342 END IF;
343
344 -- call availability API for PO, ASN, Intransit shipment or Internal Req
345 -- For PO, the available quantity is the quantity ordered
346 -- minus the quantity already delivered on that document minu quantity received
347 -- minus quantity transferred to the ASN (for WMS orgs). It is the
348 -- expected supply still remaining to be satisfied against the document
349 -- line.
350 -- For ASN, the availability is the total quantity on the ASN - quantity
351 -- received on the ASN.
352 -- For Intransit shipment, the availability is the total quantity
353 -- on the intransit shipment - quantity received against the intransit shipment
354 -- line.
355 -- For internal Req, the availability is the total quantity on the internal
356 -- requisition document - quantity received against this document.
357 IF (l_debug = 1) THEN
358 debug_print('calling RCV get_available_supply_demand');
359 END IF;
360
361 RCV_availability.get_available_supply_demand
362 (
363 x_return_status => l_return_status
364 , x_msg_count => l_msg_count
365 , x_msg_data => l_msg_data
366 , x_available_quantity => l_available_quantity
367 , x_source_uom_code => l_source_uom_code
368 , x_source_primary_uom_code => l_source_primary_uom_code
369 , p_organization_id => p_organization_id
370 , p_item_id => p_item_id
371 , p_revision => p_revision
372 , p_lot_number => p_lot_number
373 , p_subinventory_code => p_subinventory_code
374 , p_locator_id => p_locator_id
375 , p_supply_demand_code => 1
376 , p_supply_demand_type_id => p_supply_source_type_id
377 , p_supply_demand_header_id => p_supply_source_header_id
378 , p_supply_demand_line_id => p_supply_source_line_id
379 , p_supply_demand_line_detail => p_supply_source_line_detail
380 , p_lpn_id => p_lpn_id
381 , p_project_id => p_project_id
382 , p_task_id => p_task_id
383 , p_api_version_number => 1.0
384 , p_init_msg_lst => fnd_api.g_false
385 );
386
387 IF (l_debug = 1) THEN
388 debug_print('return status from RCV_availability.get_available_supply_demand = ' || l_return_status);
389 debug_print('available quantity = ' || l_available_quantity);
390 debug_print('source uom code = ' || l_source_uom_code);
391 debug_print('source primary uom code = ' || l_source_primary_uom_code);
392 END IF;
393
394 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
395 raise fnd_api.g_exc_error;
396 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
397 raise fnd_api.g_exc_unexpected_error;
398 END IF;
399
400 -- need uom conversion if source uom is different from primary uom
401 IF (l_available_quantity > 0 AND l_source_uom_code is not NULL AND l_source_uom_code <> l_source_primary_uom_code) THEN
402 IF (l_debug = 1) THEN
403 debug_print('calling inv_convert.inv_um_convert');
404 debug_print('item_id = ' || p_item_id);
405 debug_print('org_id = ' || p_organization_id);
406 debug_print('lot_number = ' || p_lot_number);
407 debug_print('l_available_quantity = ' || l_available_quantity);
408 debug_print('l_source_uom_code = ' || l_source_uom_code);
409 debug_print('l_source_primary_uom_code = ' || l_source_primary_uom_code);
410 END IF;
411
412 l_primary_available_qty := inv_convert.inv_um_convert
413 (
414 item_id => p_item_id
415 , lot_number => p_lot_number
416 , organization_id => p_organization_id
417 , precision => null
418 , from_quantity => l_available_quantity
419 , from_unit => l_source_uom_code
420 , to_unit => l_source_primary_uom_code
421 , from_name => null
422 , to_name => null
423 );
424 ELSE
425 l_primary_available_qty := l_available_quantity;
426 END IF;
427
428
429 IF (p_supply_source_type_id = inv_reservation_global.g_source_type_po OR
430 p_supply_source_type_id = inv_reservation_global.g_source_type_intransit OR
431 p_supply_source_type_id = inv_reservation_global.g_source_type_internal_req) THEN
432
433 -- get the sum of quantity that is already reserved on the document.
434 BEGIN
435 SELECT nvl(sum(primary_reservation_quantity), 0)
436 INTO l_primary_reserved_quantity
437 FROM mtl_reservations
438 WHERE supply_source_type_id = p_supply_source_type_id
439 AND supply_source_header_id = p_supply_source_header_id
440 AND supply_source_line_id = p_supply_source_line_id
441 AND nvl(project_id, -99) = nvl(p_project_id, -99)
442 AND nvl(task_id, -99) = nvl(p_task_id, -99);
443 EXCEPTION
444 WHEN no_data_found THEN
445 IF (l_debug = 1) THEN
446 debug_print('No reservation found');
447 END IF;
448
449 l_primary_reserved_quantity := 0;
450 END;
451
452
453 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_asn) THEN
454
455 -- get the sum of quantity that is already reserved on the document.
456 BEGIN
457 SELECT nvl(sum(primary_reservation_quantity), 0)
458 INTO l_primary_reserved_quantity
459 FROM mtl_reservations
460 WHERE supply_source_type_id = p_supply_source_type_id
461 AND supply_source_header_id = p_supply_source_header_id
462 AND supply_source_line_id = p_supply_source_line_id
463 AND supply_source_line_detail = p_supply_source_line_detail
464 AND nvl(project_id, -99) = nvl(p_project_id, -99)
465 AND nvl(task_id, -99) = nvl(p_task_id, -99);
466 EXCEPTION
467 WHEN no_data_found THEN
468 IF (l_debug = 1) THEN
469 debug_print('No reservation found');
470 END IF;
471
472 l_primary_reserved_quantity := 0;
473 END;
474
475
476 END IF;
477
478 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_rcv) THEN
479
480 -- error out if organization_id or item id is null
481 IF (p_organization_id is null or p_item_id is null) THEN
482 fnd_message.set_name('INV', 'INV_NO_ORG_ITEM');
483 fnd_msg_pub.add;
484 RAISE fnd_api.g_exc_error;
485 END IF;
486
487 -- call availability API for available quantity in receiving
488 IF (l_debug = 1) THEN
489 debug_print('Receiving supply, before calling INV_RCV_availability.get_available_supply_demand');
490 END IF;
491
492 INV_RCV_availability.get_available_supply_demand
493 (
494 x_return_status => l_return_status
495 , x_msg_count => l_msg_count
496 , x_msg_data => l_msg_data
497 , x_available_quantity => l_available_quantity
498 , x_source_uom_code => l_source_uom_code
499 , x_source_primary_uom_code => l_source_primary_uom_code
500 , p_organization_id => p_organization_id
501 , p_item_id => p_item_id
502 , p_revision => p_revision
503 , p_lot_number => p_lot_number
504 , p_subinventory_code => p_subinventory_code
505 , p_locator_id => p_locator_id
506 , p_supply_demand_code => 1
507 , p_supply_demand_type_id => p_supply_source_type_id
508 , p_supply_demand_header_id => p_supply_source_header_id
509 , p_supply_demand_line_id => p_supply_source_line_id
510 , p_supply_demand_line_detail => p_supply_source_line_detail
511 , p_lpn_id => p_lpn_id
512 , p_project_id => null -- p_project_id
513 , p_task_id => null -- p_task_id
514 , p_api_version_number => 1.0
515 , p_init_msg_lst => fnd_api.g_false
516 );
517
518 IF (l_debug = 1) THEN
519 debug_print('return status from get_available_supply_demand = ' || l_return_status);
520 debug_print('available quantity = ' || l_available_quantity);
521 debug_print('source uom code = ' || l_source_uom_code);
522 debug_print('source primary uom code = ' || l_source_primary_uom_code);
523 END IF;
524
525 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
526 raise fnd_api.g_exc_error;
527 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
528 raise fnd_api.g_exc_unexpected_error;
529 END IF;
530
531 -- need uom conversion if source uom is different from primary uom
532 IF (l_available_quantity > 0 AND l_source_uom_code is not NULL AND l_source_uom_code <> l_source_primary_uom_code) THEN
533 IF (l_debug = 1) THEN
534 debug_print('calling inv_convert.inv_um_convert');
535 debug_print('item_id = ' || p_item_id);
536 debug_print('org_id = ' || p_organization_id);
537 debug_print('lot_number = ' || p_lot_number);
538 debug_print('l_available_quantity = ' || l_available_quantity);
539 debug_print('l_source_uom_code = ' || l_source_uom_code);
540 debug_print('l_source_primary_uom_code = ' || l_source_primary_uom_code);
541 END IF;
542
543 l_primary_available_qty := inv_convert.inv_um_convert
544 (
545 item_id => p_item_id
546 , lot_number => p_lot_number
547 , organization_id => p_organization_id
548 , precision => null
549 , from_quantity => l_available_quantity
550 , from_unit => l_source_uom_code
551 , to_unit => l_source_primary_uom_code
552 , from_name => null
553 , to_name => null
554 );
555 ELSE
556 l_primary_available_qty := l_available_quantity;
557 END IF;
558
559
560 -- get the sum of quantity that is already reserved on the document.
561 BEGIN
562 SELECT nvl(sum(primary_reservation_quantity), 0)
563 INTO l_primary_reserved_quantity
564 FROM mtl_reservations
565 WHERE supply_source_type_id = p_supply_source_type_id
566 AND organization_id = p_organization_id
567 AND inventory_item_id = p_item_id;
568 EXCEPTION
569 WHEN no_data_found THEN
570 IF (l_debug = 1) THEN
571 debug_print('No reservation found');
572 END IF;
573
574 l_primary_reserved_quantity := 0;
575 END;
576
577 END IF; -- end of WIP supply
578
579 -- calculate the final available to reserve quantity from available quantity from document and
580 -- reserved quantity of the document in primary uom
581 IF (l_debug = 1) THEN
582 debug_print('primary available qty = ' || l_primary_available_qty);
583 debug_print('primary reserved qty = ' || l_primary_reserved_quantity);
584 END IF;
585
586 l_qty_available_to_reserve := nvl(l_primary_available_qty, 0) - nvl(l_primary_reserved_quantity, 0);
587
588 x_qty_available_to_reserve := l_qty_available_to_reserve;
589 x_qty_available := nvl(l_primary_available_qty, 0);
590
591 x_return_status := l_return_status;
592 EXCEPTION
593 WHEN fnd_api.g_exc_error THEN
594 x_return_status := fnd_api.g_ret_sts_error;
595 IF (l_debug = 1) THEN
596 debug_print('expected error in available_supply_to_reserve');
597 END IF;
598 --
599 WHEN fnd_api.g_exc_unexpected_error THEN
600 x_return_status := fnd_api.g_ret_sts_unexp_error ;
601 IF (l_debug = 1) THEN
602 debug_print('unexpected error in available_supply_to_reserve');
603 debug_print('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM);
604 END IF;
605 --
606 WHEN OTHERS THEN
607 x_return_status := fnd_api.g_ret_sts_unexp_error ;
608 IF (l_debug = 1) THEN
609 debug_print('others error in available_supply_to_reserve');
610 debug_print('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM);
611 END IF;
612 --
613 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
614 THEN
615 fnd_msg_pub.add_exc_msg
616 ( g_pkg_name
617 , 'available_supply_to_reserve'
618 );
619 END IF;
620 --
621 END available_supply_to_reserve;
622
623 PROCEDURE available_demand_to_reserve
624 (
625 p_api_version_number IN NUMBER DEFAULT 1.0
626 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
627 , x_return_status OUT NOCOPY VARCHAR2
628 , x_msg_count OUT NOCOPY NUMBER
629 , x_msg_data OUT NOCOPY VARCHAR2
630 , p_organization_id IN NUMBER DEFAULT NULL
631 , p_item_id IN NUMBER DEFAULT NULL
632 , p_primary_uom_code IN VARCHAR2 DEFAULT NULL
633 , p_demand_source_type_id IN NUMBER
634 , p_demand_source_header_id IN NUMBER
635 , p_demand_source_line_id IN NUMBER
636 , p_demand_source_line_detail IN NUMBER DEFAULT fnd_api.g_miss_num
637 , p_project_id IN NUMBER DEFAULT NULL
638 , p_task_id IN NUMBER DEFAULT NULL
639 , x_qty_available_to_reserve OUT NOCOPY NUMBER
640 , x_qty_available OUT NOCOPY NUMBER
641 ) IS
642 l_api_version_number CONSTANT NUMBER := 1.0;
643 l_api_name CONSTANT VARCHAR2(30) := 'avilable_demand_to_reserve';
644 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
645 l_msg_count NUMBER;
646 l_msg_data VARCHAR2(2000);
647 l_debug NUMBER;
648 l_wip_entity_type NUMBER;
649 l_wip_job_type VARCHAR2(15);
650 l_available_quantity NUMBER;
651 l_source_uom_code VARCHAR2(3);
652 l_source_primary_uom_code VARCHAR2(3);
653 l_primary_reserved_quantity NUMBER;
654 l_qty_available_to_reserve NUMBER;
655 l_primary_available_qty NUMBER;
656 l_wdd_primary_quantity NUMBER;
657 l_wdd_primary_reserved_qty NUMBER;
658 l_wdd_available_qty NUMBER;
659 l_order_available_qty NUMBER;
660 l_rsv_primary_uom_code VARCHAR2(3);
661 l_order_quantity_uom_code VARCHAR2(3);
662 BEGIN
663 IF (g_debug IS NULL) THEN
664 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
665 END IF;
666
667 l_debug := g_debug;
668
669 IF (l_debug = 1) THEN
670 debug_print('In available_demand_to_reserve');
671 debug_print('demand source type id = ' || p_demand_source_type_id);
672 debug_print('demand source header id = ' || p_demand_source_header_id);
673 debug_print('demand source line id = ' || p_demand_source_line_id);
674 debug_print('demand source line detail = ' || p_demand_source_line_detail);
675 debug_print('project id = ' || p_project_id);
676 debug_print('task id = ' || p_task_id);
677 END IF;
678
679 -- error out if demand source type id is null
680 IF (p_demand_source_type_id is null) THEN
681 fnd_message.set_name('INV', 'INV_NO_DEMAND_TYPE');
682 fnd_msg_pub.add;
683 RAISE fnd_api.g_exc_error;
684 END IF;
685
686 -- for WIP demand source
687 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip) THEN
688
689 -- error out if demand source header id is null
690 IF (p_demand_source_header_id is null) THEN
691 fnd_message.set_name('INV','INV_NO_DEMAND_INFO');
692 fnd_msg_pub.add;
693 RAISE fnd_api.g_exc_error;
694 END IF;
695
696 -- get wip entitty type from wip_record_cache
697 inv_reservation_util_pvt.get_wip_cache
698 (
699 x_return_status => l_return_status
700 , p_wip_entity_id => p_demand_source_header_id
701 );
702
703 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
704 RAISE fnd_api.g_exc_error;
705 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
706 RAISE fnd_api.g_exc_unexpected_error;
707 ELSE
708 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_demand_source_header_id).wip_entity_type;
709 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_demand_source_header_id).wip_entity_job;
710 END IF;
711
712 IF (l_debug = 1) THEN
713 debug_print('wip entity type = ' || l_wip_entity_type);
714 END IF;
715
716 -- call availability API for the WIP entity type to get the quantity
717 -- available on the document. This quantity is the quantity ordered
718 -- minus the quantity already delivered on that document. It is the
719 -- expected demand still remaining to be satisfied against the document
720 -- line.
721 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_cmro) THEN
722 IF (l_debug = 1) THEN
723 debug_print('calling WIP cmro get_available_supply_demand');
724 END IF;
725
726 AHL_INV_RESERVATIONS_GRP.get_available_supply_demand
727 (
728 x_return_status => l_return_status
729 , x_msg_count => l_msg_count
730 , x_msg_data => l_msg_data
731 , x_available_quantity => l_available_quantity
732 , x_source_uom_code => l_source_uom_code
733 , x_source_primary_uom_code => l_source_primary_uom_code
734 , p_organization_id => null
735 , p_item_id => null
736 , p_revision => null
737 , p_lot_number => null
738 , p_subinventory_code => null
739 , p_locator_id => null
740 , p_supply_demand_code => 2
741 , p_supply_demand_type_id => p_demand_source_type_id
742 , p_supply_demand_header_id => p_demand_source_header_id
743 , p_supply_demand_line_id => p_demand_source_line_id
744 , p_supply_demand_line_detail => p_demand_source_line_detail
745 , p_lpn_id => null
746 , p_project_id => null -- p_project_id
747 , p_task_id => null -- p_task_id
748 , p_api_version_number => 1.0
749 , p_init_msg_lst => fnd_api.g_false
750 );
751
752 IF (l_debug = 1) THEN
753 debug_print('return status from cmro get_available_supply_demand = ' || l_return_status);
754 debug_print('available quantity = ' || l_available_quantity);
755 debug_print('source uom code = ' || l_source_uom_code);
756 debug_print('source primary uom code = ' || l_source_primary_uom_code);
757 END IF;
758
759 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
760 raise fnd_api.g_exc_error;
761 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
762 raise fnd_api.g_exc_unexpected_error;
763 END IF;
764
765 -- get the sum of quantity that is already reserved on the document.
766 -- bug #5458083 added demand_source_line_detail in the where clause
767 -- for cmro demand.
768 BEGIN
769 SELECT nvl(sum(primary_reservation_quantity), 0)
770 INTO l_primary_reserved_quantity
771 FROM mtl_reservations
772 WHERE demand_source_type_id = p_demand_source_type_id
773 AND demand_source_header_id = p_demand_source_header_id
774 AND demand_source_line_id = p_demand_source_line_id
775 AND demand_source_line_detail = p_demand_source_line_detail;
776 EXCEPTION
777 WHEN no_data_found THEN
778 IF (l_debug = 1) THEN
779 debug_print('No reservation found for cmro test number');
780 END IF;
781
782 l_primary_reserved_quantity := 0;
783 END;
784
785
786 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo OR
787 l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
788 IF (l_debug = 1) THEN
789 debug_print('calling opm get_available_supply_demand');
790 END IF;
791 GME_API_GRP.get_available_supply_demand
792 (
793 x_return_status => l_return_status
794 , x_msg_count => l_msg_count
795 , x_msg_data => l_msg_data
796 , x_available_quantity => l_available_quantity
797 , x_source_uom_code => l_source_uom_code
798 , x_source_primary_uom_code => l_source_primary_uom_code
799 , p_organization_id => null
800 , p_item_id => null
801 , p_revision => null
802 , p_lot_number => null
803 , p_subinventory_code => null
804 , p_locator_id => null
805 , p_supply_demand_code => 2
806 , p_supply_demand_type_id => p_demand_source_type_id
807 , p_supply_demand_header_id => p_demand_source_header_id
808 , p_supply_demand_line_id => p_demand_source_line_id
809 , p_supply_demand_line_detail => p_demand_source_line_detail
810 , p_lpn_id => null
811 , p_project_id => null -- p_project_id
812 , p_task_id => null -- p_task_id
813 , p_api_version_number => 1.0
814 , p_init_msg_lst => fnd_api.g_false
815 );
816
817 IF (l_debug = 1) THEN
818 debug_print('return status from batch/fpo get_available_supply_demand = ' || l_return_status);
819 debug_print('available quantity = ' || l_available_quantity);
820 debug_print('source uom code = ' || l_source_uom_code);
821 debug_print('source primary uom code = ' || l_source_primary_uom_code);
822 END IF;
823
824 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
825 raise fnd_api.g_exc_error;
826 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
827 raise fnd_api.g_exc_unexpected_error;
828 END IF;
829
830 -- get the sum of quantity that is already reserved on the document.
831 BEGIN
832 SELECT nvl(sum(primary_reservation_quantity), 0)
833 INTO l_primary_reserved_quantity
834 FROM mtl_reservations
835 WHERE demand_source_type_id = p_demand_source_type_id
836 AND demand_source_header_id = p_demand_source_header_id
837 AND demand_source_line_id = p_demand_source_line_id;
838 EXCEPTION
839 WHEN no_data_found THEN
840 IF (l_debug = 1) THEN
841 debug_print('No reservation found for batch/fpo');
842 END IF;
843
844 l_primary_reserved_quantity := 0;
845 END;
846
847 END IF;
848
849 -- need uom conversion if source uom is different from primary uom
850 IF (l_source_uom_code <> l_source_primary_uom_code) THEN
851 IF (l_debug = 1) THEN
852 debug_print('calling inv_convert.inv_um_convert');
853 debug_print('l_available_quantity = ' || l_available_quantity);
854 debug_print('l_source_uom_code = ' || l_source_uom_code);
855 debug_print('l_source_primary_uom_code = ' || l_source_primary_uom_code);
856 END IF;
857
858 l_primary_available_qty := inv_convert.inv_um_convert
859 (
860 item_id => p_item_id
861 , lot_number => null
862 , organization_id => p_organization_id
863 , precision => null
864 , from_quantity => l_available_quantity
865 , from_unit => l_source_uom_code
866 , to_unit => l_source_primary_uom_code
867 , from_name => null
868 , to_name => null
869 );
870 ELSE
871 l_primary_available_qty := l_available_quantity;
872 END IF;
873
874 l_qty_available_to_reserve := l_primary_available_qty - l_primary_reserved_quantity;
875
876 ELSIF (p_demand_source_type_id in
877 (inv_reservation_global.g_source_type_oe,
878 inv_reservation_global.g_source_type_internal_ord,
879 inv_reservation_global.g_source_type_rma)) THEN
880
881 IF (p_demand_source_line_detail is not NULL AND p_demand_source_line_detail <> fnd_api.g_miss_num) THEN
882
883 IF (l_debug = 1) THEN
884 debug_print('p_demand_source_line_detail is not NULL and p_demand_source_line_detail <> fnd_api.g_miss_num');
885 END IF;
886
887 -- get wdd requested quantity with line detail level
888 BEGIN
889 SELECT nvl(sum(requested_quantity), 0)
890 INTO l_wdd_primary_quantity
891 FROM wsh_delivery_details
892 WHERE source_line_id = p_demand_source_line_id
893 AND delivery_detail_id = p_demand_source_line_detail
894 AND nvl(project_id, -99) = nvl(p_project_id, -99)
895 AND nvl(task_id, -99) = nvl(p_task_id, -99);
896 EXCEPTION
897 WHEN no_data_found THEN
898 IF (l_debug = 1) THEN
899 debug_print('No wdd found for source_line_id: '|| p_demand_source_line_id);
900 debug_print('demand_source_line_detail: ' || p_demand_source_line_detail);
901 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
902 END IF;
903
904 FND_MESSAGE.SET_NAME('INV', 'INV_WDD_NOT_FOUND');
905 FND_MSG_PUB.ADD;
906 RAISE fnd_api.g_exc_error;
907 END;
908
909 IF (l_debug = 1) THEN
910 debug_print('l_wdd_primary_quantity = ' || l_wdd_primary_quantity);
911 END IF;
912
913 -- get reservation quantity against the wdd with line detail level
914 BEGIN
915 SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
916 INTO l_wdd_primary_reserved_qty, l_rsv_primary_uom_code
917 FROM mtl_reservations
918 WHERE demand_source_type_id = p_demand_source_type_id
919 AND demand_source_header_id = p_demand_source_header_id
920 AND demand_source_line_id = p_demand_source_line_id
921 AND demand_source_line_detail = p_demand_source_line_detail
922 GROUP BY primary_uom_code;
923 EXCEPTION
924 WHEN no_data_found THEN
925 IF (l_debug = 1) THEN
926 debug_print('No reservation is found for detail level, demand_source_type_id= ' || p_demand_source_type_id);
927 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
928 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
929 debug_print('demand_source_line_detail = ' || p_demand_source_line_detail);
930 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
931 END IF;
932
933 l_wdd_primary_reserved_qty := 0;
934 END;
935
936 IF (l_debug = 1) THEN
937 debug_print('l_wdd_primary_reserved_qty = ' || l_wdd_primary_reserved_qty);
938 END IF;
939
940 -- get all reservation quantity at the order line level
941 BEGIN
942 SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
943 INTO l_primary_reserved_quantity, l_rsv_primary_uom_code
944 FROM mtl_reservations
945 WHERE demand_source_type_id = p_demand_source_type_id
946 AND demand_source_header_id = p_demand_source_header_id
947 AND demand_source_line_id = p_demand_source_line_id
948 GROUP BY primary_uom_code;
949 EXCEPTION
950 WHEN no_data_found THEN
951 IF (l_debug = 1) THEN
952 debug_print('No reservation is found for line level, demand_source_type_id= ' || p_demand_source_type_id);
953 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
954 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
955 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
956 END IF;
957
958 l_primary_reserved_quantity := 0;
959 END;
960
961 IF (l_debug = 1) THEN
962 debug_print('l_primary_reserved_quantity = ' || l_primary_reserved_quantity);
963 END IF;
964
965 ELSIF (p_demand_source_line_detail = fnd_api.g_miss_num) THEN
966
967 IF (l_debug = 1) THEN
968 debug_print('p_demand_source_line_detail = fnd_api.g_miss_num');
969 END IF;
970
971 -- get all reservation quantity at the order line level
972 BEGIN
973 SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
974 INTO l_primary_reserved_quantity, l_rsv_primary_uom_code
975 FROM mtl_reservations
976 WHERE demand_source_type_id = p_demand_source_type_id
977 AND demand_source_header_id = p_demand_source_header_id
978 AND demand_source_line_id = p_demand_source_line_id
979 GROUP BY primary_uom_code;
980 EXCEPTION
981 WHEN no_data_found THEN
982 IF (l_debug = 1) THEN
983 debug_print('No reservation is found for line level, demand_source_type_id= ' || p_demand_source_type_id); debug_print('demand_source_header_id = ' || p_demand_source_header_id);
984 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
985 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
986 END IF;
987
988 l_primary_reserved_quantity := 0;
989 END;
990
991 IF (l_debug = 1) THEN
992 debug_print('l_primary_reserved_quantity = ' || l_primary_reserved_quantity);
993 END IF;
994
995 ELSIF (p_demand_source_line_detail is null) THEN
996
997 IF (l_debug = 1) THEN
998 debug_print('p_demand_source_line_detail is null');
999 END IF;
1000
1001 -- get all reservation quantity with the line detail = null
1002 BEGIN
1003 SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
1004 INTO l_primary_reserved_quantity, l_rsv_primary_uom_code
1005 FROM mtl_reservations
1006 WHERE demand_source_type_id = p_demand_source_type_id
1007 AND demand_source_header_id = p_demand_source_header_id
1008 AND demand_source_line_id = p_demand_source_line_id
1009 AND demand_source_line_detail is null
1010 GROUP BY primary_uom_code;
1011 EXCEPTION
1012 WHEN no_data_found THEN
1013 IF (l_debug = 1) THEN
1014 debug_print('No reservation is found for line level, demand_source_type_id= ' || p_demand_source_type_id); debug_print('demand_source_header_id = ' || p_demand_source_header_id);
1015 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1016 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
1017 END IF;
1018
1019 l_primary_reserved_quantity := 0;
1020 END;
1021
1022 IF (l_debug = 1) THEN
1023 debug_print('l_primary_reserved_quantity = ' || l_primary_reserved_quantity);
1024 END IF;
1025 END IF;
1026
1027 -- get total ordered quantity at the order line level
1028 -- ????? for available quantity, do we need to substract ordered quantity from shipped quantity
1029 --Bug#7481265.Added overship tolerance in below sql .
1030 BEGIN
1031 SELECT ordered_quantity + (ordered_quantity * ship_tolerance_above/100), order_quantity_uom
1032 INTO l_available_quantity, l_order_quantity_uom_code
1033 FROM oe_order_lines_all
1034 WHERE line_id = p_demand_source_line_id
1035 AND nvl(project_id, -99) = nvl(p_project_id, -99)
1036 AND nvl(task_id, -99) = nvl(p_task_id, -99);
1037 EXCEPTION
1038 WHEN no_data_found THEN
1039 IF (l_debug = 1) THEN
1040 debug_print('No order is found for line_id = ' || p_demand_source_line_id);
1041 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
1042 END IF;
1043
1044 END;
1045
1046 IF (l_rsv_primary_uom_code IS NULL) THEN
1047 l_rsv_primary_uom_code := p_primary_uom_code;
1048 IF (l_rsv_primary_uom_code IS NULL) THEN
1049 BEGIN
1050 SELECT primary_uom_code INTO l_rsv_primary_uom_code FROM
1051 mtl_system_items WHERE organization_id = p_organization_id
1052 AND inventory_item_id = p_item_id;
1053 EXCEPTION WHEN no_data_found THEN
1054 IF (l_debug = 1) THEN
1055 debug_print('Cannot find the primary unit of measure');
1056 END IF;
1057 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
1058 FND_MSG_PUB.ADD;
1059 RAISE fnd_api.g_exc_error;
1060 END;
1061 END IF;
1062
1063 END IF;
1064
1065 IF (l_order_quantity_uom_code <> l_rsv_primary_uom_code) THEN
1066 l_primary_available_qty := inv_convert.inv_um_convert
1067 (
1068 item_id => p_item_id
1069 , lot_number => null
1070 , organization_id => p_organization_id
1071 , precision => null
1072 , from_quantity => l_available_quantity
1073 , from_unit => l_order_quantity_uom_code
1074 , to_unit => l_rsv_primary_uom_code
1075 , from_name => null
1076 , to_name => null
1077 );
1078 ELSE
1079 l_primary_available_qty := l_available_quantity;
1080 END IF;
1081
1082 IF (l_debug = 1) THEN
1083 debug_print('l_available_quantity = ' || l_available_quantity);
1084 debug_print('l_primary_available_qty = ' || l_primary_available_qty);
1085 END IF;
1086
1087 IF (p_demand_source_line_detail is not NULL AND p_demand_source_line_detail <> fnd_api.g_miss_num) THEN
1088
1089 IF (l_debug = 1) THEN
1090 debug_print('p_demand_source_line_detail is not NULL AND p_demand_source_line_detail <> fnd_api.g_miss_num');
1091 END IF;
1092
1093 l_wdd_available_qty := nvl(l_wdd_primary_quantity,0) - nvl(l_wdd_primary_reserved_qty,0);
1094 l_order_available_qty := nvl(l_primary_available_qty,0) - nvl(l_primary_reserved_quantity,0);
1095
1096 IF (l_debug = 1) THEN
1097 debug_print('l_wdd_available_qty = ' || l_wdd_available_qty);
1098 debug_print('l_order_available_qty = ' || l_order_available_qty);
1099 END IF;
1100
1101 IF (l_wdd_available_qty < l_order_available_qty) THEN
1102 l_qty_available_to_reserve := l_wdd_available_qty;
1103 ELSE
1104 l_qty_available_to_reserve := l_order_available_qty;
1105 END IF;
1106 ELSE
1107 l_qty_available_to_reserve := nvl(l_primary_available_qty,0) - nvl(l_primary_reserved_quantity,0);
1108 END IF;
1109
1110 IF (l_debug = 1) THEN
1111 debug_print('l_qty_available_to_reserve = ' || l_qty_available_to_reserve);
1112 END IF;
1113
1114 END IF; -- end of if WIP demand source
1115
1116 IF (l_debug = 1) THEN
1117 debug_print('l_qty_available_to_reserve = ' || l_qty_available_to_reserve);
1118 debug_print('l_primary_available_qty = ' || l_primary_available_qty);
1119 debug_print('l_return_status = ' || l_return_status);
1120 END IF;
1121
1122 x_qty_available_to_reserve := l_qty_available_to_reserve;
1123 x_qty_available := nvl(l_primary_available_qty, 0);
1124
1125 x_return_status := l_return_status;
1126 EXCEPTION
1127 WHEN fnd_api.g_exc_error THEN
1128 x_return_status := fnd_api.g_ret_sts_error;
1129 --
1130 WHEN fnd_api.g_exc_unexpected_error THEN
1131 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1132 --
1133 WHEN OTHERS THEN
1134 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1135 --
1136 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1137 THEN
1138 fnd_msg_pub.add_exc_msg
1139 ( g_pkg_name
1140 , 'available_demand_to_reserve'
1141 );
1142 END IF;
1143 --
1144
1145 END available_demand_to_reserve;
1146
1147 END inv_reservation_avail_pvt;