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