[Home] [Help]
PACKAGE BODY: APPS.INV_RESERVATION_PUB
Source
1 PACKAGE BODY inv_reservation_pub AS
2 /* $Header: INVRSVPB.pls 120.4 2007/12/17 13:24:27 ckrishna ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_Reservation_PUB';
5 g_version_printed BOOLEAN := FALSE;
6
7
8 PROCEDURE mydebug (p_message IN VARCHAR2,p_module_name IN VARCHAR2,p_level IN NUMBER)
9 IS
10 BEGIN
11 IF g_version_printed THEN
12 inv_log_util.TRACE ('$Header: INVRSVPB.pls 120.4 2007/12/17 13:24:27 ckrishna ship $',g_pkg_name||'.'||p_module_name, p_level);
13 END IF;
14 inv_log_util.TRACE (p_message,g_pkg_name||'.'||p_module_name, p_level);
15
16 END mydebug;
17
18
19 -- INVCONV
20 -- Change this version of create_reservation to invoke the new overloaded version
21 -- introduced for inventory convergence
22 -- ==============================================================================
23 PROCEDURE create_reservation
24 (
25 p_api_version_number IN NUMBER
26 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
27 , x_return_status OUT NOCOPY VARCHAR2
28 , x_msg_count OUT NOCOPY NUMBER
29 , x_msg_data OUT NOCOPY VARCHAR2
30 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
31 , p_serial_number IN inv_reservation_global.serial_number_tbl_type
32 , x_serial_number OUT NOCOPY inv_reservation_global.serial_number_tbl_type
33 , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
34 , p_force_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
35 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
36 , p_over_reservation_flag IN NUMBER DEFAULT 0
37 , x_quantity_reserved OUT NOCOPY NUMBER
38 , x_reservation_id OUT NOCOPY NUMBER
39 , p_partial_rsv_exists IN BOOLEAN DEFAULT FALSE
40 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
41
42 ) IS
43
44 l_api_version_number CONSTANT NUMBER := 1.0;
45 l_api_name CONSTANT VARCHAR2(30) := 'Create_Reservation';
46 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
47 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
48
49 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
50 l_progress NUMBER;
51 l_secondary_quantity_reserved NUMBER;
52
53 BEGIN
54 -- Standard call to check for call compatibility
55 IF NOT fnd_api.compatible_api_call(l_api_version_number
56 , p_api_version_number
57 , l_api_name
58 , G_PKG_NAME
59 ) THEN
60 RAISE fnd_api.g_exc_unexpected_error;
61 END IF;
62
63 -- Initialize message list.
64 IF fnd_api.to_boolean(p_init_msg_lst) THEN
65 fnd_msg_pub.initialize;
66 END IF;
67
68 -- INVCONV BEGIN 4066306
69 l_rsv_rec := p_rsv_rec;
70 -- Initialize process attributes
71 l_rsv_rec.secondary_uom_code := NULL;
72 l_rsv_rec.secondary_uom_id := NULL;
73 l_rsv_rec.secondary_reservation_quantity := NULL;
74 l_rsv_rec.secondary_detailed_quantity := NULL;
75 -- INVCONV END 4066306
76
77 l_progress := 10;
78 IF l_debug=1 THEN
79 mydebug('Calling the overloaded procedure create_reservation',l_api_name,9);
80 END IF;
81
82 -- BUG 4360466 - ensure p_partial_rsv_exists is passed
83 inv_reservation_pub.create_reservation
84 (
85 p_api_version_number => 1.0
86 , p_init_msg_lst => p_init_msg_lst
87 , x_return_status => l_return_status
88 , x_msg_count => x_msg_count
89 , x_msg_data => x_msg_data
90 , p_rsv_rec => l_rsv_rec
91 , p_serial_number => p_serial_number
92 , x_serial_number => x_serial_number
93 , p_partial_reservation_flag => p_partial_reservation_flag
94 , p_force_reservation_flag => p_force_reservation_flag
95 , p_validation_flag => p_validation_flag
96 , p_over_reservation_flag => p_over_reservation_flag
97 , x_quantity_reserved => x_quantity_reserved
98 , x_secondary_quantity_reserved => l_secondary_quantity_reserved
99 , x_reservation_id => x_reservation_id
100 , p_partial_rsv_exists => p_partial_rsv_exists
101 , p_substitute_flag => p_substitute_flag /* Bug 6044651 */
102 );
103
104 IF (l_debug=1) THEN
105 mydebug ('Return Status after creating reservations '||l_return_status,l_api_name,1);
106 END IF;
107
108
109 IF l_return_status = fnd_api.g_ret_sts_error THEN
110 IF l_debug=1 THEN
111 mydebug('Raising expected error'||l_return_status,l_api_name,9);
112 END IF;
113 RAISE fnd_api.g_exc_error;
114
115 END IF ;
116
117 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
118 IF l_debug=1 THEN
119 mydebug('Raising unexpected error'||l_return_status,l_api_name,9);
120 END IF;
121 RAISE fnd_api.g_exc_unexpected_error;
122 END IF;
123
124 x_return_status := l_return_status;
125
126 IF (l_debug=1) THEN
127 mydebug ('Return Status '||x_return_status,l_api_name,9);
128 mydebug ('Reservation Quantity '||x_quantity_reserved,l_api_name,9);
129 mydebug ('Secondary Reservation Quantity '||l_secondary_quantity_reserved,l_api_name,9);
130 mydebug('Reservation Id '||x_reservation_id,l_api_name,9);
131 END IF;
132
133
134 EXCEPTION
135
136 WHEN fnd_api.g_exc_error THEN
137 x_return_status := fnd_api.g_ret_sts_error;
138
139 -- Get message count and data
140 fnd_msg_pub.count_and_get
141 ( p_count => x_msg_count
142 , p_data => x_msg_data
143 );
144 IF l_debug=1 THEN
145 mydebug('Error Obtained at'||l_progress,l_api_name,9);
146 END IF;
147
148 WHEN fnd_api.g_exc_unexpected_error THEN
149 x_return_status := fnd_api.g_ret_sts_unexp_error ;
150
151 -- Get message count and data
152 fnd_msg_pub.count_and_get
153 ( p_count => x_msg_count
154 , p_data => x_msg_data
155 );
156
157 IF l_debug=1 THEN
158 mydebug('Error Obtained at'||l_progress,l_api_name,9);
159 END IF;
160
161
162 WHEN OTHERS THEN
163 x_return_status := fnd_api.g_ret_sts_unexp_error ;
164
165 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
166 THEN
167 fnd_msg_pub.add_exc_msg
168 ( g_pkg_name
169 , l_api_name
170 );
171 END IF;
172
173 -- Get message count and data
174 fnd_msg_pub.count_and_get
175 ( p_count => x_msg_count
176 , p_data => x_msg_data
177 );
178
179 IF l_debug=1 THEN
180 mydebug('Error Obtained at'||l_progress,l_api_name,9);
181 END IF;
182
183
184 END create_reservation;
185
186
187 -- INVCONV BEGIN
188 -- OVERLOADED Version of create_reservation introduced for inventory convergence
189 -- Incorporate secondary_quantity_reserved
190 -- Strip out PROCESS_BRANCH logic
191 -- =======================================
192 PROCEDURE create_reservation
193 (
194 p_api_version_number IN NUMBER
195 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
196 , x_return_status OUT NOCOPY VARCHAR2
197 , x_msg_count OUT NOCOPY NUMBER
198 , x_msg_data OUT NOCOPY VARCHAR2
199 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
200 , p_serial_number IN inv_reservation_global.serial_number_tbl_type
201 , x_serial_number OUT NOCOPY inv_reservation_global.serial_number_tbl_type
202 , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
203 , p_force_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
204 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
205 , p_over_reservation_flag IN NUMBER DEFAULT 0
206 , x_quantity_reserved OUT NOCOPY NUMBER
207 , x_secondary_quantity_reserved OUT NOCOPY NUMBER
208 , x_reservation_id OUT NOCOPY NUMBER
209 , p_partial_rsv_exists IN BOOLEAN DEFAULT FALSE
210 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
211
212
213 ) IS
214
215 l_api_version_number CONSTANT NUMBER := 1.0;
216 l_api_name CONSTANT VARCHAR2(30) := 'Create_Reservation';
217 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
218 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
219
220 /**** {{ R12 Enhanced reservations code changes }}****/
221 -- l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
222 -- l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type := p_rsv_rec;
223 -- l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
224 -- l_mtl_reservation_tbl_count NUMBER;
225 l_error_code NUMBER;
226 -- l_rsv_updated BOOLEAN :=FALSE;
227 -- l_primary_uom_code VARCHAR2(3);
228 l_progress NUMBER;
229 -- l_quantity_reserved NUMBER;
230 -- l_secondary_quantity_reserved NUMBER; -- INVCONV
231 /*** End R12 ***/
232
233 BEGIN
234 -- Standard call to check for call compatibility
235 IF NOT fnd_api.compatible_api_call(l_api_version_number
236 , p_api_version_number
237 , l_api_name
238 , G_PKG_NAME
239 ) THEN
240 RAISE fnd_api.g_exc_unexpected_error;
241 END IF;
242
243 -- Initialize message list.
244 IF fnd_api.to_boolean(p_init_msg_lst) THEN
245 fnd_msg_pub.initialize;
246 END IF;
247
248 l_progress := 10;
249 l_progress := 40;
250
251 /**** {{ R12 Enhanced reservations code changes }}****/
252 /****
253 --Do not need this code as this is being moved to the private API
254 -- Commneting out the code
255
256 IF p_partial_rsv_exists THEN
257 --Since create reservation is called by OM even when they updated the existing reservation
258 --we need to query existing reservations and see if the existing reservation can be updated.
259 IF l_debug=1 THEN
260
261 mydebug('Partial reservation flag passed as true,need to check existing reservations',l_api_name,1);
262 mydebug('The value of partial reservations flag is ='||p_partial_reservation_flag,l_api_name,1);
263
264 END IF;
265
266 l_progress :=50;
267
268 inv_reservation_pvt.convert_quantity
269 (x_return_status => l_return_status,
270 px_rsv_rec => l_from_rsv_rec
271 );
272
273 IF l_debug=1 THEN
274 mydebug('REturn Status from convert quantity'||l_return_status,l_api_name,9);
275 END IF;
276
277 IF l_return_status = fnd_api.g_ret_sts_error THEN
278
279 IF l_debug=1 THEN
280 mydebug('Raising expected error'||l_return_status,l_api_name,9);
281 END IF;
282 RAISE fnd_api.g_exc_error;
283
284 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
285
286 IF l_debug=1 THEN
287 mydebug('Rasing Unexpected error'||l_return_status,l_api_name,9);
288 END IF;
289 RAISE fnd_api.g_exc_unexpected_error;
290
291 END IF;
292
293 l_progress :=60;
294
295 IF l_debug=1 THEN
296 mydebug('Calling query reservation to query existing reservations'||l_return_status,l_api_name,9);
297 END IF;
298
299 l_progress := 70;
300
301 inv_reservation_pvt.query_reservation
302 (p_api_version_number => 1.0,
303 p_init_msg_lst => fnd_api.g_false,
304 x_return_status => l_return_status,
305 x_msg_count => x_msg_count,
306 x_msg_data => x_msg_data,
307 p_query_input => l_from_rsv_rec,
308 p_lock_records => fnd_api.g_true,
309 x_mtl_reservation_tbl => l_mtl_reservation_tbl,
310 x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count,
311 x_error_code => l_error_code
312 );
313
314
315 IF l_debug=1 THEN
316 mydebug ('Return Status after querying reservations '||l_return_status,l_api_name,1);
317 END IF;
318
319 l_progress := 80;
320
321 IF l_return_status = fnd_api.g_ret_sts_error THEN
322
323 IF l_debug=1 THEN
324 mydebug('Raising expected error'||l_return_status,l_api_name,9);
325 END IF;
326 RAISE fnd_api.g_exc_error;
327
328 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
329
330 IF l_debug=1 THEN
331 mydebug('Rasing Unexpected error'||l_return_status,l_api_name,9);
332 END IF;
333 RAISE fnd_api.g_exc_unexpected_error;
334
335 END IF;
336
337 IF (l_debug=1) THEN
338
339 mydebug ('x_mtl_reservation_tbl_count='|| l_mtl_reservation_tbl_count,l_api_name,4);
340
341 END IF;
342
343 IF l_debug=1 THEN
344
345 inv_reservation_pvt.print_rsv_rec (p_rsv_rec);
346 END IF;
347
348 l_progress := 90;
349
350 FOR i IN 1..l_mtl_reservation_tbl_count LOOP
351 inv_reservation_pvt.print_rsv_rec (l_mtl_reservation_tbl(i));
352
353 --If the queried reservation record is staged or has a lot number stamped or is
354 -- revision controlled or has an LPN Id stamped or has a different SubInventory
355 l_progress := 100;
356
357 IF ((l_mtl_reservation_tbl(i).staged_flag='Y')
358 OR (nvl(l_mtl_reservation_tbl(i).lot_number,'@@@')<>nvl(p_rsv_rec.lot_number,'@@@') AND p_rsv_rec.lot_number<>fnd_api.g_miss_char)
359 OR (nvl(l_mtl_reservation_tbl(i).revision,'@@@')<>nvl(p_rsv_rec.revision,'@@@')AND p_rsv_rec.revision <>fnd_api.g_miss_char)
360 OR (nvl(l_mtl_reservation_tbl(i).lpn_id,-1)<>nvl(p_rsv_rec.lpn_id,-1)AND p_rsv_rec.lpn_id <> fnd_api.g_miss_num)
361 OR (nvl(l_mtl_reservation_tbl(i).subinventory_code,'@@@')<>nvl(p_rsv_rec.subinventory_code,'@@@')AND p_rsv_rec.subinventory_code <>fnd_api.g_miss_char)) THEN
362
363 IF (l_debug=1) THEN
364 mydebug('Skipping reservation record',l_api_name,9);
365 END IF;
366
367 l_progress := 110;
368
369 GOTO next_record;
370 ELSE
371
372 IF (l_debug=1) THEN
373
374 mydebug('Need to update reservation record',l_api_name,9);
375 END IF;
376
377 IF l_debug=1 THEN
378
379 mydebug('Reservation record that needs to be updated',l_api_name,9);
380 inv_reservation_pvt.print_rsv_rec (l_mtl_reservation_tbl(i));
381
382 END IF;
383
384 l_progress := 120;
385
386 l_to_rsv_rec.primary_reservation_quantity := l_from_rsv_rec.primary_reservation_quantity
387 + l_mtl_reservation_tbl(i).primary_reservation_quantity;
388 -- INVCONV BEGIN
389 -- Look at the reservation table row to determine if this is a dual control item.
390 -- If it is dual control and a secondary_reservation_quantity has been supplied,
391 -- then calculate the to_resv_rec.secondary_reservation_quantity.
392 -- Otherwise leave it empty to be computed as necessary by the private level API
393 IF l_mtl_reservation_tbl(i).secondary_reservation_quantity is not NULL and
394 l_to_rsv_rec.secondary_reservation_quantity is not NULL THEN
395 l_to_rsv_rec.secondary_reservation_quantity := l_from_rsv_rec.secondary_reservation_quantity
396 + l_mtl_reservation_tbl(i).secondary_reservation_quantity;
397
398 END IF;
399 -- INVCONV END
400 l_progress := 130;
401
402 IF l_from_rsv_rec.reservation_uom_code = l_mtl_reservation_tbl(i).reservation_uom_code THEN
403
404 l_to_rsv_rec.reservation_quantity := l_from_rsv_rec.reservation_quantity + l_mtl_reservation_tbl(i).reservation_quantity;
405
406 ELSE
407
408 l_to_rsv_rec.reservation_quantity := NULL;
409
410 END IF;
411
412 l_progress := 140;
413
414 IF (l_debug=1) THEN
415
416 mydebug('Calling update reservations to update reservation record',l_api_name,9);
417
418 END IF;
419
420 inv_reservation_pub.update_reservation
421 (p_api_version_number => 1.0,
422 p_init_msg_lst => fnd_api.g_false,
423 x_return_status => l_return_status,
424 x_msg_count => x_msg_count,
425 x_msg_data => x_msg_data,
426 x_quantity_reserved => l_quantity_reserved,
427 x_secondary_quantity_reserved => l_secondary_quantity_reserved, -- INVCONV
428 p_original_rsv_rec => l_mtl_reservation_tbl(i),
429 p_to_rsv_rec => l_to_rsv_rec,
430 p_original_serial_number => p_serial_number,
431 p_to_serial_number => p_serial_number,
432 p_validation_flag => fnd_api.g_true,
433 p_partial_reservation_flag => p_partial_reservation_flag,
434 p_check_availability => fnd_api.g_true
435 );
436
437 l_progress := 150;
438
439 IF (l_debug=1) THEN
440 mydebug ('Return Status after updating reservations '||l_return_status,l_api_name,1);
441 END IF;
442
443 IF l_return_status = fnd_api.g_ret_sts_error THEN
444
445 IF l_debug=1 THEN
446 mydebug('Raising expected error'||l_return_status,l_api_name,9);
447 END IF;
448
449 RAISE fnd_api.g_exc_error;
450
451 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
452
453 IF l_debug=1 THEN
454 mydebug('Raising Unexpected error'||l_return_status,l_api_name,9);
455 END IF;
456
457 RAISE fnd_api.g_exc_unexpected_error;
458 END IF;
459
460 l_progress := 160;
461
462 l_quantity_reserved:=l_quantity_reserved - l_mtl_reservation_tbl(i).primary_reservation_quantity;
463
464 x_quantity_reserved := l_quantity_reserved;
465 x_reservation_id := l_mtl_reservation_tbl(i).reservation_id;
466
467 l_rsv_updated := TRUE;
468 x_return_status := l_return_status;
469 EXIT;
470
471 END IF;
472
473 <<next_record>>
474 NULL;
475 END LOOP;
476
477 END IF;
478
479 -- End. Do not need this as we are moving this to the private package.
480
481 IF NOT l_rsv_updated THEN
482
483 --End. Commenting the code as this is moved to the private package
484 ********************/
485 /*** End R12 ***/
486
487 l_progress := 180;
488 IF l_debug=1 THEN
489 mydebug('Calling create Reservations to create reservations',l_api_name,9);
490 END IF;
491
492 -- BUG 5244157 - Ensure parameters passed accurately from public to private layer
493 inv_reservation_pvt.create_reservation
494 (
495 p_api_version_number => 1.0
496 , p_init_msg_lst => fnd_api.g_false
497 , x_return_status => l_return_status
498 , x_msg_count => x_msg_count
499 , x_msg_data => x_msg_data
500 , p_rsv_rec => p_rsv_rec
501 , p_serial_number => p_serial_number
502 , x_serial_number => x_serial_number
503 , p_partial_reservation_flag => p_partial_reservation_flag
504 , p_force_reservation_flag => p_force_reservation_flag -- 5244157
505 , p_validation_flag => fnd_api.g_true
506 , p_over_reservation_flag => p_over_reservation_flag
507 , x_quantity_reserved => x_quantity_reserved
508 , x_secondary_quantity_reserved => x_secondary_quantity_reserved -- INVCONV
509 , x_reservation_id => x_reservation_id
510 , p_substitute_flag => p_substitute_flag /* Bug 6044651 */
511 );
512
513 IF (l_debug=1) THEN
514 mydebug ('Return Status after creating reservations '||l_return_status,l_api_name,1);
515 END IF;
516
517
518 IF l_return_status = fnd_api.g_ret_sts_error THEN
519 IF l_debug=1 THEN
520 mydebug('Raising expected error'||l_return_status,l_api_name,9);
521 END IF;
522 RAISE fnd_api.g_exc_error;
523
524 END IF ;
525
526 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
527
528 IF l_debug=1 THEN
529 mydebug('Raising unexpected error'||l_return_status,l_api_name,9);
530 END IF;
531 RAISE fnd_api.g_exc_unexpected_error;
532 END IF;
533
534 x_return_status := l_return_status;
535
536 /**** {{ R12 Enhanced reservations code changes }}****/
537 --END IF; -- Commented out for R12
538 /*** End R12 ***/
539
540 IF (l_debug=1) THEN
541 mydebug ('Return Status '||x_return_status,l_api_name,9);
542 mydebug ('Reservation Quantity '||x_quantity_reserved,l_api_name,9);
543 mydebug ('secondary Reservation Quantity '||x_secondary_quantity_reserved,l_api_name,9);
544 mydebug('Reservation Id '||x_reservation_id,l_api_name,9);
545 END IF;
546
547
548 EXCEPTION
549
550 WHEN fnd_api.g_exc_error THEN
551 x_return_status := fnd_api.g_ret_sts_error;
552
553 -- Get message count and data
554 fnd_msg_pub.count_and_get
555 ( p_count => x_msg_count
556 , p_data => x_msg_data
557 );
558 IF l_debug=1 THEN
559 mydebug('Error Obtained at'||l_progress,l_api_name,9);
560 END IF;
561
562 WHEN fnd_api.g_exc_unexpected_error THEN
563 x_return_status := fnd_api.g_ret_sts_unexp_error ;
564
565 -- Get message count and data
566 fnd_msg_pub.count_and_get
567 ( p_count => x_msg_count
568 , p_data => x_msg_data
569 );
570
571 IF l_debug=1 THEN
572 mydebug('Error Obtained at'||l_progress,l_api_name,9);
573 END IF;
574
575
576 WHEN OTHERS THEN
577 x_return_status := fnd_api.g_ret_sts_unexp_error ;
578
579 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
580 THEN
581 fnd_msg_pub.add_exc_msg
582 ( g_pkg_name
583 , l_api_name
584 );
585 END IF;
586
587 -- Get message count and data
588 fnd_msg_pub.count_and_get
589 ( p_count => x_msg_count
590 , p_data => x_msg_data
591 );
592
593 IF l_debug=1 THEN
594 mydebug('Error Obtained at'||l_progress,l_api_name,9);
595 END IF;
596
597
598 END create_reservation;
599 -- INVCONV END
600 -- ============
601
602 -- INVCONV BEGIN
603 -- Add parameter x_secondary_quantity_reserved
604 -- Strip out process inventory forking logic
605 PROCEDURE update_reservation
606 (
607 p_api_version_number IN NUMBER
608 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
609 , x_return_status OUT NOCOPY VARCHAR2
610 , x_msg_count OUT NOCOPY NUMBER
611 , x_msg_data OUT NOCOPY VARCHAR2
612 , x_quantity_reserved OUT NOCOPY NUMBER
613 , x_secondary_quantity_reserved OUT NOCOPY NUMBER
614 , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
615 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
616 , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
617 , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
618 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
619 , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
620 , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
621 , p_over_reservation_flag IN NUMBER DEFAULT 0
622 ) IS
623
624
625 l_api_version_number CONSTANT NUMBER := 1.0;
626 l_api_name CONSTANT VARCHAR2(30) := 'Update_Reservation';
627 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
628 l_quantity_reserved NUMBER;
629 l_secondary_quantity_reserved NUMBER;
630 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
631
632 BEGIN
633 -- Standard call to check for call compatibility
634 IF NOT fnd_api.compatible_api_call(l_api_version_number
635 , p_api_version_number
636 , l_api_name
637 , G_PKG_NAME
638 ) THEN
639 RAISE fnd_api.g_exc_unexpected_error;
640 END IF;
641
642 -- Initialize message list.
643 IF fnd_api.to_boolean(p_init_msg_lst) THEN
644 fnd_msg_pub.initialize;
645 END IF;
646
647 -- INVCONV BEGIN
648 -- Strip out all inventory branching logic
649 -- Start Process Branching Logic ----
650 -- End Process Branching Logic ----
651 -- INVCONV END
652
653 -- bug 1611697 - Performance
654 -- Allow validation_flag to be false if false is passed to this api.
655 -- Previously, always called pvt api with true.
656
657 IF l_debug=1 THEN
658 mydebug('The value of the p_partial_reservation_flag is :'||p_partial_reservation_flag,l_api_name,9);
659 mydebug('The value of the p_check_availability is :' ||p_check_availability,l_api_name,9);
660
661 END IF;
662 inv_reservation_pvt.update_reservation
663 (
664 p_api_version_number => 1.0
665 , p_init_msg_lst => fnd_api.g_false
666 , x_return_status => l_return_status
667 , x_msg_count => x_msg_count
668 , x_msg_data => x_msg_data
669 , x_quantity_reserved => l_quantity_reserved
670 , x_secondary_quantity_reserved => l_secondary_quantity_reserved
671 , p_original_rsv_rec => p_original_rsv_rec
672 , p_to_rsv_rec => p_to_rsv_rec
673 , p_original_serial_number => p_original_serial_number
674 , p_to_serial_number => p_to_serial_number
675 , p_validation_flag => p_validation_flag
676 , p_partial_reservation_flag => p_partial_reservation_flag
677 , p_check_availability => p_check_availability
678 , p_over_reservation_flag => p_over_reservation_flag
679 );
680
681 IF l_return_status = fnd_api.g_ret_sts_error THEN
682 RAISE fnd_api.g_exc_error;
683 END IF ;
684
685 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
686 RAISE fnd_api.g_exc_unexpected_error;
687 END IF;
688
689 x_return_status := l_return_status;
690 x_quantity_reserved := l_quantity_reserved;
691 x_secondary_quantity_reserved := l_secondary_quantity_reserved;
692
693 EXCEPTION
694
695 WHEN fnd_api.g_exc_error THEN
696 x_return_status := fnd_api.g_ret_sts_error;
697
698 -- Get message count and data
699 fnd_msg_pub.count_and_get
700 ( p_count => x_msg_count
701 , p_data => x_msg_data
702 );
703
704 WHEN fnd_api.g_exc_unexpected_error THEN
705 x_return_status := fnd_api.g_ret_sts_unexp_error ;
706
707 -- Get message count and data
708 fnd_msg_pub.count_and_get
709 ( p_count => x_msg_count
710 , p_data => x_msg_data
711 );
712
713 WHEN OTHERS THEN
714 x_return_status := fnd_api.g_ret_sts_unexp_error ;
715
716 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
717 THEN
718 fnd_msg_pub.add_exc_msg
719 ( g_pkg_name
720 , l_api_name
721 );
722 END IF;
723
724 -- Get message count and data
725 fnd_msg_pub.count_and_get
726 ( p_count => x_msg_count
727 , p_data => x_msg_data
728 );
729
730 END update_reservation;
731
732
733 -- INVCONV
734 -- Update call to private layer to incorporate secondary_quantity_reserved
735 PROCEDURE update_reservation
736 (
737 p_api_version_number IN NUMBER
738 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
739 , x_return_status OUT NOCOPY VARCHAR2
740 , x_msg_count OUT NOCOPY NUMBER
741 , x_msg_data OUT NOCOPY VARCHAR2
742 , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
743 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
744 , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
745 , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
746 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
747 , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
748 , p_over_reservation_flag IN NUMBER DEFAULT 0
749 ) IS
750
751
752 l_api_version_number CONSTANT NUMBER := 1.0;
753 l_api_name CONSTANT VARCHAR2(30) := 'Update_Reservation';
754 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
755 l_quantity_reserved NUMBER;
756 l_secondary_quantity_reserved NUMBER; -- INVCONV
757 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
758
759 BEGIN
760 -- Standard call to check for call compatibility
761 IF NOT fnd_api.compatible_api_call(l_api_version_number
762 , p_api_version_number
763 , l_api_name
764 , G_PKG_NAME
765 ) THEN
766 RAISE fnd_api.g_exc_unexpected_error;
767 END IF;
768
769 -- Initialize message list.
770 IF fnd_api.to_boolean(p_init_msg_lst) THEN
771 fnd_msg_pub.initialize;
772 END IF;
773
774 IF l_debug=1 THEN
775 mydebug('Calling the overloaded procedure update_reservation',l_api_name,9);
776 END IF;
777
778
779 inv_reservation_pub.update_reservation
780 (p_api_version_number => 1.0,
781 p_init_msg_lst => fnd_api.g_false,
782 x_return_status => l_return_status,
783 x_msg_count => x_msg_count,
784 x_msg_data => x_msg_data,
785 x_quantity_reserved => l_quantity_reserved,
786 x_secondary_quantity_reserved => l_secondary_quantity_reserved,
787 p_original_rsv_rec => p_original_rsv_rec,
788 p_to_rsv_rec => p_to_rsv_rec,
789 p_original_serial_number => p_original_serial_number ,
790 p_to_serial_number => p_to_serial_number,
791 p_validation_flag => p_validation_flag,
792 p_partial_reservation_flag => fnd_api.g_false,
793 p_check_availability => p_check_availability,
794 p_over_reservation_flag => p_over_reservation_flag
795 );
796
797
798 IF (l_debug=1) THEN
799 mydebug ('Return Status after updating reservations '||l_return_status,l_api_name,1);
800 END IF;
801
802 IF l_return_status = fnd_api.g_ret_sts_error THEN
803
804 IF l_debug=1 THEN
805 mydebug('Raising expected error'||l_return_status,l_api_name,9);
806 END IF;
807
808 RAISE fnd_api.g_exc_error;
809
810 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
811
812 IF l_debug=1 THEN
813 mydebug('Raising Unexpected error'||l_return_status,l_api_name,9);
814 END IF;
815
816 RAISE fnd_api.g_exc_unexpected_error;
817 END IF;
818
819
820 x_return_status := l_return_status;
821
822 EXCEPTION
823
824 WHEN fnd_api.g_exc_error THEN
825 x_return_status := fnd_api.g_ret_sts_error;
826 -- Get message count and data
827 fnd_msg_pub.count_and_get
828 ( p_count => x_msg_count
829 , p_data => x_msg_data
830 );
831
832 WHEN fnd_api.g_exc_unexpected_error THEN
833 x_return_status := fnd_api.g_ret_sts_unexp_error ;
834
835 -- Get message count and data
836 fnd_msg_pub.count_and_get
837 ( p_count => x_msg_count
838 , p_data => x_msg_data
839 );
840
841 WHEN OTHERS THEN
842 x_return_status := fnd_api.g_ret_sts_unexp_error ;
843
844 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
845 THEN
846 fnd_msg_pub.add_exc_msg
847 ( g_pkg_name
848 , l_api_name
849 );
850 END IF;
851
852 -- Get message count and data
853 fnd_msg_pub.count_and_get
854 ( p_count => x_msg_count
855 , p_data => x_msg_data
856 );
857
858 END update_reservation;
859
860
861 -- INVCONV
862 -- Invoke OVERLOAD version of relieve_reservation
863 PROCEDURE relieve_reservation
864 (
865 p_api_version_number IN NUMBER
866 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
867 , x_return_status OUT NOCOPY VARCHAR2
868 , x_msg_count OUT NOCOPY NUMBER
869 , x_msg_data OUT NOCOPY VARCHAR2
870 , p_rsv_rec
871 IN inv_reservation_global.mtl_reservation_rec_type
872 , p_primary_relieved_quantity IN NUMBER
873 , p_relieve_all IN VARCHAR2 DEFAULT fnd_api.g_true
874 , p_original_serial_number
875 IN inv_reservation_global.serial_number_tbl_type
876 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
877 , x_primary_relieved_quantity OUT NOCOPY NUMBER
878 , x_primary_remain_quantity OUT NOCOPY NUMBER
879 ) IS
880 l_api_version_number CONSTANT NUMBER := 1.0;
881 l_api_name CONSTANT VARCHAR2(30) := 'Relieve_Reservation';
882 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
883 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0); -- INVCONV
884 l_secondary_relieved_quantity NUMBER; -- INVCONV
885 l_secondary_remain_quantity NUMBER; -- INVCONV
886 BEGIN
887 -- Standard call to check for call compatibility
888 IF NOT fnd_api.compatible_api_call(l_api_version_number
889 , p_api_version_number
890 , l_api_name
891 , G_PKG_NAME
892 ) THEN
893 RAISE fnd_api.g_exc_unexpected_error;
894 END IF;
895
896 -- Initialize message list.
897 IF fnd_api.to_boolean(p_init_msg_lst) THEN
898 fnd_msg_pub.initialize;
899 END IF;
900
901 IF l_debug=1 THEN
902 mydebug('Calling the overloaded procedure relieve_reservation',l_api_name,9);
903 END IF;
904
905 inv_reservation_pub.relieve_reservation
906 (
907 p_api_version_number => 1.0
908 , p_init_msg_lst => p_init_msg_lst
909 , x_return_status => l_return_status
910 , x_msg_count => x_msg_count
911 , x_msg_data => x_msg_data
912 , p_rsv_rec => p_rsv_rec
913 , p_primary_relieved_quantity => p_primary_relieved_quantity
914 , p_secondary_relieved_quantity => l_secondary_relieved_quantity
915 , p_relieve_all => p_relieve_all
916 , p_original_serial_number => p_original_serial_number
917 , p_validation_flag => p_validation_flag
918 , x_primary_relieved_quantity => x_primary_relieved_quantity
919 , x_secondary_relieved_quantity => l_secondary_relieved_quantity
920 , x_primary_remain_quantity => x_primary_remain_quantity
921 , x_secondary_remain_quantity => l_secondary_remain_quantity
922 );
923
924 IF l_return_status = fnd_api.g_ret_sts_error THEN
925 RAISE fnd_api.g_exc_error;
926 END IF ;
927
928 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
929 RAISE fnd_api.g_exc_unexpected_error;
930 END IF;
931
932 x_return_status := l_return_status;
933
934 EXCEPTION
935
936 WHEN fnd_api.g_exc_error THEN
937 x_return_status := fnd_api.g_ret_sts_error;
938 -- Get message count and data
939 fnd_msg_pub.count_and_get
940 ( p_count => x_msg_count
941 , p_data => x_msg_data
942 );
943 WHEN fnd_api.g_exc_unexpected_error THEN
944 x_return_status := fnd_api.g_ret_sts_unexp_error ;
945
946 -- Get message count and data
947 fnd_msg_pub.count_and_get
948 ( p_count => x_msg_count
949 , p_data => x_msg_data
950 );
951 WHEN OTHERS THEN
952 x_return_status := fnd_api.g_ret_sts_unexp_error ;
953 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
954 THEN
955 fnd_msg_pub.add_exc_msg
956 ( g_pkg_name
957 , l_api_name
958 );
959 END IF;
960 -- Get message count and data
961 fnd_msg_pub.count_and_get
962 ( p_count => x_msg_count
963 , p_data => x_msg_data
964 );
965 END relieve_reservation;
966
967 -- INVCONV
968 -- Introduce new overload
969 -- Incorporate secondary quantities
970 -- ================================
971 PROCEDURE relieve_reservation
972 (
973 p_api_version_number IN NUMBER
974 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
975 , x_return_status OUT NOCOPY VARCHAR2
976 , x_msg_count OUT NOCOPY NUMBER
977 , x_msg_data OUT NOCOPY VARCHAR2
978 , p_rsv_rec
979 IN inv_reservation_global.mtl_reservation_rec_type
980 , p_primary_relieved_quantity IN NUMBER
981 , p_secondary_relieved_quantity IN NUMBER
982 , p_relieve_all IN VARCHAR2 DEFAULT fnd_api.g_true
983 , p_original_serial_number
984 IN inv_reservation_global.serial_number_tbl_type
985 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
986 , x_primary_relieved_quantity OUT NOCOPY NUMBER
987 , x_secondary_relieved_quantity OUT NOCOPY NUMBER
988 , x_primary_remain_quantity OUT NOCOPY NUMBER
989 , x_secondary_remain_quantity OUT NOCOPY NUMBER
990 ) IS
991 l_api_version_number CONSTANT NUMBER := 1.0;
992 l_api_name CONSTANT VARCHAR2(30) := 'Relieve_Reservation';
993 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
994 BEGIN
995 -- Standard call to check for call compatibility
996 IF NOT fnd_api.compatible_api_call(l_api_version_number
997 , p_api_version_number
998 , l_api_name
999 , G_PKG_NAME
1000 ) THEN
1001 RAISE fnd_api.g_exc_unexpected_error;
1002 END IF;
1003
1004 -- Initialize message list.
1005 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1006 fnd_msg_pub.initialize;
1007 END IF;
1008
1009 inv_reservation_pvt.relieve_reservation
1010 (
1011 p_api_version_number => 1.0
1012 , p_init_msg_lst => fnd_api.g_false
1013 , x_return_status => l_return_status
1014 , x_msg_count => x_msg_count
1015 , x_msg_data => x_msg_data
1016 , p_rsv_rec => p_rsv_rec
1017 , p_primary_relieved_quantity => p_primary_relieved_quantity
1018 , p_secondary_relieved_quantity => p_secondary_relieved_quantity
1019 , p_relieve_all => p_relieve_all
1020 , p_original_serial_number => p_original_serial_number
1021 , p_validation_flag => fnd_api.g_true
1022 , x_primary_relieved_quantity => x_primary_relieved_quantity
1023 , x_secondary_relieved_quantity => x_secondary_relieved_quantity
1024 , x_primary_remain_quantity => x_primary_remain_quantity
1025 , x_secondary_remain_quantity => x_secondary_remain_quantity
1026 );
1027
1028 IF l_return_status = fnd_api.g_ret_sts_error THEN
1029 RAISE fnd_api.g_exc_error;
1030 END IF ;
1031
1032 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1033 RAISE fnd_api.g_exc_unexpected_error;
1034 END IF;
1035
1036 x_return_status := l_return_status;
1037
1038 EXCEPTION
1039
1040 WHEN fnd_api.g_exc_error THEN
1041 x_return_status := fnd_api.g_ret_sts_error;
1042 -- Get message count and data
1043 fnd_msg_pub.count_and_get
1044 ( p_count => x_msg_count
1045 , p_data => x_msg_data
1046 );
1047 WHEN fnd_api.g_exc_unexpected_error THEN
1048 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1049
1050 -- Get message count and data
1051 fnd_msg_pub.count_and_get
1052 ( p_count => x_msg_count
1053 , p_data => x_msg_data
1054 );
1055 WHEN OTHERS THEN
1056 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1057 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1058 THEN
1059 fnd_msg_pub.add_exc_msg
1060 ( g_pkg_name
1061 , l_api_name
1062 );
1063 END IF;
1064 -- Get message count and data
1065 fnd_msg_pub.count_and_get
1066 ( p_count => x_msg_count
1067 , p_data => x_msg_data
1068 );
1069 END relieve_reservation;
1070 -- INVCONV END
1071
1072 --
1073 -- INVCONV BEGIN
1074 -- Strip out process forking logic
1075 PROCEDURE delete_reservation
1076 (
1077 p_api_version_number IN NUMBER
1078 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1079 , x_return_status OUT NOCOPY VARCHAR2
1080 , x_msg_count OUT NOCOPY NUMBER
1081 , x_msg_data OUT NOCOPY VARCHAR2
1082 , p_rsv_rec
1083 IN inv_reservation_global.mtl_reservation_rec_type
1084 , p_serial_number
1085 IN inv_reservation_global.serial_number_tbl_type
1086 )IS
1087 l_api_version_number CONSTANT NUMBER := 1.0;
1088 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Reservation';
1089 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1090 BEGIN
1091 -- Standard call to check for call compatibility
1092 IF NOT fnd_api.compatible_api_call(l_api_version_number
1093 , p_api_version_number
1094 , l_api_name
1095 , G_PKG_NAME
1096 ) THEN
1097 RAISE fnd_api.g_exc_unexpected_error;
1098 END IF;
1099
1100 -- Initialize message list.
1101 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1102 fnd_msg_pub.initialize;
1103 END IF;
1104
1105 inv_reservation_pvt.delete_reservation
1106 (
1107 p_api_version_number => 1.0
1108 , p_init_msg_lst => fnd_api.g_false
1109 , x_return_status => l_return_status
1110 , x_msg_count => x_msg_count
1111 , x_msg_data => x_msg_data
1112 , p_rsv_rec => p_rsv_rec
1113 , p_original_serial_number => p_serial_number
1114 , p_validation_flag => fnd_api.g_true
1115 );
1116
1117 IF l_return_status = fnd_api.g_ret_sts_error THEN
1118 RAISE fnd_api.g_exc_error;
1119 END IF ;
1120
1121 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1122 RAISE fnd_api.g_exc_unexpected_error;
1123 END IF;
1124
1125 x_return_status := l_return_status;
1126
1127 EXCEPTION
1128
1129 WHEN fnd_api.g_exc_error THEN
1130 x_return_status := fnd_api.g_ret_sts_error;
1131
1132 -- Get message count and data
1133 fnd_msg_pub.count_and_get
1134 ( p_count => x_msg_count
1135 , p_data => x_msg_data
1136 );
1137
1138 WHEN fnd_api.g_exc_unexpected_error THEN
1139 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1140
1141 -- Get message count and data
1142 fnd_msg_pub.count_and_get
1143 ( p_count => x_msg_count
1144 , p_data => x_msg_data
1145 );
1146
1147 WHEN OTHERS THEN
1148 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1149
1150 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1151 THEN
1152 fnd_msg_pub.add_exc_msg
1153 ( g_pkg_name
1154 , l_api_name
1155 );
1156 END IF;
1157
1158 -- Get message count and data
1159 fnd_msg_pub.count_and_get
1160 ( p_count => x_msg_count
1161 , p_data => x_msg_data
1162 );
1163
1164 END delete_reservation;
1165
1166 -- INVCONV
1167 -- Strip out process forking logic
1168 PROCEDURE transfer_reservation
1169 (
1170 p_api_version_number IN NUMBER
1171 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1172 , x_return_status OUT NOCOPY VARCHAR2
1173 , x_msg_count OUT NOCOPY NUMBER
1174 , x_msg_data OUT NOCOPY VARCHAR2
1175 , p_is_transfer_supply IN VARCHAR2 DEFAULT fnd_api.g_true
1176 , p_original_rsv_rec
1177 IN inv_reservation_global.mtl_reservation_rec_type
1178 , p_to_rsv_rec
1179 IN inv_reservation_global.mtl_reservation_rec_type
1180 , p_original_serial_number
1181 IN inv_reservation_global.serial_number_tbl_type
1182 , p_to_serial_number
1183 IN inv_reservation_global.serial_number_tbl_type
1184 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
1185 , p_over_reservation_flag IN NUMBER DEFAULT 0
1186 , x_to_reservation_id OUT NOCOPY NUMBER
1187 ) IS
1188 l_api_version_number CONSTANT NUMBER := 1.0;
1189 l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Reservation';
1190 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1191 BEGIN
1192 -- Standard call to check for call compatibility
1193 IF NOT fnd_api.compatible_api_call(l_api_version_number
1194 , p_api_version_number
1195 , l_api_name
1196 , G_PKG_NAME
1197 ) THEN
1198 RAISE fnd_api.g_exc_unexpected_error;
1199 END IF;
1200
1201 -- Initialize message list.
1202 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1203 fnd_msg_pub.initialize;
1204 END IF;
1205
1206 -- Start Process Branching Logic ----
1207 -- INVCONV - Process Branching Logic removed from here
1208 -- End Process Branching Logic ----
1209
1210 -- bug 1611697 - Performance
1211 -- Allow validation_flag to be false if false is passed to this api.
1212 -- Previously, always called pvt api with true.
1213 /**** {{ R12 Enhanced reservations code changes }}****/
1214 IF (p_to_serial_number.COUNT > 0 OR p_original_serial_number.COUNT > 0)
1215 THEN
1216 inv_reservation_pvt.transfer_reservation
1217 (
1218 p_api_version_number => 1.0
1219 , p_init_msg_lst => fnd_api.g_false
1220 , x_return_status => l_return_status
1221 , x_msg_count => x_msg_count
1222 , x_msg_data => x_msg_data
1223 , p_original_rsv_rec => p_original_rsv_rec
1224 , p_to_rsv_rec => p_to_rsv_rec
1225 , p_original_serial_number => p_original_serial_number
1226 , p_to_serial_number => p_to_serial_number
1227 , p_validation_flag => p_validation_flag
1228 , p_over_reservation_flag => p_over_reservation_flag
1229 , x_reservation_id => x_to_reservation_id
1230 );
1231 ELSE
1232 /*** End R12 ***/
1233 inv_reservation_pvt.transfer_reservation
1234 (
1235 p_api_version_number => 1.0
1236 , p_init_msg_lst => fnd_api.g_false
1237 , x_return_status => l_return_status
1238 , x_msg_count => x_msg_count
1239 , x_msg_data => x_msg_data
1240 , p_original_rsv_rec => p_original_rsv_rec
1241 , p_to_rsv_rec => p_to_rsv_rec
1242 , p_original_serial_number => p_original_serial_number
1243 , p_validation_flag => p_validation_flag
1244 , p_over_reservation_flag => p_over_reservation_flag
1245 , x_reservation_id => x_to_reservation_id
1246 );
1247 /**** {{ R12 Enhanced reservations code changes }}****/
1248 END IF;
1249 /*** End R12 ***/
1250 IF l_return_status = fnd_api.g_ret_sts_error THEN
1251 RAISE fnd_api.g_exc_error;
1252 END IF ;
1253
1254 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1255 RAISE fnd_api.g_exc_unexpected_error;
1256 END IF;
1257
1258 x_return_status := l_return_status;
1259
1260 EXCEPTION
1261
1262 WHEN fnd_api.g_exc_error THEN
1263 x_return_status := fnd_api.g_ret_sts_error;
1264
1265 -- Get message count and data
1266 fnd_msg_pub.count_and_get
1267 ( p_count => x_msg_count
1268 , p_data => x_msg_data
1269 );
1270
1271 WHEN fnd_api.g_exc_unexpected_error THEN
1272 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1273
1274 -- Get message count and data
1275 fnd_msg_pub.count_and_get
1276 ( p_count => x_msg_count
1277 , p_data => x_msg_data
1278 );
1279
1280 WHEN OTHERS THEN
1281 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1282
1283 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1284 THEN
1285 fnd_msg_pub.add_exc_msg
1286 ( g_pkg_name
1287 , l_api_name
1288 );
1289 END IF;
1290
1291 -- Get message count and data
1292 fnd_msg_pub.count_and_get
1293 ( p_count => x_msg_count
1294 , p_data => x_msg_data
1295 );
1296
1297 END transfer_reservation;
1298
1299 -- INVCONV
1300 -- Strip out process forking logic
1301 PROCEDURE query_reservation
1302 (
1303 p_api_version_number IN NUMBER
1304 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1305 , x_return_status OUT NOCOPY VARCHAR2
1306 , x_msg_count OUT NOCOPY NUMBER
1307 , x_msg_data OUT NOCOPY VARCHAR2
1308 , p_query_input
1309 IN inv_reservation_global.mtl_reservation_rec_type
1310 , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
1311 , p_sort_by_req_date
1312 IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
1313 , p_cancel_order_mode
1314 IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
1315 , x_mtl_reservation_tbl
1316 OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
1317 , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
1318 , x_error_code OUT NOCOPY NUMBER
1319 ) IS
1320 l_api_version_number CONSTANT NUMBER := 1.0;
1321 l_api_name CONSTANT VARCHAR2(30) := 'Query_Reservation';
1322 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1323 -- OPM BUG 1415345 BEGIN
1324 l_query_input inv_reservation_global.mtl_reservation_rec_type
1325 := p_query_input;
1326 -- OPM BUG 1415345 END
1327 BEGIN
1328 -- Standard call to check for call compatibility
1329 IF NOT fnd_api.compatible_api_call(l_api_version_number
1330 , p_api_version_number
1331 , l_api_name
1332 , G_PKG_NAME
1333 ) THEN
1334 RAISE fnd_api.g_exc_unexpected_error;
1335 END IF;
1336
1337 -- Initialize message list.
1338 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1339 fnd_msg_pub.initialize;
1340 END IF;
1341
1342 -- Start Process Branching Logic ----
1343 -- INVCONV - Remove Process Branching Logic
1344 -- End Process Branching Logic ----
1345
1346
1347 inv_reservation_pvt.query_reservation
1348 (
1349 p_api_version_number => 1.0
1350 , p_init_msg_lst => fnd_api.g_false
1351 , x_return_status => l_return_status
1352 , x_msg_count => x_msg_count
1353 , x_msg_data => x_msg_data
1354 , p_query_input => l_query_input
1355 , p_lock_records => p_lock_records
1356 , p_sort_by_req_date => p_sort_by_req_date
1357 , p_cancel_order_mode => p_cancel_order_mode
1358 , x_mtl_reservation_tbl => x_mtl_reservation_tbl
1359 , x_mtl_reservation_tbl_count => x_mtl_reservation_tbl_count
1360 , x_error_code => x_error_code
1361 );
1362
1363 IF l_return_status = fnd_api.g_ret_sts_error THEN
1364 RAISE fnd_api.g_exc_error;
1365 END IF ;
1366
1367 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1368 RAISE fnd_api.g_exc_unexpected_error;
1369 END IF;
1370
1371 x_return_status := l_return_status;
1372
1373 EXCEPTION
1374
1375 WHEN fnd_api.g_exc_error THEN
1376 x_return_status := fnd_api.g_ret_sts_error;
1377
1378 -- Get message count and data
1379 fnd_msg_pub.count_and_get
1380 ( p_count => x_msg_count
1381 , p_data => x_msg_data
1382 );
1383
1384 WHEN fnd_api.g_exc_unexpected_error THEN
1385 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1386
1387 -- Get message count and data
1388 fnd_msg_pub.count_and_get
1389 ( p_count => x_msg_count
1390 , p_data => x_msg_data
1391 );
1392
1393 WHEN OTHERS THEN
1394 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1395
1396 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1397 THEN
1398 fnd_msg_pub.add_exc_msg
1399 ( g_pkg_name
1400 , l_api_name
1401 );
1402 END IF;
1403
1404 -- Get message count and data
1405 fnd_msg_pub.count_and_get
1406 ( p_count => x_msg_count
1407 , p_data => x_msg_data
1408 );
1409
1410 END query_reservation;
1411
1412 -- INVCONV
1413 -- Strip out process forking logic
1414 /*
1415 ** ----------------------------------------------------------------------
1416 ** For Order Management(OM) use only. Please read below:
1417 ** MUST PASS DEMAND SOURCE HEADER ID AND DEMAND SOURCE LINE ID
1418 ** ----------------------------------------------------------------------
1419 ** This API has been written exclusively for Order Management, who query
1420 ** reservations extensively.
1421 ** The generic query reservation API, query_reservation(see signature above)
1422 ** builds a dynamic SQL to satisfy all callers as it does not know what the
1423 ** search criteria is, at design time.
1424 ** The dynamic SQL consumes soft parse time, which reduces performance.
1425 ** An excessive use of query_reservation contributes to performance
1426 ** degradation because of soft parse times.
1427 ** Since we know what OM would always use to query reservations
1428 ** - demand source header id and demand source line id, a new API
1429 ** with static SQL would be be effective, with reduced performance impact.
1430 ** ----------------------------------------------------------------------
1431 ** Since OM has been using query_reservation before this, the signature of the
1432 ** new API below remains the same to cause minimal impact.
1433 ** ----------------------------------------------------------------------
1434 */
1435 PROCEDURE query_reservation_om_hdr_line
1436 (
1437 p_api_version_number IN NUMBER
1438 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1439 , x_return_status OUT NOCOPY VARCHAR2
1440 , x_msg_count OUT NOCOPY NUMBER
1441 , x_msg_data OUT NOCOPY VARCHAR2
1442 , p_query_input
1443 IN inv_reservation_global.mtl_reservation_rec_type
1444 , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
1445 , p_sort_by_req_date
1446 IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
1447 , p_cancel_order_mode
1448 IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
1449 , x_mtl_reservation_tbl
1450 OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
1451 , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
1452 , x_error_code OUT NOCOPY NUMBER
1453 ) IS
1454 l_api_version_number CONSTANT NUMBER := 1.0;
1455 l_api_name CONSTANT VARCHAR2(30) := 'Query_Reservation';
1456 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1457 -- OPM BUG 1415345 BEGIN
1458 l_query_input inv_reservation_global.mtl_reservation_rec_type
1459 := p_query_input;
1460 -- OPM BUG 1415345 END
1461 BEGIN
1462 -- Standard call to check for call compatibility
1463 IF NOT fnd_api.compatible_api_call(l_api_version_number
1464 , p_api_version_number
1465 , l_api_name
1466 , G_PKG_NAME
1467 ) THEN
1468 RAISE fnd_api.g_exc_unexpected_error;
1469 END IF;
1470
1471 -- Initialize message list.
1472 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1473 fnd_msg_pub.initialize;
1474 END IF;
1475
1476
1477 -- Start Process Branching Logic ----
1478 -- INVCONV - Strip out process forking logic
1479 -- End Process Branching Logic ----
1480
1481 inv_reservation_pvt.query_reservation_om_hdr_line
1482 (
1483 p_api_version_number => 1.0
1484 , p_init_msg_lst => fnd_api.g_false
1485 , x_return_status => l_return_status
1486 , x_msg_count => x_msg_count
1487 , x_msg_data => x_msg_data
1488 , p_query_input => l_query_input
1489 , p_lock_records => p_lock_records
1490 , p_sort_by_req_date => p_sort_by_req_date
1491 , p_cancel_order_mode => p_cancel_order_mode
1492 , x_mtl_reservation_tbl => x_mtl_reservation_tbl
1493 , x_mtl_reservation_tbl_count => x_mtl_reservation_tbl_count
1494 , x_error_code => x_error_code
1495 );
1496
1497 IF l_return_status = fnd_api.g_ret_sts_error THEN
1498 RAISE fnd_api.g_exc_error;
1499 END IF ;
1500
1501 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1502 RAISE fnd_api.g_exc_unexpected_error;
1503 END IF;
1504
1505 x_return_status := l_return_status;
1506
1507 EXCEPTION
1508
1509 WHEN fnd_api.g_exc_error THEN
1510 x_return_status := fnd_api.g_ret_sts_error;
1511
1512 -- Get message count and data
1513 fnd_msg_pub.count_and_get
1514 ( p_count => x_msg_count
1515 , p_data => x_msg_data
1516 );
1517
1518 WHEN fnd_api.g_exc_unexpected_error THEN
1519 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1520
1521 -- Get message count and data
1522 fnd_msg_pub.count_and_get
1523 ( p_count => x_msg_count
1524 , p_data => x_msg_data
1525 );
1526
1527 WHEN OTHERS THEN
1528 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1529
1530 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1531 THEN
1532 fnd_msg_pub.add_exc_msg
1533 ( g_pkg_name
1534 , l_api_name
1535 );
1536 END IF;
1537
1538 -- Get message count and data
1539 fnd_msg_pub.count_and_get
1540 ( p_count => x_msg_count
1541 , p_data => x_msg_data
1542 );
1543
1544 END query_reservation_om_hdr_line;
1545
1546 END inv_reservation_pub;