[Home] [Help]
PACKAGE BODY: APPS.INV_RESERVATION_AVAIL_PVT
Source
1 PACKAGE BODY inv_reservation_avail_pvt AS
2 /* $Header: INVVRVAB.pls 120.23.12020000.4 2013/02/01 06:47:13 brana ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RESERVATION_AVAIL_PVT';
5 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: INVVRVAB.pls 120.23.12020000.4 2013/02/01 06:47:13 brana 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 l_rti_primary_quantity NUMBER := 0; -- 11899495
54
55 BEGIN
56 IF (g_debug IS NULL) THEN
57 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
58 END IF;
59
60 l_debug := g_debug;
61
62 IF (l_debug = 1) THEN
63 debug_print('In available_supply_to_reserve');
64 debug_print('organization id = ' || p_organization_id);
65 debug_print('inventory item id = ' || p_item_id);
66 debug_print('revision = ' || p_revision);
67 debug_print('lot number = ' || p_lot_number);
68 debug_print('subinventory = ' || p_subinventory_code);
69 debug_print('locator id = ' || p_locator_id);
70 debug_print('supply source type id = ' || p_supply_source_type_id);
71 debug_print('supply source header id = ' || p_supply_source_header_id);
72 debug_print('supply source line id = ' || p_supply_source_line_id);
73 debug_print('supply source line detail = ' || p_supply_source_line_detail);
74 debug_print('project id = ' || p_project_id);
75 debug_print('task id = ' || p_task_id);
76 END IF;
77
78 -- error out if supply source type id is null
79 IF (p_supply_source_type_id is null) THEN
80 fnd_message.set_name('INV', 'INV_NO_SUPPLY_TYPE');
81 fnd_msg_pub.add;
82 RAISE fnd_api.g_exc_error;
83 END IF;
84
85 -- for WIP supply source
86 IF (p_supply_source_type_id = inv_reservation_global.g_source_type_wip) THEN
87
88 -- error out if supply source header id is null
89 IF (p_supply_source_header_id is null) THEN
90 fnd_message.set_name('INV','INV_NO_SUPPLY_INFO');
91 fnd_msg_pub.add;
92 RAISE fnd_api.g_exc_error;
93 END IF;
94
95 -- get wip entity type from wip_record_cache
96 inv_reservation_util_pvt.get_wip_cache
97 (
98 x_return_status => l_return_status
99 , p_wip_entity_id => p_supply_source_header_id
100 );
101
102 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
103 RAISE fnd_api.g_exc_error;
104 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
105 RAISE fnd_api.g_exc_unexpected_error;
106 ELSE
107 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_type;
108 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_job;
109 END IF;
110
111 IF (l_debug = 1) THEN
112 debug_print('wip entity type = ' || l_wip_entity_type);
113 END IF;
114
115 -- call availability API for the WIP entity type to get the quantity
116 -- available on the document. This quantity is the quantity ordered
117 -- minus the quantity already delivered on that document. It is the
118 -- expected supply still remainin to be satisfied against the document
119 -- line.
120 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete) THEN
121 -- remove comment later
122 IF (l_debug = 1) THEN
123 debug_print('calling WIP discrete get_available_supply_demand');
124 END IF;
125
126 IF (p_fm_supply_source_type_id =
127 inv_reservation_global.g_source_type_inv) THEN
128 IF (l_debug = 1) THEN
129 debug_print('It is a return transaction.');
130 END IF;
131 l_return_txn := 1;
132 -- 1 means it is a return txn. we are transferring from inv to
133 -- wip and
134 -- 0 means not a return transaction.
135 END IF;
136
137 WIP_RESERVATIONS_GRP.get_available_supply_demand
138 (
139 x_return_status => l_return_status
140 , x_msg_count => l_msg_count
141 , x_msg_data => l_msg_data
142 , x_available_quantity => l_available_quantity
143 , x_source_uom_code => l_source_uom_code
144 , x_source_primary_uom_code => l_source_primary_uom_code
145 , p_organization_id => p_organization_id
146 , p_item_id => p_item_id
147 , p_revision => p_revision
148 , p_lot_number => p_lot_number
149 , p_subinventory_code => p_subinventory_code
150 , p_locator_id => p_locator_id
151 , p_supply_demand_code => 1
152 , p_supply_demand_type_id => p_supply_source_type_id
153 , p_supply_demand_header_id => p_supply_source_header_id
154 , p_supply_demand_line_id => p_supply_source_line_id
155 , p_supply_demand_line_detail => p_supply_source_line_detail
156 , p_lpn_id => p_lpn_id
157 , p_project_id => null -- p_project_id
158 , p_task_id => null -- p_task_id
159 , p_api_version_number => 1.0
160 , p_init_msg_lst => fnd_api.g_false
161 , p_return_txn => l_return_txn
162 );
163
164 IF (l_debug = 1) THEN
165 debug_print('return status from get_available_supply_demand = ' || l_return_status);
166 debug_print('available quantity = ' || l_available_quantity);
167 debug_print('source uom code = ' || l_source_uom_code);
168 debug_print('source primary uom code = ' || l_source_primary_uom_code);
169 END IF;
170
171 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
172 raise fnd_api.g_exc_error;
173 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
174 raise fnd_api.g_exc_unexpected_error;
175 END IF;
176 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_osfm) THEN
177 -- remove comment later
178 IF (l_debug = 1) THEN
179 debug_print('calling osfm get_available_supply_demand');
180 END IF;
181
182 WSM_RESERVATIONS_GRP.get_available_supply_demand
183 (
184 x_return_status => l_return_status
185 , x_msg_count => l_msg_count
186 , x_msg_data => l_msg_data
187 , x_available_quantity => l_available_quantity
188 , x_source_uom_code => l_source_uom_code
189 , x_source_primary_uom_code => l_source_primary_uom_code
190 , p_organization_id => p_organization_id
191 , p_item_id => p_item_id
192 , p_revision => p_revision
193 , p_lot_number => p_lot_number
194 , p_subinventory_code => p_subinventory_code
195 , p_locator_id => p_locator_id
196 , p_supply_demand_code => 1
197 , p_supply_demand_type_id => p_supply_source_type_id
198 , p_supply_demand_header_id => p_supply_source_header_id
199 , p_supply_demand_line_id => p_supply_source_line_id
200 , p_supply_demand_line_detail => p_supply_source_line_detail
201 , p_lpn_id => p_lpn_id
202 , p_project_id => null -- p_project_id
203 , p_task_id => null -- p_task_id
204 , p_api_version_number => 1.0
205 , p_init_msg_lst => fnd_api.g_false
206 );
207
208 IF (l_debug = 1) THEN
209 debug_print('return status from get_available_supply_demand = ' || l_return_status);
210 debug_print('available quantity = ' || l_available_quantity);
211 debug_print('source uom code = ' || l_source_uom_code);
212 debug_print('source primary uom code = ' || l_source_primary_uom_code);
213 END IF;
214
215 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
216 raise fnd_api.g_exc_error;
217 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
218 raise fnd_api.g_exc_unexpected_error;
219 END IF;
220 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo OR
221 l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
222 -- remove comment later
223 IF (l_debug = 1) THEN
224 debug_print('calling fpo get_available_supply_demand');
225 END IF;
226 GME_API_GRP.get_available_supply_demand
227 (
228 x_return_status => l_return_status
229 , x_msg_count => l_msg_count
230 , x_msg_data => l_msg_data
231 , x_available_quantity => l_available_quantity
232 , x_source_uom_code => l_source_uom_code
233 , x_source_primary_uom_code => l_source_primary_uom_code
234 , p_organization_id => p_organization_id
235 , p_item_id => p_item_id
236 , p_revision => p_revision
237 , p_lot_number => p_lot_number
238 , p_subinventory_code => p_subinventory_code
239 , p_locator_id => p_locator_id
240 , p_supply_demand_code => 1
241 , p_supply_demand_type_id => p_supply_source_type_id
242 , p_supply_demand_header_id => p_supply_source_header_id
243 , p_supply_demand_line_id => p_supply_source_line_id
244 , p_supply_demand_line_detail => p_supply_source_line_detail
245 , p_lpn_id => p_lpn_id
246 , p_project_id => null -- p_project_id
247 , p_task_id => null -- p_task_id
248 , p_api_version_number => 1.0
249 , p_init_msg_lst => fnd_api.g_false
250 );
251 /* Added following elsif for bug 13524480 */
252 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_cmro) THEN
253 IF (l_debug = 1) THEN
254 debug_print('calling cmro get_available_supply_demand');
255 END IF;
256
257 AHL_INV_RESERVATIONS_GRP.get_available_supply_demand (
258 p_api_version_number => 1.0
259 , p_init_msg_lst => fnd_api.g_false
260 , x_return_status => l_return_status
261 , x_msg_count => l_msg_count
262 , x_msg_data => l_msg_data
263 , p_organization_id => p_organization_id
264 , p_item_id => p_item_id
265 , p_revision => p_revision
266 , p_lot_number => p_lot_number
267 , p_subinventory_code => p_subinventory_code
268 , p_locator_id => p_locator_id
269 , p_supply_demand_code => 1
270 , p_supply_demand_type_id => p_supply_source_type_id
271 , p_supply_demand_header_id => p_supply_source_header_id
272 , p_supply_demand_line_id => p_supply_source_line_id
273 , p_supply_demand_line_detail => p_supply_source_line_detail
274 , p_lpn_id => p_lpn_id
275 , p_project_id => null -- p_project_id
276 , p_task_id => null -- p_task_id
277 , x_available_quantity => l_available_quantity
278 , x_source_uom_code => l_source_uom_code
279 , x_source_primary_uom_code => l_source_primary_uom_code
280 );
281
282 END IF;
283
284 IF (l_debug = 1) THEN
285 debug_print('return status from get_available_supply_demand = ' || l_return_status);
286 debug_print('available quantity = ' || l_available_quantity);
287 debug_print('source uom code = ' || l_source_uom_code);
288 debug_print('source primary uom code = ' || l_source_primary_uom_code);
289 END IF;
290
291 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
292 raise fnd_api.g_exc_error;
293 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
294 raise fnd_api.g_exc_unexpected_error;
295 END IF;
296
297 -- need uom conversion if source uom is different from primary uom
298 IF (l_available_quantity > 0 AND l_source_uom_code is not NULL AND l_source_uom_code <> l_source_primary_uom_code) THEN
299 IF (l_debug = 1) THEN
300 debug_print('calling inv_convert.inv_um_convert');
301 debug_print('item_id = ' || p_item_id);
302 debug_print('org_id = ' || p_organization_id);
303 debug_print('lot_number = ' || p_lot_number);
304 debug_print('l_available_quantity = ' || l_available_quantity);
305 debug_print('l_source_uom_code = ' || l_source_uom_code);
306 debug_print('l_source_primary_uom_code = ' || l_source_primary_uom_code);
307 END IF;
308
309 l_primary_available_qty := inv_convert.inv_um_convert
310 (
311 item_id => p_item_id
312 , lot_number => p_lot_number
313 , organization_id => p_organization_id
314 , precision => null
315 , from_quantity => l_available_quantity
316 , from_unit => l_source_uom_code
317 , to_unit => l_source_primary_uom_code
318 , from_name => null
319 , to_name => null
320 );
321 ELSE
322 l_primary_available_qty := l_available_quantity;
323 END IF;
324
325
326 -- get the sum of quantity that is already reserved on the document.
327 BEGIN
328 -- BUG 5052424 BEGIN
329 -- For OPM assess exisiting reservations at line level
330 -- Otherwise assess at header level
331 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo OR
332 l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
333 SELECT nvl(sum(primary_reservation_quantity), 0)
334 INTO l_primary_reserved_quantity
335 FROM mtl_reservations
336 WHERE supply_source_type_id = p_supply_source_type_id
337 AND supply_source_header_id = p_supply_source_header_id
338 AND supply_source_line_id = p_supply_source_line_id;
339 ELSE
340 SELECT nvl(sum(primary_reservation_quantity), 0)
341 INTO l_primary_reserved_quantity
342 FROM mtl_reservations
343 WHERE supply_source_type_id = p_supply_source_type_id
344 AND supply_source_header_id = p_supply_source_header_id;
345 END IF;
346 -- BUG 5052424 END
347 EXCEPTION
348 WHEN no_data_found THEN
349 IF (l_debug = 1) THEN
350 debug_print('No reservation found');
351 END IF;
352
353 l_primary_reserved_quantity := 0;
354 END;
355
356 -- bug 10039922 Added g_source_type_req
357
358 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_po OR
359 p_supply_source_type_id = inv_reservation_global.g_source_type_asn OR
360 p_supply_source_type_id = inv_reservation_global.g_source_type_intransit OR
361 p_supply_source_type_id = inv_reservation_global.g_source_type_req OR
362 p_supply_source_type_id = inv_reservation_global.g_source_type_internal_req) THEN
363
364 -- error out if supply source header or line id is null
365 IF (p_supply_source_header_id is null or p_supply_source_line_id is null) THEN
366 fnd_message.set_name('INV','INV_NO_SUPPLY_INFO');
367 fnd_msg_pub.add;
368 RAISE fnd_api.g_exc_error;
369 END IF;
370
371 -- for ASN supply, error if if supply source line detail is null
372 IF (p_supply_source_type_id = inv_reservation_global.g_source_type_asn
373 and p_supply_source_line_detail is null) THEN
374 fnd_message.set_name('INV','INV_NO_SUPPLY_INFO');
375 fnd_msg_pub.add;
376 RAISE fnd_api.g_exc_error;
377 END IF;
378
379 -- call availability API for PO, ASN, Intransit shipment or Internal Req
380 -- For PO, the available quantity is the quantity ordered
381 -- minus the quantity already delivered on that document minu quantity received
382 -- minus quantity transferred to the ASN (for WMS orgs). It is the
383 -- expected supply still remaining to be satisfied against the document
384 -- line.
385 -- For ASN, the availability is the total quantity on the ASN - quantity
386 -- received on the ASN.
387 -- For Intransit shipment, the availability is the total quantity
388 -- on the intransit shipment - quantity received against the intransit shipment
389 -- line.
390 -- For internal Req, the availability is the total quantity on the internal
391 -- requisition document - quantity received against this document.
392 IF (l_debug = 1) THEN
393 debug_print('calling RCV get_available_supply_demand');
394 END IF;
395
396 RCV_availability.get_available_supply_demand
397 (
398 x_return_status => l_return_status
399 , x_msg_count => l_msg_count
400 , x_msg_data => l_msg_data
401 , x_available_quantity => l_available_quantity
402 , x_source_uom_code => l_source_uom_code
403 , x_source_primary_uom_code => l_source_primary_uom_code
404 , p_organization_id => p_organization_id
405 , p_item_id => p_item_id
406 , p_revision => p_revision
407 , p_lot_number => p_lot_number
408 , p_subinventory_code => p_subinventory_code
409 , p_locator_id => p_locator_id
410 , p_supply_demand_code => 1
411 , p_supply_demand_type_id => p_supply_source_type_id
412 , p_supply_demand_header_id => p_supply_source_header_id
413 , p_supply_demand_line_id => p_supply_source_line_id
414 , p_supply_demand_line_detail => p_supply_source_line_detail
415 , p_lpn_id => p_lpn_id
416 , p_project_id => p_project_id
417 , p_task_id => p_task_id
418 , p_api_version_number => 1.0
419 , p_init_msg_lst => fnd_api.g_false
420 );
421
422 IF (l_debug = 1) THEN
423 debug_print('return status from RCV_availability.get_available_supply_demand = ' || l_return_status);
424 debug_print('available quantity = ' || l_available_quantity);
425 debug_print('source uom code = ' || l_source_uom_code);
426 debug_print('source primary uom code = ' || l_source_primary_uom_code);
427 END IF;
428
429 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
430 raise fnd_api.g_exc_error;
431 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
432 raise fnd_api.g_exc_unexpected_error;
433 END IF;
434
435 -- need uom conversion if source uom is different from primary uom
436 IF (l_available_quantity > 0 AND l_source_uom_code is not NULL AND l_source_uom_code <> l_source_primary_uom_code) THEN
437 IF (l_debug = 1) THEN
438 debug_print('calling inv_convert.inv_um_convert');
439 debug_print('item_id = ' || p_item_id);
440 debug_print('org_id = ' || p_organization_id);
441 debug_print('lot_number = ' || p_lot_number);
442 debug_print('l_available_quantity = ' || l_available_quantity);
443 debug_print('l_source_uom_code = ' || l_source_uom_code);
444 debug_print('l_source_primary_uom_code = ' || l_source_primary_uom_code);
445 END IF;
446
447 l_primary_available_qty := inv_convert.inv_um_convert
448 (
449 item_id => p_item_id
450 , lot_number => p_lot_number
451 , organization_id => p_organization_id
452 , precision => null
453 , from_quantity => l_available_quantity
454 , from_unit => l_source_uom_code
455 , to_unit => l_source_primary_uom_code
456 , from_name => null
457 , to_name => null
458 );
459 ELSE
460 l_primary_available_qty := l_available_quantity;
461 END IF;
462
463 -- bug 10039922 Added g_source_type_req
464 IF (p_supply_source_type_id = inv_reservation_global.g_source_type_po OR
465 p_supply_source_type_id = inv_reservation_global.g_source_type_intransit OR
466 p_supply_source_type_id = inv_reservation_global.g_source_type_req OR
467 p_supply_source_type_id = inv_reservation_global.g_source_type_internal_req) THEN
468
469 -- get the sum of quantity that is already reserved on the document.
470 BEGIN
471 SELECT nvl(sum(primary_reservation_quantity), 0)
472 INTO l_primary_reserved_quantity
473 FROM mtl_reservations
474 WHERE supply_source_type_id = p_supply_source_type_id
475 AND supply_source_header_id = p_supply_source_header_id
476 AND supply_source_line_id = p_supply_source_line_id
477 AND nvl(project_id, -99) = nvl(p_project_id, -99)
478 AND nvl(task_id, -99) = nvl(p_task_id, -99);
479 EXCEPTION
480 WHEN no_data_found THEN
481 IF (l_debug = 1) THEN
482 debug_print('No reservation found');
483 END IF;
484
485 l_primary_reserved_quantity := 0;
486 END;
487
488
489 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_asn) THEN
490
491 -- get the sum of quantity that is already reserved on the document.
492 BEGIN
493 SELECT nvl(sum(primary_reservation_quantity), 0)
494 INTO l_primary_reserved_quantity
495 FROM mtl_reservations
496 WHERE supply_source_type_id = p_supply_source_type_id
497 AND supply_source_header_id = p_supply_source_header_id
498 AND supply_source_line_id = p_supply_source_line_id
499 AND supply_source_line_detail = p_supply_source_line_detail
500 AND nvl(project_id, -99) = nvl(p_project_id, -99)
501 AND nvl(task_id, -99) = nvl(p_task_id, -99);
502 EXCEPTION
503 WHEN no_data_found THEN
504 IF (l_debug = 1) THEN
505 debug_print('No reservation found');
506 END IF;
507
508 l_primary_reserved_quantity := 0;
509 END;
510
511
512 END IF;
513
514 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_rcv) THEN
515
516 -- error out if organization_id or item id is null
517 IF (p_organization_id is null or p_item_id is null) THEN
518 fnd_message.set_name('INV', 'INV_NO_ORG_ITEM');
519 fnd_msg_pub.add;
520 RAISE fnd_api.g_exc_error;
521 END IF;
522
523 -- call availability API for available quantity in receiving
524 IF (l_debug = 1) THEN
525 debug_print('Receiving supply, before calling INV_RCV_availability.get_available_supply_demand');
526 END IF;
527
528 INV_RCV_availability.get_available_supply_demand
529 (
530 x_return_status => l_return_status
531 , x_msg_count => l_msg_count
532 , x_msg_data => l_msg_data
533 , x_available_quantity => l_available_quantity
534 , x_source_uom_code => l_source_uom_code
535 , x_source_primary_uom_code => l_source_primary_uom_code
536 , p_organization_id => p_organization_id
537 , p_item_id => p_item_id
538 , p_revision => p_revision
539 , p_lot_number => p_lot_number
540 , p_subinventory_code => p_subinventory_code
541 , p_locator_id => p_locator_id
542 , p_supply_demand_code => 1
543 , p_supply_demand_type_id => p_supply_source_type_id
544 , p_supply_demand_header_id => p_supply_source_header_id
545 , p_supply_demand_line_id => p_supply_source_line_id
546 , p_supply_demand_line_detail => p_supply_source_line_detail
547 , p_lpn_id => p_lpn_id
548 , p_project_id => null -- p_project_id
549 , p_task_id => null -- p_task_id
550 , p_api_version_number => 1.0
551 , p_init_msg_lst => fnd_api.g_false
552 );
553
554 IF (l_debug = 1) THEN
555 debug_print('return status from get_available_supply_demand = ' || l_return_status);
556 debug_print('available quantity = ' || l_available_quantity);
557 debug_print('source uom code = ' || l_source_uom_code);
558 debug_print('source primary uom code = ' || l_source_primary_uom_code);
559 END IF;
560
561 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
562 raise fnd_api.g_exc_error;
563 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
564 raise fnd_api.g_exc_unexpected_error;
565 END IF;
566
567 -- need uom conversion if source uom is different from primary uom
568 IF (l_available_quantity > 0 AND l_source_uom_code is not NULL AND l_source_uom_code <> l_source_primary_uom_code) THEN
569 IF (l_debug = 1) THEN
570 debug_print('calling inv_convert.inv_um_convert');
571 debug_print('item_id = ' || p_item_id);
572 debug_print('org_id = ' || p_organization_id);
573 debug_print('lot_number = ' || p_lot_number);
574 debug_print('l_available_quantity = ' || l_available_quantity);
575 debug_print('l_source_uom_code = ' || l_source_uom_code);
576 debug_print('l_source_primary_uom_code = ' || l_source_primary_uom_code);
577 END IF;
578
579 l_primary_available_qty := inv_convert.inv_um_convert
580 (
581 item_id => p_item_id
582 , lot_number => p_lot_number
583 , organization_id => p_organization_id
584 , precision => null
585 , from_quantity => l_available_quantity
586 , from_unit => l_source_uom_code
587 , to_unit => l_source_primary_uom_code
588 , from_name => null
589 , to_name => null
590 );
591 ELSE
592 l_primary_available_qty := l_available_quantity;
593 END IF;
594
595
596 -- get the sum of quantity that is already reserved on the document.
597 BEGIN
598 SELECT nvl(sum(primary_reservation_quantity), 0)
599 INTO l_primary_reserved_quantity
600 FROM mtl_reservations
601 WHERE supply_source_type_id = p_supply_source_type_id
602 AND organization_id = p_organization_id
603 AND inventory_item_id = p_item_id
604 AND demand_source_type_id <> 5;-- bug 9706800: Consider reservations only for Sales Order and not for WIP Jobs/OPM batches since MOL quantity
605 -- which is being crossdocked to wip is already taken in to consideration (inv_rcv_availability.get_available_supply_demand)
606 EXCEPTION
607 WHEN no_data_found THEN
608 IF (l_debug = 1) THEN
609 debug_print('No reservation found');
610 END IF;
611
612 l_primary_reserved_quantity := 0;
613 END;
614
615 END IF; -- end of WIP supply
616 --Start 11899495
617 IF nvl(l_primary_reserved_quantity,0) > 0 THEN
618 BEGIN
619 SELECT Nvl(ABS(SUM(primary_quantity)),0)
620 INTO l_rti_primary_quantity
621 FROM rcv_transactions_interface rti
622 WHERE to_organization_id = p_organization_id
623 AND item_id = p_item_id
624 AND NVL(item_revision, '@@@') = NVL(p_revision,NVL(item_revision, '@@@'))
625 AND rti.processing_status_code <> 'ERROR'
626 AND rti.transaction_status_code <> 'ERROR'
627 AND NOT exists (SELECT '1' FROM rcv_transactions rt
628 WHERE rt.interface_transaction_id = rti.interface_transaction_id)
629 AND (TRANSACTION_TYPE = 'DELIVER'
630 OR (TRANSACTION_TYPE IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
631 AND EXISTS (SELECT '1' FROM rcv_transactions rt
632 WHERE rt.transaction_id = rti.parent_transaction_id
633 AND rt.transaction_type IN ('RECEIVE','ACCEPT','REJECT','TRANSFER')))
634 OR (TRANSACTION_TYPE IN ('CORRECT')
635 AND quantity < 0
636 AND EXISTS (SELECT '1' FROM rcv_transactions rt
637 WHERE rt.transaction_id = rti.parent_transaction_id
638 AND rt.transaction_type IN ('RECEIVE')))
639 OR (TRANSACTION_TYPE IN ('CORRECT')
640 AND quantity > 0
641 AND EXISTS (SELECT '1' FROM rcv_transactions rt
642 WHERE rt.transaction_id = rti.parent_transaction_id
643 AND rt.transaction_type IN ('DELIVER'))));
644 EXCEPTION
645 WHEN OTHERS THEN
646 l_rti_primary_quantity := 0;
647 END;
648
649 END IF;
650
651 IF (l_debug = 1) THEN
652 debug_print('l_rti_primary_quantity:'||l_rti_primary_quantity);
653 END IF;
654 -- calculate the final available to reserve quantity from available quantity from document and
655 -- reserved quantity of the document in primary uom
656 IF (l_debug = 1) THEN
657 debug_print('primary available qty = ' || l_primary_available_qty);
658 debug_print('primary reserved qty = ' || l_primary_reserved_quantity);
659 END IF;
660
661 IF nvl(l_primary_reserved_quantity, 0) >= nvl(l_rti_primary_quantity, 0) THEN
662 l_qty_available_to_reserve := nvl(l_primary_available_qty, 0) - (nvl(l_primary_reserved_quantity, 0) - nvl(l_rti_primary_quantity, 0));
663 ELSE
664 l_qty_available_to_reserve := nvl(l_primary_available_qty, 0) ;
665 END IF;
666 --End 11899495
667
668 x_qty_available_to_reserve := l_qty_available_to_reserve;
669 x_qty_available := nvl(l_primary_available_qty, 0);
670
671 x_return_status := l_return_status;
672 EXCEPTION
673 WHEN fnd_api.g_exc_error THEN
674 x_return_status := fnd_api.g_ret_sts_error;
675 IF (l_debug = 1) THEN
676 debug_print('expected error in available_supply_to_reserve');
677 END IF;
678 --
679 WHEN fnd_api.g_exc_unexpected_error THEN
680 x_return_status := fnd_api.g_ret_sts_unexp_error ;
681 IF (l_debug = 1) THEN
682 debug_print('unexpected error in available_supply_to_reserve');
683 debug_print('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM);
684 END IF;
685 --
686 WHEN OTHERS THEN
687 x_return_status := fnd_api.g_ret_sts_unexp_error ;
688 IF (l_debug = 1) THEN
689 debug_print('others error in available_supply_to_reserve');
690 debug_print('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM);
691 END IF;
692 --
693 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
694 THEN
695 fnd_msg_pub.add_exc_msg
696 ( g_pkg_name
697 , 'available_supply_to_reserve'
698 );
699 END IF;
700 --
701 END available_supply_to_reserve;
702
703
704 PROCEDURE available_demand_to_reserve
705 ( p_api_version_number IN NUMBER DEFAULT 1.0
706 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
707 , x_return_status OUT NOCOPY VARCHAR2
708 , x_msg_count OUT NOCOPY NUMBER
709 , x_msg_data OUT NOCOPY VARCHAR2
710 , p_organization_id IN NUMBER DEFAULT NULL
711 , p_item_id IN NUMBER DEFAULT NULL
712 , p_primary_uom_code IN VARCHAR2 DEFAULT NULL
713 , p_demand_source_type_id IN NUMBER
714 , p_demand_source_header_id IN NUMBER
715 , p_demand_source_line_id IN NUMBER
716 , p_demand_source_line_detail IN NUMBER DEFAULT fnd_api.g_miss_num
717 , p_project_id IN NUMBER DEFAULT NULL
718 , p_task_id IN NUMBER DEFAULT NULL
719 , x_qty_available_to_reserve OUT NOCOPY NUMBER
720 , x_qty_available OUT NOCOPY NUMBER
721 ) IS
722 x_qty_available_to_reserve2 NUMBER;
723 x_qty_available2 NUMBER;
724
725 BEGIN
726 --- MUOM Fulfillment Call the overloaded procedure
727 available_demand_to_reserve(
728 p_api_version_number => p_api_version_number
729 , p_init_msg_lst => p_init_msg_lst
730 , x_return_status => x_return_status
731 , x_msg_count => x_msg_count
732 , x_msg_data => x_msg_data
733 , p_organization_id => p_organization_id
734 , p_item_id => p_item_id
735 , p_primary_uom_code => p_primary_uom_code
736 , p_demand_source_type_id =>p_demand_source_type_id
737 , p_demand_source_header_id => p_demand_source_header_id
738 , p_demand_source_line_id => p_demand_source_line_id
739 , p_demand_source_line_detail =>p_demand_source_line_detail
740 , p_project_id => p_project_id
741 , p_task_id => p_task_id
742 , x_qty_available_to_reserve => x_qty_available_to_reserve
743 , x_qty_available => x_qty_available
744 , x_qty_available_to_reserve2 => x_qty_available_to_reserve2
745 , x_qty_available2 => x_qty_available2) ;
746
747 END available_demand_to_reserve;
748
749 --MUOM overloaded procedure for x_qty_available_to_reserve2, x_qty_available2
750
751 PROCEDURE available_demand_to_reserve
752 (
753 p_api_version_number IN NUMBER DEFAULT 1.0
754 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
755 , x_return_status OUT NOCOPY VARCHAR2
756 , x_msg_count OUT NOCOPY NUMBER
757 , x_msg_data OUT NOCOPY VARCHAR2
758 , p_organization_id IN NUMBER DEFAULT NULL
759 , p_item_id IN NUMBER DEFAULT NULL
760 , p_primary_uom_code IN VARCHAR2 DEFAULT NULL
761 , p_demand_source_type_id IN NUMBER
762 , p_demand_source_header_id IN NUMBER
763 , p_demand_source_line_id IN NUMBER
764 , p_demand_source_line_detail IN NUMBER DEFAULT fnd_api.g_miss_num
765 , p_project_id IN NUMBER DEFAULT NULL
766 , p_task_id IN NUMBER DEFAULT NULL
767 , x_qty_available_to_reserve OUT NOCOPY NUMBER
768 , x_qty_available OUT NOCOPY NUMBER
769 , x_qty_available_to_reserve2 OUT NOCOPY NUMBER
770 , x_qty_available2 OUT NOCOPY NUMBER
771 ) IS
772 l_api_version_number CONSTANT NUMBER := 1.0;
773 l_api_name CONSTANT VARCHAR2(30) := 'avilable_demand_to_reserve';
774 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
775 l_msg_count NUMBER;
776 l_msg_data VARCHAR2(2000);
777 l_debug NUMBER;
778 l_wip_entity_type NUMBER;
779 l_wip_job_type VARCHAR2(15);
780 l_available_quantity NUMBER;
781 l_source_uom_code VARCHAR2(3);
782 l_source_primary_uom_code VARCHAR2(3);
783 l_primary_reserved_quantity NUMBER;
784 l_qty_available_to_reserve NUMBER;
785 l_primary_available_qty NUMBER;
786 l_wdd_primary_quantity NUMBER;
787 l_wdd_primary_reserved_qty NUMBER;
788 l_wdd_available_qty NUMBER;
789 l_order_available_qty NUMBER;
790 l_rsv_primary_uom_code VARCHAR2(3);
791 l_order_quantity_uom_code VARCHAR2(3);
792 l_wdd_picked_qty NUMBER := 0; --Added for Bug# 8807194
793 l_primary_wdd_picked_qty NUMBER; --Added for Bug# 8807194
794 l_wdd_uom_code VARCHAR2(3); --Added for Bug# 8807194
795 l_over_shippable_qty NUMBER ; --Bug#8983636
796
797 --Bug 12978409: start
798 lot_conv_factor_flag NUMBER := 0;
799 l_lot_primary_rsv_qty_total NUMBER := 0;
800 l_lot_rsv_quantity_rsv_uom NUMBER;
801 l_lot_primary_rsv_qty NUMBER;
802 l_order_line_uom VARCHAR2(3);
803 l_lot_rsv_qty_order_uom NUMBER;
804
805 /* MUOM Fulfillment Project*/
806 l_wdd_secondary_quantity NUMBER;
807 l_wdd_secondary_reserved_qty NUMBER;
808 l_rsv_secondary_uom_code VARCHAR2(3);
809 l_secondary_reserved_quantity NUMBER;
810 l_available_quantity2 NUMBER;
811 l_order_quantity_uom2 VARCHAR2(3);
812 l_wdd_picked_qty2 NUMBER;
813 l_wdd_uom2 VARCHAR2(3);
814 l_wdd_available_qty2 number;
815 l_order_available_qty2 number;
816 l_qty_available_to_reserve2 number;
817
818 CURSOR check_if_lot_conv_exists(p_lot_number varchar2, p_inventory_item_id number, p_organization_id number) IS
819 SELECT count(*)
820 FROM mtl_lot_uom_class_conversions
821 WHERE lot_number = p_lot_number
822 AND inventory_item_id = p_inventory_item_id
823 AND organization_id = p_organization_id
824 AND (disable_date IS NULL or disable_date > sysdate);
825
826 CURSOR rsv_with_lots IS
827 SELECT organization_id, inventory_item_id, lot_number,
828 primary_uom_code, primary_reservation_quantity, reservation_uom_code
829 FROM mtl_reservations
830 WHERE demand_source_type_id = p_demand_source_type_id
831 AND demand_source_header_id = p_demand_source_header_id
832 AND demand_source_line_id = p_demand_source_line_id
833 AND demand_source_line_detail is null
834 AND lot_number is not null;
835
836 CURSOR get_order_line_uom IS
837 SELECT order_quantity_uom
838 FROM oe_order_lines_all
839 WHERE line_id = p_demand_source_line_id;
840
841 --Bug 12978409: end
842 BEGIN
843 IF (g_debug IS NULL) THEN
844 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
845 END IF;
846
847 l_debug := g_debug;
848
849 IF (l_debug = 1) THEN
850 debug_print('In available_demand_to_reserve');
851 debug_print('demand source type id = ' || p_demand_source_type_id);
852 debug_print('demand source header id = ' || p_demand_source_header_id);
853 debug_print('demand source line id = ' || p_demand_source_line_id);
854 debug_print('demand source line detail = ' || p_demand_source_line_detail);
855 debug_print('project id = ' || p_project_id);
856 debug_print('task id = ' || p_task_id);
857 END IF;
858
859 -- error out if demand source type id is null
860 IF (p_demand_source_type_id is null) THEN
861 fnd_message.set_name('INV', 'INV_NO_DEMAND_TYPE');
862 fnd_msg_pub.add;
863 RAISE fnd_api.g_exc_error;
864 END IF;
865
866 -- for WIP demand source
867 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip) THEN
868
869 -- error out if demand source header id is null
870 IF (p_demand_source_header_id is null) THEN
871 fnd_message.set_name('INV','INV_NO_DEMAND_INFO');
872 fnd_msg_pub.add;
873 RAISE fnd_api.g_exc_error;
874 END IF;
875
876 -- get wip entitty type from wip_record_cache
877 inv_reservation_util_pvt.get_wip_cache
878 (
879 x_return_status => l_return_status
880 , p_wip_entity_id => p_demand_source_header_id
881 );
882
883 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
884 RAISE fnd_api.g_exc_error;
885 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
886 RAISE fnd_api.g_exc_unexpected_error;
887 ELSE
888 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_demand_source_header_id).wip_entity_type;
889 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_demand_source_header_id).wip_entity_job;
890 END IF;
891
892 IF (l_debug = 1) THEN
893 debug_print('wip entity type = ' || l_wip_entity_type);
894 END IF;
895
896 -- call availability API for the WIP entity type to get the quantity
897 -- available on the document. This quantity is the quantity ordered
898 -- minus the quantity already delivered on that document. It is the
899 -- expected demand still remaining to be satisfied against the document
900 -- line.
901 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_cmro) THEN
902 IF (l_debug = 1) THEN
903 debug_print('calling WIP cmro get_available_supply_demand');
904 END IF;
905
906 AHL_INV_RESERVATIONS_GRP.get_available_supply_demand
907 (
908 x_return_status => l_return_status
909 , x_msg_count => l_msg_count
910 , x_msg_data => l_msg_data
911 , x_available_quantity => l_available_quantity
912 , x_source_uom_code => l_source_uom_code
913 , x_source_primary_uom_code => l_source_primary_uom_code
914 , p_organization_id => null
915 , p_item_id => null
916 , p_revision => null
917 , p_lot_number => null
918 , p_subinventory_code => null
919 , p_locator_id => null
920 , p_supply_demand_code => 2
921 , p_supply_demand_type_id => p_demand_source_type_id
922 , p_supply_demand_header_id => p_demand_source_header_id
923 , p_supply_demand_line_id => p_demand_source_line_id
924 , p_supply_demand_line_detail => p_demand_source_line_detail
925 , p_lpn_id => null
926 , p_project_id => null -- p_project_id
927 , p_task_id => null -- p_task_id
928 , p_api_version_number => 1.0
929 , p_init_msg_lst => fnd_api.g_false
930 );
931
932 IF (l_debug = 1) THEN
933 debug_print('return status from cmro get_available_supply_demand = ' || l_return_status);
934 debug_print('available quantity = ' || l_available_quantity);
935 debug_print('source uom code = ' || l_source_uom_code);
936 debug_print('source primary uom code = ' || l_source_primary_uom_code);
937 END IF;
938
939 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
940 raise fnd_api.g_exc_error;
941 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
942 raise fnd_api.g_exc_unexpected_error;
943 END IF;
944
945 -- get the sum of quantity that is already reserved on the document.
946 -- bug #5458083 added demand_source_line_detail in the where clause
947 -- for cmro demand.
948 BEGIN
949 SELECT nvl(sum(primary_reservation_quantity), 0)
950 INTO l_primary_reserved_quantity
951 FROM mtl_reservations
952 WHERE demand_source_type_id = p_demand_source_type_id
953 AND demand_source_header_id = p_demand_source_header_id
954 AND demand_source_line_id = p_demand_source_line_id
955 AND demand_source_line_detail = p_demand_source_line_detail;
956 EXCEPTION
957 WHEN no_data_found THEN
958 IF (l_debug = 1) THEN
959 debug_print('No reservation found for cmro test number');
960 END IF;
961
962 l_primary_reserved_quantity := 0;
963 END;
964
965
966 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo OR
967 l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
968 IF (l_debug = 1) THEN
969 debug_print('calling opm get_available_supply_demand');
970 END IF;
971 GME_API_GRP.get_available_supply_demand
972 (
973 x_return_status => l_return_status
974 , x_msg_count => l_msg_count
975 , x_msg_data => l_msg_data
976 , x_available_quantity => l_available_quantity
977 , x_source_uom_code => l_source_uom_code
978 , x_source_primary_uom_code => l_source_primary_uom_code
979 , p_organization_id => null
980 , p_item_id => null
981 , p_revision => null
982 , p_lot_number => null
983 , p_subinventory_code => null
984 , p_locator_id => null
985 , p_supply_demand_code => 2
986 , p_supply_demand_type_id => p_demand_source_type_id
987 , p_supply_demand_header_id => p_demand_source_header_id
988 , p_supply_demand_line_id => p_demand_source_line_id
989 , p_supply_demand_line_detail => p_demand_source_line_detail
990 , p_lpn_id => null
991 , p_project_id => null -- p_project_id
992 , p_task_id => null -- p_task_id
993 , p_api_version_number => 1.0
994 , p_init_msg_lst => fnd_api.g_false
995 );
996
997 IF (l_debug = 1) THEN
998 debug_print('return status from batch/fpo get_available_supply_demand = ' || l_return_status);
999 debug_print('available quantity = ' || l_available_quantity);
1000 debug_print('source uom code = ' || l_source_uom_code);
1001 debug_print('source primary uom code = ' || l_source_primary_uom_code);
1002 END IF;
1003
1004 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1005 raise fnd_api.g_exc_error;
1006 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1007 raise fnd_api.g_exc_unexpected_error;
1008 END IF;
1009
1010 -- get the sum of quantity that is already reserved on the document.
1011 BEGIN
1012 SELECT nvl(sum(primary_reservation_quantity), 0)
1013 INTO l_primary_reserved_quantity
1014 FROM mtl_reservations
1015 WHERE demand_source_type_id = p_demand_source_type_id
1016 AND demand_source_header_id = p_demand_source_header_id
1017 AND demand_source_line_id = p_demand_source_line_id;
1018 EXCEPTION
1019 WHEN no_data_found THEN
1020 IF (l_debug = 1) THEN
1021 debug_print('No reservation found for batch/fpo');
1022 END IF;
1023
1024 l_primary_reserved_quantity := 0;
1025 END;
1026
1027 END IF;
1028
1029 -- need uom conversion if source uom is different from primary uom
1030 IF (l_source_uom_code <> l_source_primary_uom_code) THEN
1031 IF (l_debug = 1) THEN
1032 debug_print('calling inv_convert.inv_um_convert');
1033 debug_print('l_available_quantity = ' || l_available_quantity);
1034 debug_print('l_source_uom_code = ' || l_source_uom_code);
1035 debug_print('l_source_primary_uom_code = ' || l_source_primary_uom_code);
1036 END IF;
1037
1038 l_primary_available_qty := inv_convert.inv_um_convert
1039 (
1040 item_id => p_item_id
1041 , lot_number => null
1042 , organization_id => p_organization_id
1043 , precision => null
1044 , from_quantity => l_available_quantity
1045 , from_unit => l_source_uom_code
1046 , to_unit => l_source_primary_uom_code
1047 , from_name => null
1048 , to_name => null
1049 );
1050 ELSE
1051 l_primary_available_qty := l_available_quantity;
1052 END IF;
1053
1054 l_qty_available_to_reserve := l_primary_available_qty - l_primary_reserved_quantity;
1055
1056 ELSIF (p_demand_source_type_id in
1057 (inv_reservation_global.g_source_type_oe,
1058 inv_reservation_global.g_source_type_internal_ord,
1059 inv_reservation_global.g_source_type_rma)) THEN
1060
1061 IF (p_demand_source_line_detail is not NULL AND p_demand_source_line_detail <> fnd_api.g_miss_num) THEN
1062
1063 IF (l_debug = 1) THEN
1064 debug_print('p_demand_source_line_detail is not NULL and p_demand_source_line_detail <> fnd_api.g_miss_num');
1065 END IF;
1066
1067 -- get wdd requested quantity with line detail level
1068 BEGIN
1069 SELECT nvl(sum(requested_quantity), 0) , nvl(sum(requested_quantity2), 0)
1070 INTO l_wdd_primary_quantity, l_wdd_secondary_quantity
1071 FROM wsh_delivery_details
1072 WHERE source_line_id = p_demand_source_line_id
1073 AND delivery_detail_id = p_demand_source_line_detail
1074 AND nvl(project_id, -99) = nvl(p_project_id, -99)
1075 AND nvl(task_id, -99) = nvl(p_task_id, -99);
1076 EXCEPTION
1077 WHEN no_data_found THEN
1078 IF (l_debug = 1) THEN
1079 debug_print('No wdd found for source_line_id: '|| p_demand_source_line_id);
1080 debug_print('demand_source_line_detail: ' || p_demand_source_line_detail);
1081 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
1082 END IF;
1083
1084 FND_MESSAGE.SET_NAME('INV', 'INV_WDD_NOT_FOUND');
1085 FND_MSG_PUB.ADD;
1086 RAISE fnd_api.g_exc_error;
1087 END;
1088
1089 IF (l_debug = 1) THEN
1090 debug_print('l_wdd_primary_quantity = ' || l_wdd_primary_quantity);
1091 debug_print('l_wdd_secondary_quantity = ' || l_wdd_secondary_quantity);
1092 END IF;
1093
1094 -- get reservation quantity against the wdd with line detail level
1095 BEGIN
1096 SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
1097 ,nvl(sum(secondary_reservation_quantity), 0)
1098 INTO l_wdd_primary_reserved_qty, l_rsv_primary_uom_code, l_wdd_secondary_reserved_qty
1099 FROM mtl_reservations
1100 WHERE demand_source_type_id = p_demand_source_type_id
1101 AND demand_source_header_id = p_demand_source_header_id
1102 AND demand_source_line_id = p_demand_source_line_id
1103 AND demand_source_line_detail = p_demand_source_line_detail
1104 GROUP BY primary_uom_code;
1105 EXCEPTION
1106 WHEN no_data_found THEN
1107 IF (l_debug = 1) THEN
1108 debug_print('No reservation is found for detail level, demand_source_type_id= ' || p_demand_source_type_id);
1109 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
1110 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1111 debug_print('demand_source_line_detail = ' || p_demand_source_line_detail);
1112 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
1113 END IF;
1114
1115 l_wdd_primary_reserved_qty := 0;
1116 l_wdd_secondary_reserved_qty:=0;
1117 END;
1118
1119 IF (l_debug = 1) THEN
1120 debug_print('l_wdd_primary_reserved_qty = ' || l_wdd_primary_reserved_qty);
1121 debug_print('l_wdd_secondary_reserved_qty = ' || l_wdd_secondary_reserved_qty);
1122 END IF;
1123
1124 -- get all reservation quantity at the order line level
1125 BEGIN
1126 SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
1127 ,nvl(sum(secondary_reservation_quantity), 0)
1128 INTO l_primary_reserved_quantity, l_rsv_primary_uom_code ,l_secondary_reserved_quantity
1129 FROM mtl_reservations
1130 WHERE demand_source_type_id = p_demand_source_type_id
1131 AND demand_source_header_id = p_demand_source_header_id
1132 AND demand_source_line_id = p_demand_source_line_id
1133 GROUP BY primary_uom_code;
1134 EXCEPTION
1135 WHEN no_data_found THEN
1136 IF (l_debug = 1) THEN
1137 debug_print('No reservation is found for line level, demand_source_type_id= ' || p_demand_source_type_id);
1138 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
1139 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1140 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
1141 END IF;
1142
1143 l_primary_reserved_quantity := 0;
1144 l_secondary_reserved_quantity :=0;
1145 END;
1146
1147 IF (l_debug = 1) THEN
1148 debug_print('l_primary_reserved_quantity = ' || l_primary_reserved_quantity);
1149 debug_print('l_secondary_reserved_quantity = ' || l_secondary_reserved_quantity);
1150 END IF;
1151
1152 ELSIF (p_demand_source_line_detail = fnd_api.g_miss_num) THEN
1153
1154 IF (l_debug = 1) THEN
1155 debug_print('p_demand_source_line_detail = fnd_api.g_miss_num');
1156 END IF;
1157
1158 -- get all reservation quantity at the order line level
1159 BEGIN
1160 SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
1161 ,nvl(sum(secondary_reservation_quantity), 0)
1162 INTO l_primary_reserved_quantity, l_rsv_primary_uom_code ,l_secondary_reserved_quantity
1163 FROM mtl_reservations
1164 WHERE demand_source_type_id = p_demand_source_type_id
1165 AND demand_source_header_id = p_demand_source_header_id
1166 AND demand_source_line_id = p_demand_source_line_id
1167 AND lot_number IS NULL --Bug 12978409
1168 GROUP BY primary_uom_code;
1169 EXCEPTION
1170 WHEN no_data_found THEN
1171 IF (l_debug = 1) THEN
1172 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);
1173 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1174 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
1175 END IF;
1176
1177 l_primary_reserved_quantity := 0;
1178 l_secondary_reserved_quantity :=0;
1179 END;
1180
1181 IF (l_debug = 1) THEN
1182 debug_print('l_primary_reserved_quantity = ' || l_primary_reserved_quantity);
1183 debug_print('l_secondary_reserved_quantity = ' || l_secondary_reserved_quantity);
1184 END IF;
1185
1186 --Bug 12978409 start Need t oconsider the lot uom conversion while calculating the total qty.
1187 FOR rsv_with_lots_rec IN rsv_with_lots LOOP
1188 EXIT WHEN rsv_with_lots%notfound;
1189
1190 OPEN check_if_lot_conv_exists(rsv_with_lots_rec.lot_number, rsv_with_lots_rec.inventory_item_id, rsv_with_lots_rec.organization_id);
1191 FETCH check_if_lot_conv_exists into lot_conv_factor_flag;
1192 CLOSE check_if_lot_conv_exists;
1193
1194 OPEN get_order_line_uom;
1195 FETCH get_order_line_uom into l_order_line_uom;
1196 CLOSE get_order_line_uom;
1197
1198 IF (l_debug = 1) THEN
1199 debug_print('inventory_item_id = ' || rsv_with_lots_rec.inventory_item_id);
1200 debug_print('lot_number = ' || rsv_with_lots_rec.lot_number);
1201 debug_print('organization_id = ' || rsv_with_lots_rec.organization_id);
1202 debug_print('primary_uom_code = ' || rsv_with_lots_rec.primary_uom_code);
1203 debug_print('reservation_uom_code= ' || rsv_with_lots_rec.reservation_uom_code);
1204 debug_print('order_line_uom = ' || l_order_line_uom);
1205 debug_print('lot_conv_factor_flag= ' || lot_conv_factor_flag);
1206 END IF;
1207
1208 IF lot_conv_factor_flag > 0 THEN
1209 IF (l_debug = 1) THEN
1210 debug_print('Lot conversion exists for this item');
1211 END IF;
1212
1213 IF rsv_with_lots_rec.primary_uom_code <> rsv_with_lots_rec.reservation_uom_code THEN
1214 IF (l_debug = 1) THEN
1215 debug_print('primary_uom_code and reservation_uom_code are different');
1216 END IF;
1217
1218 l_lot_rsv_quantity_rsv_uom := inv_convert.inv_um_convert(
1219 Item_id => rsv_with_lots_rec.inventory_item_id
1220 , Lot_number => rsv_with_lots_rec.lot_number
1221 , Organization_id => rsv_with_lots_rec.organization_id
1222 , Precision => null
1223 , From_quantity => nvl(rsv_with_lots_rec.primary_reservation_quantity, 0)
1224 , From_unit => rsv_with_lots_rec.primary_uom_code
1225 , To_unit => rsv_with_lots_rec.reservation_uom_code
1226 , from_name => NULL
1227 , to_name => NULL
1228 );
1229 IF (l_debug = 1) THEN
1230 debug_print('reservation qty with lots in reservation uom (honoring lot conversion) = '
1231 || l_lot_rsv_quantity_rsv_uom);
1232 END IF;
1233
1234 l_lot_primary_rsv_qty := inv_convert.inv_um_convert(
1235 Item_id => rsv_with_lots_rec.inventory_item_id
1236 , Organization_id => rsv_with_lots_rec.organization_id
1237 , Precision => null
1238 , From_quantity => l_lot_rsv_quantity_rsv_uom
1239 , From_unit => rsv_with_lots_rec.reservation_uom_code
1240 , To_unit => rsv_with_lots_rec.primary_uom_code
1241 , from_name => NULL
1242 , to_name => NULL
1243 );
1244
1245 l_lot_primary_rsv_qty_total := l_lot_primary_rsv_qty_total + l_lot_primary_rsv_qty ;
1246
1247 IF (l_debug = 1) THEN
1248 debug_print('reservation qty with lots in primary uom (honoring lot conversion)= '
1249 || l_lot_primary_rsv_qty);
1250 debug_print('total reservation qty with lots in primary uom (honoring lot conversion)= '
1251 || l_lot_primary_rsv_qty_total);
1252 END IF;
1253
1254 ELSIF rsv_with_lots_rec.primary_uom_code <> l_order_line_uom THEN
1255 IF (l_debug = 1) THEN
1256 debug_print('primary_uom_code and order_uom_code are different');
1257 END IF;
1258
1259 l_lot_rsv_qty_order_uom := inv_convert.inv_um_convert(
1260 Item_id => rsv_with_lots_rec.inventory_item_id
1261 , Lot_number => rsv_with_lots_rec.lot_number
1262 , Organization_id => rsv_with_lots_rec.organization_id
1263 , Precision => null
1264 , From_quantity => nvl(rsv_with_lots_rec.primary_reservation_quantity, 0)
1265 , From_unit => rsv_with_lots_rec.primary_uom_code
1266 , To_unit => l_order_line_uom
1267 , from_name => NULL
1268 , to_name => NULL
1269 );
1270 IF (l_debug = 1) THEN
1271 debug_print('reservation qty with lots in order uom (honoring lot conversion) = '
1272 || l_lot_rsv_qty_order_uom);
1273 END IF;
1274
1275 l_lot_primary_rsv_qty := inv_convert.inv_um_convert(
1276 Item_id => rsv_with_lots_rec.inventory_item_id
1277 , Organization_id => rsv_with_lots_rec.organization_id
1278 , Precision => null
1279 , From_quantity => l_lot_rsv_qty_order_uom
1280 , From_unit => l_order_line_uom
1281 , To_unit => rsv_with_lots_rec.primary_uom_code
1282 , from_name => NULL
1283 , to_name => NULL
1284 );
1285 l_lot_primary_rsv_qty_total := l_lot_primary_rsv_qty_total + l_lot_primary_rsv_qty ;
1286
1287 IF (l_debug = 1) THEN
1288 debug_print('reservation qty with lots in primary uom (honoring lot conversion)= '
1289 || l_lot_primary_rsv_qty);
1290 debug_print('total reservation qty with lots in primary uom (honoring lot conversion)= '
1291 || l_lot_primary_rsv_qty_total);
1292 END IF;
1293 ELSE
1294 l_lot_primary_rsv_qty_total := l_lot_primary_rsv_qty_total + nvl(rsv_with_lots_rec.primary_reservation_quantity, 0);
1295 IF (l_debug = 1) THEN
1296 debug_print('primary_uom_code and reservation_uom_code are same');
1297 debug_print('l_lot_primary_rsv_qty_total = ' || l_lot_primary_rsv_qty_total);
1298 END IF;
1299
1300 END IF;
1301
1302 ELSE
1303 l_lot_primary_rsv_qty_total := l_lot_primary_rsv_qty_total + nvl(rsv_with_lots_rec.primary_reservation_quantity, 0);
1304 IF (l_debug = 1) THEN
1305 debug_print('Lot conversion doesnt exist for this item');
1306 debug_print('l_lot_primary_rsv_qty_total = ' || l_lot_primary_rsv_qty_total);
1307 END IF;
1308 END IF;
1309 END LOOP;
1310
1311 IF (l_debug = 1) THEN
1312 debug_print('Total reservation qty with lots in primary uom = ' || l_lot_primary_rsv_qty_total);
1313 END IF;
1314
1315 l_primary_reserved_quantity := l_primary_reserved_quantity + l_lot_primary_rsv_qty_total;
1316
1317 IF (l_debug = 1) THEN
1318 debug_print('Total primary reservation qty for lots and non lots rsv records = ' || l_lot_primary_rsv_qty_total);
1319 END IF;
1320 --Bug 12978409 end
1321
1322 ELSIF (p_demand_source_line_detail is null) THEN
1323
1324 IF (l_debug = 1) THEN
1325 debug_print('p_demand_source_line_detail is null');
1326 END IF;
1327
1328 -- get all reservation quantity with the line detail = null
1329 BEGIN
1330 SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
1331 ,nvl(sum(secondary_reservation_quantity), 0)
1332 INTO l_primary_reserved_quantity, l_rsv_primary_uom_code ,l_secondary_reserved_quantity
1333 FROM mtl_reservations
1334 WHERE demand_source_type_id = p_demand_source_type_id
1335 AND demand_source_header_id = p_demand_source_header_id
1336 AND demand_source_line_id = p_demand_source_line_id
1337 AND demand_source_line_detail is null
1338 AND lot_number is null --lydal
1339 GROUP BY primary_uom_code;
1340 EXCEPTION
1341 WHEN no_data_found THEN
1342 IF (l_debug = 1) THEN
1343 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);
1344 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1345 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
1346 END IF;
1347
1348 l_primary_reserved_quantity := 0;
1349 l_secondary_reserved_quantity :=0;
1350 END;
1351
1352 IF (l_debug = 1) THEN
1353 debug_print('l_primary_reserved_quantity = ' || l_primary_reserved_quantity);
1354 debug_print('l_secondary_reserved_quantity = ' || l_secondary_reserved_quantity);
1355 END IF;
1356
1357 --Bug 12978409 start Need t oconsider the lot uom conversion while calculating the total qty.
1358 FOR rsv_with_lots_rec IN rsv_with_lots LOOP
1359 EXIT WHEN rsv_with_lots%notfound;
1360
1361 OPEN check_if_lot_conv_exists(rsv_with_lots_rec.lot_number, rsv_with_lots_rec.inventory_item_id, rsv_with_lots_rec.organization_id);
1362 FETCH check_if_lot_conv_exists into lot_conv_factor_flag;
1363 CLOSE check_if_lot_conv_exists;
1364
1365 OPEN get_order_line_uom;
1366 FETCH get_order_line_uom into l_order_line_uom;
1367 CLOSE get_order_line_uom;
1368
1369 IF (l_debug = 1) THEN
1370 debug_print('inventory_item_id = ' || rsv_with_lots_rec.inventory_item_id);
1371 debug_print('lot_number = ' || rsv_with_lots_rec.lot_number);
1372 debug_print('organization_id = ' || rsv_with_lots_rec.organization_id);
1373 debug_print('primary_uom_code = ' || rsv_with_lots_rec.primary_uom_code);
1374 debug_print('reservation_uom_code= ' || rsv_with_lots_rec.reservation_uom_code);
1375 debug_print('order_line_uom = ' || l_order_line_uom);
1376 debug_print('lot_conv_factor_flag= ' || lot_conv_factor_flag);
1377 END IF;
1378
1379 IF lot_conv_factor_flag > 0 THEN
1380 IF (l_debug = 1) THEN
1381 debug_print('Lot conversion exists for this item');
1382 END IF;
1383
1384 IF rsv_with_lots_rec.primary_uom_code <> rsv_with_lots_rec.reservation_uom_code THEN
1385 IF (l_debug = 1) THEN
1386 debug_print('primary_uom_code and reservation_uom_code are different');
1387 END IF;
1388
1389 l_lot_rsv_quantity_rsv_uom := inv_convert.inv_um_convert(
1390 Item_id => rsv_with_lots_rec.inventory_item_id
1391 , Lot_number => rsv_with_lots_rec.lot_number
1392 , Organization_id => rsv_with_lots_rec.organization_id
1393 , Precision => null
1394 , From_quantity => nvl(rsv_with_lots_rec.primary_reservation_quantity, 0)
1395 , From_unit => rsv_with_lots_rec.primary_uom_code
1396 , To_unit => rsv_with_lots_rec.reservation_uom_code
1397 , from_name => NULL
1398 , to_name => NULL
1399 );
1400 IF (l_debug = 1) THEN
1401 debug_print('reservation qty with lots in reservation uom (honoring lot conversion) = '
1402 || l_lot_rsv_quantity_rsv_uom);
1403 END IF;
1404
1405 l_lot_primary_rsv_qty := inv_convert.inv_um_convert(
1406 Item_id => rsv_with_lots_rec.inventory_item_id
1407 , Organization_id => rsv_with_lots_rec.organization_id
1408 , Precision => null
1409 , From_quantity => l_lot_rsv_quantity_rsv_uom
1410 , From_unit => rsv_with_lots_rec.reservation_uom_code
1411 , To_unit => rsv_with_lots_rec.primary_uom_code
1412 , from_name => NULL
1413 , to_name => NULL
1414 );
1415 l_lot_primary_rsv_qty_total := l_lot_primary_rsv_qty_total + l_lot_primary_rsv_qty ;
1416
1417 IF (l_debug = 1) THEN
1418 debug_print('reservation qty with lots in primary uom (honoring lot conversion)= '
1419 || l_lot_primary_rsv_qty);
1420 debug_print('total reservation qty with lots in primary uom (honoring lot conversion)= '
1421 || l_lot_primary_rsv_qty_total);
1422 END IF;
1423
1424 ELSIF rsv_with_lots_rec.primary_uom_code <> l_order_line_uom THEN
1425 IF (l_debug = 1) THEN
1426 debug_print('primary_uom_code and order_uom_code are different');
1427 END IF;
1428
1429 l_lot_rsv_qty_order_uom := inv_convert.inv_um_convert(
1430 Item_id => rsv_with_lots_rec.inventory_item_id
1431 , Lot_number => rsv_with_lots_rec.lot_number
1432 , Organization_id => rsv_with_lots_rec.organization_id
1433 , Precision => null
1434 , From_quantity => nvl(rsv_with_lots_rec.primary_reservation_quantity, 0)
1435 , From_unit => rsv_with_lots_rec.primary_uom_code
1436 , To_unit => l_order_line_uom
1437 , from_name => NULL
1438 , to_name => NULL
1439 );
1440 IF (l_debug = 1) THEN
1441 debug_print('reservation qty with lots in order uom (honoring lot conversion) = '
1442 || l_lot_rsv_qty_order_uom);
1443 END IF;
1444
1445 l_lot_primary_rsv_qty := inv_convert.inv_um_convert(
1446 Item_id => rsv_with_lots_rec.inventory_item_id
1447 , Organization_id => rsv_with_lots_rec.organization_id
1448 , Precision => null
1449 , From_quantity => l_lot_rsv_qty_order_uom
1450 , From_unit => l_order_line_uom
1451 , To_unit => rsv_with_lots_rec.primary_uom_code
1452 , from_name => NULL
1453 , to_name => NULL
1454 );
1455 l_lot_primary_rsv_qty_total := l_lot_primary_rsv_qty_total + l_lot_primary_rsv_qty ;
1456
1457 IF (l_debug = 1) THEN
1458 debug_print('reservation qty with lots in primary uom (honoring lot conversion)= '
1459 || l_lot_primary_rsv_qty);
1460 debug_print('total reservation qty with lots in primary uom (honoring lot conversion)= '
1461 || l_lot_primary_rsv_qty_total);
1462 END IF;
1463 ELSE
1464 l_lot_primary_rsv_qty_total := l_lot_primary_rsv_qty_total + nvl(rsv_with_lots_rec.primary_reservation_quantity, 0);
1465 IF (l_debug = 1) THEN
1466 debug_print('primary_uom_code and reservation_uom_code are same');
1467 debug_print('l_lot_primary_rsv_qty_total = ' || l_lot_primary_rsv_qty_total);
1468 END IF;
1469
1470 END IF;
1471
1472 ELSE
1473 l_lot_primary_rsv_qty_total := l_lot_primary_rsv_qty_total + nvl(rsv_with_lots_rec.primary_reservation_quantity, 0);
1474 IF (l_debug = 1) THEN
1475 debug_print('Lot conversion doesnt exist for this item');
1476 debug_print('l_lot_primary_rsv_qty_total = ' || l_lot_primary_rsv_qty_total);
1477 END IF;
1478 END IF;
1479 END LOOP;
1480
1481 IF (l_debug = 1) THEN
1482 debug_print('Total reservation qty with lots in primary uom = ' || l_lot_primary_rsv_qty_total);
1483 END IF;
1484
1485 l_primary_reserved_quantity := l_primary_reserved_quantity + l_lot_primary_rsv_qty_total;
1486
1487 IF (l_debug = 1) THEN
1488 debug_print('Total primary reservation qty for lots and non lots rsv records = ' || l_lot_primary_rsv_qty_total);
1489 END IF;
1490 --Bug 12978409 end
1491
1492 END IF; --p_demand_source_line_detail
1493
1494
1495 -- get total ordered quantity at the order line level
1496 -- ????? for available quantity, do we need to substract ordered quantity from shipped quantity
1497 BEGIN
1498 SELECT ordered_quantity , order_quantity_uom
1499 ,ordered_quantity2 , ordered_quantity_uom2
1500 INTO l_available_quantity, l_order_quantity_uom_code
1501 ,l_available_quantity2, l_order_quantity_uom2
1502 FROM oe_order_lines_all
1503 WHERE line_id = p_demand_source_line_id; --Bug14629017
1504 --AND nvl(project_id, -99) = nvl(p_project_id, -99)
1505 --AND nvl(task_id, -99) = nvl(p_task_id, -99);
1506 EXCEPTION
1507 WHEN no_data_found THEN
1508 IF (l_debug = 1) THEN
1509 debug_print('No order is found for line_id = ' || p_demand_source_line_id);
1510 debug_print('project_id = ' || p_project_id || ' and task_id = ' || p_task_id);
1511 END IF;
1512 END;
1513 --8983636 begin
1514 IF (p_organization_id IS NOT NULL) THEN
1515 IF (NOT INV_CACHE.set_org_rec(p_organization_id)) THEN
1516 IF (l_debug = 1) THEN
1517 debug_print('EXCEPTION while trying to set org parameters');
1518 END IF;
1519 RAISE fnd_api.g_exc_error;
1520 END IF;
1521 END IF;
1522 IF (l_debug = 1) THEN
1523 debug_print('wms enabled ? : '||NVL(INV_CACHE.org_rec.wms_enabled_flag,'N') );
1524 END IF;
1525 IF ( NVL(INV_CACHE.org_rec.wms_enabled_flag,'N') = 'Y' ) THEN
1526 BEGIN
1527 SELECT nvl((ordered_quantity * ship_tolerance_above/100),0) INTO l_over_shippable_qty
1528 FROM oe_order_lines_all
1529 WHERE line_id = p_demand_source_line_id
1530 AND nvl(project_id, -99) = nvl(p_project_id, -99)
1531 AND nvl(task_id, -99) = nvl(p_task_id, -99)
1532 AND NOT EXISTS (SELECT 1 FROM MTL_RESERVATIONS MR
1533 WHERE MR.demand_source_line_id = p_demand_source_line_id
1534 AND MR.demand_source_type_id = p_demand_source_type_id
1535 AND MR.demand_source_header_id = p_demand_source_header_id
1536 AND NVL (MR.staged_flag,'N') <> 'Y' ) ;
1537 IF (l_debug = 1) THEN
1538 debug_print('overshippable qty :'||l_over_shippable_qty);
1539 END IF;
1540 l_available_quantity := l_available_quantity + l_over_shippable_qty ;
1541 EXCEPTION
1542 WHEN no_data_found THEN
1543 IF (l_debug = 1) THEN
1544 debug_print('Querying overship tolerance ,No record found for line_id = ' || p_demand_source_line_id);
1545 debug_print('This may be because not all qty is staged for this line');
1546 END IF;
1547 END;
1548 END IF;
1549
1550 /*8983636-ends*/
1551
1552
1553 IF (l_rsv_primary_uom_code IS NULL) THEN
1554 l_rsv_primary_uom_code := p_primary_uom_code;
1555 IF (l_rsv_primary_uom_code IS NULL) THEN
1556 BEGIN
1557 SELECT primary_uom_code INTO l_rsv_primary_uom_code FROM
1558 mtl_system_items WHERE organization_id = p_organization_id
1559 AND inventory_item_id = p_item_id;
1560 EXCEPTION WHEN no_data_found THEN
1561 IF (l_debug = 1) THEN
1562 debug_print('Cannot find the primary unit of measure');
1563 END IF;
1564 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
1565 FND_MSG_PUB.ADD;
1566 RAISE fnd_api.g_exc_error;
1567 END;
1568 END IF;
1569
1570 END IF;
1571
1572 IF( l_rsv_secondary_uom_code is null) then
1573 l_rsv_secondary_uom_code:= l_order_quantity_uom2;
1574 END IF;
1575
1576 IF (l_order_quantity_uom_code <> l_rsv_primary_uom_code) THEN
1577 l_primary_available_qty := inv_convert.inv_um_convert
1578 (
1579 item_id => p_item_id
1580 , lot_number => null
1581 , organization_id => p_organization_id
1582 , precision => null
1583 , from_quantity => l_available_quantity
1584 , from_unit => l_order_quantity_uom_code
1585 , to_unit => l_rsv_primary_uom_code
1586 , from_name => null
1587 , to_name => null
1588 );
1589 ELSE
1590 l_primary_available_qty := l_available_quantity;
1591 END IF;
1592
1593 IF (l_debug = 1) THEN
1594 debug_print('l_available_quantity = ' || l_available_quantity);
1595 debug_print('l_primary_available_qty = ' || l_primary_available_qty);
1596 debug_print('l_available_quantity2 = ' || l_available_quantity2);
1597 END IF;
1598
1599 /* Start bug# 8807194 : Calculating the available to reserve quantity as ordered qty - reserved qty - wdd picked qty
1600 This is done for all the cases wherein wdd is pick released/shipped but there are no staged reservations.
1601 For example, staging transfer to a non reservable subinventory */
1602
1603 BEGIN
1604 SELECT nvl((Sum(wdd.picked_quantity)),0), wdd.requested_quantity_uom
1605 ,nvl((Sum(wdd.picked_quantity2)),0)
1606 INTO l_wdd_picked_qty, l_wdd_uom_code
1607 ,l_wdd_picked_qty2
1608 FROM wsh_delivery_details wdd
1609 WHERE wdd.source_line_id = p_demand_source_line_id
1610 AND wdd.released_status IN ('Y','C')
1611 AND NOT EXISTS
1612 (
1613 SELECT 1 FROM mtl_reservations mr
1614 WHERE mr.demand_source_line_id = wdd.source_line_id
1615 AND nvl(mr.staged_flag, 'N') = 'Y'
1616 AND mr.inventory_item_id = wdd.inventory_item_id
1617 AND mr.organization_id = wdd.organization_id
1618 AND nvl(mr.subinventory_code, '@@@') = nvl(wdd.subinventory, '@@@')
1619 AND nvl(mr.locator_id, -999) = nvl(wdd.locator_id, -999)
1620 AND nvl(mr.lot_number, '@@@') = nvl(wdd.lot_number, '@@@')
1621 )
1622 AND NOT EXISTS (SELECT 1 from mtl_parameters
1623 WHERE organization_id = wdd.organization_id
1624 AND NVL(wms_enabled_flag,'N') = 'Y') --Bug 9036307
1625 GROUP BY wdd.requested_quantity_uom ;
1626
1627 EXCEPTION
1628 WHEN no_data_found THEN
1629 l_wdd_picked_qty := 0;
1630 IF (l_debug = 1) THEN
1631 debug_print('No delivery detail found which is staged/shipped and doesnt have staged reservation associated with the line_id = '
1632 || p_demand_source_line_id);
1633 END IF;
1634 END;
1635
1636 IF (l_wdd_uom_code IS NOT NULL AND l_wdd_uom_code <> l_rsv_primary_uom_code) THEN
1637 l_primary_wdd_picked_qty := inv_convert.inv_um_convert
1638 (
1639 item_id => p_item_id
1640 , lot_number => null
1641 , organization_id => p_organization_id
1642 , precision => null
1643 , from_quantity => l_wdd_picked_qty
1644 , from_unit => l_wdd_uom_code
1645 , to_unit => l_rsv_primary_uom_code
1646 , from_name => null
1647 , to_name => null
1648 );
1649 ELSE
1650 l_primary_wdd_picked_qty := l_wdd_picked_qty;
1651 END IF;
1652
1653 IF (l_debug = 1) THEN
1654 debug_print('l_wdd_picked_qty = ' || l_wdd_picked_qty);
1655 debug_print('l_primary_wdd_picked_qty = ' || l_primary_wdd_picked_qty);
1656 debug_print('l_wdd_picked_qty2 = ' || l_wdd_picked_qty2);
1657 END IF;
1658
1659 /* End bug# 8807194 */
1660
1661
1662 IF (p_demand_source_line_detail is not NULL AND p_demand_source_line_detail <> fnd_api.g_miss_num) THEN
1663
1664 IF (l_debug = 1) THEN
1665 debug_print('p_demand_source_line_detail is not NULL AND p_demand_source_line_detail <> fnd_api.g_miss_num');
1666 END IF;
1667
1668 l_wdd_available_qty := nvl(l_wdd_primary_quantity,0) - nvl(l_wdd_primary_reserved_qty,0);
1669 l_order_available_qty := nvl(l_primary_available_qty,0) - nvl(l_primary_reserved_quantity,0);
1670
1671 l_wdd_available_qty2 := nvl(l_wdd_secondary_quantity,0) - nvl(l_wdd_secondary_reserved_qty,0);
1672 l_order_available_qty2 := nvl(l_available_quantity2,0) - nvl(l_secondary_reserved_quantity,0);
1673
1674 IF (l_debug = 1) THEN
1675 debug_print('l_wdd_available_qty = ' || l_wdd_available_qty);
1676 debug_print('l_order_available_qty = ' || l_order_available_qty);
1677 debug_print('l_wdd_available_qty2 = ' || l_wdd_available_qty2);
1678 debug_print('l_order_available_qty2 = ' || l_order_available_qty2);
1679 END IF;
1680
1681 IF (l_wdd_available_qty < l_order_available_qty) THEN
1682 l_qty_available_to_reserve := l_wdd_available_qty;
1683 ELSE
1684 l_qty_available_to_reserve := l_order_available_qty;
1685 END IF;
1686 --
1687 IF (l_wdd_available_qty2 < l_order_available_qty2) THEN
1688 l_qty_available_to_reserve2 := l_wdd_available_qty2;
1689 ELSE
1690 l_qty_available_to_reserve2 := l_order_available_qty2;
1691 END IF;
1692 ELSE
1693 --l_qty_available_to_reserve := nvl(l_primary_available_qty,0) - nvl(l_primary_reserved_quantity,0);
1694 /* Bug# 8807194: Commented the above calculation and rewrote it below. Reduced l_primary_wdd_picked_qty as well to
1695 get the l_qty_available_to_reserve */
1696 l_qty_available_to_reserve := nvl(l_primary_available_qty,0) - nvl(l_primary_reserved_quantity,0) - nvl(l_primary_wdd_picked_qty,0) ;
1697 l_qty_available_to_reserve2 := nvl(l_available_quantity2,0) - nvl(l_secondary_reserved_quantity,0) - nvl(l_wdd_secondary_reserved_qty,0) ;
1698 END IF;
1699
1700 IF (l_debug = 1) THEN
1701 debug_print('l_qty_available_to_reserve = ' || l_qty_available_to_reserve);
1702 debug_print('l_qty_available_to_reserve2 = ' || l_qty_available_to_reserve2);
1703 END IF;
1704
1705 END IF; -- end of if WIP demand source
1706
1707 IF (l_debug = 1) THEN
1708 debug_print('l_qty_available_to_reserve = ' || l_qty_available_to_reserve);
1709 debug_print('l_primary_available_qty = ' || l_primary_available_qty);
1710 debug_print('l_return_status = ' || l_return_status);
1711 debug_print('l_qty_available_to_reserve2 = ' || l_qty_available_to_reserve2);
1712 debug_print('l_available_quantity2 = ' || l_available_quantity2);
1713 END IF;
1714
1715 x_qty_available_to_reserve := l_qty_available_to_reserve;
1716 x_qty_available := nvl(l_primary_available_qty, 0);
1717
1718 x_qty_available_to_reserve2 := l_qty_available_to_reserve2;
1719 x_qty_available2 := nvl(l_available_quantity2, 0);
1720
1721 x_return_status := l_return_status;
1722 EXCEPTION
1723 WHEN fnd_api.g_exc_error THEN
1724 x_return_status := fnd_api.g_ret_sts_error;
1725 --
1726 WHEN fnd_api.g_exc_unexpected_error THEN
1727 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1728 --
1729 WHEN OTHERS THEN
1730 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1731 --
1732 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1733 THEN
1734 fnd_msg_pub.add_exc_msg
1735 ( g_pkg_name
1736 , 'available_demand_to_reserve'
1737 );
1738 END IF;
1739 --
1740
1741 END available_demand_to_reserve;
1742
1743 END inv_reservation_avail_pvt;