[Home] [Help]
PACKAGE BODY: APPS.INV_QUANTITY_TREE_GRP
Source
1 PACKAGE BODY inv_quantity_tree_grp as
2 /* $Header: INVGQTTB.pls 120.0 2005/05/25 05:13:16 appldev noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_Quantity_Tree_GRP';
5
6 -- synonyms used in this program
7 -- qoh quantity on hand
8 -- rqoh reservable quantity on hand
9 -- qr quantity reserved
10 -- qs quantity suggested
11 -- att available to transact
12 -- atr available to reserve
13 -- sqoh secondary quantity on hand -- invConv change
14 -- srqoh secondary reservable quantity on hand -- invConv change
15 -- sqr secondary quantity reserved -- invConv change
16 -- sqs secondare quantity suggested -- invConv change
17 -- satt secondary available to transact -- invConv change
18 -- satr secondary available to reserve -- invConv change
19
20 -- Procedure
21 -- clear_quantity_cache
22 -- Description
23 -- Delete all quantity trees in the memory. Should be called when you call
24 -- rollback. Otherwise the trees in memory may not be in sync with the data
25 -- in the corresponding database tables
26 PROCEDURE clear_quantity_cache
27 IS
28 BEGIN
29 inv_quantity_tree_pvt.clear_quantity_cache;
30 END clear_quantity_cache;
31
32 -- Procedure
33 -- create_tree
34 -- Description
35 -- Create a quantity tree, if it does not exist, in memory based on the input
36 -- and return the tree id
37 PROCEDURE create_tree
38 ( p_api_version_number IN NUMBER
39 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
40 , x_return_status OUT NOCOPY VARCHAR2
41 , x_msg_count OUT NOCOPY NUMBER
42 , x_msg_data OUT NOCOPY VARCHAR2
43 , p_organization_id IN NUMBER
44 , p_inventory_item_id IN NUMBER
45 , p_tree_mode IN INTEGER
46 , p_is_revision_control IN BOOLEAN
47 , p_is_lot_control IN BOOLEAN
48 , p_is_serial_control IN BOOLEAN
49 , p_grade_code IN VARCHAR2 DEFAULT NULL -- invConv change
50 , p_asset_sub_only IN BOOLEAN DEFAULT FALSE
51 , p_include_suggestion IN BOOLEAN DEFAULT FALSE
52 , p_demand_source_type_id IN NUMBER DEFAULT -9999
53 , p_demand_source_header_id IN NUMBER DEFAULT -9999
54 , p_demand_source_line_id IN NUMBER DEFAULT -9999
55 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
56 , p_lot_expiration_date IN DATE DEFAULT NULL
57 , p_onhand_source IN NUMBER DEFAULT inv_quantity_tree_pvt.g_all_subs
58 , x_tree_id OUT NOCOPY INTEGER
59 ) IS
60 l_api_version_number CONSTANT NUMBER := 1.0;
61 l_api_name CONSTANT VARCHAR2(30) := 'Create_Tree';
62 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
63 BEGIN
64 -- Standard call to check for call compatibility
65 IF NOT fnd_api.compatible_api_call(l_api_version_number
66 , p_api_version_number
67 , l_api_name
68 , G_PKG_NAME
69 ) THEN
70 RAISE fnd_api.g_exc_unexpected_error;
71 END IF;
72
73 -- Initialize message list.
74 IF fnd_api.to_boolean(p_init_msg_lst) THEN
75 fnd_msg_pub.initialize;
76 END IF;
77
78 inv_quantity_tree_pvt.create_tree
79 (
80 p_api_version_number => 1.0
81 , p_init_msg_lst => fnd_api.g_false
82 , x_return_status => l_return_status
83 , x_msg_count => x_msg_count
84 , x_msg_data => x_msg_data
85 , p_organization_id => p_organization_id
86 , p_inventory_item_id => p_inventory_item_id
87 , p_tree_mode => p_tree_mode
88 , p_is_revision_control => p_is_revision_control
89 , p_is_lot_control => p_is_lot_control
90 , p_is_serial_control => p_is_serial_control
91 , p_grade_code => p_grade_code
92 , p_asset_sub_only => p_asset_sub_only
93 , p_include_suggestion => p_include_suggestion
94 , p_demand_source_type_id => p_demand_source_type_id
95 , p_demand_source_header_id => p_demand_source_header_id
96 , p_demand_source_line_id => p_demand_source_line_id
97 , p_demand_source_name => p_demand_source_name
98 , p_lot_expiration_date => p_lot_expiration_date
99 , p_onhand_source => p_onhand_source
100 , x_tree_id => x_tree_id
101 );
102
103 IF l_return_status = fnd_api.g_ret_sts_error THEN
104 RAISE fnd_api.g_exc_error;
105 END IF ;
106
107 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
108 RAISE fnd_api.g_exc_unexpected_error;
109 END IF;
110
111 x_return_status := l_return_status;
112
113 EXCEPTION
114
115 WHEN fnd_api.g_exc_error THEN
116 x_return_status := fnd_api.g_ret_sts_error;
117
118 -- Get message count and data
119 fnd_msg_pub.count_and_get
120 ( p_count => x_msg_count
121 , p_data => x_msg_data
122 );
123
124 WHEN fnd_api.g_exc_unexpected_error THEN
125 x_return_status := fnd_api.g_ret_sts_unexp_error ;
126
127 -- Get message count and data
128 fnd_msg_pub.count_and_get
129 ( p_count => x_msg_count
130 , p_data => x_msg_data
131 );
132
133 WHEN OTHERS THEN
134 x_return_status := fnd_api.g_ret_sts_unexp_error ;
135
136 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
137 THEN
138 fnd_msg_pub.add_exc_msg
139 ( g_pkg_name
140 , l_api_name
141 );
142 END IF;
143
144 -- Get message count and data
145 fnd_msg_pub.count_and_get
146 ( p_count => x_msg_count
147 , p_data => x_msg_data
148 );
149
150 END create_tree;
151
152 -- Procedure
153 -- query_tree
154 -- Description
155 -- Query a quantity tree for quantity information at the level
156 -- specified by the input
157 PROCEDURE query_tree
158 ( p_api_version_number IN NUMBER
159 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
160 , x_return_status OUT NOCOPY VARCHAR2
161 , x_msg_count OUT NOCOPY NUMBER
162 , x_msg_data OUT NOCOPY VARCHAR2
163 , p_tree_id IN INTEGER
164 , p_revision IN VARCHAR2
165 , p_lot_number IN VARCHAR2
166 , p_subinventory_code IN VARCHAR2
167 , p_locator_id IN NUMBER
168 , x_qoh OUT NOCOPY NUMBER
169 , x_rqoh OUT NOCOPY NUMBER
170 , x_qr OUT NOCOPY NUMBER
171 , x_qs OUT NOCOPY NUMBER
172 , x_att OUT NOCOPY NUMBER
173 , x_atr OUT NOCOPY NUMBER
174 , p_transfer_subinventory_code IN VARCHAR2
175 , p_cost_group_id IN NUMBER DEFAULT NULL
176 , p_lpn_id IN NUMBER DEFAULT NULL
177 , p_transfer_locator_id IN NUMBER DEFAULT NULL
178 ) IS
179
180 l_sqoh NUMBER;
181 l_srqoh NUMBER;
182 l_sqr NUMBER;
183 l_sqs NUMBER;
184 l_satt NUMBER;
185 l_satr NUMBER;
186
187 BEGIN
188
189 -- invConv changes begin:
190 -- Calling the new signature API.
191 inv_quantity_tree_grp.query_tree
192 ( p_api_version_number => p_api_version_number
193 , p_init_msg_lst => p_init_msg_lst
194 , x_return_status => x_return_status
195 , x_msg_count => x_msg_count
196 , x_msg_data => x_msg_data
197 , p_tree_id => p_tree_id
198 , p_revision => p_revision
199 , p_lot_number => p_lot_number
200 , p_subinventory_code => p_subinventory_code
201 , p_locator_id => p_locator_id
202 , x_qoh => x_qoh
203 , x_rqoh => x_rqoh
204 , x_qr => x_qr
205 , x_qs => x_qs
206 , x_att => x_att
207 , x_atr => x_atr
208 , x_sqoh => l_sqoh -- invConv change
209 , x_srqoh => l_srqoh -- invConv change
210 , x_sqr => l_sqr -- invConv change
211 , x_sqs => l_sqs -- invConv change
212 , x_satt => l_satt -- invConv change
213 , x_satr => l_satr -- invConv change
214 , p_transfer_subinventory_code => p_transfer_subinventory_code
215 , p_cost_group_id => p_cost_group_id
216 , p_lpn_id => p_lpn_id
217 , p_transfer_locator_id => p_transfer_locator_id
218 );
219
220 END query_tree;
221
222 -- invConv changes begin
223 PROCEDURE query_tree
224 ( p_api_version_number IN NUMBER
225 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
226 , x_return_status OUT NOCOPY VARCHAR2
227 , x_msg_count OUT NOCOPY NUMBER
228 , x_msg_data OUT NOCOPY VARCHAR2
229 , p_tree_id IN INTEGER
230 , p_revision IN VARCHAR2
231 , p_lot_number IN VARCHAR2
232 , p_subinventory_code IN VARCHAR2
233 , p_locator_id IN NUMBER
234 , x_qoh OUT NOCOPY NUMBER
235 , x_rqoh OUT NOCOPY NUMBER
236 , x_qr OUT NOCOPY NUMBER
237 , x_qs OUT NOCOPY NUMBER
238 , x_att OUT NOCOPY NUMBER
239 , x_atr OUT NOCOPY NUMBER
240 , x_sqoh OUT NOCOPY NUMBER -- invConv change
241 , x_srqoh OUT NOCOPY NUMBER -- invConv change
242 , x_sqr OUT NOCOPY NUMBER -- invConv change
243 , x_sqs OUT NOCOPY NUMBER -- invConv change
244 , x_satt OUT NOCOPY NUMBER -- invConv change
245 , x_satr OUT NOCOPY NUMBER -- invConv change
246 , p_transfer_subinventory_code IN VARCHAR2
247 , p_cost_group_id IN NUMBER DEFAULT NULL
248 , p_lpn_id IN NUMBER DEFAULT NULL
249 , p_transfer_locator_id IN NUMBER DEFAULT NULL
250 ) IS
251 l_api_version_number CONSTANT NUMBER := 1.0;
252 l_api_name CONSTANT VARCHAR2(30) := 'Query_Tree';
253 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
254 BEGIN
255 -- Standard call to check for call compatibility
256 IF NOT fnd_api.compatible_api_call(l_api_version_number
257 , p_api_version_number
258 , l_api_name
259 , G_PKG_NAME
260 ) THEN
261 RAISE fnd_api.g_exc_unexpected_error;
262 END IF;
263
264 -- Initialize message list.
265 IF fnd_api.to_boolean(p_init_msg_lst) THEN
266 fnd_msg_pub.initialize;
267 END IF;
268
269 inv_quantity_tree_pvt.query_tree
270 (
271 p_api_version_number => 1.0
272 , p_init_msg_lst => fnd_api.g_false
273 , x_return_status => l_return_status
274 , x_msg_count => x_msg_count
275 , x_msg_data => x_msg_data
276 , p_tree_id => p_tree_id
277 , p_revision => p_revision
278 , p_lot_number => p_lot_number
279 , p_subinventory_code => p_subinventory_code
280 , p_locator_id => p_locator_id
281 , x_qoh => x_qoh
282 , x_rqoh => x_rqoh
283 , x_qr => x_qr
284 , x_qs => x_qs
285 , x_att => x_att
286 , x_atr => x_atr
287 , x_sqoh => x_sqoh -- invConv change
288 , x_srqoh => x_srqoh -- invConv change
289 , x_sqr => x_sqr -- invConv change
290 , x_sqs => x_sqs -- invConv change
291 , x_satt => x_satt -- invConv change
292 , x_satr => x_satr -- invConv change
293 , p_transfer_subinventory_code => p_transfer_subinventory_code
294 , p_cost_group_id => p_cost_group_id
295 , p_lpn_id => p_lpn_id
296 , p_transfer_locator_id => p_transfer_locator_id
297 );
298
299 IF l_return_status = fnd_api.g_ret_sts_error THEN
300 RAISE fnd_api.g_exc_error;
301 END IF ;
302
303 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
304 RAISE fnd_api.g_exc_unexpected_error;
305 END IF;
306
307 x_return_status := l_return_status;
308
309 EXCEPTION
310
311 WHEN fnd_api.g_exc_error THEN
315 fnd_msg_pub.count_and_get
312 x_return_status := fnd_api.g_ret_sts_error;
313
314 -- Get message count and data
316 ( p_count => x_msg_count
317 , p_data => x_msg_data
318 );
319
320 WHEN fnd_api.g_exc_unexpected_error THEN
321 x_return_status := fnd_api.g_ret_sts_unexp_error ;
322
323 -- Get message count and data
324 fnd_msg_pub.count_and_get
325 ( p_count => x_msg_count
326 , p_data => x_msg_data
327 );
328
329 WHEN OTHERS THEN
330 x_return_status := fnd_api.g_ret_sts_unexp_error ;
331
332 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
333 THEN
334 fnd_msg_pub.add_exc_msg
335 ( g_pkg_name
336 , l_api_name
337 );
338 END IF;
339
340 -- Get message count and data
341 fnd_msg_pub.count_and_get
342 ( p_count => x_msg_count
343 , p_data => x_msg_data
344 );
345
346 END query_tree;
347
348 -- Procedure
349 -- update_quantities
350 -- Description
351 -- Update quantity at the level specified by the input and
352 -- return the quantities at the level after the update
353 PROCEDURE update_quantities
354 ( p_api_version_number IN NUMBER
355 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
356 , x_return_status OUT NOCOPY VARCHAR2
357 , x_msg_count OUT NOCOPY NUMBER
358 , x_msg_data OUT NOCOPY VARCHAR2
359 , p_tree_id IN INTEGER
360 , p_revision IN VARCHAR2 DEFAULT NULL
361 , p_lot_number IN VARCHAR2 DEFAULT NULL
362 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
363 , p_locator_id IN NUMBER DEFAULT NULL
364 , p_primary_quantity IN NUMBER
365 , p_quantity_type IN INTEGER
366 , x_qoh OUT NOCOPY NUMBER
367 , x_rqoh OUT NOCOPY NUMBER
368 , x_qr OUT NOCOPY NUMBER
369 , x_qs OUT NOCOPY NUMBER
370 , x_att OUT NOCOPY NUMBER
371 , x_atr OUT NOCOPY NUMBER
372 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
373 , p_cost_group_id IN NUMBER DEFAULT NULL
374 , p_containerized IN NUMBER DEFAULT inv_quantity_tree_pvt.g_containerized_false
375 , p_lpn_id IN NUMBER DEFAULT NULL
376 , p_transfer_locator_id IN NUMBER DEFAULT NULL
377 ) IS
378
379 l_sqoh NUMBER;
380 l_srqoh NUMBER;
381 l_sqr NUMBER;
382 l_sqs NUMBER;
383 l_satt NUMBER;
384 l_satr NUMBER;
385 l_secondary_quantity NUMBER;
386
387 BEGIN
388
389 -- invConv changes begin : calling the overload procedure
390 update_quantities
391 ( p_api_version_number => p_api_version_number
392 , p_init_msg_lst => p_init_msg_lst
393 , x_return_status => x_return_status
394 , x_msg_count => x_msg_count
395 , x_msg_data => x_msg_data
396 , p_tree_id => p_tree_id
397 , p_revision => p_revision
398 , p_lot_number => p_lot_number
399 , p_subinventory_code => p_subinventory_code
400 , p_locator_id => p_locator_id
401 , p_primary_quantity => p_primary_quantity
402 , p_secondary_quantity => l_secondary_quantity -- invConv change
403 , p_quantity_type => p_quantity_type
404 , x_qoh => x_qoh
405 , x_rqoh => x_rqoh
406 , x_qr => x_qr
407 , x_qs => x_qs
408 , x_att => x_att
409 , x_atr => x_atr
410 , x_sqoh => l_sqoh -- invConv change
411 , x_srqoh => l_srqoh -- invConv change
412 , x_sqr => l_sqr -- invConv change
413 , x_sqs => l_sqs -- invConv change
414 , x_satt => l_satt -- invConv change
415 , x_satr => l_satr -- invConv change
416 , p_transfer_subinventory_code => p_transfer_subinventory_code
417 , p_cost_group_id => p_cost_group_id
418 , p_containerized => p_containerized
419 , p_lpn_id => p_lpn_id
420 , p_transfer_locator_id => p_transfer_locator_id
421 );
422 -- invConv changes end.
423
424 END update_quantities;
425
426 -- invConv changes begin : overload
427 PROCEDURE update_quantities
428 ( p_api_version_number IN NUMBER
429 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
430 , x_return_status OUT NOCOPY VARCHAR2
431 , x_msg_count OUT NOCOPY NUMBER
432 , x_msg_data OUT NOCOPY VARCHAR2
433 , p_tree_id IN INTEGER
434 , p_revision IN VARCHAR2 DEFAULT NULL
435 , p_lot_number IN VARCHAR2 DEFAULT NULL
436 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
437 , p_locator_id IN NUMBER DEFAULT NULL
438 , p_primary_quantity IN NUMBER
439 , p_secondary_quantity IN NUMBER -- invConv change
440 , p_quantity_type IN INTEGER
441 , x_qoh OUT NOCOPY NUMBER
442 , x_rqoh OUT NOCOPY NUMBER
443 , x_qr OUT NOCOPY NUMBER
444 , x_qs OUT NOCOPY NUMBER
445 , x_att OUT NOCOPY NUMBER
446 , x_atr OUT NOCOPY NUMBER
447 , x_sqoh OUT NOCOPY NUMBER -- invConv change
451 , x_satt OUT NOCOPY NUMBER -- invConv change
448 , x_srqoh OUT NOCOPY NUMBER -- invConv change
449 , x_sqr OUT NOCOPY NUMBER -- invConv change
450 , x_sqs OUT NOCOPY NUMBER -- invConv change
452 , x_satr OUT NOCOPY NUMBER -- invConv change
453 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
454 , p_cost_group_id IN NUMBER DEFAULT NULL
455 , p_containerized IN NUMBER DEFAULT inv_quantity_tree_pvt.g_containerized_false
456 , p_lpn_id IN NUMBER DEFAULT NULL
457 , p_transfer_locator_id IN NUMBER DEFAULT NULL
458 ) IS
459 l_api_version_number CONSTANT NUMBER := 1.0;
460 l_api_name CONSTANT VARCHAR2(30) := 'Update_Quantities';
461 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
462 BEGIN
463 -- Standard call to check for call compatibility
464 IF NOT fnd_api.compatible_api_call(l_api_version_number
465 , p_api_version_number
466 , l_api_name
467 , G_PKG_NAME
468 ) THEN
469 RAISE fnd_api.g_exc_unexpected_error;
470 END IF;
471
472 -- Initialize message list.
473 IF fnd_api.to_boolean(p_init_msg_lst) THEN
474 fnd_msg_pub.initialize;
475 END IF;
476
477 inv_quantity_tree_pvt.update_quantities
478 (
479 p_api_version_number => 1.0
480 , p_init_msg_lst => fnd_api.g_false
481 , x_return_status => l_return_status
482 , x_msg_count => x_msg_count
483 , x_msg_data => x_msg_data
484 , p_tree_id => p_tree_id
485 , p_revision => p_revision
486 , p_lot_number => p_lot_number
487 , p_subinventory_code => p_subinventory_code
488 , p_locator_id => p_locator_id
489 , p_primary_quantity => p_primary_quantity
490 , p_secondary_quantity => p_secondary_quantity -- invConv change
491 , p_quantity_type => p_quantity_type
492 , x_qoh => x_qoh
493 , x_rqoh => x_rqoh
494 , x_qr => x_qr
495 , x_qs => x_qs
496 , x_att => x_att
497 , x_atr => x_atr
498 , x_sqoh => x_sqoh -- invConv change
499 , x_srqoh => x_srqoh -- invConv change
500 , x_sqr => x_sqr -- invConv change
501 , x_sqs => x_sqs -- invConv change
502 , x_satt => x_satt -- invConv change
503 , x_satr => x_satr -- invConv change
504 , p_transfer_subinventory_code => p_subinventory_code
505 , p_cost_group_id => p_cost_group_id
506 , p_containerized => p_containerized
507 , p_lpn_id => p_lpn_id
508 , p_transfer_locator_id => p_transfer_locator_id
509 );
510
511 IF l_return_status = fnd_api.g_ret_sts_error THEN
512 RAISE fnd_api.g_exc_error;
513 END IF ;
514
515 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
516 RAISE fnd_api.g_exc_unexpected_error;
517 END IF;
518
519 x_return_status := l_return_status;
520
521 EXCEPTION
522
523 WHEN fnd_api.g_exc_error THEN
524 x_return_status := fnd_api.g_ret_sts_error;
525
526 -- Get message count and data
527 fnd_msg_pub.count_and_get
528 ( p_count => x_msg_count
529 , p_data => x_msg_data
530 );
531
532 WHEN fnd_api.g_exc_unexpected_error THEN
533 x_return_status := fnd_api.g_ret_sts_unexp_error ;
534
535 -- Get message count and data
536 fnd_msg_pub.count_and_get
537 ( p_count => x_msg_count
538 , p_data => x_msg_data
539 );
540
541 WHEN OTHERS THEN
542 x_return_status := fnd_api.g_ret_sts_unexp_error ;
543
544 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
545 THEN
546 fnd_msg_pub.add_exc_msg
547 ( g_pkg_name
548 , l_api_name
549 );
550 END IF;
551
552 -- Get message count and data
553 fnd_msg_pub.count_and_get
554 ( p_count => x_msg_count
555 , p_data => x_msg_data
556 );
557
558 END update_quantities;
559
560 -- Procedure
561 -- do_check
562 -- Description
563 -- check whether the updates done in a tree so far are still valid
564 -- Return
565 -- x_no_violation = true if no violation has found
566 -- , otherwise = false
567 PROCEDURE do_check
568 ( p_api_version_number IN NUMBER
569 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
570 , x_return_status OUT NOCOPY VARCHAR2
571 , x_msg_count OUT NOCOPY NUMBER
572 , x_msg_data OUT NOCOPY VARCHAR2
573 , p_tree_id IN INTEGER
574 , x_no_violation OUT NOCOPY BOOLEAN
575 ) IS
576 l_api_version_number CONSTANT NUMBER := 1.0;
577 l_api_name CONSTANT VARCHAR2(30) := 'Do_Check';
578 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
579 BEGIN
580 -- Standard call to check for call compatibility
581 IF NOT fnd_api.compatible_api_call(l_api_version_number
582 , p_api_version_number
586 RAISE fnd_api.g_exc_unexpected_error;
583 , l_api_name
584 , G_PKG_NAME
585 ) THEN
587 END IF;
588
589 -- Initialize message list.
590 IF fnd_api.to_boolean(p_init_msg_lst) THEN
591 fnd_msg_pub.initialize;
592 END IF;
593
594 inv_quantity_tree_pvt.do_check
595 (
596 p_api_version_number => 1.0
597 , p_init_msg_lst => fnd_api.g_false
598 , x_return_status => l_return_status
599 , x_msg_count => x_msg_count
600 , x_msg_data => x_msg_data
601 , p_tree_id => p_tree_id
602 , x_no_violation => x_no_violation
603 );
604
605 IF l_return_status = fnd_api.g_ret_sts_error THEN
606 RAISE fnd_api.g_exc_error;
607 END IF ;
608
609 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
610 RAISE fnd_api.g_exc_unexpected_error;
611 END IF;
612
613 x_return_status := l_return_status;
614
615 EXCEPTION
616
617 WHEN fnd_api.g_exc_error THEN
618 x_return_status := fnd_api.g_ret_sts_error;
619
620 -- Get message count and data
621 fnd_msg_pub.count_and_get
622 ( p_count => x_msg_count
623 , p_data => x_msg_data
624 );
625
626 WHEN fnd_api.g_exc_unexpected_error THEN
627 x_return_status := fnd_api.g_ret_sts_unexp_error ;
628
629 -- Get message count and data
630 fnd_msg_pub.count_and_get
631 ( p_count => x_msg_count
632 , p_data => x_msg_data
633 );
634
635 WHEN OTHERS THEN
636 x_return_status := fnd_api.g_ret_sts_unexp_error ;
637
638 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
639 THEN
640 fnd_msg_pub.add_exc_msg
641 ( g_pkg_name
642 , l_api_name
643 );
644 END IF;
645
646 -- Get message count and data
647 fnd_msg_pub.count_and_get
648 ( p_count => x_msg_count
649 , p_data => x_msg_data
650 );
651
652 END do_check;
653
654 -- Procedure
655 -- do_check
656 -- Description
657 -- check whether the updates done in all trees so far are still valid
658 -- Return
659 -- x_no_violation = true if no violation has found
660 -- , otherwise = false
661 PROCEDURE do_check
662 ( p_api_version_number IN NUMBER
663 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
664 , x_return_status OUT NOCOPY VARCHAR2
665 , x_msg_count OUT NOCOPY NUMBER
666 , x_msg_data OUT NOCOPY VARCHAR2
667 , x_no_violation OUT NOCOPY BOOLEAN
668 ) IS
669 l_api_version_number CONSTANT NUMBER := 1.0;
670 l_api_name CONSTANT VARCHAR2(30) := 'Do_Check';
671 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
672 BEGIN
673 -- Standard call to check for call compatibility
674 IF NOT fnd_api.compatible_api_call(l_api_version_number
675 , p_api_version_number
676 , l_api_name
677 , G_PKG_NAME
678 ) THEN
679 RAISE fnd_api.g_exc_unexpected_error;
680 END IF;
681
682 -- Initialize message list.
683 IF fnd_api.to_boolean(p_init_msg_lst) THEN
684 fnd_msg_pub.initialize;
685 END IF;
686
687 inv_quantity_tree_pvt.do_check
688 (
689 p_api_version_number => 1.0
690 , p_init_msg_lst => fnd_api.g_false
691 , x_return_status => l_return_status
692 , x_msg_count => x_msg_count
693 , x_msg_data => x_msg_data
694 , x_no_violation => x_no_violation
695 );
696
697 IF l_return_status = fnd_api.g_ret_sts_error THEN
698 RAISE fnd_api.g_exc_error;
699 END IF ;
700
701 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
702 RAISE fnd_api.g_exc_unexpected_error;
703 END IF;
704
705 x_return_status := l_return_status;
706
707 EXCEPTION
708
709 WHEN fnd_api.g_exc_error THEN
710 x_return_status := fnd_api.g_ret_sts_error;
711
712 -- Get message count and data
713 fnd_msg_pub.count_and_get
714 ( p_count => x_msg_count
715 , p_data => x_msg_data
716 );
717
718 WHEN fnd_api.g_exc_unexpected_error THEN
719 x_return_status := fnd_api.g_ret_sts_unexp_error ;
720
721 -- Get message count and data
722 fnd_msg_pub.count_and_get
723 ( p_count => x_msg_count
724 , p_data => x_msg_data
725 );
726
727 WHEN OTHERS THEN
728 x_return_status := fnd_api.g_ret_sts_unexp_error ;
729
730 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
731 THEN
732 fnd_msg_pub.add_exc_msg
733 ( g_pkg_name
734 , l_api_name
735 );
736 END IF;
737
738 -- Get message count and data
739 fnd_msg_pub.count_and_get
740 ( p_count => x_msg_count
741 , p_data => x_msg_data
742 );
743
744 END do_check;
745
746 -- Procedure
747 -- free_tree
748 -- Description
752 -- but have not make corresponding changes to the underlying database
749 -- Free a tree when no longer needed.
750 -- Warning
751 -- If you have called update_quantities to change quantity on the tree
753 -- tables, these changes are lost when you call free_tree.
754 PROCEDURE free_tree
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_tree_id IN INTEGER
761 ) IS
762 l_api_version_number CONSTANT NUMBER := 1.0;
763 l_api_name CONSTANT VARCHAR2(30) := 'Free_Tree';
764 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
765 BEGIN
766 -- Standard call to check for call compatibility
767 IF NOT fnd_api.compatible_api_call(l_api_version_number
768 , p_api_version_number
769 , l_api_name
770 , G_PKG_NAME
771 ) THEN
772 RAISE fnd_api.g_exc_unexpected_error;
773 END IF;
774
775 -- Initialize message list.
776 IF fnd_api.to_boolean(p_init_msg_lst) THEN
777 fnd_msg_pub.initialize;
778 END IF;
779
780 inv_quantity_tree_pvt.free_tree
781 (
782 p_api_version_number => 1.0
783 , p_init_msg_lst => fnd_api.g_false
784 , x_return_status => l_return_status
785 , x_msg_count => x_msg_count
786 , x_msg_data => x_msg_data
787 , p_tree_id => p_tree_id
788 );
789
790 IF l_return_status = fnd_api.g_ret_sts_error THEN
791 RAISE fnd_api.g_exc_error;
792 END IF ;
793
794 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
795 RAISE fnd_api.g_exc_unexpected_error;
796 END IF;
797
798 x_return_status := l_return_status;
799
800 EXCEPTION
801
802 WHEN fnd_api.g_exc_error THEN
803 x_return_status := fnd_api.g_ret_sts_error;
804
805 -- Get message count and data
806 fnd_msg_pub.count_and_get
807 ( p_count => x_msg_count
808 , p_data => x_msg_data
809 );
810
811 WHEN fnd_api.g_exc_unexpected_error THEN
812 x_return_status := fnd_api.g_ret_sts_unexp_error ;
813
814 -- Get message count and data
815 fnd_msg_pub.count_and_get
816 ( p_count => x_msg_count
817 , p_data => x_msg_data
818 );
819
820 WHEN OTHERS THEN
821 x_return_status := fnd_api.g_ret_sts_unexp_error ;
822
823 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
824 THEN
825 fnd_msg_pub.add_exc_msg
826 ( g_pkg_name
827 , l_api_name
828 );
829 END IF;
830
831 -- Get message count and data
832 fnd_msg_pub.count_and_get
833 ( p_count => x_msg_count
834 , p_data => x_msg_data
835 );
836
837 END free_tree;
838
839 -- Procedure
840 -- backup_tree
841 -- Description
842 -- backup the current state of a tree
843 -- Note
844 -- This is only a one level backup. Calling it twice will
845 -- overwrite the previous backup
846 PROCEDURE backup_tree
847 (
848 x_return_status OUT NOCOPY VARCHAR2
849 , p_tree_id IN INTEGER
850 ) IS
851 BEGIN
852 inv_quantity_tree_pvt.backup_tree(x_return_status, p_tree_id);
853 END backup_tree;
854
855 -- Procedure
856 -- restore_tree
857 -- Description
858 -- restore the current state of a tree to the state
859 -- at the last time when savepoint_tree is called
860 -- Note
861 -- This is only a one level restore. Calling it more than once
862 -- has the same effect as calling it once.
863 PROCEDURE restore_tree
864 (
865 x_return_status OUT NOCOPY VARCHAR2
866 , p_tree_id IN INTEGER
867 ) IS
868 BEGIN
869 inv_quantity_tree_pvt.restore_tree(x_return_status, p_tree_id);
870 END restore_tree;
871
872 END inv_quantity_tree_grp;